Урок
17-18
Тема:
Програмні засоби для складних обчислень, аналізу даних та
фінансових розрахунків.
Ви дізнаєтеся:
v які програми застосовують для аналізу даних;
v як у середовищі табличного процесора застосовують
інструмент Фільтр;
v як використовувати формули Microsoft Excel для управління власними
Тема:Програми які застосовують для аналізу даних.
Аналіз даних є невід’ємною
частиною процесу дослідження систем будь-якого типу. Існує велика кількість
додатків, які можна використовувати для розв’язування задач аналізу даних як у
пакетному режимі, так й у вигляді бібліотек функцій. Деякі з них, спеціального
призначення та широкого спектру застосування, подано в таблиці 13.1.
Серед програм, які також часто застосовують
для нескладного аналізу даних, є табличні процесори. Якщо багато клітинок
електронної таблиці заповнені різноманітними даними, то візуально вибрати ті,
дані в яких потрібно проаналізувати, роздрукувати, скопіювати, змінити тощо,
досить важко. У Microsoft Excel є
засоби добору даних, що відповідають певним умовам, до яких належать як функції
для роботи з даними, інструменти добору даних за критеріями, так і спеціальні
надбудови.
Умову пошуку деяких даних
найчастіше накладають на ключові параметри,
тобто такі, значення яких є унікальними, неповторними в усій розглядуваній
множині однотипних об’єктів. Для кожного журналу оцінок учнів таким параметром
може бути прізвище та ім’я учня (якщо в одному класі немає кількох учнів з
однаковим прізвищем та ім’ям), а для розкладу руху автобусів — номер рейсу.
Тільки ключові параметри дають змогу одночасно ідентифікувати об’єкт:
наприклад, за номером рейсу можна точно визначити, про який рейс ідеться, а за
пунктом відправлення — ні. У Microsoft
Excel пошук за ключовим значенням можна здійснити за допомогою функції
VLOOKUP (рос. ВПР) з розділу Списки та
масиви майстра функцій, яка використовується в такий спосіб. Спочатку
задане ключове значення відшукується в першому стовпці таблиці, а потім
переглядається весь рядок, де воно міститься. Функція повертає значення зі
стовпця із заданим номером у цьому рядку. Повний формат функцій подано на
малюнку 13.1.1
Приклад: розглянемо таблицю з
даними про результати першого туру учнівської олімпіади з програмування, у якій
кожному з учасників присвоєно деякий код (мал. 13.2).
Тренер команди знає код учасника
та бажає за введеним кодом у клітинку J2 отримати
у клітинці K2 загальну суму балів за
розв’язки всіх завдань. Для цього у клітинку К2 він має ввести таку формулу:
=VLOOKUP(J2;А2:H201;8;FALSE)
Тут J2 - адреса клітинки, до якої користувач вводить код, А2:H201 - діапазон таблиці, 8 - номер
стовпця, який містить дані про суму балів. Значення FALSE останнього аргумента означає, що в першому стовпці
шукатиметься саме те значення, яке було введено до клітинки J2.
Тема: Як у середовищі табличного процесора застосовують інструмент Фільтр?
Фільтрування — це вибір
даних у клітинках електронної таблиці, що відповідають певним умовам. Після
виконання фільтрування в таблиці відображаються лише ті рядки, дані в яких
відповідають умовам фільтрування.
Усі інші тимчасово приховуються.
Якщо встановити табличний курсор
у довільну клітинку заповненого даними діапазону клітинок (деякі клітинки цього
діапазону можуть бути порожніми) і виконати Основне/Редагування/Сортування
або Дані/ Фільтр, то біля правої межі кожної клітинки першого рядка цього
діапазону клітинок з’являться кнопки відкриття списку (мал. 13.3). Таким чином встановлюється
режим, за допомогою якого можна здійснювати фільтрування.
У цих списках містяться:
- вказівки сортування даних за
значеннями даного стовпця;
- вказівка Фільтрування за кольором;
- вказівка Видалити фільтр із;
вказівка відкриття меню команд для
встановлення умов фільтрування: Фільтри
чисел (якщо в стовпці — числові дані), або Текстові фільтри (якщо в стовпці — текстові дані), або Фільтри дат (якщо в стовпці — дати);
- прапорець Виділити все;
- прапорці для значень у
клітинках даного стовпця
Якщо зняти позначку прапорця Виділити все, встановити позначки
прапорців для деяких з наведених значень і вибрати кнопку OK, то відбудеться фільтрування, після якого в таблиці будуть
відображатися лише ті рядки, у яких значення в даному стовпці дорівнюють
вибраним.
У списку Фільтри чисел вказівки Дорівнює,
Не дорівнює, Більше, Більше або дорівнює,
Менше, Менше або дорівнює, Між відкривають вікно Користувацький автофільтр
(мал. 13.5), у якому можна встановити умову фільтрування: просту або складену з
двох простих, поєднаних логічними операціями І чи АБО. Результат виконання цих
логічних операцій збігається з результатом відповідних логічних функцій AND і
OR. Саме ж фільтрування виконується після вибору кнопки OK.
(мал.13.5)
Після
виконання фільтрування за умовою, наведеною на малюнку 13.5, вихідна таблиця
виглядатиме так, як на малюнку 13.6. У ній відображуються тільки ті рядки, для
яких у стовпці Score виконується
умова (більше або дорівнює 360). Інші рядки таблиці, заповнені даними,
приховано.
Вказівка Перші 10 відкриває вікно
Автофільтр для добору найкращої десятки (мал.
13.7), у якому можна встановити умову фільтрування, за якою відображатимуться
тільки ті рядки таблиці, які містять у даному стовпці вказану кількість
найбільших (найменших) значень.
Вказівка Більше середнього (Менше середнього) виконує фільтрування, за яким
відображуються тільки ті рядки таблиці, які містять у даному стовпці значення,
більші (менші), ніж середнє арифметичне значення в даному стовпці.
Для вказівок встановлення умов Текстових фільтрів також відкривається
вікно Користувацький автофільтр, поля
якого містять списки умов для текстів.
Фільтрування можна здійснювати
послідовно для кількох стовпців таблиці. Тоді кожний наступний фільтр буде
застосований уже тільки для тих рядків таблиці, які відображаються після
застосування всіх попередніх фільтрів. Тобто загальна умова фільтрування — це
умови фільтрування для кожного стовпця, об’єднані логічною операцією І.
Щоб скасувати фільтрування,
можна:
- виконати вказівку Дані/Очистити;
- виконати вказівку Основне/Редагування/Сортування й фільтр/
Очистити;
- виконати вказівку Видалити фільтр із у списку стовпця, за
даними якого було проведено фільтрування;
- установити позначку прапорця Виділити все у списку стовпця, за даними
якого було проведено фільтрування, після чого вибрати кнопку OK.
Щоб взагалі скасувати режим, у
якому можна проводити фільтрування, потрібно повторно виконати одну з вказівок,
яка встановлює цей режим.
Способи фільтрування, розглянуті
вище, дають змогу виконати фільтрування не для всіх випадків. Так, наприклад,
розглянутими засобами не можна виконати фільтрування за умовою, яка є
об’єднанням умов фільтрування двох стовпців логічною операцією АБО, наприклад:
(за друге завдання 100 балів) АБО (за третє завдання 100 балів). Виконати
фільтрування за такою та іншими складеними умовами можна з використанням так
званого розширення фільтра.
Тема: Як використовувати формули Microsoft Excel для управління власними
фінансами?
Окремим видом аналізу даних,
зокрема грошових, є обчислення грошових сум, відсотків або термінів виплати за
отримані кредити, а також прибутку як результату інвестування. У більшості
функцій обчислення здійснюються за формулами складних відсотків.
Розглянемо задачу: ви
плануєте по завершенні освітнього закладу відкрити власну справу й накопичити
деякий стартовий капітал. Для цього відкриваєте в банку депозитний рахунок і
відразу вносите на нього 1000 грн, а протягом року поповнюєте його щомісяця на
100 грн. Банк нараховує вам 18 % річних у кінці кожного місяця, й отриманий
прибуток автоматично переводиться на ваш рахунок. Питання: яка сума
накопичиться на вашому рахунку на кінець року?
Задачу можна розв’язати,
обчислюючи суми на рахунку послідовно в кінці кожного місяця. Так, за перший
місяць банк нарахує 12 % / 12 = 1 % від загальної суми, і на рахунку буде 1000
+ 1000 * 1 % = 1010 грн. У кінці другого місяця відсотки нараховуватимуться вже
не на 1000 грн, а на 1010 (це й називається «складним відсотком»), й у вас на
рахунку буде 1015 + 1015 * 1,5 % = 1030,22 грн. Фінансова функція FV (рос. БС)
автоматизує виконання обчислень за описаною схемою. Вікно аргументів функції
зображено на малюнку 13.11.
Пояснимо призначення аргументів
функції, що розглядається.
- Ставка - відсоткова ставка, тобто кількість відсотків, які
нараховуються на загальну суму в кінці кожного періоду нарахувань. У нашому
прикладі період дорівнює одному місяцю, у кінці якого нараховується 18 % / 12 =
1,5 %.
- Кпер - кількість періодів, за якими здійснюватиметься обчислення. У
нашому випадку період дорівнює одному місяцю, а нам потрібно обчислити стан
рахунку за підсумком року. Тому значення цього аргументу — 12.
- Спл - сума, на яку поповнюють рахунок протягом кожного періоду. У
нашій задачі це 100 грн.
- Зв - зведена теперішня
вартість. Сума, яку вносять на рахунок відразу (у нас — 1000 грн).
- Тип - час оплати. Якщо цей
аргумент дорівнює 0, то оплата здійснюється наприкінці періоду, якщо 1 — на
початку.
Отже, після введення аргументів
ми отримаємо таку формулу: =FV(12%/12;12;100;1000;0). На малюнку 13.12.1
(мал.13.12.1)
у діапазоні В1:В14 наведено
результати послідовних обчислень, а в клітинці А17 — обчислень з використанням
фінансової функції.
Формули Microsoft Excel
допомагають обчислити майбутню вартість позичених коштів та вкладених, і також
за отриманими результатами можна зрозуміти, скільки часу потрібно для
досягнення цілей. Для цього можна використати такі функції (табл. 13.2)
Завдання для самостійної роботи
1. Складіть інфографіку про
призначення засобів аналізу даних у середовищі табличного процесора.
2. Складіть інструкцію з
використання фінансових функцій табличного процесора для фінансових розрахунків
своєї родини.
3. Ви плануєте разом з родиною
розпочати власний бізнес із вирощування полуниці. Проведіть імітаційний
експеримент із використанням генератора випадкових чисел для дослідження
розміру зібраного урожаю полуниці, якщо 1 м2 у середньому приносить 40 ± 5 кг
ягід. Побудуйте графік розподілу.
4. Ви плануєте через півроку
побудувати новий спортивний майданчик у школі. Для цього громада може виділити
для проекту 100 000 грн. Ви підрахували, що кожного місяця силами
старшокласників зможете зібрати ще по 1000 грн. Який бюджет ви отримаєте через
півроку на побудову майданчика, якщо відкриєте депозит у банку під 17 % річних?
5. Для утеплення житла вашій
родині потрібно 30 000 грн. Визначте, що вигідніше: взяти кредит у банку під 19
% річних з деяким щомісячним платежем на рік чи протягом року накопичувати суму
на депозитному рахунку з тим самим місячним платежем.
Комментариев нет:
Отправить комментарий