Програмні засоби для складних обчислень, аналізу даних та фінансових розрахунків

Які програми застосовують для аналізу даних?

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

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

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

У Microsoft Excel пошук за ключовим значенням можна здійснити за допомогою функції VLOOKUP (рос. ВПР) з розділу Списки та масиви майстра функцій, яка використовується в такий спосіб. Спочатку задане ключове значення відшукується в першому стовпці таблиці, а потім переглядається весь рядок, де воно міститься. Функція повертає значення зі стовпця із заданим номером у цьому рядку. Повний формат функцій подано на малюнку.

Як приклад розглянемо таблицю з даними про результати першого

туру учнівської олімпіади з програмування, у якій кожному з учасників

присвоєно деякий код.

Тренер команди знає код учасника та бажає за введеним кодом у клітинку J2 отримати у клітинці K2 загальну суму балів за розв’язки всіх завдань. Для цього у клітинку К2 він має ввести таку формулу:

=VLOOKUP(J2;А2:H201;8;FALSE)

Тут J2 — адреса клітинки, до якої користувач вводить код, А2:H201 — діапазон таблиці, 8 — номер стовпця, який містить дані про суму балів. Значення FALSE останнього аргументу означає, що в першому стовпці шукатиметься саме те значення, яке було введено до клітинки J2.

Як у середовищі табличного процесора застосовують інструмент Фільтр?

Фільтрування — це вибір даних у клітинках електронної таблиці, що відповідають певним умовам.

Після виконання фільтрування в таблиці відображаються лише ті рядки, дані в яких відповідають умовам фільтрування. Усі інші тимчасово приховуються.

Якщо встановити табличний курсор у довільну клітинку заповненого даними діапазону клітинок (деякі клітинки цього діапазону можуть бути порожніми) і виконати Основне/Редагування/Сортування або Дані/Фільтр, то біля правої межі кожної клітинки першого рядка цього діапазону клітинок з’являться кнопки відкриття списку. Таким чином встановлюється режим, за допомогою якого можна здійснювати фільтрування.

У цих списках містяться:

- вказівки сортування даних за значеннями даного стовпця;

- вказівка Фільтрування за кольором;

- вказівка Видалити фільтр із;

- вказівка відкриття меню команд для встановлення умов фільтрування: Фільтри чисел (якщо в стовпці — числові дані), або Текстові фільтри (якщо в стовпці — текстові дані), або Фільтри дат (якщо в стовпці — дати);

- прапорець Виділити все;

- прапорці для значень у клітинках даного стовпця.

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

У списку Фільтри чисел вказівки Дорівнює, Не дорівнює, Більше, Більше або дорівнює, Менше, Менше або дорівнює, Між відкривають вікно Користувацький автофільтр, у якому можна встановити умову фільтрування: просту або складену з двох простих, поєднаних логічними операціями І чи АБО. Результат виконання цих логічних операцій збігається з результатом відповідних логічних функцій AND і OR. Саме ж фільтрування виконується після вибору кнопки OK.

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

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

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

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

Для вказівок встановлення умов Текстових фільтрів також відкривається вікно Користувацький автофільтр, поля якого містять списки умов для текстів.

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

Щоб скасувати фільтрування, можна:

- виконати вказівку Дані/Очистити;

- виконати вказівку Основне/Редагування/Сортування й фільтр/Очистити;

- виконати вказівку Видалити фільтр із у списку стовпця, за даними якого було проведено фільтрування;

- установити позначку прапорця Виділити все у списку стовпця, за даними якого було проведено фільтрування, після чого вибрати кнопку OK.

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

Способи фільтрування, розглянуті вище, дають змогу виконати фільтрування не для всіх випадків. Так, наприклад, розглянутими засобами не можна виконати фільтрування за умовою, яка є об’єднанням умов фільтрування двох стовпців логічною операцією АБО, наприклад: (за друге завдання 100 балів) АБО (за третє завдання 100 балів). Виконати фільтрування за такою та іншими складеними умовами можна з використанням так званого розширеного фільтра.

Для встановлення розширеного фільтра й виконання фільтрування за таким фільтром необхідно:

1. Скопіювати у вільні клітинки електронної таблиці назви тих стовпців, за даними яких буде здійснюватися фільтрування.

2. Увести в клітинки під назвами стовпців умови фільтрування (якщо ці умови повинні об’єднуватися логічною операцією І, то вони мають розташовуватися в одному рядку, якщо логічною операцією АБО — у різних).

3. Виконати вказівку Дані/Сортування й фільтр/Додатково.

4. У вікні Розширений фільтр:

1. Вибрати один з перемикачів для вибору області розташування результату фільтрування.

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

3. Увести в поле Діапазон умов адресу діапазону клітинок, у яких розташовані скопійовані назви стовпців й умови (доцільно також використовувати кнопку Згорнути).

4. Якщо було вибрано перемикач, — скопіювати результат до іншого розташування, увести в поле Діапазон для результату адресу діапазону клітинок, де має розміститися результат фільтрування.

5. Вибрати кнопку OK.

Як використати надбудову табличного процесора Пакет аналізу для виконання аналізу даних?

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

Пакет аналізу містить цілий ряд інструментів

Функції пакету аналізу даних можна використовувати одночасно лише на одному аркуші. Щоб виконати аналіз даних на решті аркушів, слід застосувати засіб аналізу на кожному аркуші.

Щоб скористатися інструментами пакета аналізу, слід обрати в середовищі табличного процесора вказівку Data Analysis у групі Analysis на вкладці Дані. Якщо вказівка Data Analysis недоступна, необхідно завантажити надбудову Пакет аналізу.

Як використовувати формули Microsoft Excel для управління власними фінансами?

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

Розглянемо задачу: ви плануєте по завершенні освітнього закладу відкрити власну справу й накопичити деякий стартовий капітал. Для цього відкриваєте в банку депозитний рахунок і відразу вносите на нього 1000 грн, а протягом року поповнюєте його щомісяця на 100 грн. Банк нараховує вам 18 % річних у кінці кожного місяця, й отриманий прибуток автоматично переводиться на ваш рахунок. Питання: яка сума накопичиться на вашому рахунку на кінець року?

Задачу можна розв’язати, обчислюючи суми на рахунку послідовно в кінці кожного місяця. Так, за перший місяць банк нарахує 12 % / 12 = 1 % від загальної суми, і на рахунку буде 1000 + 1000 1 % = 1010 грн. У кінці другого місяця відсотки нараховуватимуться вже не на 1000 грн, а на 1010 (це й називається «складним відсотком»), й у вас на рахунку буде 1015 + 1015 1,5 % = 1030,22 грн. Фінансова функція FV (рос. БС) автоматизує виконання обчислень за описаною схемою. Вікно аргументів функції зображено на малюнку.

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

- Ставка — відсоткова ставка, тобто кількість відсотків, які нараховуються на загальну суму в кінці кожного періоду нарахувань. У нашому прикладі період дорівнює одному місяцю, у кінці якого нараховується 18 % / 12 = 1,5 %.

- Кпер — кількість періодів, за якими здійснюватиметься обчислення. У нашому випадку період дорівнює одному місяцю, а нам потрібно обчислити стан рахунку за підсумком року. Тому значення цього аргументу — 12.

- Спл — сума, на яку поповнюють рахунок протягом кожного періоду. У нашій задачі це 100 грн.

- Зв — зведена теперішня вартість. Сума, яку вносять на рахунок відразу (у нас — 1000 грн).

- Тип — час оплати. Якщо цей аргумент дорівнює 0, то оплата здійснюється наприкінці періоду, якщо 1 — на початку.

Отже, після введення аргументів ми отримаємо таку формулу: =FV(12%/12;12;100;1000;0). На малюнку у діапазоні В1:В14 наведено результати послідовних обчислень, а в клітинці А17 — обчислень з використанням фінансової функції.

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