У цій статті йдеться про те, як провести аналіз даних за допомогою зведеної таблиці. Для тренування Ви можете використовувати таблицю, доступну за цим посиланням (проста таблиця xlsx).
Перше, що може знадобитися проаналізувати за допомогою зведеної таблиці – це підбити проміжні підсумки. У нашому прикладі це може бути необхідність підрахувати обсяги продажу по всіх магазинах на кожну дату.
Для цього необхідно натиснути на будь-якому заголовку рядка зведеної таблиці (у нашому прикладі - це поля Дата, Точка продажуі Марка телефону), і у вкладках, що відкрилися Робота зі зведеними таблицямиперейти на вкладку Параметри. На ній потрібно натиснути кнопку Параметри поляу групі Активне поле.
У вікні першою закладкою буде закладка.
Відсутність такої закладки означає, що Ви не вибрали заголовок рядка, тобто курсор встановлений на комірці з числовим значенням.
На закладці Проміжні підсумки та фільтриВи можете вибрати умову для виведення проміжних підсумків. Пропонуються такі умови:
Встановивши автоматичне підбиття проміжних підсумків, ми отримаємо наступну таблицю, яка містить проміжні підсумки для кожної умови:
Якщо налаштування проміжних підсумків за допомогою команди Параметри поля, не дає видимих результатів, перевірте налаштування відображення проміжних підсумків за допомогою команди Проміжні висновкигрупи Макетвкладки Конструктор.
Допустимо, нам необхідно вивести проміжні підсумки тільки для дат, приховавши проміжні підсумки для точок продажу. Для цього клацніть будь-яке поле таблиці з назвою магазину та викличте контекстне меню. У ньому потрібно прибрати галочку з умови Проміжний підсумок: точка продажу. Як бачимо, проміжні підсумки залишилися лише дат:
Часто буває необхідно відсортувати дані зведеної таблиці для кращого їх сприйняття. Для цього достатньо вибрати поле, яким потрібно провести сортування, перейти на вкладку Загальні, у групі Редагуваннянатиснути на кнопку Сортування та фільтрта встановити потрібні Вам умови сортування.
Дуже корисною функцією для аналізу інформації у зведеній таблиці є можливість угруповання даних. Наприклад, нам потрібно згрупувати наші продажі у тижні місяця. Для цього потрібно виділити дати, що входять у перший тиждень (15.05-21.05):
Зверніть увагу, що для зручності виділення ми згорнули дані по окремих магазинах, скориставшись кнопкою + у лівій частині осередку під назвою магазину.
Далі потрібно виконати команду Угруповання по виділеномугрупи Групувативкладки Параметри. У таблиці з'явиться новий стовпець, у якому поле Група1буде об'єднувати обрані нами поля.
Залишиться тільки перейменувати назву групи шляхом простого редагування осередку:
Для скасування угруповання достатньо скористатися командою Розгрупуватиз цієї ж групи, попередньо вибравши поле, яке підлягає розгрупуванню. Зверніть увагу, що не можна розгрупувати поле, яке ми включили в умову побудови зведеної таблиці, наприклад поле Точка продажуабо Дата.
Розглянемо ще один спосіб виведення даних, який допоможе нам проаналізувати інформацію з нашої таблиці. Наприклад, нам потрібно дізнатися обсяги виручки над грошах, а вигляді відсотка від загального обсягу виручки протягом період продажів.
Для цього потрібно виділити будь-яку комірку в стовпці Виручка нашої зведеної таблиці. Після цього потрібно виконати команду Параметри поляу групі Активне полевкладки Параметри.
У діалоговому вікні необхідно перейти на вкладку Додаткові обчисленняі з меню вибрати пункт Частка від суми по стовпцю. Після натискання кнопки Ок, наша таблиця буде мати такий вигляд:
Якщо дані в таблиці будуть виводитися не у вигляді відсотків, перевірте налаштування числового формату осередків (це можна зробити відразу ж у діалоговому вікні Параметри поля, натиснувши кнопку Числовий формат, або викликавши відповідне вікно з контекстного меню).
Для аналізу списків даних (таблиць даних) в Excel 2007, які мають безліч рядків або записів, часто використовуються такі засоби, як зведені таблиці. Зведені таблиці значно полегшують перегляд, обробку та узагальнення даних у списках Excel 2007.
Щоб Excel працювала з введеними даними як із таблицею даних, а чи не з простим набором даних, необхідно її відформатувати як таблицю. Для цього натисніть кнопку Таблиця, розташовану у вкладці Вставка. У діалоговому вікні Створення таблиці (рис. 1) вкажіть передбачуваний діапазон, в якому буде розміщуватися таблиця, та встановіть прапорець Таблиця із заголовками.
Рис. 1.
До цього діапазону буде застосовано форматування, тобто. застосований експрес-стиль таблиці, заданий за умовчанням, активізуються контекстні інструменти під загальною назвою "Робота з таблицями", які входять у контекстну вкладку Конструктор. Для форматування таблиці також можна застосувати засіб "Форматувати як таблицю" на вкладці "Головна".
Рис. 2.
Для побудови звіту цієї таблиці доцільно застосувати потужний засіб "Зведена таблиця". Для застосування цього засобу до списків даних або таблиць даних необхідно активізувати одну з осередків таблиці даних, наприклад осередок таблиці "Залишки товарів на складі". Потім натисніть кнопку "Зведена таблиця", яка знаходиться на вкладці "Вставка" у групі "Таблиця" (рисунок 3).
Рис. 3.
У вікні діалогу "Створення зведеної таблиці" потрібно вибрати (виділити) таблицю або діапазон і вказати, куди слід помістити звіт (бажано на новий аркуш), потім клацнути ОК. Відкриється спеціальний майстер зведених таблиць (рисунок 4).
Рис. 4.
У лівій частині робочого аркуша відображається зображення звіту (Зведена Таблиця1), а у правій частині аркуша розташовані інструменти створення зведеної таблиці: чотири порожніх областей і список полів. Для побудови звіту треба у правій частині перетягнути необхідні поля у відповідні області зведеної таблиці: "Фільтр звіту", "Назва стовпців", "Назва рядків" та "Значення".
Наприклад, якщо вибрати поля: № складу, Найменування, Ціна (грн.) та перетягнути їх у відповідні області: "Назва стовпців", "Назва рядків" та "Значення", то у правій частині вони відображатимуться в цих областях. При цьому в лівій частині робочого листа буде побудовано зведену таблицю або звіт (рис. 5).
Рис. 5.
Слід зазначити, що в області "Значення" виконуються якісь математичні обчислення, наприклад, підсумовування (Сума по полю Ціна). Щоб змінити тип обчислень, треба в області "Значення" клацнути лівою кнопкою миші по полю "Сума по полю Ціна" і у меню вибрати команду "Параметри полів значень", потім у вікні діалогу "Параметри поля значень" вибрати потрібну функцію і клацнути на кнопки ОК.
Для зміни структури зведеної таблиці треба у правій частині аркуша перетягнути поля іншу область зведеної таблиці чи видалити. Слід зазначити, що з видалення поля треба перетягнути його межі таблиці.
Потужними засобами аналізу даних Excel 2007 є:
Кошти аналіз "що - якщо" поміщені на вкладці "Дані" у групі "Робота з даними", а "Пошук рішень" на вкладці "Дані" у групі "Analysis".
Підбір параметрів забезпечує вирішення задачі підбору параметра функції одного аргументу. Диспетчер сценаріїв призначений для створення сценаріїв (спочатку додаються кілька сценаріїв для різних випадків), а потім виконується перегляд сценаріїв з метою прогнозування процесу створення звіту за сценарієм.
Програма "Пошук рішень" призначена для вирішення складних систем рівнянь, лінійних та нелінійних задач оптимізації. В основі надбудови Solver лежать ітераційні методи.
Програма Excel – це не просто табличний редактор, але ще й потужний інструмент для математичних та статистичних обчислень. У додатку є безліч функцій, призначених для цих завдань. Щоправда, не всі ці можливості за промовчанням активовані. Саме до таких прихованих функцій відноситься набір інструментів «Аналіз даних». Давайте з'ясуємо, як його можна увімкнути.
Щоб скористатися можливостями, які надає функція «Аналіз даних», потрібно активувати групу інструментів "Пакет аналізу", виконавши певні дії у налаштуваннях Microsoft Excel. Алгоритм цих дій практично однаковий для версій програми 2010, 2013 та 2016 років, і має лише незначні відмінності у версії 2007 року.
Після виконання цих дій вказана функція буде активована, а її інструментарій доступний на стрічці Excel.
Тепер ми можемо запустити будь-який із інструментів групи «Аналіз даних».
Робота в кожній функції має власний алгоритм дій. Використання деяких інструментів групи «Аналіз даних»описані в окремих уроках.
Як бачимо, хоча блок інструментів "Пакет аналізу"і не активовано за умовчанням, процес його включення досить простий. У той же час, без знання чіткого алгоритму дій навряд чи користувач зможе швидко активувати цю дуже корисну статистичну функцію.
ЗАВДАННЯ № 1
Статистичний аналіз даних у програмі MS Excel
Мета роботи: навчитися обробляти статистичні дані за допомогою вбудованих функцій MS Excel; вивчити можливості Пакету аналізу та його інструменти: « Генерація випадкових чисел», «Гістограма» , « Описова статистика»на прикладі обробки вимірів швидкості руху.
Відповідно до методичних вказівок до лабораторної роботи «Вимірювання швидкості руху автомобілів» (за дисципліною «Дослідження та проектування автомобільних доріг») обробити експериментальні дані вимірювань методами математичної статистики у програмі Excel. Для чого:
1. Обчислити статистичні характеристики, використовуючи вбудовані функції: - Мінімальне значення швидкості руху Vмін;
Максимальне значення швидкості руху Vмакс; - Середнє значення швидкості руху Vср;
Стандартне відхилення S;
Стандартне відхилення середнього Sср;
Коефіцієнт Стьюдента (для визначення довірчого інтервалу) t; - Довірчий інтервал для Р = 0.95.
2. Отримати статистичні характеристики, використовуючи інструмент «Описова статистика» з додаткового пакету «Аналіз даних».
3. Побудувати гістограму розподілу швидкості руху.
4. Побудувати кумулятивну криву (криву накопиченої частості).
5. Побудувати теоретичну криву розподілу швидкості руху.
Для отримання достатньої кількості вихідних даних (результатів вимірювання швидкості) використовувати імітаційний експеримент за допомогою інструмента « Генерація випадкових чисел»доповнення «Аналіз даних».
За виконання п.п. 3 і 4 підібрати інтервал швидкостей («кишеню» – у термінології Excel), що дозволяє отримати найбільш симетричну гістограму, що демонструє нормальний закон розподілу.
Зразок виконання наведений у файлі Основи ПК1-Студент.xls.
Методичні вказівки
Припустимо, що ми виконали серію з 10 дослідів, вимірюючи деяку величину Х. Таблиця 1. Зразковий вид листа «Обробка експерименту»
Записи в колонках D і Е – це підказки, які допоможуть розібратися з тим, які характеристики ми будемо розраховувати. Колонка F у Вас має бути поки що порожньою, до неї будуть поміщені наші формули.
Обробку результатів почнемо з розрахунку числа дослідів n.
Для визначення числа значень використовується спеціальна функція, яка називається РАХУНОК. Для введення формули з функціями використовується Майстер функцій, який запускається командою "Вставка функції" через меню "Вставка" - "Функція" або кнопкою на панелі інструментів з позначенням f x .
Клацніть мишкою по осередку F6 , де має бути результат і запустимо Майстер функцій.
Перший крок роботи (рисунок 1) служить вибору потрібної функції.
Для обробки даних експерименту використовують статистичні функції. Тому насамперед у списку категорій вибираємо категорію «Статистичні». У другому вікні відображається список статистичних функцій.
Список функцій упорядкований за алфавітом, що дозволяє легко знайти потрібну нам функцію РАХУНОК («Підраховує кількість чисел у списку аргументів»).
Виділивши клацанням цю функцію, натискаємо кнопку Ok та переходимо до кроку 2.
Другий крок (рисунок 2) служить завдання аргументів функції.
Функції РАХУНОК треба вказати, які числа їй треба перераховувати, або в яких осередках знаходяться ці числа. Наступні два етапи обробки серії дослідів проводяться аналогічно.
У осередку F7 з допомогою функції СРЗНАЧ розраховується середнє значення вибірки, у осередку F8 – стандартне відхилення вибірки, з допомогою функції СТАНДОТКЛОН. .
Аргументами цих функцій служить той самий діапазон осередків.
Для розрахунку довірчого інтервалу необхідно визначити коефіцієнт Стьюдента. Він залежить від ймовірності помилки (при надійності, що зазвичай задається 95% ймовірність помилки становить 5%), і від числа ступенів свободи n-1).
Для знаходження коефіцієнта Стьюдента використовується статистична функція Excel СТЬЮДРАСПОБР (“Стьюдента розподіл зворотний”). Особливістю цієї функції є те, що перший аргумент число 5% (або 0,05) вводиться у відповідне вікно з клавіатури. Для другого вказуємо адресу осередку, де є значення n , потім дописуємо у вікні “-1”. Отримуємо запис “F6-1”.
Для знаходження довірчого інтервалу використовується нормальна формула множення. Звичайно, замість букв там повинні стояти адреси осередків, де знаходяться коефіцієнт Стьюдента та стандартне відхилення середнього. Як правило, значення довірчого інтервалу округляється до однієї значущої цифри, такий самий порядок оточення має бути й у середнього. Тому остаточний результат можна записати так: з 95% надійністю Х = 14,80±0,05 . На закінчення порахуємо відносну помилку визначення Х: = ДІ / Х СР (формула: "= F11 / F7"). Значення відносної помилки зазвичай виражають у відсотках, ми маємо 0,3%.
Для виконання завдань 2 та 3 використовується надбудова «Пакет аналізу» (з меню Сервіс .Аналіз даних Гістограма).
Для встановлення надбудови викликати меню Сервіс Надбудови та з запропонованого списку доступних до встановлення надбудов вибрати «Пакет аналізу» (див. Встановлення надбудов
Excel на ПК.doc).
