Екцел формула: Издвоји сва подударања помоћу помоћне колоне -

Преглед садржаја

Генеричка формула

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

Резиме

Један од начина за издвајање више подударања у програму Екцел је коришћење ИНДЕКС-а и МАТЦХ-а са помоћном колоном која означава подударне податке. Ово избегава сложеност напредније формуле низа. У приказаном примеру, формула у Х6 је:

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

где су цт (Г3), подаци (Б3: Е52) и помоћници (Е3: Е52) именовани опсези.

Објашњење

Изазов са претраживачким формулама које преузимају више од једног подударања је управљање дупликатима (тј. Више подударања). Формуле претраживања попут ВЛООКУП и ИНДЕКС + МАТЦХ могу лако пронаћи прво подударање, али је много теже потражити „сва поклапања“ када критеријуми пронађу више од једног подударања.

Ова формула се бави овим изазовом помоћу помоћног ступца који враћа нумеричку вредност која се може користити за лако издвајање више подударања. Формула у помоћној колони изгледа овако:

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

Помоћна колона тестира сваки ред у подацима да види да ли се одељење у колони Ц подудара са вредношћу у И3, а зграда у колони Д одговара вредности у Ј3. Оба логичка теста морају вратити ТРУЕ да би АНД вратила ТРУЕ.

За сваки ред, резултат функције АНД додаје се на „вредност изнад“ у помоћној колони да би се генерисао број. Практични ефекат ове формуле је бројач прираста који се мења само када се пронађе (ново) подударање. Тада вредност остаје иста док се не пронађе следеће поклапање. Ово функционише јер су ТРУЕ / ФАЛСЕ резултати који се враћају по АНД приморани на вредности 1/0 као део операције збрајања. ФАЛСЕ резултати не додају ништа, а ТРУЕ резултати додају 1.

Повратак у подручје издвајања, формула претраживања за Име у колони Х изгледа овако:

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

Радећи изнутра, део формуле ИНДЕКС + МАТЦХ тражи име за прво пронађено подударање, користећи број реда у колони Г као вредност подударања:

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

ИНДЕКС прима све 3 колоне података као низ (именовани опсег „подаци“), а МАТЦХ је конфигурисан да одговара броју реда унутар помоћне колоне (именовани опсег „помоћник“) у режиму тачног подударања (3. аргумент постављен на нулу) .

Ту постаје очигледна паметност формуле. Помоћни ступац очигледно садржи дупликате, али није важно, јер ће се МАТЦХ подударати само са првом вредношћу. Према дизајну, свака „прва вредност“ одговара тачном реду у табели података.

Формуле у колонама И и Ј су исте као и Х, осим броја колоне, који се у сваком случају повећава за један.

Израз ИФ који обавија формулу ИНДЕКС / МАТЦХ врши једноставну функцију - проверава сваки број реда у области за издвајање да би утврдио да ли је број реда мањи или једнак вредности у Г3 (названом опсегу "цт"), што је укупан број свих одговарајућих записа. Ако је тако, покреће се логика ИНДЕКС / МАТЦХ. Ако није, ИФ избацује празан низ ("").

Формула у Г3 (названа опсег "цт") је једноставна:

=MAX(helper)

Будући да је максимална вредност у помоћној колони једнака укупном броју подударања, функција МАКС је све што нам треба.

Напомена: област за издвајање мора бити ручно конфигурисана да обрађује онолико података колико је потребно (тј. 5 редова, 10 редова, 20 редова итд.). У овом примеру ограничено је на 5 редова само да би радни лист био компактан.

Ову технику сам научио у књизи Мике Гирвина Цонтрол + Схифт + Ентер.