Построение диаграммы по таблице в microsoft excel

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

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

Вариант 1: Построение диаграммы по таблице

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

1. Перед тем как приступить к созданию любой диаграммы, необходимо построить таблицу с данными, на основе которой она будет строиться. Затем переходим на вкладку «Вставка» и выделяем область таблицы, которая будет выражена в диаграмме.

«ТОРНАДО» ПРИБЫЛИ

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

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

Когда нужно сравнить два показателя за несколько месяцев или лет, используют диаграмму типа «Торнадо» (рис. 1).

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

• наибольший приток прибыли в компанию обеспечивает ценовой сегмент «Стандарт». Однако даже в самые низкоприбыльные периоды прибыль от сегмента «Элит» не опускается ниже 400 тыс. руб. в месяц, что говорит о стабильных позициях компании в указанном сегменте;
• за отчетный год компания заработала максимальную прибыль в июле: «Стандарт» — 2100 тыс. руб., «Элит» — 1300 тыс. руб. Причины:

– сезонность;

– результаты профессиональной маркетинговой компании или активных продаж;

– заключение нового договора;

– оптовые поставки крупному, но непостоянному клиенту;

минимальный приток прибыли с обоих сегментов в ноябре и апреле. Типичные причины:

– закрытие торговых точек;

– неудачная политика скидок;

– сезонный спад продаж;

– снижение цен в ответ на демпинг конкурентов в указанные периоды.

Построение диаграммы «Торнадо»

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

Шаг 1. Готовят таблицу с исходными данными.

В стандартной линейчатой диаграмме линии направлены в одну сторону. Чтобы линии в диаграмме расходились в разные стороны, нужно, чтобы один из столбцов имел отрицательные значения. На рис. 1 это сегмент «Элит». В таблицу 1 вносят данные по прибыли в столбцы «Сегмент “Стандарт”» и «Сегмент “Элит” Вспом.».

Для построения диаграммы используют зеленый столбец «Элит», где данные из вспомогательного столбца умножены на минус 1. Чтобы скрыть минусы, в Формате ячеек настроен тип «#;#;0» (рис. 2). Это позволит убрать минусы перед числами в таблице и диаграмме, представленной на рис. 1.

Шаг 2. Вставляют линейчатую диаграмму с накоплением. Для этого выполняют команду: Вставка → Диаграммы → Линейчатая с накоплением. По умолчанию диаграмма имеет вид, как на рис. 3.

Шаг 3. Форматируют нужный вид диаграммы.

Вертикальную ось с названиями месяцев переносят вправо. Для этого вызывают контекстное меню горизонтальной оси и выбирают Формат оси, а в Параметрах оси — обратный порядок значений (рис. 4). Кроме этого, в группе «Вертикальная ось пересекает» выбирают Максимальное значение по оси. Затем горизонтальную ось удаляют. Легенду смещают вниз.

Для увеличения ширины линеек выполняют команду: Формат ряда данных → Параметры ряда → Боковой зазор 40 %. Добавляют подписи данных. Настраивают шрифты и заливку цветом согласно корпоративным стандартам. Получают диаграмму, как на рис. 1.

Построение гистограммы с двумя рядами данных

Перед тем, как приступать к редактированию гистограммы, следует ввести исходные данные. Их лучше расположить столбцами.

Подготовленные данные для диаграммы лучше расположить столбцами

После того, как с заполнением столбцов будет покончено, понадобиться сделать следующее:

1. Кликните правой кнопкой мыши по области построения диаграммы. В открывшемся контекстном меню выберите пункт «Выбрать данные».

2. Очистите поле «Диапазон данных для диаграммы» (там могут быть старые данные или предложенные по умолчанию), после чего нажмите на кнопку «Добавить» над левым рабочим окном («Элементы легенды»).

3. Откроется окно «Изменение ряда». В его верхнюю строку введите название первого массива данных. Затем очистите второю строку (по умолчанию там стоит единица) и выберите первый столбец на рабочем листе. Ссылка на него добавится во вторую строку окна. Нажмите «ОК».

4. Снова нажмите на кнопку «Добавить». Откроется окно «Изменение ряда». В его верхнюю строку введите название второго массива данных. Затем очистите второю строку (по умолчанию там стоит единица) и выберите второй столбец на рабочем листе. Ссылка на него добавится во вторую строку окна. Нажмите «ОК».

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

6. Откроется окно «Подписи оси» с единственной строкой. Установив в ней курсор, выделите необходимые ячейки. Подписи тут же добавятся на гистограмму. Кликните «ОК».

7. Проверьте корректность введенной информации в окне «Выбор источника данных» и вновь нажмите на кнопку «ОК».

8. Перейдите на вкладку «Работа с диаграммами». В ней Вы найдете три дополнительных меню:

• в меню «Конструктор» можно изменить стиль диаграммы (попробуйте использовать другую цветовую тему);

• меню «Макет» поможет добавить заголовок, подписать оси и выполнить простое форматирование области построения;

• в меню «Формат» можно изменить шрифты и размер диаграммы, выполнить другие базовые операции.

На этом построение гистограммы закончено. В таком виде ее можно добавить в презентацию PowerPoint, на веб-ресурс, в текстовый документ и т. д.

Изменение макета и стиля диаграммы

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

Применение готового макета

1. Кликните по диаграмме, которую вы хотите изменить, и перейдите во вкладку «Конструктор», расположенную в основной вкладке «Работа с диаграммами».

Выберите макет, который вы хотите использовать (группа «Стили диаграмм»), после чего он будет успешно изменен.

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

Применение готового стиля

1. Кликните по диаграмме, к которой вы хотите применить готовый стиль, и перейдите во вкладку «Конструктор».
2. В группе «Стили диаграмм» выберите тот, который хотите использовать для своей диаграммы
3. Изменения сразу же отразятся на созданном вами объекте.

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

Ручное изменение макета

1. Кликните мышкой по диаграмме или отдельному элементу, макет которого вы хотите изменить. Сделать это можно и по-другому:
   • Кликните в любом месте диаграммы, чтобы активировать инструмент «Работа с диаграммами».
   • Во вкладке «Формат», группа «Текущий фрагмент» нажмите на стрелку рядом с пунктом «Элементы диаграммы», после чего можно будет выбрать необходимый элемент.
2. Во вкладке «Конструктор» в группе «Макеты диаграмм» кликните по первому пункту — «Добавить элемент диаграммы».

В развернувшемся меню выберите, что вы хотите добавить или изменить.

Примечание: Параметры макета, выбранные и/или измененные вами, будут применены только к выделенному элементу (части объекта). В случае если вы выделили всю диаграмму, к примеру, параметр «Метки данных» будет применен ко всему содержимому. Если же выделена лишь точка данных, изменения будут применены исключительно к ней.

Ручное изменение формата элементов

1. Кликните по диаграмме или ее отдельному элементу, стиль которого вы хотите изменить.

Перейдите во вкладку «Формат» раздела «Работа с диаграммами» и выполните необходимое действие:

Для форматирования выделенного элемента диаграммы выберите «Формат выделенного фрагмента» в группе «Текущий фрагмент». После этого можно будет установить необходимые параметры форматирования.

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

Для форматирования текста выберите желаемый стиль в группе «Стили WordArt». Здесь же можно выполнить «Заливку текста», определить «Контур текста» или добавить специальные эффекты.

Сохранение в качестве шаблона

Нередко бывает так, что созданная вами диаграмма может понадобиться в дальнейшем, точно такая же или ее аналог, это уже не столь важно. В данном случае лучше всего сохранять полученный объект в качестве шаблона, упростив и ускорив таким образом свою работу в будущем

Для этого:

1. Кликните по диаграмме правой кнопкой мышки и выберите в контекстном меню пункт «Сохранить как шаблон».

В появившемся окне системного «Проводника» укажите место для сохранения и задайте желаемое имя файлу.

Нажмите по кнопке «Сохранить» для подтверждения.

Настройка диаграммы с областями

1. Откройте файл в Google Таблицах на компьютере.
2. Дважды нажмите на нужную диаграмму.
3. Перейдите на вкладку Дополнительные на панели справа.
4. Разверните нужный раздел:
   • Стиль диаграммы. Здесь можно настроить внешний вид диаграммы.
   • Названия диаграмм и осей. В этом разделе можно отредактировать и отформатировать заголовки.
   • Серии. Здесь можно изменить цвета линий и расположение оси, а также добавить планки погрешностей, ярлыки данных и линию тренда.
   • Легенда. В этом разделе можно изменить расположение легенды и отформатировать ее текст.
   • Горизонтальная ось. Здесь можно отредактировать и отформатировать текст ярлыков, а также задать обратное направление оси.
   • Вертикальная ось. В этом разделе можно отредактировать и отформатировать текст ярлыков, установить минимальное и максимальное значения оси, а также создать логарифмическую шкалу.
   • Сетка. Здесь можно добавить линии сетки и настроить их.

Как отобразить диаграмму в процентах

Для отображения процентного соотношения рекомендуется создавать круговую диаграмму. Создаем таблицу, выделяем данные, при выборе диаграмма указываем «Круговая». Мы оказываемся во вкладке «Конструктор», где необходимо выбрать макет, на котором присутствуют символы процента.

И теперь все данные отображаются в проценте соотношении.

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

Элементарный график изменения

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

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

Затем отправьтесь к вкладке «Вставка», где у вас есть возможность осуществить выбор типа графика, который будет подходящим в конкретной ситуации.

2

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

После выбора нужного вида диаграммы вам необходимо скопировать таблицу данных связать ее с графиком. Результат будет следующим.

В нашем случае на диаграмме представлено две линии. Первая имеет красный цвет. Вторая – синий. Последняя нам не нужна, поэтому мы можем удалить ее, выбрав ее и нажав кнопку «Удалить». Поскольку мы имеем лишь одну линию, легенда (блок с названиями отдельных линий графика) также может быть удалена. Но маркеры лучше назвать. Найдите панель «Работа с диаграммами» и блок «Подписи данных» на вкладке «Макет». Здесь вы должны определить положение чисел.

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

Но вы можете смело обходиться без заголовка. Чтобы удалить его, вам нужно переместить его в область графика, которая невидима для постороннего глаза (над ним). Если вам все еще нужно название диаграммы, вы можете получить доступ ко всем необходимым настройкам через меню «Название диаграммы» на той же вкладке. Вы также можете найти его на вкладке «Макет».

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

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

Типы диаграмм

График

Идеально подходить для отображения изменения объекта во времени и определения тенденций.Пример отображения динамики затрат и общей выручки компании за год:

Гистограмма

Хорошо подходит для сравнения нескольких объектов и изменения их отношения со временем.Пример сравнения показателя эффективности двух отделов поквартально:

Круговая

Предназначения для сравнения пропорций объектов. Не может отображать динамику.Пример доли продаж каждой категории товаров от общей реализации:

Диаграмма с областями

Подходит для отображения динамики различий между объектами во времени

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

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

Так как для нас первостепенно видеть именно потенциал, то данный ряд отображается первым. Из ниже приведенной диаграммы видно, что с 11 часов до 16 часов отдел не справляет с потоком клиентов.

Точечная

Представляет собой систему координат, где положение каждой точки задается значениями по горизонтальной (X) и вертикальной (Y) осям. Хорошо подходить, когда значение (Y) объекта зависит от определенного параметра (X).

Пример отображения тригонометрических функций:

Поверхность

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

Биржевая

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

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

Лепестковая

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

На ниже приведенной диаграмме представлено сравнение 3-х организаций по 4-ем направлениям: Доступность; Ценовая политика; Качество продукции; Клиентоориентированность. Видно, что компания X лидирует по первому и последнему направлению, компания Y по качеству продукции, а компания Z предоставляет лучшие цены.

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

Построение диаграммы на основе таблицы

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

1. Прежде, чем приступать к построению любой диаграммы, необходимо создать таблицу и заполнить ее данными. Будущая диаграмма будет построена на основе именно этой таблицы.
2. Когда таблица будет полностью готова, необходимо выделить область, которую требуется отобразить в виде диаграммы, затем перейти во вкладку “Вставка”. Здесь будут представлены для выбора разные типы диаграмм:
   • Гистрограмма
   • График
   • Круговая
   • Иерархическая
   • Статистическая
   • Точечная
   • Каскадная
   • Комбинированная

   Примечание:В зависимости от версии программы количество разновидностей диаграмм может быть разным.

   Также, существуют и другие типы диаграмм, но они не столь распространённые. Ознакомиться с полным списком можно через меню “Вставка” (в строке меню программы в самом верху), далее пункт – “Диаграмма”.

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

   Диаграмма в виде графика будет отображается следующим образом:

   А вот так выглядит круговая диаграмма:

Как в Excel создать гистограмму с переменными шириной и высотой?

Разве не удобно было бы создавать гистограммы, столбцы которых могли бы иметь переменную ширину и высоту? Ширина и высота столбцов одновременно регулировались бы при построении графика, по вашим данным.

Захватите мышкой кончик стрелки, и не отпуская, переместите его.

Меняем цвет блоков диаграммы

Таким же образом можно изменить цвет Легенды, и блока Название диаграммы.

Щелкнув по маленькому треугольнику в шапке таблицы, можно изменить Сортировку списка, или отключить ненужные Текстовые фильтры.

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

Если необходимо добавить еще один блок в таблице, то выделяем и копируем уже готовый блок, и вставляем его в столбец рядом.

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

Как сделать сводную диаграмму

Данный инструмент более сложный по сравнению с описанными выше. Ранее всё происходило автоматически. Вам нужно было только выбрать внешний вид и желаемый тип. Здесь всё иначе. На этот раз придется всё делать вручную.

1. Выделите нужные ячейки в таблице и кликните на соответствующую иконку.

1. Сразу после этого появится окно «Создать сводную диаграмму». Необходимо указать:
   • таблицу или диапазон значений;
   • место, где следует разместить объект (на новый или текущий лист).
2. Для продолжения нажмите на кнопку «OK».

1. В результате этого вы увидите:
   • пустую сводную таблицу;
   • пустую диаграмму;
   • поля сводной диаграммы.

1. Необходимо перенести мышкой желаемые поля в области (по своему усмотрению):
   • легенды;
   • оси;
   • значений.

1. Кроме этого, вы можете настроить, какое именно значение нужно выводить. Для этого сделайте правый клик мыши по каждому полю и нажмите на пункт «Параметры полей значений…».

1. В результате этого появится окно «Параметры поля значений». Здесь вы сможете:
   • подписать источник своим имением;
   • выбрать операцию, которую следует использовать для сведения данных в выбранном поле.

Для сохранения нажмите на кнопку «OK».

Вкладка «Анализировать»

После того как вы создадите сводную диаграмму, у вас откроется новая вкладка «Анализировать». Она сразу же исчезнет, если активным станет другой объект. Для возврата достаточно снова кликнуть на диаграмму.

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

Параметры сводной таблицы

1. Нажмите на самую первую иконку.
2. Выберите пункт «Параметры».

1. Благодаря этому появится окно настроек данного объекта. Здесь вы сможете задать желаемое имя таблицы и множество других параметров.

Для сохранения настроек необходимо кликнуть на кнопку «OK».

Как изменить активное поле

Если вы кликните на эту иконку, то увидите, что все инструменты не активны.

Для того чтобы можно было изменить какой-нибудь элемент, нужно сделать следующее.

1. Кликните на что-нибудь на вашей диаграмме.

1. В результате этого данное поле выделится «кружочками».
2. Если вы снова кликните на иконку «Активное поле», то увидите, что инструменты стали активными.

1. Для настроек нужно кликнуть на соответствующее поле.

1. В результате этого появится окно «Параметры поля».

1. Для дополнительных настроек перейдите на вкладку «Разметка и печать».

1. Чтобы сохранить внесенные изменения, необходимо нажать на кнопку «OK».

Как вставить срез

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

1. Кликните на кнопку «Вставить срез».
2. В результате этого появится окно со списком полей, которые есть в сводной таблице.

1. Выберите какое-нибудь поле и нажмите на кнопку «OK».

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

1. Если вы нажмете на какую-нибудь строчку, то увидите, что все остальные записи в таблице исчезли. Осталось только то, где среднее значение соответствует выбранному.

1. Если кликнуть на другую цифру, то результат сразу же изменится.

1. Количество строк может быть абсолютно любым (минимум одна).

1. Если хотите удалить срез, нужно кликнуть на крестик в правом верхнем углу.

1. Благодаря этому таблица восстановится до исходного вида.

Для того чтобы убрать это окно срезов, необходимо сделать несколько несложных шагов:

1. Кликните правой кнопкой мыши по данному элементу.
2. После этого появится контекстное меню, в котором нужно выбрать пункт «Удалить ‘название поля’».

Результат будет следующим

Обратите внимание на то, что в правой части редактора снова появилась панель для настройки полей сводной таблицы.

Как вставить временную шкалу

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

1. Кликните на соответствующую кнопку.

1. В нашем случае мы увидим следующее окно с ошибкой.

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

Для обновления информации в таблице нужно кликнуть на соответствующую кнопку.

Как изменить информацию для построения

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

1. Кликните на иконку «Источник данных».
2. В появившемся меню выберите одноименный пункт.

1. Далее вас попросят указать нужные ячейки.
2. Для сохранения изменений кликните на «OK».

От: admin

Эта тема закрыта для публикации ответов.