Innehåll
Alla klassiska sök- och typsubstitutionsfunktioner VPR (VISNING), GPR (SLÖKUP), MER EXPONERAD (MATCH) och sådana som dem har en viktig egenskap – de söker från början till slut, dvs från vänster till höger eller uppifrån och ner i källdata. Så snart den första matchande matchningen hittas, stoppas sökningen och endast den första förekomsten av elementet vi behöver hittas.
Vad ska vi göra om vi inte behöver hitta den första utan den sista händelsen? Till exempel, den senaste transaktionen för kunden, den senaste betalningen, den senaste beställningen, etc.?
Metod 1: Hitta den sista raden med en matrisformel
Om den ursprungliga tabellen inte har en kolumn med ett datum eller ett serienummer för en rad (order, betalning …), så är vår uppgift i själva verket att hitta den sista raden som uppfyller det givna villkoret. Detta kan göras med följande matrisformel:
Här:
- Funktion IF (OM) kontrollerar alla celler i en kolumn en efter en Kund och visar radnumret om det innehåller det namn vi behöver. Radnumret på arket ges till oss av funktionen LINJE (RAD), men eftersom vi behöver radnumret i tabellen måste vi dessutom subtrahera 1, eftersom vi har en rubrik i tabellen.
- Sedan funktionen MAX (MAX) väljer det maximala värdet från den bildade uppsättningen radnummer, dvs numret på klientens senaste rad.
- Funktion INDEX (INDEX) returnerar innehållet i cellen med det hittade sista numret från någon annan obligatorisk tabellkolumn (Beställningskod).
Allt detta måste anges som matrisformel, dvs:
- I Office 365 med de senaste uppdateringarna installerade och stöd för dynamiska arrayer kan du helt enkelt trycka ange.
- I alla andra versioner, efter att ha angett formeln, måste du trycka på kortkommandot ctrl+shift+ange, vilket automatiskt lägger till lockiga klammerparenteser i formelfältet.
Metod 2: Omvänd sökning med den nya UPPSÖKNINGSfunktionen
Jag har redan skrivit en lång artikel med en video om en ny funktion SE (XLOOKUP), som dök upp i de senaste versionerna av Office för att ersätta den gamla VLOOKUP (VISNING). Med hjälp av BROWSE löses vår uppgift ganska elementärt, eftersom. för denna funktion (till skillnad från VLOOKUP) kan du uttryckligen ställa in sökriktningen: top-down eller bottom-up – dess sista argument (-1) är ansvarig för detta:
Metod 3. Sök efter en sträng med senaste datum
Om vi i källdata har en kolumn med ett serienummer eller ett datum som spelar en liknande roll, så ändras uppgiften - vi behöver inte hitta den sista (lägsta) raden med en matchning, utan raden med den senaste ( max) datum.
Jag har redan diskuterat i detalj hur man gör detta med klassiska funktioner, och låt oss nu försöka använda kraften i de nya dynamiska array-funktionerna. För större skönhet och bekvämlighet konverterar vi också originalbordet till ett "smart" bord med en kortkommando ctrl+T eller kommandon Hem – Formatera som en tabell (Hem — Formatera som tabell).
Med deras hjälp löser detta "mördarpar" vårt problem mycket graciöst:
Här:
- Funktion först FILTER (FILTRERA) väljer bara de rader från vår tabell där i kolumnen Kund – namnet vi behöver.
- Sedan funktionen BETYG (SORTERA) sorterar de markerade raderna efter datum i fallande ordning, med den senaste affären överst.
- Funktion INDEX (INDEX) extraherar den första raden, dvs returnerar den sista handeln vi behöver.
- Och slutligen tar den externa FILTER-funktionen bort den extra första och tredje kolumnen från resultaten (Beställningskod и Kund) och lämnar endast datum och belopp. För detta används en uppsättning konstanter. {0;1;0;1}, definiera vilka kolumner vi vill att (1) eller inte vill att (0) ska visa.
Metod 4: Hitta den sista matchningen i Power Query
Tja, för fullständighetens skull, låt oss titta på en lösning på vårt omvända sökproblem med Power Query-tillägget. Med hennes hjälp löses allt mycket snabbt och vackert.
1. Låt oss konvertera vår ursprungliga tabell till en "smart" med ett kortkommando ctrl+T eller kommandon Hem – Formatera som en tabell (Hem — Formatera som tabell).
2. Ladda den i Power Query med knappen Från tabell/sortiment fliken Data (Data – från tabell/intervall).
3. Vi sorterar (genom rullgardinsmenyn för filtret i rubriken) vår tabell i fallande datumordning, så att de senaste transaktionerna ligger överst.
4… På fliken Transformation välja ett lag Grupp av (Omvandla — Gruppera efter) och ställ in grupperingen efter kunder, och välj alternativet som en aggregeringsfunktion Alla rader (Alla rader). Du kan namnge den nya kolumnen vad du vill – till exempel Detaljer.
Efter gruppering kommer vi att få en lista med unika namn på våra kunder och i kolumnen Detaljer – tabeller med alla transaktioner för var och en av dem, där den första raden kommer att vara den senaste transaktionen, vilket är vad vi behöver:
5. Lägg till en ny beräknad kolumn med knappen Anpassad kolumn fliken Lägg till kolumn (Lägg till kolumn — Lägg till anpassad kolumn)och skriv in följande formel:
Här Detaljer – det här är kolumnen från vilken vi tar tabeller efter kunder, och 0 {} är numret på raden vi vill extrahera (radnumreringen i Power Query börjar från noll). Vi får en kolumn med poster (Spela in), där varje post är den första raden från varje tabell:
Det återstår att utöka innehållet i alla poster med knappen med dubbla pilar i kolumnrubriken Sista affären välj önskade kolumner:
… och radera sedan kolumnen som inte längre behövs Detaljer genom att högerklicka på dess titel – Ta bort kolumner (Ta bort kolumner).
Efter att ha laddat upp resultaten till arket igenom Hem — Stäng och ladda — Stäng och ladda in (Hem — Stäng & Ladda — Stäng & Ladda till...) vi kommer att få ett så fint bord med en lista över de senaste transaktionerna som vi ville ha:
När du ändrar källdata får du inte glömma att uppdatera resultaten genom att högerklicka på dem – kommandot Uppdatera & spara (Uppdatera) eller kortkommandon ctrl+andra+F5.
- SÖKNINGSfunktionen är en ättling till VLOOKUP
- Hur man använder de nya dynamiska array-funktionerna SORT, FILTER och UNIC
- Hitta den sista icke-tomma cellen i en rad eller kolumn med UPPSÖKNINGSfunktionen