Інформаційні системи і технології 4 10 Графічне

Скачать презентацию Інформаційні системи і технології 4 10 Графічне

ICT222222(Лек).ppt

Количество слайдов: 128

Інформаційні системи і технології

4. 10. Графічне представлення табличних даних в MS Excel. На третьому етапі побудови необхідно задати такі параметри діаграми: заголовки, різні підписи, вісі, лінії сітки, легенди, таблиці даних, підписи даних – які представляють собою заголовки рядків і колонок. З цією метою використовуються відповідні вкладки діалогового вікна (рис. ). По замовчуванню, діаграма будується на основі виділеної області даних, якщо в цій області виділені назви рядків і колонок Excel автоматично створює підписи на їх основі. В правій частині діалогового вікна наводиться зразок діаграми в тому вигляді в якому вона буде згідно заданих параметрів.

4. 10. Графічне представлення табличних даних в MS Excel. Вкладка «Заголовки» призначена для введення назви діаграми та її вісей. Вкладка «Оси» забезпечує виведення на екран вісей Х, У та Z. Вкладка «Линии сетки» забезпечує внесення/знищення основних та проміжних ліній на вісях Х, У та Z. Вкладка «Легенда» дає змогу вписати/виписати легенду (позначення рядів даних), а також задати місце її розташування на діаграмі. Вкладка «Подписи данных» дозволяє виводити на екран підписи даних (в категоріях, значеннях, долях і т. д. ). Вкладка «Таблица данных» забезпечує, у випадку необхідності, виведення в області діаграми таблицю з даними.

4. 10. Графічне представлення табличних даних в MS Excel.

4. 10. Графічне представлення табличних даних в MS Excel. Задавши необхідні параметри діаграми, необхідно натиснути кнопку „Далее”. На четвертому етапі, в діалоговому вікні „Размещение диаграммы”, що з’явиться після виконання останньої операції, необхідно вибрати місце розміщення діаграми на окремому листі або в уже існуючому поряд із таблицею.

4. 10. Графічне представлення табличних даних в MS Excel. Після того, як діаграма створена, у випадку необхідності, її можна відредагувати. Для зміни того чи іншого об’єкта діаграми потрібно: l Навести вказівник миші на об’єкт, який потрібно відредагувати і натиснути праву клавішу миші; l В контекстному меню, що з’явиться, вибрати потрібну команду; l В діалоговому вікні задати потрібні параметри і натиснути кнопку „ОК”. Необхідно зазначити, що кругові та кільцеві діаграми відображають значення тільки однієї змінної.

4. 10. Графічне представлення табличних даних в MS Excel.

4. 10. Графічне представлення табличних даних в MS Excel надає можливості по створенню об'ємних діаграм. В Excel вона представлена шістьма типами об’ємних даних: Гістограма, Лінійчата, Діаграма з областями, Графік та Поверхня. Для її створення необхідно на першому етапі її побудови вибрати тривимірний вигляд. До просторової діаграми можна перейти також в режимі її редагування. Для цього необхідно: 1. Правою клавішею миші в області діаграми активізувати контекстне меню; 2. В списку команд, вибрати Тип диаграммы ; 3. В діалоговому вікні „Тип диаграммы” вибрати об’ємний вигляд діаграми.

4. 10. Графічне представлення табличних даних в MS Excel. В контекстному меню для об’ємних графіків і діаграм, порівняно з плоскими зображеннями, є додаткова команда – Объемный вид. Дана команда дозволяє змінювати просторову орієнтацію діаграми.

4. 10. Графічне представлення табличних даних в MS Excel. Обробка результатів є важливою частиною аналізу даних. Не залежно від того чи проводяться лабораторні дослідження, чи вивчаються результати програм, в початкових даних завжди присутній номер досліду та кількісна характеристика. Для такого роду досліджень, найбільш зручним є ХУ-графік. При обробці даних часто виникає питання про тенденції поведінки. В Excel встановлення функціональної залежності та прогнозування графічно представлених даних здійснюється за допомогою лінії тренду. Лінії тренду графічно зображують тенденції зміни даних.

4. 10. Графічне представлення табличних даних в MS Excel. Для побудови лінії тренду необхідно: 1. В уже створеному графіку правою клавішею виділити ряд даних та активізувати контекстне меню ; 2. В списку команд вибрати Добавить меню тренда ; 3. Після того, як з’явиться діалогове вікно, активізувати вкладку „Тип” і вибрати потрібний тип тренду. 4. Перейти на вкладку “Параметри” і активізувати індикатори опцій “показывать уравнение на диаграмме” та “поместить на диаграмму величину достоверности аппроксимации”.

4. 10. Графічне представлення табличних даних в MS Excel. Для задання прогнозування даних та інших параметрів необхідно: 1. На створеній діаграмі виділити правою клавішею миші ряд даних; 2. В контекстному меню вибрати команду „Добавить меню тренда”; 3. Активізувати вкладку „Параметры” діалогового вікна „Линия тренда”; 4. В розділі „Прогноз” задати передбачення на задану кількість одиниць;

4. 10. Графічне представлення табличних даних в MS Excel.

4. 10. Графічне представлення табличних даних в MS Excel. Для зміни параметрів лінії тренду необхідно: l навести вказівник миші на лінію тренда; l натиснути праву клавішу миші, в контекстному меню вибрати команду Формат линии тренда ; l в діалоговому вікні „Формат линии тренда” внести зміни до параметрів лінії тренду (ширина, тип, прогноз і т. д. ) після чого натиснути клавішу "ОК". Під час вибору лінії тренду потрібно звертати увагу на відповідність поведінки даних і на поведінку кривої апроксимації, це забезпечить вірність прогнозу та зменшить його похибки.

4. 11. Налаштування MS Excel. Друк робочих листів. Налаштування MS Excel здійснюється в діалоговому вікні „Параметры” активізація якого здійснюється за командою Сервис/Параме тры. Діалогове вікно містить 13 вкладок ( рис. ).

4. 11. Налаштування MS Excel. Друк робочих листів.

4. 11. Налаштування MS Excel. Друк робочих листів. Друк в Excel має свої особливості. Пов’язано це з тим, що робочі листи Excel можуть мати різні розміри, тоді як більшість принтерів призначена для роботи з аркушами стандартного розміру А 4. В даних умовах можлива організація двох способів друкування: l розбиття робочих листів на фрагменти та їх посторінкове друкування; l друкування робочих листів зменшених до розмірів друкарської сторінки. На практиці ці два способи поєднуються. Друк робочих листів здійснюється за командою Файл/Печать. Дана команда активізує діалогове вікно „Печать” (рис. ).

4. 11. Налаштування MS Excel. Друк робочих листів.

4. 11. Налаштування MS Excel. Друк робочих листів. Дане діалогове вікно містить 4 розділи: „Принтер”; „Печать”; „Копии”; „Вывести на печать”. Розділ „Принтер” містить інформацію про принтер, що використовується для друку. Розділ „Печать” використовується для задання режиму друкування „Все”, „Страницы”. Розділ „Вывести на печать” задає області друку – „Выделенный диапазон”, „Выделенные листы” та „Всю книгу”. Розділ „Копии” задає кількість копій об’єкту, що друкується та послідовність друку сторінок об’єкту.

4. 11. Налаштування MS Excel. Друк робочих листів. Кнопка „Просмотр” використовується для попереднього перегляду робочого листа перед друком (рис). Режим перегляду містить 9 команд “Далее”, “Назад”, “Масштаб” (режим збільшеного зменшеного перегляду), “Печать”, “Страница” (задаються параметри сторінки), “Поля” (задаються параметри полів), “Разметка страницы” (перехід в режим розмітки сторінки), “Закрыть” і “Справка”. Інші параметри друку встановлюються шляхом натискання кнопки „Свойства”, після чого активізується діалогове вікно „Свойства, ”(рис. ), що містить три вкладки: „Бумага”, „Графика”, „Параметры устройства”.

4. 10. Налаштування MS Excel. Друк робочих листів.

4. 11. Налаштування MS Excel. Друк робочих листів.

4. 11. Налаштування MS Excel. Друк робочих листів. Вкладка „Бумага” використовується для вибору розміру паперу, орієнтації сторінок. Вкладка „Графика” задає роздільну здатність друку. У випадку, якщо перед друком необхідно змінити параметри розміщення інформації на сторінці, потрібно виконати команду Файл/Параметры страницы. Після виконання команди з’явиться діалогове вікно (рис. ), яке містить наступні вкладки: l„Страница”; l„Поля”; l„Колонтитулы”; l„Лист”.

4. 11. Налаштування MS Excel. Друк робочих листів.

4. 11. Налаштування MS Excel. Друк робочих листів. Друкування робочих листів великих розмірів, можна здійснити шляхом розділу на сторінки. Розбиття робочого листа на сторінки виконується програмою Excel автоматично або користувачем за допомогою команди Вставка/Разрыв страницы , при цьому границі сторінок відмічаються пунктирними лініями. Перед виконанням даної команди, курсор необхідно поставити у місце, де пропонується встановити розрив сторінки. Якщо заданий режим розбиття сторінок є невірним, то його можна відмінити за допомогою команди Вставка/Убрать разрыв страницы , але перед цим потрібно виділити весь робочий лист.

4. 11. Зведені таблиці. Підбір параметра. Створення зведених таблиць зв'язане з необхідністю отримання підсумкових даних по декількох параметрах таблиці. Розглянемо процедуру створення зведеної таблиці на прикладі таблиці.

4. 11. Зведені таблиці. Підбір параметра. Нехай, наприклад, потрібно розрахувати суму значень в полі "Виручка від реалізації" для кожної фірми, а також визначити відсоток суми значень полів "Податок" кожної фірми до загальної суми сплаченого податку. Для обчислення заданих критеріїв виконати наступні дії: • встановити курсор в будь-яку комірку за межами таблиці і виконати команду <Данные/Сводная таблица>; • в діалоговому вікні, що з'явиться, необхідно активізувати потрібний індикатор (в нашому випадку <В списке или базе данных MS Excel> і натиснути кнопку <Далее>; • в наступному діалоговому вікні ввести, якщо він відсутній, діапазон комірок таблиці на основі яких створюватиметься зведена таблиця.

4. 11. Зведені таблиці. Підбір параметра. В наступному вікні здійснюється формування колонок зведеної таблиці, для цього необхідно: lнатиснути ліву кнопку миші на назві потрібної колонки (в нашому випадку "Фірма") і не відпускаючи ліву кнопку миші перетягнути назву в поле "Столбец"; lв поле "Данные" перетягнути кнопки з назвами, в яких буде здійснюватись підрахунок значень зведеної таблиці (в нашому випадку це "Виручка від реалізації" та "Податок"); lдля визначення функцій підрахунку, що будуть виконуватись в заданих колонках, необхідно двічі натиснути ліву клавішу миші на кнопці з назвою колонки в полі "Данные" (в нашому випадку на кнопці з іменем <Виручка від реалізації>;

4. 11. Зведені таблиці. Підбір параметра. lпісля появи наступного вікна діалогу в списку "Операция" вибрати потрібну (в нашому випадку "Сумма"); lза допомогою кнопки "Дополнительно" активізувати список "Дополнительные вычисления" в цьому списку вибрати "Доля от суммы по строке", натиснути кнопку <Далее>. В списку "Поле" виділити ім'я колонки; lвиконати подібні дії для колонки "Податок"; lпослідовно активізувати кнопки , <Далее>; lвказати область розміщення зведеної таблиці "Существующий лист", натиснути кнопку Готово.

4. 11. Зведені таблиці. Підбір параметра.

Розглянемо приклад створення багатосторінкової зведеної таблиці. Створити зведену таблицю, в якій би посторінково для кожного магазину виводилися наступні параметри: в полі “Ціна” – середнє значення ціни кожної книги та сума значень в полі “Кількість проданих” для кожної книги.

4. 11. Зведені таблиці. Підбір параметра. Підбір параметру – це засіб Excel для так званого аналізу “что, если”. При цьому значення комірок змінюються так, щоб число в цільовій комірці стало рівним заданому. Активізується процедура за командою “Сервіс/Параметри”

4. 11. Зведені таблиці. Підбір параметра. В якості прикладу застосування даного засобу розглянемо наступну ситуацію: Студент зайняв перше місце на олімпіаді і отримав премію 1000 грн. Він вирішив витратити їх на оплату свого навчання. Оплата здійснюється через 20 місяців в розмірі 2000 тис. грн. АТ “Квант” власні акції по ціні 100 грн. за акцію, на які нараховуються відсотки щомісяця в розмірі 5%, окрім того кожного місяця по акціях здійснюється перерахунок по формулі складних відсотків. Питається чи встигне студент накопичити потрібну суму для оплати навчання до вказаного терміну?

4. 11. Зведені таблиці. Підбір параметра. Коефіцієнт накопичування розраховується за допомогою формули складних відсотків: (1 + відсоток)термін = коефіцієнт накопичування. Для нашого випадку маємо формулу =(1+В 4)^В 5; Для розрахунку суми виплат використовується формула (Сума виплат = Вартість акцій Коефіцієнт накопичення Кількість акцій). В нашому випадку маємо = В 2*В 6*В 3.

4. 11. Зведені таблиці. Підбір параметра. Виконавши дані розрахунки можна побачити, що купивши 10 акцій АТ “Квант” студент через 20 місяців отримує суму 2653, 298 грн. При зміні в таблиці початкових даних відповідно змінюються і результати розрахунку, що значно спрощує процедуру. Значно складніше визначити значення початкових даних (на основі яких формуються формули) виходячи із заданих кінцевих результатів розрахунків. Наприклад: Скільки потрібно місяців для отримання 3000 грн. ?

4. 11. Зведені таблиці. Підбір параметра. Для розв'язку таких задачі використовується засіб Excel – підбір параметра, розглянемо його використання на прикладі таблиці. Процедура використання даного засобу полягає в наступному: Виконати команду Сервіс/Подбор параметра. В діалоговому вікні “Подбор параметра” (рис. ), що з'явиться після виконання команди, в полі “Установить в ячейке” вказати адресу комірки в якій потрібно розмістити результат розрахунку. В нашому випадку комірка В 8.

4. 11. Зведені таблиці. Підбір параметра. В полі “Значение” потрібно ввести числове значення 3000 (рис. ). В це поле повинні вводитися тільки числа. В полі “Изменяя значения ячейки” вказати адресу комірки значення в якій повинні змінюватися для отримання потрібного результату, в нашому випадку комірка з терміном вкладу, тобто В 5 (рис. ). Натиснути кнопку “ОК”. На екрані з'явиться діалогове вікно “Результат подбора параметра” (рис. 32) з повідомленням про отримання потрібного результату.

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

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

4. 13. Створення сценаріїв. макросів. Диспетчер Розглянемо процедуру створення макросу. Нехай, наприклад, потрібно округлити дані до одного знаку після коми, знайти їх суму чи середнє значення. Для цього необхідно виконати наступні дії: l Виділити потрібний діапазон комірок; l Виконати команду Сервис/Макрос/Начать запись ; l В діалоговому вікні, що з’явиться (рис. ) ввести ім’я макросу, та присвоїти комбінацію клавіш, яка буде активізувати даний макрос (вводити тільки букви). l. Натиснути кнопку ОК , після того як з’явиться панель „Остановить запись” (рис. ) активізувати кнопку „Относительная ссылка”;

4. 13. Створення сценаріїв. макросів. Диспетчер

4. 13. Створення сценаріїв. l. Активізувати макросів. Диспетчер функцію ОКРУГЛ, ввести необхідні аргументи функції. Виконати операцію для всіх даних (можна шляхом автозаповнення); l. Виділити комірку під даними, в якій записати формулу знаходження суми діапазону даних; l. В комірку, ввести формулу для знаходження середнього значення серед заданого діапазону даних; l. Активізувати кнопку „Остановить запись” панелі інструментів „Остановка макроса”.

4. 13. Створення сценаріїв. макросів. Диспетчер

4. 13. Створення сценаріїв. макросів. Диспетчер Створений макрос, при активізації буде здійснювати автоматичне округлення даних до одного знаку після коми, знаходити суму всіх даних та середнє значення. Активізувати створений макрос можна командою Сервис/Макросы. В діалоговому вікно „Макросы” (рис. ) із списку вибирають потрібний макрос. Макрос може бути застосований до будь-яких інших даних, що були заздалегідь введені. При цьому необхідно звертати увагу на співвідношення комірки з якої починався запис макроса і областю розміщення даних, що підлягають обрахунку за допомогою макросу.

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

4. 13. Створення сценаріїв. макросів. Диспетчер Процедура створення кнопки полягає в наступному: l Активізувати панель інструментів „Формы” за допомогою команди Вид/Панели инструментов/Формы ; l Активізувати кнопку (кнопка), лівою клавішею миші; l Перемістити вказівник миші на місце розміщення кнопки на робочому столі; l Після виконання останньої дії, з’явиться діалогове вікно з переліком макросів. В якому необхідно вибрати той, для котрого присвоюється кнопка і натиснути кнопку „ОК”.

4. 13. Створення сценаріїв. макросів. Диспетчер Після виконання перерахованих операцій, макрос можна активізувати за допомогою кнопки. Кнопці можна присвоїти ім’я, яке буде містити інформацію про призначення макросу. Для цього необхідно: l. Виділити правою клавішею миші кнопку; l. Після того, як з’явиться курсор в полі імені кнопки, видалити стару назву та ввести за допомогою клавіатури нову; l. Клацнути мишею в будь-якій порожній комірці, поза областю кнопки.

4. 13. Створення сценаріїв. макросів. Диспетчер В процесі моделювання Excel надає можливості перевірити наслідки змін заданих значень, при цьому зберегти набір змінених вхідних даних в якості сценарію, котрий можна реалізувати в будь-який момент. Для цього використовується програма „Диспетчер сценариев”. Сценарій можна зберігати, редагувати та видаляти. Диспетчер сценаріїв дозволяє також створювати звіт для вибраного сценарію.

4. 13. Створення сценаріїв. макросів. Диспетчер Для ілюстрації можливостей „Диспетчера сценариев” розглянемо наступний приклад: Вкладник купив акції АТ ”Saturn” по ціні 15000 на суму 150000 грн. з метою отримання через 20 місяців 500000 грн. Вартість однієї акції 15000 грн. , по акціях щомісячно здійснюється нарахування складних відсотків в розмірі 5% по формулі: "До виплати = (вартість акції * кількість акцій * ((1+відсотки) ^ Термін вкладу))" або із застосуванням адресації комірок "= (В 1 * В 2 * (1 + В 3) ^ В 4)"

4. 13. Створення сценаріїв. макросів. Диспетчер 1. Необхідно створити базу початкових даних у вигляді таблиці.

4. 13. Створення сценаріїв. макросів. Диспетчер 2. Пропонується розглянути наступні варіанти подій – яку суму отримає вкладник до виплати якщо: а) негативний сценарій – відсотки по акціях впали до 4% на місяць; б) позитивний сценарій – відсотки по сценаріях піднімаються до 6% на місяць; в) початковий сценарій. Так як при застосуванні сценарію початкові дані будуть змінені, доцільно попередньо зробити копію листа з даними.

4. 13. Створення сценаріїв. 3. Після створення початкової бази даних необхідно виконати команду Сервис/Сценарии , на екрані з’явиться діалогове вікно „Диспетчер сценариев” (рис. ). макросів. Диспетчер

4. Для створення нового сценарію необхідно виділити комірку з даними, що будуть змінюватися і натиснути кнопку „Добавить”. На екрані з’явиться діалогове вікно „Добавление сценария” (рис. ).

4. 13. Створення сценаріїв. макросів. Диспетчер

4. 13. Створення сценаріїв. l. В макросів. Диспетчер полі „Название сценария” потрібно ввести ім’я нового сценарію (в нашому випадку перший - позитивний сценарій). l. В полі „Изменение ячейки” вказати адрес комірки, що буде змінюватися (це значення відсотків – комірка В 3). l. В полі „Примечания” можна ввести за бажанням коментар, щодо змін. l. Закрити діалогове вікно „Добавление сценария”, шляхом натискання кнопки „ОК”. l. В вікні діалогу „Значение ячеек сценария” вказати значення відсотків першого сценарію 0, 06 і натиснути кнопку „Добавить”.

4. 13. Створення сценаріїв. макросів. Диспетчер Для сценаріїв В і С повторити відповідні пункти створення сценарію та ввести відповідні значення відсотків – 0, 04 та 0, 05. Натиснути кнопку „ОК”, щоб закрити діалогове вікно „Значение ячеек сценария”. Створені сценарії будуть відображені в списку „Сценарии” діалогового вікна „Диспетчер сценариев”. Натиснути кнопку „Закрыть”. Таким чином, створено три сценарії розвитку подій. Необхідно зазначити, що створені сценарії дійсні тільки для робочого листа, на якому вони створені.

4. 13. Створення сценаріїв. макросів. Диспетчер Після того, як сценарії створені їх можна застосувати для аналізу розвитку подій, для цього необхідно: 1. Натиснути команду „Сервис/Сценарии. 2. В діалоговому вікні „Сервис сценариев”, що з’явиться, в списку „Сценарии” виділити потрібний (рис. ). 3. Натиснути кнопку „Вывести”. 4. У випадку необхідності виконати пункти 2 та 3. 5. Закрити вікно, натисканням кнопки „ОК”.

4. 13. Створення сценаріїв. макросів. Диспетчер

В будь-який створений сценарій можна вносити зміни. Це робиться за наступною процедурою: 1. Виконати команду Сервис/Сценарии. 2. В діалоговому вікні, що з’явиться вибрати ім’я сценарію, що буде змінюватися. 3. Натиснути кнопку „Изменить” і внести необхідні зміни – ім’я сценарію, адреси комірки, примітки і т. д. 4. Натиснути кнопку „ОК”. У випадку коли необхідність в сценарії відпала, його можна знищити за наступною процедурою: 1. Виконати команду Сервис/Сценарии. 2. В діалоговому вікні, що з’явиться виділити ім’я сценарію, що підлягає знищенню. 3. Натиснути кнопку „Удалить”.

4. 13. Створення сценаріїв. макросів. Диспетчер По створених сценаріях можна отримати результуючі дані у вигляді звіту. Звіт створюється на окремій сторінці й може бути представленим у формі - структури або зведеної таблиці. Для створення звіту необхідно: 1. Виконати команду Сервис/Сценарии. 2. В діалоговому вікні, що з’явиться натиснути кнопку „Отчет”. На екрані з’явиться діалогове вікно „Отчет по сценарию” (рис. ). 3. Активізувати потрібний тип звіту, наприклад „Структура”.

4. 13. Створення сценаріїв. макросів. Диспетчер 4. В полі „Ячейки результата„ вказати адреси комірок в яких виводяться результати сценаріїв (в нашому випадку це комірка із значенням виплати тобто В 5). Якщо вибрати звіт типу „Сводная таблица” цей пункт треба виконати обов’язково, для звіту „Структура” цього можна не виконувати. 5. Натиснути кнопку „ОК”. Після цього Excel створить вибраний звіт на окремому робочому листі. Програма Excel здійснює автоматичне форматування звіту.

4. 13. Створення сценаріїв. макросів. Диспетчер

4. 14. Таблиці підстановок. Пошук рішень Підставляючи в початкову таблицю різні значення, можна прослідкувати залежність результатів обчислень від вмісту комірок, на які є посилання в формулі. Але так отримуємо тільки один варіант розв'язку. Щоб отримати максимум інформації для аналізу можна використати інструмент Excel – “Таблица подстановки”. Таблицю підстановки можна створювати з однією або двома змінними. Значення змінних задаються в вигляді списків. При створенні таблиці підстановки списки початкових даних задаються у вигляді рядків або у вигляді колонок таблиці.

4. 14. Таблиці підстановок. Пошук рішень Розглянемо процедуру створення і використання таблиці підстановок. Таблиця підстановок з однією змінною (однією коміркою введення) дозволить розраховувати, наприклад значення коефіцієнта накопичування і суми виплат для різних термінів вкладів. В якості бази візьмемо таблицю розрахунку відсотків по акціях АТ “Квант”, тільки скоротимо термін вкладу з 20 місяців до 10, тим самим вказавши інтервал термінів вкладу – 10 місяців. Всі формули, що використовувалися в таблиці залишаються незмінними.

4. 14. Таблиці підстановок. Пошук рішень 1 А В С D E Термін в місяцях 10 11 12 13 14 15 16 Коефіцієнт накопичення =(1+В 4)^В 5 2, 653297705 2, 653297705 2 3 4 5 6 7 8 Вартість акції, грн. Кількість акцій, шт. Відсоткова ставка на місяць Термін в місяцях Коефіцієнт накопичування Сума виплат, грн. 100 10 5, 00% 20 2, 6532 2653, 29 9 10 11 12 17 18 19 20 2, 653297705 F G Сума виплат =В 2*В 6*В 3 2653, 297705 2653, 297705 2653, 297705

4. 14. Таблиці підстановок. Пошук рішень l. В першу комірку колонки “Коефіцієнт накопичення” треба ввести формулу, ця формула повинна копіювати формулу із попередньої таблиці, тобто =(1+В 4)^В 5. l. В першу комірку колонки “Сума виплат” ввести формулу ідентичну формулі попередньої таблиці, тобто =В 2*В 6*В 3. Подібним чином можна створити колонки для розрахунку ще декількох формул. l. Обов'язково звернути увагу на присутність в формулах прямих та непрямих посилань на адрес комірки, що визначена в якості комірки введення, тобто комірки в якій змінюються початкові дані (комірка В 5).

4. 14. Таблиці підстановок. Пошук рішень l. Формули для обчислень обов'язково повинні бути представлені в верхньому рядку або в лівій крайній колонці, оскільки значення в списку розраховуються тільки в напрямку зростання номерів рядків (колонок); l. Виділити діапазон комірок, що містять значення підстановки та формули, в нашому випадку D 2: F 12; l. Виконати команду Данные / Таблица подстановки ; l. Після того як на з'явиться діалогове вікно “Таблица подстановки” (рис) розмістити курсор в полі “Подставлять значения по строкам в: ” і ввести адресу комірки введення, тобто В 5. Натиснути кнопку “ОК” для запуску процесу створення таблиці підстановок.

4. 14. Таблиці підстановок. Пошук рішень

4. 14. Таблиці підстановок. Пошук рішень В результаті в виділеному діапазоні з'явиться створена таблиця підстановок з однією змінною. Результатом використанням таблиці підстановок є масив, який в нашому випадку побудований за допомогою команди = {Таблица(; В 5)}. Цей масив можна редагувати як єдине ціле, змінити окремі комірки неможливо. Таким чином, ми отримали таблицю, в якій з заданою періодичністю виводяться дані, які показують залежність коефіцієнту накопичення та суми виплат від терміну вкладу. Щоб доповнити уже створену таблицю підстановки, необхідно ввести додаткові значення у відповідні комірки, виділити діапазон даних (уже із новими значеннями) і виконати знову команду Данные / Таблица подстановок.

4. 14. Таблиці підстановок. Пошук рішень Створимо тепер таблицю підстановок з двома змінними. За допомогою цієї таблиці можна буде розрахувати суму виплат в залежності від терміну вкладу і кількості куплених акцій. В якості прикладу візьмемо попередню таблицю. а) Термін вкладу може змінюватися від 10 до 20 місяців; б) Кількість акцій може змінюватися від 10 до 15. Для таблиці підстановок з двома змінними необхідно два діапазони значень підстановки – окремо для кожної змінної. Розглянемо процедуру її створення:

4. 14. Таблиці підстановок. Пошук рішень 1. Ввести значення підстановок для першої змінної в колонці робочого листа: діапазон D 3: D 13 – тобто змінні значення для терміну вкладу, так як це показано в таблиці. 5 6 7 8 9 10 11 12 А Вартість акції, грн. Кількість акцій, шт. Відсоткова ставка на місяць Термін в місяцях Коефіцієнт по формулі Сума виплат, грн. В 100 10 5, 00% 20 2, 6532 2653, 2 С Термін в місяцях 1 2 3 4 D =В 2*В 6*В 3 10 11 E F G Кількість акцій 10 11 12 H I J 13 14 15 12 13 14 15 16 17 18 19

4. 14. Таблиці підстановок. Пошук рішень 2. Ввести значення підстановок для другої змінної в рядок, діапазон E 2: J 2, так як це показано в таблиці 3. 3. Формулу, в яку потрібно підставляти значення змінних ввести в комірку, що знаходиться на перетині колонки і рядка із значенням змінних підстановок. В нашому випадку це комірка D 2. Формула повинна бути ідентичною формулі, що знаходиться в комірці В 8, і по якій здійснюється розрахунок суми виплат, тобто потрібно внести формулу =В 2*В 6*В 3 (див табл). Звернути увагу на обов'язкову присутність в формулі прямих і непрямих посилань на комірки, в яких знаходяться дані, що змінюються (в нашому випадку В 3 та В 5). 4. Виділити діапазон комірок, що містять значення підстановок і формули, в нашому випадку D 2: J 13. 5. Виконати команду/Данные/Таблица подстановки.

6. В діалоговому вікні, що з'явиться після виконання команди, в полі “Подставлять значения по строкам в: ” задайте адресу першої змінної комірки, в нашому випадку адресу В 5 – комірка, в якій змінюються значення Кількості акцій, в полі “Подставлять значения по столбцам в: ” – задати адресу другої змінної комірки, в нашому випадку адреса В 3 – комірка, в якій змінюються значення терміну вкладу. Під час виконання операцій даного пункту необхідно звертати увагу на розміщення змінних даних (в рядках, в колонках). 7. Для запуску процесу створення таблиці підстановок натиснути кнопку “ОК”. В результаті на екрані з'явиться створена таблиця підстановок з двома змінними отримані дозволяють робити аналіз ситуації в залежності від зміни двох параметрів: кількості акцій та терміну вкладу, і приймати відповідні рішення.

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

4. 14. Таблиці підстановок. Пошук рішень Розглянемо можливості пошуку рішень на наступному прикладі: Необхідно визначити при яких значеннях ціни і об'єму продажу прибуток буде максимальним при умові, що собівартість не залежить від об'єму виробництва. Початкові данні представлені в таблиці. 1 2 3 А В С Розрахунок прибутку з врахуванням змін попиту в залежності від ціни на товар Максимальний об'єм виробництва, шт. 15000 4 Максимальна ціна, грн. 1499 5 Собівартість, грн. 450 6 Ціна, грн. 500 7 Попит (об'єм продаж), шт. =-10*В 6+15000 8 Прибуток, грн. =(В 6 -В 5)*В 7

4. 14. Таблиці підстановок. Пошук рішень Між зменшенням ціни і попиту існує залежність, що визначається формулою у=-10 х+15000, де у – попит, х – ціна. Тобто в комірці В 7 вводиться формула для розрахунку попиту =– 10*В 6+15000. Прибуток розраховується за наступною залежністю: прибуток = (ціна–собівартість)*попит, тобто в комірку В 8 потрібно ввести формулу = (В 6 В 5)*В 7.

4. 14. Таблиці підстановок. Пошук рішень Під час пошуку рішення необхідно враховувати наступні обмеження: l об'єм продажу за період, що розглядається не повинен перевищувати 15000 одиниць товару; l ціна не може бути вищою за верхню межу в 1499 грн. (якщо встановити вищу ніхто не захоче купити товар); l ціна не повинна бути нижчою за собівартість виробу.

4. 14. Таблиці підстановок. Пошук рішень Розглянемо процедуру пошуку рішення або іншими словами механізм визначення ціни і об'єму продажу при яких прибуток буде максимальним: 1. Створити таблицю з початковими даними, тобто таблицю. 2. Виконати команду Сервис/Поиск решения. 3. В діалоговому вікні “Поиск решения” (рис. ), в полі “Установить целевую ячейку” вказати адресу комірки, значення якої повинно відповідати поставленим вимогам. В нашому випадку – це комірка, що містить формулу розрахунку прибутку, тобто В 8. Необхідно пам’ятати, що цільова комірка завжди повинна містити формулу, в якій є посилання на комірки, в яких данні

4. 14. Таблиці підстановок. Пошук рішень

4. 14. Таблиці підстановок. Пошук рішень 4. В групі опцій “Равно” вибрати опцію “Максимальному значению”, (коли потрібно знайти не максимальне значення, а конкретне потрібно активізувати опцію “Значению”). 5. В полі “Изменяя ячейки” встановити адреси комірок, в яких дані будуть змінюватися. В нашому випадку – це комірки із значеннями попиту та ціни, тобто адреси В 6; В 7. 6. Наступний етап – це введення обмежень. Для цього необхідно натиснути кнопку “Добавить”. На екрані з'явиться діалогове вікно “Добавление ограничения” (рис. ).

4. 14. Таблиці підстановок. Пошук рішень 7. В полі “Ссылка на ячейку” вказати адресу комірки, на яку повинно діяти обмеження. В нашому випадку перше обмеження – попит не може бути більшим за максимальний об'єм виробництва, тобто: в полі “Ссылка на ячейку” вводимо адресу В 7, в наступному полі вибираємо оператор <=, а в полі “Ограничения” – адресу комірки обмеження з верхньою границею В 3. Натиснути кнопку “Добавить”. 8. Залишилося ще два обмеження, що стосуються ціни. Для введення додаткових обмежень необхідно повторно виконати команду “Добавить” в полі “Ссылка на ячейку” ввести адресу комірки В 6, в наступному полі обмеження не більше максимальної ціни 1499 грн. , тобто <= (не більше). В полі “Ограничения” – адресу комірки із верхнею межею обмеження, тобто адресу В 4. Натиснути кнопку “ОК”.

4. 14. Таблиці підстановок. Пошук рішень 9. Виконати команду “Добавить”. В діалоговому вікні “Добавление ограничений” в полі “Ссылка на ячейку” задати адресу комірки В 6, в наступному полі вибрати >=, в полі “Ограничения” вказати адресу комірки В 5. Натиснути “ОК”. Таким чином ми задали останнє обмеження – ціна не повинна бути нижчою від собівартості товару. Задані обмеження з'являться в списку “Ограничения” діалогового вікна “Поиск решения”. Якщо в заданих обмеженнях є помилка для виправлення необхідно натиснути “Изменить”. 10. У випадку, якщо необхідно змінити параметри пошуку рішення, потрібно натиснути кнопку “Параметры”. Після чого з'явиться вікно діалогу “Параметры поиска решения”, в якому можна змінювати параметри пошуку, що були задані по замовчуванню. Натиснути “ОК”.

4. 14. Таблиці підстановок. Пошук рішень 11. Після того, як всі параметри і обмеження задані, потрібно запустити процедуру натисканням кнопки “Выполнить” діалогового вікна “Поиск решения”. 12. Після того, як пошук рішення буде завершено, в таблицю буде внесено нові значення, а також з'явиться діалогове вікно “Результаты поиска решения” (рис. ). 13. Для збереження отриманого результату потрібно активізувати “Сохранить найденное решение” – таблиця буде оновлена у відповідності до найденого рішення.

4. 14. Таблиці підстановок. Пошук рішень У випадку, якщо пошук завершився вдало, можна вказати, які звіти потрібно вставити у робочу книгу. Для цього в списку “Тип отчёта” виділити назву потрібного, якщо потрібно декілька - виділити їх при натисненій клавіші Ctrl. Всі виділені звіти будуть вставлені в робочу книгу. Звіти, що пропонуються, містять наступну інформацію: l звіт “Результаты” – містить інформацію про початкові і поточні значення цільової комірки та змінних комірок, а також про відповідність значень заданим обмеженням; l звіт “Устойчивость” – відображає знайдений результат, а також нижній та верхні граничні значення для змінних комірок; звіт “Пределы” – показує залежність рішень від зміни формули або обмежень. l звіт “Пределы” – показує залежність рішень від зміни формули або обмежень.

4. 14. Таблиці підстановок. Пошук рішень Якщо в майбутньому планується використовувати створену модель процедури пошуку рішення, знайдене рішення можна зберегти як сценарій. Для цього необхідно натиснути кнопку “Сохранить сценарий”. Якщо дана процедура пошуку використовуватися в подальшому не буде – натиснути кнопку “ОК”. Таким чином, використовуючи процедуру пошуку рішення можна розв'язувати досить складні, з математичної точки зору, задачі.

ТЕМА 3. ВИКОРИСТАННЯ ЗАСОБІВ MS EXCEL ДЛЯ РОЗВЯЗКУ ФІНАНСОВОЕКОНОМІЧНИХ ЗАДАЧ Зміст теми: 3. 1. Вступ. 3. 2. Пакет аналізу. 3. 3. Моделі і методи фінансових розрахунків. 3. 3. 1. Загальні положення. 3. 3. 2. Базові моделі фінансових операцій. 3. 4. Фінансово-економічні розрахунки засобами Excel. 3. 5. Функції для аналізу ануїтетів та інвестиційних проектів. 3. 5. 1. Визначення майбутньої вартості платежів.

ТЕМА 3. ВИКОРИСТАННЯ ЗАСОБІВ MS EXCEL ДЛЯ РОЗВЯЗКУ ФІНАНСОВОЕКОНОМІЧНИХ ЗАДАЧ Зміст теми (продовження): 3. 5. 2. Визначення поточної вартості платежів. 3. 5. 3. Розрахунок терміну платежів та відсоткових ставок. 3. 5. 4. Визначення швидкості обороту інвестицій. 3. 5. 5. Розрахунок основних та відсоткових періодичних платежів. 3. 6. Фінансові функції MS Excel для розрахунку амортизації обладнання. 3. 7. Функції для аналізу цінних паперів.

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

3. 1. Вступ На даний момент стандартний курс фінансових розрахунків включає в себе наступні основні теми: • Логіка фінансових операцій (операції накопичування); • Прості відсотки, складні відсотки; • Грошові потоки (переміщення грошових мас в різних сферах діяльності); • Аналіз ефективності інвестиційних проектів; • Оцінка фінансових активів.

3. 2. Пакет аналізу В MS Excel застосовується 15 основних і 37 додаткових фінансових функцій. Додаткові функції, що реалізують фінансово-економічні методи обробки і аналізу в MS Excel реалізовані у вигляді спеціалізованого програмного розширення – надбудови. У випадку застосування додаткових фінансових функцій необхідно встановити сервісну програму «Пакет аналізу» . Даний пакет активізується командою /Сервис/Надстройки/ та вибором із списку, що з’явиться індикатора назви “Пакет аналізу”.

3. 2. Пакет аналізу Рис. Активізація пакету аналізу.

3. 2. Пакет аналізу Якщо процес установки завершено успішно, то в меню „Сервіс” з’явиться ще один пункт – “Аналіз даних”, а також при створенні формул стає доступною нова група функцій. Рис. Вікно “Аналіз даних”.

3. 3. Моделі і методи фінансових розрахунків 3. 3. 1. Загальні положення. Кількісний фінансовий аналіз передбачає застосування уніфікованих моделей і методів розрахунків фінансових показників. Умовно методи фінансової математики поділяються на дві категорії: базові і прикладні. До базових методів і моделей відносяться: 1. прості і складні відсотки, як основа операцій пов’язаних з накопичуванням коштів і дисконтуванням платежів; 2. розрахунок потоків платежів щодо різних видів фінансових операцій.

3. 3. Моделі і методи фінансових розрахунків До прикладних методів фінансових розрахунків відносяться: 1. планування і оцінка ефективності фінансово кредитних операцій; 2. розрахунок страхових ануїтетів; 3. планування погашення довгострокової заборгованості; 4. планування погашення іпотечних позик та споживчих кредитів; 5. фінансові розрахунки по цінним паперам; 6. лізингові банківські операції; 7. планування і аналіз інвестиційних проектів та ін.

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

3. 3. Моделі і методи фінансових розрахунків Основними поняттями фінансових методів розрахунків є: l відсоток – абсолютна величина прибутку від наданої суми коштів (в борг, в депозит); l база – значення внесених (отриманих) коштів, з часом може змінюватися; l відсоткова ставка – відносна величина прибутку за фіксований інтервал часу, що вимірюється у відсотках; l період нарахування – інтервал часу, до якого відноситься відсоткова ставка;

3. 3. Моделі і методи фінансових розрахунків l капіталізація відсотків – сумування нарахованих відсотків з початково внесеними (отриманими) коштами; l накопичення – розмір збільшення початкової суми коштів у зв’язку з капіталізацією; l дисконтування – зведення вартісної величини, що відноситься до майбутнього на деякий, зазвичай більш ранній момент часу (операція обернена до накопичення).

3. 3. Моделі і методи фінансових розрахунків В фінансових розрахунках використовуються наступні види відсоткових ставок: l в залежності від бази для нарахування відсотків розрізняють прості відсотки (постійна база) і складні відсотки (змінна база); l по принципу розрахунку розрізняють ставку накопичення – дискурсивна ставка і облікову ставку – антисипативна ставка; l в залежності постійне чи змінне значення відсоткової ставки на протязі фінансової операції – фіксовані і плаваючі (в залежності чи фіксується змінна в часі база і розмір надбавки до неї - маржі).

3. 3. Моделі і методи фінансових розрахунків 3. 3. 2. Базові моделі фінансових операцій. Існують різні способи нарахування відсотків від надання коштів в будь-якій формі. Відповідно застосовують різні види відсоткових ставок. Відсотки відрізняються по базі їх нарахування. Для розрахунку застосовується постійна і послідовно змінна база. Постійна – за базу для розрахунків приймається постійна не змінна в часі сума. Змінна – за базу приймається сума, отримана на попередньому етапі накопичення або дисконтування. При постійній базі для розрахунків використовують процедуру простих відсотків, при змінній – процедуру складних відсотків.

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

3. 3. Моделі і методи фінансових розрахунків 1. Тривалість року (К=360 днів – звичайні або комерційні відсотки та К=365, 366 днів – точні відсотки); 2. Кількість днів у місяці (кожен місяць – 30 днів або враховується точне число календарних днів); 3. Є розподіл нарахування відсотків в суміжних календарних періодах (загальна сума відсотків ділиться між періодами або відповідно до фактичних дат) чи ні; 4. Наявність змінних ставок (сума накопичення враховує термін дії кожної змінної ставки); 5. Реінвестування коштів (фінансових активів). Реінвестування коштів представляє собою неодноразове послідовне повторення накопичення по простим відсоткам в межах заданого терміну;

3. 3. Моделі і методи фінансових розрахунків - Накопичення і виплата відсотків при споживчому кредиті. В даному випадку використовується метод разового нарахування відсотків на всю суму кредиту з приєднанням розрахованої суми до основної суми позики в момент відкриття кредиту. Виплата кредиту здійснюється з у відповідності до умов кредитування. Вказується кількість виплат на рік і термін кредиту в роках. Розмір виплати визначається як (сума кредиту+відсотки по кредиту)/кількість виплат.

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

3. 3. Моделі і методи фінансових розрахунків математичне дисконтування; l банківський (комерційний) облік. Математичне дисконтування. Розв’язується задача обернена накопиченню початкової суми позики: тобто визначається, яку початкову суму необхідно надати в борг, щоб отримати в кінці терміну задану суму коштів, при умові що на борг нараховуються відсотки по заданій ставці. Банківський або комерційний облік. В цьому виді дисконтування відсотки нараховуються на суму, котру необхідно сплатити в кінці терміну, у відповідності до облікової ставки. l

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