Fungsi INDEX di Excel

Fungsi Indeks di Excel

Fungsi INDEX di Excel membantu mengekstraksi nilai sel, yang berada dalam larik (rentang) tertentu dan, di persimpangan nomor baris dan kolom yang disebutkan. Dengan kata lain, fungsi menuju ke sel (dalam rentang tertentu) yang posisinya ditentukan, mengambil nilainya, dan mengembalikannya sebagai output. Fungsi INDEX juga dapat mengekstrak array nilai dari kumpulan data.

Misalnya, lembar kerja berisi nama benua dan negara terkait. Entri, diketik di Excel (tanpa tanda kutip ganda), terdaftar sebagai berikut:

Kolom A

Sel A1 berisi ‘Asia.’
Sel A2 berisi ‘Afrika.’
Sel A3 berisi ‘Eropa.’
Sel A4 berisi ‘Amerika Utara.’

Kolom B

Sel B1 berisi ‘India.’
Sel B2 berisi ‘Nigeria.’
Sel B3 berisi ‘Prancis.’
Sel B4 berisi ‘Kanada.’

Masukkan rumus ‘=INDEX(A1:B4,3,2)’ di sel C1. Tekan tombol ‘Enter’ dan hasilnya adalah ‘Prancis’ (tanpa tanda kutip ganda).

Pertama, fungsi excel INDEX masuk ke rentang A1:B4. Dari rentang ini, ia mengambil nilai sel, yang berada di persimpangan baris ketiga (baris 3) dan kolom kedua (kolom B). Sel ini adalah B3 dan nilainya adalah ‘Prancis.’

Fungsi INDEX dapat mengembalikan nilai berikut:

Nilai di persimpangan nomor baris dan kolom yang ditentukan
Nilai dalam tabel atau rentang bernama yang nomor baris dan kolomnya ditentukan
Nilai dalam rentang yang tidak berdekatan yang nomor baris dan kolomnya ditentukan

Fungsi INDEX di Excel berguna ketika kumpulan data besar dan seseorang mengetahui posisi sel dari mana nilai perlu diekstraksi.

Fungsi INDEX dikategorikan di bawah fungsi Pencarian dan Referensi Excel.

Anda bebas menggunakan gambar ini di situs web Anda, templat, dll., Harap berikan kami tautan atribusiBagaimana Memberikan Atribusi? Tautan Artikel menjadi Hyperlink
Misalnya: Sumber: Fungsi INDEX di Excel (wallstreetmojo.com)

Sintaks Fungsi INDEX di Excel

Ada dua bentuk fungsi INDEX di excel, array dan referensi. Sintaks kedua bentuk ditunjukkan pada gambar berikut.

Mari kita bahas kedua bentuk fungsi INDEX Excel satu per satu.

Bentuk Array dari Fungsi INDEX Excel

Dalam bentuk larik, fungsi INDEX mengambil nilai sel di dalam larik atau tabel. Array dapat berupa satu rentang sel (horizontal atau vertikal) atau beberapa rentang yang berdekatan. Nilai sel, yang berada di persimpangan nomor baris dan kolom yang disediakan, dikembalikan.

Fungsi INDEX menerima argumen berikut dalam bentuk array:

Larik: Ini bisa berupa rentang sel, tabel, konstanta array, atau nama rentang Nama Rentang Nama rentang di Excel adalah nama yang diberikan ke rentang untuk referensi di masa mendatang. Untuk memberi nama rentang, pertama-tama pilih rentang data lalu sisipkan tabel ke rentang tersebut, lalu beri nama rentang tersebut dari kotak nama di sisi kiri jendela.baca lebih lanjut. Sel yang nilainya akan dikembalikan harus berada dalam array ini.
Row_num: Ini adalah nomor baris dari array dari mana nilai akan dikembalikan.
Column_num: Ini adalah nomor kolom dari array dari mana nilai akan dikembalikan.

Argumen ‘array’ dan ‘row_num’ adalah wajib, sedangkan argumen ‘column_num’ adalah opsional.

Penting untuk menentukan ‘nomor_baris’ atau ‘nomor_kolom’ untuk mengekstrak nilai dari larik satu dimensi. Untuk mengekstrak nilai dari larik dua dimensi, seseorang perlu menentukan keduanya, ‘nomor_baris’ dan ‘nomor_kolom.’

Catatan 1: Array satu dimensi terdiri dari satu baris atau satu kolom. Array dua dimensi terdiri dari banyak baris dan kolom.

Catatan 2: Jika ‘nomor_baris’ dan ‘nomor_kolom’ keduanya dimasukkan sebagai nol (atau kosong), kode ‘#VALUE!’ kesalahan dikembalikan. Misalnya, rumus ‘=INDEX(A1:A4,0,0)’ dan ‘=INDEX(A1:A4,,)’ mengembalikan ‘#VALUE!’ kesalahan.

Catatan 3: Jika argumen ‘larik’ terdiri dari satu kolom, argumen ‘nomor_kolom’ dapat dihilangkan, tetapi ‘nomor_baris’ harus disertakan untuk mengambil nilai. Demikian pula, jika argumen ‘array’ terdiri dari satu baris, argumen ‘nomor_baris’ dapat dihilangkan, tetapi ‘nomor_kolom’ harus ditentukan untuk mengambil nilainya.

Misalnya, dalam kasus satu kolom, gunakan rumus ‘=INDEX(array,row_num)’ untuk mengambil nilai. Dalam kasus satu baris, gunakan rumus ‘=INDEX(array,,column_num)’ untuk mengambil nilai.

Formulir Referensi Fungsi INDEX Excel

Dalam formulir referensi, fungsi INDEX mengambil nilai sel dalam area yang ditentukan (larik atau rentang). Mungkin ada beberapa area yang tidak berdekatan dalam formulir referensi. Dalam kasus seperti itu, area tertentu, di mana fungsi INDEX harus mencari nilainya, dapat ditentukan.

Fungsi INDEX di Excel menerima argumen berikut dalam formulir referensi:

Referensi: Ini adalah area yang dapat berupa rentang tunggal atau beberapa rentang yang tidak berdekatan. Dalam kasus beberapa rentang yang tidak berdekatan, gunakan koma untuk memisahkan rentang. Selain itu, semua rentang harus diapit dalam tanda kurung. Misalnya, argumen ‘referensi’, (A1:C2, C4:D7) menyiratkan dua area (larik atau rentang), A1:C2 dan C4:D7.
Row_num: Ini adalah nomor baris area dari mana nilai akan dikembalikan.
Column_num: Ini adalah nomor kolom area dari mana nilai akan dikembalikan.
Area_num: Angka ini menunjukkan area (argumen ‘referensi’) di mana fungsi INDEX akan mencari nilai. Area pertama dari argumen ‘referensi’ diberi nomor 1. Area kedua diberi nomor 2 dan seterusnya. Misalnya, jika argumen ‘referensi’ adalah (A1:C2, C4:D7, F4:H8) dan ‘area_num’ adalah 3, fungsi INDEX mencari nilai di area ketiga dari argumen ‘referensi’, yaitu , F4:H8.

Argumen ‘reference’ dan ‘row_num’ diperlukan, sedangkan argumen ‘column_num’ dan ‘area_num’ bersifat opsional.

Dalam gambar berikut, kotak abu-abu (ditunjuk oleh panah merah) menunjukkan area yang berbeda (larik atau rentang) dari argumen referensi. Selanjutnya, nomor area masing-masing rentang juga diberikan.

Catatan 1: Jika argumen ‘area_num’ dihilangkan, fungsi INDEX excel mencari nilai di area pertama argumen ‘referensi’.

Catatan 2: Semua area yang dinyatakan dalam argumen ‘referensi’ harus ditempatkan di satu lembar kerja. Jika area pertama ada di satu lembar kerja dan yang kedua ada di lembar kerja lain, fungsi INDEX mengembalikan ‘#VALUE!’ kesalahan.

Catatan 3: Perbedaan antara larik dan formulir referensi adalah bahwa yang pertama, rentang yang berdekatan digunakan, sedangkan yang kedua, rentang yang tidak berdekatan digunakan.

Bagaimana cara menggunakan Fungsi INDEX di Excel?

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

Contoh #1–Bentuk Larik Dengan Larik Satu Dimensi

Gambar berikut menunjukkan jumlah karyawan (kolom C) yang bekerja di berbagai departemen (kolom B) dari suatu organisasi.

Kami ingin mengekstrak nilai sel C7 dengan bantuan fungsi INDEX. Gunakan formulir array dengan kolom C sebagai array tunggal.

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

Langkah 1: Masukkan rumus berikut di sel E2.

‘=INDEKS(C3:C7,5)’

Langkah 2: Tekan tombol ‘Enter’. Keluaran di sel E2 adalah 4.

Penjelasan: Dalam rumus yang dimasukkan pada langkah 1, kami menghilangkan argumen ‘column_num’ karena array (C3:C7) adalah satu kolom. Sel C7 berada di baris kelima larik C3:C7. Jadi, argumen ‘row_num’ dimasukkan sebagai 5.

Oleh karena itu, rumus INDEX yang diberikan menghasilkan 4, yang merupakan nilai baris 5 dari rentang C3:C7.

Contoh #2–Bentuk Larik Dengan Larik Dua Dimensi

Gambar berikut menunjukkan jumlah karyawan yang bekerja di lima departemen perusahaan multinasional Perusahaan Multinasional Perusahaan multinasional (MNC) didefinisikan sebagai badan usaha yang beroperasi di negara asalnya dan juga memiliki cabang di luar negeri. Kantor pusat biasanya tetap berada di satu negara, mengendalikan dan mengkoordinasikan semua cabang internasional. baca lebih lanjut (MNC). Angka tersebut terhitung selama empat tahun, yakni 2015-2018.

Kami ingin mengekstraksi nilai sel E5 dengan bantuan fungsi INDEX excel. Gunakan formulir larik dengan seluruh kumpulan data (kecuali judul di baris 2) sebagai larik.

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

Langkah 1: Masukkan rumus berikut di sel B9.

‘=INDEKS(B3:F7,3,4)’

Langkah 2: Tekan tombol ‘Enter’. Keluaran di sel B9 adalah 629.

Penjelasan: Dalam rumus sebelumnya (dimasukkan pada langkah 1), banyak baris dan kolom dari kumpulan data dimasukkan sebagai larik tunggal (B3:F7). Rumus INDEX mengembalikan nilai sel, yang berada di persimpangan baris 3 dan kolom 4 dari rentang B3:F7. Sel pada posisi ini adalah sel E5 dan nilainya 629.

Oleh karena itu, keluarannya, 629, sesuai dengan baris ketiga dan kolom keempat dari rentang B3:F7.

Contoh #3–Bentuk Larik Dengan Nomor Baris dan/atau Kolom sebagai Nol

Bekerja pada data contoh #2, kami ingin menerapkan fungsi INDEX ke seluruh dataset (B3:F7). Selanjutnya, amati output dalam kasus berikut:

Kasus 1: Ketika kedua argumen ‘baris_nomor’ dan ‘kolom_nomor’ adalah nol

Kasus 2: Bila ‘nomor_baris’ atau ‘nomor_kolom’ adalah nol

Kedua kasus tersebut dibahas sebagai berikut:

Kasus 1: Ketika nomor baris dan kolom dimasukkan sebagai nol, fungsi INDEX mengembalikan kesalahan ‘#VALUE’.

Langkah-langkah yang harus diikuti: Masukkan rumus ‘=INDEX(B3:F7,0,0)’ di sel B9. Tekan tombol ‘Enter’ dan hasilnya adalah kesalahan ‘#VALUE’. Ini ditunjukkan pada gambar berikut.

Kesimpulan: Ketika beberapa baris dan kolom digunakan sebagai array, perlu untuk menentukan nomor baris dan kolom untuk mengekstrak nilai sel tertentu. Jika nomor baris dan kolom dimasukkan sebagai nol, hasilnya adalah kesalahan ‘#VALUE’.

Kasus 2: Bila nomor baris atau kolom adalah nol, dan rumus INDEX dimasukkan sebagai rumus larik, hasilnya adalah larik nilai.

Langkah-langkah yang harus diikuti: Pilih rentang kosong B11:B15. Masukkan rumus ‘=INDEX(B3:F7,0,1)’ di sel pertama yang dipilih (sel B11). Tekan tombol ‘Ctrl+Shift+Enter’ bersamaan.

Keluaran dalam sel B11, B12, B13, B14, dan B15 masing-masing adalah ‘Penjualan’, ‘SDM’, ‘Operasi’, ‘Pemasaran’, dan ‘Keuangan’. Semua keluaran diperoleh (tanpa tanda kutip ganda) seperti yang ditampilkan dalam rentang B3:B7.

Atau, pilih baris kosong D11:H11. Masukkan rumus ‘=INDEX(B3:F7,3,0)’ sebagai rumus larik. Outputnya adalah larik nilai baris 5 (dari gambar sebelumnya). Jadi, hasilnya adalah ‘Operasi’, ‘176’, ‘665’, ‘629’, dan ‘497’ tanpa tanda kutip ganda.

Kesimpulan: Ketika salah satu nomor baris atau kolom ditentukan sementara yang lain disetel ke nol, fungsi INDEX mengembalikan larik nilai. Namun, dalam kasus tersebut, rumus INDEX harus dimasukkan sebagai rumus larik.

Selain itu, penting untuk memilih rentang keluaran kosong sebelum memasukkan rumus INDEX. Kisaran keluaran ini harus memiliki sel kosong sebanyak larik kumpulan data sumber.

Catatan: Formula array biasanya diselesaikan dengan menekan tombol CSE (Ctrl+Shift+Enter).

Contoh #4–Bentuk Referensi Dengan Banyak Array Dua Dimensi

Gambar berikut berisi tiga kumpulan data yang menampilkan jumlah karyawan yang bekerja di berbagai departemen MNC. Kumpulan data pertama (B2:E7) menunjukkan angka untuk tahun 2015-2017. Kumpulan data kedua (D9:F12) dan ketiga (C14:E18) menunjukkan angka untuk tahun 2015 dan 2016.

Gunakan formulir referensi fungsi excel INDEX untuk mengekstrak nilai sel F11. Selanjutnya, kategorikan tiga set data menjadi tiga area yang mengecualikan baris atas yang berisi heading.

Langkah-langkah untuk melakukan tugas sebelumnya tercantum sebagai berikut:

Langkah 1: Masukkan rumus berikut di sel B20.

‘=INDEKS((B3:E7,D10:F12,C15:E18),2,3,2)’

Langkah 2: Tekan tombol ‘Enter’. Outputnya adalah 665, seperti yang ditunjukkan pada gambar berikut.

Penjelasan: Dalam rumus sebelumnya (dimasukkan pada langkah 1), ketiga kumpulan data (diberikan dalam pertanyaan pada contoh ini) telah dikategorikan ke dalam tiga wilayah argumen ‘referensi’. Area tersebut adalah B3:E7, D10:F12, dan C15:E18. Judul telah dikecualikan dari area ini.

Selanjutnya, fungsi INDEX mencari nilai di area kedua (D10:F12). Hal ini karena argumen ‘area_num’ adalah 2. Argumen ‘row_num’ dan ‘column_num’ masing-masing adalah 2 dan 3. Jadi, fungsi INDEX mengembalikan nilai sel yang berada di persimpangan baris kedua dan kolom ketiga di area D10:F12. Ini sel F11.

Oleh karena itu, output dari rumus INDEX adalah 665.

Properti yang Berlaku untuk Kedua Bentuk Fungsi INDEX

Properti yang berlaku untuk formulir larik dan referensi dicantumkan sebagai berikut:

sebuah. Jika argumen ‘nomor_baris’ dan ‘nomor_kolom’ dimasukkan sebagai nol, fungsi INDEX excel mengembalikan ‘#VALUE!’ kesalahan.

Untuk mengambil nilai dari larik kolom tunggal, gunakan rumus berikut:

‘=INDEX(array,row_num)’ dalam bentuk array
‘=INDEX((reference),row_num,,area_num)’ dalam formulir referensi

Untuk mengambil nilai dari larik baris tunggal, gunakan rumus berikut:

‘=INDEX(array,,column_num)’ dalam bentuk array
‘=INDEX((reference),,col_num,area_num)’ dalam formulir referensi

Jika semua nilai kolom dari array diperlukan secara vertikal, tentukan ‘nomor_kolom’ dan atur ‘nomor_baris’ ke nol. Demikian pula, jika semua nilai baris dari array diperlukan secara horizontal, tentukan ‘nomor_baris’ dan atur ‘nomor_kolom’ ke nol. Dalam kedua kasus tersebut, pilih rentang keluaran kosong sebelum memasukkan rumus INDEX. Selain itu, tekan tombol ‘Ctrl+Shift+Enter’ untuk menyelesaikan rumus.
Argumen ‘nomor_baris’, ‘nomor_kolom’, dan ‘nomor_area’ harus merujuk ke sel dalam larik atau referensi yang ditentukan. Jika tidak, fungsi INDEX mengembalikan ‘#REF!’ kesalahan.

Catatan: Dalam pointer ‘c,’ rentang keluaran kosong dapat dipilih secara vertikal atau horizontal tergantung pada apakah array vertikal atau horizontal diperlukan.

Selanjutnya, jumlah sel kosong (dari rentang keluaran) harus sama dengan sel dari larik baris atau kolom tertentu (yang sedang disalin) dari kumpulan data sumber.

INDEX Dengan Fungsi Excel Lainnya

Fungsi INDEX di excel dapat digunakan dengan beberapa fungsi Excel lainnya dengan cara berikut:

sebuah. Fungsi INDEX digunakan dalam kombinasi dengan fungsi seperti SUM, AVERAGE, MIN, dan MAX untuk membuat rentang dinamis. Misalnya, rumus ‘AVERAGE(C3:INDEX(C3:C7,3))’ mengembalikan rata-rata sel C3, C4, dan C5. Ini karena rumus ‘INDEX(C3:C7,3)’ mengembalikan referensi ke sel C5. Jadi, rumus yang dihasilkan menjadi ‘AVERAGE(C3:C5).’ Selain itu, dengan perubahan argumen ‘row_num’ dari fungsi INDEX, range dari fungsi AVERAGE berubah. Hal ini mengakibatkan perubahan pada output yang diperoleh.

Fungsi INDEX dan MATCH MATCH Di ExcelFungsi MATCH mencari nilai tertentu dan mengembalikan posisi relatifnya dalam rentang sel tertentu. Outputnya adalah posisi pertama yang ditemukan untuk nilai yang diberikan. Menjadi fungsi pencarian dan referensi, ini berfungsi untuk pencocokan tepat dan perkiraan. Misalnya, jika rentang A11:A15 terdiri dari angka 2, 9, 8, 14, 32, rumus ‘MATCH(8,A11:A15,0)’ mengembalikan 3. Hal ini karena angka 8 berada pada urutan ketiga posisi. read more fungsi digunakan bersama untuk melakukan pencarian kiri. Kombinasi fungsi INDEX dan MATCH ini mengatasi keterbatasan fungsi VLOOKUP Excel. Misalnya, batasan seperti penyortiran data wajib, ukuran nilai pencarian yang dibatasi, penurunan kecepatan Excel, dan seterusnya dihilangkan.
Fungsi INDEX dapat digunakan dengan fungsi COUNTA Fungsi 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)’ mengembalikan 2. read more untuk mengekstrak nilai sel yang terakhir digunakan dari kumpulan data. Dengan perubahan nilai sel yang terakhir digunakan, output diperbarui dengan sendirinya.

Catatan 1: Rentang dinamis diperbarui secara otomatis dengan penambahan atau penghapusan data.

Catatan 2: Biasanya, fungsi INDEX mengembalikan nilai sel. Namun, ketika referensi selReferensi SelReferensi 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 selengkapnya dan titik dua (:) mendahului fungsi INDEX, ia mengembalikan referensi sel. Hal ini dapat diamati pada rumus AVERAGE dan INDEX dari pointer ‘a.’

Pertanyaan yang Sering Diajukan

Tentukan fungsi INDEX di Excel.

Fungsi INDEX di Excel mengembalikan nilai sel yang arraynya telah ditentukan. Selain itu, nomor baris dan kolom sel ini juga ditentukan dalam argumen rumus INDEX.

Fungsi INDEX juga dapat mengambil nilai seluruh baris atau kolom suatu rentang. Fungsi INDEX memiliki dua versi, array dan referensi. Kedua versi memiliki kumpulan argumennya sendiri.

Catatan: Untuk detail terkait argumen fungsi INDEX, lihat judul ‘sintaks fungsi INDEX di Excel’ di artikel ini.

Bagaimana cara menggunakan fungsi INDEX di Excel?

Langkah-langkah penggunaan fungsi INDEX di Excel adalah sebagai berikut:

a. Pilih formulir yang relevan (array atau referensi) dari fungsi INDEX yang akan diterapkan. Untuk ini, pertimbangkan jenis datanya.
b. Berikan argumen ke fungsi INDEX. Jika array nilai diperlukan, pilih rentang keluaran kosong sebelum memasukkan argumen.
c. Tekan salah satu tombol ‘Enter’ atau tombol CSE (Ctrl+Shift+Enter), bergantung pada jenis keluaran yang diperlukan.

Fungsi INDEX memproses argumen. Selanjutnya, ia mengembalikan nilai tunggal atau larik nilai berdasarkan cara penggunaannya.

Bagaimana cara menggunakan fungsi INDEX MATCH sebagai pengganti fungsi VLOOKUP di Excel?

Fungsi INDEX MATCH adalah kombinasi serbaguna. Ini karena fungsi ini dapat dengan mudah mencari nilai, terlepas dari lokasi pencarian dan kolom kembali.

Rumus untuk melakukan pencarian (dalam baris dan kolom) dengan menggunakan fungsi INDEX MATCH dinyatakan sebagai berikut:

‘=INDEX(column to return a value from,MATCH(vlookup value,column to be look up,0),MATCH( nilai hlookup,baris yang akan dicari,0))’

Cara kerja rumus ini dijelaskan sebagai berikut:

a. Rumus ‘MATCH(nilai vlookup, kolom yang akan dicari,0)’ mencari nilai vlookup di kolom yang akan dicari (atau kolom pencarian). Rumus ini mencari kecocokan persis dan mengembalikan nomor baris.
b. Rumus ‘MATCH(hlookup value,row to be look up,0)’ mencari nilai hlookup pada baris yang akan dicari (atau baris lookup). Rumus ini mencari pencocokan persis dan mengembalikan nomor kolom.
c. Fungsi INDEX menggunakan nomor baris dan kolom yang dikembalikan oleh fungsi MATCH. Fungsi INDEX mengembalikan nilai yang sesuai dari ‘kolom untuk mengembalikan nilai dari’ (atau mengembalikan kolom).

Oleh karena itu, rumus INDEX dan MATCH mengembalikan nilai pada perpotongan nomor baris dan kolom tertentu.

Catatan 1: Dimungkinkan juga untuk memasukkan hanya nomor baris atau hanya nomor kolom ke fungsi INDEX. Untuk memberikan nomor baris saja, gunakan rumus MATCH pertama (diberikan dalam penunjuk ‘a’) dengan fungsi INDEX. Demikian juga, untuk memberikan nomor kolom saja, gunakan rumus MATCH kedua (diberikan dalam penunjuk ‘b’) dengan fungsi INDEX.

Catatan 2: Mencari nilai di persimpangan baris dan kolom disebut pencarian 2 arah (atau pencarian 2 dimensi atau pencarian matriks).

Artikel yang Direkomendasikan

Ini telah menjadi panduan untuk fungsi INDEX di Excel. Di sini kita membahas cara kerja rumus INDEX di Excel beserta contoh dan template Excel yang dapat diunduh. Anda juga dapat melihat fungsi-fungsi berguna ini di Excel–