Робота з даними в Excel 2007: режим «Итоги»

Робота з даними в Excel 2007: режим «Итоги»

У бухгалтерській практиці бази даних (табличні масиви) зовсім не завжди мають регулярну структуру, коли можна написати формулу, а потім скопіювати її. Якраз навпаки: у реальному житті регулярні таблиці бухгалтер зустрічає нечасто. Візьмемо хоча б Головну книгу. Кількість операцій за кожен день у ній різна. Щоб визначити, наприклад, обороти щодо певної групи проводок за кожен день, доведеться у формулах уручну проставляти діапазон підсумовування для кожної дати! Що й казати, перспектива нерадісна. Якщо не використовувати режим підбиття підсумків. Для бухгалтера робота з підсумками є дуже важливою. Тому ми й вирішили виділити цю тему в окрему статтю та розглянути: яку функцію виконують підсумки, які їх переваги та недоліки, чим конкретно може бути корисний режим «Итоги» практикуючому бухгалтеру. Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства

Режим підбиття підсумків, як і автофільтр, призначено для обробки великих баз даних. Але між цими інструментами є принципова відмінність. Автофільтр не змінює вихідну базу даних, він лише аналізує її записи та показує (або приховує) їх на екрані. Що стосується режиму «

Алгоритм роботи режиму «Итоги»

Для роботи в режимі «

Важливе зауваження. Зверніть увагу на фразу «…змінить своє значення.». Ми вжили її стосовно ключового поля в базі проводок. Вона означає, що перед використанням режиму «

Як викликати режим «Итоги»

Роботу з підсумками Excel 2007 зосереджено в меню «

1) завантажуємо Excel, відкриваємо файл, зображений на рис. 1;

2) викликаємо меню «

3) знаходимо групу «

4) клацаємо по іконці «Промежуточные итоги». Відкриється вікно, як показано на рис. 3.

У цьому вікні є чотири групи параметрів. Стисло опишемо їх призначення.

«При каждом изменении в:» — тут потрібно вказати ключове поле (або так зване поле групування ). Повернемося до бази на рис. 1. Якщо я хочу визначити підсумкові значення за обсягами продажів за день, ключовим полем виступатиме колонка «Дата». Саме зміст комірок у цій колонці Excel переглядатиме на предмет зміни їх значень. Параметр «При каждом изменении в:» оформлено у вигляді списку. Значеннями цього списку є всі доступні поля бази даних Excel;

«Операция:» — це теж список, у якому перелічено всі можливі типи операцій над полями при підбитті підсумків. Це може бути підсумовування, підрахунок кількості значень, обчислення середнього, визначення максимуму, мінімуму чи добутку;

«Добавить итоги по:» — цей параметр визначає, за якими з полів бази даних Excel має підрахувати підсумки. Параметр оформлено у вигляді невеликого вікна, в якому показано список усіх полів бази даних. Ліворуч від кожної назви є невеликий квадратик. Клацання миші по квадратику робить його активним. У цьому випадку всередині квадратика з’явиться галочка. На рис. 3 активними полями для підсумків є «Кол» і «Сумма». Це означає, що формули для визначення підсумків Excel поставить саме в цих колонках;

«Заменить текущие итоги» — коли параметр уключено , кожне нове визначення підсумків замінює попередні значення. Якщо прапорець відключити, то нові підсумки Excel додасть до попередніх. У такий спосіб з’являється можливість створити таблицю з багаторівневими підсумками;

«Конец страницы между группами» — параметр відіграє не стільки функціональну, скільки оформлювальну роль. Якщо його включити , то при кожній зміні у значенні ключового поля на лист Excel буде вставлено символ початку нової сторінки. Таким чином, кожну групу підсумкових значень буде надруковано з нового листа. На практиці цей режим використовують рідко;

«Итоги под данными» — параметр визначає, в якому місці Excel має додати рядок із підсумковими значеннями. Якщо прапорець включено , у результуючій таблиці спочатку йдуть значення, а потім — рядок з підсумками (так прийнято за умовчанням). Якщо прапорець відключити, то в результуючому звіті Excel спочатку розташує підсумки, а під ними знаходитимуться дані.

Унизу вікна розташовано три кнопки:

— «Убрать все» — видаляє підсумки з таблиці;

— «ОК» — ініціює підбиття підсумків;

— «Отмена» — скасовує виконання операції з розрахунку підсумків.

Приклади роботи з підсумками

Докладно деталі роботи з інструментом «

ПІДСУМКИ ЗА ОДНИМ КЛЮЧОВИМ ПОЛЕМ

Це — найпростіший спосіб використання підсумків. Хоча на практиці він, мабуть, є найпоширенішим. Роботу в цьому режимі ми розглянемо на прикладі бази даних, зображеної на рис. 1. Наше завдання — визначити сумарний оборот щодо кожного контрагента у вартісному та кількісному виразі. Виконуємо такі дії:

1) відкриваємо файл із базою даних;

2) робимо активною будь-яку комірку в колонці «

3) викликаємо меню «

4) знаходимо групу «

5) із меню, що з’явилося, вибираємо значення «

6) викликаємо меню «

7) знаходимо групу «

8) клацанням миші розкриваємо список «

9) клацаємо по списку «

10) у списку «

11) прапорець «

12) натискуємо на «

Після кожної зміни в полі «Дата» з’являється додатковий рядок. Ключовим полем у нас була колонка «А». У цій колонці в кожному рядку з підсумками Excel поставив спеціальний запис — значення ключового поля та слово «Итог». Наприклад, для дати «03/02/2010» тут стоятиме значення «03/02/2010 Итог». Далі по рядку (у колонках «Е» та «G») з’явилися підсумки. Перший підсумок у полі «Кол» (рис. 4 на с. 23) розташовано в комірці «E14», він дорівнює 264 (шт.). Другий підсумок знаходиться в комірці «G14», він дорівнює 464,8 грн. Це — не що інше, як обороти в кількісному та вартісному виразі за дату «03/02/2010».

Ліворуч від рядків із підсумками з’явилися спеціальні значки. На рис. 4 їх показано у вигляді квадратиків зі знаком «-» (мінус). Це — символи управління структурою таблиці. Від кожного прямокутника в напрямку «вгору» йде графічний елемент, що нагадує прямокутну дужку. Він показує, які рядки в базі даних увійшли до розрахунку поточного підсумку. Так, на рис. 4 до підсумків за «03/02/2010» увійшли рядки робочого листа з другого по тринадцятий.

Клацання по квадратику з «мінусом» згортає групу підсумків в один рядок. Спробуйте клацнути лівою кнопкою по квадратику, розташованому напроти рядка «14». На екрані залишиться тільки рядок із підсумком за цю дату. Записи бази даних, починаючи з другої по тринадцяту, Excel прибере з екрана (зробить невидимими).

У правому верхньому куті робочого листа з’явилися три нові елементи. Вони виглядають як прямокутники з написами «1», «2» і «3». Це — кнопки згортання та розгортання підсумків за рівнями групування. За їх допомогою можна миттєво змінити ступінь деталізації підсумкового звіту. Виконуємо такі дії:

1) клацаємо по прямокутнику з цифрою «2». На екрані залишилися тільки рядки з підсумками (номери «14», «32», «54» тощо — рис. 5, угорі);

2) клацаємо по прямокутнику з цифрою «1». На екрані залишився єдиний рядок із загальним підсумком щодо всієї бази даних (рис. 5, унизу).

Підсумуємо, що ж ми отримали. При настроюванні параметрів підсумків як ключове поле було зазначено колонку «Дата». Тому в цій колонці Excel і поставив коментарі, продублювавши значення ключового поля разом зі словом «Итог». Інші колонки (крім тих, де є обчислення) він залишив порожніми. Це неприйнятний результат, і проблема виявилася при згортанні підсумків. Наприклад, переглядаючи дані на другому рівні (рис. 5, угорі), ми не бачимо даних про покупця, найменування товару, номер документа. У нашому прикладі в цих даних сенсу немає. Але трапляються випадки, коли цей недолік ускладнює роботу з підсумковою таблицею. Наприклад, при роботі із вкладеними підсумками. Як вирішити цю проблему, ми розглянемо трохи пізніше.

Тепер розберемося, як Excel визначив підсумки. Клацаємо по будь-якому значенні, де підраховано підсумок. Наприклад, по комірці «E14». У ній ми бачимо формулу «=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;E2:E13)». Це — убудована функція Excel, яку він застосував для розрахунку підсумкових значень у базі даних. Синтаксис функції такий: «ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции, Блок1, Блок2...)». Перший параметр визначає тип операції, що виконується. Далі йдуть адреси блоків комірок, над якими виконується ця операція. Можна зазначити до 29 різних блоків. Функція «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» є багатофункціональною. Перелік виконуваних нею операцій зазначено в таблиці.

Функцію «

Важливою перевагою функції «

ВИДАЛЕННЯ ПІДСУМКІВ

Щоб прибрати підсумки з бази даних, виконуємо такі дії:

1) ставимо активну комірку на область бази даних;

2) викликаємо меню «

3) знаходимо групу «

4) у вікні «

ВКЛАДЕНІ ПІДСУМКИ

Повернемося до бази на рис. 1 та спробуємо знайти сумарний оборот щодо кожного окремого контрагента (у вартісному та кількісному виразі) за кожну дату. Для цього нам знадобиться підбити підсумки двічі. Спочатку — щодо контрагенту (поле «

1) відкриваємо файл із базою даних;

2) ставимо активну комірку на область бази даних;

3) викликаємо меню «

4) знаходимо групу «

5) із меню, що з’явилося, вибираємо

6) у списку «

7) параметр «

8) у списку «

9) клацаємо по кнопці «

10) у списку «

11) параметр «

12) у списку «

13) натискуємо на «ОК»;

14) викликаємо меню «Данные»;

15) знаходимо групу «Структура» та клацаємо по іконці «Промежуточные итоги» (рис. 2). З’явиться вікно, як показано на рис. 3;

16) клацанням миші розкриваємо список «При каждом изменении в:», вибираємо значення «Покупець»;

17) клацаємо по списку «Операция:», вибираємо значення «Сумма»;

18) у списку «

19) прапорець «

20) натискуємо на «

21) знову викликаємо меню «

22) знаходимо групу «

23) клацанням миші розкриваємо список «

24) клацаємо по списку «

25) у списку «

26) прапорець «

27) натискуємо на «

У таблиці з’явилися два рівні підсумків. У колонці «Дата» розташовано підсумки за датою, у колонці «Покупатель» — сумарні обсяги реалізації щодо кожного контрагента.

ПІДСУМКИ ПЛЮС АВТОФІЛЬТР

При роботі з інструментами обробки даних особливо цікавим є їх спільне використання. У цьому випадку йдеться про роботу з підсумками при одночасній фільтрації даних. У такому режимі ви можете отримати підсумки не для всієї бази даних, а лише для певної її частини. Спробуємо застосувати два згадані інструменти до таблиці, зображеної на рис. 1. Наша мета — підрахувати підсумки реалізації за датами вибірково для одного покупця. Виконуємо такі дії:

1) відкриваємо файл із базою даних;

2) ставимо активну комірку на область бази даних;

3) викликаємо меню «

4) знаходимо групу «

5) із меню, що з’явилося, вибираємо «

6) клацаємо по значку вибору в колонці «

7) як значення поля «Покупатель» вибираємо «ООО “Коло”»;

8) натискуємо на «

9) викликаємо меню «

10) у групі «

11) у вікні «

12) вибираємо значення «

13) у списку «

14) у списку «

15) уключаємо прапорець «

16) натискуємо на «

Перед нами практично такий самий результат, як показано на рис. 7. Але є одна відмінність. Спробуємо зняти автофільтр. Виконуємо такі дії:

1) клацаємо по значку вибору в полі «

2) із меню, що розкрилося, вибираємо «

Ми відмінили автофільтр. Тепер на екрані з’явилися всі записи. Перегорнувши всю базу, ми побачимо, що інструмент «