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