§ 14. Типи посилань на комірки в табличному процесорі Excel

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

діапазони комірок

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

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

Розрізняють зв’язний і незв’язний діапазони.

Зв’язним є діапазон, який можна виділити переміщенням вказівника при натиснутій лівій кнопці миші.

Для виділення прямокутного діапазону потрібно протягнути вказівник між кутовими діагонально протилежними комірками діапазону або клацнути одну кутову комірку діапазону та при натиснутій клавіші Shift — діагонально протилежну комірку.

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

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

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

А1:С5 — прямокутний діапазон із 15-ти комірок (рис. 14.1, а);

2:4 — діапазон з усіх комірок рядків 2, 3 і 4;

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

В7 — діапазон з однієї комірки (рис. 14.1, б).

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

А1:С5;В7 — незв’язний діапазон із 16 комірок (рис 14.1, а, б).

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

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

Типи посилань

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

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

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

Посилання, яке містить тільки адреси комірок, називають відносним.

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

Якщо формулу =A4+B5 скопіювати до комірки, що міститься на два стовпці праворуч і три рядки вище від заданої, то в новій формулі всі заголовки стовпців збільшаться на 2, а номери рядків зменшаться на 3, і ми отримаємо формулу =C1+D2 (рис. 14.2).

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

Для копіювання формули до суміжних комірок можна виділити комірку з формулою та перетягнути маркер заповнення (рис. 14.3).

Отримані таким чином формули будуть модифікованими.

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

Якщо скопіювати до будь-якої комірки формулу =$А$1, вона не змінить свого вигляду і матиме посилання на комірку з адресою А1 (рис. 14.4).

Якщо в посиланні на комірку заголовок стовпця і номер рядка є різнотипними (абсолютним і відносним), тобто знак «$» міститься тільки перед заголовком стовпця або номером рядка, то таке посилання називають мішаним.

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

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

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

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

При переміщенні даних з адресної комірки А1 до комірки С3 всі посилання А1; $A$1; A$1; $A1 відповідно модифікуються таким чином: С3; $С$3; С$3; $С3 (рис. 14.5).

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

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

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

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

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

3. Як виділити діапазон стовпців або рядків?

4. Яке посилання називають відносним?

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

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

Вправа 14

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

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

2) Уведіть у комірку С3 формулу для розрахунку маси м’яса, потрібного для приготування борщу для 8-х класів. Установіть числовий формат даних з одним десятковим знаком після коми.

3) Уведіть у комірки D3, E3, F3 формули для розрахунку маси овочів для 8-х класів. Перетягуванням маркера заповнення скопіюйте відповідні формули до діапазонів С4:С6, D4:D6, Е4:Е6, F4:F6.

4) У комірку В7 введіть формулу для розрахунку загальної кількості порцій борщу для всіх класів.

5) Перетягуванням маркера заповнення скопіюйте формулу з комірки В7 до діапазону комірок C7:F7. У комірку G2 введіть текст «Маса продуктів, кг», а в комірку G3 — формулу для розрахунку загальної маси всіх продуктів для 8-х класів.

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

7) Збережіть файл з іменем Вправа 14 у відповідній папці. Завершіть роботу за комп’ютером.

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

Виконайте тестове завдання 14 з автоматичною перевіркою на сайті

interactive.ranok.com.ua.

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

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

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

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

Хід роботи

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

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

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

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

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

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

6. Уведіть у комірку Е4 формулу для розрахунку зарплати Петренка в євро.

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

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

9. У комірках D8, Е8 розрахуйте суму зарплати всіх працівників у гривнях і євро.

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

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

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