Membuat List Dinamis dengan Kriteria

Membuat List Dinamis dengan Kriteria Menggunakan Data Validasi Excel

Tips kali ini merupakan lanjutan dari tips ExcelManiacs sebelumnya yaitu tentang Cara Membuat Daftar Dinamis dengan Data Validasi Excel, pada kolom komentar dalam posting tips tersebut ada sebuah pertanyaan dari rekan ExcelManiacs yang tidak menyebutkan namanya bertanya:

Keesokan harinya, "UNKNOWN" mengirim email dengan melampirkan contoh file excel berisi data stock barang untuk dibuatkan drop down list Nama-nama Barang dengan kolom keterangan "Dijual" dan "Tidak dijual" dengan ketentuan: Nama Barang dengan kriteria "Dijual" terlihat dalam drop down list, sedangkan Nama Barang dengan kriteria "Tidak dijual" otomatis tidak tampil dalam drop down list tersebut.

Silahkan unduh file dari ExcelManiacs dengan nama file : Dropdown_List_berKriteria.xlsx dan seperti biasa tentunya via Google Drive sebagai bahan berlatih pada PC atau Komputer Anda.

Selanjutnya, ExcelManiacs akan usahakan untuk menjelaskan 6 (enam) langkah cara Membuat List Dinamis dengan Kriteria ini, mudah-mudahan dapat dimanfaatkan fungsinya sesuai dengan kebutuhan Anda.

Langkah-langkah Membuat List Dinamis dengan Kriteria:

Membuat Rumus Nomor Urut Otomatis
Membuat Rumus Kriteria
Membuat "Conditional Formatting"
Membuat Kolom "Cek Rows"
Membuat Kolom "Barang Ready"
Membuat Drop Down List Menggunakan Data Validasi

Gambar berikut adalah tabel data stock barang sebagai salah satu contoh penerapan dari fungsi "Membuat List Dinamis dengan Kriteria Menggunakan Data Validasi Excel".

Tabel Stock Barang

Langkah 1: Membuat Rumus Nomor Urut Otomatis

Tabel di atas hanya berisi 10 (sepuluh) Nama Barang, pertanyaannya: "Bagaimana jika jumlah Nama Barang lebih dari 100 atau bahkan lebih dari 1000 nama barang?"

Untuk menghemat waktu, masukkan rumus atau formula excel pada cell A2 dengan tujuan ketika Anda menginput Nama Barang Baru maka Nomor Urut akan otomatis terisi:

=IF(ISBLANK(B2);"";COUNTA($B$2:B2))

lalu copy paste ke cell "A3" hingga ke cell di bawahnya sesuai keinginan Anda seperti terlihat pada gambar di bawah:

Rumus Nomor Urut Otomatis

Langkah 2: Membuat Rumus Kriteria

Contoh kriteria dalam tabel data nama barang di atas adalah "Ready" dan "OutOfStock" yang tertera dalam kolom Keterangan.

Ready, adalah kriteria barang apabila stock barang tersedia/lebih dari atau sama dengan 1 (satu).
OutOfStock, adalah kriteria barang apabila stock barang kosong atau 0 (nol).

Silahkan ketik dalam cell "E2", rumus sebagai berikut:

=IF(D2="";"";IF(D2>0;"Ready";"OutOfStock"))

kemudian copy paste ke cell "E3" hingga ke cell di bawahnya sesuai keinginan Anda seperti terlihat pada gambar di bawah:

Membuat Rumus Kriteria

Langkah 3: Membuat "Conditional Formatting"

Membuat "Conditional Formatting" kita gunakan sebagai fungsi untuk memberi format berbeda bagi kriteria yang terdapat dalam kolom Keterangan yaitu format "warna" pada kriteria "Ready" dan "OutOfStock".

Mengapa kita harus menggunakan fungsi ini? "Kembali lagi" tentunya adalah dengan tujuan menghemat waktu kerja kita dengan memaksimalkan fitur-fitur yang disediakan oleh Microsoft Office Excel.
Tentu bisa Anda bayangkan, apabila jumlah data nama barang lebih dari 1000 item harus kita warnai satu persatu kita warnai berdasarkan kriterianya?
Silahkan ikuti langkahnya sesuai gambar berikut:
➤Pertama: Block cell E2 hingga cell E15

Block cell E2 hingga cell E15

➤Kedua: klik ribbon/toolbar Home, lalu klik Conditional Formatting kemudian klik New Rule...

Ribbon/toolbar Home

New Conditional Formatting

➤Ketiga: klik "Format only cells that contain" kemudian pilih drop down list "Specific Text" kemudian pilih drop down berikutnya "Containing" seperti gambar di bawah.

Membuat conditional formatting baru

Kemudian ketik "Ready" tepat di kolom seperti pada gambar di bawah.

Membuat conditional formatting baru

➤Keempat: setelah tombol Format... seperti pada gambar di atas Anda klik, maka excel akan menampilkan menu Format Cells seperti gambar di bawah kemudian silahkan ikuti langkah-langkahnya sesuai nomor yang tertera:

Memberi Format Kondisi pada Cell

Setelah tombol OK Anda klik, maka kolom Keterangan dengan kriteria "Ready" akan berubah wana biru seperti pada gambar berikut:

Format cells yang telah diberi Conditional Formatting

Selanjutnya, untuk memberi format cell berwarna "Merah" pada tulisan "OutOfStock", silahkan lakukan langkah-langkah yang ExcelManiacs jelaskan di atas.

Langkah 4: Membuat Kolom "Cek Rows"

Fungsi dan tujuan dari kolom yang ExcelManiacs beri istilah dengan "Cek Rows" adalah sebagai kolom yang berisi formula/rumus kombinasi untuk menampilkan baris atau row dengan kriteria barang "Ready" saja sedangkan kriteria barang "OutOfStock" tidak tampil dalam kolom "Cek Rows".

Silahkan masukkan rumus pada cell "G2" lalu copy paste ke baris selanjutnya dengan menggunakan kombinasi rumus sebagai berikut:

=IF(OFFSET(B2;0;3)="Ready";ROW(A2)-1;"")

Membuat Kolom "Cek Rows"

Langkah 5: Membuat Kolom "Barang Ready"

Kolom Barang Ready

Kolom "Barang Ready" seperti di atas adalah kolom berisi rumus-rumus kombinasi yang berfungsi untuk memerintahkan excel mensortir atau menyusun dengan otomatis barang-barang yang tersedia sehingga tersusun rapi berurutan tanpa diselingi baris yang kosong seperti terlihat pada kolom "Cek Rows".

Langkah 6: Membuat Drop Down List Menggunakan Data Validasi

Silahkan klik cell "J2" lalu klik ribbon Data, kemudian klik Data Validation seperti gambar di bawah ini:

Membuat Drop Down List

Lalu Excel akan menampilkan jendela menu Data Validation seperti gambar berikut:

Menentukan List Data Validasi

Setelah tombol OK Anda klik, maka Membuat List Dinamis dengan Kriteria telah selesai Anda buat.

Untuk memeriksa apakah drop down list berfungsi dengan sempuna, silahkan klik tanda panah yang tampak di sebelah kanan cell "J2", maka akan terlihat seperti pada gambar di bawah.

Drop down List Dinamis dengan Kriteria Barang "Ready"

Tambahan: Cek Jumlah Barang Ready

Buatlah kolom pada cell "K1" dengan judul "Jumlah" yang berfungsi untuk mengetahui jumlah Barang Ready ketika Anda memilih Nama Barang yang dalam drop down list.

Kemudian silahkan masukkan rumus ke dalam cell "K2"sebagai berikut:

=IFERROR(VLOOKUP(J2;Stock_Barang!B:D;3;FALSE);"")

Rumus untuk mengetahui jumlah barang

Maka jumlah barang akan tampil dalam kolom "Jumlah" sesuai dengan Nama Barang yang Anda pilih pada drop down list.

Memeriksa jumlah barang

Agar lebih mudah untuk Anda pelajari, silahkan unduh file dengan nama "Dropdown List berKriteria.xlxs" di Google Drive.

Demikian tips dari ExcelManiacs, mudah-mudahan bermanfaat.

>>> Terima Kasih<<<