Rumus excel berdasarkan beberapa kondisi

  1. 8 bulan yang lalu
    Di sunting 8 bulan yang lalu oleh goral

    selamat malam para master..
    saya mempunyai data seperti yang terlampir
    karena saya masih blm matang dalam membuat rumus,
    mohon bantuannya untuk rumus excelnya,ya :)
    trimaksih sebelumnya

  2. Caton

    7 Peb 2018 Terverifikasi Indonesia + 11.522 Poin

    Yang kurang saya pahami dari contoh data yang dilampirkan adalah:

    — Mengapa ada baris data yang diduplikasi pada tabel data. Bagaimana jika nilai pada kolom Nilai Kontrak dikosongkan, apakah ada pengaruhnya (hanya untuk nilai duplikat pada kolom Nilai Kontrak saja)?

    — Pada tabel hasil, apa yang dijadikan acuan pencarian nilai pada tabel data? Baiknya ada kolom tambahan sebagai acuan, misalkan kolom Nomor SPT.

    Silahkan diperiksa file terlampir... ;)

  3. — Mengapa ada baris data yang diduplikasi pada tabel data. Bagaimana jika nilai pada kolom Nilai Kontrak dikosongkan, apakah ada pengaruhnya (hanya untuk nilai duplikat pada kolom Nilai Kontrak saja)?
    - biar mudah untuk di pivot :) dan juga saat saya filter berdasarkan no.(di kolom"No sPT") maka bisa menampilkan semua tgl (di kolom"tgl pengerjaan")

  4. — Pada tabel hasil, apa yang dijadikan acuan pencarian nilai pada tabel data? Baiknya ada kolom tambahan sebagai acuan, misalkan kolom Nomor SPT.
    - sebenarnya tabel hasil dengan tabel data itu jadi satu,mas@Caton :)
    sengaja saya pisah,agar saya bisa lebih mudah menjelaskannya

    mohon saran dan masukannya,ya mas..
    trimakasih

  5. Caton

    8 Peb 2018 Terverifikasi Indonesia + 11.522 Poin

    Dengan sedikit modifikasi, contoh yang saya berikan di atas dapat disatukan ke dalam satu tabel seperti yang mas @Goral maksudkan.

    — Tahap Pertama (sheet TES 1), adalah menentukan formula untuk mendapatkan kunci kalkulasinya, yakni No. SPT. Guna menghindari penjumlahan terhadap Nilai Yang Dikerjakan pada kunci yang sama (hasil duplikasi), maka Nilai Yang Dikerjakan diambil berdasarkan kunci yang muncul pertama kali saja. Jadi, diasumsikan jika ada kunci yang sama (No. SPT muncul lebih dari 1 kali), maka nilai tersebut dianggap sebagai duplikasi. Ini adalah kriteria yang harus terpenuhi. Dilemanya, jika ternyata ada kunci yang sama, namun memiliki Nilai Yang Dikerjakan berbeda (bukan hasil duplikasi), maka formulanya bisa gagal... :) Solusinya (mungkin) dengan cara membandingkan Nilai Yang Dikerjakan untuk setiap No. SPT yang sama. Jika nanti memang ada kasusnya seperti itu, coba dimodifikasi kembali formulanya. Untuk saat ini, saya hanya mencoba membantu membuatkan formulanya sesuai kondisi yang ada.

    — Tahap Kedua (sheet TES 2), gabungkan proses penentuan kunci kalkulasi tersebut ke dalam sebuah formula utuh untuk mendapatkan Total Nilai Yang Dikerjakan per No. SPT. Untuk kolom-kolom lainnya, buatkan formula yang akan menguji apakah ada nilai pada kolom Total Nilai Yang Dikerjakan pada baris yang sama. Jika ada nilainya (Total Nilai Yang Dikerjakan <> 0), maka lanjutkan dengan proses kalkulasi. Jika tidak, kembalikan nilai 0 (nol) atau Empty String.

    Terlampir contoh solusi yang dapat saya berikan. Demikian, semoga bermanfaat... ;)

  6. trimakasih untuk solusinya mas@Caton ..formulanya ini yang sangat membantu saya..baik,mas.akan saya coba buat data ulang lagi(coba2 modifikasi rumus2 yg ada) :) ..jikalau nanti butuh bantuan,mohon di beri petunjuk lagi ya,mas
    trimakasih banyak

  7. Caton

    8 Peb 2018 Terverifikasi Indonesia + 11.522 Poin

    Sama-sama mas @Goral... semoga bermanfaat :)

  8. Di sunting 8 bulan yang lalu oleh goral

    mas@Caton
    setelah saya utak atik,..saya pindah posisi sedemikian rupa :)
    ternyata ada kendala,nih mas
    mohon bantuannya,ya
    berikut saya lampirkan contoh yg saya buat (sheet "TES 3")
    maklum,masih newbie..

  9. Caton

    8 Peb 2018 Terverifikasi Indonesia + 11.522 Poin

    Untuk kolom L, kolom M dan kolom N, ubah argumen Logical Test dari fungsi IF dari:

    =IF(LEN([@J]);...)

    menjadi:

    =IF([@J]>0;...)

    Contohnya untuk sel L6 menjadi:

    =IF([@J]>0;SUMPRODUCT(([C]=[@C])*([J]>0)*[G])-[@J];0)

    Demikian. Semoga sesuai... ;)

  10. kalo untuk menghilangkan tanda (-) dikolom(biar kosongan) gitu gimana caranya ya,mas@Caton..?

  11. Caton

    8 Peb 2018 Terverifikasi Indonesia + 11.522 Poin
    Di sunting 8 bulan yang lalu oleh Caton

    Pake format sel saja, misalkan seperti berikut:

    Shoot051.png

    Atau gunakan Custom Conditional Formatting, dengan formula:

    =($K6=0)

    kemudian atur Cell Number menggunakan Custom Number:

    ;;;

    dan Applies To range $K$6:$L$14. Demikian... ;)

  12. Di sunting 8 bulan yang lalu oleh goral

    ooww,begitu ya,mas
    baik mas..
    satu lagi mas,bikin penasaran
    kolom kuning tulisan "selesai" kok gak bisa kosong ya,.kayak contoh nya mas @Caton dulu itu
    saya coba format sel,..ndak bisa

  13. Caton

    8 Peb 2018 Terverifikasi Indonesia + 11.522 Poin
    Di sunting 8 bulan yang lalu oleh Caton

    Formulanya disesuaikan seperti yang saya jelaskan di atas mas @Goral... :) Jadi untuk kolom L (pada tabel kolom SELISIH) dimulai dari sel L6, formulanya:

    =IF([@J]>0;SUMPRODUCT(([C]=[@C])*([J]>0)*[G])-[@J];0)

    Untuk kolom M (pada tabel kolom STATUS) dimulai dari sel M6, formulanya:

    =IF([@J]>0;IF([@K]>20000;"BELUM SELESAI";"SELESAI");"")

    Dan untuk kolom N (pada tabel kolom TANGGAL SELESAI) dimulai dari sel N6, formulanya:

    =IF([@J]>0;IF([@L]="SELESAI";SUMPRODUCT(MAX(([C]=[@C])*[H]));"");"")

    Intinya, jika pada kolom K (TOTAL YANG DIKERJAKAN) bernilai 0, maka nilai dari ketiga kolom selanjutnya akan menjadi 0 (nol) atau Empty String. Untuk kolom STATUS dan kolom TANGGAL SELESAI tidak perlu menggunakan Conditional Formatting atau Custom Cell Formatting karena hasilnya sudah akan berupa Empty String jika hasil fungsi IF = FALSE. Demikian... ;)

  14. siap mas @Caton :)
    saya utek2 lagi
    maaf merepotkan
    perlu banyak belajar,nih..hahahaha
    semoga panjang umur mas ya
    aamin

  15. Caton

    8 Peb 2018 Terverifikasi Indonesia + 11.522 Poin

    Aamiin... Semoga demikian pula adanya bagi mas @Goral... :)

  16. alhamdulillah,mas @Caton masalah sudah teratasi (barusan teratasi) :)
    Aamiin..

 

atau Mendaftar untuk ikut berdiskusi!