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

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

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

1. Создать таблицу для расчета платежей за телефонные разговоры в соответствии с образцом (рис. 7). Ввести в таблицу следующие названия столбцов: № п/п, Клиент, Начало разговора (ч., мин.), Окончание разговора (ч., мин.), Время разговора (мин.), Начисление (руб.), Скидка (%), Итого (руб.), Реальная дата оплаты, Количество просроченных дней, Пеня (руб.), Стоимость разговора (руб.).Ввести 10 записей.

2. Для выполнения расчетов за пределами таблицы ввести константы: Стоимость 1 минуты= 5,7р.; Скидка = 50%; Назначенная дата оплаты = 16 янв.; % пени = 1% от итоговой суммы за каждый просроченный день.

3. В столбец № п/п ввести ряд чисел с использованием команды контекстного меню Прогрессия.

4. В столбец Клиент ввести фамилии клиентов.

5. В столбцы Начало разговора,иОкончание разговораввестипроизвольные значения в формате Время. Телефонные разговоры могут происходить в любое время суток.

6. Время разговора рассчитать как разность между окончанием и началом разговора в минутах. Для расчета использовать функции ЧАС и МИНУТЫ.

7. Начисление = Время разговора * Стоимость 1 минуты.

8. Скидка равна 50%, если разговор происходил с 22:00 ч. до 7:00 ч. В другое время суток скидки нет. Для расчета использовать функции ЧАС и ЕСЛИ.

9. Итого = Начисление - Начисление * Скидка.

10. В столбецРеальная дата оплатыввести произвольные значения даты оплаты телефонных разговоров в формате Дата. Если оплата произведена позднее назначенной даты оплаты (16 янв.), то с клиента взимается пеня.

11. Количество просроченных дней рассчитать как разность между реальной и назначенной датами оплаты (количество просроченных дней при этом будет отрицательной величиной).

12. Пеня начисляется в размере % пени (1%) от итоговой суммы за каждый просроченный день. Для расчета использовать функции ЕСЛИ и ABS.

13. Стоимость разговора = Итого + Пеня.

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

 

 

Рис. 7

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

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

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

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

- столбец Стоимость разговора выделить светло-серым фоном;

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

- добавить две строки для заголовка таблицы и ввести в заголовок текст «Расчет платежей за телефонные разговоры». Во вторую строку ввести название месяца оплаты «Декабрь». Назначить жирный цветной шрифт Courier, размер 12 pt, увеличить высоту строки. После заголовка перед шапкой вставить пустую строку.

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

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

16. Присвоить листу имя Платежи.

 

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

17. Создать внедренную гистограмму. В качестве рядов использовать данные из столбцов Начисление, Итого, Стоимость разговора,в качестве категорий – данные из столбца Клиент.

18. Добавить в гистограмму ряд Пеня путем копирования. Изобразить ряд Пеняв виде графика с маркерами, используя вспомогательную ось. Изменить форму маркеров у графика. Максимальное значение графика пометить выноской с текстом «Наибольшее значение пени». Присвоить заголовок диаграмме и названия осям. Создать рамки вокруг заголовков и названий осей, убрать рамки у легенды.

19. На отдельном листе создать нестандартную трубчатую диаграмму, отображающую итоговое, максимальное и среднее значения рядаСтоимость разговора, в качестве категорий использовать ряд названий: Сумма, Максимальное, Среднее. Ввести в диаграмму заголовок и надпись с текстом «Диаграмму построил (а) Фамилия Имя, группа .№». Листу с диаграммой присвоить имя Диаграмма_Итоги.Добавить на лист узор подложки. Изменить цвет ярлычка листа.

 

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

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

20. С помощью инструмента Подбор параметра решить уравнение:

 

Ответ: .

 

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

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

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