5.10. Копіювання формул. Використання відносної та абсолютної адресації

Копіювання формул в Excel відбувається тими ж способами, що й копіювання даних:

• за допомогою команд Копировать та Вставка пункту меню Правка;

• за допомогою кнопок Копировать та Вставка панелі інструментів Стандартная;

• за допомогою команд Копировать та Вставка контекстного меню, що відкривається при натисненні правої клавіші миші;

• з використанням функції Автозаповнення;

• за допомогою комбінації клавіш на клавіатурі (Ctrl + C – копіювати, Ctrl + V – вставити).

За замовчуванням усі посилання в формулах розглядаються як відносні. Це означає, що посилання, які використовуються в формулі, вказують системі, де знаходяться комірки, значення яких будуть підставлятися в формулу, відносно комірки, в якій знаходиться формула. Наприклад, розглянемо як працює відносна адресація при введенні в комірку А3 формули =А1+А2. На "комп'ютерній мові" це означає: "Взяти значення із комірки, що знаходиться на два рядки вище за мене, додати значення комірки, що знаходиться на рядок вище за мене і результат помістити в моїй комірці". Якщо скопіювати таку формулу із комірки А3 в комірку С3, то нова формула матиме вигляд =С1+С2, тому що Excel буде за аналогією брати значення із комірки, що знаходиться на два рядки вище, додавати значення комірки, що знаходиться на рядок вище і результат поміщати у поточну комірку. Зручність використання відносних посилань стає очевидною при копіюванні формул (згадайте вправу №12).

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

Для того щоб змінити відносне посилання на абсолютне, треба:

• клацнути на комірці, що містить формулу і в якій потрібно виконати заміну типу посилання;

• перейти в режим редагування формул;

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

• натиснути клавішу F4.

Для того щоб зробити абсолютною частину адреса комірки, що належить тільки стовпцю або тільки рядку, слід натиснути клавішу F4 знов. При кожному натисканні клавіші F4 знак $ буде мігрувати, наприклад після натискання клавіші F4 адреса $А$1 перетвориться на А$1, а після наступного натиснення – в $А1, потім – в А1 і знов – на адрес $А$1.

Формули можна не тільки копіювати, але й переміщувати. Для цього замість команди Копировать, потрібно застосовувати команду Вырезать.

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

Рис. 5.13. Використання абсолютної адресації

Як можна побачити на рис.5.13, активною коміркою є комірка І7, де введена формула розрахунку ціни одиниці товару залежно від поточного курсу USD, що в свою чергу введено в комірку В3. Якщо при введенні формули в комірку І7 використовувати відносну адресацію (формула матиме такий вигляд =В3*Н7 ), а потім скопіювати її в комірки І8:І19, то результат обчислення в цих комірках буде дорівнювати нулю, тобто формула працює невірно. Зробивши активною комірку І8, в рядку формул можна побачити, що формула має вигляд =В4*Н8, в комірці І9 – =В5*Н9 і т.д. У нашому прикладі необхідно, щоб посилання на комірку В3 не змінювалось при копіюванні формули. Для цього й використовується абсолютна адресація. В кінцевому варіанті формула в комірці І7 повинна мати вигляд =$B$3*H7 .

Вправи:

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

• Зробити активною комірку В2 і ввести текст: Месяца

• Зробити активною комірку С2 і ввести текст: Доходы

• Зробити активною комірку D2 і ввести текст: Доходы с нарастающим итогом. При цьому текст, що вводиться, розповсюджується на комірки Е2 та F2 (рис.5.14).

Р ис. 5.14. Фрагмент виконання вправи №14

• Зробити активною комірку В3 і ввести текст: Январь 2002. Натиснути клавішу Enter і переконатися в тому, що введений текст був автоматично розпізнаний як дата (змінився формат введених даних і замість Январь 2002 з'явилися такі дані: янв.02 ).

• Зробити активною комірку В3 і, використовуючи маркер Автозаповнення, заповнити комірки В4:В14 місяцями року.

• Зробити активною комірку С3 і ввести число, що буде відповідати доходу за січень, припустимо число 200.

• Зробити активною комірку С4 і, оскільки з умови завдання виходить, що доходи повинні зрости на 10%, слід ввести формулу =С3*1,1. Коефіцієнт 1,1 як раз і показує, що дані із комірки С3 збільшуються на десять відсотків.

• Використовуючи маркер Автозаповнення, скопіювати цю формулу до комірки С14.

• Виділити діапазон комірок С3:С14 та відкрити діалогове вікно Формат ячеек (за допомогою пункту меню Формат>Ячейки, за допомогою контекстного меню, що відкривається при натисненні правої клавіші миші або за допомогою комбінації клавіш на клавіатурі Ctrl+1).

• Вибрати на вкладці Число числовий формат Денежный. В полі Число десятичных знаков поставити 2, а у полі Обозначение – грн. Украинский і натиснути кнопку ОК.

• Зробити активною комірку D3. У діапазоні комірок D3:D14 повинні відображатися дані доходів із наростаючим підсумком. Це означає, що в комірці D3 будуть відображені дані доходів за січень, в комірці D4 – за січень і лютий, у D5 – за січень, лютий і березень. Тому будемо застосовувати формули. В комірці D3 ввести =С3.

• Зробити активною комірку D4 і ввести формулу =D3+С4.

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

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

• виділити стовпці В, С і D;

• вибрати команду Формат>Столбец>Ширина;

• у віконці Ширина столбца, що відкриється, ввести число 15 та натиснути кнопку ОК.

• Змінити висоту рядків від 3-го до 14-го включно. Для цього треба:

• виділити рядки від 3-го до 14-го;

• вибрати команду Формат>Строка>Высота;

• у віконці Высота строки, що відкриється, ввести число 20 та натиснути кнопку ОК.

• Виділити діапазон комірок В2:D2. Викликати діалогове вікно Формат ячеек.

• вибрати вкладку Выравнивание. У полях по горизонтали: і по вертикали: вибрати позицію по центру. У полі Отображение встановити прапорець переносить по словам;

• вибрати вкладку Шрифт. Вибрати шрифт Times New Roman, Начертание – полужирный, Размер – 12;

• вибрати вкладку Граница. У полі тип линии: вибрати товсту лінію та клацнути по кнопках внешние і внутренние;

• натиснути кнопку ОК.

• Для комірок В3:В14 встановити шрифт Times New Roman, Начертание – полужирный курсив, Размер – 12 та вирівняти дані посередині комірок.

• Для комірок С3:D14 встановити шрифт Times New Roman, Начертание – обычный, Размер – 12 та вирівняти дані посередині комірок.

• Для комірок В3:D14 за допомогою діалогового вікна Формат ячеек вкладки Граница встановити обрамлення комірок подвійною лінією.

• Тепер для того щоб сітка робочого аркушу не заважала сприйняттю створеної таблиці її можна убрати. Для цього необхідно виконати команду Сервис>Параметры і у вікні Параметры на вкладці Вид убрати прапорець Сетка та натиснути кнопку ОК.

• Перейменувати робочий аркуш Лист1 в Мои доходы. Для цього треба клацнути правою клавішею миші по ярличку Лист1, вибрати пункт меню Переименовать та ввести нове ім'я.

• Зберегти результати виконання цих вправ на магнітному носії під ім'ям Вправа №14.

Р ис. 5.15. Результат виконання вправи №14

Якщо все виконано правильно, то результат повинен бути таким як на рис. 5.15. Тепер, у разі зміни даних тільки в комірці С3, усі інші комірки будуть перераховуватись автоматично.

Вправи:

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

    Месяцы	Доходы		Расходы		Остаток на счету
    	2001	2002	2001	2002	2001	2002
    Январь
    Февраль
    …..
    Декабрь
    Итого:

16. Самостійно створити таблицю розрахунків динаміки загальної злочинності. Необхідно розрахувати динаміку в абсолютних одиницях та у відсотках. Також зробити форматування та обрамлення усіх даних. Початковими даними є тільки дані зареєстрованих злочинів по місяцях. При форматуванні даних таблиці слід скористатися кнопкою Объединить и поместить в центре на панелі інструментів Форматирование.

	Зареєстровано злочинів у звітному місяці		Динаміка		Зареєстровано злочинів з початку року (з наростаючим підсумком)		Динаміка
	2001	2002	абс.	%	2001	2002	абс.	%
Січень	42852	41395
Лютий	51610	50957
Березень	56848	55262
Квітень	50229	48290
Травень	48382	45933
Червень	51519	48896
Липень	49114	45296
Серпень	46298	44952
Вересень	48526	48319
Жовтень	48100	44149
Листопад	47504	41917
Грудень	56654	57185

Виконуючи вправу №16, слід враховувати, що динаміка в абсолютних одиницях – це різниця між даними 2002 року і 2001 року. Якщо ця різниця є негативною величиною, то це означає, що загальна злочинність зменшується, якщо позитивною величиною – злочинність збільшується.

17. Самостійно створити таблицю розрахунків динаміки загальної злочинності за видами злочинів. Необхідно розрахувати динаміку в абсолютних одиницях та у відсотках. Також зробити форматування та обрамлення усіх даних. Початковими даними є тільки дані зареєстрованих злочинів за видами злочинів. При форматуванні даних таблиці слід скористатися кнопкою Объединить и поместить в центре на панелі інструментів Форматирование.

Види злочинів		Всього		Динаміка
		1999	2000	В абс.	В %
Розбій		5173	5636
Тяжке тілесне ушкодження		7047	6852
Із застосу-ванням	Вогнепальної зброї	29	33
	Бойових припасів та вибухових речовин	1	3
Крадіжка державного або колективного майна		82768	94608
З них	Зі складів, баз, магазинів та інших торгових точок	14969	11337
	Вантажів на транспорті	1614	1890