Суммарное произведение с поисковым массивом
На одном листе Total Value у меня список сборок в A2:A21. В заголовках столбцов B1:BA1 у меня перечислены 52 недели года. Мне нужно заполнить эту матрицу общей стоимостью компонентов, используемых для каждой сборки каждую неделю.
Для каждой сборки 1,2...20 существует лист с соответствующим именем (т.е.,'1','2'...'20'), который содержит в столбце A (N!A2:...) список компонентов (ID) и в столбце B ( N!B1:...) количество этого компонента, необходимого для сборки.
Кроме того, у меня есть еще один лист CV, который содержит таблицу значений всех компонентов по неделям — CV!A2:A200 содержит идентификаторы компонентов и CV!B1:BA1 перечисляет недели.
Я пытаюсь придумать динамическую формулу для каждого пересечения «Сборка/Неделя» в Total Value, которая будет СУММУ ПРОИЗВОДИТЬ количество каждого необходимого компонента (из соответствующего листа сборки) на значение этих компонентов в данную неделю, указанное в CV.
Если бы была только одна сборка и CV содержала бы только компоненты, необходимые для этой сборки, это был бы простой СУММПРОИЗВ. Массив 1 будет содержать количество компонентов для сборки, а массив 2 будет содержать значения компонентов на этой неделе. В моем случае CV — это длинный список в произвольном порядке. Мне нужен второй массив в SUMPRODUCT для точного поиска значений компонентов в массиве 1 за эту неделю.
Я даже не знаю, как начать с этого. Есть идеи?
Спасибо, что посмотрели это. Я добавил иллюстративные изображения. Что касается расчетов, в Total Value для сборки 1, недели 1 мы суммируем количество компонентов во второй таблице на значения соответствующих компонентов для недели 1, указанные в таблице CV. Я ограничил количество строк/столбцов в изображениях, чтобы сделать их видимыми, но на практике CV будет состоять из сотен компонентов.
Ответы 1
Привет, попробуйте эту формулу в ячейке «Общая стоимость»! B2:
=СУММПРОИЗВ(IFNA(ВПР(ДВССЫЛ("'"&$A2&"'!A2:A200"),CV!$A$1:$BA$200,B$1+1,0),0),ДВССЫЛ("'" &$A2&"'!B2:B200"))
Должно работать, если я правильно понял
Спасибо за это. Я сделал несколько тестовых вычислений вручную, и это возвращает разные числа. Я думаю, что значения компонентов в массиве, возвращаемом функцией ВПР, не совпадают с количествами компонентов во втором массиве.
Привет, спасибо за тестирование, вы можете сделать это еще раз? : D забыл сделать это точное совпадение с ВПР (я отредактировал его в исходном ответе). Я протестировал его в своей версии и первых нескольких результатах, и все выглядит нормально, но, пожалуйста, проверьте его еще раз: =СУММПРОИЗВ(ЕСЛИНА(ВПР(ДВССЫЛ("'"& $A2&"'!A2:A200"),CV!$A$1:$BA$200,B$1+1,0),0),ДВССЫЛ("'"&$A2&"'!B2:B200 "))
Я попытался воспроизвести структуру ваших таблиц, и она не очень понятна. Не могли бы вы прикрепить изображение структуры ваших таблиц и вручную показать один расчет, чтобы проиллюстрировать это для нас.