lookup data dengan sebagian kata kunci

Pertanyaan:"Hai oom abimono, saya di kantor sering banget tuh harus lookup data dari tabel lain yang dibuat oleh orang lain. Masalahnya, tabel referensinya sering kacau. Misalkan harusnya nama itemnya adalah 'apel malang', yang sering dijadikan referensi adalah 'apel' saja. Bulan depannya boro-boro sama apel malang. Bisa jadi ditulisnya 'apel malanggg'. Nah, akhirnya yang dipakai keywordnya aja deh, yaitu 'apel'. Gimana dong caranya? Kayaknya sih kalau pakai vlookup kata kuncinya harus sama kan ya? Bingung nih. Tolong dibantu ya oom. Pecas ndahe." - Gadis penikmat "hujan meteor", Jakarta.

Jawab:Hai penikmat hujan meteor, langsung saja ya, lookup dengan kata kuci tentu saja bisa, tapi dengan syarat bahwa satu kata kunci hanya boleh merefer ke satu kata juga di file tujuan. Kalau tidak hasilnya tidak sesuai. Contohnya pada kasus di atas, kata-kata yang berwarna merah di kolom B adalah kata kuncinya. Yang dicari adalah harga di kolom C dengan kata kunci di kolom E. Rumus yang digunakan di F3 adalah:

=LOOKUP(9^9,SEARCH(E3,$B$3:$B$12),$C$3:$C$12), kemudian copy ke range F4 sampai F12.

Penjelasan:

1. ...SEARCH(E3,$B$3:$B$12)... berfungsi untuk mencari apakah kata kunci ada di salah satu dari array yang dicari, dalam hal ini B3:B12. Jika ada dia akan memberikan angka.

2. =LOOKUP(9^9, [hasil search], ...) akan menunjukkan letak cell yang memberikan angka terdekat dengan 9^9. Dalam hal ini, jika kata kunci yang dicari adalah unik, maka baris dengan kata kunci itu adalah satu-satunya yang memberi hasil angka akibat fungsi SEARCH, sisanya akan memberikan hasil #VALUE!.

3. angka pada langkah kedua ini akan menunjukkan satu posisi pada array tujuan. Dengan formula =LOOKUP( [posisi yang ditunjuk], [array tujuan] ), maka akan diambil salah satu anggota array tujuan.

SUMIF dengan sebagian kata kunci

Pertanyaan:"Hai oom Excelmaniawan dan tante Excelmaniawati.... Sayah baru mendarat di dunia Exceltainment nihhhh... Mau minta tulungggg nih.... Pliss Pliss tolong dibantuh yaaaahhh... Beginih ceritahnyaahhh. Sayah kan mau jumlahkan angka berdasarkan kriteria tertentu. Kalau yang ituh sudah tahu sayah rumusnyah.... pakai SUMIF kannn. Nah masalahnnya, antara kriteria yang di tabel awal dan tabel hasil tidak sama. Di tabel summary hanya kriterianya sajahhh... Misal nih, di tabel awal ada jeruk nipis, jeruk purut, jeruk bali, dan kulit jeruk, di tabel hasil hanya ada satu kriteria untuk ini: "Jeruk". Minggu kemaren saya sudah tanyah ke trainer Excel sayah yang datang ke kantor. Udah bayar mahal-mahal jawabannya enteng banget: Nggak bisahhh! Uhhhhh.... Saya kesel dehhhh... Ayuh dung XL-maniaaahhh... bantuh saya yaahhh... Pimp my Excel!" - Dian Sastroh "baruh menikahhh", Semarang.

Jawab:Hai Dian, langsung jawab ya. Ini cukup sering ditanyakan di milis XL-mania. Saya berikan contoh seperti gambar di atas. Di sisi kiri adalah data sumber, dan di kanan hasilnya. Grouping data itu ditentukan warnanya, di situ Dian bisa lihat bahwa ada bermacam-macam jeruk dan bermacam-macam coklat. Konsep dasarnya tetap menggunakan SUMIF, tapi yang dimodifikasi sedikit. Pada kriteria yang dicari, kita apit dengan tanda "*" sehingga SUMIF ini akan mencari data sumber yang mengandung kata kunci. Misalnya di F3, formulanya adalah:

=SUMIF($B$3:$B$12,"*"&E3&"*",$C$3:$C$12), kemudian copy ke range F4 sampai F16.

Penjelasan:

1. =SUMIF( [range], [criteria], [sum range] ) berfungsi untuk menjumlahkan data pada [sum range] jika data pada [range] memenuhi [criteria].

2. ..."*"&E3&"*"... artinya menyatakan bahwa formula pada F3 mencari semua yang memenuhi kriteria "*ayam*". Artinya semua kata di B3:B12 yang mengandung "ayam" akan dijumlahkan.

3. bug aneh di excel 2007 ituhhhh... 4.5. Kali ini saya akan membahas bug terkenal di Excel 2007 itu. Bagi yang belum

tahu, cobalah Anda lakukan perkalian seperti = 425 * 154.20 atau = 850 * 77.1. Jika hasilnya adalah 100,000, artinya Excel 2007 Anda belum di-patch atau belum kebagian service pack dan harus mendownload paket di sini.

6.

7. Untuk Anda yang sudah tahu, atau tidak mengalami bug dashyat ini... Artikel ini tidak menjadi "basi" untuk Anda, karena saya akan menjelaskan mengapa ini terjadi :) Saya coba jelaskan dengan bahasa yang sederhana.Hal pertama yang harus dimengerti adalah Excel menyimpan data dalam bentuk data biner. Artinya bilangan seperti 77.1 misalnya, akan disimpan oleh Excel sebagai:

0100 0000 0101 0011 0100 0110 0110 0110 0110 0110 0110 0110 0110 0110 0110 0110

yang artinya "7", "7", ".", dan "1". Setelah disimpan ke dalam data biner, di dalam Excel ada satu "alat" untuk menampilkan data tersebut ke monitor Anda. "Alat" inilah yang salah menampilkan data biner yang bernilai 65,535 sebagai 100,000. "Alat" ini juga salah mengubah data jika nilai suatu angka mendekati 65,535. Perhitungan = 77.1 * 850 menampilkan 100,000. Perhitungan = 77.1 * 850 + 2 menampilkan 65,537. Tapi perhitungan = 77.1 * 850 + 1 menampilkan 100,001.

Salah satu hal yang memperkuat bahwa hal ini hanya kesalahan tampilan adalah bahwa Anda masih mendapatkan grafik yang benar jika menggunakan angka 100,000 "gadungan" ini ke dalam chart. Begitu pula jika Anda memiliki macro "terbilang", maka fungsi buatan anda sendiri [UDF] itu akan menyebutkan kalimat terbilang yang benar, bukan "seratus ribu".

Ada yang mungkin bertanya-tanya, "Kalau benar bahwa itu hanya tampilan,

artinya jika kita mengetik 65,535 harusnya akan ditampilkan sebagai 100,000 dong? Kayak posting yang ini nihhhh...."

Hmmm.... harusnya Anda melihat lagi ke atas dan lihat kata "mendekati" saya bold. Jadi yang bermasalah adalah angka seperti 65,534.999999, bukan 65,535 sendiri. Mengapa itu dapat terjadi? Baca lagi! Kita perhatikan lagi angka biner dari 77.1:

0100 0000 0101 0011 0100 0110 0110 0110 0110 0110 0110 0110 0110 0110 0110 0110

Dapat Anda lihat bahwa 0110 adalah angka yang berulang. Apa yang sebenarnya terjadi? Bilangan biner untuk 77.1 memiliki desimal yang berulang. Seperti 1 / 3 yang bilangan desimalnya 0.3333333333... dan 1 / 6 yang bilangan desimalnya 0.16666666666... Jika Anda mengalikan kembali 1 / 3 dengan 3, Anda akan mendapat angka 1. Tapi jika Anda mengalikan kembali 0.3333333333 dengan 3, maka Anda mendapat 0.9999999999. Hal yang serupa juga terjadi dengan bilangan biner itu, hasil operasi matematika dengan bilangan tersebut tidak terlalu akurat dan ada selisih yang sangat kecil. Artinya, dari awal 77.1 memang disimpan sebagai bilangan biner yang nilainya tidak benar-benar tepat 77.1!

"Mengapa menggunakan data biner dan bukan desimal dalam menyimpan data?"

Ada banyak alasan untuk ini, tapi yang utama adalah karena standar floating point yang digunakan Excel adalah IEEE-754, suatu standar yang umum digunakan.

"Jadi, angka berapa saja yang salah ditampilkan?"

OK, jika saya bilang "mendekati", mungkin pertanyaannya seberapa dekat? Angka yang salah ditampilkan adalah antara 65534.99999999995 dan 65535.99999999995. Sebagai catatan, Anda tidak dapat memasukkan angka ini di Excel langsung karena Excel akan membulatkan menjadi 15 bilangan saja. Contohnya jika Anda menginput 65534.99999999997 maka Excel akan mengubahnya menjadi 65534.9999999999. Begitu pula jika Anda menginput 65534.99999999998 maka hasilnya juga 65534.9999999999.

"Mengapa masalah floating point ini hanya terjadi di Excel 2007 dan tidak di Excel 2003?"

Hmmm... Anda yakin begitu? Excel 2003 juga salah hitung lho :) mau bukti? Di Excel 2003 SP 2, coba Anda ketikkan =DEC2HEX(850*77.1,4), maka hasilnya adalah FFFE dan bukan FFFF. Tapi jika =DEC2HEX(TRUNC(850*77.1),4), maka hasilnya adalah FFFF. Bagaimana dengan =DEC2HEX(SQRT(850*77.1)^2,4) ?

Hasilnya juga FFFF!

Sementara sampai sini dulu. In a nut shell, "salah hitung" di Excel 2007 sebenarnya hanya kesalahan display / tampilan saja. "Alat" untuk melakukan tampilan itu kurang benar. Tapi metode perhitungannya sudah "aneh" dari dulu karena masalah floating point ini. Di postingan berikutnya saya akan tunjukkan bahwa semua versi excel bisa salah hitung! :D :D :D

Untuk Anda yang senang melihat-lihat "alat" apa yang saya bicarakan di atas, silahkan download file ini. Saya pribadi lebih senang penjelasan yang sederhana :)

menjabarkan data sesuai jumlah yang ditentukan

Pertanyaan:

"Hai semuaaaaaa... Saya lagi belajar Excel nih. Kemaren liat-liat arsip XL-mania. Keren banget yang filteran data unik pake rumusnya. Penasaran aja, kalo misalnya kebalikannya bisa ga? Jadi ada data, di kanannya ada jumlahnya. Dari dua kolom ini bisa muncul satu kolom lagi, isinya daftar dari data pertama tadi, tapi udah sesuai jumlah di kanannya. Kalo bingung coba deh liat attachmentnya. Thanx yaaaaa..... Kalo ga bisa ga papa kok... Tapi kalo bisa... Ntar adahadiahnya!" - Mulan Mei, Medan.

Jawab:Uhm... Kirim aja hadiahnya! Hehehe.... Di E3 tulis: =IF(E2="item",$B$3,OFFSET($B$2,--(COUNTIF($E2:E$3,E2)=ROUND(VLOOKUP(E2,$B$3:$C$9,2,FALSE),0))+MATCH(E2,$B$3:$B$9,0),,,)), kemudian copy ke range E4 dan E15.

Penjelasan:

1. =IF(E2="item",$B$3... berfungsi melihat apakah cell tersebut adalah cell teratas, jika ya maka dikeluarkan data paling atas, yaitu $B$3, jika tidak ke langkah ke-2.

2. ...(COUNTIF($E2:E$3,E2)... berfungsi untuk melihat sudah berapa kali data di atas cell yang bersangkutan muncul.

3. ...VLOOKUP(E2,$B$3:$C$9,2,FALSE)... berfungsi untuk melihat berapa kali data ini ingin ditampilkan sesuai tabel pertama.

4. ...ROUND(...,0)... berfungsi untuk berjaga-jaga seandainya nilai pada kolom C bukan integer. Fungsi ini dapat dihilangkan jika semua angka pada kolom C adalah integer (tidak mengandung desimal atau pecahan).

5. ...COUNTIF(...)=ROUND(...)... berfungsi untuk melihat berapa banyak data yang sudah teraktualisasi di kolom E. Jika sudah sesuai dengan yang diinginkan pada langkah 3, maka akan menghasilkan nilai TRUE, jika belum akan menghasilkan FALSE.

6. ...MATCH(E2,$B$3:$B$9,0)... berfungsi untuk mencari urutan ke berapakah data E pada kolom B.

7. ...--... mengubah logika TRUE menjadi 1 dan FALSE menjadi 0.8. Artinya jika langkah 5 sudah menghasilkan TRUE, maka dia akan menambahkan

angka yang dihasilkan pada langkah 6, sehingga data yang ditampilkan berikutnya bergeser 1 baris dari hasil langkah 6. Cara menggesernya adalah dengan fungsi pada langkah ke-9.

9. ...OFFSET($B$2,x,)... berfungsi untuk menampilkan data pada x baris setelah $B$2, dimana x sendiri merupakan operasi

File:Anda dapat mendownload materi tips ini di sini

mengubah tanggal format SAP ke format Excel

Pertanyaan:"Hai XL-mania! Saya baru ikut XL-mania nih. Ternyata Excel seru juga ya. Saya ada problem. Mungkin gampang buat kalian, tapi susah buat saya. Saya sering download data dari SAP, format tanggalnya adalah teks yang dipisah titik. Nah saya pengen ubah ke format Excel. Selama ini saya manual. Ada ga sih rumus gampangnya?" - Olga yang lagi "belajar excel", Surabaya.

Jawab:Hai Olga! Jawaban atas pertanyaan ini sebenarnya ada dua macam, tergantung setting angka di system Olga. Kalau setting Indonesia, cukup di C3 pakai formula: =DATEVALUE(SUBSTITUTE(B3;".";"/")) , kemudian dicopy ke range C4:C11. Tapi kalau setting English, di C3 pakai formula =DATE(RIGHT(B3,4),MID(B3,4,2),LEFT(B3,2)) , kemudian dicopy ke range C4:C11. [perhatikan, kedua formula ini menggunakan tanda pemisah yang berbeda, yaitu koma ( , ) untuk setting english dan titik koma ( ; ) untuk setting Indonesia] Setting ini dapat dilihat di control panel, regional and language options, regional option.

Penjelasan metode 1:

1. ...SUBSTITUTE(B3;".";"/")... berfungsi untuk mengubah titik ( . ) pada tanggal menjadi slash ( / ). Dengan demikian, tanggal dalam format teks seperti 12.08.2007 akan menjadi 12/08/2007. Tapi sampai tahap ini tanggal masih berformat teks.

2. =DATEVALUE(...) berfungsi untuk mengubah tanggal hasil langkah pertama dari format teks menjadi format tanggal.

Penjelasan metode 2:

1. ...RIGHT(B3,4)... berfungsi untuk mengambil 4 karakter paling kanan dari cell B3. Artinya dari 12.08.2007 akan terambil "2007".

2. ...MID(B3,4,2)... berfungsi untuk mengambil karakter dari cell B3, mulai karakter ke 4, sebanyak 2 karakter. Artinya dari 12.08.2007 akan terambil "08".

3. ...LEFT(B3,2)... berfungsi untuk mengambil 2 karakter paling kiri dari B3. Artinya dari 12.08.2007 akan terambil "12".

4. =DATE(...) berfungsi untuk mengeluarkan data berupa tanggal sesuai tahun, bulan, dan tanggal yang dimasukkan secara berurutan.

File:Anda dapat mendownload materi tips ini di sini

fungsi baru di excel 2007

Pertanyaan:"Hai XL-mania! Saya tertarik untuk install Excel 2007 nih. Penasaran aja, sebenernya banyak ga sih fungsi tambahan di Excel 2007? Kalo ada daftar fungsinya mau dongggg..." - Rismawati Indah, Surabaya.

Jawab:Memang benar ada fungsi tambahan di Microsoft Excel 2007, tapi kebanyakan adalah fungsi engineering. Fungsi seperti SUMIFS dan AVERAGEIFS sendiri tidak kompatibel dengan Excel versi lama. Fungsi seperti ini akan dibahas formula penggantinya di postingan selanjutnya. Berikut adalah daftarnya:

Cube: CUBEKPIMEMBER - Menunjukkan nama, properti, dan ukuran KPI [Key Performance Indicator] serta menampilkan nama dan properti KPICube: CUBEMEMBER - Menampilkan anggota dalam hirarki cube.Cube: CUBEMEMBERPROPERTY - Menampilkan nilai dari properti anggota cube.Cube: CUBERANKEDMEMBER - Menampilkan urutan atau rangking member dalam suatu set.Cube: CUBESET - Mendefinisikan sebuah set member.Cube: CUBESETCOUNT - Menampilkan banyak item dalam suatu set.Cube: CUBEVALUE - Menampilkan nilai agregat dalam suatu cube.Engineering: BESSELI - Menampilkan modified Bessel function In(x)Engineering: BESSELJ - Menampilkan the Bessel function Jn(x)Engineering: BESSELK - Menampilkan the modified Bessel function Kn(x)Engineering: BESSELY - Menampilkan the Bessel function Yn(x)Engineering: BIN2DEC - Mengubah angka biner menjadi desimal.Engineering: BIN2HEX - Mengubah angka biner menjadi heksadesimal.Engineering: BIN2OCT - Mengubah angka biner menjadi oktal.Engineering: COMPLEX - Mengubah bilangan real dan koefisien imajiner menjadi bilangan kompleks.Engineering: CONVERT - Mengubah angka dari suatu sistem ukuran menjadi sistem yang lain.Engineering: DEC2BIN - Mengubah angka desimal menjadi biner.Engineering: DEC2HEX - Mengubah angka desimal menjadi heksadesimal.Engineering: DEC2OCT - Mengubah angka desimal menjadi oktal.Engineering: DELTA - Menguji apakah kedua nilai sama.Engineering: ERF - Mengembalikan fungsi error.Engineering: ERFC - Mengembalikan fungsi error komplementer.Engineering: GESTEP - Menguji apakah sebuah angka lebih besar dari ambang batasannya.Engineering: HEX2BIN - Mengubah angka heksadesimal menjadi biner.Engineering: HEX2DEC - Mengubah angka heksadesimal menjadi desimal.Engineering: HEX2OCT - Mengubah angka heksadesimal menjadi oktal.Engineering: IMABS - Menghasilkan angka absolut dari sebuah bilangan kompleks.Engineering: IMAGINARY - Menampilkan koefisien imajiner dari bilangan kompleks.Engineering: IMARGUMENT - Mengembalikan argumen theta, sudut yang diekspresikan dalam radian.Engineering: IMCONJUGATE - Menghasilkan konjugasi kompleks dari bilangan kompleks.Engineering: IMCOS - Menghasilkan cosinus dari bilangan kompleks.Engineering: IMDIV - Menampilkan hasil bagi dari dua buah bilangan kompleks.Engineering: IMEXP - Menampilkan nilai eksponensial dari sebuah bilangan kompleks.Engineering: IMLN - Menghasilkan logaritma natural dari sebuah bilangan kompleks.Engineering: IMLOG10 - Menghasilkan logaritma 10 dari sebuah bilangan kompleks.Engineering: IMLOG2 - Menghasilkan logaritma 2 dari sebuah bilangan kompleks.Engineering: IMPOWER - Menghasilkan bilangan kompleks yang dijadikan pangkat dari integer.

Engineering: IMPRODUCT - Menampilkan hasil kali bilangan kompleks (dari 2 buah sampai 29 buah)Engineering: IMREAL - Menampilkan koefisien riil dari bilangan kompleks.Engineering: IMSIN - Menghasilkan sinus dari bilangan kompleks.Engineering: IMSQRT - Menghasilkan akar kuadrat dari bilangan kompleks.Engineering: IMSUB - Menampilkan selisih antara 2 bilangan kompleks.Engineering: IMSUM - Menampilkan jumlah antara 2 bilangan kompleks.Engineering: OCT2BIN - Mengubah angka oktal menjadi biner.Engineering: OCT2DEC - Mengubah angka oktal menjadi desimal.Engineering: OCT2HEX - Mengubah angka oktal menjadi heksadesimal.External: EUROCONVERT - Mengubah sebuah angka ke standard Euro.External: SQL.REQUEST - Melakukan koneksi dengan data eksternal dan melakukan query, menghasilkan sebuah array.Math and trigonometry: SUMIFS - Menjumlahkan sesuai beberapa kriteria (di Excel versi lebih lama dapat digantikan dengan memodifikasi penggunaan dari SUMPRODUCT)Statistical: AVERAGEIF - Menampilkan rata-rata jika memenuhi kriteria tertentu (di Excel versi lebih lama dapat digantikan dengan formula CSE kombinasi AVERAGE dan IF)Statistical: AVERAGEIFS - Menampilkan rata-rata jika memenuhi beberapa kriteria tertentu.Text: FINDB - Versi "double byte" dari FIND( )Text: LEFTB - Versi "double byte" dari LEFT( )Text: LENB - Versi "double byte" dari LEN( )Text: MIDB - Versi "double byte" dari MID( )Text: REPLACEB - Versi "double byte" dari REPLACE( )Text: RIGHTB - Versi "double byte" dari RIGHT( )Text: SEARCHB - Versi "double byte" dari SEARCH( )

File:Anda dapat mendownload materi ini di sini

lookup dengan banyak kriteria

Pertanyaan:"Tempat belajar excel paling OK ya XL-maniaaa... Gitu kata orang-orang! Langsung tanya nih boss, gimana ya caranya cari data berdasarkan banyak kriteria atau lookup berdasarkan banyak kriteria? Umumnya sih saya sering cari data berdasarkan dua kriteria, tapi sekarang saya perlu rumus untuk cari data berdasar 3 kriteria. Lagi pusing-pusing mikir, temen saya tanya, gimana caranya lookup berdasarkan 4 kriteria... Pusing... Pusing... Pusing... Contoh data terlampir." - Obama Mc Cain, Amerika.

Jawab:Pertanyaan: "lookup berdasar banyak kriteria" dan "mencari data berdasar banyak kriteria" adalah salah satu topik yang cukup populer di XL-mania. Pertanyaan ini sering muncul lebih dari 3kali per minggu sehingga terkadang perlu difilter. Anyway, mulai sekarang member XL-mania dapat menemukan artikelnya di sini :) hehehe... Untuk contoh data seperti terlampir, formula yang dapat digunakan di F14 adalah:

{=INDEX($F$3:$F$11,MATCH(B14&C14&D14&E14,$B$3:$B$11&$C$3:$C$11&$D$3:$D$11&$E$3:$E$11))}

kemudian dicopy ke range F15:F16. Formula ini adalah formula array sehingga untuk mengakhirinya harus menekan Ctrl+Shift+Enter.

Penjelasan:

1. Dasar formula ini sama dengan formula "vlookup yang bisa nengok ke kiri"2. =INDEX($F$3:$F$11,...) berfungsi untuk memberikan nilai pada baris yang

ditunjuk.3. Sedangkan ...MATCH(...,...))... berfungsi untuk mencari pada baris berapa data

yang sesuai4. Pada fungsi MATCH, data yang dicari adalah gabungan dari kolom B, C, D, dan

E, sehingga data yang dicari dituliskan sebagai ...B14&C14&D14&E14...5. Formula ini bukanlah satu-satunya cara, masih banyak cara lain di sini

File:Anda dapat mendownload materi tips ini di sini

membuat tabel data unik tanpa pivot table

Pertanyaan: "Halo guru Excel! Saya sedang belajar Excel nih, tolong bantuan rumus ajaibnya yah. Saya ingin membuat tabel seperti yang biasa dibuat dengan pivot table. Tujuannya sih untuk memfilter data unik, kemudian menentukan jumlahnya. Kalau untuk jumlahnya saya sudah tahu caranya. Kalau di data saya ini pakai =COUNTIF($B$3:$B$15,D3). Masalahnya bagaimana cara membuat item unik di kolom D itu dengan formula? Pasti ada kan caranya? Tujuan saya pakai formula agar tabel itu selalu update, tidak seperti pivot table yang harus selalu di-refresh." - Sarah Azharia, Jakarta.

Jawab:Hai Sarah, tanpa basa basi ya... formula ajaibnya bisa ditulis di D3 =INDEX($B$3:$B$15,MATCH(0,COUNTIF($D$2:D2,$B$3:$B$15),0)), akhiri dengan menekan tombol Ctrl+Shift+Enter sehingga keluar tanda {...} yang mengapit formula tersebut. Formulanya akan menjadi {=INDEX($B$3:$B$15,MATCH(0,COUNTIF($D$2:D2,$B$3:$B$15),0))}. Kemudian copy dari D3 ke range D4:D10.

Penjelasan:

1. {...} adalah tanda bahwa formula tersebut adalah formula array2. ...COUNTIF($D$2:D2,$B$3:$B$15)... adalah akan mencari apakah data di

atasnya sudah pernah muncul lebih dari sekali, jika belum pernah muncul akan memberi hasil 0.

3. Dari COUNTIF di atas, formula ...MATCH(0, ... ,0)... akan mencari, pada baris ke berapa akan muncul angka 0 pertama kali.

4. ...=INDEX(...,xxx) adalah fungsi untuk mencari data dari suatu array pada urutan ke xxx. Pada rangkaian ini, hasil dari MATCH di atas akan menjadi xxx yang dicari.

File:Anda dapat mendownload materi ini di sini dan mendapatkan simulasi yang lebih jelas dalam detail "penjelasan".

menjumlah berdasar banyak kriteria tanpa SUMIFS

Pertanyaan:"Halo Excel expert! Saya punya masalah nih. Saya ingin menjumlahkan berdasarkan banyak kriteria. Kasusnya seperti terlampir. Ceritanya saya ingin menjumlahkan berdasarkan syarat1 dan syarat2. Saya ingin yang syarat1-nya "bbb" dan syarat2-nya "222". Jadi yang akan dijumlahkan adalah D4, D6, dan D9. Kalau datanya sedikit sih gampang. Tapi ini datanya banyak buaaannnggeettt. Kata temen saya kalau pakai Excel 2007, tinggal tulis di G6: =SUMIFS(D3:D9,B3:B9,G3,C3:C9,G4). Tapi saya nggak punya Excel 2007. Ada ga cara lain biar ga usah pakai SUMIFS?" - Sandra Dewiana, Jakarta.

Jawab:Hai Sandra, sepertinya ini nyambung dengan postingan saya tentang fungsi baru di Excel 2007. Sebenarnya ada kok cara lain untuk menjumlah berdasarkan beberapa kriteria / banyak kriteria. Misalnya pada posisi data dan syarat seperti kasus Sandra, di G6 saya tinggal tulis =SUMPRODUCT((B3:B9=G3)*(C3:C9=G4)*D3:D9).

Penjelasan:

1. =SUMPRODUCT(... berfungsi untuk mencari hasil kali dua buah array....(B3:B9=G3)... memberikan hasil TRUE jika hasilnya sama dengan G3, FALSE jika tidak sama.

2. ...(C3:C9=G4)... memberikan hasil TRUE jika hasilnya sama dengan G4, FALSE jika tidak sama.

3. ...D3:D9... adalah nilai yang akan dijumlahkan

File:Anda dapat mendownload materi ini di sini dan mendapatkan simulasi yang lebih jelas dalam detail "penjelasan".