Модифікація параметрів діапазону даних

Кожному типу діаграм відповідають певні параметри, змінюючи які можна виділити окремі дані на діаграмі. Наприклад, на гістограмі змінюється відстань між категоріями і розмір перекриття (відстань, на які стовпчики різноманітних рядів перекриваються). Слід помітити, що якщо в гістограмі відображається тільки один діапазон даних, то всі стовпчики (за замовчанням) заливаються одним кольором. При необхідності різноманітні стовпчики заливаються різними кольорами. На графіку можна водночас відобразити декілька діапазонів даних різноманітними лініями або використати маркери ліній, а також формат ліній самих графіків для того, щоб розрізняти серію даних. Щоб змінити параметри відображення діапазону даних на діаграмі, виконайте наступне:

1. В списку Элементы диаграммы натисніть на ряд «Х».

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

2. Натисніть на кнопці Формат рядов данных.

 

Приклад виконання роботи представлено в додатку 1.

 

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

Додаток 1.

 

Проаналізуємо табличну інформацію.

 

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

 

2.1. Виділимо діапазон А2:D8.

2.2. За допомогою піктограми Мастер диаграмм почнемо побудову діаграми. (Рис. Приклад 1)

 

Рис. Приклад 1

2.3. В діалоговому вікні вибираємо потрібний графік та натискаємо кнопку Далее>.

2.4. В другому вікні контролюємо вибраний діапазон та ряди діаграми.

2.5. В третьому вікні вводимо назву діаграми та вісей, можемо змінити формат осей, лінію сітки та легенду. (Рис. Приклад 2)

 

 

 

Рис. Приклад 2

 

2.6. В четвертому вікні вибираємо місце розташування діаграми – на окремому листі чи в поточному та натискаємо на кнопці Готово. Одержуємо побудовану діаграму. (Рис. Приклад 3)

3. Аналогічно вибираємо діапазон даних для інших типів діаграм та виконуємо дії по створенню діаграми. (Рис. Приклад 4)

4. На прикладі свого варіанту виконуємо зміни в легенді, форматі діаграм, діапазоні даних по алгоритмам, що представлені в теоретичній частині.

 

Обсяг продажу за грудень 1998 року
№з.п.	Підприємство	план	факт
тис.грн	тис.грн
	Універмаг
	Маг.Олена
	Маг.Світлана
	У Коробкіна
	Швидко

 

 

Рис. Приклад 3

 

Обсяг продажу за грудень 1998 року
№з.п.	Підприємство	план	факт	перевищення	недовиконання
тис.грн	тис.грн
	Універмаг
	Маг.Олена
	Маг.Світлана
	У Коробкіна
	Швидко

 

Рис. Приклад 4

Лабораторна робота №5

ЗНАХОДЖЕННЯ КОРЕНІВ НЕЛІНІЙНОГО РІВНЯННЯ.

 

 

Мета роботи – придбати навички знаходження коренів нелінійного рівняння засобів Excel.

Короткі теоретичні вiдoмocmi

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

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

Візьмемо для прикладу квадратне рівняння х²-5х+6=0. Для знаходження коренів рівняння виконаємо наступні дії:

· В комірку С3 (рис. 1) введемо формулу для обчислення значення функції, що стоїть в рівнянні зліва від знаку рівності. Як аргумент використовуємо посилання на комірку С2, тобто =С2^2-5*C2+6.

Рис. 1. Вікно діалогу Подбор параметра

· У вікні діалогу Подбор параметра (рис. 1) в полі Установить в ячейке введемо посилання на комірку з формулою, в полі Значение - бажаний результат, в полі Изменяя значения ячейки – посилання на комірку, в якій зберігатиметься значення параметра, що підбирається (вміст цієї комірки не може бути формулою).

· Після натиснення на кнопку Ok Excel виведе вікно діалогу Результат подбора параметра. Якщо підібране значення необхідно зберегти, то натисніть на Оk, і результат буде збережений в комірці, заданій раніше в полі Изменяя значения ячейки. Для відновлення значення, яке було в комірці С2 до використання команди Подбор параметра, натисніть кнопку Отмена.

При підборі параметра Excel використовує ітераційний (циклічний) процес. Кількість ітерацій і точність встановлюються в меню Сервис/Параметры/вкладка Вычисления. Якщо Excel виконує складну задачу підбору параметра, можна натиснути кнопку Пауза у вікні діалогу Результат подбора параметра і зупинити обчислення, а потім натиснути кнопку Шаг, щоб виконати чергову ітерацію і проглянути результат. При розв’язанні задачі в покроковому режимі з'являється кнопка Продолжить - для повернення в звичайний режим підбору параметра.

Повернемось до прикладу. Знову виникає питання: як одержати другий корінь? Як і у попередньому випадку необхідно задати початкове наближення. Це можна зробити таким чином (рис. 2,а):

 

б   Рис. 2. Пошук другого кореня

 

· В комірку Х (С2) вводимо початкове наближення.

· В комірку Хi (С3) вводимо формулу для обчислення чергового наближення до кореня, тобто
=X-(X^2-5*X+6)/(2*X-5).

· В комірку С4 помістимо формулу, яка задаватиме обчислення значення функції, що стоїть в лівій частині початкового рівняння, в точці Хi.

· Після цього вибираємо команду Подбор параметра, де в якості комірки, що змінюється, приймаємо комірку С2. Результат обчислень зображений на рис. 2,б (в комірці С2 - кінцеве значення, а в комірці С3 - попереднє).

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

Пошук розв’язку

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

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

Знайти: х₁, х₂, …, х_n.

такі, що: F(х₁, х₂, …, х_n)> {Max; Min; = Value}

при обмеженнях: G(х₁, х₂, …, х_n)> { £ Value; ³ Value; = Value}

Шукані змінні – комірки робочого листу Excel – називаються регульованими комірками. Цільова функція F(х₁, х₂, …, х_n), іноді називається просто ціллю, повинна задаватися у вигляді формули в комірці робочого листа. Ця формула може містити функції, визначені користувачем, і повинна залежати (посилатися) від регульованих комірок. У момент постановки задачі визначається, що робити з цільовою функцією. Можливий вибір одного з варіантів:

· знайти максимум цільової функції F(х₁, х₂, …, х_n);

· знайти мінімум цільової функції F(х₁, х₂, …, х_n);

· досягти того, щоб цільова функція F(х₁, х₂, …, х_n) мала фіксоване значення: F(х₁, х₂, …, х_n)= а.

Функції G(х₁, х₂, …, х_n) називаються обмеженнями. Їх можна задати як у вигляді рівності, так і нерівностей. На регульовані комірки можна накласти додаткові обмеження: невід’ємності і/або цілочисельності, тоді шуканий розв’язок шукається в області невід’ємних і/або цілих чисел.

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

Розглянемо, як скористатися Поиском решения на прикладі того ж квадратного рівняння.

Рис. 3. Вікно діалогу Поиск решения

 

Після відкриття діалогу Поиск решения (рис. 3) необхідно виконати наступні дії:

1. У поле Установить целевую ячейку ввести адресу комірки, що містить формулу для обчислення значень функції, що оптимізується, в нашому прикладі цільова комірка – це С4, а формула в ній має вигляд: =C3^2- 5*C3 + 6;

2. Для максимізації значення цільової комірки, встановити перемикач максимальному значению в положення 8, для мінімізації використовується перемикач минимальному значению, в нашому випадку встановлюємо перемикач в положення значенню і вводимо значення 0;

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

4. У полі Обмеження за допомогою кнопки Добавить ввести всі обмеження, яким повинен відповідати результат пошуку: для нашого прикладу обмежень задавати не потрібно;

5. Для запуску процесу пошуку розв’язку натиснути кнопку Выполнить.

 

Для збереження одержаного розв’язку необхідно використати перемикач Сохранить найденное решение у вікні діалогу, що відкрилося, Результаты поиска решения. Після чого робочий лист прийме вигляд, представлений на рис. 4.

 

Рис. 4. Результати пошуку

 

 

Завдання.

 

Знайти корінь нелінійного рівняння f1(x)=f2(x) на заданому відрізку [ a,b ] засобами Excel за допомогою опції Подбор параметра і використовуючи можливості Поиск решения при обмеженнях корінь ≥ a і корінь ≤ b.

Варіанти завдань наведені в таблиці.

 

ВАРІАНТИ ЗАВДАНЬ

№ п/п	Рівняння	А	В
	tg(x) = 1/х		π/2
	x - x^3 + 1 =0
	x + 3 = x^3
	x + x^3 - 5 =0
	2x + x^5 - 1 =0
	1 + x = x^3
	1 - 3 x + x^5=0
	1 - 5 x + x^4=0

 

Лабораторна poбoтa №6