2-мерный vlookup + match, извлечение из двух разных наборов данных?

Question

вот хитрый для меня, надеюсь, не для вас.

У меня есть таблица данных на одном листе, в которой перечислены номера продуктов в столбце и материалы, использованные для создания указанного продукта, в верхнем ряду с соответствующими количествами, где они встречаются.

На втором листе у меня есть ежедневный список, который состоит из того, какие продукты были произведены с соответствующим количеством, произведенным в другом столбце.

На третьем листе я хотел бы видеть итоги того, какие материалы использовались в тот день, в зависимости от того, какие продукты были произведены.

Ex. Ежедневный лист показывает из списка, что было произведено 80 единиц продукта 1. Требуется 1 единица материала х, чтобы произвести продукт. Поэтому я хотел бы, чтобы формула показывала мне, что я использовал 80 единиц материала х, отображаемых под заголовком этого столбца.

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

Спасибо!

Answer 1

Вот стратегия, которая использует комбинацию Tables Excel и Pivot Table для отчета о результатах

(Разработано в Excel 2010)

• Сконфигурируйте данные об использовании вашего продукта / материала в виде Table: я назвал это tblQuantities
• Сконфигурируйте ваши ежедневные данные производства в виде Table: я назвал это tblProduction
• Расширьте tblProduction с помощью формул, чтобы получить расход материала на один продукт для идентификатора материала
• Установите tblProduction чтобы автоматически расширять формулы при добавлении новых строк
• Добавьте Pivot Table чтобы получить суммированные данные ежедневного потребления

tblQuantities

tblProduction

Формула для столбцов tblProduction Material

=INDEX(tblQuantities,MATCH([@Product],tblQuantities[ProductID],0),
 MATCH(INDEX(tblProduction[#Headers],COLUMN()),tblQuantities[#Headers],0))
 *[@Quant]

Настройка Pivot

Сводный результат

Answer 2

Для решения я предполагаю следующую, слегка упрощенную настройку:

Чтобы достичь этого результата,

сделайте следующее:

• Добавьте новый столбец рядом с вашей таблицей с количествами (здесь это столбец E). Здесь вы суммируете количество проданных товаров. Я использовал эту формулу:

  = СУММЕСЛИМН (I:, Н: Н, А4)

  , В зависимости от того, хотите ли вы продажи на определенную дату, вы, конечно, можете изменить эту формулу, так как в данный момент она суммирует продукты за все даты.
• Добавить таблицу со всеми материалами
• Подсчитайте сумму по следующей формуле

  = СУММПРОИЗВ (($ B $ 4: $ D $ 6 * $ E $ 4: $ E $ 6)* ($ B $ 3: $ D $ 3 = $ К4))