На листе Фильтры с помощью расширенного фильтра выведите всех сотрудников 1 отдела, имеющих разряд выше 13-го в таблицу с заголовками: Фамилия, Разряд, Зарплата.

8. С помощью расширенного фильтра выберите экономистов, получивших зарплату больше 12000 в таблицу Фамилия, Отдел, Разряд, Отработано часов, Зарплата.

9. Создайте сводную таблицу для анализа общей суммы заработной платы и отработанного времени по всем отделам и разрядам.

10. Создайте сводную таблицу для анализа средней, максимальной и минимальной заработной платы по должностям.

11. Создайте сводную таблицу для анализа средней оплаты за час по отделам и образованию.

Практическая работа № 15

Создание базы данных «Отель»

(использование EXCEL для создания базы данных;

функции ВПР, ЕОШИБКА)

1. Переименуйте: Лист1- БД Отель;Лист2 - Вспомогательные таблицы;

Лист3 – Архив.

2. На листе «Вспомогательные таблицы» создайте следующие таблицы, учитывая соглашения:

· в графы, не выделенные серым цветом, введите исходные данные;

· диапазону ячеек A2:A5 присвойте имя Типы_номеров, диапазону ячеек E2:E5 – Вид_пансиона (Вставка®Имя®Присвоить);

· выделенную серым цветом графу заполните с помощью списка (Данные ® Проверка; закладка ПараметрыдалееТип данных ® Список; Источник ® Типы_номеров.)

После выполнения этого задания таблица на листе «Вспомогательные таблицы» будет выглядеть так как на рис.3.12.

Рис. 3.12. Лист «Вспомогательные таблицы»

Первая цифра номера комнаты – это этаж. На 3-м и 4-м этажах размещение типов номеров аналогично второму этажу.

Для того чтобы проставить цену номеров воспользуйтесь функцией ВПР(категория Ссылки и массивы), аргументами которой являются:

Искомое_значение – тип номера (В8),

Табл_массив – таблица, в которой ведется поиск ($A$2:$B$5),

Номер_индекса_столбца – номер столбца в таблице, где находится стоимость ( у нас - 2),

Диапазон просмотра –0.

Графа «Занятость» заполняется по формуле:

ЕСЛИ(ЕОШИБКА(ВПР(А8;’БД Отель’!$C$2:$C$35;1;0));0;1). Для ввода этой формулы вызываете функцию ЕСЛИ, затем сразу функцию ЕОШИБКА(категория Проверка свойств и значений), затем функцию ВПР (контролируете строку формул). Задаете аргументы для функции ВПР, затем щелкаете по строке формул и заканчиваете вводить формулу.

Размножьте формулу на 50 строк. В результате выполнения ячейки будут заполнены нулями.

Введите формулу для расчета Итого занято.

3. На листе «БД Отель» наберите заголовки столбцов

4. В графу «Тип номера» введите формулу, выводящую тип номера в зависимости от номера комнаты:

ЕСЛИ(С2>19;ВПР(С2;’Вспомогательные таблицы’!$A$8:$B$37;2;0);” ”).

Размножьте эту формулу на 50 строк.

5. Аналогично составьте и введите формулу для вывода цены номера в день в зависимости от типа номера. Размножьте формулу на 50 строк.

6. Вид пансиона оформите как поле со списком (Данные®ПроверкадалееТип данных ® Список,затем Источник® Вид_пансиона), размножьте формулу на 50 строк.

7. Для вывода цены пансиона в день используйте формулу:

ЕСЛИ(F2<>””;ВПР(F2;‘Вспомогательные таблицы’!$E$3:$F$5;2;0);” “), размножьте формулу на 50 строк.

8. Введите формулу для расчета оплаты за день:

Если «Вид пансиона» <> “”, то «Цена номера в день» + «Цена пансиона», иначе «Цена номера в день». Размножьте формулу на 50 строк.

9. Скопируйте заголовки столбцов с листа «БД Отель» на лист «Архив».

10. В столбец I введите заголовок «Дата выезда», в столбец J введите заголовок «Количество дней», в столбец K введите заголовок «Общая стоимость».

11. Введите формулу для выдачи даты выезда ЕСЛИ(С2>19;СЕГОДНЯ();” “) (СЕГОДНЯ() – это функция), установите для столбца формат ДАТА, размножьте формулу на 50 строк.

12. Введите формулу для расчета количества дней: если номер комнаты>19, то «Дата выезда» - «Дата заезда» + 1, иначе пусто. Размножьте формулу на 50 строк.

13. Введите формулу для расчета общей стоимости:

Если номер комнаты>19, то «Оплата за номер в день» * «Количество дней», иначе пусто. Размножьте формулу на 50 строк.

14. Проверьте работу формул на листе «БД Отель». Для этого введите не менее 10 записей о клиентах с разными датами заезда и другими исходными данными.

15. Проверьте работу формул на листе «Архив». Для этого оформите выезд из отеля двух клиентов, учитывая следующие соглашения.

На лист «Архив» заносятся данные о клиентах, которые выезжают из отеля. Для того чтобы перенести данные о клиенте с листа «БД Отель» на лист «Архив», выполняются следующие действия:

• Выделяется вся запись и копируется в буфер обмена.
• На листе «Архив» активизируется ячейка в столбце А в пустой строке.
• Выполняется вставка из буфера обмена.
• На листе «БД Отель» удаляется строка, в которой была запись о выехавшем клиенте.

17. На листе «Вспомогательные таблицы» с помощью Автофильтра найдите список свободных номеров.

18. На листе «БД Отель» научитесь сортировать записи по датам и номерам комнат.

19. Создайте копию листа «БД Отель» с новым именем «Итоги». Подведите промежуточные и общие итоги среднего значения оплаты по разным типам номеров.

20. Создайте копию листа «БД Отель» с новым именем «Критерии». С помощью расширенного фильтра подготовьте списки клиентов (фамилия, вид пансиона, оплата за номер), проживающих в номерах разного типа: люксе, 1-местном, 2-местном.

21. По «БД Отель» постройте сводную таблицу для анализа спроса на разные виды пансиона клиентами, проживающими в номерах различного типа. Постройте диаграмму по созданной сводной таблице.

22. Продемонстрируйте работу базы данных.