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

↑

▷ Содержание

⇐ ПредыдущаяСтр 2 из 5Следующая ⇒

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, у відкритому вікні вписати:

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

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

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

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

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

Варіант №1

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

Лист 1

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

Лист 2

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

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

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

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

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

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

Варіант №2

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

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

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

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

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

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

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

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

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

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

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

Варіант №3

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

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

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

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

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

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

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

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

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

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

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

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

Варіант №4

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

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

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

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

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

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

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

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

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

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

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

Варіант №5

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

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

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

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

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

Лист 1                

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

Лист 2

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

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

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

Варіант №6

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

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

Лист 2

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

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

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

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

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

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

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

Варіант №7

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

Лист 1

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

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

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

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

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

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

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

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

Лист 2

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

Сніданок

№ замовлення за чеком

Кількість

Вартість

…

….

*

До сплати

*

Обід

№ замовлення за чеком

Кількість

Вартість

…

….

*

До сплати

*

Вечеря

№ замовлення за чеком

Кількість

Вартість

…

….

*

До сплати

*

Шведський стіл

№ замовлення за чеком

Кількість

Вартість

…

….

*

До сплати

*