Terimakasih banyak mas Anton Terlampir revisinya Thanks -zainul-
From: [email protected] [mailto:[email protected]] On Behalf Of summonery Sent: Sunday, November 13, 2011 7:29 PM To: [email protected] Subject: [belajar-excel] Re: Mega Formula untuk Fungsi Terbilang silakan dicoba untuk: 2,000 1,000,001 11,000,001 1,000,000,001 1,000,100,101 100,000,000,000 111,000,000,000 111,000,000,001 >semoga bermanfaat *topik tenggelam dalam topik --- In [email protected], "zainul_ulum[at]yahoo[dot]com" <zainul_ulum@...> wrote: > > Be-Exceler, > Mohon bantuan untuk menguji fungsi mega formula yang aku buat untuk fungsi > terbilang untuk angka bulat dari 1 sampai 999999999 > > Fungsi ini menggunakan bantuan dua buah [static named ranges], yaitu > 1. frm > refers to ="000000000" > > 2. ListHuruf > refers to > ={"","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"} > > Mega Formulanya dengan asumsi angka yang akan disebut di cell A2 > =SUBSTITUTE(INDEX(ListHuruf,MID(TEXT(A2,frm),1,1)+1) > &REPT(" ratus ",-MID(TEXT(A2,frm),1,3)>99) > &INDEX(ListHuruf,(-MID(TEXT(A2,frm),2,2)<20)*MID(TEXT(A2,frm),2,2)+1) > &INDEX(ListHuruf,(-MID(TEXT(A2,frm),2,2)>19)*MID(TEXT(A2,frm),2,1)+1) > &REPT(" puluh > ",-MID(TEXT(A2,frm),2,2)>19)&INDEX(ListHuruf,(-MID(TEXT(A2,frm),2,2)>19)*MID > (TEXT(A2,frm),3,1)+1) > &REPT(" juta ",LEN(A2)>6) > &INDEX(ListHuruf,MID(TEXT(A2,frm),4,1)+1) > &REPT(" ratus ",-MID(TEXT(A2,frm),4,3)>99) > &INDEX(ListHuruf,(-MID(TEXT(A2,frm),5,2)<20)*MID(TEXT(A2,frm),5,2)*(A2>2000) > +1) > &INDEX(ListHuruf,(-MID(TEXT(A2,frm),5,2)>19)*MID(TEXT(A2,frm),5,1)*(A2>2000) > +1) > &REPT(" puluh ",-MID(TEXT(A2,frm),5,2)>19) > &INDEX(ListHuruf,(-MID(TEXT(A2,frm),5,2)>19)*MID(TEXT(A2,frm),6,1)+1) > &REPT("seribu ",(A2<2000)*(LEN(A2)=4)) > &REPT(" ribu ",(A2>2000)) > &INDEX(ListHuruf,MID(TEXT(A2,frm),7,1)+1) > &REPT(" ratus ",-MID(TEXT(A2,frm),7,3)>99) > &INDEX(ListHuruf,(-MID(TEXT(A2,frm),8,2)<20)*MID(TEXT(A2,frm),8,2)+1) > &INDEX(ListHuruf,(-MID(TEXT(A2,frm),8,2)>19)*MID(TEXT(A2,frm),8,1)+1) > &REPT(" puluh ",-MID(TEXT(A2,frm),8,2)>19) > &INDEX(ListHuruf,(-MID(TEXT(A2,frm),8,2)>19)*MID(TEXT(A2,frm),9,1)+1),"satu > ratus","seratus") > > Contoh file terlampir > Terimakasih > -zainul- >
Terbilangv6.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
