Оператор SUMIFS с функцией массива

и заранее спасибо! Я пытаюсь суммировать значение столбца в Excel 2013 на основе ряда условий. Уравнение, которое у меня есть, выглядит так:

=SUM(SUMIFS((SQLExport!$AZ:$AZ),SQLExport!$AN:$AN,{"Review","Completed"},SQLExport!$AY:$AY,D4,SQLExport!$Y:$Y,">="&Snapshot!$B$2,SQLExport!$Y:$Y,"<="&Snapshot!$D$2))

На английском языке я хочу СУММИРОВАТЬ значения в столбце SQLExport! $ AZ: $ AZ на основе:

SQLExport! $ AN: столбец $ AN имеет значение «Обзор» ИЛИ «Завершено».

И SQLExport! $ AY: столбец $ AY равен D4

И SQLExport! $ Y: столбец $ Y больше или равен дате в снимке! $ B $ 2

И SQLExport! $ Y: столбец $ Y меньше или равен дате в снимке! $ D $ 2

После этого мне нужно иметь возможность разделить СУММЕСЛИ на результат другой ячейки. (Это не должно быть проблемой, если я могу заставить СУММЕСЛИ работать.)

Результаты, которые я получаю, неизменно выше, чем при вычислении вручную. Однако я не знаю, в чем дело. Любая помощь будет принята с благодарностью!

Спасибо!


excel excel-formula sumifs excel-2013
person Mark Mazich    schedule 24.09.2014    source источник
comment
Почему внешний SUM()? Разве =SUMIFS(SumRange, Range1, condition1, range2, condition2,...) не сделает то, что вам нужно?   -  person JNevill    schedule 24.09.2014
comment
К сожалению, без SUM () уравнение дает ответ значительно меньше, чем желаемый результат. С СУММ (СУММЕСЛИМН ()) результат - 39, только с СУММЕСЛИМН () результат - 14. Желаемый результат - 29. Пожалуйста, сообщите.   -  person Mark Mazich    schedule 24.09.2014
comment
Ваша формула мне кажется удовлетворительной - СУММ вам нужна потому, что при использовании {Review, Completed} в качестве одного из критериев СУММЕСЛИМН возвращает массив из двух значений, поэтому СУММ используется для их суммирования. Как вы думаете, почему результат неправильный, как вы рассчитываете 29? Если результат 14 без SUM, это означает, что вы получаете 14 за часть проверки (и предположительно 25 за завершенную).   -  person barry houdini    schedule 24.09.2014

Ответы (2)


arrow_upward
0
arrow_downward

Поскольку ваши условия немного сложнее, чем обычные условия типа SUMIFS, которые обычно встречаются, подумайте о переключении на =SumProduct(). Это могло бы иметь форму:

SumProduct(((SQLExport!$AN:$AN="Review") + (SQLExport!$AN:$AN="Completed")) * (SQLExport!$AY:$AY=D4) * (SQLExport!$Y:$Y >= Snapshot!$B$2) * (SQLExport!$Y:$Y <= Snapshot!$D$2) * (SQLExport!$AZ:$AZ) * 1)
person JNevill    schedule 24.09.2014
comment
Вы не можете использовать такое ИЛИ в формулах этого типа - ИЛИ возвращает единственный результат ИСТИНА / ЛОЖЬ, а не требуемый массив значений ИСТИНА / ЛОЖЬ - вы можете смоделировать ИЛИ, добавив условия типа (SQLExport!$AN:$AN="Review")+(SQLExport!$AN:$AN="Completed") - person barry houdini; 24.09.2014
comment
Сам не пробовал, но вы, наверное, правы. Я изменил формулу на более логическую арифметику-y вместо использования OR () - person JNevill; 24.09.2014
comment
Результатом этого выражения является #VALUE! где оценка отображается как СУММПРОИЗВ ({# ЗНАЧЕНИЕ!; 0; 0; 0; 0; 0; 0; 0; 0; 0; 7; 0; 0; 0; 0; 0; 0; 3; 0; 0 ; 0; 0}) - person Mark Mazich; 24.09.2014
comment
Вам не нужно * 1 в конце или * для умножения этого диапазона ... и эти функции фактически приведут к ошибке #VALUE, если у вас есть какой-либо текст в столбце AZ (например, заголовок) - с SUMPRODUCT лучше использовать определенный диапазон, например AZ2: AZ1000 ..... или заменить *(SQLExport!$AZ:$AZ)*1 на just,SQLExport!$AZ:$AZ - person barry houdini; 24.09.2014
comment
У меня был случайный амперсанд в формуле, который был заменен формулой sum(sumifs()) в вопросе. Я бы согласился с @barryhoudini об использовании определенного диапазона, если у ваших данных нет заголовков, и в этом случае эта формула будет прекрасной (хотя и немного медленной). Также *1 в этом примере лишнее, но я всегда просто оставляю его на тот случай, если я захочу переключить его со сложного sumifs на сложный countifs, удалив диапазон сумм (SQLExport!$AZ:$AZ) - person JNevill; 24.09.2014
comment
Хорошо, я удалил * 1, и текущее уравнение выглядит следующим образом после того, как Excel любезно предложил исправить опечатку где-то там: '= SUMPRODUCT ((SQLExport! $ AN: $ AN = 08-Отзыв клиента) + (SQLExport! $ AN: $ AN = 09-Завершено) * (SQLExport! $ AY: $ AY = D4) * (SQLExport! $ Y: $ Y ›= Снимок! $ B $ 2) * (SQLExport! $ Y: $ Y‹ = Snapshot! $ D $ 2), SQLExport! $ AZ: $ AZ) 'Использование диапазона вроде AZ2-AZ1000 по-прежнему вызывало ошибку #VALUE, но текущий код приводит к 75708 в качестве ответа. Чуть выше 29 хотелось бы. Я сам рассчитываю фильтрацию таблицы и соответствующим образом суммирую столбец. - person Mark Mazich; 24.09.2014
comment
Вам понадобится дополнительный набор круглых скобок вокруг двух условий, связанных с + (как в примере JNevill). - person barry houdini; 24.09.2014
comment
Я понятия не имею, как вы, ребята, это понимаете. Его путь выше меня превосходно. Так что спасибо тебе. Я пробовал: =SUMPRODUCT(((SQLExport!$AN:$AN="08-Customer review") + (SQLExport!$AN:$AN="09-Completed")) * (SQLExport!$AY:$AY=D4) * (SQLExport!$Y:$Y >= Snapshot!$B$2) * (SQLExport!$Y:$Y <= Snapshot!$D$2) * (SQLExport!AZ2:AZ50000) * 1), и это дает мне результат # N / A. В моей таблице есть заголовки. Пожалуйста, посоветуйте, я в вашей власти. - person Mark Mazich; 24.09.2014
comment
Барри, ваша последняя формула: =SUMPRODUCT(((SQLExport!$AN:$AN="08-Customer review")+(SQLExport!$AN:$AN="09-Completed"))*(SQLExport!$AY:$AY=D4)*(SQLExport!$‌​Y‌​:$Y>=Snapshot!$B$2)*(SQLExport!$Y:$Y<=Snapshot!$D$2),SQLExport!$AZ:$AZ) дает мне неверный результат Array1. - person Mark Mazich; 24.09.2014
comment
Если у вас есть заголовки, я бы порекомендовал: =SUMPRODUCT(((SQLExport!$AN2:$AN10000="08-Customer review")+(SQLExport!$AN2:$AN10000="09-Completed"))*(SQLExport!$AY2:$AY10000=D4)*(SQLExport!$‌​‌​Y‌2​:$Y10000>=Snapshot!$B$2)*(SQLExport!$Y2:$Y10000<=Snapshot!$D$2)*SQLExport!$AZ2:$AZ10000) Это работает так: каждый оператор условия (те, которые с =, <= или >= возвращают истину или ложь, то есть только 1 или 0. Эти 1 и 0 складываются и умножаются. в соответствии с формулой для каждой записи, затем умножается на то, что находится в столбце AZ, делает это для каждой строки и складывает результаты. - person JNevill; 24.09.2014
comment
Дж. Невилл, это выдает мне совершенно неверную подсказку с формулой. Что еще я могу вам предложить, чтобы помочь? Текущая формула =SUMPRODUCT(((SQLExport!$AN2:$AN10000="08-Customer review")+(SQLExport!$AN2:$AN10000="09-Completed"))*(SQLExport!$AY2:$AY10000=D4)*‌​(SQLExport!$‌​‌​Y‌2​:$Y10000>=Snapshot!$B$2)*(SQLExport!$Y2:$Y10000<=Snapshot!$D$‌​2)*SQLExport!$AZ:$AZ) Пожалуйста, сообщите! - person Mark Mazich; 24.09.2014
comment
Незначительное исправление вашей последней попытки: =SUMPRODUCT(((SQLExport!$AN2:$AN10000="08-Customer review")+(SQLExport!$AN2:$AN10000="09-Completed"))*(SQLExport!$AY2:$AY10000=D4)*‌​‌​(SQLExport!$‌​‌​Y‌2​:$Y10000>=Snapshot!$B$2)*(SQLExport!$Y2:$Y10000<=Snapshot!$‌​D$‌​2)*SQLExport!$AZ2:$AZ10000) Обратите внимание на изменение диапазона в столбце А-Я. При использовании sumproduct обязательно, чтобы все ваши диапазоны, которые вы тестируете и умножаете, имели одинаковую длину. - person JNevill; 24.09.2014
comment
=SUMPRODUCT(((SQLExport!$AN2:$AN100000="08-Customer review")+(SQLExport!$AN2:$AN100000="09-Completed"))*(SQLExport!$AY2:$AY100000=D4)*‌​‌​‌​(SQLExport!$‌​‌​Y‌2​:$Y100000>=Snapshot!$B$2)*(SQLExport!$Y2:$Y100000<=Snapshot‌​!$‌​D$‌​2)*SQLExport!$AZ2:$AZ100000) По-прежнему выдает сообщение о недопустимом выражении. - person Mark Mazich; 24.09.2014
comment
Надеюсь, я не поставил вас в тупик, ребята. Любые идеи относительно того, почему это было бы недопустимым выражением? Что я могу попросить вас помочь в устранении неполадок? - person Mark Mazich; 24.09.2014
comment
Иногда проблемы возникают, когда вы копируете формулы - особенно из комментариев - я скопировал обратно одну из этих формул, и некоторые данные, кажется, требуют повторного ввода - позвольте мне опубликовать ответ с предложенной формулой - я думаю, что копирование из ответа может быть лучше.... - person barry houdini; 24.09.2014

arrow_upward
0
arrow_downward

Попробуйте эту версию всего с 9 строками - давайте посмотрим, сможем ли мы заставить это работать, прежде чем увеличивать размеры диапазонов:

=SUMPRODUCT(((SQLExport!$AN2:$AN10="08-Customer review")+(SQLExport!$AN2:$AN10="09-Completed"))*(SQLExport!$AY2:$AY10=D4)*(SQLExport!$Y2:$Y10>=Snapshot!B2)*(SQLExport!$Y2:$Y10<=Snapshot!D2),SQLExport!$AZ2:$AZ10)

Это верная формула для меня, и она остается в силе, когда я копирую обратно на свой рабочий лист.

person barry houdini    schedule 24.09.2014
comment
Приносим извинения за задержку с ответом. =SUMPRODUCT(((SQLExport!$AN2:$AN99000="08-Customer review")+(SQLExport!$AN2:$AN99000="09-Completed"))*(SQLExport!$AY2:$AY99000=D4)*(SQLExport!$Y2:$Y99000>=Snapshot!B2)*(SQLExport!$Y2:$Y99000<=Snapshot!D2),SQLExport!$AZ2:$AZ99000) Работал как выражение, но в результате вернул 39. - person Mark Mazich; 25.09.2014
comment
Меня это не удивляет - как я сказал изначально, похоже, что с формулой СУММЕСЛИМН ничего плохого нет, и она также возвращает 39 - возможно, это правильный результат, а другой ваш метод расчета неверен? Возможно, вы сможете изолировать проблему - что, если вы используете COUNTIFS в соответствии с исходной формулой, но без диапазона сумм - сколько строк это даст вам? - person barry houdini; 25.09.2014