Як створити діаграму Венна в Excel - безкоштовне завантаження шаблону

Цей підручник продемонструє, як створити діаграму Венна у всіх версіях Excel: 2007, 2010, 2013, 2016 та 2022 роки.

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

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

На жаль, цей тип діаграми не підтримується в Excel, тому вам доведеться будувати його з нуля самостійно. Також не забудьте ознайомитися з нашою надбудовою Chart Creator-інструментом для створення приголомшливих розширених діаграм Excel, ледве піднявши палець.

Наприкінці цього покрокового підручника ви дізнаєтесь, як повністю з нуля побудувати в Excel динамічну діаграму Венна з двома чи трьома категоріями.

Починаємо

По -перше, нам потрібні деякі необроблені дані для роботи. Щоб показати вам мотузки, як ви могли здогадатися з наведеного вище прикладу, ми збираємось проаналізувати результати фіктивного опитування, щоб оцінити переваги бренду 1624 респондентів, які щодня п’ють колу. Розглядаються такі марки: Coca-Cola, Pepsi та Dr Pepper.

Серед усіх цих любителів безалкогольних напоїв деякі виявилися євангелістами брендів, які дотримувалися виключно однієї марки, а інші обрали відразу дві або три марки.

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

Ось короткий огляд елементів таблиці:

• Бренд - Цей стовпець визначає набори в опитуванні, а також усі можливі комбінації наборів.
• Респонденти - Цей стовпець представляє відповідні значення для кожного набору загалом та кожної комбінації зокрема.
• Відносна частка (необов’язково) - Ви можете порівняти свої дані іншими способами, наприклад, визначити відносну частку кожного набору щодо найбільшого набору. Ця колонка існує з такою метою. Оскільки кока -кола - найбільша дитина, комірка В2 буде нашим еталонним значенням (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*С2”В G2 і скопіюйте його до наступних двох клітинок (G3:G4).
6. Крок #6 - Створіть мітки діаграм:
   1. Копіювати "= A2 & CHAR (10) & TEXT (D2, “#, ##”)”В H2 і скопіюйте формулу в наступні дві клітинки (Н3: Н4).

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

Крок №1: Знайдіть кількість елементів, що належать виключно одному набору.

На жаль, ви не можете просто взяти цифри з вихідної таблиці даних і працювати з ними. Якби ви це зробили, ви б насправді кілька разів перерахували деякі відповіді на опитування, ефективно перекручуючи дані.

Чому це так? Ну, оскільки діаграма Венна складається з ділянок, що перетинаються, тісно пов’язаних між собою, будь -який даний набір може бути частиною кількох інших наборів одночасно, що призводить до дублювання значень.

Наприклад, набір "Coca-Cola" (В2) включає все респонденти, які проголосували за бренд, взявши відповіді з чотирьох різних категорій:

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

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

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

• 357 для Coca-Cola
• 217 для Pepsi
• 349 для доктора Пеппера

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

Давайте з'ясуємо, скільки респондентів насправді відповіли "Кока-Кола та Пепсі » шляхом введення цієї формули до комірки D5:

1	= B5- $ B $ 8

З тієї ж ознаки використовуйте ці формули, щоб знайти значення діаграм для категорій "Пепсі" (А3) та «Доктор Пеппер» (А4):

• За “Пепсі, "Введіть"= B3-B5-B7+B8”В D3.
• За “Доктор Пеппер, "Тип"= В4-В6-В7+В8”В D4.

Крок №2: Обчисліть значення діаграми для областей перетину двох кіл.

При обчисленні чисел, що представляють перетин двох множин-де значення в межах даної області перетину одночасно належать двом множинам-виникає однакова проблема дублюючих значень.

На щастя для нас, щоб обійти проблему, потрібно лише деякий час. Знову ж таки, ці цифри ми шукаємо:

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

Давайте з'ясуємо, скільки респондентів насправді відповіли "Кока-Кола та Пепсі » шляхом введення цієї формули до комірки D5:

1	= B5- $ B $ 8

Скопіюйте формулу в комірки D6 та D7 перетягнувши ручку заповнення вниз.

Крок №3: Скопіюйте число, прив’язане до області перетину трьох наборів, у значення діаграми стовпця.

Єдине значення, яке ви можете взяти з вихідних даних, - це право посередині діаграми.

Заповніть стовпець значень діаграми, скопіювавши кількість респондентів, які обрали всі три марки у сусідню клітинку (D8). Просто введіть "= В8”В D8.

Крок №4: Накресліть значення осей х і у для кіл діаграми Венна.

У порожній клітинці біля таблиці з вашими даними відобразіть координати осі x і y, які будуть використовуватися як центри кіл.

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

На кроці 13 ви дізнаєтесь, як змінити розмір областей, що перекриваються, змінивши ці значення.

Поки що вам залишається лише скопіювати їх у свою електронну таблицю:

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

Для діаграм Венна лише з двома категоріями використовуйте такі координати:

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

Крок 5: Знайдіть значення розміру кола, що ілюструють відносні частки (необов’язково).

Якщо ці дані вам не потрібні, перейдіть до Крок No7.

Щоб кола були пропорційними до еталонного значення (C2), налаштуйте стовпець з написом “Розмір кола”Та скопіюйте цю формулу в G2.

1

= 250*С2

Для запису, номер "250” - це постійне значення, засноване на координатах, що допомагає накладати кола.

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

Крок #6: Створіть власні мітки даних.

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

Налаштуйте іншу фіктивну колонку (стовпець Н) і введіть наступну формулу H2:

1	= A2 & CHAR (10) & TEXT (D2, "#, ##")

Виконати формулу для інших клітин (Н3: Н4).

Після того, як ви виконали всі кроки, описані вище, ваші дані повинні виглядати так:

Крок #7: Створіть порожній графік розсіювання XY.

Нарешті у вас є всі дані діаграми для створення приголомшливої ​​діаграми Венна. В якості точки переходу налаштуйте порожній графік розсіювання.

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

Крок #8: Додайте дані діаграми.

Додайте значення осі x і y, щоб окреслити положення кіл. Клацніть правою кнопкою миші на графіку діаграми та виберіть "Виберіть Дані»З меню, що з'явиться.

В Виберіть Джерело даних у діалоговому вікні виберіть "Додати.”

Потрапивши туди, додайте новий ряд даних під назвою “Кока-Кола:”

1. За “Назва серії,”Клітинка виділення В2.
2. За “Значення серії X,”Вибрати клітинку E2.
3. За “Значення серії Y,”Вибрати клітинку F2.

Використовуйте цей метод для створення Серія “Пепсі” (В3, E3, F3) і Серія “Доктор Пеппер” (В4, 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, які позиціонують першу (Серія "Кока-кола"), другий (Серія "Пепсі"), і третє (Серія «Доктор Пеппер») коло відповідно. Ось як це працює:

Як ви вже здогадалися, ви можете легко змінити, наскільки кола накладаються, змінивши координати, що дозволяє переміщати кола вліво і вправо (значення осі x) і вгору та вниз (значення осі y), як вам зручно .

Наприклад, давайте розсунемо коло, що представляє Серія "Кока-кола" трохи вгору-тим самим збільшуючи площу перекриття між ними Серія "Кока-кола" та Серія "Пепсі"-оскільки у нас є порівняно велика кількість респондентів, які п’ють лише ці дві марки.

Щоб виконати трюк, просто змініть відповідне значення осі y.

Використання цього методу виведе ваші діаграми Венна на абсолютно новий рівень, зробивши їх неймовірно універсальними.

Крок 14: Додайте мітки даних.

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

Повторіть процес, описаний у кроках 14 та 15, для кожної серії даних.

Спочатку додамо мітки даних. Клацніть правою кнопкою миші маркер даних, що представляє Серія "Пепсі" і виберіть "Додати мітки даних.”

Крок 15: Налаштуйте мітки даних.

Замініть значення за замовчуванням спеціальними мітками, які ви розробили раніше. Клацніть правою кнопкою миші будь-яку мітку даних і виберіть "Форматувати мітки даних.”

Після того, як панель завдань спливе, виконайте наведені нижче дії.

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

Коли ви завершите цей крок, ваша діаграма має виглядати так:

Крок #16: Додайте текстові поля, пов'язані зі значеннями перетину.

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

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

Таким же чином додайте решту значень перекриття до діаграми. Ось ще один знімок екрана, який допоможе вам орієнтуватися в процесі.

Крок 17: Приховати маркери даних.

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

1. Натисніть кнопку «Заповнити & Лінія”.
2. Перейдіть до Маркер вкладка.
3. Під "Заповнити,"Вибрати"Без заповнення.”

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

Крок 18: Додайте назву діаграми.

Нарешті, додайте назву діаграми:

1. Виберіть ділянку ділянки.
2. Натисніть кнопку «Елементи діаграми”.
3. Перевірте "Заголовок діаграми”Коробка.

Змініть назву діаграми відповідно до ваших даних, і ваша зіркова діаграма Венна готова!