content:
Semua yang Perlu Anda Ketahui Tentang XLOOKUP - Tutorial Excel Semua yang Perlu Anda Ketahui Tentang XLOOKUP - Tutorial Excel
Langsung ke isi
Tutorial Excel
Menu
Beranda
Tips Excel
Functions
Power Pivot
VBA
Tentang Kami
Semua yang Perlu Anda Ketahui Tentang XLOOKUP
Juni 11, 2022 Mei 23, 2022 oleh Jack
Ada berita besar di dunia Excel baru-baru ini. Pengganti baru untuk fungsi Excel yang paling terkenal adalah diumumkan.
Pindah VLOOKUP, waktu Anda dalam sorotan sudah berakhir!
XLOOKUP ada di sini dan dapat melakukan semua yang dapat dilakukan VLOOKUP dan banyak lagi!
Mari kita lihat fungsi baru ini secara mendetail.
Masalah dengan VLOOKUP
Mengapa repot-repot memperkenalkan fungsi pencarian baru? VLOOKUP berfungsi dengan baik, bukan?
Nah, ada banyak masalah dengan VLOOKUP yang mungkin tidak disadari oleh pengguna pemula. Ketika kita tidak sadar, hal-hal bisa salah dan kita mungkin mendapatkan hasil yang salah.
Default VLOOKUP tidak sama persis. Argumen ke-4 di VLOOKUP adalah opsional, tetapi defaultnya adalah menggunakan perkiraan kecocokan. Ini sering dapat menyebabkan hasil yang salah.
VLOOKUP hanya dapat mencari dari item pertama hingga terakhir dan mengembalikan kecocokan pertama.
VLOOKUP tidak dapat mengembalikan hasil dari kiri, itu hanya dapat mengembalikan hasil di sebelah kanan kolom pencarian. Ini berarti terkadang kita perlu mengatur ulang atau menambahkan kolom pembantu ke data kita.
VLOOKUP perlu mereferensikan seluruh tabel. Ini berarti kami berpotensi mereferensikan banyak sel secara tidak perlu dan ini bisa sangat tidak efisien saat menghitung ulang buku kerja.
Kolom pencarian harus menjadi kolom pertama di tabel referensi kami. Sekali lagi, ini terkadang berarti perlu mengatur ulang data kita dengan canggung.
Menghapus atau menyisipkan kolom dapat merusak VLOOKUP. Referensi kolom argumen ke-3 sering dimasukkan sebagai nilai kode keras. Ini berarti jika kita menyisipkan atau menghapus kolom, posisi relatif kolom kembali ke kolom pencarian dapat berubah tetapi nilai hard code tidak akan diperbarui. Ini dapat menyebabkan VLOOKUP kami mengembalikan kesalahan atau hasil yang tidak diinginkan.
Perkiraan kecocokan hanya dapat mengembalikan item terkecil berikutnya.
Perkiraan kecocokan membutuhkan data yang diurutkan dalam urutan menaik. Data yang tidak disortir dalam situasi ini dapat menyebabkan hasil yang salah.
XLOOKUP bertujuan untuk memperbaiki masalah ini.
Versi Excel Apa yang Akan Memiliki XLOOKUP?
Hanya Excel untuk Office 365 yang akan mendapatkan fungsi XLOOKUP baru. Excel 2019 dan semua versi sebelumnya tidak akan pernah mendapatkan fungsi baru ini.
XLOOKUP saat ini hanya tersedia bagi mereka yang terdaftar di Orang Dalam Kantor program. Namun pada akhirnya akan tersedia secara umum untuk semua pengguna Office 365.
Sintaks XLOOKUP
Jadi seperti apa tampilan XLOOKUP? Ada 3 argumen wajib dan 2 argumen opsional.
= XLOOKUP ( lookup_value, lookup_array, results_array, [match_mode], [search_mode] )
nilai lookup (diperlukan) – Ini adalah nilai yang ingin kita cari.
lookup_array (diperlukan) – Ini adalah larik nilai yang ingin kita temukan lookup_valuenya.
hasil_array (diperlukan)- Ini adalah larik nilai yang sesuai yang ingin kita kembalikan hasilnya.
[match_mode] (opsional) – Ini adalah jenis kecocokan yang akan dikembalikan. Jika argumen ini tidak dimasukkan, defaultnya akan sama persis.
= Hasil yang sama persis. Jika tidak ada kecocokan persis yang ditemukan, kesalahan #N/A akan ditampilkan. Ini adalah mode pencocokan default.
-1 = Hasil yang sama persis. Jika tidak ada kecocokan persis yang ditemukan, nilai terkecil berikutnya akan dikembalikan.
1 = Hasil yang sama persis. Jika tidak ada kecocokan persis yang ditemukan, nilai terbesar berikutnya akan dikembalikan.
2 = Hasil yang sama persis, tetapi karakter khusus ?, * dan ~ akan bertindak sebagai karakter wildcard dalam lookup_value yang memungkinkan kita mengembalikan kecocokan sebagian.
[search_mode] (opsional) – Ini adalah jenis pencarian yang akan dilakukan. Jika argumen ini tidak dimasukkan, defaultnya adalah mencari dari pertama hingga terakhir.
1 = Melakukan pencarian dari item pertama hingga terakhir di lookup_array dan akan mengembalikan kecocokan pertama dari results_array.
-1 = Melakukan pencarian dari item terakhir ke item pertama di lookup_array dan akan mengembalikan kecocokan terakhir dari results_array.
2 = Melakukan pencarian biner dari item pertama hingga terakhir di lookup_array. Hasil_array kami perlu diurutkan dalam urutan menaik, atau hasilnya mungkin salah.
-2 = Melakukan pencarian biner dari item terakhir ke item pertama di lookup_array. Hasil_array kami perlu diurutkan dalam urutan menurun, atau hasilnya mungkin salah.
Baca Juga: Buat Visual Lampu Lalu Lintas Dinamis Untuk Dasbor Excel Anda
XLOOKUP Hanya Membutuhkan Tiga Argumen
XLOOKUP terdengar lebih rumit daripada VLOOKUP! Pasti ada lebih banyak pilihan yang tersedia.
99% dari waktu, kami ingin melakukan pencarian pencocokan persis dasar. XLOOKUP hanya membutuhkan 3 argumen pertama untuk melakukan ini. Ini membuat XLOOKUP lebih sederhana daripada VLOOKUP dalam situasi ini.
= XLOOKUP ( B2, Countries[Country], Countries[Population] )
Dalam contoh ini, kami ingin mengembalikan populasi untuk negara tertentu.
Kami ingin menemukan kecocokan yang tepat untuk negara tersebut dan mengembalikan populasi yang sesuai dari tabel data.
Kita hanya perlu menggunakan tiga argumen pertama di XLOOKUP untuk menemukan dan mengembalikan kecocokan yang tepat.
XLOOKUP Dapat Mengembalikan Hasil Dari Kiri
Array pencarian dan array hasil direferensikan secara independen. Akibatnya, ini berarti XLOOKUP dapat mengembalikan hasil di sebelah kiri larik pencarian.
= XLOOKUP ( B2, Countries[Capital], Countries[Country] )
Dalam contoh ini, kami ingin mengembalikan negara untuk ibu kota tertentu.
Kali ini kita menempatkan kolom negara sebagai argumen array hasil, dan kita dapat mencari dari kanan ke kiri tanpa masalah!
XLOOKUP Dapat Mengembalikan Hasil Horizontal
XLOOKUP juga akan menggantikan HLOOKUP, karena dapat mengembalikan hasil horizontal juga.
Array pencarian dan array hasil tidak harus vertikal. Mereka juga bisa berupa array horizontal.
Baca Juga: Cara Melakukan Pajak di Excel - Termasuk Template Gratis
Faktanya, “X” di XLOOKUP dipilih sebagian karena berfungsi dengan data vertikal dan horizontal.
Sampai jumpa HLOOKUP!
= XLOOKUP ( B8, $C$2:$H$2, $C$5:$H$5 )
Dalam contoh ini, kami memiliki beberapa judul kolom bulan yang disusun secara horizontal dengan nilai yang sesuai pada baris di bawah ini.
Kita dapat menggunakan XLOOKUP untuk mencari bulan tertentu dan mengembalikan hasil dari total baris.
XLOOKUP Tidak Dapat Melakukan Vertikal & Horizontal
Karena XLOOKUP bekerja dengan array vertikal dan horizontal, kita mungkin tergoda untuk mencoba mencampur dan mencocokkannya. Sayangnya, ini tidak berhasil.
= XLOOKUP ( B2, D4:D6, E2:G2 )
Dalam contoh ini, kami mencoba melakukan pencarian kami dalam larik vertikal dan mengembalikan nilai dari larik horizontal. Ini menghasilkan #NILAI kesalahan.
XLOOKUP Dapat Mengembalikan Item Terkecil atau Terbesar Berikutnya [Approximate Match]
Perkiraan kecocokan VLOOKUP hanya dapat mengembalikan item terkecil berikutnya dalam daftar. Kami juga perlu mengurutkan data kami dalam urutan menaik untuk mengembalikan hasil yang benar.
Dengan XLOOKUP kita dapat memilih untuk mengembalikan item terkecil berikutnya atau terbesar berikutnya. Dan kami tidak perlu mengurutkan data kami agar ini berfungsi.
Argumen ke-4 di XLOOKUP memberi kita opsi untuk mengembalikan item yang lebih kecil berikutnya atau item yang lebih besar berikutnya.
Contoh terkecil berikutnya
Dalam contoh ini, kami mencoba mencari skor persentase dan mengembalikan nilai huruf yang sesuai.
Kami perlu menggunakan perkiraan kecocokan, karena skor dapat berupa nilai apa pun dari 0% hingga 100% dan kami tidak dapat mencantumkan semua kemungkinan.
Jika kita menggunakan -1 dalam argumen ke-4 maka ini akan memungkinkan kita untuk mengembalikan item terkecil berikutnya.
Mencari 69,9% akan menghasilkan nilai huruf C karena 60% adalah kecocokan terkecil berikutnya.
Mencari 70% akan menghasilkan nilai huruf B karena 70% adalah sama persis.
Mencari 70,1% akan menghasilkan nilai huruf B karena 70% adalah kecocokan terkecil berikutnya.
Contoh Terbesar Berikutnya
Bekerja dengan contoh yang sama persis dan mengalihkan argumen ke-4 ke 1 akan menghasilkan hasil sebagai berikut.
Mencari 69,9% akan menghasilkan nilai huruf B karena 70% adalah kecocokan terbesar berikutnya.
Mencari 70% akan menghasilkan nilai huruf B karena 70% adalah sama persis.
Mencari 70,1% akan menghasilkan nilai huruf A karena 80% adalah kecocokan terbesar berikutnya.
Ini bukan cara kerja nilai, tetapi saya tidak ingin membuat contoh baru
XLOOKUP Dapat Mencari Terakhir ke Pertama
Sudah cukup terkenal bahwa VLOOKUP mencari yang pertama hingga terakhir dan mengembalikan kecocokan pertama. XLOOKUP dapat melakukannya juga.
Tapi XLOOKUP juga bisa mencari last to first dan mengembalikan pertandingan terakhir.
Kami tidak perlu mengurutkan data kami dalam urutan yang berbeda untuk melakukan ini. Argumen ke-5 XLOOKUP memungkinkan kita untuk Cari terakhir-ke-pertama.
Baca Juga: 15 Cara Membuat Tanda Centang di Excel
= XLOOKUP ( B2, Contacts[First], Contacts[Email], 0, -1 )
Dalam contoh ini, kami mencari email kontak berdasarkan nama depan mereka.
Perhatikan, ada dua Garret dalam daftar kontak kita.
Kali ini, kami menentukan kecocokan tepat dengan menggunakan dalam argumen ke-4. Kami kemudian dapat menentukan untuk mencari yang terakhir ke yang pertama dengan menggunakan -1 dalam argumen ke-5.
Sekarang XLOOKUP akan mengembalikan email untuk Garret terakhir dalam daftar kami!
XLOOKUP Dengan Karakter Wildcard
VLOOKUP secara otomatis menggunakan karakter wildcard Excel (*, ?, ~) dalam pencariannya. Ini bagus jika ini adalah niat Anda, tetapi bisa membuat frustrasi jika Anda mencoba mencari sesuatu yang benar-benar mengandung karakter ini.
XLOOKUP hadir dengan opsi pencarian wildcard dalam argumen ke-4 sehingga Anda dapat secara eksplisit meminta pencarian menggunakan karakter wildcard.
= XLOOKUP ( B2, Contact[Name], Contact[Email], 2 )
Dalam contoh ini kita menggunakan * dan ? karakter untuk mencari kecocokan sebagian dalam nama dan kemudian mengembalikan alamat email yang sesuai.
Joan* akan mencari pertandingan pertama dimulai dengan Joan dan mengandung karakter apa pun setelahnya. Ini akan kembali jsmith@google.com sebagai pertandingan pertama.
Joa?? akan mencari pertandingan pertama dimulai dengan Joa dan diikuti oleh dua karakter apa saja dan diakhiri dengan e. Ini akan kembali jpencott@cbsnews.com sebagai pertandingan pertama.
Cari Beberapa Kriteria Dengan XLOOKUP
Kita dapat memanfaatkan mesin kalkulasi baru di Excel bersama dengan XLOOKUP untuk mencari dengan mudah berdasarkan beberapa kriteria.
= XLOOKUP ( B2 & C2, Contacts[First] & Contacts[Last], Contacts[Email] )
Dalam contoh ini, kami ingin mencari alamat email kontak berdasarkan nama depan dan belakang mereka. Kita perlu mencari berdasarkan dua kriteria.
Kita sekarang dapat menggunakan karakter ampersand untuk melakukan ini.
Kita dapat membuat nilai pencarian baru berdasarkan nama depan dan belakang dengan menggabungkannya bersama dengan ampersand.
Kita kemudian perlu melakukan hal yang sama untuk array pencarian. Kami membuat array pencarian baru dengan menggabungkan kolom nama depan dan belakang dengan ampersand.
Apa yang Akan Terjadi Pada VLOOKUP & HLOOKUP?
Sekarang XLOOKUP telah membuat kedua fungsi ini menjadi usang, apa yang akan terjadi pada VLOOKUP dan HLOOKUP?
Kami masih perlu menggunakannya jika kami berencana untuk berbagi buku kerja kami dengan siapa pun yang tidak menggunakan Office 365 karena XLOOKUP tidak tersedia di versi Excel lainnya.
Microsoft tidak akan menghapusnya dari Excel. Mereka akan tetap ada dan tersedia untuk digunakan. Tapi saya bisa melihat waktu di masa depan yang jauh ketika mereka menemui nasib yang sama dengan fungsi DATEDIF.