Резюме
Чтобы преобразовать текстовые значения в числа и суммировать результат, вы можете использовать формулы ИНДЕКС и ПОИСКПОЗ, а также функцию СУММ. В показанном примере формула H5 имеет следующий вид:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
где «код» - это именованный диапазон K5: K9, а «значение» - именованный диапазон L5: L9.
Примечание: это формула массива, и ее необходимо вводить с помощью Ctrl + Shift + Enter.
Объяснение
В основе этой формулы лежит базовая формула ИНДЕКС и ПОИСКПОЗ, используемая для преобразования текстовых значений в числа, как определено в таблице поиска. Например, чтобы перевести «EX» в соответствующее число, мы должны использовать:
=INDEX(value,MATCH("EX",code,0))
который вернет 4.
Однако поворот в этой проблеме заключается в том, что мы хотим преобразовать и суммировать диапазон текстовых значений в столбцах от C до G в числа. Это означает, что нам нужно предоставить более одного значения поиска, и нам нужно, чтобы INDEX возвращал более одного результата. Стандартный подход - это такая формула:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
После выполнения MATCH у нас есть массив из 5 элементов:
=SUM(INDEX(value,(2,2,3,2,5)))
Похоже, что ИНДЕКС должен вернуть 5 результатов в СУММ. Однако, если вы попробуете это, функция ИНДЕКС вернет только один результат СУММ. Чтобы заставить INDEX возвращать несколько результатов, нам нужно использовать довольно непонятный трюк и заключить MATCH в N и IF следующим образом:
N(IF(1,MATCH(C5:G5,code,0)))
Это фактически заставляет ИНДЕКС предоставить более одного значения функции СУММ. После выполнения INDEX у нас есть:
=SUM((3,3,2,3,-1))
А функция SUM возвращает сумму элементов в массиве, 10. Чтобы подробно описать это поведение, см. Эту интересную статью на веб-сайте EXCELXOR.
