Виконання обчислень у табличному процесорі Excel. Формули

Прочитатйте теоретичний матеріал:

Для опрацювання даних в Excel використовуються формули і функції.

Формула в Excel — це вираз, що починається зі знака рівності і задає операції над даними у клітинках електронної таблиці та порядок їх виконання.

Формула може містити числа, тексти, адреси клітинок і діапазонів клітинок, знаки математичних дій (оператори), дужки та імена функцій.

Правила запису формул

1. Запис формули починають знаком «=», далі йде сама формула.
   Для внесення у формулу адреси клітинки достатньо клацнути відповідну клітинку.

2. Формулу слід записувати за правилами лінійного запису: вираз має бути записаний у вигляді рядка символів, порядок дій у виразі регулюється дужками.

Приклад:

Вираз 17∗5+2143∗4−41 має бути записаний так: =(17∗5+21)/(43∗4−41)

Під час уведення формул потрібно дотримуватися таких правил:

• для позначення математичних дій використовуються такі оператори:
  + – додавання; – – віднімання;
  * – множення; / – ділення;
  ^ – піднесення до степеня; % – знаходження відсотків.
  Наприклад, =12+138; =2^4-3; =1000/3417+5;

• у формулах Excel не можна пропускати оператор множення;

• пріоритет операцій збігається з порядком виконання операцій, прийнятим у математиці, за окремим винятком: операція знаходження протилежного числа в Excel має вищий пріоритет, ніж операція піднесення до степеня. Тому в Excel значення за формулою =–5^2 дорівнює 25, а не –25, як у математиці;

• для обчислення відсотків від числа потрібно виконати множення числа на ці відсотки, увівши у формулу після кількості відсотків знак %. Наприклад, формула знаходження 25 % від числа 134 виглядатиме так: =134*25%. Результатом обчислень буде число 33,5;

• для змінення порядку виконання дій використовують круглі дужки. Наприклад, =(12+13)8, =2^(4-3), =1000/(3417)+5.

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

Приклад:

У клітинку D2 введена формула =((3+2)^2)/2. У клітинці відображається реультат обчислення виразу. Якщо зробити клітинку D2 поточною, у рядку формул відобразиться введена формула (рис.1).

Рис.1

Адреси клітинок у формулах

У формулах можна використовувати адреси клітинок.

Приклад:

Для обчислення вартості покупки слід у клітинці С2 (рис.2) знайти добуток значень з клітинок А2 і В2, тому в клітинку С2 введено формулу =А2*В2 (рис.2).

Рис.2

Рис.3

Якщо у формулі використовуються адреси клітинок, то для обчислення за такою формулою використовуються дані із вказаних клітинок.

Зверни увагу!

Щоб при введенні формули увести в формулу адресу клітинки, слід клацнути по цій клітинці.

При цьому адреси клітинок у формулі та межі відповідних клітинок виділяються певним кольором для зручності контролю правильності введення формул (рис.2). Уведення формули завершується натисненням клавіші Enter або вибором кнопки Ввід у Рядку формул. Після введення формули виділення кольорами зникає.

Якщо змінити дані у клітинках, адреси яких задіяні у формулі, результат обчислень за формулою буде автоматично переобчислено.

Приклад:

Якщо змінити вміст клітинки А2, переобчислення значення у клітинці С2 відбудеться автоматично (рис.4).

Рис.4

Можливі помилки при введенні формул

Під час уведення формул у клітинках електронної таблиці можуть з’являтися повідомлення про помилки.

Копіювання та переміщення формул

Вміст клітинок з формулами можна копіювати та переміщувати, використовуючи елементи керування Стрічки, команди контекстного меню, сполучення клавіш, а також автозаповнення.

Щоб обчислити вартість покупки другого і третього товарів, можна скопіювати формулу з клітинки С2 у клітинки С3:С4 . Зробити це можна за допомогою автозаповнення:
1. Зробити поточною клітинку С2, у якій міститься формула для копіювання.
2. Навести вказівник на маркер заповнення.
3. Протягти маркер заповнення через діапазон С3:С4.

У клітинки діапазону С3:С4 скопіюються формули, за ними буде виконано обчислення і в клітинках відобразяться результати (рис.1).

Рис.1

При цьому у формулі автоматично змінилися адреси клітинок і формули набули вигляду =A3*B3 та =A4*B4.

Копіювання відбувалося з клітинки С2 у клітинку С3, різниця між номерами рядків цих двох клітинок дорівнює 3 – 2 = 1. Тому у скопійованій формулі всі номери рядків у адресах клітинок збільшилися на 1.

Аналогічно, під час копіювання формули з С3 у клітинку С4 формула =A3*B3 змінилась на =A4*B4.

Скопіюємо формулу з клітинки С2 у клітинку D2 (рис.2). В адресі клітинки D2 номер стовпця на 1 більше, ніж номер стовпця клітинки С2, тому всі номери стовпців у формулі, що копіюється, збільшилися саме на 1 стовпець, і формула =A2*B2 автоматично змінилася на =B2*C2.

Рис.2

Зверни увагу!

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

Модифікація формул під час копіювання формул відбувається за таким правилом: до номера стовпця (рядка) додається різниця номерів кінцевого та початкового стовпців (рядків).

Зверни увагу!

Під час переміщення формули не модифікуються.

Імена та адреси клітинок і діапазонів, використані у формулах, називають посиланнями.
Посилання, які змінюються під час переміщення та/чи копіювання формул, називають відносними.

Під час копіювання чи переміщення формул відбувається модифікація відносних посилань у нових формулах.

Клітинки, на які у формулах зроблено посилання, називатимемо адресними.

Під час копіювання та переміщення формул можуть виникнути помилки, аналогічно до тих, які виникають під час уведення даних. Видалення адресних клітинок призводить до помилок у формулах, які містять посилання на видалені клітинки.

Абсолютні та мішані посилання

Іноді формули містять посилання на значення, яке після переміщення чи копіювання формул не повинно змінюватись.

Посилання, які не змінюються під час копіювання формул, називають абсолютними.

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

Абсолютні та/або мішані посилання зліва від заголовка стовпця та/чи рядка містять символ $.

На закріплення виконайте інтерактивні вправи.

Скріни виконаних вправ прикріпіть до цього завдання.