Załóżmy, że masz trudne zadanie wyciągnięcia wszystkich elementów listy A, które zawierają elementy z listy B.
Czyli w prostym przykładzie na liście B, mamy elementy „ewa” i „stefan”. Dlatego z listy A musimy wyciągnąć elementy „ewa” oraz „ewa mokra” bo zawierają element z listy B („ewa”). Musimy wyciągnąć również elementy „stefan” i „nowak stefan”, bo zawierają element z listy B („stefan”). Pozostałych elementów listy A nie wyciągamy, bo nie mają wspólnych części z listą B.
Ręcznie proces jest zrozumiały, ale czasochłonny przy dłuższych listach, jak więc to zrobić w Excelu, żeby wyciągnąć interesujące nas elementy za pomocą formuł w Excelu? (w filmie widzowie 118 – możesz zobaczyć jak zrobić to za pomocą filtrów zaawansowanych, co jest prostszą czynnością, ale nie może być dynamiczne jak formuły).
Będziemy potrzebowali kolumny pomocniczej. Zaczniemy od funkcji ZNAJDŹ, która pozwoli nam zidentyfikować, czy któryś z elementów listy B znajduje się w danym elemencie listy A.
=ZNAJDŹ($D$2:$D$3;A2)
Funkcja ZNAJDŹ zwraca numer znaku od którego szukany tekst (element listy B) znajduje się w konkretnym elemencie listy A (odwołanie do komórki A2). Jeśli go nie znajdzie zwraca błąd argumentu.
Ponieważ szukamy elementów z listy (tablicy) funkcja ZNAJDŹ zwraca nam również listę (tablicę), jeśli podejrzymy jej wynik klawiszem F9 w trybie edycji komórki to zobaczymy 2 wyniki — {1\#ARG!} (tyle mamy elementów listy B). Oznacza to, że w pierwszym elemencie listy A został odnaleziony pierwszy element listy B (na 1 znaku), a drugi element listy B już nie został odnaleziony.
Nam zależy na tym, żeby wiedzieć, czy chociaż jeden element został odnaleziony, czyli musimy policzyć wszystkie liczby. Możemy to zrobić korzystając z funkcji CZY.LICZBA , która zwróci nam wartość PRAWDA czy podana jej wartość jest liczbą oraz wartość FAŁSZ, jeśli podana jej wartość nie jest liczba (np.: jest błędem).
=CZY.LICZBA(ZNAJDŹ($D$2:$D$3;A2))
Czyli dla pierwszego elementu będziemy mieli wynik {PRAWDA\FAŁSZ}. Wartości PRAWDA i FAŁSZ może być ciężko zliczyć poza tym chcemy uniknąć zatwierdzania naszej formuły jako formuły tablicowej, więc zamieniamy wartości logiczne na 0 i 1 za pomocą podwójnej negacji (dwóch znaków minus), a następnie wstawiamy jest do funkcji SUMA.ILOCZYNÓW, żeby zsumować całą listę naszych wyników.
=SUMA.ILOCZYNÓW(–CZY.LICZBA(ZNAJDŹ($D$2:$D$3;A2)))
Teraz mamy 0 gdy, żaden element z listy B nie został odnaleziony w elemencie listy A, a wartości większe od 0 (u nas tylko 1), gdy chociaż jeden element z listy B został znaleziony w konkretnym elemencie listy A. Ale jeszcze nie skończyliśmy obliczeń, bo zwracane wartości (rys. 4) nie pomogą nam łatwo odnaleźć konkretnych elementów listy A, dlatego chcemy je zamienić na numer wiersza w liście A. Możemy to zrobić za pomocą funkcji JEŻELI i funkcji WIERSZ. Funkcja JEŻELI posłuży nam do tego, żeby dodatkowo rozróżnić interesujące nas elementy i te niechciane, bo jeśli wynik wcześniejszych obliczeń będzie większy od zera to zwróci numer wiersza (WIERSZ() – WIERSZ($A$1)), a jeśli będzie równy zero to zwróci FAŁSZ (trzeci argument funkcji JEŻELI pominięty.
=JEŻELI(SUMA.ILOCZYNÓW(–CZY.LICZBA(ZNAJDŹ($D$2:$D$3;A2)));WIERSZ()-WIERSZ($A$1))
Jeśli funkcji WIERSZ nie podamy żadnej wartości, to zwróci numer wiersza arkusza, w którym aktualnie się znajduje, a ponieważ nam zależy na numerze wiersza listy A, a nie arkusza, to musimy jeszcze odjąć numer wiersza nagłówka (WIERSZ($A$1)).
Jak mamy już ponumerowane interesujące nas elementy, to wyciągnięcie ich jest relatywnie proste. Przede wszystkim będziemy potrzebować funkcji INDEKS, żeby wiedzieć skąd wyciągamy elementy oraz dodatkowo funkcji MIN.K lub MAX.K, żeby wyciągać poszczególne elementy po kolei. Jedynym odrobinę trudniejszym elementem jest wyznaczenie kolejnych ‘k’. Robi się to najczęściej za pomocą funkcji ILE.WIERSZY z dynamicznie rozrastającym się zakresem – pierwsza część zakresu jest zablokowana bezwzględnie, a druga względnie ($F$2:F2). Cała formuła przyjmie postać:
=INDEKS($A$2:$A$7;MIN.K($B$2:$B$7;ILE.WIERSZY($F$2:F2)))
Pojawiać się tutaj będą błędy (#LICZBA!), gdy przeciągniemy formułę niżej niż mamy interesujących nas elementów. Najprościej poradzić sobie z tym funkcją JEŻELI.BŁĄD, ale jeśli nie chcemy, żeby Excel za każdym razem obliczał formułę wewnątrz funkcji JEŻELI.BŁĄD, to możemy policzyć wszystkie wartości większe od zera w kolumnie pomocniczej (=LICZ.JEŻELI($B$2:$B$7;">0")), a następnie sprawdzać, czy nie zeszliśmy już dalej niż jest interesujących nas elementów:
=JEŻELI(ILE.WIERSZY($F$2:F2)>$H$2;"";INDEKS($A$2:$A$7;MIN.K($B$2:$B$7;ILE.WIERSZY($F$2:F2))))
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
