Обща формула
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Обобщение
За да извлечете само уникални стойности от списък или колона, можете да използвате формула на масив, базирана на INDEX, MATCH и COUNTIF. В показания пример формулата в D5, копирана надолу, е:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
където "списък" е наименуваният диапазон B5: B11.
Забележка: това е формула на масив и трябва да се въведе с помощта на control + shift + enter.
Обяснение
Ядрото на тази формула е основно търсене с INDEX:
=INDEX(list,row)
С други думи, дайте на INDEX списъка и номер на ред и INDEX ще извлече стойност, която да добави към уникалния списък.
Усилената работа е да измислим ROW числото, за да дадем INDEX, така че да получим само уникални стойности. Това се прави с MATCH и COUNTIF, а основният трик е тук:
COUNTIF($D$4:D4,list)
Тук COUNTIF преброява колко пъти елементите, които вече са в уникалния списък, се появяват в главния списък, използвайки разширяваща се справка за диапазона, $ D $ 4: D4.
Разширяващата се справка е абсолютна от едната страна, относителна от другата. В този случай, когато формулата се копира надолу, препратката ще се разшири, за да включи още редове в уникалния списък.
Обърнете внимание, че препратката започва в D4, един ред над първия уникален запис, в уникалния списък. Това е умишлено - искаме да броим елементи * вече * в уникалния списък и не можем да включим текущата клетка, без да създадем кръгова препратка. И така, започваме от реда по-горе.
Важно: уверете се, че заглавието на уникалния списък не се появява в главния списък.
За критериите в COUNTIF използваме самия главен списък. Когато са зададени множество критерии, COUNTIF ще върне множество резултати в масив. На всеки нов ред имаме различен масив като този:
(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
Забележка: COUNTIF обработва множество критерии с връзка „ИЛИ“ (т.е. COUNTIF (диапазон, („червен“, „син“, „зелен“)) брои червено, синьо или зелено.
Сега имаме масивите, от които се нуждаем, за да намерим позиции (номера на редове). За това използваме MATCH, настроен за точно съвпадение, за да намерим нулеви стойности. Ако поставим масивите, създадени от COUNTIF по-горе в MATCH, ето какво получаваме:
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)
MATCH намира елементите, като търси брой нула (т.е. търси елементи, които все още не се появяват в уникалния списък). Това работи, защото MATCH винаги връща първото съвпадение, когато има дубликати.
И накрая, позициите се подават в INDEX като номера на редове и INDEX връща името на тази позиция.
Версия без масив с LOOKUP
Можете да създадете формула без масив, за да извлечете уникални елементи, като използвате гъвкавата функция LOOKUP:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
Конструкцията на формулата е подобна на формулата INDEX MATCH по-горе, но LOOKUP може да обработва операцията с масива.
- COUNTIF връща броя на всяка стойност от "списък" в разширяващия се диапазон $ D $ 4: D4
- Сравняването с нула създава масив от TRUE и FALSE стойности
- Числото 1 се разделя на масива, създавайки масив от 1s и # DIV / 0 грешки
- Този масив става lookup_vector вътре в LOOKUP
- Справочната стойност на 2 е по-голяма от всякакви стойности в lookup_vector
- LOOKUP ще съответства на последната стойност без грешка в масива за търсене
- LOOKUP връща съответната стойност в result_vector, именувания диапазон "list"
Извличайте елементи, които се появяват само веднъж
Формулата LOOKUP по-горе е лесна за разширяване с логическа логика. За да извлечете списък с уникални елементи, които се появяват само веднъж в изходните данни, можете да използвате формула като тази:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Единственото допълнение е вторият израз COUNTIF:
COUNTIF(list,list)=1
Тук COUNTIF връща масив от брой елементи като този:
(2;2;2;2;2;1;2)
които се сравняват с 1, което води до масив от TRUE / FALSE стойности:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
които действат като „филтър“ за ограничаване на изхода до елементи, които се появяват само веднъж в изходните данни.
