Копіювання формул. Абсолюні та відносні посилання
За допомогою автозаповнення можна копіювати формули. Щоб скопіювати формулу потрібно встановити покажчик миші на маркер автозаповнення, натиснути праву кнопку миші й протягти покажчик вздовж рядка або стовпця до потрібної клітинки. Формула буде скопійована в сусідні клітинки, а адреси аргументів при копіюванні зміняться (рис. 8).
Рис. 8. Зміна посилань на клітинки при копіюванні формули з С1 в С3
В клітинці С1 записана формула =А1+В1. При копіюванні цієї формули в клітинки С2 і С3 посилання змінилися: в клітинці С2 записана формула =А2+В2, а в клітинці С3 записана формула = А3+В3.
Такі посилання, які змінюються при копіюванні формули називають відносними. А1 і В1 − це відносні посилання.
В Excel є ще один вид посилань на клітинки – це абсолютне посилання. Його записують так: $A$1. Таке посилання не змінюється при копіюванні формули в інші клітинки (рис. 9). Якщо в формулі використають абсолютне посилання, говорять, що «клітинку закріплено». Можна окремо закріпити тільки стовпець ($A1) або тільки рядок (A$1). Такі посилання називають змішаними.
Рис. 9. Абсолютне посилання не змінюється при копіюванні формули
Символ «$» в посиланні можна вставити з клавіатури або за допомогою клавіші <F4>. При повторному натисканні на клавішу <F4> позначка «$» послідовно з’являється або зникає перед літерою стовпця, або номером рядка.
Використання імен клітинок і діапазонів у формулах
Клітинці або діапазону клітинок в MS Excel можна приїти їм’я, яке можна використовувати в формулах як абсолютне посилання. В MS Excel існує декілька способів присвоєння імен окремим клітинкам або діапазонам.
Спосіб 1. Виділити клітинку або діапазон, якому треба прсвоїти ім’я; в рядку формул в полі Имя ввести ім’я, що починається з літери і не містить пропусків; завершити присвоєння імені натисканням клавіши <Enter> (рис 10).
Рис. 10. Присвоєння імені «ціна» клітинці В7 в полі Имя
Спосіб 2. Виділити клітинку або діапазон, якому треба прсвоїти ім’я. Виконати команду меню Вставка→Имя→Присвоить. В ділоговому вікні Присвоение имени ввести ім’я, натиснути кнопку <ОК> (рис. 11).
Рис. 11. Присвоєння імені через діалогове вікно
Створене ім’я можна використовувати в формулах як аргумент. Ввести ім’я в формулу можна з клавіатури або за допомогою команди Вставка→Имя→Применить.
Форматування даних
Форматування даних – це спосіб відображення даних, записаних в клітинках таблиці. Користувач має можливість встановити в кожній клітинці будь-який шрифт, колір фону клітинки (заливку), границі клітинки. Для представлення числових даних в MS Excel передбачено більше десятка вбудованих форматів і можливість створювати власні формати (так званий формат користувача). Для створення таблиць з різною кількістю рядків або стовпчитків існує можливість об’єднання кітнок. В об’єднаній клітинці залишаються дані тільки з першої (лівоі верхньої) клітинки об’єднуваного діапазону. Інші дані втрачаються.
Доступ до діалогового вікна Формат ячеек користувач має через меню Формат, команда Ячейки…(рис. 12, 13).
Рис. 12. Діалогове вікно Формат ячеек, вкладка Выравнивание
Рис. 13. Діалогове вікно Формат ячеек, вкладка Число
Завдання та приклади
Приклад 1. Обчислити витрати пального і його вартість при превезенні вантажів до різних міст України різними траспортними засобами.
Рішення
Створити на робочому аркуші довідкову таблицю як показано на рис. 14 з даними про норми витрат пального (літрів на 100 км шляху) для кожного виду транспортного засобу.
Рис.14. Довідкова таблиця для розрахунку витрат пального
Створити на тому ж робочому аркуші робочу таблицю в діапазоні А11: Н30 як показано на рис. 15 з даними про відстані між містами України і Харьковом. Об’єднати такі діапазони, А11:А13, В11:В13 та С11:Н113, С12:D12, E12:F12, G12:H12.
Рис. 15. Таблиця для розрахунків
Обчислити витрати пального для Ікаруса. Для цього в клітинку D14 записати таку формулу: =В14*$В$3/100 після завершення введення формули скопіювати її в діапазон С15:С30. В клітинці В3 записана норма витрати пального в літрах на 100 кілометрів для Ікаруса, це посинання має бути незмінним для всього діапазону С14:С30. Тому посилання на клітинку В3 є абсолютним – записаним за допомогою знака «$». Аналогічно обчислюється витрата пального для Мермедесу та ЛІАЗу.
Обчислити вартість витрат на пальне для кожного виду транспорту в залежності від відстані до міста. Для цього створити ще одну довідкову таблицю з ціною на пальне, як показано на рис. 16.
Рис. 16. Довідкова таблиця 3 для розрахунку витрат на пальне
Після створення довідкової табиці в клітинку D14 записати таку формулу: =В14*$В$7 після завершення введення формули скопіювати її в діапазон D15:D30.
Приклад 2. На основі вихідних даних (рис. 17) обчислити вартість кожного автомобіля з урахуванням транспортних витрат і наданої знижки а також загальну вартість всіх замовлень. Розв’язати задачу з використанням імен діапазонів.
Рис. 17. Вихідні дані для розрахунку вартості автомобілів
Рішення
Створити на робочому аркуші таблицю як показано на рис.17. Одним із описаних вище способів присвоїти наступним діапазонам такі імена: діапазону D2:D18 – price; діапазону Е2:Е18 – transport; діапазону F2:F18 – discont.
В клітинку G1 додати до таблиці ще один заголовок Вартість. В клітинку G2 ввести таку формулу = price*(1- discont/100)+ transport. Скопіювати формулу в діапазон G3: G18.
Виділити клітинку G18 і натиснути кнопку
на
панелі інструментів. Переконатися, що
в клітинку вміщена така формула:
=СУММ(G3:
G18).Виділяючи кожний діапазон з’ясувати встановлені формати даних.
Завдання для самостійного виконання
Вихідні дані для виконання даної лабораторної роботи знаходяться в файлі 1sem_1mod_1LR.xls, який розміщений за адресою, вказаною викладачем, і в таблиці 2.
Завдання 1
1.1. Для кожного стовпчика таблиці встановити такі формати:
стовпці А, G, H – формат Общий (Формат→Ячейки→Число);
стовпці В,С – формат Время (вибрати зразок формату hh:mm, тобто час повинен виглядати так: 13:20);
стовпець D – формат Денежный (без позначення одиниць);
стовпець E - формат Процентный;
стовпець F - формат Числовой, встановити число десяткових знаків рівним 0.
1.2. Встановити таку ширину стовпців (Формат → Столбец → Ширина): для стовпчика А − 20, для стовпчиків С і D − 14, для інших стовпчиків – автомаичний підбір ширини (Формат → Столбец → Автободбор ширины).
1.3. Для першого рядка таблиці встановити вирівнювання тексту по центру по вертикалі і по горизонталі, перенос слів в клітинці (Формат →Ячейки...→Выравнивание). Додати заливку будь-яким світлим тоном (Формат →Ячейки...→Вид).
1.4. Для всієї таблиці встановити шрифт Arial 9 пт, для першого рядка − напівжирне накреслення шрифту (Формат →Ячейки...→Шрифт).
1.5. Встановити для таблиці такі границі: зовнішні границі оформити суцільною жирною лінією, а внутрішні – суцільною тонкою лінією (Формат → Ячейки...→Граница).
1.6. Додати в таблицю ще один стовпець Дата найближчого рейсу. Для цього, виділити стовпець Авіакомпанія, що виконує рейс і виконати команду Вставка→Столбцы. Встановити для нього формат Дата (dd.mm.yy) і заповнити датами на підставі даних стовпця Дні здійснення рейсу. В ньому числом позначені дні тижня, коли рейс виконується, а зірочками – дні, коли рейсу немає. Вибирайте найближчу можливу дату. Стовпець Дні здійснення рейсу після цього видалити.
Таблиця 2