Домашняя страница Undo Do Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Статьи об Excel

МЕНЮ САЙТА
  • 1
  • 2
  • 3

КАТЕГОРИИ РАЗДЕЛА

ОПРОСЫ
Какой версией Excel Вы пользуетесь?
Всего ответов: 56881
Главная » Статьи » Эффективная работа в Excel » Приёмы работы с формулами

Суммирование по нескольким критериям

Предположим что у Вас есть вот такой отчёт по продажам торговых представителей:



Из него Вам необходимо узнать сколько карандашей продал торговый представитель Иванов в январе.



ПРОБЛЕМА: Как суммировать данные по нескольким критериям??

РЕШЕНИЕ: Способ 1:
Code
=БДСУММ(A1:G16;F1;I1:K2)

В английской версии:
Code
=DSUM(A1:G16,F1,I1:K2)

КАК ЭТО РАБОТАЕТ:
 


Из указанной нами базы данных A1:G16 функция БДСУММ извлекает и суммирует данные столбца Количество (аргумент "Поле" = F1) по заданным в ячейках I1:K2 (Продавец = Иванов; Продукция = Карандаши; Месяц = Январь) критериям.



МИНУСЫ
: Список критериев должен быть на листе.

ПРИМЕЧАНИЯ: Количество критериев суммирования ограничено оперативной памятью.

ОБЛАСТЬ ПРИМЕНЕНИЯ
: Любая версия Excel

Способ 2:
Code
=СУММПРОИЗВ((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)

В английской версии:
Code
=SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)


КАК ЭТО РАБОТАЕТ:

Функция СУММПРОИЗВ формирует массивы из значений ИСТИНА и ЛОЖЬ, согласно выбранным критериям, в памяти Excel.



Если-бы вычисления производились в ячейках листа (для наглядности я всю работу формулы продемонстрирую так, как-будто вычисления происходят на листе, а не в памяти), то массивы выглядели бы так:



Очевидно что если например, D2=Карандаши, то значение будет равно ИСТИНА, а если D3=Папки, то ЛОЖЬ (так как критерием отбора товара в нашем примере является значение Карандаши).



Зная о том что значение ИСТИНА всегда равно 1, а ЛОЖЬ всегда равно 0 мы продолжаем работать с массивами как с числами 0 и 1.
Перемножив полученные значения массивов между собой последовательно, мы получим ОДИН массив из нолей и единиц. Там где выполнялись все три критерия отбора, (ИВАНОВ, КАРАНДАШИ, ЯНВАРЬ) т.е. все условия принимали значения ИСТИНА получаем 1 (1*1*1 = 1), если же хотя-бы одно условие не выполнялось - получим 0 (1*1*0 = 0 ; 1*0*1 = 0 ; 0*1*1 = 0 ).

Теперь осталось только умножить полученный массив на массив содержащий данные, которые нам необходимо в итоге просуммировать ( диапазон F2:F16) и собственно, просуммировать то что на 0 не умножилось.




Теперь сравните полученные при помощи формулы и при пошаговом вычислении на листе массивы (выделены красным).


 
Думаю всё понятно :)

МИНУСЫ: СУММПРОИЗВ - "тяжёлая" формула массива. При вычислениях на больших диапазонах данных заметно увеличивается время пересчёта.

ПРИМЕЧАНИЯ: Количество обрабатываемых массивов ограничено 255.

ОБЛАСТЬ ПРИМЕНЕНИЯ: Любая версия Excel

Способ 3: Формула массива
Code
=СУММ(ЕСЛИ((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2);F2:F16))

В английской версии:
Code
=SUM(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))


КАК ЭТО РАБОТАЕТ:  Точно так же как и Способ №2. Есть только два отличия - данная формула вводится нажатием Ctrl+Shift+Enter, а не просто нажатием Enter и массив 0-й и 1-ц не умножается на диапазон суммирования, а отбирается с помощью функции ЕСЛИ.

МИНУСЫ: Формулы массива при вычислениях на больших диапазонах данных заметно увеличивают время пересчёта.

ПРИМЕЧАНИЯ: Количество обрабатываемых массивов ограничено 255.
 
ОБЛАСТЬ ПРИМЕНЕНИЯ
: Любая версия Excel

Способ 4:
Code
=СУММЕСЛИМН(F2:F16;B2:B16;I2;D2:D16;J2;A2:A16;K2)

В английской версии:
Code
=SUMIFS(F2:F16,B2:B16,I2,D2:D16,J2,A2:A16,K2)


КАК ЭТО РАБОТАЕТ:
Функция СУММЕСЛИМН требует обязательно указать диапазон суммирования (в нашем случае F2:F16) и хотя бы одну пару Диапазон/Условие.
По сути эта функция "один в один" повторяет действие функции СУММПРОИЗВ в нашем примере, но не является формулой массива.



МИНУСЫ: нет.
 
ОБЛАСТЬ ПРИМЕНЕНИЯ
: Начиная с версии Excel 2007.

ПРИМЕЧАНИЯ: Количество пар диапазон/критерий ограничено 127



xls     XLSX

Категория: Приёмы работы с формулами | Добавил: Serge_007 (20.03.2011)
Просмотров: 93859 | Комментарии: 34 | Теги: формулы эксель, Суммирование по нескольким критерия, сумм, формулы Excel | Рейтинг: 5.0/10


Всего комментариев: 341 2 »
0   Спам
1    Bagir   (07.02.2012 13:45) [ Материал]
   Спасибо, очень показательный пример!

0   Спам
2    Пытливый   (21.04.2012 13:23) [ Материал]
   Да спасибо автору, всё разжевано до мелочей

0   Спам
3    ALARMus   (07.08.2012 16:41) [ Материал]
   А в данном конкретном примере для функции СУММПРОИЗВ Примечание не относиться ("Количество обрабатываемых массивов ограничено 255.") ?
Массив ведь всего один.

0   Спам
4    Evgeniy   (17.07.2013 10:05) [ Материал]
   А если один из критериев пустой, ексель ищет нулевые значения, допустим необходимо по одному из критериев найти любое значение, не меняя формулы, есть ли какой-то вариант задать критерий так чтобы есель воспринимал его как любое значение, или нужен макрос, и поможет ли он?

0   Спам
5    Serge_007   (17.07.2013 10:58) [ Материал]
   Evgeniy, Ваш вопрос не имеет отношения к этой статье

0   Спам
6    Denisko   (10.09.2013 10:05) [ Материал]
   Натолкнулся на одну неприятную особенность при использовании данной формулы - если база данных находится в другом файле, то она обязательно должна быть открыта, иначе формула не будет считать. У меня Офис 2007. Никто не в курсе, в 2010 или 2013 офисе данная оплошность исправлена?

0   Спам
7    Serge_007   (10.09.2013 12:44) [ Материал]
   В статье приведены четыре формулы. Про какую из них Вы пишете?

Например формула
Код
=СУММПРОИЗВ((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)
работает с закрытыми книгами

ЗЫ Это не оплошность. Так устроены функции листа. Независимо от версий Excel

0   Спам
8    Denisko   (10.09.2013 16:15) [ Материал]
   Упс, простите, писал, о чем думал - а думал я о "суммеслимн". =) "суммпроизв" слишком тяжелая. Стоит целерон 2,1гГц и 512мб оперативки - страшно повесить систему.

0   Спам
9    Serge_007   (10.09.2013 17:27) [ Материал]
   Разница в быстродействии между СУММЕСЛИМН() и СУММПРОИЗВ() небольшая, обе формулы довольно "тяжелые", хотя СУММЕСЛИМН() немного быстрее. В любом случае, если исходных данных много - лучше пользоваться сводной таблицей. Это в разы быстрее и удобнее

0   Спам
10    urlchik   (08.02.2014 14:32) [ Материал]
   А как сделать количество, если количество больше 50, за январь - если столбец с месяцем имеет формат даты ДД,ММ,ГГГГ?

0   Спам
11    Serge_007   (08.02.2014 15:21) [ Материал]
   В ячейке К2 должна быть значение даты в том же формате, как и в столбце А

0   Спам
12    MaestroSVK   (02.04.2014 06:19) [ Материал]
   Добрый день, подскажите, пожалуйста, если в одном из диапазонов условий (новый столбец E) будут значения дат в таком формате 04.05.2014, 03.01.2013 и т.п.
Одним из условий будет меньше или равно, допустим 03.01.2014, которое будет в ячейке N2. Как будет выглядеть формула?
В моем случае это не получается сделать: выдает ошибку.
Формула =СУММЕСЛИМН(G2:G16;B2:B16;J2;D2:D16;K2;A2:A16;L2;E2:E16;<=N2).

0   Спам
13    Serge_007   (02.04.2014 07:42) [ Материал]
   Условие <= пишется в кавычках

0   Спам
14    MaestroSVK   (02.04.2014 07:58) [ Материал]
   Написал так:
Код
=СУММЕСЛИМН(G2:G16;B2:B16;J2;D2:D16;K2;A2:A16;L2;E2:E16;"<=N2")

Выводит ноль.

0  
15    MaestroSVK   (02.04.2014 08:53) [ Материал]
   Т.е. СУММЕСЛИМН не работает с условиями, кроме равно, в которых идет ссылка на ячейку?

0   Спам
16    MaestroSVK   (02.04.2014 08:55) [ Материал]
   Нашел ответ на свой вопрос:)
Код
=СУММЕСЛИМН(G2:G16;B2:B16;J2;D2:D16;K2;A2:A16;L2;E2:E16;"<="&N2)

0   Спам
17    Mike   (02.04.2014 17:28) [ Материал]
   Спасибо за статью, давно хотел разобраться с подобным инструментарием, не всегда получается создать дополнительные ячейки для промежуточных расчетов. Особенное спасибо за масштаб статьи - рассмотрено сразу же несколько способов суммирования по нескольким критериям. А чем большим инструментарием мы владеем, тем эффективнее решаем такие задачи.

0   Спам
18    Павел   (07.08.2014 16:52) [ Материал]
   Хорошо всё расписано, но есть один вопрос. Если в столбце "А" указан не месяц, а конкретная дата, при этом суммировать надо диапазон, к примеру от "10 января до 27 января"

0   Спам
19    Gulmira   (22.08.2014 15:39) [ Материал]
   Добрый день, подскажите пож. как быть если в одном диапазоне несколько критериев, вариант сумеслимн(F:F;D:D;"карандаши")+сумеслимн(F:F;D:D;"папки") не подходит.

0  
20    Serge_007   (22.08.2014 18:20) [ Материал]
   Gulmira , Вы точно читали статью? В ней четыре способа решения Вашей задачи и приложенный пример

0   Спам
21    Анна   (24.08.2014 12:08) [ Материал]
   подскажите, как построить формулу если одно условие - поиск значения по горизонтали, второе- по вертикали? выдает ошибку
заранее спасибо!

0  
22    Serge_007   (24.08.2014 12:14) [ Материал]
    Анна, давайте файл с ошибкой

0   Спам
23    Анна   (24.08.2014 12:20) [ Материал]
   а как его прикрепить к комментарям?

0  
24    Serge_007   (24.08.2014 12:24) [ Материал]
   Никак
Нет такой опции в комментариях к статьям
Но это ведь Вы начали задавать вопросы там, где это делать не положено :)

ЗЫ Для решения проблем человечество изобрело форум

0   Спам
25    Анна   (24.08.2014 12:28) [ Материал]
   хорошо, я задам вопрос в положенном месте.

1-25 26-34
Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!