Innhold
Alle klassiske søke- og typesubstitusjonsfunksjoner VPR (VISNING), GPR (OPPSLAG), MER UTSATT (KAMP) og slike som dem har én viktig funksjon – de søker fra begynnelse til slutt, dvs. venstre-til-høyre eller topp-til-bunn i kildedataene. Så snart det første samsvarende treffet er funnet, stopper søket og bare den første forekomsten av elementet vi trenger blir funnet.
Hva skal vi gjøre hvis vi trenger å finne ikke den første, men den siste forekomsten? For eksempel, siste transaksjon for klienten, siste betaling, siste ordre osv.?
Metode 1: Finne den siste raden med en matriseformel
Hvis den opprinnelige tabellen ikke har en kolonne med en dato eller et serienummer for en rad (ordre, betaling ...), så er vår oppgave faktisk å finne den siste raden som tilfredsstiller den gitte betingelsen. Dette kan gjøres med følgende matriseformel:
Her:
- Funksjon IF (HVIS) sjekker alle cellene i en kolonne én etter én Kunde- og viser linjenummeret hvis det inneholder navnet vi trenger. Linjenummeret på arket er gitt til oss av funksjonen LINE (RAD), men siden vi trenger radnummeret i tabellen, må vi i tillegg trekke fra 1, fordi vi har en overskrift i tabellen.
- Deretter funksjonen MAX (MAKS) velger den maksimale verdien fra det dannede settet med radnummer, dvs. nummeret til den siste linjen til klienten.
- Funksjon INDEX (INDEKS) returnerer innholdet i cellen med det siste tallet som ble funnet fra en annen nødvendig tabellkolonne (Bestillingskode).
Alt dette må legges inn som matriseformel, dvs:
- I Office 365 med de siste oppdateringene installert og støtte for dynamiske arrays kan du ganske enkelt trykke Enter.
- I alle andre versjoner, etter å ha skrevet inn formelen, må du trykke på hurtigtasten Ctrl+Skift+Enter, som automatisk legger til krøllete klammeparenteser i formellinjen.
Metode 2: Omvendt oppslag med den nye OPPSLAG-funksjonen
Jeg har allerede skrevet en lang artikkel med en video om en ny funksjon SE (XLOOKUP), som dukket opp i de nyeste versjonene av Office for å erstatte den gamle VLOOKUP (VISNING). Ved hjelp av BROWSE løses oppgaven vår ganske elementært, pga. for denne funksjonen (i motsetning til VLOOKUP), kan du eksplisitt angi søkeretningen: top-down eller bottom-up – det siste argumentet (-1) er ansvarlig for dette:
Metode 3. Søk etter en streng med siste dato
Hvis vi i kildedataene har en kolonne med et serienummer eller en dato som spiller en lignende rolle, blir oppgaven endret – vi må ikke finne den siste (laveste) linjen med samsvar, men linjen med den siste ( maksimum) dato.
Jeg har allerede diskutert i detalj hvordan du gjør dette ved å bruke klassiske funksjoner, og la oss nå prøve å bruke kraften til de nye dynamiske array-funksjonene. For større skjønnhet og bekvemmelighet konverterer vi også det originale bordet til et "smart" bord ved hjelp av en hurtigtast Ctrl+T eller kommandoer Hjem – Formater som en tabell (Hjem — Formater som tabell).
Med deres hjelp løser dette "morderparet" problemet vårt veldig grasiøst:
Her:
- Funksjon først FILTER (FILTER) velger bare de radene fra tabellen vår hvor i kolonnen Kunde- – navnet vi trenger.
- Deretter funksjonen GRADE (SORTERE) sorterer de valgte radene etter dato i synkende rekkefølge, med den siste avtalen øverst.
- Funksjon INDEX (INDEKS) trekker ut den første raden, dvs. returnerer den siste handelen vi trenger.
- Og til slutt fjerner den eksterne FILTER-funksjonen den ekstra første og tredje kolonnen fra resultatene (Bestillingskode и Kunde-) og etterlater bare dato og beløp. For dette brukes en rekke konstanter. {0;1;0;1}, definere hvilke kolonner vi vil (1) eller ikke vil at (0) skal vise.
Metode 4: Finne det siste treffet i Power Query
Vel, for fullstendighetens skyld, la oss se på en løsning på vårt omvendte søkeproblem ved å bruke Power Query-tillegget. Med hennes hjelp er alt løst veldig raskt og vakkert.
1. La oss konvertere vår originale tabell til en "smart" ved å bruke en hurtigtast Ctrl+T eller kommandoer Hjem – Formater som en tabell (Hjem — Formater som tabell).
2. Last den inn i Power Query med knappen Fra Tabell/Rekkevidde tab Data (Data – fra tabell/område).
3. Vi sorterer (gjennom rullegardinlisten til filteret i overskriften) tabellen vår i synkende rekkefølge etter dato, slik at de siste transaksjonene er øverst.
4… I fanen Transformation velge et lag Gruppe av (Transformer – Grupper etter) og angi grupperingen etter kunder, og velg alternativet som en aggregeringsfunksjon Alle linjer (Alle rader). Du kan navngi den nye kolonnen hva du vil – for eksempel Detaljer.
Etter gruppering vil vi få en liste over unike navn på våre kunder og i kolonnen Detaljer – tabeller med alle transaksjonene til hver av dem, der den første linjen vil være den siste transaksjonen, som er det vi trenger:
5. Legg til en ny beregnet kolonne med knappen Egendefinert kolonne tab Legg til kolonne (Legg til kolonne – Legg til egendefinert kolonne)og skriv inn følgende formel:
Her Detaljer – dette er kolonnen vi henter tabeller fra kunder fra, og 0 {} er nummeret på raden vi ønsker å trekke ut (radnummerering i Power Query starter fra null). Vi får en kolonne med poster (Record), der hver oppføring er den første raden fra hver tabell:
Det gjenstår å utvide innholdet i alle poster med knappen med doble piler i kolonneoverskriften Siste avtale velge de ønskede kolonnene:
… og slett deretter kolonnen som ikke lenger er nødvendig Detaljer ved å høyreklikke på tittelen – Fjern kolonner (Fjern kolonner).
Etter å ha lastet opp resultatene til arket gjennom Hjem — Lukk og last — Lukk og last inn (Hjem — Lukk og last — Lukk og last til...) vi vil få et så fint bord med en liste over nylige transaksjoner, som vi ønsket:
Når du endrer kildedataene, må du ikke glemme å oppdatere resultatene ved å høyreklikke på dem – kommandoen Oppdater og lagre (Forfriske) eller hurtigtast Ctrl+andre+F5.
- OPPSLAK-funksjonen er en etterkommer av VLOOKUP
- Slik bruker du de nye dynamiske array-funksjonene SORT, FILTER og UNIC
- Finne den siste ikke-blanke cellen i en rad eller kolonne med OPSLAKK-funksjonen