Сортування та групування елементів зведених таблиць.

Форматування зведених таблиць.

Обчислення у зведених таблицях.

Робота зі зведеною таблицею.

Створення зведеної таблиці з урахуванням даних списку.

Під час створення зведеної таблиці можна використовувати одне із чотирьох типів джерел даних: список даних Microsoft Excel, зовнішнє джерело даних, кілька діапазонів консолідації (окремих списків Microsoft Excel), інша зведена таблиця.

Створимо зведену таблицю на основі списку Замовлення товарівкниги Продаж товарів(Див. рис. 4.1).

Мал. 4.1. перелік Замовлення товарів

Щоб створити зведену таблицю виходячи з даних списку, необхідно виконати такі дії.

1. Поставити покажчик комірки у місці списку, основі якого буде створюватися зведена таблиця.

2. За допомогою меню Дані, команди Зведені таблицівикликати майстер створення зведених таблиць та діаграм (див. рис. 4.2).

Мал. 4.2. Перший крок майстра зведених таблиць та діаграм

3. На першому кроці необхідно вказати тип джерела даних – Створити таблицю з урахуванням даних, які у списку чи базі данихExcel,та вид створюваного звіту – зведена таблиця.

4. На другому кроці майстра необхідно вказати абсолютну адресу діапазону осередків вихідної таблиці, для якої створюється зведена таблиця, тобто список, на основі якого створюється зведена таблиця.

Якщо перед вибором команди Зведена таблицяви виділили тільки одну комірку в цьому списку, то майстер правильно заповнить дане поле вікно діалогу.

5. На останньому кроці майстра необхідно вказати місце, де ви хочете розмістити зведену таблицю.

Найбезпечніше створювати таблицю на новому аркуші, для чого встановити перемикач «Поміщати таблицю в…»на «Новий лист». В іншому випадку цей перемикач потрібно встановити на «Існуючий лист»та вказати діапазон (або абсолютну адресу першого осередку, створюваної таблиці) поточного листа або будь-якого іншого існуючого листа.

6. Після закінчення роботи майстра на робочому аркуші відобразиться:

§ порожній макет таблиці,

§ список можливих полів зведеної таблиці,

§ панель інструментів Зведені таблиці(Див. рис. 4.3).

Мал. 4.3. Створення зведеної таблиці.

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

Області, які служать для позначення місцезнаходження полів у зведеній таблиці, називаються осями(Axis). Виділяють вісь сторінок, осі стовпців та рядків.

Якщо поля розміщуються на осі стовпців або рядків, всі елементи цих полів можна переглянути тільки за допомогою лінійки прокручування, особливо для великих списків. Елементи, розміщені на вісь сторінок, відображаються лише по одному.

У кожну область макета можна помістити будь-яку кількість полів. Щоб видалити поле, перетягніть його за межі макета.

Продовжуючи роботу з нашим прикладом (див. рис. 4.1), перетягнувши поле Країна одержувачана вісь стовпців,поле Категоріяна вісь рядківта поле Вартість замовленьв область елементів даних,отримаємо зведену таблицю, зображену малюнку 4.4.

Після того як початкова структура таблиці створена, її можна реорганізовувати виходячи з нагальної необхідності аналізу даних. Реорганізувати зведену таблицю – це означає змінити орієнтацію чи становище однієї чи кількох полів. Зміна положення таблиці використовується для перегляду даних під іншим кутом зору. В оригіналі зведена таблиця називається Pivot Table(Таблиця, що обертається). Саме можливість зміни орієнтації таблиці, наприклад, транспонування заголовків стовпців у заголовки рядків (див. рис. 4.5) і навпаки, зробили зведені таблиці потужним аналітичним засобом.

Мал. 4.4. Зведена таблиця на основі даних списку Замовлення товарів

Мал. 4.5. Транспонування поля Країна одержувачау заголовки стовпців

Переміщати поля зведеної таблиці можна перетягуванням заголовків стовпців за допомогою миші. Крім того, повністю змінити весь макет зведеної таблиці можна за допомогою команди Майстер, меню Зведена таблицяпанелі інструментів Зведені таблиціта клавішу Макет. Переміщати заголовки полів у діалоговому вікні (див. рис. 4.6) можна так само за допомогою миші.

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

Наприклад, у нашому полі Країна одержувача, розміщеному на осі рядків, клацнувши на стрілці списку, що розкривається, і знявши прапорець зліва від елементів, які потрібно приховати, можна залишити тільки необхідні для аналізу країни.

Мал. 4.6. Вікно зміни макету майстра зведеної таблиці

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

Значення поля на осі сторінок можна відобразити на окремих аркушах. Навіть якщо зведена таблиця містить вісь сторінок, вся таблиця зберігається одному листі книги. Командою Відобразити сторінкина панелі інструментів Зведені таблиці(кнопка Зведена таблиця) створює на окремих аркушах ряд пов'язаних таблиць, у кожній з яких відображатиметься один елемент поля сторінок. Кожному аркушу присвоюється ім'я елемента поля, що відображається.

Зведена таблиця не оновлюється автоматично, хоча вона пов'язана з вихідними даними. Для того, щоб оновити зведену таблицю, потрібно виділити в ній будь-яку комірку, а потім використовувати в меню Данікоманду Оновити дані(аналогічна команда є в контекстному меню або можна використовувати однойменну кнопку на панелі інструментів Зведені таблиці.

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

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

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

До числових полів, розміщених у область даних зведеної таблиці, Excel застосовує функцію Сума, а до будь-яких нечислових полів – функцію Кількість значень.

В області даних можна використовувати інші підсумкові функції, для цього необхідно зробити наступне.

1. Поставити покажчик комірки на будь-яку комірку області даних.

2. За допомогою команди Параметри поляна панелі інструментів Зведені таблиці(кнопка Зведена таблиця), відкрити діалогове вікно (Див. рис. 4.7).

3. У полі Операціявідкритого діалогового вікна вибрати потрібну функцію.

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

1. Перетягніть потрібне число разів кнопку даного поля з панелі Список полів зведеної таблиці(Див. рис. 4.3) в область даних. Якщо панель на панелі відсутня на екрані, використовується клавіша Відобразити список полівпанелі інструментів Зведені таблиці.

Мал. 4.7. Діалогове вікно Обчислення поля зведеної таблиці

2. Виділити будь-яку комірку даного поля в області даних та викликати діалогове вікно Обчислення поля зведеної таблиці(див. рис. 4.7), де вибрати необхідне поле.

3. Повторити другий пункт потрібне число разів.

Якщо використовується кілька різних підсумкових функцій чи області даних міститься кілька полів, то області даних з'являється осередок Дані(див. рис. 4.8), у згорнутому списку якої можна вибрати які саме обчислення необхідно залишити.

Мал. 4.8. Використання підсумкових функцій Сумаі Кількість
для поля Вартість замовлення

Окрім стандартних підсумкових функцій Сума, Кількість значень, Середнє, Максимум, Мінімум, твірта ін. Excel дозволяє використовувати додаткові обчислення. Наприклад, в осередку області даних зведеної таблиці можна відобразити частку від підсумку рядка або стовпця, в яких знаходиться осередок, частку від загальної суми.

Щоб використовувати додаткові обчислення, необхідно виділити будь-яку комірку в області даних, відкрити діалогове вікно Обчислення поля зведеної таблиці, в якому за допомогою кнопки Додаткововідкрити та вибрати додаткові обчислення (див. рис. 4.9).

Мал. 4.9. Використання додаткових обчислень у зведених таблицях

Excel дозволяє створювати власні обчислювані поля та елементи. Обчислюване поле – це нове поле, отримане за допомогою операцій (переважно арифметичних перетворень) над існуючими полями зведеної таблиці.

Для того щоб створити власне поле, що обчислюється, необхідно вчинити наступним чином.

1. Поставити покажчик комірки на будь-яку комірку зведеної таблиці.

2. За допомогою команди Формули– Обчислюване полена панелі інструментів Зведені таблиці(кнопка Зведена таблиця) відкрити діалогове вікно Вставка поля, що обчислюється(Див. рис. 4.10).

3. У полі Ім'яввести ім'я поля, що створюється.

4. У полі Формуласкласти за допомогою арифметичних операцій, що застосовуються до полів зведеної таблиці, формулу, що обчислюється. Поля таблиці вибираються зі списку Полеподвійним клацанням миші або клавішею Додати поле.

Для видалення поля, що обчислюється, потрібно поставити покажчик осередку в будь-яке місце зведеної таблиці; потім, викликавши діалогове вікно Вставка поля, що обчислюється(меню Формула, команда Обчислюване поле), у згорнутому списку Ім'явибрати ім'я поля, що видаляється, і натиснути клавішу видалити.

Зміна формули поля, що обчислюється, проводиться аналогічним чином, тільки після вибору імені поля, необхідно змінити формулу в рядку Формулата натиснути клавішу Змінити.

Мал. 4.10. Діалогове вікно Вставка поля, що обчислюється

Крім створення обчислюваних полів Excel дозволяє створювати обчислювані елементи вже полях області даних зведеної таблиці. Обчислюваний елемент – це новий елемент у існуючому полі, отриманий за допомогою операцій над іншими елементами поля.

Для створення обчислюваного елемента необхідно зробити так.

1. Поставити покажчик комірки на ім'я поля чи будь-який елемент цього поля на осі рядків чи осі стовпців.

2. За допомогою команди Формули– Об'єкт, що обчислюєтьсяна панелі інструментів Зведені таблиці(кнопка Зведена таблиця), відкрити діалогове вікно Вставка обчислюваного елемента в...(Див. рис. 4.11).

3. У полі Ім'явищеназваного діалогового вікна додається ім'я нового елемента, а поле Формула- Вказати формулу його розрахунку. Елементи додаються до формули подвійним клацанням миші у полі Елементиабо натисканням кнопки Додати елемент.

Видалити або змінити об'єкт, що обчислюється, можна за допомогою того ж діалогового вікна Вставка об'єкта, що обчислюється в…шляхом вибору цього об'єкта у списку Ім'ята натискання відповідної клавіші видалитиабо Змінити, потім ОК.

Мал. 4.11. Додавання обчислюваного елемента в полі Категорія

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

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

Excel пропонує понад 20 автоформатів зведених таблиць, які вибираються за допомогою команди Формат звіту, на панелі інструментів Зведені таблиці, меню Зведена таблиця.

У зведених таблицях можлива зміна числових форматів виділених осередків звичайним способом зміни числових форматів. Якщо потрібно змінити числовий формат для всього поля, слід зробити так.

1. Поставити покажчик комірки на будь-яку комірку даного поля в області даних.

2. На панелі інструментів Зведені таблиці, меню Зведена таблиця, вибрати команду Параметри поля.

3. У діалоговому вікні Обчислення поля зведеної таблиці (див. мал. 4.7) натиснути клавішу Формат.

4. У вікні, що відкрилося Формат осередківвстановити необхідний числовий формат.

Написи зовнішніх полів можна центрувати щодо написів внутрішніх полів. Наприклад, на малюнку 4.12 заголовки поля Країна Одержувачацентровані по вертикалі щодо заголовків поля Категорія, написи Австрія Підсумок, Бразилія Результат відцентровані по горизонталі. Щоб відформатувати подібним чином зведену таблицю, необхідно в діалоговому вікні Параметри зведеної таблиці(команда Параметри таблиціменю Зведена таблиця, панель інструментів Зведені таблиці), увімкнути прапорець Об'єднувати осередки заголовків.

Мал. 4.12. Центрування написів зовнішніх полів щодо
до написів внутрішніх полів

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

За умовчанням структурне виділення вимкнено, щоб використати даний спосіб форматування необхідно виконати такі дії.

1. За допомогою команди Виділити – Дозволити виділення, меню Зведена таблицяна панелі інструментів Зведені таблицізробити доступним меню структурного виділення.

2. У меню структурного виділення, що відкрилося, вибрати елементи виділення: Виділити – Тільки заголовки, Тільки дані, Дані та заголовки.

Наприклад, на малюнку 4.12 виділено дані та заголовки підсумків по країні ( Австрія Підсумок,Бразилія Підсумокі т.д.).

При використанні звичайної команди Сортуванняменю Дані Excel сортує всі екземпляри елементів поля та зберігає цей порядок сортування під час перетворення таблиці.

Наприклад, якщо у зведеній таблиці, показаній на малюнку 4.12, потрібно розмістити всі категорії товарів у зворотному алфавітному порядку, необхідно виділити будь-який елемент поля Категорія, вибрати команду Сортуванняв меню Дані, встановити перемикач За зменшенням. Після чого зміниться порядок відображення категорій товарів переважають у всіх місцях зведеної таблиці, де ці елементи зустрічаються.

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

Такий порядок сортування має перевагу перед звичайним сортуванням і називається автосортуванням. Для того, щоб використовувати автовортування, необхідно виконати такі дії.

1. Виділити комірку будь-якого елемента поля або кнопку поля, яке необхідно сортувати (у прикладі малюнка 4.13 – поле Категорія).

2. Натиснути кнопку Параметри поляна панелі інструментів Зведені таблиціабо вибрати аналогічну команду в меню Зведена таблиця.

3. У діалоговому вікні, що відкрилося. Обчислення поля зведеної таблиці(див. рис. 4.7) натиснути клавішу Додатково.

4. У вікні діалогу , показаному на малюнку 4.14, встановити перемикач за зростанням(або за зменшенням), а потім у згорнутому списку за допомогою полявибрати поле, значення якого будуть використовуватися під час автосортування.

Мал. 4.14. Установка автосортування за допомогою діалогового вікна
Додаткові параметри поля зведеної таблиці

У прикладі, що розглядається, основою для сортування поля Категорія будуть служити значення поля Сума по полю Вартість замовлення.

За допомогою цього ж діалогового вікна (див. рис. 4.14) можна відобразити лише кілька найбільших або найменших елементів поля, ґрунтуючись на значеннях в області даних зведеної таблиці. Для цього необхідно, виділивши осередок елемента потрібного поля, відкрити описаним раніше способом діалогове вікно Додаткові параметри поля зведеної таблиці, увімкнути Автовідображення найкращої десяткиі встановити кількість найбільших або найменших елементів, що відображаються.

Якщо в структурі зведеної таблиці кілька полів помістити на вісь рядків або стовпців, Excel автоматично групує елементи внутрішнього поля для кожного заголовка зовнішнього поля. Наприклад, на малюнку 4.12 елементи поля Категоріязгруповані для кожного елемента поля Країна одержувача, яким доданий проміжний результат по групі елементів внутрішнього поля.

Крім того, у разі потреби Excel дозволяє здійснювати групування:

§ вибраних елементів полів на осі рядків або стовпців;

§ числових елементів полів, розміщених на осі рядків чи стовпців;

§ за тимчасовими діапазонами поля дати, поміщеного на осі рядків або стовпців.

Для того, щоб згрупувати вибрані елементи, необхідно:

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

2) вибрати команду Групуватиу підменю Група та структура, меню Дані.

В результаті буде створено нове поле, в якому виділені елементи будуть згруповані до групи з ім'ям Група1(Див. рис. 4.15).

Мал. 4.15. Створення групи елементів Кондитерські вироби,
Напоїі Фрукти

Можна приховати елементи групи, двічі клацнувши мишею на ім'я групи ( Група1); щоб знову вивести елементи групи на екран, необхідно двічі клацнути мишею на заголовку групи ще раз. Крім того, для приховування або відображення елементів групи використовуються команди Приховати деталіі Відобразити деталіменю Дані, підміню Група та структура.

Повністю видалити з таблиці поле, що містить елементи групи, можна, перетягнувши заголовок поля межі таблиці, малюнку 4.15 це полі Категорія.

У разі потреби можна змінити ім'я групи, змінивши значення будь-якої комірки, що містить ім'я цієї групи. У наведеному прикладі малюнка 4.15 це осередок A3.

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

Мал. 4.16. Угруповання числових елементів поля

Угруповання елементів за тимчасовими діапазонами здійснюється в тому випадку, якщо поле на осі рядків або сторінок містить дані типу дата час. Такий тип даних групується аналогічно числовим елементам, тільки у виведеному на екран, в результаті застосування команди Групувати, діалоговому вікні Групування(див. мал. 4.17) необхідно вибрати часові інтервали угруповання від секунди до року.

Мал. 4.17. Угруповання поля дати

Для того щоб видалити всі групи та повернути поле до несгрупованого, виду потрібно поставити покажчик комірки на будь-який згрупований елемент і вибрати команду Розгрупуватив меню Дані, підміню Група та структура.

Існує дві можливості побудови зведених діаграм: можна побудувати діаграму по вже створеній зведеній таблиці та побудувати зведену діаграму одночасно з побудовою таблиці. У будь-якому випадку зведені таблиця та діаграма є пов'язаними об'єктами, будь-які зміни в одному об'єкті призводять до відповідних змін в іншому.

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

При побудові зведеної діаграми на основі списку необхідно, виділивши будь-яку комірку списку, викликати майстер побудови зведених таблиць та діаграм (див. рис. 4.2). На першому кроці майстра у групі Вигляд створюваного звітупоставити перемикач на полі Зведена діаграма (зі зведеною таблицею). Завдяки роботі майстра буде створено зведену діаграму та пов'язану з нею зведену таблицю. Наприклад, на малюнку 4.18 показано зведену діаграму, пов'язану з таблицею малюнка 4.4.

Крім таких елементів звичайних діаграм Microsoft Excel як ряд, значення, осі, зведені діаграми мають спеціалізовані елементи:

§ поле сторіноквикористовується для фільтрації даних за конкретним елементом, відповідає осі сторінок зведеної таблиці;

§ поле данихнадає значення для порівняння або зміни, що відповідає області даних зведеної таблиці, залежно від джерела даних, в даному полі можна змінювати підсумкову функцію;

§ поле ряду, В якому відображаються можливі ряди, елементи в полі містять окремі ряди даних, що відповідає осі стовпців зведеної таблиці.

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

Мал. 4.18. Зведена діаграма

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

Запитання для самоконтролю.

1. Дати визначення зведеної таблиці.

2. Пояснити терміни, що використовуються при створенні зведених таблиць: вихідні дані, підсумкова функція, елемент поля, вісь рядків, вісь стовпців, вісь сторінок, область даних.

3. Особливості роботи з елементами поля, розміщеного на вісь сторінок.

4. Що розуміють під реорганізацією зведеної таблиці, які методи її здійснення?

5. Оновлення зведеної таблиці.

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

7. Зміна підсумкових функцій полів, розміщених у область даних.

8. Створення обчислюваних полів та обчислюваних елементів.

9. Особливості форматування зведених таблиць.

10. Перерахувати елементи структурного виділення зведених таблиць.

11. Автосортування елементів поля зведеної таблиці.

12. Відображення кількох найбільших чи найменших елементів поля зведеної таблиці.

13. Угруповання вибраних елементів полів на осі рядків або осі стовпців.

14. Угруповання полів зведених таблиць за часовими діапазонами.

15. Особливості побудови зведених діаграм.

16. Спеціальні елементи зведених діаграм та їх відповідність елементам зв'язаних зведених таблиць.

Запитання та завдання для самостійної роботи.

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

2. Розмістити поле Країна одержувачав область рядків, а поле Категорія- В область стовпців.

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

4. Розмістити інформацію про замовлення різних категорій товарів на окремих аркушах книги.

5. До списку Замовлення товарівкниги Продаж товарівдодати 20 000 000 на перше замовлення по кондитерським виробам, замовленим в Австрію.

Поверніться до зведеної таблиці та оновіть дані. Перевірте зміни.

Видалити 20 000 000, додані на перше замовлення в Австрію (кондитерські вироби) і знову оновити зведену таблицю.

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

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

8. Використовуючи згорнутий список Дані,відобразити тільки суму по полю Вартість замовлень.

9. Додати до зведеної таблиці обчислюване поле Вартість зі знижкою, що розраховується зменшенням сумарної вартості на 15%.

11. Для одержаної зведеної таблиці встановити, використовуючи команди автоформату, формат Таблиця 10 , а потім Класична зведена таблиця.

12. Додати до існуючої зведеної таблиці поле, в якому підраховується загальна вартість товару Ціна*Кількість*(1–Знижка) . Встановити для цього поля грошовий формат, грн, тільки цілу частину. Сума за кількістю одиниць товарувидалити з області даних.

13. За списком Продаж товарівстворити зведену таблицю, помістивши на вісь рядків поле Країна одержувача, на вісь стовпців – поле Дата розміщення замовленьв область даних – поле Вартість замовлення. Елементи поля Дата розміщення замовленьзгрупувати за роками та кварталами.

14. За зведеною таблицею попереднього завдання створити діаграму.

15. Не створюючи зведеної таблиці, створити за списком Продаж товарівдіаграму, в якій відобразити залежність кількості замовлень, що надходять із різних країн, від місяця та року надходження замовлення.

16. У створеній діаграмі перетягнути поле Дата розміщення замовленняу полі сторінок та переглянути залежності за окремими місяцями та роками.

Завдання лабораторної роботи 4.1.

1. Скопіювати до себе в папку файл Лабораторна робота 4. Усі наступні завдання лабораторної роботи виконати на підставі списку Відправлення товарівкниги файлу Лабораторна робота 4.xls, у кожному завданні створюючи незалежний звіт.

2. На новому аркуші, з ім'ям Зведена таблиця_1, Створити зведену таблицю, що має структуру аналогічну підсумковій таблиці четвертого завдання лабораторної роботи 3.1.

3. На кількох аркушах книги створити зведені таблиці, які б показували залежність замовленої кількості одиниць товару з посади співробітника, працюючого із замовленням, і зажадав від категорії товару. Різні способи доставки товару розмістити на окремі аркуші книги. Створити незалежний звіт, а чи не звіт на основі існуючого.

4. Аркуш, на якому відображена зведена таблиця за всіма способами доставки, перейменувати в Усі способи доставки.

5. У зведену таблицю, розташовану на аркуші Усі способи доставки, додайте поле, в якому рахується кількість замовлень по кожній категорії товару з посади співробітника. Назвати нове поле Кількість замовлень.

6. У цю саму зведену таблицю додати поле, у якому зазначатиметься частка кількості замовлень за цією категорією товарів від загальної кількості замовлень, прийнятих співробітником певної посади. Поле назвати Відсоток кількості замовлень.

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

8. На новому аркуші Залежністьстворити зведену таблицю, що відображатиме залежність кількості замовлень від категорії товарів та міста поставки. Поле в області даних перейменувати в Кількість замовлень.

9. У полі Категорія товарівдодати обчислюваний елемент Вегетаріанська продукція, в якому буде підраховано кількість замовлень по всіх категоріях товарів, крім Рибопродуктів, Молокопродуктів, М'яса та птиці.

Завдання лабораторної роботи 4.2.

1. Використовуючи список у файлі Лабораторна робота 4_2.xls, створити зведену діаграму, що показує залежність середньої ціни товару, що поставляється від його категорії та міста поставки . Показати в діаграмі дані тільки на замовлення, доставлені авіа.

2. Лист, у якому створилася пов'язана з діаграмою зведена таблиця, перейменувати в Зведена таблиця_1.

3. Використовуючи дані листа Зведена таблиця_1,Вивести на окремий лист детальну інформацію про ціни на рибопродукти, спрямовані в Новий Орлеан авіа.

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

5. Створити нову зведену таблицю (незалежний звіт), яка аналізує залежність сумарної кількості одиниць товарів від категорії товарів та посади співробітника.

6. Аркуш перейменувати на Менеджери.

7. Усіх менеджерів виділити в окрему групу Менеджери. Написи зовнішніх полів центрувати щодо внутрішніх полів.

8. Видалити з таблиці поле, яке містить елементи групи Менеджери.

9. Створити зведену діаграму за даними зведеної таблиці, розташованої на сторінці Менеджери.

10. На новому аркуші Кварталистворити зведену діаграму (створювати діаграму не на основі існуючого звіту, а незалежну), що показуватиме кількість замовлень на кожну категорію товару по квартально, у діаграмі показати лише інформацію 2003 року.

Дякуємо Вам за виявлений інтерес до нашого сайту. Компанія Айтішнік існує з 2006 року і надає послуги IT аутсорсингу. Аутсорсинг - це доручення необхідних, але непрофільних для компанії робіт іншої організації. У нашому випадку це: створення, підтримка та супровід сайтів, просування сайтів у пошукових системах, підтримка та адміністрування серверів під керуванням Debian GNU/Linux.

Сайти на Joomla

В даний час інформації, сайт де факто, стає як мінімум візитною карткою організації, а найчастіше одним з інструментів бізнесу. Вже зараз сайти створюються не лише для організацій та приватних осіб, а й для окремих товарів, послуг та навіть подій. На сьогоднішній день сайт це не лише джерело реклами на гігантську аудиторію, а й інструмент для продажу та зав'язування нових контактів. Ми створюємо сайти за допомогою CMS Joomla! Ця система управління сайтами проста та інтуїтивно зрозуміла. Вона дуже поширена і, отже, в Інтернеті про неї міститься велика кількість інформації. Знайти спеціаліста, що працює з Joomla, теж нескладно. І вам не треба далеко ходити! Наша компанія Айтішнік займається обслуговуванням та супроводом сайтів на Joomla! Ми проведемо всі технічні роботи, візьмемо на себе все листування з хостером та реєстратором домену, наповнимо сайт та оновимо на ньому інформацію. І хоча Joomla проста в управлінні, інтуїтивно зрозуміла. Але чи будете ви регулярно виконувати необхідні роботи на сайті? Скільки часу вони заберуть у вас? Якщо ви хочете сконцентруватися на своїй справі, довірте підтримку вашого сайту нам. Ми зробимо все, що від нас залежить, щоб сайт жив і приносив користь своєму власнику.
Якщо ви комерційна організація, яка рекламує або продає свої товари, послуги в Інтернеті, то вам просто необхідне просування сайту в пошукових системах. Адже для того, щоб продати щось треба, як мінімум, щоби це побачили, щоб про це дізналися. І ми допоможемо вам у цьому, ми просунемо ваш Joomla сайт у пошукових системах. Залежно від конкуренції та виділеного для просування бюджету, ваш сайт займатиме гідні позиції у пошуковій видачі. Сайт збільшить ваш прибуток!

Сервери Debian

Рано чи пізно, прагнучи відкритості та прозорості свого бізнесу, багато компаній стикаються з необхідністю забезпечення ліцензійної чистоти використовуваного програмного забезпечення. Однак, далеко не завжди витрати на ліцензійні відрахування є прийнятними, особливо для малого та середнього бізнесу. Виходом із цієї складної ситуації є рішення про перехід на Open Source технології. Одним із напрямків Open Source є операційна система Linux (Лінукс). Співробітники нашої компанії спеціалізуються на Debian Linux (Дебіан Лінукс). Це найстаріший і найстійкіший дистрибутив операційної системи Лінукс. Ми пропонуємо вам послуги з впровадження Debian Linux на Вашому підприємстві, настроювання, обслуговування та підтримку серверів.

Інформація та реклама

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

Для того, щоб побудувати подібний об'єкт, необхідно виконати такі дії.

1. Створіть якусь таблицю.

1. Виділіть інформацію, на основі якої ви маєте намір побудувати діаграму.

1. Перейдіть на вкладку "Вставка". Клацніть на іконку «Рекомендовані діаграми».

1. Після цього ви побачите вікно "Вставка діаграми". Запропоновані варіанти залежатимуть від того, що саме ви виділите (перед натисканням на кнопку). Вони можуть бути інші, оскільки все залежить від інформації в таблиці.

1. Щоб побудувати діаграму, виберіть будь-яку з них та натисніть на «OK».

1. В даному випадку об'єкт виглядатиме ось так.

Ручний вибір типу діаграми

1. Виділіть потрібні дані для аналізу.

1. Потім клацніть на якусь іконку із зазначеної області.

1. Відразу після цього розкриється список різних типів об'єктів.

1. Натиснувши на будь-який з них, ви отримаєте бажану діаграму.

Для того, щоб було легше визначитися з вибором, достатньо навести на будь-яку з мініатюр.

Які бувають діаграми

Можна виділити кілька основних категорій:

• гістограми;

• графік чи діаграма з областями;

• кругові або кільцеві діаграми;

Цей тип підходить для тих випадків, коли всі значення в сумі дають 100 відсотків.

• ієрархічна діаграма;

• статистична діаграма;

• точковий або пухирцевий графік;

У разі крапка є своєрідним маркером.

• каскадна чи біржова діаграма;

• комбінована діаграма;

Якщо нічого із вище запропонованого вам не підходить, можна використовувати комбіновані варіанти.

• поверхнева або пелюсткова;

Як зробити зведену діаграму

Даний інструмент складніший у порівнянні з описаними вище. Раніше все відбувалося автоматично. Вам потрібно було лише вибрати зовнішній вигляд та бажаний тип. Тут все по-іншому. На цей раз доведеться все робити вручну.

1. Виділіть потрібні комірки у таблиці та клацніть на відповідну іконку.

1. Відразу після цього з'явиться вікно "Створити зведену діаграму". Необхідно вказати:
   • таблицю чи діапазон значень;
   • місце, де слід розмістити об'єкт (новий чи поточний лист).
2. Щоб продовжити, натисніть кнопку «OK».

1. В результаті цього ви побачите:
   • порожню зведену таблицю;
   • порожню діаграму;
   • поля зведеної діаграми.

1. Необхідно перенести мишкою бажані поля в області (на власний розсуд):
   • легенди;
   • значень.

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

1. В результаті цього з'явиться вікно "Параметри поля значень". Тут ви зможете:
   • підписати джерело своїм маєтком;
   • вибрати операцію, яку потрібно використовувати для зведення даних у вибраному полі.

Щоб зберегти, натисніть кнопку «OK».

Вкладка «Аналізувати»

Після того, як ви створите зведену діаграму, у вас відкриється нова вкладка "Аналізувати". Вона відразу зникне, якщо активним стане інший об'єкт. Для повернення достатньо знову натиснути на діаграму.

Розглянемо кожен розділ уважніше, оскільки з їх можна змінити всі елементи до невпізнанності.

Параметри зведеної таблиці

1. Натисніть на першу іконку.
2. Виберіть «Параметри».

1. Завдяки цьому з'явиться вікно налаштувань даного об'єкта. Тут ви зможете задати бажане ім'я таблиці та безліч інших параметрів.

Для збереження налаштувань необхідно натиснути кнопку «OK».

Як змінити активне поле

Якщо ви натисніть на цю іконку, то побачите, що всі інструменти не активні.

Для того, щоб можна було змінити якийсь елемент, потрібно зробити наступне.

1. Клацніть на щось на вашій діаграмі.

1. В результаті цього поле виділиться «кружечками».
2. Якщо ви знову клацніть на іконку «Активне поле», то побачите, що інструменти стали активними.

1. Для налаштувань потрібно натиснути на відповідне поле.

1. Внаслідок цього з'явиться вікно «Параметри поля».

1. Для додаткових параметрів перейдіть на вкладку «Розмітка та друк».

1. Щоб зберегти зміни, необхідно натиснути на кнопку «OK».

Як вставити зріз

За бажанням ви можете налаштувати вибірку за певними значеннями. Завдяки цій функції дуже зручно аналізувати дані. Особливо, якщо таблиця дуже велика. Для того, щоб скористатися цим інструментом, потрібно зробити наступні кроки:

1. Клацніть на кнопку «Вставити зріз».
2. Внаслідок цього з'явиться вікно зі списком полів, які є у зведеній таблиці.

1. Виберіть поле і натисніть кнопку «OK».

1. В результаті цього з'явиться маленьке віконце (його можна переміщати в будь-яке зручне місце) з усіма унікальними значеннями (підсумків відомості) цієї таблиці.

1. Якщо ви натиснете на якийсь рядок, то побачите, що всі інші записи в таблиці зникли. Залишилося лише те, де середнє значення відповідає вибраному.

Тобто за замовчуванням (коли у вікні зрізів усі рядки виділені блакитним кольором) у таблиці відображаються всі значення.

1. Якщо натиснути на іншу цифру, то результат відразу ж зміниться.

1. Кількість рядків може бути абсолютно будь-якою (мінімум одна).

Змінюватиметься як зведена таблиця, і діаграма, побудована за її значенням.

1. Якщо хочете видалити зріз, потрібно клікнути на хрестик у верхньому правому кутку.

1. Завдяки цьому таблиця відновиться до вихідного вигляду.

Для того, щоб прибрати це вікно зрізів, необхідно зробити кілька нескладних кроків:

1. Клацніть правою кнопкою миші по даному елементу.
2. Після цього з'явиться контекстне меню, в якому потрібно вибрати пункт «Видалити назву поля».

1. Результат буде наступним. Зверніть увагу, що в правій частині редактора знову з'явилася панель для налаштування полів зведеної таблиці.

Як вставити тимчасову шкалу

Для того, щоб вставити зріз за датою, потрібно зробити наступні кроки.

1. Клацніть на відповідну кнопку.

1. У нашому випадку побачимо наступне вікно з помилкою.

Справа в тому, що для зрізу за датою в таблиці мають бути відповідні значення.

Принцип роботи є повністю ідентичним. Просто ви фільтруватимете виведення записів не за цифрами, а за датами.

Як оновити дані у діаграмі

Для оновлення інформації в таблиці потрібно натиснути на відповідну кнопку.

Як змінити інформацію для побудови

Для редагування діапазону осередків у таблиці необхідно виконати такі операції:

1. Клацніть на іконку «Джерело даних».
2. У меню виберіть однойменний пункт.

1. Далі вас попросять вказати потрібні осередки.
2. Щоб зберегти зміни, натисніть «OK».

Редагування діаграми

Якщо ви працюєте з діаграмою (неважливо якою – звичайною чи зведеною), у вас буде з'являтися вкладка «Конструктор».

На цій панелі багато інструментів. Розглянемо кожен із них уважніше.

Додати елемент

За бажання ви завжди можете додати якийсь об'єкт, який відсутній у даному шаблоні діаграми. Для цього необхідно:

1. Натиснути на піктограму "Додати елемент діаграми".
2. Вибрати бажаний об'єкт.

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

Якщо стандартний шаблон при створенні діаграми вам не сподобався, ви можете використовувати інші варіанти макетів. Для цього достатньо виконати такі дії.

1. Клацніть на відповідну іконку.
2. Виберіть потрібний макет.

Необов'язково відразу вносити зміни до вашого об'єкта. Під час наведення на будь-яку іконку буде доступний попередній перегляд.

Якщо знайдете щось підходяще, просто натисніть на цей шаблон. Зовнішній вигляд автоматично зміниться.

Щоб змінити колір елементів, необхідно виконати такі кроки.

1. Натисніть відповідну іконку.
2. Внаслідок цього ви побачите величезну палітру різних відтінків.

1. Якщо хочете подивитися, як це виглядатиме на вашій діаграмі, просто наведіть курсор на будь-який з кольорів.

1. Для збереження змін потрібно клацнути на вибраний відтінок.

Окрім цього, можна використовувати готові теми оформлення. Для цього потрібно зробити кілька простих операцій.

1. Розкрийте повний список варіантів цього інструменту.

1. Щоб побачити, як це виглядає у збільшеному вигляді, достатньо навести курсор на будь-яку з іконок.

1. Для збереження змін необхідно натиснути на вибраний варіант.

Крім цього, доступні маніпуляції з інформацією, що відображається. Наприклад, можна поміняти рядки та стовпці місцями.

Після натискання цієї кнопки ви побачите, що діаграма стала виглядати зовсім інакше.

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

Якщо натиснути ще раз, все повернеться назад.

Щоб змінити діапазон даних у таблиці для побудови діаграми, потрібно клікнути на іконку «Вибрати дані». У цьому вікні ви зможете

• вибрати потрібні осередки;
• видалити, змінити чи додати ряди;
• редагувати підписи горизонтальної осі.

Щоб зберегти зміни, потрібно натиснути на кнопку «OK».

Як змінити тип діаграми

1. Натиснути на вказану іконку.
2. У вікні вибрати потрібний вам шаблон.

1. При виборі будь-якого з пунктів у лівій частині екрана, праворуч з'являтимуться можливі варіанти для побудови діаграми.

1. Для спрощення вибору можна навести курсор на будь-яку мініатюру. В результаті цього ви побачите її у збільшеному розмірі.

1. Для зміни типу потрібно натиснути на будь-який з варіантів і зберегти за допомогою кнопки «OK».

Висновок

У цій статті ми крок за кроком розглянули технологію побудови діаграм у редакторі Excel. Крім цього, була особлива увага приділена оформленню та редагуванню створених об'єктів, оскільки недостатньо вміти використовувати лише готові варіанти від розробників Microsoft. Ви повинні вчитися змінювати зовнішній вигляд під свої потреби та бути оригінальними.

Якщо у вас щось не виходить, можливо ви виділяєте не той елемент. Необхідно враховувати, що з кожної фігури використовуються свої унікальні властивості. Якщо ви змогли щось змінити, наприклад, з колом, то з текстом зробити те саме не вийде.

Відеоінструкція

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

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

Завантажити нотатку у форматі або , приклади у форматі

При створенні стандартної діаграми на основі звичайних даних (не зведеної таблиці), ви підставляєте діаграму діапазон осередків, що містять окремі значення. Кожен осередок є індивідуальним об'єктом із власним значенням. Діаграма представляє кожну комірку у вигляді точки на графіку і виводить їх усі окремо. У той же час дані в зведеній таблиці відносяться до єдиного великого об'єкта. Значення, що відображаються в зведеній таблиці, не становлять незалежні дані, що займають окремі осередки, - це елементи більшого об'єкта зведеної таблиці, що зберігається на робочому аркуші.

Під час створення діаграми для зведеної таблиці ви заповнюєте її окремими фрагментами даних, представленими окремими осередками. Натомість ви використовуєте для побудови діаграми весь макет зведеної таблиці. Зведена діаграма - це діаграма, яка в якості вихідних даних використовує об'єкт PivotLayout зведеної таблиці. Використання об'єкта PivotLayout дозволяє в інтерактивному режимі додавати, видаляти, фільтрувати та оновлювати поля даних усередині зведеної діаграми так само, як це робиться в зведеній таблиці. Внаслідок виконання всіх цих дій ви отримаєте інтерактивне графічне представлення даних зведеної таблиці.

Створення зведених діаграм

Щоб ознайомитись з методикою створення діаграми для простої зведеної таблиці, зверніть увагу на зведену таблицю, показану на рис. 1. Вона містить інформацію про суму продажу кожному за ринку збуту. Поле Бізнес-сегментзнаходиться в області фільтра звіту, що дозволяє аналізувати дані щодо ділових напрямків компанії.

Створення діаграми на основі використовуваного набору даних дозволить ідентифікувати дохід компанії, отриманий від різних ринків збуту, але при цьому на діаграмі можна буде проаналізувати внесок різних бізнес-сегментів у дохід компанії. Для початку розташуйте покажчик у будь-якому місці зведеної таблиці та перейдіть на вкладку стрічки Вставка. В групі Діаграмиознайомтеся з типами діаграм, які можна створювати на основі даних зведеної таблиці, а потім виберіть найбільш підходящий для вас. Наприклад, клацніть на кнопці Гістограмата виберіть перший (двовимірний) варіант гістограми (рис. 2).

Як бачимо на рис. 3, після вибору типу діаграми вона з'явиться у вікні програми.

Зверніть увагу: щойно створена зведена діаграма розташовується на тому ж аркуші, що і вихідна зведена таблиця. Якщо вам більше подобається, щоб зведена діаграма і вихідна таблиця знаходилися на різних аркушах, вчиніть наступним чином: встановіть курсор у зведеній таблиці і натисніть . Тим самим ви вкажете програмі додати зведену діаграму новий лист. Щоб змінити розташування раніше створеної зведеної діаграми, клацніть правою кнопкою миші (в області побудови) і в контекстному меню виберіть команду Перемістити діаграму. На екрані з'явиться діалогове вікно, де потрібно вказати нове розташування об'єкта діаграми.

На діаграмі з'являються кнопки полів зведеної таблиці (див. рис. 3). Ці кнопки розташовані на зведеній діаграмі, пофарбовані в сірий колір і забезпечені списками, що розкриваються. З допомогою цих кнопок можна переупорядкувати діаграму чи застосувати фільтри до базової зведеної таблиці. Кнопки полів зведеної таблиці відображаються під час друку зведеної таблиці. Якщо потрібно приховати кнопки, що відображаються в області зведеної діаграми, просто видаліть їх. Клацніть на зведеній діаграмі та перейдіть на контекстну вкладку Аналізувати. Клацніть на кнопці меню, що розкривається Кнопки полів, щоб приховати деякі або всі кнопки полів зведеної діаграми (рис. 4).

Мал. 4. Натисніть кнопку Приховати все, щоб кнопки полів не псували вигляд діаграми під час друку

Також кнопки полів зведеної діаграми можна прибрати, натиснувши на одній з них правою кнопкою миші і вибравши в контекстному меню Сховати усі кнопки полів на діаграмі(Рис. 5).

Ви отримали діаграму, яка є візуальним поданням числових даних зведеної таблиці. Більше того, оскільки така діаграма ґрунтується на зведеній таблиці, будь-які зміни у зведеній діаграмі негайно викличуть відповідні зміни у самій зведеній таблиці і навпаки. Наприклад, на рис. 6 показано, що після додавання до зведеної таблиці поля Регіонна діаграмі відображатиметься ще один вимір, що відображатиме розподіл обсягу продажів по регіонах. Зверніть увагу: на діаграмі, показаній на рис. 6, не виводяться проміжні результати. При створенні зведених діаграм Excel повністю ігнорує комірки з будь-якими підсумковими даними. Якщо як фільтр поля сторінки вибрати Бізнес-сегмент, він буде застосовуватися як до зведеної таблиці, а й до зведеної діаграмі. Подібна поведінка програми ґрунтується на тому, що зведена діаграма та зведена таблиця створюються на основі даних, взятих з одного кешу. Таким чином, якщо ви додасте в джерело даних нову інформацію, а потім оновите зведену таблицю, зведена діаграма буде оновлена ​​автоматично.

Можливість створення зведених діаграм надасть у ваше розпорядження високопродуктивні інтерактивні засоби виведення звітів, які ви зможете використовувати самостійно, не маючи навичок професійного програміста. Вам не обов'язково створювати зведену діаграму на основі зведеної таблиці. Можна побудувати зведену діаграму з урахуванням вихідних даних. Клацніть на будь-який з осередків таблиці з вихідними даними та перейдіть на вкладку Вставка. В групі Діаграмиклацніть на кнопці Зведена діаграма.На екрані з'явиться діалогове вікно створення зведеної діаграми. У ньому задаються самі параметри, як і під час створення зведеної таблиці.

Правила роботи зі зведеними діаграмами

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

У зведеній діаграмі виводяться далеко ще не всі поля зведеної таблиці.Одна з найпоширеніших помилок користувачів, які створюють зведені таблиці та діаграми, полягає у припущенні, що поля з області стовпців виводяться на зведеній діаграмі вздовж осі X. Зокрема, таблиця, показана на рис. 7 має дуже зручний для аналізу даних формат. В область стовпців таблиці розміщено поле Фіскальний період, а в область рядків - поле Регіон. Подібна структура даних найкраще підходить для роботи зі зведеними таблицями.

Припустимо, необхідно створити на основі зведеної таблиці діаграму. Ви інтуїтивно уявляєте, що вздовж осі X відкладатимуться фіскальні періоди, а бізнес-сегменти вказуватимуться вздовж осі Y. Але, як показано на рис. 8, в отриманій зведеній діаграмі вздовж осі X відкладаються значення поля Регіон, а вздовж осі Y - значення поля Фіскальний період.

То чому структура зведеної таблиці не зберігається при поданні її у вигляді зведеної діаграми? Та тому, що області у зведеній діаграмі залежать від строго визначених програмою областей зведеної таблиці:

• Вісь Y.Відповідає області стовпців зведеної таблиці та утворює вертикальну вісь зведеної діаграми.
• Ось X.Відповідає області рядків зведеної таблиці та утворює горизонтальну вісь зведеної діаграми.

Взявши до уваги цю інформацію, погляньте ще раз на рис. 7. У зведеній діаграмі поле Фіскальний періодбуде виводитися вздовж осі Y, оскільки знаходиться в області стовпців. Водночас поле Регіонбуде розташовуватись на осі X, так як воно знаходиться в області рядків. Тепер припустимо, що в області рядків зведеної таблиці є фіскальні періоди, а регіони представлені в області стовпців. Зміна структури призведе до створення нової зведеної діаграми (рис. 9).

Обмеження на форматування зведеної діаграми Excel 2013.У застарілих версіях Excel (попередніх Excel 2007) користувачі уникали застосування зведених діаграм, оскільки вони мали багато обмежень, пов'язаних із форматуванням. Неможливо переміщувати або змінювати ключові елементи зведеної діаграми, а в разі зміни вихідної зведеної таблиці втрачалося форматування діаграми. Також неможливо було застосовувати певні типи діаграм. Через перелічені обмеження багато користувачів ставилися до зведених діаграм як до громіздкого і непрактичного інструменту.

Починаючи з версії Excel 2007, можливості зведених діаграм значно розширені. Тепер користувачі можуть форматувати практично будь-який компонент. На додаток до цього зведені діаграми Excel 2007 перестали втрачати форматування при зміні зведених таблиць, на яких вони засновані. Більше того, як ви вже знаєте, зведені діаграми тепер розташовуються на тому самому робочому аркуші, що й вихідна зведена таблиця. У версії Excel 2013 з'явилися додаткові покращення (кнопки полів та зрізи).

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

• все ще неможливо створювати на основі зведених таблиць точкові графіки, пухирцеві та біржові діаграми;
• лінії трендів, що відстежуються, виявляються загубленими при видаленні або додаванні полів вихідної зведеної таблиці;
• не допускається зміна розмірів підписів даних у зведених діаграмах.

Альтернатива зведеним діаграмам

Існують дві причини, через які потрібно мати альтернативу зведеним діаграмам. По-перше, ви не хочете мати справу з додатковими витратами, пов'язаними із застосуванням зведених діаграм. По-друге, ви хочете уникнути певних обмежень форматування, властивих зведеним діаграм. Зведені таблиці іноді застосовуються лише для підбиття підсумків та перегляду даних у процесі підготовки діаграми. У таких ситуаціях вам не потрібно зберігати вихідні дані та виділяти пам'ять та місце на жорсткому диску для кешу зведених таблиць. У прикладі на рис. 10 показано зведену таблицю, в якій наведено квартальні звіти з кожної послуги.

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

Спосіб 1. Перетворення зведеної таблиці на статичні значення.Після створення зведеної таблиці та налаштування її структури виділіть усю таблицю та скопіюйте її в буфер обміну (для виділення всієї таблиці можна скористатися клавіатурним скороченням Ctrl+Shift+*). Перейдіть на вкладку Головнаі клацніть на кнопці Вставка, а потім виберіть у розкривному меню команду Вставити значення. Тим самим ви видаляєте зведену таблицю і замініть її статичними значеннями, отриманими з урахуванням останнього стану зведеної таблиці. Отримані значення стають основою створюваної згодом діаграми. Ця методика застосовується видалення інтерактивних елементів зведеної таблиці. Таким чином, ви перетворюєте зведену таблицю на стандартну таблицю і створите не зведену діаграму, а звичайну діаграму, що не піддається фільтрації та реорганізації. Це зауваження стосується також способів 2 та 3.

Спосіб 2. Видалення вихідної зведеної таблиці.Якщо ви вже створили зведену діаграму, то можете перетворити її на звичайну діаграму. І тому досить видалити вихідну зведену таблицю, де базується зведена діаграма. Виділіть всю зведену таблицю та натисніть клавішу . Не забувайте, що цей спосіб, на відміну від попереднього, не дозволяє працювати навіть із вихідними статичними значеннями, на основі яких будується зведена діаграма. Іншими словами, якщо у вас попросять показати вихідні дані діаграми, ви не зможете цього зробити. Якщо ви стикаєтеся з ситуацією, в якій діаграма не має вихідних даних, відобразіть таблицю даних діаграми. Таблиця даних дозволяє ознайомитися з даними, що відображаються на самій діаграмі.

Спосіб 3. Створення малюнка на основі зведеної таблиці.Багатьом може здатися дивною сама ідея представляти зведену таблицю як малюнка, але це цілком адекватний спосіб передачі аналізованих даних, який вимагає застосування спеціальних заходів захисту. На додаток до невеликого розміру кінцевого файлу, ви завжди маєте можливість повністю контролювати те, яку інформацію користувач зможе побачити на екрані. Щоб скористатися цим способом, скопіюйте всю зведену таблицю в буфер обміну. Відкрийте нову робочу книгу, клацніть правою кнопкою миші в будь-якому її місці і виберіть команду Спеціальна вставка. Вкажіть необхідний формат даних, що вставляються (наприклад, Малюнок). На робочий лист вставляється моментальна копія зведеної діаграми.

Спосіб 4. Використання осередків, пов'язаних зі зведеною таблицею, як джерело даних зведеної діаграми.Багато користувачів Excel, далекі від створення зведених діаграм, побоюються обмежень, які накладаються на засоби форматування діаграм та управління даними, що містяться у цих діаграмах. Дуже часто користувачі відмовляються від застосування зведених таблиць, оскільки не знають, як можна обійти обмеження. Проте, якщо вам потрібно зберегти можливість фільтрації даних і створювати списки «першої десятки», можете зв'язати стандартну діаграму зі зведеною таблицею, не вдаючись до створення самої зведеної таблиці.

У зведеній таблиці, показаній на рис. 11, представлені 10 кращих ринків збуту, визначених за показником Період продажу (у годинах). Зверніть увагу, що область фільтра звіту дозволяє фільтрувати дані за напрямками діяльності.

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

Припустимо, що вам потрібно представити показані дані у вигляді графіка, щоб показати взаємозв'язок між часом виконання робіт та доходом. У цьому необхідно зберегти можливість фільтрації першої десятки ринків збуту. На жаль, зведена діаграма в даному випадку не застосовується. У випадку уявити зведену таблицю як графіка не можна. Способи 1–3 також не підходять, оскільки втрачаються будь-які інтерактивні елементи. Яке ж рішення? Скористайтеся осередками навколо зведеної таблиці, щоб налаштувати зв'язок між вихідними даними та діаграмою, що створюється на їх основі. Іншими словами, потрібно створити блок даних, який буде джерелом інформації для звичайної діаграми. Цей блок даних пов'язані з елементами зведеної таблиці. Це означає, що й зведена таблиця змінюється, то змінюється і вихідний блок даних діаграми.

Встановіть курсор у комірці, розташованій поруч зі зведеною таблицею, як показано на рис. 12. Задайте посилання для першого елемента даних, вказавши в ній діапазон, який буде використовуватися в стандартній діаграмі (якщо при спробі послатися на комірку зведеної таблиці у вас у формулі «вилазить» функція ОТРИМАТИ.ДАНІ.ЗВІДНОЇ.ТАБЛИЦІ, то ви не зможете протягнути» формулу (щоб подолати цю скруту ознайомтеся із заміткою ).

Мал. 12. Почніть зв'язування блоку даних із даними зведеної таблиці з першого елемента, який має потрапити на діаграму

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

Створивши блок даних із посиланнями на зведену таблицю, можна створити йому звичайну діаграму. У прикладі на основі даного набору була створена точкова діаграма. Використовуючи зведені діаграми, ви не отримаєте нічого подібного. На рис. 14 показаний результат застосування описаної методики. Ви можете фільтрувати дані за напрямками діяльності, для чого застосовується поле ФІЛЬТРИ, але зберігаєте можливість довільного форматування стандартної діаграми, не обмеженої рамками зведеної таблиці. Спробуйте вибрати один чи інший бізнес-сегмент та подивитися, як зміниться діаграма.

Мал. 14. Ця методика дозволяє застосовувати функціональні засоби зведеної таблиці, але зберігає можливість довільного форматування діаграми

Нотатка написана на основі книги Джелен, Александер. . Розділ 6.

У цій статті ми розглянемо методи створення та використання зведеної діаграми.

Зведена діаграма - це діаграма, яка автоматично об'єднує та узагальнює великі масиви даних. Побудувати зведену діаграму можна на основі простої, і зведеної таблиці.

Першим ми розглянемо спосіб створення зведеної діаграми на основі зведеної таблиці. Для практичного тренування можна скористатися нашим прикладом, завантаживши його за посиланням (проста таблиця.xlsx).

Для створення зведеної таблиці на підставі даних вже готової діаграми виконайте такі дії:

1. Виберіть необхідну Вам зведену таблицю, клацнувши по ній;

2. На вкладці Вставкав групі Діаграмивиберіть потрібний тип діаграми.

Ми вибрали простий лінійний графік. В результаті з'явився готовий графік, що містить дані зведеної таблиці, а також вікно:

Зверніть увагу, вікно Область фільтра зведеної таблиціне дозволяє змінити умови побудови діаграми - тобто, не можна побудувати графік за стовпцями основної таблиці (наприклад - по стовпцю Об'єм продажів, шт.), які включені до зведену таблицю. І навпаки - включення даних у зведену таблицю одночасно відбивається на зведеній діаграмі:

Вікно Область фільтра зведеної таблиціпризначено для зручного управління зведеною таблицею та діаграмою, побудованою на її основі:

Змінюючи значення фільтрів і полів осі, можна відображати сегмент даних, що цікавить, для певних значень осей координат. Наприклад, дуже зручно аналізувати дані про продаж в окремих магазинах за останній тиждень:

За умовчанням, зведена діаграма створюється тому ж аркуші, де знаходиться зведена таблиця. Це не завжди зручно, тому Ви можете перемістити зведену діаграму на новий аркуш за допомогою команди Перемістити діаграмуіз контекстного меню. Налаштування формату зведеної діаграми проводиться так само, як і звичайної, але з використанням команд із групи вкладок Робота зі зведеними діаграмами, які відкриваються після натискання на зведеній діаграмі.

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

1. Виділити необхідний діапазон даних (чи встановити курсор на потрібну нам таблицю - тоді Excel автоматично підставить всю таблицю в діапазон даних);

2. На вкладці Вставкав групі Таблицівибрати розділ Зведена таблиця, а потім пункт Зведена діаграма.