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