Pak Panji,
Asumsi di sheet Rekap :
1. ada kode di kolom A yang diisi oleh user (bukan hasil formula)
2. ada uraian di kolom D yang diisi oleh user (bukan hasil formula) -> jika
hasil formula, maka pasti akan ambil yang pertama ditemukan berdasar nilai
kode
Formula pada sheet rekap digunakan untuk mengambil data Vol dan Sat yang
bersesuaian saja dari sheet Data, berdasar Kode (kolom A) dan Uraian (kolom
D).
1. Formula untuk kolom Sat di F11 :
=LOOKUP(1,1/*(**(Data!$A$9:$A$92=$A11)*****(Data!$D$9:$D$92=$D11)**)*,*
Data!E$9:E$92*)
kemudian copy formula ke kolom Sat lainnya.
*Biru *: kriteria berdasar kode (kolom A)
*Hijau *: kriteria berdasar uraian (kolom D)
* : setara logika AND karena seluruh kriteria harus terpenuhi
Formula akan selalu mengambil yang terakhir ditemui yang sesuai
kriteria.
Jika bagian ****(Data!$D$9:$D$92=$D11) *dihilangkan, maka menjadi
lookup berdasar 1 kriteria. Formula vLookUp untuk mendapatkan uraian juga
kegiatan lookup berdasar 1 kriteria. Perbedaannya, pada formula di atas
yang dihilangkan bagian ****(Data!$D$9:$D$92=$D11)* mengambil yang ditemui
terakhir, sedang vLookUp dengan match type 0 akan mengambil yang pertama
ditemui.
2. Formula untuk kolom Vol di E11 :
yang awalnya berdasar 1 kriteria, yaitu berdasar kolom A saja, yang
berbunyi :
=SUMPRODUCT(*(Data!$A$9:$A$92=$A11)*1*,Data!F$9:F$92)
tampak bahwa kriteria-kriteria yang lain (selain kolom A) dalam formula
tersebut dianggap cocok (*1*) tanpa perlu diperiksa lagi.
Ketika ada kriteria yang butuh diperiksa dulu, maka susunan *1* diubah
menjadi bentuk perbandingan kriteria yang harus diperiksa.
Kriteria selain kolom A yang akan diperiksa adalah berdasar kolom D yang
bunyi perbandingannya adalah :
*(Data!$D$9:$D$92=$D11)*
Maka *1* diubah menjadi *(Data!$D$9:$D$92=$D11)* dan formula akan menjadi
berbunyi :
=SUMPRODUCT(*(Data!$A$9:$A$92=$A11)***(Data!$D$9:$D$92=$D11)*,Data!F$9:F$92)
Jika kuatir lupa dihari-hari depan bahwa bentuk seperti itu masih bisa
dikomputasi berdasar sekiun (saking buanyaknya) kriteria yang harus
diperiksa, maka tambahkan lagi sebagai bunyi 'kriteria lain dianggap cocok
(*1*) tanpa perlu diperiksa', sehingga kelak akan ingat lagi dengan thread
ini.
=SUMPRODUCT(*(Data!$A$9:$A$92=$A11)***(Data!$D$9:$D$92=$D11)***1*
,Data!F$9:F$92)
Menggunakan formula Index Match juga bisa. Nah untuk pustaka Index Match
ini, kita tunggu BeExceller lain yang punya waktu luang untuk mencet-mencet
keyboard susun kalimat dongengnya....
Wassalam,
Kid.
2013/2/4 Panji Hakiki Jamin <[email protected]>
> **
>
>
> Terima kasih banyak bantuannya Mr. Kid.
> Ada sedikit pertanyaan lagi tentang fungsi tersebut :
> >> Bagaimana rumus yg akan digunakan untuk fungsi penjumlahan apabila kita
> salah menuliskan kode di sheet Data sehingga penjumlahan tidak hanya dengan
> kode di kolom A tetapi cross check dengan uraian pekerjaan di sheet Data.
>
> ex : kolom A9 di sheet Data dengan uraian di kolom D9 adalah A001 dengan
> vol F9, apabila kita memasukkan dengan kode yang sama di kolom A21 di sheet
> Data dengan uraian di kolom D21 dan vol F21, bagaimana menuliskan rumus
> fungsi penjumlahan untuk cross check tidak hanya dengan kode di kolom A
> tetapi juga dengan uraian di kolom D, sehingga penjumlahan tersebut
> benar-benar sesuai dengan kode dan uraian pekerjaan
>
> Terima kasih,
> Panji Hakiki
>
>
> ------------------------------
> *From:* Mr. Kid <[email protected]>
> *To:* [email protected]
> *Sent:* Sunday, February 3, 2013 9:45 PM
>
> *Subject:* Re: [belajar-excel] Ask : Klarifikasi dengan Fungsi IF, SUMIF
>
>
> Sekedar pustaka fungsi :
> 1. Uraian pekerjaan (D11) ambil data dari sheet sebelah berdasar kode di
> A11 :
> =VLOOKUP($A11,Data!$A$9:D$92,4,0)
> Bahasa manusianya :
> "ambil kolom ke-4 dari A9:D9 di sheet Data untuk nilai kode di sheet data
> kolom pertama dari A9:D92 [artinya kolom A-nya] bernilai persis sama (,0)
> dengan kode di A11 sheet rekap."
>
> 2. Untuk kolom Sat di F11 ambil data di kolom E sheet Data berdasar nilai
> A11 yang harus sama dengan nilai di kolom A sheet Data
> =VLOOKUP($A11,Data!$A$9:F$92,COLUMN()-1,0)
> >> karena formula ditulis di kolom F (cell F11), maka hasil dari fungsi
> Column() adalah 6 [kolom F adalah kolom ke-6 Excel]
> >> karena lokasi pengambilan data di sheet Data adalah pada kolom E, maka
> hasil Column() dikurangi 1
> >> karena struktur kolom Sat pada tabel di sheet data adalah sama dengan
> struktur kolom Sat di sheet rekap, maka rujukan referensi ke sheet Data
> dibuat tidak absolut kolom akhir [Data!$A$9:F$92] (perhatikan tidak adanya
> karakter $ pada sisi kolom F setelah karakter ':' yang berarti 'sampai
> dengan' )
> >> formula bisa di-copy ke kolom Sat lainnya di sheet Rekap
>
> 3. Untuk kolom Vol di E11 ambil data di kolom F sheet Data selama
> recordnya memiliki kode (kolom A) yang sama dengan kode di A11 sheet rekap.
> =SUMPRODUCT( (Data!$A$9:$A$92=$A11)*1 , Data!F$9:F$92 )
> >> Kriteria : kolom A sheet Data sama dengan nilai A11 di sheet rekap [
> (Data!$A$9:$A$92=$A11) ]
> >> hasil bunyi kriteria tersebut adalah nilai TRUE/FALSE yang bertipe
> Boolean
> >> agar menjadi numerik, maka dikonversi dengan mengalikannya dengan angka
> 1, sehingga TRUE akan menjadi 1 dan FALSE akan menjadi 0.
> >> deretan hasil kriteria akan dikalikan dengan nilai Vol yang akan
> diambil [ Data!F$9:F$92 ]
> >> karena dikuatirkan adanya data bertipe Text pada area Data!F$9:F$92 ,
> maka Data!F$9:F$92 diletakkan sebagai array data ke-2 dalam SumProduct.
> >> Jika pasti Data!F$9:F$92 selalu bertipe numerik, maka karakter bagian
> teks [*1 ,] dalam formula SumProduct bisa diganti dengan operator perkalian
> '*' yang setara dengan logika AND menjadi :
> =SUMPRODUCT( (Data!$A$9:$A$92=$A11) * Data!F$9:F$92 )
>
> Wassalam,
> Kid.
>
>
> 2013/2/1 Panji Hakiki Jamin <[email protected]>
>
> **
>
> Selamat malam semua angota Milis,
>
> Saya kebingungan untuk hasil rekapitulasi dari beberapa data yang sama dan
> menuliskan data tersebut serta menjumlahkan data ke sheet yang berbeda.
> Mohon bantuannya untuk menyelesaikan permasalahan tersebut..Data terlampir
>
> Terima kasih
> Panji
>
>
>
>
>
>
