Практикум по информационным технологиям в профессиональной деятельности - Елена Михеева

9. Проведите форматирование заголовка таблицы. Для этого выделите интервал ячеек от А1 до D1, объедините их кнопкой панели инструментов Объединить и поместить в центре или командой меню Формат/Ячейки/вкладка Выравнивание/отображение – Объединение ячеек. Задайте начертание шрифта – полужирное, цвет – по вашему усмотрению.

Рис. 8.6. Задание интервала ячеек при суммировании функцией СУММ

Проведите форматирование ячейки D13 таблицы. Для выделения результата финансового анализа выполните заливку цветом ячейки D13 (Формат/Ячейки/вкладка Вид).

Конечный вид таблицы приведен на рис. 8.7.

Рис. 8.7. Таблица расчета финансового результата (задание 8.1)

10. Постройте диаграмму (линейчатого типа) изменения финансовых результатов по дням недели с использованием Мастера диаграмм.

Для этого выделите интервал ячеек с данными финансового результата D4:D10 и выберите команду Вставка/Диаграмма. На первом шаге работы с Мастером диаграмм выберите тип диаграммы – линейчатая; на втором шаге на вкладке Ряд в окошке Подписи оси Х укажите интервал ячеек с днями недели А4:А10 (рис. 8.8).

Рис. 8.8. Задание Подписи оси Х при построении диаграммы

Далее введите название диаграммы и подписи осей; дальнейшие шаги построения диаграммы осуществляются по подсказкам мастера. Конечный вид диаграммы приведен на рис. 8.9.

11. Произведите фильтрацию значений дохода, превышающих 4200 руб.

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

Рис. 8.9. Конечный вид диаграммы задания 8.1

Для установления режима фильтра установите курсор внутри созданной таблицы и воспользуйтесь командой Данные/Фильтр/Автофильтр. В заголовках полей появятся стрелки выпадающих списков. Щелкните по стрелке в заголовке поля, на которое будет наложено условие (в столбце «Доход»), и вы увидите список всех неповторяющихся значений этого поля. Выберите команду для фильтрации – условие (рис. 8.10).

Рис. 8.10. Выбор варианта фильтрации

В открывшемся окне «Пользовательский автофильтр» задайте условие «Больше 4200» (рис. 8.11).

Произойдет отбор данных по заданному условию.

Проследите, как изменились вид таблицы и построенная диаграмма (см. рис. 8.12).

12. Сохраните созданную электронную книгу в своей папке.

Рис. 8.11. Задание условия фильтрации

Рис. 8.12. Вид таблицы после фильтрации данных

Задание 8.2. Заполнить таблицу «Анализ продаж» (рис. 8.13), произвести расчеты, выделить минимальную и максимальную суммы покупки; по результатам расчета построить круговую диаграмму суммы продаж. Использовать созданный стиль «Шапка таблиц» (Формат/Стиль/Шапка таблиц)

Формулы для расчета:

Сумма = Цена х Количество;

Всего = сумма значений колонки «Сумма».

Рис. 8.13. Исходные данные для задания 8.2

Краткая справка. Для определения максимального (минимального) значения данных установите курсор в ячейке расчета, выберите встроенную функцию МАКС (МИН) из категории «Статистические», в качестве первого числа выделите диапазон ячеек значений столбца «Сумма» (ячейки Е3:Е10).

Дополнительные задания

Задание 8.3. Заполнить ведомость учета брака, произвести расчеты, выделить минимальную, максимальную и среднюю суммы брака, а также средний процент брака; произвести фильтрацию данных по условию процента брака < 9 %, построить график отфильтрованных значений изменения суммы брака по месяцам (рис. 8.14)

Формула для расчета:

Сумма брака = Процент брака х Сумма затрат.

Рис. 8.14. Исходные данные для задания 8.3

Краткая справка. В колонке «Процент брака» установите процентный формат чисел (Формат/Ячейки, вкладка Число, формат – Процентный).

Задание 8.4. Заполнить таблицу анализа продаж (рис. 8.15), произвести расчеты, выделить минимальную и максимальную продажу (количество и сумму); произвести фильтрацию по цене, превышающей 9300 руб.; построить гистограмму отфильтрованных значений изменения выручки по видам продукции

Формулы для расчета:

Всего = Безналичные платежи + Наличные платежи;

Выручка от продажи = Цена х Всего.

Рис. 8.15. Исходные данные для задания 8.4

Практическая работа 9

Тема: ГРАФИЧЕСКОЕ ИЗОБРАЖЕНИЕ СТАТИСТИЧЕСКИХ ДАННЫХ И ПРОГНОЗИРОВАНИЕ В MICROSOFT EXCEL

Цель занятия. Изучение информационной технологии использования возможностей Microsoft Excel для статистических расчетов, графического представления данных и прогнозирования.

Задание 9.1. С помощью диаграммы (обычная гистограмма) отобразить данные о численности населения России (млн чел.) за 1970–2005 гг.

Исходные данные представлены на рис. 9.1, результаты работы – на рис. 9.3.

Порядок работы

1. Откройте редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу (при стандартной установке Microsoft Office выполните Пуск/Все программы/Microsoft Excel).

Рис. 9.1. Исходные данные для задания 9.1

2. Создайте на листе 1 таблицу численности населения по образцу (см. рис. 9.1). Для ввода значений лет создайте ряд чисел с интервалом в 7 лет (введите первые два значения даты – 1970 и 1977 г., выделите обе ячейки и протяните вправо за маркер автозаполнения до нужной конечной даты).

3. Постройте диаграмму (обычная гистограмма) по данным таблицы. Для этого выделите интервал ячеек с данными численности населения A3:G3 и выберите команду Вставка/Диаграмма. На первом шаге работы с Мастером диаграмм выберите тип диаграммы – гистограмма обычная; на втором шаге на вкладке Ряд в окошке Подписи оси Хукажите интервал ячеек с годами B2:G2 (рис. 9.2). Далее введите название диаграммы и подписи осей; дальнейшие шаги построения диаграммы осуществляются по подсказкам мастера.

Рис. 9.2. Второй этап построения диаграммы

Задание 9.2. Осуществить прогноз численности населения России на 2012 г. добавлением линии тренда к ряду данных графика

Краткая справка. Для наглядного показа тенденции изменения некоторой переменной целесообразно на график вывести линию тренда. Это возможно не для всех типов диаграмм, а только для гистограмм, линейчатых диаграмм, диаграмм с областями, графиков. Введенная линия тренда сохраняет связь с исходным рядом, т. е. при изменении данных соответственно изменяется линия тренда. Линию тренда можно использовать для прогноза данных.

Порядок работы

1. Добавьте линию тренда к диаграмме, построенной в задании 9.1. Для этого сделайте диаграмму активной щелчком мыши по ней и в меню Диаграмма выберите команду Добавить линию тренда.

2. В открывшемся окне Линия тренда (см. рис. 9.3) на вкладке Тип выберите вид тренда – полиномиальный 4-й степени.

Рис. 9.3. Задание типа линии тренда

3. Для осуществления прогноза на вкладке Параметры введите название кривой «Линия тренда» и установите параметр прогноза – вперед на один период (рис. 9.4).

Рис. 9.4. Задание параметров линии тренда

4. На диаграмме будут показаны линия тренда и прогноз на один период вперед (рис. 9.5).

Рис. 9.5. Конечный вид диаграммы и линии тренда задания 9.1

5. Внесите численное значение прогноза на 2012 г. в исходную таблицу. Если вы все сделали правильно, то прогноз численности населения России по линии тренда составит 131 млн чел.

Задание 9.3. Построить график о числе заключенных населением России браков. Добавить линейную линию тренда и составить прогноз на три периода вперед

Исходные данные представлены на рис. 9.6, результаты работы – на рис. 9.7.

Рис. 9.6. Исходные данные для задания 9.3

Рис. 9.7. Конечный вид графика и линии тренда задания 9.3

Задание 9.4. Построить линейчатую диаграмму изменения стоимости автомобилей в зависимости от года выпуска. Добавить линейную и логарифмическую линии тренда. Определить, какой вид линии тренда дает более реальный прогноз. Определить примерную стоимость автомобилей 1998 и 1997 гг. выпуска

Исходные данные представлены на рис. 9.8, результаты работы – на рис. 9.9.

Рис. 9.8. Исходные данные для задания 9.4