Üldine valem
=FILTER(list1,COUNTIF(list2,list1))
Kokkuvõte
Andmete filtreerimiseks sobivate väärtuste kahest loendist eraldamiseks võite kasutada funktsiooni FILTER ja funktsiooni COUNTIF või COUNTIFS. Näidatud näites on valem F5-s järgmine:
=FILTER(list1,COUNTIF(list2,list1))
kus nimekiri1 (B5: B16) ja loend2 (D5: D14) nimetatakse vahemikeks. FILTERi tagastatud tulemus sisaldab ainult loendis1 olevaid väärtusi, mis kuvatakse loendis2 .
Märkus. FILTER on Excel 365 uus dünaamilise massiivi funktsioon.
Selgitus
See valem tugineb funktsioonile FILTER andmete hankimiseks funktsiooni COUNTIF põhjal loodud loogilise testi põhjal:
=FILTER(list1,COUNTIF(list2,list1))
töötades seestpoolt, kasutatakse funktsiooni COUNTIF tegeliku filtri loomiseks:
COUNTIF(list2,list1)
Teatis me kasutame List2 nagu vahemikus argumendi ja List1 kriteeriumidena argument. Teisisõnu, palume COUNTIFil loendada kõik loendi1 väärtused, mis kuvatakse loendis2. Kuna anname kriteeriumidele COUNTIF-i jaoks mitu väärtust, saame tagasi mitme tulemusega massiivi:
(1;1;0;1;0;1;0;0;1;0;1;1)
Pange tähele, et massiiv sisaldab 12 loendit, ühe loendi1 iga väärtuse jaoks . Null väärtus näitab väärtuse List1 et ei leita List2 . Mis tahes muu positiivne arv näitab loendis1 olevat väärtust, mis on loendis2 . See massiiv tagastatakse otse funktsioonile FILTER kaasamise argumendina:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Filtrifunktsioon kasutab massiivi filtrina. Kõik nulliga seotud nimekirjas1 olevad väärtused eemaldatakse, samas kui positiivse arvuga seotud väärtused säilivad.
Tulemuseks on 7 sobiva väärtuse massiiv, mis levib vahemikku F5: F11. Kui andmed muutuvad, arvutab FILTER uute andmete põhjal ümber ja tagastab uue sobivate väärtuste loendi.
Mittevastavad väärtused
Mittevastavate väärtuste väljavõtmiseks loendist1 (st väärtused loendis1, mida ei kuvata loendis2 ) saate valemile lisada funktsiooni EI:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
Funktsioon NOT muudab COUNTIF-i tulemuse tõhusalt ümber - iga nullist erinev arv muutub FALSE ja mis tahes null väärtus muutub TRUE. Tulemuseks on loetelu väärtustest loendis1, mida loendis2 pole .
Indeksiga
Sobitavate väärtuste väljavõtmiseks on võimalik luua valem ilma funktsioonita FILTER, kuid valem on keerulisem. Üks võimalus on kasutada funktsiooni INDEX sellises valemis:
G5 valem, mis on kopeeritud, on järgmine:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Märkus: see on massiivivalem ja see tuleb sisestada juhtklahviga + shift + enter, välja arvatud Excelis 365.
Selle valemi tuumaks on funktsioon INDEX, mis saab massiivi argumendina list1 . Enamik ülejäänud valemist arvutab lihtsalt rea numbri, mida kasutada väärtuste sobitamiseks. See avaldis loob suhteliste ridade arvude loendi:
ROW(list1)-ROW(INDEX(list1,1,1))+1
mis tagastab 12 numbri massiivi, mis tähistab loendi1 ridu :
(1;2;3;4;5;6;7;8;9;10;11;12)
Need filtreeritakse funktsiooni IF ja sama loogikaga, mida kasutati eespool filtris FILTER, mis põhineb funktsioonil COUNTIF:
COUNTIF(list2,list1) // find matching values
Saadud massiiv näeb välja selline:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
See massiiv edastatakse otse funktsioonile SMALL, mida kasutatakse järgmise vastava rea numbri toomiseks, kui valem kopeeritakse veerust alla. Väärtuse k väärtus (arvan, et n) arvutatakse laieneva vahemikuga:
ROWS($G$5:G5) // incrementing value for k
Funktsiooni IFERROR kasutatakse selliste vigade püüdmiseks, mis ilmnevad valemi kopeerimisel ja sobivate väärtuste lõppemisel. Selle idee teise näite saamiseks vaadake seda valemit.
