Теми статей
Обрати теми

Excel 2007: робота з Конструктором зведених таблиць

Редакція БК
Стаття

Excel 2007: робота з Конструктором зведених таблиць

 

img 1

Програма Excel 2007 пропонує бухгалтеру широкий асортимент засобів з управління зведеними таблицями. Це і параметри зведених звітів, і властивості полів таблиці. Але є ще один інструмент, що поєднує в собі низку спеціальних можливостей роботи зі зведеними звітами. Ідеться про Конструктор зведених таблиць. Про цей новий інструмент Excel 2007 ми й поговоримо в нашій статті. А заразом розглянемо додаткові можливості, приховані у Майстрі зведених таблиць.

Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства

 

Завершивши створення зведеної таблиці, є сенс змінити макет і формат звіту, щоб зробити його привабливішим. Способів для вирішення такого завдання декілька, і один із них — звернутися до Конструктора зведених таблиць. Познайомитися з основними можливостями цього компонента Excel і буде сьогодні нашим завданням.

 

Робота з Конструктором зведених таблиць

Щоб викликати Конструктор, виконайте такі дії:

1) поставте активну комірку всередину зведеної таблиці. У правій частині головного меню програми з'явиться розділ «

Работа со сводными таблицами»;

2) у цьому розділі клацніть по кнопці «

Конструктор». Стрічка основного меню набере вигляду, як показано на рис. 1. На ній з’являться три групи іконок: «Макет», «Параметры стилей сводной таблицы» і «Стили сводной таблицы». Вони дозволяють змінити стиль оформлення зведеної таблиці, характер визначення проміжних підсумків, відкоригувати макет звіту тощо. Подивимося на роботу основних інструментів Конструктора на конкретних прикладах.

 

img 2

 

УПРАВЛІННЯ ПІДСУМКАМИ У ЗВЕДЕНІЙ ТАБЛИЦІ

Зведені звіти часто використовують як заготовки для виконання різних розрахунків. У таких розрахунках може знадобитися сформувати проміжні підсумки за значеннями ключового поля. Цю роботу можна виконати спеціальним інструментом «

Итоги» MS Excel. Але з нею успішно впорається і Конструктор зведених таблиць. Подивимося, який вигляд це має на практиці. А як вихідними даними скористаємося вже знайомою базою з реалізації товарів (рис. 2). Виконуємо такі дії:

img 3

1) формуємо зведений звіт, де в області рядків розташовано два поля: дати і назви покупців. До області даних переносимо поле «

Сумма», зазначивши для нього операцію підсумовування. Отримаємо структуру таблиці, як показано на рис. 3 (див. с. 18). За логікою даних у ній можна підбити підсумок за обсягами реалізації кожної групи покупців за певну дату;

img 4

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

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

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

img 5

4) із цього списку вибираємо пункт «

Показывать все промежуточные итоги в нижней части группы». Після зміни значення в полі «Дата» Excel запровадить до зведеного звіту рядок із підсумками (рис. 3).

Той самий результат можна отримати іншим способом — через параметри поля зведеної таблиці. Виконайте такі дії:

1) клацніть правою кнопкою миші на будь-якому значенні поля «

Дата»;

2) з контекстного меню виберіть пункт «

Параметры поля…». Відкриється однойменне вікно «Параметры поля…» (рис. 5);

img 6

3) у групі «

Итоги» поставте перемикач у положення «Другие», у віконці з переліком функцій виберіть «Сумма»;

4) натисніть на «

ОК». Таблиця набуде форми, як показано на рис. 3.

Для того щоб додати або скасувати додавання підсумків за рядками та стовпцями зведеного звіту, можна скористатися іконкою «

Общие итоги» (рис. 1). Клацання по цій іконці розкриває меню з чотирьох пунктів: «Отключить для строк и столбцов», «Включить для строк и столбцов», «Включить только для строк», «Включить только для столбцов». Клацання по будь-якому з пунктів включає або скасовує відображення відповідних підсумків.

 

ЗМІНА МАКЕТА ЗВЕДЕНОГО ЗВІТУ

Можливості управління макетом зосереджено на іконці «

Макет отчета». Клацаємо по ній лівою кнопкою. Розкриється меню, як показано на рис. 6. Тут Excel пропонує три варіанти: «Показать в сжатой форме», «Показать в форме структуры», «Показать в табличной форме».

img 7

Варіант «

Показать в сжатой форме» використовують, щоб щільніше розташувати дані по горизонталі. При цьому кілька ключових полів Excel переносить до одного стовпця, а структуру взаємозв’язку між полями показує за допомогою відступів. Зовнішній вигляд нашої таблиці (рис. 3) у стислому форматі показаний на рис. 7.

img 8

Варіант «

Показать в форме структуры» застосовують, щоб показати структуру даних у класичному стилі зведеної таблиці. Вибираючи «Показать в табличной форме», ви зобразите звіт у традиційному табличному форматі.

Порада Вибирайте варіант «Показать в табличной форме», якщо плануєте подальше перетворення зведеної таблиці. Цей спосіб краще за інші застосовний для копіювання чи переміщення комірок через буфер обміну.

 

ДОДАВАННЯ ПОРОЖНІХ РЯДКІВ ДО ЗВЕДЕНОГО ЗВІТУ

У зведену таблицю можна вставити порожні рядки після кожної зміни ключового поля. Виконайте такі дії:

1) відкрийте таблицю, як на рис. 3. Підсумки з неї можна видалити. Для цього викликаємо Конструктор, клацаємо по іконці «

Промежуточные итоги», а потім з меню, що з’явилося, вибираємо «Не показывать промежуточные суммы» (рис. 4);

2) залишаючись усередині зведеного звіту, клацаємо по іконці «

Пустые строки». З’явиться меню, як показано на рис. 8;

img 9

3) вибираємо варіант «

Вставить пустую строку после каждого элемента». Звіт набуде форми, як показано на рис. 9.

img 10

Щоб видалити порожні рядки, потрібно звернутися до пункту «

Удалить пустую строку после каждого элемента».

Виникає закономірне запитання: коли може стати у пригоді можливість додавання порожніх рядків до зведеного звіту?

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

1) відкриваємо зведену таблицю;

2) не виходячи зі зведеної таблиці, клацаємо по іконці «

Пустые строки»;

3) з меню (рис. 8) вибираємо «

Вставить пустую строку после каждого элемента»;

4) виділяємо всю зведену таблицю. Для цього викликаємо меню «

Параметры», клацаємо по іконці «Выбрать» стрічки «Действия». З меню, що розкрилося, вибираємо пункт «Всю сводную таблицю»;

5) копіюємо таблицю до буфера обміну («

Ctrl+C»);

6) не знімаючи виділення, клацаємо правою кнопкою миші, з контекстного меню вибираємо опцію «

Специальная вставка». З’явиться вікно «Специальная вставка»;

7) у вікні спеціальної вставки ставимо перемикач у положення «

Значения»;

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

ОК»;

9) виділяємо колонку «

С» («Сумма, грн.»);

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

Ctrl+G». З’явиться вікно «Переход»;

11) у цьому вікні клацаємо на кнопці «

Выделить...». З’явиться вікно «Выделение группы ячеек»;

12) у цьому вікні ставимо перемикач «

Выделить» у положення «пустые ячейки»;

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

ОК». Після цього активною стане перша порожня комірка в колонці «С». У прикладі на рис. 3 це комірка «С7». До неї потрібно ввести формулу для розрахунку ПДВ за значенням у комірці «С6» (сума обороту за всіма контрагентами за «03/01/2009»);

14) не знімаючи виділення, уводимо формулу. Для таблиці на рис. 3. вона матиме такий вигляд: «

=C6*0,2»;

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

Ctrl+Enter». Після кожного проміжного підсумку з’явиться додатковий рядок із сумою ПДВ.

 

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

За допомогою Конструктора зведених таблиць можна швидко відформатувати звіт, використовуючи вбудовану бібліотеку стилів. Для цього виконуємо такі дії:

1) відкриваємо зведений звіт (рис. 3);

2) стаємо всередину зведеної таблиці;

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

Конструктор»;

4) на стрічці Конструктора у групі «

Стили сводной таблицы» (рис. 1) клацаємо по будь-якій іконці. Я вибрав «Стиль сводной таблицы: светлый 1». Звіт набуде вигляду, як показано на рис. 10.

img 11

Тепер усі заголовки рядків та колонок зведеної таблиці виділено напівжирним зображенням. Таким же форматуванням позначено проміжні підсумки. Але це ще не все.

У стилі таблиці записано багато параметрів. Деякі з них зображено у групі іконок «

Параметры стилей сводной таблицы» (рис. 1). Виконайте такі дії:

1) клацніть по іконці «

Заголовки строк» (група «Параметры стилей сводной таблицы», рис. 1). Заголовки втратять напівжирне зображення;

2) клацніть по іконі «

Заголовки колонок». Формат заголовків колонок стане звичайним;

3) включіть прапорець «

Чередующиеся строки», це дозволить поперемінно виділити кожен рядок світлим і темним фоном. Кожен непарний рядок зведеного звіту на рис. 3 буде виділено сірим фоном;

4) клацніть по іконці «

Чередующиеся столбцы». Excel виділить фоном усі непарні колонки. У нашому прикладі на рис. 3 це буде стовпець «Сумма, грн.».

Зрозуміло, ви можете користуватися не тільки наявними стилями, а й створювати свої. Усе це можна зробити безпосередньо зі стрічки Конструктора. Докладно про роботу зі стилями ви зможете прочитати в одній із наших попередніх статей щодо роботи з Excel 2007 (наприклад, «Б & К», 2009, № 16, с. 19, № 17, с. 11, № 18, с. 31).

 

Інші операції зі зведеною таблицею

У завершальній частині статті ми покажемо кілька можливостей щодо роботи зі зведеними звітами, які досі залишалися поза зоною нашої уваги. Водночас їх застосування напевно урізноманітнить прийоми роботи з Excel 2007.

 

ЯК ВИДАЛИТИ ФОРМАТУВАННЯ ЗВЕДЕНОГО ЗВІТУ

Якщо до зведеної таблиці застосувати вбудований стиль, не всі параметри форматування можна буде змінити вручну. Повернемося до таблиці на рис. 10. За допомогою стилю «

Стиль сводной таблицы: светлый 1» ми відформатували заголовки таблиці напівжирним зображенням. Виконайте такі дії:

1) поставте активну комірку на «

A2»;

2) натисніть на «

Ctrl+B», щоб скасувати напівжирне зображення. Формат комірки залишився тим самим.

Усе правильно: ті атрибути форматування, які явно зазначено у стилі, мають вищий пріоритет. Щоб скасувати дію стилю, виконайте такі дії:

1) поставте покажчик активної комірки всередину зведеної таблиці;

2) клацніть по кнопці «

Конструктор» меню «Работа со сводными таблицами». У правій частині стрічки цього меню з’явиться група «Стили сводной таблицы» з галереєю доступних стилів оформлення.

 

СОРТУВАННЯ ДАНИХ У ЗВЕДЕНОМУ ЗВІТІ

Відсортувати дані у зведеному звіті можна декількома способами. Наприклад, через параметри ключового поля. Але можна скористатися стрічкою «

Параметры» меню «Работа со сводными таблицами». Виконуємо такі дії:

1) відкриваємо зведений звіт;

2) стаємо на довільний елемент ключового поля (при сортуванні за колонкою «

Дата» таблиці на рис. 10 це може бути, наприклад, комірка «A2»);

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

Сортировка» стрічки «Параметры» (рис. 11). З’явиться вікно з параметрами сортування;

img 12

4) залежно від завдання вказуємо тип сортування (за збільшенням, за убуванням або вручну, рис. 12);

img 13

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

ОК».

Зверніть увагу на можливість сортування даних «

Вручную». У цьому режимі ви зможете перемістити елементи в колонці ключового поля довільним чином. Така дія визначить порядок сортування всієї зведеної таблиці.

 

ОЧИЩЕННЯ, ВИБІР ТА ПЕРЕМІЩЕННЯ ЗВЕДЕНОГО ЗВІТУ

Ці можливості знаходяться у групі «

Действия» стрічки «Параметры» (рис. 11). Виконуємо такі дії:

1) відкриваємо зведену таблицю, як показано на рис. 10;

2) ставимо покажчик активної комірки всередину зведеного звіту;

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

Очистить» (рис. 11). З’явиться меню з двох пунктів: «Очистить все» та «Очистить фильтры»;

4) вибираємо варіант «

Очистить все». Зведену таблицю буде видалено з робочого листа;

Важливо! У цьому прикладі Excel видалить тільки вміст і макет зведеної таблиці. Сам об’єкт залишиться на робочому листі у вигляді порожнього макета. Щоб позбавитися зведеної таблиці разом з макетом, потрібно видалити відповідні рядки чи колонки робочого листа;

5) скасовуємо останню дію комбінацією «Ctrl+Z». Зведений звіт знову з’явиться на робочому листі;

6) залишаючись усередині зведеної таблиці, клацаємо по іконці «Выбрать» (рис. 11). З’явиться меню, зображене на рис. 13;

img 14

7) у цьому меню вибираємо «

Всю сводную таблицу». Excel виділить увесь зведений звіт. Саме через це меню можна виділити заголовки або значення у зведеній таблиці;

8) не залишаючи зведеної таблиці, у групі «

Действия» клацаємо по іконці «Переместить» (рис. 11). З’явиться вікно, як показано на рис. 14;

img 15

9) у цьому вікні вказуємо нове місце розташування зведеного звіту. Це може бути інший лист або існуючий лист. В останньому випадку потрібно зазначити комірку, де починатиметься зведена таблиця.

Визначивши місце розташування зведеного звіту, натискуємо на «

ОК». Excel перемістить усю зведену таблицю на нове місце.

 

НАСТРОЮВАННЯ ВИГЛЯДУ ЗВЕДЕНОЇ ТАБЛИЦІ

Крім форматування з настроювання параметрів зведеної таблиці, на панелі «

Параметры» у групі «Показать или скрыть» передбачено три іконки для управління зовнішнім виглядом зведеної таблиці. Ось їх призначення:

— «

Список полей» — відкриває вікно «Список полей сводной таблицы»;

— «

Кнопка +/-» — включає або відключає перегляд символів групування у зведеному звіті;

— «

Заголовки полей» — включає або відключає режим відображення заголовків ключових полів зведеної таблиці.

 

НАСТРОЮВАННЯ ДЖЕРЕЛА ДАНИХ

Отже, ви створили зведений звіт, настроїли його макет, підібрали параметри форматування. І в цей момент з’ясовується, що вихідні дані зазнали змін. Наприклад, до них було додано нові записи, і ці записи не потрапляють до області даних зведеної таблиці. Як діяти в такій ситуації? Формувати весь звіт наново? Звичайно ж, ні. Для вирішення проблеми достатньо просто змінити джерело даних, скориставшись іконкою «

Изменить источник данных» у групі «Данные» стрічки «Параметры» (рис. 11).

Виконуємо такі дії:

1) відкриваємо зведений звіт;

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

Параметры» меню «Работа со сводными таблицами»;

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

Изменить источник данных». Відкриється меню з двох пунктів (рис. 15);

img 16

4) вибираємо варіант «

Изменить источник данных…». Відкриється вікно Майстра зведених таблиць;

5) у цьому вікні вводимо новий діапазон робочого листа;

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

ОК».

Говорячи про управління джерелами даних, потрібно відзначити ще один важливий момент. Якщо ви звернули увагу, при роботі зі зведеними таблицями нам жодного разу не зустрівся інструмент створення звіту за декількома діапазонами консолідації. А для бухгалтера така можливість у певних ситуаціях дуже важлива! Ось приклад.

Є дві бази даних. Одна з них знаходиться на листі «

Поступления». Тут зібрано відомості про надходження товарів (рис. 15). Друга база розташована на листі «Продажи». У ній записано інформацію щодо обсягів продажів (рис. 16). Структура обох таблиць зрозуміла. У таблиці «Поступления» першою колонкою є «НаимТов», це назва ТМЦ, що надійшла. Потім іде стовпець «Кол» — кількість товару, що надійшов. Далі по черзі: ціна за одиницю, сума надходження, номер накладної, дата надходження, назва постачальника. Склад таблиці «Продажи» такий самий. Наше завдання — визначити залишки товарів у кількісному виразі. Фактично нам потрібно поелементно «вирахувати» одну таблицю з іншої, використовуючи для цього поле «Кол». Тут є два моменти:

img 17

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

2) ми повинні вигадати, як виконати операцію віднімання полів у зведеній таблиці (у стандартному наборі арифметичних дій над полем такої операції немає). Виконуємо такі дії:

1) відкриваємо лист «

Продажи»;

2) виділяємо колонку «

B» (поле «Кол»);

3) клацаємо правою кнопкою миші, з контекстного меню вибираємо «

Вставить». Ми додали робочий стовпець між полем «НаимТов» і «Кол»;

4) як заголовок колонки «

B» друкуємо текст «Кол». Тепер виходить, що в базі продажів у нас два поля з однаковим заголовком. У принципі, це припустимо, але може внести плутанину. Тому виправимо ситуацію із самого початку;

5) переходимо на заголовок колонки «

C» (комірка «C2» у таблиці «Продажи»);

6) коригуємо заголовок, доповнивши його праворуч символом «

пробел», тобто колонка «С» у нас називатиметься «Кол »;

7) до комірки «

B2» пишемо формулу «=-С2»;

8) копіюємо формулу на всю висоту таблиці (рис. 17).

img 18

Важливо! Заголовки колонок «B» на листах «Продажи» і «Поступления» повинні точно збігатися. Це важливо при створенні зведеного звіту з декількох робочих листів.

З цього моменту в робочій колонці таблиці «Продажи» буде показано зведення щодо обсягів реалізації, але зі знаком «-». Тепер нам достатньо підсумувати елементи колонки «Кол» листа «Поступления» з елементами колонки «Кол» листа «Продажи», і завдання буде вирішено. Але для цього нам знадобиться інструмент для створення звітів за кількома діапазонами консолідації (за декількома листами робочої книги). Такий інструмент в Excel 2007 є, але його потрібно включити через параметри програми. Виконуємо такі дії:

1) клацаємо по кнопці «Офис» програми Excel 2007;

2) у вікні головного меню клацаємо по кнопці «Параметры Excel» (рис. 18). Відкриється вікно настройок програми (рис. 19);

img 19

 

img 20

3) у цьому вікні переходимо на пункт «

Настройка». У центральній частині вікна з’явиться віконце з переліком команд MS Excel;

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

Выбрать команды из:», вибираємо значення «Все команды» (рис. 19);

5) у детальному переліку команд знаходимо «

Мастер сводных таблиц и диаграмм», виділяємо його;

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

Добавить», щоб перенести іконку Майстра на панель швидкого доступу;

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

ОК». У районі кнопки «Офис» на панелі швидкого доступу з’явиться значок для виклику Майстра зведених таблиць. Він відрізняється від використовуваних нами раніше засобів роботи зі зведеними звітами тим, що містить усі режими формування зведених звітів. У тому числі й роботи з кількома діапазонами консолідації. Тепер можна розпочинати безпосередньо вирішення завдання. Виконуємо такі дії:

1) створюємо новий лист, стаємо на лівий верхній кут цього листа;

2) клацаємо по іконці виклику Майстра зведених таблиць на панелі швидкого запуску. З’явиться вікно, як показано на рис. 20;

img 21

3) у цьому вікні ставимо перемикач «

Создать таблицу на основе данных, находящихся в:» у положення «в нескольких диапазонах консолидации»;

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

Далее». Відкриється вікно другого кроку роботи з Майстром зведених таблиць. У ньому Excel запропонує два варіанти: «Создать одно поле страницы» або «Создать поля страницы» у зведеному звіті;

5) вибираємо опцію «

Создать одно поле страницы»;

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

Далее». З’явиться вікно для визначення джерела даних, як показано на рис. 21;

img 22

7) переходимо на лист «

Продажи»;

8) обводимо колонки «

A:B» на цьому листі. Ми включили до джерела даних зведення про найменування товарів та про кількість продажів (зі знаком «-»);

9) у вікні Майстра зведених таблиць натискуємо на кнопку «

Добавить»;

10) переходимо на лист «

Поступления», блок «A:B» Excel виділить автоматично;

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

Далее»;

12) на завершальному етапі Excel запропонує помістити таблицю на новий чи на існуючий лист. Вибираємо «

на существующий лист»;

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

Готово». На поточному листі з’явиться зведена таблиця. У ній тільки одна колонка із залишками та колонка із загальним підсумком щодо залишків. Значення у цих стовпцях однакові, тому підсумки краще приховати;

14) клацаємо по пункту «

Конструктор» меню роботи зі зведеними таблицями;

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

Общие итоги» у групі «Макет»;

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

Отключить для строк и столбцов». Фрагмент отриманого звіту щодо залишків ТМЦ показано на рис. 22. Усі значення в цьому звіті підраховано як різницю між надходженнями (лист «Поступления») та продажами (лист «Продажи»).

img 23

На цьому ми завершимо тему роботи зі зведеними звітами. І в нашій наступній статті почнемо працювати з діловою графікою Excel, познайомимося з особливостями її реалізації в Excel 2007.

 

Чекаю ваших листів, пропозицій та зауважень на

bk@id.factor.ua, nictomkar@rambler.ru або на форумі редакції www.bk.factor.ua/forum . Успішної роботи!
App
Завантажуйте наш мобільний додаток Factor

© Factor.Media, 1995 -
Всі права захищені

Використання матеріалів без узгодження з редакцією заборонено

Ознайомитись з договором-офертою

Приєднуйтесь
Адреса
м. Харків, 61002, вул. Сумська, 106а
Ми приймаємо
ic-privat ic-visa ic-visa

Ми використовуємо cookie-файли, щоб зробити сайт максимально зручним для вас та аналізувати використання наших продуктів та послуг, щоб збільшити якість рекламних та маркетингових активностей. Дізнатися більше про те, як ми використовуємо ці файли можна тут.

Дякуємо, що читаєте нас Увійдіть і читайте далі