Absensi keterlambatan

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

    Mohon bantuanya sangat untuk menfilter data nama, dan waktu keterlambatan 1, dan terlambat 2, dari data terlampir. trimakasih banyak seblumnya.Absen terlambat.xlsx

    Terlampir solusi dari saya. Mudah-mudahan sesuai... ;)

  2. mana datanya?

  3. mohon maaf sudah di upload lg pak

  4. pak @Caton mohon bantuanya

  5. Caton

    8 Peb 2018 Terverifikasi Jawaban Terpilih Indonesia + 11.522 Poin

    Terlampir solusi dari saya. Mudah-mudahan sesuai... ;)

  6. pak @caton di panggil tuh, hhhhh

    coba pakai filtr aja pak ntar kolom trlambatnya tinggal di custom filtr less than atau greatr than

    Pict >> http://prntscr.com/ibwhm2

  7. Trims pak @Caton dan pak @abdulmunibf, apabila ada waktu boleh dijelaskan pak, logika dari rumusnya, hehe

  8. Caton

    8 Peb 2018 Terverifikasi Indonesia + 11.522 Poin

    Awalnya, saya ingin menggunakan Array Formula. Namun, oleh karena Array Formula pada dasarnya agak sulit dipahami dan ditelusuri, maka saya gunakan saja kolom bantu (yakni pada kolom M, tersembunyi). Kolom bantu tersebut digunakan untuk mendapatkan baris dari kolom NAMA untuk setiap NAMA yang pertama kali muncul. Kemunculan NAMA selanjutnya (duplikasinya) akan diabaikan. Dengan demikian sudah didapatkan indeks baris dari masing-masing NAMA:

    =IF(COUNTIF($B$5:B6;B6)=1;ROW();"")

    Selanjutnya indeks baris yang sudah didapat melalui kolom M diambil berdasarkan indeks terkecil ke indeks terbesar (Ascending) dengan formula:

    ... SMALL($M$6:$M$368;ROW(A1)) ...

    Notasi ROW(A1) pada formula di atas menjadi indeks ke-n dari barisan nilai pada kolom M. Demikian seterusnya. Selanjutnya adalah mengambil nilai kolom NAMA, dalam hal ini, menggunakan fungsi INDEX:

    =INDEX($B$6:$B$368;SMALL($M$6:$M$368;ROW(A1))-5)

    Oleh karena nilai yang dihasilkan oleh fungsi SMALL tersebut pada dasarnya adalah nomor baris pada kolom NAMA, sedangkan senarai data pada fungsi INDEX tersebut dimulai dari baris ke 6 (sel B6), maka kita harus menyesuaikan indeks barisnya, agar indeks baris yang diambil oleh fungsi SMALL merujuk ke indeks data fungsi INDEX. Pada formula di atas, notasi -5 akan membuat indeks baris dari fungsi SMALL sesuai dengan indeks data pada fungsi INDEX. Terakhir, oleh karena fungsi SMALL dapat menghasilkan kesalahan #N/A jika barisan nilai pada kolom M sudah tidak ditemukan lagi (dikarenakan nomor indeks fungsi ROW() yang terus bertambah), maka kita gunakan fungsi IFERROR untuk mengatasi kesalahan tersebut:

    =IFERROR(INDEX($B$6:$B$368;SMALL($M$6:$M$368;ROW(A1))-5);"")

    Demikian tahap pertama untuk mengambil 1 nilai saja dari kolom NAMA. Semoga tidak bingung... :D

  9. terimakasih ilmunya pak, bermanfaat sekali..semoga menjadi kebaikan bagi bp..

    Kalo baru tahap pertama, ditunggu tahap selanjutny pak hihi...

  10. Caton

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

    Sebelumnya perlu diketahui dahulu, bahwa tipe nilai pada range D6:L368 sebaiknya (atau harus) berupa numerik, sedangkan format selnya bisa disesuaikan. Meski tipe nilai TEXT bisa saja dikalkulasikan dengan menambahkan notasi *1 dalam formula yang digunakan untuk membuatnya menjadi numerik, kadang hal tersebut sering merepotkan...

    Untuk mengambil nilai dari kolom TERLAMBAT MASUK, sebenarnya bisa saja menggunakan fungsi COUNTIFS dan SUMIFS. Kadangkala saya sering tanpa sadar langsung saja mengetikkan formula SUMPRODUCT... :D Untuk kolom P dan kolom Q, masing-masing dengan fungsi SUMPRODUCT, COUNTIFS dan SUMIFS, akan menjadi seperti berikut:

    =IF(LEN(O6);COUNTIFS($B$6:$B$368;O6;$I$6:$I$368;">0";$I$6:$I$368;"<=5");"")
    =IF(LEN(O6);SUMPRODUCT((($B$6:$B$368=O6)*($I$6:$I$368>0)*($I$6:$I$368<=5))*1);"")

    dan

    =IF(LEN(O6);SUMIFS($I$6:$I$368;$B$6:$B$368;O6;$I$6:$I$368;">0";$I$6:$I$368;"<=5");"")
    =IF(LEN(O6);SUMPRODUCT((($B$6:$B$368=O6)*($I$6:$I$368>0)*($I$6:$I$368<=5))*$I$6:$I$368);"")

    sedangkan kolom R dan kolom S, tinggal mengubah notasi "<=5" menjadi ">5" saja untuk mendapatkan hasilnya... ;)

    Untuk formula dengan fungsi SUMPRODUCT, intinya adalah pengujian terhadap nilai kolom B dan kolom I. Untuk setiap nilai pada range B6:B368 yang sama dengan nilai sel O6 (sampai sel O36) akan dihasilkan nilai TRUE atau FALSE. Demikian juga untuk setiap nilai pada range I6:I368 yang >0 dan setiap nilai pada range I6:I368 yang <=5, akan dihasilkan nilai TRUE atau FALSE. Hasil akhir dari formula:

    ... (($B$6:$B$368=O6)*($I$6:$I$368>0)*($I$6:$I$368<=5)) ...

    pada dasarnya juga sama, TRUE atau FALSE. Namun karena terjadi operasi perkalian, maka hasilnya sudah berupa 1 atau 0. Gambarannya (untuk melihat lebih jelas, klik kanan gambar, pilih Open image in new tab):

    Formula Bar.png

    Selanjutnya, tinggal mengalikan hasilnya dengan 1 jika ingin menghitung jumlah kemunculan datanya (sama dengan COUNT), atau dikalikan dengan nilai nilai pada range I6:I368 untuk mendapatkan jumlah nilainya (sama dengan SUM).

    Demikian yang dapat saya jelaskan. Semoga bermanfaat... ;)

  11. mantap ilmunya,mas @Caton
    cara belajarnya gimana mas..?
    hehehehe
    ilmunya sangat bermanfaat

  12. thanks pak @canton

  13. 7 bulan yang lalu

    pak @caton mohon penjelasannya, untuk usulan dari bp jumlah keterlambatannya terdapat kata kali, itu untuk menampilkan dan menghilangkan kali gimana ya? Contoh : 7 kali, padahal kalo di rumus tidak ditambahkan kata kali. Trims.

  14. Caton

    12 Peb 2018 Terverifikasi Indonesia + 11.522 Poin

    Tinggal diubah saja format selnya, dari Custom menjadi General atau Number... ;)

  15. Thanks pak @Caton

 

atau Mendaftar untuk ikut berdiskusi!