Cara menggunakan VLOOKUP, INDEX, ... di Excel yang semestinya anda ketahui

Fungsi VLOOKUP, INDEX, MATCH, ... adalah fungsi yang cukup kompleks tetapi berkesan dalam perlombongan data di Excel . Dalam artikel ini, saya akan membimbing anda secara terperinci bagaimana menggunakan VLOOKUP dan fungsi yang berkaitan dengan berkesan.

1. Fungsi VLOOKUP

Penggunaan : Mencari nilai yang ditentukan pada lajur pertama dan menarik data yang sepadan dari baris yang sama di lajur lain. Ini adalah salah satu fungsi yang paling popular dan memberikan sokongan hebat untuk tugas pejabat yang kompleks.

Formula : = VLOOKUP (look__value, table_array, col_index_num, [range_lookup])

Di mana :

+ Lookup_value : Nilai untuk dicari.

+ Table_array : Dua atau lebih lajur data.

+ Col_index_num : Jumlah lajur untuk menarik data.

+ Range_lookup : Tentukan sama ada carian benar-benar tepat (SALAH) atau agak tepat (BENAR atau dihilangkan)

Contoh : Kami menggunakan fungsi VLOOKUP untuk mencari Bonus setiap pekerja. Nilai yang perlu dicari adalah Cell Position C2; Array yang hendak dikesan ialah array F1: G3. Semasa memasukkan formula = VLOOKUP (C2, $ F $ 1: $ G $ 3,2,0) (1) hasilnya adalah STAFF 2000 dan ROOMER 5000 (2).

2. Fungsi INDEX

Penggunaan : Mengembalikan rujukan ke sel di dalam larik berdasarkan jumlah baris dan lajur yang anda tentukan.

Formula : = INDEX (tatasusunan, baris_num, [kolum_num])

Antaranya : Array (diperlukan): Julat sel atau pemalar array.

 

 

Contoh : Apabila kita perlu mendapatkan nilai baris pertama, lajur kedua dalam jadual, kita menggunakan fungsi INDEX . Array adalah tatasusunan yang perlu kita mengesan ialah B2: C5; Row_num adalah baris 1; Column_num adalah kolum 2. Semasa memasukkan formula INDEX (B2: C5,1,2,1) (1) hasilnya adalah "HEAD OF ROOM" (2).

3. Fungsi PERTANDINGAN

Penggunaan : Mencari nilai tertentu dalam julat sel, dan menunjukkan kedudukan relatif nilai tersebut.

Formula : = PERTANDINGAN (look__alue, lookup_array, [match_type])

Di mana :

+ lookup_value (diperlukan) : Nilai yang ingin anda padankan dalam array carian. Tukar nombor nilai carian boleh menjadi nilai (nombor, teks, atau nilai logik) atau rujukan sel ke nombor, teks, atau nilai logik.

+ lookup_array (diperlukan) : Julat sel yang hendak dicari.

+ match_type (pilihan) : Nombor -1, 0, atau 1. Argumen jenis padanan menentukan bagaimana Excel memadankan nilai pencarian dengan nilai dalam tatasusunan. Nilai lalai untuk argumen ini ialah 1.

Contohnya : Apabila kita perlu mengetahui kedudukan PAKAIAN apa, kita menggunakan fungsi MATCH . Semasa memasukkan formula = MATCH (FF2 & "*", "B2: B7", 0) (1), hasilnya adalah 3 (2).

3. Fungsi tidak langsung

Penggunaan : Mengembalikan rujukan sel atau julat yang ditentukan oleh rentetan teks. Gunakan fungsi TIDAK LANGSUNG ketika anda ingin menukar rujukan ke sel dalam formula tanpa mengubah formula itu sendiri.

Formula : = LANGSUNG (ref_text, [a1])

Di mana :

+ Ref_text (diperlukan) : Rujukan ke sel yang mengandungi gaya rujukan A1, gaya rujukan R1C1 atau rujukan ke sel sebagai rentetan teks.

+ Sekiranya teks rujukan bukan rujukan sel yang sah, INDIRECT mengembalikan nilai ralat #REF! .

+ Jika ref_text merujuk buku kerja lain (rujukan luaran), buku kerja itu mesti dibuka. Sekiranya buku kerja sumber tidak dibuka, LANGSUNG mengembalikan nilai ralat #REF! .

 

 

Contohnya : Apabila anda hanya perlu mengira komisen dan anda ingin menyeret, menjatuhkan atau memindahkan kotak hasil ke kedudukan lain tanpa mengubah formula wang, kami menggunakan LANGSUNG . Semasa memasukkan formula

= LANGSUNG (“B2”, BENAR) * LANGSUNG (“C2”, BENAR) (1) akan memberikan hasilnya 700 * 100 = 70000 (2).

4. Fungsi OFFSET

Penggunaan : Mengembalikan rujukan ke julat dengan bilangan baris yang ditentukan dan bilangan lajur yang ditentukan dari sel atau julat sel. Rujukan yang dikembalikan boleh berupa sel tunggal atau rangkaian sel. Anda boleh menentukan bilangan baris dan lajur yang akan dikembalikan.

Formula : = OFFSET (rujukan, baris, col, [tinggi], [lebar])

Di mana :

+ Rujukan (diperlukan) : Kawasan rujukan tempat anda ingin mendasarkan jarak rujukan. Julat rujukan mesti merujuk kepada sel atau rangkaian sel yang berdekatan; jika tidak, OFFSET mengembalikan nilai ralat #VALUE !. .

+ Baris (diperlukan) : Bilangan baris, atas atau bawah, yang anda mahu rujuk sel kiri atas. Baris boleh menjadi positif (yang bermaksud di bawah rujukan permulaan) atau negatif (yang bermaksud di atas rujukan permulaan).

+ Kol (wajib) : Jumlah lajur, di sebelah kiri atau kanan, yang anda mahukan sel di sudut kiri atas hasilnya. Lajur boleh menjadi positif atau negatif.

+ Tinggi (pilihan) : Ketinggian, dalam bilangan baris, yang anda inginkan untuk rujukan yang dikembalikan. Ketinggian mestilah nombor positif.

+ Lebar (pilihan) : Lebar, dalam jumlah lajur, yang anda inginkan untuk rujukan yang dikembalikan. Lebar mestilah nombor positif.

Contoh : Kami menggunakan OFFSET untuk melokalkan nilai, dan kemudian menggunakan fungsi SUM untuk mengira jumlahnya. Sel yang kita rujuk ialah A1; berbanding dengan A1, Baris julat rujukan berada di bawah 1 sel, jadi Baris adalah 1; berbanding dengan A1, Cols kiri adalah 1 sel, jadi Cols adalah 1; Ketinggian kita mengambil 3 baris; Lebar kita mendapat 2 lajur. Jadi hasilnya ketika menambahkan semua nilai di wilayah ini adalah 2025.

5. Fungsi TRANSPOSE

Penggunaan : Menukar julat sel mendatar menjadi julat menegak dan sebaliknya, iaitu menukar baris menjadi lajur dan lajur menjadi baris.

Formula : = TRANSPOSE (array)

Melakukan

+ Langkah 1 : Pilih sel kosong.

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

+ Langkah 3 : Masukkan julat sel asal.

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

Contoh : Apabila kita perlu menukar senarai harga keseluruhan produk dari menegak ke mendatar, kita menggunakan fungsi TRANSPOSE dengan susunan nilai B2 hingga C4. Semasa memasukkan formula = TRANSPOSE (B2: C4) (1) akan memberikan hasilnya seperti yang ditunjukkan (2).

6. Fungsi HYPERLINK

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

Rumus : = HYPERLINK (link_lokasi, [nama_ mesra]).

Di mana :

Link_location dapat merujuk ke lokasi dalam dokumen, seperti sel tertentu atau julat bernama dalam lembaran kerja atau buku kerja Excel, atau penanda buku dalam dokumen Microsoft Word.

Jalannya boleh ke fail yang disimpan di cakera keras. Laluan itu juga boleh menjadi jalan konvensyen penamaan universal (UNC) pada pelayan (dalam Microsoft Excel untuk Windows).

 

 

 

 

Contoh : Pautan yang panjang dan membingungkan akan mengganggu pengguna. Oleh itu, sila gunakan HYPERLINK untuk menjadikan antara muka lebih mudah digunakan. Memasukkan formula = HYPERLINK (A1, "LINK GOOGLE") (1) akan menghasilkan LINK GOOGLE (2).

Artikel di atas membimbing VLOOKUP dan fungsi yang berkaitan. Semoga artikel di atas berguna kepada anda.