Урок №16

Практична робота №9 Виконання обчислень з використанням вбудованих функцій.


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

Теоретична частина
1 Поняття, призначення та класифікація функцій 

1.1 Поняття та призначення функцій 
В електронних таблицях Excel часто для проведення розрахунків використовують різноманітні функції. Функції – це заздалегідь визначені формули, що виконують обчислення за заданими величинами (аргументах) і в зазначеному порядку.
Функції дозволяють виконувати, як прості, так і складні обчислення. Функції в Excel використовуються для виконання стандартних обчислень. Значення, що використовуються для обчислення функцій, називаються аргументами. Значення, що є функціями як відповідь називають результатом. Крім вбудованих функцій, можна використовувати в обчисленнях функції користувачі, що створюються за допомогою засобів Excel.
1.2 Синтаксис функцій 
Щоб використати функцію, потрібно ввести її як частину формули в комірку робочого аркуша. Послідовність, у якій мають розміщуватися використовувані у формулі символи називають синтаксисом функції. Всі функції використовують однакові основні правила синтаксису. Якщо порушити правила синтаксису, то Excel видасть повідомлення про помилку у формулі. Для спрощення роботи з функціями більшість з них була названа від скорочення російськомовних значень цих функцій: Наприклад:
СУММ – функція, що здійснює додавання елементів. 
СРЗНАЧ – функція, що визначає середнє значення. 
Формула починається зі знака «=», за яким вводиться ім’я функції, дужка, що відкривається, список аргументів, розділених крапкою з комою «;», далі дужка, що закривається.
Наприклад: =СУММ(В2;С2). 
Бувають функції без аргументів, які мають такий синтаксис: =Ім’я_функції().
Наприклад: =СЕГОДНЯ(). 
Загальний вигляд функції: = ім’я функції (параметр/и)
Існують різні типи аргументів: число, текст, логічне значення (Правда чи Лож), формули чи інші функції. В кожному конкретному випадку необхідно використовувати відповідний тип аргументу.
1.3 Введення функцій 
Функцію можна вводити в комірку в рядку формули або безпосередньо в комірці. Другий спосіб не є оптимальним, оскільки вимагає знання точного імені функції. Після введення функції та натискання кнопки Enter автоматично відбуваються обчислення і в комірці відображається результат.
1.4 Класифікація функцій
Функції, які використовуються найчастіше і дозволяють виконати сумування даних, визначити середнє, максимальне, мінімальне значення, винесені на панель інструментів Стандартна (піктограма ∑). Для зручності роботи Excel функції розбиті за категоріями:  
o    математичні функції;
o    статистичні функції; 
o    логічні функції; 
o    фінансові функції; 
o    функції дати і часу; 
o    вкладені функції; 
o    функції роботи з базами даних; 
o    текстові функції; 
o    функції посилання та масивів. 
За допомогою текстових функцій є можливість обробляти текст: витягати символи, знаходити потрібні, записувати символи в суворо потрібне місце тексту і багато чого іншого.
1.5 Майстер функцій 
Майстер функцій – це спеціальна програма, за допомогою якої можна вибрати потрібну функцію і виконати її, вказавши всі потрібні параметри. Майстер функцій можна викликати таким чином:
1) Вставка→Функции 
2) натискання кнопки Мастер функций (fx), що розміщенна на панелі інструментів Головна;
3) Shift+F3. 
Вікно Мастера функций складається з трьох частин.

У першій можна ввести опис дії, яку необхідно виконати і натиснути кнопку Найти. Цей метод використовується, якщо користувач не знає чи не пам‟ятає, як називається потрібна йому функція. Нижче є поле для вибору категорії функцій. Для спрощення роботи з великим обсягом вбудованих функцій всі вони розділені на категорії залежно від призначення. Це значно спрощує пошук потрібної функції. Є окремо виділені категорії: 10 недавно используемых функций та Полный алфавитный перечень для спрощення пошуку функцій. У третій частині є можливість вибору функцій відповідно до категорії. При виборі функцій в нижній частині вікна відображається коротка інформація про призначення цієї функції. Після вибору функції з‟являється ще одне діалогове вікно для визначення аргументів визначеної функції. Це можна зробити шляхом введення потрібних даних із клавіатури або безпосередньо зазначенням адреси у таблиці за допомогою миші. Верхня частина вікна містить перелік аргументів та поля для їх введення. У нижній частині – короткий опис функції. Якщо розмістити курсор мишки в полі для введення деякого аргументу, в нижній частині з‟являється пояснення до цього аргументу та його тип. Всі обов‟язкові аргументи виділені напівжирним шрифтом.
1.6 Довідки про функції 
Довідку про необхідну функцію можна одержати, якщо вибрати її зі списку у довідковій системі Excel. Довідку про функції під час її введення в комірку робочого листка можна одержати за допомогою Помощника. Якщо ви знаходитеся у вікні діалогу Мастера функций, то на панелі інструментів необхідно вибрати іструменет, який позначається знаком «?» для виклику Помощника. Якщо ви вводите формулу безпосередньо в комірку робочого листа, то просто наведіть курсор мишки в рядок формул та натисніть F1. Під час діалогу деталізуйте тему довідки – введіть ім‟я функції або її частину. У наступному діалоговому вікні необхідно виділити одну з запропонованих функцій, початок яких збігається з введеним
фрагментом.
2 Математичні та статичні функції
2.1 Математичні функції
Математичні функції використовують різноманітні математичні дії. Вони спрощують різного роду математичні обчислення, наприклад арифметичні та тригонометричні. Розглянемо деякі із них.
o    СУММ – додає аргументи. 
o    КОРЕНЬ – повертає додатне значення квадратного кореня. 
o    COS, SIN, TAN – тригонометричні функції cos, sin і tg. 
o    ACOS, ATAN – зворотні тригонометричні функції arсcos, arсtg. 
o    ГРАДУСЫ – перетворює радіани в градуси. 
o    LN – натуральний логарифм числа. 
o    ABS – модуль числа. 
o    ПИ – повертає число Пі (π=3.14). 
o    ЗНАК – повертає знак числа. 
o    ПРОИЗВЕД – повертає добуток аргументів. 
o    СТЕПЕНЬ – повертає результат піднесення до степеня.
o    12 ОКРУГЛ – закруглює число до заданої кількості десяткових розрядів. 
o    ОСТАТ – повертає залишок від ділення. 
o    СЛЧИС – повертає випадкове число в інтервалі від 0 до 1. 
o    РИМСКОЕ – перетворює число в арабському записі до числа в римському як текст. 
o    СУММЕСЛИ – повертає суму вмісту комірок, яке задовольняє заданий критерій; 
o    СУММКВ – повертає суму квадратів аргументів. 
o    МОБР, МУММНОЖ, МОПРЕД – зворотна матриця, добуток та визначник матриці. 
В електронній таблиці Excel вибрати математичні функції можна з використанням Мастера функций, де в полі Категорія необхідно вибрати Математические і тоді можна буде вибрати необхідну математичну функцію. Якщо виділити курсором мишки будь-яку функцію, то внизу буде написано, що розраховує подана функція та її синтаксис.

Функції категорії Математические Окрему групу становлять функції призначені, призначені для роботи з матрицями. В їх застосуванні є особливості: аргументами таких функцій є діапазон комірок. При введенні функцій, аргументами яких є масиви (матриці) і які повертають як результат матрицю, необхідно перед введенням функції виділяти не одну комірку, куди буде розміщений результат, а діапазон.Завершити введення аргументів матричних функцій слід обов‟язково натисканням комбінації клавіш Ctrl+Shift+Enter, а не просто кнопки OK. Часто на аргументи цих функцій накладається обмеження, викликані математичним обґрунтуванням цих операцій (наприклад, фіксована кількість рядків та стовпців). Для того, щоб обчислити суму значень, введених у деякий діапазон комірок, необхідно виконати таке: 1 Виділити всі комірки діапазону, які будуть додаватися та натиснути кнопку ∑ (автосума) на панелі інструментів. У наступній комірці нижче або правіше від виділеного діапазону з‟явиться результат сумування. 2 Зробити активною комірку, в яку буде розміщено суму та натиснути ∑. Excel автоматично запропонує діапазон сумування, який буде відображатися у формулі. За необхідності його можна замінити. Після такого проведення сумування в комірці, де відображається результат, буде знаходитись формула. Наприклад, сума діапазону комірок рядка від А1 до А20: =СУММ(А1:А20). У цьому прикладі знак «:» означає діапазон з вказівкою першого і останнього членів діапазону. У цьому випадку функція СУММ використовується з одним аргументом – діапазоном комірок. Однак вона може мати більше одного аргументу (до 30), розділених «;». При цьому кожний аргумент може бути як числом, адресою комірки, так і діапазоном комірок. Також можна додавати і діапазони комірок, не розміщені поруч, тобто окремі. Для виділення таких діапазонів необхідно утримувати клавішу Ctrl і мишкою виділяти діапазони. Якщо, наприклад, необхідно перемножити два числа, то треба записати: =ПРОИЗВЕД(А1;В1). За допомогою функції СУММЕСЛИ можна просумувати значення з діапазону комірок, але при цьому здійснюється додавання лише тих значень, які відповідають певному критерію.
2.2 Статистичні функції
Статистичні функції призначені для проведення статистичного аналізу. Крім того, їх можна використовувати для факторного та регресійного аналізу. Спочатку розглянемо найуживаніші:o    СРЗНАЧ – визначає середнє значення.
o    МИН, МАКС – визначає мінімальне та максимальне значення.
o    СЧЕТ – визначає кількість числових аргументів.
Ці функції винесені на панель інструментів Стандартная. Приклад  Необхідно знайти максимальне значення в діапазоні від А2 до А7. Розв’язання Для розв‟язання цієї задачі використовують функцію МАКС(аргументи), що повертає максимальне значення зі списку аргументів: =МАКС(А2:А7).
3 Функції дати і часу та вкладені функції 3.1 Функції дати і часу
Для роботи зі значенням типу дата та час в Excel використовують функції категорії ДАТА і ВРЕМЯ. Розглянемо деякі із них.
1.      Функція ДАТА : Функція ДАТА повертає значення дати. Загальний вигляд функції ДАТА(рік;місяць;день). Функція ДАТА(2000;2;1) залежно від установленого формату дати повертає значення 01.02.00.
2.      Функція ДЕНЬ :Функція ДЕНЬ повертає день дати в числовому форматі. Наприклад, у комірці F2 вміщена дата 28.10.2003, тоді значення функції ДЕНЬ(F2) дорівнює 28.
Функція ДЕНЬНЕД: Функція ДЕНЬНЕД визначає день тижня, на який припадає дата, визначена як аргумент. Синтаксис функції:ДЕНЬНЕД(дата;тип). При цьому аргумент тип визначає порядок розрахунку і може мати значення: 1 (за замовчуванням) – число від 1 (неділя) до 7; 2 – число від 1 (понеділок) до 7; 3 – число від 0 (неділя) до 6. Функція =ДЕНЬНЕД(28.10.2003) повертає значення 3, а функція =ДЕНЬНЕД(«23.10.2003»;2) – значення 2.
3.      Функція СЕГОДНЯ Функція СЕГОДНЯ має загальний вигляд СЕГОДНЯ() і повертає значення поточної дати.
4.      Функція ЧАС Функція ЧАС повертає значення часу в налаштованому часовому форматі. Синтаксис запису: ЧАС(години;хвилини;секунди).
5.      Функція ТДАТ Функція ТДАТ повертає поточну дату та час. Синтаксис функції: ТДАТ().
6.      Функція МЕСЯЦ Функція МЕСЯЦ використовується для визначення місяця. Синтаксис функції: МЕСЯЦ(дата в числовому форматі). Наприклад, МЕСЯЦ(10.01.2007) повертає значення 1.
7.      Функція ДНЕЙ360 Функція ДНЕЙ360 визначає кількість днів між двома датами, яку вона вираховує на основі 360-денного року.
 3.2 Вкладені функції
Вкладені функції – це функції, аргументами яких можуть бути інші функції. Розглянемо деякі приклади.
Приклад  Необхідно визначити поточний рік.

Приклад  Необхідно визначити поточний рік. Приклад  Необхідно визначити поточний рік.Розв’язання Функція ГОД() визначає значення року від певної дати, а функція СЕГОДНЯ() визначає поточну дату. Тому для визначення поточного року необхідно записати: =ГОД(СЕГОДНЯ()).

Практична частина

1)      Створення і форматування таблиці. Введення формул, організація обчислень
Нехай на складі знаходиться такий товар: CD-R, CD-RW, DVD-R, DVD-RW, flesh-пам’ять (1 Гб), flesh-пам’ять (16 Гб). На Листі1 файлу Формули_функції.xls створіть таблицю для обліку кількості і вартості проданого товару (див. рис.1). Відформатуйте її згідно зразка. Здійсніть відповідні обчислення:
- Ціна у доларах обчислюється як частка ціни в гривнях та коефіцієнта обмінного курсу. У клітинку В3 введіть відповідну формулу. Використайте поняття абсолютного і відносного посилання.
-  Аналогічні обчислення виконайте для комірок діапазону В4:В8 і встановіть для клітинок діапазону B3:B8 відповідний грошовий формат.
-  У клітинку D3 введіть формулу    =С3+С3*0,2, що обчислює ціну з ПДВ.
-  За допомогою Автозаповнення скопіюйте зазначену формулу до діапазону D4: D8.
-  Заповніть клітинки діапазону F3:F8, використовуючи відповідну формулу або вбудовану функцію ПРОИЗВЕД (множення). За допомогою Автозаповненняскопіюйте зазначену формулу до діапазону F4:F8.
- Обчисліть загальну вартість проданого товару.
-  Перейменуйте Лист1 на Вартість покупки.
Рис.1. Товари на складі

2)    Використання стандартних функцій редактора Ехсеl
Перейменуйте Лист2 на Успішність студентів і створіть таблицю за зразком  (рис.2).
- Заповніть стовпці CDEFGзначеннями від 1 до 12 балів.
- Обчисліть середній бал кожного студента (стовпець І) і середній бал з кожного виду робіт (рядок 11), використовуючи відповідну стандартну функцію.
- Обчисліть сумарний бал кожного студента (стовпець J).
Рис. 2. Таблиця успішності

Питання для контролю і самоперевірки
1)     Призначення і можливості електронної таблиці Microsoft Excel 2010.
2)     Поняття про форматування даних у комірках таблиці. Способи зміни форматування даних у комірках.
3)       Як закріпити певні області таблиці?
4)     Засоби обчислення в Ехсеl 2010.
5)     З якого символу починається формула?
6)    Що може бути складовими елементами формул?
7)    Як визначається порядок виконання операцій у формулах?
8)    Які дії необхідно виконати для введення формули до клітинки?
9)    Операція Автозаповнення, її сутність і порядок застосування.
10)          Задання у формулах абсолютного і відносного посилання.
11)          Категорії функцій, що використовуються у Microsoft Excel 2010. Способи здійснення вибору діапазону значень функції.
12)          Яким способом можна вставити функцію у формулу?

Немає коментарів:

Дописати коментар