Робота з ЕТ

Основним інструментом для обробки даних в Excel є формули.

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

Компоненти формул

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

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

Оператори, тобто інструкції для виконання дій (наприклад, +,-,*).

Числа або текстові значення (наприклад, 0,12 або Дохід).

Функції з набору вбудованих функцій Excel (наприклад, СУММ).

Посилання на комірки і діапазони - ці компоненти присутні, якщо у формулу потрібно підставити значення, що містяться в інших комірках (наприклад, А2,СЗ:С15).

Зазначимо ще раз, що Excel вважає формулою будь-який запис у комірці, який починається із символу =. Однак запис формули можна почати і зі знаків + або -, при цьому наступний запис також буде сприйнятий як формула. Наведемо приклади формул у комірках ЕТ:

=С1*В1

=СУМ(АЗ:А12)

=0,35*F7+1,4*F7

Введення формул до ЕТ

Формула, що набирається в електронній таблиці, як і будь-який інший запис, відображається у рядку формул. Однак є одна відмінність: після команди введення формули (натискання клавіші = або клацання по кнопці =) у рядку формул з'являються нові кнопки: X; V. Зміст їх простий: клацання по кнопці з червоним хрестиком X скасовує внесені до формули зміни, а клацання по кнопці з зеленої «галочкою» V. дає команду на виконання формули, записаної у комірці. Завершити введення формули можна натисканням на клавішу Enter, або переміщенням курсора до іншої комірки, або клацанням по кнопці з зеленою галочкою в рядку формул.

Після команди введення запис формули в комірці зникає і з'являється результат її виконання або повідомлення про помилку. Відредагувати введену формулу можна як звичайний текст, введений до комірки. Для прикладу наберіть у комірці А4 формулу =А1+А2+АЗ. Потім відредагуйте її, замінивши на формулу =А1+А2*АЗ.

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

Оператори в Excel (арифметичні, порівняння тощо)

В електронних таблицях Excel є чотири види операторів: арифметичні, текстові, оператори порівняння й оператори посилань (адресні оператори).

Арифметичні оператори

Ці оператори призначені для виконання арифметичних операцій над числами. У Excel можуть застосовуватися сім арифметичних операторів .

Арифметичні оператори

Символ оператора	Назва оператора	Приклад формули	Результат
+	Додавання	=1,5+2,2	3,7
-	Віднімання	=6-5,5	0,5
/	Ділення	=4/5	0,8
*	Множення	=5*6	30
^	Піднесення до степеня	=3^2	9
%	Відсоток	=25%	0,25

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

Оператори порівняння

У роботі з числами і текстом застосовуються оператори порівняння . Вони використовуються для надання твердженням значень ІСТИНА або ХИБНІСТЬ. Якщо твердження правильне, то комірці, що містить формулу, буде надано значення ІСТИНА. Якщо ж твердження неправильне, то до комірки буде занесене значення ХИБНІСТЬ.

Символ оператора	Назва оперетора	Приклад формули	Результат
=	Дорівнює	=2=3	ХИБНІСТЬ
>	Більше	=2>3	ХИБНІСТЬ
<	Менше	=2<3	ІСТИНА
>=	Більше або дорівнює	=2>=3	ХИБНІСТЬ
<=	Менше або дорівнює	=2<=3	ІСТИНА
0	Не дорівнює	=2<>3	ІСТИНА

Текстовий оператор

В Excel є один текстовий оператор & . Цей оператор об'єднує послідовності символів із різних комірок в одну послідов­ність, тому його називають також оператором об'єднання.

Нехай до комірок А1 і А2 введені слова «по» і «тяг» відповідно, а до комірки A3 записана формула =А1&А2, тоді результатом у комірці A3 буде слово "потяг".

Текстові значення у формулах беруть у лапки "...". Наприклад, щоб скласти ім'я файла Config.sys зі змісту двох комірок С1 і С2, що мають значення Config і sys відповідно, потрібно записати формулу:

=С1&"."&С2

Адресні оператори

Адресні оператори використовуються при вказівці посилань на комірки таблиці. Нагадаємо, що прямокутні діапазони комірок позначаються за допомогою двокрапки, наприклад, A3 : Р7 . Коли в посиланні потрібно об'єднати два діапазони комірок, то діапазони записуються через кому, наприклад: A3:F7, В6:К13. Отже, вираз =СУМ(АЗ:Р7, В6:К13, С5) означає додавання комірок прямокутних діапазонів A3:F7 і В6:К13, а також комірки С5.

Двокрапка і кома, що використовуються при записуванні посилань на комірки, саме і є адресними операторами. Ще один адресний оператор у Excel це пробіл. За допомогою пробілу можна посилатися на ділянку перетину діапазонів. Наприклад, запис =СУМ (F1:F8_F5:F13) означатиме додавання комірок від А5 до F8 (тут символ _ позначає порожній пробіл, а не підкреслення).

Пріоритет операторів

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

Наведемо загальний список операторів Excel у порядку зменшення їх пріорітету:

1. Адресні оператори (:, <пробіл> ).

2. Заперечення (використовується для вказівки від'ємних чисел: -1, -0,2 тощо).

3. Відсоток (%).

4. Піднесення до степеня ( ^ ).

5. Множення і ділення ( * і /).

6. Додавання і віднімання (+ і -).

7. Об'єднання послідовностей символів ( & ).

8. Оператори порівняння ( = ,<,>,<=%>=,<>).

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

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

Припустимо, вам потрібно ввести одну й ту саму формулу у суміжні комірки. У цьому разі найзручніше скористатися маркером заповнення. Активізуйте комірку із початковою формулою і протягніть маркер через комірки, до яких потрібно скопіювати формулу. До цих комірок будуть занесені копії формул (із потрібними адресами!), а зміст комірок буде розрахований за цими формулами.

Як приклад розглянемо множення двох діапазонів-стовпців. Запишіть у діапазон А1:А6 і В1:В6 числа, а до комірки С1 занесіть формулу =А1*В1.

Перетягніть маркер заповнення з комірки С1 до комірки, що розташована нижче С2:С6. Результат множення двох стовпців буде поміщений у стовпець С.

Відносні й абсолютні посилання

Обговоримо форми запису посилань у формулах. Це питання тісно пов'язане з принципами адресації комірок в електронних таблицях Excel. Дотепер усі посилання, що використовувалися, були відносними, тобто такими, у яких відлік необхідних комірок робиться від поточної комірки. Наприклад, якщо ви записали до комірки A3 формулу =А1+А2 і скопіювали цю формулу до комірки В5, то в цій комірці одержите формулу =ВЗ+В4. При використанні відносних посилань дана первинна формула є операцією, при якій потрібно обчислити суму двох комірок, розташованих вище. Програма Excel використовує відносні посилання за умовчанням.

Однак можливі випадки, коли зміна адреси в посиланнях на комірку небажана, наприклад, якщо до комірки записано постійний коефіцієнт, що застосовується у формулах. Тоді використовують абсолютні посилання, що не змі­нюються під час копіювання формули. Абсолютні посилання відрізняються від відносних наявністю знака долара $. Формула =$А$1 + $А$2, записана в абсолютних посиланнях, не буде змінюватися при копіюванні або переміщенні до будь-якої комірки.

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

Використання функцій

Синтаксис функцій

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

Розглянемо звернення до функції в Excel. Запис функції починається зі вказівки імені функції, потім випливає список аргументів, взятий у дужки. Наприклад, функція знаходження максимального значення серед аргументів число 1, число2,. . має вигляд:

МАКС(число 1 ,число2,..)

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

Дужки, в які взято список аргументів, завжди мають бути парними; пробіли перед дужками або після них не допускаються. Список аргументів може складатися з чисел, посилань, тексту, масивів, логічних величин (ІСТИНА або ХИБНІСТЬ), значень помилок (наприклад, #ДЕЛ/0). Окремі аргументи в списку слід розділяти комами. Деякі функції можуть мати до 30 аргументів.

Аргументи можуть належати до різних типів. Список аргументів, що складається з чисел і посилань, може бути таким:

СУМ(1,10,В2:В8)

Ця формула повертає значення, що дорівнює сумі чисел 1, 10 і чисел у комірках від В2 до В8.

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

СУМ(А1 :С5,МАКС(В1 :ЕЗ))

функція МАКС, що повертає максимальне значення в діапазоні В1 :ЕЗ, є вкладеною.

Діагностування помилок у формулах

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

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

#ИМЯ? - неможливість розпізнати ім'я, що використовується. Ця помилка виникає, коли неправильно зазначене ім'я об'єкта або є посилання на видалене ім'я, коли неправильно записана функція, коли при запису адрес замість латини використана кирилиця тощо.

#ЗНАЧ! - спроба некоректного використання функції. Поширеною помилкою є невідповідність даних установленому формату, наприклад, замість числа або дати в аргументі стоїть текст.

#ЧИСЛО! - з'являється при неправильному запису або використанні чисел. Можливо, у функції з числовим аргументом використовується аргумент нечислового формату або до комірки введена формула, що повертає занадто велике значення по модулю (понад 10³⁷).

#ССЬІЛКА! - означає неправильне вживання посилань, які є у формулі. Можливо, формула містить посилання на комірку, що уже видалена, або посилання на комірку, до якої скопійовано зміст інших комірок.

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

#ПУСТО! - значення помилки, що з'являється при заданні в посиланні порожньої множини комірок. Можливо, що ви помилилися у визначенні перетину двох діапазонів. Наприклад, поставили у вираз типу (АЗІА6 С4:Р7) замість коми пробіл.

#Н/Д - скорочення від терміна «невизначені дані». Це значення помилки звичайно спеціально вводять до комірок, щоб запобігти обчисленням у них (наприклад, при відсутності даних). Коли необхідні дані з'являються, їх просто вводять у формулу.