Створення зведеної таблиці
В Excel єдиним засобом створення зведених таблиць є засіб “Майстер зведених таблиць”(Pivot Table Wizard). Доступ до нього — через команду “ДаніðЗведена таблиця”(Data ð Pivot Table Report) або за відповідною кнопкою панелі інструментів “Зведена таблиця”. В результаті виконання команди з’явиться перше з діалогових вікон (рис. 4), в якому необхідно визначити вид джерела даних.
Можливими видами джерел вихідних даних можуть бути:
* Списки або бази даних Excel.В першому рядку повинні бути заголовки стовпчиків. Максимальний розмір для Excel 97— 65 535 записів ´ 256 полів.
* Зовнішні джерела даних. Звичайно дані вводяться в робочий листок за допомогою окремого додатку — Query.
* Кілька діапазонів консолідації. Ця процедура еквівалентна об’єднанню інформації в таблиці (консолідації таблиць), але з використанням всіх інструментів зведених таблиць.
* Інші зведені таблиці.Якщо активна робоча книга не має зведених таблиць — ця опція не доступна.
Після обрання виду джерела даних треба клацнути на кнопці “Далі“і перейти до наступного етапу. Вигляд діалогового вікна першого етапу наведений на рис. 4.
Рис. 4. Перше діалогове вікно “Майстра зведених таблиць”.
В залежності від виду джерела даних другий етап може складатись з одного або з двох кроків. “Майстер зведених таблиць” видасть запит безпосередньо про діапазон вихідних даних. Якщо перед активізацією команди “ДаніðЗведена таблиця”курсор розміщувався у будь-якій комірці вихідного діапазону, Excel автоматично запропонує діапазон, з яким користувач може погодитись або відкорегувати його. На рис. 5 наведено друге діалогове вікно “Майстра зведених таблиць”, яке відповідає джерелу вихідних даних “Список или база данных Microsoft Excel”.
Рис. 5. Друге діалогове вікно “Майстра зведених таблиць”.
На третьому етапі з’являється третє діалогове вікно (рис. 6), в якому визначається загальна структура зведеної таблиці. Поля бази даних (списку) будуть представлені у вигляді кнопок, розташованих в правій частині вікна, які можна пересунути у відповідні області схеми зведеної таблиці. Схема має чотири області:
Рис. 6. Третє діалогове вікно “Майстра зведених таблиць”.
* Рядок.Переміщені в цю область кнопки полів відобразяться як заголовки полів рядка в зведеній таблиці.
* Стовпчик.Переміщені в цю область кнопки полів відобразяться як заголовки полів стовпчика в зведеній таблиці.
* Дані.Це поле даних, які підсумовуються в зведеній таблиці.
* Сторінка. Значення цього поля використовується як заголовок сторінки в зведеній таблиці.
В кожну з цих областей можна перемістити будь-яку кількість полів. Невикористані поля будуть відсутні у зведеній таблиці. При пересуванні кнопки поля в область даних автоматично застосовується функція СУММ, якщо поле містить числові значення, або функція СЧЁТ, якщо поле містить нечислове значення. На цьому кроці при налаштуванні зведеної таблиці для зміни поля треба двічі клацнути на ньому. Можна вказати для конкретного поля функцію підбиття підсумків — підрахунок кількості значень або іншу. Можна також визначити, які елементи поля не показувати або пропустити. Поля можна змінити і після створення макету зведеної таблиці.
На останньому етапі в четвертому діалоговому вікні треба вказати, куди буде записана зведена таблиця — або на існуючий робочий листок, або на новий (рис. 7).
Рис. 7. Четверте діалогове вікно “Майстра зведених таблиць”.
Для завершення створення зведеної таблиці треба на четвертому етапі клацнути на кнопці “Готово”(Finish).
Зведена таблиця — динамічний об’єкт. Її можна змінювати та налаштовувати. Для цього зручно використовувати панель інструментів “Зведені таблиці“(Pivot Table). Наприклад:
* На листку зведеної таблиці є кнопки полів (комірки, що містять назви полів). Будь-яку з кнопок можна пересунути в інше місце зведеної таблиці (ця операція має назву перекручування (pivoting)). Excel миттєво змінить структуру зведеної таблиці, реагуючи на внесені зміни.
* Щоб вилучити поле із зведеної таблиці, треба клацнути на кнопці поля і пересунути його мишкою за межі зведеної таблиці. Кнопка поля буде перехрещена символом “´“. Після звільнення кнопки мишки вибране поле буде виключено з таблиці.
* Щоб додати нове поле в зведену таблицю , треба помістити табличний курсор на одну з комірок зведеної таблиці і залучити команду “ДаніðЗведена таблиця” — на екрані з’явиться третє діалогове вікно “Майстра зведених таблиць”,в якому можна зробити потрібні зміни і для закінчення клацнути на кнопці “Готово”.
* Якщо зміни торкнулись вихідних даних, то зведена таблиця не зміниться автоматично — її потрібно поновити вручну одним з способів:
® Залучити команду “Дані ð Поновити дані”(Datað Refresh Data).
® Клацнути правою кнопкою мишки на будь-якій комірці зведеної таблиці і у контекстному меню, що з’являється, вибрати команду “Поновити дані”(Refresh Data).
® Клацнути на кнопці “Поновити дані”(Refresh Data), яка розташована на панелі інструментів “Зведені таблиці“.
· Опції зміни полів зведеної таблиці знаходяться у діалоговому вікні “Обчислення поля зведеної таблиці“(Pivot Table Field). Щоб отримати доступ до цих опцій, треба або двічі клацнути на кнопці поля, або скористатись командою контекстного меню “Поле”(Field), яке з’являється після клацання правою кнопкою мишки. Опції полів і їх призначення:
® “Ім’я”(Name) — дозволяє змінити ім’я поля.
® “Орієнтація”(Orientation) — дозволяє змінити розташування елементів поля.
® “Проміжні підсумки” (Subtotals) — дозволяє змінити тип вираховуваних проміжних підсумків. Проміжні підсумки має сенс підбивати у тому випадку, якщо в рядках або стовпчиках зведеної таблиці знаходиться кілька полів. В списку типу проміжних підсумків можна по черзі або одночасно (утримуючи натиснутою клавішу Ctrl) виділити кілька елементів. Тоді результат обчислення проміжних підсумків буде знаходитись в кількох рядках. Щоб виключити проміжні підсумки, треба вибрати перемикач “Ні“(None).
® “Сховати елементи” (Hide Items) — дозволяє не відображувати на екрані один чи кілька елементів поля. Для цього треба клацнути на назві елемента, який треба сховати.
· Групування елементів зведеної таблиці.Щоб створити групу, треба виділити комірки, які будуть згруповані. Потім залучити команду “Дані ð Група та структура ð Групувати”(Datað Group and Оutineð Group) або клацнути на кнопці “Групувати”(Group) на панелі інструментів “Зведені таблиці“. В результаті Excel створить нове поле і привласнить йому назву. В цьому полі будуть знаходитись елементи, які були у виділених перед цим комірках. Після цього можна вилучити зайві поля і перейменувати назви полів і елементів.
· Кожна комірка в області даних зведеної таблиці уявляє собою кілька записів вихідної бази даних. Іноді треба з’ясувати, з яких полів складається підсумкове значення. Для цього треба двічі клацнути на потрібній комірці. Excel створить новий листок, в якому відобразить дані, що були використані для отримання інформації в обраній комірці (при умові, що встановлена опція “Розгортання дозволено”в діалоговому вікні “Параметри зведеної таблиці“).
Аналіз даних електронних таблиць через сценарій “а що як…”
Однією з привабливих рис Excel є можливість використання формул для побудови динамічних моделей, які миттєво реагують на зміну вихідних даних. Зміна значень у комірках по визначеній системі і відслідковування одержаних результатів в тих комірках, де містяться формули, і є аналізом “а що як…”. Інакше кажучи аналіз “а що як…” — це процес пошуку відповідей на сформульовані питання.
Що таке аналіз “а що як…”
Розглянемо таблицю, пов’язану з обробкою інформації про іпотечні позики. На робочому листку таблиця уявляє собою дві секції, в одній з яких розташовані комірки введення вихідних даних, а в іншій — комірки результатів, значення яких вираховуються за певними формулами.
Розрахунок іпотечної позики
| Вихідні дані | |
| Ціна Перший внесок Термін погашення позики Процентна ставка | $ 201 900 20% 8% |
| Результати розрахунку | ||
| Розмір позики | $ 161 520 | =Ціна*(1-Перший_внесок) |
| Місячна платня | $ 1 185 | =ППЛАТ(Процентна_ставка/12;Термін_погашення;-Розмір_ позики) |
| Загальна сума | $ 426 664 | =Місячна_платня*Термін_погашення |
| Загальна сума комісійних | $ 224 764 | =Загальна_сума-Ціна |
За допомогою цього робочого листка легко відповісти на такі і подібні питання:
* а що як домовитись про більш низьку ціну на майно?
* а що як кредитор забажає за перший внесок 20% виплати?
* а що як буде змога отримати 40-річну позику?
* а що як процентна ставка знизиться до 7,5%?
Відповіді на ці питання можна отримати, змінюючи значення у комірках введення вихідних даних і спостерігаючи за зміною у комірках результатів.
На цьому нескладному прикладі треба звернути увагу на будову таблиці — вона створена так, що забезпечує максимальну гнучкість для внесення змін. Отже одна з рекомендацій по створенню таблиць з метою аналізу — намагатись не використовувати суворо визначені значення у формулах. Замість цього треба розміщувати значення в певних комірках і використовувати посилання на них у формулах. “Суворо визначені значення” — це звичайні числа або константи. Використання посилання має дві переваги — через ім’я прояснюється, яке значення використовується, і полегшується зміна значення.
Для реалізації аналізу “а що як…”Excel надає чотири основні можливості:
* Аналіз “а що як…” вручну. Для цього необхідно помістити нові значення у комірки і спостерігати, як зміниться результат у комірках з формулами.
* Аналіз “а що як…” з використанням макросів.Цей спосіб передбачає створення макросів для автоматичної заміни значень у комірках з вихідними даними.
* Таблиці підстановки.Цей спосіб передбачає створення таблиці, в якій будуть представлені ряд систематичних значень одного чи двох вихідних параметрів і відповідний ряд результатів обчислень за формулами.
* Диспетчер сценаріїв.Цей спосіб дозволяє створити поіменований сценарій і одержати звіти з використанням засобів структуризації або зведених таблиць.
Аналіз “а що як…” вручну
Аналіз “а що як…” вручну базується на такому принципі: змінюючи інформацію у вихідних комірках за сценарієм, спостерігають за зміною результатів. Під “сценарієм” мається на увазі певний набір даних, що знаходиться в одній або кількох вихідних комірках, посилання на які використовується в одній чи кількох ключових комірках з формулами. При необхідності можна зберегти кожний сценарій в новій робочій книзі.