Üldine valem
=FILTER(list1,COUNTIF(list2,list1))
Kokkuvõte
Kahe loendi võrdlemiseks ja ühiste väärtuste väljavõtmiseks võite kasutada valemit, mis põhineb funktsioonidel FILTER ja COUNTIF. Näidatud näites on valem F5-s järgmine:
=FILTER(list1,COUNTIF(list2,list1))
kus nimekiri1 (B5: B15) ja loend2 (D5: D13) nimetatakse vahemikeks. Mõlemas loendis kuvatud väärtused levivad vahemikku F5: F11.
Selgitus
Funktsioon FILTER aktsepteerib väärtuste massiivi ja argumenti "include", mis filtreerib massiivi loogilise avaldise või väärtuse põhjal.
Sellisel juhul esitatakse massiiv nimega vahemikuna "list1", mis sisaldab kõiki väärtusi jaotises B5: B15. Hulka argument on tarnitud COUNTIF funktsiooni, mis on astmeline sees filter:
=FILTER(list1,COUNTIF(list2,list1))
COUNTIF on seadistatud vahemikuna loend2 ja kriteeriumidena loend1 . Kuna anname COUNTIF-ile üksteist kriteeriumiväärtust, tagastab COUNTIF üksteist tulemust sellises massiivis:
(1;1;0;1;0;1;0;1;0;1;1)
Pange tähele, et 1-d vastavad loendi2 üksustele, mis kuvatakse loendis1.
See massiiv edastatakse otse funktsioonile FILTER argumendina "include":
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
Funktsioon FILTER filtreerib loendi1, kasutades COUNTIF-i pakutavaid väärtusi. Nulliga seotud väärtused eemaldatakse; muud väärtused säilivad.
Lõpptulemuseks on mõlemas loendis olev väärtuste massiiv, mis levib vahemikku F5: F11.
Laiendatud loogika
Ülaltoodud valemis kasutame filtrina COUNTIF-i töötlemata tulemusi. See töötab, kuna Excel hindab mis tahes nulliväärtust väärtuseks TÕENE ja nulli VÄÄR. Kui COUNTIF tagastab arvu, mis on suurem kui 1, töötab filter endiselt korralikult.
TRUE ja FALSE tulemuste selgesõnaliseks sundimiseks võite kasutada "> 0" järgmiselt:
=FILTER(list1,COUNTIF(list2,list1)>0)
Duplikaatide eemaldamine või sortimine
Duplikaatide eemaldamiseks pesake valem funktsiooni UNIQUE sisse:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Tulemuste sortimiseks pesake funktsioon SORT:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Loendist2 puuduvad loendi väärtused
Loendis 1 olevate loendist 2 puuduvate väärtuste väljastamiseks võite loogika ümber pöörata järgmiselt:
=FILTER(list1,COUNTIF(list2,list1)=0)
