[XLS-PMG-02]: Rumus Terbilang di Excel (Tanpa Macro/VBA)

Referensi :
Platform : Excel 2003
Lokasi File : click di sini

Rumus terbilang ini dimaksudkan untuk mengubah angka ke huruf yang biasanya dituliskan di kwitansi ataupun di lembar invoice.

Fungsi sejenis sudah banyak yang membuatnya (hasil googling) tetapi biasanya menggunakan fasilitas pemrograman visual basic application for excel (vba) atau juga dikenal sebagai macro. Pada tulisan ini akan diuraikan tahapan untuk membuat fungsi terbilang menggunakan fasilitas ‘named range’ tanpa menggunakan macro.

Contoh penggunaan fungsi yang akan dibuat

image

Tahapan pembuatan fungsi ‘Terbilang’:

Misal angka yang akan dikonversi adalah 112148712.25:

Untuk membuat ‘Named Range’ di excel 2003, pada menu ‘Insert’, pilih ‘Names’ kemudian click ‘Define’.

Masukkan ‘Names in workbooks’ sesuai dengan isi tabel dr kolom ‘Named Range’  dan ‘Refers to’ sesuai dengan kolom Formula dari tabel yang sama.

No Tahapan Formula Hasil NamedRange (Variable)
1 Konversi angka ke text dengan format (“000000000.00”) maksimal angka yang diproses adalah sampai jutaan dengan dua desimal =TEXT(A1,REPT(“0”,9) & “.00”) 112148712.25 Angka
2 Mengambil angka bulat =LEFT(Angka,9) 112148712 Bulat
3 Mengambil angka desimal =RIGHT(Angka,2) 25
4 Membuat List Angka ={0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19} ListAngka
5 Membuat List Huruf ={“-“,”satu”,”dua”,”tiga”,”empat”,”lima”,”enam”,”tujuh”,”delapan”,”sembilan”,”sepuluh”,”sebelas”,”dua belas”,”tiga belas”,”empat belas”,”lima belas”,”enam belas”,”tujuh belas”,”delapan belas”,”sembilan belas”} ListHuruf
6 Angka Bulat (112148712) dibagi menjadi tiga bagian : yaitu 112, 148 dan 712. Masing-masing bagian diambil satu angka di awal dan dua angka sisanya. Misal untuk angka 712, akan dipecah menjadi 7 dan 12
7 Mengambil angka pertama dari 712 =LEFT(RIGHT(bulat,3)) 7 rat1
8 Mengambil dua angka sisanya dari 712 =MID(RIGHT(bulat,3),2,2) 12 sat1
9 Mengambil angka pertama dari 148 =LEFT(RIGHT(LEFT(bulat,LEN(bulat)-3),3)) 1 rat2
10 Mengambil dua angka sisanya dari 148 =MID(RIGHT(LEFT(bulat,LEN(bulat)-3),3),2,2) 48 sat2
11 Mengambil angka pertama dari 112 =LEFT(RIGHT(LEFT(bulat,LEN(bulat)-6),3)) 1 rat3
12 Mengambil dua angka sisanya dari 112 =MID(RIGHT(LEFT(bulat,LEN(bulat)-6),3),2,2) 12 sat3
13 Konversi rat1 ke angka =SUBSTITUTE(IF(–rat1>0,LOOKUP(–rat1,ListAngka,ListHuruf) & ” ratus “,”-“),”satu ratus”,”seratus”) tujuh ratus Ratusan1
14 Konversi sat1 ke angka =IF(–sat1>19,LOOKUP(–LEFT(sat1,1),ListAngka,ListHuruf) & ” puluh ” & LOOKUP(–RIGHT(sat1,1),ListAngka,ListHuruf),LOOKUP(–sat1,ListAngka,ListHuruf)) dua belas Satuan1
15 Konversi rat2 ke angka =SUBSTITUTE(IF(–rat2>0,LOOKUP(–rat2,ListAngka,ListHuruf) & ” ratus “,”-“),”satu ratus”,”seratus”) seratus Ratusan2
16 Konversi sat2 ke angka =IF(–sat2>19,LOOKUP(–LEFT(sat2,1),ListAngka,ListHuruf) & ” puluh ” & LOOKUP(–RIGHT(sat2,1),ListAngka,ListHuruf),LOOKUP(–sat2,ListAngka,ListHuruf)) empat puluh delapan Satuan2
17 Konversi rat3 ke angka =SUBSTITUTE(IF(–rat3>0,LOOKUP(–rat3,ListAngka,ListHuruf) & ” ratus “,”-“),”satu ratus”,”seratus”) seratus Ratusan3
18 Konversi sat3 ke angka =IF(–sat3>19,LOOKUP(–LEFT(sat3,1),ListAngka,ListHuruf) & ” puluh ” & LOOKUP(–RIGHT(sat3,1),ListAngka,ListHuruf),LOOKUP(–sat3,ListAngka,ListHuruf)) dua belas Satuan3
19 Konversi sen =IF(–RIGHT(Angka,2)>0,RIGHT(Angka,2) &”/100″,””) 25/100 sen
20 Menulis bagian jutaan =IF((VALUE(rat3)+VALUE(sat3))>0,Ratusan3 & Satuan3 & ” juta “,”-“) seratus dua belas juta strJutaan
21 Menulis bagian ribuan =IF((VALUE(rat2)+VALUE(sat2))=0,”-“,IF(VALUE(sat2)=1,”seribu “,Ratusan2 & Satuan2 & ” ribu “)) seratus empat puluh delapan ribu strRibuan
22 Menulis bagian ratusan =IF((VALUE(rat1)+VALUE(sat1))>0,Ratusan1 & Satuan1,”-“) tujuh ratus dua belas strRatusan
23 Menggabung Jutaan+Ribuan+Ratusan =IF(LEN(Angka)>12,”#BELUM SAMPAI MILYARAN!!#”,TRIM(SUBSTITUTE(SUBSTITUTE(strJutaan & ” ” & strRibuan & ” ” & strRatusan & ” ” & sen, “-“,””),” “,” “))) seratus dua belas juta seratus empat puluh delapan ribu tujuh ratus dua belas 25/100

Jika diinginkan penyebutan angka dibelakang koma, pada formula di step ke 19 (konversi sen) dirubah menjadi:

=IF(–RIGHT(Angka,2)>0,”koma ” & IF(–RIGHT(Angka,2)>19, LOOKUP(–LEFT(RIGHT(Angka,2),1),ListAngka,ListHuruf) & ” puluh ” & LOOKUP(–RIGHT(RIGHT(Angka,2),1),ListAngka,ListHuruf),LOOKUP(–RIGHT(Angka,2),ListAngka,ListHuruf)),””)

sehingga hasil dari formula terbilang menjadi:

image

download file untuk versi terbilang dengan menyebutkan angka desimal ada di sini

update 26 Mei 2011:

jika diinginkan penyebutan misalnya

112148712.25 = “seratus dua belas juta seratus empat puluh delapan ribu tujuh ratus dua belas rupiah dua puluh lima sen”

silahkan dunlut filenya

Silahkan mencoba…..

8 thoughts on “[XLS-PMG-02]: Rumus Terbilang di Excel (Tanpa Macro/VBA)”

  1. Pingback: [XLS-PMG-03]: Pemakaian Fungsi Terbilang di Excel Tanpa Macro « coretan tentang autocad dan excel
  2. Pingback: [XLS-PMG-04]: Spell Number in Excel Without Macro (No VBA) « coretan tentang autocad dan excel

  3. mas aku gak paham cara buatnya. tahapan itu diketik dimana dan disimpan dimana? apkah patokan yang dibuat adalah harus angka ini 112148712.25. tolong tahap dari yang sederhana di jelaskan mohon bantuannya? terus gimana angka yang ada tanda komanya?

    Reply

      1. kalau menghilangkan koma contoh “dua puluh lima juta rupiah koma dua puluh satu” rumusnya gimana?

      2. “dua puluh lima juta rupiah koma dua puluh satu” penulisan dalam bahasa indonesia yang benar adalah “dua puluh lima juta rupiah koma dua satu”

Leave a comment