По роду экономической службы мне ежемесячно приходится формировать отчеты о товарообороте магазинов нашей компании. Торговые документы, откуда я беру первичные данные, представляют из себя длинную «портянку», поэтому заниматься отбором нужных показателей вручную, - дело неблагодарное.
Для успешного решения сей задачи я использую мою любимую функцию СУММЕСЛИ(диапазон; критерий; диапазон суммирования).
Она позволяет отбирать и суммировать показатели по заданному критерию из указанных массивов данных. В качестве критерия может выступать как число, так и выражение (текст). Покажу ее действие на простом примере. Исходные данные: дата, № торговой точки, населенный пункт и товарооборот с учетом НДС (ТО)
ячейки | 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).
Желаю успехов в применении данной формулы!