Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:
18 лаб роб Відносні та абсолютні посилання.doc
Скачиваний:
3
Добавлен:
04.12.2018
Размер:
313.86 Кб
Скачать

Лабораторна робота №18 (2 год.)

Тема: Відносні та абсолютні посилання на чарунки.

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

Порядок виконання роботи

Завдання. Підготувати комп’ютер до виконання лабораторної роботи.

Послідовність дій при виконанні завдання:

  1. Перевірити завантаженість ОС Windows ХР на вашому комп’ютері.

  2. Відкрити папку Work та переконатися, що в ній існує папка з назвою вашої групи. Якщо папка з назвою вашої групи відсутня, створіть дану папку. (Файл - Создать►- Папку)

  3. Скопіюйте в вашу папку з папки Excel 1, що зареєстрована в папці Student файли Ехсеl_10, Ехсеl_11, Ехсеl_12, Ехсеl_13, Ехсеl_14, Ехсеl_15, Ехсеl_16.

  4. Увага всю подальшу роботу виконувати тільки з файлами вашої папки!!!

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

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

Стиль посилань А1

За замовчуванням Excel використовує стиль посилань названий відносним  А1, що визначає стовпці літерами (від А до IV, усього не більш 256 стовпців), а рядки номерами (від 1 до 65536). Ці літери і номери називаються заголовками рядків і стовпців. Для посилання на комірки введіть літеру стовпця, а слідом номер рядка. Наприклад, посилання D50 указує на комірку, розташовану на перетинанні стовпця D і рядка 50. Для посилання на діапазон комірок введіть адресу комірки, що знаходиться в лівому верхньому кутку діапазону, двокрапка (:), а потім адресу комірки, що знаходиться в правому нижньому кутку діапазону. Нижче наведені приклади посилань.

Для вказівки посилання на...

Введіть

Комірку у стовпці А і рядку 10

А10

Діапазон комірок у стовпці А и рядках з 10 по 20

А10:А20

Діапазон комірок у рядку 15 і стовпцях з В по Е

В15:Е15

Всі комірки в рядку 5

5:5

Всі комірки в рядках з 5 по 10

5:10

Всі комірки в стовпці Н

Н:Н

Всі комірки в стовпцях з Н по J

H:J

Діапазон комірок у стовпцях з А по Е і рядках з 10 по 20

А10:Е20

Відносні й абсолютні посилання

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

Відносне посилання - це така форма запису адреси комірки у формулі, що при копіюванні змінюються. Приклади запису відносних посилань: А1, С72 і т. д.

Наприклад, формула в комірці В4 перемножує вміст комірок А5 і С1 (=А5*С1) і дає результат 6. При копіюванні цієї формули в комірку D5 змінюються обидва посилання, формула набере вигляду = С6*Е2 і видасть результат 20 (див. малюнок).

Абсолютне посилання - це така форма запису адреси комірок у формулі, що при копіюванні не змінюється адреса комірки. Щоб встановити абсолютну форму запису потрібно дописати перед ім'ям стовпця або номером рядка знак долара ($). Приклади запису абсолютних посилань: $А$1 (цілком абсолютне посилання на комірку А1), $D4 (напівабсолютне посилання на комірку D4, при копіюванні не буде змінюватися літера стовпця), V$2 (напівабсолютне посилання на комірку V2, при копіюванні не буде змінюватися номер рядка).

Наприклад, якщо потрібно в комірку В4 ввести формулу, що множить вміст комірок А5 і С1, причому адреса комірки А5 при копіюванні не повинна змінюватися, вона повинна мати вигляд =$A$5*C1. При копіюванні формули в інші комірки буде змінюватися тільки посилання C1 (див. малюнок).

Висновок: Якщо необхідно, щоб посилання не змінювалися при копіюванні формули в іншу комірку, скористайтеся абсолютними посиланнями. Наприклад, якщо є формула, що множить вміст комірки А5 на комірку С1 (=А5*С1), то при копіюванні формули в іншу комірку зміняться обидва посилання. Для створення абсолютного посилання на комірку С1, поставте знак долара ($) перед тією частиною посилання, що не повинна змінюватися. Щоб створити абсолютне посилання на комірку С1, помістіть знак долара так, як показано в прикладі: =А5*$С$1.

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

Пояснення: Кожне натискання клавіші F4 переключає тип посилання в наступній послідовності: якщо у формулі виділити посилання, що має вигляд С1 натиснути F4  формула прийме вигляд $С$1 (абсолютний стовпець і абсолютний рядок)  натиснути F4  формула прийме вигляд $С1 (абсолютний стовпець і відносний рядок)  натиснути F4  формула прийме вигляд С$1 (відносний стовпець і абсолютний рядок)  натиснути F4  формула повернеться до початкового вигляду С1 (відносний стовпець і відносний рядок).

Завдання 1.Навчитися робити обчислення з застосуванням абсолютних посилань.

Послідовність дій при виконанні завдання:

  1. Відкрити файл Ехсеl_10.

  2. Відкрити робочий лист Приклад.

  3. Заповнити порожні комірки в шести таблицях.

Пояснення:

Розрахунки в табл.№1 - табл.№4 прості і не вимагають додаткових коментарів.

Пояснення до розрахунків у табл. №5.

У блоці комірок N37:N41 потрібно підрахувати відсотки. Бажано зробити це таким чином: в комірку N37 ввести формулу і, використовуючи маркер розмноження, розмножити її в комірку N38:N41. Якщо в комірку N37 ввести формулу =М37/М41 і розмножити її на блок комірок N38:N41 без деякого необхідного корегування, то розмножені формули і результати обчислення за ними будуть мати вигляд, як показано на малюнку:

Усі формули, отримані в результаті розмноження, будуть видавати значення #ДЕЛ/0!. Повідомлення #ДЕЛ/0! видається в тому випадку, якщо в формулі намагаються розділити число на 0. Дійсно, при розмноженні формули =М37/М41, записаної в комірку N37, зміняться обидві адреси. Нас цікавить зміна адреси комірки записаної в знаменнику: М41 змінитися на М42, М43, М44, М45. У цих комірках значення дорівнюють 0, тому результат обчислення за формулами видає значення #ДЕЛ/0!. Для перегляду формул записаних в комірки N38:N41 включіть режим відображення формул в комірках таблиці (Сервис – Параметры… - вкладка Вид – включити режим Формулы).

За умовою задачі всі відсотки повинні братися від числа, що міститься в комірці М41, тобто адреса цієї комірки при розмноженні змінюватися не повинна. Для цього в підсумковій формулі, записаній в комірку N37, адреса М41 повинна бути записана як абсолютна, формула повинна мати вигляд =М37/$М$41. У цьому випадку при розмноженні формули на блок комірок N38:N41 формули і результати обчислення за цими формулами будуть мати вигляд, як показано на малюнку:

Завдання 2. Розібрати рішення практичної задачі.

Послідовність дій при виконанні завдання:

  1. Відкрити робочий лист Виторг.

  2. Виділити блок комірок Е8:К22.

  3. Клацнути по символу (Автосумма) на панелі інструментів Стандартная.

  4. У блоці комірок К8:К22 з’являться результати підсумовування за всіма рядками.

  5. Зробити активною комірку К23.

  6. Клацнути по символу (Автосумма).

  7. В комірці К23 з’явиться сума виторгу п'ятнадцяти магазинів за шість місяців (380 850 грн.)

  8. Зробити активною комірку L8.

  9. При виконанні подальших дій необхідно керуватися коментарями, що надані до Завдання 1.

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

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

  1. У перший блок комірок вводять дані про ціни в у.о.

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

  3. Створюють другий блок комірок для введення формул, що зв'язують комірки, підготовлені в п.1 і п.2

  4. В одну з комірок другого блоку (звичайно у верхню або ліву) вводять формулу.

  5. У цій формулі, у потрібних місцях, розставляють знаки абсолютного посилання.

  6. Розмножують формулу на весь другий блок комірок.

Послідовність дій при виконанні завдання 3:

  1. Зробити активною комірку F9.

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

  3. Клацнути мишкою по комірці Е9.

  4. Ввести знак множення.

  5. Клацнути мишкою по комірці Е6 та натиснути Enter.

  6. В комірці F9 з'явиться результат 23,49 , а в рядку формул вигляд формули =Е9*Е6.

  7. Клацнути мишкою в рядку формул і перетворити формулу до вигляду =Е9*$Е$6 - цим накладається заборона на зміну адреси Е6 при розмноженні формули. Зроблено це тому, що кожне зі значень ціни в у.о. повинно бути помножене на вміст комірки Е6.

  8. Розмножити формулу на блок комірок F10:F20.

Результат розрахунку:

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

Завдання 4. Відкрити робочий лист ПДВ. Заповнити порожні клітки таблиці результатами обчислень.

Послідовність дій при виконанні завдання:

  1. Заповнення блоку комірок G5:G11 виконується методом, описаним в Завданні 3.

  2. В комірку Н5 вводиться формула =E5*G5, по якій буде підраховуватися вартість усіх принтерів марки Stylus Color 680.

  3. Формула з комірки Н5 розмножується на блок комірок Н6:Н11 для розрахунку вартості принтерів кожного типу.

  4. В комірці Н12 підраховується загальна вартість усіх принтерів (команда Автосумма).

  5. В комірці Н13 підраховується величина податку на додану вартість (ПДВ), що складає 20% від загальної вартості принтерів. Величина відсотка ПДВ введена в G13.

  6. В комірку Н13 введемо формулу =H12*G13.

  7. У комірці Н14 підраховується вартість усіх товарів з урахуванням ПДВ шляхом введення формули підсумовування вмісту двох комірок Н12 і Н13.

Завдання 5. Відкрити робочий лист Порівняння. Визначити при різних значеннях курсу долара і розміру ПДВ вартість усіх принтерів.

Запропоноване завдання є ілюстрацією використання заздалегідь підготовленої розрахункової таблиці для повсякденної роботи користувача. Щоб одержати відповідь на поставлене питання, досить у двох комірках розрахункової таблиці на листі ПДВ вводити надані значення курсу валюти і величини ПДВ, щоб в комірці Н14 одержувати результат.

Послідовність дій при виконанні завдання 5:

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

Курс$

5,55

ПДВ

20%

Для переносу результату на лист Порівняння потрібно:

  1. Відкрити лист ПДВ та скопіювати вміст комірки Н14.

  2. Відкрити лист Порівняння.

  3. Зробити активною комірку Е15.

  4. Викликати діалогове вікно команди Специальная вставка.

  5. Включити опцію Значения, ОК.

Розрахунок для другого варіанта при наступних вихідних даних проводиться таким способом:

Курс$

5,30

ПДВ

15%

  1. На листі ПДВ ввести: в комірку В5 значення 5,30, в комірку G13 значення 15%.

  2. Результат з комірки Н14 перенести на лист Порівняння в комірку F15.

Аналогічним чином підрахувати значення для третього, четвертого і п'ятого варіантів.

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]