[XLS-PMG-08]: Fungsi Terbilang di Excel Tanpa Macro Versi Mega Formula Rev.03

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) & " "
&INDEX(_huruf,1+MID(TEXT(A1,_frm),3,1)))

 

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)&" "
&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) & " "
&INDEX(_huruf,1+MID(TEXT(A1,_frm),3,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)&" "
&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) & " "
&INDEX(_huruf,1+MID(TEXT(A1,_frm),3,1)))&REPT(" milyar",(TEXT(A1,_frm)/10^9)>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)&" "
&IF(–MID(TEXT(A1,_frm),5,2)<=19,INDEX(_huruf,1+MID(TEXT(A1,_frm),5,2)),LOOKUP(–MID(TEXT(A1,_frm),5,2),_angka,_huruf) & " "
&INDEX(_huruf,1+MID(TEXT(A1,_frm),6,1)))&REPT(" juta",(TEXT(A1,_frm)/10^6)>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)&" "
&IF(–MID(TEXT(A1,_frm),8,2)<=19,INDEX(_huruf,1+MID(TEXT(A1,_frm),8,2)),LOOKUP(–MID(TEXT(A1,_frm),8,2),_angka,_huruf) & " "
&INDEX(_huruf,1+MID(TEXT(A1,_frm),9,1)))&REPT(" ribu",(TEXT(A1,_frm)/10^3)>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)&" "
&IF(–MID(TEXT(A1,_frm),8,2)<=19,INDEX(_huruf,1+MID(TEXT(A1,_frm),8,2)),LOOKUP(–MID(TEXT(A1,_frm),8,2),_angka,_huruf) & " "
&INDEX(_huruf,1+MID(TEXT(A1,_frm),9,1)))&REPT(" ribu",(TEXT(A1,_frm)/10^3)>1),"satu ribu","seribu")

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)&" "
&IF(–MID(TEXT(A1,_frm),11,2)<=19,INDEX(_huruf,1+MID(TEXT(A1,_frm),11,2)),LOOKUP(–MID(TEXT(A1,_frm),11,2),_angka,_huruf) & " "
&INDEX(_huruf,1+MID(TEXT(A1,_frm),12,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)&" "
&IF(–MID(TEXT(A7,_frm),2,2)<=19,INDEX(_huruf,1+MID(TEXT(A7,_frm),2,2)),LOOKUP(–MID(TEXT(A7,_frm),2,2),_angka,_huruf) & " "
&INDEX(_huruf,1+MID(TEXT(A7,_frm),3,1)))&REPT(" milyar",(TEXT(A7,_frm)/10^9)>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)&" "
&IF(–MID(TEXT(A7,_frm),5,2)<=19,INDEX(_huruf,1+MID(TEXT(A7,_frm),5,2)),LOOKUP(–MID(TEXT(A7,_frm),5,2),_angka,_huruf) & " "
&INDEX(_huruf,1+MID(TEXT(A7,_frm),6,1)))&REPT(" juta",(TEXT(A7,_frm)/10^6)>1)&" "

&SUBSTITUTE(
REPT("seratus ",–MID(TEXT(A7,_frm),7,1)=1)&REPT(INDEX(_huruf,1+MID(TEXT(A7,_frm),7,1)) &" ratus",–MID(TEXT(A7,_frm),7,1)>1)&" "
&IF(–MID(TEXT(A7,_frm),8,2)<=19,INDEX(_huruf,1+MID(TEXT(A7,_frm),8,2)),LOOKUP(–MID(TEXT(A7,_frm),8,2),_angka,_huruf) & " "
&INDEX(_huruf,1+MID(TEXT(A7,_frm),9,1)))&REPT(" ribu",(TEXT(A7,_frm)/10^3)>1),"satu ribu","seribu")&" "

&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)&" "
&IF(–MID(TEXT(A7,_frm),11,2)<=19,INDEX(_huruf,1+MID(TEXT(A7,_frm),11,2)),LOOKUP(–MID(TEXT(A7,_frm),11,2),_angka,_huruf) & " "
&INDEX(_huruf,1+MID(TEXT(A7,_frm),12,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)&" "
&INDEX(_huruf,1+MID(TEXT(A1,_frm),15,1))) &" sen",–MID(TEXT(A1,_frm),14,2)>0)

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)&" "
&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) & " "
&INDEX(_huruf,1+MID(TEXT(A1,_frm),3,1)))&REPT(" milyar",(TEXT(A1,_frm)/10^9)>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)&" "
&IF(–MID(TEXT(A1,_frm),5,2)<=19,INDEX(_huruf,1+MID(TEXT(A1,_frm),5,2)),LOOKUP(–MID(TEXT(A1,_frm),5,2),_angka,_huruf) & " "
&INDEX(_huruf,1+MID(TEXT(A1,_frm),6,1)))&REPT(" juta",(TEXT(A1,_frm)/10^6)>1)&" "

&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)&" "
&IF(–MID(TEXT(A1,_frm),8,2)<=19,INDEX(_huruf,1+MID(TEXT(A1,_frm),8,2)),LOOKUP(–MID(TEXT(A1,_frm),8,2),_angka,_huruf) & " "
&INDEX(_huruf,1+MID(TEXT(A1,_frm),9,1)))&REPT(" ribu",(TEXT(A1,_frm)/10^3)>1),"satu ribu","seribu")&" "

&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)&" "
&IF(–MID(TEXT(A1,_frm),11,2)<=19,INDEX(_huruf,1+MID(TEXT(A1,_frm),11,2)),LOOKUP(–MID(TEXT(A1,_frm),11,2),_angka,_huruf) & " "
&INDEX(_huruf,1+MID(TEXT(A1,_frm),12,1))))&" rupiah "

&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)&" "
&INDEX(_huruf,1+MID(TEXT(A1,_frm),15,1))) &" sen",–MID(TEXT(A1,_frm),14,2)>0)

Dengan metode atau cara yang sama bisa dikembangkan untuk konversi ke bahasa inggris atau bahasa lainnya.

==semoga bermanfaat==

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s