Cara Menggunakan Fungsi INDIRECT Excel


Mari kita bahas cara menggunakan fungsi INDIRECT Excel. Bayangkan sebuah fungsi Excel yang, dengan satu argumen, dapat menambahkan kecanggihan ke lembar kerja Anda dengan membiarkannya berperilaku dinamis.

Itu dapat mengambil instruksi dari sel input, memasukkannya ke dalam rumus dan menampilkan nilai atau perhitungan yang diperbarui seperti yang diminta oleh sel input. Itulah yang dilakukan fungsi INDIRECT Excel, dan ini adalah rahasia yang Anda rahasiakan.

Sintaksis

Mari kita mulai dengan dasar-dasarnya!

INDIRECT biasanya hanya membawa satu argumen, dan itu adalah referensi sel. Ada argumen kedua opsional di mana Anda bisa mengetik TRUE atau FALSE. BENAR adalah defaultnya. Jika Anda mengetik FALSE itu berarti Anda menggunakan gaya referensi sel R1C1.

=INDIRECT(ref_text, [a1])

Sebagian besar pengguna Excel akrab (dan cukup nyaman) dengan gaya A1 yang merujuk ke lokasi sel. Kami tidak akan terlalu banyak menjelaskan R1C1 di sini, tetapi setidaknya perlu penjelasan singkat.


gaya R1C1

Misalnya, dalam gaya referensi A1, sel B5 merujuk ke kolom kedua/baris kelima dari lembar bentang Excel. Tetapi konvensi penamaan sel R1C1 mengacu pada nomor baris terlebih dahulu, diikuti dengan nomor kolom.

Sel B5 akan disebut R5C2 menggunakan gaya R1C1. Dalam rumus, gaya R1C1 menggunakan referensi relatif dengan memberi tahu Excel seberapa jauh jarak sel yang direferensikan dari sel aktif. Misal seperti R[-1]C berarti nilai sel yang akan diproses adalah satu baris di atas, di kolom yang sama ini.

Itu R1C1 singkatnya. Kami berjanji untuk tidak merinci tentang cara menggunakannya sekarang. Itu untuk waktu dan tempat lain. Saat ini, kami akan berkonsentrasi pada gaya A1 agar tetap sederhana.

Bagaimana INDIRECT bekerja dengan referensi sel

Untuk membantu Anda memahami apa yang INDIRECT lakukan dan cara kerjanya, lihat lembar di bawah ini.

Fungsi tidak langsung ExcelBiasanya di Excel, kita tahu bahwa jika Anda mengetik EQUALS dan referensi sel, Excel hanya menyalin dan mengembalikan apa pun yang ada di sel itu. Di sel A5, kami mengetik =A3, dan seperti yang diharapkan, Excel hanya menyalin teks yang ada di sel A3 dan menampilkan A1.

Mari kita lihat bagaimana fungsi INDIRECT mengubahnya.

=INDIRECT(A3)

Fungsi tidak langsung ExcelSekarang sel A5 mengatakan “Hari ini adalah hari Senin.”

Fungsi INDIRECT menggunakan referensi sel dalam tanda kurung sebagai instruksi tentang apa yang harus dilakukan. Dalam hal ini, instruksi mengatakan, “Tampilkan sel nilai A3.”

Dan karena Excel mengetahui bahwa kita menggunakan gaya referensi A1 (argumen opsional adalah TRUE atau dihilangkan), rumus di sel A5 secara tidak langsung mengatakan =A1.

Baca Juga:  Cara Membuat Diagram Lingkaran di Excel

Sekarang setelah kita sampai sejauh ini, saya yakin Anda bertanya-tanya, “Itu bagus, tapi bagaimana ini berguna?”

Kami sedang menuju ke sana.

Menggunakan INDIRECT dengan rentang bernama

Pertama, mari kita gunakan fungsi INDIRECT dengan rentang bernama. Berikut ini contoh.

Pada lembar ini, kami memiliki angka penjualan bulanan dari tiga item produk. Tepat di bawah, kami ingin dapat memasukkan nama buah di sel B7. Dan kami ingin Excel melakukan fungsi SUM, AVERAGE, MIN, dan MAX dengan mengekstrak nilai yang benar dari kumpulan data A1 hingga M4 dan mengembalikan hasil perhitungan di lokasi masing-masing.

Fungsi tidak langsung ExcelIngat bahwa kita akan menginginkan INDIRECT untuk menemukan sel input, dalam hal ini, sel B7. Sel input itu, pada gilirannya, memberikan instruksi tentang referensi sel. Tapi B7 tidak akan berisi referensi sel. Itu akan berisi nama buah, apakah apel, mangga, atau pir.

Sekarang Anda mungkin telah menangkap. Kami dapat memberi nama rentang sel A2:M2 “Apel”, A3:M3 “Mangga” dan A4:M4 “Pear.”

Ini disebut membuat rentang bernama. Untuk melakukan ini, cukup pilih rentang yang akan diganti oleh nama yang Anda tetapkan, dan ketik nama yang diinginkan di Kotak Nama di kiri atas lembar kerja Excel (lihat di bawah).

Fungsi tidak langsung ExcelSetelah rentang bernama kami ditentukan, mereka sekarang akan bertindak seperti referensi sel, jadi mereka siap untuk digunakan dengan INDIRECT. Dan begitulah cara kita membuat sheet kita mulai berperilaku dinamis, tergantung pada apa yang diketik di B7.

Jadi jika kita ingin mencari jumlah semua penjualan mangga, kita akan memasukkan Mangga di B7 dan di B8, ketik:

=SUM(INDIRECT(B7))

Fungsi tidak langsung ExcelCara lain untuk memikirkan hal ini adalah dengan mengetik:

=SUM(Mangoes)

di sel B8.

Hasilnya akan sama, tetapi masalah dengan pendekatan itu adalah kita juga perlu memasukkan:

=AVERAGE(Mangoes)
=MIN(Mangoes)
=MAX(Mangoes)

Kita perlu mengetikkan keempat formula setiap kali kita ingin mendapatkan angka untuk setiap item produk. Saat kami menggunakan INDIRECT, kami menulis rumus =SUM(INDIRECT(B7) sekali, memberi tahu Excel untuk mencari referensi sel yang ditanyakan di B7.

Menggunakan INDIRECT untuk mengembalikan string teks

Inilah fakta yang menyenangkan. INDIRECT juga dapat digunakan untuk menyalin dengan tepat apa yang ada di dalam sel, alih-alih mengarahkan ke sel lain.

Di sel A5 di bawah, misalnya, memasukkan:

=INDIRECT("A3")

Fungsi tidak langsung Excelakan mengembalikan teks yang ditampilkan di sel A3 alih-alih mencari instruksi referensi sel lainnya di A3.

Baca Juga:  Tutorial Penyaringan Lanjut Excel VBA

Sekarang mengapa kita ingin melakukan itu, terutama karena kita bisa saja mengetik =A3 tanpa menggunakan INDIRECT sama sekali?

Nah, menempatkan tanda kutip ganda di sekitar argumen dalam fungsi INDIRECT mengatakan kepada Excel, “Perlakukan argumen ini sebagai string teks.”

Ini sekarang menjadikannya nilai tetap, sehingga rumus akan selalu menunjuk ke A3 apakah sel di sekitarnya dipindahkan, disisipkan, atau dihapus. Mengetahui hal ini berguna dalam situasi berikut.

Gunakan INDIRECT untuk merujuk ke lembar kerja yang berbeda

Kita dapat menggunakan prinsip ini untuk mendapatkan INDIRECT untuk membuat referensi lembar kerja dinamis.

Misalnya, jika kita memiliki data penjualan selama lima tahun terakhir, dengan setiap tahun berada di lembar terpisah dan memiliki format yang sama, kita dapat menulis satu rumus pada lembar kueri untuk mendapatkan informasi tentang tahun yang kita pilih.

Idenya mirip dengan contoh rentang bernama yang kita lihat di atas. Dengan memasukkan nama sheet, Excel mengetahui bahwa kita tidak mengacu pada lembar kerja yang aktif. Kita perlu menentukan lembar kerja mana yang kita cari untuk mengekstrak data tanpa hardcoding nama lembar kerja ke dalam rumus lembar kueri. Untuk melakukan ini, kita akan menggunakan INDIRECT.

Sel B1 akan menjadi sel input variabel. Nilai di sel B1 akan sesuai dengan nama lembar. Format Excel untuk merujuk ke nama lembar kerja adalah ‘Nama Lembar’!jadi

SUM('2016'!B2:M2)

akan menambahkan nilai dalam rentang B2 hingga M2 pada lembar 2016. Triknya adalah menemukan cara untuk menambahkan tanda seru ke akhir tahun di sel input sehingga Excel memahami bahwa nama lembar sedang dirujuk.

Ini membantu untuk mengingat bahwa rumus INDIRECT sedang mencari referensi sel untuk mengetahui apa yang harus dilakukan. Jika INDIRECT tidak mengenali argumen seperti itu, itu akan mengembalikan kesalahan.

Itu sebabnya kami akan memasukkan fungsi INDIRECT ke dalam rumus SUM, dan mengapit referensi non-sel dengan tanda kutip ganda. Kami juga membutuhkan operator penggabungan (&) untuk menggabungkan elemen teks ke referensi sel. Menerapkan ini, kami mendapatkan yang berikut:

=SUM(INDIRECT("'"&$B$1&"'!B2:M2"))

Excel tidak langsungSeperti yang Anda lihat di atas, elemen berikut diapit oleh tanda kutip ganda:

    • Kutipan tunggal untuk menunjukkan nama sheet
    • Tanda seru dan rentang sel

Selain itu, elemen-elemen ini telah digabungkan ke referensi B1 dengan ampersand di kedua sisinya.

Anda mungkin menunjukkan bahwa rumus juga akan berfungsi tanpa tanda kutip tunggal di sekitar nama lembar, dan itu benar. Saat nama lembar tidak berisi spasi, Excel hanya memerlukan tanda seru untuk mengidentifikasinya sebagai nama lembar.

Baca Juga:  Cara Menggabungkan Dan Membatalkan Pivot Dengan Wizard Tabel Pivot

Tetapi contoh di atas ditunjukkan jika Anda mengalami masalah ini nanti. Plus, tidak ada salahnya untuk menempatkan tanda kutip tunggal di sana setiap saat. Alih-alih hanya mencoba menghafal format, masuk akal untuk memahami mengapa hal itu dilakukan dengan cara tertentu sehingga Anda dapat terus menerapkan prinsip-prinsip ini saat setiap situasi muncul.

Gunakan INDIRECT untuk merujuk ke buku kerja yang berbeda

Kami juga menggunakan konsep di atas untuk merujuk ke buku kerja lain saat menggunakan fungsi INDIRECT. Format umum untuk merujuk ke rentang di buku kerja lain adalah

=[WorkbookName.xlsx]SheetName!range

jadi

=SUM('[New Workbook.xlsx]Sheet1'!D39:D43)

akan menambahkan nilai dalam rentang D39 hingga D43 di Buku Kerja BaruLembar1.

Karena kita sekarang ahli dengan fungsi INDIRECT, kita dapat mengganti referensi apa pun yang kita suka ke dalam rumus di atas dengan menunjuk sel input. Dalam contoh di bawah, sel B1 adalah sel input, dan akan berisi nama lembar di buku kerja baru.

=SUM(INDIRECT('[New Workbook.xlsx]”&B1&“'!D39:D43)

Dalam semua kasus, buku kerja yang dirujuk harus terbuka. Jika tidak, INDIRECT akan memberikan kesalahan #REF.

Gunakan INDIRECT untuk membuat daftar dropdown dependen

Aplikasi lain yang berguna dari fungsi INDIRECT adalah membuat daftar dropdown di mana opsi berubah tergantung pada apa yang dipilih dalam daftar dropdown sebelumnya.

Dalam contoh berikut, sel A2 memiliki daftar turun bawah yang berisi tiga nama departemen. Sel B2 berisi dropdown lain di mana nama karyawan berubah, tergantung pada departemen mana yang dipilih.

Fungsi tidak langsung ExcelFungsi tidak langsung ExcelMenyiapkan ini cukup sederhana.

    • Langkah 1: Dari tab Data di pita Excel, buat daftar validasi data di lokasi terpisah menggunakan nama departemen. Ini akan menjadi pilihan pilihan untuk sel A2 (lihat di bawah).

Fungsi tidak langsung Excel

    • Langkah 2: Buat rentang bernama menggunakan opsi daftar yang sama ini, masukkan nama karyawan untuk setiap departemen, seperti yang ditunjukkan di bawah ini.

Fungsi tidak langsung Excel

    • Langkah 3: Membuat daftar validasi data untuk sel B2 dengan rumus =INDIRECT(A2).

Fungsi tidak langsung ExcelNama karyawan sekarang akan berubah tergantung pada departemen yang dipilih di sel A2.

Belajarlah lagi

Apa pendapat Anda tentang fungsi INDIRECT Excel? Tugas apa yang akan Anda gunakan untuk menyederhanakannya? Suarakan dengan komentar di bawah!

Tinggalkan komentar