Fungsi SUBSTITUTE Bercabang (Nested Substitute)

Pernah menggunakan Fungsi Substitute? yang berguna untuk mengganti teks yang lama dengan yang baru, dengan sintaks =SUBSTITUTE(text;old_text;new_text). Contoh sederhana misal pada cell A1 tertulis teks "Agus", kemudian kita ingin menggantinya dengan "Dewi", rumusnya =SUBSTITUTE(A1;"Agus";"Dewi")
Mudah bukan?

Tapi bagaimana dengan teks yang ingin kita ganti lebih dari satu? 2 atau lebih? Fungsi SUBSTITUTE ini mengijinkan kita untuk dibuat nested atau bercabang seperti halnya Fungsi IF bercabang (Nested IF's).

Kasus: Kita ingin merapikan data yang kondisinya seperti ini:

Yang ingin dihilangkan adalah karakter tanda sambung(-), titik(.), koma(,) dan garis miring(/) dan diganti dengan karakter kosong atau "" kalau di MS Excel.

Bagaimana Nested Substitute itu?
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"-";"");",";"");".";"");"/";"")

Silahkan jika ingin ada penambahan karakter yang ingin diganti/dihilangkan dengan karakter yang baru.

Salam

Download File Latihan (Excel 2003)
Download File Latihan (Excel 2010)

Menghitung Bonus/Komisi Penjualan

Sebenarnya ini sama dengan postingan saya mengenai pengambilan data dari 2 tabel, saya akan memberikan contoh pada kasus menghitung bonus atau komisi berdasarkan hasil penjualan salesman.

Tabel Kasus 1:

Pada kolom bonus yang saya warna kuning akan muncul secara otomatis yang merupakan bonus (%) berdasarkan kriteria Sales/Penjualan dan Masa Kerja yang dibedakan menjadi 2, yaitu :
1. Masa Kerja <=5 Tahun
2. Masa Kerja > 5 Tahun

Rumus yang bisa diterapkan untuk mencari Bonus (%) dari 2 tabel referensi berdasarkan masa kerja karyawan/salesman:

1. =VLOOKUP(C2;IF(B2<=5;$G$2:$H$6;$J$2:$K$6);2)

2. =IF(B2<=5;VLOOKUP(C2;$G$2:$H$6;2);VLOOKUP(C2;$J$2:$K$6;2))

3. =IF(B2<=5;INDEX($G$2:$H$6;MATCH(C2;$G$2:$G$6);2);INDEX($J$2:$K$6;MATCH(C2;$J$2:$J$6);2))


Tabel Kasus 2:
Contoh yang kedua ini sebenarnya hampir sama, tapi berbeda bentuk tabel dan penyelesaiannya,

Kemudian untuk tabel rujukan discount

Mohon diperhatikan ada 3 tabel diatas, Tabel Pro1 dan Pro2 sudah jelas adalah tabel potongan harga atau discount. Tabel yang ke-3 adalah tabel rujukan/referensi untuk menentukan posisi tabel discount bagi Pro1 dan Pro2.

Pada Cell C17 di-input =VLOOKUP(A17;$G$24:$H$25;2) yang akan menghasilkan nilai 1 sesuai Tabel Rujukan diatas, kemudian copas kebawah ke Cell C18.

Pada Cell D17 di-input
=B17*INDEX(($H$17:$H$21;$K$17:$K$21);MATCH(B17;$G$17:$G$21);;C17)
yang akan menghasilkan nilai Rp.5000, kemudian copas-lah ke bawah sampai Cell D18.

Pada Cell E17 di-input =INDEX(($H$17:$H$21;$K$17:$K$21);MATCH(B17;$G$17:$G$21);;C17) yang akan menghasilkan nilai 2%

Alternative rumus Cell D17 (Disc Rp.) :
=B17*VLOOKUP(B17;CHOOSE(C17;G17:H21;J17:K21);2)

=B17*INDEX((H17:H21;K17:K21);MATCH(B17;G17:G21);;VLOOKUP(A17;G24:H25;2;FALSE))

Alternative rumus Cell E17 (Disc %) :
=VLOOKUP(B17;CHOOSE(C17;G17:H21;J17:K21);2)

=INDEX((H17:H21;K17:K21);MATCH(B17;G17:G21);;VLOOKUP(A17;G24:H25;2;FALSE))

Download File Latihan

Ambil Data dengan "OR" Criteria

Mungkin pembaca sudah familiar dengan penggunaan Fungsi OR yang digabung dengan Fungsi IF, seperti ini: =IF(OR(A1=2;B1=3);"Ya";"Tidak"), jika salah satu kondisi yaitu A1=2 atau B1=3 terpenuhi maka akan ditampilkan Teks "Ya", sebaliknya jika keduanya tidak terpenihi maka akan ditampilkan "Tidak". Ini contoh yang sederhana yang bayak digunakan oleh para pengguna Excel. Saya akan memberikan contoh pengambilan data dengan kondisi OR tanpa menggunakan Fungsi OR, bingung ya...langsung aja ke TKP.

Tabel Kasus:

Dengan kriteria dan hasil yang diinginkan:

Jadi ada 2 kriteria, yaitu Area atau Nama, yang berdasarkan salah satu dari 2 kriteria tsb, kita ingin mengambil data Item atau Produk.

Well ini contoh2x rumusnya:

dan ada lagi....

Silahkan dicoba kasus dan rumus diatas, dan siapa tahu anda bisa membuat rumus dan berkreasi sendiri.

Download File Latihan (Excel 2003)
Download File Latihan (Excel 2010)

Mengurutkan Data dan Menghilangkan Duplikasi

Postingan kali ini saya ingin membahas topik mengurutkan data dan menghilangkan data yang dobel. Mungkin ada kasus yang tabel data yang datanya acak / random dan kita ingin merapikannya. Saya kasih contoh tabel2x yang sederhana supaya lebih mudah memahami rumusnya.

Tabel Kasus 1:

Pada gambar diatas hasil yang dicapai adalah mengambil data unik dan yang ke-2 sekaligus mengurutkannya.

Rumus Data Unik (Extract Unique Values)
1. =IFERROR(INDEX($A$3:$A$11; MATCH(0;COUNTIF($C$2:C2;$A$3:$A$11);0));"")
Rumus Data nik dan Sort
2. =IFERROR(INDEX($A$3:$A$11;MATCH(0;COUNTIF($A$3:$A$11;"<"&$A$3:$A$11)-SUM(COUNTIF($A$3:$A$11;"="&E$2:E2));0));"")

Tabel Kasus 2:

Kasus yang ke-2 terdapat blank atau baris yang kosong, jika menggunakan rumus pada kasus sebelumnya tidak akan berhasil.
 Rumus Data Unik
1. =IFERROR(INDEX($A$15:$A$23; MATCH(0;IF(ISBLANK($A$15:$A$23);1;COUNTIF($C$14:C14; $A$15:$A$23)); 0));"")
2. =IFERROR(INDEX($A$15:$A$23;AGGREGATE(15;6;IF(FREQUENCY(IFERROR(MATCH(IF($A$15:$A$23<>"";$A$15:$A$23);$A$15:$A$23;0);"");ROW($A$15:$A$23)-ROW($A$15)+1);ROW($A$15:$A$23)-ROW($A$15)+1);ROWS($A$1:A1)));"")

Rumus Data Unik dan Sort
3. =IFERROR(INDEX($A$15:$A$23;MATCH(SMALL(IF($A$15:$A$23<>"";IF(ISNA(MATCH($A$15:$A$23;$F$14:F14;0));MMULT((IF($A$15:$A$23<>"";$A$15:$A$23)>TRANSPOSE(IF($A$15:$A$23<>"";$A$15:$A$23)))+0;ROW($A$15:$A$23)^0)));1);MMULT((IF($A$15:$A$23<>"";$A$15:$A$23)>TRANSPOSE(IF($A$15:$A$23<>"";$A$15:$A$23)))+0;ROW($A$15:$A$23)^0);0));"")

DOWNLOAD FILE LATIHAN

Menjumlahkan dari 2 Tabel Berbeda Dimensi

Pernahkah anda mempunyai kasus punya 2 tabel yang berbeda dimensi, Tabel A mempunyai 5 baris data dan Tabel B mempunyai 10 baris data, dan kita harus menjumlahkan item dari 2 tabel tersebut? Mari kita membahasnya.

Tabel Kasus:

Hasil yang diinginkan menjumlahkan........

Rumus yang bisa diaplikasikan (Input di cell B13 dan copy ke bawah) :
=SUMIF($A$2:$D$9;A13;$B$2:$E$9)

atau

=SUMIF($A$2:$B$6;A13;$B$2:$B$6)+SUMIF($D$2:$E$9;A13;$E$2:$E$9)

atau

=IFERROR(VLOOKUP(A13;$A$2:$B$6;2;FALSE);0)+VLOOKUP(A13;$D$2:$E$9;2;FALSE)

Salam

Download File Latihan

Membuat Akhiran Untuk Angka dan Tanggal

Pernah membaca tulisan angka seperti ini? 1st, 2nd atau 3rd atau di bahasakan menjadi yang pertama, kedua dan ketiga. Dalam bahasa inggris 1st, 2nd dan 3rd adalah Ordinal Suffixes atau akhiran yang menandakan suatu urutan. Misal "Anna seated in 1st (dibaca "first") class" atau "Anna is the 2nd (dibaca "second") born". Nah saya ingin membahas tentang ini, dengan Excell tentunya. Bagaimana menambahkan akhiran urutan ini pada sebuah angka dan tanggal.

Hasil yang diinginkan seperti ini:

Rumus yang bisa diterapkan seperti ini:

=A2&""&MID("thstndrdthstndrdth";MATCH(IF(MOD(A2;100)>29;MOD(A2;10)+20;MOD(A2;100));{0;1;2;3;4;21;22;23;24};1)*2-1;2)

atau

=A2&MID("thstndrdth";MIN(9;2*RIGHT(A2)*(MOD(A2-11;100)>2)

atau

=A2&IF(OR(MOD(A2;100)={11\12\13});"th";LOOKUP(--RIGHT(A2);{0\"th";1\"st";2\"nd";3\"rd";4\"th"}))

atau

=A28&IF(AND(A2>=10;A2<=14);"th";CHOOSE(MOD(A2;10)+1;"th";"st";"nd";"rd";"th";"th";"th";"th";"th";"th"))

Contoh berikut adalah penambahan akhiran untuk Tanggal:

Rumus yang dapat diaplikasikan:

=DAY(A1)&IF(OR(DAY(A1)={1;2;3;21;22;23;31});CHOOSE(1*RIGHT(DAY(A1)*0,1);"st";"nd";"rd ");"th")&TEXT(A1;" mmmm, yyyy")

atau

=DAY(A3)&IF(INT(MOD(DAY(A3);100)/10)=1;"th"; IF(MOD(DAY(A3);10)=1;"st";IF(MOD(DAY(A3);10)=2;"nd";IF(MOD(DAY(A3);10)=3;"rd";"th"))))& " day of " & TEXT(A3;"mmmm yyyy")

atau

= TEXT(A5;"mmmm d")&LOOKUP(DAY(A5);{1\2\3\4\21\22\23\24\31;"st"\"nd"\"rd"\"th"\"st"\"nd"\"rd"\"th"\"st"})&", "&YEAR(TODAY())

atau

=TEXT(A7;"mmmm") & " " & DAY(A7) &MID("thstndrdth";MIN(9;2*RIGHT(DAY(A7))*(MOD(DAY(A7)-11;100)>2)+1);2) & ", " & YEAR(A7)

Download File Latihan