Как создать диаграмму Венна в Excel - скачать шаблон бесплатно

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

Диаграмма Венна - это диаграмма, которая сравнивает два или более наборов (наборов данных) и иллюстрирует различия и сходства между ними с перекрывающимися кругами. Вот как это работает: круг представляет все элементы в данном наборе, а области пересечения характеризуют элементы, которые одновременно принадлежат к нескольким наборам.

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

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

К концу этого пошагового руководства вы узнаете, как построить динамическую диаграмму Венна с двумя или тремя категориями в Excel полностью с нуля.

Начиная

Во-первых, нам нужны необработанные данные для работы. Чтобы показать вам основы, как вы, возможно, догадались из приведенного выше примера диаграммы, мы собираемся проанализировать результаты вымышленного опроса, в котором оцениваются предпочтения брендов 1624 респондентов, которые пьют колу ежедневно. Речь идет о брендах Coca-Cola, Pepsi и Dr Pepper.

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

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

Вот краткий обзор элементов таблицы:

• Марка - В этом столбце определяются наборы в опросе, а также все возможные комбинации наборов.
• Респонденты - В этом столбце представлены соответствующие значения для каждого набора в целом и каждой комбинации в частности.
• Относительная доля (необязательно) - Вы можете сравнить свои данные другими способами, например, определить относительную долю каждого набора по отношению к самому большому набору. Этот столбец существует для такой цели. Поскольку кока-кола - самый большой ребенок в округе, ячейка B2 будет нашим эталонным значением (100%).

Итак, без лишних слов, приступим к работе.

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

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

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

1. Шаг № 1 - Расчет значений диаграммы:
   2. Тип «= B2-B5-B6 + B8”В камеру D2.
   3. Тип «= B3-B5-B7 + B8”В камеру D3.
   4. Тип "= B4-B6-B7 + B8 ” в камеру D4.
2. Шаг № 2 - Расчет значений диаграммы (продолжение):
   7. Входить “= B5- $ B $ 8" в D5 и скопируйте формулу в D6 а также D7.
3. Шаг № 3 - Расчет значений диаграммы (продолжение):
   8. Установите ячейку D8 равной B8, набрав «= B8» в D8.
4. Шаг № 4 - Расположите пузыри:
   1. Добавьте на лист постоянные значения для X и Y, как показано на скриншоте выше (E2: F4).
5. Шаг № 5 - Рассчитайте размеры круга:
   1. Входить «= 250 * C2" в G2 и скопируйте его в следующие две ячейки (G3:G4).
6. Шаг № 6 - Создайте метки диаграмм:
   1. Копировать "= A2 & СИМВОЛ (10) & ТЕКСТ (D2; «#, ##»)" в H2 и скопируйте формулу в следующие две ячейки (H3: H4).

Теперь давайте разберем каждый шаг более подробно.

Шаг №1: Найдите количество элементов, принадлежащих исключительно одному набору.

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

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

Например, набор «Кока-Кола» (Би 2) включает в себя все респонденты, проголосовавшие за бренд, получили ответы из четырех разных категорий:

• Кола-Кола и Пепси
• Coca-Cola и Dr Pepper
• Coca-Cola, Pepsi и Dr Pepper
• Только Кока-Кола

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

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

• 357 для Coca-Cola
• 217 для Pepsi
• 349 для Dr Pepper

Выберите соответствующее значение из ваших фактических данных (B5) и вычтите из него значение, представляющее область, где все три круга перекрываются (B8).

Давайте узнаем, сколько респондентов на самом деле ответили "Кока-Кола и Пепси » введя эту формулу в ячейку D5:

1	= B5- $ B $ 8

Точно так же используйте эти формулы, чтобы найти значения диаграммы для категорий. «Пепси» (А3) а также «Доктор Пеппер» (A4):

• Для "Пепси," входить "= B3-B5-B7 + B8" в D3.
• Для "доктор Пеппер," тип "= B4-B6-B7 + B8" в D4.

Шаг № 2: Вычислите значения диаграммы для областей пересечения двух кругов.

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

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

Выберите соответствующее значение из ваших фактических данных (B5) и вычтите из него значение, представляющее область, где все три круга перекрываются (B8).

Давайте выясним, сколько респондентов на самом деле ответили "Кока-Кола и Пепси » введя эту формулу в ячейку D5:

1	= B5- $ B $ 8

Скопируйте формулу в ячейки D6 а также D7 перетащив маркер заполнения вниз.

Шаг № 3: Скопируйте число, связанное с областью пересечения трех наборов, в столбец «Значение диаграммы».

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

Заполните столбец значений диаграммы, скопировав количество респондентов, выбравших все три бренда, в соседнюю ячейку (D8). Просто введите «= B8" в D8.

Шаг №4: Обведите значения по осям x и y для кругов диаграммы Венна.

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

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

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

На данный момент все, что вам нужно сделать, это скопировать их в свою электронную таблицу:

Значение X	Значение Y
38	35
50	65
62	35

Для диаграмм Венна только двух категорий используйте эти координаты:

Значение X	Значение Y
39	50
66	50

Шаг № 5: Найдите значения размера круга, иллюстрирующие относительные доли (необязательно).

Если вам не нужны эти данные, перейдите к Шаг # 7.

Чтобы круги были пропорциональны опорному значению (C2), настройте столбец с надписью «Размер круга»И скопируйте эту формулу в G2.

1	= 250 * C2

Для справки: число «250”- это постоянное значение, основанное на координатах, чтобы круги перекрывались.

Выполните формулу для оставшихся двух ячеек (G3: G4), которые обозначают два других круга диаграммы, перетаскивая маркер заливки.

Шаг № 6: Создайте собственные метки данных.

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

Настройте еще один фиктивный столбец (столбец H) и введите следующую формулу в H2:

1	= A2 & СИМВОЛ (10) & ТЕКСТ (D2; "#, ##")

Выполните формулу для остальных ячеек (H3: H4).

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

Шаг № 7: Создайте пустой график рассеяния XY.

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

1. Выберите любую пустую ячейку.
2. Перейти к Вставлять таб.
3. Щелкните значок «Вставить точечную (X, Y) или пузырьковую диаграмму" значок.
4. Выбирать "Разброс.”

Шаг № 8: Добавьте данные диаграммы.

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

в Выберите источник данных диалоговом окне выберите «Добавлять.”

Оказавшись там, добавьте новую серию данных с именем «Кока-Кола:”

1. Для "Название серии,"Выделить ячейку Би 2.
2. Для "Значения серии X,"Выберите ячейку E2.
3. Для "Значения серии Y,"Выберите ячейку F2.

Используйте этот метод для создания Серии “Пепси” (B3, E3, F3) а также Серии “доктор Пеппер” (B4, E4, F4).

Шаг № 9: Измените диапазон шкалы горизонтальной и вертикальной оси.

Измените масштаб осей так, чтобы они начинались с 0 и заканчивались на 100, чтобы центрировать маркеры данных около середины области диаграммы.

Щелкните правой кнопкой мыши вертикальную ось и выберите «Ось формата.”

в Ось формата область задач, сделайте следующее:

1. Перейдите к Параметры оси таб.
2. Установить Минимальные границы к "0.”
3. Установить Максимальные границы к "100.”

Перейти к горизонтальной оси и повторить тот же процесс.

Шаг № 10: Удалите оси и линии сетки.

Очистите диаграмму, удалив оси и линии сетки. Щелкните правой кнопкой мыши каждый элемент и выберите «Удалить.”

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

Вот что у вас должно получиться - минимализм в лучшем виде:

Шаг № 11: Увеличьте маркеры данных.

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

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

На панели задач выполните следующие простые шаги:

• Щелкните значок «Заливка и линия" значок.
• Переключитесь на Маркер таб.
• Под "Параметры маркера," проверить "Встроенный"И установите Размер значение "10.”
• Под "Наполнять," Выбрать "Сплошная заливка.”
• Щелкните значок «Цвет заливки», Чтобы открыть цветовую палитру и выбрать чернить.

Промойте и повторите для каждой серии данных.

Шаг № 12: Нарисуйте круги.

Не закрывая вкладку, прокрутите вниз до Граница раздел и создайте круги для диаграммы Венна, следуя этим инструкциям:

1. Нажмите «Сплошная линия.”
2. Откройте цветовую палитру и выберите разные цвета для каждого маркера данных:
   1. Для Серии “Кока-Кола," выбирать золото.
   2. Для Серии “Пепси," Выбрать зеленый.
   3. Для Серии “Доктор Пеппер," выбирать синий.
3. Изменять "Прозрачность" к "40%.”
4. Изменять "Ширина”Для каждой серии данных к соответствующим значениям в столбец Размер круга (столбец G):
   1. Для Серии “Кока-Кола," входить «250» (G2).
   2. Для Серии “Пепси," введите “230” (G3).
   3. Для Серии “Доктор Пеппер,”Установите значение на «200» (G4).

ПРИМЕЧАНИЕ: Если вы хотите, чтобы круги были одинаковыми, просто установите Ширина значение "250" для каждого.

Шаг № 13: Измените координаты оси X и Y, чтобы настроить области перекрытия (необязательно).

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

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

Значение X	Значение Y
38	35
50	65
62	35

Это значения по осям x и y, определяющие положение первого (Серия «Кока-Кола»), второй (Серия «Пепси»), а третий (Серия «Dr Pepper») круг соответственно. Вот как это работает:

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

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

Чтобы осуществить трюк, просто измените соответствующее значение оси Y.

Использование этого метода поднимет ваши диаграммы Венна на совершенно новый уровень, сделав их невероятно универсальными.

Шаг № 14: Добавьте метки данных.

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

Повторите процесс, описанный в шагах 14 и 15 для каждой серии данных.

Во-первых, давайте добавим метки данных. Щелкните правой кнопкой мыши маркер данных, представляющий Серия «Пепси» и выберите «Добавьте метки данных.”

Шаг № 15: Настройте метки данных.

Замените значения по умолчанию на ранее созданные вами пользовательские метки. Щелкните правой кнопкой мыши любую метку данных и выберите «Отформатируйте метки данных.”

Когда появится панель задач, сделайте следующее:

1. Перейти к Параметры метки таб.
2. Нажмите «Значение из ячеек.”
3. Выделите соответствующую ячейку в столбце Ярлык (H2 для Coca-Cola, H3 для Pepsi и H4 для Dr Pepper).
4. Нажмите «OK.”
5. Снимите флажок "Значение Y" коробка.
6. Снимите флажок "Показать линии выноски" коробка.
7. Под "Положение ярлыка,”Выберите следующее:
   1. Для Серии “Пепси,"Нажмите"Выше.”
   2. Для Серии “Кока-Кола," Выбрать "Левый.”
   3. Для Серии “Доктор Пеппер," выбирать "Верно.”
8. Отрегулируйте размер / жирность / цвет шрифта под Дом > Шрифт.

Когда вы закончите этот шаг, ваша диаграмма должна выглядеть так:

Шаг №16: Добавьте текстовые поля, связанные со значениями пересечения.

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

1. Выберите область построения.
2. Перейти к Вставлять таб.
3. Щелкните значок «Текстовое окно" кнопка.
4. Создайте текстовое поле.
5. Тип "=”В Панель формул.
6. Выделите соответствующую ячейку из столбец Диаграмма Значение (столбец D).
7. Отрегулируйте размер / полужирность / цвет шрифта, чтобы они соответствовали другим меткам в Дом > Шрифт.

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

Шаг № 17: Скройте маркеры данных.

Маркеры данных, поддерживающие диаграмму, больше не нужны, поэтому давайте сделаем их невидимыми. Щелкните правой кнопкой мыши любой маркер данных, откройте Форматировать ряд данных tab и сделайте следующее:

1. Щелкните значок «Заливка и линия" значок.
2. Переключитесь на Маркер таб.
3. Под "Наполнять," выбирать "Без заливки.”

Повторите процесс для каждого маркера данных.

Шаг № 18: Добавьте заголовок диаграммы.

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

1. Выберите область построения.
2. Щелкните значок «Элементы диаграммы" значок.
3. Проверить "Заголовок диаграммы" коробка.

Измените заголовок диаграммы в соответствии с вашими данными, и ваша звездная диаграмма Венна готова!