Выполните поиск каждой ячейки в диапазоне, используя Excel (для Mac)

Question

Учитывая строку в Excel с переменным числом столбцов, заполненных номерами элементов, я хотел бы сделать поиск в другой таблице, перечисляя стоимость для каждого элемента в заполненных столбцах, и суммировать общую стоимость. Мой Google-фу подвел меня, так как я не могу найти способ выполнить формулу Excel (в данном случае VLOOKUP) для каждой ячейки в диапазоне. В основном, если у меня есть следующие таблицы:

ItemCosts Item | Cost A | 10 B | 20 C | 40

заказы Order | 1 | A 2 | A | B 3 | B | C

Как рассчитать итоговую сумму в следующей таблице по номеру заказа?

итог заказа Order | Total 1 | 10 2 | 30 3 | 60

Мне удалось написать несколько формул, которые генерируют диапазон поиска в форме Orders!$B$2:$C$2, Я думал, что смогу использовать этот диапазон в качестве входных данных для некоторой формулы, чтобы сопоставить элементы в заказе с их стоимостью и суммировать их. Я надеялся, что смогу выполнить VLOOKUP для каждого элемента в диапазоне, и он мог бы вернуть диапазон из ItemCosts таблицу, которую я мог бы затем суммировать, но VLOOKUP принимает только одну ячейку в качестве значения поиска. Я чувствую, что упускаю что-то очевидное. Должен ли я просто сделать это в VBA? В этом ответе упоминается, что VLOOKUP может взять массив, а затем вернуть массив, но я не смог воспроизвести его в Excel 2016 для Mac. Он просто возвращает первое совпадение как одно значение.

РЕДАКТИРОВАТЬ: На основании комментариев кажется, что ненормализованная таблица заказов может быть проблемой. Но даже если это выглядит так, мне не ясно, как выполнить поиск стоимости каждого элемента, чтобы я мог рассчитать общую сумму.

заказы Order | Item 1 | A 2 | A 2 | B 3 | B 3 | C

Answer 1

Есть два способа получения требуемого результата с помощью "стандартных формул", которые я собираюсь предложить. Я предполагаю, что, возможно, есть функция массива, которая могла бы решить эту проблему без вспомогательных столбцов / листов, но они, как правило, сложны и сложны в обслуживании.

Для нормализованной версии вы можете сделать следующее:

1) Добавьте столбец значений в таблицу заказов:

'Column of Cost in ItemCost Table' = ColumnCost
'Column of Item in ItemCost Table' = ColumnItem

Order | Item | Value
1     | A    |=index(ColumnCost, match(ItemCell, ColumnItem, 0))
2     | A    |...
2     | B    |...
3     | B    |...
3     | C    |...

2) Используйте формулу sumif для суммирования по столбцу Value в таблице заказов, обусловленной номером заказа.

Для ненормализованной версии вместо вспомогательного столбца вы можете использовать вспомогательный лист той же формы и размеров, что и лист заказов.

Использование той же структуры формулы индекса, что и в приведенном выше примере, но вместо использования ItemCell в листе заказа. Это будет выглядеть следующим образом:

заказы

Order |
1     | A
2     | A | B
3     | B | C

Заказы помощника

Order |
1     | 10
2     | 10 | 20
3     | 20 | 40

Для сводной таблицы вы просто суммируете по соответствующему столбцу.

Вы можете найти пример этого здесь: https://ufile.io/rbxf1