hmmm...
formulanya lemot karena karena diminta bekerja terhadap seluruh cells dalam
suatu kolom.
=IFERROR(INDEX(BOSNET!$AM:$AM,SMALL(IF((BOSNET!$A:$A='db
Retur'!$E$2)*(BOSNET!$B:$B='db Retur'!$E$3),ROW(BOSNET!$AM:$AM
)-ROW(BOSNET!$AM$2)+2),ROWS($A$2:$A2))),"")
Kesalahan utama dalam menggunakan array formula, terutama pada inputan
berupa array adalah seperti yang dikuning di atas, yaitu merujuk ke semua
cells dalam kolom tersebut.
Pada array formula atau formula yang diberi inputan array, berapapun jumlah
cells nya akan dikomputasi.
Pada formula di atas, jika menggunakan Excel 2003 yang berisi 65000 baris,
maka formula di atas akan mengkomputasi 65000 baris di setiap kolom yang
dikuning, walau cells yang terisi data baru 1 bijik record saja.
Bagaimana dengan Excel 2007 ke atas yang ada 1juta baris ?
Tentu Excel akan terkesan lemot dalam bekerja. dan berulang kali, selalu
ditekankan bahwa yang lemot bukan Excel-nya, tapi user yang membuat formula
tersebut.
Bagaimana supaya menjadi lebih cepat ?
1. ganti semua rujukan yang dikuning agar merujuk ke baris yang ada datanya
saja. Jika data mulai baris 2 dan baru ada 3 data, berarti rujukannya dari
baris 2 sampai baris 4 saja. bunyi rujukan $am:$am akan menjadi $am$2:$am$4.
Kalau recordnya berubah menjadi sampai baris 10 Excel bagaimana ?
>> ya tentu saja ubah lagi formulanya agar merujuk sampai baris 10
Gak praktis dong.
>> iya, memang gak praktis kalau begitu terus menerus. Kalau mau lebih
praktis, buatlah nama range yang merujuk secara dinamis.
2. ganti rujukan yang dikuning agar merujuk ke suatu nama range yang
dinamis.
Misal untuk kolom AM dibuat nama range bernama KolomAM yang dirujuk
dengan formula =$AM$2:index( $AM:$AM , counta( $a:$a ) )
dengan asumsi, bahwa kolom A selalu terisi alias kolom kunci yang tidak
boleh kosong disetiap record data.
Jadi nanti di array formula, semua bunyi yang merujuk ke range AM:AM
diubah menjadi KolomAM (nama range yang dirujuk dinamis alias nama range
yang dinamis)
Wah... nama range-nya harus dibuat dulu dong per kolom yang dipakai.
Ada banyak sheet dan per sheet ada banyak kolom nih... Jadi capek bikin
nama range-nya. Mana gak mudeng pula...
>> hmmm... capek juga ngasih saran solusinya kellezz...
3. Pakailah fitur List (xl2003) atau Excel Table (xl2007 ke atas) supaya
rujukan formula bisa dinamis.
>> gak pakai bikin nama range dinamis lagi kan... jadi gak ada alasan bakal
capek bikin nama range...
>> alasannya apa lagi ?
4. Walau sudah dirujuk gak sekolom (alias ke baris berdata saja) atau sudah
pakai nama range dinamis ataupun pakai object excel table, suatu saat array
formula tersebut akan membuat kesan Excel bekerja lemot lagi, yaitu ketika
record datanya sangat banyak. Cobalah manfaatkan kolom-kolom bantu atau
tabel-tabel bantu yang disusun dengan formula sederhana.
5. solusi nomor 4 itu pun suatu saat akan membuat Excel terkesan lemot
lagi... Kalau bisa, tabel bantu atau kolom bantu nya pakai fitur Pivot
Table.
>> yaaaa..... harus belajar lagi... capek ah belajarnya
>> hehehe... capek juga ngajak belajarnya
6. suatu saat, tabel bantu atau kolom bantu pakai pivot table menjadi gak
efisien dan Excel terkesan ada lag output... Andai memungkinkan, ubah
sedikit layout outputnya supaya bisa tanpa tabel bantu dan langsung disusun
dengan pivot table.
7. solusi 6 akan ada maksimumnya, yaitu ketika datanya sangat banyak dan
pivot tablenya mulai terasa berat dalam loading data. Mulailah memanfaatkan
fitur Get External Data untuk memilah data yang pokok dan membentuknya
menjadi tabel sumber data pivot table yang siap pakai dengan jumlah record
yang khusus per layout output.
8. solusi 7 masih bisa menjadi lambat suatu saat nanti, maka mulailah
memindahkan data ke database, seperti ke Access, MySQL, Oracle, MS SQL
Server, PostgreSQL, dsb, lalu manfaatkan fitur Get External Data lagi. Tapi
kali ini yang disuruh kerja keras adalah si database dan bukan Excel lagi.
9. solusi 8 masih bisa lemot lagi (percaya deh), maka mulailah membuat
sistem database pengolahan data. Excel bisa dijadikan area output jadinya
saja.
10. kalau nomor 9 ini masih lemot juga, maka ganti developer yang membangun
sistem databasenya, karena ndak memberi peringatan akan keseimbangan antara
infrastruktur dan data yang harus diolah.
11. kalau nomor 10 gagal juga. duduk manis di meja kerja dan nikmatilah
masa menunggu setiap proses kerja yang dilakukan. Alihkan waktu menunggu
dengan sesuatu yang lebih bermanfaat, seperti chating dengan gadget, online
di sosmed, atau belajar.... (prioritasnya mengikuti kebanyakan manusia loh
ya, jadi jangan protes). Tapi kayanya lebih bagus kalau prioritasnya ndak
begitu.
12. sudah capek ngetiknya dan kayanya ndak ada pokok benang merahnya
wakakkakaka....
ups... VBA nya dimana ya ? jangan-jangan terselip n jatuh entah dimana...
Barang Siapa yang menemukannya, mohon dipasang di nomor 1 sampai 9 sebagai
pelengkap solusi setiap nomornya.
;)
jangan diambil ati ya...
tulisan di atas itu cuman iseng manasin jari malem-malem di temperatur 9
derajat :]
Wassalam,
Kid
On Fri, Mar 20, 2015 at 9:35 PM, Zulfikar N [email protected]
[belajar-excel] <[email protected]> wrote:
>
>
> Dear Excel Master,
> Mohon bantuannya, apakah ada fungsi lain untuk menggatikan fungsi INDEX
> pada formula berikut:
>
> {=IFERROR(INDEX(BOSNET!$AM:$AM,SMALL(IF((BOSNET!$A:$A='db
> Retur'!$E$2)*(BOSNET!$B:$B='db
> Retur'!$E$3),ROW(BOSNET!$AM:$AM)-ROW(BOSNET!$AM$2)+2),ROWS($A$2:$A2))),"")}
>
>
> Salam
> Zulfikar
> Palopo, Sulsel
>
>
>
