Четвертий крок роботи Мастера диаграмм

На останньому кроці роботи Майстра діаграм задається розміщення діаграми: на окремому робочому листку або на листку з таблицею (рис. 12.5).

Рисунок 12.5 – Четвертий крок роботи Майстра діаграм

Завершення роботи Майстра діаграм відбувається натисненням на кнопку Готово.

Створена діаграма з'явиться на робочому листку (рис. 12.6).

Рисунок 12.6 – Створена діаграма

Як приклад розглянемо етапи побудови графіка функцій f (x), якщо

Процес побудови графіка функцій почнемо зі створення таблиці у Excel (рис. 12.7).

Рисунок 12.7

Тепер нам необхідно виділити тільки рядок з функцією f (x) (рис. 12.8).

Рисунок 12.8

Після чого необхідно викликати Майстер діаграм та у вкладці Стандартные обрати тип График та вигляд графіка. Далі перейти на наступний крок, де у вікні з’явиться графік (рис. 12.9)

Рисунок 12.9 Рисунок 12.10

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

Рисунок 12.11

Потім поле Подпись по оси Х (рис. 12.12) буде заповнене та на графіку з’являться правильні підписи за віссю Х.

Рисунок 12.12 Рисунок 12.13

Після чого переходимо на наступний крок Майстра діаграм, де з’являється вікно, що зображене на рис. 12.13.

На вкладці Заголовки можна зробити підписи графіка, підписи осей Х та У (рис. 12.14).

Рисунок 12.14 Рисунок 12.15

Для налаштування ліній сітки необхідно обрати вкладку Линии сетки та поставити прапорець у полі Ось Х - основные линии (рис. 12.15).

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

Рисунок 12.16

12.3. Редагування та форматування діаграм

12.3.1. Редагування діаграми

Діаграму, як будь-який об'єкт, можна розмістити в довільному місці робочого листка. Використовуючи кутові маркери, можна змінювати розмір діаграми.

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

1 Вибрати з меню Диаграмма один із пунктів, на який потрібно;

2 Викликати контекстне меню (натиснути праву кнопку мишки) у вільному місці діаграми, з контекстного меню вибрати один із пунктів (аналогічних як і в меню Диаграмма);

3 Натиснути кнопку Мастер диаграмм та повторити потрібні кроки роботи майстра. Якщо діаграма була виділена, то не створюється нова діаграма, а здійснюється зміна параметрів активної (рис. 12.17);

4 Скористатися потрібною піктограмою з панелі інструментів Диаграммы.

Після внесення потрібних змін, діаграма буде налаштована.

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

Іноді необхідно здійснити налаштування деякої частини діаграми (змінити колір рядів даних, здійснити зафарбування фону, змінити шкалу, шрифт підписів тощо).

У цьому випадку достатньо на потрібному елементі діаграми (заголовка, осі, ряду даних, області стінок діаграми тощо) натиснути праву клавішу мишки і з контекстного меню вибрати пункт Формат (наприклад, Формат линий сетки, Формат оси, Формат рядов данных).

Рисунок 12.17 – Меню діаграми для повернення на один із кроків роботи майстра діаграм

12.3.2. Додавання даних до діаграм

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

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

Включення додаткових даних також можливе за допомогою контекстного меню форматування. За командою Исходные данные виділені додаткові дані включаються в діаграму.

12.3.3. Зміна орієнтації об’ємних діаграм

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

Рисунок 12.18 – Елементи об’ємної діаграми

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

Розворот об’ємної діаграми можна здійснювати також за допомогою діалогового вікна Формат трѐ хмерной проэкции У цьому вікні необхідно задати кут і напрямок повороту діаграми, її висоту, піднесення, перспективу та інші. Вікно активізується за командою Объемный вид із спеціального контекстного меню, яке можна викликати на екрані, коли виділено область побудови діаграми.

12.3.4. Виділення сегментів кругової діаграми

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

З цією метою досить виділити відповідний сегмент і потім перетягти його на відповідне місце діаграми.

Для відновлення первинного вигляду діаграми необхідно виділити сегмент, повернути його на колишнє місце і вилучити маркери-чотирикутники натисканням на клавішу ESC.

Рисунок 12.19 – Виділення сегментів колової діаграми

12.4. Інтерполювання даних за допомогою діаграм

Розглянемо приклад, в якому за допомогою діаграм можна відновити втрачені дані шляхом інтерполювання даних.

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

На основі даних стовпців Місяці та Кількість проданих одиниць товару побудувати діаграму – графік.

На третьому кроці роботи Майстра побудови діаграм у вікні Параметри диаграммы на закладці Линий сетки задати налаштування, як показано на рис. 12.20.

Рисунок 12.20 - Вікно налаштування параметрів діаграми

Відобразити на графіку ті значення, яких немає в таблиці, необхідно викликати з меню Сервис - Параметры... вікно Параметры і на закладці Диаграмма встановити перемикач Значення интерполируются так, як це показано на рис. 12.21.

Рисунок 12.21 – Вікно Параметри

У результаті графік матиме вигляд, наведений на рис. 12.22.

Рисунок 12.22 – Одержаний графік після інтерполяції

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

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

Тема 13. РОЗВ’ЯЗАННЯ ПРИКЛАДНИХ ЗАДАЧ В EXCEL

13.1. Підбір параметрів

Щоб визначити значення однієї комірки при змінені значення іншої (такі комірки мають бути пов’язані формулою) використовують підбір параметрів.

Розглянемо цю процедуру на прикладі.

Приклад 13.1.1 Необхідно визначити термін кредиту, за якого перший внесок (поле «Всього до сплати, грн..») становить 500 грн. (у таблиці на рис. 13.1 – 566.7);

Розв’язання

Формули для розрахунку відображені на рис. 13.2.

Рисунок 13.1 – Розрахунок початкового внеску за кредит

Рисунок 13.2 – Формули розрахунку для початкового внеску за кредит

Для цього спочатку встановлюють курсор у комірку G2, після чого необхідно активізувати команди Сервис→ Подбор параметра. Внаслідок цих дій з’явиться вікно (рис. 13.3), в якому в полі Установить в ячейке вводять адресу комірки, значення якої необхідно знайти; в полі Значение: – числове значення, яке потрібно знайти для активної комірки (G2); в полі Изменяя значение ячейки – адресу комірки С2, значення якої необхідно знайти та активізувати кнопкою ОК.

Рисунок 13.3 – Підбір значення Рисунок 13.4 – Результат підбору

Після чого виведеться результат виконання операції (рис. 13.4). Якщо рішення знайдено, то при активізації кнопки ОК нове значення залишається в комірці G2, а кнопки Отмена – відновлюється попереднє значення.

У результаті знайдений термін кредиту становить 28.6 місяців (рис. 13.5)

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

Рисунок 13.5 - Результат розрахунку

Приклад 13.1.2 За допомогою Excel розв’язати рівняння ln(х) = 10.

Тут необхідно в комірку А1 ввести значення 10, а у комірку В1 ввести формулу =Ln(A1) та натиснути клавішу Enter (рис. 13.6).

Рисунок 13.6 – Завдання рівняння в Excel

Потім необхідно виділити комірку В1, та за допомогою меню Сервис→ Подбор параметра викликати вікно Подбор параметра. У цьому вікні задаємо значення підбору параметрів: Установить в ячейке – адресу комірки В1, де знаходиться формула, значення якої необхідно знайти, Значение – значення 10 (праву частину рівняння), Изменяя значение ячейки – адресу комірки А1, значення якої буде змінюватися.

Рисунок 13.7 – Підбір параметра

Після виконання одержимо результат, що зображено на рис. 13.8. Отже, результатом розв’язку рівняння буде х = 22025.84. Правильність результату можна перевірити за допомогою функцій Excel, якщо в будь-якій комірці набрати таку функцію =LN(A1), де А1=22025, 84. Результат виконання цієї функції буде 9, 999972. Таким чином, рівняння розв’язане правильно.

Рисунок 13.8 – Результат виконання пошуку параметра

13.2. Пошук рішення

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

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

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

Наприклад, на основі таблиці з обчисленнями прибутку залежно від витрат на рекламу можна визначити такі оптимальні витрати на рекламу, які забезпечать максимальний прибуток тощо.

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

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

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

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

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

Рисунок 13.9 – Вікно пошуку рішень

У вікні 1 – адреса цільової комірки, 2 – значення цільової комірки, яке необхідно встановити, 3 – діапазон значень, що змінюються, 4 – вікно завдання обмежень, які можна добавити за допомогою кнопки Добавить, які можна редагувати за допомогою кнопки Изменить. Кнопка Выполнить активує програму.

Для кращого розуміння даного питання розглянемо приклад.

Приклад 13.2.1 У таблиці на рис. 13.10 сумарна кількість товарів Тип 1 - Тип 6 становить 1610 шт, дані витрати на кожну одиницю товару різного типу. Необхідно обчислити загальну вартість товарів. Визначити кількість одиниць продукції кожного типу, яку має виготовляти фірма, якщо на даному обладнанні не можна виготовляти більше ніж 2000 одиниць продукції, якщо на виготовлення всієї продукції виділяється 500000 грн.

Розв’язання

Спочатку обчислюються загальні витрати для кожного типу товару за формулою

Загальні витрати = Кількість*Витрати на одиницю.

Спочатку створюється математична модель.

Позначимо кількість одиниць продукції першого типу як x ₁, другого типу - як x₂, третього типу - x₃ і т. ін.

За умов задачі на виробництво продукції виділяється 500000 гривень, тобто загальні витрати мають сягати до 500000, то цільова функція матиме вигляд:

F(x_i) = 300∙ x₁ +200∙ x₂ +100∙ x₃ + 250∙ x₄ +80∙ x₅ + 75∙ x₆ → 500000

На значення параметрів x_i за умовою задачі накладаються обмеження. Оскільки виготовляється продукція, то значення комірок від В2 до В7 повинно бути цілим, оскільки половину від товару не виготовляють, а виготовляють цілу одиницю товару. Також значення цих комірок повинно бути додатним, оскільки одиниці товару не може бути від’ємним. Запишемо обмеження математично:

x ₁, x₂, x₃, x₄, x₅, x₆ ≥ 0,

x ₁, x₂, x₃, x₄, x₅, x₆ → цілі,

x₁+x₂+x₃+x₄+x₅+x₆ ≤ 2000.

Рисунок 13.10 – Таблиця товарів різного типу

Оскільки на даному обладнанні не можна виготовляти більше ніж 2000 одиниць продукції, то на сумарну кількість товару (комірку В8) необхідно накласти обмеження ≤ 2000.

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

Викличемо програму Поиск решения, після чого з’явиться вікно програми (рис. 13.11). У зоні Установить целевую ячейку треба вказати адресу цільової комірки, тобто D8, і показати, якої величини повинно бути значення комірки, тобто 500000.

У зоні Изменяя ячейки необхідно вказати адреси змінних комірок, тобто В2: В7, оскільки змінюється кількість одиниць продукції.

Рисунок 13.11 – Вікно Поиск решения

У зоні Ограничения необхідно занести обмеження, натиснувши на кнопку Добавить. З’явиться вікно для обмежень (рис. 13.12).

Рисунок 13.12 - Вікно Добавление ограничения

У полі Ссылка на ячейку вказується адреса комірок з обмеженнями, посередині вибирається умова (> =, < =, =, цел., двоичн.), а праворуч у полі Ограничение заноситься значення обмеження.

Кожного разу після занесення обмеження натискається кнопка Добавить, доки всі обмеження не будуть вказані, а потім натискається кнопка Ок

Потім натискається кнопка Выполнить Через деякий час з’являється повідомлення, що зображене на рис. 13.13.

Рисунок 13.13– Результати пошуку рішення при знайденому рішенні

Якщо економіко-математична модель створена неправильно (обмеження суперечать одне одному або їх недостатньо для визначення оптимального рішення), видається повідомлення, показане на рис. 13.14.

Рисунок 13.14 - Результати пошуку рішення за відсутності рішення

На основі знайденого рішення можна створити Звіт за результатами обчислень (тип Отчета - Результаты), який міститиме інформацію про попередні значення і отримані значення, а потім натиснути Ок

Після виконання пошуку оптимального розв’язку з’явиться таблиця з оптимальним значенням (рис. 13.15).

Рисунок 13.15 – Таблиця з оптимальним значенням Звіт показаний на рис. 13.16

Рисунок 13.16 – Звіт знайденого оптимального розв’язку