Begini saja : 1. copas formula ini ke cell F11 =LOOKUP(1,1/*(**(Data!$A$9:$A$92=$A11)*****(Data!$D$9:$D$92=$D11)**)*,* Data!E$9:E$92*)
2. copas formula ini ke cell E11 =SUMPRODUCT(*(Data!$A$9:$A$92=$A11)***(Data!$D$9:$D$92=$D11)*,Data!F$9:F$92) 3. Copy cell E11 dan F11 dan paste ke kolom-kolom Vol dan Sat lainnya di sheet rekap 4. Copy E11 sampai kolom terkanan data di baris 11 itu, lalu blok semua data yang butuh formula di kolom E misal dari E12 sampai E39, lalu paste 5. baru setelah itu baca-baca dongeng di email tadi ketika akan beranjak tidur, supaya mimpi indah (kalau tidak tersesat) Wassalam, Kid. 2013/2/4 Panji Hakiki Jamin <[email protected]> > ** > > > Masih bingung Mr. Kid..saya coba telaah pelan-pelan solusi dari Mr. > Kid..anyway terima kasih banyak > > wassalam > Panji > > > ------------------------------ > *From:* Mr. Kid <[email protected]> > *To:* [email protected] > *Sent:* Monday, February 4, 2013 8:55 AM > > *Subject:* Re: [belajar-excel] Ask : Klarifikasi dengan Fungsi IF, SUMIF > > > 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 > > > > > > > > >
