Построение гистограммы с группировкой

15. Перейдите на новый рабочий лист (Лист3).

16. Введите на этот лист рабочей книги следующую таблицу:

 

 

Выделив диапазон ячеек A4:D7, постройте объемную гистограмму с группировкой, отражающую динамику изменения объемов финансирования каждой отрасли по годам. Введите название диаграммы – «Динамика изменения объемов финансирования по отраслям» и заголовки осей: Х – «Отрасли социальной сферы» и Y – «В млрд. рублей». Разместите гистограмму на отдельном листе рабочей книги и переименуйте его в «Гистограмма2».

17. Отредактируйте построенную диаграмму. Для этого:

· замените в легенде имена Ряд1, Ряд2 и Ряд3 на 2007 г., 2008 г., 2009 г. Для этого выделите легенду, из контекстного меню легенды выберите команду Выбрать данные, нажмите кнопку Изменить и в поле Имя ряда введите новую ссылку щелчком по ячейке В3 (2007 г.). Аналогично измените остальные имена;

· измените способ вывода в диаграмме строк и столбцов. Такая диаграмма будет наглядно характеризовать динамику роста финансирования каждой социальной отрасли. Для этого на вкладке Конструктор в группе Данные выберите команду Строка/Столбец. Повторным щелчком по кнопке Строка/Столбец верните диаграмму в исходное состояние;

· с помощью контекстного меню исключите из диаграммы ряд, соответствующий 2007 г.;

· добавьте в диаграмму новый ряд для 2010 г. Для этого добавьте соответствующий ряд в исходную таблицу, перейдите на лист с диаграммой, выполните команду Конструктор/Дан­ные/Выбрать данные и нажмите кнопку Добавить. Введите ссылки на заголовок и добавленный ряд исходной таблицы;

· измените место расположения диаграммы. Для этого выполните команду Конструктор/Расположение/Пере­мес­тить диаграмму. Разместите диаграмму на листе с исходной таблицей;

· измените тип диаграммы. Для этого выполните команду Конструктор/Тип/Изменить тип диаграммы и в списке типов выберите Цилиндрическая с группировкой.

 

Построение линейчатой диаграммы с накоплением

18. Вставьте новый лист после существующих листов. Для этого щелкните вкладку Вставить лист в нижней части экрана:

 

 

19. Переименуйте вставленный Лист4 в «Линейчатая диаграмма».

20. Постройте сравнительную диаграмму распределения мужчин и женщин по возрастным группам в соответствии с прилагаемой таблицей.

 

 

Для этого выполните следующие действия:

· задайте диапазон ячеек для построения диаграммы (A1:C8);

· перейдите на вкладку Вставка и в группе Диаграммы выберите тип диаграммы – линейчатая с накоплением;

· добавьте горизонтальные линии сетки, выполнив команду Макет/Оси/Сетка/Горизонтальные линии сетки по основной оси/Основные линии сетки;

· вместо легенды используйте текстовые поля с надписями «Женщины» и «Мужчины» (Вставка/Текст/Надпись);

· с помощью контекстного меню удалите легенду;

· для размещения меток делений с левой стороны из контекстного меню вертикальной оси выберите Формат оси и в поле Подписи оси вместо «рядом с осью» установите «внизу».

· для перевода отрицательных процентов в положительные на оси значений создайте и примените специальный пользовательский формат: 0%;0%;0%. Для этого вызовите контекстное меню горизонтальной оси, выберите Формат оси, в левой части диалогового окна Формат оси установите Число, в списке числовых форматов установите Все форматы, в поле ввода Код формата введите нужный, нажмите кнопки Добавить и Закрыть.

 

Построение пузырьковой диаграммы

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

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

Построение лепестковой диаграммы

22. На новом листе «Лепестковая диаграмма» самостоятельно постройте заполненную лепестковую диаграмму, характеризующую сезонность продаж туристских путевок в течение года по данным приведенной таблицы.

 

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

23. На новом листе «График Ганта» постройте простую диаграмму Ганта, отображающую во времени начала работ проекта и их продолжительность. Исходные данные содержатся в приведенной ниже таблице.

 

Для построения диаграммы выполните следующие действия:

· выберите данные, которые нужно показать на диаграмме Ганта (A2:D8);

· выполните команду Вставка / Диаграммы / Линейчатая/ Линейчатая с накоплением;

· добавьте на диаграмму данные о продолжительности работ. Для этого выполните команду Конструктор/Данные /Вы­брать данные и в окне Выбор источника данных нажмите кнопку Добавить. В диалоговом окне Изменение ряда в поле Имя ряда щелчком мыши введите ссылку на ячейку с именем «Продолжительность в днях», а в поле Значения введите мышью ссылку на диапазон ячеек С3:С8 с данными о продолжительности работ. Нажмите ОК;

· нажмите кнопку Изменить и введите в поле Диапазон подписей оси ссылку на диапазон ячеек с наименованиями работ для подписей вертикальной оси категорий;

· два раза щелкните по кнопке ОК;

· на диаграмме щелкните по ряду данных «Начало работы»;

· выполните команду Формат / Текущий фрагмент / Формат выделенного фрагмента;

· в группе Заливка выберите вариант Нет заливки, щелкните по кнопке Закрыть;

· выделите вертикальную ось (категорий) или щелкните по диаграмме и выберите ее в списке элементов диаграммы по команде Формат/Текущий фрагмент/Область диаграммы /Вер­тикальная ось (категорий);

· на вкладке Формат в группе Текущий фрагмент щелкните Формат выделенного фрагмента;

· в группе Параметры оси установите флажок обратный порядок категорий, а в группе Горизонтальная ось пересекает – в максимальной категории. Нажмите кнопку Закрыть;

· выделите ряд «Начало работы», вызовите контекстное меню и в левой части окна Формат ряда данных выберите Заливка, а в правой – Нет заливки;

· вызовите контекстное меню легенды и удалите ее;

· вызовите контекстное меню горизонтальной оси (значений). В диалоговом окне Формат оси в группе Параметры оси измените минимальное значение с «авто» на «фиксированное», введя дату 01.02.09 (или число 39845); максимальное значение с «авто» на «фиксированное», введя дату 02.04.09 (или число 39905); цену основных делений введите 10, а цену промежуточных делений – 2.

· введите название диаграммы по команде Макет/Подписи /Название диаграммы/Над диаграммой;

· отредактируйте размеры шрифтов отдельных элементов и размеры области диаграммы.

 

 

Трендовый анализ

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

Трендовый анализ – это тенденция развития процесса, позволяющая в вероятностном аспекте прогнозировать его дальнейшее поведение.

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

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

Для оценки близости значений линии тренда к фактическим данным принято использовать коэффициент детерминации R ². Этот коэффициент изменяется в пределах от 0 до 1. Чем ближе к 1 значение R ², тем лучше качество подгонки.

При подборе линии тренда к данным Excel автоматически рассчитывает значение R ². Можно отобразить это значение на диаграмме.

24. Вставьте новый лист Рабочей книги Excel и переименуйте его в «Линия тренда».

25. Введите приведенную ниже таблицу.

26. Для этой таблицы сначала постройте диаграмму График. Для этого выделите диапазон ячеек A3:B9 и выполните команду Вставка/Диаграммы/График/График.

27. Для этого графика постройте линию тренда. Для этого:

· выделите график и из контекстного меню выберите команду Добавить линию тренда;

· в диалоговом окне Параметры линии тренда выберите вид линии тренда: полиномиаль­ная 4-й степени;

· в этом же окне установите: прогноз вперед на 1 период и поместить на диаграмму величину достоверности аппроксимации;

· нажмите кнопку Закрыть;

· покажите на диаграмме линии проекции по команде Макет/Анализ/Линии/Линии;

· удалите легенду;

· с помощью контекстного меню Формат линии тренда установите цвет линии – красный;

· введите название диаграммы «Динамика спроса », заливка текста произвольная;

· нажмите кнопку ОК.

28. Сохраните результаты лабораторной работы в файле с именем lab2.xlsx.

ЛАБОРАТОРНАЯ РАБОТА № 3