Kontynuujemy temat znajdowania elementów listy A, które zawierają elementy listy B. Dzięki wysiłkowi użytkownika YT Bill Szysz dostałem formułę, która wyciąga elementy z listy A bez kolumny pomocniczej.
Budowanie rozwiązania zaczniemy od zbudowania formuły, która oblicza ilość elementów, które spełniają postawiony warunek.
Zaczynamy od tego, że korzystamy z funkcji ZNAJDŹ (zwraca uwagę na wielkość liter) ewentualnie SZUKAJ.TEKST (nie zwraca uwagi na wielkość liter), żeby znaleźć elementy listy B w elementach listy A. W formule skorzystam z funkcji ZNAJDŹ, bo ma krótszy zapis, a wielkość liter jest identyczna w przykładowych listach.
Pojawia się jednak problem przy korzystaniu z funkcji ZNAJŹ ponieważ jeśli podamy pierwszy i drugi argument jako listy (kolumny), to wynik zwracany przez funkcję ZNAJDŹ nam nie pomoże, ponieważ Excel będzie porównywał nam poszczególne elementy na listach, a nie każdy element listy B z każdym elementem listy A. Żeby porównać listy w ten sposób jedna musi mieć orientację poziomą (być wierszem), a druga mieć orientację pionową (być kolumną). Zapisywanie list w odmiennej orientacji (pion-poziom) jest uciążliwe, dlatego pozostawiamy je jako listy w kolumnach, ale zmieniamy orientację listy B w formule za pomocą funkcji TRANSPONUJ.
=ZNAJDŹ(TRANSPONUJ($C$2:$C$3);$A$2:$A$7)
Dzięki temu wynikiem funkcji ZNAJDŹ jest macierz (tablica) mająca tyle kolumn ile jest elementów na liście B i tyle wierszy ile jest elementów na liście A. Na skrzyżowaniu uzyskujemy informację, czy dany element listy B został odnaleziony w danym elemencie listy A. Wynik zwracany przez aktualną formułę jest mało czytelny ({1\#ARG!;1\#ARG!;#ARG!\#ARG!;#ARG!\1;#ARG!\7;#ARG!\#ARG!}) dlatego dodatkowo przedstawiam go w postaci zakresu w Excelu, któray ułatwi zrozumienie tego wyniku (w powyższym zapisie backslash (\) oznacza nową kolumnę, a średnik (;) nowy wiersz).
Formuła, którą zbudujemy do obliczenia ilości elementów nie będzie najkrótszą formułą, ale będzie częścią formuły, którą musimy stworzyć, żeby wyciągać kolejne elementy listy A spełniające założenia.
Dalszym krokiem będzie zsumowanie wartości w poszczególnych wierszach pomijając błędy (przyjmując, że mają wartość zero). Czyli wynik będzie przedstawiał się jako jedna kolumna, którą widać obok wcześniej pokazywanych danych:
Żeby osiągnąć taki wynik w pierwszej kolejności musimy zamienić wartości błędów na zera – najprościej można to zrobić za pomocą funkcji JEŻELI.BŁĄD.
=JEŻELI.BŁĄD(ZNAJDŹ(TRANSPONUJ($C$2:$C$3);$A$2:$A$7);0)
Dopiero wtedy możemy zsumować wartości w poszczególnych wierszach. Chyba najprościej można to zrobić przez pomnożenie przez macierz (tablicę) zawierającą w kolumnie tyle jedynek ile jest elementów na liście B. Taką macierz można stworzyć np: podnosząc numery wierszy elementów listy B do zerowej potęgi:
WIERSZ($C$2:$C$3)^0
Teraz musimy przemnożyć przez siebie stworzone macierz (więcej o mnożeniu macierzy w Porada #102 — Mnożenie macierzy przez siebie MACIERZ.ILOCZYN, ich zapis i formuły tablicowe)
=MACIERZ.ILOCZYN(JEŻELI.BŁĄD(ZNAJDŹ(TRANSPONUJ($C$2:$C$3);$A$2:$A$7);0);WIERSZ($C$2:$C$3)^0)
Teraz musimy zsumować wartości większe od zera. To już proste zadanie bo, wystarczy prosta operacja porównania. Tylko musimy pamiętać, że wynikowy wartości PRAWDA i FAŁSZ nie są sumowane przez funkcję SUMA, dlatego musimy je zamienić na zera i jedynki np.: dodając zero:
=SUMA((MACIERZ.ILOCZYN(JEŻELI.BŁĄD(ZNAJDŹ(TRANSPONUJ($C$2:$C$3);$A$2:$A$7);0);WIERSZ($C$2:$C$3)^0)>0)+0)
Musimy pamiętać, żeby tą formułę zatwierdzać kombinacją klawisz Ctrl + Shift + Enter, ponieważ jest to formuła tablicowa.
W ten sposób dowiedzieliśmy się ile jest elementów, które spełniają nasze założenia. Teraz potrzebujemy te elementy wyciągnąć. Potrzebujemy do tego części formuły zwracającej wynik mnożenia macierzy.
=MACIERZ.ILOCZYN(JEŻELI.BŁĄD(ZNAJDŹ(TRANSPONUJ($C$2:$C$3);$A$2:$A$7);0);WIERSZ($C$2:$C$3)^0)
Teraz nie chcemy sumować wartości większych od zera tylko zwrócić dla nich numer wiersza odpowiadającego im elementom listy A. Wystarczy, że wynik funkcji MACIERZ.ILOCZYN włożymy do funkcji JEŻELI i odpowiednio policzymy numery wierszy za pomocą funkcji WIERSZ. Dla wartości chcemy, żeby funkcja JEŻELI zwracała wartości logiczne FAŁSZ, więc nie wypełniamy trzeciego argumentu tej funkcji.
=JEŻELI(MACIERZ.ILOCZYN(JEŻELI.BŁĄD(ZNAJDŹ(TRANSPONUJ($C$2:$C$3);$A$2:$A$7);0);WIERSZ($C$2:$C$3)^0);WIERSZ($A$2:$A$7)-WIERSZ($A$1))
Teraz, ponieważ mamy ponumerowane po kolei interesujące nas elementy listy A, wystarczy je wyciągać za pomocą funkcji INDEKS i funkcji MIN.K, której będziemy zwiększać argument k licząc kolejne wiersze funkcją ILE.WIERSZY:
=JEŻELI(MACIERZ.ILOCZYN(JEŻELI.BŁĄD(ZNAJDŹ(TRANSPONUJ($C$2:$C$3);$A$2:$A$7);0);WIERSZ($C$2:$C$3)^0);WIERSZ($A$2:$A$7)-WIERSZ($A$1))
Naszą formułę dopracujemy, żeby nie pokazywała błędów, jeśli dojdziemy do wiersza, który przekroczy ilość elementów, które spełniają za łożenia (to już obliczyliśmy). Wystarczy do tego funkcja JEŻELI i sprawdzanie ilości wiersza:
=JEŻELI(ILE.WIERSZY($E$2:E2)>$G$2;"";INDEKS($A$2:$A$7;MIN.K(JEŻELI(MACIERZ.ILOCZYN(JEŻELI.BŁĄD(ZNAJDŹ(TRANSPONUJ($C$2:$C$3);$A$2:$A$7);0);WIERSZ($C$2:$C$3)^0);WIERSZ($A$2:$A$7)-WIERSZ($A$1));ILE.WIERSZY($E$2:E2))))
Hura dobrnęliśmy do końca formuły.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
