Гіпермаркет Знань>>Інформатика>>Інформатика 11 клас>> Інформатика: Впорядкування, пошук та фільтрування даних в ЕТ.

Практична робота до предмету Інформатика 11 клас.

Тема «Тема. Аналіз даних в Excel».

Розгляд теми: Впорядкування, пошук та фільтрування даних в ЕТ.

                                                                         Лекція.

                                                     Тема. Аналіз даних в Excel.

 Мета заняття – засвоєння студентами основних засобів проведення аналізу даних в MS EXСEL.

Розв’язання економічних задач за допомогою засобів:  Подбор параметра; Поиск решения; Таблица подстановки.

План лекції

1   Засіб Подбор параметра
2   Засіб Поиск решения.
3    Засіб Таблица подстановки.

                                                             Пояснення нового матеріалу.

                                                                      Підбір параметра

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

Підбір параметра забезпечує дослідження даних, зв'язаних математичним рівнянням типу F(x)=А, у якому змінна х є шуканим параметром, а значення А – необхідним результатом.

Схема використання засобу Подбор параметра:

                                                                 Сервис → Подбор параметра…

В поле Установить в ячейке ввести адресу комірки з формулою обчислень
В поле Значение ввести необхідний результат
В поле Изменяя значение ячейки ввести адресу змінного параметра
Натиснути кнопку ОК
У вікні Результат подбора параметра

•    натиснути кнопку OK, якщо результат відповідає необхідним вимогам;
•    в протилежному випадку натиснути Отмена для повернення до початкових значень комірок.

Приклад використання засобу Подбор параметра  представлені на рис. 1, 2.

 
Рис.1 . Фрагмент вікна Excel з діалоговим вікном Подбор параметра

 
Рис. 2. Результати роботи засобу Подбор параметра

В завданні для досягнення необхідних планових показників підприємства (у прикладі – значення 1300), передбачається уточнити показники філії 2 (початкове значення – 120, уточнене значення після підбору параметрів – 168).

                                                                               Засіб Поиск решения.

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

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

•    Розрізняють такі дані оптимізаційної задачі: параметри управління, цільова функція і обмеження.

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

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

•    Кожне обмеження задачі в математичному записі має такий вигляд:

h(параметри управління) <=  b,
де h — деяка функція; b — дійсне число. Знак відношення може приймати інший вигляд(=,  > або <)

Інсталяція засобу Поиск решения

Якщо в меню Сервис немає пункту Поиск решения, цей засіб потрібно інсталювати. Для інсталяції засобу Поиск решения виконайте команду Сервис /Надстройки. Потім у групі Список надстроек виберіть пункт Поиск решения. Після цього натисніть кнопку ОК.

Засіб Поиск решения

•    Для запуску засобу Поиск решения виконайте команду Сервис /Поиск решения.
•    Діалогове вікно Поиск решения містить три групи полів, які потрібно заповнити. Це опції для цільової комірки, адреси комірок із змінними параметрами управління та поле обмежень.
•    У групі опцій цільової комірки зазначте адресу комірки (у полі Установить целевую ячейку) і тип оптимізаційної задачі. У підгрупі равной (тип задачі) є такі пункти:

-    максимальному значению (задача максимізації цільової функції);

-    минимальному значению (задача мінімізації цільової функції);

-    значению (задача рівності цільової функції конкретному числу).За замовчуванням цільовою вважається комірка, де розміщується курсор.

•    Адреси змінних параметрів управління задачі задайте в полі Изменяя ячейки.
•    Обмеження задачі задаються у групі Ограничения. їх вводять натисканням кнопки Добавить. Ця кнопка викликає діалогове вікно Добавление ограничения, де потрібно заповнити три поля. У лівому полі Ссылка на ячейку зазначають адресу лівої частини обмеження, у правому полі Ограничение — адресу правої частини обмеження або число, з яким порівнюється ліва частина, і в центральному полі вибирають тип обмеження: <=, =, >=, "цел" або "двоич". Останні два типи вказують, що ліва частина набуває лише цілих значень або відповідно значення 0 і 1. Обмеження додають до списку обмежень, використовуючи кнопку Добавить або ОК. При цьому:

-    кнопка Добавить дає змогу записати наступне обмеження;

-    кнопка ОК закриває вікно Добавление ограничения.

•    Редагувати обмеження можна за допомогою кнопки Изменить.
•    Параметри керування засобом Поиск решения задають у діалоговому вікні Параметры поиска решения, що викликається натисканням кнопки Параметры. Розглянемо параметри керування (їх значення за замовчуванням наведено в дужках).

-    Максимальное время (100 с) — максимальний час, відведений на розв'язування задачі. Якщо за цей час Поискрешения не знайде оптимального розв'язку, він повідомить результати останньої ітерації.
-    Предельное количество итераций (100) — обмеження на час роботи Поиск решения у термінах максимальної кількості ітерацій алгоритму.
-    Относительная погрешность (0,000001) — відносна точність, з якою шукається оптимальне значення цільової комірки.
-    Допустимое отклонение (5 %) —допустиме відхилення значення цільової комірки від оптимального, якщо в задачі є параметри, область зміни яких обмежена цілими числами.
-    Параметр сходимость (0,0001). Якщо відносна зміна у п'яти останніх ітераціях менша від цього параметра, оптимізаційна задача вважається розв'язаною. Цей параметр можна застосувати тільки для нелінійних задач.
-    Параметр линейная модель використовує методи лінійного програмування.
-    Параметр значения не отрицательные означає, що всі змінні параметри невід'ємні.
-    Параметр автоматическое масштабирование використовують тоді, коли значення змінних параметрів та оптимальне значення цільової комірки істотно різняться.
-    Параметр показывать результаты итераций виводить проміжні результати після кожної ітерації.
-    У нелінійних задачах у групі Оценки доцільніше вибрати опцію квадратичная.
-    Параметри разности і Метод поиска. Доцільніше залишити значення параметрів цих груп, що є за замовчуванням.

•   Для розв'язання задачі натисніть кнопку Выполнить.

                                                              Аналіз результатів

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

•    Для простого відображення розв'язку в робочому аркуші виберіть опцію Сохранить найденное решение.
•    Для відмови від отриманого розв'язку виберіть опцію Восстановить исходные значения.
•    Для відображення результатів на окремому аркуші виберіть тип звіту Результаты. При цьому на новому аркуші буде наведено інформацію про оптимальний план та оптимальне значення параметрів, а також про зв'язаність (рівність лівої та правої частин обмеження) чи незв'язаність обмежень.
•    Для отримання звіту про стійкість розв'язку щодо малих змін у цільовій функції та обмеженнях виберіть тип звіту Устойчивость.Зауважимо, що найважливішим результатом звіту про стійкість є множники Лагранжа (тіньові ціни). Множник Лагранжа для кожного обмеження вказує на миттєве покращення значення цільової функції, якщо збільшити (за умови відношення "<") праву частинуобмеження на 1.
•    Для аналізу допустимих змін кожного параметра за умови, що значення інших параметрів є фіксованими і такими, як в оптимальному плані, використовують тип звіту Пределы.
•    За допомогою миші можна вибрати кілька типів звітів одночасно.

                                                                       Моделі оптимізації

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

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

Задача.

Знайти кількість товарів, що потрібно продати в 4 кварталі, щоб дістати максимальний прибуток, якщо:

ціна на товар повинна бути <=  650 грн.;
кількість товару повинна бути цілим і додатним числом;
середня знижка >= 5%;
річна витрата на рекламу повинна бути <=5200 грн.

Розв'язання.

Створимо таблицю за наданим нижче зразком(рис 3) та проведемо попередні обчислення.

 
Рис. 3. Таблиця з вхідними даними.

Скористаємося засобом Поиск решения для знаходження кількості товару, середньої знижки і витрат на рекламу в 4-у кварталі, при яких в 4-у кварталі можна отримати максимальний прибуток:

1)    як цільову комірку вкажемо комірку Е16 і встановимо в полі Равной пункт максимальному значенню;
2)    в якості змінних виберемо комірки  Е6, Е8, Е12;
3)    задамо обмеження на значення комірок

•    ціна на товар (Е7) повинна бути менше або рівна 650 грн.;
•    кількість товарів (Е6) повинна бути цілим і додатним (>=0) числом;
•    середня знижка (Е8) повинна бути більше або рівна 5%;
•    річна витрата на рекламу (F12) не повинна перевищувати 5200 грн.

Створимо звіт Результаты за знайденим рішенням та збережемо зроблені зміни .
Етапи та результати розв’язання задачі представлені нижче(рис. 4)
 

 
Рис. 4. . Фрагмент вікна Excel з діалоговим вікном Поиск решения. Результати роботи

                                                                Таблиця підстановки

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

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

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

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

•    Виділити діапазон під представлення числових даних таблиці, включаючи формулу і змінні значення параметрів   
•    Команда Данные → Таблица подстановки  
•    Вказати значення підстановки по столбцам и/или по строкам → ОК

Приклади різних варіантів заповнення Таблицы подстановки наведені на рис. 5.

Наприклад, при визначенні виручки від реалізації мінеральної води в комірку С7 введена формула. Після завдання в діапазоні комірок B8:B16 необхідних даних для Таблиці підстановки і виділення діапазону B7:C16 викликаємо засіб Таблица подстановки. У діалоговому вікні в полі Подставлять значение по строкам вказана адреса комірки B7.

При розрахунку виручки від реалізації печива формула введена в комірку H7. Дані для Таблиці підстановки вказані в діапазоні комірок H6:L6. Перед застосуванням засобу Таблица подстановки виділяємо діапазон комірок H6:L7. У діалоговому вікні в полі Подставлять значение по столбцам  вказана адреса комірки H6.

Третій приклад з двома входами додаткових пояснень не вимагає.

 
Рис. 5. Приклади варіантів використання Таблиці підстановки

Література: [ 1, 2, 3, 7]

Надіслала вчитель інформатики Деснянського економіко – правового технікуму при МАУП м. Києва Рябець Людмила Іванівна

_{Календарно-тематичне планування, завдання школяру 11 класу з інформатики скачати, Інформатика онлайн}