Menghitung data yang isi cell formula/time

  1. 3 minggu lalu

    Permisi Admin dan Teman-Teman, Mohon ijin bertanya

    saya punya masalah dalam menghitung data yang isi cellnya formulla atau time,
    FIle yang saya lampirkan merupakan Recap Absensi selama satu bulan yang datanya saya ambil dari beberapa sheet(tanggal1-31). yang saya tanyakan bagaimana mencari jumlah HK(Hari Kerja) "DZ" yang datanya berpatokan pada IN di setiap tanggal. saya sudah coba pakai Count,CountIF,SUM tapi blm ketemu, apa mungkin karena isi cellnya merupakan formula dan ada cell yang memang isinya kosong. -image-

    mohon solusinya para suhu dan mastah

    @giefilth ...

    Maksudnya, kolom DZ pada sheet RECAP PER MONTH ya? Kalau benar, coba masukkan dahulu formula berikut pada sel DZ7:

    =SUMPRODUCT((ISNUMBER(TIMEVALUE($F7:$DY7))+($F7:$DY7="LF"))*(MOD(COLUMN($A$1:$DT$1)-1;4)=0))

    Untuk baris berikutnya, tinggal salin sel DZ7 lalu tempel ke baris selanjutnya.

    Demikian, semoga sesuai.

  2. Caton

    Agu 26 Terverifikasi Jawaban Terpilih Indonesia + 14.352 Poin
    Di sunting 3 minggu lalu oleh Caton

    @giefilth ...

    Maksudnya, kolom DZ pada sheet RECAP PER MONTH ya? Kalau benar, coba masukkan dahulu formula berikut pada sel DZ7:

    =SUMPRODUCT((ISNUMBER(TIMEVALUE($F7:$DY7))+($F7:$DY7="LF"))*(MOD(COLUMN($A$1:$DT$1)-1;4)=0))

    Untuk baris berikutnya, tinggal salin sel DZ7 lalu tempel ke baris selanjutnya.

    Demikian, semoga sesuai.

  3. Di sunting 3 minggu lalu oleh giefilth

    Terimakasih banyak, seneng banget di balas sama mastah @Caton sudah betul mastah tinggal di modif sedikit =SUMPRODUCT((ISNUMBER(TIMEVALUE($F7:$DY7))+($F7:$DY7="LF")+($F7:$DY7="S")+($F7:$DY7="CK")+($F7:$DY7="CM")+($F7:$DY7="C"))*(MOD(COLUMN($A$1:$DT$1)-1;4)=0))

    hasilnya sudah sesuai mastah, tapi saya belum paham rumusnya mastah @Caton kalo tidak keberatan boleh di jelaskan ga masatah? hehehe

    Terimakasih mastah

    *kalo disini ada cendolnya saya kirim cendol deh buat mastah

  4. Caton

    Agu 27 Terverifikasi Indonesia + 14.352 Poin

    @giefilth ...

    Sebenarnya, kalau memang targetnya hanya untuk menghitung kolom-kolom yang ada isinya (tanpa ada pengecualian, misalkan bukan bernilai C, CM dan sebagainya), maka cukup menggunakan salah satu formula berikut:

    =SUMPRODUCT((LEN($F7:$DY7)>0)*(MOD(COLUMN($A$1:$DT$1)-1;4)=0))
    =SUMPRODUCT(($F$6:$DY$6="IN")*(LEN($F7:$DY7)>0))

    atau menggunakan salah satu formula dengan fungsi COUNTIFS berikut:

    =COUNTIFS($F$6:$DY$6;"IN";$F7:$DY7;"<>""")-COUNTIFS($F$6:$DY$6;"IN";$F7:$DY7;"")
    =COUNTIFS($F$6:$DY$6;"IN";$F8:$DY8;"?*")

    Catatan: Untuk formula dengan komparasi pada teks header, pastikan teks header setiap kolom pertama dari setiap kelompok tanggal tidak mengandung spasi.

    Formula yang saya berikan sebelumnya di atas, saya buat dengan asumsi teks selain waktu dan LF (seperti C, CM dan sebagainya) tidak dihitung hari kerja. Jadi, jika ada pengecualian tertentu, bisa menggunakan contoh awal yang saya berikan sebelumnya. Namun, jika hanya ingin menghitung jumlah hari yang ada nilainya (berupa waktu atau notasi lainnya), gunakan alternatif formula yang saya jelaskan di atas.

    @giefilth ... kalo tidak keberatan boleh di jelaskan ...

  5. Caton

    Agu 27 Terverifikasi Indonesia + 14.352 Poin

    @giefilth ...

    Maaf... tadi ada pekerjaan yang harus diselesaikan segera, jadi penjelasannya jadi terputus... :D

    @giefilth ... kalo tidak keberatan boleh di jelaskan ...

    Ini sebenarnya gak rumit mas. Formulasi:

    ... ISNUMBER(TIMEVALUE($F7:$DY7)) ...

    digunakan untuk mengkonversi nilai pada setiap sel dalam range $F7:$DY7 menjadi nilai waktu. Proses konversi ini dilakukan karena nilai pada range tersebut pada dasarnya bukan berupa angka, namun teks (yakni hasil dari fungsi VLOOKUP). Apabila hasil konversi tepat (jika nilai pada sel yang diuji dalam format waktu, misalkan 08:00), maka hasil dari fungsi TIMEVALUE adalah angka, sebaliknya jika teks yang akan dikonversi bukan dalam format waktu (jam), hasilnya akan error (#VALUE!). Dengan fungsi ISNUMBER, hasil konversi tadi diuji kembali apakah benar nilainya berupa angka atau bukan (TRUE atau FALSE).

    Tadinya, karena saya pikir akan ada kriteria khusus yang ditentukan, maka saya buat tambahan formulasi berikut:

    ... ($F7:$DY7="LF") ...

    yang akan menguji isi sel apakah sama dengan "LF". Idenya, sebuah sel bisa berisi nilai waktu, atau notasi khusus lainnya seperti "LF". Saya anggap, selain nilai waktu, hanya notasi tersebut yang diizinkan. Sama seperti formulasi sebelumnya, hasil dari formulasi ini adalah TRUE atau FALSE.

    Selanjutnya, adalah dengan menggabungkan kedua formula tersebut ke dalam sebuah operasi logika OR. Idenya, jika sel berisi nilai waktu, maka bukan teks khusus (misalkan "LF"). Sebaliknya, jika nilai sel bukan waktu, maka periksa apakah nilai tersebut adalah nilai yang diizinkan. Gabungan kedua formula di atas:

    ... (ISNUMBER(TIMEVALUE($F7:$DY7))+($F7:$DY7="LF")) ...

    hanya akan menghasilkan nilai 1 atau 0 saja, dikarenakan mengacu kepada sel sama. Berikutnya adalah formulasi:

    ... (MOD(COLUMN($A$1:$DT$1)-1;4)=0) ...

    yang digunakan untuk merujuk ke kolom pertama dari setiap kelompok tanggal (kolom yang memiliki header IN). Oleh karena kolom IN berada pada posisi pertama setiap 4 kolom, maka perlu perhitungan khusus agar sel yang diuji hanya pada kolom pertama saja dari setiap 4 kolom. Jadi, nilai yang diuji adalah pada kolom 1, 5, 9 dan seterusnya. Atau bisa juga dengan formula:

    ... (MOD(COLUMN($A$1:$DT$1);4)=1) ...

    Hasil formula ini akan dikalkulasikan dengan formula sebelumnya dengan menggunakan operasi logika AND. Jadi, jika formula terakhir ini bernilai TRUE (misalkan pada kolom ke-1 atau ke-5 dan lain sebagainya), dan nilai dari formulasi sebelumnya menghasilkan angka 1, maka hasil akhirnya:

    = 1 * TRUE = 1

    Jadi, untuk kolom ke-1, kolom ke-5 dan seterusnya akan dihasilkan nilai 1. Selain kolom tersebut akan menghasilkan nilai 0. Jika nilai dari formula ini adalah 0, maka hasil formula secara keseluruhan juga 0. Demikian juga dengan formula sebelumnya...

    Catatan: Range pada fungsi COLUMN($A$1:$DT$1) harus sama lebarnya dengan range yang akan diuji (range F:DZ).

    Saya kira itu saja yang bisa saya jelaskan saat ini mas, soalnya lagi ada pekerjaan yang harus dilanjutkan kembali... :D

    Demikian.

  6. @Caton Mantappp Mastah saya ucapkan terimakasih banyak sudah sangat membantu dan nambah ilmu.. kalo di forum sebelah nanti saya kirim cendol mas hehe

  7. Caton

    Agu 27 Terverifikasi Indonesia + 14.352 Poin

    @giefilth ...

    Sama-sama mas... Sayangnya di forum ini gak ada yang jualan cendol ... :D

 

atau Mendaftar untuk ikut berdiskusi!