W tym samouczku dowiesz się, jak znaleźć ostatnie wystąpienie elementu na liście za pomocą formuł programu Excel.
Ostatnio pracowałam nad ustaleniem agendy spotkania.
Miałem listę w Excelu, na której miałem listę osób i dat, w których pełnili funkcję „Przewodniczącego spotkania”.
Ponieważ na liście było powtórzenie (co oznacza, że dana osoba była wielokrotnie Przewodniczącym Spotkań), musiałem również wiedzieć, kiedy ostatni raz osoba pełniła funkcję „Przewodniczącego Spotkania”.
Stało się tak, ponieważ musiałem zapewnić, że ktoś, kto niedawno przewodniczył, nie zostanie ponownie przydzielony.
Postanowiłem więc użyć magii funkcji Excela, aby to zrobić.
Poniżej znajduje się końcowy wynik, w którym mogę wybrać nazwę z listy rozwijanej i podaje mi datę ostatniego wystąpienia tej nazwy na liście.
Jeśli dobrze rozumiesz funkcje programu Excel, wiesz, że nie ma jednej funkcji programu Excel, która może to zrobić.
Ale jesteś w sekcji Formula Hack i tutaj sprawiamy, że dzieje się magia.
W tym samouczku pokażę ci trzy sposoby na zrobienie tego.
Znajdź ostatnie wystąpienie - przy użyciu funkcji MAX
Podziękowania dla tej techniki należy do artykułu autorstwa Excel MVP Charley Kyd.
Oto formuła programu Excel, która zwróci ostatnią wartość z listy:
=INDEKS($B$2:$B$14,PRODUKTSUMA(MAX(WIERSZ($A$2:$A$14)*($D$3=$A$2:$A$14))-1))
Oto jak działa ta formuła:
- Funkcja MAX służy do znalezienia numeru wiersza ostatniej pasującej nazwy. Na przykład, jeśli nazwa to Glen, zwróci 11, ponieważ znajduje się w 11 wierszu. Ponieważ nasza lista zaczyna się od drugiego rzędu, 1 zostało odjęte. Tak więc pozycja ostatniego wystąpienia Glen to 10 na naszej liście.
- SUMPRODUCT służy do zapewnienia, że nie musisz używać Control + Shift + Enter, ponieważ SUMPRODUCT może obsługiwać formuły tablicowe.
- Funkcja INDEX jest teraz używana do znalezienia daty ostatniego pasującego nazwiska.
Znajdź ostatnie wystąpienie - za pomocą funkcji WYSZUKAJ
Oto kolejna formuła wykonania tej samej pracy:
= WYSZUKAJ(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)
Oto jak działa ta formuła:
- Wartość wyszukiwania to 2 (zobaczysz, dlaczego… czytaj dalej)
- Zakres wyszukiwania to 1 / ($ A $ 2: $ A $ 14 = $ D $ 3) - Zwraca 1, gdy znajdzie pasującą nazwę i błąd, gdy nie. Tak więc otrzymujesz tablicę. Na przykład dla wartości wyszukiwania Glen, tablica będzie miała postać {#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DZIEL/0!;#DZIEL/0!;1;#DZIEL/0!;#DZIEL/0!;#DZIEL/0!}.
- Trzeci argument ([wektor_wyniku]) to zakres, z którego daje wynik, czyli w tym przypadku daty.
Powodem, dla którego ta formuła działa, jest to, że funkcja WYSZUKAJ używa przybliżonej techniki dopasowania. Oznacza to, że jeśli znajdzie dokładnie pasującą wartość, zwróci ją, ale jeśli nie może, przeskanuje całą tablicę do końca i zwróci następną największą wartość, która jest niższa niż wartość wyszukiwania.
W tym przypadku wartością wyszukiwania jest 2, aw naszej tablicy otrzymamy tylko jedynki lub błędy. Skanuje więc całą tablicę i zwraca pozycję ostatniej 1 - która jest ostatnią pasującą wartością nazwy.
Znajdź ostatnie wystąpienie - przy użyciu funkcji niestandardowej (VBA)
Pozwól, że pokażę ci również inny sposób na zrobienie tego.
Możemy stworzyć niestandardową funkcję (zwaną również funkcją zdefiniowaną przez użytkownika) za pomocą VBA.
Zaletą tworzenia funkcji niestandardowej jest to, że jest łatwa w użyciu. Nie musisz za każdym razem martwić się tworzeniem złożonej formuły, ponieważ większość pracy odbywa się w backendzie VBA.
Stworzyłem prostą formułę (która bardzo przypomina formułę WYSZUKAJ.PIONOWO).
Aby utworzyć funkcję niestandardową, musisz mieć kod VBA w edytorze VB. Za chwilę podam kod i kroki, aby umieścić go w edytorze VB, ale najpierw pokażę, jak to działa:
Oto formuła, która da Ci wynik:
=LastItemLookup($ D$3,$A$2:$B$14,2)
Formuła przyjmuje trzy argumenty:
- Wartość wyszukiwania (jest to nazwa w komórce D3)
- Zakres wyszukiwania (będzie to zakres zawierający nazwy i daty - A2:B14)
- Numer kolumny (jest to kolumna, z której chcemy uzyskać wynik)
Po utworzeniu formuły i umieszczeniu kodu w edytorze VB możesz jej używać tak samo, jak innych zwykłych funkcji arkusza kalkulacyjnego Excel.
Oto kod formuły:
'To jest kod funkcji, która znajduje ostatnie wystąpienie wartości odnośnika i zwraca odpowiednią wartość z określonej kolumny 'Kod utworzony przez Sumit Bansal (https://trumpexcel.com) Funkcja LastItemLookup(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long For i = LookupRange.Columns(1).Cells.Count To 1 Krok -1 If Lookupvalue = LookupRange.Cells(i, 1) Then LastItemLookup = LookupRange.Cells(i, ColumnNumber) Zakończ funkcję Koniec, jeśli dalej i Zakończ funkcję
Oto kroki, aby umieścić ten kod w edytorze VB:
- Przejdź do zakładki Deweloper.
- Kliknij opcję Visual Basic. Spowoduje to otwarcie edytora VB w zapleczu.
- W okienku Eksplorator projektu w edytorze VB kliknij prawym przyciskiem myszy dowolny obiekt skoroszytu, do którego chcesz wstawić kod. Jeśli nie widzisz Eksploratora projektów, przejdź do karty Widok i kliknij Eksplorator projektów.
- Przejdź do Wstaw i kliknij Moduł. Spowoduje to wstawienie obiektu modułu do skoroszytu.
- Skopiuj i wklej kod w oknie modułu.
Teraz formuła byłaby dostępna we wszystkich arkuszach skoroszytu.
Zauważ, że musisz zapisać skoroszyt w formacie .XLSM, ponieważ zawiera makro. Ponadto, jeśli chcesz, aby ta formuła była dostępna we wszystkich używanych skoroszytach, możesz zapisać ją w skoroszycie makr osobistych lub utworzyć na jej podstawie dodatek.
