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:

Membuat List Dinamis Dengan Kriteria
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:
  1. Membuat Rumus Nomor Urut Otomatis
  2. Membuat Rumus Kriteria
  3. Membuat “Conditional Formatting”
  4. Membuat Kolom “Cek Rows”
  5. Membuat Kolom “Barang Ready”
  6. 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”.
Membuat List Dinamis Dengan Kriteria
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:
Membuat List Dinamis Dengan Kriteria
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 List Dinamis Dengan Kriteria
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

Membuat List Dinamis dengan Kriteria
Block cell E2 hingga cell E15

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

Membuat List Dinamis dengan Kriteria
Ribbon/toolbar Home
Membuat List Dinamis dengan Kriteria
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 List Dinamis dengan Kriteria
Membuat conditional formatting baru

Kemudian ketik “Ready” tepat di kolom seperti pada gambar di bawah.

Membuat List Dinamis dengan Kriteria
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:

Membuat List Dinamis dengan Kriteria
Memberi Format Kondisi pada Cell

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

Membuat List Dinamis dengan Kriteria
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 List Dinamis dengan Kriteria
Membuat Kolom “Cek Rows”

Langkah 5: Membuat Kolom “Barang Ready”

Membuat List Dinamis dengan Kriteria
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 List Dinamis dengan Kriteria
Membuat Drop Down List
Lalu Excel akan menampilkan jendela menu Data Validation seperti gambar berikut:
Membuat List Dinamis dengan Kriteria
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.
Membuat List Dinamis dengan Kriteria
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);””)

Membuat List Dinamis dengan Kriteria
Rumus untuk mengetahui jumlah barang
Maka jumlah barang akan tampil dalam kolom “Jumlah” sesuai dengan Nama Barang yang Anda pilih pada drop down list.
Membuat List Dinamis dengan Kriteria
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<<<


Source: Membuat List Dinamis dengan Kriteria

Tinggalkan Balasan