Gunakan fungsi INDIRECT dalam formula Excel untuk mengubah julat rujukan sel yang digunakan dalam formula tanpa perlu mengedit formula itu sendiri. Ini memastikan bahawa sel yang sama digunakan, walaupun perubahan spreadsheet anda.
Catatan: Arahan dalam artikel ini diguna pakai untuk Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel untuk Mac, dan Excel Online.
Gunakan Julat Dinamik dengan formula COUNTIF - INDIRECT
Fungsi INDIRECT boleh digunakan dengan beberapa fungsi yang menerima rujukan sel sebagai hujah, seperti fungsi SUM dan COUNTIF.
Menggunakan INDIRECT sebagai hujah untuk COUNTIF mencipta pelbagai rujukan dinamik sel yang boleh dikira oleh fungsi jika nilai sel memenuhi kriteria. Ia melakukan ini dengan mengubah data teks, kadang-kadang dirujuk sebagai rentetan teks, ke dalam rujukan sel.
Contoh ini didasarkan pada data yang ditunjukkan dalam imej di atas. COUNTIF - formula INDIRECT yang dicipta dalam tutorial adalah:
= COUNTIF (TIDAK LANGSUNG (E1 & ":" & E2), "> 10")
Dalam formula ini, argumen untuk fungsi INDIRECT mengandungi:
- Rujukan sel E1 dan E2, yang mengandungi data teks D1 dan D6.
- Operator rangkaian, kolon (:) dikelilingi oleh tanda petikan berganda (' ') yang menjadikan kolon menjadi rentetan teks.
- Dua ampersands (&) yang digunakan untuk menggabungkan, atau bergabung bersama, kolon dengan rujukan sel E1 dan E2.
Hasilnya adalah bahawa INDIRECT menukarkan rentetan teks D1: D6 ke dalam rujukan sel dan lulus selaras dengan fungsi COUNTIF untuk dikira jika sel yang dirujuk adalah lebih besar daripada 10.
Fungsi INDIRECT menerima sebarang input teks. Ini boleh menjadi sel dalam lembaran kerja yang mengandungi teks atau rujukan teks teks yang dimasukkan terus ke dalam fungsi.
Secara dinamik Tukar Julat Formula
Ingat, matlamatnya adalah untuk mencipta formula dengan pelbagai dinamik. Rentang dinamik boleh diubah tanpa menyunting formula itu sendiri.
Dengan mengubah data teks yang terdapat di dalam sel-sel E1 dan E2, dari D1 dan D6 hingga D3 dan D7, julat yang disumbangkan oleh fungsi ini boleh dengan mudah diubah dari D1: D6 hingga D3: D7. Ini menghapuskan keperluan untuk secara langsung mengedit formula dalam sel G1.
Fungsi COUNTIF dalam contoh ini hanya mengira sel mengandungi nombor jika mereka lebih besar daripada 10. Walaupun empat daripada lima sel dalam julat D1: D6 mengandungi data, hanya tiga sel mengandungi nombor. Sel yang kosong atau mengandungi data teks diabaikan oleh fungsi tersebut.
Mengira Teks dengan COUNTIF
Fungsi COUNTIF tidak terhad kepada mengira data angka. Ia juga mengira sel yang mengandungi teks dengan memeriksa jika ia sepadan dengan teks tertentu.
Untuk melakukan ini, formula berikut dimasukkan dalam sel G2:
= COUNTIF (TIDAK LANGSUNG (E1 & ":" & E2), "dua")
Dalam formula ini, fungsi INDIRECT merujuk sel B1 hingga B6. Fungsi COUNTIF merangkumi bilangan sel yang mempunyai nilai teks dua di dalamnya.
Dalam kes ini, hasilnya ialah 1.
COUNTA, COUNTBLANK, dan TIDAK LANGSUNG
Dua fungsi kiraan Excel lain adalah COUNTA, yang menghitung sel yang mengandungi sebarang jenis data sambil mengabaikan hanya kosong atau sel kosong, dan COUNTBLANK, yang hanya mengira sel kosong atau kosong dalam julat.
Oleh kerana kedua-dua fungsi ini mempunyai sintaks yang serupa dengan fungsi COUNTIF, mereka boleh digantikan dengan contoh di atas dengan INDIRECT untuk mencipta formula berikut:
= COUNTA (TIDAK LANGSUNG (E1 & ":" & E2))= COUNTBLANK (TIDAK LANGSUNG (E1 & ":" & E2)
Untuk julat D1: D6, COUNTA kembali jawapan 4, kerana empat daripada lima sel mengandungi data. COUNTBLANK mengembalikan jawapan 1 kerana terdapat hanya satu sel kosong dalam julatnya. Manfaat menggunakan fungsi INDIRECT dalam semua formula ini adalah bahawa sel-sel baru boleh dimasukkan di mana-mana julat. Julat ini dinamik di dalam pelbagai fungsi, dan hasilnya dikemas kini dengan sewajarnya. Tanpa fungsi INDIRECT, setiap fungsi perlu diedit untuk memasukkan semua 7 sel, termasuk yang baru. Manfaat fungsi INDIRECT adalah bahawa nilai teks boleh dimasukkan sebagai rujukan sel dan ia secara dinamik mengemas kini julat setiap kali spreadsheet anda berubah. Ini menjadikan penyelenggaraan spreadsheet keseluruhan lebih mudah, terutama untuk spreadsheet yang sangat besar. Mengapa Menggunakan Fungsi TIDAK LANGSUNG?
