Обща формула
=SUMPRODUCT(--(A1=things))>0
Обобщение
Ако искате да тествате клетка, за да видите дали тя се равнява на едно от няколко неща, можете да го направите с формула, която използва функцията SUMPRODUCT.
Контекст
Да приемем, че имате списък с текстови низове в диапазона B5: B11 и искате да тествате всяка клетка спрямо друг списък с неща в диапазон E5: E9. С други думи, за всяка клетка в B5: B11 искате да знаете: равна ли е тази клетка на някое от нещата в E5: E9?
Можете да започнете да изграждате голяма формула, базирана на вложени IF изрази, но формула на масив, базирана на SUMPRODUCT, е по-прост, по-чист подход.
Решение
Решението е да се създаде формула, която ще тества множество стойности и ще върне списък с TRUE / FALSE стойности. След като имаме това, можем да обработим този списък (всъщност масив) с SUMPRODUCT.
Формулата, която използваме, изглежда така:
=SUMPRODUCT(--(B5=things))>0
Обяснение
Ключът е този фрагмент:
--(B5=things)
което просто сравнява стойността в B5 с всяка стойност в посочения диапазон "неща". Тъй като сравняваме B5 с масив (т.е. наименувания диапазон "неща", E5: E11), резултатът ще бъде масив от TRUE / FALSE стойности като този:
(TRUE; FALSE; FALSE; FALSE; FALSE)
Ако имаме дори една ИСТИНА в масива, знаем, че В5 се равнява на поне едно нещо в списъка, така че, за да принудим стойностите ИСТИНА / НЕВЯРНО до 1s и 0s, използваме двойно отрицателно (-, наричано още двойно единствен). След тази принуда имаме следното:
(1; 0; 0; 0; 0)
Сега обработваме резултата със SUMPRODUCT, който ще събере елементите в масива. Ако получим някакъв ненулев резултат, имаме поне едно съвпадение, така че използваме> 1, за да принудим краен резултат или TRUE, или FALSE.
С твърдо кодиран списък
Няма изискване да използвате диапазон за вашия списък с неща. Ако търсите само малък брой неща, можете да използвате списък във формат на масив, който се нарича константа на масива. Например, ако просто търсите цветовете червено, синьо и зелено, можете да използвате ("червено", "синьо", "зелено") по следния начин:
--(B5=("red","blue","green"))
Справяне с допълнителни интервали
Ако клетките, които тествате, съдържат допълнително пространство, те няма да съвпадат правилно. За да премахнете цялото допълнително пространство, можете да промените формулата, за да използвате функцията TRIM по следния начин:
=SUMPRODUCT(--(TRIM(A1)=things))>0
