Jak utworzyć diagram Venna w programie Excel - bezpłatne pobieranie szablonu

Ten samouczek pokaże, jak utworzyć diagram Venna we wszystkich wersjach programu Excel: 2007, 2010, 2013, 2016 i 2022.

Diagram Venna to wykres, który porównuje dwa lub więcej zestawów (zbiorów danych) i ilustruje różnice i podobieństwa między nimi za pomocą nakładających się okręgów. Oto, jak to działa: okrąg reprezentuje wszystkie elementy w danym zestawie, podczas gdy obszary przecięcia charakteryzują elementy, które jednocześnie należą do wielu zestawów.

Diagram pomaga zademonstrować wszystkie możliwe relacje między zestawami, zapewniając bezdenną studnię danych analitycznych – dlatego jest szeroko stosowany w wielu branżach.

Niestety ten typ wykresu nie jest obsługiwany w programie Excel, więc będziesz musiał samodzielnie zbudować go od zera. Nie zapomnij również sprawdzić naszego dodatku Chart Creator, narzędzia do tworzenia oszałamiających zaawansowanych wykresów Excela przy ledwie kiwnięciu palcem.

Pod koniec tego samouczka krok po kroku dowiesz się, jak całkowicie od podstaw zbudować dynamiczny diagram Venna z dwiema lub trzema kategoriami w programie Excel.

Pierwsze kroki

Po pierwsze, potrzebujemy surowych danych do pracy. Aby pokazać, jak można się domyślić z przykładowego diagramu powyżej, przeanalizujemy wyniki fikcyjnej ankiety oceniającej preferencje dotyczące marek 1624 respondentów, którzy na co dzień piją colę. Wspomniane marki to Coca-Cola, Pepsi i Dr Pepper.

Spośród wszystkich tych miłośników napojów bezalkoholowych, niektórzy okazali się ewangelistami marki, którzy trzymali się wyłącznie jednej marki, podczas gdy inni wybierali dwie lub trzy marki jednocześnie.

Biorąc to wszystko pod uwagę, rozważ poniższą tabelę zawierającą wyniki ankiety:

Oto krótki przegląd elementów tabeli:

• Marka - Ta kolumna definiuje zestawy w ankiecie oraz wszystkie możliwe kombinacje zestawów.
• Respondenci - Ta kolumna przedstawia odpowiednie wartości dla każdego zestawu ogólnie i każdej kombinacji w szczególności.
• Udział względny (opcjonalnie) - Możesz chcieć porównać swoje dane w inny sposób, na przykład określając względny udział każdego zestawu w stosunku do największego zestawu. W tym celu istnieje ta kolumna. Ponieważ Cola jest największym dzieciakiem w okolicy, komórka B2 będzie naszą wartością referencyjną (100%).

Więc bez zbędnych ceregieli zabierzmy się do pracy.

Przygotuj dane wykresu

Zanim będziesz mógł wykreślić diagram Venna, musisz obliczyć wszystkie niezbędne dane wykresu. Pierwsze sześć kroków obszernie opisuje, jak określić elementy układanki. Pod koniec kroku 6 Twoje dane powinny wyglądać tak:

Dla tych z Was, którzy chcą pominąć część teoretyczną i od razu przejść do działania, oto algorytm do naśladowania:

1. Krok #1 - Oblicz wartości wykresu:
   2. Rodzaj „=B2-B5-B6+B8”do komórki D2.
   3. Rodzaj „=B3-B5-B7+B8”do komórki D3.
   4. Rodzaj "=B4-B6-B7+B8” do komórki D4.
2. Krok #2 - Oblicz wartości wykresu cd.:
   7. Wejść „=B5-$B8$" do D5 i skopiuj formułę w dół do D6 oraz D7.
3. Krok #3 - Oblicz wartości wykresu cd.:
   8. Ustaw komórkę D8 równą B8, wpisując „=B8” do D8.
4. Krok #4 - Ustaw bąbelki:
   1. Dodaj stałe wartości X i Y do arkusza tak, jak widać na powyższym zrzucie ekranu (E2:F4).
5. Krok #5 - Oblicz rozmiary kół:
   1. Wejść „=250*C2" do G2 i skopiuj go do następnych dwóch komórek (G3:G4).
6. Krok #6 - Utwórz etykiety diagramów:
   1. Kopiuj "=A2&ZNAK(10)&TEKST(D2, „#,##”)" do H2 i skopiuj formułę do kolejnych dwóch komórek (H3:H4).

Teraz przyjrzyjmy się bliżej każdemu krokowi.

Krok 1: Znajdź liczbę elementów należących wyłącznie do jednego zestawu.

Niestety, nie możesz po prostu wziąć liczb z oryginalnej tabeli danych i uruchomić z nimi. Gdybyś to zrobił, faktycznie policzyłbyś niektóre odpowiedzi z ankiety wiele razy, skutecznie zniekształcając dane.

Dlaczego tak jest? Cóż, ponieważ diagram Venna składa się z przecinających się obszarów blisko ze sobą powiązanych, każdy dany zestaw może być częścią wielu innych zestawów jednocześnie, co skutkuje zduplikowanymi wartościami.

Na przykład zestaw „Coca-Cola” (B2) zawiera wszystko respondentów, którzy głosowali na markę, wybierając odpowiedzi z czterech różnych kategorii:

• Cola-Cola i Pepsi
• Coca-Cola i Dr Pepper
• Coca-Cola & Pepsi & Dr Pepper
• Tylko Coca-Cola

Dlatego musisz oddzielić ziarno od plew i znaleźć odpowiednią wartość wykresu dla każdej opcji ankiety z oryginalnej tabeli.

Biorąc to pod uwagę, naszym pierwszym krokiem jest odróżnienie respondentów, którzy wybrali tylko jedną markę coli spośród wszystkich innych. Osoby te charakteryzują się następującymi liczbami na schemacie:

• 357 dla Coca-Coli
• 217 dla pepsi
• 349 dla dr Pepper

Wybierz odpowiednią wartość z rzeczywistych danych (B5) i odejmij od niego wartość reprezentującą obszar, w którym wszystkie trzy okręgi nakładają się (B8).

Sprawdźmy, ilu respondentów faktycznie odpowiedziało „Coca-Cola i Pepsi” wpisując tę ​​formułę do komórki D5:

1

=B5-$B8$

Z tego samego powodu użyj tych wzorów, aby znaleźć wartości wykresu dla kategorii „Pepsi” (A3) oraz „Dr Pepper” (A4):

• Do "Pepsi," wejść "=B3-B5-B7+B8" w D3.
• Do "Dr Pepper," rodzaj "=B4-B6-B7+B8" w D4.

Krok #2: Oblicz wartości na wykresie dla obszarów przecięcia dwóch okręgów.

Podczas obliczania liczb reprezentujących przecięcie dwóch zbiorów – gdzie wartości w danym obszarze przecięcia należą jednocześnie do dwóch zbiorów – pojawia się ten sam problem zduplikowanych wartości.

Na szczęście dla nas obejście problemu powinno zająć tylko chwilę. Znowu są to liczby, których szukamy:

Wybierz odpowiednią wartość z rzeczywistych danych (B5) i odejmij od niego wartość reprezentującą obszar, w którym wszystkie trzy okręgi nakładają się (B8).

Sprawdźmy, ilu respondentów faktycznie odpowiedziało „Coca-Cola i Pepsi” wpisując tę ​​formułę do komórki D5:

1

=B5-$B8$

Skopiuj formułę do komórek D6 oraz D7 przeciągając w dół uchwyt wypełniania.

Krok #3: Skopiuj numer połączony z obszarem przecięcia trzech zestawów do kolumny Wartość wykresu.

Jedyną wartością, którą możesz pobrać z oryginalnych danych, jest ta pośrodku diagramu.

Uzupełnij kolumnę wartości wykresu, kopiując liczbę respondentów, którzy wybrali wszystkie trzy marki do sąsiedniej komórki (D8). Po prostu wpisz „=B8" do D8.

Krok 4: Nakreśl wartości osi x i y dla okręgów diagramu Venna.

W pustej komórce obok tabeli z Twoimi danymi zaznacz współrzędne osi X i Y, które będą używane jako środki okręgów.

Poniższe wartości są stałymi, które określają położenie okręgów diagramu Venna na wykresie, dając pełną kontrolę nad tym, jak daleko od siebie zostaną umieszczone okręgi.

W kroku 13 dowiesz się, jak zmienić rozmiar nakładających się obszarów, modyfikując te wartości.

Na razie wszystko, co musisz zrobić, to skopiować je do swojego arkusza kalkulacyjnego:

X Wartość	Wartość Y
38	35
50	65
62	35

W przypadku diagramów Venna z tylko dwiema kategoriami użyj tych współrzędnych:

X Wartość	Wartość Y
39	50
66	50

Krok #5: Znajdź wartości Rozmiar koła ilustrujące względne udziały (opcjonalnie).

Jeśli nie potrzebujesz tych danych, przejdź do Krok #7.

Aby koła były proporcjonalne do wartości odniesienia (C2), skonfiguruj kolumnę oznaczoną „Rozmiar koła” i skopiuj tę formułę do G2.

1

=250*C2

Dla przypomnienia liczba „250” to stała wartość oparta na współrzędnych, która pomaga nakładać się okręgów.

Wykonaj formułę dla pozostałych dwóch komórek (G3:G4), które oznaczają dwa pozostałe okręgi diagramu, przeciągając uchwyt wypełniania.

Krok #6: Utwórz niestandardowe etykiety danych.

Zanim ostatecznie przystąpimy do budowy diagramu, zaprojektujmy szybko niestandardowe etykiety danych, które będą zawierać nazwę danego zestawu wraz z odpowiadającą mu wartością, która zlicza liczbę respondentów, którzy wybrali tylko jedną markę coli.

Skonfiguruj kolejną fikcyjną kolumnę (kolumna H) i wprowadź następujący wzór do H2:

1	=A2&ZNAK(10)&TEKST(D2, "#,##")

Wykonaj formułę dla pozostałych komórek (H3:H4).

Po wykonaniu wszystkich kroków opisanych powyżej Twoje dane powinny wyglądać tak:

Krok 7: Utwórz pusty wykres punktowy XY.

W końcu masz wszystkie dane z wykresu, aby zbudować oszałamiający diagram Venna. Jako punkt wyjścia skonfiguruj pusty wykres punktowy.

1. Wybierz dowolną pustą komórkę.
2. Przejdź do Wstawić patka.
3. Kliknij "Wstaw punktowy (X,Y) lub wykres bąbelkowy" Ikona.
4. Wybierać "Rozpraszać.”

Krok #8: Dodaj dane wykresu.

Dodaj wartości osi x i y, aby określić położenie okręgów. Kliknij prawym przyciskiem myszy na wykresie wykresu i wybierz „Wybierz dane” z menu, które się pojawi.

w Wybierz źródło danych w oknie dialogowym wybierz „Dodać.”

Tam dodaj nową serię danych o nazwie „Coca-Cola:”

1. Do "Nazwa serii,” zaznacz komórkę B2.
2. Do "Wartości serii X,” wybierz komórkę E2.
3. Do "wartości serii Y,” wybierz komórkę F2.

Użyj tej metody, aby stworzyć Seria “Pepsi” (B3, E3, F3) oraz Seria “Dr Pepper” (B4, E 4, F4).

Krok #9: Zmień zakresy skali osi poziomej i pionowej.

Zmień skalę osi, aby zaczynała się od 0 i kończyła na 100, aby wyśrodkować znaczniki danych w pobliżu środka obszaru wykresu.

Kliknij prawym przyciskiem myszy oś pionową i wybierz „Oś formatuj.”

w Formatuj oś w okienku zadań wykonaj następujące czynności:

1. Przejdź do Opcje osi patka.
2. Ustaw Minimalne granice do "0.”
3. Ustaw Maksymalne granice do "100.”

Przejdź do osi poziomej i powtórz ten sam proces.

Krok 10: Usuń osie i linie siatki.

Wyczyść wykres, wymazując osie i linie siatki. Kliknij prawym przyciskiem myszy każdy element i wybierz „Kasować.”

Teraz jest dobry moment, aby powiększyć wykres, aby lepiej widzieć swój nowy, fantazyjny diagram Venna. Wybierz wykres i przeciągnij uchwyty, aby go powiększyć.

Oto, co powinieneś mieć w tym momencie - minimalizm w najlepszym wydaniu:

Krok #11: Powiększ znaczniki danych.

Aby usprawnić proces, zwiększ znaczniki, w przeciwnym razie zostaną zakopane pod kręgami w dalszej części drogi.

Kliknij prawym przyciskiem myszy dowolny z trzech znaczników danych i wybierz „Formatuj serię danych.”

W okienku zadań wykonaj następujące proste kroki:

• Kliknij "Wypełnij i linia" Ikona.
• Przełącz na Znacznik patka.
• Pod "Opcje znaczników," Sprawdź "Wbudowanyprzycisk radiowy i ustaw Rozmiar wartość do „10.”
• Pod "Napełnić," Wybierz "Solidne wypełnienie.”
• Kliknij "Kolor wypełnienia” ikona, aby otworzyć paletę kolorów i wybierz czarny.

Przepłucz i powtórz dla każdej serii danych.

Krok #12: Narysuj koła.

Bez zamykania karty przewiń w dół do Granica sekcji i utwórz okręgi dla diagramu Venna, postępując zgodnie z tymi instrukcjami:

1. Kliknij "Linia ciągła.”
2. Otwórz paletę kolorów i wybierz różne kolory dla każdego znacznika danych:
   1. Do Seria “Coca-Cola," wybierać złoto.
   2. Do Seria “pepsi," Wybierz Zielony.
   3. Do Seria “Dr Pepper," wybierać niebieski.
3. Reszta "Przezroczystość" do "40%.”
4. Reszta "Szerokość” dla każdej serii danych do odpowiednich wartości w kolumna Rozmiar koła (kolumna G):
   1. Do Seria “Coca-Cola," wejść „250” (G2).
   2. Do Seria “pepsi," Wpisz “230” (G3).
   3. Do Seria “Dr Pepper,” ustaw wartość na „200” (G4).

NOTATKA: Jeśli chcesz, aby kręgi były takie same, po prostu ustaw Szerokość wartość do „250" dla każdego.

Krok #13: Zmień współrzędne osi X i Y, aby dostosować nakładające się obszary (opcjonalnie).

Normalnie współrzędne X i Y określające położenie okręgów należy pozostawić niezmienione. Jednak dla tych, którzy chcą dostosować sposób, w jaki koła powinny się nakładać, oto jak to zrobić.

Aby odświeżyć swoją pamięć, spójrz na współrzędne leżące u podstaw diagramu Venna użytego w samouczku.

X Wartość	Wartość Y
38	35
50	65
62	35

Są to wartości osi x i y pozycjonujące pierwszy (Seria „Coca-Cola”), druga (Seria „Pepsi”) i trzeci (Seria „Dr Pepper”) koło odpowiednio. Oto jak to działa:

Jak można się domyślić, możesz bez wysiłku zmienić stopień nakładania się kręgów, modyfikując współrzędne, co pozwala na przesuwanie kręgów w lewo i w prawo (wartości osi x) oraz w górę i w dół (wartości na osi y), jakkolwiek uznasz to za stosowne .

Na przykład przesuńmy kółko reprezentujące Seria „Coca-Cola” nieco w górę, zwiększając w ten sposób obszar nakładania się między Seria „Coca-Cola” oraz Seria „Pepsi”-ponieważ mamy stosunkowo dużą liczbę respondentów pijących tylko te dwie marki.

Aby wykonać sztuczkę, po prostu zmień odpowiednią wartość osi y.

Korzystanie z tej metody przeniesie twoje diagramy Venna na zupełnie nowy poziom, czyniąc je niewiarygodnie wszechstronnymi.

Krok #14: Dodaj etykiety danych.

Pozostałe kroki zostaną poświęcone uczynieniu diagramu bardziej informacyjnym.

Powtórz proces opisany w krokach 14 i 15 dla każdej serii danych.

Najpierw dodajmy etykiety danych. Kliknij prawym przyciskiem myszy znacznik danych reprezentujący Seria „Pepsi” i wybierz „Dodaj etykiety danych.”

Krok 15: Dostosuj etykiety danych.

Zastąp wartości domyślne wcześniej zaprojektowanymi etykietami niestandardowymi. Kliknij prawym przyciskiem myszy dowolną etykietę danych i wybierz „Formatuj etykiety danych.”

Gdy pojawi się okienko zadań, wykonaj następujące czynności:

1. Przejdź do Opcje etykiet patka.
2. Kliknij "Wartość z komórek.”
3. Zaznacz odpowiednią komórkę z kolumny Etykieta (H2 dla Coca-Coli, H3 dla Pepsi i H4 dla doktora Peppera).
4. Kliknij "OK.”
5. Odznacz „Wartość Y" skrzynka.
6. Odznacz „Pokaż linie lidera" skrzynka.
7. Pod "Pozycja etykiety,” wybierz następujące:
   1. Do Seria “pepsi," Kliknij "Nad.”
   2. Do Seria “Coca-Cola," Wybierz "Lewo.”
   3. Do Seria “Dr Pepper," wybierać "Dobrze.”
8. Dostosuj rozmiar czcionki / pogrubienie / kolor pod Dom > Czcionka.

Po zakończeniu tego kroku twój diagram powinien wyglądać tak:

Krok #16: Dodaj pola tekstowe połączone z wartościami przecięcia.

Wstaw cztery pola tekstowe dla każdej nakładającej się części diagramu, aby wyświetlić odpowiednią liczbę osób, które wybrały wiele marek jednocześnie.

1. Wybierz obszar działki.
2. Przejdź do Wstawić patka.
3. Kliknij "Pole tekstowe" przycisk.
4. Utwórz pole tekstowe.
5. Rodzaj "=” do Formuła Bar.
6. Zaznacz odpowiednią komórkę z kolumna Wartość wykresu (kolumna D).
7. Dostosuj rozmiar / pogrubienie / kolor czcionki, aby pasowały do ​​​​innych etykiet poniżej Dom > Czcionka.

W ten sam sposób dodaj pozostałe wartości nakładania się do wykresu. Oto kolejny zrzut ekranu, który pomoże Ci poruszać się po procesie.

Krok #17: Ukryj znaczniki danych.

Nie potrzebujesz już znaczników danych wspierających diagram, więc uczyńmy je niewidocznymi. Kliknij prawym przyciskiem myszy dowolny znacznik danych, otwórz Formatuj serię danych i wykonaj następujące czynności:

1. Kliknij "Wypełnij i linia" Ikona.
2. Przełącz na Znacznik patka.
3. Pod "Napełnić," wybierać "Nie wypełniać.”

Powtórz ten proces dla każdego znacznika danych.

Krok #18: Dodaj tytuł wykresu.

Na koniec dodaj tytuł wykresu:

1. Wybierz obszar działki.
2. Kliknij "Elementy wykresu" Ikona.
3. Sprawdź "Tytuł wykresu" skrzynka.

Zmień tytuł wykresu, aby pasował do Twoich danych, a Twój gwiezdny diagram Venna jest gotowy!