Часть 2. Анализ и управление данными. Работа с массивами. Часть 1. Создание, редактирование, форматирование таблицы

Часть 1. Создание, редактирование, форматирование таблицы. Построение диаграмм

Создание таблицы

1. Создать таблицу расчета стоимости аренды автомобилей в соответствии с образцом (рис. 22). Ввести в таблицу следующие названия столбцов: № автомобиля по реестру, Дата аренды, Время начала аренды, Время окончания аренды, Продолжительность аренды, День недели, Стоимость аренды в $, Стоимость аренды в рублях.Ввести 10 записей.

2. В столбец № автомобиля по реестру ввести номера автомобилей: L-21, L-22, L-23, L-24.

3. В столбец Дата аренды ввести даты с 01.11.05 по 10.11.05, используя автозаполнение.

4. В столбцы Время начала аренды и Время окончания аренды ввести произвольно значения, учитывая, что аренда автомобилей производится с 6:00 до 24:00.

5. Значения остальных столбцов рассчитать по формулам:

- значения столбца Продолжительность аренды рассчитать как разность между окончанием и началом аренды в часах. Для расчета использовать функцию ЧАС;

- для расчета столбца День неделииспользовать функцию ДЕНЬНЕД, используя значения столбца Дата аренды;

- Стоимость арендыменяется в зависимости от дня недели (будни или выходные дни):

День недели >5 <=5
Стоимость часа аренды,$

Для расчета стоимости аренды использовать функцию ЕСЛИ. Значения стоимостей часа аренды ввести за пределами таблицы;

- Стоимость аренды в рублях = Стоимость аренды в $* Курс $.Значение курса доллара ввести за пределами таблицы;

- создать имена для диапазона ячеек столбцов Дата аренды, День неделии применить их в формулах таблицы;

 

Рис. 22

6. В соответствии с образцом в выделенных ячейках рассчитать сводные показатели значений столбцов Стоимость аренды в $, Стоимость аренды в рублях по соответствующим функциям. При вводе функций использовать различные средства: Мастер функцийилиАвтосуммирование.

 

Форматирование таблицы

7. Отформатировать таблицу:

- для ячеек первой строки («шапки») таблицы задать вертикальное выравнивание текста, длинные заголовки столбцов переносить в ячейке по словам;

- изменить шрифт «шапки» на жирный Arial, выполнить заливку, расположить текст по центру;

- значения столбца День недели выделить светло-желтым цветом;

- присвоить ячейкам, содержащим денежные величины, денежный формат;

- добавить строку для заголовка таблицы и ввести в заголовок текст «Стоимость аренды автомобилей». Заголовок отцентрировать в пределах блока таблицы; назначить жирный цветной шрифт MS Sans Serif, размер 12 pt; выполнить заливку. После заголовка перед шапкой вставить пустую строку;

- отменить вывод на экран сетки таблицы. Расчертить таблицу линиями: внешнюю рамку таблицы – жирной цветной линией, «шапку» таблицы подчеркнуть толстой пунктирной линией, столбцы и строки расчертить тонкими черными линиями;

- добавить примечания к ячейкам с максимальным и минимальным значениями стоимости аренды;

- используя условное форматирование, выделить цветом в столбце Продолжительность арендызначения >4 часов.

8. Присвоить листу имя Стоимость аренды.Изменить цвет ярлыка листа.

 

Построение диаграмм

9. На отдельном листе построить линейчатую диаграмму (объемный вариант), отображающую среднее, максимальное, минимальное и итоговое значения ряда Стоимость аренды в $. В качестве категорий использовать ряд названий: Среднее, Максимальное, Минимальное, Итого.

10. Присвоить заголовок диаграмме и названия осям. Для столбца с максимальным значением изменить заливку, выбрав узор штриховки «Крупная клетка». Добавить подписи данных в значениях. Изменить положение и шрифт подписей.

11. Листу с диаграммой присвоить имя Диаграмма_Стоимость.

12. Создать внедренную объемную круговую диаграмму, отображающую стоимость аренды в рублях автомобиля L-22. В качестве категорий использовать данные из столбца День недели. В качестве подписей данных использовать значения. Ввести в диаграмму заголовок «Аренда автомобиля L-22»

13. Обвести диаграмму рамкой с тенью и выполнить заливку области диаграммы.

14. Сохранить файл под именем Фамилия_№ задания.xlsx.

 

Часть 2. Анализ и управление данными. Работа с массивами

Подбор параметра

15. Используя инструмент Подбор параметра, рассчитать, каков должен быть курс доллара, чтобы средняя стоимость аренды в рублях стала равна заданной величине.

Таблица подстановки

16. При помощи таблицы подстановки данных проанализировать зависимость итоговой стоимости аренды в долларах от стоимости часа аренды в будни (подставить значения 35, 40, 45, 50, 55 $). Исходные значения выделить цветом.

 

17. Сохранить файл на жестком диске и на дискете под именем Фамилия_№ задания_2.xls.