Формула на 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, за да може И да върне TRUE.

За всеки ред резултатът от функцията И се добавя към "стойността по-горе" в помощната колона, за да генерира брой. Практическият ефект на тази формула е нарастващ брояч, който се променя само когато бъде намерено (ново) съвпадение. Тогава стойността остава същата, докато се намери следващото съвпадение. Това работи, защото резултатите TRUE / FALSE, върнати от AND, са принудени към стойности 1/0 като част от операцията по сумиране. FALSE резултатите не добавят нищо, а TRUE резултатите добавят 1.

Обратно в зоната за извличане, формулата за търсене за Име в колона Н изглежда така:

=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.