СУЧАСНА ІНФОРМАТИКА (сила практики в теорії): Абсолютні, відносні й мішані посилання на комірки та діапазони комірок

Формули можна вводити в будь-які комірки аркуша. Якщо вміст комірки починається зі знаку рівності =, LibreOffice Calc тлумачить це як попередження про наступну за ним формулу і намагається виконати вказані у формулі дії. Якщо це вдається (формулу записано правильно, а її аргументи мають той самий тип, що передбачено), при перегляді користувач у цій комірці побачить результат розрахунків. Інакше він побачить повідомлення про помилку. Саму формулу відображено в комірці лише у тому випадку, коли комірка перебуває в режимі редагування (наприклад, після подвійного клацання на ній). Якщо комірку лише виділено, то її формулу подано в рядку формули у верхній частині вікна Libre Office Calc.

Типи операцій обчислення:

арифметичні: +, -, *, /, ^, %;
порівняння: =, >, <, >=, <=; <>
об’єднання тексту: & (амперсанд);
посилань:

двокрапка : — оператор діапазону, створює посилання на всі клітинки, які розташовано між двома посиланнями (включно з ними);
крапка з комою ; — оператор об’єднання, об’єднує кілька посилань в одне;
пробіл — оператор перетину, який створює посилання на клітинки, спільні у двох посиланнях.

Порядок застосування (пріоритет) операторів такий:

Оператори посилань (:, пробіл, ;)
Унарний мінус (у записі від’ємних чисел)
%
^
*, /
+, -
&
Оператори порівняння

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

Відносні посилання — посилання, які при копіюванні формул (вмісту комірок) змінюються відповідним чином (адреса аргумента «зсувається» на ту саму кількість клітин по горизонталі і вертикалі, на яку «пересунуто» формулу).

Наприклад, якщо до комірки А3 записали формулу =А1+А2 і скопіювали цю формулу до комірки В5, то в комірці В5 буде формула =В3+В4.

Абсолютні посилання — посилання, які при копіюванні формул (вмісту комірок) не змінюються ніяким чином. Такі посилання використовують, коли зміна адреси у посиланнях на комірку небажана. Наприклад, якщо до комірки записано сталий постійний коефіцієнт, використаний у багатьох. Абсолютні посилання відрізняються від відносних наявністю знака $ перед назвою стовпчика і номером рядка.

Мішані посилання — це посилання, у яких при копіюванні формул (вмісту комірок) не змінюється лише назва стовпчика або номер рядка. У мішаних посиланнях знак $ використовують або безпосередно перед назвою стовпця, або безпосередньо перед номером рядка, який потрібно залишити тим самим.

Види помилок при наборі формули

Синтаксичні:

відсутність знаку рівності «=» призводить до тлумачення формули як тексту;
неповна адреса комірки призводить до відображення #NAME? у комірці.

Циклічні посилання: посилання в аргументах формули на саму формулу. Призводить до повідомлення про помилку (Виявлене циклічне посилання). Дані в комірці з такими посиланнями обнуляються. Роботу формули заблоковано, тобто при зміні даних перерахунку величини функції проведено не буде.
Логічні: неправильно складено формулу, у формулі помилкове посилання на комірку.

Тлумачення відображення помилок

##### — числова величина не вміщається в комірці;
#DIV/0! — спроба ділення на нуль;
#NAME? — не можливо розпізнати, що використано у формулі. Наприклад, використано неповну адресу комірки;
#VALUE — формула повертає величину, що не відповідає означенню формули або функції. Ця помилка також виникає у випадку посилання на текст замість числа;
#REF! — використано недопустиме посилання на комірку.

Приклад 1. Обчислити для учнів класу суму річних оцінок з певних предметів і середній бал з цих предметів у класі.

Опис виконання. Для поданого прикладу заповнення комірок А1:F6 у комірку G2 ввести формулу =SUM(B2:F2), а в комірку В8 ввести формулу для обчислення середнього значення =AVERAGE(B2:B6). За допомогою маркера автозаповнення поширити формули на всі відповідні комірки таблиці.

Приклад 2. Скласти таблицю розрахунку заробітної плати.

Опис виконання. Для поданого прикладу заповнення комірок А1:G8 зробити таке:

В комірку G2 (Нараховані гроші) ввести формулу =D2*E2/F2 і поширюємо її на діапозон G2:G6.
В комірку H2 (Податок) ввести формулу =G2*20/100 і поширити її на діапозон H2:H6.
В комірку I2 (До виплати) ввести формулу =G2-H2 і поширити її на діапозон I2:I6.
В комірку G8 (ВСЬОГО) ввести формулу =SUM(G2:G6) і поширити її на діапозон G8:I8.

Приклад 3. Обчислити площу круга, об’єм сфери, бічну поверхню циліндра, змінюючи радіус (круга, сфери, основи циліндра) від 1 до 2 з кроком 0,2 при висоті циліндра 4,2.

Опис виконання. Для поданого прикладу заповнення комірок А1:А10 і А9:B10 зробити таке:

В комірку В2 ввести формулу: =$A$10*A2^2 і поширити її на діапозон B2:B7.
В комірку С2 ввести формулу: =4/3*$A$10*A2^3 і поширити її на діапозон C2:C7.
В комірку D2 ввести формулу: =2*$A$10*A2*$B$10 і поширити її на діапозон D2:D7.

Приклад 4. Обчислити cos(5); sin(–1,3); 5^1/2; x² + y⁵ при х = 8,9 та у = 9,1; x² + x⁹ – 6 при х = 6,2.

Приклад 5. Обчислити для різних аргументів х від 1 до 20 з кроком 0,5 значення функції:
а) y = sin(x + 5) lg x – (x + 8.96³)^1/2;
б) z = tg(x + 3.4) – cos y.

Створення формули за допомогою Помічника з функцій
Набір формул з клавіатури передбачає знання назв і синтаксису запису формул. Навряд чи пересічний користувач спроможний запам'ятати весь перелік формул з описом правильного запису аргументів і призначення. Для полегшення роботи використовують Помічник з функцій.

Приклад 6. Визначити рівень освітніх досягнень учнів залежно від їх середнього балу, використовуючи Помічник з функцій).

1. Створити таку таблицю.

2. В комірку Н2 ввести знак рівності — початок запису формули.
3. Викликати Помічник з функцій:

або за допомогою кнопки f(x), розташованої біля рядка формул;
або за допомогою вказівки меню Вставка / Функція;
або за допомогою комбінації клавіш Ctrl + F2.

У програмному вікні Function Wizard вибрати категорію логічні, функцію IF і натиснути кнопку Next >>.

5. В наступному вікні у пункті Текст ввести умову G2>=10, у рядок Тоді_значення вказати результат високий, що відповідає істинності умови, а в Інакше значення знову ввести функцію IF.

6. При введенні другої функції IF використати складену умову AND(G2>=7;G2<10) .

7. При введенні третьої функції IF у рядок Текст ввести складену умову AND(G2>=4;G2<7), у Тоді_значення вказати результат достатній, а в Інакше значення ввести початковий. Результат можна переглянути на вкладці Structure (Структура).