![](https://cdn.wiki-base.com/7401116/excel_formula_filter_to_extract_matching_values__2.png.webp)
Yleinen kaava
=FILTER(list1,COUNTIF(list2,list1))
Yhteenveto
Voit suodattaa tietoja vastaavien arvojen purkamiseksi kahteen luetteloon käyttämällä FILTER-funktiota ja COUNTIF- tai COUNTIFS-toimintoa. Esitetyssä esimerkissä kaava kohdassa F5 on:
=FILTER(list1,COUNTIF(list2,list1))
missä luettelo1 (B5: B16) ja luettelo2 (D5: D14) on nimetty alueeksi . Tulos palauttama suodin ainoastaan arvoja list1 jotka näkyvät Lista2 .
Huomautus: SUODATIN on uusi dynaaminen matriisitoiminto Excel 365: ssä.
Selitys
Tämä kaava perustuu FILTER-toimintoon tietojen noutamiseen perustuen loogiseen testiin, joka on rakennettu COUNTIF-funktiolla:
=FILTER(list1,COUNTIF(list2,list1))
Työskentely sisältä ulospäin, COUNTIF-funktiota käytetään varsinaisen suodattimen luomiseen:
COUNTIF(list2,list1)
Huomaa, että käytämme luettelo2 aluealustana ja luettelo1 kriteeriargumenttina. Toisin sanoen, pyydämme LASKE.JOS laskea kaikki arvot list1 jotka näkyvät Lista2. Koska annamme kriteereille COUNTIF useita arvoja, saamme takaisin joukon, jolla on useita tuloksia:
(1;1;0;1;0;1;0;0;1;0;1;1)
Huomaa array sisältää 12 laskee, yksi kutakin arvo list1 . Nolla-arvo osoittaa arvo list1 että ei löydy list2 . Muita positiivinen luku osoittaa arvoa list1 , joka löytyy list2 . Tämä taulukko palautetaan suoraan FILTER-funktioon include-argumenttina:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Suodatintoiminto käyttää taulukkoa suodattimena. Mitään arvoa list1 liittyy nolla on poistettu, kun taas mikä tahansa arvo, joka liittyy positiivinen luku selviää.
Tuloksena on joukko 7 vastaavaa arvoa, jotka valuvat alueelle F5: F11. Jos tiedot muuttuvat, FILTER laskee uudelleen ja palauttaa uuden luettelon vastaavista arvoista uusien tietojen perusteella.
Ei-yhteensopivat arvot
Poimia kuin vastaavia arvoja list1 (eli arvot list1 , jotka eivät näy List2 ) voit lisätä ei toimi kaavan näin:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
NOT-toiminto kääntää COUNTIF-tuloksen tehokkaasti - mistä tahansa lukusta, joka ei ole nolla, tulee EPÄTOSI ja kaikista nollan arvoista TOSI. Tuloksena on luettelo arvot list1 , jotka eivät ole läsnä list2 .
Indeksillä
On mahdollista luoda kaava vastaavien arvojen purkamiseksi ilman FILTER-funktiota, mutta kaava on monimutkaisempi. Yksi vaihtoehto on käyttää INDEX-funktiota tällaisessa kaavassa:
G5: n kaava, kopioitu alaspäin, on:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Huomaa: tämä on matriisikaava, ja se on syötettävä näppäimillä + + shift + enter paitsi Excel 365: ssä.
Tämän kaavan ydin on INDEX-funktio, joka vastaanottaa listan1 taulukko argumenttina. Suurin osa jäljellä olevasta kaavasta laskee yksinkertaisesti rivinumeron, jota käytetään vastaaviin arvoihin. Tämä lauseke luo luettelon suhteellisista rivinumeroista:
ROW(list1)-ROW(INDEX(list1,1,1))+1
joka palauttaa 12 numeron matriisin, joka edustaa listan1 rivejä :
(1;2;3;4;5;6;7;8;9;10;11;12)
Nämä suodatetaan IF-funktiolla ja samalla logiikalla, jota käytettiin edellä FILTER-toiminnossa COUNTIF-funktion perusteella:
COUNTIF(list2,list1) // find matching values
Tuloksena oleva taulukko näyttää tältä:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Tämä taulukko toimitetaan suoraan PIENI-funktioon, jota käytetään seuraavan vastaavan rivinumeron hakemiseen, kun kaava kopioidaan alas sarakkeesta. Pienen k-arvo (ajatellaan n: tä) lasketaan laajenevalla alueella:
ROWS($G$5:G5) // incrementing value for k
IFERROR-funktiota käytetään virheiden ansaitsemiseen, kun kaava kopioidaan alas ja vastaavat arvot loppuvat. Katso toinen esimerkki tästä ajatuksesta tästä kaavasta.