| Referensi | : |
[XLS-PMG-06]:Fungsi Terbilang Di Excel Tanpa Macro (Versi Mega Formula) Rev.02 |
| Platform | : | Excel 2007 |
| Lokasi File | : | Download |
Tulisan ini adalah revisi ketiga dari posting sebelumnya dengan melakukan beberapa perubahan antara lain:
|
Rev.02 |
Rev.03 |
|
| Panjang rumus konversi angka ke huruf: | ||
| — Versi bahasa indonesia |
1741 |
1362 |
| — Versi bahasa inggris tanpa cents |
2187 |
|
| — Versi bahasa indonesia dengan sen |
1620 |
|
| — Versi bahsa inggris dengan cents |
1376 |
Secara garis besar urutan untuk merubah angka menjadi huruf adalah:
A. VERSI KONVERSI TANPA ANGKA DESIMAL
Secara umum urutan yang saya pakai untuk merubah angka menjadi huruf adalah:
1. Proses pembacaan angka ke huruf dengan menggunakan bantuan 3 (tiga) “Named Range” yaitu:
— Named Range untuk memformat angka: _frm=REPT("0",12)
— Named Range larik/array angka: _angka={0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,30,40,50,60,70,80,90}
–Named Range larik/array huruf: _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","dua puluh","tiga puluh","empat puluh","lima puluh","enam puluh","tujuh puluh","delapan puluh","sembilan puluh"}
2. Format angka menjadi format text dengan panjang 12 karakter.
Misal angka di cell [A1] berisi angka 111456789876 (12 karakter), maka dengan menggunakan fungsi =TEXT(A1,_frm) menjadi 111456789876 (12 karakter)
3. Angka digrupkan atau dibagi menjadi 4 (empat) yaitu : [Grup Milyar]= 111, [Grup Juta]= 457, [Grup Ribu]= 898 dan [Grup Satuan]= 876
4. Proses konversi dimulai berurutan dari [Grup Milyar], [Grup Juta],[Grup Ribu] dan [Grup Satuan]. Masing-masing grup terdiri dari [Ratusan] dan [Puluhan].
4.1 Konver Angka Milyar-an atau [Grup Milyar]
[Grup Milyar]=111 terdiri dari [Ratusan]= 1 dan [Puluhan]= 11
A. Konversi Angka Ratusan ke Huruf
Angka [Ratusan] diambil dengan rumus =MID(TEXT(A2,_frm),1,1)
Dengan menggunakan fungsi =INDEX(_huruf,[Ratusan]+1) atau =INDEX(_huruf,1+1) akan mengambil isi array _huruf yang ke 2 yaitu kata “satu” kemudian ditambahkan kata “ratus” sehingga rumusnya menjadi =INDEX(_huruf,1+1) & “ ratus”
Karena bahasa indonesia tidak mengenal kalimat “satu ratus”, maka rumus =INDEX(_huruf,1+1) & “ ratus” hanya berlaku jika [Ratusan]>1 sehinga jika [Ratusan]= 1 tidak dilakukan pengambilan isi array _huruf tetapi langsung dirubah menjadi kata “seratus”.
Fungsi –MID(TEXT(A1,_frm),1,1)=1 untuk mengecek [Ratusan]=1, sedangkan –MID(TEXT(A1,_frm),1,1)>1 untuk mengecek [Ratusan]>1. Tanda double minus “–“ di depan MID berfungsi untuk merubah text “1” dari fungsi MID(TEXT(A1,_frm),1,1) menjadi angka 1.
Sedangkan untuk menampilkan menampilkan angka [Ratusan]=1 menggunakan fungsi
=REPT("seratus ",–MID(TEXT(A1,_frm),1,1)=1)
dan fungsi
=REPT(INDEX(_huruf,1+MID(TEXT(A1,_frm),1,1)) &" ratus",–MID(TEXT(A1,_frm),1,1)>1)
untuk menampilkan [Ratusan]>1
Apabila digabungkan Fungsi [Ratusan] menjadi:
| =REPT("seratus ",–MID(TEXT(A1,_frm),1,1)=1)&REPT(INDEX(_huruf,1+MID(TEXT(A1,_frm),1,1)) &" ratus",–MID(TEXT(A1,_frm),1,1)>1) |
B. Konversi Angka Puluhan ke Huruf
Angka [Puluhan] diambil dengan fungsi =MID(TEXT(A1,_frm),2,2).
Hasil rumus ini kemudian dicek lagi jika [Puluhan]<=19, maka proses konversi langsung mengambil index dari array/larik _huruf dengan fungsi:
=INDEX(_huruf,1+MID(TEXT(A1,_frm),2,2)
sedangkan jika [Puluhan]>19, maka proses konversi dua tahap yaitu mengambil angka bulan puluhan di tambah dengan angka satuannya.
Fungsi untuk mengambil angka bulat untuk [Puluhan]>19
=LOOKUP(–MID(TEXT(A1,_frm),2,2),_angka,_huruf)
dan untuk mengambil angka satuannya:
=INDEX(_huruf,1+MID(TEXT(A1,_frm),3,1))
Gabungan fungsi [Puluhan] menjadi:
|
=IF(–MID(TEXT(A1,_frm),2,2)<=19,INDEX(_huruf,1+MID(TEXT(A1,_frm),2,2)),LOOKUP(–MID(TEXT(A1,_frm),2,2),_angka,_huruf) & " " |
Sedangkan fungsi gabungan [Ratusan] dan [Puluhan] menjadi:
|
=REPT("seratus ",–MID(TEXT(A1,_frm),1,1)=1)&REPT(INDEX(_huruf,1+MID(TEXT(A1,_frm),1,1)) &" ratus",–MID(TEXT(A1,_frm),1,1)>1)&" " |
Fungsi di atas akan ditambahkan kata “milyar” jika nilai angka yang akan dikonversi lebih lebih dari satu milyar. Rumus untuk mengecek angka mencapai milyaran adalah:
=REPT(" milyar",(TEXT(A1,_frm)/10^9)>1)
Sehingga untuk grup [Milyar] dengan akhiran “ milyar” fungsinya menjadi:
|
=REPT("seratus ",–MID(TEXT(A1,_frm),1,1)=1)&REPT(INDEX(_huruf,1+MID(TEXT(A1,_frm),1,1)) &" ratus",–MID(TEXT(A1,_frm),1,1)>1)&" " |
4.2 Konversi Angka Juta-an atau [Grup Juta]
Angka juta-an atau [Grup Juta] didapat dengan memodifikasi fungsi / rumus MID() dari fungsi di atas.
=MID(TEXT(A1,_frm),1,1), artinya mengambil huruf dari text A1 yang telah diformat mulai dari huruf ke-1 sebanyak 1 huruf. Jika pengambilan karakter dimulai dari huruf ke 4 atau huruf ke-(1+3) akan didapat huruf ratusan dari [Grup Jutaan].
kemudian dengan memodifikasi REPT(" milyar",(TEXT(A1,_frm)/10^9)>1) menjadi REPT(" juta",(TEXT(A1,_frm)/10^3)>1) maka akan merubah akhiran “ milyar” menjadi “ juta”
Fungsi [Grup Juta] menjadi:
|
=REPT("seratus ",–MID(TEXT(A1,_frm),4,1)=1)&REPT(INDEX(_huruf,1+MID(TEXT(A1,_frm),4,1)) &" ratus",–MID(TEXT(A1,_frm),4,1)>1)&" " |
4.3 Konversi Angka Ribua-an atau [Grup Ribu]
Dengan memodifikasi fungsi [Grup Juta], fungsi [Grup Ribu] menjadi:
|
=REPT("seratus ",–MID(TEXT(A1,_frm),7,1)=1)&REPT(INDEX(_huruf,1+MID(TEXT(A1,_frm),7,1)) &" ratus",–MID(TEXT(A1,_frm),7,1)>1)&" " |
Karena dalam bahasa indonesia tidak ada kalimat “satu ribu” maka diperlukan ditambahkan fungsi SUBSTITUTE () untuk merubah “satu ribu” menjadi “seribu”
|
=SUBSTITUTE(REPT("seratus ",–MID(TEXT(A1,_frm),7,1)=1)&REPT(INDEX(_huruf,1+MID(TEXT(A1,_frm),7,1)) &" ratus",–MID(TEXT(A1,_frm),7,1)>1)&" " |
4.4. Konversi Angka Satuan [Grup Satuan]
Hasil modifikasi [Grup Ribu], funsi [Grup Satuan] menjadi:
|
=REPT("seratus ",–MID(TEXT(A1,_frm),10,1)=1)&REPT(INDEX(_huruf,1+MID(TEXT(A1,_frm),10,1)) &" ratus",–MID(TEXT(A1,_frm),10,1)>1)&" " |
Rumus lengkap konversi angka ke huruf tanpa angka desimal:
|
=TRIM(REPT("seratus ",–MID(TEXT(A7,_frm),1,1)=1)&REPT(INDEX(_huruf,1+MID(TEXT(A7,_frm),1,1)) &" ratus",–MID(TEXT(A7,_frm),1,1)>1)&" " &REPT("seratus ",–MID(TEXT(A7,_frm),4,1)=1)&REPT(INDEX(_huruf,1+MID(TEXT(A7,_frm),4,1)) &" ratus",–MID(TEXT(A7,_frm),4,1)>1)&" " &SUBSTITUTE( &REPT("seratus ",–MID(TEXT(A7,_frm),10,1)=1)&REPT(INDEX(_huruf,1+MID(TEXT(A7,_frm),10,1)) &" ratus",–MID(TEXT(A7,_frm),10,1)>1)&" " |
B. VERSI KONVERSI DENGAN ANGKA DUA ANGKA DESIMAL (ANGKA DENGAN SEN)
Dengan merubah named range semula _frm=REPT("0",12) menjadi _frm=REPT("0",12)&MID(1/10,2,1)&"00" dan menambahkan fungsi di akhir rumus VERSI KONVERSI TANPA ANGKA DESIMAL:
|
&REPT(IF(–MID(TEXT(A1,_frm),14,2)<=19,INDEX(_huruf,1+MID(TEXT(A1,_frm),14,2)),LOOKUP(–MID(TEXT(A1,_frm),14,2),_angka,_huruf)&" " |
Rumus lengkap konversi angka ke huruf dengan dua angka desimal (sen):
|
=TRIM(REPT("seratus ",–MID(TEXT(A1,_frm),1,1)=1)&REPT(INDEX(_huruf,1+MID(TEXT(A1,_frm),1,1)) &" ratus",–MID(TEXT(A1,_frm),1,1)>1)&" " &REPT("seratus ",–MID(TEXT(A1,_frm),4,1)=1)&REPT(INDEX(_huruf,1+MID(TEXT(A1,_frm),4,1)) &" ratus",–MID(TEXT(A1,_frm),4,1)>1)&" " &SUBSTITUTE( &REPT("seratus ",–MID(TEXT(A1,_frm),10,1)=1)&REPT(INDEX(_huruf,1+MID(TEXT(A1,_frm),10,1)) &" ratus",–MID(TEXT(A1,_frm),10,1)>1)&" " &REPT(IF(–MID(TEXT(A1,_frm),14,2)<=19,INDEX(_huruf,1+MID(TEXT(A1,_frm),14,2)),LOOKUP(–MID(TEXT(A1,_frm),14,2),_angka,_huruf)&" " |
Dengan metode atau cara yang sama bisa dikembangkan untuk konversi ke bahasa inggris atau bahasa lainnya.
==semoga bermanfaat==
coretan tentang autocad dan excel 