Cara menggunakan VLOOKUP, INDEX, ... di Excel harus Anda ketahui

Fungsi VLOOKUP, INDEX, MATCH, ... adalah fungsi yang cukup kompleks tetapi efektif dalam penambangan data di Excel . Pada artikel ini, saya akan memandu Anda secara rinci cara menggunakan VLOOKUP dan fungsi terkait secara efektif.

1. Fungsi VLOOKUP

Penggunaan : Menemukan nilai yang ditentukan di kolom pertama dan menarik data yang cocok dari baris yang sama di kolom lain. Ini adalah salah satu fungsi paling populer dan memberikan dukungan hebat untuk tugas kantor yang kompleks.

Rumus : = VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Dimana :

+ Lookup_value : Nilai yang akan dicari.

+ Table_array : Dua atau lebih kolom data.

+ Col_index_num : Jumlah kolom untuk menarik data.

+ Range_lookup : Tentukan apakah pencarian benar-benar akurat (SALAH) atau hanya relatif akurat (TRUE atau dihilangkan).

Contoh : Kami menggunakan fungsi VLOOKUP untuk menemukan Bonus dari setiap karyawan. Nilai yang dicari adalah Cell Position C2; Larik yang akan dideteksi adalah larik F1: G3. Saat memasukkan rumus = VLOOKUP (C2, $ F $ 1: $ G $ 3,2,0) (1) hasilnya adalah STAFF 2000 dan ROOMER 5000 (2).

2. Fungsi INDEX

Penggunaan : Mengembalikan referensi ke sel di dalam larik berdasarkan jumlah baris dan kolom yang Anda tentukan.

Rumus : = INDEX (array, row_num, [column_num])

Di antaranya : Array (wajib): Rentang sel atau konstanta array.

Contoh : Saat kita perlu mendapatkan nilai dari baris pertama, kolom kedua dalam tabel, kita menggunakan fungsi INDEX . Array adalah larik yang perlu kita deteksi adalah B2: C5; Row_num adalah baris 1; Column_num adalah kolom 2. Saat memasukkan rumus INDEX (B2: C5,1,2,1) (1) hasilnya adalah "HEAD OF ROOM" (2).

3. Fungsi MATCH

Penggunaan : Mencari nilai tertentu dalam satu rentang sel, dan memperlihatkan posisi relatif dari nilai tersebut.

Rumus : = MATCH (lookup_value, lookup_array, [match_type])

Dimana :

+ lookup_value (wajib) : Nilai yang ingin Anda cocokkan dalam larik pencarian. Mengubah angka nilai pencarian bisa berupa nilai (angka, teks, atau nilai logika) atau referensi sel ke angka, teks, atau nilai logika.

+ lookup_array (wajib) : Rentang sel yang akan dicari.

+ match_type (opsional) : Angka -1, 0, atau 1. Argumen tipe pencocokan menentukan bagaimana Excel mencocokkan nilai pencarian dengan nilai dalam array pencarian. Nilai default untuk argumen ini adalah 1.

Contoh : Saat kita perlu mengetahui posisi CLOTH, kita menggunakan fungsi MATCH . Saat memasukkan rumus = MATCH (FF2 & "*", "B2: B7", 0) (1), hasilnya adalah 3 (2).

3. Fungsi INDIRECT

Penggunaan : Mengembalikan referensi sel atau rentang yang ditentukan oleh string teks. Gunakan fungsi INDIRECT saat Anda ingin mengubah referensi ke sel dalam rumus tanpa mengubah rumus itu sendiri.

Rumus : = TIDAK LANGSUNG (ref_text, [a1])

Dimana :

+ Ref_text (wajib) : Referensi ke sel yang berisi gaya referensi A1, gaya referensi R1C1, atau referensi ke sel sebagai string teks.

+ Jika teks referensi bukan referensi yang valid sel, TIDAK LANGSUNG kembali yang #REF! Nilai Kesalahan . .

+ Jika ref_text mereferensikan buku kerja lain (referensi eksternal), buku kerja itu harus terbuka. Jika workbook sumber tidak terbuka, TIDAK LANGSUNG kembali yang #REF! Nilai Kesalahan . .

Contoh : Ketika Anda hanya perlu menghitung komisi dan Anda ingin menyeret, menjatuhkan atau memindahkan kotak hasil ke posisi lain tanpa mengubah rumus uang, kami menggunakan TIDAK LANGSUNG . Saat memasukkan rumus

= TIDAK LANGSUNG ("B2", TRUE) * TIDAK LANGSUNG ("C2", TRUE) (1) akan memberikan hasil 700 * 100 = 70000 (2).

4. Fungsi OFFSET

Penggunaan : Mengembalikan referensi ke rentang dengan jumlah baris tertentu dan jumlah kolom tertentu dari sel atau rentang sel. Referensi yang dikembalikan bisa berupa satu sel atau satu rentang sel. Anda dapat menentukan jumlah baris dan kolom yang akan dikembalikan.

Rumus : = OFFSET (referensi, baris, kolom, [tinggi], [lebar])

Dimana :

+ Referensi (wajib) : Area referensi tempat Anda ingin mendasarkan jarak referensi. Rentang referensi harus merujuk ke sel atau rentang sel yang berdekatan; jika tidak, OFFSET mengembalikan nilai kesalahan #VALUE !. .

+ Baris (wajib) : Jumlah baris, atas atau bawah, yang Anda ingin rujuk ke sel kiri atas. Baris bisa positif (yang berarti di bawah referensi awal) atau negatif (yang berarti di atas referensi awal).

+ Kolom (wajib) : Jumlah kolom, ke kiri atau kanan, yang Anda inginkan untuk dirujuk oleh sel di sudut kiri atas hasil. Kolom bisa positif atau negatif.

+ Tinggi (opsional) : Tinggi, dalam jumlah baris, yang ingin Anda miliki untuk referensi yang dikembalikan. Tinggi harus berupa angka positif.

+ Lebar (opsional) : Lebar, dalam jumlah kolom, yang ingin Anda miliki untuk referensi yang dikembalikan. Lebar harus berupa angka positif.

Contoh : Kami menggunakan OFFSET untuk melokalkan nilai, lalu menggunakan fungsi SUM untuk menghitung jumlahnya. Sel yang kami maksud adalah A1; dibandingkan dengan A1, Rows of the reference range dibawah 1 cell, jadi Rows adalah 1; dibandingkan dengan A1, Kolom kiri adalah 1 sel, jadi Kolom adalah 1; Tinggi kami mengambil 3 baris; Lebar kami mendapatkan 2 kolom. Jadi hasil penjumlahan semua nilai di wilayah tersebut adalah 2025.

5. Fungsi TRANSPOSE

Penggunaan : Mengubah rentang sel horizontal menjadi rentang vertikal dan sebaliknya, yaitu mengubah baris menjadi kolom dan kolom menjadi baris.

Rumus : = TRANSPOSE (larik)

Perbuatan

+ Langkah 1 : Pilih sel kosong.

+ Langkah 2 : Masuk = TRANSPOSE (B2: C4).

+ Langkah 3 : Masukkan rentang sel asli.

+ Langkah 4 : Terakhir, tekan CTRL + SHIFT + ENTER.

Contoh : Saat kita perlu mengonversi seluruh daftar harga produk dari vertikal ke horizontal, kita menggunakan fungsi TRANSPOSE dengan larik nilai B2 ke C4. Saat memasukkan rumus = TRANSPOSE (B2: C4) (1) akan memberikan hasil seperti yang ditunjukkan (2).

6. Fungsi HYPERLINK

Kegunaan : Membuat hyperlink ke dokumen yang disimpan di intranet atau Internet.

Formula : = HYPERLINK (link_location, [friendly_name]).

Dimana :

Link_location bisa merujuk ke lokasi dalam dokumen, seperti sel tertentu atau rentang bernama di lembar kerja atau di buku kerja Excel, atau ke bookmark di dokumen Microsoft Word.

Jalurnya bisa ke file yang disimpan di hard drive. Lintasan juga dapat berupa lintasan konvensi penamaan universal (UNC) di server (di Microsoft Excel untuk Windows).

Contoh : Link yang panjang dan membingungkan akan mengganggu pengguna. Jadi silakan gunakan HYPERLINK untuk membuat antarmuka lebih mudah digunakan. Memasukkan rumus = HYPERLINK (A1, “LINK GOOGLE”) (1) akan menghasilkan LINK GOOGLE (2).

Artikel di atas memandu VLOOKUP dan fungsi terkait. Semoga artikel di atas bermanfaat bagi Anda.