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

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

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

1. Создать таблицу расчета стоимости мобильных телефонных разговоров в соответствии с образцом (рис. 18). Ввести в таблицу следующие названия столбцов:Фамилия, Тариф, Стоимость минуты входящего вызова ($), Стоимость минуты исходящего вызова ($), Продолжительность входящих вызовов (мин.) Продолжительность исходящих вызовов (мин.), Стоимость разговоров за месяц ($), Бонус (мин.), Конечная стоимость ($).Ввести 10 записей.

 

Рис. 18

2. В столбец Фамилия ввести фамилии клиентов.

3. Столбец Тарифпроизвольно заполнить названиями тарифных планов. Сотовая компания предлагает три тарифных плана (А, В, С).

4. Столбцы Стоимость минуты входящего вызова и Стоимость минуты исходящего вызова заполнить, используя дополнительную таб.1 и функцию ПРОСМОТР.

5. Столбцы Продолжительность входящих вызововиПродолжительность исходящих вызововзаполнить произвольными значениями (в пределах 0 ¸.200 мин.).

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

- Стоимость разговоров за месяц = (Стоимость минуты входящего вызова* Продолжительность входящих вызовов) + (Стоимость минуты исходящего вызова * Продолжительность исходящих вызовов);

- Бонус (скидку)рассчитать с использованием функции ЕСЛИ.

В зависимости от стоимости разговоров за месяц компания предоставляет клиенту бонус в виде определенного количества дополнительных минут исходящих вызовов:

 

Стоимость разговоров	<$5	<=$10	>$10
Бонус	0 мин.	5 мин.	10 мин.

 

- Конечная стоимость = Стоимость разговоров за месяц – (Бонус * Стоимость минуты исходящего вызова);

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

 

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

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

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

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

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

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

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

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

9. Присвоить листу имя Расчет.

 

 

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

10. Создать внедренную линейчатую диаграмму. В качестве рядов использовать данные из столбцовПродолжительность входящих вызовови Продолжительность исходящих вызовов,в качестве категорий – данные из столбца Фамилия.

11. Добавить в диаграмму ряд Бонусперетаскиванием мышью, отобразить значения точек ряда.

12. Присвоить заголовок диаграмме и названия осям. Создать рамки вокруг заголовков и названий осей, убрать рамки у легенды.

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

14. Добавить на лист узор подложки. Изменить цвет ярлычка листа.

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

 

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

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

16. С помощью инструмента Подбор параметрарассчитать, при каком значении стоимости исходящих вызовов конечная стоимость разговоров у первого клиента достигнет заданной величины.

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

17. При помощи таблицы подстановки данных проанализировать зависимость итоговой суммы стоимости разговоров за месяц от стоимости минуты входящего вызова и стоимости минуты исходящего вызова для тарифаА (подставить значения 0,02, 0,04, 0,06 и 0,06, 0,08, 0,1$ соответственно). Исходные значения выделить цветом.

 

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