Практична робота 13 - Обчислення та розрахунки в таблицях і логічні функції MS Excel, НУДПСУ

Практична робота № 13

Тема. MS Excel. Обчислення та розрахунки в таблицях. Логічні функції

Мета. Ознайомитись із основами роботи в середовищі Ms Excel: типи даних, види посилань, функції. Набути практичні навики використання логічних функцій.

Технологія виконання

1. Скопіювати на Робочий стіл  файл з описом pr_5  і відкрити його.

2. На Робочому столі створіть папку PR_5  <Власне прізвище>, для збереження результатів роботи. Та не забудьте потім скопіювати на сервер у власну папку.

3. Викликати табличний процесор MS Excel і створити нову книгу у файлі з іменем PR_5  <Власне прізвище>.xls.

4. Перейменувати Лист1 на "Оцінки" та Лист2 на "Стипендія".

5. На аркуші "Оцінки" створити і відформатувати таблицю згідно зазначеного зразка (колір, стиль рамки, розмір шрифту на свій смак).

6.1.  Заповнити стовбець А від 1 до 10 використовуючи Прогрессия.

6.2.  Заповнити діапазон В3:G12 використовуючи Форма починаючи із себе (в поле Прізвище вводим Прізвища одногрупників по списку починаючи із себе, в поле Контракт вводим значення Так або Ні, в поля Предмети вводим випадкові оцінки від 2 до 5).

6.3.  Додати перелік прізвищ до Списков (Сервис-Параметры-Списки).

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

6.5.  Відсортувати таблицю по спаданню поля Максимальний бал.

6.6.  Встановити Условное форматирование для діапазону D3:G12 (умова –значення =2, формат – клітинка червоного кольору).

6.7.  В комірку Н13 ввести формулу для відображення Середнього балу позитивних оцінок по всіх предметах усіх студентів (тобто не враховуємо оцінку 2) використовуючи функцію суммесли та счётесли.

6.8.  У діапазоні B15:H18 створити таблицю, що відображає кількість оцінок по кожному з предметів (функція счётесли). Останній стовбець містить сумарну кількість оцінок по всіх предметах.

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

6.10. В діапазоні J15:J18 ввести розподіл кількості студентів в залежності від Середнього балу (діапазон в комірках І15:І17) використовуючи функцію Частота (виводить массив розподілу даних по масиву критеріїв). Функцію необхідно вводити як для масивів. (Виділяємо діапазон, вводимо функцію, нажимаємо Ctrl+Shift+Enter).

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

7. Перейти на аркуш "Стипендія" та пов’язати стовбці A, B, C, H (аркуша "Оцінки") із А, В, С, D (аркуша "Стипендія") тобто встановити посилання на аркуш Оцінки.

7.1.  Додати стовбці"Стипендія" та "Отримано".

7.2.  У клітинку G1 ввести розмір податку (податок у відсотках рівний порядковому номеру студента). Даній клітинці присвоїти ім’я "Податок" та відповідну примітку.

7.3.  У клітинку G2 ввести Мінімальний розмір стипендії (МРС у гривнях рівний порядковому номеру студента+100). Даній клітинці присвоїти примітку відповідного змісту.

7.4.  Записати формулу для обчислення розміру стипендії використовуючи логічні функції для одного із студентів  у стовбець "Стипендія" враховуючи, що та заповнити відповідні клітинки для інших студентів.

7.5.  Записати формулу для обчислення суми із врахуванням податку для одного із студентів у стовбець "Отримано" враховуючи, що Отримано=Стипендія-Податок. та заповнити відповідні клітинки для інших студентів.

7.6. Встановити Автофільтр на поле "Середній бал" >4.

8. Необхідно врахувати, що формат клітинок повинен відповідати змісту (клітинки, які містять грошову суму повинні бути грошового типу, відсотки – процентного і т.д.).

9. Переглянути створену таблицю в режимі Предварительный просмотр і зберегти результати роботи. Результат кожного з аркушів повинен бути розміщений на одній сторінці.

Контрольні запитання

1. Які ви знаєте типи даних в Excel?

2. Як зробити вертикальне вирівнювання чисел у грошовому форматі в стовпці таблиці?

3. Як вибрати товщину і колір лінії для обрамлення діапазону клітинок?

4. Як записують адресу діапазону клітинок?

5. Які типи посилань використовують в Excel?

6. Як змінити вид посилання на клітинки у формулі?

7. Для чого використовують імена клітинок?

8. Як записують повну адресу клітинки?

9. На які категорії поділяють стандартні функції Excel?

10. Наведіть приклади стандартних функцій різних категорій.

11. Чи допускає функція СЧЁТЕСЛИ() роботу із розривними діапазонами?

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

13. Для чого використовується функція РАНГ()?

14. Для чого використовується функція ЧАСТОТА()?

15. Які логічні функції ви знаєте?

16. Для чого використовують фільтрацію?

17. Як заповники клітинки значеннями геометричної прогресії?

18. Як розділити робочий аркуш на області?

19. Для чого використовується примітка?

20. Для чого використовується "Специальная вставка"?

21. Як встановити однакову висоту та ширину клітинок?

22. Які існують варіанти завдання критеріїв для розглянутих вище функцій?

23. Яку кількість варіантів форматування можна задати в умовному форматуванні?

З повагою ІЦ “KURSOVIKS”!