Диаграмма в экселе как сделать: Создание диаграммы от начала до конца

Трюк №54. Три быстрых способа обновления диаграмм — MS Excel

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

Перетаскивание данных

Можно добавить данные к существующему ряду или создать абсолютно новый ряд данных, просто перетащив данные на диаграмму. Excel попытается решить, как следует обработать данные, но при этом он может добавить их ,к существующему ряду данных, тогда как вы хотели создать новый. Однако можно заставить Excel открыть диалоговое окно, в котором можно будет выбрать необходимое действие. Попробуйте добавить на лист какие-то данные (рис. 5.13).

Рис. 5.13. Данные для обыкновенной гистограммы

При помощи мастера диаграмм создайте обыкновенную гистограмму только для диапазона $A$1:$D$5 (рис. 5.14).

Рис. 5.14. Обыкновенная диаграмма только для определенного диапазона

Выделите диапазон A6:D6, правой кнопкой мыши щелкните рамку выделения и, удерживая правую кнопку, перетащите данные на диаграмму. Когда вы отпустите кнопку, появится диалоговое окно Специальная вставка (Paste Special) (рис. 5.15).

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

Выберите параметр В столбцах (Columns) и щелкните на кнопке ОК. Ряд данных для мая (May) будет добавлен на диаграмму (рис. 5.16).

Рис. 5.16. Обыкновенная гистограмма с новым рядом данных

Диалоговое окно Специальная вставка (Paste Special) выполняет большинство действий, которые нужны для этого искусного трюка.

Диаграмма и строка формул

Диаграмму можно обновить и при помощи строки формул. Выделив диаграмму и щелкнув на ней ряд данных, посмотрите на строку формул: вы увидите формулу, которую Excel использует для ряда данных. В этой формуле, которая называется функцией РЯД (SERIES), обычно указывается четыре аргумента, хотя для пузырьковой диаграммы требуется дополнительный пятый аргумент, обозначающий размер ([Size]).

Синтаксис (или порядок структуры) функции РЯД (SERIES) выглядит так: =SERIES([Name];[X Values];[Y Values];[Plot Order]), в русской версии Excel =РЯД([Имя];[Значения X];[Значения Y];[Номер графика]). Так, допустимая функция РЯД (SERIES) может выглядеть, как на рис. 5.17: =SERIES(Sheet1!$В$1;Sheet1!$А$2:$А$5;Sheet1!$В$2:$В$5;1), в русской версии Excel =РЯД(Лист1!$В$1;Лист!!$А$2:$А$5;Лист1!$В$2:$В$5;1).

На рис. 5.17 первая часть ссылки, Sheet1!$B$1, относится к имени или заголовку диаграммы — 2004. Вторая часть ссылки, Sheet1!$A$2:$A$5, относится к значениям по оси X, в данном случае — к месяцам. Третья часть ссылки, Sheet1!$B$2:$B$5, относится к значениям по оси Y, то есть 7.43, 15, 21.3 и 11.6. Наконец, последняя часть формулы, 1, относится к порядковому номеру графика, или к номеру ряда. В данном случае, когда у нас только один ряд, значение может быть равно только 1. Если бы рядов было несколько, у первого ряда был бы номер 1, у второго — номер 2 и т. д.

Рис. 5.17. Обыкновенная гистограмма с выделенной строкой формул

Чтобы изменить диаграмму, измените ссылки на ячейки в строке формул. Помимо ссылок на ячейки, в диаграммы можно вводить и явные значения, известные как массивы констант (подробнее об этом в разделе «Константы в формулах массива» справки по Excel — для вызова справки нажмите кнопку F1). Для этого добавьте {} (фигурные скобки) вокруг значений по осям X и Y, как показано в следующей формуле: =SERIES(«My Ваr»;{«А»;»В»;»С»;»D»};{1;2;3;4};1), в русской версии Excel =РЯД(«My Ваr»;{«А»;»В»;»С»;»D»};{1;2;3;4};1). В этой формуле РЯД (SERIES) А, В, С и D — это значения по оси X, а 1, 2, 3 и 4 — соответствующие им значения по оси Y. Используя этот метод, можно создавать и обновлять диаграммы, не храня данные в ячейках.

Перетаскивание граничной области

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

Рис. 5.18. Выделенная граничная область ряда диаграммы

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

Диаграмма «ящик с усами» (boxplot) в Excel 2016

Excel 2016, как известно, обогатился новыми типами диаграмм. Одна такая, которая диаграмма Парето, уже была показана. В этот раз рассмотрим другую, чисто статистическую. Называется «ящик с усами» или «коробчатая диаграмма» (box-and-whiskers plot или boxplot).

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

Зачем нужна такая диаграмма? Допустим, есть выборка для анализа. А еще лучше несколько выборок, которые нужно сравнить. Для этого рассчитывают различные показатели. Однако к любому расчету всегда хочется добавить наглядности, чтобы мозг перешел в режим образного представления, а не довольствовался сухими цифрами и формулами. Поэтому основные характеристики ловко изображают на рисунке. Отличным вариантом будет как раз диаграмма «ящик с усами».

На рисунке показан формат по умолчанию. Как видно, сравниваются две выборки путем изображения двух «ящиков с усами».

Что здесь что обозначает?

Крестик посередине – это среднее арифметическое по выборке.

Линия чуть выше или ниже крестика – медиана.

Нижняя и верхняя грань прямоугольника (типа ящика) соответствует первому и третьему квартилю (значениям, отделяющим ¼ и ¾ выборки). Расстояние между 1-м и 3-м квартилем – это межквартильный размах (или расстояние).

Горизонтальные черточки на конце «усов» – максимальное и минимальное значение (без учета выбросов, см. ниже).

Отдельные точки – это выбросы, которые показываются по умолчанию. Если значение выходит за пределы 1,5 межквартильных размаха от ближайшего квартиля, то оно считается аномальным. Их можно скрыть (см. ниже настройки).

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

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

Общий вид диаграммы настраивается стандартно. Можно менять цвет, добавлять подписи и т.д. Для этого есть две контекстные вкладки на ленте (Конструктор и Формат). Но есть настройки, предназначенные специально для этой диаграммы.

Выбираем какой-либо ряд и жмем Ctrl+1. Либо два раза кликаем по какому-нибудь «ящику». Можно через правую кнопку Формат ряда данных…. Справа вылазит панель настроек.

Рассмотрим по порядку.

Боковой зазор – регулирует ширину ящиков и расстояние между ними.

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

Показывать точки выбросов – отражать экстремальные значения.

Выбросы – это точки, выходящие за пределы 1,5 межквартильных размаха.

Показать средние метки – среднее арифметическое (крестики). Стоят по умолчанию, но можно скрыть.

Показать среднюю линию – только для различных категорий. Показывает изменения по категориям.

Если добавить линии, то изменения после эксперимента станут видны еще лучше. В справке написано, что соединяются медианы, но на графике почему-то соединяются средние. Чудеса.

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

Своевременное использование диаграммы «ящик-усы» может дать весьма ценную и наглядную информацию. Аналитику, который использует специализированные программы или трудоемкие настройки Excel, будет очень приятно иметь такую диаграмму под рукой.

Как показано в ролике ниже, все делается очень быстро и просто.

Поделиться в социальных сетях:

Как создавать диаграммы Excel (столбцы или гистограммы) с условным форматированием

В этом руководстве показано, как создавать диаграммы Excel с условным форматированием во всех версиях Excel: 2007, 2010, 2013, 2016 и 2019.

В эта статья

• Начало работы
• Шаг № 1: Подготовка данных диаграммы.
• Шаг № 2: Настройте столбчатую диаграмму.
• Шаг №3: Измените значения ширины перекрытия и зазора.
• Шаг №4: Настройте цветовую схему.

Условное форматирование — это практика назначения пользовательского форматирования ячейкам Excel — цвета, шрифта и т. д. — на основе заданных критериев (условий). Эта функция помогает анализировать данные, находить статистически значимые значения и выявлять закономерности в заданном наборе данных.

Но, к сожалению, в Excel нет встроенных инструментов для применения условного форматирования к диаграммам Excel.

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

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

Приступая к работе

Рассмотрим этот образец набора данных, содержащий данные об эффективности продаж:

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

Шаг №1: Подготовьте данные диаграммы.

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

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

Например, на снимке экрана выше видно, что показатели продаж за данный месяц должны находиться в диапазоне 9 долларов США.от 0 000 до 150 000 долларов США считается удовлетворительным.

Чтобы создать правила, используйте три пустые строки над набором данных:

• Строка 1 (метка): Используйте эти ячейки для маркировки правил форматирования.
• Строка 2 (минимальное значение): Значения в этой строке определяют нижнюю границу для каждого условия.
• Строка 3 (максимальное значение): Аналогично, эти значения устанавливают верхние границы.

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

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

C4 и скопируйте ее в D4 , перетащив маркер заполнения:

 =C1&": из "&ТЕКСТ(C2, "$#,##")&" в "&ТЕКСТ(C3, " $#,##") 

Формула составляет аккуратно выглядящую динамическую метку на основе ранее установленных правил условного форматирования. Функция ТЕКСТ форматирует значения как валюту. Но если ваш тип данных отличается, примените вместо этого эту формулу:

 =C1&": с "&ТЕКСТ(C2, "#,##")&" на "&ТЕКСТ(C3, "#,##") 

Или этот, когда вы работаете с процентами:

 =C1&" : от "&ТЕКСТ(C2, "#%")&" до "&ТЕКСТ(C3, "#%") 

Наконец, найдите значения диаграммы. Введите следующую формулу в C5 , скопируйте ее в C12 и в соответствующие ячейки в столбце D :

 =ЕСЛИ(И(C$2<$B5,$B5<=C$3),$ B5,NA()) 

Формула сравнивает заданное фактическое значение в столбец B против заданных граничных значений с помощью функций ЕСЛИ и И. Если значение попадает в диапазон, оно копируется в этот столбец. В противном случае формула возвращает ошибку #Н/Д, чтобы одно и то же значение не отображалось несколько раз.

Шаг № 2: Настройте столбчатую диаграмму.

Собрав все данные диаграммы, настройте простую столбчатую диаграмму или гистограмму в качестве альтернативы:

1. Выделите все данные диаграммы, кроме столбцов, содержащих фактические значения и правила, удерживая Клавиша Ctrl ( A4:A12 и C4:D12 ).
2. Перейдите на вкладку Вставка .
3. Выберите « Вставить столбец или гистограмму. »
4. Выберите « Кластеризованный столбец/кластеризованный столбец. »

В результате вы получите диаграмму с точками данных, построенными на основе правил условного форматирования — как и было обещано:

Шаг № 3. Измените значения параметров «Перекрытие» и «Ширина зазора».

Прежде чем закрыть это руководство, устраните проблему с неуместными столбцами, вызванными невидимыми пустыми значениями (ошибки #Н/Д), нанесенными на диаграмму.

Щелкните правой кнопкой мыши любой из столбцов и выберите « Формат данных серии » в появившемся контекстном меню.

На панели задач измените положение и ширину столбцов:

1. Перейдите на вкладку Параметры серии .
2. Измените « Series Overlap » на « 100%. »
3. Установите ширину зазора на « 60%. »

Шаг 4: Настройте цветовую схему.

Наконец, последние штрихи. Не закрывая панель задач Format Data Series , измените цветовую схему диаграммы:

1. Перейдите на вкладку
   Fill & Line .
2. В разделе « Fill » выберите « Solid fill ».
3. Щелкните значок « Цвет заливки » и выберите зеленый из цветовой палитры.

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

Как сделать сравнительную таблицу в Excel?

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

Пошаговая реализация сравнительной диаграммы в Excel

Шаг 1: Создание набора данных

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

Рис. 1. Набор данных

Шаг 2: Форматирование нашего набора данных

На этом этапе мы попробуем отформатировать наш набор данных, чтобы упростить визуализацию и понимание набора данных. У нас есть несколько названий штатов для разных городов, мы объединим название штата в одну ячейку. Для этого выберите ячейки для общего состояния, а затем на вкладке ГЛАВНАЯ щелкните параметр «Объединить и центрировать».

Мы также установим выравнивание по центру для текста.

Рис. 2. Объединение ячеек

После того, как мы щелкнем параметр «Объединить и центрировать», Excel откроет всплывающее окно с запросом на объединение ячеек, нажмите «ОК».

Рис. 3. Всплывающее окно «Объединение ячеек»

Как только мы нажмем «ОК», Excel объединит две ячейки в одну.

Рис. 4. Объединенные ячейки

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

Рис. 5. Выравнивание по центру текста ячейки

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

Рис. 6 – Выравнивание по центру

Нам нужно повторить те же шаги для оставшихся двух разных данных состояния.

Рис. 7. Набор данных

Шаг 3: Вставка пустого столбца между каждыми данными

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