Hai Ivan,
Array formula disebuah cell bisa dicopy ke cell lain.
Jika ada array formula di A1, dan ingin di-copy ke A2:A9, klik cell A1,
tekan CTRL C, blok A2:A9 (A1 tidak ikut diblok), tekan CTRL V
Dari array formula berbunyi :
=VLOOKUP($L2;IF($D$6:$D$21=Sheet1!M2;$B$6:$D$21);2;FALSE)
1. Andaikan kolom B:D berisi data bukan teks semua dan tidak ada error
value alias datanya bertipe numerik atau datetime atau boolean
(true/false), maka bisa tanpa fungsi IF, sehingga array formula dengan
vLookUp tersebut bisa diubah menjadi formula biasa seperti :
=vLookUp( $L2 , Index( ($D$6:$D$21=Sheet1!M2) * $B$6:$D$21 , 0 , 0 ) , 2 ,
FALSE )
bahkan bisa menggunakan SumIFs.
2. Andaikan antara kolom B:D, ada minimal 1 kolom berisi data yang bertipe
teks, maka susunan formula vLookUp nya menjadi :
=vLookUp( Trim($L2) , Index( Rept( $B$6:$D$21 , ($D$6:$D$21=Sheet1!M2) )
, 0 , 0 ) , 2 , FALSE )
Jika hasil vLookUp yang diambil (dalam hal ini kolom ke-2 dari B:D alias
nilai di kolom C) adalah bertipe numerik, maka hasil formula vLookUp di
atas perlu dikonversi menjadi bertipe numerik, seperti ini :
= 1 * vLookUp( Trim($L2) , Index( Rept( $B$6:$D$21 , ($D$6:$D$21=Sheet1!M2)
) , 0 , 0 ) , 2 , FALSE )
Fungsi Index Match juga bisa digunakan dalam kasus ini, seperti :
1. array formula karena ada IF-nya
=Index( $C$6:$C$21 , Match( $L2 , IF($D$6:$D$21=Sheet1!M2,$B$6:$B$21) ,
0 ) ) -> perhatikan jumlah kolom setiap reference
2. bila kolom B dan cell L2 bertipe numerik
a> array formula tanpa IF :
=Index( $C$6:$C$21 , Match( $L2 , ($D$6:$D$21=Sheet1!M2) * $B$6:$B$21 ,
0 ) )
b> formula biasa : (perhatikan cara mengubah formula 2a)
=Index( $C$6:$C$21 , Match( $L2 , INDEX( ($D$6:$D$21=Sheet1!M2) *
$B$6:$B$21 , 0 ) , 0 ) )
3. bila kolom B dan L2 bertipe teks
a> array formula tanpa IF :
=Index( $C$6:$C$21 , Match( $L2 , Rept( $B$6:$B$21 ,
($D$6:$D$21=Sheet1!M2)
) , 0 ) )
b> formula biasa : (perhatikan cara mengubah formula 2a)
=Index( $C$6:$C$21 , Match( $L2 , INDEX( Rept( $B$6:$B$21 ,
($D$6:$D$21=Sheet1!M2) ) , 0 ) , 0 ) )
btw,
Array formula yang diletakkan pada banyak cells dan merujuk ke banyak baris
data (apalagi ke cells yang belum ada datanya), akan memberi kesan Excel
bekerja lamban. Jadi, mohon ditanamkan sejak sekarang bahwa yang lamban
bukan Excel-nya, tapi yang memilih metode pengolahan datanya.
Untuk menghindari hal yang demikian, andaikan memungkinkan merujuk ke data
terurut dan pengolahan datanya bisa disusun per blok data, maka aturlah
demikian lalu menggunakan rujukan dinamis. Contoh pada kasus ini, nilai M2
yang berelasi dengan kolom D bisa menjadi penyusun blok, dengan cara
mengurutkan data di B:D berdasar kolom D ASC, lalu berdasar kolom B ASC (2
kriteria sort).
Formula Index bisa digunakan untuk mendapatkan area blok range yang sesuai
untuk nilai M tertentu, yang kemudian bisa digunakan dalam formula vLookUp
atau Index Match.
Wassalam,
Kid
2015-10-01 22:48 GMT+07:00 Ivan Sebastian [email protected]
[belajar-excel] <[email protected]>:
>
>
> master2 excel... butuh bantuan lagi nih...
> kalo ada rumus formula arraynya seperti ini..
> {=VLOOKUP($L2;IF($D$6:$D$21=Sheet1!M2;$B$6:$D$21);2;FALSE)}
>
> apa bisa diubah ke bentuk formula biasa ya??
>
> itu rumus vlookup dengan 2 kriteria kan ya... saya lagi cari2 rumus
> formula untuk vlookup 2 kriteria yg simple sebenernya pingin pake index dan
> match... tapi gagal paham.. dicoba2 tetep aja gagal mulu..
>
> kalo formula yg ini pas datanya banyak jadi repot jg mesti harus cse satu
> persatu xixixixi...
>
> thx
>
>
>
