Inhoud
Alle klassieke zoek- en typevervangingsfuncties VPR (VERT.ZOEKEN), GPR (HERZOEKEN), MEER BLOOTGESTELD (WEDSTRIJD) en die zoals zij hebben één belangrijk kenmerk: ze zoeken van het begin tot het einde, dat wil zeggen van links naar rechts of van boven naar beneden in de brongegevens. Zodra de eerste overeenkomende match is gevonden, stopt het zoeken en wordt alleen het eerste exemplaar van het element dat we nodig hebben gevonden.
Wat te doen als we niet de eerste, maar de laatste gebeurtenis moeten vinden? Bijvoorbeeld de laatste transactie voor de klant, de laatste betaling, de meest recente bestelling, etc.?
Methode 1: De laatste rij vinden met een matrixformule
Als de originele tabel geen kolom heeft met een datum of een volgnummer van een rij (bestelling, betaling …), dan is het onze taak om de laatste rij te vinden die aan de gegeven voorwaarde voldoet. Dit kan met de volgende matrixformule:
Hier:
- Functie IF (ALS) controleert alle cellen in een kolom één voor één en geeft het regelnummer weer als het de naam bevat die we nodig hebben. Het regelnummer op het blad wordt ons gegeven door de functie LINE (RIJ), maar omdat we het rijnummer in de tabel nodig hebben, moeten we bovendien 1 aftrekken omdat we een kop in de tabel hebben.
- Dan de functie MAX (MAX) selecteert de maximale waarde uit de gevormde reeks rijnummers, dwz het nummer van de meest recente regel van de client.
- Functie INDEX (INHOUDSOPGAVE) retourneert de inhoud van de cel met het gevonden laatste getal uit een andere vereiste tabelkolom (Bestelcode).
Dit alles moet worden ingevoerd als matrix formule, dat wil zeggen:
- In Office 365 met de nieuwste updates geïnstalleerd en ondersteuning voor dynamische arrays, drukt u eenvoudig op Enter.
- In alle andere versies moet u na het invoeren van de formule op de sneltoets . drukken Ctrl+Shift+Enter, die er automatisch accolades aan toevoegt in de formulebalk.
Methode 2: Omgekeerd opzoeken met de nieuwe LOOKUP-functie
Ik heb al een lang artikel geschreven met een video over een nieuwe feature BEKIJK (XZOEKEN), die in de nieuwste versies van Office verscheen ter vervanging van de oude VERT.ZOEKEN (VERT.ZOEKEN). Met behulp van BROWSE is onze taak vrij elementair opgelost, want. voor deze functie (in tegenstelling tot VERT.ZOEKEN), kun je expliciet de zoekrichting instellen: top-down of bottom-up – het laatste argument (-1) is hiervoor verantwoordelijk:
Methode 3. Zoek naar een string met de laatste datum
Als we in de brongegevens een kolom hebben met een serienummer of een datum die een vergelijkbare rol speelt, dan wordt de taak aangepast - we moeten niet de laatste (laagste) regel vinden met een overeenkomst, maar de regel met de laatste ( maximum) datum.
Ik heb al in detail besproken hoe je dit kunt doen met behulp van klassieke functies, en laten we nu proberen de kracht van de nieuwe dynamische array-functies te gebruiken. Voor meer schoonheid en gemak zetten we de originele tafel ook om in een "slimme" tafel met behulp van een sneltoets Ctrl+T of commando's Home – Opmaak als tabel (Home - Opmaken als tabel).
Met hun hulp lost dit "moordenaarspaar" ons probleem zeer gracieus op:
Hier:
- Functie eerst FILTER (FILTER) selecteert alleen die rijen uit onze tabel waar in de kolom – de naam die we nodig hebben.
- Dan de functie GRADE (SOORT) sorteert de geselecteerde rijen op datum in aflopende volgorde, met de meest recente deal bovenaan.
- Functie INDEX (INHOUDSOPGAVE) extraheert de eerste rij, dwz retourneert de laatste transactie die we nodig hebben.
- En ten slotte verwijdert de externe FILTER-functie de extra 1e en 3e kolom uit de resultaten (Bestelcode и ) en laat alleen de datum en het bedrag achter. Hiervoor wordt een array van constanten gebruikt. {0;1;0;1}, definiëren welke kolommen we wel (1) of niet (0) willen weergeven.
Methode 4: De laatste overeenkomst vinden in Power Query
Laten we voor de volledigheid eens kijken naar een oplossing voor ons probleem met omgekeerd zoeken met behulp van de Power Query-invoegtoepassing. Met haar hulp is alles heel snel en mooi opgelost.
1. Laten we onze originele tabel omzetten in een "slimme" tabel met behulp van een sneltoets Ctrl+T of commando's Home – Opmaak als tabel (Home - Opmaken als tabel).
2. Laad het in Power Query met de knop Van tafel/bereik tab Data (Gegevens — Uit tabel/bereik).
3. We sorteren (via de drop-down lijst van het filter in de header) onze tabel in aflopende volgorde van datum, zodat de meest recente transacties bovenaan staan.
4... Op het tabblad Transformatie kies een team Groeperen op (Transformeren - Groeperen op) en stel de groepering op klanten in, en selecteer als aggregatiefunctie de optie: Alle lijnen (Alle rijen). U kunt de nieuwe kolom elke gewenste naam geven, bijvoorbeeld Details.
Na het groeperen krijgen we een lijst met unieke namen van onze klanten en in de kolom Details - tabellen met alle transacties van elk van hen, waarbij de eerste regel de laatste transactie is, wat we nodig hebben:
5. Voeg een nieuwe berekende kolom toe met de knop Aangepaste kolom tab Kolom toevoegen (Kolom toevoegen — Aangepaste kolom toevoegen)en voer de volgende formule in:
Here Details – dit is de kolom waaruit we tabellen van klanten nemen, en 0 {} is het nummer van de rij die we willen extraheren (rijnummering in Power Query begint bij nul). We krijgen een kolom met records (Record), waarbij elk item de eerste rij van elke tabel is:
Het blijft om de inhoud van alle records uit te breiden met de knop met dubbele pijlen in de kolomkop Laatste deal de gewenste kolommen selecteren:
... en verwijder vervolgens de kolom die niet langer nodig is Details door met de rechtermuisknop op de titel te klikken - Kolommen verwijderen (Verwijder kolommen).
Na het uploaden van de resultaten naar het blad via Home — Sluiten en laden — Sluiten en laden (Home — Sluiten & laden — Sluiten & laden naar…) we krijgen zo'n mooie tabel met een lijst van recente transacties, zoals we wilden:
Wanneer u de brongegevens wijzigt, moet u niet vergeten de resultaten bij te werken door er met de rechtermuisknop op te klikken - het commando Bijwerken en opslaan (Vernieuwen) of sneltoets Ctrl+anders+F5.
- De functie ZOEKEN is een afstammeling van VERT.ZOEKEN
- Hoe u de nieuwe dynamische arrayfuncties SORT, FILTER en UNIC gebruikt?
- De laatste niet-lege cel in een rij of kolom vinden met de LOOKUP-functie