Indhold
Alle klassiske søge- og typeerstatningsfunktioner VPR (OPLYSNING), GPR (HOPSLAG), MERE UDSAT (MATCH) og dem som dem har én vigtig egenskab – de søger fra begyndelsen til slutningen, dvs. fra venstre mod højre eller fra top til bund i kildedataene. Så snart det første matchende match er fundet, stopper søgningen, og kun den første forekomst af det element, vi har brug for, bliver fundet.
Hvad skal vi gøre, hvis vi ikke skal finde den første, men den sidste forekomst? For eksempel den sidste transaktion for kunden, den sidste betaling, den seneste ordre osv.?
Metode 1: Find den sidste række med en matrixformel
Hvis den oprindelige tabel ikke har en kolonne med en dato eller et løbenummer på en række (ordre, betaling …), så er vores opgave i virkeligheden at finde den sidste række, der opfylder den givne betingelse. Dette kan gøres med følgende matrixformel:
Her:
- Funktion IF (HVIS) kontrollerer alle celler i en kolonne én efter én Kunden og viser linjenummeret, hvis det indeholder det navn, vi har brug for. Linjenummeret på arket er givet til os af funktionen LINE (RÆKKE), men da vi skal bruge rækkenummeret i tabellen, skal vi desuden trække 1 fra, fordi vi har en overskrift i tabellen.
- Derefter funktionen MAX (MAKS) vælger den maksimale værdi fra det dannede sæt rækkenumre, dvs. nummeret på klientens seneste linje.
- Funktion INDEX (INDEKS) returnerer indholdet af cellen med det fundne sidste tal fra enhver anden påkrævet tabelkolonne (Ordrenummer).
Alt dette skal indtastes som matrixformel, dvs.:
- I Office 365 med de seneste opdateringer installeret og understøttelse af dynamiske arrays kan du blot trykke Indtast.
- I alle andre versioner, efter at have indtastet formlen, skal du trykke på tastaturgenvejen Ctrl+Flytte+Indtast, som automatisk tilføjer krøllede klammeparenteser til den i formellinjen.
Metode 2: Omvendt opslag med den nye OPSLAG-funktion
Jeg har allerede skrevet en lang artikel med en video om en ny funktion UDSIGT (XLOOKUP), som dukkede op i de seneste versioner af Office for at erstatte det gamle VLOOKUP (OPLYSNING). Ved hjælp af BROWSE løses vores opgave ganske elementært, pga. for denne funktion (i modsætning til VLOOKUP) kan du udtrykkeligt indstille søgeretningen: top-down eller bottom-up – dets sidste argument (-1) er ansvarlig for dette:
Metode 3. Søg efter en streng med den seneste dato
Hvis vi i kildedataene har en kolonne med et serienummer eller en dato, der spiller en lignende rolle, så ændres opgaven - vi skal ikke finde den sidste (laveste) linje med et match, men linjen med den seneste ( maksimum) dato.
Jeg har allerede diskuteret i detaljer, hvordan man gør dette ved hjælp af klassiske funktioner, og lad os nu prøve at bruge kraften i de nye dynamiske array-funktioner. For større skønhed og bekvemmelighed konverterer vi også den originale tabel til en "smart" tabel ved hjælp af en tastaturgenvej Ctrl+T eller kommandoer Hjem – Formater som en tabel (Hjem — Formater som tabel).
Med deres hjælp løser dette "morderpar" vores problem meget yndefuldt:
Her:
- Funktion først FILTER (FILTER) vælger kun de rækker fra vores tabel, hvor i kolonnen Kunden – det navn, vi har brug for.
- Derefter funktionen GRADE (SORTERE) sorterer de valgte rækker efter dato i faldende rækkefølge, med den seneste aftale øverst.
- Funktion INDEX (INDEKS) udtrækker den første række, dvs. returnerer den sidste handel, vi skal bruge.
- Og endelig fjerner den eksterne FILTER-funktion den ekstra 1. og 3. kolonne fra resultaterne (Ordrenummer и Kunden) og efterlader kun dato og beløb. Til dette bruges en række konstanter. {0;1;0;1}, der definerer hvilke kolonner vi ønsker (1) eller ikke ønsker (0) skal vise.
Metode 4: Find det sidste match i Power Query
Nå, lad os for fuldstændighedens skyld se på en løsning på vores omvendte søgeproblem ved hjælp af Power Query-tilføjelsen. Med hendes hjælp er alt løst meget hurtigt og smukt.
1. Lad os konvertere vores originale tabel til en "smart" ved hjælp af en tastaturgenvej Ctrl+T eller kommandoer Hjem – Formater som en tabel (Hjem — Formater som tabel).
2. Indlæs den i Power Query med knappen Fra tabel/område fanen data (Data — fra tabel/område).
3. Vi sorterer (via rullelisten for filteret i overskriften) vores tabel i faldende datoorden, så de seneste transaktioner er øverst.
4… I fanen Transformation vælge et hold Gruppe af (Transformer - Gruppér efter) og indstil grupperingen efter kunder, og vælg muligheden som en sammenlægningsfunktion Alle linjer (Alle rækker). Du kan navngive den nye kolonne, hvad du vil – f.eks Detaljer.
Efter gruppering får vi en liste over unikke navne på vores kunder og i kolonnen Detaljer – tabeller med alle transaktionerne for hver af dem, hvor den første linje vil være den seneste transaktion, hvilket er, hvad vi har brug for:
5. Tilføj en ny beregnet kolonne med knappen Brugerdefineret kolonne fanen Tilføj kolonne (Tilføj kolonne — Tilføj tilpasset kolonne)og indtast følgende formel:
Her Detaljer – det er den kolonne, hvorfra vi tager tabeller efter kunder, og 0 {} er nummeret på den række, vi vil udtrække (rækkenummerering i Power Query starter fra nul). Vi får en kolonne med poster (Optage), hvor hver post er den første række fra hver tabel:
Det er tilbage at udvide indholdet af alle poster med knappen med dobbeltpile i kolonneoverskriften Sidste aftale valg af de ønskede kolonner:
… og slet derefter den kolonne, der ikke længere er nødvendig Detaljer ved at højreklikke på dens titel – Fjern kolonner (Fjern kolonner).
Efter upload af resultaterne til arket igennem Hjem — Luk og indlæs — Luk og indlæs (Hjem — Luk og indlæs — Luk og indlæs til...) vi får sådan et flot bord med en liste over seneste transaktioner, som vi ønskede:
Når du ændrer kildedataene, må du ikke glemme at opdatere resultaterne ved at højreklikke på dem – kommandoen Opdater & Gem (Opdater) eller tastaturgenvej Ctrl+andre+F5.
- LOOKUP-funktionen er en efterkommer af VLOOKUP
- Sådan bruges de nye dynamiske array-funktioner SORT, FILTER og UNIC
- Find den sidste ikke-blanke celle i en række eller kolonne med OPSLAG-funktionen