Calc: сумма по нескольким критериям
Уважаемая редакция! Можно ли в программе Calc просуммировать значения в базе данных сразу по нескольким критериям? Думаю, такой пример будет полезен всем бухгалтерам, перешедшим на бесплатный OpenOffice. Заранее благодарю.
А. Шеховцова, главный бухгалтер, г. Харьков
Отвечает Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства
Задача определения итогов с учетом нескольких условий для бухгалтера очень важна. В чем, собственно, проблема? В Calc есть удобная функция «
SUMIF()». Она позволяет просуммировать значения с учетом некоторого условия. При этом «SUMIF()» может накапливать сумму по значениям одного столбца, проверяя выполнение условий в другой колонке. Однако на практике этого бывает мало и бухгалтеру нужно организовать суммирование значений, проверяя несколько условий одновременно. Реализовать такой механизм расчетов можно в любой электронной таблице, и Calc не исключение. Причем сделать это можно разными способами. Основные из них я сейчас покажу на небольшом примере. Приступим.На рис. 1 показан фрагмент отчета по счету «
28.х», который был импортирован в Excel из программы «1С» через отчет по проводкам. От исходного отчета я оставил только дату операции (колонка «Дата»), место хранения, наименование ТМЦ (поле «Наим»), количество (поле «Кол.»). Остальные столбцы исходного отчета я удалил. 
Наша задача — найти объем продаж (в штуках!) товара «Сумка женская», который был реализован с места хранения «Основной склад». Вот как это сделать.
Способ 1. Использование рабочей колонки
1. Ставим указатель в ячейку «
F1», пишем заголовок рабочей колонки «Пр.» (рис. 1).2. Становимся на ячейку «
F2». Пишем формулу для анализа условия: «=(C2="Сумка женская")*(B2="Основной склад")».3. Копируем формулу на всю высоту таблицы. В тех строках, где условие справедливо (наименование товара «
Сумка женская» и реализация идет со склада «Основной склад»), в колонке «F» появится значение «1». Остается накопить сумму.4. В ячейку «
G1» пишем заголовок «Рез.».5. В «
G2» набираем формулу «=E2*F2».6. Копируем формулу вниз на всю высоту таблицы.
7. Суммируем значения колонки «
G» по формуле: «=SUM(G2:G71)» (ячейка «G72»). Задача решена.Есть и другие варианты. Например, можно воспользоваться функцией «
SUMIF()». Формула выглядит так: «=SUMIF(F2:F65536;1;E2:E65536)». Такой же результат даст и формула «=SUM PRODUCT(E2:E65536;F2:F65536)» (ячейка «G73»).Важно!
Номер строки «65536» я поставил для того, чтобы обработать всю таблицу. Тогда при пополнении базы новыми записями они автоматически попадут в область суммирования.
Способ 2. Использование формулы-массива
В Calc несколько формул можно объединить в одно выражение типа «массив». Не путайте формулу-массив и вложенные функции. Вложенные функции Calc обрабатывает последовательно, передавая промежуточные значения «по цепочке». Например, формула «
=ABS(SUM(G2:G71))» состоит из двух вложенных функций. Вначале Calc обработает внутреннюю функцию суммирования, а затем к результату применит функцию «Abs» — определение модуля. Формулу-массив Саlс обрабатывает «параллельно», поэтому вычисления в массиве могут выполняться с одновременной проверкой условий. Для нашего примера делаем так:1) становимся на ячейку «
G74»;2) вводим формулу «
=SUM((C2:C100="Сумка женская")*(B2:B100="Основной склад")*E2:E100)»;3) нажимаем комбинацию «
Ctrl+Shift+Enter» (не «Enter»!).Важно! По комбинации «Ctrl+Shift+Enter» Calc преобразует введенное выражение в формулу-массив. Признаком такого преобразования будут фигурные скобки, куда Calc поместит формулу. Вводить эти скобки с клавиатуры не нужно.
Способ 3. Использование функции DSUM()
В этом варианте мы применим для решения задачи средства работы с базами данных. Нам понадобится функция «
DSUМ()». Делаем так:1) формируем специальный блок ячеек для записи условий просмотра. Пусть это будет блок «
I1:J2» (рис. 2). В этот блок пишем те заголовки базы данных, по которым собираемся вести суммирование;
Важно! Заголовки блока значений должны в точности совпадать с заголовками основной базы данных.
2) в ячейку «G75» пишем формулу: «=DSUМ(A1:E75;E1;I1:J2)». В ней базой данных является блок «A1:E75». Значения для суммирования функция будет брать из колонки «E». На это указывает второй параметр: в ячейке «E1» записано имя поля «Кол», а сведения о количестве ТМЦ находятся в колонке «E». Каждый раз, прибавляя данное к промежуточному результату, функция проверит условие из блока «I1:J2». Если оно истинно, то суммирование произойдет, если нет, то будет обрабатываться следующее значение.
Все описанные приемы можно с успехом распространить на три, четыре и большее количество условий без каких-либо ограничений. Например, чтобы определить объемы продаж (в штуках) товара «Сумка женская», реализованные с места хранения «Основной склад» начиная с 12.12.2007 г., нужно откорректировать формулы, а именно:
— в рабочей колонке условие для ячейки «G2» примет вид «=(C2="Сумка женская")*(B2="Основной склад")*(A2>DATEVALUE("13.12.2007"))»;
— формула-массив станет такой: «{=СУММ((C2: C100="Сумка женская")*(B2:B100="Основной склад")*(A2:A100>DATEVALUE("13.12.2007")) *E2:E100)}».
Жду ваших вопросов, замечаний и предложений на
bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum . Удачной работы!
