Використання формул в електронних таблицях


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

Типи операцій обчислення:
  • арифметичні: +, -, *, /, ^, %;
  • порівняння: =, >, <, >=, <=; <>
  • об’єднання тексту: & (амперсанд);
  • посилань:
    • двокрапка : — оператор діапазону, створює посилання на всі клітинки, які розташовано між двома посиланнями (включно з ними);
    • крапка з комою ; — оператор об’єднання, об’єднує кілька посилань в одне;
    • пробіл — оператор перетину, який створює посилання на клітинки, спільні у двох посиланнях.
Порядок застосування (пріоритет) операторів такий:
  1. Оператори посилань (:, пробіл, ;)
  2. Унарний мінус (у записі від’ємних чисел)
  3. %
  4. ^
  5. *, /
  6. +, -
  7. &
  8. Оператори порівняння
Якщо формула містить оператори з однаковим пріоритетом (наприклад, оператори множення та ділення), обчислення виконують у порядку набору й тлумачення (зліва направо).

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

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

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

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

Види помилок при наборі формули
  1. Синтаксичні:
    • відсутність знаку рівності «=» призводить до тлумачення формули як тексту;
    • неповна адреса комірки призводить до відображення #NAME? у комірці.
  2. Циклічні посилання: посилання в аргументах формули на саму формулу. Призводить до повідомлення про помилку (Виявлене циклічне посилання). Дані в комірці з такими посиланнями обнуляються. Роботу формули заблоковано, тобто при зміні даних перерахунку величини функції проведено не буде.
  3. Логічні: неправильно складено формулу, у формулі помилкове посилання на комірку.
Тлумачення відображення помилок
  • ##### — числова величина не вміщається в комірці;
  • #DIV/0! — спроба ділення на нуль;
  • #NAME? — не можливо розпізнати, що використано у формулі. Наприклад, використано неповну адресу комірки;
  • #VALUE — формула повертає величину, що не відповідає означенню формули або функції. Ця помилка також виникає у випадку посилання на текст замість числа;
  • #REF! — використано недопустиме посилання на комірку.
Приклад 1. Обчислити для учнів класу суму річних оцінок з певних предметів і середній бал з цих предметів у класі.
Опис виконання. Для поданого прикладу заповнення комірок А1:F6 у комірку G2 ввести формулу =SUM(B2:F2), а в комірку В8 ввести формулу для обчислення середнього значення =AVERAGE(B2:B6). За допомогою маркера автозаповнення поширити формули на всі відповідні комірки таблиці.

Приклад 2. Скласти таблицю розрахунку заробітної плати.
Опис виконання. Для поданого прикладу заповнення комірок А1:G8 зробити таке:
  1. В комірку G2 (Нараховані гроші) ввести формулу =D2*E2/F2 і поширюємо її на діапозон G2:G6.
  2. В комірку H2 (Податок) ввести формулу =G2*20/100 і поширити її на діапозон H2:H6.
  3. В комірку I2 (До виплати) ввести формулу =G2-H2 і поширити її на діапозон I2:I6.
  4. В комірку G8 (ВСЬОГО) ввести формулу =SUM(G2:G6) і поширити її на діапозон G8:I8.
Приклад 3. Обчислити площу круга, об’єм сфери, бічну поверхню циліндра, змінюючи радіус (круга, сфери, основи циліндра) від 1 до 2 з кроком 0,2 при висоті циліндра 4,2.
Опис виконання. Для поданого прикладу заповнення комірок А1:А10 і А9:B10 зробити таке:
  1. В комірку В2 ввести формулу: =$A$10*A2^2 і поширити її на діапозон B2:B7.
  2. В комірку С2 ввести формулу: =4/3*$A$10*A2^3 і поширити її на діапозон C2:C7.
  3. В комірку D2 ввести формулу: =2*$A$10*A2*$B$10 і поширити її на діапозон D2:D7.
Приклад 4. Обчислити cos(5); sin(–1,3); 51/2; x2 + y5 при х = 8,9 та у = 9,1; x2 + x9 – 6 при х = 6,2.
Приклад 5. Обчислити для різних аргументів х від 1 до 20 з кроком 0,5 значення функції:
а) y = sin(x + 5) lg x – (x + 8.963)1/2;
б) z = tg(x + 3.4) – cos y.
   
Створення формули за допомогою Помічника з функцій
Набір формул з клавіатури передбачає знання назв і синтаксису запису формул. Навряд чи пересічний користувач спроможний запам'ятати весь перелік формул з описом правильного запису аргументів і призначення. Для полегшення роботи використовують Помічник з функцій.

Приклад 6. Визначити рівень освітніх досягнень учнів залежно від їх середнього балу, використовуючи Помічник з функцій).
  1. Створити таку таблицю.
  1. В комірку Н2 ввести знак рівності — початок запису формули.
  2. Викликати Помічник з функцій:
    • або за допомогою кнопки, розташованої біля рядка формул;
    • або за допомогою вказівки меню Вставка / Функція;
    • або за допомогою комбінації клавіш Ctrl + F2.
  3. У програмному вікні Function Wizard вибрати категорію логічні, функцію IF і натиснути кнопку Next >>.
  1. В наступному вікні у пункті Текст ввести умову G2>=10, у рядок Тоді_значення вказати результат високий, що відповідає істинності умови, а в Інакше значення знову ввести функцію IF.
  1. При введенні другої функції IF використати складену умову AND(G2>=7;G2<10) .
  1. При введенні третьої функції IF у рядок Текст ввести складену умову AND(G2>=4;G2<7), у Тоді_значення вказати результат достатній, а в Інакше значення ввести початковий. Результат можна переглянути на вкладці Structure (Структура).
  1. Натиснути кнопку Гаразд.
  2. Результат поширити на діапазон комірок Н2:Н7.

Закріплення вивченого матеріалу
Створіть книгу LibreOffice Calc. Кількість її аркушів збільшіть до 8. Запишіть книгу у вказану вчителем теку з іменем клас-прізвище. Виконайте подані далі завдання 1–8, подаючи відповідь на відповідному аркуші і звітуючи про виконання кожного завдання одразу по завершенню роботи над ним.
  1. Відомий вік у роках кожного члена сім'ї: дідусь — 72, бабуся — 66, тато — 40, мати — 38, син — 15, дочка — 10. Визначити середній вік у сім'ї.
  2. Скласти прайс-лист книжкового магазину, який містить таку інформацію: список книжок та їх авторів, ціну за один екземпляр, кількість проданих книжок, їх вартість. Знайти загальну вартість проданих книжок.
  3. Клієнт відкрив рахунок у банку на деяку суму під 12% річних. Яка сума буде на його рахунку через 10 років при сталих річних відсотках? Подати щорічні зміни на рахунку таблицею.
  4. Протягом тижня тато Карло давав Буратіно N яблук для продажу. Кіт Базиліо забирав у нього 25% яблук щодня. Подати дані таблицею. Знайти, скільки яблук за тиждень кіт Базиліо відібрав у Буратіно.
  5. Подати таблицею величини функцій f(x) = cos(–3x + π/2) та g(x) = |sin(x/3 + π/2)|1/2 при зміні аргумента x у межах від –3 до 1 з кроком 0,2.
  6. Створити таблицю розрахунку заробітної плати для працівників малого підприємства, у якому працює 5 осіб. Таблиця має містити: № з/п, ПІБ, посаду, оклад, премію, податок, до сплати:
    • податок вилучають у розмірі 20% від суми окладу і премії;
    • до видачі підлягає сума окладу та премії без податку;
    • таблиця містить записи про 5 працівників;
    • підрахувати суми коштів, необхідні для виплати премії, сплати податку та всього до сплати;
    • підрахувати середню заробітну плату працівника.
  7. Ціна електричної енергії для фізичних осіб складає 0,2802 гривень за 1 КВт ∙ год. Для квартир 1–4 показники лічильника у КВт ∙ год за поточний місяць відповідно складають 1698, 395, 1231, 2751, 4999, за попередній місяць — 1569, 256, 985, 2569, 4626. Визначити за допомогою розрахунків у комірках електронної таблиці:
    • витрати електроенергії у кожній квартирі та разом;
    • вартість електроенергії, витраченої у кожній квартирі, та разом;
    • середні арифметичні, найменші і найбільші величини витрат і вартості.
Врахувати, що у квартирі 3 мешкає багатодітна сім'я, яка має пільгу — знижку 50% у межах до 120 КВт ∙ год (і лише у цих межах).
  1. Підготувати аркуш для визначення суми площ 10 кругів за відомими їхніми радіусами.

Комментариев нет:

Отправить комментарий