Вопрос или проблема
Вот моя формула:
=SUMIFS('Февраль 16 Данные Таблица'!$AD:$AD,'Февраль 16 Данные Таблица'!$AI:$AI,"N",'Февраль 16 Данные Таблица'!$AJ:$AJ,"N",'Февраль 16 Данные Таблица'!$AK:$AK,"N",'Февраль 16 Данные Таблица'!$AG:$AG,"Y",'Февраль 16 Данные Таблица'!$AN:$AN,">=1/1/2016",'Февраль 16 Данные Таблица'!$AC:$AC,{"A","L","S","R"})
Я ввел формулу как массивную формулу с помощью CTRL + SHIFT + ENTER.
В настоящее время SUMIFS возвращает только сумму значений, где “A” истинно для столбца AC. Что мне нужно сделать, чтобы исправить формулу?
Если один из ваших аргументов критериев в функции SUMIFS является массивной константой, функция вернет массив значений, которые тестируют истинность для каждого элемента. Соответственно, чтобы суммировать этот массив, вам нужно обернуть SUMIFS в функцию SUM.
Итак, предполагая, что ваша формула в остальном верна:
=SUM(SUMIFS('Февраль 16 Данные Таблица'!$AD:$AD,'Февраль 16 Данные Таблица'!$AI:$AI,"N",'Февраль 16 Данные Таблица'!$AJ:$AJ,"N",'Февраль 16 Данные Таблица'!$AK:$AK,"N",'Февраль 16 Данные Таблица'!$AG:$AG,"Y",'Февраль 16 Данные Таблица'!$AN:$AN,">=1/1/2016",'Февраль 16 Данные Таблица'!$AC:$AC,{"A","L","S","R"}))
должно сработать
Формула SUMIFS не принимает массив сама по себе. Вы можете разбить ее на =SUM и =IF выражения. Например:
=SUM(IF('Февраль 16 Данные Таблица'!$AI:$AI,"N",IF('Февраль 16 Данные Таблица'!$AJ:$AJ,"N" .... ,'Февраль 16 Данные Таблица'!$AD:$AD)))))
Замените … на ваш другой код. Это лишь рекомендация.
Ответ или решение
Чтобы использовать массив в качестве условия в формуле SUMIFS в Excel, важно понимать, что структура функции SUMIFS не поддерживает прямую работу с массивами. Хотя вы можете использовать формулу с массивом, нужно помнить, что результат её применения будет массив значений, и для правильной агрегации этих значений необходимо использовать дополнительные функции, такие как SUM или IF.
Краткое объяснение проблемы
Согласно вашему примеру формулы:
=SUMIFS('February 16 Data Table'!$AD:$AD,'February 16 Data Table'!$AI:$AI,"N",'February 16 Data Table'!$AJ:$AJ,"N",'February 16 Data Table'!$AK:$AK,"N",'February 16 Data Table'!$AG:$AG,"Y",'February 16 Data Table'!$AN:$AN,">=1/1/2016",'February 16 Data Table'!$AC:$AC,{"A","L","S","R"})можно заметить, что вы пытаетесь использовать массив {"A","L","S","R"} в качестве условия для столбца AC. Однако формула SUMIFS не может принимать массивы в качестве критериев напрямую. В качестве решения вы можете использовать функции SUM и IF, чтобы обработать массивы.
Решение с помощью SUM и IF
Вот как можно переписать вашу формулу, чтобы корректно обработать массив:
=SUM(IF(('February 16 Data Table'!$AI:$AI="N")*('February 16 Data Table'!$AJ:$AJ="N")*('February 16 Data Table'!$AK:$AK="N")*('February 16 Data Table'!$AG:$AG="Y")*('February 16 Data Table'!$AN:$AN>=DATE(2016,1,1))*('February 16 Data Table'!$AC:$AC={"A","L","S","R"}), 'February 16 Data Table'!$AD:$AD, 0))Объяснение формулы:
-
Условие в IF: Внутри функции IF мы проверяем каждое условие, которое вы указали. Все условия объединяются с помощью умножения (
*), что равно логическому "И". Если все условия истинны, возвращается соответствующее значение из столбца AD, в противном случае 0. -
Массив в условии: Использование массива в условии
('February 16 Data Table'!$AC:$AC={"A","L","S","R"})позволяет проверить, если значение в столбце AC соответствует хотя бы одному из указанных значений. В таком случае формула возвращает значения из столбца AD. -
Оборачиваем в SUM: Внутренняя формула IF возвращает массив, который нужно суммировать, поэтому мы используем функцию SUM, чтобы получить общий результат.
-
Ошибка окружения: Чтобы формула с массивом корректно работала, вам необходимо ввести её в режиме массива, т.е. нажать
CTRL + SHIFT + ENTER.
Заключение
Использование массива в условиях для функции SUMIFS требует дополнительных шагов. Применяя описанный выше метод с использованием SUM и IF, вы сможете эффективно обрабатывать массивные условия и получать желаемые результаты. Если требуется провести дополнительные уточнения или у вас возникнут вопросы по другим аспектам Excel, пожалуйста, не стесняйтесь обращаться за помощью.