Fungsi INDIRECT di Excel

Fungsi TIDAK LANGSUNG Di Excel

Fungsi INDIRECT Excel secara tidak langsung mengacu pada sel, rentang sel, lembar kerja, dan buku kerja. Pertama, ia menerima string teks yang dimasukkan sebagai argumen dan mengubahnya menjadi alamat sel yang valid. Selanjutnya, ia pergi ke alamat sel ini dan mengembalikan nilainya.

Misalnya, pada gambar berikutnya, sel B1 berisi string teks ‘c4.’ Masukkan rumus ‘=INDIRECT(B1)’ di sel mana pun, misalnya D7. Rumus Excel INDIRECT ini berfungsi sebagai berikut:

Fungsi INDIRECT mengacu pada sel B1, yang terdiri dari ‘c4.’
Fungsi INDIRECT masuk ke sel C4 dan mengembalikan nilainya ‘Jessica’ di sel D7.

Dengan cara ini, string teks ‘c4’ diubah menjadi alamat sel C4. Kemudian, fungsi Excel INDIRECT menuju ke alamat sel ini (C4) dan mengembalikan nilainya (Jessica).

Fungsi INDIRECT memungkinkan pengguna menentukan alamat sel tidak langsung dalam bentuk string teks. Selain sel, fungsi ini juga bisa merujuk ke rentang bernama, buku kerja lain, dan lembar kerja dengan bantuan string teks.

Keuntungan menggunakan fungsi INDIRECT Excel adalah referensi tidak langsung tidak berubah dengan penyisipan atau penghapusan baris atau kolom baru. Selain itu, pengguna dapat mengubah string teks (alamat sel tidak langsung) dari sel yang dirujuk dalam rumus tanpa mengubah rumus INDIRECT itu sendiri.

Misalnya, dalam contoh sebelumnya, ketik ‘c2’ (tanpa tanda kutip ganda) di sel B1 dan pertahankan rumus ‘=INDIRECT(B1).’ Jadi, kali ini fungsi INDIRECT masuk ke sel C2 dan mengembalikan isinya. Oleh karena itu, string teks berubah daripada rumus Excel INDIRECT.

Menggunakan fungsi INDIRECT Excel mengonversi nilai teks menjadi alamat sel yang valid. Fungsi INDIRECT dikategorikan dalam fungsi LOOKUP dan REFERENCE Excel.

Sintaks Fungsi INDIRECT

Sintaks fungsi INDIRECT Excel ditunjukkan pada gambar berikut:

Fungsi menerima argumen berikut:

ref_text: Ini adalah alamat sel atau referensi selReferensi sel Referensi sel di excel merujuk sel lain ke sel untuk menggunakan nilai atau propertinya. Misalnya, jika kita memiliki data di sel A2 dan ingin menggunakannya di sel A1, gunakan =A2 di sel A1, dan ini akan menyalin nilai A2 di A1.baca lebih lanjut. Ini mengacu pada sel yang berisi string teks. String teks ini adalah alamat sel tidak langsung yang dapat kita tentukan dengan gaya A1 atau gaya R1C1. ‘Ref_text’ juga bisa merujuk ke sel yang berisi nama rentang bernama, buku kerja, atau lembar kerja.
a1: Ini adalah nilai logis (atau Boolean)–benar atau salah. Ini menentukan gaya referensi yang digunakan oleh argumen ‘ref_text’. Itu dapat mengambil salah satu dari nilai-nilai berikut:

a1	Jika true atau dihilangkan atau 1, argumen ‘ref_text’ dianggap bergaya A1.
a1	Ketika salah atau 0, argumen ‘ref_text’ dianggap bergaya R1C1.

Argumen ‘ref_text’ bersifat wajib sedangkan ‘a1’ bersifat opsional.

Catatan 1: Jika string teks dimasukkan dalam referensi gaya R1C1 (seperti r2c3), argumen ‘a1’ (sebagai 0 atau salah) wajib dimasukkan dalam rumus excel INDIRECT.

Catatan 2: Untuk informasi lebih lanjut tentang referensi gaya A1 dan gaya R1C1, lihat topik ‘apa perbedaan gaya A1 dengan referensi gaya R1C1?’ di akhir contoh #5.

Bagaimana Cara Menggunakan Fungsi INDIRECT di Excel?

Mari kita perhatikan beberapa contoh untuk memahami cara kerja fungsi INDIRECT di Excel.

Contoh #1–Referensi ke Rentang Bernama

Tabel berikut menampilkan jumlah pengunjung website www.xyz.com. Pengunjung ini telah dikategorikan dalam saluran yang berbeda. Setiap saluran tercantum dalam kolom ‘Cari Saluran’.

Selanjutnya, para pengunjung ini berasal dari lima negara: India, Amerika Serikat, Australia, Singapura, dan Kanada. Setiap negara diwakili oleh kolom masing-masing.

Kami ingin melakukan tugas-tugas berikut:

Hitung jumlah total pengunjung ke setiap negara menggunakan fungsi SUMFungsi SUMFungsi SUM di excel menambahkan nilai numerik dalam rentang sel. Dikategorikan di bawah fungsi Matematika dan Trigonometri, dimasukkan dengan mengetikkan ‘=SUM’ diikuti dengan nilai yang akan dijumlahkan. Nilai yang diberikan ke fungsi dapat berupa angka, referensi sel, atau rentang.Baca lebih lanjut.
Buat range bernama Range Bernama Di ExcelName range di Excel adalah nama yang diberikan ke range untuk referensi di masa mendatang. Untuk memberi nama rentang, pertama-tama pilih rentang data, lalu sisipkan tabel ke rentang tersebut, lalu beri nama rentang dari kotak nama di sisi kiri jendela.baca lebih lanjut untuk angka masing-masing rentang lima negara. Kita harus memanggil setiap rentang sesuai dengan negara yang diwakilinya. Kisaran ini harus mengecualikan total dan judul kolom.
Hitung jumlah total pengunjung dari Kanada dengan menggunakan rentang bernama dan fungsi INDIRECT Excel.
Terapkan validasi dataValidasi DataValidasi data di excel membantu mengontrol jenis input yang dimasukkan oleh pengguna di lembar kerja.baca lebih lanjut fitur Excel ke sel H4. Dengan mengetikkan nama negara di sel H4, sel I4 harus menunjukkan jumlah masing-masing. Gunakan rentang bernama dan fungsi INDIRECT Excel untuk hal yang sama.

Langkah-langkah untuk melakukan tugas yang diberikan tercantum sebagai berikut:

Langkah 1 : Pada baris 8, kita harus menghitung total pengunjung setiap negara dengan menggunakan fungsi SUM. Total kolom B, C, D, E, dan F ditunjukkan pada gambar berikutnya.

Buat rentang bernama untuk data setiap negara. Mari kita mulai dengan menamai rentang F3:F7 sebagai ‘Kanada.’ Untuk ini, lakukan tugas-tugas berikut:

sebuah. Pilih rentang F3:F7.

Di kotak nama, ketik ‘Kanada’ tanpa tanda kutip ganda. Kotak nama berada di sebelah kiri bilah rumus dan ditunjukkan dengan panah merah di gambar berikutnya .
Tekan tombol ‘Enter’.

Kisaran F3:F7 diberi nama ‘Kanada.’

Catatan: Nama rentang bernama tidak peka huruf besar-kecil. Ini menyiratkan bahwa jika rentang disebut ‘angka’, istilah ‘Gambar’, ‘angka’, dll. berarti sama dengan Excel.

Metode alternatif untuk membuat rentang bernama telah dibahas dalam petunjuk berikut (langkah 1a hingga langkah 1c).

Langkah 1a: Pilih rentang (F3:F7) untuk diberi nama.

Klik ‘Pengelola Nama’ dari tab ‘Rumus’ di pita Excel.
Klik ‘Baru’.

Langkah 1b: Kotak ‘Nama Baru’ terbuka. Selanjutnya, lakukan tugas berikut:

sebuah. Di kotak ‘Name’, ketik ‘Canada’ tanpa tanda kutip ganda.

Di ‘Mengacu pada’ rentang yang dipilih F3:F7 ditampilkan. Jika rentang ini tidak ditampilkan di kotak ‘Merujuk ke’, pilih rentang tersebut.
Klik ‘OK.’ Terakhir, tutup kotak dialog ‘Pengelola Nama’.

Rentang yang dipilih (F3:F7) diberi nama ‘Kanada.’ Pemilihan rentang ini telah ditunjukkan pada gambar berikut dan gambar di bawah langkah 1a.

Langkah 1c: Demikian juga, kita harus membuat rentang bernama untuk nomor dari kelima negara. Lima rentang bernama berjudul Australia (D3:D7), Kanada (F3:F7), India (B3:B7), Singapura (E3:E7), dan AS (C3:C7) telah dibuat.

Rentang bernama ditunjukkan pada gambar berikut.

Langkah 2: Untuk menghitung jumlah pengunjung dari Kanada, masukkan rumus berikut di sel I4.

‘=JUMLAH(TIDAK LANGSUNG(H4))’

Fungsi INDIRECT Excel merujuk ke sel H4, yang berisi nama ‘Kanada.’ Ini masuk ke rentang ‘Kanada’ (F3:F7) dan mengembalikan totalnya. Itu karena fungsi INDIRECT diapit oleh fungsi SUM.

Langkah 3: Sebelum menerapkan fitur validasi data, rentang bernama yang terdiri dari nama lima negara perlu dibuat. Untuk ini, lakukan tugas berikut dalam urutan yang diberikan:

sebuah. Pilih ‘Pengelola Nama’ dari tab “Rumus” dan klik ‘Baru.’ Kotak dialog ‘Nama Baru’ terbuka.

Pada kotak ‘Nama’, ketikkan ‘Negara’ (tanpa tanda kutip ganda), seperti yang ditunjukkan pada gambar berikut.
Di kotak ‘Merujuk ke’, pilih sel B2:F2. Klik ‘Oke.’
Tutup jendela ‘Pengelola Nama’.

Rentang bernama berjudul ‘Negara’ dibuat.

Langkah 4: Terapkan validasi dataValidasi DataValidasi data di excel membantu mengontrol jenis input yang dimasukkan oleh pengguna di lembar kerja. fitur baca lebih lanjut ke sel H4. Untuk ini, lakukan tugas berikut dalam urutan yang ditentukan:

sebuah. Pilih sel (H4) tempat kita harus menerapkan fitur validasi data.

Klik tarik-turun ‘Validasi Data’ dari tab “Data” Excel. Pilih ‘Validasi Data’.
Jendela ‘Validasi Data’ terbuka, seperti yang ditunjukkan pada gambar berikut. Di tab ‘Pengaturan’, pilih ‘Daftar’ dari tarik-turun ‘Izinkan’.
Di ‘Sumber’, ketik ‘=Negara’ tanpa tanda kutip ganda.
Pilih kotak centang ‘Abaikan kosong’ dan ‘Tarik-turun dalam sel ”.’ Klik ‘OK.’

Fitur validasi data telah diterapkan pada sel H4. Oleh karena itu, tergantung pada apakah kotak centang ‘in-cell dropdown’ dipilih atau tidak (pada langkah d), panah drop-down mungkin muncul atau tidak muncul di sebelah sel H4.

Panah drop-down memang muncul di sebelah sel H4, meski belum diperlihatkan pada gambar berikut. Panah drop-down ini (di sebelah sel H4) menunjukkan nama lima negara, India, AS, Australia, Singapura, dan Kanada.

Catatan: Fitur validasi data membatasi input yang dapat dimasukkan pengguna dalam sel lembar kerja. Ini memungkinkan pengguna untuk memasukkan hanya nilai-nilai yang ditentukan oleh aturan validasi data.

Langkah 5: Setelah fitur validasi data diterapkan, kita harus memasukkan nama salah satu dari lima negara (India, AS, Australia, Singapura, dan Kanada) di sel H4. Jumlah total pengunjung ke negara yang dipilih akan muncul di sel I4.

Untuk memasukkan nama negara di sel H4, seseorang dapat mengetikkan negara yang diinginkan atau memilihnya dari panah drop-down di sebelah sel H4.

Pesan kesalahan muncul jika input selain nama dari lima negara yang diberikan dimasukkan di sel H4. Pesan ini menyatakan bahwa batasan telah ditentukan untuk sel ini (H4).

Misalnya, masukkan ‘India’ di sel H4. Jumlah pengunjungnya (30.019) muncul di sel I4. Itu ditunjukkan pada gambar berikut.

Pada petunjuk berikut (langkah 5a hingga langkah 5b), hasil fitur validasi data diperiksa.

Langkah 5a: Masukkan ‘Australia’ di sel H4. Jumlah total pengunjung (30.336) muncul di sel I4. Hal yang sama ditunjukkan pada gambar berikut.

Langkah 5b: Masukkan ‘Australia’ di sel H4. Jumlah total pengunjung (30.336) muncul di sel I4. Hal yang sama ditunjukkan pada gambar berikut.

Oleh karena itu, perlu diperhatikan bahwa kami tidak mengubah rumus INDIRECT Excel sel I4 untuk mendapatkan total pengunjung dari berbagai negara. Kami hanya mengubah nama rentang di sel H4.

Jadi, kami mengubah konten sel yang dirujuk dalam rumus (H4) tanpa mengubah rumus itu sendiri (di sel I4).

Contoh #2–Referensi ke Lembar Kerja

Gambar berikutnya (gambar 1, gambar 2, dan gambar 3) menunjukkan pendapatan penjualan (dalam $) yang dihasilkan oleh berbagai saluran (di bawah ‘Cari Saluran’) di negara lain. Setiap saluran mewakili kategori pengunjung situs web www.xyz.com .

Selanjutnya, satu gambar telah dibuat dalam satu lembar kerja Excel. Akibatnya, setiap lembar kerja diberi nama oleh negara yang diwakilinya. Jadi, misalnya, lembar kerja yang memuat angka-angka India disebut ‘India.’

Total pendapatan penjualan dari lima negara (India, AS, Australia, Singapura, dan Kanada) telah dibagikan. Total ini telah dihitung dengan fungsi SUM Excel.

Kami ingin melakukan tugas-tugas berikut:

Hitung total pendapatan penjualan yang dihasilkan oleh masing-masing negara menggunakan fungsi INDIRECT dan SUM.
Periksa silang hasil fungsi INDIRECT di excel (terlampir dalam SUM) dengan fungsi SUM sederhana.

Total pendapatan penjualan Singapura dan Kanada masing-masing adalah $65.358 dan $20.600. Untuk negara-negara ini, asumsikan bahwa penjualan yang dihasilkan oleh saluran yang berbeda tercantum dalam rentang C2:C8 dari lembar kerja berjudul ‘Singapura’ dan ‘Kanada’. Sel C2 berisi keterangan ‘Penjualan’, dan sel C8 memiliki jumlah rentang C3:C7 (dihitung dengan fungsi SUM).

[Harap diperhatikan bahwa total pendapatan penjualan yang dihitung dengan fungsi SUM telah dibagikan untuk memungkinkan pemeriksaan silang hasil.]

Gambar 1

Total pendapatan penjualanPendapatan PenjualanPendapatan penjualan mengacu pada pendapatan yang dihasilkan oleh entitas bisnis mana pun dengan menjual barangnya atau menyediakan layanannya selama operasi normalnya. Dilaporkan setiap tahun, triwulanan, atau bulanan sesuai kasus dalam laporan laba rugi/laporan laba rugi entitas bisnis. Baca lebih lanjut (dalam $) India ditunjukkan pada gambar berikut:

[Pertimbangkan seluruh kolom ‘saluran penelusuran’ sebagai rentang B2:B8 lembar kerja ‘India.’ Kolom ‘penjualan’ berada dalam rentang C2:C8. Kisaran ini juga menyertakan judul kotak hijau.]

Gambar 2

Total pendapatan penjualan (dalam $) Amerika Serikat ditunjukkan pada gambar berikut:

[Pertimbangkan kolom ‘search channel’ sebagai rentang B2:B8 dari lembar kerja ‘USA.’ Kolom ‘penjualan’ berada dalam rentang C2:C8.]

Gambar 3

Total pendapatan penjualan (dalam $) Australia ditunjukkan pada gambar berikut:

[Pertimbangkan gambar ini sebagai bagian dari lembar kerja berjudul ‘Australia.’]

Langkah-langkah untuk menghitung total pendapatan penjualan dari berbagai negara dengan menggunakan fungsi INDIRECT Excel tercantum sebagai berikut:

Langkah 1: Untuk mengetahui total pendapatan penjualan yang dihasilkan oleh masing-masing negara, gabungkan (gabungkan) nama lembar kerja dan angka penjualan berdasarkan saluran (tercantum di kolom ‘penjualan’ pada gambar sebelumnya). Itu karena angka penjualan dari berbagai negara berada di lembar kerja yang terpisah.

Di sel C4, mulailah mengetik rumus INDIRECT Excel berikut.

‘=SUM(TINDAK LANGSUNG(B4’

Hal yang sama ditunjukkan pada gambar berikutnya.

Langkah 2: Lengkapi rumus sebelumnya (dimasukkan pada langkah 1) di sel C4.

‘=SUM(TIDAK LANGSUNG(B4&”!C3:C7))’

Hal yang sama ditunjukkan pada gambar berikut.

Argumen ‘ref_text’ (B4&’!C3:C7) dimulai dengan sel B4, yang berisi ‘India.’ ‘India’ adalah nama lembar kerja yang berisi gambar 1. Jadi, dalam argumen pertama fungsi INDIRECT, kita merujuk ke lembar kerja secara tidak langsung dengan namanya.

Selanjutnya, kami menggunakan operator ampersand (&) untuk menggabungkan nama sheet (India) di sel B4 dan rentang yang akan dijumlahkan (C3:C7) di gambar 1.

Langkah 3: Tekan tombol ‘Enter’. Outputnya muncul di sel C4, seperti yang ditunjukkan pada gambar berikut.

Karena fungsi INDIRECT di excel disertakan dalam fungsi SUM (lihat rumus yang dimasukkan pada langkah 2), jumlah rentang C3:C7 lembar kerja ‘India’ dikembalikan.

Oleh karena itu, total pendapatan sebesar $199.363 cocok dengan jumlah ‘SUM’ pada gambar 1. Ini menyiratkan bahwa hasil dari rumus sebelumnya (pada langkah 2) sama dengan hasil fungsi SUM sederhana (pada gambar kedua berikutnya).

Penerapan fungsi SUM ke rentang C3:C7 telah ditunjukkan pada gambar berikut untuk memungkinkan pemeriksaan silang hasil.

Dalam petunjuk berikut (langkah 3a sampai langkah 3c), total pendapatan penjualan dari berbagai negara diperiksa.

Langkah 3a: Untuk menghitung total pendapatan penjualan AS, lakukan tugas berikut:

Masukkan ‘USA’ (tanpa tanda kutip ganda) di sel B4.
Masukkan rumus ‘=SUM(INDIRECT(B4&’!C3:C7))’ di sel C4.
Tekan tombol ‘Enter’.

Dalam rumus INDIRECT Excel yang diberikan, nilai sel B4 (lembar kerja bernama ‘USA’) digabungkan dengan range (C3:C7 dari gambar 2) yang akan dijumlahkan.

Oleh karena itu, total pendapatan sebesar $1.030.984 muncul di sel C4. Angka ini sama dengan ‘jumlah’ jumlah gambar 2.

Langkah 3b: Masukkan ‘Singapura’ di sel B4. Rumus yang dimasukkan di sel C4 tetap sama seperti di langkah sebelumnya (langkah 3a).

Tekan tombol ‘Enter’. Keluaran $65.358 muncul di sel C4, seperti yang ditunjukkan pada gambar berikut.

Langkah 3c: Masukkan ‘Kanada’ di sel B4. Pertahankan rumus yang sama seperti langkah sebelumnya (langkah 3a).

Keluaran di sel C4 adalah $20.600.

Demikian pula, kita dapat menghitung total pendapatan penjualan Australia dengan memasukkan nama masing-masing di sel B4 dan mempertahankan rumus sel C4.

Oleh karena itu, dengan mengubah nama sheet (di sel B4) dan mempertahankan formula (di sel C4), kami memperoleh total pendapatan penjualan dari berbagai negara. Harap perhatikan bahwa kami tidak mengubah rumus INDIRECT (di sel C4).

Jadi, kami mengubah nilai argumen pertama, ‘ref_text’ (nama sheet berbeda), meskipun alamat sel rumus excel INDIRECT tetap sama (B4).

Contoh #3–Referensi ke Bulan Terakhir (R1C1-Style)

Gambar berikutnya (gambar 1, gambar 2, dan gambar 3) menunjukkan pendapatan penjualan (dalam $) yang dihasilkan oleh berbagai saluran (di bawah ‘saluran pencarian’) dalam tiga bulan pertama tahun 2018. Seluruh data terkait dengan situs web www .xyz.com.

Kami ingin melakukan tugas-tugas berikut:

Cari tahu total pendapatan penjualan bulan lalu (Maret) dengan menggunakan gaya referensi R1C1 dari fungsi INDIRECT di excel.
Tambahkan kolom kelima (kolom E) ke kumpulan data yang ada, yang berisi penjualan bulan April. Cari tahu total pendapatan penjualan bulan tambahan terakhir (April) dengan menggunakan gaya R1C1 dari fungsi INDIRECT.
Periksa ulang hasil rumus INDIRECT dengan total bulan masing-masing. Total ini telah dihitung dengan bantuan fungsi SUM Excel.

Angka bulan April adalah–$12.943 (pencarian organik), $7.279 (pengunjung langsung), $7.889 (pencarian berbayar), $10.327 (pengunjung sosial), $2.154 (rujukan), dan $40.592 (total).

Gambar 1

Pendapatan penjualan berdasarkan saluran (dalam $) untuk Januari, Februari, dan Maret ditunjukkan pada gambar berikut:

Gambar 2

Total pendapatan penjualan ($) untuk Januari, Februari, dan Maret ditunjukkan pada gambar berikut:

Gambar 3

Total pendapatan penjualan bulan Maret dan April dihitung dalam sel B13 (berisi tanda tanya) yang ditunjukkan pada gambar berikut:

Langkah-langkah untuk melakukan tugas yang diberikan tercantum sebagai berikut:

Langkah 1: Mulailah dengan memasukkan rumus berikut di sel B13.

‘=TIDAK LANGSUNG(“R10C”&COUNTA(10:10))’

Hal yang sama ditunjukkan pada gambar berikut.

Langkah 2: Selesaikan rumus sebelumnya (langkah 1) di sel B13.

‘=LANGSUNG(“R10C”&COUNTA(10:10),FALSE)’

Hal yang sama ditunjukkan pada gambar berikut.

Argumen pertama dari fungsi excel INDIRECT adalah ‘R10C’&COUNTA(10:10). Dalam argumen ini, kami telah menggabungkan R10C dan fungsi COUNTAFungsi COUNTAFungsi COUNTA adalah fungsi excel statistik bawaan yang menghitung jumlah sel yang tidak kosong (tidak kosong) dalam rentang sel atau referensi sel. Misalnya, sel A1 dan A3 berisi nilai, tetapi sel A2 kosong. Rumus ‘=COUNTA(A1,A2,A3)’ menghasilkan 2. baca selengkapnya dengan bantuan operator ‘&.’ Fungsi COUNTA menghitung jumlah total sel yang tidak kosong di baris 10. Ini mengembalikan 4.

Jadi, R10C digabungkan dengan 4 hasil di sel nomor R10C4. Karena argumen kedua fungsi INDIRECT adalah ‘FALSE’, R10C4 dikenali sebagai referensi gaya R1C1 oleh Excel.

Catatan: Untuk informasi lebih lanjut tentang referensi gaya R1C1, lihat topik ‘apa perbedaan gaya A1 dengan referensi gaya R1C1?’ di akhir contoh #5.

Langkah 3: Tekan tombol ‘Enter’. Karena argumen ‘ref_text’ mengevaluasi ke R10C4, rumus INDIRECT Excel mengembalikan nilai sel di persimpangan baris 10 dan kolom 4. Dalam gaya A1, ini adalah sel D10 Excel.

Oleh karena itu, outputnya adalah $249.344.

Langkah 4: Tambahkan angka penjualan untuk bulan April di kolom E dari kumpulan data yang ada. Masukkan rumus berikut di sel B13.

‘=LANGSUNG(“R10C”&COUNTA(10:10),FALSE)’

Argumen pertama dari fungsi Excel INDIRECT adalah ‘R10C’&COUNTA(10:10), dan argumen kedua adalah ‘FALSE’. Fungsi COUNTA mengembalikan nilai 5 karena ada lima sel yang tidak kosong di baris 10.

Ketika R10C digabungkan dengan 5, menghasilkan R10C5. R10C5 ini adalah referensi gaya R1C1 yang menyiratkan sel E10 (gaya A1). Nomor baris adalah 10, dan nomor kolom adalah 5 (kolom E).

Karenanya, rumus Excel INDIRECT mengembalikan nilai sel E10. Jadi, total pendapatan penjualan untuk bulan April adalah $40.592.

Demikian juga, kita dapat menambahkan data penjualan bulan-bulan yang tersisa ke dataset yang ada. Kemudian, dengan rumus INDIRECT yang sama (dimasukkan pada langkah 2 dan 4), kita dapat memperoleh total pendapatan penjualan bulan terakhir yang ditambahkan.

Dapat dilihat bahwa bahkan setelah menambahkan data untuk bulan April, rumus INDIRECT Excel tetap sama. Dengan kata lain, rumus INDIRECT yang kami gunakan untuk menghitung pendapatan penjualan bulan Maret sama dengan rumus bulan April.

Rumus INDIRECT tidak berubah karena menganggap ‘R10C’ dalam argumen ‘ref_text’ sebagai string teks.

Oleh karena itu, referensi tidak langsung tidak berubah dengan penyisipan atau penghapusan baris atau kolom baru di Excel.

Contoh #4–Referensi ke Array Tabel

Gambar berikut berisi lima larik tabel. Setiap larik menyertakan pendapatan penjualan yang dihasilkan dari berbagai saluran pengunjung (di bawah ‘saluran pencarian’) situs web (www.xyz.com).

Satu larik tabel mewakili data satu negara. Untuk lima negara, India, AS, Kanada, Australia, dan Singapura, susunan tabel masing-masing diberi nama ‘Ind’, ‘States’, ‘Can’, ‘Aus’, dan ‘Sin’.

Selain itu, seluruh rentang dari setiap larik tabel adalah rentang bernama. Nama range sama dengan nama array tabel. Misalnya, rentang D2:E7 disebut ‘Ind,’ G2:H7 dinamai ‘Negara Bagian’, dan seterusnya.

Kami ingin melakukan tugas-tugas berikut:

Dapatkan penjualan saluran ‘Pengunjung Langsung’ Singapura (di sel B3) dengan bantuan fungsi VLOOKUPFungsi VLOOKUPFungsi excel VLOOKUP mencari nilai tertentu dan mengembalikan kecocokan yang sesuai berdasarkan pengenal unik. Pengidentifikasi unik secara unik terkait dengan semua catatan database. Misalnya, ID karyawan, nomor daftar siswa, nomor kontak pelanggan, alamat email penjual, dll., adalah pengidentifikasi unik. baca lebih lanjut tentang Excel.
Dapatkan penjualan saluran ‘Pengunjung Langsung’ Singapura (di sel B3) dengan menggunakan kombinasi fungsi VLOOKUP dan INDIRECT.
Dapatkan penjualan saluran ‘Rujukan’ India (di sel B3) dengan menggunakan kombinasi fungsi VLOOKUP dan INDIRECT Excel.

Seseorang dapat mengecek ulang output dari pointer sebelumnya dengan angka dari array tabel masing-masing.

Langkah-langkah untuk melakukan tugas yang diberikan tercantum sebagai berikut:

Langkah 1: Untuk mendapatkan penjualan saluran ‘Pengunjung Langsung’ Singapura dengan fungsi VLOOKUP, lakukan tugas berikut:

sebuah. Masukkan ‘pengunjung langsung’ (tanpa tanda kutip ganda) di sel B1.

Pastikan rentang G10:H15 diberi nama ‘Sin.’ Setelah itu, masukkan ‘Sin’ di sel B2.
Masukkan rumus ‘=VLOOKUP(B1,B2,2,0)’ di sel B3.
Tekan tombol ‘Enter’.

Kesalahan ‘N/A’ keluaran muncul di sel B3, seperti yang ditunjukkan pada gambar berikut.

Error ‘N/A’ muncul karena kita salah memasukkan argumen ‘table_array’ (argumen kedua) sebagai ‘B2.’ Sayangnya, fungsi VLOOKUP tidak dapat mengenali argumen ini (B2). Argumen ‘table_array’ selalu dimasukkan sebagai rentang sel.

Oleh karena itu, argumen yang salah membuat rumus VLOOKUP menampilkan kesalahan.

Seandainya kita memasukkan rumus VLOOKUP sebagai: ‘=VLOOKUP(B1,G10:H15,2,0)’ di sel B3, hasilnya adalah $4.587.

Langkah 2: Untuk mendapatkan penjualan saluran ‘Pengunjung Langsung’ Singapura dengan kombinasi fungsi VLOOKUP dan INDIRECT, lakukan tugas berikut:

Masukkan ‘Pengunjung Langsung’ di sel B1 dan ‘Sin’ di sel B2. Rentang G10:H15 telah diberi nama ‘Sin’ di langkah sebelumnya (langkah 1).
Masukkan rumus ‘=VLOOKUP(B1,INDIRECT(B2),2,0)’ di sel B3, seperti yang ditunjukkan pada gambar berikut.

Argumen pertama, sel B1 adalah ‘lookup_value’ dari fungsi VLOOKUP. Jadi, fungsi ini mencari string ‘Direct Visitor’.

Fungsi excel INDIRECT mengacu pada sel B2, yang berisi ‘Sin.’ ‘Sin’ ini adalah argumen ‘table_array’ dari fungsi VLOOKUP. Fungsi VLOOKUP mencari string ‘pengunjung langsung’ dalam rentang ‘Sin’ (G10:H15).

Selanjutnya, argumen ‘col_index_num’ adalah 2. Jadi, fungsi VLOOKUP mencari nilai pencarian (pengunjung langsung) di kolom kedua rentang ‘Sin’.

Karena argumen keempat (range_lookup) adalah 0, rumus VLOOKUP yang diberikan mengembalikan pencocokan persis. Pencocokan persis adalah nilai yang sama dengan nilai pencarian.

Langkah 3: Tekan tombol ‘Enter’. Keluaran $4.587 muncul di sel B3. Keluaran ini cocok dengan nilai sel H12 yang merupakan bagian dari susunan tabel Singapura.

Oleh karena itu, rumus sebelumnya (dimasukkan pada langkah 2) telah mengembalikan penjualan saluran ‘Pengunjung Langsung’ Singapura.

Langkah 4: Untuk mendapatkan penjualan saluran ‘Rujukan’ India dengan bantuan fungsi VLOOKUP dan INDIRECT, lakukan tugas berikut dalam urutan yang disebutkan:

Masukkan ‘rujukan’ di sel B1 dan ‘Ind’ di sel B2.
Pastikan rentang D2:E7 diberi nama ‘Ind.’
Masukkan rumus ‘=VLOOKUP(B1,INDIRECT(B2),2,0)’ di sel B3.
Tekan tombol ‘Enter’.

Outputnya muncul di sel B3, seperti yang ditunjukkan pada gambar berikutnya.

Kami telah mengubah nilai sel B1 dan B2 menjadi ‘Rujukan’ dan ‘Ind’. Karena rentang bernama tidak peka huruf besar/kecil, ‘IND’ dan ‘Ind’ artinya sama untuk Excel.

Argumen pertama (lookup_value) dari VLOOKUP adalah ‘B1.’ Keluaran ‘INDIRECT(B2)’ berfungsi sebagai argumen ‘table_array’ dari fungsi VLOOKUP. Argumen ‘col_index_num’ adalah 2. Argumen ‘range_lookup’ adalah 0 atau salah (cocok persis).

VLOOKUP mencari nilai ‘rujukan’ dalam rentang bernama ‘Ind’ (D2:E7). Ia mengembalikan kecocokan persis dari kolom kedua rentang ‘Ind.’

Oleh karena itu, penjualan saluran ‘Rujukan’ India adalah $3.192. Output ini cocok dengan nilai sel E7. Sel ini adalah bagian dari susunan tabel negara India.