Formuła ogólna
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Podsumowanie
Wyszukiwanie z uwzględnieniem wielkości liter w programie Excel
Domyślnie w przypadku standardowych wyszukiwań w programie Excel wielkość liter nie jest rozróżniana. Zarówno WYSZUKAJ.PIONOWO, jak i INDEKS / PODAJ.POZYCJĘ zwrócą po prostu pierwsze dopasowanie, ignorując wielkość liter.
Bezpośrednim sposobem obejścia tego ograniczenia jest użycie formuły tablicowej opartej na INDEX / MATCH z EXACT. Jeśli jednak szukasz tylko wartości liczbowych, SUMPRODUCT + EXACT zapewnia również interesujący i elastyczny sposób wyszukiwania z uwzględnieniem wielkości liter.
W tym przykładzie używamy następującego wzoru
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Chociaż ta formuła jest formułą tablicową, nie trzeba jej wprowadzać za pomocą klawiszy Control + Shift + Enter, ponieważ SUMPRODUCT obsługuje tablice natywnie.
Wyjaśnienie
SUMPRODUCT jest zaprojektowany do pracy z tablicami, które mnoży, a następnie sumuje.
W tym przypadku mamy dwie tablice z SUMPRODUCT: B3: B8 i C3: C8. Sztuczka polega na uruchomieniu testu na wartościach w kolumnie B, a następnie przekonwertowaniu wynikowych wartości PRAWDA / FAŁSZ na 1 i 0. Uruchamiamy test z EXACT w następujący sposób:
EXACT(E3,B3:B8)
Który tworzy tę tablicę:
(FALSE; FALSE; TRUE; FALSE; FALSE; FALSE)
Zauważ, że prawdziwa wartość na pozycji 3 to nasza zgodność. Następnie używamy podwójnej ujemnej wartości (tj. -, która jest technicznie „podwójną jednostką jednoargumentową”), aby przekształcić te wartości PRAWDA / FAŁSZ na 1 i 0. Wynikiem jest następująca tablica:
(0; 0; 1; 0; 0; 0)
W tym momencie obliczeń formuła SUMPRODUCT wygląda następująco:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
SUMPRODUCT następnie po prostu mnoży elementy w każdej tablicy, tworząc ostateczną tablicę:
(0; 0; 775; 0; 0; 0)
Który SUMPRODUCT następnie sumuje i zwraca 775.
Zatem sednem tej formuły jest to, że wartości FALSE są używane do anulowania wszystkich innych wartości. Jedyne wartości, które przetrwały, to te, które były PRAWDZIWE.
Zauważ, że ponieważ używamy SUMPRODUCT, ta formuła ma unikalny zwrot: jeśli jest wiele dopasowań, SUMPRODUCT zwróci sumę tych dopasowań. Może to być to, czego chcesz, ale uważaj, jeśli spodziewasz się wielu dopasowań!
Pamiętaj, że ta formuła działa tylko dla wartości liczbowych, ponieważ SUMPRODUCT nie obsługuje tekstu. Jeśli chcesz pobrać tekst, użyj INDEX / MATCH + EXACT.
