ЛЕКЦІЯ 2. РОБОТА З ФОРМУЛАМИ В ТАБЛИЧНОМУ ПРОЦЕСОРІ MS Excel План лекції: 1. Формула робочого листа. 2. Допустимі операції у формулах. 3. Поняття відносної та абсолютної адресації Мета: вивчення правил введення формул в електронну таблицю, операторів MS Excel та порядок їх виконання, поняття відносної та абсолютної адресації. Міні-лексикон: Міні-лексикон: формула робочого листа, оператори, операнди, абсолютне посилання, відносне посилання, змішане посилання.

Формула робочого листа – це елемент, який робить електронні таблиці дуже корисними та популярними. З формулами введеними в комірки можна працювати так само, як і з іншими даними, тобто: копіювати, переміщати, видаляти. Формула повинна починатися зі знака рівності (=), за яким іде набір величин, що обчисляються. Формули в Excel підпорядковуються визначеному синтаксису, у який входить: n знак рівності (=), n операнди (елементи, що обчисляються), n оператори. Операндами можуть бути: n константа, n посилання або діапазони посилань, n заголовки, n імена або функції.

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

Для посилання на комірку з іншої книги треба послідовно вказати: ім'я книги в квадратних дужках, потім ім'я листа, знак "! " і адресу комірки (=[Книга2]Лист1 !$А$1). При створенні формул використовують: n арифметичні операції; n операції порівняння; n операції конкатенації рядків; n адреси комірок або діапазонів комірок; n числа; n строкові константи; n вбудовані функції; n функції користувача. ОператориОперанди

Операторами позначаються операції, які треба виконати над операндами формули. У Excel включено чотири види операторів: Арифметичні оператори використовуються для виконання основних математичних обчислень над числами (наприклад: додавання, вирахування або множення, інші дії над числами й одержання чисельних результатів). Результатом виконання арифметичної операції завжди є число. Оператори порівняння використовуються для позначення операцій порівняння двох чисел. Результатом виконання операції порівняння є логічне значення ІСТИНА або НЕПРАВДА.

Текстовий оператор "&" (операції конкатенації рядків) використовується для позначення операції об'єднання послідовностей символів у єдину послідовність. Адресні оператори (адреси комірок або діапазонів комірок) об'єднують діапазони комірок для здійснення обчислень. Якщо формула складається з кількох операторів, дії виконуються відповідно до пріоритету, поданому в таблиці. Для зміни порядку виконання операторів використовуються круглі скобки.

Символ оператора Призначення оператора ^Піднесення до степеня /Ділення *Множення -Віднімання +Додавання &Конкатенація (зтягування рядків) =Логічне порівняння: дорівнює >Логічне порівняння: більше ніж <Логічне порівняння: менше ніж <>Логічне порівняння: не дорівнює >=Логічне порівняння: не менше ніж <=Логічне порівняння:не більше ніж

Приклади формул робочого листа Формула робочого листа Опис =3*5Добуток трьох на пять =А1+С1Додавання вмісту комірок А1 та С1 =С3^(4/5)Піднесення до степеня (4/5) вмісту комірки С3 =СУММ(С4:С8)Використавши вбудовану функцію СУММ, розраховується сума значень діапазону С4:С8 =А1& &C1Конкатенація тексту комірки А1, пропуску та тексту комірки С1

Приорітети оперцій у формулах 1. Адресні оператори (:, ). 2. Відємність (використовується для вказування відємних чисел: -1;- 0,23 тощо). 3. Процент (%). 4. Піднесення до степеня (^). 5. Множення та ділення ( * та / ). 6. Додавання та віднімання ( + та - ). 7. Обєднання послідовності символів ( & ). 8. Оператори порівняння ( =,, =, <> ). !!! Якщо у формулі використовуються оператори з однаковими пріоритетами (* та /), то вони виконуються у послідовному порядку з ліва на право. Щоб змінити порядок виконання дій використовують круглі дужки.

Помилки, які виникають під час написання формул ПомилкаОпис #ПУСТО!Використано помилковий оператор перерахунку діапазонів, або ж помилкове посилання на комірку #ДЕЛ/0!В якості дільника використовується посилання на комірку значення якої дорівнює нулю або пусте #ЗНАЧ!Введено текст замість числового або логічного значення;

#ССЫЛКА!Посилання на комірку, яка не існує #ИМЯ?Помилка у використанні імені, або використання імен, які не існують #ЧИСЛО!Функція з числовим аргументом використовує некоректний аргумент #Н/Д!Скорочення терміну Неопределенные Данные. Допомагає запобігти використанню посилань на пусту комірку ######Комірка занадто вузька для числа

Посилання є ідентифікатором комірки або групи комірок книги. Вони поділяються на: n відносні; n абсолютні; n змішані. Відносне посилання вказує на комірку виходячи з її положення по відношенню до комірки, в якій знаходиться формула. За замовченням для таких посилань застосовується нотація у вигляді А1, де літера позначає стовпчик, а цифра - рядок, на перехресті яких знаходиться комірка. Якщо формулу, що містить відносні посилання, скопіювати в іншу комірку, вона буде посилатись на комірки з тим же відносним розташуванням, що і в вихідній формулі.

Використання відносних адрес дозволяє "заповнити" формулою ряд суміжних комірок, для такого швидкого копіювання формул зручно використовувати спосіб перетягування маркера заповнення (правий нижній кут виділених комірок). Абсолютне посилання задає на абсолютне положення комірки на листі. Щоб задати абсолютне посилання, перед літерою стовпчика і номером рядка ставиться символ $, наприклад, $А$1. При копіюванні формули, що містить абсолютне посилання, в обчисленнях будуть використовуватись значення тих же комірок, що і у вихідній формулі. Змішане посилання можна розглядати як комбінацію відносного і абсолютного посилання.

В ньому теж використовується символ $, але тільки перед літерою стовпчика або перед номером рядка. Наприклад: $А1 або А$1 Як і в абсолютному посиланні символ $ використовується, щоб "зафіксувати" стовпчик або рядок. !!! За допомогою клавіші F4 можна циклічно змінювати тип виділеного посилання. Посилання не обов'язково вводити в формулу з клавіатури. Для цього зручно користуватись мишею: щиглик по комірці вставляє посилання на неї в формулу. Відносні посилання (відносна адресація комірок) – це посилання в яких відлік необхідних комірок розпочинається з поточної комірки. Абсолютні посилання – це посилання на комірки адреси яких не змінюються під час копіювання формул.

Абсолютна адресація Відносна адресація

Домашнє завдання. Вивчити та законспектувати такі теми: 1. Створення списків автозаповнення користувача. 2. Заповнення даними діапазону комірок. 3. Спеціальна вставка. 4. орматування комірок: форматування чисел, вирівнювання вмісту комірок. 5. Автоформатування. 6. Умовне форматування.