Paragraf
Kabeh fungsi telusuran klasik lan substitusi jinis VPR (VLOOKUP), GPR (HLOOKUP), LUWIH DIBUKA (PERTANDINGAN) lan sing kaya wong-wong mau duwe siji fitur penting - padha nggoleki saka wiwitan nganti pungkasan, yaiku kiwa-tengen utawa ndhuwur-kanggo-ngisor ing data sumber. Sanalika pertandhingan sing cocog pisanan ditemokake, telusuran mandheg lan mung kedadeyan pisanan saka unsur sing kita butuhake ditemokake.
Apa sing kudu ditindakake yen kita ora nemokake sing pisanan, nanging kedadeyan pungkasan? Contone, transaksi pungkasan kanggo klien, pembayaran pungkasan, pesenan paling anyar, etc.?
Cara 1: Nemokake Baris Terakhir kanthi Rumus Array
Yen tabel asli ora duwe kolom karo tanggal utawa nomer seri saka baris (pesenan, pembayaran ...), banjur tugas kita, nyatane, kanggo nemokake baris pungkasan sing gawe marem kondisi tartamtu. Iki bisa ditindakake kanthi rumus array ing ngisor iki:
Kene:
- fungsi IF (Yen) mriksa kabeh sel ing kolom siji-siji customer lan nampilake nomer baris yen ngemot jeneng kita kudu. Nomer baris ing sheet diwenehi kanggo kita dening fungsi LINE (ROW), nanging amarga kita kudu nomer baris ing meja, kita uga kudu nyuda 1, amarga kita duwe header ing meja.
- Banjur fungsi MAX (MAX) milih nilai maksimum saka pesawat kawangun saka nomer baris, IE nomer baris paling anyar saka klien.
- fungsi INDEKS (INDEX) ngasilake isi sel kanthi nomer pungkasan sing ditemokake saka kolom tabel liyane sing dibutuhake (Kode pesenan).
Kabeh iki kudu ngetik minangka rumus susunan, yaiku:
- Ing Office 365 kanthi nganyari paling anyar sing diinstal lan ndhukung array dinamis, sampeyan mung bisa menet Ketik.
- Ing kabeh versi liyane, sawise ngetik rumus, sampeyan kudu mencet trabasan keyboard Ctrl+shift+Ketik, sing bakal nambahake kurung kriting kanthi otomatis ing garis rumus.
Cara 2: Goleki mbalikke nganggo fungsi LOOKUP anyar
Aku wis nulis artikel dawa karo video babagan fitur anyar Deleng (XLOOKUP), sing katon ing versi paling anyar saka Office kanggo ngganti VLOOKUP lawas (VLOOKUP). Kanthi bantuan BROWSE, tugas kita ditanggulangi kanthi dhasar, amarga. kanggo fungsi iki (ora kaya VLOOKUP), sampeyan bisa nyetel arah telusuran kanthi jelas: top-down utawa bottom-up - argumen pungkasan (-1) tanggung jawab kanggo iki:
Cara 3. Telusuri senar kanthi tanggal paling anyar
Yen ing data sumber kita duwe kolom kanthi nomer seri utawa tanggal sing nduweni peran sing padha, mula tugas kasebut diowahi - kita kudu nemokake dudu baris pungkasan (paling ngisor) kanthi pertandhingan, nanging baris kanthi paling anyar ( maksimum) tanggal.
Aku wis rembugan ing rinci carane nindakake iki nggunakake fungsi klasik, lan saiki ayo kang nyoba kanggo nggunakake daya saka fungsi array dinamis anyar. Kanggo kaendahan lan penak, kita uga ngowahi tabel asli dadi meja "pinter" nggunakake trabasan keyboard Ctrl+T utawa dhawuh Ngarep - Format minangka tabel (Ngarep - Format minangka Tabel).
Kanthi bantuan, "pasangan pembunuh" iki ngrampungake masalah kita kanthi apik:
Kene:
- Fungsi pisanan Nyaring (FILTER) milih mung sing larik saka tabel kita ngendi ing kolom customer – jeneng kita kudu.
- Banjur fungsi GRADE (SORT) ngurutake baris sing dipilih miturut tanggal kanthi urutan mudhun, kanthi kesepakatan paling anyar ing ndhuwur.
- fungsi INDEKS (INDEX) mbeber baris pisanan, IE ngasilake perdagangan pungkasan kita kudu.
- Lan, pungkasane, fungsi FILTER eksternal mbusak kolom 1 lan 3 ekstra saka asil (Kode pesenan и customer) lan mung ninggalake tanggal lan jumlah. Kanggo iki, array saka konstanta digunakake. {0;1;0;1}, nemtokake kolom sing pengin (1) utawa ora pengin (0) ditampilake.
Cara 4: Nemokake Pertandhingan Terakhir ing Power Query
Dadi, kanggo ngrampungake, ayo goleki solusi kanggo masalah telusuran mbalikke nggunakake add-in Power Query. Kanthi bantuan dheweke, kabeh wis ditanggulangi kanthi cepet lan apik.
1. Ayo ngowahi tabel asli dadi "pinter" nggunakake trabasan keyboard Ctrl+T utawa dhawuh Ngarep - Format minangka tabel (Ngarep - Format minangka Tabel).
2. Muat menyang Power Query kanthi tombol Saka Tabel / Range tab Data (Data — Saka Tabel/Rentang).
3. Kita ngurutake (liwat dhaptar gulung mudhun saka saringan ing header) tabel ing urutan tanggal mudhun, supaya transaksi paling anyar ana ing ndhuwur.
4… Ing tab transformasi milih tim Kelompok dening (Transform - Grup Miturut) lan nyetel panglompokan dening pelanggan, lan minangka fungsi aggregating, pilih pilihan Kabeh garis (Kabeh baris). Sampeyan bisa menehi jeneng kolom anyar apa wae sing disenengi - contone Details.
Sawise nglumpukake, kita bakal entuk dhaptar jeneng unik klien lan ing kolom kasebut Details - tabel kanthi kabeh transaksi saben wong, ing ngendi baris pisanan bakal dadi transaksi paling anyar, yaiku sing dibutuhake:
5. Tambah kolom sing diwilang anyar nganggo tombol Kolom khusus tab Tambah kolom (Tambah kolom - Tambah kolom khusus)lan ketik rumus ing ngisor iki:
kene Details – iki kolom saka kang kita njupuk tabel dening pelanggan, lan 0 {} yaiku nomer baris sing pengin diekstrak (nomer baris ing Power Query diwiwiti saka nol). Kita entuk kolom kanthi cathetan (Rekam), ing ngendi saben entri minangka baris pisanan saka saben tabel:
Iku tetep kanggo nggedhekake isi kabeh cathetan karo tombol panah pindho ing header kolom Kesepakatan pungkasan milih kolom sing dikarepake:
... banjur mbusak kolom sing ora perlu maneh Details kanthi ngeklik tengen ing judhul - Mbusak kolom (Busak kolom).
Sawise upload asil kanggo sheet liwat Ngarep - Nutup lan mbukak - Nutup lan mbukak (Ngarep - Tutup & Muat - Tutup & Muat menyang…) kita bakal entuk tabel sing apik karo dhaptar transaksi anyar, kaya sing dikarepake:
Nalika sampeyan ngganti data sumber, sampeyan ora kudu lali nganyari asil kanthi ngeklik tengen - printah Nganyari & Simpen (Refresh) utawa trabasan keyboard Ctrl+Alt+F5.
- Fungsi LOOKUP minangka turunan saka VLOOKUP
- Cara nggunakake fungsi array dinamis anyar SORT, FILTER, lan UNIC
- Nemokake sel ora kosong pungkasan ing baris utawa kolom kanthi fungsi LOOKUP