Генеричка формула
=FILTER(list1,COUNTIF(list2,list1))
Резиме
Да бисте филтрирали податке за издвајање одговарајућих вредности на две листе, можете користити функцију ФИЛТЕР и функцију ЦОУНТИФ или ЦОУНТИФС. У приказаном примеру, формула у Ф5 је:
=FILTER(list1,COUNTIF(list2,list1))
где су листа1 (Б5: Б16) и листа2 (Д5: Д14) именовани опсези. Резултат који је вратио ФИЛТЕР укључује само вредности у листи1 које се појављују у листи2 .
Напомена: ФИЛТЕР је нова функција динамичког низа у програму Екцел 365.
Објашњење
Ова формула се ослања на функцију ФИЛТЕР за преузимање података на основу логичког теста изграђеног помоћу функције ЦОУНТИФ:
=FILTER(list1,COUNTIF(list2,list1))
радећи изнутра, функција ЦОУНТИФ користи се за стварање стварног филтера:
COUNTIF(list2,list1)
Приметите да користимо лист2 као аргумент опсега, а лист1 као аргумент критеријума. Другим речима, ми тражимо ЦОУНТИФ да преброји све вредности у листе1 које се појављују у листу2. Будући да за критеријуме дајемо ЦОУНТИФ вишеструке вредности, враћамо низ са више резултата:
(1;1;0;1;0;1;0;0;1;0;1;1)
Имајте на уму да низ садржи 12 бројања, по једно за сваку вредност у лист1 . Нулта вредност означава вредност у листи1 која није пронађена у листи2 . Било који други позитиван број означава вредност у листи1 која се налази у листи2 . Овај низ се враћа директно у функцију ФИЛТЕР као аргумент укључују:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Функција филтера користи низ као филтер. Уклања се свака вредност у листи1 повезана са нулом, док свака вредност повезана са позитивним бројем преживљава.
Резултат је низ од 7 одговарајућих вредности који се преливају у опсег Ф5: Ф11. Ако се подаци промене, ФИЛТЕР ће поново израчунати и вратити нову листу одговарајућих вредности на основу нових података.
Вредности које се не подударају
Да бисте из листе1 издвојили неподударајуће вредности (тј. Вредности на списку1 које се не појављују на списку2 ), можете додати функцију НОТ у формулу попут ове:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
Функција НОТ ефективно поништава резултат из ЦОУНТИФ - сваки број који није нула постаје ФАЛСЕ, а свака нулта вредност постаје ТРУЕ. Резултат је листа вредности у листи1 које нису присутне у листи2 .
Уз ИНДЕКС
Могуће је створити формулу за издвајање одговарајућих вредности без функције ФИЛТЕР, али формула је сложенија. Једна од могућности је употреба функције ИНДЕКС у формули попут ове:
Копирана формула у Г5 је:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Напомена: ово је формула низа и мора се унети са цонтрол + схифт + ентер, осим у програму Екцел 365.
Језгро ове формуле је функција ИНДЕКС, која прима лист1 као аргумент низа. Већина преостале формуле једноставно израчунава број реда који ће се користити за подударање вредности. Овај израз генерише листу релативних бројева редова:
ROW(list1)-ROW(INDEX(list1,1,1))+1
који враћа низ од 12 бројева који представљају редове у лист1 :
(1;2;3;4;5;6;7;8;9;10;11;12)
Они су филтрирани помоћу функције ИФ и исте логике коришћене горе у ФИЛТЕР-у, на основу функције ЦОУНТИФ:
COUNTIF(list2,list1) // find matching values
Добијени низ изгледа овако:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Овај низ се испоручује директно функцији СМАЛЛ, која се користи за дохваћање следећег одговарајућег броја реда док се формула копира низ колону. Вредност к за СМАЛЛ (мислите нтх) израчунава се са опсегом који се шири:
ROWS($G$5:G5) // incrementing value for k
Функција ИФЕРРОР користи се за хватање грешака које се јављају када се формула препише и остане без подударних вредности. За други пример ове идеје погледајте ову формулу.
