Konten
Selamat! Anda berhasil mencapai hari terakhir maraton 30 fungsi Excel dalam 30 hari. Ini adalah perjalanan yang panjang dan menarik di mana Anda telah mempelajari banyak hal berguna tentang fungsi Excel.
Pada hari ke-30 maraton, kami akan mengabdikan studi fungsi TIDAK LANGSUNG (INDIRECT), yang mengembalikan tautan yang ditentukan oleh string teks. Dengan fungsi ini, Anda dapat membuat daftar drop-down dependen. Misalnya, ketika memilih negara dari daftar dropdown menentukan opsi mana yang akan muncul di daftar dropdown kota.
Jadi, mari kita lihat lebih dekat bagian teoretis dari fungsi TIDAK LANGSUNG (TIDAK LANGSUNG) dan jelajahi contoh-contoh praktis penerapannya. Jika Anda memiliki informasi atau contoh tambahan, silakan bagikan di komentar.
Fungsi 30: TIDAK LANGSUNG
fungsi TIDAK LANGSUNG (INDIRECT) mengembalikan tautan yang ditentukan oleh string teks.
Bagaimana Anda bisa menggunakan fungsi INDIRECT?
Karena fungsi TIDAK LANGSUNG (INDIRECT) mengembalikan tautan yang diberikan oleh string teks, Anda dapat menggunakannya untuk:
- Buat tautan awal yang tidak bergeser.
- Buat referensi ke rentang bernama statis.
- Buat tautan menggunakan informasi sheet, baris, dan kolom.
- Buat larik angka yang tidak bergeser.
Sintaks INDIRECT (TIDAK LANGSUNG)
fungsi TIDAK LANGSUNG (INDIRECT) memiliki sintaks berikut:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_teks (link_to_cell) adalah teks tautan.
- a1 – jika sama dengan TRUE (TRUE) atau tidak ditentukan, maka gaya tautan yang akan digunakan A1; dan jika FALSE (FALSE), maka gayanya R1C1.
Perangkap TIDAK LANGSUNG (TIDAK LANGSUNG)
- fungsi TIDAK LANGSUNG (INDIRECT) dihitung ulang setiap kali nilai dalam lembar kerja Excel berubah. Ini bisa sangat memperlambat buku kerja Anda jika fungsi tersebut digunakan dalam banyak rumus.
- Jika fungsi TIDAK LANGSUNG (TIDAK LANGSUNG) membuat tautan ke buku kerja Excel lain, buku kerja itu harus dibuka atau rumus akan melaporkan kesalahan # REF! (#TAUTAN!).
- Jika fungsi TIDAK LANGSUNG (INDIRECT) mereferensikan rentang yang melebihi batas baris dan kolom, rumus akan melaporkan kesalahan # REF! (#TAUTAN!).
- fungsi TIDAK LANGSUNG (INDIRECT) tidak dapat mereferensikan rentang bernama dinamis.
Contoh 1: Buat tautan awal yang tidak bergeser
Pada contoh pertama, kolom C dan E berisi angka yang sama, jumlah mereka dihitung menggunakan fungsi JUMLAH (SUM) juga sama. Namun, formulanya sedikit berbeda. Di sel C8, rumusnya adalah:
=SUM(C2:C7)
=СУММ(C2:C7)
Di sel E8, fungsinya TIDAK LANGSUNG (TIDAK LANGSUNG) membuat tautan ke sel awal E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Jika Anda menyisipkan baris di bagian atas lembar dan menambahkan nilai untuk Januari (Jan), maka jumlah di kolom C tidak akan berubah. Rumus akan berubah, bereaksi terhadap penambahan garis:
=SUM(C3:C8)
=СУММ(C3:C8)
Namun, fungsi TIDAK LANGSUNG (INDIRECT) memperbaiki E2 sebagai sel awal, sehingga Januari secara otomatis dimasukkan dalam perhitungan total kolom E. Sel akhir telah berubah, tetapi sel awal tidak terpengaruh.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Contoh 2: Tautan ke rentang bernama statis
fungsi TIDAK LANGSUNG (INDIRECT) dapat membuat referensi ke rentang bernama. Dalam contoh ini, sel-sel biru membentuk rentang Daftar Angka. Selain itu, rentang dinamis juga dibuat dari nilai di kolom B Daftar AngkaDyn, tergantung pada jumlah angka dalam kolom ini.
Jumlah untuk kedua rentang dapat dihitung hanya dengan memberikan namanya sebagai argumen ke fungsi JUMLAH (SUM), seperti yang Anda lihat di sel E3 dan E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Alih-alih mengetikkan nama rentang ke dalam fungsi JUMLAH (SUM), Anda bisa merujuk ke nama yang tertulis di salah satu sel lembar kerja. Misalnya, jika nama Daftar Angka ditulis di sel D7, maka rumus di sel E7 akan menjadi seperti ini:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Sayangnya fungsinya TIDAK LANGSUNG (INDIRECT) tidak dapat membuat referensi rentang dinamis, jadi ketika Anda menyalin rumus ini ke dalam sel E8, Anda akan mendapatkan kesalahan # REF! (#TAUTAN!).
Contoh 3: Buat tautan menggunakan informasi sheet, baris, dan kolom
Anda dapat dengan mudah membuat tautan berdasarkan nomor baris dan kolom, serta menggunakan nilai FALSE (FALSE) untuk argumen fungsi kedua TIDAK LANGSUNG (TIDAK LANGSUNG). Beginilah cara tautan gaya dibuat R1C1. Dalam contoh ini, kami menambahkan nama sheet ke tautan – 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Contoh 4: Buat larik angka yang tidak bergeser
Terkadang Anda perlu menggunakan array angka dalam rumus Excel. Dalam contoh berikut, kita ingin merata-ratakan 3 angka terbesar di kolom B. Angka-angka tersebut dapat dimasukkan ke dalam rumus, seperti yang dilakukan di sel D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Jika Anda membutuhkan array yang lebih besar, maka Anda tidak mungkin ingin memasukkan semua angka dalam rumus. Opsi kedua adalah menggunakan fungsi BARIS (ROW), seperti yang dilakukan pada rumus array yang dimasukkan di sel D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Opsi ketiga adalah menggunakan fungsi BARIS (STRING) bersama dengan TIDAK LANGSUNG (TIDAK LANGSUNG), seperti yang dilakukan dengan rumus array di sel D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Hasil untuk ketiga formula akan sama:
Namun, jika baris disisipkan di bagian atas lembar, rumus kedua akan mengembalikan hasil yang salah karena fakta bahwa referensi dalam rumus akan berubah seiring dengan pergeseran baris. Sekarang, alih-alih rata-rata dari tiga angka terbesar, rumus mengembalikan rata-rata dari angka terbesar ke-3, ke-4, dan ke-5.
Menggunakan fungsi TIDAK LANGSUNG (TIDAK LANGSUNG), rumus ketiga menyimpan referensi baris yang benar dan terus menunjukkan hasil yang benar.