☰

Расчет общего объема продаж с использованием нескольких критериев в двух таблицах

tag-icon • tag-icon microsoft-excel worksheet-function
Расчет общего объема продаж с использованием нескольких критериев в двух таблицах

В настоящее время я работаю сТаблица 1и пытаясь подсчитать общий объем продаж[Столбец C]продуктов с тем жеИДЕНТИФИКАТОРи сопоставление единиц запаса и единиц продаж вТаблица 2. Я попытался использовать формулу СУММЕСЛИМН следующим образом:

=SUMIFS(Table2!E:E, Table2!A:A, Table1!A1, Table2!C:C, Table2!D:D)

Однако, похоже, это не дает мне желаемого результата. Может ли кто-нибудь помочь мне с этим? Я был бы очень признателен за вашу помощь. Спасибо!

Таблица 1 (Желаемые результаты):

Таблица 1

Таблица 2:

Таблица 2

решение1

Если я правильно понимаю, то SUMIFS()это не та функция, которая будет работать для желаемого результата, вместо этого используйте следующее, как показано на скриншоте:

введите описание изображения здесь

• Формула, используемая в ячейке C2и Sheet Named Table1заполняющая ее:

=LET(
     _extract, FILTER(Table2!C$2:E$19,(Table2!A$2:A$19=Table1!A2)*(Table1!B2=Table2!B$2:B$19),""),
     SUM(--(INDEX(_extract,,1)=INDEX(_extract,,2))*TAKE(_extract,,-1)))
  • Используя FILTER()функцию извлечения диапазона данных с заголовками как Stock Unit, Sales Unitи Salesна основе IDиName
  • Далее, используя SUM()функцию, выполняем булеву операцию для сравнения массивов Sales Unitи , Stock Unitизвлеченных из вышеприведенного, вычисляем сумму произведений соответствующих массивов.

Кроме того, если вы используете MS365, то вы можете вывести весь вывод с помощью одной единственной формулы, поэтому нет необходимости заполнять здесь:

=MAP(A2:A7,B2:B7,LAMBDA(x,y,
  LET(z, FILTER(Table2!C:E,(Table2!A:A=x)*(Table2!B:B=y),""), 
  SUM(--(INDEX(z,,1)=INDEX(z,,2))*TAKE(z,,-1)))))
  • Использование MAP()функции для итерации значений каждой ячейки для выполнения пользовательских LAMBDA()вычислений.
  • В рамках LAMBDA()использования a LET()для передачи FILTER()функции для извлечения трех желаемых столбцов на основе IDиName
  • Далее, суммируем произведения соответствующих массивов, сравниваем с Stock Unit--> INDEX(z,,1)с Sales Unit--> INDEX(z,,2), что возвращает TRUEи , FALSEа затем умножаем то же самое на , Salesчтобы получить желаемый результат после суммирования.

Одним из простых способов может быть использование SUMIFS()Column Sales Unit, взятого как Criteria Rangeи Stock UnitsColumn as Criteria, однако, это зависит от ваших предпочтений:

введите описание изображения здесь

=SUMPRODUCT(
    SUMIFS(
        Table2!E:E,
        Table2!A:A, Table1!A2,
        Table2!B:B, Table1!B2,
        Table2!D:D, UNIQUE(
            Table2!C:C
        )
    )
)

Еще один альтернативный способ, но он рекомендуется, так как он замедлит работу Excel.

=SUM(FILTER(Table2!E:E,(IFNA(XMATCH(Table2!D:D,UNIQUE(Table2!C:C)),0))*(A2=Table2!A:A)*(Table2!B:B=Table1!B2),0))

Связанный контент