Gunakan 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 ketika spreadsheet Anda berubah.
Catatan: Petunjuk dalam artikel ini berlaku untuk Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel untuk Mac, dan Excel Online.
Gunakan Rentang Dinamis dengan rumus COUNTIF - TIDAK LANGSUNG
Fungsi INDIRECT dapat digunakan dengan sejumlah fungsi yang menerima referensi sel sebagai argumen, seperti fungsi SUM dan COUNTIF.
Menggunakan INDIRECT sebagai argumen untuk COUNTIF menciptakan rentang dinamis referensi sel yang dapat dihitung oleh fungsi jika nilai sel memenuhi kriteria. Ia melakukan ini dengan mengubah data teks, kadang-kadang disebut sebagai string teks, menjadi referensi sel.
Contoh ini didasarkan pada data yang ditunjukkan pada gambar di atas. COUNTIF - rumus TIDAK LANGSUNG yang dibuat dalam tutorial adalah:
= COUNTIF (TIDAK LANGSUNG (E1 & ":" & E2), "> 10")
Dalam rumus ini, argumen untuk fungsi TIDAK LANGSUNG berisi:
- Referensi sel E1 dan E2, yang berisi data teks D1 dan D6.
- Operator jangkauan, titik dua (:) dikelilingi oleh tanda kutip ganda (' ') yang mengubah usus besar menjadi string teks.
- Dua ampersand (&) yang digunakan untuk menggabungkan, atau bergabung bersama, titik dua dengan referensi sel E1 dan E2.
Hasilnya adalah bahwa TIDAK LANGSUNG mengubah string teks D1: D6 menjadi referensi sel dan meneruskannya ke fungsi COUNTIF untuk dihitung jika sel yang direferensikan lebih besar dari 10.
Fungsi TIDAK LANGSUNG menerima input teks apa pun. Ini dapat berupa sel di lembar kerja yang berisi referensi teks atau sel teks yang dimasukkan langsung ke fungsi.
Dinamis Ubah Rentang Formula
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, kisaran yang dijumlahkan oleh fungsi dapat dengan mudah diubah dari D1: D6 ke D3: D7. Ini menghilangkan kebutuhan untuk langsung mengedit rumus di sel G1.
Fungsi COUNTIF dalam contoh ini hanya menghitung sel yang mengandung 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. Ini juga menghitung sel yang berisi teks dengan memeriksa apakah mereka cocok dengan teks tertentu.
Untuk melakukan ini, rumus berikut dimasukkan di sel G2:
= COUNTIF (TIDAK LANGSUNG (E1 & ":" & E2), "dua")
Dalam rumus ini, fungsi INDIRECT merujuk 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 TIDAK LANGSUNG
Dua fungsi penghitungan Excel lainnya adalah COUNTA, yang menghitung sel yang berisi semua jenis data sementara mengabaikan hanya sel kosong atau kosong, dan COUNTBLANK, yang hanya menghitung sel kosong atau kosong dalam rentang.
Karena kedua fungsi ini memiliki sintaks yang mirip dengan fungsi COUNTIF, fungsi ini dapat digantikan dengan contoh di atas dengan TIDAK LANGSUNG untuk membuat rumus berikut:
= COUNTA (TIDAK LANGSUNG (E1 & ":" & E2))= COUNTBLANK (TIDAK LANGSUNG (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. Manfaat menggunakan fungsi INDIRECT dalam semua rumus ini adalah bahwa sel-sel baru dapat disisipkan di mana saja dalam rentang. Kisaran dinamis bergeser ke dalam berbagai fungsi, dan hasilnya diperbarui sesuai. Tanpa fungsi TIDAK LANGSUNG, setiap fungsi perlu diedit untuk memasukkan semua 7 sel, termasuk yang baru. Manfaat fungsi INDIRECT adalah bahwa nilai teks dapat disisipkan sebagai referensi sel dan diperbarui secara dinamis setiap kali spreadsheet Anda berubah. Hal ini membuat pemeliharaan spreadsheet secara keseluruhan menjadi lebih mudah, terutama untuk spreadsheet yang sangat besar. Mengapa Menggunakan Fungsi INDIRECT?
