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

Часть 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.