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.
Terjawab oleh Caton
Lihat Jawaban terkait@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.