=INDIRECT(E2) membaca sel yang alamatnya tertulis sebagai teks di E2. Kalau E2 berisi C4, rumusnya menghasilkan nilai di C4. Alamatnya juga bisa disusun dari beberapa bagian: =INDIRECT("C"&E3) membaca kolom C di nomor baris yang ada di E3.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Address | Value | |
| 2 | Apple | Fruit | $1.20 | C4 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | 6 | $1.10 | |
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
F2 membaca C4, yaitu harga Carrot, 1,10. Ubah E3 menjadi 2 untuk harga Apple.
Sintaks INDIRECT
=INDIRECT(ref_text, [a1])
ref_text: teks yang menuliskan sebuah referensi:"C4","B2:B6","Prices!A2","'Price list'!A2:B9".a1:TRUEatau dikosongkan untuk alamat gaya A1.FALSEmembaca gaya R1C1, dengan"R4C3"berarti baris 4, kolom 3, yang cocok kalau baris dan kolomnya sama-sama berupa angka.
Kalau teksnya bukan alamat yang valid, hasilnya #REF!. INDIRECT menghasilkan referensi sungguhan, jadi bisa dipakai di dalam SUM, COUNTIF, VLOOKUP, dan setiap fungsi yang menerima rentang.
Membuat rentang dari angka
Alamatnya bisa berupa satu rentang utuh. Menggabungkan sebuah angka ke dalamnya menghasilkan rentang yang ukurannya ditentukan oleh sebuah sel.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Rows | Total | ||
| 2 | Jan | 4,200 | 3 | 12,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 |
Dengan 3 di E2, teksnya menjadi B2:B4, dan F2 menjumlahkan Jan sampai Mar: 12.900. Atur E2 menjadi 6 untuk setengah tahun, 27.900. 1+E2 ada di sana karena datanya dimulai di baris 2. Total yang sama bisa ditulis tanpa INDIRECT, =SUM(B2:INDEX(B2:B7,E2)), yang tidak bersifat volatile; halaman OFFSET membandingkan pilihan-pilihannya.
Merujuk ke sheet yang namanya ada di sebuah sel
Nama sheet juga bisa diambil dari sebuah sel. Dengan begitu, satu rumus ringkasan bisa menjadi pencarian di berbagai sheet: setiap baris membaca sheet yang namanya ada di kolom A. Tanda kutip tunggal di sekitar nama itu membuat rumus tetap berfungsi untuk nama yang mengandung spasi.
| A | B | |
|---|---|---|
| 1 | Month | Total |
| 2 | Jan | 12,500 |
| 3 | Feb | 12,200 |
| 4 | Mar | 13,700 |
B2 menyusun teks 'Jan'!B2:B4 lalu menjumlahkannya: 12.500. B3 dan B4 adalah rumus yang sama yang disalin ke bawah, jadi keduanya membaca Feb (12.200) dan Mar (13.700). Buka tab Feb dan ubah sebuah angka: ringkasannya ikut berubah. Ketik Feb menggantikan Jan di A2 dan B2 sekarang menjumlahkan Feb. B2:B4 di dalam tanda kutip adalah teks, jadi tidak berubah saat rumusnya disalin ke bawah; hanya referensi A2 yang berubah.
Drop down list bertingkat
Drop down kedua yang item-itemnya bergantung pada drop down pertama adalah pekerjaan klasik INDIRECT. Di Excel, pengaturannya biasanya seperti ini:
- Taruh item-item setiap kategori di satu kolom lalu beri nama setiap rentang sesuai kategorinya: pilih kolom-kolomnya beserta header-nya lalu pakai Formulas > Create from Selection > Top row. Ini membuat nama
Fruit,Vegetable, danDairy. - Beri A2 daftar kategorinya: Data > Data Validation > Allow: List, Source
Fruit,Vegetable,Dairy. - Beri B2 daftar dengan Source
=INDIRECT(A2). Saat A2 berisi Fruit, daftarnya membaca rentang yang bernama Fruit.
Lembar kerja di bawah membuat hal yang sama dengan satu sheet per kategori, bukan rentang bernama. D2 memakai INDIRECT untuk menampilkan item-item dari sheet yang namanya ada di A2 sebagai spill, dan daftar di B2 membaca D2:D4.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Item | Items for the category | |
| 2 | Fruit | Apple | Apple | |
| 3 | Pear | |||
| 4 | Plum |
Pilih Dairy di A2: D2:D4 berganti menjadi Milk, Butter, Cheese, begitu juga pilihan di B2. B2 tetap menyimpan nilai lamanya sampai kamu memilih yang baru; Excel berperilaku sama, dan karena itulah formulir sering menambahkan pengecekan seperti =COUNTIF(D2:D4,B2)>0 di samping item. Di Excel 365, kamu bisa melewati rentang bernama dan mengarahkan daftar kedua ke rumus spill, misalnya =INDIRECT("'"&A2&"'!A2:A4") di sel bantu dan =D2# sebagai Source. Halaman drop down list membahas sisa pengaturannya.
INDIRECT bersifat volatile, dan mengabaikan baris yang disisipkan
Ada dua efek samping karena INDIRECT membaca teks, bukan referensi:
- Dihitung ulang di setiap perubahan. Excel tidak bisa tahu sel mana yang akan ditunjuk oleh sebuah teks, jadi Excel menghitung ulang setiap INDIRECT setelah ada suntingan di mana pun di workbook. Beberapa puluh INDIRECT tidak masalah; puluhan ribu membuat setiap ketikan terasa lambat. INDEX dengan nomor baris (
=INDEX(C:C,E3)) memberikan hasil yang sama dengan=INDIRECT("C"&E3)dan hanya dihitung ulang saat input-nya berubah. - Alamatnya tidak bergeser. Sisipkan sebuah baris di atas baris 4 dan
=C4berubah menjadi=C5, tetapi=INDIRECT("C4")tetap membaca C4, yang sekarang adalah baris yang berbeda. Terkadang memang itu tujuannya, yaitu referensi yang harus tetap di satu sel apa pun yang terjadi pada lembar kerjanya. Lebih sering, itu adalah bug yang menunggu seseorang menyisipkan baris.
INDIRECT ke workbook lain hanya berfungsi selama workbook itu terbuka; kalau tertutup, hasilnya #REF!.
Latihan: harga dari nomor baris
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Row | Price | |
| 2 | Apple | Fruit | $1.20 | 5 | ||
| 3 | Pear | Fruit | $1.50 | |||
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
Giliranmu: Di F2, pakai INDIRECT untuk menghasilkan harga di kolom C pada nomor baris yang tertulis di E2.
Pertanyaan yang sering diajukan
Apa fungsi INDIRECT di Excel?
INDIRECT mengubah teks menjadi referensi. =INDIRECT("C4") menghasilkan nilai C4, dan =INDIRECT(E2) menghasilkan nilai dari alamat sel apa pun yang tertulis di E2. Alamatnya bisa disusun dengan &, jadi =INDIRECT("C"&E2) membaca kolom C di nomor baris yang ada di E2.
Bagaimana cara merujuk ke sheet lain yang namanya ada di sebuah sel?
Susun alamatnya dengan nama sheet di dalam tanda kutip tunggal: =INDIRECT("'"&A2&"'!B2"). Tanda kutipnya membuat rumus tetap berfungsi untuk nama yang mengandung spasi. =SUM(INDIRECT("'"&A2&"'!B2:B4")) menjumlahkan sebuah rentang di sheet tersebut.
Kenapa INDIRECT menghasilkan #REF!?
Teksnya bukan alamat yang valid, menyebut sheet yang tidak ada, atau menunjuk ke workbook lain yang sedang tertutup. Cek teks yang disusun rumusnya dengan menaruh ekspresi yang sama di sebuah sel tersendiri, tanpa INDIRECT.
Apakah INDIRECT bersifat volatile?
Ya. Excel menghitung ulang setiap INDIRECT setiap kali ada perubahan di mana pun di workbook, karena Excel tidak bisa tahu lebih dulu sel mana yang akan ditunjuk oleh teks itu. Beberapa INDIRECT tidak masalah; ribuan INDIRECT akan memperlambat workbook. INDEX sering bisa melakukan pekerjaan yang sama tanpa bersifat volatile.
