coretan tentang autocad dan excel

September 9, 2013

[XLS-PMG-06]:Fungsi Terbilang di Excel Tanpa Macro (Versi Mega Formula) Rev.02

Filed under: Excel — Tags: — cadex @ 08:57

Referensi

:

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

Platform

:

Excel 2007

Lokasi File

:

download

Pada posting [XLS-PMG-02]: Rumus Terbilang di Excel (Tanpa Macro/VBA), diuraikan langkah membuat fungsi terbilang dengan menggunakan fasilitas ‘named range’. Pada posting tersebut menggunakan 21 (dua puluh satu) named ranges.

Kelemahan dari fungsi terbilang di posting sebelumnya adalah mengharuskan user untuk selalu mengubah “named range” [Angka], jika angka yang akan disebutkan terbilangnya ada di sheet lain.

Misalkan angka yang akan disebut terbilangnya ada di sheet [SheetBaru] Range [B1], maka range [Angka] yang sebelumnya refer ke

[Angka]=TEXT(terbilang!A18,REPT(“0″,9)&”.00″) dirubah menjadi

[Angka]=TEXT(SheetBaru!B1,REPT(“0″,9)&”.00″)

Posting kali ini akan digunakan cara baru yaitu menggunakan satu formula yang panjang (Mega Formula) dan dua “named range” yang bersifat tetap atau konstan yang tidak perlu dirubah-rubah lagi jika akan meng-copy formula di sheet lain dalam satu workbook.

Mega Formula menggunakan kombinasi fungsi excel yaitu empat fungsi manipulati text : TEXT(), REPT(), MID(), SUBSTITUTE() dan satu fungsi array : INDEX()

Berikut adalah langkah-langkahnya:

1. Buat 2 (dua) “named range”

Named Range

Refer To

Note

frm

=”000000000″

maksimal angka 999999999 interger (tanpa desimal)

ListHuruf

={“”,”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”}

  

2. Misal angka yang akan disebut terbilangnya ada di [A2], maka rumus atau formula terbilangnya adalah

Rev.01 update menjadi maximal 12 digit angka bulat

=IF(LEN(INT(A2))>LEN(_frm),”#Maximal 12 digit angka bulat#”,
SUBSTITUTE(
INDEX(ListHuruf,MID(TEXT(A2,_frm),1,1)+1)
&REPT(” ratus “,0+MID(TEXT(A2,_frm),1,3)>99)
&INDEX(ListHuruf,(0+MID(TEXT(A2,_frm),2,2)<20)*MID(TEXT(A2,_frm),2,2)+1)
&INDEX(ListHuruf,(0+MID(TEXT(A2,_frm),2,2)>19)*MID(TEXT(A2,_frm),2,1)+1)
&REPT(” puluh “,0+MID(TEXT(A2,_frm),2,2)>19)
&INDEX(ListHuruf,(0+MID(TEXT(A2,_frm),2,2)>19)*MID(TEXT(A2,_frm),3,1)+1)
&REPT(” milyar “,LEN(A2)>9)

&INDEX(ListHuruf,MID(TEXT(A2,_frm),4,1)+1)
&REPT(” ratus “,0+MID(TEXT(A2,_frm),4,3)>99)
&INDEX(ListHuruf,(0+MID(TEXT(A2,_frm),5,2)<20)*MID(TEXT(A2,_frm),5,2)+1)
&INDEX(ListHuruf,(0+MID(TEXT(A2,_frm),5,2)>19)*MID(TEXT(A2,_frm),5,1)+1)
&REPT(” puluh “,0+MID(TEXT(A2,_frm),5,2)>19)
&INDEX(ListHuruf,(0+MID(TEXT(A2,_frm),5,2)>19)*MID(TEXT(A2,_frm),6,1)+1)
&REPT(” juta “,(0+MID(TEXT(A2,_frm),4,3)>0))

&SUBSTITUTE(INDEX(ListHuruf,MID(TEXT(A2,_frm),7,1)+1)
&REPT(” ratus “,0+MID(TEXT(A2,_frm),7,3)>99)
&INDEX(ListHuruf,(0+MID(TEXT(A2,_frm),8,2)<20)*MID(TEXT(A2,_frm),8,2)*(A2>=2000)+1)
&INDEX(ListHuruf,(0+MID(TEXT(A2,_frm),8,2)>19)*MID(TEXT(A2,_frm),8,1)*(A2>=2000)+1)
&REPT(” puluh “,0+MID(TEXT(A2,_frm),8,2)>19)
&INDEX(ListHuruf,(0+MID(TEXT(A2,_frm),8,2)>19)*MID(TEXT(A2,_frm),9,1)+1)
&REPT(“seribu “,(A2<2000)*(LEN(A2)=4))
&REPT(” ribu “,(A2>=2000)*(0+MID(TEXT(A2,_frm),7,3)>0)), “satu ribu”,”seribu”)

&INDEX(ListHuruf,MID(TEXT(A2,_frm),10,1)+1)
&REPT(” ratus “,0+MID(TEXT(A2,_frm),10,3)>99)
&INDEX(ListHuruf,(0+MID(TEXT(A2,_frm),11,2)<20)*MID(TEXT(A2,_frm),11,2)+1)
&INDEX(ListHuruf,(0+MID(TEXT(A2,_frm),11,2)>19)*MID(TEXT(A2,_frm),11,1)+1)
&REPT(” puluh “,0+MID(TEXT(A2,_frm),11,2)>19)
&INDEX(ListHuruf,(0+MID(TEXT(A2,_frm),11,2)>19)*MID(TEXT(A2,_frm),12,1)+1),
“satu ratus”,”seratus”
))

Code for spelling number to English :

=IF(LEN(INT(A3))>LEN(_frm),”#Maximal 12 digit#”,
INDEX(ListNumber,MID(TEXT(A3,_frm),1,1)+1)
&REPT(” hundred “,0+MID(TEXT(A3,_frm),1,3)>99)
&INDEX(ListNumber,(0+MID(TEXT(A3,_frm),2,2)<20)*MID(TEXT(A3,_frm),2,2)+1)
&INDEX(ListNumber_,(0+MID(TEXT(A3,_frm),2,2)>=20)*(0+MID(TEXT(A3,_frm),2,2)<=59)*(MID(TEXT(A3,_frm),2,1)))
&INDEX(ListNumber,(0+MID(TEXT(A3,_frm),2,2)>59)*MID(TEXT(A3,_frm),2,1)+1)
&REPT(“ty”,0+MID(TEXT(A3,_frm),2,2)>59)
&REPT(“-“,0+MID(TEXT(A3,_frm),3,1)>0)
&INDEX(ListNumber,(0+MID(TEXT(A3,_frm),2,2)>=20)*MID(TEXT(A3,_frm),3,1)+1)
&REPT(” billion “,LEN(A3)>9)

&INDEX(ListNumber,MID(TEXT(A3,_frm),4,1)+1)
&REPT(” hundred “,0+MID(TEXT(A3,_frm),4,3)>99)
&INDEX(ListNumber,(0+MID(TEXT(A3,_frm),5,2)<20)*MID(TEXT(A3,_frm),5,2)+1)
&INDEX(ListNumber_,(0+MID(TEXT(A3,_frm),5,2)>=20)*(0+MID(TEXT(A3,_frm),5,2)<=59)*(MID(TEXT(A3,_frm),5,1)))
&INDEX(ListNumber,(0+MID(TEXT(A3,_frm),5,2)>59)*MID(TEXT(A3,_frm),5,1)+1)
&REPT(“ty”,0+MID(TEXT(A3,_frm),5,2)>59)
&REPT(“-“,0+MID(TEXT(A3,_frm),6,1)>0)
&INDEX(ListNumber,(0+MID(TEXT(A3,_frm),5,2)>=20)*MID(TEXT(A3,_frm),6,1)+1)
&REPT(” million “,(0+MID(TEXT(A3,_frm),4,3)>0))

&INDEX(ListNumber,MID(TEXT(A3,_frm),7,1)+1)
&REPT(” hundred “,0+MID(TEXT(A3,_frm),7,3)>99)
&INDEX(ListNumber,(0+MID(TEXT(A3,_frm),8,2)<20)*MID(TEXT(A3,_frm),8,2)+1)
&INDEX(ListNumber_,(0+MID(TEXT(A3,_frm),8,2)>=20)*(0+MID(TEXT(A3,_frm),8,2)<=59)*(MID(TEXT(A3,_frm),8,1)))
&INDEX(ListNumber,(0+MID(TEXT(A3,_frm),8,2)>59)*MID(TEXT(A3,_frm),8,1)+1)
&REPT(“ty”,0+MID(TEXT(A3,_frm),8,2)>59)
&REPT(“-“,0+MID(TEXT(A3,_frm),9,1)>0)
&INDEX(ListNumber,(0+MID(TEXT(A3,_frm),8,2)>=20)*MID(TEXT(A3,_frm),9,1)+1)
&REPT(” thousand “,0+MID(TEXT(A3,_frm),7,3)>0)

&INDEX(ListNumber,MID(TEXT(A3,_frm),10,1)+1)
&REPT(” hundred “,(0+MID(TEXT(A3,_frm),10,3))>99)
&INDEX(ListNumber,(0+MID(TEXT(A3,_frm),11,2)<20)*MID(TEXT(A3,_frm),11,2)+1)
&INDEX(ListNumber_,(0+MID(TEXT(A3,_frm),11,2)>=20)*(0+MID(TEXT(A3,_frm),11,2)<=59)*(MID(TEXT(A3,_frm),11,1)))
&INDEX(ListNumber,(0+MID(TEXT(A3,_frm),11,2)>59)*MID(TEXT(A3,_frm),11,1)+1)
&REPT(“ty”,0+MID(TEXT(A3,_frm),11,2)>59)
&REPT(“-“,0+MID(TEXT(A3,_frm),12,1)>0)
&INDEX(ListNumber,(0+MID(TEXT(A3,_frm),11,2)>=20)*MID(TEXT(A3,_frm),12,1)+1)
)

Silahkan dicoba, jika ada yang salah penyebutan angkanya tolong aku diberitahu ya…

-zainul-

6 Comments »

  1. met malem mas…. tengkiu dah mau share rumus terbilang tanpa menggunakan makro. Namun setelah saya coba masih ada kesalahan penyebutan mas… untuk bilangan bulat 300.000 (tiga ratus ribu) misalnya, disana masih menyebutkan “tiga ratus”, tanpa menyebutkan ribu… tengkiu

    Comment by nuryoko, aris — May 20, 2012 @ 20:19

    • kalo diingikan menulis angka tiga ratus ribu, cara memasukan angkanya adalah 300000
      kalo diinginkan menulis angka tiga juta tiga, cara memasukkan angkanya 3000003

      Comment by cadex — May 20, 2012 @ 21:12

  2. coba deh mas klo 30001000 (Tiga Juta Seribu), Thank you

    Comment by Jimmy — December 7, 2013 @ 15:59

    • terimakasih koreksinya mas Jimmy.
      silahkan didownload lagi rumus yang sudah saya koreksi..

      Comment by cadex — December 8, 2013 @ 19:38

  3. mas bisa nggak bikin rumus terbilang tanpa predikat puluh, ribu, ratus hanya anka saja.. misal 73 jadi tujuh tiga, bukan tujuh puluh tiga, ini digunakan untuk nulis raport, karena terbatasnya kolom & aturan dipenuliasan rapot tanpa menggunakan predikat pluh, ribu, ratus, terima kasih…

    Comment by Cahyadi Slamet — December 19, 2013 @ 07:04

    • terlebih dahulu didownload file di halaman ini dan
      angka yang akan dirubah ada di A5
      =INDEX(ListHuruf,MID(A5,1,1)+1) & ” ” & INDEX(ListHuruf,MID(A5,2,1)+1) & ” ” & INDEX(ListHuruf,MID(A5,3,1)+1)

      Comment by cadex — December 20, 2013 @ 15:08


RSS feed for comments on this post.

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

Create a free website or blog at WordPress.com.

%d bloggers like this: