Построение кольцевой диаграммы. Процентная диаграмма в Excel инструкция по созданию

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

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

Построим объемную круговую диаграмму, которая отображает загрузку производства в течение года.

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

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

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

В Excel представлены 6 типов круговых диаграмм:

Рис. Виды круговой диаграммы.

круговая – отображает вклад каждого значения в общую сумму;

объемная круговая;

вторичная круговая – часть значений основной диаграммы вынесена на вторую диаграмму;

разрезанная круговая – секторы значений отделены друг от друга;

объемная разрезанная круговая;

вторичная гистограмма – часть значений основной диаграммы вынесена в гистограмму.

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

Рис.

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

Рис. Изменение вида круговой диаграммы.

На круговой диаграмме секторы можно поворачивать на 360 по кругу. Для этого следует выбрать на ленте вкладку Макет и пункт Формат выделенного .

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

Рис. Вторичная диаграмма.

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

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

Человеческое сознание устроено таким образом, что ему легче воспринимать визуальные объекты, в противном случае их приходится представлять. Именно этот факт обусловил широкое распространение диаграмм. Для их создания не нужно устанавливать отдельные программы, достаточно запомнить, Excel .

Процесс создания диаграммы

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

Далее, необходимо выделить таблицу вместе с названиями столбцов и колонок, а потом перейти ко вкладке “Вставить”, выбрав там пункт “Диаграммы”. Будет предложено несколько вариантов, в меню “Круговая” доступно 4 типа: два последних применяются для сложных наборов данных, в которых одни показатели зависят от других, для простых таблиц используют первую и вторую диаграммы, отличаются они лишь наличием разрезов. После нажатия на иконку появится диаграмма.

Изменение данных и внешнего вида

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

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

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

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

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

Создание диаграмм

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

2. Перейти на вкладку «Вставка», и в разделе «Диаграммы» щёлкнуть желаемый вид.

3. Как видно, в разделе «Диаграммы» пользователю на выбор предлагаются разные виды диаграмм. Иконка рядом с названием визуально поясняет, как будет отображаться диаграмма выбранного вида. Если щёлкнуть любой из них, то в выпадающем списке пользователю предлагаются подвиды.

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

Если пользователю нужен первый из предлагаемых вариантов – гистограмма, то, вместо выполнения пп. 2 и 3, он может нажать сочетание клавиш Alt+F1.

4. При внимательном взгляде на подвиды можно заметить, что все относятся к одному из двух вариантов. Они отличаются сплошной (в зелёном прямоугольнике), или частичной (в оранжевом) закраской элементов диаграммы. Следующие два скриншота, соответствующие «зелёному» и «оранжевому» выбору, наглядно демонстрируют разницу.

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

В обоих случаях значение процента почти не видно. Это связано с тем, что на диаграммах отображается абсолютное его значение (т.е. не 14,3%, а 0,143). На фоне больших значений такое малое число еле видно.

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

Редактирование диаграмм

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

Вкладка «Конструктор»

Для отображения значений процентов часто применяется круговая диаграмма в эксель. Чтобы построить круговую диаграмму, сохранив прежние данные, нужно щёлкнуть первый слева инструмент линейки – «Изменить тип диаграммы», и выбрать желаемый подвид строки «Круговая».

На следующем скриншоте показан результат активации инструмента «Строка/Столбец», производящий взаимную замену данных на осях X и Y. Как видно, однотонная гистограмма предыдущего скриншота получила цвета, и стала гораздо привлекательней.

В разделе «Стили диаграмм» вкладки «Конструктор» можно менять стиль диаграмм. После открытия выпадающего списка этого раздела пользователю становится доступным выбор одного из 40 предлагаемых вариаций стилей. Без открытия этого списка доступно всего 4 стиля.

Очень ценен последний инструмент – «Переместить диаграмму». С его помощью диаграмму можно перенести на отдельный полноэкранный лист.

Как видно, лист с диаграммой добавляется к существовавшим листам.

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

Вкладки «Макет» и «Формат»

Инструменты вкладок «Макет» и «Формат» в основном относятся к внешнему оформлению диаграммы.

Чтобы добавить название, следует щёлкнуть «Название диаграммы», выбрать один из двух предлагаемых вариантов размещения, ввести имя в строке формул, и нажать Enter.

При необходимости аналогично добавляются названия на оси диаграммы X и Y.

Инструмент «Легенда» управляет отображением и положением поясняющего текста. В данном случае это – названия месяцев. Их можно удалить или переместить влево, вверх или вниз.

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

Если при построении диаграммы был выбран её объёмный вариант, то на вкладке «Макет» будет активен инструмент «Поворот объёмной фигуры». С его помощью можно менять угол наблюдения диаграммы.

Инструмент «Заливка фигуры» вкладки «Формат» позволяет залить любым цветом, рисунком, градиентом или текстурой фон диаграммы (как показано на скриншоте) или любой из её элементов (в данном случае – столбиков).

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

Добавление новых данных

После создания диаграммы для одного ряда данных в некоторых случаях бывает необходимо добавить на диаграмму новые данные. Для этого сначала нужно будет выделить новый столбик – в данном случае «Налоги», и запомнить его в буфере обмена, нажав Ctrl+C. Затем щёлкнуть на диаграмме, и добавить в неё запомненные новые данные, нажав Ctrl+V. На диаграмме появится новый ряд данных «Налоги».

Новые возможности диаграмм в Excel 2013

Диаграммы рассматривались на примере широко распространённой версии Excel 2010. Так же можно работать и в Excel 2007. А вот версия 2013 года имеет ряд приятных нововведений, облегчающих работу с диаграммами :

• в окне вставки вида диаграммы введён её предварительный просмотр в дополнение к маленькой иконке;
• в окне вставки вида появился новый тип – «Комбинированная», сочетающий несколько видов;
• в окне вставки вида появилась страница «Рекомендуемые диаграммы», которые версия 2013 г. советует, проанализировав выделенные исходные данные;
• вместо вкладки «Макет» используются три новые кнопки – «Элементы диаграммы», «Стили диаграмм» и «Фильтры диаграммы», назначение которых ясно из названий;
• настройка дизайна элементов диаграммы производится посредством удобной правой панели вместо диалогового окна;
• подписи данных стало возможным оформлять в виде выносок и брать их прямо с листа;
• при изменении исходных данных диаграмма плавно перетекает в новое состояние.

Видео: Построение диаграмм в MS Office Excel

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

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

Как построить круговую диаграмму в Excel

Составим для учебных целей простую табличку:

Нам необходимо наглядно сравнить продажи какого-либо товара за 5 месяцев. Удобнее показать разницу в «частях», «долях целого». Поэтому выберем тип диаграммы – «круговую».

Одновременно становится доступной вкладка «Работа с диаграммами» - «Конструктор». Ее инструментарий выглядит так:

Что мы можем сделать с имеющейся диаграммой:

Попробуем, например, объемную разрезанную круговую.

На практике пробуйте разные типы и смотрите как они будут выглядеть в презентации. Если у Вас 2 набора данных, причем второй набор зависим от какого-либо значения в первом наборе, то подойдут типы: «Вторичная круговая» и «Вторичная гистограмма».

Использовать различные макеты и шаблоны оформления.

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

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

Создать круговую диаграмму в Excel можно от обратного порядка действий:

Если выбор программы не совпадает с задуманным нами вариантом, то выделяем элемент легенды и нажимаем «Изменить». Откроется окно «Изменение ряда», где «Имя ряда» и «Значения» - ссылки на ячейки (ставим те, которые нужны) и жмем ОК.

Как изменить диаграмму в Excel

Все основные моменты показаны выше. Резюмируем:

1. Выделить диаграмму – перейти на вкладку «Конструктор», «Макет» или «Формат» (в зависимости от целей).
2. Выделить диаграмму либо ее часть (оси, ряды) – щелкнуть правой кнопкой мыши.
3. Вкладка «Выбрать данные» - для изменения названий элементов, диапазонов.

Все изменения и настройки следует выполнять на вкладках «Конструктор», «Макет» или «Формат» группы инструментов «Работа с диаграммами». Группа инструментов появляется в заголовке окна как дополнительное меню при активации графической области.

Круговая диаграмма в процентах в Excel

Простейший вариант изображения данных в процентах:

1. Создаем круговую диаграмму по таблице с данными (см. выше).
2. Щелкаем левой кнопкой по готовому изображению. Становится активной вкладка «Конструктор».
3. Выбираем из предлагаемых программой макетов варианты с процентами.

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

Второй способ отображения данных в процентах:

Результат проделанной работы:

Как построить диаграмму Парето в Excel

Вильфредо Парето открыл принцип 80/20. Открытие прижилось и стало правилом, применимым ко многим областям человеческой деятельности.

Согласно принципу 80/20, 20% усилий дают 80% результата (только 20% причин объяснят 80% проблем и т.д.). Диаграмма Парето отражает данную зависимость в виде гистограммы.

Построим кривую Парето в Excel. Существует какое-то событие. На него воздействует 6 причин. Оценим, какая из причин оказывает большее влияние на событие.

Получилась диаграмма Парето, которая показывает: наибольшее влияние на результат оказали причина 3, 5 и 1.

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

Для построения круговой диаграммы надо выделить на свёрнутой таблице листа Итоги столбцы Период и Сумма к выплате (диапазон D1:Е15).

В меню Вставка, в разделе Диаграммы выбрать Круговая

в появившемся окне выбрать тип диаграммы – Круговая.

На листе Итоги п оявляется круговая диаграмма. Для отображения процентного соотношения Суммы к выплате по кварталам следует:

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

Снова щёлкнуть правой кнопкой мыши по диаграмме и выбрать в списке Формат подписей данных. В открывшемся окне установить:

- «Параметры подписи» - доли,

- «Положение подписи» - У вершины, снаружи.

Чтобы расположить эту диаграмму на отдельном листе, надо в меню Вставка в разделе Расположение щёлкнуть Переместить диаграмму. В открывшемся окне отметить «на отдельном листе », нажать ОК. Назвать лист Круговая.

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

П
остроим гистограмму, изобразив на ней по периодамСумму к выплате, Оплачено и Долг. Порядок создания диаграммы как в предыдущем задании. Однако здесь удобно скрыть столбец Разница на листе Итоги . Для этого активизируем любую ячейку этого столбца. Выполним команду Формат/Столбец/Скрыть. Выделим необходимые столбцы:

Построим гистограмму:

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

Затем выполнить команду правой кнопкой мыши Изменить тип диаграммы для ряда. Выбрать закладку График, а после этого из списка выбрать График. Диаграмма примет такой вид.

Для линейного графика удобно создать дополнительную ось Y-ов справа на графике. Это тем более необходимо, если значения для линейного графика несоизмеримы со значениями других столбцов гистограммы. Щёлкнуть по линейному графику и выполнить команду Формат ряда данных.

В открывшемся окне: открыть закладку Параметры ряда и установить флажок по вспомогательной оси . После этого на графике появится дополнительная ось Y – (справа). Нажать кнопку ОК .

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

Фильтрация (выборка) данных

Перейти на лист Автофильтр . Отфильтровать данные в поле Период по значению 1 кв и 2 кв , в поле Долг вывести значения, не равные нулю.

Выполнение. Сделать активной любую ячейку таблицы листа Автофильтр . Выполнить команду Данные /Фильтр/ Сортировка и фильтр У каждого столбца таблицы появится стрелка. Раскроем список в заголовке столбца Период и выберем Текстовые фильтры , затем равно . Появится окно Пользовательский автофильтр , в котором выполним установки:

В заголовке столбца Долг выберем из списка Числовые фильтры , затем Настраиваемый фильтр . Откроется окно Пользовательский автофильтр , в котором сделаем установки:

После этого получим:

Расширенный фильтр

Команда Расширенный фильтр (дополнительный) , в отличие от команды Фильтр , требует задания условий отбора строк в отдельном диапазоне рабочего листа или на другом листе. Диапазон условий включает в себя заголовки столбцов условий и строки условий. Заголовки столбцов в диапазоне условий должны точно совпадать с заголовками столбцов в исходной таблице. Поэтому заголовки столбцов для диапазона условий лучше копировать из таблицы. В диапазон условий включаются заголовки только тех столбцов, которые используются в условиях отбора. Если к одной и той же таблице надо применить несколько диапазонов условий, то диапазонам условий (как именованным блокам) удобно присвоить имена. Эти имена затем можно использовать вместо ссылок на диапазон условий. Примеры диапазонов условий (или критериев отбора):

Если условия расположены в разных строках, то это соответствует логическому оператору ИЛИ . Если Сумма к выплате больше 100000, а Адрес – любой (первая строка условия). ИЛИ если Адрес - Пермь, а Сумма к выплате – любая, то из списка будут отобраны строки, удовлетворяющие одному из условий.

Другой пример диапазона условий (или критерия отбора):

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

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

Создадим новый лист Фильтр.

Пример 1 . Из таблицы на листе Рабочая_ведомость с помощью расширенного фильтра отобрать записи, у которых Период – 1 кв и Долг+Пеня >0. Результат нужно получить в новой таблице на листе Фильтр .

На листе Фильтр для вывода результата фильтрации создадим шапку таблицы копированием заголовков из таблицы Рабочая_ведомость . Если выделяемые блоки несмежные, то при выделении применить клавишу Ctrl . Расположить, начиная с ячейки А5 :

Н
а листеФильтр создадим диапазон условий в верхней части листа Фильтр в ячейках А1:В2 . Названия полей и значения периодов обязательно копировать с листа Рабочая_ведомость .

Условие1 .

Выполним команду: Данные/Сортировка и Фильтр/ Дополнительно . Появится диалоговое окно:

Исходный диапазон и диапазон условий вставьте с помощью клавиши F 3 .

Установить флажок скопировать результат в другое место. Поместить полученные результаты на листе Фильтр в диапазон А5:С5 (выделить ячейки А5:С5 ). Получим результат:

Пример 2. Из таблицы на листе Рабочая_ведомость с помощью расширенного фильтра отобрать строки с адресом Омск за 3 кв с суммой к выплате больше 5000 и с адресом Пермь за 1 кв с любой суммой к выплате. На листе Фильтр создадим диапазон условий в верхней части листа в ячейках D 1: F 3 .

Присвоим имя этому диапазону условий Условие_2 .

Названия полей и значения периодов обязательно копировать с листа Рабочая ведомость . Затем выполнить команду Данные/Сортировка и Фильтр/Дополнительно .

В диалоговом окне сделать следующие установки:

Получим результат:

Пример 3 . Выбрать сведения о заказчиках с кодами - К-155, К-347 и К-948 , долг которых превышает 5000.

На листе Фильтр в ячейках H 1: I 4 создадим диапазон условий с именем Условие3.

Названия полей обязательно копировать с листа Рабочая_ведомость .

После выполнения команды Данные/ Сортировка и Фильтр/ Дополнительно в диалоговом окне сделать следующие установки:

Получим результат:

Вычисляемые условия

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

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

В ячейку, где формируется критерий, вводится знак «=»(равно).

Затем вводится формула, которая вычисляет логическую константу (ЛОЖЬ или ИСТИНА).

Пример 4 . Из таблицы на листе Рабочая ведомость отобрать строки, в которых значения Оплачено больше среднего значения по этому столбцу. Результат получить на листе Фильтр в новой таблице:

На листе Фильтр создадим «шапку» новой таблицы копированием с листа Рабочая ведомость .

Для удобства создания вычисляемого условия расположим на экране два окна: одно – лист Рабочая ведомость , другое – лист Фильтр. Для этого выполним команду Вид/ Окно/Новое окно . Затем команду Вид/ Окно/Упорядочить всё . Установим флажок слева направо . На экране появятся два окна, в первом из которых расположим лист Рабочая ведомость , а во втором – лист Фильтр. Благодаря этому удобно создавать формулу для критерия отбора на листе Фильтр .

Сделаем активной ячейку E 22 листа Фильтр, создадим в ней выражение:

Введем знак = (равно), щёлкнем по ячейке F 2 на листе Рабочая ведомость (F 2 - первая ячейка столбца Оплачено).

Введем знак >(больше).

С помощью мастера функций введём функцию СРЗНАЧ .

В окне аргументов этой функции укажем диапазон ячеек F 2: F 12 (выделим его на листе Рабочая ведомость ). Так как диапазон, для которого находим СРЗНАЧ , не меняется, то адреса диапазона должны быть абсолютными, то есть $ F $2:$ F $12 . Знак $ можно установить с помощью функциональной клавиши F 4 . В окне функции СРЗНАЧ нажать ОК.

Для проверки выполнения условия со средним значением сравнивается значение каждой ячейки столбца F . Поэтому в левой части неравенства адрес F 2 – относительный (он меняется). СРЗНАЧ в правой части неравенства – величина постоянная . Поэтому диапазон ячеек для этой функции имеет абсолютные адреса $ F $2:$ F $12 .

В ячейке E 22 листа Фильтр сформируется константа Истина или Ложь :

Сделаем активной любую свободную ячейку листа Фильтр и выполним команду Данные/Сортировка и Фильтр/Дополнительно .

В диалоговом окне сделаем установки. Исходный диапазон определим клавишей F 3 . Для ввода диапазона условий выделим ячейки Е21:Е22 листа Фильтр (заголовок столбца вычисляемого условия не заполняется, но выделяется вместе с условием). Для диапазона результата выделим ячейки А21:С21 на листе Фильтр.

Получим результат: