Засоби роботи з табличними даними

Користувачі найчастіше застосовують MS Excel для роботи з списками чи, іншими словами, базами даних робочого листа (worksheet database). По своїй суті список — це упорядкований набір даних. Список можна також розглядати як табличну базу даних. Звичайно в першому рядку списку знаходяться заголовки, а в наступних рядках — дані. Стовпці списку називають полями (fields), а рядка — записами (records). Розмір списку теоретично обмежений розмірами робочого листа, тобто в нього не може бути більш 256 полів і 65 536 записів. MS Excel має могутні засоби по роботі з табличними базами даних: консолідація, сортування, фільтрація, проміжні підсумки і зведені таблиці.

1. Консолідація робочих листів

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

Як приклад для консолідації розглянемо обробку даних про обсяги продажів фірми "ТОВ Термопластавтомат" за перший квартал поточного року (рис. 7.1).

Опишемо процес консолідації даних.

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

2. Виберіть команду Дані | Консолідація. На екрані відобразиться діалогове вікно Консолідація (рис. 7.2).

3. У списку Функція вкажіть тип консолідації. Припустимі типи: Сума, Кількість значень, Середнє, Максимум, Мінімум, Добуток, Кількість чисел, Зміщене відхилення, Незміщене відхилення, Зміщена дисперсія, Незміщена дисперсія. У даному випадку виберіть значення Сума.

4. У полі Посилання укажіть вихідний діапазон даних, що повинний бути консолідований. У даному випадку [71січ.xls]січень!$A$1:$B$7. Натисніть кнопку Додати. У результаті діапазон буде доданий у список Список діапазонів.

Рис. 7.1- Консолідація даних

Рис. 7.2 - Діалогове вікно Консолідація

5. Повторите пп. 3, 4 для інших консолідованих діапазонів. В даному випадку для діапазонів [71лют.xls]лютий!$A$1:$B$7 і [71бер.xls]березень!$A$1:$B$7.

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

7. Вкажіть, що повинно містити діапазон призначення: фіксовані значення, що надалі не будуть змінюватися при змінах у вихідних даних, чи зв'язані величини, що обновляються при змінах у вихідних даних. Якщо фіксовані значення, то зніміть прапорець Створювати зв'язки з вихідними даними. У даному випадку цей прапорець треба установити.

8. Натисніть кнопку ОК.

2 Сортування даних

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

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

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

1. Виділите чарунку всередині сортованого списку чи виділіть його цілком. У нашому випадку, наприклад, виділите чарунку А2.

2. Виберіть команду Дані | Сортування. На екрані відобразиться діалогове вікно Сортування діапазону (рис. 7.3).

3. Вибір у списках Сортувати по, Потім по, В останню чергу по , використовуючи поля для впорядкування списку.

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

4. Натисніть кнопку ОК.

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

Рис. 7.3 - Сортування таблиці Замовлення і діалогове вікно „Сортування діапазону”Діалогове вікно Параметри сортування також використовується для сортування даних не по рядках, а по стовпцях. Для чого в цьому вікні в групі Сортування виберіть перемикач стовпці діапазону. Натисніть кнопку ОК. Далі виконуйте операції згідно з описаним вище алгоритмом.

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

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

3 Використання Автофільтру

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

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

- значення поля для пошуку точної відповідності;

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

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

Розглянемо процес автофільтрації на прикладі фільтрації даних таблиці Замовлення.

1: Перед використанням Автофільтру переконаєтеся, що він не використовується для якого-небудь іншого списку. Для цього виберіть команду Дані | Фільтр. Якщо команда Автофільтр відзначена прапорцем, виберіть її, щоб скасувати Автофільтр для іншого списку,

2. Виберіть чарунку всередині фільтрованого списку чи виділіть його цілком. У нашому випадку, наприклад, виділите чарунку А2.

3. Виберіть команду Дані | Фільтр | Автофільтр. У результаті чарунки з назвами полів перетворяться в списки, що розкриваються, (рис. 7.4).

Рис. 7.4 – Списки Автофільтру, що розкриваються

4. Розкрийте список, що відповідає полю, яке варто включити в критерій. Виберіть один із припустимих критеріїв:

• Усі — вивід на екран усіх записів;

• Перші 10 — вивід на екран заданого числа чи заданий відсоток перших чи останніх записів;

• Умова — вивід на екран записів за умовою, заданою у відображуваному діалоговому вікні Користувальницький автофільтр;

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

Наприклад, у нашому випадку в списку клієнт виберіть точне значення ТУП. Результат автофільтрації буде негайно відображений на екрані (рис. 7.5).

Рис. 7.5 - Результат автофільтрації

Рис. 7.6 - Діалогове вікно „Користувальницький автофільтр”

Рис. 7.7 – Результат виконання умов „Користувальницького автофільтру”

Якщо в критерій автофільтрації необхідно включити інше поле, поверніться до п. 4 алгоритму. Якщо критерій повинний складатися з двох компонентів, то зі списку автофільтрації виберіть значення Умова для конкретного стовпця, наприклад, дата виконання. На екрані відобразиться діалогове вікно Користувальницький автофільтр (рис. 7.6), що дозволяє швидко задати більш складну умову, чим звичайне порівняння. У лівому верхньому списку, що розкривається, вибирається операція порівняння (у даному випадку виберіть більше), у правом вибирається чи вводиться значення (уведіть 14568,85 грн). Потім, якщо необхідно, відзначте один з перемикачів „І”, „ЧИ” і задайте другу операцію порівняння (більше чи рівно) і значення (29.01.2003). Результат виконання умов Користувальницького автофільтру зображено на рис. 7.7.

4 Проміжні підсумки

У MS Excel є засіб, що дозволяє одержати попередні результати, якщо потрібно об'єднати дані в окремі групи, наприклад, згрупувати продажу по клієнтах чи датах виконання замовлення.

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

1. Виділіть чарунку списку, а потім виберіть команду Дані | Підсумки. На екрані з'явиться вікно Проміжні підсумки (рис. 7.8).

2. Вкажіть, як групувати дані, вибравши значення в списку При кожній зміні в. Список містить назви стовпців бази чи даних списку. Наприклад, ми будемо підраховувати загальну вартість замовлень кожного одержувача, тому виберіть Одержувач.

3. Виберіть операцію, виконувану над даними, зі списку Операція. Можливо підвести підсумки по одній з наступних операцій: Сума, Кількість значень, Середнє, Максимум, Мінімум, Добуток,

Рис. 7.8 - Вікно „Проміжні підсумки”

Рис. 7.9 - Отримані проміжні підсумки

Кількість чисел, Зміщене відхилення, Незміщене відхилення, Зміщена дисперсія, Незміщена дисперсія. У даному випадку виберіть Сума.

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

5. Щоб замінити всі старі проміжні підсумки на знову створені, установите прапорець Замінити поточні підсумки. У даному випадку цей прапорець повинний бути знятий.

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

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

8. Натисніть кнопку ОК.

5 Зведена таблиця

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

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

- легко перетворюються;

- дозволяють виконувати автоматичний добір інформації;

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

Опишемо покроковий процес створення зведеної таблиці на прикладі таблиці Замовлення (рис. 7.10). Для цього:

1. Виберіть команду Дані | Зведена таблиця. На екрані з'явиться перше вікно Майстра зведених таблиць (рис. 7.11).

Рис. 7.10 – Вихідні дані таблиці Замовлення

Рис. 7.11 - Перше вікно „Майстра зведених таблиць”

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

Перемикач Джерело даних

в списку чи бази даних Microsoft ExcelСписок чи таблиця, з позначеними стовпцями, розташована на робочому листі MS Excel

в зовнішньому джерелі данихФайли і таблиці, створені іншими програмами, наприклад, MS Access

у кількох діапазонах консолідаціїКілька списків чи таблиця з позначеними стовпцями, розташована на робочому листі MS Excel

в іншій зведеній таблиці чи діаграміІнша існуюча в активній робочій книзі зведена таблиця

Рис. 7. 12- Друге вікно „Майстра зведених таблиць”

Рис. 7.13 - Третє вікно „Майстра зведених таблиць”

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

Рис. 7.14 - Вікно „Майстер зведених таблиць і діаграм – макет”

3. На екрані з'явиться друге вікно Майстра зведених таблиць (мал. 7.12). На цьому кроці ви повинні вказати діапазон, що містить дані, по яких буде будуватися зведена таблиця. Якщо джерело даних знаходиться в іншій робочій книзі, то необхідно скористатися кнопкою Огляд. Отже, після введення посилання на діапазон даних натисніть кнопку Далі. На екрані з'явиться третє вікно Майстра зведених таблиць (мал. 7.13).4. Спочатку натисніть кнопку Макет для створення структури зведеної таблиці. На екрані відобразиться вікно Майстер зведених таблиць і діаграм-макет (рис. 7.14). Перш ніж створювати макет зведеної таблиці, треба визначитися, яка інформація стане вводитися в області рядків, стовпців, даних і сторінок зведеної таблиці.

• Отже, для створення структури зведеної таблиці виберіть поле, що містить дані, по яких потрібно підвести підсумки, і перетягнете відповідну кнопку в область Дані. У нашому випадку, будемо підводити підсумки по Полю Вартість доставки. Для того щоб вибрати операцію, по якій підводяться підсумки, двічі клацніть по полю Вартість послуги, розташованої в області Дані. На екрані відобразиться вікно Обчислення поля зведеної таблиці (рис. 7.15).

Рис. 7.15. Вікно Обчислення поля зведеної таблиці

У списку Операція перераховані припустимі операцій: Сума, Кількість значень, Середнє, Максимум, Мінімум, Добуток, Кількість чисел, Зміщене відхилення, Незміщене відхилення, Зміщена дисперсія; Незміщена дисперсія. У даному випадку виберіть Сума. Список, що розкриває, Додаткові обчислення дозволяє розширити безліч припустимих операцій. Його значення: Ні, Відмінність, Частка, Приведена відмінність, З наростаючим підсумком у полі, Частка від суми по рядку, Частка від суми по стовпці, Частка від загальної суми, Індекс. У даному випадку виберіть Ні і натисніть кнопку ОК. Відбудеться повернення у вікно Майстер зведених таблиць і діаграм: макет.

• Для того, щоб помістити елементи поля в рядках, з міткою в лівій частині таблиці, перетягнете кнопку для обраного поля в область Рядок. У даному випадку ло рядкам будуть розміщатися дані про клієнтів, тому перетягнете поле Клієнт в область Рядок.

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

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

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

Рис. 7.16 - Вікно Майстер зведених таблиць і діаграм – макет зі структурою майбутньої зведеної таблиці

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

Рис. 7.17 - Вікно Параметри зведеної таблиці

5. У групі Помістити таблицю в (див. рис. 7.13) є два перемикачі новий лист і існуючий лист, що задають місце розташування зведеної таблиці. Якщо обраний перемикач існуючий лист, то в поле треба привести посилання на верхню ліву чарунку діапазону, де буде розташовуватися таблиця. У нашому випадку виберіть перемикач новий лист і натисніть кнопку Готово.

Зведена таблиця побудована. Для того щоб зведена таблиця не була занадто громіздкої, обмежимося переглядом одержувачів, яким виконано поставку 18.01.2003р. (співробітники, що прийняли замовлення Антонюк і Дрозд) (рис. 7.18). Натиснувши кнопку Майстер діаграм панелі інструментів Зведена таблиця, до зведеної таблиці можна додати зведену діаграму (рис. 7.19).Рис. 7.18 – Зведена таблиця

Рис. 7.19 – Зведена діаграма

ЛІТЕРАТУРА

1.Бухвалов А.В. и др. Финансовые вычисления для профессионалов.- СПб.: БХВ-Петербург, 2001.-320с. ил.

2.Гарнаев А.Ю. Excel, VBA, Internet в экономике и финансах.- СПб.: БХВ-Петербург, 2001.- 816с.:ил.

3.Евдокимов В.В. и др. Экономическая информатика. Учебник для вузов. Под ред. Д.э.н., проф. В.В.Евдокимова. – СПб.: Питер, 1997. – 592с.

4.Згуровський М.З., Коваленко І.І., Міхайленко В.М. Вступ до комп’ютерних інформаційних технологій: Навч.посіб. – К.: Вид-во Європ. ун-ту (фінанси, інформ. системи, менеджм. і бізнес), 2000.- 265 с.

5.Информатика. Базовый курс/ Симонович С.В. и др.- СПб.: Питер, 2000.- 640с.:ил.

6.Карлберг, Конрад. Бизнес-анализ с помощью Excel.: Пер с англ.- К.: Диалектика, 1997.- 448с.: ил.

7.Лук‘янова В.В. Комп‘ютерний аналіз даних: Посібник. – К.: Видавничий центр „Академія”, 2003. – 344с. (Альма-матер)