Bagaimana cara menampilkan data dengan memanggil tanggal

  1. tahun lalu

    Selamat malam,

    ada yang bisa bantu, untuk soal yang satu ini:

    saya mempunyai data (2 sheet), yang jika diinput tanggal, maka akan tampil otomatis
    data yang dipanggil tersebut sesuai bulannya. namun terdapat kesulitan pada pencarian jumlah.

    misalnya: bulan 4, type CL, pemakaian dibawah 0 ada 50, type BT, pemakaian diatas 0 ada 32
    biasa kan klo mencari banyaknya jumlah menggunakan Count ya mas, tp, saya bingung menggabungkan fungsi ini dsni..

    terima kasih untuk semua yang akan menjawab

  2. Caton

    27 Apr 2017 Terverifikasi Indonesia + 11.522 Poin

    Bagaimana kalau formulanya diubah menggunakan fungsi COUNTIFS, sehingga pada sel M15 formulanya menjadi:

    =COUNTIFS(DATA!$A$3:$A$31;"=LISTRIK";DATA!$B$3:$B$31;"="&$L15;OFFSET(DATA!$C$2;1;MATCH($L$7;DATA!$D$2:$AB$2;0)+1;29;1);"<0")

    dan pada sel N15 formulanya:

    =COUNTIFS(DATA!$A$3:$A$31;"=LISTRIK";DATA!$B$3:$B$31;"="&$L15;OFFSET(DATA!$C$2;1;MATCH($L$7;DATA!$D$2:$AB$2;0)+1;29;1);">0")

  3. Caton

    27 Apr 2017 Terverifikasi Indonesia + 11.522 Poin
    Di sunting tahun lalu oleh Caton

    Atau, bila menggunakan fungsi SUMPRODUCT, formula pada sel M15 menjadi:

    =SUMPRODUCT((DATA!$A$3:$A$31="LISTRIK")*(DATA!$B$3:$B$31=$L15)*(OFFSET(DATA!$C$2;1;MATCH($L$7;DATA!$D$2:$AB$2;0)+1;29;1)<0))

    dan pada sel N15 formulanya:

    =SUMPRODUCT((DATA!$A$3:$A$31="LISTRIK")*(DATA!$B$3:$B$31=$L15)*(OFFSET(DATA!$C$2;1;MATCH($L$7;DATA!$D$2:$AB$2;0)+1;29;1)>0))

  4. waahh bnr work mas.. tp yang saya bingung kok pakai SUMPRODUCT juga bisa ya? padahal saya juga sudah coba itu, tp, setelah saya cek salah di "*" pemberian bintang itu.. sedikit2 saya paham mas.. terima kasih banyak ya sudah membantu..

  5. Caton

    28 Apr 2017 Terverifikasi Indonesia + 11.522 Poin

    Terima kasih juga mas bro, karena saya jadi punya 'soal' buat latihan dan belajar... :)

  6. Mas klo untuk soal yang sperti ini bagaimana ya, kok saya error terus y coba ikut pakai SUMPRODUCT
    file terlampir mas

    terima kasih sebelumnya mas..

  7. Caton

    28 Apr 2017 Terverifikasi Indonesia + 11.522 Poin

    Klo saya evaluasi formulanya, masalahnya ada pada fungsi OFFSET yang mas @awraabadi masukkan:

    (OFFSET(DATA!$C$2;3;MATCH($D$1;DATA!$D$2:$BA$2;0)+1;2:8)<0)

    Lihat masalahnya pada MATCH($D$1;DATA!$D$2:$BA$2;0)+1;2:8)... Nilai (tanggal) yang dicari seharusnya merujuk ke sel $F$1, serta pengisian dimensi tinggi dan lebar OFFSET seharusnya dipisahkan oleh koma (atau titik koma). Mungkin yang mas @awraabadi maksud:

    (OFFSET(DATA!$C$2;2;MATCH($F$1;DATA!$2:$2;0)-1;7;1)<0)

    Saya juga belum banyak mengerti benar formula-formula di Excel, masih coba-coba. Contohnya untuk kasus di awal -- file CSR-(Detail), ternyata juga bisa menggunakan fungsi SUM dengan proses CSE (Array Formula). Detil formulanya sama dengan SUMPRODUCT, hanya saja musti tekan Control-Shift-Enter (CSE). Kalau tak salah, hal itu karena SUMPRODUCT sudah merupakan fungsi array.

    Untuk kasus blank cell, saya belum nemu solusinya. Mungkin rekan-rekan lain lebih paham caranya.

    Hasil coba-coba saya ada dilampiran berikut.

  8. Caton

    29 Apr 2017 Terverifikasi Indonesia + 11.522 Poin

    Satu hal lagi, untuk kasus blank cell, oleh karena kriteria pencarian data adalah "0 < nilai > 0" maka nilai 0 tetap terlewatkan. Dengan formula SUMPRODUCT yang digunakan pada contoh kasus mas bro, Excel tidak menjumlahkan nilai sebenarnya pada sel, namun menjumlahkan nilai logika (1 atau 0) yang dihasilkan formula. Misalnya, dengan formula:

    =SUMPRODUCT((DATA!$A$4:$A$10="LISTRIK")*(DATA!$B$4:$B$10=$A6)*(OFFSET(DATA!$C$2;2;MATCH($F$1;DATA!$2:$2;0)-1;7;1)<0))

    masing-masing akan menghasilkan:

    =SUMPRODUCT({TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}*{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}*{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE})
    
    =SUMPRODUCT({0;1;0;0;0;0;0})

    Sehingga dengan formula tersebut di atas, blank cell tidak menjadi masalah. Kecuali, kriterianya mengharuskan pencarian "nilai = 0", mungkin diakhir formula yang sudah ada, harus ditambahkan:

    ... *(OFFSET(DATA!$C$2;2;MATCH($F$1;DATA!$2:$2;0)-1;7;1)<>"")

    untuk memastikan tidak ada blank cell yang terjumlahkan. Namun ini hanya sebatas evaluasi dan pengetahuan saya... :)

  9. seep mas, terima kasih sudah mau share ilmunya. oke saya coba lagi deh..

  10. mas, memang ada hasilnya y.. tp hasilnya masih belum benar ya.. oke deh mas.. gpp coba saya cari2 lagi

  11. Caton

    29 Apr 2017 Terverifikasi Indonesia + 11.522 Poin

    Hehe... saya cuman betulin formula punya mas sendiri (sel B6). Saya pikir mas maunya pake formula tersebut. Soalnya saya juga bingung, pada contoh hasil yang diinginkan, apakah itu contoh untuk bulan Januari? Karena setelah saya telusuri, untuk bulan Januari 2017, sel Resume!E6 pada (pemakaian listrik kolom W di atas 0) tidak ada nilai yang memenuhi kriteria, namun pada contoh ada 1. Atau saya yang salah lihat (efek ngantuk :) ...) Oleh karena untuk sel Resume!D6:D10 hasilnya sesuai dengan contoh, saya pikir formulanya sudah sesuai... :)

  12. he... gpp mas.. mantaab jiwa mas... terima kasih sudah selalu respon..

  13. Caton

    30 Apr 2017 Terverifikasi Indonesia + 11.522 Poin

    Jadi, hasil yang benar gimana mas bro? Saya penasaran aja... Klo gak keberatan, bisa ditunjukin hasilnya secara manual pada sheet Resume (tanggal yang dirujuk dan data pada tabelnya). Bisa jadi materi belajar Excel buat saya mas bro. Trims sebelumnya... :)

  14. he.. saya juga bingung sebenernya mas, cara kasih gambaran contohnya.. yang pasti saya ingin mendapatkan jumlah dari data tersebut, bukan total.
    misalnya angka 0 (NOL) ada berapa?
    angka (1) satu ada berapa?
    seperti itu kira2 mas.. (semacam kaya fungsi "COUNT")

    he... maaf mas, jd merepotkan

    ini saya lampirkan file barunya mas..
    luar biasa mas ini... terima kasih banyak mas

  15. Caton

    1 Mei 2017 Terverifikasi Indonesia + 11.522 Poin

    Khan namanya juga masih belajar dan saling berdiskusi mas... Saya gak merasa direpotkan, sebaliknya saya senang saja karena ada yang bisa saya pelajari. Klo masih ada yang salah, itu sih biasa. Betul.. betul.. betul... :D Mungkin bagi yang sudah mahir, kasus-kasus seperti ini sih sudah biasa... Cukup beberapa menit, kelar sudah... :D

    Btw, terima kasih juga karena masih mau berbagi mas. Dari soal-soal yang mas diskusikan di sini, saya bisa belajar banyak, semisalnya dengan mempelajari solusi-solusi dari mas @diditsatriyadi dan mas @Fujiansyah92. Saya mau coba lagi 'memecahkan' kasus ini, sebisa mungkin sebelum kedua master di atas mengeluarkan jurusnya... ;) atau sebelum saya jadi pusing sendiri... hehe... Terima kasih juga sebelumnya.

  16. diditsatriyadi

    2 Mei 2017 Terverifikasi Surabaya + 1.103 Poin

    selamat pagi mas @awraabadi, iya saya habis liburan cuma bisa buka forum dari hape tapi gak bisa jawab soalnya hapenya jadul gak ada excelnya hehe.. ini baru bisa buka excel di komputer, kayaknya persoalannya pengembangan dari sebelumnya ya, untuk yang terakhir ini kita gunakan saja SUMPRODUCT
    kira2 kalo di sel B6 formulanya :

    =SUMPRODUCT(--(DATA!$A$4:$A$10="LISTRIK"),--(DATA!$B$4:$B$10=$A6),--(OFFSET(DATA!$D$3,1,4*MONTH($F$1),ROWS(DATA!$A$4:$A$10),1)<0))


    ada 3 yg di sumproduct, array pertama mengecek kata "listrik", array kedua mengecek type, array ketiga mengecek data yang kurang dari 0
    hasil dari tiap array sebenarnya kumpulan kata "true" dan "false" tapi karena ada tanda dobel min ("--") berubah jadi angka 1 dan 0, nah itu yg dikalikan sama sumproduct
    khusus utk array ketiga karena dia tergantung dari bulan input, maka digunakan formula OFFSET
    coba dicek dulu kalo ada salah nanti kita ralat lagi hehe.

  17. Caton

    2 Mei 2017 Terverifikasi Indonesia + 11.522 Poin

    Selamat siang mas @diditsatriyadi. Saya sudah coba file yang mas lampirkan dan saya bandingkan dengan file yang saya lampirkan di atas (CSR 002-2.xlsx), hasil kalkulasinya sama. Namun oleh mas @awraabadi dinyatakan belum benar hasilnya.

    @awraabadi mas, memang ada hasilnya y.. tp hasilnya masih belum benar ya.. oke deh mas.. gpp coba saya cari2 lagi

    Pada file lampiran mas @awraabadi yang terakhir, keterangannya:

    Ini adalah angka manual diambil dari sheet "DATA", PEMAKAIAN FEB. terlihat type "SK" yang mempunyai nilai dibawah NOL terdapat
    L = 1
    W = 0
    sedangkan diatas NOL terdapat
    L = 0
    W = 1

    Dan setelah saya uji kembali, hasilnya tetap sama. Apakah saya yang gagal memahami kasusnya, atau ada perbedaan khusus. Maklum mas, saya masih belajar :) jadi rada-rada lama mengevaluasi kasusnya... :D Saya masih penasaran, karena setelah saya evaluasi formula dari mas @diditsatriyadi ternyata hasilnya tidak berbeda dengan yang saya coba... Btw, ini bukan protes lho mas. Mohon pencerahan lebih lanjut dari mas @diditsatriyadi . Trims.

    Note: pada file yang saya lampirkan (CSR 002-2.xlsx) dengan file terakhir yang dilampirkan mas @awraabadi terdapat perbedaan pada kolom A (3 baris terakhir) dan pada header tabel data (sehingga terdapat sedikit perbedaan pada fungsi OFFSET). Berikut screen shoot dari hasil penyelesaian saya (CSR 002-2.xlsx) dan evaluasi manual pada file lampiran mas @awraabadi terakhir.

  18. Salam hormat untuk semua master2 disini.

    Mas @Caton ,setelah saya teliti kembali, ternyata formula yang mas @Caton buat "BENAR"
    saya yang salah karena kurang teliti he.., terima kasih mas, telah koreksi kembali
    untuk file dengan header terbaru, (yang bukan tanggal), iya itu saya buat agar tidak bikin rancuh antara kolom tanggal dan kolom total pemakaian..
    maksudnya yang baru ini saya buat agar mudah dibaca permasalahan soalnya mas.

    untuk mas @diditsatriyadi, selamat pagi juga mas, he... terima kasih mas, sudah ikut join
    mas, itu apa lagi ya? ada simbol ini "--" di formula..fungsinya untuk apa y?, saya masih belum ngerti mas

    oy mas @Caton, @diditsatriyadi dan mungkin master2 yg lain, ada solusi tidak, untuk nilai yang nol agar bisa tetap dihitung?
    sudah saya coba dengan "<=0" (lebih kecil sama dengan nol), namun kolom yang tidak diisi nilai tetap terhitung..

    solusi nya bagaimana ya mas..? mohon pencerahannya kembali mas..

    contoh file yg dimaksud saya lampirkan mas

    terima kasih

  19. diditsatriyadi

    2 Mei 2017 Terverifikasi Surabaya + 1.103 Poin

    nah udah ada penjelasannya, semoga mas @Caton gak protes lagi..
    minum kopi dulu mas @awraabadi kalo buka excelnya, dibaca dan dipelajari pelan2 biar teliti..
    termasuk penjelasan double minus ("--") sudah dijelaskan mas @Caton dan saya juga di atas..
    utk masalah membedakan 0 dan blank, kalo saya tambahkan 1 kriteria lagi di SUMPRODUCT, yaitu ISNUMBER.. cuma ditegaskan lagi mas, 0 itu ikut kelompok yang mana, ikut 'di bawah nol' atau 'di atas nol' ??
    kalo uplod saya ini asumsinya ikut di bawah nol, jadi kriterianya "<=0" dan yang di atas nol kriterianya ">0"

  20. Caton

    2 Mei 2017 Terverifikasi Indonesia + 11.522 Poin

    Olala... mas @awraabadi, mas membuat saya bingung saja, sampai-sampai musti konfirmasi ke mas @diditsatriyadi :D (bukan protes mas @diditsatriyadi, konfirmasi saja... ;) )

    Untuk kasus pada file CSR (Case Lebih kecil sama dengan nol).xlsx, saya ikut mas @diditsatriyadi, yakni dengan menambahkan 1 kalkulasi tambahan terhadap kriteria 0 dan blank cell, dalam hal ini, saya mencoba menggunakan kriteria <>"" sebagaimana yang saya jelaskan di atas sebelumnya. Jadi mas @awraabadi bisa menggunakan fungsi ISNUMBER atau evaluasi <>"" terhadap nilai setiap baris (sudah saya coba menggunakan fungsi SUMPRODUCT, COUNTIFS dan SUM). Namun saya lebih menganjurkan menggunakan fungsi ISNUMBER dikarenakan lebih presisi dalam menguji tipe nilai setiap baris (sel kosong, spasi dan teks bisa disaring). Dan juga, sebagaimana yang dijelaskan mas @diditsatriyadi, perhitungan terhadap nilai 0 mau masuk blok di bawah atau di atas nol. Atau masuk di kedua-duanya? :D

    Terima kasih buat mas @awraabadi dan mas @diditsatriyadi. Saya mau ngopi dulu sambil nyimak diskusi selanjutnya.

  21. Newer ›
 

atau Mendaftar untuk ikut berdiskusi!