Fungsi VLOOKUP sentiasa menjadi salah satu fungsi Excel yang paling berkuasa. Ia membolehkan anda mencari nilai dalam lajur pertama jadual dan mengembalikan nilai daripada medan di sebelah kanan. Tetapi Excel juga mempunyai fungsi yang dipanggil XLOOKUP, yang membolehkan anda mencari nilai dalam mana-mana lajur atau baris dan mengembalikan data daripada mana-mana lajur lain.
Cara XLOOKUP Berfungsi
Fungsi XLOOKUP adalah lebih mudah untuk digunakan daripada fungsi VLOOKUP, kerana bukannya menentukan nilai untuk lajur hasil, anda boleh menentukan keseluruhan julat.
Fungsi ini juga membolehkan anda mencari kedua-dua lajur dan baris, mencari nilai pada sel bersilang.
Parameter fungsi XLOOKUP adalah seperti berikut:
=XLOOKUP (lookup_value, lookup_array, return_array, [match_mode], [search_mode])
- lookup_value: Nilai yang anda ingin cari
- lookup_array: Tatasusunan (lajur) yang anda ingin cari
- return_array: Hasil (lajur) yang anda ingin dapatkan nilai daripada
- mode_match (pilihan): Pilih padanan tepat (0), padanan tepat atau nilai terkecil seterusnya (-1) atau padanan kad liar (2).
- mod_carian (pilihan): Pilih sama ada hendak mencari bermula dengan item pertama dalam lajur (1), item terakhir dalam lajur (-1), carian binari menaik (2) atau carian binari menurun (-2).
Berikut ialah beberapa carian paling biasa yang boleh anda lakukan dengan fungsi XLOOKUP.
Cara Mencari Satu Keputusan Menggunakan XLOOKUP
Cara paling mudah untuk menggunakan XLOOKUP ialah mencari hasil tunggal menggunakan titik data dari satu lajur.
-
Hamparan contoh ini ialah senarai pesanan yang diserahkan oleh wakil jualan, termasuk item, bilangan unit, kos dan jumlah jualan.
Image -
Jika anda ingin mencari jualan pertama dalam senarai yang diserahkan oleh wakil jualan tertentu, anda boleh membuat fungsi XLOOKUP yang mencari nama lajur Rep. Fungsi ini akan mengembalikan hasil daripada lajur Jumlah. Fungsi XLOOKUP untuk ini ialah:
=XLOOKUP(I2, C2:C44, G2:G44, 0, 1)
- I2: Menunjuk pada Nama Wakil sel carian
- C2:C44: Ini ialah lajur Rep, iaitu tatasusunan carian
- G2:G33: Ini ialah lajur Jumlah, iaitu tatasusunan kembali
- 0: Memilih padanan tepat
- 1: Memilih padanan pertama dalam keputusan
-
Apabila anda menekan Enter dan taip nama wakil jualan, sel Jumlah hasil akan menunjukkan kepada anda hasil pertama dalam jadual untuk wakil jualan tersebut.
Image -
Jika anda ingin mencari jualan terbaharu (memandangkan jadual disusun mengikut tarikh dalam susunan terbalik), tukar argumen XLOOKUP terakhir kepada - 1, yang akan bermula carian daripada sel terakhir dalam tatasusunan carian dan sebaliknya memberikan anda hasil carian itu.
Image -
Contoh ini menunjukkan carian serupa yang boleh anda lakukan dengan fungsi VLOOKUP dengan menggunakan lajur Rep sebagai lajur pertama jadual carian. Walau bagaimanapun, XLOOKUP membolehkan anda mencari mana-mana lajur dalam mana-mana arah. Contohnya, jika anda ingin mencari wakil jualan yang menjual pesanan Pengikat pertama tahun itu, anda akan menggunakan fungsi XLOOKUP berikut:
=XLOOKUP(I2, D2:D44, C2:C44, 0, 1)
- D2: Menuding ke sel carian Item
- D2:D44: Ini ialah lajur Item, iaitu tatasusunan carian
- C2:C44: Ini ialah lajur Rep, iaitu tatasusunan kembali di sebelah kiri tatasusunan carian
- 0: Memilih padanan tepat
- 1: Memilih padanan pertama dalam keputusan
-
Kali ini, hasilnya ialah nama wakil jualan yang menjual pesanan pengikat pertama tahun itu.
Image
Lakukan Padanan Menegak dan Mendatar dengan XLOOKUP
Keupayaan XLOOKUP lain yang tidak mampu dimiliki oleh VLOOKUP ialah keupayaan untuk melakukan carian menegak dan mendatar, bermakna anda boleh mencari item di bawah lajur dan merentasi baris juga.
Ciri carian dwi ini merupakan pengganti yang berkesan untuk fungsi Excel lain seperti INDEX, MATCH atau HLOOKUP.
-
Dalam contoh hamparan berikut, jualan untuk setiap wakil jualan dibahagikan mengikut suku tahun. Jika anda ingin melihat jualan suku ketiga untuk wakil jualan tertentu, tanpa fungsi XLOOKUP, carian jenis ini akan menjadi sukar.
Image -
Dengan fungsi XLOOKUP, carian jenis ini mudah. Menggunakan fungsi XLOOKUP seterusnya, anda boleh mencari jualan suku ketiga untuk wakil jualan tertentu:
=XLOOKUP(J2, B2:B42, XLOOKUP(K2, C1:H1, C2:H42))
- J2: Menuding ke sel carian Rep
- B2:B42: Ini ialah lajur Item, iaitu tatasusunan carian lajur
- K2: Menuding ke sel carian Suku
- C1:H1: Ini ialah tatasusunan carian baris
- C2:H42: Ini ialah tatasusunan carian untuk jumlah dolar pada setiap suku tahun
Fungsi XLOOKUP bersarang ini mula-mula mengenal pasti wakil jualan, dan fungsi XLOOKUP seterusnya mengenal pasti suku yang dikehendaki. Nilai pulangan akan ialah sel tempat kedua-duanya memintas.
-
Hasil untuk formula ini ialah pendapatan suku satu untuk wakil dengan nama Thompson.
Image
Menggunakan Fungsi XLOOKUP
Fungsi XLOOKUP hanya tersedia untuk pelanggan Office Insider, tetapi akan dilancarkan tidak lama lagi kepada semua pelanggan Microsoft 365.
Jika anda ingin menguji fungsi itu sendiri, anda boleh menjadi Office Insider. Pilih Fail > Akaun, kemudian pilih menu lungsur Office Insider untuk melanggan.
Sebaik sahaja anda menyertai program Office Insider, versi Excel anda yang dipasang akan menerima semua kemas kini terkini dan anda boleh mula menggunakan fungsi XLOOKUP.
