Terimakasih penjelasannya Mr Kid, saya jadi paham kenapa file excel saya
menjadi lambat, wong isinya indirect semua.hahaha
2015-10-08 13:12 GMT+07:00 'Mr. Kid' [email protected]
mailto:[email protected] [belajar-excel] <[email protected]
mailto:[email protected]>:
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]
mailto:[email protected] [belajar-excel] <[email protected]
mailto:[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 :
~ 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)
Demikian, semoga dapat berguna
Salam,
Adrie
2015-10-08 10:17 GMT+07:00 heru mulyono [email protected]
mailto:[email protected] [belajar-excel] <[email protected]
mailto:[email protected]>:
Yth, Master excel
Mohon bantuan seperti file terlampir.
terimakasih
