Глава 9. Построение диаграмм

С помощью приложения MS Excel можно легко создавать профессионально оформленные диаграммы разнообразных типов. Выбрав тип, макет и стиль каждой диаграммы можно быстро и профессионально обработать массивы данных любого размера.

Диаграммы помогают визуально представить данные и проанализировать их. Данные различных типов отображаются на диаграммах по-разному. Некоторые диаграммы нельзя использовать для определенных типов данных.

В MS Excel 2013 существует 10 типов диаграмм, показанные на рис.9.1, каждый из которых имеет несколько вариантов.

Рис. 9.1. Все виды диаграмм

Гистограмма

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

Задание 1. Построить гистограмму.

Ваши действия:

1. Откройте книгу Лабораторные работы.xlsx, перейдите на лист Лаб.8.

2. Выделите диапазон ячеек А1:Е6 и скопируйте в книгу Упражнения.xlsx на лист Упр.9 (рис.9.2).

Рис.9.2. Исходные данные для построения диаграмм

3. Выделите два диапазона ячеек: A3:D3 и при нажатой клавише Ctrl добавьте второй диапазон A6:D6.

4. На вкладке Вставка выберите тип диаграммы – Гистограмма (рис.9.3).

5. Используя команду Вставка, Добавить элемент диаграмы, Название диаграммы, Над диаграммой дайте название диаграммы – Сравнительный анализ продаж.

6. Выполните команду Конструктор, Добавить элемент диаграммы, Легенда, Сверху. В результате Легенда переместится над диаграммой.

7. Выделите диаграмму правой кнопкой мыши и выберите команду Выбрать данные.

8. Измените подписи горизонтальной оси, указав диапазон ячеек – B2:D2.

Рис.9.3. Гистограмма

Круговая

Круговая диаграмма (имеет 5 подтипов) характеризует абсолютную величину каждого элемента ряда данных, а так же позволяет наглядно оценить вклад каждого элемента ряда в общую сумму. Такую диаграмму рекомендуется использовать, когда необходимо подчеркнуть какой-либо значительный элемент или сегмент данных. Отдельные сегменты диаграммы могут быть выделены (вырезаны) и указаны числовые значения сегмента круговой диаграммы.

Круговую диаграмму рекомендуется использовать, если:

- нужно отобразить только один ряд данных;

- все значения данных являются положительными;

- имеется не более семи категорий.

Задание 2. Построить круговую диаграмму.

Ваши действия:

1. Выделите итоговые данные – Е3:Е6.

2. На вкладке Вставка выберите тип диаграммы – Объемная круговая (рис.9.4).

3. Дайте название диаграммы – Продажа билетов.

4. Легенду расположите внизу диаграммы.

5. В параметрах подписи, вызванной по команде Конструктор, Добавить элемент диаграмы, Подписи данных, Дополнительные параметры подписей данных снимите флажок – значения и установите флажок – доли, Положение метки выберите – У края, снаружи.

Рис.9.4.Объемная круговая диаграмма

6. Выделите данные за февраль – С3:С6.

7. На вкладке Вставка выберите тип диаграммы – Круговая (рис.9.5).

8. Дайте название диаграммы – Продажа билетов в феврале.

9. В подписях данных для диаграммы укажите значения и имена категорий.

10. В подписях данных выберите Положение метки – У края, снаружи и оттяните все метки от края диаграммы в разные стороны.

11. Поверните диаграмму вокруг своей оси на угол ~20°, используя команду Формат ряда данных на правой кнопке мыши. Это делается для того, чтобы линии выноски хорошо просматривались и не перекрещивались.

Рис.9.5. Круговая диаграмма

Линейчатая

Линейчатая диаграмма (имеет 6 подтипов) отражает взаимное соотношение отдельных компонентов таблицы. В диаграммах этого типа категории обычно располагаются по вертикальной оси, а значения — по горизонтальной, таким образом, большее внимание уделяется сопоставлению значений и меньшее – изменениям во времени.

Задание 3. Построить линейчатую диаграмму.

Ваши действия:

1. Выделите данные за январь – А3:В6.

2. На вкладке Вставка выберите тип диаграммы – Линейчатая с группировкой (рис.9.6).

3. Легенду удалите (она становится не нужна, так как стротся диаграмма для одного ряда данных).

4. Дайте название диаграммы – Количество проданных билетов в январе.

5. В подписях данных для диаграммы укажите значения, Положение метки – У края, снаружи.

6. Выделите правой кнопкой мыши любое значение горизонтальной оси и выберите команду Формат оси.

7. Установите максимальное значение – 120 и цену основного деления – 40.

Рис.9.6. Линейчатая диаграмма

График

График отражает изменение значений данных за равные промежутки времени (имеет 7 подтипов). График в Excel строится по точкам. На графиках данные категорий равномерно распределяются вдоль горизонтальной оси, а все значения равномерно распределяются вдоль вертикальной оси. Графики идеально подходят для представления тенденций изменения данных с равными интервалами, такими как месяцы, кварталы или финансовые годы.

Задание 4. Построить график.

Ваши действия:

1. Выделите в таблице исходных данных значения за январь, февраль и март вместе с заголовками – А2:D6.

2. На вкладке Вставка выберите График с маркерами (рис.9.7).

3. На вкладке Конструктор выполните команду Добавить элемент диаграммы, Таблица данных, Показывать легенду.

4. Подпишите вертикальную (Билеты, шт.) и горизонтальную оси (Пункты назначения), используя команду Конструктор, Добавить элемент диаграммы, Названия осей.

5. Дайте название диаграммы – Продажа билетов.

Рис.9.7. График

Точечная

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

Задание 5. Построить точечную диаграмму для функции Y=X³-12X²+3.

Ваши действия:

1. В ячейки А11-А31 введите значения аргумента Х:

- начальное значение аргумента: -5;

- приращение аргумента равно 1;

- конечное значение равно +15.

2. В ячейку В11 введитеформулу =А11^3-12*A11^2+3, затем распространите ее до ячейки В31.

Рис. 9.8. Исходные данные для точечной диаграммы

3. По полученным данным постройте точечную диаграмму на этом же листе справа от таблицы данных (рис.9.9).

Рис.9.9. Точечная диаграмма

Поверхность

Поверхность используется для отображения отношений между большими объемами данных, которые трудно продемонстрировать иначе. Цветные полосы на поверхности не являются рядами данных — они указывают разницу между значениями.

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

Задание 6. Построить поверхность для функции Y=EXP(-((X1-2)²-(X1-2)(X2-1)+(X2-1)²)).

Ваши действия:

1. Создайте таблицу значений функции двух переменных с использованием смешанной адресации.

2. Переменная Х1 меняется от 0 до 3,5 с шагом 0,25 и расположена в строке (рис.9.10).

3. Переменная Х2 меняется от 0 до 4,5 с шагом 0,25 и расположена в столбце.

4. В ячейку В2 введитеследующую формулу, используя смешанную адресацию:

=EXP(-((B$35-2)^2-(B$35-2)*($A36-1)+($A36-1)^2))

Рис.9.10. Таблица исходных данных

5. Постройте диаграмму поверхности и расположите ее под исходной таблицей (рис.9.11).

Рис.9.11. График поверхности

Комбинированная

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

Задание 7. Построить Комбинированную диаграмму.

Ваши действия:

1. Перейдите на лист Лаб.8 и для Задания 2 (см. рис.8.13) постройте Комбинированную диаграмму.

2. Выделите ячейки В26-С33.

3. Выполните команду Вставка, Диаграммы, Комбинированная.

4. С помощью команды Конструктор, Выбрать данные измените названия рядов легенды: Ряд 1 – Данные по реализации (млн.руб.), Ряд 2 – Относительный прирост за год.

5. Легенду расположите над диаграммой.

6. Подпишите вертикальную ось – млн.руб.

7. Итоговая диаграмма, отображающая данные по реализации в виде гистограммы и процент относительного прироста, который необходимо построить по вспомогательной оси в виде графика, показана на рис.9.12.

8. Диаграмму расположите рядом с данными.

Рис. 9.12. Комбинированная диаграмма со вспомогательной осью

Самостоятельная работа 9. Построение диаграмм.

Откройте файл Лабораторные работы.xlsx.

Задание 1. Построить линейчатую диаграмму.

Ваши действия:

1. Перейдите на лист Лаб.7.

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

3. Диаграмму расположите рядом с данными.

Рис.9.13. Линейчатая диаграмма

Задание 2. Построить графики для 3-х функций.

1. На листе Лаб.7 постройте диаграммы для данных Задания 2 (см. рис.7.19) в виде графиков для трех функций Y=SIN(X), Y=1,25*SIN(2*X), Y=1,5*SIN(4*X):

- выделите ячейки В17:D38;

- выберите тип диаграммы – График с маркерами (рис.9.14).

Рис. 9.14. Графики функций
Y=SIN(X), Y=1,25*SIN(2*X), Y=1,5*SIN(4*X)

2. Выделите диаграмму и перейдите на вкладку Конструктор:

- добавьте название диаграммы;

- расположите легенду над диаграммой;

- установите максимальное и минимальное значения на вертикальной оси – 1,500;

- установите цену деления на горизонтальной оси – 1,000.

3. Диаграмму расположите рядом с данными.

Задание 3. Построить график функции Y=SIN(X)+1,25*SIN(2*X)+1,5*SIN(4*X).

1. На листе Лаб.7 выделите ячейки Е17:Е38 (см. рис.7.19).

2. Выберите тип диаграммы – График с маркерами (рис.9.15).

3. Установите максимальное значение на горизонтальной оси – 6,500.

4. Измените полученную диаграмму, добавив заголовок и удалив легенду.

Рис.9.15. График функцииY=SIN(X)+1,25*SIN(2*X)+1,5*SIN(4*X)

Вопросы для самоконтроля

1. Какие виды диаграмм существуют в Excel?

2. Какие новые вкладки появляются на Ленте, если выделить диаграмму?

3. Сколько существует типов диаграмм?

Тесты

1. Сколько существует типов диаграмм?

A. 10

B. 11

C. 12

D. 13

2. Можно ли изменить параметры диаграммы после ее построения?

A. Можно изменить только размер диаграммы.

B. Можно изменить тип диаграммы, ряд данных, расположение диаграммы, ее размеры и т.д.

C. Можно изменить все, кроме типа диаграммы.

D. Ничего изменить нельзя: диаграмму надо построить заново.

3. Для построения диаграммы в таблице необходимо выделить …

A. Заголовки строк таблицы.

B. Заголовки столбцов таблицы.

C. Итоговую строку.

D. Числовые данные.

4. Для круговой диаграммы необходимо выделить …

A. Заголовки строк таблицы.

B. Заголовки столбцов таблицы.

C. Один столбец чисел.

D. Несколько столбцов с числами.

5. Диаграмму можно разместить …

A. На отдельном листе.

B. На имеющемся листе.

C. На специальном листе.

6. Что следует сделать для выделения диаграммы?

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

B. Сделать щелчок левой кнопкой мыши по имени любого столбца, на котором находится диаграмма.

C. Сделать щелчок левой кнопкой мыши по диаграмме.

D. Выделить область листа, на которой находится диаграмма.

7. Что произойдет с диаграммой при удалении таблицы, для которой она была создана?

A. Ничего.

B. Диаграмма будет удалена.

C. Останется пустая диаграмма.

D. Таблицу нельзя удалить, пока не удалена диаграмма.

8. Какой командой следует воспользоваться для добавления подписей значений?

A. Конструктор, Добавить элемент диаграммы, Оси.

B. Конструктор, Добавить элемент диаграммы, Подписи данных.

C. Конструктор, Добавить элемент диаграммы, Таблица данных.

D. Конструктор, Добавить элемент диаграммы, Легенда.

9. Какую область листа необходимо выделить при создании диаграммы, отображающей данные за 1, 2, 3 и 4 кварталы?

A. Никакую. Область листа будет выделена автоматически

B. А35:Е38

C. А35:Е39

D. А35:F39

10. Какую область листа необходимо выделить при создании круговой диаграммы, отображающей данные за 4 квартал?

A. А35:А38;Е35:Е38

B. А35:А39;Е35:Е39

C. А35:Е38

D. A35:F39

11. Какой вкладкой следует воспользоваться для создания диаграммы?

A. Вставка.

B. Разметка страницы.

C. Данные.

D. Вид.

12. Какие новые вкладки появляются на Ленте, если выделить диаграмму?

A. Разметка страницы.

B. Конструктор.

C. Макет.

D. Формат.

13. На какой вкладке находятся элементы для изменения типа диаграммы (например, с круговой на гистограмму)?

A. Разметка страницы.

B. Конструктор.

C. Макет.

D. Формат.

14. Что следует сделать для обновления диаграммы при изменении значений в таблице, для которой построена диаграмма?

A. Заново создать диаграмму.

B. Перед изменением данных необходимо выделить диаграмму.

C. Выделить диаграмму и нажать клавишу клавиатуры F9.

D. Ничего делать не надо, диаграмма изменится автоматически.

15. Что произойдет со значениями в таблице при удалении диаграммы?

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

B. Значения в ячейках, для которых создавалась диаграмма, будут удалены, а также будут удалены значения во всех влияющих ячейках.

C. Значения в ячейках, для которых создавалась диаграмма, будут удалены, а также будут удалены значения во всех зависимых ячейках.

D. Ничего не произойдет.

16. Можно ли изменить цену делений на вертикальной оси диаграммы?

A. Да, можно установить любую цену делений.

B. Да, но цена деления должна быть кратна 10.

C. Да, но только при изменении значений ячеек в таблице.

D. Нельзя изменить.

17. Как можно изменить подписи (метки делений) на горизонтальной оси диаграммы?

A. Выделить ось и ввести с клавиатуры другие подписи.

B. Выделить ось и воспользоваться окном "Формат оси".

C. Выделить диаграмму и воспользоваться окном "Формат области диаграммы".

D. Нельзя изменить без изменения значений ячеек в таблице.

18. Как изменить подписи на легенде диаграммы?

A. Выделить легенду и ввести с клавиатуры другие подписи.

B. Выделить легенду и воспользоваться окном "Формат легенды".

C. Выделить последовательно каждый элемент легенды и воспользоваться окном "Формат элемента легенды".

D. Нельзя изменить без изменения значений ячеек в таблице.

19. Как изменить положение диаграммы на листе?

A. Никак нельзя.

B. Перетащить за любую границу диаграммы.

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

20. Перетащить за любой маркер диаграммы.

21. Для какой цели можно использовать команду Переместить диаграмму? (выберите все правильные ответы)

A. Для перемещения диаграммы на отдельный лист данной книги.

B. Для перемещения диаграммы на другой лист данной книги.

C. Для перемещения диаграммы в другую книгу.

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

22. В какой вкладке находятся элементы для оформления диаграммы?

A. Разметка страницы.

B. Конструктор.

C. Макет.

D. Формат.

23. В какой вкладке находятся элементы для установки точного размера диаграммы?

A. Разметка страницы.

B. Конструктор.

C. Макет.

D. Формат.

ГЛАВА 10. АНАЛИЗ ДАННЫХ

Одно из наиболее важных достоинств MS Excel состоит в том, что программа позволяет легко и быстро выполнять анализ «что-если». Вы можете изменять основные переменные и сразу же видеть результаты этих изменений.

Автоматическое обновление вычислений обеспечивает интерактивную обратную связь с вашими экспериментами «что-если». Если для модели установлен автоматический пересчет, то можно изменить значение в ячейке и тут же увидеть результаты пересчета во всех ячейках, которые зависят от измененного значения.

Команда Анализ «что-если» находится на вкладке Данные в разделе команд Работа с данными и содержит три команды, показанные на рис. 10.1.

Рис. 10.1. Перечень команд анализа данных

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

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

Задание 1. Поиск заданного значения с помощью механизма подбора параметра.

Необходимо узнать количество сдаваемых в прокат видеокассет, чтобы прокатный бизнес не был убыточен. Другими словами, нужно узнать, сколько должно быть в прокате кассет, чтобы оплатить суммарные расходы и получить заданный чистый доход. Для этого можно использовать анализ «что-если» путем подбора параметра.

Ваши действия:

1. Откройте файл Упражнения.xlsx.

2. Вставьте в рабочую книгу новый лист с именем Упр.10.

3. Введите исходные данные, показанные на рис. 10.2.

4. Для расчетных полей используйте формулы:

- Суммарный доход =В2*В3

- Чистый доход =В4–В5

Рис. 10.2. Исходные данные

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

6. Перейдите на вкладку Данные и в разделе команд Работа с данными выберите команду Анализ «что-если».

7. Выберите команду Подбор параметра (см. рис. 10.1).

8. В появившемся диалоговом окне введите 0 в поле Значение (т.е. задайте то значение, которое хотите получить), а в поле Изменяя значение ячейки – $В$3 (т.е. укажите ссылку на ячейку, которую хотите изменить) (рис. 10.3).

9. Нажмите на клавишу ОК.

Рис. 10.3. Диалоговое окно Подбор параметра

10. Excel подсчитает значение с помощью анализа «что-если» и покажет вам окно результата подбора параметра (рис. 10.4).

Рис. 10.4. Диалоговое окно Результат подбора параметра

11. Если хотите обновить данные на листе, нажмите на клавишу ОК.

Обратите внимание, что при открытом диалоговом окне Результат подбора параметра результат вычислений виден в ячейке В3 – это значение 100.

12. Нажмите на клавишу Отмена, чтобы оставитьна рабочем листе исходные данные.

13. Проверьте, что активной является, по-прежнему, ячейка В6, т.е. та, в которой есть формула для расчета чистого дохода.

14. Выполните команду Подбор параметра

15. Установите в поле Значение новое число – 50000р.

16. Установите в поле Изменяя значение ячейки – $В$2. В результате Excel рассчитает новое значение Платы за прокат – это 1000р.

17. Нажмите на клавишу Отмена, чтобы снова оставитьна рабочем листе исходные данные.

Обратите внимание, что возможность отказаться от результатов подбора параметров делает этот способ удобным при анализе альтернативных вариантов.

Таблица данных с одной переменной

Необходимо узнать, как будет меняться Чистый доход при изменении Платы за прокат. Это можно сделать, используя первый способ, используя механизм подбора параметра. Однако в этом случае пришлось бы повторять всю процедуру несколько раз для каждого значения Платы за прокат. Гораздо более быстрым и простым способом является использование таблицы данных с одной переменной, которая проверяет воздействие выбранной переменной на несколько формул.

Задание 2. Создать таблицу данных для одной переменной с расположением исходных данных по строкам.

Ваши действия:

1. В столбец D (Плата за прокат) введите значения, для которых необходимо найти альтернативные варианты подсчета Чистого дохода: 1000р., 1250р. и т.д. (для ввода исходных используйте режим автозаполнения) рис. 10.5.

Рис. 10.5. Таблица данных с расположением переменных по строкам

2. В ячейку E2 введите формулу, которая возвращает значение Чистого дохода (т.е. просто нужно сослаться на соответствующую ячейку, где эта формула уже имеется - это ячейка В6).

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

3. Выделите диапазон ячеек D2:E11 (таблица должна выделяться без заголовков).

4. Откройте вкладку Данные, в группе команд Работа с данными выполните команду Анализ «что-если», затем команду Таблица данных.

5. В появившемся диалоговом окне Таблица данных в поле Подставлять значения по строкам в введите ссылку на входную ячейку, для которой должны быть изменены исходные значения в таблице, т.е. $В$2 (рис. 10.6) и нажмите на клавишу ОК.

Рис. 10.6. Диалоговое око Таблица данных для построения таблицы подстановки с одной переменной

6. Результат расчета Чистого дохода появится в столбце E (см. рис. 10.5).

Задание 3. Создать таблицу данных для одной переменной с расположением исходных данных по столбцам.

Ваши действия:

1. В ячейки В14:J14 введите значения Количества кассет от 100 до 300 с помощью режима автозаполнения с шагом 25 (рис. 10.7).

Рис. 10.7. Таблица данных с расположением

значений переменных по столбцам

Обратите внимание, что при создании таблицы данных для одной переменной, при условии, что сами значения переменной расположены в каждом новом столбце, необходимо выполнять следующее два правила: формула для расчета должна находиться ниже на одну ячейку исходных данных и левее столбца c исходными данными.

2. В ячейку А15 введите формулу для расчета Чистого дохода или ссылку на готовую формулу, например, на ячейку В6, как показано на рис. 10.7.

3. Выделите диапазон ячеек А14:I15.

4. Откройте диалоговое окно Таблица данных.

5. В поле Подставлять значения по столбцам в укажите ссылку на ячейку В3, чтобы подсчитать Чистый доход в зависимости от переменной Количество кассет (рис. 10.8).

Рис. 10.8. Диалоговое око Таблица данных для построения таблицы подстановки с одной переменной

6. Нажмите на клавишу ОК. В результате произойдет заполнение таблицы расчетными данными Чистого дохода (см. рис. 10.7).

Таблица данных с двумя переменными

Необходимо узнать, как будет изменяться Чистый доход при изменении двух переменных: Платы за прокат и Количества кассет, находящихся в прокате.

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

Задание 4. Создать таблицу данных для двух переменных.

Ваши действия:

1. В ячейки H3:P3 введите количество кассет от 100 до 300 с шагом 25, а в ячейки G4:G12 введите значения Платы за прокат от 1000р. до 3000р. с шагом 250 (рис. 10.9).

2. На пересечении исходных данных в ячейку F3 введите ссылку на формулу, расположенную в ячейке В6.

Рис. 10.9. Готовая таблица данных для двух переменных

3. Правильно выделите диапазон ячеек для построения таблицы данных – G3:P12. Это очень важный этап, т.е. необходимо захватить формулу, оба диапазона переменных и пустые ячейки, куда будут внесены расчетные значения Чистого дохода.

4. Перейдите на вкладку Данные, в разделе Работа с данными выполните команду Анализ «Что, если…».

5. Введите в поле Подставлять значения по столбцам в адрес ячейки, в которой храниться значение, определяющее количество кассет – это ячейка В3 (рис. 10.10).

Рис. 10.10. Диалоговое око Таблица данных для построения таблицы подстановки с двумя переменными

6. Введите в поле Подставлять значения по строкам в адрес ячейки, в которой хранится значение, определяющее Плату за прокат – это ячейка В2.

7. Нажмите на клавишу ОК и проанализируйте заполненную таблицу.