Mengambil jam awal dan jam akhir lewat dari 24 jam

  1. 3 tahun lalu

    Perkenalkan saya Aryo. Saya ingin meminta ilmu dan bantuan untuk file excel yang terlampir. Saya terkendala dalam pengambilan waktu pada shift 3 . Saya sudah menggunakan rumus min/max+index+match dan bantuan vlookup, tetapi tidak mendapatkan waktu awal dan akhir pada shift 3. Mohon sekiranya mas/mbak untuk membantu saya. Terima kasih.

    @Morczzz ...

    Coba periksa kembali formulanya. Formula yang digunakan :

    =IFERROR(IF(LEN($B7);INDEX(DATA!$E$7:$E$680;AGGREGATE(15;6;(ROW(DATA!$A$7:$A$680)-3)/((DATA!$B$7:$B$680=$C$3)*(DATA!$C$7:$C$680=$B7)*(DATA!$E$7:$E$680=$C$4));1));"");"")

    Pada formula tersebut, formula untuk mencari data yang sesuai kriteria adalah:

    ((DATA!$B$7:$B$680=$C$3)*(DATA!$C$7:$C$680=$B7)*(DATA!$E$7:$E$680=$C$4))

    dimana formula tersebut akan menghasilkan nilai 0 jika salah satu atau seluruh kriteria tidak ditemukan, dan nilai 1 jika seluruh kriteria ditemukan. Sampai di sini, formula sudah sesuai yang diharapkan.

    Formulasi berikutnya adalah menentukan pada baris berapa data yang memenuhi kriteria tersebut berada? Pada formula di atas, formula :

    (ROW(DATA!$A$7:$A$680)-3)

    bertujuan akan menghasilkan indeks baris data (1, 2, 3 dst). Maka, seharusnya formula tersebut menghasilkan nilai berurutan dari 1 sampai dengan jumlah baris. Dengan formula tersebut, formula ROW(DATA!$A$7:$A$680) akan menghasilkan array nilai 7 sampai dengan 680 (mulai baris A7 sampai dengan baris A680). Dan ini tidak sesuai dengan tujuan awal dimana formula tersebut harus menghasilkan nilai berupa indeks baris data berurutan dari 1 sampai dengan akhir baris (untuk range A7:A680, indeks seharusnya mulai dari 1 sampai dengan 674). Sehingga, untuk mendapatkan indeks baris yang sesuai, maka ditambahkan pengurang (ROW - n).

    Pada formula (ROW(DATA!$A$7:$A$680)-3), dengan pengurang 3 maka indeks nilai yang dihasilkan adalah :

    = (ROW(DATA!$A$7:$A$680) - 3
    = (7:680) - 3
    = 4:677

    Tentunya hasil tersebut tidak sesuai, karena seharusnya indeks baris yang diharapkan adalah 1:674. Ini adalah kesalahan pertama. Seharusnya:

    = (ROW(DATA!$A$7:$A$680) - 6
    = (7:680) - 6
    = 1:674

    sehingga formula intinya menjadi :

    (ROW(DATA!$A$7:$A$680)-6)/((DATA!$B$7:$B$680=$C$3)*(DATA!$C$7:$C$680=$B7)*(DATA!$E$7:$E$680=$C$4))

    Dengan formula tersebut, setiap indeks baris yang ditetapkan dalam formula

    (ROW(DATA!$A$7:$A$680)-6)

    akan dibagi dengan nilai yang dihasilkan oleh blok formula

    ((DATA!$B$7:$B$680=$C$3)*(DATA!$C$7:$C$680=$B7)*(DATA!$E$7:$E$680=$C$4))

    Oleh karena nilai pada blok indeks baris akan berisi nilai > 0, sedangkan nilai pada blok kriteria dapat berupa nilai 0 atau nilai 1, maka hasil akhirnya dapat berupa angka yang merupakan indeks baris, atau error berupa #DIV/0!. Sebagai gambaran,

    = ROW(A1:A5) / ISODD(ROW(A1:A5))
    = {1/TRUE, 2/FALSE, 3/TRUE, 4/FALSE, 5/TRUE}
    = {1/1, 2/0, 3/1, 4/0, 5/1}
    = {1, #DIV/0!, 3, #DIV/0!, 5}

    Agar Excel tidak ikut mengkalkulasi nilai Error tersebut, digunakanlah fungsi AGGREGATE, dengan indeks fungsi 15 = fungsi SMALL dan indeks opsi = 6 (Ignore Error Values). Mengapa menggunakan indeks fungsi 15 (SMALL)? Karena yang dicari adalah nilai terkecil atau nilai pertama yang muncul sesuai kriteria pencarian.

    Setelah mendapatkan indeks baris yang dikehendaki, selanjutnya dalah menentukan nilai aktual pada baris data yang diinginkan. Untuk nilai JAM AWAL, ada pada kolom F sheet DATA. Namun, pada formula yang mas gunakan,

    ... INDEX(DATA!$E$7:$E$680; ...

    data diambil bukan pada kolom F, tetapi pada kolom E! Tentu saja hasilnya menjadi tidak tepat. Koreksi formulanya dapat dilihat pada file terlampir.

    Demikian.

  2. Caton

    22 Jul 2021 Terverifikasi Indonesia + 20.101 Poin

    @Morczzz ...

    Coba periksa file terlampir. Mungkin sudah sesuai dengan yang ditanyakan.

    Demikian.

  3. Terima kasih mas @Caton bersedia menanggapi.

    Tetapi yang saya maksud sebagaimana terlampir pada file berikut:
    Mohon sekiranya dapat menanggapi kembali. Terima kasih.

  4. Caton

    22 Jul 2021 Terverifikasi Indonesia + 20.101 Poin

    @Morczzz ...

    Terlampir contoh formulasinya.

    Demikian.

  5. Terima kasih banyak mas @Caton atas bantuannya.

  6. Mohon maaf mas @Caton menyita waktunya kembali.

    Apakah dapat diambil berdasarkan 3 kategori: nama, shift, dan tanggal?
    atau tinggal saya tambahkan mengikuti pada formula.

  7. Caton

    22 Jul 2021 Terverifikasi Indonesia + 20.101 Poin

    @Morczzz...

    Bisa. Coba tambahkan saja kriterianya dalam fungsi AGGREGATE...

    (($D$4:$D$28=$K6)*($E$4:$E$28=$L$3))

    Potongan formula di atas inti dari formulanya, dimana blok:

    ($D$4:$D$28=$K6)

    untuk menguji apakah pada kolom D ada nama yang sesuai dengan nilai pada sel K6, dan blok:

    ($E$4:$E$28=$L$3)

    untuk menguji apakah ada nilai pada kolom E yang sesuai dengan nilai shift pada sel L3. Maka, untuk menambahkan kriteria lainnya, tinggal tambahkan saja dalam blok formula tersebut.

    Misalkan, kriteria tanggal ada pada sel L4, maka periksa apakah nilai pada sel tersebut sesuai dengan nilai pada kolom B. Contoh :

    ($B$4:$B$28=$L$4)

    kemudian tambahkan formula tersebut ke dalam formula sebelumnya, misalkan:

    (($B$4:$B$28=$L$4)*($D$4:$D$28=$K6)*($E$4:$E$28=$L$3))

    Demikian, selamat mencoba dan semoga sesuai.

  8. Mas @Caton terima kasih.

    Tapi setelah saya tambahkan, kenapa tidak terambil waktu awal dan akhir pada file terlampir.
    Maaf direpotkan oleh saya yang ingin ilmu dari mas.

  9. Caton

    23 Jul 2021 Terverifikasi Jawaban Terpilih Indonesia + 20.101 Poin

    @Morczzz ...

    Coba periksa kembali formulanya. Formula yang digunakan :

    =IFERROR(IF(LEN($B7);INDEX(DATA!$E$7:$E$680;AGGREGATE(15;6;(ROW(DATA!$A$7:$A$680)-3)/((DATA!$B$7:$B$680=$C$3)*(DATA!$C$7:$C$680=$B7)*(DATA!$E$7:$E$680=$C$4));1));"");"")

    Pada formula tersebut, formula untuk mencari data yang sesuai kriteria adalah:

    ((DATA!$B$7:$B$680=$C$3)*(DATA!$C$7:$C$680=$B7)*(DATA!$E$7:$E$680=$C$4))

    dimana formula tersebut akan menghasilkan nilai 0 jika salah satu atau seluruh kriteria tidak ditemukan, dan nilai 1 jika seluruh kriteria ditemukan. Sampai di sini, formula sudah sesuai yang diharapkan.

    Formulasi berikutnya adalah menentukan pada baris berapa data yang memenuhi kriteria tersebut berada? Pada formula di atas, formula :

    (ROW(DATA!$A$7:$A$680)-3)

    bertujuan akan menghasilkan indeks baris data (1, 2, 3 dst). Maka, seharusnya formula tersebut menghasilkan nilai berurutan dari 1 sampai dengan jumlah baris. Dengan formula tersebut, formula ROW(DATA!$A$7:$A$680) akan menghasilkan array nilai 7 sampai dengan 680 (mulai baris A7 sampai dengan baris A680). Dan ini tidak sesuai dengan tujuan awal dimana formula tersebut harus menghasilkan nilai berupa indeks baris data berurutan dari 1 sampai dengan akhir baris (untuk range A7:A680, indeks seharusnya mulai dari 1 sampai dengan 674). Sehingga, untuk mendapatkan indeks baris yang sesuai, maka ditambahkan pengurang (ROW - n).

    Pada formula (ROW(DATA!$A$7:$A$680)-3), dengan pengurang 3 maka indeks nilai yang dihasilkan adalah :

    = (ROW(DATA!$A$7:$A$680) - 3
    = (7:680) - 3
    = 4:677

    Tentunya hasil tersebut tidak sesuai, karena seharusnya indeks baris yang diharapkan adalah 1:674. Ini adalah kesalahan pertama. Seharusnya:

    = (ROW(DATA!$A$7:$A$680) - 6
    = (7:680) - 6
    = 1:674

    sehingga formula intinya menjadi :

    (ROW(DATA!$A$7:$A$680)-6)/((DATA!$B$7:$B$680=$C$3)*(DATA!$C$7:$C$680=$B7)*(DATA!$E$7:$E$680=$C$4))

    Dengan formula tersebut, setiap indeks baris yang ditetapkan dalam formula

    (ROW(DATA!$A$7:$A$680)-6)

    akan dibagi dengan nilai yang dihasilkan oleh blok formula

    ((DATA!$B$7:$B$680=$C$3)*(DATA!$C$7:$C$680=$B7)*(DATA!$E$7:$E$680=$C$4))

    Oleh karena nilai pada blok indeks baris akan berisi nilai > 0, sedangkan nilai pada blok kriteria dapat berupa nilai 0 atau nilai 1, maka hasil akhirnya dapat berupa angka yang merupakan indeks baris, atau error berupa #DIV/0!. Sebagai gambaran,

    = ROW(A1:A5) / ISODD(ROW(A1:A5))
    = {1/TRUE, 2/FALSE, 3/TRUE, 4/FALSE, 5/TRUE}
    = {1/1, 2/0, 3/1, 4/0, 5/1}
    = {1, #DIV/0!, 3, #DIV/0!, 5}

    Agar Excel tidak ikut mengkalkulasi nilai Error tersebut, digunakanlah fungsi AGGREGATE, dengan indeks fungsi 15 = fungsi SMALL dan indeks opsi = 6 (Ignore Error Values). Mengapa menggunakan indeks fungsi 15 (SMALL)? Karena yang dicari adalah nilai terkecil atau nilai pertama yang muncul sesuai kriteria pencarian.

    Setelah mendapatkan indeks baris yang dikehendaki, selanjutnya dalah menentukan nilai aktual pada baris data yang diinginkan. Untuk nilai JAM AWAL, ada pada kolom F sheet DATA. Namun, pada formula yang mas gunakan,

    ... INDEX(DATA!$E$7:$E$680; ...

    data diambil bukan pada kolom F, tetapi pada kolom E! Tentu saja hasilnya menjadi tidak tepat. Koreksi formulanya dapat dilihat pada file terlampir.

    Demikian.

  10. Terima kasih banyak mas @Caton atas ilmu yang diberikan.

 

atau Mendaftar untuk ikut berdiskusi!