Резюме
Щоб перевести текстові значення в цифри і підсумувати результат, ви можете використовувати формулу INDEX і MATCH та функцію SUM. У наведеному прикладі формула в H5 має вигляд:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
де "код" - іменований діапазон K5: K9, а "значення" - іменований діапазон L5: L9.
Примітка: це формула масиву, і її потрібно вводити за допомогою control + shift + enter.
Пояснення
Серцем цієї формули є основна формула INDEX і MATCH, яка використовується для перекладу текстових значень у числа, як визначено в таблиці пошуку. Наприклад, щоб перекласти "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)))
Отже, здається, INDEX повинен повернути 5 результатів до SUM. Однак, якщо ви спробуєте це, функція INDEX поверне лише один результат SUM. Щоб INDEX повернув кілька результатів, нам потрібно скористатися досить незрозумілим трюком і обернути MATCH у N та IF, як це:
N(IF(1,MATCH(C5:G5,code,0)))
Це фактично змушує INDEX надавати більше одного значення функції SUM. Після запуску INDEX ми маємо:
=SUM((3,3,2,3,-1))
А функція SUM повертає суму елементів у масиві, 10. Щоб отримати хороший запис про цю поведінку, див. Цю цікаву статтю на веб-сайті EXCELXOR.
