- •Міністерство освіти, науки, молоді та спорту України
- •Основи роботи в табличному процесорі Excel 2010
- •Оновлене робоче середовище Excel 2010
- •1 2 4 5 6 7 8 10 3
- •Керування книгами й аркушами
- •Введення й редагування даних, автозаповнення
- •Форматування даних
- •Обчислення в Excel 2010, формули та функції
- •Створення формул
- •Копіювання формул. Відносні й абсолютні посилання
- •Застосування функцій
- •Графічний аналіз даних в Excel 2010
- •Створення діаграм
- •Побудова графіків функцій
- •Створення нестандартних діаграм
- •Створення діаграм типу Поверхня
- •Використання спарклайнів (інфокривих)
- •Додаткові засоби аналізу даних в Excel 2010
- •Підбір параметра
- •Пошук рішення
- •Обробка списків (баз даних)
- •Створення зведених таблиць
- •Лабораторні роботи Лабораторна робота 1Основи роботи в табличному процесорі Excel Завдання
- •Лабораторна робота 2Застосування вбудованих функцій в Excel Завдання
- •Лабораторна робота 3Побудова діаграм в Excel Завдання
- •Лабораторна робота 4Аналіз даних в Excel Завдання
- •Лабораторна робота 5Обробка списків, створення зведених таблиць Завдання
- •Список рекомендованої літератури
- •Методичні вказівки до самостійної роботи над розділом "Застосування Microsoft Excel 2010"
Підбір параметра
Підбір параметра призначений для простого аналізу даних типу «що-якщо». За допомогою цього засобу можна вирішувати рівняння з одним невідомим. Підбір невідомого значення виконують методом «зворотних обчислень» (послідовним наближенням невідомого значення в комірці, що впливає на результат, до певного заданого значення результату в цільовій комірці. Технологія підбору параметра така:
за необхідності ввести в комірки аркуша постійні величини, що впливають на результат;
зарезервувати комірку під невідому величину (впливова комірка);
увести в цільову комірку формулу з посиланням на впливову;
установити курсор у цільову комірку;
вибрати вкладку стрічки Дані Аналіз «що якщо» – Підбір параметра;
у вікні підбору параметра вказати бажане значення результату та впливову комірку (рис. 26).
Приклад: Продаж кави, відома ціна однієї чашки (1,75 грн), формула для визначення виторгу (виторг = ціна чашки * кількість). Необхідно визначити кількість чашок, за якої виторг досягне значення 5000 грн.
Рис. 26. Підбір параметра
Пошук рішення
Використовується для розв’язання задач оптимізації пошуку мінімуму, максимуму або заданого значення функції декількох змінних з урахуванням обмежень на значення змінних та їх співвідношення.
Функція Пошук рішення перебуває на вкладці стрічки Дані і може бути відсутня в Excel 2010 при стандартному варіанті установки пакета Microsoft Office. Для додавання функції Пошук рішення необхідно застосувати настроювання стрічки Файл Параметри – Надбудови, вибрати в нижній частині вікна розділ Керування: Надбудови Excel, натиснути кнопку [Перейти] і включити опцію «Пошук рішення». За наявності установчих файлів Microsoft Office компонент буде автоматично доданий до функцій вкладки Дані.
Відправною в процесі пошуку оптимального рішення є створена на робочому аркуші модель обчислення, яка включає:
Цільову комірку, значення в якій повинне бути максимізоване, мінімізоване або ж дорівнювати певному значенню; містить формулу, що прямо або опосередковано посилається на змінювані комірки;
Змінювані комірки невідомі задачі, комірки, значення в яких будуть послідовно (методом ітерацій) змінюватися доти, поки не буде отримане оптимальне значення в цільовій комірці;
Обмеження на співвідношення змінних формули з посиланнями на змінювані комірки.
У моделі пошуку рішення допускається до 200 змінюваних комірок (змінних), по два обмеження для кожної змінюваної комірки та 200 обмежень для всієї моделі.
Технологія пошуку рішення така:
за необхідності ввести в комірки аркуша сталі величини, які впливають на результат;
зарезервувати комірки під невідомі величини (змінювані комірки);
увести в цільову комірку формулу із прямими або непрямими посиланнями на змінювані комірки;
увести формули для обмежень із посиланнями на змінювані комірки;
установити курсор у цільову комірку;
вибрати функцію вкладки Дані Пошук рішення;
у вікні пошуку рішення вказати вид оптимізації та діапазон змінюваних комірок (рис. 27);
для введення обмежень натиснути кнопку [Додати] у вікні пошуку рішення (рис. 28), для введення наступного обмеження натиснути кнопку [Додати] у вікні додавання обмежень, для завершення кнопку [ОК];
натиснути кнопку [Знайти рішення] у вікні пошуку рішення.
Рис. 27. Вікно пошуку рішення
Рис. 28. Вікно додавання обмежень під час пошуку рішення
В економічних задачах лінійної оптимізації, як правило, необхідні так звані природні обмеження на змінювані комірки їх значення повинні бути невід’ємними (>= 0) та цілими, перше обмеження можна задати за допомогою опції у вікні пошуку рішення (рис. 27), а друге – за допомогою кнопки [Параметри] у вікні пошуку рішення (рис. 27) – після її натиснення необхідно відключити опцію «Ігнорувати цілочислені обмеження».
Моделі пошуку розв’язку оптимізаційних задач слід розміщати на окремих аркушах, тоді параметри пошуку рішення моделі зберігаються у вікні пошуку рішення кожного аркуша.