[XLS-PMG-02]: Rumus Terbilang Di Ms. Office -Excel (Tanpa Macro/VBA)
| Tema | : | Otomatisaasi Perkantoran |
| Sub Tema | : | 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
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:
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…..
