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)». Такий самий результат дасть і формула «=SUMPRODUCT(E2:E65536;F2:F65536)» (комірка «G73»).Важливо!
Номер рядка «65536» я поставив для того, щоб обробити всю таблицю. Тоді при поповненні бази новими записами вони автоматично потраплять до області підсумовування.
Спосіб 2. Використання формули-масиву
У Calc декілька формул можна об’єднати в один вираз типу «масив». Не слід плутати масив формул та вкладені функції. Вкладені функції Calc обробляє послідовно, передаючи проміжні значення «за ланцюжком». Наприклад, формула «
=ABS(SUM(G2:G71))» складається із двох вкладених функцій. Спочатку Calc обробить внутрішню функцію підсумовування. Потім до результату застосує функцію «Abs» — визначення модуля. Формулу-масив Excel обробляє «паралельно», тому обчислення в масиві можуть виконуватися з одночасною перевіркою умов. Для нашого прикладу робимо так: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. Успішної роботи!
