Генеричка формула
=FILTER(list1,COUNTIF(list2,list1))
Резиме
Да бисте упоредили две листе и извукли уобичајене вредности, можете користити формулу засновану на функцијама ФИЛТЕР и ЦОУНТИФ. У приказаном примеру, формула у Ф5 је:
=FILTER(list1,COUNTIF(list2,list1))
где су листа1 (Б5: Б15) и листа2 (Д5: Д13) именовани опсези. Резултат, вредности које се појављују на обе листе, преливају се у опсег Ф5: Ф11.
Објашњење
Функција ФИЛТЕР прихвата низ вредности и аргумент „укључи“ који филтрира низ на основу логичког израза или вредности.
У овом случају, низ се пружа као именовани опсег „лист1“, који садржи све вредности у Б5: Б15. Укључују аргумент доставља функције ЦОУНТИФ који је смештен унутар ФИЛТЕР:
=FILTER(list1,COUNTIF(list2,list1))
ЦОУНТИФ је подешен са лист2 као опсег , а лист1 као критеријум . Пошто дајемо ЦОУНТИФ једанаест вредности критеријума, ЦОУНТИФ враћа једанаест резултата у низу попут овог:
(1;1;0;1;0;1;0;1;0;1;1)
Приметите да 1 одговара ставкама на списку2 које се појављују на списку1.
Овај низ се испоручује директно функцији ФИЛТЕР као аргумент „укључи“:
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
Листа функција ФИЛТЕР филтрира листу1 користећи вредности које пружа ЦОУНТИФ. Вредности повезане са нулом се уклањају; остале вредности су сачуване.
Коначни резултат је низ вредности које постоје на обе листе, а који се прелива у опсег Ф5: Ф11.
Проширена логика
У горњој формули као филтер користимо сирове резултате из ЦОУНТИФ. То функционише зато што Екцел сваку вредност која није нула процењује као ИСТИНА, а нулу као ФАЛСЕ. Ако ЦОУНТИФ врати број већи од 1, филтер ће и даље радити исправно.
Да бисте изричито форсирали ТРУЕ и ФАЛСЕ резултате, можете користити "> 0" овако:
=FILTER(list1,COUNTIF(list2,list1)>0)
Уклоните дупликате или сортирајте
Да бисте уклонили дупликате, само угнезди формулу унутар функције УНИКУЕ:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Да бисте сортирали резултате, угнезди се у функцији СОРТ:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Вредности листе недостају у листи2
Да бисте избацили вредности у листи1 која недостаје са листе2, логику можете обрнути овако:
=FILTER(list1,COUNTIF(list2,list1)=0)
