Mencari Kejadian Terakhir (VLOOKUP Terbalik)

Semua carian klasik dan fungsi penggantian jenis VPR (TAMPILAN), GPR (HLOOKUP), LEBIH TERDEDAH (PERLAWANAN) dan mereka seperti mereka mempunyai satu ciri penting – mereka mencari dari awal hingga akhir, iaitu kiri-ke-kanan atau atas-ke-bawah dalam data sumber. Sebaik sahaja padanan padanan pertama ditemui, carian berhenti dan hanya kejadian pertama elemen yang kami perlukan ditemui.

Apa yang perlu dilakukan jika kita perlu mencari bukan yang pertama, tetapi kejadian terakhir? Contohnya, transaksi terakhir untuk pelanggan, pembayaran terakhir, pesanan terkini, dsb.?

Kaedah 1: Mencari Baris Terakhir dengan Formula Tatasusunan

Jika jadual asal tidak mempunyai lajur dengan tarikh atau nombor siri baris (pesanan, pembayaran ...), maka tugas kami, sebenarnya, untuk mencari baris terakhir yang memenuhi syarat yang diberikan. Ini boleh dilakukan dengan formula tatasusunan berikut:

Di sini:

• fungsi IF (JIKA) menyemak semua sel dalam lajur satu demi satu Pelanggan dan memaparkan nombor baris jika ia mengandungi nama yang kami perlukan. Nombor baris pada helaian diberikan kepada kami oleh fungsi LINE (ROW), tetapi kerana kita memerlukan nombor baris dalam jadual, kita juga perlu menolak 1, kerana kita mempunyai pengepala dalam jadual.
  
• Kemudian fungsi MAX (MAX) memilih nilai maksimum daripada set nombor baris yang dibentuk, iaitu nombor baris terkini pelanggan.
• fungsi INDEKS (INDEX) mengembalikan kandungan sel dengan nombor terakhir yang ditemui daripada mana-mana lajur jadual lain yang diperlukan (Kod pesanan).

Semua ini mesti dimasukkan sebagai formula larik, iaitu:

• Dalam Office 365 dengan kemas kini terkini yang dipasang dan sokongan untuk tatasusunan dinamik, anda boleh menekan sahaja Masukkan.
• Dalam semua versi lain, selepas memasukkan formula, anda perlu menekan pintasan papan kekunci Ctrl+Beralih+Masukkan, yang secara automatik akan menambah pendakap kerinting padanya dalam bar formula.

Kaedah 2: Carian terbalik dengan fungsi LOOKUP baharu

Saya sudah menulis artikel panjang dengan video tentang ciri baharu VIEW (XLOOKUP), yang muncul dalam versi terkini Office untuk menggantikan VLOOKUP lama (TAMPILAN). Dengan bantuan BROWSE, tugas kami diselesaikan dengan agak asas, kerana. untuk fungsi ini (tidak seperti VLOOKUP), anda boleh menetapkan arah carian secara eksplisit: atas ke bawah atau bawah ke atas - hujah terakhirnya (-1) bertanggungjawab untuk ini:

Kaedah 3. Cari rentetan dengan tarikh terkini

Jika dalam data sumber kita mempunyai lajur dengan nombor siri atau tarikh yang memainkan peranan yang sama, maka tugas itu diubah suai - kita perlu mencari bukan baris terakhir (paling rendah) dengan padanan, tetapi baris dengan yang terkini ( maksimum) tarikh.

Saya telah membincangkan secara terperinci cara melakukan ini menggunakan fungsi klasik, dan sekarang mari cuba menggunakan kuasa fungsi tatasusunan dinamik baharu. Untuk keindahan dan kemudahan yang lebih baik, kami juga menukar jadual asal kepada jadual "pintar" menggunakan pintasan papan kekunci Ctrl+T atau perintah Laman Utama – Format sebagai jadual (Laman Utama — Format sebagai Jadual).

Dengan bantuan mereka, "pasangan pembunuh" ini menyelesaikan masalah kami dengan sangat anggun:

Di sini:

• Berfungsi dahulu FILTER (PENAPIS) memilih hanya baris tersebut daripada jadual kami di mana dalam lajur Pelanggan – nama yang kita perlukan.
• Kemudian fungsi GRED (SORT) mengisih baris yang dipilih mengikut tarikh dalam tertib menurun, dengan tawaran terbaharu di bahagian atas.
• fungsi INDEKS (INDEX) mengekstrak baris pertama, iaitu mengembalikan perdagangan terakhir yang kita perlukan.
• Dan, akhirnya, fungsi FILTER luaran mengalih keluar lajur 1 dan 3 tambahan daripada hasil carian (Kod pesanan и Pelanggan) dan hanya meninggalkan tarikh dan amaun. Untuk ini, pelbagai pemalar digunakan. {0;1;0;1}, menentukan lajur yang kita mahu (1) atau tidak mahu (0) dipaparkan.
  

Kaedah 4: Mencari Padanan Terakhir dalam Power Query

Nah, demi kesempurnaan, mari kita lihat penyelesaian kepada masalah carian terbalik kami menggunakan alat tambah Power Query. Dengan bantuannya, semuanya diselesaikan dengan cepat dan indah.

1. Mari tukar jadual asal kami kepada jadual "pintar" menggunakan pintasan papan kekunci Ctrl+T atau perintah Laman Utama – Format sebagai jadual (Laman Utama — Format sebagai Jadual).

2. Muatkannya ke dalam Power Query dengan butang Daripada Jadual/Julat tab Tarikh (Data — Daripada Jadual/Julat).

3. Kami mengisih (melalui senarai juntai bawah penapis dalam pengepala) jadual kami dalam susunan tarikh menurun, supaya transaksi terkini berada di atas.

4… Dalam tab Transformasi memilih pasukan Kumpulan oleh (Ubah — Kumpulan Mengikut) dan tetapkan kumpulan mengikut pelanggan, dan sebagai fungsi pengagregatan, pilih pilihan Semua baris (Semua baris). Anda boleh menamakan lajur baharu apa sahaja yang anda suka – contohnya Butiran.

Selepas mengumpulkan, kami akan mendapat senarai nama unik pelanggan kami dan dalam lajur Butiran – jadual dengan semua urus niaga setiap satu daripadanya, di mana baris pertama akan menjadi urus niaga terkini, itulah yang kami perlukan:

5. Tambahkan lajur baru yang dikira dengan butang Lajur tersuai tab Tambah lajur (Tambah lajur — Tambah lajur tersuai)dan masukkan formula berikut:

Di sini Butiran – ini ialah lajur tempat kami mengambil jadual oleh pelanggan, dan 0 {} ialah nombor baris yang ingin kami keluarkan (penomboran baris dalam Power Query bermula dari sifar). Kami mendapat lajur dengan rekod (rekod), di mana setiap entri ialah baris pertama daripada setiap jadual:

Ia kekal untuk mengembangkan kandungan semua rekod dengan butang dengan anak panah berganda dalam pengepala lajur Perjanjian terakhir memilih lajur yang dikehendaki:

… dan kemudian padamkan lajur yang tidak diperlukan lagi Butiran dengan mengklik kanan pada tajuknya - Alih keluar lajur (Alih keluar lajur).

Selepas memuat naik keputusan ke helaian melalui Rumah — Tutup dan muat — Tutup dan muatkan (Laman Utama — Tutup & Muatkan — Tutup & Muatkan ke…) kami akan mendapat jadual yang bagus dengan senarai transaksi terkini, seperti yang kami mahu:

Apabila anda menukar data sumber, anda tidak boleh lupa untuk mengemas kini keputusan dengan mengklik kanan padanya - arahan Kemas kini & Simpan (Segarkan semula) atau pintasan papan kekunci Ctrl+Alt+F5.

• Fungsi LOOKUP adalah turunan daripada VLOOKUP
• Cara menggunakan fungsi tatasusunan dinamik baharu SORT, FILTER dan UNIC
• Mencari sel bukan kosong terakhir dalam baris atau lajur dengan fungsi LOOKUP