Узагальнення по темі.

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

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

1) Лінійчата (об'ємна лінійчата) смугова – відображає ряди даних у горизонтальній послідовності ( у вигляді горизонтальних смуг); кількісні дані розташовуються уздовж горизонтальної осі.

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

3) Кругова (об'ємна кругова) діаграма – відображає зв'язок окремих частин із загальним набором даних (тобто, частку кожної величини в їх загальній сумі) .

4) Графік (об'ємний графік) – являє собою графік з маркерами, які відповідають даним певної серії.

5) Точкова діаграма – відображає вид залежності між двома серіями даних. Може мати вигляд дискретних точок або графіка (у тому числі і вирівняного).

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

Створення діаграми складається з двох етапів. На першому етапі потрібно виділити діапазон даних, тобто, серії даних, категорію даних разом з підписами даних. Цей етап не є обов’язковим, але його виконання прискорить і дещо спростить побудову діаграми.

На другому етапі потрібно викликати майстер діаграм і заповнити поля відповідних діалогових вікон майстра, задаючи вигляд та параметри діаграми. Робота з майстром складається з 4-х кроків.

1 крок: вибір типу та виду діаграми.

2 крок: вибір розташування даних. Добавить та Удалить) .Крім того, можна змінити підпис даних, адресу комірок кожної серії та адресу діапазону категорії даних.

3 крок: введення параметрів діаграми.

4 крок: вибір розташування діаграми.

Перехід від одного кроку до іншого здійснюється натисненням кнопок Далее або Назад . Завершення побудови діаграми можна здійснити натисненням кнопки Готово на будь-якому кроці.

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

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

Питання теми:

· використання таблиць підстановки;

· розв’язання завдань оптимізації.

 

Основні терміни теми:

 

 

 

Тема 15. Аналіз даних електронних таблиць

1. Використання таблиць підстановки.

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

Розглянемо таке завдання: необхідно побудувати таблицю значень деякої функції F(x) для певного діапазону значень аргументів. Таку задачу можна розв’язати вже вивченим раніш способом: розмістити у одному стовпчику (рядку) значення аргументів, а в іншому - відповідні значення функції. При цьому достатньо записати функцію для однієї комірки, а для решти виконати копіювання формули (автозаповнення). Але такий спосіб має цілу низку недоліків:

по-перше, кожна формула буде обчислена окремо, що для великого набору даних вплине на швидкість опрацювання таблиці;

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

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

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

Використання таблиці підстановки з одним параметром здійснюється за таким алгоритмом:

1) В деякий діапазон комірок (рядковий або стовпчиковий) потрібно занести значення аргументу (вихідні дані).

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

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

4) Викликати засіб Таблица подстановки і заповнити поля відповідного діалогового вікна, що з’явиться (рис 15.1). При цьому:

а) якщо діапазон вихідних даних є рядковим, то заповнюється поле Подставлять значения по столбцам в: ;

б) якщо діапазон вихідних даних є стовпчиковим, то заповнюється поле Подставлять значения по строкам в: .

В будь-якому випадку заповнюється лише одне поле.

Приклад. Розглянемо обчислення таблиці функції Y = X2 на проміжку [1, 2] за допомогою таблиці підстановки. Задамо значення аргументу в комірках В5:В15. В суміжну комірку з початковим значенням аргументу запишемо функцію. В даному випадку початкове значення аргументу міститься в першій комірці діапазону – комірці В5. Отже, в комірку С5 запишемо функцію, в даному випадку В5^2. Аргументом цієї функції потрібно вказати комірку з початковим значенням Х. Далі виділяємо діапазон В5:С15 і викликаємо засіб Таблица подстановки… Оскільки дані являють собою стовпчиковий діапазон, то комірку з початковим значенням Х (комірку В5) вказуємо у полі Подставлять значения по строкам в: (рис 15.1). Результат обчислень наведений на рис 15.2.  

 

 

 

 

 

 

 

 

 

 

 

 

 

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

Приклад. Побудувати таблицю значень функції f(x,y) = x2 – y2. Використаємо таблицю підстановки з двома параметрами. Діапазони С8:С13 та D7:H7 містять значення аргументів х та у. До комірок E2 та F2 занесені початкові значення аргументів. Вони обрані довільно. Комірка С7 містить функцію; в даному випадку там записаний вираз =E2^2-F2^2. Діапазон D8:H13 містить значення функції. Результати наведені на рис 15.3.

  

 

2. Розв’язання завдань оптимізації.

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

2.1 Використання засобу Подбор параметра.

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

Алгоритм використання засобу має такий вигляд:

1) Записати в деяку комірку певне значення аргументу. Назвемо це значення початковим. Воно повинно бути допустимим, тобто, входити до області визначення функції.

2) Записати до іншої комірки функцію. В ролі параметра використати адресу комірки з початковим значенням аргументу.

3) Викликати засіб Подбор параметра. (команда Сервис – Подбор параметра).

4) Заповнити полі відповідного діалогового вікна, що з’явиться.

Приклад. Знайти корені рівняння x2 – 5x+6=0. Це означає , що потрібно підібрати таке значення параметра (змінної x), для якого функція прийме задане значення, рівне 0.

Нехай комірка А3 містить початкове значення параметра х, в даному випадку рівне 2, а комірка В3 – значення виразу f(x)=x2 – 5x+6. Знайдемо розв’язок рівняння f(x)=0.

Для цього необхідно виконати таку послідовність дій:

1) виділити комірку з формулою;

2) вибрати пункт меню Сервис – Подбор параметра;

3) заповнити текстові вікна діалогового вікна: (рис 15.4.)

 у вікні Установить в ячейке записуємо адресу В3– ;

 у вікні Значение – задаємо потрібне значення; у даному випадку рівне 0;

 у вікні Изменяя значение ячейки задаємо адресу комірки А3.

В результаті у комірці А3 буде значення 1,999007, а у комірці В3 – значення 0,000994 (рис 15.5). Оскільки процес підбору параметра носить ітераційний характер з певною точністю, то результат не точний а наближений.

  

 

Це приблизно відповідає точному значенню кореня х=2. Якби в ролі початкового значення аргументу було задане значення х=4, то одержаний розв’язок мав би вигляд х=3,0007; f(x)=0,000701; тобто, ближче до точного значення х=3.Отже, результат в значній мірі залежить від початкового значення аргументу..

  

 

 

Аналогічно даний метод можна застосувати для функціональних залежностей складнішого виду.

Засіб Подбор параметра можна застосовувати і в діаграмах. Розглянемо приклад. Нехай діапазон А3:А10 містить значення аргументу х, а діапазон В3:В10 – значення функції f(x) для цих значень аргументу. Нехай за результатами цієї таблиці побудована діаграма (наприклад, типу точкова.). Тоді, задаючи прямо на графіку потрібне значення функції, можна за допомогою засобу Подбор параметра знайти потрібне значення аргументу. Для цього необхідно виконати таку послідовність дій:

а) вибрати на діаграмі точку;

б) змінити її значення до потрібного рівня шляхом переміщення методом перетаскування маніпулятором;

в) заповнити діалогове вікно Подбор параметра , що з’явиться

На рис 15.6. зображений процес зміни значення функції для х=3,1. Пунктиром позначене нове значення, а нижче - реальне значення у заданій точці (в даному випадку воно дорівнює 0,194). В результаті в комірці А9, яка відповідала значенню х=3,1 , з’явиться нове значення, що відповідає значенню функції f(x)=0,194.

Зауважимо, що якби діаграма будувалась би не за функціональною залежністю, а за фіксованими даними (тобто в даному випадку діапазон В3:В10 містив би не значення функції, а деякі константи), то діалогове вікно не з’являлось би, і підбор параметра не відбувався би.

Недоліками даного засобу є те, що:

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

по-друге, неможливо знайте найбільше або найменше значення функції, а лише наперед задане;

по-третє, можна використовувати лише функціональні залежності від одного аргументу.

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

  

 

3. Використання надбудови Поиск решения.

Цей спосіб пошуку розв’язку екстремальних завдань більш потужний, ніж попередній, і, відповідно, має більше параметрів налагодження. Для його використання необхідно виконати такі дії:

1) Записати математичну модель завдання; тобто, записати початкові значення параметрів, функціональну залежність та граничні значення параметрів.

2) Обрати пункт меню Сервис – Поиск решения…

3) Заповнити параметри діалогового вікна Поиск решения (рис 15.7)

4) Натиснути кнопку Выполнить

В результаті з’явиться діалогове вікно з результатами пошуку розв’язку завдання (рис 15.8).

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

Приклад .Знайти найменше значення функції f(x)= x2 – 5x+6 на інтервалі [2, 4].

Для виконання завдання виконаємо таку послідовність дій:

1) у комірку А3 запишемо початкове значення аргументу (воно обирається довільно із заданого інтервалу). В даному випадку знову запишемо значення 0.

2) В комірку В3 запишемо функцію. В ролі параметра використаємо комірку А3. Ці два кроки аналогічні попередньому випадку.

3) Обираємо пункт меню Сервис – Поиск решения...

4) Заповнюємо поля вікна Поиск решения

· в поле Установить целевую ячейку запишемо адресу комірки В3;

· перемикач Равной встановлюємо в позицію Минимальному значению;

· в поле Изменяя ячейки записуємо адресу комірки з параметром – А3;

· в поле Ограничения записуємо обмеження на параметр (в даному випадку на комірку А3). Запис обмежень здійснюється за допомогою кнопки Добавить. При з’являється діалогове вікно Добавление ограничения , для якого необхідно заповнити відповідні поля. Помітимо, що межі інтервалу задані в комірках C3 та D3, що записано в обмеженнях;

5) Натискуємо кнопку Выполнить.  

 

 

В результаті одержимо такий результат: в комірці В3 міститься найменше значення на інтервалі – в даному випадку f(x)=–0,25, а в комірці А3 – відповідне значення аргументу х=2,5.

 

  

 

 

Аналізуючи графік функції на заданому інтервалі (рис 15.6) можна переконатись, що розв’язок знайдений вірно.

 

Узагальнення по темі.

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

Заповнення таблиці відбувається через пункт меню Данные – Таблица подстановки…

Використання таблиці підстановки з одним параметром здійснюється за таким алгоритмом:

1) В деякий діапазон комірок (рядковий або стовпчиковий) потрібно занести значення аргументу (вихідні дані).

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

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

4) Викликати засіб Таблица подстановки і заповнити поля відповідного діалогового вікна, що з’явиться. При цьому:

а) якщо діапазон вихідних даних є рядковим, то заповнюється поле Подставлять значения по столбцам в: ;

б) якщо діапазон вихідних даних є стовпчиковим, то заповнюється поле Подставлять значения по строкам в: .

В будь-якому випадку заповнюється лише одне поле.

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

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

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

Алгоритм використання засобу має такий вигляд:

1) Записати в деяку комірку певне значення аргументу. Назвемо це значення початковим. Воно повинно бути допустимим, тобто, входити до області визначення функції.

2) Записати до іншої комірки функцію. В ролі параметра використати адресу комірки з початковим значенням аргументу.

3) Викликати засіб Подбор параметра. (команда Сервис – Подбор параметра).

4) Заповнити полі відповідного діалогового вікна, що з’явиться.

Надбудова Поиск решения надає більш потужний спосіб пошуку розв’язку екстремальних завдань і, відповідно, має більше параметрів налагодження. Для його використання необхідно виконати такі дії:

5) Записати математичну модель завдання; тобто, записати початкові значення параметрів, функціональну залежність та граничні значення параметрів.

6) Обрати пункт меню Сервис – Поиск решения…

7) Заповнити параметри діалогового вікна Поиск решения;

8) Натиснути кнопку Выполнить.

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