LOOKUP DENGAN RANGE DATE

  1. 6 tahun lalu

    Selamat malam ,
    salam kenal saya Lala member baru..
    Dear para master excel. saya ingin bertanya,
    sya punya data excel yang akan saya lampirkan. ada dua problem :

    1. saya memberi conditional formating (use a to determine) pada sheet template, apakah ada rumus yang salah? soalnya di laptop saya conditional formating nya berjalan. namun ketika dibuka di PC lain menggunakan spreedsheet 2016 conditional formating nya ada yang gagal. (pada sheet rumusnya sudah saya tulis setelah baris terakhir).
    2. pada sheet lookup data, bagaimana menggunakan rumus INDEX, SMALL dengan kriteria lookup value nya range tanggal (misal mencari data dengan range tanggal dari 8 aug 2018 - 05 sep 2018)?

    mohon solusinya. terimakasih.

  2. Caton

    9 Agu 2018 Terverifikasi Indonesia + 20.101 Poin

    Mbak @Lala ...

    ... data excel yang akan saya lampirkan ...

    Sepertinya, lampiran yang mbak maksudkan belum terunggah ... :)

  3. Wkwwk, kok bisa ya. padahal tadi udah terunggah..
    berikut ms @Caton

  4. Caton

    10 Agu 2018 Terverifikasi Indonesia + 20.101 Poin
    Di sunting 6 tahun lalu oleh Caton

    Mbak @Lala ...

    Coba periksa kembali file terlampir. Mudah-mudahan sesuai dengan yang ditanyakan. Jika belum sesuai, silahkan didiskusikan kembali. Catatan, file yang saya upload berekstensi XLSB (biar ukurannya lebih kecil) yang sepertinya tidak kompatible dengan versi Excel 2003 / WPS / LibreOffice...

  5. Terimaksih mas @Caton ..
    saya sudah mencoba pakai conditional formating dg rumus =AND(ISNUMBER(O3);O3>J3). Tapi setelah saya tes dengan mengisi tanggal di kolom departemen kadang fungsi nya berjalan dan kadang tidak.. padahal saya isi dengan tanggal yg sama, jadi yg satu berwarna abu" yg kedua berwarna putih.. seperti SS sya dbawah ini.

  6. Di sunting 6 tahun lalu oleh manweljs_

    coba diedit menjadi :

    =AND(ISNUMBER(O$3);O$3>J$3)

    atau :

    =AND(ISNUMBER($O3);$O3>$J3)

    atau mungkin

    =AND(ISNUMBER(O$3);O$3>$J$3)

    ya pokoknya di tuker2 lah dolarnya :D

  7. untuk kasus yang ke-2, yang sya maksud yaitu di sheet lookup data pada kolom G dan H berisi tanggal, nah itu sebagai acuan untuk menarik data dari sheet template sesuai dengan kolom nya.
    Jadi misal saya ingin mencari data dari tanggal 20 aug 2018 sampai dengan 05 sep 2018, ada data apa saja..
    dan acuan tanggal tersebut mengacu pada sheet template dikolom departemen.
    demikian mas @Caton

  8. kasus 1 sudah terselesaikan..
    trimakasih ms @Caton @manweljs_

  9. Caton

    10 Agu 2018 Terverifikasi Indonesia + 20.101 Poin
    Di sunting 6 tahun lalu oleh Caton

    Mbak @Lala ...

    [1]. Untuk yang pertama, aturannya simple saja. Karena pembandingnya adalah nilai pada kolom J (DUE DATE), maka pastikan kolomnya bersifat absolut. Sedangkan kolom yang dibandingkan (area departemen) bersifat relatif. Sehingga jika dibuatkan formula menjadi:

    =AND(ISNUMBER(O3);O3>$J3)

    Kolom O dibuat relatif oleh karena saat Conditional Formatting-nya di terapkan (Applies To) ke range $O$3:$AU$3949, Excel akan otomatis memindahkan alamat sel masing-masing sel referensi (dalam hal ini kolom O), baik ke kanan (berdasarkan kolom) atau ke bawah (berdasarkan baris). Sedangkan kolom J, oleh karena bersifat tetap (absolut) pada kolomnya, hanya akan berpindah ke bawah (berdasarkan baris). Sehingga dengan formula tersebut, Excel akan membandingkan setiap nilai dari kolom O sampai dengan kolom AU, baris ke-3 (sel O3) sampai dengan baris ke-3949 (sel AU3949) apakah lebih besar dari nilai pada kolom J baris ke-3 (sel J3) sampai dengan baris ke-3949 (sel J3949).

    Semoga penggunaan alamat relatif dan absolut ini dapat dipahami agar dapat diterapkan pada Conditional Formatting lainnya.

    [2]. Untuk yang kedua, sel G1 dan H1 memang digunakan sebagai acuan range waktu yang akan diperiksa. Kalau dicari di sheet LOOKUP DATA, memang tidak ada. Nilai dari kedua sel tersebut tersimpan pada nama range Lookup.First dan Lookup.Last. Oleh karena saya menghindari penggunaan Array Formula, maka solusi yang saya gunakan adalah dengan menggunakan kolom bantu pada sheet TEMPLATE kolom AW.

    Pada kolom tersebut, akan dilakukan perbandingan nilai pada kolom O sampai dengan kolom AU untuk setiap barisnya, apakah ada tanggal pada range tersebut yang masuk kondisi yang ditentukan. Apabila ada tanggal yang masuk kondisi, maka formula akan menghasilkan nilai indeks baris, selain itu formula akan menghasilkan baris kosong.

    Nilai-nilai pada range AW3:AW3949 dinamakan Lookup.Helper. Nilai yang ada pada nama range tersebut yang kemudian digunakan oleh formula pada sheet TEMPLATE. Intinya, karena nilai pada nama range tersebut berisi indeks baris, maka nilai-nilainya otomatis tersusun secara ascending, meskipun ada kemungkinan nilainya tidak berurutan (misalkan berupa 1, 2, 5, 6, 7, 11 ...). Karena nilainya sudah tersususn ascending, maka fungsi SMALL digunakan untuk mengambil indeks baris dari data yang cocok (sesuai kondisi).

    Saya tidak tahu dimana letak hasil yang tidak sesuai, karena acuan saya adalah nilai-nilai yang tertera di lampiran file yang mbak berikan pertama-tama. Mungkin mbak @Lala bisa menunjukkan masalahnya dimana, mungkin dengan memberikan contoh hasil secara manual?

  10. Caton

    11 Agu 2018 Terverifikasi Indonesia + 20.101 Poin

    Untuk pertanyaan poin ke-2, coba mbak @Lala periksa kembali apakah hasilnya sesuai dengan yang mbak inginkan, dan bandingkan formula yang saya susun dengan formula sebelumnya (formula awal dari file yang mbak lampirkan) untuk melihat perbedaannya. Pada dasarnya, kedua formula hampir sama. Saya hanya memperbaiki proses perbandingannya saja dan berusaha menghindari kalkulasi menggunakan Array Formula. Dalam hal ini, logika yang saya gunakan adalah:

    Untuk kondisi dimana setiap tanggal pada kolom departemen (kolom O sampai dengan kolom AU) pada sheet TEMPLATE yang yang lebih besar atau sama dengan tanggal pada sel G1 sheet LOOKUP DATA (atau Lookup.First) DAN lebih kecil atau sama dengan tanggal pada sel H1 sheet LOOKUP DATA (atau Lookup.last) maka tampilkan indeks baris datanya. Jika tidak sesuai, tampilkan teks kosong.

    Satu hal lagi, informasi yang saya maksud pada sheet LOOKUP DATA seperti pada gambar berikut:

    AA11234.png

    bertujuan agar ada opsi formula jika versi Excel yang digunakan adal Excel 2003, karena pada Excel 2003 tidak memiliki fungsi IFERROR, maka alternatifnya adalah menggunakan fungsi IF dan ISERROR. Namun jika mbak menggunak versi Excel 2007+, sebaiknya gunakan formula dengan fungsi IFERROR.

    Demikian mbak @Lala ... :)

 

atau Mendaftar untuk ikut berdiskusi!