Vispārēja formula
=FILTER(list1,COUNTIF(list2,list1))
Kopsavilkums
Lai filtrētu datus, lai divos sarakstos iegūtu atbilstošas vērtības, varat izmantot funkciju FILTER un funkciju COUNTIF vai COUNTIFS. Parādītajā piemērā formula F5 ir:
=FILTER(list1,COUNTIF(list2,list1))
kur List1 (B5: B16) un List2 (D5: D14) ir nosaukts diapazoniem. FILTER atgrieztajā rezultātā ir iekļautas tikai vērtības list1, kas parādās list2 .
Piezīme: FILTER ir jauna dinamiskā masīva funkcija programmā Excel 365.
Paskaidrojums
Šī formula balstās uz funkciju FILTER, lai izgūtu datus, pamatojoties uz loģisko testu, kas izveidots, izmantojot funkciju COUNTIF:
=FILTER(list1,COUNTIF(list2,list1))
strādājot no iekšpuses uz āru, faktiskā filtra izveidošanai tiek izmantota funkcija COUNTIF:
COUNTIF(list2,list1)
Ievērojiet, ka mēs izmantojam list2 kā diapazona argumentu un list1 kā kritēriju argumentu. Citiem vārdiem sakot, mēs lūdzam COUNTIF uzskaitīt visas vērtības list1, kas parādās list2. Tā kā kritērijiem piešķiram COUNTIF vairākas vērtības, mēs iegūstam masīvu ar vairākiem rezultātiem:
(1;1;0;1;0;1;0;0;1;0;1;1)
Ievērojiet, ka masīvā ir 12 skaitļi, pa vienam katrai saraksta1 vērtībai . Nulles vērtība norāda vērtību list1, kas nav atrodama list2 . Jebkurš cits pozitīvs skaitlis norāda vērtību list1, kas atrodama list2 . Šis masīvs tiek tieši atgriezts FILTER funkcijā kā arguments arguments:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Filtrēšanas funkcija izmanto masīvu kā filtru. Jebkura vērtība 1. sarakstā, kas saistīta ar nulli, tiek noņemta, bet jebkura vērtība, kas saistīta ar pozitīvu skaitli, izdzīvo.
Rezultāts ir 7 atbilstošu vērtību masīvs, kas izplūst diapazonā F5: F11. Ja mainīsies dati, FILTER pārrēķinās un atgriezīs jaunu atbilstošo vērtību sarakstu, pamatojoties uz jaunajiem datiem.
Neatbilstošas vērtības
Lai izvilktu neatbilstošās vērtības no list1 (ti, vērtības list1, kas neparādās list2 ), formulai varat pievienot funkciju NOT šādi:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
Funkcija NOT faktiski atceļ rezultātu no COUNTIF - jebkurš skaitlis, kas nav nulle, kļūst FALSE, un jebkura nulles vērtība kļūst PATIESA. Rezultāts ir saraksts ar vērtībām sarakstā 1 ietvertajiem ražojumiem , kas nav klāt List2 .
Ar INDEX
Ir iespējams izveidot formulu, lai izvilktu atbilstošās vērtības bez funkcijas FILTER, taču formula ir sarežģītāka. Viena no iespējām ir izmantot funkciju INDEX šādā formulā:
Formula G5, kas nokopēta uz leju, ir:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Piezīme: šī ir masīva formula, un tā jāievada ar vadības taustiņu + Shift + Enter, izņemot Excel 365.
Šīs formulas kodols ir funkcija INDEX, kas kā masīva argumentu saņem list1 . Lielākā daļa atlikušās formulas vienkārši aprēķina rindas numuru, ko izmantot vērtību saskaņošanai. Šis izteiciens ģenerē relatīvo rindu numuru sarakstu:
ROW(list1)-ROW(INDEX(list1,1,1))+1
kas atgriež 12 skaitļu masīvu, kas apzīmē 1. saraksta rindas :
(1;2;3;4;5;6;7;8;9;10;11;12)
Tie tiek filtrēti, izmantojot funkciju IF un to pašu loģiku, kas iepriekš izmantota FILTER, pamatojoties uz funkciju COUNTIF:
COUNTIF(list2,list1) // find matching values
Iegūtais masīvs izskatās šādi:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Šis masīvs tiek piegādāts tieši funkcijai MAZA, kas tiek izmantota, lai iegūtu nākamo atbilstošās rindas numuru, kamēr formula tiek nokopēta kolonnā. K vērtību SMALL (domāju, ka n) vērtē ar paplašinošu diapazonu:
ROWS($G$5:G5) // incrementing value for k
Funkcija IFERROR tiek izmantota, lai noķertu kļūdas, kas rodas, ja formula tiek nokopēta un beidzas atbilstošās vērtības. Citu šīs idejas piemēru skatiet šajā formulā.
