Обобщение
За да преведете текстовите стойности в числа и да сумирате резултата, можете да използвате формула 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.
