Kalau penjelasan seperti ini dibilang penjelasan singkat, saya mau tahu
seperti apa yaaa penjelasan panjang lebarnya Mas Kid.
On 06/06/2014 9:28, 'Mr. Kid' [email protected] [belajar-excel] wrote:
hehehe... (kasus ini menggunakan data berorientasi horisontal loh ya)
bukan ndak bisa Pak, hanya mulai kurang efisien.
Biasanya CountIF akan sedikit lebih lambat dari Match kalau lebih dari
100. Tapi ini relatif terhadap mesin masing-masing kok.
Pada data yang berorientasi vertikal, maka besar kemungkinan bahwa
area yang akan di CountIF apalagi CountIFs akan sangat banyak.
Fungsi Count (termasuk yang pakai IF maupun IFs) akan menganalisa
seluruh cell yang dirujuk. Jika cell yang dirujuk ada 1000, maka
keseluruhannya (yang 1000 cell itu) akan diperiksa satu per satu untuk
menghasilkan output nilai hasil Count tersebut. Berarti, pasti minimal
ada 1000 proses pemeriksaan.
Ketika kebutuhannya sekedar ada data atau tidak dengan 1 kriteria,
berarti tidak butuh informasi ada berapa datanya. Hasil Count > 0
berarti ada datanya dan hasil Count = 0 berarti tidak ada datanya.
Cukup hanya itu saja yang dibutuhkan. Tidak peduli ada 1 atau 3 atau 5
data. Pada kebutuhan seperti ini, sebenarnya yang dibutuhkan, minimal
ketemu 1 saja maka proses sudah cukup informasi untuk memutuskan bahwa
ada datanya. Jika pemeriksaan ada datanya atau tidak diperiksa dengan
Count, maka akan ada proses sia-sia, yaitu setelah mendapatkan hasil
analisa bahwa menemukan 1 data dimaksud, masih saja memeriksa cells
lain sampai 1000 cell terperiksa.
Itu sebabnya, untuk data yang banyak (asumsikan saja ada lebih dari
100 data pada komputer yang bekerja berat), fungsi Count berpotensi
melakukan proses sia-sia yang cukup banyak. Maka pada kondisi ini
(data lebih dari 100 di komputer yang terbebani), kurang 'mesiniawi'
jika ada banyak proses sia-sia. Maka sebaiknya memilih menggunakan
fungsi Match.
Sampai disini, *kesimpulan sementara *adalah : (cek keberadaan data
berdasar 1 kriteria)
-> gunakan CountIF atau CountIFs secara bijak jika enggan menggunakan
Match
-> Match lebih efisien dibanding CountIF atau CountIFs
Pada kasus-kasus yang berupa pengecekan keberadaan data berdasar lebih
dari 1 kriteria (2,3,dst), akan sedikit berubah konstelasinya.
Kriteria yang lebih dari 1 akan membutuhkan pemeriksaan secara
menyeluruh berdasar kriteria-kriteria yang sesuai.
-> Fungsi CountIF jelas tidak bisa dipakai, kecuali jika akan
menggunakan kolom bantu yang menggabungkan semua kriteria dalam sebuah
cell, sehingga kriterianya menjadi 1 saja, yaitu komposit dari seluruh
kriteria yang dibutuhkan.
-> Fungsi CountIFs bisa digunakan dan akan bekerja dengan cara yang
sama, yaitu memeriksa seluruh cell rujukan. Jika ada 1000 cell, maka
ke-1000 cells itu akan diperiksa. Jika ada 2 kriteria, maka setiap
cell dilakukan 2 proses. Jadi total 2000 proses. Kecepatannya tidak
akan lebih lambat 2 kali lipat dari penggunaan terhadap 1 kriteria,
melainkan hanya lebih lambat sedikit saja. Artinya jika ada n
kriteria, bukan berarti akan ekponensial n melambatnya.
-> Fungsi Match bisa digunakan selama seluruh kriteria disusun
membentuk sebuah array. Artinya, jika ada 1000 cells data berdasar 2
kriteria, maka Match akan membentuk array 1 dimensi berdasar 2
kriteria itu. Jadi dalam hal ini, akan ada 3000 proses untuk membentuk
arraynya saja, kemudian ditambah sekian proses untuk proses pencariannya.
Contoh : data kriteria 1 di C1:C1000 harus sama dengan 1 dan kriteria
2 di G1:G1000 harus bernilai > 1500
>> dengan Match
Match( 1 , (c1:c1000=1) * (g1:g1000>1500) , 0 )
proses 1 : (c1:c1000=1) -> ada 1000 cells = 1000 proses
proses 2 : (g1:g1000>1500) -> ada 1000 cells = 1000 proses
proses 3 : * -> mengalikan hasil proses 1 dengan proses 2 -> ada 1000
cells = 1000 proses
total 3000 proses
setelah didapat array hasil proses 3, barulah melakukan proses
pencarian nilai 1 (lookup_value). Andai ditemukan pada item array ke
350 (karena item ke-1 sampai item ke-349 di proses 3 menghasilkan
nilai 0 dan item ke-350 hasil proses 3 yang bernilai 1), maka akan ada
tambahan 350 proses.
Total 3000 + 350 proses = 3350 proses.
>> Pada menggunaan CountIFs
CountIFs( c1:c1000,1,g1:g1000,">1500")>0
prosesnya ada sebanyak :
proses 1 : c1:c1000 bernilai 1 -> ada 1000 cells = 1000 proses :
asumsikan ada 800 cells kolom C itu yang bernilai 1
proses 2 : g1:g1000 > 1500 hanya pada yang kolom c nya adalah 1 -> ada
800 cells = 800 proses : asumsikan ada 470 cells kolom G yang bernilai
> 1500 maka hasil CountIF (berupa sebuah nilai saja) bernilai 470
proses 3 : memeriksa hasil proses 2 (yang cuma sebuah nilai, yaitu
nilai 470 hasil proses 2) apakah lebih dari 0 (ada datanya) -> 1 nilai
= 1 proses
Total : 1000 + 800 + 1 = 1801 proses
Tentu selisih antara 3350 proses milik match terhadap 1801 proses
milik CountIFs cukup banyak, yaitu 1549 proses. Daripada proses itu
digunakan untuk hal yang ndak guna, mending digunakan untuk kegiatan
lain alias mengkalkulasi cell lain.
Jadi, sampai disini, bisa disimpulkan bahwa :
1. Fungsi CountIF, CountIFs, atau Match untuk memeriksa keberadaan
data, sebaiknya dipilih dengan bijak.
2. Untuk keberadaan data yang berdasar 1 kriteria saja, akan efisien
dengan Match
3. Jika keberadaan data berdasar kriteria akan menggunakan CountIF,
sebaiknya digunakan untuk jumlah record yang tidak banyak relatif
menurut kemampuan mesin setempat agar lebih 'mesiniawi'
4. Pada keberadaan data yang berdasar lebih dari 1 kriteria, CountIFs
lebih efisien daripada Match yang membutuhkan proses pembentukan array
1 dimensi.
Sekarang, mari kita bayangkan jika ada 50000 data saja.
Yang lebih parahnya lagi, penggunaan rujukan ke seluruh kolom seperti
$c:$c atau $g:$g
Jika di xl2003 yang maksimum baris Excel ada 65000 sekian, penggunaan
CountIF akan bertambah 1 proses untuk mencari tahu dimana baris
terakhir sheet yang ada datanya (baris di last cell dalam sheet).
Jika dengan Match dan untuk 1 kriteria, maka tergantung ketemunya
dimana (alias tetap efisien). Jika pada kriteria yang lebih dari 1,
maka penggunaan Match yang ditulis :
Match( 1 , ($c:$c=1)*($g:$g) , 0 )
akan bekerja mengolah data sebanyak 65000 sekian baris alias sama saja
datanya ada 65000 sekian, padahal yang terisi data hanya 1000 baris saja.
Bagaimana jika Excel yang digunakan adalah xl2007 ke atas yang ada 1M
lebih baris (1 juta lebih loh). Hasilnya, manusianya mengeluh tidak
pada tempatnya karena berujar : "Excel itu lemot sekale.... " dan
dikabarkan ke sluruh penjuru dunia tentang kelemotan Excel. Padahal
kan yang lemot manusianya kan ya... karena ndak mau belajar untuk
memahami Excel.
Jadi, rujukan ke seluruh kolom sebaiknya dihindari. Caranya bagaimana ?
Antara lain dengan salah satu opsi dari beberapa opsi berikut :
1. Manfaatkan fitur Excel Table (xl2007 ke atas) atau List (xl2003)
agar area data menjadi dinamis dan mudah dirujuk secara tepat dalam
kedinamisannya
2. Buat nama range yang merujuk ke suatu area secara dinamis. Tentu
saja dengan formula seperti Index dan kawan-kawan. Nah opsi ini
membutuhkan kemauan untuk belajar. Hati-hati loh... kegiatan belajar
itu sangatlah berat bagi manusia yang pengen maju dengan cara instan.
Artinya, kemajuan yang didapat instan adalah kemajuan semu... kemajuan
bawang kotong alias tong kosong sajah...
3. Ubah sumber data jika belum berkaidah tabel database menjadi tabel
berkaidah tabel database, dan susun ulang proses untuk menghasilkan
output agar bisa memanfaatkan fitur seperti Pivot Table (baik Excel
Pivot Table atau Power Pivot [xl2010 keatas])
Kira-kira demikian uraian singkatnya...
;)
Wassalam,
Kid.
2014-06-06 8:20 GMT+07:00 joko welas [email protected]
<mailto:[email protected]> [belajar-excel]
<[email protected] <mailto:[email protected]>>:
pak kid
maksih .. kalo boleh tau kenapa ya mr.kid kalo lebih dari 100 kok
gak bisa
dengan fungsi countif....
salam
joko
2014-06-05 7:55 GMT+07:00 'Mr. Kid' [email protected]
<mailto:[email protected]> [belajar-excel]
<[email protected]
<mailto:[email protected]>>:
Untuk jumlah cell $D2:$K2 yang tidak lebih dari 100
bisa dengan fungsi CountIF sebagai alat cek keberadaan suatu
nilai, seperti :
=( CountIF( $D2:$K2 , M$1 ) >0 ) *$L2
Wassalam,
Kid.
2014-06-04 10:29 GMT+07:00 'Bagus' [email protected]
<mailto:[email protected]> [belajar-excel]
<[email protected]
<mailto:[email protected]>>:
Hai Joko;
Coba begini:
=ISNUMBER(MATCH(M$1,$D2:$K2,0))*$L2
Copas kekanan dan kebawah
Wassalam
~ Bagus ~
----- Original Message -----
*From:* joko welas [email protected] [belajar-excel]
<mailto:[email protected]+[belajar-excel]>
*To:* [email protected]
<mailto:[email protected]>
*Sent:* Wednesday, June 04, 2014 8:47 AM
*Subject:* [belajar-excel] memberi uang hadir otomatis
[1 Attachment]
asalamu alaikum wr. wb
salam be excel
begini suhu excel
saya punya data rekap hadir. setiap peserta yang hadir
dapat uang hadir.
pertanyaanya. bagaimana memberikan uang hadir tersebut
sesuai nama jang hadir saja.
untuk jelasnya file terlampir.
salam
joko
