Bendroji formulė
=FILTER(list1,COUNTIF(list2,list1))
Santrauka
Norėdami filtruoti duomenis ir išskleisti atitinkamas vertes iš dviejų sąrašų, galite naudoti funkciją FILTER ir funkciją COUNTIF arba COUNTIFS. Parodytame pavyzdyje formulė F5 yra:
=FILTER(list1,COUNTIF(list2,list1))
kur sąrašas1 (B5: B16) ir sąrašas2 (D5: D14) yra pavadinti diapazonai. Grąžintas pagal FILTER rezultatas apima tik vertybes List1 , kurie rodomi Saraksts2 .
Pastaba: FILTRAS yra nauja „Excel 365“ dinaminio masyvo funkcija.
Paaiškinimas
Ši formulė remiasi funkcija FILTER, kad gautų duomenis pagal loginį testą, sukurtą naudojant funkciją COUNTIF:
=FILTER(list1,COUNTIF(list2,list1))
dirbant iš vidaus, funkcija COUNTIF naudojama tikram filtrui sukurti:
COUNTIF(list2,list1)
Atkreipkite dėmesį, kad mes naudojame sąrašą2 kaip diapazono argumentą ir sąrašą1 kaip kriterijų argumentą. Kitaip tariant, mes prašome COUNTIF suskaičiuoti visas sąrašo 1 reikšmes, kurios rodomos sąraše2. Kadangi pagal kriterijus COUNTIF suteikiame kelias reikšmes, gauname masyvą su keliais rezultatais:
(1;1;0;1;0;1;0;0;1;0;1;1)
Atkreipkite dėmesį, kad masyve yra 12 skaičių, po vieną kiekvienai reikšmei sąraše1 . Nulis reikšmė nurodo sąrašo1 vertę, kurios nerandama sąraše2 . Bet kuris kitas teigiamas skaičius nurodo sąrašo1 vertę, kuri randama sąraše2 . Šis masyvas grąžinamas tiesiai į funkciją FILTRAS kaip argumentą „įtraukti“:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Filtro funkcija naudoja masyvą kaip filtrą. Bet vertės List1 , susijusių su nuliu bus pašalintas, o bet kokia vertė siejama su teigiamas skaičius išlieka.
Rezultatas yra 7 sutampančių verčių masyvas, kuris patenka į F5: F11 diapazoną. Jei duomenys pasikeis, FILTER perskaičiuos ir pateiks naują suderinamų verčių sąrašą, pagrįstą naujais duomenimis.
Neatitinkančios vertės
Norėdami išskleisti neatitinkančias reikšmes iš „ list1“ (t. Y. Reikšmės „ list1“ , kurios nerodomos „list2“ ), prie tokios formulės galite pridėti funkciją NOT:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
Funkcija NOT efektyviai pakeičia rezultatą iš COUNTIF - bet koks ne nulis skaičius tampa NETIESA, o bet kuri nulio reikšmė - TIKRA. Rezultatas yra sąrašas1 reikšmių , kurių nėra sąraše2, sąrašas .
Su INDEX
Galima sukurti formulę, kad atitikties reikšmės būtų išgautos be funkcijos FILTER, tačiau formulė yra sudėtingesnė. Viena iš galimybių yra naudoti INDEX funkciją tokioje formulėje:
G5 formulė, nukopijuota žemyn, yra:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Pastaba: tai yra masyvo formulė, kurią reikia įvesti paspaudus „Control + Shift + Enter“, išskyrus „Excel 365“.
Šios formulės esmė yra funkcija INDEX, kuri gauna list1 kaip masyvo argumentą. Didžioji dalis likusios formulės paprasčiausiai apskaičiuoja eilutės numerį, kurį reikia naudoti, kad atitiktų reikšmes. Ši išraiška sukuria santykinių eilučių skaičių sąrašą:
ROW(list1)-ROW(INDEX(list1,1,1))+1
kuris pateikia 12 skaičių masyvą, nurodantį 1 sąrašo eilutes :
(1;2;3;4;5;6;7;8;9;10;11;12)
Jie filtruojami naudojant funkciją IF ir tą pačią logiką, kuri buvo naudojama aukščiau FILTER, remiantis funkcija COUNTIF:
COUNTIF(list2,list1) // find matching values
Gautas masyvas atrodo taip:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Šis masyvas yra pristatomas tiesiai į SMALL funkciją, kuri naudojama norint gauti kitą atitinkantį eilutės numerį, kai formulė nukopijuojama žemyn stulpelyje. K vertė SMALL (pagalvokime n-ąją) apskaičiuojama plečiant diapazoną:
ROWS($G$5:G5) // incrementing value for k
IFERROR funkcija naudojama klaidoms, atsirandančioms nukopijavus formulę ir pasibaigus atitinkamoms reikšmėms, sulaikyti. Kitą šios idėjos pavyzdį rasite šioje formulėje.
