Часть 1. Создание, редактирование, форматирование таблицы. Построение диаграмм
Создание таблицы
1. Создать таблицу оплаты услуг спутникового телевидения в соответствии с образцом (рис. 24). Ввести в таблицу следующие названия столбцов: № п/п, Клиент, Число каналов, Дата платежа, Сумма оплаты ($), Задолженность в днях, Пеня ($), Итоговая сумма ($). Ввести 10 записей.
2. В столбец № п/п ввести ряд чисел с использованием команды Правка-Заполнить.
3. Столбец Клиент заполнить произвольными значениями, создав дополнительную таб.1 и применив функции СЦЕПИТЬ и ЛЕВСИМВ.
4. В столбцы Число каналов, Дата платежа ввести значения в соответствии с образцом.
5. Сумма оплаты равна тарифуи зависит от числа каналов (см. дополнительную таб. 2). Для расчёта использовать функцию ЕСЛИ.
6. Для выполнения дальнейших расчетов за пределами таблицы ввести константы: Назначенная дата оплаты = 10 февраля; % пени = 1%.
7. Задолженность в днях рассчитать как разность между датой платежа и назначенной датой оплаты. Если оплата произведена до назначенной даты оплаты включительно, то задолженность в днях равна нулю. Для расчета использовать функции ЕСЛИ, при этом количество просроченных дней будет отрицательной величиной.
8. Пеня = % пени *Сумма оплаты *|Задолженность в днях|. В расчетах использовать функцию ABS.
Рис. 24
9. Итоговая сумма = Сумма оплаты +Пеня.
10. Создать имя для диапазона ячеек столбца Пеня и применить его в формулах таблицы
11. В соответствии с образцом в выделенных ячейках рассчитать суммарные, максимальные и средние значения столбцов Сумма оплаты и Итоговая сумма. При вводе функций использовать различные средства: Мастер функций или Автосуммирование.
Форматирование таблицы
12. Отформатировать таблицу:
- для ячеек первой строки («шапки») таблицы задать вертикальное выравнивание текста, длинные заголовки столбцов переносить в ячейке по словам;
- применить к ячейкам «шапки» таблицы шрифт Arial Cyr, начертание – жирный подчеркнутый курсив, размер 10 pt; выполнить заливку, расположить текст по центру;
- столбец Итоговая сумма выделить светло-серым фоном;
- присвоить ячейкам, содержащим денежные величины, денежный формат;
- добавить строку для заголовка таблицы и ввести в заголовок текст «Оплата услуг спутникового телевидения». Заголовок отцентрировать в пределах блока таблицы. Назначить жирный цветной шрифт Courier, размер 12 pt, увеличить высоту строки. После заголовка перед шапкой вставить пустую строку.
- отменить вывод на экран сетки таблицы. Расчертить таблицу линиями, используя кнопку Границы на Пи Форматирование: внешнюю рамку таблицы – «толстыми внешними линиями», «шапку» таблицы подчеркнуть цветной линией, столбцы и строки расчертить тонкими черными линиями «все границы»;
- используя условное форматирование, выделить любым цветом ячейки столбца Сумма оплаты, значения которых выше среднего
- добавить примечания к ячейкам с максимальным и минимальным значениями итоговой суммы.
13. Присвоить листу имя Оплата TV. Изменить цвет ярлыка листа.
Построение диаграмм
14. Создать внедренную гистограмму с накоплением, отражающую оплату каналов спутникового TV. В качестве рядов использовать данные из столбцов Сумма оплаты и Пеня, в качестве категорий – данные из столбца Клиенты.
15. Добавить в гистограмму ряд Число каналов путем копирования. Изобразить ряд Число каналов в виде графика с маркерами, используя вспомогательную ось. Изменить форму маркеров у графика.
16. Присвоить заголовок диаграмме – «Оплата кабельных каналов TV», назвать основную ось У – «Сумма оплаты », вспомогательную ось У – «Каналы », ось категорий – «Клиенты ». Обвести заголовки рамками. Расположить легенду вверху справа.
17. На отдельном листе создать нестандартную черно-белую круговую диаграмму, отображающую значения ряда Итоговая сумма. В качестве категорий использовать ряд Клиенты. Добавить подписи данных в виде значений и имен категорий. Отделить один из секторов от остальных и изменить для него узор заливки. Убрать легенду. Листу с диаграммой присвоить имя Диаграмма_Оплата.
18. Сохранить файл под именем Фамилия_№ задания.xls.
Часть 2. Анализ и управление данными. Работа с массивами
Подбор параметра, таблица подстановки
19. Добавить новый лист Подбор. На листе Подбор создать приведенную на рис. 26 таблицу. Ввести формулу в ячейку B3.
20. Используя инструмент Подбор параметра, рассчитать, сколько требуется продать домов, чтобы получить прибыль в размере 1,65 млрд. руб.
Рис. 26
21. На листе Оплата TV с помощью таблицы подстановки с одним параметром проанализировать влияние % пени на итоговую сумму. % пени может принимать значения: 0,4%, 0,6%, 0,8%, 1,00%, 1,2%.
22. Сохранить файл на жестком диске и на дискете под именем Фамилия_№ задания_2.xls.
