Формула на Excel: XLOOKUP с логически критерии -

Обща формула

=XLOOKUP(1,(rng1="red")*(rng2>100),results)

Обобщение

За да използвате XLOOKUP с множество логически, изградете изрази с булева логика и след това потърсете числото 1. В примера XLOOKUP се използва за търсене на първата продажба в Чикаго над 250 долара. Формулата в G6 е:

=XLOOKUP(1,(D5:D14="chicago")*(E5:E14>250),B5:B14)

който връща 0347, номера на поръчката на първия запис, който отговаря на предоставените критерии.

Забележка XLOOKUP не чувствителни към малки и големи букви.

Обяснение

XLOOKUP може да обработва масиви естествено, което го прави много полезна функция при конструиране на критерии, базирани на множество логически изрази.

В показания пример търсим номера на първата поръчка до Чикаго над $ 250. Ние изграждаме справочен масив, използвайки следния израз и логическа логика:

(D5:D14="chicago")*(E5:E14>250)

Когато този израз се изчислява, първо получаваме два масива от TRUE FALSE стойности като този:

(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)* (FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE)

Когато двата масива се умножат един по друг, математическата операция води до единичен масив от 1 и 0 като този:

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

Сега имаме следната формула и можете да видите защо използваме 1 за справочна стойност:

=XLOOKUP(1,(0;0;0;0;0;0;0;1;0;0),B5:B14)

XLOOKUP съвпада с 1 на 8-ма позиция и връща съответната 8-ма стойност от B5: B14, което е 0347.

С един-единствен критерий

Както се вижда по-горе, математическите операции автоматично принуждават TRUE и FALSE стойности към 1 и 0. Следователно, когато се използват множество изрази, търсещата стойност 1 има смисъл. В случаите, когато имате само един критерий, да речем „сума> 250“, можете да потърсите TRUE вместо това по следния начин:

=XLOOKUP(TRUE,E5:E14>250,B5:B14)

Като алтернатива можете да принудите стойностите TRUE FALSE на 1 и 0 и да използвате 1 като този.

=XLOOKUP(1,--(E5:E14>250),B5:B14)

Интересни статии...