47355

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

Лекция

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

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

Украинкский

2014-03-30

110.18 KB

15 чел.

ЛЕКЦІЯ 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 дозволяє створювати зведені діаграми, причому їх будувати можна як на основі вже існуючих зведених таблиць, а також на основі початкових таблиць.

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

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

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


 

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

81781. Преднаука и наука. Генезис науки и проблема периодизации её истории 31.74 KB
  Генезис науки и проблема периодизации её истории. Исследуя историю любого материального или духовного явления в том числе и науки следует иметь в виду что это сложный диалектический поступательный процесс появления различий включающий в себя ряд качественно своеобразных этапов фаз и т. Применяя сказанное о периодизации к истории науки следует прежде всего подчеркнуть следующее. Вопрос о периодизации истории науки и ее критериях по сей день является дискуссионным и активно обсуждается в отечественной и зарубежной литературе.
81783. Средневековая наука. Организация науки в средневековых университетах 33.78 KB
  Первый из них факультет свободных искусств trium был наиболее многочисленным и считался подготовительным для трех других факультетов: медицинского юридического и теологического – самого малочисленного но обучение на котором было самым продолжительным. Таким образом Парижский университет оказался в плену противоречивых тенденций: превратиться в центр беспристрастных исследований связанных с изучением античного наследия но всегда стоящих перед опасностью впасть в инакомыслие либо подчинить исследование религиозным целям и тем самым...
81784. Формирование опытной науки в новоевропейской культуре 31.1 KB
  Изменяется роль человека в мире. Происходит постепенная смена мировоззренческой ориентации: для человека значимым становится посюсторонний мир автономным универсальным и самодостаточным становится индивид. Отсюда и характерное для эпохи Возрождения стремление познать принципы функционирования механизмов приборов устройств и самого человека.
81785. Наука в собственном смысле слова: классическая наука, неклассическая и постклассическая 30.52 KB
  Таким образом основные стороны бытия науки это вопервых сложный противоречивый процесс получения нового знания; вовторых результат этого процесса т. объединение полученных знаний в целостную развивающуюся органическую систему а не простое их суммирование; втретьих социальный институт со всей своей инфраструктурой: организация науки научные учреждения и т.; этос нравственность науки профессиональные объединения ученых ресурсы финансы научное оборудование система научной информации различного рода коммуникации ученых и т....
81786. Формирование науки как профессиональной деятельности. Возникновение дисциплинарно организованной науки 35.37 KB
  Возникновение дисциплинарно организованной науки. Несмотря на большое значение великих прозрений античности влияние науки арабов средневекового Востока гениальных идей эпохи Возрождения естествознание до XVII в. У истоков науки как профессиональной деятельности стоит Френсис Бэкон 1561 1626 утверждавший что достижения науки ничтожны и что она нуждается в великом обновлении.
81787. Становление социальных и гуманитарных наук 36.39 KB
  Если на этапе преднауки как первичные идеальные объекты так и их отношения соответственно смыслы основных терминов языка и правила оперирования с ними выводились непосредственно из практики и лишь затем внутри созданной системы знания языка формировались новые идеальные объекты то теперь познание делает следующий шаг. Оно начинает строить фундамент новой системы знания как бы сверху по отношению к реальной практике и лишь после этого путем ряда опосредствований проверяет созданные из идеальных объектов конструкции сопоставляя их с...
81788. Научное знание как система, его особенности и структура 31.63 KB
  Рассмотрим основные особенности научного познания или критерии научности. Его основная задача обнаружение объективных законов действительности природных социальных общественных законов самого познания мышления и др. Нацеленность науки на изучение не только объектов преобразуемых в сегодняшней практике но и тех которые могут стать предметом практического освоения в будущем является важной отличительной чертой научного познания. Существенным признаком научного познания является его системность...
81789. Эмпирический и теоретический уровни научного знания, критерии их различия 30.8 KB
  Эмпирический уровень научного познания включает в себя наблюдение эксперимент группировку классификацию и описание результатов наблюдения и эксперимента моделирование. Теоретический уровень научного познания включает в себя выдвижение построение и разработку научных гипотез и теорий; формулирование законов; выведение логических следствий из законов; сопоставление друг с другом различных гипотез и теорий теоретическое моделирование а также процедуры объяснения предсказания и обобщения. Соотношение эмпирического и теоретического...