Урок (практична робота) MS Excel. Використання формул та функцій. Побудова діаграм

ПРАКТИЧНА РОБОТА

ТЕМА: MS Excel. Використання формул та функцій. Побудова діаграм

МЕТА: Сформувати поняття формули; абсолютних, відносних та мішаних посилань; розглянути методи введення формул; пояснити правила запису абсолютних, відносних та мішаних посилань на комірки та діапазони комірок; правила запису формул і використання адрес комірок та діапазонів у формулах; формувати вміння працювати з формулами, використовувати різні типи посилань, будувати діаграми та графіки на основі табличних даних; форматувати діаграми.

Обладнання: ПК, робочий зошит, тестові завдання

В результаті виконання практичної роботи студент повинен

знати:

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

вміти:

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

Тривалість роботи 2 години

Питання для підготовки до практичної роботи

1. З яких компонентів може складатися запис формул?
2. Назвіть правила введення формул до ЕТ.
3. Що зображено в клітинці після введення в неї формули?
4. Який пріоритет операцій в ЕТ?
5. Як можна змінити порядок виконання арифметичних дій у формулах?
6. Як виконати копіювання формули?
7. Які особливості копіювання формул з відносними й абсолютними посиланнями?
8. Для чого призначена кнопка Автосумма?
9. Що розуміють під поняттям діаграма?
10. Які способи побудови діаграми ви знаєте?
11. Які види діаграм ви знаєте?

Короткі теоретичні відомості

Обчислення в таблицях виконуються за допомогою формул. Формула може складатися з математичних операторів, значень, посилань на комірку й імена функцій. Результатом виконання формули є деяке нове значення, що міститься в комірці, де знаходиться формула. Формула починається зі знаку рівності "=". У формулі можуть використовуватися арифметичні оператори: (^) — піднесення до степеня; (*) — множення, (/) — ділення; (+) — додавання, (—) – віднімання. Порядок обчислень визначається звичайними математичними законами. Для зміни порядку виконання дій використовують круглі дужки.

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

Для введення формули у комірку потрібно ввести знак "=" і необхідну формулу для обчислення. Після натискання клавіші Enter у комірці з'явиться результат обчислення.

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

Наприклад:

=СУММ(А5:А9) – сума значень в клітинках А5, А6, А7, А8, А9;

=СРЗНАЧ(G4:G6) – середнє значення в клітинках G4, G5, G6.

Функції можуть входити одна в іншу, наприклад:

=СУММ(F1:F20)ОКРУГЛ(СРЗНАЧ(H4:H8);2);

Для введення функції у комірку необхідно:

1. виділити клітинку для формули (один раз клікнути на ній);
2. викликати Мастер функций за допомогою команди меню Вставка  Функция або кнопки ;
3. у діалоговому вікні, що з'явилося (рис. 23), вибрати тип функції в переліку Категория і потім потрібну функцію в переліку Выберите функцию;

Рис. 23 Вікно Мастер функций

4. натиснути кнопку ОК;
5. у полях Число1, Число2 та ін. наступного вікна (рис. 24) ввести аргументи функції (числові значення або посилання на комірки);

Рис. 24 Вікно Аргументы функции

6. щоб не вказувати аргументи з клавіатури, можна натиснути кнопку , яка знаходиться праворуч текстового поля Число1, наприклад, і виділити мишею ділянку комірок, що містять аргументи функції; для виходу з цього режиму слід натиснути кнопку ;
7. натиснути ОК.

Вставити в комірку функцію суми СУММ можна за допомогою кнопки .

Копіювання формул. Якщо формулу треба застосувати для всього стовпця таблиці, то її створюють у самій верхній комірці, а потім формулу копіюють по всьому стовпцю – покажчик миші підводять до правого нижнього кутка комірки з формулою (при цьому він перетворюється на «+»), натискують на мишу й, не відпускаючи її, «тягнуть» формулу вздовж стовпця. Формула копіюється, автоматично змінюються адреси комірок.

Така адресація, коли відбувається зміна адрес комірок при копіюванні, називається відносною. Також існує абсолютна адресація, коли адреси комірок при копіюванні не змінюються. Адреса має вигляд $А$2 і створюється введенням символів з клавіатури або адресу виділяють і натискують на клавішу F4. Абсолютні адреси слугують, зокрема, для посилання на клітинки, які містять константи, що є у формулах.

Змішана адреса містить лише один символ $. Правило: частина адреси після символу $ не модифікується під час копіювання формули.

Різні види помилок розкривають причини, з яких нас не розуміє Excel:

# ДЕЛ/О! - Означає ділення на нуль. Найчастіше буває тоді, коли за дільник вибрана пуста клітина.

# ИМЯ? - Неправильно набрано ім'я клітини в формулі. Наприклад, замість латинських літер використані українські.

# ЗНАЧ! - Замість числа у формулі використано текст.

#ССЫЛКА! - Клітинка, на яку робилося посилання, була видалена командою Удалить  меню Правка, коли відбувався зсув клітинок по рядку і стовпчику.

#ЧИСЛО! - Спроба добути квадратний корінь з від'ємного числа,

#### результат обчислень надто великий або малий, щоб його можна відобразити в Excel.

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

Діаграми поділяються на стандартні (найбільш поширені) та нестандартні (використовуються зрідка).

Часто діаграми будують за допомогою програми, яка має назву Майстер діаграм, її можна запустити двома способами:

• натисканням на кнопку Майстер діаграм на панелі інструментів;
• командами з меню Вставка – Диаграмма....

Під керівництвом майстра виконують чотири кроки.

Крок 1: вибирають тип і вигляд діаграми.

Крок 2: задають діапазони з даними (якщо вони не були, вибрані заздалегідь).

Крок 3: задають параметри (підписи, легенду, вигляд осей наявність сітки) діаграми.

Крок 4: зазначають, куди заносити діаграму (на окрему чи поточну сторінку).

Щоб перейти до наступного кроку, натискають на кнопку Далее, а щоб повернутися назад — на кнопку Назад.

Можна пропустити один чи два кроки, натискаючи відразу на кнопку Далее, Щоб завершити (часто достроково) роботу майстра діаграм і отримати діаграму, натискають на кнопку Готово. Зміни у створеній діаграмі можна внести за допомогою контекстного меню елементів, команди Диаграмма або панелі інструментів з назвою Диаграмма.

Щоб вставити в діаграму пропущений елемент, використовують команди Вставка – Диаграмма... – Параметры диаграммы. Зверніть увагу на те, що тип діаграми можна будь-коли змінити. Для цього діаграму треба вибрати, викликати майстра діаграм, вибрати інший тип і натиснути на кнопку Готово.

Майже всі діаграми (крім кругової і пелюсткової) мають дві головні осі: горизонтальну – вісь категорій, вертикальну — вісь значень. Об'ємні діаграми мають ще третю вісь — рядів даних.

Діаграма складається з багатьох елементів. Нижче наведено ті, назви яких можна прочитати на екрані, навівши на елемент вказівник миші:

• область діаграми;
• область побудови діаграми;
• легенда;
• заголовок діаграми;
• вісь ряду даних;
• вісь категорій;
• вісь значень;
• маркери даних;
• назва осі значень;
• назва осі категорій;
• стіни, кути (в об'ємних діаграмах) тощо.

Рис. 25 Діаграма

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

Завдання для практичного виконання

Завдання на 4-6 балів

1. Запустіть Excel.

Через ярлик програми на Робочому столі (якщо такий є) або Пуск  Программы  Microsoft Office  Excel.

2.    Подайте наведені вирази як формули Excel та виконайте їх обчислення на Лист 1:

3.    Перейменуйте Лист1 на: Завдання 1.
4.    Збережіть книгу під назвою ПЗ_7_1 у власній папці.
5.    Перейдіть на Лист2. Перейменуйте його: Завдання 2
6.    В комірки А4, А5, А6 введіть формули для обчислення в Excel та запишіть результати обчислення.

Значення змінної знаходиться в комірці А1 і рівне 4, значення змінної - в комірці А2 – число 3, значення змінної - в клітинці А3 – число 5.

7.    Збережіть внесені зміни у файлі.
8.    Продемонструйте результат викладачу.

Завдання на 7-8 балів

9.    З робочого столу скопіюйте папку До ЛПЗ 7 у власну папку
10. З папки До ЛПЗ 7 відкрийте файл Завдання 7_2.
11. Перейдіть на лист Вартість продуктів.
12. Виконайте обчислення вартості товару.

В комірку E3 введіть формулу =C3*D3. Скопіюйте цю формулу на діапазон E4:E7

13. За допомогою кнопки Автосумма на панелі інструментів визначте загальну вартість товару в національній валюті.

Виділіть комірку Е8 і за допомогою кнопки уведіть до неї формулу =СУММ(E3:E7), після чого натисніть Enter.

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

Виділіть комірку F3, уведіть до неї формулу =E3/$A$10, натисніть Enter. Скопіюйте цю формулу на діапазон F4:F8

15. Відформатуйте дані таблиці, задавши відповідний грошовий формат даних.
16. Збережіть внесені зміни у файлі.
17. Продемонструйте результат викладачу.

Завдання на 9-10 балів

18. Перейдіть на лист Населення в регіонах України.
19. Введіть формули для обчислення чисельності сільського населення.
20. Визначте загальну площу території вказаних областей.
21. Додайте до таблиці стовпчик «Відсоток міського населення».
22. Введіть формули для обчислення відсотка міського населення.

В комірку F3 введіть формулу =D3/C3. Скопіюйте цю формулу на діапазон F4:F14. Встановіть для діапазону F3:F14 формат даних Процентный.

23. Додайте до таблиці стовпчик «Міське населення >= 60%».
24. Введіть формули для виявлення тих областей, в яких відсоток міського населення нt менше, ніж 60 %.

В комірку G3 введіть формулу =ЕСЛИ(F3>=60%;"Так";"Ні"). Скопіюйте цю формулу на діапазон G4: G14.

25. Введіть формули для обчислення середнього значення відсотка міського населення у вказаних регіонах.
26. Встановіть оптимальну ширину для кожного стовпчика. Відформатуйте таблицю таким чином, щоб всі дані відображались шрифтом Tahoma 13, заголовки виділіть синім кольором на жовтому фоні. В результаті ви повинні отримати наступну таблицю (рис. 26).

Рис. 26 Населення в регіонах України

27. Побудуйте діаграму, що демонструє загальну кількість населення у вказаних регіонах.
    1.                 Виділіть діапазони комірок A3:A14, C3:C14, утримуючи клавішу Ctrl.
    2.                 Натисніть кнопку на панелі інструментів.
    3.                 Виберіть тип діаграму Гістограма об’ємна і натисніть кнопку Далее.
    4.                 На вкладці Диапазон данных встановіть прапорець в полі «в строках» і натисніть кнопку Далее.
    5.                 На вкладці Заголовки в полі «Название диаграммы» введіть «Чисельність населення» і натисніть кнопку Далее.
    6.                 На останньому кроці виберіть розташування на існуючому аркуші книги.
    7.                 Порівняйте створену гістограму із зразком:

Рис. 27 Гістограма Чисельність населення

28. Відредагуйте діаграму.
    1. Натисніть правою кнопкою на області побудови діаграми і викличте команду «Исходные данные».
    2. У вікні, що відкрилося перейдіть на вкладку «Ряд» і встановіть курсор в полі «Подписи оси Х». Виділіть в таблиці комірку з текстом «Населення (тис. чол.)».
    3. Зверніть увагу на зміни на діаграмі.
29. Відформатуйте діаграму, використовуючи зміну фону, колір шрифту, тип і колір ліній тощо.
30. Збережіть внесені зміни у файлі.
31. Продемонструйте результат викладачу.

Завдання на 11-12 балів

32. На новому листі розв’язати графічно рівняння .

              Метод графічного розв’язання цього рівняння полягає в побудові на одній координатній площині графіків функцій та і у визначенні координат точок перетину цих графіків.

33. Збережіть внесені зміни у файлі.
34. Продемонструйте результат викладачу.