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

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

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

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

Команда Итоги використовується для від­сортованих даних показників, що беруть участь у формуванні підсумків. У результаті виконання команд Данные®Итоги з'явиться діалогове вікно (рис. 9), у якому:

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

• визначити у списку Операция тип підсумкової операції (Сумма, Среднее тощо);

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

Рисунок 9 – Вигляд діалогового вікна Промежуточные итоги

Режим структурованої бази даних (рис. 10) дозволяє переглядати різні частини списку за допомогою кнопок, що розташовані в лівому полі робочого аркуша. Кнопки мають позначки «+» та «-», що означає дії згорнути та розгорнути. Кнопка з номером 1 дозволяє переглянути загальний підсумок, з номером 2 – підсумок за окремими групами, визначеними в списку При каждом изменении в:, а з

Рисунок 10 – Вигляд структурованої бази номером 3 - усі підсумки.

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

Для відміни режиму підсумків досить виконати команди Данные®Итоги, у вікні Промежуточные итоги необхідно натиснути кнопку Убрать все.

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

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

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

Команду Консолидация можна використовувати декількома спо­собами. Можна зв'язати консолідовані дані з вхідними даними, щоб на­ступні зміни у вхідних листах відображалися в підсумковому листі. Або можна просто консолідувати вхідні дані без створення зв'язків.

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

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

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

Консолідувати дані можна по розташуванню або по категорії.

• Якщо консолідують дані по розташуванню, Microsoft Excel зби­рає інформацію з однаково розташованих комірок кожного вхід­ного листа.

• При консолідації даних по категорії Excel використовує як основу для об'єднання листів заголовки стовпців або рядків.

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

Також можна використовувати кожну з наступних функцій: Ко­личество значений, Среднее, Максимум, Минимум, Произведение, Количество чисел, Несмещенное отклонение, Смещенное отклоне­ние, Несмещенная дисперсия и Смещенная дисперсия.

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

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

1. На листі виділити верхню ліву комірку діапазону, у який повинні бути розміщені консолідовані дані.

2. Виконати команди Данные®Консолидация. З'явиться діалогове вікно Консолидация (рис. 11), у якому задаються вихідні діапазони для консолідації й деякі інші параметри.

Рисунок 11 – Вид діалогового вікна Консолидация

3. У списку Функція вибрати функцію, за допомогою якої буде виконана консолідація (вибирати функцію Сумма).

4. Увести посилання на перший вихідний діапазон у поле Посилання

5. Натиснути кнопку Добавить. Уведене посилання з'явиться в списку Список диапазонов.

6. Потім у поле Ссылка ввести посилання на другий діапазон вихідних даних.

7. Знов натиснути кнопку Добавить. Уведене посилання з'явиться в списку Список диапазонов. Інші діапазони вводяться аналогічно.

8. У групі Использовать в качестве имен вказати, де розташовані заголовки.

9. Після уведення всіх посилань натиснути кнопку ОК. У зазначеному місці з'являться консолідовані дані.

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

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

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

2. Виконати команду Данные, Консолидация. З'явиться вікно Консолидация.

3. У списку Функция вибрати функцію Сумма, за допомогою якої буде виконана консолідація.

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

5. Натиснути кнопку Добавить, щоб додати діапазон до списку вихідних діапазонів консолідації. Потім у поле Ссылка ввести посилання на другий діапазон вихідних даних. Знов натиснути кнопку Добавить. Уведене посилання з'явиться в списку Список диапазонов. Інші діапазони додаються аналогічно.

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

7. Натиснути кнопку ОК. У зазначеному діапазоні з'являться консолідовані дані.

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

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

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

Створення зведених таблиць є одним із потужних засобів обробки інформації у Microsoft Excel ХР, що дозволяє виконувати складні дії з даними, наприклад:

• здійснювати вибір інформації з допомогою критеріїв з інших аркушів однієї книги або з декількох книг;

• сформувати зведену таблицю з даних інших джерел;

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

• будувати зведену таблицю за даними інших зведених таблиць тощо.

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

§ підготувати зведені дані на окремому аркуші робочої книги;

• виконати команди Данные®Сводная таблица і за допомогою Мастера сводных таблиц виконувати інструкції для їх побудови:

- крок 1 - вибрати тип джерела даних для побудови таблиці і натиснути кнопку Далее>;

- крок 2 - вказати діапазон даних, на основі яких будується таблиця і натиснути кнопку Далее>.

- крок 3 - встановити місце розташування зведеної таблиці;

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

- натиснути кнопку <ОК>, а потім кнопку Готово.

На екрані з'явиться зведена таблиця.

Дата добавления: 2014-10-31