[XLS-PMG-02]: Rumus Terbilang Di Excel (Tanpa Macro/VBA)|Adm. Perkantoran

[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.
NoTahapanFormulaHasilNamedRange (Variable)
1Konversi angka ke text dengan format (“000000000.00″) maksimal angka yang diproses adalah sampai jutaan dengan dua desimal=TEXT(A1,REPT(“0″,9) & “.00″)112148712.25Angka
2Mengambil angka bulat=LEFT(Angka,9)112148712Bulat
3Mengambil angka desimal=RIGHT(Angka,2)25
4Membuat List Angka={0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19}ListAngka
5Membuat 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
6Angka 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
7Mengambil angka pertama dari 712=LEFT(RIGHT(bulat,3))7rat1
8Mengambil dua angka sisanya dari 712=MID(RIGHT(bulat,3),2,2)12sat1
9Mengambil angka pertama dari 148=LEFT(RIGHT(LEFT(bulat,LEN(bulat)-3),3))1rat2
10Mengambil dua angka sisanya dari 148=MID(RIGHT(LEFT(bulat,LEN(bulat)-3),3),2,2)48sat2
11Mengambil angka pertama dari 112=LEFT(RIGHT(LEFT(bulat,LEN(bulat)-6),3))1rat3
12Mengambil dua angka sisanya dari 112=MID(RIGHT(LEFT(bulat,LEN(bulat)-6),3),2,2)12sat3
13Konversi rat1 ke angka=SUBSTITUTE(IF(–rat1>0,LOOKUP(–rat1,ListAngka,ListHuruf) & ” ratus “,”-”),”satu ratus”,”seratus”)tujuh ratusRatusan1
14Konversi sat1 ke angka=IF(–sat1>19,LOOKUP(–LEFT(sat1,1),ListAngka,ListHuruf) & ” puluh ” & LOOKUP(–RIGHT(sat1,1),ListAngka,ListHuruf),LOOKUP(–sat1,ListAngka,ListHuruf))dua belasSatuan1
15Konversi rat2 ke angka=SUBSTITUTE(IF(–rat2>0,LOOKUP(–rat2,ListAngka,ListHuruf) & ” ratus “,”-”),”satu ratus”,”seratus”)seratusRatusan2
16Konversi 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 delapanSatuan2
17Konversi rat3 ke angka=SUBSTITUTE(IF(–rat3>0,LOOKUP(–rat3,ListAngka,ListHuruf) & ” ratus “,”-”),”satu ratus”,”seratus”)seratusRatusan3
18Konversi sat3 ke angka=IF(–sat3>19,LOOKUP(–LEFT(sat3,1),ListAngka,ListHuruf) & ” puluh ” & LOOKUP(–RIGHT(sat3,1),ListAngka,ListHuruf),LOOKUP(–sat3,ListAngka,ListHuruf))dua belasSatuan3
19Konversi sen=IF(–RIGHT(Angka,2)>0,RIGHT(Angka,2) &”/100″,””)25/100sen
20Menulis bagian jutaan=IF((VALUE(rat3)+VALUE(sat3))>0,Ratusan3 & Satuan3 & ” juta “,”-”)seratus dua belas jutastrJutaan
21Menulis bagian ribuan=IF((VALUE(rat2)+VALUE(sat2))=0,”-”,IF(VALUE(sat2)=1,”seribu “,Ratusan2 & Satuan2 & ” ribu “))seratus empat puluh delapan ribustrRibuan
22Menulis bagian ratusan=IF((VALUE(rat1)+VALUE(sat1))>0,Ratusan1 & Satuan1,”-”)tujuh ratus dua belasstrRatusan
23Menggabung 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…..

Post a Comment

0 Comments