Formula Excel

Kali ini saya akan mencoba membedah source code dari salah satu fitur Tunas Gadget yaitu Change Case. Change Case bertujuan untuk merubah huruf besar dan kecil pada sel Excel menjadi HURUF BESAR (UPPERCASE), huruf kecil (lower case), Huruf kalimat (Sentense case) dan Huruf Judul (Title Case). Jika fitur ini mudah didapatkan di Microsoft Word, maka untuk di Microsoft Excel kita bisa menggunakan fitur ini.

Di sini saya menggunakan form sebagai Graphic User Interface (GUI) atau antar muka agar kita bisa lebih mudah menjalankan fungsinya dibanding kita menggunakan function-function yang terpisah. Karena dalam fitur Change Case ini terdapat 8 function sehingga akan lebih mudah jika kita menggunakan form daripada 8 function tersebut.

Kita dapat membuat sebuah file baru yang disimpan sebagai file Excel Add-in atau sebagai file excel biasa. Saran saya kita memilih sebagai file Excel Add-in.

Selanjutnya kita membuat form dengan menekan tombol Alt + F8 (Microsoft Visual Basic) lalu Insert > User Form sehingga muncul seperti gambar berikut:

Setelah sekian lama tidak sempat “menengok” blog ini, ternyata jumlah kunjungan blog Formula Excel ini semakin meningkat. Saat saya menulis post ini terdapat sebanyak 16.939 kunjungan dan total halaman yang dilihat (pageviews) sebesar 46.942 sejak 1 Januari 2011 hingga 19 November 2011 yang berarti +/- 1539 kunjungan per bulan. Memang jumlah ini bukan angka yang besar karena hanya terdapat 2,77 halaman per kunjungan, namun bukankah semua harus disyukuri? :)

Di lihat dari grafiknya, kunjungan meningkat mulai bulan September hingga November 2011 ini. Berikut grafiknya.

Mengkopi banyak sheet memang mudah dengan menu Move or Copy sheet. Tapi bagaimana jika ingin copy isinya saja dengan jumlah sheet yang banyak? Apakah Anda akan melakukan Copy dan Paste satu per satu sheet?

Kenapa harus copy isinya saja? Kenapa tidak copy sheet saja biar mudah dan tidak repot?

Jika Anda mempunyai file dengan banyak sheet di mana terdapat sheet rekap yang berisi rumus yang link dengan sheet-sheet tersebut, tentunya jika melakukan Copy Sheet dan Delete Sheet rumus Anda akan menjadi #REF! semua!

Biar mempermudah, berikut gambarannya:

File Sumber: Adalah file yang berisi data mentah yang tidak terdapat rumus apapun. Nama dan jumlah sheet adalah baku, misal terdapat 10 cabang dengan kode cabang BD, MT, SN, CD, AY, PK, PG, GR, BU, dan TP.

• Pada file target buka Visual Basic Editor (Alt + F11)
• Pilih Insert > Module
• Tuliskan kode macro berikut:

• copy_data() yang fungsinya untuk mengkopi sheet BD, MT, SN, CD, AY, PK, PG, GR, BU, TP dari file sumber ke file target
• delete_data() yang fungsinya untuk memilih sheet pada file target dan menjalankan macro  clear_format.
• clear_format() yang fungsinya untuk menghapus isi (clear content - bukan hapus baris/kolom).

Hasil rumus yang error seperti #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, atau #NULL! selain akan membuat tampilan tabel Excel Anda tidak enak dipandang juga tidak akan bisa dijumlahkan. Tapi hanya dengan rumus sederhana IFERROR maka hasilnya akan menjadi lebih baik.

Syntaxnya adalah =IFERROR(value, value_if_error)

• Value = nilai atau sel yang error
• Value_If_Error = nilai yang akan dimunculkan jika sel tersebut error. Bisa diisi dengan kosong "", nol (0), atau teks apa saja, misal "Asset Tidak Ada"

• F14: =IFERROR(VLOOKUP(E14,$A$2:$C$83,2,0),"Asset Tidak Ada")
• Jika hasil dari (VLOOKUP(E14,$A$2:$C$83,2,0) adalah #N/A maka yang muncul adalah "Asset Tidak Ada".
• Namun jika hasilnya tidak #N/A, maka keterangan yang muncul adalah hasil dari VLOOKUP. 
• G14: =IFERROR(VLOOKUP(E14,$A$2:$C$83,3,0),0)
• Jika hasil dari (VLOOKUP(E14,$A$2:$C$83,3,0) adalah #N/A maka yang muncul adalah angka nol (0).
• Namun jika hasilnya tidak #N/A, maka keterangan yang muncul adalah hasil dari VLOOKUP.

Apakah Anda pernah menggunakan fungsi TEXT? Fungsi TEXT adalah fungsi yang disediakan oleh Excel untuk merubah format angka, tanggal, persentase, waktu, dll menjadi format yang mudah dibaca ketika digabungkan dengan teks yang lain.

Syntaxnya mudah saja:   =TEXT(value, format_text)

• Value = nilai yang akan diformat, bisa dirujuk ke sebuah sel.
• Format_Text = Format yang akan digunakan.

• A1: 3/8/2011 dengan format sel "d-mmm-yy"
• B1: ="Sales "&TEXT(A1,"mmmm yyyy")
• Menggabungkan kata "Sales " dengan tanggal di A1 yang formatnya adalah "mmmm yyyy" sehingga menghasilkan kalimat "Sales March 2011"
• Jika tanpa menggunakan rumus TEXT maka hasilnya akan menjadi "Sales 40610"
• B3: ="Purchase "&TEXT(A1,"mmm-yy")
• Menggunakan format yang lain yaitu "mmm-yy" sehingga menghasilkan kalimat "Purchase Mar-11"
• A6: 10000 dengan format sel Accounting - Decimal Places = 2
• B6: ="Total Pembayaran "&TEXT(A6,"Rp 0.00")
• Menggabungkan kalimat "Total Pembayaran " dengan angka di A6 yang formatnya adalah "Rp 0.00" sehingga menghasilkan kalimat "Total Pembayaran Rp 10000.00"
• Jika tanpa menggunakan rumus TEXT maka hasilnya akan menjadi "Total Pembayaran 10000"
• B8: ="Total Penerimaan "&TEXT(A6,"Rp 0,000")
• Menggunakan format yang lain yaitu "Rp 0,000" (dengan pemisah ribuan dan tanpa dua digit desimal) sehingga menghasilkan kalimat "Total Penerimaan Rp 10,000"
• B10: ="Sebanyak "&TEXT(A6,"0,000")&" orang menghadiri acara tersebut."
• Menggunakan format yang lain yaitu "0,000" (tanpa tanda Rp dan dengan pemisah ribuan) sehingga menghasilkan kalimat "Sebanyak 10,000 orang menghadiri acara tersebut."
• A12: 85.25%
• B12: ="Penerimaan bulan ini hanya mencapai "&TEXT(A12,"0.00%")&" dibanding bulan lalu."
• Menggabungkan kalimat "Penerimaan bulan ini hanya mencapai " dengan persentase di A12 dan "dibanding bulan lalu." sehingga menghasilkan kalimat "Penerimaan bulan ini hanya mencapai 85.25% dibanding bulan lalu."
• Jika tanpa rumus TEXT maka hasilnya akan menjadi "Penerimaan bulan ini hanya mencapai 0.8525 dibanding bulan lalu."
• B14: ="Pada tanggal "&TEXT(A1,"d-mmm-yy")&" diterima pembayaran sebesar "&TEXT(A6,"Rp 0,000.00")&" atau sebesar "&TEXT(A12,"0,00%")&" dari total yang seharusnya dibayar."
• Menggabungkan antara tanggal, nilai, dan persentase menjadi suatu kalimat lengkap dengan format yang mudah dibaca "Pada tanggal 8-Mar-11 diterima pembayaran sebesar Rp 10,000.00 atau sebesar 085% dari total yang seharusnya dibayar."
• Jika tanpa rumus TEXT maka hasilnya akan menjadi "Pada tanggal 40610 diterima pembayaran sebesar 10000 atau sebesar 0.8525 dari total yang seharusnya dibayar."

Menjawab pertanyaan Saudara Huda melalui email tentang macro VBA yang otomatis dijalankan saat sebuah sel berubah nilainya. Berikut gambarannya:

1. Di Excel silahkan buka Visual Basic Editor pada menu Tools > Macro > Visual basic Editor (Alt + F11).
2. Klik ganda pada sheetnya lalu masukkan code VBAnya:
   
   Private Sub Worksheet_Change(ByVal Target As Range)
   
   If Target.Address = "$A$1" Then
        Worksheet_Calculate                    'macro yang akan dijalankan saat sel A1 berubah
   End If
   End Sub
   
   
   
3. Pilih Insert > Module dan tuliskan code VBA berikut:
   
   Sub Worksheet_Calculate()
       Dim Rng As Integer
       Range("BASTB!H24:H33").Select        'Menentukan baris mana saja yang akan di hide
       Selection.EntireRow.Hidden = False      'Baris akan di unhide
       Rng = Selection.Rows.Count
       ActiveCell.Offset(0, 0).Select
       Application.ScreenUpdating = True
       For i = 1 To Rng
       If ActiveCell.Value = "-" Then                'Tanda "-" bisa diganti dengan 0 atau "teks apa saja"
       Selection.EntireRow.Hidden = True        'Baris akan di hide
       ActiveCell.Offset(1, 0).Select
       Else
       ActiveCell.Offset(1, 0).Select
       End If
       Next i
   
   'Jika ada baris lain yang akan dihide, maka tambahkan lagi codingnya
   
       Range("BASTB!H41:H50").Select        'Menentukan baris mana saja yang akan di hide
       Selection.EntireRow.Hidden = False      'Baris akan di unhide
       Rng = Selection.Rows.Count
       ActiveCell.Offset(0, 0).Select
       Application.ScreenUpdating = True
       For i = 1 To Rng
       If ActiveCell.Value = "-" Then                 'Tanda "-" bisa diganti dengan 0 atau "teks apa saja"
       Selection.EntireRow.Hidden = True        'Baris akan di hide
       ActiveCell.Offset(1, 0).Select
       Else
       ActiveCell.Offset(1, 0).Select
       End If
       Next i
   
   'Akhiri dengan menambahkan coding di bawah ini
       Range("BASTB!A1").Select          'menempatkan sel kembali ke A1
   End Sub
   
   
4. Jika ada beberapa baris yang akan dihide, maka ulangi code Simpan file tersebut dan silahkan coba
   

Menjawab pertanyaan saudara Yoga pada postingan saya yang berjudul "Memisahkan Nama Depan dan Nama Belakang" dan meminta saya untuk dibuatkan rumusnya, maka berikut adalah rumusnya.

Rumus saya buat dalam dua versi:

1. Mencari karakter spasi (" ") sebagai pemisah di antara ketiga nama (kolom B & C) sedang nama yang terpisah terdapat di kolom D, E, dan F.
2. Menggabungkan rumus (tanpa kolom B & C)

Kepada para pengunjung blog Formula Excel, sebelumnya saya mohon maaf atas keterlambatan update Billing Formula v1.0 dan Tunas Gadget v1.0 yang telah expire ini. Tanggal expire sudah saya ganti menjadi tanggal 31 Desember 2011. File update bisa didownload di menu DOWNLOAD. Untuk Tunas Gadget Anda cukup mengganti (replace) file tersebut di folder yang sama.

Saya juga mohon maaf karena kesibukan saya, saya belum sempat lagi menambahkan artikel-artikel yang mungkin berguna bagi Anda semua. Saya berterima kasih kepada teman-teman sekalian yang telah merespon blog saya ini melalui email saya. Bagi yang telah saya bantu solusi atas pertanyaan-pertanyaan, saya harap semoga bisa banyak membantu dan mempermudah pekerjaan Anda. Namun bagi yang belum sempat atau tidak bisa saya jawab pertanyaannya (karena tidak berhubungan dengan Excel) saya mohon maaf karena tidak bisa banyak membantu.

Terima kasih telah mengunjungi blog saya ini.

Jika anda menemukan data dengan angka yang berformat teks, tentunya ada tidak dapat menjumlahkan atau mengolah angka-angka tersebut untuk dijumlahkan, di-sumif, ataupun dengan rumus lainnya karena formatnya yang masih berupa teks. Berikut gambaran perbedaan format number dan format teks:

• 1. Cek regional setting anda di control panel. Jika regional setting anda Indonesia, maka karakter pemisah ribuan adalah "." (titik) dan karakter pemisah desimal adalah "," (koma). Sedang jika regional setting anda adalah English (United States), maka karakter pemisah ribuan adalah "," (koma) dan karakter pemisah desimal adalah "." (titik).
• 2. Blok A1:A17 lalu pilih Edit > Replace (Ctrl + H). Karena komputer saya regional settingnya adalah Indonesia, maka ketik pada kolom Find what: dengan "," (koma) dan kosongkan pada kolom Replace with: lalu klik Replace All. Anda dapat melihat bahwa karakter "," (koma) sudah hilang. Jangan tekan tombol Close dulu.

• 3. Masih di kotak dialog Find and Replace, sekarang ganti pada kolom Find what: dengan "." (titik) dan pada kolom Replace with: dengan "," (koma), lalu tekan tombol Replace All.

Jika Anda bekerja dengan banyak data yang menggunakan nama orang, suatu saat Anda mungkin ingin memisahkan nama-nama tersebut menjadi dua bagian, yaitu nama dengan dan nama belakang. Dengan menggunkan rumus ini, pekerjaan tersebut sangat mudah dilakukan:

• LEFT(A1 artinya anda akan mengambil nama dari kolom A1 yang dimulai dari paling kiri.
• FIND(" ";$A$1:$A$16;1)-1 akan mencari karakter spasi " ". Misal pada nama "Aan Arifin" karakter spasi ditemukan pada karakter ke-4, maka nilai yang dihasilkan adalah 4. Rumus -1 artinya angka 4 tadi dikurangi dengan -1 agar menjadi 3, sesuai dengan nama "Aan" yang hanya ada 3 karakter.

• =MID(A1; artinya anda akan mengambil nama dari kolom A1 yang dimulai dari tengah.
• FIND(" ";$A$1:$A$16;1)+1, sama seperti rumus FIND di atas, untuk mencari karakter spasi " ", namun kali ini +1 untuk mencari nama belakang. Rumus FIND(" ";$A$1:$A$16;1)+1 akan menghasilkan angka 5, di mana nama "Arifin" dimulai dengan karakter ke-5. Rumus ;100) artinya karakter yang diambil untuk nama belakang diasumsikan 100 karakter.

Terkadang kita dihadapkan dengan banyak data yang bisa berjumlah ratusan atau bahkan ribuan. Dan masalahnya data tersebut mungkin saja bisa terjadi duplikasi data atau data lebih dari satu. Nah, kalau data yang terduplikasi tersebut menjadi reference atau sumber dari perhitungan tertentu, hal ini tentunya akan menjadi masalah bagi anda dalam mencari selisihnya.

Biar lebih mudah, berikut saya berikan gambarannya:

• Hasil dari rumus COUNTIF($C$19:$C$32;C19) adalah jumlah duplikasi data. Dalam hal ini type Avanza ada 2 duplikasi data, sedang yang lain hanya ada 1 data.
• Jika hasil dari perhitungan COUNTIF lebih dari 1, maka teks yang dimunculkan adalah "Duplikasi data", sedang jika tidak lebih dari 1, maka isi selnya kosong "".