Spis treści
Wszystkie klasyczne funkcje wyszukiwania i zastępowania typów VPR (WYSZUKAJ.PIONOWO), GPR (WYSZUKAJ.POZIOMO), WIĘCEJ EKSPOZYCYJNE (MECZ) a ci jemu podobni mają jedną ważną cechę – przeszukują od początku do końca, czyli od lewej do prawej lub od góry do dołu w danych źródłowych. Po znalezieniu pierwszego pasującego dopasowania wyszukiwanie zatrzymuje się i zostaje znalezione tylko pierwsze wystąpienie potrzebnego elementu.
Co zrobić, jeśli musimy znaleźć nie pierwsze, ale ostatnie zdarzenie? Na przykład ostatnia transakcja dla klienta, ostatnia płatność, ostatnie zamówienie itp.?
Metoda 1: Znajdowanie ostatniego wiersza za pomocą formuły tablicowej
Jeśli oryginalna tabela nie posiada kolumny z datą lub kolejnym numerem wiersza (zamówienie, płatność…), to naszym zadaniem jest tak naprawdę znalezienie ostatniego wiersza, który spełnia dany warunek. Można to zrobić za pomocą następującej formuły tablicowej:
Tutaj:
- Funkcjonować IF (JEŚLI) sprawdza wszystkie komórki w kolumnie jedna po drugiej Klient i wyświetla numer wiersza, jeśli zawiera nazwę, której potrzebujemy. Numer wiersza na arkuszu jest nam podawany przez funkcję LINE (WIERSZ), ale ponieważ potrzebujemy numeru wiersza w tabeli, dodatkowo musimy odjąć 1, ponieważ mamy nagłówek w tabeli.
- Następnie funkcja MAX (MAKS.) wybiera maksymalną wartość z utworzonego zestawu numerów wierszy, czyli numer ostatniej linii klienta.
- Funkcjonować INDEKS (INDEKS) zwraca zawartość komórki z ostatnią znalezioną liczbą z dowolnej innej wymaganej kolumny tabeli (Kod zamówienia).
Wszystko to musi być wpisane jako formuła tablicowa, tj:
- W usłudze Office 365 z zainstalowanymi najnowszymi aktualizacjami i obsługą tablic dynamicznych wystarczy nacisnąć Wchodzę.
- We wszystkich pozostałych wersjach po wpisaniu formuły będziesz musiał nacisnąć skrót klawiaturowy Ctrl+Shift+Wchodzę, który automatycznie doda do niego nawiasy klamrowe na pasku formuły.
Metoda 2: Wyszukiwanie wsteczne z nową funkcją WYSZUKAJ
Napisałem już długi artykuł z filmem o nowej funkcji ZOBACZ (XWYSZUKAJ), który pojawił się w najnowszych wersjach pakietu Office, aby zastąpić starą funkcję WYSZUKAJ.PIONOWO (WYSZUKAJ.PIONOWO). Z pomocą BROWSE nasze zadanie rozwiązujemy dość elementarnie, bo. dla tej funkcji (w przeciwieństwie do funkcji WYSZUKAJ.PIONOWO) można jawnie ustawić kierunek wyszukiwania: top-down lub bottom-up – odpowiada za to jej ostatni argument (-1):
Metoda 3. Wyszukaj ciąg z najnowszą datą
Jeśli w danych źródłowych mamy kolumnę z numerem seryjnym lub datą pełniącą podobną rolę, to zadanie jest modyfikowane – musimy znaleźć nie ostatnią (najniższą) linię z dopasowaniem, ale linię z najnowszym ( maksymalna) data.
Omówiłem już szczegółowo, jak to zrobić za pomocą klasycznych funkcji, a teraz spróbujmy wykorzystać moc nowych dynamicznych funkcji tablicowych. Dla większego piękna i wygody zamieniamy również oryginalny stół w „inteligentny” stół za pomocą skrótu klawiaturowego Ctrl+T lub komendy Strona główna – Formatuj jako tabelę (Strona główna — Formatuj jako tabelę).
Z ich pomocą ta „zabójcza para” bardzo wdzięcznie rozwiązuje nasz problem:
Tutaj:
- Funkcja pierwsza FILTER (FILTR) wybiera tylko te wiersze z naszej tabeli, które znajdują się w kolumnie Klient – imię, którego potrzebujemy.
- Następnie funkcja GRADE (SORTOWAĆ) sortuje wybrane wiersze według daty w porządku malejącym, z najnowszą transakcją u góry.
- Funkcjonować INDEKS (INDEKS) wyodrębnia pierwszy wiersz, tj. zwraca ostatnią potrzebną transakcję.
- I na koniec funkcja FILTER zewnętrzna usuwa z wyników dodatkowe kolumny 1. i 3. (Kod zamówienia и Klient) i pozostawia tylko datę i kwotę. W tym celu używana jest tablica stałych. {0;1;0;1}, określając, które kolumny chcemy (1) lub nie (0) wyświetlać.
Metoda 4: Znajdowanie ostatniego dopasowania w Power Query
Cóż, ze względu na kompletność przyjrzyjmy się rozwiązaniu naszego problemu z wyszukiwaniem wstecznym przy użyciu dodatku Power Query. Z jej pomocą wszystko rozwiązuje się bardzo szybko i pięknie.
1. Przekształćmy nasz oryginalny stół w „inteligentny” za pomocą skrótu klawiaturowego Ctrl+T lub komendy Strona główna – Formatuj jako tabelę (Strona główna — Formatuj jako tabelę).
2. Załaduj go do Power Query za pomocą przycisku Z tabeli/zakresu Zakładka Dane (Dane — z tabeli/zakresu).
3. Sortujemy (poprzez listę rozwijaną filtra w nagłówku) naszą tabelę w porządku malejącym według dat, tak aby najnowsze transakcje znajdowały się na górze.
4… W zakładce Transformacja wybierz drużynę Grupuj według (Przekształć — Grupuj według) i ustaw grupowanie według klientów, a jako funkcję agregującą wybierz opcję Wszystkie linie (Wszystkie rzędy). Możesz nazwać nową kolumnę, jak chcesz – na przykład Szczegóły.
Po pogrupowaniu otrzymamy listę unikalnych nazw naszych klientów oraz w kolumnie Szczegóły – tabele ze wszystkimi transakcjami każdego z nich, gdzie w pierwszej linii będzie najnowsza transakcja, czyli to, czego potrzebujemy:
5. Dodaj nową kolumnę obliczeniową za pomocą przycisku Kolumna niestandardowa Zakładka Dodaj kolumnę (Dodaj kolumnę — Dodaj kolumnę niestandardową)i wprowadź następującą formułę:
Tutaj Szczegóły – to jest kolumna, z której pobieramy tabele przez klientów, oraz 0 {} to numer wiersza, który chcemy wyodrębnić (numeracja wierszy w dodatku Power Query zaczyna się od zera). Otrzymujemy kolumnę z rekordami (Rekord), gdzie każdy wpis jest pierwszym wierszem z każdej tabeli:
Pozostaje rozwinąć zawartość wszystkich rekordów przyciskiem z podwójnymi strzałkami w nagłówku kolumny Ostatnia oferta wybierając żądane kolumny:
… a następnie usuń kolumnę, która nie jest już potrzebna Szczegóły klikając prawym przyciskiem myszy na jego tytuł – Usuń kolumny (Usuń kolumny).
Po przesłaniu wyników do arkusza przez Strona główna — Zamknij i załaduj — Zamknij i załaduj w (Strona główna — Zamknij i załaduj — Zamknij i załaduj do…) dostaniemy tak fajną tabelę z listą ostatnich transakcji, jak chcieliśmy:
Gdy zmieniasz dane źródłowe, nie zapomnij zaktualizować wyników, klikając je prawym przyciskiem myszy – polecenie Zaktualizuj i zapisz (Odświeżać) lub skrót klawiaturowy Ctrl+inny+F5.
- Funkcja WYSZUKAJ jest potomkiem funkcji WYSZUKAJ.PIONOWO
- Jak korzystać z nowych dynamicznych funkcji tablicowych SORT, FILTER i UNIC
- Znajdowanie ostatniej niepustej komórki w wierszu lub kolumnie za pomocą funkcji WYSZUKAJ