Formuła ogólna
=FILTER(list1,COUNTIF(list2,list1))
Podsumowanie
Aby porównać dwie listy i wyodrębnić wspólne wartości, możesz użyć formuły opartej na funkcjach FILTR i LICZ.JEŻELI. W pokazanym przykładzie formuła w F5 to:
=FILTER(list1,COUNTIF(list2,list1))
gdzie lista1 (B5: B15) i lista2 (D5: D13) to nazwane zakresy. Wynik, wartości, które pojawiają się na obu listach, wlewają się do zakresu F5: F11.
Wyjaśnienie
Funkcja FILTER akceptuje tablicę wartości i argument „include”, który filtruje tablicę na podstawie wyrażenia logicznego lub wartości.
W tym przypadku tablica jest udostępniana jako nazwany zakres „lista1”, który zawiera wszystkie wartości z B5: B15. To argument dostarczany przez funkcję COUNTIF, który jest zagnieżdżony filtr:
=FILTER(list1,COUNTIF(list2,list1))
LICZ.JEŻELI jest skonfigurowane z list2 jako zakresem i list1 jako kryterium . Ponieważ podajemy LICZ.JEŻELI jedenaście wartości kryteriów, LICZ.JEŻELI zwraca jedenaście wyników w tablicy takiej jak ta:
(1;1;0;1;0;1;0;1;0;1;1)
Zauważ, że 1 odpowiadają elementom z listy2, które pojawiają się na liście1.
Ta tablica jest dostarczana bezpośrednio do funkcji FILTER jako argument „include”:
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
Funkcja FILTER filtruje listę1 przy użyciu wartości podanych przez LICZ.JEŻELI. Wartości powiązane z zerem są usuwane; inne wartości są zachowane.
Ostatecznym wynikiem jest tablica wartości, które istnieją na obu listach, która rozlewa się do zakresu F5: F11.
Rozszerzona logika
W powyższym wzorze jako filtru używamy nieprzetworzonych wyników z LICZ.JEŻELI. To działa, ponieważ program Excel ocenia każdą wartość niezerową jako PRAWDA, a zero jako FAŁSZ. Jeśli LICZ.JEŻELI zwraca liczbę większą niż 1, filtr nadal będzie działał poprawnie.
Aby wymusić jawnie wyniki TRUE i FALSE, możesz użyć „> 0” w ten sposób:
=FILTER(list1,COUNTIF(list2,list1)>0)
Usuń duplikaty lub posortuj
Aby usunąć duplikaty, po prostu umieść formułę w funkcji UNIQUE:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Aby posortować wyniki, zagnieźdź w funkcji SORTUJ:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Brak wartości listy na liście 2
Aby wyświetlić wartości z listy 1, których brakuje na liście 2, możesz odwrócić logikę w następujący sposób:
=FILTER(list1,COUNTIF(list2,list1)=0)
