Профессиональные приемы работы в  Microsoft Excel

Top.Mail.Ru

Яндекс.Метрика

По роду экономической службы мне ежемесячно приходится формировать отчеты о товарообороте магазинов нашей компании. Торговые документы, откуда я беру первичные данные, представляют из себя длинную «портянку», поэтому заниматься отбором нужных показателей вручную, - дело неблагодарное.

Для успешного решения сей задачи я использую мою любимую функцию СУММЕСЛИ(диапазон; критерий; диапазон суммирования).

Она позволяет отбирать и суммировать показатели по заданному критерию из указанных массивов данных. В качестве критерия может выступать как число, так и выражение (текст). Покажу ее действие на простом примере. Исходные данные: дата, № торговой точки, населенный пункт и товарооборот с учетом НДС (ТО) 

ячейки A B C D
1 Дата №№ магазина Город Товарооборот с учетом НДС, в руб.
2 1 ноя 1 Ельск 125 246,00
3 1 ноя 2 Ельск 175 550,00
4 1 ноя 4 Сосновка 246 222,00
5 1 ноя 6 Ельск 47 555,00
6 1 ноя 10 Сосновка 138 444,00
7 2 ноя 2 Ельск 45 568,00
8 2 ноя 4 Сосновка 23 569,00
9 2 ноя 10 Сосновка 352 468,00
10 3 ноя 1 Ельск 56 822,00
11 3 ноя 6 Ельск 155 526,00
12 4 ноя 1 Ельск 63 525,00
13 4 ноя 2 Ельск 85 656,00
14 4 ноя 4 Сосновка 288 895,00
15 4 ноя 6 Ельск 32 244,00

Требуется рассчитать ТО по каждому магазину и городу за период. «Рисуем» отчеты: 

Показатель №№ магазина Сумма, руб.
Товарооборот по магазинам с учетом НДС за период с 1 по 4 ноября 1 245 593,00
2 306 774,00
4 558 686,00
6 235 325,00
10 490 912,00

 

Показатель Город Сумма, руб.
Товарооборот по городам с учетом НДС за период с 1 по 4 ноября Ельск 787 692,00
Сосновка 1 049 598,00

В первом отчете в ячейке показателя «Сумма, руб.» вышеуказанная функция примет вид =СУММЕСЛИ($B:$B;1;$D:$D), или =СУММЕСЛИ($B:$B;2;$D:$D), или =СУММЕСЛИ($B:$B;4;$D:$D) и т.д., во втором - соответственно =СУММЕСЛИ($С:$С;”Ельск”;$D:$D), или =СУММЕСЛИ($С:$С;”Сосновка”;$D:$D). Как видно из формул, в 1-м случае диапазон отбора – столбец B:B («№№ магазина»), критерий отбора – «номера магазинов», во 2-м случае диапазон отбора – столбец С:С город»), критерий отбора – «название города». Вам необязательно прописывать критерий, как показано у меня в примере, гораздо легче указывать в качестве критерия относительную ячейку. Например, формулы для первого отчета можно написать иначе: =СУММЕСЛИ($B:$B;B2;$D:$D), где В2 – ячейка с нужным для расчета критерием (при копировании функции не забываем про «абсолютность» и «относительность» ячеек, о которых уже упоминал Алексей Шмуйлович).

 

Небольшой совет №1! Если ваши отчеты находятся на том же рабочем листе, что и массивы исходных данных (и возможно пересечение диапазонов критериев отбора первичной информации с диапазонами критериев отбора Вашего отчета), то во избежание возникновения циклических ссылок Вам следует либо ограничить диапазон отбора, например, =CУММЕСЛИ($В$1:$В$15;В2;$D$1:$D$15), либо все-таки указать критерий текстом или числом (=CУММЕСЛИ($B:$B;1;$D:$D)).

 

Небольшой совет №2! Кроме вышесказанного, в ячейке «критерий» функции СУММЕСЛИ можно использовать знаки «<» или «>». Если меня интересуют результаты по ТО магазинов №№ 6 и10, то функция примет вид: =СУММЕСЛИ($B:$B;”>4”;$D:$D).

 

Желаю успехов в применении данной формулы!