Формула Excel: багатокритеріальний пошук та транспонування -

Зміст

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

(=INDEX(rng1,MATCH(1,($A1=rng2)*(B$1=rng3),0)))

Резюме

Для виконання багатокритеріального пошуку та транспонування результатів у таблицю можна використовувати формулу масиву на основі INDEX та MATCH. У наведеному прикладі формула в G5 має вигляд:

(=INDEX(amount,MATCH(1,($F5=location)*(G$4=date),0)))

Зверніть увагу, що ця формула є формулою масиву і її потрібно вводити за допомогою control + shift + enter.

Ця формула також використовує три іменовані діапазони: розташування = B5: B13, сума = D5: D13, дата = C5: C13

Пояснення

Ядром цієї формули є INDEX, яка отримує значення з названого діапазону "сума" (B5: B13):

=INDEX(amount,row_num)

де row_num опрацьовується за допомогою функції MATCH та деякої логічної логіки:

MATCH(1,($F5=location)*(G$4=date),0)

У цьому фрагменті місце в F5 порівнюється з усіма розташуваннями, а дата в G4 порівнюється з усіма датами. Результатом у кожному випадку є масив значень TRUE та FALSE. Коли ці масиви множать разом, математична операція примушує значення TRUE і FALSE до значень one та нулів, так що масив пошуку, що надходить у MATCH, виглядає так:

(1;0;0;0;0;0;0;0;0)

MATCH налаштовано на відповідність 1 як точну відповідність і повертає позицію до INDEX як номер рядка. Число 1 працює для значення підстановки, оскільки масив тепер містить лише 1 і 0, як показано вище.

F5 та G4 вводяться як змішані посилання, щоб формулу можна було скопіювати через таблицю без змін.