#google-sheets #spreadsheet
Вопрос:
У меня есть рабочая тетрадь Google Sheets с 2 листами.
На листе 1 перечислено около 1000 наименований (компонентов). Список может вырасти. 
(игнорируйте столбцы от E до K в приведенном выше примере)
На листе 2 перечислены некоторые продукты (на данный момент их около 200, но список может вырасти). Каждый продукт может содержать до 12 компонентов. Они перечислены в столбцах C-N (все эти столбцы имеют проверку данных, поэтому их необходимо выбрать из списка 1000 компонентов листа 1). Столбец R содержит количество единиц товара. 
На листе 1, рядом с каждым компонентом, я хочу рассчитать количество единиц компонента, используемых во всех продуктах. Конечно countif , будут учитываться только экземпляры конкретного компонента Sheet 1!C2:N200 . Аналогично sumproduct , это не соответствует требованиям, потому что 1. C-N не являются числовыми, а 2. C-N не являются фиксированными.
Существует ли простой способ рассчитать количество единиц для каждого компонента? (без использования VBScript/Javascript и без копирования-вставки списка компонентов транспонирования на лист 2)
Я думаю о каком-то причудливом способе использования sumif или sumifs , возможно, здесь есть ответ, но еще не понял.
Комментарии:
1. Почему
=SUMPRODUCT(--(Sheet2!C:N=A1))не работает?A1быть очевидным заполнителем для вашей ячейки на 1-м листе.2. @JvdV Не уверен, что понимаю формулу. Просто чтобы уточнить, что мне нужно: мне нужна формула для проверки всех строк на наличие значений от C до N для компонента (A1 в вашем примере), и если компонент найден для строки, добавьте значение, указанное в R, к сумме и сделайте это для всех строк, в которых найдено значение A1.
3. можете ли вы поделиться упрощенной электронной таблицей?
4. Правильно @OC2PS, так
=SUMPRODUCT((Sheet2!C:N=A1)*Sheet2!R:R)что лучше? В любом случае, у тебя есть ответ, который я вижу =)
Ответ №1:
В ячейку B2 листа 1. введите эту формулу:
=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A,QUERY(SPLIT(FLATTEN(Sheet2!C2:Namp;"|"amp;Sheet2!R2:R),"|",0,0),"select Col1,SUM(Col2) group by Col1"),2,0)))
если вы хотите вернуть 0 вместо пробелов для неиспользуемых компонентов, это небольшое изменение должно сделать это.
=ARRAYFORMULA(IF(A2:A="",,IFERROR(VLOOKUP(A2:A,QUERY(SPLIT(FLATTEN(Sheet2!C2:Namp;"|"amp;Sheet2!R2:R),"|",0,0),"select Col1,SUM(Col2) group by Col1"),2,0),0)))
Комментарии:
1. @OC2PS основываясь на вашей репутации, я предполагаю , что вы знаете, как переводить, если вы находитесь в европейском регионе, который использует ; вместо разделителей параметров…
2. Обратите также внимание, что эта формула предназначена для действия на всю колонку B листа 1. Вам нужно будет удалить любые другие формулы, которые находились ниже ячейки B2 и, следовательно, «мешали» выводу этой формулы.
3. Это сработало! Ты потрясающая. Однако я не знаком с формулами массива. Для меня и для других, кто приходит на эту страницу, не могли бы вы расширить свой ответ, объяснив компоненты формулы и то, что происходит? P.S. Я использую вторую версию вашей формулы с несколькими небольшими изменениями, относящимися к моему листу, например, адреса ячеек и т. Д.
4. @OC2PS, конечно. вот примерный лист, созданный специально для этой проблемы. Я изложил большинство шагов на вкладке под названием MK.Demo. Надеюсь, это поможет! docs.google.com/spreadsheets/d/…
5. @MattKing, я ценю демонстрацию электронных таблиц вместо одной из OC2PS!
Ответ №2:
Дополнительным способом является создание базы данных
=ARRAYFORMULA(SPLIT(FLATTEN(Sheet2!A2:Aamp;"|"amp;Sheet2!C2:Namp;"|"amp;Sheet2!R2:R),"|",,false))
а затем вычислить с помощью сводной таблицы, добавленной в
https://docs.google.com/spreadsheets/d/1Lokoms22tuqU53TlhAcdqaldQe_H7Pq8Jah2kBBYo7c/edit#gid=1233865099
