Тема 17. Електронні таблиці MS Excel: Зведені таблиці. Таблиці даних.

Зведені таблиці

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

Зведена таблиця може бути створена на основі даних, що містяться в списку або базі даних Microsoft Excel, на декількох робочих листах Microsoft Excel, в зовнішній базі даних[1] або в іншій зведеній таблиці.

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

 Нехай в результаті ми отримали наступну таблицю:

Табл.1.

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

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

Побудова зведеної таблиці здійснюється за допомогою так званого Майстра зведених таблиць. Для запуску Майстра зведених таблиць слід з головного меню вибрати Данные Þ Cводная таблица. Побудова зведеної таблиці за допомогою Майстра здійснюється в три кроки.

·                 Після запуску на екрані з’являється діалогова панель 1-го кроку (мал.1). На цьому кроці слід вказати, на основі яких даних необхідно створити зведену таблицю. Є чотири можливості:

- створити таблицю на основі списку або бази даних Microsoft Excel (за замовчуванням);

- створити таблицю використовуючи зовнішнє джерело даних;

- створити таблицю на основі даних, що знаходяться в декількох діапазонах консолідації;

- створити таблицю на основі даних в іншій зведеній таблиці.

Мал. 1.

·                 Вибір тієї чи іншої можливості здійснюється встановленням відмітки біля потрібного пункту. В розглядуваній задачі слід вибрати перший пункт Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel. Власне вибирати цей пункт не потрібно, оскільки він автоматично встановлюється при запуску Майстра, тобто за замовчуванням. Як видно з мал.1, разом зі зведеною таблицею можна вивести і діаграму, встановивши необхідну позначку. Після цього, слід натиснути клавішу Далее, щоби перейти до наступного кроку.

·                 На кроці 2 слід вказати діапазон даних для зведеної таблиці. На екран виводиться діалогова панель, на якій знаходиться вікно Диапазон (мал. 2). Якщо перед запуском Майстра була виділена комірка[2], що знаходиться серед діапазону даних, то дані для зведеної таблиці будуть автоматично виділені, а у вікні Диапазон буде вказано їх адресу. Щоби вказати інший діапазон, необхідно ввести його адресу у вікно або просто виділити його на листі.

Мал. 2.

 Після виділення даних, для переходу до наступного кроку, слід

 натиснути клавішу Далее.

·                 На екран виводиться діалогова панель (мал. 3):

Мал. 3.

Після натискання клавіші Готово, якщо вибрано Новый лист, на новому робочому листі виводиться заготовка для зведеної таблиці разом з панеллю інструментів Сводные таблицы (мал.4):

Мал.4.

Цей етап є найважливішим при створенні зведеної таблиці. Саме на цьому етапі задається вид таблиці. Внизу панелі знаходяться кнопки з назвами полів (стовпців) списку. Для отримання таблиці, в якій в першому стовпці знаходяться назви магазинів, а в другому – прибутки, слід за допомогою миші перетягнути кнопку Магазин в прямокутник з надписом Перетащите сюда поля строк, а кнопку з надписом Сума в прямокутник Перетащите сюда элементы данных. Після цього, ми одержимо зведену таблицю:

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

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

Можна побудувати іншу таблицю, по іншому розмішуючи в заготовці для зведеної таблиці назви полів. Так, перетаскуючи в область Перетащите сюда поля строк кнопку Магазин, в область Перетащите сюда поля столбцов – кнопку Жанр, а в область Данные – кнопку Кількість, одержимо зведену таблицю:

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

В Excel 2000, при роботі з зведеними таблицями активізується панель інструментів – Сводные таблицы.[3] Вона може бути як плаваючою, тобто знаходитись в будь-якому місці екрану, або прикріпленою, як правило, до панелей інструментів внизу або вверху екрану. Якщо табличний курсор не знаходиться всередині зведеної таблиці, то плаваюча панель має вигляд:

Якщо ж курсор знаходиться всередині зведеної таблиці, то панель інструментів ще додатково містить назви полів:

 

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

 Виділивши потрібний зразок і клацнувши на кнопці ОК, Excel перетворить зведену таблицю до заданого вигляду. Так, вибравши першу з запропонованих форм, одержимо:

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

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

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

Іменування комірок

Всяка комірка в Microsoft Excel має свою адресу – номер стовпця, де вона знаходиться і номер рядка. Наприклад, комірка, що знаходиться на перетині рядка 8 і стовпця С має адресу С8. Адреси комірок також називають ще іменами комірок. Щоби уникнути плутанини, нижче, ми будемо називати адресами лише імена утворені в описаний вище спосіб.

Нехай в комірці А1 знаходиться ціна одного виробу, а в комірці А2 – закуплена кількість виробів. Щоби в комірці А3 підрахувати сумарні витрати на закупку, слід в цій комірці записати формулу =А1*А2. Якщо дивитись на саму формулу, то з неї безпосередньо не можна встановити, що за величини знаходяться в комірках А1 і А2. Якщо формула не така проста, а більш складна, то легко заплутатись. Тому, у ряді випадків, більш зручним є присвоєння коміркам імен. Імена комірок можна вибирати у відповідності зі змістом величин, що в них знаходяться. При записі формул використання імен надає формулам прозорості. Так, присвоївши в розглянутому вище випадку, комірці А1 ім’я Ціна, а комірці А2 – ім’я Кількість, можна записати формулу в комірці А3 як =Ціна*Кількість. В цьому випадку, ми уже з вигляду формули можемо сказати, які величини в неї входять.

Щоби присвоїти ім’я комірці, слід:

· виділити потрібну комірку, встановивши на неї вказівник миші і клацнувши лівою клавішею миші

· в поле Имя ввести нове ім’я комірки (мал. 5). На малюнку в полі Имя знаходиться адреса комірки, де знаходиться табличний курсор, – А1

· натиснути клавішу Enter.

Мал. 5.

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

· помістити курсор у комірку, якій необхідно присвоїти ім’я

· з головного меню вибрати Вставка Þ Имя Þ Присвоить. В результаті на екран виводиться діалогове вікно Присвоение имени (мал.6)

· набрати нове ім’я в полі Имя, після чого клацнути на клавиші Добавить

· натиснути клавишу ОК.

Мал. 6.

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

·                 помістити курсор у комірку, ім’я якої потрібно змінити

·                 з головного меню слід вибрати Вставка Þ Имя Þ Присвоить. В результаті на екран виводиться діалогове вікно Присвоение имени (мал.6)

·                 клацнути мишею на імені, яке потрібно замінити, а потім на кнопці Удалить

·                 ввести нове ім’я в полі Имя, після чого клацнути на кнопці Добавить

·                 клацнути на кнопці ОК.

Зауваження

1. Якщо формула містить ім’я, яке вже видалено, то в комірці, де знаходиться формула, виводиться повідомлення про помилку: #ИМЯ? Це значить, що в формулі є імена комірок, яких немає на робочому листі. Слід відкоригувати формулу, виключивши з неї такі імена.

2. Щоби побачити всі формули, які посилаються на дану комірку (ім’я якої потрібно змінити), слід з головного меню вибрати Сервис Þ Зависимости Þ Зависимые ячейки. Якщо на дану комірку є посилання, то з’являється стрілка, яка вказує на залежну комірку. При наявності залежних від даної комірки інших комірок, слід внести зміни у формули в цих комірках з тим, щоби виключити ім’я, яке видаляється.

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

Обчислення типу "що - якщо" вручну

 Що трапиться, якщо збільшити різницю між собівартістю і продажною ціною? Що трапиться, якщо збільшити зарплату співробітникам на 1%? Такі, чисто практичні задачі, що на кожному кроці виникають в малому і великому бізнесі, можна розв’язати за допомогою засобів прогнозування Excel.

 Розглянемо наступну задачу: Припустимо, що ми купуємо для своєї фірми в кредит 10 комп’ютерів вартістю 5376 грн. Відсоткова ставка складає 12% (річна), а термін кредиту – 24 місяці. Необхідно знайти величину щомісячних виплат.

 Спочатку розв’яжемо цю задачу "вручну", тобто без використання таблиць даних.

Сформуємо робочий лист:

Тут в В3 занесена загальна вартість комп’ютерів (10 штук), в комірку В4 – відсоток за кредит, в комірку В5 – число місяців погашення кредиту. Для одержання величини щомісячних виплат ми користуємось вбудованою в Excel фінансовою функцією ППЛАТ.

 Функція ППЛАТ призначена для обчислень величини виплат за ссудою на основі постійних виплат і постійної відсоткової ставки. Ця функція може мати 5 аргументів, але обов’язковими є перші три.

Синтаксис:

ППЛАТ(ставка; чвип; сума; мс; тип).

Тут ставка – відсоткова ставка за ссуду (за один період);

 чвип – число виплат за ссудою (кількість періодів);

 сума – загальна сума (величина кредиту);

 мс – майбутня сума, тобто сума, яку потрібно досягнути після останньої виплати. Якщо цей аргумент відсутній, то вона покладається рівною нулю;

 тип – це число 0 або 1, що позначає, коли має проводитись виплата: 0, - якщо платити потрібно в кінці періоду; 1 – якщо оплата здійснюється на початку періоду. Відсутність аргументу еквівалентна тому, що його значення дорівнює нулю.

 Виплати, розраховані за функцією ППЛАТ не включають в себе податків.

Після внесення даних і формул, як показано вище, слід встановити відповідні формати для комірок. Для комірок В3, В7 – грошовий, а для комірки В4 – відсотковий. Для цього потрібно помістити табличний курсор (тобто прямокутник) у відповідну комірку і в головному меню вибрати Формат Þ Ячейки... . В результаті на екран виводиться діалогове вікноФормат ячеек, з якого і необхідно вибрати потрібний формат (Денежный або Процентный).

Після встановлення відповідних форматів, робочий лист набуде наступного вигляду:

 Зрозуміло, що якщо ми захочемо розрахувати величину щомісячних виплат у тому випадку, коли число комп’ютерів, які ми купляємо не 10, а скажімо 12, то у комірку В3 нам потрібно буде занести сумарну вартість покупки, а для цього ціну одного комп’ютера потрібно помножити на їх кількість. Згодьтесь, що 5376 грн. множити на 12 усно важко. Тому, потрібно намагатись так організувати робочий лист, щоби мати змогу легко змінювати вхідні дані, а не використовувати проміжні, як було зроблено вище. Тому доповнимо робочий лист додатковими даними і замінимо значення в комірці В3 на формулу:

Після того, як в комірках Е3, Е5 встановлено грошовий формат, робочий лист набуде вигляду:

Тепер, якщо потрібно зробити розрахунок величини щомісячних виплат при купівлі іншої кількості комп’ютерів, то достатньо занести потрібне число комп’ютерів в комірку Е4. Так, помістивши в комірку число 12, матимемо:

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

Усунути цей недолік легко, використовуючи таблиці даних. Є декілька різновидів таблиць даних: 1) таблиці даних, що містять одну вхідну змінну і одну формулу; 2) таблиці даних, що містять одну вхідну змінну і декілька формул; 3) таблиці даних, що містять дві вхідних змінних і одну формулу.

Таблиці даних з однією вхідною змінною і однією формулою

 Припустимо, що нам цікаво знати зміну величини виплат в залежності від кількості закуплених комп’ютерів. Іншими словами, ми хочемо побудувати таблицю, в одному стовпці якої стоїть кількість закуплених комп’ютерів, а в другому – відповідна величина щомісячних виплат. Будемо починати цю таблицю з кількості комп’ютерів, що дорівнює 7, а завершимо кількістю комп’ютерів, що дорівнює 12.

 Заносимо в комірку В9 текст "Кількість комп’ютерів", в комірку В10 – число 7, в комірку В11 – число 8. А далі, використовуючи автозаповнення (Автозаполнение), щоби вручну не вводити в кожну комірку значення, заповнюємо комірки В12:В15. Текст має чисто декоративне значення. Його можна і не вводити. Для формування таблиці даних він не відіграє ніякої ролі.

 Тепер важливий момент. Величина виплат буде міститись в іншому стовпці (стовпці С), починаючи з С10 і закінчуючи С15. Так от, над цими комірками (в комірці С9) необхідно розмістити формулу, за якою будуть розраховуватись елементи стовпця. В даному випадку, це формула для обчислення величини виплат. Вона знаходиться в комірці В7 і містить функцію ППЛАТ. Просте копіювання не приведе до потрібного результату, оскільки в формулі автоматично будуть замінені адреси комірок. І в результаті, при такому копіюванні ми побачимо

В комірці С9, куди була скопійована формула з В7, ми бачимо повідомлення про помилку #ДЕЛ/0! (ділення на 0). Усунути це можна в два способи.

Перший, – це записати в комірці В7 замість формули =ППЛАТ(В4/12; В5; В3) формулу = ППЛАТ($В$4/12; $В$5; $В$3), замінивши тим самим відносні посилання на абсолютні. Тоді при копіюванні, ті літери і числа адреси комірки, біля яких стоїть знак долара не змінюються. Після такого редагування комірки В7 можна скопіювати її в С9.

Другий спосіб полягає в присвоєнні імен коміркам, що входять в розрахункову формулу в комірці В7, тобто коміркам В3, В4, В5. Присвоїмо їм імена Вартість, Відсоток, Термін відповідно. Після цього, потрібно записати формулу в комірці В7 через ці імена. Це робиться автоматично (тобто виправляти не потрібно), якщо з самого початку, перед записом формули, присвоїти імена коміркам-аргументам. Отже, формула в В7 запишеться: =ППЛАТ(Відсоток/12; Термін; Вартість). Після цього формулу копіюємо з комірки В7 в комірку С9.

Після копіювання, виділяємо комірки В9:С15:

Далі, вибираємо з головного меню Данные Þ Таблица подстановки. На екран виводиться діалогове вікно Таблица подстановки:

В цьому діалоговому вікні слід вказати, як комп’ютер повинний підставляти дані в формулу: по стовпцях чи по рядках. Досить часто помиляються, вказуючи замість "Підставляти значення по стовпцях в:" – "Підставляти значення по рядках " і навпаки. В нашому випадку, дані (кількість комп’ютерів) містяться в стовпці В.Тому комп’ютер повинний одне за другим підставляти значення з цього стовпця. Це значить, що він буде послідовно рухатись вниз по стовпцю, - підставляти значення по рядках (тобто, брати числа, спочатку з В10, потім з В11 і т.д).

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

Далі, потрібно клацнути мишею на тій комірці, значення якої необхідно змінювати. В даному випадку це комірка Е4, яка містить кількість комп’ютерів, що закупляються. Адреса цієї комірки з’явиться, після клацання на ній мишею, всередині зменшеного вікна Таблица подстановки. Знову клацаємо мишею на кнопці  в правій частині вікна, щоби відновити діалогове вікно до звичайного розміру. В повнорозмірному вікні клацаємо на кнопці ОК. На екрані з’являється таблиця, яка містить кількість куплених комп’ютерів і величину виплат. Щоби таблиця даних мала саме такий вигляд, потрібно встановити в комірках С10:С15 грошовий формат.

 Описаний засіб Таблиці даних можна використовувати для розв’язку чисто математичних задач.

 Розглянемо це на прикладі табулювання функції. Нехай потрібно протабулювати функцію  на проміжку [1,0; 2,0] з кроком 0,2.

 Формуємо робочий лист.

Спочатку присвоюємо ім’я комірці А1. Назвемо її х. Занесемо в цю комірку перше значення , тобто 1. Помістимо в комірку В1 формулу: =x^2*sin(x). В комірках А2 і А3 запишемо відповідно 1 (знову повторюємо 1-е значення : =1) і 1,2 (наступне значення ). За допомогою автозаповнення заповнюємо А4:А7 значеннями 1,4; ...; 2. Після цього виділяємо блок комірок А1:В7 і вибираємо з головного меню Данные Þ Таблица подстановки. В діалоговому вікні Таблица подстановки вказуємо, що підставляти значення потрібно по рядках і що комірка, в яку потрібно підставляти значення є $А$1. В результаті одержимо наступну таблицю:

Таблиці даних з однією вхідною змінною і декількома формулами

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

Розглянемо, як приклад, табулювання функцій  і  на інтервалі [0; 1] з кроком 0,2.

Як і в попередньому випадку, присвоїмо комірці А1 ім’я х. В комірки А2:А7 занесемо значення аргументу () автозаповненням. В комірки В1 і С1 помістимо формули =х^2 i =x^3.

Виділяємо всю таблицю (А1:С7) і вибираємо з головного меню Данные Þ Таблица подстановки. В діалоговому вікні Таблица подстановки потрібно, як і в попередньому випадку, вказати комірку A1, або $A$1, або просто x (ім’я комірки).

В результаті одержимо:

Таблиці даних з двома вхідними змінними і однією формулою

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

Нехай задано функцію двох змінних . Потрібно протабулювати її в прямокутнику [2; 3]´[1; 2] змінюючи  з кроком 0,2, а  – з кроком 0,1.

Формуємо робочий лист.

Дамо імена коміркам: А1 – х, В1 – у. Занесемо в них початкові значення  і  (=2, =1). Виберемо комірку, де буде розміщуватись лівий верхній кут таблиці. Нехай це комірка В3. Записуємо в цю комірку формулу =х*у^2+КОРЕНЬ(х). Заносимо в комірки С3:Н3 значення  з кроком 0,2, а в комірки В4:В14 значення  з кроком 0,1. Виділяємо блок В3:Н14 і застосувавши команду Данные Þ Таблица подстановки, вказуємо в діалоговому вікні, що з’являється, в полі Подставлять значения по столбцам адресу комірки х ($А$1), а в полі Подставлять значения по строкам адресу комірки у ($B$1). Після натискання кнопки ОК маємо таблицю:

[1] Зовнішня база даних - база даних створена за допомогою інших програм (не Microsoft Excel) , що називаються системами управління базами даних (СУБД). До таких програм відносяться, зокрема, Microsoft Access, Borland dBase, Borland Paradox.

[2] Виділити комірку – встановити на неї вказівник миші і клацнути лівою клавішею. В результаті навколо комірки з’являється жирний прямокутник.

[3] В Excel 97 створення зведених таблиць дещо відрізняється за формою – Майстру потрібно 4 кроки замість 3-х, але ідеологія їх побудови є такою самою