Тема: основи технології опрацювання числових даних у середовищі табличного процесора

Мета:

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

• списки автозаповнення та засоби роботи з ними;
• застосування відносних, абсолютних та мішаних посилань;
• правила модифікації посилань при копіюванні формул;
• правила запису формул, типи операторів та їх призначення;
• синтаксис основних математичних, статистичних та логічних функцій;

• створювати власні користувацькі списки;
• змінювати стилі адресації;
• створювати, редагувати та копіювати формули;
• застосовувати формули для розв'язання задач;
• використовувати майстер функцій для роботи з вбудованими функціями.

Обладнання: комп’ютери зі встановленими ОС та LibreOffice Calc, (дана) інструкція.

Структура уроку

1. Організаційний момент.
2. Актуалізація опорних знань.
3. Вивчення нового матеріалу.
4. Інструктаж з ТБ.
5. Закріплення вивченого матеріалу.
6. Підведення підсумків уроку.
7. Домашнє завдання.

Хід уроку

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

2. Актуалізація опорних знань

1. Які основні можливості електронних таблиць Вам відомі?
2. Які типи даних можна зберігати в клітинках таблиці?
3. Як змінюється вигляд вказівника залежно від дії, яку виконують?
4. Які елементи розташовано на панелі формул?

3. Вивчення нового матеріалу
Мотивація. Редактор електронних таблиць призначено для автоматизації обчислень та спрощення обробки числової структурованої інформації. Тому необхідно знати засоби прискорення вводу та обробки даних і вміти раціонально їх використовувати залежно від поставленої задачі.

Автозаповнення є дуже корисним прийомом, який дозволяє спростити введення даних у електронні таблиці і значно скоротити час, необхідний для введення тексту, послідовних рядів чисел та формул. В LibreOffice Calc можна швидко заповнити діапазон комірок однаковими величинами, рівнозмінними даними, вбудованими списками (назвами місяців, днів тижня). Є можливість створювати та використовувати власні списки.

Заповнення комірок однаковими величинами (введення + виділення):

• ввести дані в комірку і виділити її;

• перемістити вказівник миші на маркер автозаповнення — маленький чорний квадрат у правому нижньому куті виділеної комірки (діапазону);

  

• для тексту і формул — при натиснутій лівій кнопці мишки протягнути маркер заповнення через комірки у потрібному напрямку;

• для чисел — зробити те саме при натиснутій Ctrl.

Можна копіювати дані одразу в кількох стовпчиках чи рядках (див. наступні малюнки).

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

Заповнення комірок однаковими величинами (виділення + введення):

• виділити потрібний діапазон;
• не знімаючи виділення, ввести дані в активну клітинку;
• натиснути клавіші лівий Alt + Enter.

Заповнення комірок однаковими величинами (виділення + введення + вказівка меню):

• виділити ряд даних і суміжний з ним діапазон заповнення:

  

• застосувати вказівку меню Зміни / Заповнити і вибрати напрямок заповнення (у поданому прикладі Вниз):

У результаті стовпчики виділеного діапазону буде заповнено даними.

Цей спосіб автозаповнення можна застосовувати як в межах одного аркуша, так і для групи виділених аркушів, які мають однакову структуру:

• натиснути клавішу Ctrl і клацнути по ярликах аркушів, які потрібно виділити;
• виділити дані і діапазон, у який ми їх копіюємо, як подано нижче;
  

• застосувати вказівку меню Зміни / Заповнити / Вниз, щоб усі виділені аркуші заповнити однаковими даними з виділеного діапазону:

Копіювати дані можна одразу на групу аркушів. Розглянемо це на такому прикладі: перекопіювати дані з аркуша І квартал на порожні аркуші ІІ квартал та ІІІ квартал:

• натиснути Ctrl і клацнути по потрібним ярликах, щоб виділити групу;

• виділити діапазон, який потрібно копіювати:

  
  

• застосувати вказівку меню Зміни / Заповнити / Аркуші..., щоб відкрити вікно діалогу Заповнити аркуші;

• у полі Вибір встановити перемикач Вставити всі і кнопку натиснути Гаразд.

У результаті всі дані виділеного діапазону буде скопійовано у відповідні діапазони аркушів І квартал та ІІ квартал:

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

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

Результат автозаповнення переміщенням маркера для кожного з прикладів даних подано нижче.

Автозаповнення з визначенням кроку зміни даних:

• у суміжні клітини ввести дані, що змінюються на потрібний нам крок;
  
  
• протягнути маркер вниз у напрямку заповнення і отримати результат.
  

Автозаповнення рядами даних (прогресіями):

• ввести величину першого (або перших двох) членів ряду у початкову (початкові) комірки заповнюваного діапазону;

• виділити діапазон, який має бути заповнений (включаючи першу комірку);

  

• виконати вказівку меню Зміни / Заповнити / Ряди...;

• у вікні діалогу Заповнити ряди визначити параметри створюваного ряду.

У групі Напрямок визначають напрямок заповнення, у групі Тип рядів — вид прогресії:

• Лінійно — зростання на величину кроку при переході до наступного елемента;

• Геометричне зростання — кожний елемент (крім першого) є добутком попереднього елемента на стале число;

• Дата — у групі Одиниця часу потрібно встановити потрібний перемикач;

• Автозаповнення — відповідає перетягуванню маркера автозаповнення в робочому аркуші. У цьому випадку поля Приріст і Одиниця часу неактивні.

На малюнках вище подано послідовність заповнення діапазонів датами, нижче — геометричною прогресією:

Автозаповнення вбудованими списками Якщо вміст комірки збігається з початковою величиною раніше заданого списку даних (назви місяців, днів тижня тощо), в результаті автозаповнення у виділений діапазон буде введено наступні елементи цього списку. Щоб скасувати цей режим, треба при перетягуванні маркера утримувати натиснутою Ctrl. Створення списку автозаповнення:

• виконати вказівку меню Засоби / Параметри;

• у лівому полі вікна діалогу вибрати LibreOffice Calc / Списки сортування і натиснути кнопку Створити;

  

• у полі Елементи набрати елементи власного списку й натиснути кнопку Додати.

Тепер у будь-якій з таблиць достатньо набрати перший елемент, виділити його, протягнути маркер. Клітинки буде заповнено наступними елементами списку:

  

Елементи списку можна також виділити в робочому аркуші і далі виконати вказівку Засоби / Параметри. Посилання на виділений діапазон буде вказано в полі Копіювати перелік з. Натиснувши кнопку Копіювати, додаємо власний список в загальний перелік.

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

Адреса клітинки — однозначний ідентифікатор, що складається з назви стовпчика і номера рядка.

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

Абсолютна адреса клітинки — адреса, яка не змінюється при копіюванні вмісту клітин. У її запису перед кожною складовою записують символ $. Наприклад, $G$5 або $FX$12.

Мішана адреса клітинки — адреса, в якій сталою є лише одна складова — назва стовпчика чи номер рядка. Наприклад, $G5 або FX$12.

Стилі адресації
Як усталено у LibreOffice Calc використовують стиль адресації назва стовпчика + номер рядка. Можна вибрати інший стиль, у якому і рядки, і стовпчики занумеровано. У цьому випадку (відносну) адресу записують так: RrCc, де:
r — зсув рядками по вертикалі;
c — зсув стовпчиками по горизонталі.

Щоб змінити стиль адресації, потрібно:

• застосувати вказівку меню Засоби / Параметри;
• у переліку LibreOffice Calc вибирати Формули;
• у полі Синтаксис формул вибирати потрібний стиль.

Подамо вигляд однієї й тієї самої формули, відображеної в різних стилях адресації:

  

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

• назву аркуша (на поданому нижче малюнку Доданок1 і Доданок2);
  
  
• повну назву файлу.

Шаблон (електронної) таблиці — (електронний) документ спеціального типу, що слугує основою для створення інших електронних документів.

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

Збереження документа як шаблону:

• використати вказівку меню Файл / Зберегти як шаблон...;
  
  
  
• у вікні Керування шаблонами натиснути кнопку Зберегти;
  
  
• в наступному вікні діалогу ввести назву шаблону.

• використати вказівку меню Файл / Зберегти як...;
• у вікні діалогу Зберегти як:
  • вибрати теку;
  • вказати назву файлу;
  • у полі Тип файлу вибирати Шаблон електронної таблиці ODF;
  • натиснути кнопку Зберегти.

Редагування шаблону:

• використати вказівку меню Файл / Створити / Шаблони;
• у вікні Керування шаблонами перейти на закладку Електронна таблиця;
• виділити потрібний шаблон.

Файли шаблонів зберігають на комп'ютері користувача, у бібліотеці LibreOffice, в інших джерелах мережі. Після збереження шаблон бажано не змінювати.

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

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

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

Типи операторів:

• арифметичні:
  + — додавання;
  - — віднімання;
  / — ділення;
  * — множення;
  % — знаходження відсотку;
  ^ — піднесення до степеня;
• оператори порівняння:
  = — дорівнює;
  > — більше;
  < — менше;
  >= — не менше (більше або дорівнює);
  <= — не перевищує (менше або дорівнює);
  <> — відмінне від (не дорівнює);
• текстові:
  & — об'єднання послідовностей знаків у одну;
• адресні:
  : — посилання на діапазон;
  ; — об'єднання посилань;
  'інтервал' — посилання на спільні клітинки двох діапазонів.

Зазвичай, після введення формули і натискання клавіші Enter, результат обчислення буде відображено у комірці, а формулу — в рядку формул. Якщо необхідно перейти в режим відображення формул, то потрібно застосувати вказівку меню Засоби / Параметри, у переліку LibreOffice Calc вибирати Вигляд, у полі Показати встановити мітку Формули.

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

Примітка. Кожен з поданих далы прикладів киконувати на чистому аркуші.

Приклад 1. Створити таблицю квадратів натуральних чисел від 10 до 99.

Вказівки до виконання

1. Заповнити діапазон B2:K2 послідовністю чисел від 0 до 9 за допомогою автозаповнення.

2. Заповнити діапазон A3:A11 послідовністю чисел від 1 до 9 за допомогою автозаповнення.

3. У клітинку B3 ввести формулу =($A3*10+B$2)^2 і скопіювати її у клітини С2:K2.

4. Не знімаючи виділення, за допомогою маркера автозаповнення копіювати формули в діапазон B4:K11.

5. форматувати таблицю для зручності використання.

Приклад 2. Створити шаблон для знаходження площі, радіусу вписаного та описаного кіл правильного трикутника за відомою довжиною сторони трикутника.

Вказівки до виконання

1. У клітини A1, A2, A4, A5, A6 внести текст-пояснення.

2. Виділити кольором клітинку B2, куди буде введено довжину сторони.

3. В клітини B4, B5, B6 відповідно внести формули для розрахунку площі, радіусу описаного та вписаного кіл (якщо не знаєте, див. наступні малюнки).

4. Використати вказівку меню Файл / Зберегти як шаблон.... У вікні Керування шаблонами натиснути кнопку Зберегти і в наступному вікні діалогу ввести назву шаблону Правильний трикутник.

На малюнку нижче показано отриману таблицю у звичайному режимі й режимі відображення формул.

Функція (електронної таблиці) — це заздалегідь визначена (розробниками ПЗ) формула, що виконує обчислення за заданими величинами, які називають аргументами.

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

Синтаксис функції — це послідовність розташування символів у її запису.

Введення запису функції (альтернативи):

• з клавіатури при появі підказки щодо синтаксису й аргументів;

  
  

• викликати вікно Помічника з функцій:

  • або натисканням клавіш Ctrl + F;
  • або натисканням кнопки Помічник з функцій у рядку формул;
  • або вказівкою меню Вставка / Функція...

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

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

Ще один інструмент для роботи з функціями вмикає вказівка меню Вставка / Список функцій.

У правій частині вікна LibreOffice Calc відкривається перелік категорій функцій, ліворуч від нього — кнопка , яка вставляє виділену функцію у формулу:

Категорії бібліотеки функцій LibreOffice Calc:

Математичні функції

SUM — знаходить суму вказаних аргументів.
Синтаксис: SUM(число1; число2; ...; число30).
Приклади використання
=SUM(C1; D5; F8; H3)
=SUM(A1; B5: B7; 20; B4+20)
=SUM(E2:E20)

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

ABS — обчислює абсолютну величину (модуль) числа.
Синтаксис: ABS(число)
Приклади використання
=ABS(-150)
=ABS(-150+23/5)
=ABS(C11)

SQRT — обчислює (арифметичний) квадратний корінь невід'ємного числа.
Синтаксис: SQRT(число)
Приклади використання
=SQRT(625)
=SQRT(А11)

POWER — підносить основу до вказаного степеня.
Синтаксис: POWER(основа; степінь)
Приклади використання
=POWER(2;5)
=POWER(4-25/3;3)
=POWER(B7;-3)

RADIANS — перетворює градусну міру в радіанну.
Синтаксис: RADIANS(число)
Приклади використання
=RADIANS(120)
=RADIANS(С5)

COS — знаходить косинус кута, заданого в радіанах. Якщо кут задано в градусах, для переведення його в радіани використовують функцію RADIANS.
Синтаксис: COS(число)
Приклади використання
=COS(B7)
=COS(RADIANS(90))

SIN — знаходить синус кута, заданого в радіанах.
Синтаксис: SIN(число)
Приклади використання
=SIN(F7)
=SIN(RADIANS(120))

TAN — знаходить тангенс кута, заданого в радіанах.
Синтаксис: TAN(число)
Приклади використання
=TAN(D7)
=TAN(RADIANS(-45))

PI — повертає 3,14159265358979 — наближення числа Піфагора π. Ця функція не має аргументів.
Синтаксис: PI()
Приклад використання
=PI()

ROUND — округляє число до вказаної кількості знаків після десяткової коми. Якщо кількість знаків від'ємна — до найближчих десятків (сотень, тисяч), якщо дорівнює 0 або не вказана — до найближчого цілого.
Синтаксис: ROUND(число;кількість знаків)
Приклади використання
=ROUND(B4;3)
=ROUND(289,55578;-2)

COMBIN — знаходить кількість комбінацій без повторень для заданої кількості елементів. Інакше кажучи, кількість підмножин, що містять k елемнтів, множини, що містить n елементів, для заданих натуральних n і k.
Синтаксис: COMBIN(число n; число k)
Приклад використання
=COMBIN(11;3)

COUNTIF — у вибраному діапазоні обчислює кількість клітинок, які задовольняють певній умові, тобто їхня величина збігається з вказаною або задовольняє певну нерівність. Умову записують як число, текст, логічний вираз, посилання на клітинку з умовою.
Синтаксис: COUNTIF(діапазон; умова)
Приклад використання
=COUNTIF(D3:D10; 100)
=COUNTIF(B3:B27; "Відмінно")
=COUNTIF(C5:C20; >3)
=COUNTIF(A2:A25; C1)

SUMIF — знаходить суму величин у вказазаному діапазоні клітин, яким відповідають комірки, величини у яких задовольняють певну умову.
Синтаксис: SUMIF(діапазон для перевірки; умова; діапазон додавання)
Приклад використання
=SUMIF(C4:C15; >3; D4:D15)

Приклад 3. Обчислити величини функції y = sin²x + (x + a)^1/2 на відрізку [–3; 3] з кроком 0,5 при a = 5,25.

1. У клітину B1 ввести величину сталої a.
2. У клітинки B3 та C3 ввести перші дві величини змінної x.
3. Виділити клітинки B3 та C3.
4. За допомогою маркера заповнити діапазон D3:N3 величинами x.
5. У клітину B4 ввести формулу =POWER(SIN(B3);2)+SQRT(B3+$B$1).
6. Скопіювати введену формулу у клітинки С4:N4.

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

Для запису функції можна використати Помічник з функцій:

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

Статистичні функції

MAX — знаходить найбільшу величину серед вказаних аргументів — чисел або посилань на клітинки з числами. Текст ігнорується. Дорівнює 0, якщо серед аргументів немає чисел.
Синтаксис: MAX(число1; число2; ...; число30)
Приклад використання
=MAX(A3:C5; D8;E5)
=MAX(A1; B2:F7; 256; B5+20)
=MAX(E1:E25)

MIN — знаходить найменшу величину серед вказаних аргументів — чисел або посилань на клітинки з числами. Текст ігнорується. Дорівнює 0, якщо серед аргументів немає чисел.
Синтаксис: MIN(число1; число2; ...; число30)
Приклад використання
=MIN(A1; B3; C3; D10; 1024; E5: E10)

AVERAGE — обчислює середнє арифметичне аргументів — чисел та посилань на клітинки з числами.
Синтаксис: AVERAGE(число1; число2; ...; число30)
Приклад використання
=AVERAGE(A1; B3; C3; D10; 1024; E5:E10)

GEOMEAN — обчислює середнє геометричне аргументів — чисел та посилань на клітинки з числами.
Синтаксис: GEOMEAN(число1; число2; ...; число30)
Приклад використання
=GEOMEAN(B2:D10; E5:E10)

COUNT — знаходить кількість чисел серед аргументів (чисел, посилань на клітинки з числами).
Синтаксис: COUNT(число1; число2; ...; число30)
Приклад використання
=COUNT(B10;C10;D10;105;E10)

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