Menyiapkan Rentang Kriteria

Excel memiliki sekumpulan fungsi basis data yang namanya selalu diawali huruf D: DSUM, DCOUNT, DAVERAGE, DGET, dan lainnya.

Fungsi-fungsi ini bekerja pada tabel yang disusun seperti basis data kecil: satu baris judul kolom di bagian atas, dengan catatan di bawahnya. Alih-alih mengetikkan kondisi di dalam rumus, Anda mengarahkan setiap fungsi D ke rentang kriteria terpisah di lembar kerja yang menjelaskan hal-hal yang harus dicocokkan.

Pelajaran ini berfokus pada cara membuat rentang kriteria dengan benar, karena setiap fungsi D bergantung padanya.

Setiap fungsi D memiliki tiga argumen yang sama:

• database - seluruh tabel, termasuk baris judulnya.
• field - kolom yang akan digunakan (nama judul dalam tanda kutip atau nomor kolom).
• criteria - rentang yang berisi aturan pencocokan Anda.

Jadi, bentuknya selalu DSUM(database, field, criteria). Argumen criteria adalah bagian yang sering keliru bagi pemula, jadi kita akan membahasnya terlebih dahulu.

Rentang kriteria hanyalah sekumpulan kecil sel dengan setidaknya dua baris:

• Baris teratas berisi judul kolom yang sama persis dengan judul pada basis data.
• Baris di bawahnya berisi kondisi.

Bayangkan data penjualan dengan judul Region, Rep, Amount. Untuk mencocokkan hanya wilayah East, rentang kriterianya terdiri dari dua sel yang ditumpuk: Region di atas dan East di bawahnya.

Judul dalam rentang kriteria Anda harus dieja secara identik dengan judul basis data. Jika kolom data Anda bernama Amount, tetapi kriteria Anda berisi Amounts atau amt, fungsi D tidak akan menemukan kolom tersebut dan mungkin menghasilkan error atau nol.

Kebiasaan paling aman adalah menyalin sel judul dari tabel lalu menempelkannya ke rentang kriteria. Dengan begitu, kecocokan teks dijamin sama persis, termasuk spasi di bagian akhir.

Misalkan A1:C13 berisi tabel dengan judul Region, Rep, Amount. Di sel E1, ketik Region, lalu di E2, ketik East. Blok dua sel tersebut, E1:E2, adalah rentang kriteria Anda.

Sekarang DSUM hanya menjumlahkan baris East. Fungsi ini membaca judul di E1, melihat bahwa judul tersebut cocok dengan kolom Region, lalu hanya mempertahankan baris yang Region-nya sama dengan East.

Secara default, kondisi teks seperti East mencocokkan nilai yang diawali teks tersebut. Jadi, East juga akan mencocokkan Eastern.

Untuk memaksa kecocokan yang persis, bungkus teks tersebut dengan perbandingan bergaya rumus: ="=East" yang diketikkan ke sel kriteria. Anda juga dapat menggunakan karakter pengganti: E* mencocokkan apa pun yang diawali E, sedangkan ?at mencocokkan Cat, Bat, atau Hat.

Kriteria tidak terbatas pada teks. Anda dapat menggunakan operator perbandingan untuk angka:

• >1000 mencocokkan jumlah di atas 1000.
• <=50 mencocokkan nilai 50 atau kurang.
• <>0 mencocokkan apa pun yang bukan nol.

Letakkan judul (misalnya Amount) di atas, lalu teks perbandingan di bawahnya. Fungsi D mengevaluasi nilai setiap catatan berdasarkan aturan tersebut.

Kondisi yang ditempatkan berdampingan pada baris yang sama digabungkan dengan AND - semuanya harus benar.

Untuk mencocokkan wilayah East dengan Amount di atas 1000, buat rentang kriteria dua kolom: judul Region dan Amount di baris teratas, lalu East dan >1000 di baris bawahnya. Catatan hanya lolos jika berada di East dan nilainya di atas 1000.

Kondisi yang ditempatkan pada baris terpisah digabungkan dengan OR - baris mana pun yang cocok akan memenuhi syarat.

Untuk mencocokkan East atau West, letakkan judul Region di atas, lalu East pada baris berikutnya dan West pada baris setelahnya. Rentang kriteria kini mencakup tiga baris, dan sebuah catatan lolos jika cocok dengan salah satu nilai tersebut.

Ingatlah untuk menyertakan semua baris tersebut dalam argumen criteria.

Anda dapat menggabungkan kedua susunan tersebut. Misalnya, Anda menginginkan (East AND >1000) OR (West AND >500).

Gunakan dua kolom, Region dan Amount. Letakkan East bersama >1000 pada satu baris, lalu West bersama >500 pada baris berikutnya. Setiap baris adalah kelompok AND; baris-baris terpisah berfungsi sebagai OR. Susunan kisi ini adalah cara fungsi D menyatakan logika yang kompleks tanpa menumpuk fungsi.

Waspadai kesalahan berikut:

• Lupa menyertakan baris judul - rentang kriteria memerlukan judul, bukan hanya kondisi.
• Memilih baris kosong di dalam rentang kriteria - baris kondisi kosong akan cocok dengan setiap catatan dan mengembalikan semuanya.
• Salah ketik pada judul sehingga tidak lagi cocok dengan tabel.
• Membiarkan blok kriteria menyentuh tabel data sehingga keduanya tumpang tindih.

Simpan rentang kriteria di area lembar kerja yang terpisah dan jelas.

Rentang kriteria adalah inti dari setiap fungsi D. Poin-poin penting:

• Rentang ini memerlukan baris judul yang namanya sama persis dengan nama pada tabel.
• Kondisi ditempatkan pada baris di bawahnya - berupa teks, karakter pengganti, atau perbandingan seperti >1000.
• Baris yang sama = AND, baris terpisah = OR.
• Hindari baris kondisi kosong karena baris tersebut akan cocok dengan semuanya.

Setelah rentang kriteria yang rapi siap, Anda dapat menggunakannya dalam DSUM, DCOUNT, DAVERAGE, dan DGET pada pelajaran berikutnya.