hmmm...
Pinjam formula mas Adrie yang berbunyi :
VLOOKUP(D$20,*INDIRECT("$A"&(ROW()-21)*7&":$B9999")*,2,0)
Tampaknya header hasil di baris 20 mulai kolom D .
Berarti record hasil pertama mulai di D21 alias baris 21.
Kemudian, tampaknya 1 blok data berisi paket 7 baris di kolom A dan B
Formula di atas, katanya untuk baris hasil mulai baris 22
~ Angka -21 karena rumus dimulai pada row ke 22, sehingga harus di
normalkan ke 1, apabila rumus tersebut dimulai pada row lain, maka angkanya
bias disesuaikan kembali.
Misal untuk baris hasil 22 tersebut, hasil *ROW()-21* adalah 1, yang
kemudian dikali 7 akan menghasilkan 7 dan artinya area lookup akan
terbentuk dari A7:B9999. Berarti, jika ada header hasil yang nilainya sama
dengan A7, maka dibaris 22 ini akan mengambil nilai kolom B baris 7. Kalau
baris 22 adalah record hasil ke-2, berarti akan terjadi sedikit pergeseran
area pengambilan.
btw,
Formula mas Adrie di atas (yang tadinya untuk baris hasil ke-1 dan ke-2
berbeda bunyi formulanya) bisa diubah menjadi formula universal yang bisa
berlaku untuk seluruh baris dan kolom hasil.
Header hasil di baris 20, record hasil pertama di baris 21 mulai kolom D.
Formula di D21 :
VLOOKUP(D$20,*INDIRECT("A"&(ROW()-21)*7+1&":B9999")*,2,0) -> tanda $
dihilangkan supaya panjang formula tetap
> di baris hasil 21, bagian *ROW()-21* akan bernilai 0, kemudian dikali 7
menjadi 0, lalu ditambah 1 menjadi 1, maka area lookup di A1:B9999
> di baris hasil 22, bagian *ROW()-21* akan bernilai 1, kemudian dikali 7
menjadi 7, lalu ditambah 1 menjadi 8, maka area lookup di A8:B9999
fyi,
fungsi Indirect termasuk kelompok fungsi volatile, yaitu yang akan selalu
dikalkulasi ulang oleh Excel setiap ada pengubahan nilai data dalam suatu
cell dimanapun dia berada walaupun TIDAK berhubungan sama sekali dengan
formula yang menggunakan fungsi kelompok volatile tersebut.
Ketika formula di atas (yang berisi fungsi Indirect yang volatile) dipasang
di banyak sekali cells, maka akan ada kemungkinan lama kelamaan akan
memberi kesan Excel menjadi lambat dalam bekerja. Oleh sebab itu, formula
di atas perlu diubah sedikit agar tidak berisi fungsi Indirect lagi.
Index bisa menghasilkan object range.
VLOOKUP(D$20,*INDex($A:$A,(ROW()-21)*7+1):$B9999*,2,0)
Andaikan baris terakhir data ternyata hanya sampai 1000, ganti saja 9999
dengan 1000 atau dengan baris terakhir data yang ada.
Formula di atas dipasang mulai cell pertama hasil, yaitu di D21, sampai
cell terakhir hasil, kolom tertentu baris tertentu
Bagian *INDex($A:$A,(ROW()-21)*7+1):$B9999* akan menghasilkan area lookup :
> di A1:B9999 ketika formula yang dikalkulasi adalah baris hasil 21
> di A8:B10000 ketika formula yang dikalkulasi adalah baris hasil 22
dst...
Kira-kira demikian
Wassalam,
Kid
2015-10-08 11:08 GMT+07:00 Adrie Handria [email protected]
[belajar-excel] <[email protected]>:
>
>
> Dear Mas Heru
>
> Boleh bantu jawab ya, semoga berhasil.
>
> Formula yang saya gunakan cukup sederhana, yaitu ---
> VLOOKUP(*D$20*,$A$2:$B$9995,2,0)
> ---
> D$20 merupakan lookup value dari "Judul", rumus tsb untuk row pertama,
> agak berbeda dengan rumus pada row ke 2 dst, yaitu --- VLOOKUP(D$20,
> *INDIRECT("$A"&(ROW()-21)*7&":$B9999")*,2,0) ---
>
> Fungsi dari *"$A"&(ROW()-21)*7&":$B9999"* itu sendiri adalah membuat
> range value tidak statis, tapi mengikuti hasilnya mengikuti row, sehingga
> apabila di tarik kebawah, formula rangenya akan seperti ini :
> *[image: Inline image 1]*
> ~ Angka -21 karena rumus dimulai pada row ke 22, sehingga harus di
> normalkan ke 1, apabila rumus tersebut dimulai pada row lain, maka angkanya
> bias disesuaikan kembali.
> ~ Angka *7 adalah jumlah row pada setiap grupnya (jumlahnya harus sama
> pada setiap grupnya)
> [image: Inline image 2]
>
> Demikian, semoga dapat berguna
> Salam,
>
>
> Adrie
>
>
>
>
> 2015-10-08 10:17 GMT+07:00 heru mulyono [email protected]
> [belajar-excel] <[email protected]>:
>
>>
>>
>> Yth, Master excel
>>
>> Mohon bantuan seperti file terlampir.
>> terimakasih
>>
>>
>
>
