sumif(s), суммапроизведение для отношения многие ко многим

Question

Я ищу формулу/функцию, которая позволит мне по сути СУММЕСЛИ(и) по всей спецификации. Я знаю отношение детали к FG (Part, FG) и знаю, сколько FG мне нужно (FG, Qty).

Как мне рассчитать количество необходимых мне деталей (Деталь, Кол-во) за один шаг? В настоящее время мне приходится СУММЕСЛИ для каждой детали/FG-отношения (Деталь, FG, Кол-во), а затем СУММЕСЛИ для каждой детали (Деталь, Кол-во).

Я бы хотел сделать это в один этап, если это возможно.

Answer 1

Для решения этой задачи можно использовать формулу массива (также известную как формула CTRL+ Shift+ Enter).

Первое, что нужно сделать, — это изменить вторую таблицу так, чтобы в ней появился отдельный столбец с датами. (Обычно лучше избегать размещения информации в заголовках столбцов.)

Я использовалТаблицы(Вставка > Таблица), чтобы было легче читать формулу.

Теперь в ячейке I2 мы можем рассчитать количество для Детали X на 11.07.14.

{=SUM(
   SUMIFS(Table2[Qty]
        ,Table2[Date]
        ,I$1
        ,Table2[FG]
        ,IF(Table1[Part]=$H2,Table1[FG])
        )
    )}

Перед вводом этой формулы удалите фигурные {}скобки и обязательно нажимайте CTRL+ Shift+ Enterпри вводе этой формулы.

Скопируйте и вставьте эту формулу в ячейки I3, J2 и J3, чтобы получить остальные значения.

---

Нетабличная версия той же формулы

{=SUM(
   SUMIFS($F$2:$F$9
        ,$E$2:$E$9
        ,I$1
        ,$D$2:$D$9
        ,IF($A$2:$A$7=$H2,$B2:$B$7)
        )
    )}