Обща формула
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Обобщение
Преглед на малки и големи букви в Excel
По подразбиране стандартните справки в Excel не са чувствителни към малки и големи букви. Както VLOOKUP, така и INDEX / MATCH просто ще върнат първото съвпадение, игнорирайки случая.
Прекият начин за заобикаляне на това ограничение е да се използва формула на масив, базирана на INDEX / MATCH с EXACT. Ако обаче търсите само цифрови стойности, SUMPRODUCT + EXACT също така предлага интересен и гъвкав начин за търсене на регистър на регистрите.
В примера използваме следната формула
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Въпреки че тази формула е формула на масив, не е необходимо да се въвежда с Control + Shift + Enter, тъй като SUMPRODUCT обработва масивите естествено.
Обяснение
SUMPRODUCT е проектиран да работи с масиви, които умножава, след което сумира.
В този случай ние сме два масива с SUMPRODUCT: B3: B8 и C3: C8. Номерът е да стартирате тест за стойностите в колона Б, след което да преобразувате получените стойности TRUE / FALSE в 1 и 0. Изпълняваме теста с EXACT точно така:
EXACT(E3,B3:B8)
Който произвежда този масив:
(FALSE; FALSE; TRUE; FALSE; FALSE; FALSE)
Имайте предвид, че истинската стойност в позиция 3 е нашето съвпадение. След това използваме двойното отрицателно (т.е. -, което технически е "двойно унарно"), за да принудим тези TRUE / FALSE стойности в 1 и 0. Резултатът е този масив:
(0; 0; 1; 0; 0; 0)
В този момент при изчислението формулата SUMPRODUCT изглежда така:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
След това SUMPRODUCT просто умножава елементите във всеки масив, за да създаде окончателен масив:
(0; 0; 775; 0; 0; 0)
Което SUMPRODUCT след това сумира и връща 775.
И така, същността на тази формула е, че стойностите FALSE се използват за отмяна на всички други стойности. Единствените ценности, които оцеляват, са тези, които са били ИСТИНА.
Имайте предвид, че тъй като използваме SUMPRODUCT, тази формула се предлага с уникален обрат: ако има множество съвпадения, SUMPRODUCT ще върне сумата от тези съвпадения. Това може и да не е това, което искате, така че внимавайте, ако очаквате множество мачове!
Не забравяйте, че тази формула работи само за числови стойности, тъй като SUMPRODUCT не обработва текст. Ако искате да извлечете текст, използвайте INDEX / MATCH + EXACT.
