Розробка заняття дистанційного навчання з дисципліни «Інформатика і КТ»  для студентів другого курсу (11 клас) спеціальності «Облік і оподаткування»

Тема: «Майстер функцій. Логічні та статистичні функції. Проведення розрахунків»

Лабораторна  практична робота

Тема: Майстер функцій. Логічні та статистичні функції. Проведення розрахунків.

Мета.

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

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

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

РАНГ, РАНГ.РВ (число, діапазон, порядок)  - повертає  рейтинг числа в списку інших чисел. Ранг – це позиція числа відносно інших. Число - це  значення, для якого визначається ранг. Діапазон – список чисел  (нечислові значення в списку ігноруються), серед яких визначається позиція. Порядок  визначає спосіб впорядкування. Якщо прядок нуль або пропущений  - ранг числа визначається від 1 для лідера. Якщо порядок  будь-яке ненульове число, то ранг визначається в зворотному порядку.

СУМЕСЛИ ( діапазон1, умова, діапазон2)  - використовується для створення підсумкового значення по деякому діапазону, опираючись  на значення іншого діапазону сумування. Клітини діапазону1 перевіряються на відповідність умові. Умова може бути подана у вигляді числа, виразу, тексту і визначає, які клітини треба сумувати. Діапазон2 – це клітини, які сумують.

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

ЕСЛИ ( умова ; значення1 ; значення2 ), де

Умова – це будь-яке значення або вираз, яке приймає значення істина або хибність( так або ні), наприклад : ЕСЛИ(А1=1,10,100), якщо в клітині А1 стоїть 1  то вираз приймає значення 10,  інакше  - 100.

Значення1 – якщо умова  істинна це значення буде повернуто до клітини.

Значення2 – це значення буде повернуто до клітини, коли умова не стверджується (в клітині А1 стоїть не 1, а будь - яке інше число).  Якщо замість значення2 нічого не стоїть то в клітині буде стояти ЛОЖЬ.

У вікно функції переміщуватися по полям аргументів можна за допомогою клавіші Тab,  також Ви отримаєте пояснення яку інформацію треба ввести до поля де стоїть курсор.

ХІД РОБОТИ

1. Відкрити вікно програми Excel. Поточну сторінку перейменувати на КТГГ.
2. .Умова задачі: Участь у спортивних змаганнях між командами оцінюється наступним чином: за перемогу –3 бали, нічию - 2, поразку - 1, не брали участь - 0 балів. Кулінарний та конкурс художньої самодіяльності оцінюють від 1 до 5 балів.
3. Створити на робочій сторінці наступну таблицю.

Результати проведення Дня здоров’я КТГГ

4. Продовжити таблицю такими стовпцями та заповнити їх формулами з використанням стандартних функцій:

Сума балів      -   СУММ(),

Кількість перемог.    -   СЧЕТЕСЛИ(),

Не брали участь – СЧЕТЕСЛИ(),

Кількість очок за перемоги  -   СУММЕСЛИ(),

Чи виграла команда в конкурсі художньої сам. - ЕСЛИ() , МАКС():

=ЕСЛИ(МАКС(по показн. Худ.сам. всіх  ком)=Худ.самод; ”Виграла”; ”-“)

Чи виграла команда в кулінарному конкурсі - ЕСЛИ, МАКС():

=ЕСЛИ(МАКС(по показн. Кул.конк.всіх ком)=Кулін.конк; ”Виграла”; ”-“)

Без поразок зіграли команди    - ЕСЛИ(),

Рейтинг команд     - РАНГ.РВ().

5. Під таблицею визначити суму балів лідера (1місце) МАКС() та аутсайдера МИН() (10 місце) змагань. Таблицю окреслити. Перейти на нову сторінку.
6. Перейменувати нову сторінку з ім’ям МріяВ. Створити на сторінці таблицю за зразком:

7. До клітини F7 занести таку формулу :

= ЕСЛИ( Е7=2 ; 80,50 ; 53,50).   Формулу скопіювати вниз.

8. Вартість замовлення становить :

= Вартість 1 місця за добу * Кількість замовлених місць* Кількість днів.

Формулу скопіювати вниз.

9. Вирахувати дату від’їзду по кожному замовленню.
10. Додати в таблицю ще декілька замовлень з розміщенням в номерах Люкс  (л).
11. Скорегувати формулу в клітині F7 :

Під таблицею знайти загальну суму всіх замовлень.

12. Скопіювати на другу сторінку всю інформацію. Сторінку перейменувати на МріяА^*.
13. Для вибору вартості 1 місця використати абсолютні адреси. В клітину F7 занести таку формулу :

= ЕСЛИ( Е7=2 ; $В$4 ; ЕСЛИ (Е7=3 ; $С$4 ; $D$4 )). Формулу скопіювати вниз.

14.  В клітину Е9 ввести 4. Таке розміщення не передбачене, виникла помилкова ситуація. Скорегувати формули стовпчика F. Якщо в клітині стоїть не передбачене значення, вивести слово Помилка.
15. Зберегти книгу з ім’ям Лабораторна робота_4. xls та надіслати викладачу на перевірку.
16. Виконати тестове завдання.

Тестові  питання (створено в MS Forms)

1. Абсолютні адреси клітин використовують

• для оформлення типового розрахунку зі зміною клітин
• для фіксації адреси при копіюванні формул
• для зміни адреси при копіюванні в формулах
• якщо формулу не треба копіювати

2. Скільки становить сумарний податок ПДФО та Військовий збір.

• 19,5%
• 18%
• 18,5%
• 20%

3. Призначення функції ЕСЛИ?

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

4. Як створити іменовані клітини?

• Вибрати клітини команда
• Диспетчер імен / Створити
• Вибрати клітини, в рядку формул ввести ім’я
• Ввести ім’я в клітину чи діапазон

5. Призначення функції РАНГ?

• повертає порядковий номер числа відносно інших чисел у списку за зростанням
• повертає порядковий номер числа відносно інших чисел у списку за спаданням
• нумерує числа в списку
• дозволяє визначити порядковий номер числа відносно інших чисел у списку

6. Як зробити з відносної адреси абсолютну?

• за допомогою клавіші F4
• за допомогою клавіші Fn
• за допомогою клавіші F4 або F4 + Fn
• за допомогою клавіші Alt +F4

7. Як вибрати несуміжний діапазон клітин?

• використовуючи клавішу Ctrl
• використовуючи клавішу Shift
• використовуючи клавішу Tab
• використовуючи клавішу Alt

8. Скільки функцій ЕСЛИ необхідно використати в формулі, якщо маємо 4 варіанти відповідей?

• 5
• 2
• 3
• 4

9. Скільки аргументів має функція ЕСЛИ?

• 5
• 2
• 3
• 4

10. Як скорегувати формулу в клітині?

• вибрати клітину внести зміни в рядку формул чи в клітині
• ввести формулу заново
• вилучити формулу
• вибрати клітину, клавіша F2 внести зміни

11. До якої категорії належать функції РАНГ.РВ(), СЧЕТЕСЛИ(), МАХ(), СРЗНАЧ()?

• статистичні
• математичні
• логічні
• аналітичні

ДОМАШНЄ ЗАВДАННЯ*

17. Створити нову книгу з ім’ям Лабораторна робота_4.1.xls. Створити на сторінці таблицю за зразком:

18. Розрахувати заробітну плату для 10 працівників. Формули до стовпців ввести самостійно виходячи з наступних умов:

• Нараховано складається з оплати за денні та нічні зміни. Тривалість нічної зміни – 5 год., денної – 8 год:

• За 1 год. денної роботи працівник отримує  - 100 грн.
• За 1 год. нічної – на 40% більше.

Якщо погодинна оплата менша за мінімальну ЗП потрібно доплатити різницю до мінімальної ЗП (знайти за допомогою Internet показник мінімальної заробітної плати по країні).

• Премія становить 20% від нараховано і нараховується працівникам, стаж яких перевищує 5 років.
• Доплата на дітей становить 500 грн. на кожну дитину, її отримують ті працівники, у яких 2 і більше дітей.
• Доплата на харчування становить 10% від оплати в нічні зміни.
• Профспілкові внески складають 1% від усіх нарахувань.
• Кредит у банку сплачують ті, хто його брав у розмірі 20% від суми нарахувань, але не більше 500 грн.
• Податки:  ПДФО нараховується – 18%, військовий збір – 1,5%.
• Сума нарахувань = Нараховано + Премія + Доплата на харчування + Доплата на дітей.
• Видати на руки = Сума нарахувань – Податки – Проф.внески - Кредит

19. Зберегти книгу і надіслати викладачу.