Obejrzyj wideo - wyszukaj drugą, trzecią lub n-tą pasującą wartość
Jeśli chodzi o wyszukiwanie danych w Excelu, często używam dwóch niesamowitych funkcji - WYSZUKAJ.PIONOWO i INDEKSU (głównie w połączeniu z funkcją PODAJ.POZYCJĘ).
Jednak te formuły są przeznaczone do znajdowania tylko pierwszego wystąpienia wartości wyszukiwania.
Ale co, jeśli chcesz sprawdzić drugą, trzecią, czwartą lub N-tą wartość.
Cóż, jest to wykonalne przy odrobinie dodatkowej pracy.
W tym samouczku pokażę Ci różne sposoby (z przykładami) wyszukiwania drugiej lub N-tej wartości w programie Excel.
Wyszukaj drugą, trzecią lub n-tą wartość w programie Excel
W tym samouczku omówię dwa sposoby wyszukiwania drugiej lub N-tej wartości w programie Excel:
- Korzystanie z kolumny pomocniczej.
- Korzystanie z formuł tablicowych.
Zacznijmy i zanurzmy się od razu.
Korzystanie z kolumny pomocy
Załóżmy, że jesteś koordynatorem szkoleń w organizacji i masz zestaw danych, jak pokazano poniżej. Chcesz wymienić wszystkie szkolenia przed nazwiskiem pracownika.
W powyższym zestawie danych pracownicy zostali przeszkoleni w zakresie różnych narzędzi Microsoft Office (Excel, PowerPoint i Word).
Teraz możesz użyć funkcji WYSZUKAJ.PIONOWO lub kombinacji INDEKS/POZYCJA, aby znaleźć szkolenie, które ukończył pracownik. Jednak zwróci tylko pierwszą pasującą instancję.
Na przykład w przypadku Jana odbył wszystkie trzy szkolenia, ale kiedy wyszukam jego imię za pomocą funkcji WYSZUKAJ.PIONOWO lub INDEX/MATCH, zawsze zwróci „Excel”, co jest pierwszym szkoleniem dla jego nazwiska na liście .
Aby to zrobić, możemy użyć kolumny pomocniczej i utworzyć w niej unikalne wartości wyszukiwania.
Oto kroki:
- Wstaw kolumnę przed kolumną zawierającą szkolenie.
- W komórce B2 wprowadź następującą formułę:
=A2&LICZ.JEŻELI($A$2:$A2,A2)
- W komórce F2 wprowadź następującą formułę i skopiuj i wklej dla wszystkich pozostałych komórek:
=JEŻELI NA (WYSZUKAJ.PIONOWO($E2&KOLUMNY($F$1:F1);$B$2:$C$14,2,0);"")
Powyższa formuła zwróci szkolenie dla każdego pracownika w kolejności, w jakiej pojawia się na liście. W przypadku braku szkolenia dla pracownika zwraca puste miejsce.
Jak działa ta formuła?
Formuła LICZ.JEŻELI w kolumnie pomocniczej sprawia, że nazwa każdego pracownika jest unikatowa, dodając do niej numer. Na przykład pierwsze wystąpienie Jana staje się Jan1, drugie wystąpienie Jana2 i tak dalej.
Formuła WYSZUKAJ.PIONOWO używa teraz tych unikalnych nazwisk pracowników, aby znaleźć pasujące szkolenie.
Zauważ, że $E2&COLUMNS($F$1:F1) to wartość wyszukiwania w formule. Spowoduje to dodanie numeru do nazwiska pracownika na podstawie numeru kolumny. Na przykład, gdy ta formuła jest używana w komórce F2, wartość wyszukiwania staje się „Jan1”. W komórce G2 staje się „John2” i tak dalej.
Korzystanie z formuły tablicowej
Jeśli nie chcesz zmieniać oryginalnego zestawu danych przez dodanie kolumn pomocniczych, możesz również użyć formuły tablicowej, aby wyszukać drugą, trzecią lub n-tą wartość.
Załóżmy, że masz taki sam zestaw danych, jak pokazano poniżej:
Oto formuła, która zwróci poprawną wartość wyszukiwania:
=JEŻELIBŁĄD(INDEKS($B$2:$B$14;MAŁY(JEŻELI($A$2:$A$14=$D2;WIERSZ($A$2:$A$14)-1;"");KOLUMNY($E$1 :E1)))),"")
Skopiuj tę formułę i wklej ją w komórce E2.
Zauważ, że jest to formuła tablicowa i musisz użyć Control + Shift + Enter (przytrzymaj klawisze Control i Shift i naciśnij klawisz Enter), zamiast naciskać tylko klawisz Enter.
Kliknij tutaj, aby pobrać przykładowy plik.
Jak działa ta formuła?
Podzielmy tę formułę na części i zobaczmy, jak to działa.
$ A $ 2: $ A $ 14 = $ D2
Powyższa część formuły porównuje każdą komórkę w A2:A14 z wartością w D2. W tym zestawie danych sprawdza, czy komórka zawiera imię „Jan”, czy nie.
Zwraca tablicę TRUE lub FALSE. Jeśli komórka nosi imię „Jan”, będzie to prawda, w przeciwnym razie będzie to fałsz.
Poniżej znajduje się tablica, którą otrzymasz w tym przykładzie:
{PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ}
Zwróć uwagę, że ma TRUE na 1, 7 i 111 pozycji, ponieważ tam w zbiorze danych pojawia się imię John.
JEŻELI($A$2:$A$14=$D2,WIERSZ($A$2:$A$14)-1””)
Powyższa formuła JEŻELI używa tablicy PRAWDA i FAŁSZ i zastępuje PRAWDA pozycją jej wystąpienia na liście (podaną przez WIERSZ($A$2:$A$14)-1) i FAŁSZ na „” (puste). Poniżej znajduje się tablica wynikowa, którą otrzymujesz za pomocą tej formuły JEŻELI:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Uwaga niż 1, 7 i 11 to pozycje występowania Jana na liście.
MAŁY(JEŻELI($A$2:$A$14=$D2,WIERSZ($A$2:$A$14)-1"");KOLUMNY($E$1:E1))
Funkcja SMALL wybiera teraz pierwszą najmniejszą, drugą najmniejszą, trzecią najmniejszą liczbę z tej tablicy. Zauważ, że używa funkcji COLUMNS do wygenerowania numeru kolumny. W komórce E2 funkcja KOLUMNY zwraca 1, a funkcja MAŁE zwraca 1. W komórce F2 funkcja KOLUMNY zwraca 2, a funkcja MAŁE zwraca 7.
INDEKS($B$2:$B$14,MAŁY(JEŻELI($A$2:$A$14=$D2,WIERSZ($A$2:$A$14)-1""),KOLUMNY($E$1:E1) ))
Funkcja INDEX zwraca teraz wartość z listy w kolumnie B na podstawie pozycji zwróconej przez funkcję MAŁA. Dlatego w komórce E2 zwraca „Excel”, który jest pierwszą pozycją w B2:B14. W komórce F2 zwraca PowerPoint, który jest siódmą pozycją na liście.
Ponieważ zdarzają się przypadki, w których dla niektórych pracowników jest tylko jedno lub dwa szkolenia, funkcja INDEX zwróci błąd. Funkcja JEŻELI.BŁĄD służy do zwracania spacji w miejscu błędu.
Zauważ, że w tych przykładach użyłem referencji do zakresu. Jednak w praktycznych przykładach korzystne jest przekształcenie danych w tabelę Excela. Konwertując na tabelę programu Excel, możesz używać odwołań strukturalnych, co ułatwia tworzenie formuł. Ponadto tabela programu Excel może automatycznie uwzględniać wszystkie nowe elementy szkoleniowe dodawane do listy (dzięki czemu nie trzeba za każdym razem dostosowywać formuł).
Co robisz, gdy musisz sprawdzić drugą, trzecią lub N-tą wartość? Jestem pewien, że jest na to więcej sposobów. Jeśli używasz czegoś łatwiejszego niż wymienione tutaj, podziel się z nami wszystkimi w sekcji komentarzy.
Kliknij tutaj, aby pobrać przykładowy plik.
