47355

Аналіз даних в середовищі MS Excel

Лекция

Информатика, кибернетика и программирование

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

Украинкский

2014-03-30

110.18 KB

19 чел.

ЛЕКЦІЯ 8

Тема: Аналіз даних в середовищі MS Excel

Мета: Ознайомити з засобами аналізу даних в середовищі MS Excel, можливостями аналізу за допомогою функцій і таблиць підстановок; набути навичок проведення аналізу за допомогою зведених таблиць

План

  1.  Проведення аналізу за допомогою таблиць підстановок.
  2.  Аналіз даних за допомогою зведених таблиць та діаграм.
  3.  Проведення аналізу за допомогою таблиць підстановок.

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

Використовуючи засоби аналізу «якщо-то» у програмі MS Excel, можна експериментувати з декількома різними наборами значень в одній або кількох формулах і аналізувати всі отримані результати.

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

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

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

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

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

Наступним кроком потрібно виділити діапазон комірок, що містить формули і значення підстановки та виконати команду Таблиця даних… колекції Аналіз «якщо» групи Знаряддя даних вкладки Дані. У результаті виконання команди відкривається діалогове вікно Таблиця даних ввести посилання на комірку введення. При цьому, якщо аргументи формули записані у стовпці, то посилання на комірку введення вказати у полі Підставляти значення за рядками до, якщо ж аргументи формули записані у рядку, то посилання на комірку введення вказати у полі Підставляти значення за стовпцями до. Для завершення операції потрібно натиснути кнопку ОК і виділений діапазон заповниться потрібними значеннями.

Рис. 8.1. Діалогове вікно Таблиця даних

Таблиці підстановок з двома змінними використовують одну формулу з двома наборами значень. Тобто формула таблиці підстановок з двома змінними повинна посилатися на дві комірки введення.

Як і у випадку з таблицею підстановки з однією змінною для того, щоб використати таблицю підстановок з двома змінними спочатку потрібно створити робочий лист з початковими даними та у певну комірку робочого аркуша ввести формулу для розрахунку. Комірка з формулою повинна знаходить у верхньому лівому кутку діапазону таблиці підстановки. Після цього необхідно ввести початкові дані – значення аргументів. Перший аргумент нижче комірки з формулою, другий – правіше комірки з формулою. Виділивши створений діапазон початкових даних виконати команду Таблиця даних… колекції Аналіз «якщо» групи Знаряддя даних вкладки Дані. У діалоговому вікні Таблиця даних визначити комірки введення і натиснути кнопку ОК, у результаті чого комірки виділеного діапазону будуть заповнені обчисленими значеннями.

Всі формули в таблиці підстановки – це масив з формул вигляду {=TABLE(;E13)} (таблиця з однією змінною) або {=TABLE(Е12;E13)} (таблиця з двома змінними). Тому у таблиці підстановки заборонено редагування окремо взятої формули або окремо взятого результату всередині таблиці підстановки.

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

  1.  Аналіз даних за допомогою зведених таблиць та діаграм.

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

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

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

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

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

Зведені таблиці спеціально розроблені для таких цілей:

  1.  виконання запитів, пов’язаних із великими обсягами даних, різними зручними способами;
  2.  проміжного підсумування й обчислення сукупного значення числових даних, зведення даних за категоріями та підкатегоріями, виконання додаткових обчислень і створення налаштовуваних формул;
  3.  розгортання та згортання рівнів даних для фокусування на результатах, а також детальний перегляд лише потрібної інформації зі зведених даних;
  4.  переміщення рядків у стовпці або стовпців у рядки («зведення») для перегляду різних зведень даних джерела;
  5.  фільтрування, сортування, групування й умовного форматування найкорисніших і найцікавіших даних для зосередження уваги на потрібній інформації;
  6.  подання стислих і ефективних звітів із примітками в Інтернеті або на папері.

Для створення зведеної таблиці можна виділити діапазон, який містить дані, що є джерелом зведення або будь-яку комірку цього діапазону. При цьому необхідно переконатися в тому, що стовпці діапазону комірок мають підписи або що підписи відображаються в таблиці, а також що в ряді комірок або в таблиці немає порожніх рядів. Після цього потрібно натиснути кнопку Зведена таблиця групи Таблиці вкладки Вставлення або виконати команду Зведена таблиця колекції Зведена таблиця. У результаті чого відображається діалогове вікно Створення зведеної таблиці. У цьому діалоговому вікні автоматично встановлюється перемикач Виберіть таблицю або діапазон, а в полі Таблиця/діапазон визначається діапазон виділених даних, які потрібно використати як початкові дані. Хоча MS Excel автоматично визначає діапазон для звіту зведеної таблиці, але його можна замінити, ввівши інший діапазон або його ім’я.

Рис. 8.2. Діалогове вікно Створення зведеної таблиці

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

При активізації довільного розділу макету зведеної таблиці відкриваються контекстні вкладки Знаряддя для зведених таблиць / Параметри та Конструктор.

Для того, щоб додати поля до зведеної таблиці достатньо встановити прапорці поряд іменем поля у вікні Список полів зведеної таблиці. За замовчуванням нечислові поля додаються до області «Підписи рядків», числові поля – до області «Значення», дані дати й часу – до області «Підписи стовпців».

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

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

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

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

Перегляд даних із виконанням таких дій:

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

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

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

Рис. 8.3. Діалогове вікно Параметри значення поля

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

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

Для створення вибірки зведеної таблиці потрібно активізувати довільну комірку зведеної таблиці, для якої потрібно створити вибірку. На контекстній вкладці Знаряддя для зведених таблиць / Параметри у групі Сортування й фільтр натиснути кнопку Вставити роздільник при цьому відкриється діалогове вікно Вставлення роздільника (рис. 8.4.). У цьому діалоговому вікні потрібно встановити прапорці поряд з полями зведеної таблиці, для яких потрібно створити вибірку й натиснути кнопку ОК закриваючи вікно Вставлення роздільника. Вибірка відображатиметься для кожного вибраного поля. У кожній вибірці можна вибрати елементи, які потрібно відфільтрувати.

Рис. 8.4. Діалогове вікно Параметри значення поля

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

Для того, щоб відключити вибірки необхідно активізувати зведену таблицю та виконати команду Зв’язки роздільника колекції Вставити роздільник групи Сортування й фільтрування контекстної вкладки Параметри. У діалоговому вікні Підключення роздільника (рис. 8.5.) вимкнути прапорці відповідних вибірок та натиснути кнопку ОК для закриття цього вікна.

Для того, щоб видалити існуючу вибірку потрібно або виділити вибірку та натиснути клавішу Delete, або ж виконати команду Видалити <ім’я роздільника> контекстного меню вибраної вибірки.

Рис. 8.5. Діалогове вікно Підключення роздільника

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

Для форматування зведеної таблиці можна використати спеціально створені стилі, які подано у групі Стилі зведеної таблиці контекстної вкладки Знаряддя для зведеної таблиці / Конструктор. Окремо виділену комірку зведеної таблиці можна від форматувати за допомогою відповідних інструментів вкладки Основне або діалогового вікна Формат клітинок.

Для того, щоб використати ієрархічні властивості даних в MS Excel необхідно створити дерево рівнів за допомогою операції групування елементів. Для цього потрібно виділити комірки, які містять назви даних (в області рядків або стовпців), що будуть належати одному рівню, та натиснути кнопку Вибір групи групи Група контекстної вкладки Параметри або виконати команду Групувати… виділених комірок зведеної таблиці. Після цього можна змінити назви отриманих полів зовнішнього рівня ієрархії. Для керування відображенням даних груп використовуються відповідні елементи управління, які можна приховати або відобразити поруч з назвами груп використовуючи кнопку Кнопки +/– групи Відобразити контекстної вкладки Параметри.

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

Рис. 8.6. Діалогове вікно Докладно

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

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

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

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

Зведену діаграму можна розмістити як об’єкт на будь-якому робочому аркуші робочої книги.


 

А также другие работы, которые могут Вас заинтересовать

18884. Евангельская тема в русском искусстве XIX века. А.А.Иванов, Н.Н Ге, И.Н.Крамской, В.Д. Поленов 31.82 KB
  Евангельская тема в русском искусстве XIX века. А.А.Иванов Н.Н Ге И.Н.Крамской В.Д. Поленов. Центральной фигурой в живописи середины века был Александр Андреевич Иванов 18061858. Путь А. Иванова никогда не был легким за ним не летела крылатая слава. При жизни его талант цени...
18885. Рококо. Интерьер - как ансамбль 25.86 KB
  Рококо. Интерьер как ансамбль. Характеристика стиля на примере Китайского дворца Антонио Ренальди в Ораниенбауме. Рококо декоративный стиль в искусстве и архитектуре зародившийся во Франции в начале 18 в. достигло апогея при Людовике XV. и распространившийся по все...
18886. Бытовой жанр в русской живописи 19в. П.А.Федотов, В.Г.Перов, И.Е. Репин, Передвижники 25.74 KB
  Бытовой жанр в русской живописи 19в. П.А.Федотов В.Г.Перов И.Е. Репин Передвижники. П.А.Федотов ― добрая ирония и красота предметного мира. Сватовство майора Вдовушка Анкор ещё анкор. В.Г.Перов ― острота социальной критики Крестный ход на Пасху Тройка Провод...
18887. Постимпрессионизм. В. Ван Гог, П.Гоген, П.Сезанн 23.7 KB
  Постимпрессионизм. В. Ван Гог П.Гоген П.Сезанн. Постимпрессионизм от лат. post после и импрессионизм условное собирательное обозначение основных направлений французской живописи конца XIX начала XX вв. Мастера постимпрессионизма многие из которых ранее примыкали к имп...
18888. Русский классицизм. Универсальный стиль эпохи. От Екатерины II до Александра I 27.9 KB
  Русский классицизм. Универсальный стиль эпохи. От Екатерины II до Александра I. Русский классицизм архитектурный стиль распространённый в России во второй половине 18 19вв. Особенностью русского классицизма являлось эклектическое сочетание в одном произведении разнос...
18889. Художественное объединение «Мир искусства» 28.78 KB
  Художественное объединение Мир искусства. Мир искусства русское художественное объединение. Оформилось в конце 1890х гг. официально в 1900 в Петербурге на основе кружка молодых художников и любителей искусства во главе с А. Н. Бенуа и С. П. Дягилевым. Как выставочный...
18890. Стиль Ампир. Величие и закат классицизма 22.13 KB
  Стиль Ампир. Величие и закат классицизма. Ампи́р от фр. empire империя стиль позднего высокого классицизма в архитектуре и прикладном искусстве. Возник во Франции в период правления императора Наполеона I; развивался в течение трёх первых десятилетий 19 века; сменил...
18891. Нелинейная архитектура 24.7 KB
  Нелинейная архитектура. Архитектура последнего десятилетия XX века ориентированная на новую сверхмощную компьютерную технологию продемонстрировала стремление к небывалому авангардистскому по сути прорыву в области формообразования на фоне которого переломы пост
18892. От «социалистического реализма» к « суровому стилю». Живопись советского периода 30х – 60х гг. XX века 28.82 KB
  От социалистического реализма к суровому стилю. Живопись советского периода 30х 60х гг. XX века. В 30е годы Рабочий и колхозница Мухиной. В начале 30х годов произошел серьезный перелом. 1932 год вышло постановление ЦКВКПБ о перестройке литературных художественных о