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

Клітинці або діапазону клітинок в MS Excel можна присвоїти ім’я, яке можна використовувати в формулах як абсолютне посилання. В MS Excel існує декілька способів присвоєння імен окремим клітинкам або діапазонам.

Спосіб 1. Виділити клітинку або діапазон, якому треба присвоїти ім’я; в рядку формул в полі Имя ввести ім’я, що починається з літери і не містить пропусків; завершити присвоєння імені натисканням клавіші < Enter > (рис 10).

Рис. 10. Присвоєння імені «ціна» клітинці В7 в полі Имя

Спосіб 2. Виділити клітинку або діапазон, якому треба присвоїти ім’я. Натиснути праву кнопку миші та з меню обрати команду Присвоить имя …. В діалоговому вікні Создание имени ввести ім’я, натиснути кнопку < ОК > (рис. 11).

Рис. 11. Присвоєння імені через діалогове вікно

Створене ім’я можна використовувати в формулах як аргумент.

Форматування даних

Форматування даних – це спосіб відображення даних, записаних в клітинках таблиці. Користувач має можливість встановити в кожній клітинці будь-який шрифт, колір фону клітинки (заливку), границі клітинки. Для представлення числових даних в MS Excel передбачено більше десятка вбудованих форматів і можливість створювати власні формати (так званий формат користувача). Для створення таблиць з різною кількістю рядків або стовпчиків існує можливість об’єднання клітинок. В об’єднаній клітинці залишаються дані тільки з першої (лівої верхньої) клітинки об’єднуваного діапазону. Інші дані втрачаються.

Доступ до діалогового вікна Формат клітинок користувач має через меню Формат, команда Ячейки … (рис. 12 та 13).

Рис. 12. Діалогове вікно Формат ячеек, вкладка Выравнивание

Рис. 13. Діалогове вікно Формат ячеек, вкладка Число

Завдання та приклади

Приклад 1. Обчислити витрати пального та його вартість при перевезенні пасажирів від міста Харків до інших міст України автобусами різних марок.

Рішення

1. Створити на робочому аркуші довідкову таблицю як показано на рис. 14 з даними про норми витрат пального (літрів на 100 км шляху) для кожного виду транспортного засобу.

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

2. Створити на тому ж робочому аркуші робочу таблицю в діапазоні А11: Н30 як показано на рис. 15 з даними про відстані між містами України та Харковом. Об ’ єднати такі діапазони, А11:А13, В11:В13 та С11:Н11, С12:D12, E12:F12, G12:H12.

Рис. 15. Таблиця для розрахунків

3. Обчислити витрати пального для автобусу марки Ікарус. Для цього в клітинку C14 записати таку формулу: =В14*$В$3/100 після завершення введення формули скопіювати її в діапазон С15:С30. В клітинці В3 записана норма витрати пального в літрах на 100 кілометрів для Ікарусу, це посилання має бути незмінним для всього діапазону С14:С30. Тому посилання на клітинку В3 є абсолютним – записаним за допомогою знака «$». Аналогічно обчислюється витрата пального для автобусів марки Мерседес та ЛІАЗ.

4. Обчислити вартість витрат на пальне для кожного виду транспорту в залежності від відстані до міста. Для цього створити ще одну довідкову таблицю з ціною на пальне, як показано на рис. 16.

Рис. 16. Довідкова таблиця 3 для розрахунку витрат на пальне

Після створення довідкової таблиці в клітинку D14 записати таку формулу: =C14*$В$7 після завершення введення формули скопіювати її в діапазон D15:D30.

Приклад 2. На основі вихідних даних (рис. 17) обчислити вартість кожного автомобіля з урахуванням транспортних витрат і наданої знижки, а також загальну вартість всіх замовлень. Розв’язати задачу з використанням імен діапазонів.

Рис. 17. Вихідні дані для розрахунку вартості автомобілів

Рішення

1. Створити на робочому аркуші таблицю як показано на рис.17. Одним із описаних вище способів присвоїти наступним діапазонам такі імена: діапазону D2:D18 – price; діапазону Е2:Е18 – transport; діапазону F2:F18 – discount.

2. В клітинку G1 додати до таблиці ще один заголовок Вартість. В клітинку G2 ввести таку формулу = price*(1-discount/100)+transport. Скопіювати формулу в діапазон G3:G18.

3. Виділити клітинку G19 і натиснути кнопку на панелі інструментів. Переконатися, що в клітинку вміщена така формула: =СУММ(G2:G18).

4. Виділяючи кожний діапазон з’ясувати встановлені формати даних.

Завдання для самостійного виконання

Вихідні дані для виконання даної лабораторної роботи знаходяться в файлі 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) і заповнити датами на підставі даних стовпця Дні здійснення рейсу. В ньому числом позначені дні тижня, коли рейс виконується, а зірочками – дні, коли рейсу немає. Вибирайте найближчу можливу дату. Стовпець Дні здійснення рейсу після цього видалити.

1.6. Додати в книгу новий аркуш, назвати його Автоформат. Скопіювати на нього таблицю. Застосувати автоформат Средний 5 (Формат → Автоформат).

Таблиця 2