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 >> >> > > >
