Генеричка формула
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Резиме
Да бисте из листе или колоне извукли само јединствене вредности, можете користити формулу низа засновану на ИНДЕКС, МАТЦХ и ЦОУНТИФ. У приказаном примеру, формула у Д5, копирана доле, је:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
где је „листа“ именовани опсег Б5: Б11.
Напомена: ово је формула низа и мора се унети користећи цонтрол + схифт + ентер.
Објашњење
Језгро ове формуле је основно претраживање са ИНДЕКС:
=INDEX(list,row)
Другим речима, дајте ИНДЕКСУ листу и број реда, а ИНДЕКС ће добити вредност коју ће додати на јединствену листу.
Напоран рад је проналажење РЕД броја који даје ИНДЕКС, тако да добијамо само јединствене вредности. Ово се ради са МАТЦХ и ЦОУНТИФ, а главни трик је овде:
COUNTIF($D$4:D4,list)
Овде ЦОУНТИФ броји колико се пута ставке које су већ на јединственој листи појављују на главној листи, користећи прошириву референцу за опсег, $ Д $ 4: Д4.
Референца која се шири је апсолутна на једној, а релативна на другој страни. У овом случају, како се формула копира надоле, референца ће се проширити тако да укључује више редова на јединствену листу.
Имајте на уму да референца почиње у Д4, један ред изнад првог јединственог уноса, на јединственој листи. Ово је намерно - желимо да на јединственој листи рачунамо ставке * које су већ * и не можемо да уврстимо тренутну ћелију без стварања кружне референце. Дакле, започињемо са горњим редом.
Важно: водите рачуна да се наслов јединствене листе не појављује на главној листи.
За критеријуме у ЦОУНТИФ-у користимо саму главну листу. Када се добије више критеријума, ЦОУНТИФ ће вратити више резултата у низ. У сваком новом реду имамо другачији низ попут овог:
(0;0;0;0;0;0;0) // row 5 (1;0;0;0;1;0;0) // row 6 (1;1;0;0;1;0;1) // row 7 (1;1;1;1;1;0;1) // row 8
Напомена: ЦОУНТИФ обрађује више критеријума са везом „ИЛИ“ (тј. ЦОУНТИФ (опсег, („црвена“, „плава“, „зелена“)) броји црвену, плаву или зелену.
Сада имамо низове који су нам потребни за проналажење позиција (бројева редова). За ово користимо МАТЦХ, постављен за тачно подударање, да бисмо пронашли нулте вредности. Ако ставимо низове створене од стране ЦОУНТИФ горе у МАТЦХ, ево шта ћемо добити:
MATCH(0,(0;0;0;0;0;0;0),0) // 1 (Joe) MATCH(0,(1;0;0;0;1;0;0),0) // 2 (Bob) MATCH(0,(1;1;0;0;1;0;1),0) // 3 (Sue) MATCH(0,(1;1;1;1;1;0;1),0) // 6 (Aya)
МАТЦХ лоцира ставке тражећи бројач од нуле (тј. Тражећи ставке које се још не појављују на јединственој листи). Ово ради, јер МАТЦХ увек враћа прво подударање када постоје дупликати.
Коначно, позиције се уносе у ИНДЕКС као бројеви редова, а ИНДЕКС враћа име на тој позицији.
Верзија без низа са ЛООКУП
Можете да направите формулу која није низ за издвајање јединствених предмета помоћу флексибилне функције ЛООКУП:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
Конструкција формуле је слична горњој формули ИНДЕКС МАТЦХ, али ЛООКУП може нативно да обрађује операцију низа.
- ЦОУНТИФ враћа бројеве сваке вредности са „листе“ у опсегу који се шири $ Д $ 4: Д4
- Упоређивањем са нулом ствара се низ ТРУЕ и ФАЛСЕ вредности
- Број 1 је подељен низом, стварајући низ 1с и # ДИВ / 0 грешака
- Овај низ постаје лоокуп_вецтор унутар ЛООКУП-а
- Вредност претраживања 2 већа је од било које вредности у лоокуп_вецтор
- ЛООКУП ће се подударати са последњом вредношћу која није грешка у низу претраживања
- ЛООКУП враћа одговарајућу вредност у ресулт_вецтор, именовани опсег „листа“
Издвојите ставке које се појављују само једном
Горњу формулу ЛООКУП лако је проширити логичком логиком. Да бисте издвојили листу јединствених предмета који се појављују само једном у изворним подацима, можете користити формулу попут ове:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Једини додатак је други израз ЦОУНТИФ:
COUNTIF(list,list)=1
Овде ЦОУНТИФ враћа низ бројања предмета попут овог:
(2;2;2;2;2;1;2)
који се упоређују са 1, што резултира низом ТРУЕ / ФАЛСЕ вредности:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
који делују као „филтер“ за ограничавање излаза на ставке које се појављују само једном у изворним подацима.
