Cara Menggunakan Rentang Dinamis di Excel Dengan COUNTIF dan INDIRECT

2024 Pengarang: Abigail Brown | [email protected]. Terakhir diubah: 2023-12-17 06:51

Yang Perlu Diketahui

• Fungsi INDIRECT mengubah rentang referensi sel dalam rumus tanpa mengedit rumus.
• Gunakan INDIRECT sebagai argumen untuk COUNTIF untuk membuat rentang dinamis sel yang memenuhi kriteria yang ditentukan.
• Kriteria ditetapkan oleh fungsi INDIRECT, dan hanya sel yang memenuhi kriteria yang dihitung.

Artikel ini menjelaskan cara menggunakan fungsi INDIRECT dalam rumus Excel untuk mengubah rentang referensi sel yang digunakan dalam rumus tanpa harus mengedit rumus itu sendiri. Ini memastikan bahwa sel yang sama digunakan, bahkan saat spreadsheet Anda berubah. Informasi berlaku untuk Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel untuk Mac, dan Excel Online.

Gunakan Rentang Dinamis Dengan Rumus COUNTIF - INDIRECT

Fungsi INDIRECT dapat digunakan dengan sejumlah fungsi yang menerima referensi sel sebagai argumen, seperti fungsi SUM dan COUNTIF.

Menggunakan INDIRECT sebagai argumen untuk COUNTIF membuat rentang dinamis referensi sel yang dapat dihitung oleh fungsi jika nilai sel memenuhi kriteria. Ini dilakukan dengan mengubah data teks, kadang-kadang disebut sebagai string teks, menjadi referensi sel.

Contoh ini didasarkan pada data yang ditunjukkan pada gambar di atas. Rumus COUNTIF - INDIRECT yang dibuat dalam tutorial adalah:

=COUNTIF(INDIRECT(E1&":"&E2), ">10")

Dalam rumus ini, argumen untuk fungsi INDIRECT berisi:

• Referensi sel E1 dan E2, yang berisi data teks D1 dan D6.
• Operator rentang, titik dua (:) dikelilingi oleh tanda kutip ganda (" ") yang mengubah titik dua menjadi teks string.
• Dua ampersand (&) yang digunakan untuk menggabungkan, atau bergabung bersama, titik dua dengan referensi sel E1 dan E2.

Hasilnya adalah INDIRECT mengubah string teks D1:D6 menjadi referensi sel dan meneruskannya ke fungsi COUNTIF untuk dihitung jika sel referensi lebih besar dari 10.

Fungsi INDIRECT menerima input teks apa pun. Ini bisa berupa sel di lembar kerja yang berisi teks atau referensi sel teks yang dimasukkan langsung ke dalam fungsi.

Mengubah Rentang Rumus Secara Dinamis

Ingat, tujuannya adalah membuat rumus dengan rentang dinamis. Rentang dinamis dapat diubah tanpa mengedit rumus itu sendiri.

Dengan mengubah data teks yang terletak di sel E1 dan E2, dari D1 dan D6 ke D3 dan D7, rentang yang dijumlahkan oleh fungsi dapat dengan mudah diubah dari D1:D6 ke D3:D7. Ini menghilangkan kebutuhan untuk mengedit rumus secara langsung di sel G1.

Fungsi COUNTIF dalam contoh ini hanya menghitung sel yang berisi angka jika lebih besar dari 10. Meskipun empat dari lima sel dalam rentang D1:D6 berisi data, hanya tiga sel yang berisi angka. Sel yang kosong atau berisi data teks diabaikan oleh fungsi.

Menghitung Teks Dengan COUNTIF

Fungsi COUNTIF tidak terbatas pada penghitungan data numerik. Itu juga menghitung sel yang berisi teks dengan memeriksa apakah mereka cocok dengan teks tertentu.

Untuk melakukannya, rumus berikut dimasukkan di sel G2:

=COUNTIF(INDIRECT(E1&":"&E2), "dua")

Dalam rumus ini, fungsi INDIRECT mereferensikan sel B1 ke B6. Fungsi COUNTIF menjumlahkan jumlah sel yang memiliki nilai teks dua di dalamnya.

Dalam hal ini, hasilnya adalah 1.

COUNTA, COUNTBLANK, dan INDIRECT

Dua fungsi penghitungan Excel lainnya adalah COUNTA, yang menghitung sel yang berisi semua jenis data sambil mengabaikan hanya sel kosong atau kosong, dan COUNTBLANK, yang menghitung hanya sel kosong atau kosong dalam suatu rentang.

Karena kedua fungsi ini memiliki sintaks yang mirip dengan fungsi COUNTIF, keduanya dapat disubstitusikan ke dalam contoh di atas dengan INDIRECT untuk membuat rumus berikut:

=COUNTA(INDIRECT(E1&":"&E2))
=COUNTBLANK(INDIRECT(E1&":"&E2)
Untuk rentang D1:D6, COUNTA mengembalikan jawaban 4, karena empat dari lima sel berisi data. COUNTBLANK mengembalikan jawaban 1 karena hanya ada satu sel kosong dalam rentang.

Mengapa Menggunakan Fungsi TIDAK LANGSUNG?
Manfaat menggunakan fungsi INDIRECT di semua rumus ini adalah sel baru dapat disisipkan di mana saja dalam rentang.
Rentang berubah secara dinamis di dalam berbagai fungsi, dan hasilnya diperbarui sesuai.





Tanpa fungsi INDIRECT, setiap fungsi perlu diedit untuk memasukkan semua 7 sel, termasuk yang baru.
Manfaat fungsi INDIRECT adalah nilai teks dapat dimasukkan sebagai referensi sel dan memperbarui rentang secara dinamis setiap kali spreadsheet Anda berubah.
Ini membuat perawatan spreadsheet secara keseluruhan menjadi lebih mudah, terutama untuk spreadsheet yang sangat besar.