Вопрос или проблема
=INDEX(‘JO with Comp’!$G$31645:$G$200000,MATCH(1,($A2911=’JO with Comp’!$K$31645:$K$200000)*((VLOOKUP($A2911,’JO with Comp’!$K$31645:$R$200000,8,0)=’JO with Comp’!$R$31645:$R$200000)),0))
Мне нужна помощь в решении формулы выше. Здесь я использую VLOOKUP, INDEX и MATCH, чтобы получить общее количество. Мне удалось получить количество для первой строки (qty = 30), но я не могу получить общую сумму (qty = 252, из колонки G), даже используя формулу SUMIF. Для справки, мне нужно получить общее FG Planned Qty (колонка G) для конкретного Кода компонента HR103(L)-PC (колонка A [первая картинка] и колонка K [вторая картинка]) из 2 различных файлов), учитывая Дату поста по месяцам (с апреля по ноябрь), Статус работы (ЗАВЕРШЕН), Тип работы (СТАНДАРТНОЕ КОЛИЧЕСТВО за вычетом КОЛИЧЕСТВА РАЗБОРКИ) и также код FG. Пример данных, которые у меня есть, приведен ниже.
Ответ или решение
Для того чтобы рассчитать общую сумму количества с несколькими критериями в Excel, можно использовать формулу, сочетающую функции SUMIFS или агрегатные функции, такие как SUMPRODUCT. При этом важно учитывать, что для достижения нужного результата необходимо задать точные критерии, такие как код компонента, статус работы и тип работы.
Пример формулы с использованием SUMIFS
Исходя из вашего запроса, вам нужно суммировать значения из столбца G (FG Planned Qty) для компонента с кодом HR103(L)-PC и при выполнение следующих условий:
- Постоянная дата (например, с апреля по ноябрь).
- Статус задания — CLOSED.
- Тип работы — STANDARD QTY (минус DISASSEMBLY QTY).
- FG код — сопоставляется с вектором данных.
Предположим, что ваш диапазон данных в таблице "JO with Comp" расположен таким образом:
- Столбец G – FG Planned Qty
- Столбец K – Код компонента
- Столбец L (или любой другой) – Статус задания
- Столбец M (или любой другой) – Тип работы
- Столбец N (или любой другой) – Дата
Вот пример формулы, которая может вам помочь:
=SUMIFS('JO with Comp'!$G$31645:$G$200000, 'JO with Comp'!$K$31645:$K$200000, "HR103(L)-PC", 'JO with Comp'!$L$31645:$L$200000, "CLOSED", 'JO with Comp'!$M$31645:$M$200000, "STANDARD QTY", 'JO with Comp'!$N$31645:$N$200000, ">="&DATE(2023,4,1), 'JO with Comp'!$N$31645:$N$200000, "<="&DATE(2023,11,30))Подробное объяснение формулы
-
SUMIFS — это функция, позволяющая суммировать значения с несколькими критериями. Она принимает аргументы в следующем порядке:
- диапазон суммируемых значений,
- диапазоны условий,
- условия.
-
Диапазоны:
'JO with Comp'!$G$31645:$G$200000: диапазон, который мы хотим суммировать.'JO with Comp'!$K$31645:$K$200000: диапазон для проверки кода компонента.'JO with Comp'!$L$31645:$L$200000: диапазон для проверки статуса задания.'JO with Comp'!$M$31645:$M$200000: диапазон для проверки типа работы.'JO with Comp'!$N$31645:$N$200000: диапазон для проверки даты.
-
Условия:
"HR103(L)-PC"— код компонента, который мы ищем."CLOSED"— статус задания."STANDARD QTY"— тип работы.">="&DATE(2023,4,1)и"<="&DATE(2023,11,30)— диапазон дат от апреля до ноября 2023 года.
Альтернативный способ — использование SUMPRODUCT
Если вам нужна более сложная логика, вы также можете использовать функцию SUMPRODUCT, которая позволяет создавать более настроенные условия. Формула может выглядеть следующим образом:
=SUMPRODUCT(('JO with Comp'!$K$31645:$K$200000="HR103(L)-PC")*('JO with Comp'!$G$31645:$G$200000)*('JO with Comp'!$L$31645:$L$200000="CLOSED")*('JO with Comp'!$M$31645:$M$200000="STANDARD QTY")*('JO with Comp'!$N$31645:$N$200000>=DATE(2023,4,1))*('JO with Comp'!$N$31645:$N$200000<=DATE(2023,11,30)))Заключение
Использование формул SUMIFS и SUMPRODUCT позволяет гибко управлять данными в Excel и осуществлять сложные математические операции с учетом множества критериев. Важно тщательно проверять корректность диапазонов и условий, чтобы избежать ошибок в расчетах. Эти практики будут полезны для дальнейшего анализа данных и принятия обоснованных решений в вашем проекте.