Загальна формула
(=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 отримає значення, яке потрібно додати до унікального списку.
Важка робота полягає в тому, щоб з’ясувати число РЯДОК, щоб отримати ІНДЕКС, щоб ми отримали лише унікальні значення. Це робиться за допомогою 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
- Значення пошуку 2 більше, ніж будь-яке значення у векторі пошуку
- 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)
які діють як "фільтр", щоб обмежити вихід до елементів, які трапляються лише один раз у вихідних даних.
