3057 0

MS Excel: как просуммировать динамический диапазон ячеек

21.01.13
Уважаемые сотрудники «Б&К»! У меня возникла такая проблема. Есть таблица Excel, которая представлена в формате базы данных. Одна из колонок этой таблицы содержит даты. Мне нужно в каких-то ячейках листа записать две переменные — начальную и конечную даты, а затем при помощи формулы посчитать сумму значений в базе для указанного диапазона. То есть задача состоит в том, чтобы просуммировать не фиксированный, а переменный диапазон ячеек. Я думаю, этот вопрос будет интересен многим читателям вашего издания. По крайней мере, мне возможности суммировать изменяющиеся диапазоны значений в MS Excel часто не хватает.


 

В программе Excel суммирование диапазонов с «плавающими» границами — частный случай суммирования по условию. О способах решения такой задачи мы не раз писали на страницах нашего издания (см. «Б & К», 2012, № 24). Поэтому подробно разбираться со всеми возможными приемами я не буду. Просто покажу на одном конкретном примере, как справиться с проблемой. Итак, на рисунке показан фрагмент базы данных, где собраны кассовые операции за март 2012 года. Изначально в базе было всего шесть полей: дата, счет дебета и счет кредита, далее в колонках «Д» и «К» показаны обороты по дебету и кредиту за текущую дату. Последний столбец (он называется «Контрагент») содержит текст, в расчетах мы его использовать не будем. База отсортирована по колонке «Дата». Перед таблицей добавлены две рабочие строки — сюда мы будем вводить формулы.

Теперь конкретно по нашей задаче. В ячейке «E1» записана дата (на рисунке это «02/03/2012»). Мы должны в ячейку «I1» внести формулу, которая просуммирует все обороты по дебету до «02/03/2012» включительно. Разумеется, если дату в «E1» изменить, формула должна посчитать сумму для нового диапазона ячеек.

Способ 1. Работаем с функцией «СУММЕСЛИ()»

Это самый простой способ решения задачи. Чтобы им воспользоваться, мы создадим дополнительную рабочую колонку. В эту колонку напишем формулу для определения условия суммирования записей, а затем укажем эту колонку в качества одного из параметров функции «СУММЕСЛИ()». Делаем так:

1. В ячейку «G3» вводим заголовок рабочей колонки. На рисунке это текст «Пр».

2. В ячейку «E1» вводим дату (на рисунке это значение «02.03.2012»).

3. В ячейку «G4» вводим формулу «=A4<=$E$1».

4. Копируем формулу на всю высоту таблицы.

5. В ячейку «F1» вводим формулу «=СУММЕСЛИ(G:G;ИСТИНА;D:D)» и нажимаем «Enter». Сразу же видим результат: дебетовый оборот от начала месяца и до «02.03.2012» включительно составил 3548,93 грн.

Краткий комментарий по поводу формулы. Первый параметр (блок «G:G») — это диапазон таблицы, где записан признак для суммирования ячеек. Этот признак мы проставили при помощи формулы. Те ячейки, которые должны попасть в общую сумму, отмечены значением «ИСТИНА». Именно этот факт мы отразили во втором параметре функции «=СУММЕСЛИ()». И теперь в процессе работы функция будет поэлементно (!) сравнивать содержимое каждой ячейки в колонке «G» со значением «ИСТИНА». Там, где условие справедливо, «=СУММЕСЛИ()» будет накапливать сумму. А значения для суммирования она возьмет из колонки «D» (обороты по дебету), которая указана в качестве третьего параметра функции.

Немного усложним задачу и посмотрим, как можно просуммировать фрагмент базы данных, ограниченный двумя датами. Начальная дата у нас будет по-прежнему в ячейке «E1». Конечную дату мы запишем в ячейку «E2» (на рисунке это «05/03/2012»). Принцип работы останется тем же — изменится только условие в рабочей колонке. Теперь формула в ячейке «G4» будет такой: «=И(A4>=$E$1;A4<=$E$2)». Копируем эту формулу на всю высоту таблицы. После чего та же самая формула («=СУММЕСЛИ(G:G;ИСТИНА;D:D)») посчитает суммарный оборот по дебету за период от «02/03/2012» до «05/03/2012» включительно. Понятное дело, что изменив условие, можно выборочно просуммировать данные по любым самым сложным логическим правилам.

Способ 2. Используем функцию «СУММПРОИЗВ()»

Подробно о работе с этой замечательной функцией можно почитать, например, в «Б & К», 2012, № 24. Применительно к нашей задаче эта функция позволяет получить более изящное решение, при котором вообще не понадобятся рабочие колонки, а все условия будут указаны прямо в тексте функции. Вернемся к ситуации, когда мы просуммировали фрагмент базы данных до указанной даты (это значение было записано в ячейке «E1»). В этом случае формула для суммирования может выглядеть так: «=СУММПРОИЗВ((A4:A65536<=E1)*D4:D65536)». Результат работы формулы: 3548,93 грн.

Важно! Указывать в качестве блока для суммирования адрес всей колонки или строки (например, вместо «A:A65535» написать «A:A») в функции «=СУММПРОИЗВ()» нельзя.

Для случая с двумя ограничениями (начальная и конечная даты) формула для суммирования будет такой: «=СУММПРОИЗВ((A4:A65536>=$E$1)*(A4:A65536<=$E$2)*(D4:D65536))». Как видите, в этом выражении функцию «И()» заменяет операция умножения. Иными словами, фрагмент формулы «(A4:A65536>=$E$1)*(A4:A65536<=$E$2)» вернет результат «ИСТИНА» для тех ячеек колонки «А», которые попадают в заданный интервал. В противном случае результат формулы будет равен «ЛОЖЬ». При перемножении числовых и логических выражений результат «ИСТИНА» Excel считает равным «1». А в итоге функция умножит на «1» все элементы из колонки «D», которые попадают внутрь заданного интервала, после чего просуммирует полученный результат.

И последнее. Возникает закономерный вопрос: а какое из предложенных решений все же лучшее. Здесь все зависит от задачи и личных предпочтений. В первом способе можно использовать очень сложные логические выражения. Но придется использовать дополнительные колонки. При работе с функцией «=СУММПРОИЗВ()» сложную логику (например, функцию «ИЛИ()») реализовать нельзя. Зато не нужно никаких колонок, формула работает сама по себе. На сегодня все.

 

Удачной работы! Жду ваших вопросов, замечаний и предложений на bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum.



Помітили помилку? Виділіть її та натисніть Ctrl+Enter, щоб повідомити нас про це
загрузка...
Коментарі (0)