Konten
Semua pencarian klasik dan fungsi substitusi ketik VPR (VLOOKUP), GPR (HLOOKUP), LEBIH TERBUKTI (COCOK) dan mereka seperti mereka memiliki satu fitur penting – mereka mencari dari awal sampai akhir, yaitu kiri-ke-kanan atau atas-ke-bawah di sumber data. Segera setelah kecocokan pertama ditemukan, pencarian berhenti dan hanya kemunculan pertama dari elemen yang kita butuhkan yang ditemukan.
Apa yang harus dilakukan jika kita perlu menemukan bukan yang pertama, tetapi yang terakhir? Misalnya, transaksi terakhir untuk klien, pembayaran terakhir, pesanan terbaru, dll.?
Metode 1: Menemukan Baris Terakhir dengan Rumus Array
Jika tabel asli tidak memiliki kolom dengan tanggal atau nomor urut baris (pesanan, pembayaran ...), maka tugas kita sebenarnya adalah menemukan baris terakhir yang memenuhi kondisi yang diberikan. Ini dapat dilakukan dengan rumus array berikut:
Sini:
- fungsi IF (JIKA) memeriksa semua sel dalam kolom satu per satu Pelanggan dan menampilkan nomor baris jika berisi nama yang kita butuhkan. Nomor baris pada lembar diberikan kepada kami oleh fungsi GARIS (BARIS), tetapi karena kita memerlukan nomor baris dalam tabel, kita juga harus mengurangi 1, karena kita memiliki header di dalam tabel.
- Maka fungsi MAX (MAKS) memilih nilai maksimum dari kumpulan nomor baris yang terbentuk, yaitu jumlah baris klien terbaru.
- fungsi INDEKS (INDEKS) mengembalikan konten sel dengan nomor terakhir yang ditemukan dari kolom tabel lain yang diperlukan (Kode pemesanan).
Semua ini harus dimasukkan sebagai rumus larik, yaitu:
- Di Office 365 dengan pembaruan terbaru yang diinstal dan dukungan untuk array dinamis, Anda cukup menekan Enter.
- Di semua versi lain, setelah memasukkan rumus, Anda harus menekan pintasan keyboard Ctrl+perubahan+Enter, yang secara otomatis akan menambahkan kurung kurawal di bilah rumus.
Metode 2: Reverse lookup dengan fungsi LOOKUP baru
Saya sudah menulis artikel panjang dengan video tentang fitur baru BACA (XLOOKUP), yang muncul di versi Office terbaru untuk menggantikan VLOOKUP lama (VLOOKUP). Dengan bantuan BROWSE, tugas kami diselesaikan dengan cukup mendasar, karena. untuk fungsi ini (tidak seperti VLOOKUP), Anda dapat secara eksplisit mengatur arah pencarian: top-down atau bottom-up – argumen terakhirnya (-1) bertanggung jawab untuk ini:
Metode 3. Cari string dengan tanggal terbaru
Jika dalam data sumber kami memiliki kolom dengan nomor seri atau tanggal yang memainkan peran serupa, maka tugas diubah - kami tidak perlu menemukan baris terakhir (terendah) dengan kecocokan, tetapi baris dengan yang terbaru ( maksimum) tanggal.
Saya telah membahas secara rinci bagaimana melakukan ini menggunakan fungsi klasik, dan sekarang mari kita coba menggunakan kekuatan fungsi array dinamis baru. Untuk keindahan dan kenyamanan yang lebih besar, kami juga mengubah tabel asli menjadi tabel "pintar" menggunakan pintasan keyboard Ctrl+T atau perintah Beranda – Format sebagai tabel (Beranda — Format sebagai Tabel).
Dengan bantuan mereka, "pasangan pembunuh" ini memecahkan masalah kami dengan sangat anggun:
Sini:
- Fungsi dulu FILTER (SARING) hanya memilih baris-baris itu dari tabel kami di mana di kolom Pelanggan - nama yang kita butuhkan.
- Maka fungsi GRADE (MENYORTIR) mengurutkan baris yang dipilih menurut tanggal dalam urutan menurun, dengan transaksi terbaru di bagian atas.
- fungsi INDEKS (INDEKS) mengekstrak baris pertama, yaitu mengembalikan perdagangan terakhir yang kita butuhkan.
- Dan, akhirnya, fungsi FILTER eksternal menghapus kolom 1 dan 3 tambahan dari hasil (Kode pemesanan и Pelanggan) dan hanya menyisakan tanggal dan jumlah. Untuk ini, array konstanta digunakan. {0;1;0;1}, menentukan kolom mana yang ingin (1) atau tidak ingin (0) ditampilkan.
Metode 4: Menemukan Kecocokan Terakhir di Power Query
Nah, demi kelengkapan, mari kita lihat solusi untuk masalah pencarian terbalik kami menggunakan add-in Power Query. Dengan bantuannya, semuanya diselesaikan dengan sangat cepat dan indah.
1. Mari kita ubah tabel asli kita menjadi tabel "pintar" menggunakan pintasan keyboard Ctrl+T atau perintah Beranda – Format sebagai tabel (Beranda — Format sebagai Tabel).
2. Muat ke Power Query dengan tombol Dari Tabel/Rentang tab Data (Data — Dari Tabel/Rentang).
3. Kami mengurutkan (melalui daftar drop-down filter di header) tabel kami dalam urutan tanggal, sehingga transaksi terbaru berada di atas.
4… Di tab Transformasi pilih tim Kelompok oleh (Transformasi — Kelompokkan Menurut) dan atur pengelompokan berdasarkan pelanggan, dan sebagai fungsi agregasi, pilih opsi Semua lini (Semua baris). Anda dapat memberi nama kolom baru apa pun yang Anda suka – misalnya Rincian.
Setelah pengelompokan, kami akan mendapatkan daftar nama unik klien kami dan di kolom Rincian – tabel dengan semua transaksi masing-masing, di mana baris pertama akan menjadi transaksi terbaru, yang kita butuhkan:
5. Tambahkan kolom terhitung baru dengan tombol Kolom khusus tab Tambahkan kolom (Tambahkan kolom — Tambahkan kolom khusus)dan masukkan rumus berikut:
Sini Rincian – ini adalah kolom tempat kami mengambil tabel berdasarkan pelanggan, dan 0 {} adalah jumlah baris yang ingin kita ekstrak (penomoran baris di Power Query dimulai dari nol). Kami mendapatkan kolom dengan catatan (Rekam), di mana setiap entri adalah baris pertama dari setiap tabel:
Tetap memperluas konten semua catatan dengan tombol dengan panah ganda di tajuk kolom Kesepakatan terakhir memilih kolom yang diinginkan:
… lalu hapus kolom yang tidak diperlukan lagi Rincian dengan mengklik kanan pada judulnya - Hapus kolom (Hapus kolom).
Setelah mengunggah hasilnya ke lembar melalui Beranda — Tutup dan muat — Tutup dan muat masuk (Beranda — Tutup & Muat — Tutup & Muat ke…) kita akan mendapatkan tabel yang bagus dengan daftar transaksi terakhir, seperti yang kita inginkan:
Ketika Anda mengubah data sumber, Anda tidak boleh lupa untuk memperbarui hasil dengan mengklik kanan pada mereka – perintah Perbarui & Simpan (Menyegarkan) atau pintasan keyboard Ctrl+lain+F5.
- Fungsi LOOKUP adalah turunan dari VLOOKUP
- Cara menggunakan fungsi array dinamis baru SORT, FILTER, dan UNIC
- Menemukan sel non-kosong terakhir dalam satu baris atau kolom dengan fungsi LOOKUP