Všeobecný vzorec
=FILTER(list1,COUNTIF(list2,list1))
Zhrnutie
Ak chcete filtrovať údaje a extrahovať zhodné hodnoty do dvoch zoznamov, môžete použiť funkciu FILTER a funkciu COUNTIF alebo COUNTIFS. V zobrazenom príklade je vzorec v F5:
=FILTER(list1,COUNTIF(list2,list1))
kde list1 (B5: B16) a list2 (D5: D14) sú pomenované rozsahy. Výsledok vrátený funkciou FILTER obsahuje iba hodnoty v zozname1, ktoré sa zobrazia v zozname2 .
Poznámka: FILTER je nová funkcia dynamického poľa v Exceli 365.
Vysvetlenie
Tento vzorec sa spolieha na funkciu FILTER pri načítaní údajov na základe logického testu zostaveného s funkciou COUNTIF:
=FILTER(list1,COUNTIF(list2,list1))
pri práci zvnútra von sa funkcia COUNTIF používa na vytvorenie skutočného filtra:
COUNTIF(list2,list1)
Všimnite si, sme pomocou Hárok2 ako rozsah argumentu a Hárok1 ako argument kritériá. Inými slovami, žiadame COUNTIF, aby spočítal všetky hodnoty v zozname1, ktoré sa vyskytujú v zozname2. Pretože kritériám dávame COUNTIF viac hodnôt, dostaneme pole s viacerými výsledkami:
(1;1;0;1;0;1;0;0;1;0;1;1)
Všimnite si, že pole obsahuje 12 impulzov, jeden pre každú hodnotu v zozname1 . Nulová hodnota označuje hodnotu v zozname1, ktorá sa nenachádza v zozname2 . Akékoľvek ďalšie kladné číslo označuje hodnotu v zozname1, ktorá sa nachádza v zozname2 . Toto pole sa vracia priamo do funkcie FILTER ako argument zahrnutia:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Funkcia filtra používa pole ako filter. Akákoľvek hodnota v zozname1 spojená s nulou je odstránená, zatiaľ čo akákoľvek hodnota spojená s kladným číslom prežije.
Výsledkom je pole 7 zhodných hodnôt, ktoré sa šíria do rozsahu F5: F11. Ak sa údaje zmenia, FILTER prepočíta a vráti nový zoznam zodpovedajúcich hodnôt na základe nových údajov.
Nezhodné hodnoty
Ak chcete extrahovať nezhodné hodnoty zo zoznamu1 (tj hodnoty v zozname1 , ktoré sa nezobrazia v zozname2 ), môžete do vzorca pridať funkciu NOT takto:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
Funkcia NOT efektívne obráti výsledok z COUNTIF - akékoľvek nenulové číslo sa stane FALSE a akákoľvek nulová hodnota sa stane TRUE. Výsledkom je zoznam hodnôt v zozname1, ktoré sa v zozname2 nenachádzajú .
S indexom INDEX
Je možné vytvoriť vzorec na extrahovanie zhodných hodnôt bez funkcie FILTER, ale vzorec je zložitejší. Jednou z možností je použitie funkcie INDEX vo vzorci, ako je tento:
Vzorec v G5, skopírovaný nadol, je:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter, s výnimkou Excel 365.
Jadrom tohto vzorca je funkcia INDEX, ktorá prijíma zoznam1 ako argument poľa. Väčšina zostávajúceho vzorca jednoducho vypočíta číslo riadku, ktorý sa má použiť na priradenie hodnôt. Tento výraz generuje zoznam relatívnych čísel riadkov:
ROW(list1)-ROW(INDEX(list1,1,1))+1
ktorá vráti pole 12 čísel predstavujúcich riadky v zozname1 :
(1;2;3;4;5;6;7;8;9;10;11;12)
Filtrované sú pomocou funkcie IF a rovnakej logiky použitej vyššie v časti FILTER na základe funkcie COUNTIF:
COUNTIF(list2,list1) // find matching values
Výsledné pole vyzerá takto:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Toto pole sa dodáva priamo do funkcie MALÉ, ktorá sa používa na získanie nasledujúceho zodpovedajúceho čísla riadku, keď sa vzorec kopíruje nadol do stĺpca. Hodnota k pre MALÉ (myslím, že n-té) sa počíta s rozširujúcim sa rozsahom:
ROWS($G$5:G5) // incrementing value for k
Funkcia IFERROR sa používa na zachytenie chýb, ku ktorým dôjde pri kopírovaní vzorca a vyčerpaní zodpovedajúcich hodnôt. Ďalší príklad tejto myšlienky nájdete v tomto vzorci.
