Yleinen kaava
=FILTER(list1,COUNTIF(list2,list1))
Yhteenveto
Voit vertailla kahta luetteloa ja purkaa yhteisiä arvoja käyttämällä FILTER- ja COUNTIF-funktioihin perustuvaa kaavaa. Esitetyssä esimerkissä kaava kohdassa F5 on:
=FILTER(list1,COUNTIF(list2,list1))
missä luettelo1 (B5: B15) ja luettelo2 (D5: D13) on nimetty alueeksi . Tulos, molemmissa luetteloissa näkyvät arvot, valuu alueelle F5: F11.
Selitys
FILTER-toiminto hyväksyy joukon arvoja ja "include" -argumentin, joka suodattaa matriisin loogisen lausekkeen tai arvon perusteella.
Tässä tapauksessa taulukko on nimetty alue "list1", joka sisältää kaikki arvot kohdassa B5: B15. Ovat väite on toimittama COUNTIF toiminto, joka on sisäkkäisiä sisällä FILTER:
=FILTER(list1,COUNTIF(list2,list1))
COUNTIF on määritetty luetteloksi 2 alueeksi ja luetteloksi1 kriteereiksi . Koska annamme COUNTIF: lle yksitoista ehtoarvoa, COUNTIF palauttaa yksitoista tulosta tällaisessa taulukossa:
(1;1;0;1;0;1;0;1;0;1;1)
Huomaa, että 1: t vastaavat listan 2 kohteita, jotka näkyvät luettelossa1.
Tämä taulukko toimitetaan suoraan FILTER-funktiolle "sisälly" -argumenttina:
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
Suodatin-toiminto suodattaa luettelon1 käyttämällä COUNTIF: n antamia arvoja. Nollaan liittyvät arvot poistetaan; muut arvot säilyvät.
Lopputulos on molemmissa luetteloissa olevien arvojen joukko, joka valuu alueelle F5: F11.
Laajennettu logiikka
Yllä olevassa kaavassa käytämme suodattimena COUNTIF: n raakatuloksia. Tämä toimii, koska Excel arvioi minkä tahansa nollasta poikkeavan arvon TOSI ja nollan EPÄTOSI. Jos COUNTIF palauttaa määrän, joka on suurempi kuin 1, suodatin toimii edelleen oikein.
Jos haluat pakottaa TOSI ja EPÄTOSI -tulokset yksiselitteisesti, voit käyttää "> 0" -tapaa seuraavasti:
=FILTER(list1,COUNTIF(list2,list1)>0)
Poista kaksoiskappaleet tai lajittele
Poista kaksoiskappaleet sijoittamalla kaava UNIQUE-toiminnon sisään:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Voit lajitella tulokset pesimällä SORT-toiminnossa:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Luettelosta 2 puuttuu luetteloarvoja
Jos haluat tulostaa luettelon 1 arvot, jotka puuttuvat luettelosta 2, voit kääntää logiikan seuraavasti:
=FILTER(list1,COUNTIF(list2,list1)=0)
