1. Яке основне призначення табличних процесорів?

2. У якому порядку виконуються дії в арифметичному виразі? Як змінити порядок виконання дій в арифметичному виразі?

3. Що таке формули? Які формули з курсу математики, фізики ви знаєте? Як вони записуються?

4. Які засоби для копіювання даних можна використати в Excel?

Формули в Excel

Як уже зазначалося, у клітинки електронної таблиці, крім чисел і текстів, можна вводити формули.

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

В електронній таблиці формула має бути записана у вигляді рядка символів (так

званий лінійний запис виразу). Наприклад, для обчислення значення виразу ^•

формула виглядатиме так: =(17*5+21 )/(43*4—41).

Під час уведення формул потрібно дотримуватися таких правил:

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

• у формулах Excel не можна пропускати оператор множення;

• пріоритет операцій збігається з порядком виконання операцій, прийнятим у математиці, за окремим винятком: операція знаходження протилежного числа в Excel

має вищий пріоритет, ніж операція піднесення до степеня. Тому в Excel значення за формулою =-5^Л2 дорівнює 25, а не -25, як у математиці;

• для обчислення відсотків від числа потрібно виконати множення числа на ці відсотки, увівши у формулу після кількості відсотків знак %. Наприклад, формула знаходження 25 % від числа 134 виглядатиме так: =134*25%. Результатом обчислень буде число 33,5;

• для змінення порядку виконання дій використовують круглі дужки. Наприклад, =(12+13)*8, =2^Л(4-3), =1000/(34*17)+5.

Після введення формули у клітинці за замовчуванням відображається результат обчислення за цією формулою, а сама формула відображається в Рядку формул, якщо зробити цю клітинку поточною. Тобто, якщо у клітинку С2 (мал. 3.24) ввести формулу =(25+67)/2, то в цій клітинці відобразиться число 46, а в Рядку формул, якщо зробити клітинку С2 поточною, відобразиться введена формула.

Адреси клітинок у формулах

Як уже зазначалося, у формулах можна використовувати адреси клітинок. Наприклад, у клітинці D3 (мал. 3.25, а) для обчислення вартості одного з видів товару введено формулу =ВЗ*СЗ (кількість зошитів * ціна одного зошита), а у клітинці В6 (мал. 3.25, б) для обчислення загальної кількості товару введено формулу =ВЗ+В4+В5 (кількість зошитів + кількість олівців + кількість ластиків).

Для обчислення за цими формулами буде використано числа, які містяться у вказаних клітинках. Тобто, під час обчислення вартості зошитів число 20 буде помножено на число 19,15 і у клітинці D3 відобразиться результат обчислення - число 383,00. Аналогічно для обчислення загальної кількості товару буде визначено суму 20 + 10 + 5 і у клітинці В6 відобразиться результат обчислень за введеною формулою - число 35.

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

Якщо у клітинці ВЗ замість числа 20 увести число 100 (мал. 3.26), то у клітинці D3 результат буде переобчиспено і в ній відобразиться нове значення вартості зошитів -1915 грн, тобто 100 од. * 19,15 грн. Ау клітинці В6 відобразиться нове значення - 115, тобто нове значення загальної кількості товарів (100 + 10 + 5).

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

Дуже часто в електронних таблицях розв’язують задачі, коли у формулах використовуються адреси клітинок, у яких записано не числа, а інші формули. У цьому разі під час обчислення спочатку буде обчислено проміжний результат, а потім - кінцевий.

Наприклад, у задачі про вартість покупки канцтоварів спочатку обчислюється вартість кожного з видів товарів (проміжні результати у клітинках D3, D4, D5) за наведеними раніше формулами. А потім у клітинку D6 вводиться формула =D3+D4+D5 з адресами цих клітинок (мал. 3.27).

Використання у формулах не самих числових значень, що містяться у клітинках, а адрес клітинок має низку переваг. На малюнках 3.28 і 3.29 зображено дві таблиці для обчислення ПДВ (податок на додану вартість), який платить державі будь-яке підприємство, що отримує прибутки. Сьогодні в Україні розмір цього податку становить 20 %.

У таблиці на малюнку 3.28 використовуються формули для обчислення ПДВ із зазначенням конкретної ціни товару: від конкретної ціни кожного товару обчислюється 20 %. А в таблиці на малюнку 3.29 використовуються формули для обчислення ПДВ з адресами клітинок, що містять ціни товарів. Відповідні формули, що містяться у клітинках D7 кожної таблиці, відображаються в Рядках формул. Як бачимо, результати обчислень в обох таблицях однакові.

Однак, під час змінення цін на товари в першій таблиці потрібно буде внести зміни у клітинках двох стовпців (Ціна і ПДВ), а у другій таблиці - лише одного (Ціна), бо у стовпці ПДВ переобчислення виконаються автоматично. А якщо таблиця велика, то економія часу виявиться досить суттєвою.

Якщо передбачити, що може змінитися також і відсоток податку, тоді доцільно виділити для відсотка ПДВ окрему клітинку (наприклад, С2) та використовувати у формулах адресу цієї клітинки, а не конкретне значення ПДВ - 20 % (мал. 3.30).

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

введення формул (мал. 3.31). Уведення формули завершується натисненням клавіші Enter або вибором кнопки

Після введення формули виді

лення кольорами зникає.

Під час уведення формул у клітинках електронної таблиці можуть з’являтися повідомлення про помилки (табл. 3.3).

Таблиця 3.3

Деякі повідомлення про помилки та причини їх появи

Копіювання та переміщення формул. Поняття про модифікацію формул

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

Наприклад, для того щоб обчислити вартість покупки олівців і ластиків (мал. 3.25), можна скопіювати формулу з клітинки D3 у клітинки D4 і D5. Зробити це можна за допомогою автозаповнення:

1. Зробити поточною клітинку D3, у якій міститься формула для копіювання.

2. Навести вказівник на маркер заповнення (маленький чорний квадрат у правому нижньому куті табличного курсора).

3. Натиснути і тримати натиснутою ліву кнопку миші та виділити діапазон клітинок D4:D5.

У клітинки діапазону D4:D5 скопіюються формули, за ними буде виконано обчислення і в клітинках відобразяться результати.

При цьому, як бачимо на малюнку 3.32, у формулі автоматично змінилися адреси клітинок і формули набули вигляду =В4*С4 та =В5*С5.

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

Пояснимо, як саме відбулася зміна адрес клітинок у формулах. Копіювання відбувалося з клітинки D3 у клітинку D4, різниця між номерами рядків цих двох клітинок дорівнює 4 - 3 = 1. Тому у скопійованій формулі всі номери рядків у адресах клітинок збільшилися на 1.

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

Скопіюємо тепер формулу з клітинки В6 у клітинку D6, скориставшись Буфером обміну. Як бачимо, і ця формула модифікувалася. В адресі клітинки D6 номер стовпця на 2 більше, ніж номер стовпця клітинки В6, тому всі номери стовпців у формулі, що копіюється, збільшилися саме на два стовпці, і формула з =ВЗ+В4+В5 автоматично змінилася на =D3+D4+D5.

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

Звертаємо вашу увагу: під час переміщення формули не модифікуються.

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

Вбудовані функції SUM, AVERAGE, MAX, MIN

Розглянемо на прикладі ще одну задачу. В електронній таблиці вказано кількість учнів у 1-11 класах школи. Потрібно визначити загальну кількість учнів школи (мап. 3.33, а).

Для обчислення потрібного значення можна записати у клітинку В14 формулу =ВЗ+В4+В5+В6+В7+В8+В9+В10+В11+В12+В13 (мал. 3.33, я). Ця формула виглядає досить громіздкою і незручною для введення. Спростити запис формули можна з використанням вбудованої функції SUM, яка призначена для обчислення суми чисел у вказаних клітинках і діапазонах клітинок. Формула буде мати такий вигляд =SUM(B3:B13), що значно компактніше і простіше для введення (мал. 3.33, б).

У 7-му класі для обчислень в електронних таблицях будемо використовувати тільки чотири вбудовані функції: SUM, AVERAGE, MAX, MIN. У таблиці 3.4 наведено їхнє призначення та запис.

Таблиця 3.4

Приклади вбудованих функцій в Excel

Функція та її призначення	Приклад запису функції та її результат
ЗиМ(діапазон) Обчислює суму чисел у вказаному діапазоні клітинок	SUM(B10:C15) Сума чисел з діапазону клітинок В10:С15
AVERAGE(Aiana30H) Обчислює середнє арифметичне чисел у вказаному діапазоні клітинок	AVERAGE(A1:A100) Середнє арифметичне чисел з діапазону клітинок А1:А100
МАХ(діапазон) Знаходить максимальне число серед чисел у вказаному діапазоні клітинок	MAX(D5:K5) Найбільше із чисел у діапазоні клітинок D5:K5
МЩдіапазон) Знаходить мінімальне число серед чисел у вказаному діапазоні клітинок	MIN(3:5) Найменше число серед чисел у стовпцях 3, 4 та 5

Уведення наведених функцій зручно виконувати так:

1. Розмістити табличний курсор у клітинку, де має бути результат обчислень.

2. Вибрати на Стрічці у групі Редагування вкладки

Основне список кнопки Автосума £.

3. Вибрати у списку потрібну функцію (мал. 3.34).

Після вибору потрібної функції в поточну клітинку автоматично вставляється знак =, ім’я функції та пара круглих дужок.

4. Увести в середину дужок потрібний діапазон клітинок. Діапазон можна ввести вручну з клавіатури або виділити потрібний діапазон вказівником миші.

5. Натиснути Enter.

Для тих, хто хоче знати більше

Імена клітинок

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

застосування імені клітинки, яка задається під час створення імені. В одній області застосування не може бути клітинок з однаковими іменами.

Надати клітинці або діапазону клітинок ім’я можна в такий спосіб: виділити клітинку або діапазон, увести її ім’я в поле Ім’я і натиснути Enter (при цьому областю застосування імені буде вся книга).

Якщо клітинка (діапазон клітинок) має ім’я та її зробити поточною, то в полі Ім’я буде відображатися не адреса клітинки, а її ім’я. Для перегляду списку всіх імен клітинок, а також даних про область застосування кожного імені можна виконати

Для виділення клітинки чи діапазону, які мають ім’я, потрібно в полі Ім’я ввести ім’я об’єкта.

Ім’я клітинки можна використовувати у формулах. Наприклад (мал. 3.35), якщо у клітинку С2 з іменем Дохід увести число 695699, у клітинку СЗ з іменем Витрати ввести число 609789, а у клітинку С4 ввести формулу =Дохід-Витрати, то у клітинці С4 відображатиметься число 85910.

Імена клітинок під час копіювання та переміщення формул не модифікуються.

Працюємо з комп'ютером

Увага! Під час роботи з комп’ютером дотримуйтеся вимог безпеки життєдіяльності та санітарно-гігієнічних норм.

1. Запустіть табличний процесор Excel.

2. Відкрийте вказаний учителем/учитепькою файл (наприклад, Розділ ЗЇПункт 3.3\ вправа 3.3.xlsx).

3. Сформулюйте, для розв’язування якої задачі було створено цю електронну таблицю (мал. 3.36). Побудуйте математичну модель цієї задачі: які вхідні дані використовуються і в яких клітинках вони розміщені; які проміжні та кінцеві ре-

зультати отримуються і в яких клітинках вони розміщені; за якими формулами здійснюються обчислення? Запишіть відповіді в зошит.

4. Уведіть на аркуші Аркуші у клітинку ЕЗ формулу =B3+C3+D3 для знаходження загальної довжини шляху, який пройшов І турист за три дні змагань.

5. Скопіюйте формулу з клітинки ЕЗ з використанням маркера заповнення в діапазон клітинок Е4:Е7. Для цього: зробіть клітинку ЕЗ поточною, наведіть вказівник на маркер заповнення та виділіть потрібний діапазон клітинок, утримуючи натисну-тою ліву кнопку миші.

6. Запишіть у клітинку В8 формулу =SUM(B3:B7) для знаходження загальної довжини шляху, який пройшли всі туристи за 1-й день змагань.

7. Скопіюйте формулу з клітинки В8 з використанням маркера заповнення в діапазон клітинок С8:Е8 для знаходження загальної довжини шляху, який пройшли всі туристи за ІІ-ІІІ день змагань, і загальної суми.

8. Відкрийте Аркуш2. Сформулюйте, для розв’язування якої задачі було створено цю електронну таблицю (мал. 3.37). Побудуйте математичну модель цієї задачі: які вхідні дані використовуються і в яких клітинках вони розміщені; які проміжні та кінцеві результати отримуються і в яких клітинках вони розміщені; за якими формулами здійснюються обчислення? Запишіть відповіді в зошит.

9. Уведіть у клітинку D3 формулу =ВЗ*СЗ для обчислення кількості хлопців у ЗЗСО № 1, а у клітинку ЕЗ введіть формулу =B3-D3 для обчислення кількості дівчат у цій школі.

10. Скопіюйте формули з діапазону клітинок D3:E3 з використанням маркера заповнення в діапазон клітинок D4:E7.

11. Уведіть у клітинку В8 формулу =SUM(B3:B7) для обчислення загальної кількості учнів у всіх школах міста.

12. Скопіюйте формулу з клітинки В8 в діапазон клітинок D8:E8 з використанням Буфера обміну.

13. Відкрийте АркушЗ. Сформулюйте, для розв’язування якої задачі було створено цю електронну таблицю (мал. 3.38). Побудуйте математичну модель цієї задачі: які вхідні дані використовуються і в яких клітинках вони розміщені; які проміжні та кінцеві результати отримуються і в яких клітинках вони розміщені; за якими формулами здійснюються обчислення? Запишіть відповіді в зошит.

14. Надайте клітинці АЗ ім’я Долар, а клітинці А6 - ім’я Євро. Для цього зробіть поточною клітинку і введіть її ім’я в поле Ім’я.

15. Уведіть у клітинку D3 формулу =СЗ*Долар для переведення ціни товару у гривнях у долари з використанням у формулі імені клітинки, а у клітинку ЕЗ формулу =СЗ*Євро для переведення ціни товару в євро.

16. Скопіюйте формули з клітинок D3 і ЕЗ у клітинки стовпців D та Е.

17. Збережіть електронну книгу у вашій папці у файлі з іменем таблиця 3.3.xlsx.

18. Закрийте вікно програми.

Найважливіше в цьому пункті

У клітинки електронної таблиці, крім чисел і текстів, можна вводити формули. Формула в Excel - це вираз, який задає операції над даними у клітинках електронної таблиці та порядок їх виконання. Починається формула зі знака = і може містити числа, текст, адреси клітинок та їх діапазонів, знаки математичних дій (оператори), дужки та імена функцій. В електронній таблиці формула має бути записана в лінійному вигляді.

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

Вміст клітинок з формулами можна копіювати та переміщувати, використовуючи елементи керування Стрічки, команди контекстного меню, сполучення клавіш, маркер заповнення.

Якщо у формулі містяться адреси клітинок, то під час копіювання у формулі відбувається автоматична зміна адрес клітинок - модифікація формули: до номера стовпця (рядка) додається різниця номерів кінцевого та початкового стовпців (рядків). Під час переміщення формули не модифікуються.

Для спрощення запису формули можна використовувати вбудовані функції SUM, AVERAGE, MAX, MIN, команди їх вставлення розміщено на Стрічці у групі Редагування вкладки Основне.

Клітинкам можна надати ім’я, яке потім можна використовувати у формулах. Під час копіювання таких формул ім’я клітинки не модифікується.

Лайте вілповілі на запитання

1°. Що таке формула? Із чого вона складається?

2*. Яких правил потрібно дотримуватися під час уведення формул?

3^м. У чому полягають переваги використання у формулах адрес клітинок і діапазонів клітинок?

4*. Як уводити у формулу адреси клітинок з використанням миші?

5*. Які повідомлення про помилки можуть з’являтися у клітинках? Про яку помилку повідомляє кожне з них?

6*. Що таке модифікація формул? Коли і як вона відбувається?

7*. Які особливості переміщення даних в Excel?

8*. Які вбудовані функції в табличному процесорі Excel ви знаєте? Яке їх призначення?

9*. Як надати клітинці ім’я? Які є вимоги до імені клітинки?

Виконайте завлання

1°. Відкрийте вказаний учителем/учителькою файл (наприклад, Розділ 3\Пунк 3.3\ зразок 3.3.1.xlsx). Уведіть у клітинку В7 формулу для обчислення загальної площі океанів, а у клітинку С7 - формулу для обчислення найбільшої глибини океанів. Збережіть електронну книгу у вашій папці у файлі з іменем завдання 3.3.1.xlsx.

2°. Створіть в Excel електронну таблицю за зразком, наведеним на малюнку 3.27. Збережіть електронну книгу у вашій папці у файлі з іменем завдання 3.3.2.xlsx.

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

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

4*. Створіть в Excel електронну таблицю за наведеним зразком (мал. 3.40) для обчислення значень нижченаведених виразів для заданих значень змінних х, у, z. Знайдіть результати і запишіть їх у зошит. Збережіть електронну книгу у вашій папці у файлі з іменем завдання 3.3.4.xlsx.

5*. Відкрийте вказаний учителем/учителькою файл (наприклад, Розділ 3\Пунк 3.3\ зразок 3.3.5.xlsx). Уведіть у відповідні клітинки формули для обчислення загальної площі та кількості населення в наведених країнах. Додайте додатковий стовпець, у якому обчисліть щільність населення в кожній країні (щільність = = кількість населення : площу країни). Збережіть електронну книгу у вашій папці у файлі з іменем завдання 3.3.5.xlsx.

6^е. Створіть електронну таблицю для обчислення значення функції у = 2х + 5 на відрізку [-10; 10] за наведеним зразком (мал. 3.41). Уведення значень у клітинки рядків 3 та 4 виконайте автозаповненням. Збережіть електронну книгу у вашій папці у файлі з іменем завдання 3.3.6.xlsx.

7*. У клітинці Е5 записано формулу: =ВЗ+С4 (мал. 3.42). Запишіть, як виглядатиме ця формула, якщо її скопіювати у клітинку: а) Е6; б) 17; в) С4; г) Е9.

8*. Відкрийте вказаний учителем/учителькою файл (наприклад, Розділ ЗЇПункт 3.3\ зразок 3.3.8.xlsx). Визначте, яким клітинкам надано імена, а також область застосування кожного з них. Запишіть результати в зошит. Надайте імена клітинкам С1 і В5. Простежте, як змінилися формули в таблиці. Збережіть електронну книгу у вашій папці у файлі з іменем завдання 3.3.8.xlsx.

9*. Відкрийте вказаний учителем/учителькою файл (наприклад, Розділ ЗЇПункт 3.3\ зразок 3.3.9.xlsx), у якому наведено оцінки журі учасникам спортивних змагань (мал. 3.43). Виконайте розрахунки в таблиці. Збережіть електронну книгу у вашій папці у файлі з іменем завдання 3.3.9.xlsx.

10*. Виміряйте довжину, ширину та висоту кухні, вітальні й спальні вашої квартири. Створіть у зошиті математичну модель обчислення площі підлоги, площі стін та об’єму кожного із цих приміщень, а також загальної площі та загального об’єму всіх приміщень. Створіть електронну таблицю, заповніть таблицю виміряними даними. Уведіть формули та обчисліть результати в таблиці. Збережіть електронну книгу у вашій папці у файлі з іменем завдання 3.3.10.xlsx.

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

 

Це матеріал з підручника Інформатика за 7 клас Ривкінд 2020