Contents [show]
Alle klassieke soek- en tipevervangingsfunksies VPR (VERSOEKING), GPR (LOOKUP), MEER BLOOTSTELLING (MATCH) en dié soos hulle het een belangrike kenmerk – hulle soek van die begin tot die einde, maw links-na-regs of bo-na-onder in die brondata. Sodra die eerste ooreenstemmende passing gevind word, stop die soektog en slegs die eerste voorkoms van die element wat ons benodig word gevind.
Wat om te doen as ons nie die eerste, maar die laaste gebeurtenis moet vind nie? Byvoorbeeld, die laaste transaksie vir die kliënt, die laaste betaling, die mees onlangse bestelling, ens.?
Metode 1: Vind die laaste ry met 'n skikkingsformule
As die oorspronklike tabel nie 'n kolom met 'n datum of 'n reeksnommer van 'n ry (bestelling, betaling …) het nie, dan is ons taak in werklikheid om die laaste ry te vind wat aan die gegewe voorwaarde voldoen. Dit kan gedoen word met die volgende skikkingsformule:
hier:
- funksie IF (AS) kontroleer alle selle in 'n kolom een vir een Kliënt en vertoon die reëlnommer as dit die naam bevat wat ons benodig. Die reëlnommer op die blad word deur die funksie aan ons gegee LINE (RY), maar aangesien ons die rynommer in die tabel nodig het, moet ons addisioneel 1 aftrek, want ons het 'n kopskrif in die tabel.
- Dan die funksie MAX (MAX) kies die maksimum waarde uit die gevormde stel rynommers, dws die nommer van die mees onlangse lyn van die kliënt.
- funksie INDEKS (INDEKS) gee die inhoud van die sel terug met die laaste getal wat gevind is uit enige ander vereiste tabelkolom (Bestel kode).
Dit alles moet ingevoer word as skikkingformule, dws:
- In Office 365 met die nuutste opdaterings geïnstalleer en ondersteuning vir dinamiese skikkings, kan jy eenvoudig druk Tik.
- In alle ander weergawes, nadat u die formule ingevoer het, sal u die sleutelbordkortpad moet druk Ctrl+Skuif+Tik, wat outomaties krulhakies daarby in die formulebalk sal voeg.
Metode 2: Omgekeerde opsoek met die nuwe OPSOEK-funksie
Ek het reeds 'n lang artikel geskryf met 'n video oor 'n nuwe kenmerk VIEW (XLOOKUP), wat in die nuutste weergawes van Office verskyn het om die ou VLOOKUP te vervang (VERSOEKING). Met die hulp van BROWSE word ons taak redelik elementêr opgelos, want. vir hierdie funksie (anders as VLOOKUP), kan jy die soekrigting uitdruklik stel: bo-na-onder of onder-na-bo – sy laaste argument (-1) is hiervoor verantwoordelik:
Metode 3. Soek vir 'n string met die jongste datum
As ons in die brondata 'n kolom het met 'n reeksnommer of 'n datum wat 'n soortgelyke rol speel, dan word die taak gewysig - ons moet nie die laaste (laagste) reël met 'n passing vind nie, maar die reël met die nuutste ( maksimum) datum.
Ek het reeds in detail bespreek hoe om dit te doen met klassieke funksies, en kom ons probeer nou om die krag van die nuwe dinamiese skikkingsfunksies te gebruik. Vir groter skoonheid en gerief, omskep ons ook die oorspronklike tabel in 'n "slim" tabel met 'n sleutelbordkortpad Ctrl+T of opdragte Tuis – Formateer as 'n tabel (Tuis — Formateer as tabel).
Met hul hulp los hierdie “moordenaarpaartjie” ons probleem baie grasieus op:
hier:
- Funksie eers FILTER (FILTER) kies slegs daardie rye uit ons tabel waar in die kolom Kliënt – die naam wat ons nodig het.
- Dan die funksie GRAAD (SORTEER) sorteer die geselekteerde rye volgens datum in dalende volgorde, met die mees onlangse transaksie boaan.
- funksie INDEKS (INDEKS) onttrek die eerste ry, dws gee die laaste handel wat ons nodig het terug.
- En uiteindelik verwyder die eksterne FILTER-funksie die ekstra 1ste en 3de kolomme uit die resultate (Bestel kode и Kliënt) en laat slegs die datum en bedrag. Hiervoor word 'n reeks konstantes gebruik. {0;1;0;1}, wat definieer watter kolomme ons wil hê (1) of nie wil hê (0) moet vertoon nie.
Metode 4: Vind die laaste pasmaat in Power Query
Wel, volledigheidshalwe, kom ons kyk na 'n oplossing vir ons omgekeerde soekprobleem deur die Power Query-byvoeging te gebruik. Met haar hulp word alles baie vinnig en pragtig opgelos.
1. Kom ons verander ons oorspronklike tabel in 'n "slim" een deur 'n sleutelbordkortpad te gebruik Ctrl+T of opdragte Tuis – Formateer as 'n tabel (Tuis — Formateer as tabel).
2. Laai dit in Power Query met die knoppie Van tabel/reeks tab data (Data – van tabel/reeks).
3. Ons sorteer (deur die aftreklys van die filter in die kopskrif) ons tabel in dalende volgorde van datum, sodat die mees onlangse transaksies bo-aan is.
4... Op die blad Transformasie kies 'n span Groepeer volgens (Transformeer — Groepeer volgens) en stel die groepering volgens kliënte, en as 'n samevoegingsfunksie, kies die opsie Alle lyne (Alle rye). Jy kan die nuwe kolom enige iets noem wat jy wil – byvoorbeeld besonderhede.
Na groepering sal ons 'n lys met unieke name van ons kliënte en in die kolom kry besonderhede – tabelle met al die transaksies van elk van hulle, waar die eerste reël die nuutste transaksie sal wees, wat is wat ons nodig het:
5. Voeg 'n nuwe berekende kolom by met die knoppie Gepasmaakte kolom tab Voeg kolom by (Voeg kolom by – Voeg pasgemaakte kolom by)en voer die volgende formule in:
hier besonderhede – dit is die kolom waaruit ons tabelle deur kliënte neem, en 0 {} is die nommer van die ry wat ons wil onttrek (rynommering in Power Query begin vanaf nul). Ons kry 'n kolom met rekords (rekord), waar elke inskrywing die eerste ry van elke tabel is:
Dit bly om die inhoud van alle rekords uit te brei met die knoppie met dubbele pyle in die kolomkop Laaste transaksie kies die gewenste kolomme:
… en verwyder dan die kolom wat nie meer nodig is nie besonderhede deur regs te klik op sy titel – Verwyder kolomme (Verwyder kolomme).
Na die oplaai van die resultate na die blad deur Tuis — Maak toe en laai — Maak toe en laai in (Tuis — Maak toe en laai — Maak toe en laai na...) ons sal so 'n mooi tabel kry met 'n lys van onlangse transaksies, soos ons wou hê:
Wanneer jy die brondata verander, moet jy nie vergeet om die resultate op te dateer deur met die rechtermuisknop daarop te klik nie – die opdrag Dateer op en stoor (Verfris) of sleutelbordkortpad Ctrl+alt+F5.
- Die OPSOEK-funksie is 'n afstammeling van VLOOKUP
- Hoe om die nuwe dinamiese skikkingsfunksies SORT, FILTER en UNIC te gebruik
- Vind die laaste nie-leë sel in 'n ry of kolom met die OPSOEK-funksie