Приклад виконання практичного завдання

1. Створити таблицю «Вартість проживання у готелі».

Готель має різні категорії номерів. Інформація про ціни на готельні послуги розміщена на Листі 1:

 

На Листі 2 розмістить таблицю, що містить кількість зайнятих номерів на конкретну дату 11.08.2008.

 

2. Розрахуйте дохід готелю (загальний та за категоріями номерів) за 11.08.2008.

3. Відсортуйте вихідну таблицю за кількістю мешканців та категорією номеру.

4. Відфільтруйте дані з зайнятими номерами.

5. Встановить проміжні підсумки за кількістю мешканців у номерах.

6. Побудуйте на окремому листі об’ємний варіант гістограми, що відображує дохід готелю за категоріями номерів.

Виконання роботи:

№	Операція	Спосіб виконання
1.	У клітинках А1:D12 Листа1 створити таблицю 1	У клітинки А1:D12 внести назву таблиці і стовбців. Виділити клітинки А2:D2, для введення даних використовувати форму введення інформації Данные → Форма. Ввести дані за рядками.
2.	У клітинках А1:D12 Листа2 створити таблицю 2	У клітинки А1:D12 внести назву таблиці і стовбців. Виділити клітинки А2:C2, для введення даних використовувати форму введення інформації Данные → Форма. Ввести данні за рядками.
3.	На Листі 2 у клітинках А21:А22 вказати курс долара	У клітинку А21 вписати «Курс долара», у клітинку А22 внести значення поточного курсу (наприклад, 8,02)
4.	Розрахувати значення у клітинках діапазону D3:D12 (Лист 2)	Внести в клітинку D3 формулу =Лист1!D3*C3/$A$22 Enter $A$22 – абсолютне посилання на клітинку За допомогою маркера заповнення скопіювати формулу на решту клітинок
5.	Розрахувати підсумкову суму у клітинках D13	Внести у клітинку D13 формулу =СУММ(D3:D12) Enter
6.	Відсортувати дані таблиці на Листе 2 за кількістю занятих номерів  і категорією номеру	1. Виділити клітинки A2:D12 2. Виконати команду Данные → Сортировка 3. Встановити у списку Сортировать по значення Категория номера і напрям сортування По возрастанию
7.	Побудова діаграми	Перед тим, як будувати діаграму, необхідно підготувати підписи, які повинні мати вигляд як склеювання трьох рядків [номер поверху], «поверх –», [категорія номеру]. У клітинки F3 внести формулу: =СЦЕПИТЬ(A3;" поверх - ";B3) За допомогою маркера заповнення скопіювати формулу на решту чарунок.
		Побудова діаграми: 1. Виділити діапазони A2:A12, D2:D12 (використовувати Ctrl). 2. Запустити майстер побудови діаграм 3. Вибрати вид гістограми. 4. Перевірити установки Исходных данных 5. Встановити розміщення.
8.	За допомогою функції Автофильтр вибрати інформацію про зайняті номери	1. Виділити клітинки A2:D12 2. Виконати команду Данные→Фильтр→ Автофильтр 3. У списку Количество занятых номеров вибрати значення Условие 4. У списках задания условий встановити не равно 0
9.	Побудувати проміжні підсумки за кількістю мешканців	1. Виділити клітинки A2:D12 2. Виконати команду Данные →Итоги 3. Виконати Операция - Сумма 3. У списку Добавить итоги по вибрати Кількість зайнятих номерів та Дохід готеля

 

Приклади використання умовного форматування:

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

Примітка. При створенні умовного формату можна посилатися на інші клітинки на листі, наприклад =Лист1!A5, але зовнішні посилання на іншу книгу використовувати неможна.

Для виконання умовного форматування необхідно:

1) виділити клітинки, які повинні автоматично змінювати свій колір;

2) виберіть у меню Формат - Условное форматирование;

3) у відкритому вікні можна задати умови та, натиснувши кнопку Формат, параметри форматування клітинок, якщо умова виконується.

Існує таблиця:

Приклад 1. У даному прикладі клітинки зі значеннями більше 18 балів заливаються зеленим, від 12 до 18 - жовтим, а менше 12 - помаранчевим кольором:

Виконання: 1) виділити діапазон B3:E10; 2) у вікні умовного форматування вписати:

Кнопка А также>> дозволяє додати додаткові умови.

Якщо для діапазону клітинок заданий критерій умовного форматування, то більше не можна форматувати ці клітинки через меню Формат - Ячейки уручну. Щоб повернути собі цю можливість потрібно видалити умови у вікні Условное форматирование за допомогою кнопки Удалить.

 

Приклад 2.

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

Якщо формула є вірною (повертає значення ИСТИНА), то спрацьовує потрібний формат. В цьому випадку можна перевіряти одні клітинки, а форматувати інші.

Необхідно виділити кольором прізвище студента, якщо він набрав у сумі за 4 модуля більше 90 балів.

 

Знак долара ($) повинен ставитися перед літерою стовпця в адресі - він фіксує стовбець, оставляючи незафіксованим посилання на рядок.

 

Результат:

 

 

Значення, що перевіряються беруться зі стовпців B, C, D, E, по черезі з кожного наступного рядка.

Приклад 3. Виділити максимальні та мінімальні значення за модулями. Виділити діапазон B3:E10, у відкритому вікні вписати:

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

Прізвище студента	Модулі
	1-й	2-й	3-й	4-й
Іванов	25	24	23	24
Петров	18	13	16	25
Сидоров	6	18	18	20
Кузнєцов	9	19	12	21
Савин	14	24	14	16
Тарасов	24	20	17	19
Михайлов	23	20	16	22
Лукин	17	15	20	21

 

Приклад 4. Виділення рядків таблиці у вигляді «зебри».

Виділити клітинки таблиці (окрім "шапки"), відкрити меню Формат - Условное форматирование, вибрати у списку варіант Формула замість Значение та ввести таку формулу: =ОСТАТ(СТРОКА(A3);2)=0

Ця формула бере номер поточного рядка (функція СТРОКА, де A3 - перша клітинка виділення), розділює його на 2 та перевіряє залишок від ділення (функція ОСТАТ()). Якщо він дорівнює нулю, тобто номер рядка парний, то відбувається форматування клітинки (необхідно задати колір шрифту або заливку). Якщо необхідно залити не кожний 2-ий, а, наприклад, кожний 5-ий рядок, то потрібно змінити в цій формулі дільник 2 на 5.

Індивідуальні завдання для виконання лабораторної роботи № 2

Варіант №1

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

 

Лист 1

Вартість путівки за номер за добу, руб. 2011 рік

Двомісний стандартний. Графік заїздів 12 днів	Тип номеру	2 дорослих	дитина 3-6 років на додат. місці	дитина 7-11 років на додат. місці	дорослий на додат. місці
25.05 — 05.06	двомісний станд.	2067	619	723	1034
25.05 — 05.06	двомісний люкс	2500	630	750	1200
06.06 — 17.06	двомісний станд.	2167	652	760	1086
06.06 — 17.06	двомісний люкс	2550	642	768	1250
18.06 — 29.06	двомісний станд.	2712	815	949	1356
18.06 — 29.06	двомісний люкс	3000	900	1020	1460
30.06 — 30.06	двомісний станд.	2778	834	975	1389
30.06 — 30.06	двомісний люкс	…
01.07 — 11.07	двомісний станд.	2778	834	975	1389
01.07 — 11.07	двомісний люкс
12.07 — 23.07	двомісний станд.	3178	952	1112	1589
12.07 — 23.07	двомісний люкс
24.07 — 04.08	двомісний станд.	3178	952	1112	1589
24.07 — 04.08	двомісний люкс
05.08 — 16.08	двомісний станд.	3178	952	1112	1589
05.08 — 16.08	двомісний люкс
17.08 — 28.08	двомісний станд.	3178	952	1112	1589
17.08 — 28.08	двомісний люкс
29.08 — 09.09	двомісний станд.	3082	926	1078	1541
29.08 — 09.09	двомісний люкс
10.09 — 21.09	двомісний станд.	2993	897	1049	1497
10.09 — 21.09	двомісний люкс
22.09 — 03.10	двомісний станд.	2549	763	893	1275
22.09 — 03.10	двомісний люкс
04.10 — 13.10	двомісний станд.	2304	693	808	1152
04.10 — 13.10	двомісний люкс
14.10 — 31.10	двомісний станд.	2156	649	756	1078
14.10 — 31.10	двомісний люкс

Лист 2

курс грн.

??????????

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

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

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

Встановити проміжні підсумки за типом номеру.

Побудуйте гістограму, що відображає динаміку цін на путівки для 2 дорослих за типом номера.

До таблиці застосуйте умовне форматування «Зебра».

Варіант №2

 

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

 

Лист 1. Ціни на послуги

Послуги	Вартість послуги (разової/денної), $
	дорослі	діти
Готель	40	30
Екскурсія	15	12
Харчування	15	12
Трансфер	10	9

 

Лист 2. Курс долара

курс долара

??????????

 

Лист 3. Розрахунок вартості туру тривалістю 7 днів для групи відпочиваючих

Дата туру	Кількість осіб певної категорії		Вартість туру $	Загальна вартість (грн.)
	дорослі	діти
12/05/11	34	21	*	*
01/06/11			*	*
01/06/11			*	*
01/06/11			*	*
12/06/11			*	*
17/06/11			*	*
17/06/11			*	*
23/06/11			*	*
……			*	*
Всього	*	*	*	*

 

Використовуючи формули, на листі 3 розрахуйте вартість туру тривалістю 7 днів у доларах, за умови, що за цей час було відвідано 3 екскурсії.

Обчисліть загальну вартість туру в рублях для кожної категорії відпочиваючих, передбачивши можливість автоматичного перерахунку при зміні курсу долара.

Відсортувати таблицю на листі 3 за датами та вартістю туру.

Відфільтрувати замовлення з вартістю туру більше 2000 грн.

Установити проміжні підсумки за датами.

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

Застосуйте умовне форматування «Виділення максимальних та мінімальних значень».

 

 

Варіант №3

 

На початку червня 2007 р. турфірма займалася реалізацією турів за різними напрямами. Інформація про реалізовані путівки і курс валют зберігається на різних листах:

 

Лист 1. Продані путівки:

дата	найменування туру	ціна туру	скидка	ціна зі знижкою	ціна $	ціна євро
01.06.07	Вена	1200	*	*	*	*
02.06.07	Анталія	500	*	*	*	*
03.06.07	Хургада	600	*	*	*	*
04.06.07	Карлови Вари	800	*	*	*	*
05.06.07	Сонячний берег	1100	*	*	*	*
06.06.07	Стамбул	800	*	*	*	*
07.06.07	Прага	870	*	*	*	*
08.06.07	Каїр	750	*	*	*	*
…	…	…
Всього			*	*	*	*

 

Лист 2. Курс валют:

Євро	??
Долар	??

 

Для заповнення стовпця «Дата» скористайтеся автозаповненням.

З допомогою функції «Если» розрахуйте знижку в 5%, що автоматично надається на тур, за умови, що вартість туру не менш 1000 грн.

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

За допомогою функції «Автосумма» розрахуйте підсумковий прибуток турфірми за зазначений період в рублях, євро і доларах.

Відсортуйте таблицю на листі 1 за назвами турів.

За допомогою функції «Автофильтр» знайдіть всі тури, вартість яких більше або дорівнює 800 грн.

Встановить проміжні підсумки назвою туру.

Побудуйте графік доходу турфірми за зазначений період (в євро).

Застосуйте умовне форматування.

 

Варіант №4

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

 

Лист 1. Страхування клієнтів:

Дата	Прізвище клієнта	Страхова сума $	Тариф $/доба	Кількість днів	Вартість поліса (грн.)
12.09.2007	Васін В.В.	1000		7
13.09.2007	Котов К.К.	5000		9
14.09.2007	Орлов О.О.	1000		15
15.09.2007	Горін Г.Г.	10000		10
…	…	…	…	…	…

Лист 2. Курс долара

курс долара

??

 

Для заповнення стовпця «Дата» скористуйтеся автозаповненням.

З допомогою функції «Если» розрахуйте тариф, що стягується за один день поїздки, який б автоматично появлявся у четвертому стовпці при введені різних страхових сум. Умова: якщо страхова сума менше 1000 $, то тариф складає 0,1 $ за добу; інакше - 0.24 $.

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

Відсортуйте таблицю на листі 1 за прізвищем клієнта.

З допомогою функції «Автофильтр» знайдіть всіх клієнтів, страхова сума у яких більше або дорівнює 800 грн.

Встановить проміжні підсумки за клієнтами.

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

Застосуйте умовне форматування для виділення максимальних і мінімальних значень.

Варіант №5

Туристична компанія пропонує путівки в дитячі табори Криму. Інформація про бази відпочинку представлена у таблицях на окремих листах.

Розрахувати на окремому листі загальний прибуток від реалізації путівокв дитячі табори Криму, якщо процент комісії від продажів путівок турагентством складає 20%.

Відсортувати таблицю на листі 1 за оцінками (кількістю зірок).

З використанням функції «Автофильтр» відібрати ті табори, у яких є відкритий басейн, а вартість путівки не перевищує 4000 грн.

Встановить проміжні підсумки для кожної категорії табору (кількість зірок) середню вартість путівки.

 

Лист 1                

 

Дитячий відпочинок у Криму

Розташування	Назва	Наша оцінка	Період роботи	Наявність місць	Вартість  путівки	Кількість місць	Послуги
п. Заозерне	Дитячий табір "Какаду"	****	червень-серпень	Є	3000	560	Відкритий басейн, п’ятиразове харчування
п. Заозерне	"Чайка (дитячий табір)"	***	червень-серпень	Немає	4500	1200	п’ятиразове харчування
п. Песчане	Дитячий табір "Мандарин"	****	червень-серпень	Є	3200	598	Відкритий басейн, триразове харчування
п. Песчане	Дитячий табір "Чорноморська Атланта"	*****	червень-серпень	Є	8000	560	п’ятиразове харчування
м. Севастополь	Дитячий табір "Атлантика (7-10 років)"	***	червень-серпень	Є	3600	1000	Відкритий басейн, п’ятиразове харчування
м. Севастополь	Дитячий табір "Атлантик-Сіті (11-12 років)"	***	червень-серпень	Немає	3450	800	Відкритий басейн, п’ятиразове харчування
м. Севастополь	Дитячий табір "Атлантик Тревел (13-17 років)"	***	червень-серпень	Є	3650	600	Відкритий басейн, п’ятиразове харчування

Лист 2

 

Замовлення путівок у дитячі табори Криму

Назва	Зміна	Продано путівок
Дитячий табір "Какаду"	1	550
"Чайка (дитячий табір)"	1	1050
Дитячий табір "Какаду"	2	555
Дитячий табір "Мандарин"	…	…
…	…	...

Побудуйте на окремому листі об’ємний варіант гістограми, що відображає вартість путівок у дитячі табори м. Севастополь.

Застосуйте умовне форматування «Зебра».

Варіант №6

Екскурсійний кіоск пропонує такий набір турів по визначних місцях Криму. У таблиці на Листі 1 надані ціни на екскурсії у 20__ р. Таблиця на Листі 2 відображає поточний стан замовлення на деяку дату.

 

Лист 1 Ціни на екскурсії Кримом у 20__ році, грн

Екскурсії Кримом у 20__ році	Вартість для дорослих	Вартість для дітей
Керч	350	315
Судак	420	315
Новий Світ	490	330
Аквапарк Судак (ціни для відпочиваючих Пансіонату «Азовський»)	315	150
Феодосія прогулянка морем на теплоході	110	70
трансфер	350	315
Ялта	980	660
Верхові прогулянки Старим Кримом	350	320
трансфер	420	315
Коктебель	420	315
Топловський монастир	420	315
Казантип, екскурсія	70	50
трансфер	140	100
Дельфінарій	420	315
трансфер	315	210
Винний та коньячний завод у Коктебелі з дегустацією вин і коньяку
екскурсія	70
дегустація (8 видів вина та 1 вид коньяку)	170

 

Лист 2

Замовлення екскурсій на __/__/__р.

ПІБ клієнта	Назва екскурсії	Кількість дорослих	Кількість дітей
Іванов В.П.	Керч	2	0
Крюков З.В.	Судак	1	1
Вакулин Г.В.	Керч	2	1
…	…

 

Відсортувати таблицю на листі 2 за назвами екскурсій.

Встановить проміжні підсумки для кожної екскурсії - загальну кількість клієнтів.

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

З використанням функції «Автофильтр» відібрати ті екскурсії, на які придбано більше 50 квитків.

Побудуйте на окремому листі об’ємний варіант гістограми, що відображає прибуток фірми від реалізації екскурсій.

Для таблиці на Листі 2 застосуйте умовне форматування «Зебра».

Варіант №7

У сезоні 2011 року столова пансіонату «Сонячний беріг» пропонує відпочиваючим меню, представлене у таблиці на Листі 1.

 

Лист 1

Меню столової пансіонату

Найменування	Прийом їжі	Одиниця	Вихід гото-вих блюд	Ціна
Апельсин	Шведський стіл	кг	0,05	…
Борщ зелений	Обід	блюдо	0,35	…
Вино Каберне (розливне)	Вечеря	літр	0,05	…
Картопля фрі фуршетна	Шведський стіл	порція	0,05
Каша пшона молочна фуршетна	Шведський стіл	порція	0,15
Кефір	Вечеря	порція	0,2
Компот з сухофруктів	Обід	порція	0,2
Кофе	Сніданок	блюдо	0,2
Кукурудзяні хлоп’я з молоком	Шведський стіл	порція	0,18
Масло вершкове	Сніданок	кг	0,015
Нарізка з огірків і помідорів	Обід	блюдо	0,1
Овочева нарізка фуршетна	Шведський стіл	порція	0,05
Курка жарена	Вечеря	блюдо	0,1
Оладки з варенням	Шведський стіл	порція	0,055
Оладки з згущ. молоком	Шведський стіл	порція	0,055
Оладки зі сметаною	Шведський стіл	порція	0,055
Пиріг Шарлотка	Шведський стіл	порція	0,04
Пудинг манний з джемом	Сніданок	блюдо	0,25
Рагу овочеве	Обід	блюдо	0,3
Рис відварний	Вечеря	порція	0,2
Рисові шоколадні шарики з молоком	Шведський стіл	порція	0,18
Салат з капусти, огірка і майонезу	Вечеря	порція	0,15
Здоба	Сніданок	шт	1
Сік	Шведський стіл	літр	0,2
Сир Російський	Сніданок	кг	0,025
Хліб	Сніданок	кг	0,05
Хліб	Обід	кг	0,05
Хліб	Вечеря	кг	0,05
Чай фуршетний	Шведський стіл	порція	0,2
Яблуко	Вечеря	кг	0,05

 

Відсортувати вихідну таблицю (Лист1) за видом прийому їжі.

З використанням функції «Автофильтр» відібрати ті блюда, які представлені на шведському столі.

Встановити проміжні підсумки - для кожного прийому їжі розрахувати загальний вихід та вартість готових блюд.

На Листі 2 побудувати таблицю, що б мала таку структуру:

Розрахуйте вартість кожного прийому їжі.

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

Застосуйте умовне форматування «Зебра» до таблиці на Листі 1.

 

Лист 2

Замовлення __.__.__р.

Сніданок