Створення зведеної таблиці
В 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 є можливість використання формул для побудови динамічних моделей, які миттєво реагують на зміну вихідних даних. Зміна значень у комірках по визначеній системі і відслідковування одержаних результатів в тих комірках, де містяться формули, і є аналізом “а що як…”. Інакше кажучи аналіз “а що як…” — це процес пошуку відповідей на сформульовані питання.
Що таке аналіз “а що як…”
Розглянемо таблицю, пов’язану з обробкою інформації про іпотечні позики. На робочому листку таблиця уявляє собою дві секції, в одній з яких розташовані комірки введення вихідних даних, а в іншій — комірки результатів, значення яких вираховуються за певними формулами.