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

Виділення діапазонів клітинок

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

Діапазон — це сукупність клітинок, які можна опрацьовувати як єдине ціле.

Залежно від способу задання, розрізняють два види діапазонів:

зв’язні і незв’язні.

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

Наприклад, на рис. 15.1 А1:С5 — прямокутний діапазон;

А:В — діапазон з усіх клітинок стовпців А і В;

2:4 — діапазон з усіх клітинок рядків 2, 3 і 4.

Для виділення зв’язного прямокутного діапазону потрібно при натиснутій лівій кнопці миші протягти вказівник від однієї кутової клітинки діапазону до діагонально протилежної (незалежно від напрямку руху вказівника).

Для виділення зв’язного діапазону стовпців або рядків потрібно протягнути вказівник з натиснутою лівою кнопкою миші по заголовках стовпців або рядків.

Незв’язним називається діапазон, який складається з кількох зв’язних діапазонів.

Щоб виділити незв’язний діапазон, потрібно, утримуючи натис-нутою клавішу Ctrl, послідовно виділити декілька зв’язних діапазонів. Незв’язний діапазон позначають посиланнями на окремі його частини, відокремлюючи їх крапкою з комою: А1:С5;В7 (див. рис. 15.1).

Для скасування виділень слід клацнути на будь-якій клітинці.

Зв’язний діапазон можна копіювати і переміщувати. Для незв’язних діапазонів такі операції не завжди можливі.

Посилання на адреси комірок

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

Клітинки, на які у формулах зроблено посилання, називатимемо адресними.

Розрізняють три типи посилань: відносні, абсолютні, мішані.

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

Якщо формулу =A4+B5 скопіювати в клітинку, яка знаходиться на два стовпці праворуч і на три рядки вище, то в новій формулі всі значення номерів стовпців збільшаться на два, а номерів рядків зменшаться на три, і отримаємо формулу: =G+D2 (рис. 15.2).

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

Копіювання формул у клітинки можна виконати за допомогою контекстного меню комірки або сполучень клавіш Ctrl + С, Ctrl+V. Копіювання формул до суміжних клітинок можна здійснити перетягуванням маркера клітинки з формулою (рис. 15.3). При цьому модифікована формула копіюється до всіх клітинок діапазону.

Якщо у формулах як незмінні коефіцієнти застосовуються посилання на клітинки зі значенням цих коефіцієнтів, то посилання на такі клітинки не повинні змінюватись. У цьому випадку використовують абсолютні посилання, які не змінюються під час копіювання формул в інші клітинки. Для уведення абсолютних посилань використовують знак $. Якщо в будь-яку клітинку скопіювати формулу =$А$1, вона не змінить свого вигляду і вказуватиме на клітинку з адресою А1 (рис. 15.4).

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

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

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

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

Так, під час переміщення клітинки А1 на місце клітинки С3 формули

модифікуються на

(рис. 15.5).

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

Видалення адресних клітинок призводить до помилок у формулах, які посилались на видалені клітинки (рис. 15.6).

Питання для самоперевірки

1. Як виділити прямокутний діапазон клітинок?

2. Скільки клітинок містить зв’язний діапазон А2:В3?

3. Як виділити незв’язний діапазон (несуміжні клітинки)?

4. Що таке відносне посилання?

5. Як записують абсолютні посилання?

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

Вправа 15

Розрахувати масу продуктів для приготування борщу у шкільній їдальні. Маса продукту розраховується як добуток норми його закладки на кількість порцій.

1) Запустіть програму Excel. Заповніть таблицю за зразком (рис. 15.7).

2) Уведіть у клітинку С3 формулу розрахунку маси м’яса для учнів і учениць восьмих класів.

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

3) Уведіть у клітинки D3; E3; F3 формули розрахунку маси інших продуктів харчування для учнів і учениць восьмих класів. Перетягуванням маркера скопіюйте усі формули для восьмих класів у вертикальні діапазони клітинок для інших класів.

4) Використовуючи функцію SUM, створіть формулу підрахунку загальної кількості порцій для учнів усіх класів у клітинці В7.

5) Перетягуванням маркера клітинки В7 скопіюйте формулу в клітинки горизонтального діапазону C7:F7.

У клітинку G2 впишіть Маса продуктів (кг), а в клітинку G3 уведіть формулу для розрахунку загальної маси продуктів (м’яса, картоплі, капусти, буряка) для учнів восьмих класів.

6) У клітинках G4:G6 розрахуйте загальну масу продуктів для решти класів.

Збережіть файл з іменем Вправа 15 у папці за вказівкою вчителя. Заверште роботу за комп’ютером.

Комп’ютерне тестування

Виконайте тестове завдання 15 з автоматичною перевіркою результату.

Практична робота 6

Розв’язування задач на обчислення

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

Обладнання: комп’ютер зі встановленим табличним процесором Excel.

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

1. Запустіть програму Excel. Заповніть початкову таблицю розрахунку зарплат за зразком (окрім клітинок Е2; В8; С4:Е8).

2. Знайдіть в інтернеті курси валют і заповніть клітинку E2.

3. У клітинці D4 за формулою розрахуйте зарплату Петренка у гривнях.

Приклад розрахунку: «Оклад» — це заробіток працівника за дні, вказані у клітинці В2. Петренко заробляє 5900/22 = 268,18 грн на день. Оскільки він відпрацював 20 днів, то його зарплата становитиме: 268,18 • 20 = 5363,6 (грн).

4. Установіть для усіх розрахункових клітинок відповідний грошовий формат із двома десятковими знаками після коми.

5. Скопіюйте формулу зарплати в гривнях для решти працівників.

6. За допомогою формули розрахуйте зарплату Петренка в євро.

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

8. Скориставшись функцією, розрахуйте загальну кількість днів, які відпрацювали робітники, у клітинці В8.

9. Скориставшись функцією, розрахуйте середній оклад у клітинці С8.

10. Копіюванням формул розрахуйте загальний заробіток усіх працівників у гривнях та євро (клітинки D8:E8).

11. Збережіть файл з іменем Практична робота 8 у папці за вказівкою вчителя.

12. Заверште роботу за комп’ютером.

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

 

 

Це матеріал з підручника Інформатика 9 клас Бондаренко 2022