Formuła ogólna
=FILTER(list1,COUNTIF(list2,list1))
Podsumowanie
Aby przefiltrować dane w celu wyodrębnienia pasujących wartości z dwóch list, można użyć funkcji FILTR oraz funkcji LICZ.JEŻELI lub LICZ.WARUNKI. W pokazanym przykładzie formuła w F5 to:
=FILTER(list1,COUNTIF(list2,list1))
gdzie lista1 (B5: B16) i lista2 (D5: D14) to nazwane zakresy. Wynik zwrócony przez FILTER zawiera tylko wartości z listy1, które pojawiają się na liście2 .
Uwaga: FILTER to nowa funkcja tablicy dynamicznej w programie Excel 365.
Wyjaśnienie
Ta formuła opiera się na funkcji FILTER do pobierania danych na podstawie testu logicznego utworzonego za pomocą funkcji LICZ.JEŻELI:
=FILTER(list1,COUNTIF(list2,list1))
działając od wewnątrz, funkcja LICZ.JEŻELI służy do stworzenia właściwego filtra:
COUNTIF(list2,list1)
Zauważ, że używamy listy2 jako argumentu zakresu i listy1 jako argumentu kryterium. Innymi słowy, prosimy LICZ.JEŻELI, aby policzyć wszystkie wartości z listy1, które pojawiają się na liście2. Ponieważ podajemy COUNTIF wiele wartości dla kryteriów, otrzymujemy tablicę z wieloma wynikami:
(1;1;0;1;0;1;0;0;1;0;1;1)
Zauważ, że tablica zawiera 12 zliczeń, po jednej dla każdej wartości z listy1 . Wartość zero wskazuje na wartość z listy1, której nie ma na liście2 . Każda inna liczba dodatnia wskazuje wartość z listy1, która znajduje się na liście2 . Ta tablica jest zwracana bezpośrednio do funkcji FILTER jako argument include:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Funkcja filtru używa tablicy jako filtru. Każda wartość z listy1 powiązana z zerem jest usuwana, podczas gdy każda wartość skojarzona z liczbą dodatnią pozostaje w mocy.
Wynikiem jest tablica 7 pasujących wartości, które mieszczą się w zakresie F5: F11. Jeśli dane ulegną zmianie, FILTER ponownie obliczy i zwróci nową listę pasujących wartości w oparciu o nowe dane.
Niezgodne wartości
Aby wyodrębnić niepasujące wartości z listy1 (tj. Wartości z listy1 , które nie pojawiają się na liście2 ), możesz dodać funkcję NIE do formuły w następujący sposób:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
Funkcja NOT skutecznie odwraca wynik z LICZ.JEŻELI - każda liczba niezerowa staje się FAŁSZEM, a każda wartość zerowa staje się PRAWDA. Rezultatem jest lista wartości w listy1 , które nie są obecne w listy2 .
Dzięki INDEX
Możliwe jest utworzenie formuły w celu wyodrębnienia pasujących wartości bez funkcji FILTER, ale formuła jest bardziej złożona. Jedną z opcji jest użycie funkcji INDEKS w formule takiej jak ta:
Wzór w G5 skopiowany w dół to:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Uwaga: jest to formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter, z wyjątkiem Excel 365.
Rdzeniem tej formuły jest funkcja INDEKS, która jako argument tablicy przyjmuje list1 . Większość pozostałych formuł po prostu oblicza numer wiersza, który ma być używany do dopasowywania wartości. To wyrażenie generuje listę względnych numerów wierszy:
ROW(list1)-ROW(INDEX(list1,1,1))+1
która zwraca tablicę 12 liczb reprezentujących wiersze z listy1 :
(1;2;3;4;5;6;7;8;9;10;11;12)
Są one filtrowane za pomocą funkcji JEŻELI i tej samej logiki użytej powyżej w FILTR, w oparciu o funkcję LICZ.JEŻELI:
COUNTIF(list2,list1) // find matching values
Wynikowa tablica wygląda następująco:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Ta tablica jest dostarczana bezpośrednio do funkcji SMALL, która jest używana do pobrania następnego pasującego numeru wiersza, gdy formuła jest kopiowana w dół kolumny. Wartość k dla SMALL (myślę, że n-ta) jest obliczana z rozszerzającym się zakresem:
ROWS($G$5:G5) // incrementing value for k
Funkcja JEŻELI BŁĄD służy do wychwytywania błędów występujących, gdy formuła jest kopiowana i zabraknie pasujących wartości. Aby zapoznać się z innym przykładem tego pomysłu, zobacz tę formułę.
