Terima kasih banyak Mr Kid dan juga Pak Hendrik,
Sangat gamblang sekali pemaparan Mr Kid , Yang saya salut dari
master-master semua.....bisa-bisanya menuangkan bahasa manusia ke bahasa
formula ,makananya apaaaaa gitu lho . Mbok sekali-kali diajakin makan
bareng biar ketularan pinternya....he..he.. Setau saya index match ya
fungsinya hampir sama kayak vlookup/hlookup gitu doang , ternyata bisa
juga buat nyari uniq record.
Terima kasih banyak Mr Kid
salam
mardi
On 15/11/2012 19:56, Mr. Kid wrote:
Pak Mardi,
Anggap saja :
1. di E1 ada teks berbunyi 'Header Data'
2. di kolom D mulai baris 2 sampai baris 9 ada data berisi angka
1,2,3,4,5,6,7,1
3. dicell D1 ada teks berbunyi 'Header Data'
Jika di E2 diberi formula :
=CountIF( E1 , D2 )
hasilnya adalah 0 karena D2 yang bernilai 1 tidak ditemukan di E1
Jika di E2 diberi formula :
=CountIF( E1 , D1 )
hasilnya adalah 1 karena D1 yang bernilai 'Header Data' bisa ditemukan
di cell E1.
Dari 2 formula ini, maka CountIF bisa berfungsi untuk alat cek apakah
suatu nilai ada dalam suatu range tertentu.
Sekarang, jika G2 dan G3 diblok lalu diberi formula :
=CountIF( E1 , D1:D2 ) lalu tekan CTRL SHIFT ENTER supaya jadi
array formula,
maka G2 akan bernilai 1 dan G3 bernilai 0.
G2 adalah hasil formula CountIF( E1 , D1:D2 ) ketika yang menjadi
kriteria adalah cell D1, sedangkan G3 adalah hasil formula CountIF( E1
, D1:D2 ) ketika yang menjadi kriteria adalah cell D2.
Jadi, dalam array formula CountIF( E1 , D1:D2 ) akan menghasilkan
sebuah larik data (array) berisi 2 item berbentuk 2 baris x 1 kolom.
Oh... ternyata sama bentuknya seperti bidang kriterianya, yaitu D1:D2
yang berupa 2 baris x 1 kolom.
masih ikut ?
kalau tertinggal, silakan baca ulang dan dipraktekkan supaya dongeng
di atas tidak sekedar menjadi suatu sastra Excel.
Jika sudah mulai tune in, mari dilanjutkan...
Pada cell K1, coba beri formula :
=Sum( D$1:D1 ) [perhatikan cara peletakan tanda $]
Bahasa manusia dari bagian D$1:D1 adalah
"Mulai dari cell D1 yang barisnya tetap (D$1) sampai (:) cell D1 yang
bebas seluruhnya"
Jadi, tanda $ akan mengunci item setelahnya agar tidak berubah-ubah.
Sekarang, coba copy formula K1 ke cell K2 dan lihat rujukannya yang
berubah menjadi D$1:D2
Sekarang, coba copy formula K1 ke cell L1 dan L2 dan lihat rujuannya
lagi. Pada L2, rujukannya berubah menjadi E$1:E2
Kalau begitu, sekarang ubah formula K1 menjadi :
=Sum( $D$1:$D1 )
Copy formula K1 ke cell M1 dan M2, lalu lihat rujukannya.
Setelah bosan dengan hal yang dasar itu, mari kita mulai ke babak
selanjutnya :
COUNTIF($E$1:E1;$D$2:$D$12)
Jika bagian tersebut (yang ada dalam formula di E2) di-copy ke E3, maka :
1. rujukannya akan menjadi apa ?
2. karena bagian kriteria berisi banyak cell, maka hasilnya berupa
array berapa baris x berapa kolom ?
kalau sudah dapat jawaban nomor 1 dan 2,
maka apa bahasa manusia dari formula di cell E3 adalah
"Hitung jumlah cacah keberadaan setiap item di D2 sampai D12 dalam
suatu range dari E1 yang barisnya tetap sampai E2 yang bebas."
Array hasil dari bagian ini, akan berisi 0 (tidak ditemukan) atau 1
(ditemukan sejumlah 1 item). Jadi setiap item kriteria dalam D2:D12
bisa menghasilkan 0 atau 1 (salah satu dari 0 atau 1), sehingga
membentuk larik berisi angka-angka 0 atau 1.
Larik (array) ini nanti yang akan menjadi lahan pencarian (lookup)
oleh fungsi Match yang berbunyi : (formula di cell E2)
MATCH(0;COUNTIF($E$1:E1;$D$2:$D$12);0)
Yang dicari adalah nilai 0 pertama yang ditemukan dalam suatu array
hasil array formula CountIF. Artinya adalah mencari posisi item
kriteria CountIF yang belum ditemukan (0) dalam area kolom E sampai
baris itu diproses.
Karena yang dicari selalu barang yang belum ada, maka hasilnya akan
berupa unique list ketika fungsi Index digunakan untuk mengambil item
data baru dari sumber yang belum pernah ada dalam kolom E sampai baris
tersebut diproses.
Moga-moga cukup jelas dalam mbuletnya dongeng tersebut. Anda bisa
menentukan akhir kisah dari dongeng di atas dengan imajinasi Anda
masing-masing.
Harapannya, akan semakin banyak yang bisa menentukan akhir kisah
setelah terbentuk suatu kisah trilogi yang apic penuh epic. :)
Wassalam,
Kid.
2012/11/15 Mardi <[email protected]
<mailto:[email protected]>>
Terima kasih pak hendrik,
Sudah daku aplikasikan ,OK . Tapi masih ada ganjalan yang ingin
saya tanyakan dari formula Pak Hendrik,
{=IFERROR(INDEX($D$2:$D$12;MATCH(0;COUNTIF($E$1:E1;$D$2:$D$12);0));"")}
1. Maksud dari 0 itu gimana ya ?
2. Kok merujuk E1 itu juga maksudnya gimana ya , apa merujuk ke 1
cell diatasnya atau gimana ? Daku coba ganti 2 cell di atasnya
hasilnya jadi 2x semua
Terima Kasih banyak
mardi
