Podsumowanie
Aby przetłumaczyć wartości tekstowe na liczby i zsumować wynik, możesz użyć formuły INDEKS i PODAJ.POZYCJĘ oraz funkcji SUMA. W pokazanym przykładzie formuła w H5 to:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
gdzie „kod” to nazwany zakres K5: K9, a „wartość” to nazwany zakres L5: L9.
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter.
Wyjaśnienie
Sercem tej formuły jest podstawowa formuła INDEKS i PODAJ.POZYCJĘ, używana do tłumaczenia wartości tekstowych na liczby zdefiniowane w tabeli przeglądowej. Na przykład, aby przetłumaczyć „EX” na odpowiedni numer, użylibyśmy:
=INDEX(value,MATCH("EX",code,0))
co zwróci 4.
Skrętem tego problemu jest jednak to, że chcemy przetłumaczyć i zsumować zakres wartości tekstowych w kolumnach od C do G na liczby. Oznacza to, że musimy podać więcej niż jedną wartość wyszukiwania i potrzebujemy INDEKSU, aby zwrócić więcej niż jeden wynik. Standardowe podejście to następująca formuła:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
Po uruchomieniu MATCH mamy tablicę zawierającą 5 elementów:
=SUM(INDEX(value,(2,2,3,2,5)))
Wygląda więc na to, że INDEKS powinien zwrócić 5 wyników do SUMA. Jeśli jednak spróbujesz tego, funkcja INDEKS zwróci tylko jeden wynik SUMA. Aby INDEX zwracał wiele wyników, musimy użyć raczej mało znanej sztuczki i zawinąć MATCH w N i IF w ten sposób:
N(IF(1,MATCH(C5:G5,code,0)))
To skutecznie zmusza INDEKS do podania więcej niż jednej wartości funkcji SUMA. Po uruchomieniu INDEX mamy:
=SUM((3,3,2,3,-1))
Funkcja SUMA zwraca sumę elementów w tablicy, 10. Aby dobrze opisać to zachowanie, zobacz ten interesujący artykuł w witrynie EXCELXOR.
