Формула Excel: Витягніть усі збіги за допомогою допоміжного стовпця -

Зміст

Загальна формула

=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")

Резюме

Одним із способів вилучення декількох збігів у Excel є використання INDEX та MATCH із допоміжним стовпцем, який позначає відповідні дані. Це дозволяє уникнути складності більш вдосконаленої формули масиву. У наведеному прикладі формула в H6 така:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

де ct (G3), дані (B3: E52) і помічник (E3: E52) називаються діапазонами.

Пояснення

Проблема з пошуковими формулами, які отримують більше одного збігу, полягає в управлінні дублікатами (тобто кількома збігами). Формули пошуку, такі як VLOOKUP та INDEX + MATCH, можуть легко знайти перший збіг, але набагато складніше шукати "всі збіги", коли критерії знаходять більше одного збігу.

Ця формула вирішує цю проблему, використовуючи допоміжний стовпець, який повертає числове значення, яке можна використовувати для легкого вилучення кількох збігів. Формула в допоміжному стовпці виглядає так:

=SUM(E2,AND(C3=$I$3,D3=$J$3))

Допоміжний стовпець перевіряє кожен рядок у даних, щоб перевірити, чи відповідає Відділ у стовпці C значення в I3, а Будівля у стовпці D - у J3. Обидва логічні тести повинні повернути TRUE, щоб AND повернув TRUE.

Для кожного рядка результат від функції AND додається до "значення вище" у допоміжному стовпці для створення підрахунку. Практичним ефектом цієї формули є збільшення лічильника, який змінюється лише тоді, коли знайдено (нове) збіг. Тоді значення залишається незмінним, поки не буде знайдено наступний збіг. Це працює, оскільки результати TRUE / FALSE, що повертаються І, примусові до значень 1/0 як частина операції підсумовування. Неправдиві результати нічого не додають, а результати TRUE додають 1.

Повернувшись до області видобутку, формула пошуку для Name у стовпці H виглядає так:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

Працюючи з вивороту, частина формули INDEX + MATCH шукає назву першого знайденого збігу, використовуючи номер рядка в стовпці G як значення збігу:

INDEX(data,MATCH($G6,helper,0),1)

INDEX отримує всі 3 стовпці даних як масив (іменований діапазон "дані"), а MATCH налаштовано на відповідність номеру рядка всередині допоміжного стовпця (іменований діапазон "помічник") у режимі точного збігу (3-й аргумент встановлений на нуль) .

Тут стає очевидним розумність формули. Допоміжний стовпець, очевидно, містить дублікати, але це не має значення, оскільки MATCH буде відповідати лише першому значенню. За задумом кожне "перше значення" відповідає правильному рядку в таблиці даних.

Формули у стовпцях I та J однакові з H, за винятком номера стовпця, який у кожному випадку збільшується на одиницю.

Оператор IF, який обгортає формулу INDEX / MATCH, виконує просту функцію - перевіряє кожний номер рядка в області вилучення, щоб дізнатись, чи є номер рядка меншим або рівним значенню в G3 (іменований діапазон "ct"), тобто загальна кількість усіх відповідних записів. Якщо так, то запускається логіка INDEX / MATCH. Якщо ні, IF виводить порожній рядок ("").

Формула в G3 (з іменем діапазону "ct") проста:

=MAX(helper)

Оскільки максимальне значення в допоміжному стовпці збігається із загальним числом збігів, функція MAX - це все, що нам потрібно.

Примітка: область вилучення потрібно налаштувати вручну, щоб обробляти стільки даних, скільки потрібно (тобто 5 рядків, 10 рядків, 20 рядків тощо). У цьому прикладі обмежується лише 5 рядками, щоб зберегти компактність робочого аркуша.

Цю техніку я навчився в книзі Майка Гірвіна Control + Shift + Enter.