coretan tentang autocad dan excel

February 2, 2011

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

Filed under: Excel — cadex @ 14:12
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…..

About these ads

6 Comments »

  1. [...] Spell Number into Bahasa Indonesia in Excel (No Macro) [...]

    Pingback by [XLS-PMG-04]: Spell Number in Excel Without Macro (No VBA) « coretan tentang autocad dan excel — October 16, 2010 @ 23:28

  2. 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?

    Comment by diffz — February 1, 2011 @ 20:19

  3. terimaksih sebelumnya

    tapi klo pake kata mata uangnya gimana semisa rupiah

    Comment by remy — June 24, 2011 @ 09:58

    • rumusnya tinggal dirubah menjadi = terbilang & ” rupiah”

      Comment by cadex — July 10, 2011 @ 22:08


RSS feed for comments on this post. TrackBack URI

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

The Shocking Blue Green Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 786 other followers

%d bloggers like this: