Lompat ke konten
Anda disini: M Jurnal » Excel Dasar » 4 Contoh Cara Menggunakan Rumus + Data Validation Excel

4 Contoh Cara Menggunakan Rumus + Data Validation Excel

Cara menggunakan data validation excel dengan Rumus TODAY, WEEKDAY, SUM, COUNTIF, AND, LEFT, RIGHT, ISNUMBER, VALUE

Panduan ini akan mengajarkan Anda contoh cara menggunakan Rumus pada Data Validation Excel. Sebelum Anda memulai, setidaknya Anda harus memahami dasar-dasar Data Validations terlebih dahulu. Lihat SUB-BAB Data Validations pada Pintasan Panduan

Data Validation + Fungsi TODAY dan WEEKDAY

Dengan menggabungkan Data Validation dan Fungsi TODAY atau WEEKDAY, Anda bisa membatasi pengisian cell dengan tanggal tertentu. Dengan cara ini, pengguna hanya bisa mengisi data kedalam cell sesuai tanggal yang memenuhi kriteria.

Untuk menggunakan Data Validation + Rumus Excel pada data berupa tanggal, Saya memiliki 2 contoh berbeda. Mari kita pelajari satu per satu.

#1 Data Validation + Fungsi TODAY

Misalnya, Saya ingin Range A2:A15 hanya bisa diisi dengan data berupa tanggal mulai dari tanggal 1 Januari 2020 sampai 5 Hari setelah tanggal hari ini.

Untuk tujuan tersebut, Anda bisa menggunakan Fungsi TODAY pada data validations. Silahkan ikuti tahap-tahap berikut:

Cara menggunakan data validation tanggal dengan Rumus TODAY excel
  1. Pilih (blok) semua Cell yang akan menggunakan Data Validation. Dalam contoh ini, Range A2:A15.
  2. Klik Tab Data pada Ribbon
  3. Klik Icon Data Validation
  4. Pada bagian Allow, pilih Date. Kemudian bagian Data pilih Between
  5. Start Date isi dengan tanggal pertama yang menjadi kriteria. Dalam contoh ini tanggal 01/01/2020
  6. Pada bagian End Date, masukkan Rumus TODAY seperti berikut: =TODAY()+5 kemudian klik Ok.

Sekarang coba kita buktikan. Silahkan ketik tanggal berapapun antara tanggal 01/01/2020 sampai 5 hari setelah tanggal hari ini pada cell A2 dan A3.

Misalnya tanggal 15/02/2020 dan 30/11/2020. Lihat hasilnya.

Cara menggunakan data validation tanggal dengan Rumus TODAY excel

Penting! Cara membuat Notice (Pesan Petunjuk Pengisian) sudah Saya jelaskan pada SUB-BAB Data Validation (Lihat Pintasan Panduan).

Sejauh ini, Excel tidak menunjukkan pesan error karena data yang kita isi kedalam cell sesuai kriteria.

Sekarang, coba masukkan tanggal berapapun sebelum tanggal 01/01/2020 pada Cell A4, misalnya tanggal 31/12/2019 dan lihat hasilnya.

Cara menggunakan data validation tanggal dengan Rumus TODAY excel

Excel secara otomatis menolak dan menunjukkan pesan kesalahan, karena tanggal tersebut tidak sesuai dengan kriteria.

Kemudian coba kita buktikan Fungsi TODAY + 5. Fungsi TODAY adalah fungsi dinamis yang secara otomatis menggunakan “Tanggal Hari Ini” saat Anda membuka File Excel.

Jadi, jika fungsi =TODAY()+5, maka 5 hari setelah tanggal hari ini bisa dimasukkan kedalam cell.

Note: Panduan ini Saya buat pada Tanggal 12/03/2021. 5 hari setelah tanggal 12 maret adalah 17 Maret 2021. Jadi saat ini, Saya tidak bisa memasukkan tanggal setelah 17/03/2021 ke dalam cell. Misalnya, Saya coba masukkan tanggal 18/03/2021, ternyata Excel menolak dan memberikan pesan kesalahan.

diskon-hosting

Cara menggunakan data validation tanggal dengan Rumus TODAY excel

Namun, jika Anda membuka file Excel pada tanggal 15/03/2021, maka Anda bisa memasukkan data hingga tanggal 20/05/2021.

Disinilah pentingnya Anda memahami penggunaan Fungsi TODAY.

#2 Data Validation + Fungsi WEEKDAY

Fungsi WEEKDAY secara default mengelompokkan hari menjadi angka 1 sampai 7, yang mana angka 1 adalah untuk Minggu, 2 untuk hari Senin, hingga 7 untuk hari Sabtu.

Nah dengan fungsi WEEKDAY ini, Anda bisa mengecualikan tanggal pada hari-hari tertentu agar tidak bisa dimasukkan ke dalam Range Data.

Misalnya, Saya hanya ingin Range A2:A15 hanya bisa diisi dengan tanggal hari kerja (Senin – Jum’at). Untuk tujuan tersebut, Anda hanya perlu mengecualikan hari Sabtu dan Minggu pada data validation menggunakan Fungsi AND + WEEKDAY Excel seperti berikut:

Cara menggunakan data validation tanggal dengan Rumus WEEKDAY excel
  1. Pilih (blok) Range A2:A15.
  2. Buka Window Data Validation (Note: ulangi tahap 2 dan 3 diatas)
  3. Pada bagian Allow, pilih Custom
  4. Masukkan gabungan Rumus AND dan WEEKDAY pada bagian Formula: =AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7) kemudian klik Ok untuk melanjutkan.

Sedikit Saya jelaskan tentang Fungsi AND dan WEEKDAY yang digunakan.

Tulis Rumus / Fungsi AND + WEEKDAY:
=
AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7)

Artinya:
ISI TRUE jika Hari pada Cell A2 tidak sama dengan Minggu dan Hari pada Cell A2 tidak sama dengan Sabtu

Penting! Ingat, pada Fungsi WEEKDAY, 1 = Minggu, 2 = Senin, 3 = Selasa, 4 = Rabu, 5 = Kamis, 6 = Jum’at, dan 7 = Sabtu. Selain itu, karena kita menggunakan 2 Fungsi WEEKDAY, maka diperlukan bantuan Fungsi AND. Selengkapnya tentang penggunaan Fungsi AND, bisa Anda pelajari di Panduan Fungsi Logika Excel.

Dengan demikian, ketika Anda mengisi tanggal (haris Sabtu / Minggu), maka Excel secara otomatis menolak dan memberikan pesan kesalahan.

Coba kita buktikan, silahkan isi tanggal 14/03/2021 (ini tanggal hari minggu), sudah pasti Excel menolak dan memberikan pesan kesalahan.

Cara menggunakan data validation tanggal dengan Rumus WEEKDAY excel

Data Validation + Fungsi SUM

Contoh ini lebih dikhususkan jika data Anda menggunakan Format Angka Valid. Perhatikan, setidaknya Anda harus memahami Format Cell khususnya Format Number untuk memasukkan angka Valid. Jika belum tahu, silahkan kunjungi Panduan Format Cell Excel

Selanjutnya, Saya memiliki contoh cara menggunakan data validation Excel untuk “Anggaran Pesta Ulang Tahun” agar tidak terjadi kesalahan input data melebihi batas anggaran (terjadi selisih).

Membatasi pengisian data berdasarkan total nilai

Perhatikan, Cell C9 adalah Total dari semua list (Range C2:C8). Sedangkan Cell C11 = Budget – Total. Saat ini, Cell C3 (Hiburan) masih kosong sehingga selisih Budget – Total = Rp 200,000.00. Kemudian, ketika pengguna memasukkan 400,000 pada cell C3, tentu akan terjadi selisih negatif sebesar -Rp 200,000.00.

Membatasi Pengisian data berdasarkan total nilai

Saya tidak ingin kesalahan ini terjadi dan hanya ingin pengguna hanya bisa meng-input data dengan total maksimal sesuai Bugdet (anggaran) yaitu Rp 1,000,000.00. Untuk tujuan tersebut, Anda bisa menggabungkan Data Validation dengan Fungsi SUM. Silahkan ikuti tahap-tahap berikut:

Cara menggunakan data validation tanggal dengan Rumus SUM excel
  1. Pilih (blok) list data yaitu range C2:C8
  2. Buka Window Data Validation seperti cara sebelumnya
  3. Pada bagian Allow, pilih Custom
  4. Pada bagian Formula, masukkan Fungsi SUM berikut: =SUM($C$2:$C$8)<=1000000 kemudian klik Ok untuk melanjutkan.

Penting! Saya menggunakan Absolute Reference (lambang $) pada range target =SUM($C$2:$C$8)<=1000000. Sebelum Anda menggunakan trik ini, setidaknya pahami terlebih dahulu penggunaan Absolute Reference Excel. Selengkapnya kunjungi Panduan Cell Reference Excel

Sementara itu, dengan operator perbandingan logika “<=” pada rumus =SUM($C$2:$C$8)<=1000000, maka Excel akan memastikan jumlah data pada Range C2:C8 lebih kecil dari atau sama dengan 1000000 (1 juta).

Sehingga pada contoh ini, ketika Total lebih besar dari Budget, maka Excel akan menolak dan menunjukkan pesan kesalahan.

Cara menggunakan data validation tanggal dengan Rumus SUM excel

Sebelum Anda menggunakan trik ini, sebaiknya pahami terlebih dahulu Operator Perbandingan Logika pada Rumus Excel. Selengkapnya kunjungi Panduan Operator Logika Excel

Data Validation + Fungsi COUNTIF

Fungsi COUNTIF pada Data Validation bisa Anda gunakan untuk mencegah kesalahan pengimputan data duplikat (ganda). Dengan trik ini, Anda bisa mencegah kesalahan duplikat data sedini mungkin. Beda ceritanya jika data Anda sudah terdapat nilai duplikat.

Penting! Jika Anda ingin mencari dan memberikan tanda pada data duplikat, silahkan kunjungi Panduan Mencari Data Duplikat. Jika ingin menghapus data duplikat, silahkan kunjungi Panduan Remove Duplicate Data.

Untuk mencegah input data duplikat menggunakan Data Validation + Fungsi COUNTIF, silahkan perhatikan contoh. Misalnya, Range A2:A10 hanya boleh diisi dengan ID Produk unik dan tidak boleh duplikat.

Untuk tujuan tersebut, silahkan pilih (blok) range A2:A10 kemudian buka Window Data Validation seperti cara sebelumnya.

Cara menggunakan data validation tanggal dengan Rumus COUNTIF excel

Pada bagian Allow, pilih Custom kemudian ketik (secara manual) Fungsi COUNTIF berikut: =COUNTIF($A$2:$A$10,A2)=1 dan klik Ok.

Perhatikan bahwa Fungsi COUNTIF membutuhkan 2 argument =COUNTIF($A$2:$A$10,A2)=1. Dengan demikian, Excel akan menghitung banyak nilai pada range A2:A10 yang sama dengan Cell A2. Selengkapnya tentang fungsi COUNTIF silahkan kunjungi Panduan Fungsi COUNTIF.

Selain itu, Saya menggunakan kriteria =1 pada rumus =COUNTIF($A$2:$A$10,A2)=1 karena tujuan Saya mencegah input data duplikat.

Dengan demikian, Excel hanya mengizinkan penginputan data yang sama pada Range A2:A10 sebanyak 1x dan tidak lebih.

Selanjutnya coba kita buktikan apakah Excel benar-benar mencegah input data duplikat. Cell A2 berisi ID Produk R1001. Kemudian, ketik kembali ID Produk tersebut ke Cell A7 dan lihat hasilnya…

Mencegah input data duplikat

Benar bukan ? Excel menolak karena ID Produk tersebut sudah ada dalam Range data yang menggunakan data validation.

Data Validation + Fungsi AND, LEFT, RIGHT, LEN, ISNUMBER, VALUE

Contoh ini sangat cocok jika data Anda memiliki pola tertentu seperti ID Produk / Nomor Induk Karyawan dll. Saya berikan contoh untuk ID Produk.

Cara menggunakan data validation tanggal dengan Rumus AND, LEFT, LEN, ISNUMBER, VALUE dan RIGHT excel

Coba perhatikan, ID Produk yang benar selalu di awali dengan huruf R, kemudian diakhiri dengan 4 digit angka.

Untuk mencegah kesalahan input ID Produk (seperti Cell A5, A6, dan A7), Anda bisa menggunakan gabungan Fungsi AND, LEFT, RIGHT, LEN, ISNUMBER, dan VALUE.

Cara menggunakan data validation tanggal dengan Rumus AND, LEFT, LEN, ISNUMBER, VALUE dan RIGHT excel

Silahkan pilih Custom pada bagian Allow, kemudian ketik Fungsi berikut pada bagian Formula: =AND(LEFT(A2)="R",LEN(A2)=5,ISNUMBER(VALUE(RIGHT(A2,4)))) dan klik Ok untuk melanjutkan.

Penjelasan: Fungsi AND pada contoh ini menggunakan 3 argument yaitu dari Fungsi LEFT, LEN dan ISNUMBER. Kemudian, setiap fungsi (LEFT, LEN, ISNUMBER, VALUE, dan RIGHT memiliki tugas masing-masing sebagai berikut:

FungsiKegunaan
LEFT(A2)=”R”Memvalidasi agar karakter pertama diawali dengan Huruf R
LEFT(A2)=5Memastikan pengguna hanya boleh memasukkan data dengan jumlah karakter = 5. Seperti R1001, ada 1 huruf (R) dan 4 Angka (1001) = 5 karakter.
ISNUMBER(VALUE(RIGHT(A2,4)))Bertujuan untuk memvalidasi agar pengguna memasukkan 4 digit angka setelah huruf R. Dimulai dari Fungsi RIGHT(A2,4) akan mengambil 4 karakter terakhir (misal R1001). Kemudian Fungsi VALUE akan mengonversi 1001 dari R1001 menjadi angka (R1001 merupakan data teks). Terakhir, Fungsi ISNUMBER akan memeriksa apakah data tersebut (1001) merupakan angka.
Fungsi ANDApabila semua kriteria pada Fungsi AND terpenuhi, maka Excel memberikan hasil TRUE. Sebaliknya FALSE jika tidak terpenuhi.

Sehingga dengan cara data validation ini, Excel akan mengkonfirmasi bahwa pengguna hanya bisa input data yang diawali dengan huruf R, dengan total semua karakter = 5 yang diakhiri dengan 4 karakter berupa angka.

Coba kita buktikan…

Silahkan masukkan ID Produk dengan format berbeda, misalnya M1001 atau R12 atau R-1001 dan hasilnya, Excel akan menolak dan memberikan pesan kesalahan.

Membatasi pengisian ID Produk

Mudah bukan ? Lalu apa selanjutnya ? Silahkan tambah skill Anda dalam menggunakan Excel melalui Pintasan Panduan Data Validations Excel berikut. Atau kunjungi Pusat Panduan Excel M Jurnal.

Pintasan Panduan Data Validation Excel

Bab sebelumnya: Template Excel.

  1. Data Validations: Untuk mengisi Cell dengan pilihan khusus (Contoh: Cell B2 hanya bisa diisi dengan angka 1, 2, 3, atau 4).
  2. Function + Data Validation: (Anda Disini).
  3. Drop-Down List: Membuat Data Validations menggunakan Drop-Down List.
  4. Dependent Drop-Down Lists: Contoh: jika Cell B1 di isi Rabu, maka Cell B2 hanya bisa di isi Siang atau Sore. Jika Cell B1 di isi Kamis, maka Cell B2 hanya bisa di isi Pagi atau Sore, dsb..

Bab berikutnya: Keyboard Shortcut.

2 tanggapan pada “4 Contoh Cara Menggunakan Rumus + Data Validation Excel”

  1. bang mau nanya dong.
    A1:A52 berisi tanggal (dd mmm yy)
    di kolom D1 mau diisi salah satu tanggal dari A1:A52 melalui data validation (drop down list)

    bagaimana caranya agar di D1 format tanggal dd mmmm yyyy dalam bahasa Indonesia (settingan komputer US) dan nama bulannya uppercase?

    mohon pencerahannya bang.
    terima kasih.

  2. Mau nanya dong, kalo mau bikin kriteria tertentu untuk input data dan pengen mencegah duplikat juga, itu rumusnya apa bisa digabung? Atau gimana caranya ya?

Komentar Anda:

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *