Hai Winov,
Formula :
=LookUp( SumProduct( (a2:c2<>"")*10^(3-{1,2,3}) ) , {0,100,101,110,111} ,
{"?","buka","tutup","titip","confirm"} )
memanfaatkan karakteristik fungsi lookup, yaitu mencari dilarik pencarian,
yang terakhir sesuai dengan kriteria lookup.
Pada kasus ini, terbentuk suatu pola kriteria yang terdiri dari 3 kolom
dengan setiap kolom dalam kondisi ada isi atau tidak ada isi.
Kondisi yang terbentuk hanyalah 2 kondisi, seperti halnya kondisi TRUE atau
FALSE. Ada isi bisa diwakilkan ke TRUE dan tidak ada isi bisa diwakilkan ke
FALSE.
Artinya, jika ada isi maka langsung dikonversi ke nilai TRUE. Tidak ada isi
ke nilai FALSE. Maka terbentuklah bunyi :
a2<>"" untuk kolom 1, b2<>"" untuk kolom 2, dan c2<>"" untuk kolom 3. Jika
dikumpulkan menjadi sebuah larik data, maka akan berbunyi : a2:c2<>""
Kombinasi kriteria berisi 3 kolom dengan kolom ke-2 dan ke-3 akan berisi
jika kolom ke-1 ada isinya akan berupa kemungkinan :
kol1 kol2 kol3
(kosong) (kosong) (kosong)
ada (kosong) (kosong)
ada (kosong) ada
ada ada (kosong)
ada ada ada
yang diubah nilainya menjadi TRUE atau FALSE :
kol1 kol2 kol3
FALSE FALSE FALSE
TRUE FALSE FALSE
TRUE FALSE TRUE
TRUE TRUE FALSE
TRUE TRUE TRUE
karena TRUE setara 1 dan FALSE setara 0, maka bisa diubah juga menjadi :
kol1 kol2 kol3 bentuk_akhir_gabungan nilai_output
0 0 0 000 ?
1 0 0 100 buka
1 0 1 101 tutup
1 1 0 110 titip
1 1 1 111 confirm
kemudian kolom bentuk_akhir_gabungan di-sort ASC
dan akan membentuk larik angka { 0 , 100 ,
101 , 110 , 110 }
dengan larik nilai output yang bersesuaian adalah { "?" , "buka" , "tutup"
, "titip" , "confirm" }
Nah, kondisi kol1 2 dan 3 yang dibentuk dalam TRUE atau FALSE dengan bunyi
a2:c2<>"" harus diubah menjadi suatu nilai 3 digit
digit ke-1 untuk kol1, digit ke-2 untuk kol2, dan digit ke-3 untuk kol3
Berarti,
digit ke-1 harus menyediakan 2 digit dibelakangnya, yaitu dengan
mengalikannya dengan 100
digit ke-2 harus menyediakan 1 digit dibelakangnya, yaitu dengan
mengalikannya dengan 10
digit ke-3 harus menyediakan 0 digit dibelakangnya, yaitu dengan
mengalikannya dengan 1
agar kalau dijumlahkan seluruh hasil perbandingan kol1 sampai kol3 akan
terbentuk 111 jika semua kolom terisi data
Andaikan kol2 kosong, berarti FALSE yang setara 0 akan membuat nilai 10
(penyedia digit setelahnya) akan dikali 0
maka pada kondisi TRUE FALSE TRUE akan terbentuk 1*100
0*10 1*1 yang jika ditambahkan menjadi 100+0+1=101
Untuk menjumlahkan semua hasil perkalian kriteria tiap kolom (yang dibentuk
a2:c2<>"") digunakanlah fungsi SumProduct yang bisa bekerja dengan inputan
berupa array.
Sedangkan proses perkaliannya untuk menghasilkan 100 , 10 , 1 bisa dengan :
a. suatu larik { 100 , 10 , 1 }
b. atau ekspresi 10^{ 2 , 1 , 0 }
c. atau ekspresi 10^( 3 - { 1 , 2 , 3 } )
ekspresi c digunakan pada formula agar tampak kunci pendinamisan formula
ketika ada banyak kolom kriteria lainnya, yaitu dengan mengubahnya menjadi
Column( $a:$c ) jika kriteria 3 kolom menggantikan larik berbunyi { 1 , 2 ,
3 }
Jadi, formula di atas bukan formula akhir terpendek, tetapi formula yang
bisa dengan mudah didinamiskan penggunaannya.
Pada kondisi kombinasi setiap kolom kriteria yang banyak, maka penggunaan
larik { 0 , 100 , 101 , 110 , 110 } yang bersesuaian
dengan larik { "?" , "buka" , "tutup" , "titip" , "confirm" } diubah
menjadi suatu tabel 2 kolom, yaitu kolom pertama adalah nilai
bentuk_akhir_gabungan dan kolom kedua adalah nilai_output
bentuk_akhir_gabungan nilai_output
0 ?
100 buka
101 tutup
110 titip
111 confirm
dan fungsi vLookUp atau formula Index Match juga menjadi bisa digunakan
sebagai alternatif penggunaan fungsi LookUp
Jadi, pada formula berbunyi :
=LookUp( SumProduct( (a2:c2<>"")*10^(3-{1,2,3}) ) , {0,100,101,110,111} ,
{"?","buka","tutup","titip","confirm"} )
bagian :
SumProduct( (a2:c2<>"")*10^(3-{1,2,3}) ) adalah nilai_lookup
{0,100,101,110,111} adalah larik pencarian
{"?","buka","tutup","titip","confirm"} adalah larik nilai output yang
diinginkan
Bagian SumProduct( (a2:c2<>"")*10^(3-{1,2,3}) ) bisa diubah menjadi :
> Sum( (a2:c2<>"")*10^(3-{1,2,3}) ) tetapi formula harus di-enter sebagai
array formula (tekan Ctrl Shift Enter)
> 10^(3-{1,2,3}) bisa diganti menjadi larik { 100 , 10 , 1 }
> 10^(3-{1,2,3}) juga bisa diganti menjadi 10^(3-Column($a:$c))
Sama kan dengan konsep pembentukan kolom bantu TRUEFALSETRUE dan sebagainya
yang sudah disampaikan pada posting sebelum posting formula ini...
:)
gitu kelleez ye..
memahami suatu formula memang dibutuhkan membaca ulang setiap bagian si
formula tersebut berkelleeez-kelleez..
Wassalam,
Kid.
2014-05-13 6:46 GMT+07:00 Winov X [email protected] [belajar-excel] <
[email protected]>:
>
>
> Yth Mr Kid,
> itu formula keren banget
>
> bisa minta tolong dijelaskan gak?
>
> tengkyu sebelumnya
>
> -Win-
> Pada Senin, 12 Mei 2014 19:41, "'Mr. Kid'
> [email protected][belajar-excel]" <
> [email protected]> menulis:
>
> Hai Iqbal,
>
> misal data di A2:C2, di D2 bisa juga diisi formula : (bukan array formula)
> =LookUp( SumProduct( (a2:c2<>"")*10^(3-{1,2,3}) ) , {0,100,101,110,111} ,
> {"?","buka","tutup","titip","confirm"} )
>
> copy ke baris data lain.
>
> *** jika regional setting komputer setempat adalah Indonesian, maka
> formula akan menjadi :
>
> =LookUp( SumProduct( (a2:c2<>"")*10^(3-{1\2\3}) ) ; {0\100\101\110\111} ;
> {"?"\"buka"\"tutup"\"titip"\"confirm"} )
>
> Wassalam,
> Kid.
>
>
>
>
>
>
> 2014-05-11 10:48 GMT+07:00 'Muhammad Iqbal'
> [email protected][belajar-excel]
> <[email protected]>:
>
>
> Dear be exceler,
> Mohon bantuan untu case seperti berikut ini
>
> *A*
> *B*
> *C*
> *result*
> text
>
>
> buka
> text
> text
>
> titip
> text
> text
> text
> confirm
> text
>
> text
> Tutup
>
> Bagai mana rumusan yang bisa saya terapkan bila kolom “result” ber logika,
> Jika kolom A berisi data, maka tulislah “Buka”
> Jika kolom A dan B berisi dana , maka tulis “titip
> Jika kolom A dan C berisi data, maka tulis “tutup”
> Jika kolom A, B dan C berisi data, maka tulis “confirm”
>
>
> Regard
> *Muhammad Iqbal*
>
>
>
>
>
>
