Bagaimana cara menampilkan data dengan tanggal?

  1. 2 tahun lalu

    Adakah yang bisa menolong saya tentang soal yang sungguh belum saya ketahui cara/solusinya dalam menyelesaikannya
    untuk para master, saya mohon kucuran ilmunya. File soal saya lampirkan..

    Apakah solusinya dengan VBA atau apa ya??,
    mohon pencerahannya :)

    thq Master

    kan judul forum ini belajar excel, saya sendiri juga belajar
    ini saya lampirkan formula versi saya
    sedikit penjelasan saja ya, sisanya di search google aja tentang guna tiap formula SUMPRODUCT, OFFSET, MATCH
    utk di sel B3 digunakan formula

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


    beda dengan formula sebelumnya kali ini tidak usah pake tombol ajaib ctrl+shift+enter karena sumproduct memang khusus untuk array
    fungsi di atas dipecah jadi 2 bagian yaitu:
    (DATA!$A$3:$A$31="LISTRIK")*(DATA!$B$3:$B$31=$A3)*(DATA!$C$3:$C$31=B$2)
    dan
    OFFSET(DATA!$C$2,1,MATCH($L$7,DATA!$D$2:$AB$2,0)+1,29,1)
    bagian pertama itu isinya adalah kumpulan data (array) logik 0 dan 1 untuk urutan baris yg sesuai dengan kata "listrik", isi sel A3 dan isi sel B2
    bagian kedua berisi array data sesuai dengan kolom yang match dengan tanggal input
    maka SUMPRODUCT menggabungkan perkalian kedua array tersebut.
    kira-kira demikian penjelasannya untuk dipelajari :D

  2. diditsatriyadi

    20 Apr 2017 Terverifikasi Surabaya + 1.103 Poin

    mungkin begini, sementara belum sempat ngasih penjelasannya dipelajari dulu saja ya :D

  3. ohh masih bisa gan pakai formula excel, tanpa scrip VB ya.. he.. tp jujur saya awam bgt dgn formula tersebut. tp, terima kasih bgt mas guru.. sudah sangat membantu sekali. klo misalkan mas guru senggang tolong saya diajarkan fungsi dari formula tersebut ya.. hee.. atau klo ada yg lebih simple dari itu, saya sangat senang sekali..

    sekali lagi terima kasih mas guru ilmunya

  4. mas guru, setelah saya cek lagi.. rupanya masih kurang deh..
    jadi begini mas, saya butuh angka di sheet data yaitu kolom pemakaian
    kolom pemakaian didpt dari

    Pemakaian = Tanggal terbaru - Tanggal yang lama

    misalnya = Feb - Jan = . . . .

    jadi Total pemakaiannya mas..

    terima kasih

  5. diditsatriyadi

    20 Apr 2017 Terverifikasi Surabaya + 1.103 Poin

    ooh begitu, tapi karena di sheet 'data' sudah dibuatkan kolom pemakaian, yaitu tepat di sebelah kanan kolom tanggal yang ditinjau, berarti sederhana saja, formula sebelumnya ditambah +1 sebagai berikut :

    =IFERROR(OFFSET(DATA!$C$2,MATCH(1,(DATA!$A$3:$A$31="LISTRIK")*(DATA!$B$3:$B$31=$A3)*(DATA!$C$3:$C$31=B$2),0),MATCH($L$7,DATA!$D$2:$AB$2,0)),0)


    menjadi

    =IFERROR(OFFSET(DATA!$C$2,MATCH(1,(DATA!$A$3:$A$31="LISTRIK")*(DATA!$B$3:$B$31=$A3)*(DATA!$C$3:$C$31=B$2),0),MATCH($L$7,DATA!$D$2:$AB$2,0)+1),0)

    untuk penjelasannya mari kita gunakan acuan mencari data untuk sel B3 :
    1. kita mencari baris pada sheet 'data' yang sesuai dengan 3 kriteria : kategori, type dan lantai.
    digunakan fungsi mencari baris dengan formula MATCH sebagai berikut

    MATCH(1,(DATA!$A$3:$A$31="LISTRIK")*(DATA!$B$3:$B$31=$A3)*(DATA!$C$3:$C$31=B$2),0)


    formula di atas artinya mencari kata "LISTRIK" dari range A3:A31, kata "KS" (dari isi sel A3) pada range B3:B31 dan kata "LD1" (dari isi sel B2) pada range C3:C31
    hasil formula ini menampilkan urut baris yang sesuai, pada contoh ini hasilnya : 3 yang artinya data ke 3 pada tabel

    2. kita mencari kolom pada sheet 'data' yang sesuai dengan tanggal input (isi sel L7) demgam formula MATCH

    MATCH($L$7,DATA!$D$2:$AB$2,0)


    artinya carilah isi L7 di range D2:AB2, pada contoh ini bila input '3/20/2017' hasilnya adalah 6 artinya kolom ke 6 dari D2:AB2
    revisi: karena permintaannya nilai pemakaian yaitu selisih dengan tanggal sebelumnya dan di sheet 'data' sudah anda buatkan kolomnya yaitu tepat di sebelah kanan <kolom> maka formula ini +1 menjadi

    MATCH($L$7,DATA!$D$2:$AB$2,0)+1


    3. dari data baris pada langkah 1 dan kolom pada langkah 2 di atas diambil nilainya, digunakan formula OFFSET formulanya, syntaxnya :

    OFFSET(DATA!C2,<baris>,<kolom>)


    artinya dengan koordinat acuan DATA!C2 bergerak ke bawah sejauh <baris> sel, lalu ke kanan sejauh <kolom> sel.
    jadi formulanya :

    OFFSET(DATA!$C$2,MATCH(1,(DATA!$A$3:$A$31="LISTRIK")*(DATA!$B$3:$B$31=$A3)*(DATA!$C$3:$C$31=B$2),0),MATCH($L$7,DATA!$D$2:$AB$2,0)+1)

    4. bila data tidak ada maka nilai formula langkah 3 di atas hasilnya #N/A, untuk menghindari ini ditambahkan formula IFERROR, yang artinya jika error maka lakukan hal lain, maka kita pilih hasilnya 0
    IFERROR(<langkah3>,0) menjadi

    =IFERROR(OFFSET(DATA!$C$2,MATCH(1,(DATA!$A$3:$A$31="LISTRIK")*(DATA!$B$3:$B$31=$A3)*(DATA!$C$3:$C$31=B$2),0),MATCH($L$7,DATA!$D$2:$AB$2,0)+1),0)

    5. langkah terakhir yang terpenting dan tidak boleh salah, yaitu : karena kita menggunakan formula array pada langkah 1, maka setelah mengetik formula di atas jangan tekan ENTER atau tombol panah, tapi HARUS tekan kombinasi tombol [CTRL]+[SHIFT]+[ENTER]
    yang membedakan dengan formula biasa adalah adanya tanda kurung kurawal buka dan tutup pada formula setelah kombinasi tombol di atas dilakukan.

    catatan, penggunaan tanda dolar ("$") untuk memudahkan formula dapat dikopi ke sel yang lain

    berikut saya uplod lagi yg kolomnya +1, semoga sesuai dgn yang diinginkan

  6. mas, sungguh detail sekali penjelasannya, terima kasih saya sangat senang dapat kesempatan mengenal formula seperti ini. tapi mmm... masih kurang sedikit ya mas, mungkin saya kurang detail dalam memberikan case,.. mohon maaf mas, jika terlalu merepotkan

    berikut terlampir contoh soal detailnya mas..

    terima kasih sangat, mas guru

  7. diditsatriyadi

    21 Apr 2017 Terverifikasi Surabaya + 1.103 Poin

    pake SUMPRODUCT, nanti kalo nganggur saya jelaskan caranya :D

  8. ohh gtu mas.. masih bingung saya.. he..
    tapi terima kasih mas, sudah berkenan membantu..

  9. diditsatriyadi

    21 Apr 2017 Terverifikasi Jawaban Terpilih Surabaya + 1.103 Poin

    kan judul forum ini belajar excel, saya sendiri juga belajar
    ini saya lampirkan formula versi saya
    sedikit penjelasan saja ya, sisanya di search google aja tentang guna tiap formula SUMPRODUCT, OFFSET, MATCH
    utk di sel B3 digunakan formula

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


    beda dengan formula sebelumnya kali ini tidak usah pake tombol ajaib ctrl+shift+enter karena sumproduct memang khusus untuk array
    fungsi di atas dipecah jadi 2 bagian yaitu:
    (DATA!$A$3:$A$31="LISTRIK")*(DATA!$B$3:$B$31=$A3)*(DATA!$C$3:$C$31=B$2)
    dan
    OFFSET(DATA!$C$2,1,MATCH($L$7,DATA!$D$2:$AB$2,0)+1,29,1)
    bagian pertama itu isinya adalah kumpulan data (array) logik 0 dan 1 untuk urutan baris yg sesuai dengan kata "listrik", isi sel A3 dan isi sel B2
    bagian kedua berisi array data sesuai dengan kolom yang match dengan tanggal input
    maka SUMPRODUCT menggabungkan perkalian kedua array tersebut.
    kira-kira demikian penjelasannya untuk dipelajari :D

  10. ga tau harus bilang apa lagi selain ucapan terima kasih sangat mas guru. yg sudah baik mengucurkan ilmunya ke saya.

    saya akan pelajari pelan2 mas.. mudah2an cepat nangkap otak saya.. he...

    terima kasih mas..

 

atau Mendaftar untuk ikut berdiskusi!