Завдання 4. Моделювання діяльності інституту післядипломної освіти

Моделювання діяльності інституту післядипломної освіти. Припустимо, що навчання в ІПО ведеться за трьома напрямами, які мають спеціалізації 1 – „Управління”, 2 – „Економіка”, 3 – „Іноземні мови” (рис. 4.6).

Рис. 4.6. Схема навчання спеціалістів в ІПО

На кожного студента заповнюється облікова картка, що містить інформацію: ПІБ, дата народження, напрямок навчання, спеціалізація, мета навчання та ін. Ці дані зберігаються у вигляді електронної картотеки й можуть використовуватися для набору статистики.

Статистичне опрацювання даних важливе:

- під час прогнозуванняі кількості слухачів на наступний рік (для створення матеріальної бази і формування викладацького складу);

- для адаптації навчальних курсів з урахуванням вікових особливостей слухачів;

- для обліку рівня кваліфікації слухачів під час підготовки навчальних програм.

1. Відкрийте книгу Excel ipo.xls з вихідними даними (шлях до файлу запитайте у викладача). Вміст файлу див. у додатку Д.

2. Скопіюйте аркуш Перекваліфікація з вихідними даними в нову книгу та збережіть її під своїм ім’ям.

3. Доповніть таблицю 8 – 10 рядками (дані для них задайте самостійно).

4. У стовпці F обчисліть вік слухачів на момент статистичного опрацювання за формулою: =ЦЕЛОЕ((СЕГОДНЯ()-B2)/365). Формулу впишіть у комірку F2, а потім скопіюйте в інші комірки стовпця F.

5. Для обчислення віку виконуються такі дії:

- із поточної дати (функція СЕГОДНЯ(), категорія функций: Дата и время) віднімається дата народження (береться зі стовпця Дата народження);

- різниця ділиться на середню тривалість року (365 днів);

- від частки відкидається дробова частина (функція ЦЕЛОЕ(), категорія функцій: Математические).

6. Обчисліть у відповідних комірках мінімальний (функція МИН) та максимальний (функція МАКС) вік студентів (категорія функцій: Статистические).

7. У відповідних комірках обчисліть кількість слухачів, які навчаються за трьома різними напрямками, використовуючи формули: =СЧЁТЕСЛИ(C2:C35;"=1") – для напряму 1; =СЧЁТЕСЛИ(C2:C35;"=2") – для напряму 2; =СЧЁТЕСЛИ(C2:C35;"=3") – для напряму 3.

Статистична функція СЧЕТЕСЛИ(Диапазон;Условие) підраховує кількість непустих комірок, які вказані у діапазоні (аргумент Диапазон), і задовольняють заданій умові (аргумент Условие).

8. У відповідних комірках обчисліть кількість слухачів за трьома віковими категоріями: до 25 років; від 25 до 40 років; після 40 років, використовуючи відповідні формули: =СЧЁТЕСЛИ(F2:F35;"<25"); =СЧЁТЕСЛИ(F2:F35;">39"); =СЧЁТ(F2:F35)-I42-I43.

Кількість слухачів вікової групи від 25 до 40 визначається відніманням із загальної кількості слухачів тих, кому менше 25 і більше 39. Для обчислення загальної кількості слухачів використовується статистична функція СЧЕТ(список значений), яка видає кількість чисел у списку аргументів.

9. Обчисліть середній вік слухачів різних курсів ІПО за формулою: =СРЗНАЧ(F2:F35). Установіть формат результата Числовой без десяткових знаків.

10. Поставте знак „1” в окремому стовпці G електронної таблиці напроти тих слухачів, які можуть опинитися у „групі ризику” по працевлаштування, та прочерком (знаком „–”) – останніх слухачів.

Для цього використовуйте логічні функції ЕСЛИ, И, ИЛИ, які реалізують поставлену умову вибору.

У групу риску умовно можна віднести слухачів курсів, у яких одночасно виконуються дві умови:

- вік більше тридцяти дев’яти;

- мета навчання – перекваліфікація або початкове навчання.

Ці умови об’єднуються логічною функцією И(логическое_условие1;логическое_условие2;…).

Друга умова складна: (мета – або перекваліфікація, або початкове навчання) (рис. 4.7). Вона реалізується у формулі логічною функцією ИЛИ(логическое_условие1;логическое_условие2;…).

Рис. 4.7. Мета навчання

Для того, щоб відмітити одним із двох знаків кожного зі слухачів, використовується функція ЕСЛИ(логическое_выражение;значение
_если_истина;значение_если_ложь). У комірку G2 необхідно записати таку формулу ЕСЛИ(И(F2>39;ИЛИ(E2="перекваліфікація";
E2="поч. навчання"));1;"-"), яку потім необхідно скопіювати у відповідні комірки.

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

11. Підрахуйте кількість слухачів, які належать до групи ризику, використовуючи математичну функцію СУММ(список чисел) за формулою: =СУММ(G2:G35).

Ця інформація може знадобитися службам працевлаштування для вирішення проблем цієї групи.

12. Підрахуйте кількість слухачів різної спеціалізації. Отримані розрахунки можна використовувати під час планування викладацького складу.

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

14. Підрахуйте кількість слухачів, які передбачають від’їзд за кордон.

15. Збережіть внесені зміни у файлі ipo.xls та здайте на перевірку викладачеві.

Контрольні питання [24]

1. Які логічні функції вбудовані в MS Excel?

2. Для чого використовується функція ЕСЛИ?

3. Для чого використовуються функції ЦЕЛОЕ, СЕГОДНЯ?

4. Що являє собою синтаксис функції СЧЕТЕСЛИ?

5. Що являє собою синтаксис функції СЧЕТ?

Тема 9. Графічне представлення даних у табличному процесорі Microsoft Excel

Мета

Закріпити навички побудови діаграм різних типів за допомогою Мастера диаграмм та їх редагування.