Ім'я файлу: 94455.docx
Розширення: docx
Розмір: 952кб.
Дата: 08.02.2022
скачати
Пов'язані файли:
теорія.docx
Model_metod_TPR.pdf
Реферат1-WPS Office.docx
Колірна система PANTONE.docx
116987.pptx
Розширення: docx
Розмір: 952кб.
Дата: 08.02.2022
скачати
Пов'язані файли:
теорія.docx
Model_metod_TPR.pdf
Реферат1-WPS Office.docx
Колірна система PANTONE.docx
116987.pptx
Лабораторна робота № 10
Тема: Використання формул і функцій у MS Excel
Мета: сформувати вміння та навички щодо роботи з формулами; абсолютними, відносними ти мішаними посиланнями; закріпити на практиці правила запису формул і перетворення абсолютних, відносних та мішаних посилань під час копіювання формул; розвивати інформаційну компетентність.
Обладнання: ПЕОМ IBM PC; ОС Windows; Microsoft Office 2013; Microsoft Excel.
Теоретичні відомості
Структура формули
Формули – це вирази, по яких виконуються обчислення. Формула завжди починається із знаку дорівнює (=). Формула може включати функції, посилання на комірки або імена, оператори і константи.
Наприклад, у формулі =СУММ(В2:В8)*30
СУММ() – функція;
В2 і В8 – посилання на комірки;
: (двокрапка) і * (зірочка) – оператори; 30 – константа.
Функції – заздалегідь визначені формули, які виконують обчислення по заданих величинах, що називаються аргументами, і у вказаному порядку. Структура функції: ім'я функції, відкриваюча дужка, список аргументів, розділених крапками з комою, закриваюча дужка. Аргументом функції може бути число, текст, логічне значення, масив, значення помилки, посилання на комірку. Як аргументи використовуються також константи, формули, або функції.
Посилання вказує на комірку або діапазон комірок аркуша, які потрібно використовувати у формулі. Можна задавати посилання на комірки інших листів поточної книги і на інші книги. Посилання на комірки інших книг називаються зв'язками.
Оператором називають знак або символ, що задає тип обчислення у формулі. Існують математичні, логічні оператори, оператори порівняння та посилання.
Константою називають постійне (не обчислюване) значення. Формула і результат обчислення формули не є константами.
Оператори
Арифметичні оператори.
Арифметичні оператори служать для виконання арифметичних операцій, таких як додавання, віднімання, множення. Операції виконуються над числами.
Використовуються наступні арифметичні оператори:
| Оператор | Значення | Приклад |
| + (знак плюс) | Додавання | A1+A2 |
| – (знак мінус) | Віднімання | A1‐A2 |
| Заперечення | ‐A1 | |
| * (зірочка) | Множення | A1*A2 |
| / (коса риска) | Ділення | A1/A2 |
| % (знак відсотка) | Відсоток | 50% |
| ^ (кришка) | Піднесення до ступеня | A1^2 |
Оператори порівняння
Оператори порівняння використовуються для порівняння двох значень. Результатом порівняння є логічне значення: або ІСТИННЕ, або ХИБНЕ.
| Оператор | Значення | Приклад |
| = (знак рівності) | Рівно | (A1=B1) |
| > (знак більший) | Більше | (A1>B1) |
| < (знак менший) | Менше | (A1 |
| >= (знак більше і рівно) | Більше або рівно | (A1>=B1) |
| <= (знак менше і рівно) | Менше або рівно | (A1<=B1) |
| <> (знак менший і більший) | Не рівно | (A1<>B1) |
Текстовий оператор конкатенації
Текстовий оператор амперсанд (&) використовується для об'єднання декількох текстових значень.
| Оператор | Значення | Приклад |
| & (амперсанд) | Об'єднання послідовностей знаків в одну послідовність | "Прізвище"&"Ім’я"&"По- батькові" |
Оператори посилань
Оператори посилань використовують для опису посилань на діапазони комірок.
| Оператор | Значення | Приклад |
| : (двокрапка) | Ставиться між посиланнями на першу і останню комірку діапазону | B5:B15 |
| ;(крапка з комою) | Оператор об'єднання | B5:B15;D5:D15 |
| (пропуск) | Оператор перетину множин, служить для посилання на загальні комірки двох діапазонів | B7:D7 C6:C8 |
Створення і редагування формул
Введення формул з клавіатури
Формули можна вводити з використанням клавіатури і миші при роботі в будь‐якій вкладці Excel. З використанням клавіатури вводять оператори (знаки дій), константи, дужки та, інколи, функції. З використанням миші виділяють комірку і діапазони комірок, що включаються у формулу:
В
иділіть комірку, в яку потрібно ввести формулу.Введіть = (знак рівності).
Виділіть мишею комірку, що є аргументом формули.
Введіть знак оператора.
Виділіть мишею комірку, що є другим аргументом формули.
При необхідності продовжуйте введення знаків операторів і виділення комірок.
Підтвердіть введення формули у комірку: натисніть клавішу Enter або Tab або кнопку
Введення (галочка) в рядку формул.
Наприклад, необхідно створити формулу для розрахунку вартості декількох книг у комірці D2
таблиці на малюнку:
виділіть комірку D2
введіть знак =,
клацніть мишею по комірці В2
введіть знак *,
клацніть мишею по комірці С2
натисніть клавішу Enter.
При введенні з клавіатури формула відображується як в рядку формул, так і безпосередньо у комірці (див. мал.). Комірки, які використовуються у формулі, виділені кольоровою рамкою, а посилання на ці комірки у формулі шрифтом того ж кольору.
Примітка: Для обчислення вартості інших книг в стовбці D слід скористатися автозаповненням.
Створення формул з використанням майстра функцій
Функція – стандартна формула, яка забезпечує виконання певних дій над значеннями, що виступають аргументами. Функції дозволяють спростити формули, особливо якщо вони довгі або складні. Функції використовують не лише для безпосередніх обчислень, але також і для перетворення чисел, наприклад для округлення, для пошуку значень, порівняння і так далі.Для створення формул з функціями зазвичай використовують групу Бібліотека функцій вкладки Формули.
Виділіть комірку, в яку потрібно ввести формулу.
Клацніть по кнопці потрібної категорії функцій в групі Бібліотека функцій, виберіть потрібну функцію.
У вікні Аргументи функції у відповідному полі (полях) введіть аргументи функції. Посилання на комірки можна вводити з клавіатури, але зручніше використовувати виділенням комірок мишею. Для цього поставте курсор у відповідне поле і на аркуші виділіть необхідну комірку або діапазон комірок. Для зручності виділення комірок вікно Аргументи функції можна пересунути або згорнути. Текст, числа і логічні вирази як аргументи зазвичай вводять з клавіатури. Як підказка у вікні відображується призначення функції, а в нижній частині
вікна відображується опис аргументу, в полі якого в даний момент знаходиться курсор. Слід мати на увазі, що деякі функції не мають аргументів.У вікні Аргументи функції натискуйте кнопку ОК.
Наприклад, в таблиці на малюнку у комірці А6 необхідно створити формулу для округлення до двох знаків після коми значення у комірці А4:
виділіть комірку А6;
клацніть по кнопці категорії Математичні в групі Бібліотека функцій і виберіть функцію ОКРУГЛ;
у вікні Аргументи функції (мал. нижче) встановіть курсор в полі Число і на аркуші виділіть комірку А4, потім встановіть курсор в полі Число розрядів і з клавіатури введіть число 2;
натисніть кнопку ОК.Для вставки функції не обов'язково використовувати кнопки категорій функцій в групі Бібліотека функцій. Для вибору потрібної функції можна використовувати майстер функцій. Причому, це можна зробити при роботі в будь‐якій вкладці:
Натисніть кнопку Вставити функцію в рядку формул.
У вікні Майстер функцій: крок 1 з 2 в списку Категорія виберіть категорію функції, потім в списку виберіть функцію.
Натискуйте кнопку ОК або двічі клацніть мишею по назві вибраної функції.
У вікні, що з'явилося, Аргументи функції так само, як і у попередньому випадку введіть аргументи функції. Натисніть кнопку ОК.
Деякі функції Excel
Excel має наступні стандартні функції:фінансові;
дата і часу;
математичні;
статистичні;
масиви;
робота з базою даних;
текстові;
логічні;
перевірка властивостей і значень;
інженерні;
аналітичні.
Наведемо список найуживаніших функцій:
Математичні функції:
ABS(число) повертає абсолютне значення числа. Наприклад, вираз ABS(‐2) дає число 2.
КОРЕНЬ(число) повертає додатне число квадратного кореня числа. Якщо число від‘ємне, то на екран виведеться повідомлення про помилку.
СТЕПІНЬ(число; показник степеня) підносить число до вказаного степеня.
ОКРУГЛ(число; кількість рядків) округлює число до вказаного значення кількості десяткових розрядів.
ОКРУГЛВВЕРХ(х; точність) повертає результат округлення з надлишком до найближчого числа, що кратне точності. Наприклад, вираз ОКРУГЛВВЕРХ (2.2552; 1) = 3.
ОКРУГЛВНИЗ(х; точність) – це округлення з недостачею. Ця функція подібна до ОКРУГЛВВЕРХ. Наприклад, ОКРУГЛВНИЗ (2,2556; 1)=2.
ПИ() виводить на екран число Пі з точністю до 15 чисел. РАДИАНЫ(кут в градусах) обчислює значення кута в радіанах. SIN(кут в радіанах чи в градусах) знаходить значення синуса кута. COS(кут в радіанах чи в градусах) знаходить значення синуса кута. EXP(число). Повертає число e, піднесене до вказаного степеня.
ПРОИЗВЕД(число1;число2;...).Перемножує від 1 до 255 чисел, логічних значень чи чисел представлених в текстовому вигляді..
СУММ(число1;число2; ...). Знаходить суму— від 1 до 255 аргументів.
СУММЕСЛИ(діапазон; критерій; діапазон сумування). Слугує для підсумовування значень у діапазоні, які відповідають вказаній умові. Припустімо, наприклад, що стовпець містить числа, і потрібно знайти суму чисел, більших за 5. Для цього можна скористатися такою формулою:
=SUMIF(B2:B25;">5"). Проте також можна застосувати умову до одного діапазону, а підсумувати відповідні значення в іншому діапазоні.
Статистичні функції:
СРЗНАЧ(число1;число2; ...). Повертає середнє арифметичне аргументів (від 1 до 255
аргументів).
СРЗНАЧА(число1;число2; ...). Повертає середнє арифметичне аргументів, враховуючи числа, текст і логічні значення (від 1 до 255 аргументів).
МАКС(число1;число2; ...). Повертає найбільше значення із списку аргументів. Логічні і текстові значення ігноруються.
МАКСА(число1;число2; ...). Повертає найбільше значення із списку аргументів. Логічні і текстові значення враховуються (аргументи, які містять значення ІСТИНА, обчислюються як 1; аргументи, які містять текст або значення ХИБНІСТЬ, обчислюються як 0 (нуль)).
МИН(число1;число2; ...). Повертає найменше значення із списку аргументів. Логічні і текстові значення ігноруються.
МИНА(число1;число2; ...). Повертає найменше значення із списку аргументів. Логічні і текстові значення враховуються (аргументи, які містять значення ІСТИНА, обчислюються як 1; аргументи, які містять текст або значення ХИБНІСТЬ, обчислюються як 0 (нуль)).
Логічні функції:
ИСТИНА(). Повертає логічне значення Истина. ЛОЖЬ(). Повертає логічне значення Ложь.
НЕ(логічне значення). Змінює значення Ложь на Истина, а Истина на Ложь.
И(логічне значення1, логічне значення2,…). Повертає значення ИСТИНА, якщо всі аргументи мають значення ИСТИНА; повертає значення ЛОЖЬ, якщо хоча б один аргумент має значення Ложь.
ИЛИ(логічне значення1, логічне значення2,…). Повертає значення ИСТИНА, якщо принаймні один аргумент має значення ИСТИНА, або ЛОЖЬ, якщо всі аргументи мають значення ЛОЖЬ.
ЕСЛИ(лог_вираз;значення_якщо_істина;значення_якщо_хибність). Повертає одне значення, якщо обчислене значення заданої умови — ИСТИНА, та інше значення, якщо обчислене значення заданої умови — ЛОЖЬ.
Переміщення і копіювання формул
Переміщати і копіювати комірки з формулами можна точно так, як і комірки з текстовими або числовими значеннями.
Крім того, при копіюванні комірок з формулами можна користуватися можливостями спеціальної вставки. Це дозволяє копіювати лише формулу без копіювання формату комірки.
При переміщенні комірки з формулою посилання, що містяться у формулі, не змінюються. При копіюванні формули посилання на комірки можуть змінюватися залежно від їх типа (відносні або абсолютні).
Помилки у функціях і аргументах
Основні помилки і деякі можливі причини їх появи приведені в таблиці.
| Відображення у комірці | Причина | Приклад |
| #ЗНАЧ! | Використання недопустимого типа аргументу або операнда | У формулу замість числа або логічного значення (ІСТИННЕ або ХИБНЕ) введений текст. Для оператора або функції, що вимагає одного значення, вказується діапазон. |
| #ДЕЛ/0! | Ділення числа на 0 (нуль). | У формулі міститься явне ділення на нуль (наприклад |
| #ИМЯ? | Excel не може розпізнати ім'я, використовуване у формулі | =А1/0). Використовується посилання на порожню комірку або комірку, що містить 0 як дільник Використовується ім'я комірки або діапазону, яке не було визначено. Помилка в написанні імені комірки або діапазону. Помилка в написанні імені функції. У посиланні на діапазон комірок пропущена двокрапка (:) У формулу введений текст, що не взято в подвійні лапки. Не задано один або декілька необхідних аргументів стандартної або призначеної для користувача функції аркуша Комірки, на які посилаються формули, були видалені або в ці комірки було поміщено вміст інших скопійованих комірок. У функції з числовим аргументом використовується неприйнятний аргумент Числове значення результату обчислення формули дуже велике або дуже мале, аби його можна було представити в Excel Використовується помилковий оператор діапазону |
| #Н/Д | Значення недоступне функції або формулі | |
| #ССЫЛКА! | Посилання на комірку вказане невірно | |
| #ЧИСЛО! | Неправильні числові значення у формулі або функції. | |
| #ПУСТО! | Задано пересічення двох областей, які насправді не мають загальних комірок |
Окрім критичних помилок, при яких неможливо обчислити результат, Excel відображає повідомлення і про можливі помилки у формулах. В цьому випадку також з'являється індикатор помилки, але у комірці відображається результат.
При виділенні комірки з помилкою поряд з нею з'являється кнопка Источник ошибки. Якщо клацнути по кнопці, з'явиться меню, в якому вказаний тип помилки, а також команди дій для виправлення помилки.
Якщо у формулі дійсно є помилка, слід виправити її за допомогою команди меню Источник ошибки або самостійно. Якщо ж створена правильна формула, можна вибрати команду меню Пропустить ошибку. При цьому індикатор помилки зникне.
1 2 3