НМЦ

ІНФОРМАЦІЙНІ ТЕХНОЛОГІЇ

Електронний посібник

 

ВФПО

МОДУЛЬ ІІ. ОРГАНІЗАЦІЯ ТАБЛИЧНИХ ДАНИХ ЗАСОБАМИ ОФІСНОГО

ПРОГРАМНОГО ЗАБЕЗПЕЧЕННЯ

 

9. Аналіз даних MICROSOFT EXCEL 2016

9.1. Поняття сортування даних

9.2. Фільтрація даних

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

9.4. Робота з діаграмами

 

Перегляд презентація:

Аналіз даних MICROSOFT EXCEL 2016

 

9. Аналіз даних MICROSOFT EXCEL 2016

9.1. Поняття сортування даних

 

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

Дані можна сортувати за текстом (від «А» до «Я» або від «Я» до «А»), числами (від найменшого до найбільшого та від найбільшого до найменшого) і датою й часом (від найстаршого до наймолодшого, від наймолодшого до найстаршого) в одному або кількох стовпцях командою Дані => Сортування. Їх можна також сортувати за самостійно створеним списком (наприклад, «Великий», «Середній», «Маленький») або за форматом, зокрема за кольором клітинки чи шрифту або набором піктограм.

 

9.2. Фільтрація даних

 

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

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

Крім значень полів, кожен із списків містить ще три елементи: Всі, Перші 10, Умова. Елемент Всі слід використовувати для відображення на екрані всіх записів після застосування фільтру.

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

Останній елемент із перелічених – елемент Умова. Використовується він для формування складнішого критерію відбору, в якому можна застосовувати умовні оператори І та АБО.

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

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

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

Коли критерії задані, треба активізувати дію розширеного фільтру. Для цього треба вибрати команду Фільтр => Додатково з вкладки Дані. На екрані з’явиться діалогове вікно Розширений фільтр, в якому треба задати параметри розширеного фільтру. Зокрема вказати діапазон комірок для фільтру (поле Вихідний діапазон) і комірок з критеріями (поле Діапазон умов). Вказувати адресу діапазону критеріїв слід за допомогою виділення потрібного діапазону після розміщення курсору в полі Діапазон умов. Не можна включати в діапазон критеріїв пустий рядок.

 

 

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

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

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

Для обчислення значень проміжних підсумків використовують підсумкову функцію, наприклад, Сума (SUM) або Середнє арифметичне (AVERAGE).

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

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

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

 

Вкладення проміжних підсумків

 

У наявні групи підсумків можна вставляти проміжні підсумки для менших груп.

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

Використання зведених таблиць

 

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

 

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

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

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

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

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

Майстер зведених таблиць і діаграм також використовується для створення звітів зведених діаграм – графічного різновиду зведених таблиць.

Щоб запустити майстер, вибираємо Зведена таблиця в меню Дані.

Коли майстер відкриється, просто потрібно натиснути Готово.

 

Натиснення кнопки Готово повідомляє, що йому слід діяти стандартним чином, а саме:

*     використати дані зі списку Excel або з бази даних;

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

*     використати усі дані списку;

*    помістити область макета звіту на новий аркуш.

 

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

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

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

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

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

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

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

 

 

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

 

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

 

Основні елементи діаграми:

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

2. Область діаграми – область діаграми, обмежена осями, яка містить ряди даних.

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

4. Категорії – зазвичай назви даних, що відображаються під горизонтальною віссю діаграми. Зазвичай у вихідній таблиці розташовані по стовпцях.

5. Осі – лінії, що обмежують область діаграми і мають поділки зі значеннями обраних одиниць виміру. Горизонтальна ось (ось Х) відповідає категоріям. Вертикальна ось (ось Y) відповідає значенням даних в категоріях. У тривимірних діаграм є третя ось (ось Z), яка, зазвичай, відповідає часу.

6. Легенда – графічні зображення та підписи, які відповідають категоріям і полегшують читання діаграми.

7. Назви – текст, який відображає назву діаграми або осей.

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

9. Лінія тренду – графік функції певного виду (лінійна, логарифмічна, статична тощо) отриманий в результаті обробки даних ряду методом найменших квадратів, дозволяє наочно уявити тенденцію зміни даних.

 

Побудова діаграми

 

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

Але, перш за все, необхідно вибрати тип діаграми. Пропозиції можна переглянути, натиснувши кнопку Рекомендовані діаграми.

Відкриється діалог Вставлення діаграми вкладка Рекомендовані діаграми, де під час вибору типу діаграми слід керуватися міркуваннями найбільш наочного представлення певних цифрових даних (ліворуч буде представлено декілька типів рекомендованих діаграм, серед яких необхідно здійснити вибір). Для того щоб здійснити усвідомлений вибір, доцільно ознайомитися з призначенням різних типів діаграм і умовами їх застосування.

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

 

Можливий вибір наступних основних типів діаграм:

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

*     графік – дані відображаються у вигляді окремих точок, які об'єднуються лініями різних типів. Використовується для відображення тенденцій протягом певного часу (день, тиждень тощо), або коли порядок категорій чи точок даних є важливим;

*     секторна – дані подаються у вигляді секторів кола. Використовується для відображення пропорцій цілого;

*     гістограма – аналогічна стовпчастій, але зображення розташовуються горизонтально, використовується для порівняння значень різних категорій, коли діаграма відображає тривалість або коли текст категорій довгий;

*     з областями – аналогічна графіку, але області розташовуються під лініями і виділяються різними кольорами;

*     точкова – на діаграмі відображаються окремі точки з позначенням координат X, Y.

*     використовується для відображення зв’язків між наборами значень;

*     поверхня – подібна графіку, але дані відображаються у вигляді тривимірної поверхні;

*     біржова – відображає мінімальні і максимальні ціни, а також ціни на момент закриття торгів;

*     сонячне проміння – нагадує секторну діаграму, але в центрі вона має отвір;

*     пелюсткова – дані відображаються відносно центральної точки, а не щодо осей X, Y.

*     комбінована – ряди даних представляються комбінацією різних типів діаграм.

 

Форматування діаграми проводиться командами, розташованими на контекстних вкладках Конструктор і Формат, які автоматично стають доступними відразу з виділенням діаграми.

 

Питання для самоконтролю

1. Розкрийте поняття Сортування даних в табличному процесорі.

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

3. Розкрийте поняття Фільтрування даних в табличному процесорі.

4. Розкрийте поняття Діаграма даних в табличному процесорі.

5. Опишіть основні елементи діаграми.

Попередня тема

На початок

Наступна тема