Rumus Excel Angka Terbilang Tanpa VBA Bagian Dua

Melanjutkan tulisan pertama Bagaimana menggunakan rumus excel angka terbilang tanpa VBA, Mengingatkan kembali strategi menghasilkan angka terbilang dengan memecah menjadi satuan trilyun, milyar, juta, ribu dan satuan, bagian pertama baru saja memastikan setiap input angka apakah sesuai dengan masing-masing satuan.

Bagian kedua ini akan dibahas fungsi LEN(), LEFT(), RIGHT(), dan IF() untuk memasing-masing kelompok satuan untuk mendapatkan angka ratusan, angka puluhan dan angka satuan. Contohnya kalau satuan bernilai 123 maka angka ratusannya adalah 1, angka puluhannya adalah 2, dan angka satuannya 3.

Langkah pertama adalah memisah angka menjadi kelompok-kelompok berisi tiga angka. Contoh 123.456.789 akan menjadi kelompok 123, kelompok 456, dan  kelompok 789. Bisa? Jangan lupa kalau angkanya 12.345 akan menjadi kelompok 12, dan kelompok 345? Makin menantang bukan.

Ini solusinya. Pertama inputnya adalah teks angka di cell B2. Kita akan pecahkan dulu kelompok satuan nya ya, artinya tiga angka terakhir, Caranya bila panjangnya lebih dari 4 angka, maka diambil 3 angka terakhir. Bila kurang dari 4 diambil sesuai panjangnya. Contoh kalau input angkanya 1.234, karena panjangnya 4 maka diambil tiga angka terakhir, yaitu 234. Kalau input angkanya 68, karena kurang dari 4 diambil semua angkanya yaitu 68.

Langkah mengambil angka satuan. Misalkan angkanya adalah 1234. Maka hasil satuannya adalah 234. Untuk menghitungnya dapat digunakan,

Jika Panjang Angka > 3, Berarti ada 3 Angka yang diambil, kalau tidak diambil sejumlah angka. Perhatikan Panjang Angka adalah 4 (Cell B5). Kali ini digunakan fungsi IF(), formatnya adalah IF (kriteria, nilai jika benar, nilai jika salah). Maka untuk memasikan angkanya adalah lebih dari 3 kita rumuskan kriterianya adalah B5 > 3, Jika benar lebih dari 3 angka maka diambil 3 angka terakhirnya saja, Jika sama dengan atau kurang dari 3 diambil kata semua angkanya.

Nah kita akan berkenalan dengan fungsi LEFT() dan RIGHT(). Keduanya digunakan untuk mengambil teks dari depan, LEFT(), dan mengambil teks dari belakang. Maksudnya? Begini. misalkan ada angka 1234. kita mau ambil 3 angka terakhir, maka kita bisa pakai fungsi RIGHT() dengan format RIGHT(alamat cell, jumlah_karakter_yang_diambil), jadi kita gunakan RIGHT(B2, 3). Maka hasilnya adalah 234.

Sebaliknya kalau digunakan fungsi LEFT() digunakan untuk mengambil 3 angka di depan, maka LEFT(B2, 3) dari angka 234.

Maka untuk angka satuan digunakan kombinasi IF, dan RIGHT seperti ini,

Rumus 3 Satuan
= IF(Satuan Benar,IF(Panjang Angka>3,RIGHT(Angka,3),Angka))

Bila diterapkan, seperti ini,

Digambar kita lihat angka 234, digunakan rumus

=IF(E9,IF(B5>3,RIGHT(B2,3),B2))

E9 berisi apakah benar/salah ada angka satuan, B5, berisi panjang angka. dan B2 berisi angka yang diperiksa.
IF (B5 > 3, digunakan untuk memeriksa apakah panjang angka > 3,
RIGHT(B2,3), Bila benar ambil tiga angka paling kanan
B2, bila salah ambil semua angka.


Selanjutnya untuk memeriksa ribuan digunakan rumus, hampir serupa dengan perubahan jumlah karakter yang diperiksa dan posisi angka yang diambil.

=IF(D9,IF(B5>6,LEFT(RIGHT(B2,6),3),LEFT(RIGHT(B2,B5),B5,3)),"")

D9, Bila angka ribuan benar, maka periksa

IF (B5 > 6, apakah panjang angka lebih dari 6 (satu juta lebih?)

LEFT( 

RIGHT(B2,6), ambil enam angka terakhir 

, 3),  ambil tiga angka pertama

Bila panjang angka kurang atau sama dengan 6, (kurang dari 1 juta)

LEFT(

RIGHT(B2,B5), ambil semua angka tersisa

, B5-3), ambil panjang angka dikurangi 3 (1,2,3 bila ada)

Demikian seterusnya

C11 = IF(C9,IF(B5>9,LEFT(RIGHT(B2,9),3),LEFT(RIGHT(B2,B5),B5-6)),"")

B11 = IF(B9,IF(B5>12,LEFT(RIGHT(B2,12),3),LEFT(RIGHT(B2,B5),B5-9)),"")

A11 = IF(A9,IF(B5>15,LEFT(RIGHT(B2,15),3),LEFT(RIGHT(B2,B5),B5-12)),"")

Sehingga dihasilkan seperti gambar berikut.

Kita baru saja menyelesaikan memecah sederetan angka menjadi kelompok tiga angka. Dengan tiga angka ini, selanjutnya kita akan turunkan menjadi ratusan, puluhan dan satuan. contoh 345 akan jadi 3,4,dan 5. Untuk mendapatkan angka ratusan caranya mudah cukup kita cek panjang angkanya apakah > 2, kalau iya ambil angka pertama, pakai LEFT(1). sedangkan untuk angka puluhan kita cek apakah panjangnya > 1, kalau iya, kita ambil dua angka terakhir, kemudian ambil angka pertama. 

Ringkasnya ada di gambar berikut.

Dari gambar terlihat dengan rumus excel sederhanan IF(), LEN(), LEFT(), dan RIGHT(), kita bisa memecah angka menjadi kelompok 3-angka, kemudian dipecah lagi menjadi ratusan, puluhan dan satuan. Untuk filenya dapat didownload di Group facebook Jagoan Excel, ya. 

Jangan lupa "share" nya.