Cum se creează diagrama Venn în Excel - Descărcare gratuită a șabloanelor

Acest tutorial va demonstra cum să creați o diagramă Venn în toate versiunile de Excel: 2007, 2010, 2013, 2016 și 2022.

O diagramă Venn este o diagramă care compară două sau mai multe seturi (colecții de date) și ilustrează diferențele și punctele comune între ele cu cercuri suprapuse. Iată cum funcționează: cercul reprezintă toate elementele dintr-un set dat, în timp ce zonele de intersecție caracterizează elementele care aparțin simultan mai multor seturi.

Diagrama ajută la demonstrarea tuturor relațiilor posibile dintre seturi, oferind un puț fără fund de date analitice - motiv pentru care a fost utilizat pe scară largă în multe industrii.

Din păcate, acest tip de diagramă nu este acceptat în Excel, deci va trebui să-l construiți de la zero pe cont propriu. De asemenea, nu uitați să consultați programul nostru de completare pentru Creator de diagrame, un instrument pentru construirea unor diagrame Excel uimitoare, în timp ce abia ridicați un deget.

Până la sfârșitul acestui tutorial pas cu pas, veți afla cum să construiți o diagramă dinamică Venn cu două sau trei categorii în Excel complet de la capăt.

Noțiuni de bază

În primul rând, avem nevoie de câteva date brute pentru a lucra. Pentru a vă arăta corzile, după cum probabil ați ghicit din exemplul de diagramă de mai sus, vom analiza rezultatele unui sondaj fictiv care măsoară preferințele de marcă a 1.624 de respondenți care consumă cola zilnic. Marcile în cauză sunt Coca-Cola, Pepsi și Dr Pepper.

Dintre toți acești iubitori de băuturi răcoritoare, unii s-au dovedit a fi evangheliști de marcă care s-au lipit exclusiv de o marcă, în timp ce alții au optat pentru două sau trei mărci simultan.

Cu toate cele spuse, luați în considerare următorul tabel care conține rezultatele sondajului:

Iată o prezentare rapidă a elementelor tabelului:

• Marca - Această coloană definește seturile din sondaj, precum și toate combinațiile posibile ale seturilor.
• Respondenții - Această coloană reprezintă valorile corespunzătoare pentru fiecare set în general și pentru fiecare combinație în special.
• Partajare relativă (opțional) - Vă recomandăm să vă comparați datele în alte moduri, cum ar fi determinarea ponderii relative a fiecărui set în raport cu cel mai mare set. Această coloană există în acest scop. Deoarece Coca-Cola este cel mai mare copil din bloc, celula B2 va fi valoarea noastră de referință (100%).

Deci, fără alte întrebări, să ne apucăm de treabă.

Pregătiți datele graficului

Înainte de a vă putea grafica diagrama Venn, trebuie să calculați toate datele grafice necesare. Primii șase pași acoperă pe larg modul de determinare a pieselor puzzle-ului. Până la sfârșitul pasului # 6, datele dvs. ar trebui să arate astfel:

Pentru cei dintre voi care doresc să treacă peste partea teoretică și să pornească direct în acțiune, iată algoritmul pe care trebuie să-l urmați:

1. Pasul 1 - Calculați valorile grafice:
   2. Tip „= B2-B5-B6 + B8”În celulă D2.
   3. Tip „= B3-B5-B7 + B8”În celulă D3.
   4. Tip "= B4-B6-B7 + B8 ” în celulă D4.
2. Pasul 2 - Calculați valorile graficului cont .:
   7. introduce „= B5- $ B $ 8”În D5 și copiați formula în D6 și D7.
3. Pasul 3 - Calculați valorile graficului cont .:
   8. Setați celula D8 egală cu B8 tastând „= B8” în D8.
4. Pasul 4 - Poziționați bulele:
   1. Adăugați valorile constante pentru X și Y în foaia de lucru exact așa cum puteți vedea în captura de ecran de mai sus (E2: F4).
5. Pasul 5 - Calculați dimensiunile cercului:
   1. introduce „= 250 * C2”În G2 și copiați-l în următoarele două celule (G3:G4).
6. Pasul 6 - Creați etichetele diagramei:
   1. Copie "= A2 & CHAR (10) & TEXT (D2, „#, ##”)”În H2 și copiați formula în următoarele două celule (H3: H4).

Acum să descompunem fiecare pas în mai multe detalii.

Pasul 1: Găsiți numărul de elemente care aparțin exclusiv unui set.

Din păcate, nu puteți pur și simplu să luați numerele din tabelul de date original și să rulați cu ele. Dacă ar fi să faceți acest lucru, ați număra unele răspunsuri la sondaj de mai multe ori, distorsionând efectiv datele.

De ce este cazul? Ei bine, întrucât o diagramă Venn este alcătuită din zone care se intersectează strâns legate între ele, orice set dat poate face parte din mai multe alte seturi simultan, rezultând valori duplicate.

De exemplu, setul „Coca-Cola” (B2) include toate respondenții care au votat pentru marcă, preluând răspunsuri din patru categorii diferite:

• Cola-Cola & Pepsi
• Coca-Cola și Dr Pepper
• Coca-Cola & Pepsi & Dr Pepper
• Doar Coca-Cola

De aceea, trebuie să separați grâul de pleavă și să găsiți o valoare grafică corespunzătoare pentru fiecare opțiune de sondaj de la tabelul original.

Acestea fiind spuse, primul nostru pas este să diferențiem respondenții care au ales doar un brand de cola de toți ceilalți. Acești oameni se caracterizează prin următoarele numere de pe diagramă:

• 357 pentru Coca-Cola
• 217 pentru Pepsi
• 349 pentru Dr. Pepper

Alegeți o valoare corespunzătoare din datele dvs. reale (B5) și scade din ea valoarea reprezentând zona în care se suprapun toate cele trei cercuri (B8).

Să aflăm câți respondenți au răspuns de fapt „Coca-Cola și Pepsi ” prin introducerea acestei formule în celulă D5:

1	= B5- $ B $ 8

În același mod, utilizați aceste formule pentru a găsi valorile grafice pentru categorii „Pepsi” (A3) și „Dr Pepper” (A4):

• Pentru "Pepsi," introduce "= B3-B5-B7 + B8”În D3.
• Pentru "Dr. Pepper," tip "= B4-B6-B7 + B8”În D4.

Pasul 2: calculați valorile graficului pentru zonele de intersecție a două cercuri.

Când se calculează numerele care reprezintă intersecția a două seturi - unde valorile dintr-o anumită zonă de intersecție aparțin simultan a două seturi - apare aceeași problemă a valorilor duplicate.

Din fericire pentru noi, ar trebui să dureze doar un moment pentru a rezolva problema. Din nou, acestea sunt numerele pe care le căutăm:

Alegeți o valoare corespunzătoare din datele dvs. reale (B5) și scade din ea valoarea reprezentând zona în care se suprapun toate cele trei cercuri (B8).

Să aflăm câți respondenți au răspuns de fapt „Coca-Cola și Pepsi ” prin introducerea acestei formule în celulă D5:

1	= B5- $ B $ 8

Copiați formula în celule D6 și D7 trăgând în jos mânerul de umplere.

Pasul 3: copiați numărul legat de zona de intersecție a trei seturi în coloana Valoare diagramă.

Singura valoare pe care o puteți lua din datele dvs. originale este cea din mijlocul diagramei.

Completați coloana valorilor graficului copiind numărul de respondenți care au ales toate cele trei mărci în celula alăturată (D8). Pur și simplu tastați „= B8”În D8.

Pasul 4: Descrieți valorile axelor x și y pentru cercurile diagramei Venn.

Într-o celulă goală de lângă tabel cu datele dvs., trasați coordonatele axelor x și y care vor fi folosite ca centre ale cercurilor.

Următoarele valori sunt constante care vor determina poziția cercurilor diagramei Venn pe graficul graficului, oferindu-vă control deplin asupra cât de departe unul de celălalt vor fi plasate cercurile.

La Pasul 13, veți învăța cum să modificați dimensiunea zonelor suprapuse modificând aceste valori.

Pentru moment, tot ce trebuie să faceți este să le copiați în foaia de calcul:

Valoarea X	Valoarea Y
38	35
50	65
62	35

Pentru diagramele Venn cu doar două categorii, utilizați aceste coordonate:

Valoarea X	Valoarea Y
39	50
66	50

Pasul 5: Găsiți valorile Dimensiunii cercului care ilustrează cotele relative (opțional).

Dacă nu aveți nevoie de aceste date, treceți la Pasul 7.

Pentru a face cercurile proporționale cu valoarea de referință (C2), configurați o coloană etichetată „Dimensiunea cercului”Și copiați această formulă în G2.

1	= 250 * C2

Pentru înregistrare, numărul „250”Este o valoare constantă bazată pe coordonatele care ajută cercurile să se suprapună.

Executați formula pentru celelalte două celule (G3: G4) care semnifică celelalte două cercuri ale diagramei prin glisarea mânerului de umplere.

Pasul 6: creați etichete de date personalizate.

Înainte de a putea trece în cele din urmă la construirea diagramei, să proiectăm rapid etichete de date personalizate care vor conține numele unui set dat împreună cu valoarea corespunzătoare care contează numărul de respondenți care au ales doar o marcă cola.

Configurați o altă coloană fictivă (coloana H) și introduceți următoarea formulă în H2:

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

Executați formula pentru celulele rămase (H3: H4).

După ce ați urmat toți pașii descriși mai sus, datele dvs. ar trebui să arate astfel:

Pasul 7: Creați un grafic de dispersie XY gol.

În cele din urmă, aveți toate datele grafice pentru a construi o diagramă uimitoare Venn. Ca punct de săritură, configurați un grafic de împrăștiere gol.

1. Selectați orice celulă goală.
2. Du-te la Introduce filă.
3. Apasă pe "Introduceți Scatter (X, Y) sau diagramă cu bule”Pictogramă.
4. Alegeți „Împrăștia.”

Pasul 8: Adăugați datele graficului.

Adăugați valorile axelor x și y pentru a contura poziția cercurilor. Faceți clic dreapta pe graficul și alegeți „Selectați Date”Din meniul care apare.

În Selectați Sursa de date caseta de dialog, alegeți „Adăuga.”

Odată ajuns acolo, adăugați o nouă serie de date numită „Coca Cola:”

1. Pentru "Numele seriei,”Evidențiați celula B2.
2. Pentru "Valorile seriei X,”Selectați celula E2.
3. Pentru "Valorile seriei Y,”Selectați celula F2.

Folosiți această metodă pentru a crea Serie “Pepsi” (B3, E3, F3) și Serie “Dr. Pepper” (B4, E4, F4).

Pasul # 9: Schimbați intervalele de scară a axelor orizontale și verticale.

Redimensionați axele pentru a începe la 0 și a se termina la 100 pentru a centra marcatorii de date aproape de mijlocul zonei graficului.

Faceți clic dreapta pe axa verticală și selectați „Formatează axa.”

În Formatează axa din panoul de activități, efectuați următoarele:

1. Navigați la Opțiuni Axis filă.
2. Seteaza Limite minime la "0.”
3. Seteaza Limite maxime la "100.”

Salt la axa orizontală și repetați același proces.

Pasul 10: îndepărtați axele și liniile de rețea.

Curățați graficul ștergând axele și liniile de rețea. Faceți clic dreapta pe fiecare element și selectați „Șterge.”

Acum ar fi un moment bun pentru a vă face graficul mai mare, astfel încât să puteți vedea mai bine noua diagramă Venn. Selectați graficul și trageți mânerele pentru ao mări.

Iată ce ar trebui să aveți în acest moment - minimalismul la maxim:

Pasul 11: Măriți markerii de date.

Pentru a eficientiza procesul, faceți marcajele mai mari sau acestea vor fi îngropate sub cercurile aflate mai jos pe drum.

Faceți clic dreapta pe oricare dintre cei trei markeri de date și alegeți „Formatează seria de date.”

În panoul de activități, urmați acești pași simpli:

• Apasă pe "Fill & Line”Pictogramă.
• Treceți la Marker filă.
• Sub "Opțiuni de marcare," verifică "Incorporat”Și setați butonul radio mărimea valoare la „10.”
• Sub "Completati," Selectați "Umplere solidă.”
• Apasă pe "Culoare umplere”Pentru a deschide paleta de culori și a alege negru.

Clătiți și repetați pentru fiecare serie de date.

Pasul 12: Desenați cercurile.

Fără a închide fila, derulați în jos până la Frontieră secțiunea și creați cercurile pentru diagrama Venn urmând aceste instrucțiuni:

1. Faceți clic pe „Linie solida.”
2. Deschideți paleta de culori și alegeți culori diferite pentru fiecare marker de date:
   1. Pentru Serie “Coca Cola,”Alege aur.
   2. Pentru Serie “Pepsi," Selectați verde.
   3. Pentru Serie “Dr. Pepper,”Alege albastru.
3. Schimbare "Transparenţă" la "40%.”
4. Schimbare "Lăţime”Pentru fiecare serie de date la valorile corespunzătoare din coloană Dimensiune cerc (coloana G):
   1. Pentru Serie “Coca Cola," introduce „250” (G2).
   2. Pentru Serie “Pepsi," introduceți “230” (G3).
   3. Pentru Serie “Dr. Pepper,”Setați valoarea la „200” (G4).

NOTĂ: Dacă doriți ca cercurile să fie la fel, pur și simplu setați Lăţime valoare la „250" pentru fiecare.

Pasul 13: Schimbați coordonatele axelor x și y pentru a regla zonele suprapuse (opțional).

În mod normal, ar trebui să lăsați nealterate coordonatele X și Y determinând poziția cercurilor. Cu toate acestea, pentru cei care doresc să personalizeze modul în care cercurile ar trebui să se suprapună, iată cum o faceți.

Pentru a vă reîmprospăta memoria, aruncați o privire la coordonatele care stau la baza diagramei Venn utilizate în tutorial.

Valoarea X	Valoarea Y
38	35
50	65
62	35

Acestea sunt valorile axelor x și y care poziționează prima (Seria „Coca-Cola”), al doilea (Seria „Pepsi”) și al treilea (Seria „Dr Pepper”) cercul respectiv. Iată cum funcționează:

După cum probabil ați ghicit, puteți schimba fără efort cât de mult se suprapun cercurile modificând coordonatele - ceea ce vă permite să mutați cercurile la stânga și la dreapta (valorile axei x) și în sus și în jos (valorile axei y), oricum veți considera potrivit .

De exemplu, să împingem cercul reprezentând Seria „Coca-Cola” un pic în sus - crescând astfel zona suprapusă între Seria „Coca-Cola” și Seria „Pepsi”-de vreme ce avem un număr relativ mare de respondenți care beau doar aceste două mărci.

Pentru a scoate trucul, pur și simplu modificați valoarea corespunzătoare a axei y.

Utilizarea acestei metode va duce diagramele Venn la un nivel complet nou, făcându-le incredibil de versatile.

Pasul 14: Adăugați etichete de date.

Restul pașilor vor fi dedicați pentru a face diagrama mai informativă.

Repetați procesul descris în pașii 14 și 15 pentru fiecare serie de date.

Mai întâi, să adăugăm etichete de date. Faceți clic dreapta pe marcajul de date care reprezintă Seria „Pepsi” și alegeți „Adăugați etichete de date.”

Pasul 15: Personalizați etichetele de date.

Înlocuiți valorile implicite cu etichetele personalizate pe care le-ați proiectat anterior. Faceți clic dreapta pe orice etichetă de date și alegeți „Formatați etichete de date.”

Odată ce panoul de activități apare, faceți următoarele:

1. Du-te la Opțiuni pentru etichete filă.
2. Faceți clic pe „Valoare din celule.”
3. Evidențiați celula corespunzătoare din coloana Etichetă (H2 pentru Coca-Cola, H3 pentru Pepsi și H4 pentru Dr. Pepper).
4. Faceți clic pe „BINE.”
5. Debifați „Valoarea Y" cutie.
6. Debifați „Afișați Liniile Leader" cutie.
7. Sub "Poziția etichetei,”Alegeți următoarele:
   1. Pentru Serie “Pepsi,"Faceți clic pe"De mai sus.”
   2. Pentru Serie “Coca Cola," Selectați "Stânga.”
   3. Pentru Serie “Dr. Pepper,"Alege"Dreapta.”
8. Ajustați dimensiunea fontului / îndrăzneala / culoarea de sub Acasă > Font.

După ce ați terminat acest pas, diagrama dvs. ar trebui să arate astfel:

Pasul 16: Adăugați casete de text legate de valorile de intersecție.

Introduceți patru casete de text pentru fiecare parte suprapusă a diagramei pentru a afișa numărul corespunzător de persoane care au optat pentru mai multe mărci simultan.

1. Selectați zona parcelei.
2. Du-te la Introduce filă.
3. Apasă pe "Casetă de text”Buton.
4. Creați o casetă de text.
5. Tip "=" în Formula Bar.
6. Evidențiați celula corespunzătoare din coloană Valoare diagramă (coloana D).
7. Reglați dimensiunea fontului / îndrăzneala / culoarea pentru a se potrivi cu celelalte etichete de sub Acasă > Font.

În același mod, adăugați restul valorilor de suprapunere în diagramă. Iată o altă captură de ecran pentru a vă ajuta să navigați prin proces.

Pasul 17: ascundeți markerii de date.

Nu mai aveți nevoie de markerii de date care susțin diagrama, așa că să-i facem invizibili. Faceți clic dreapta pe orice marker de date, deschideți fișierul Formatează seria de date și efectuați următoarele:

1. Apasă pe "Fill & Line”Pictogramă.
2. Treceți la Marker filă.
3. Sub "Completati,”Alege”Fără umplere.”

Repetați procesul pentru fiecare marker de date.

Pasul 18: Adăugați titlul graficului.

În cele din urmă, adăugați titlul graficului:

1. Selectați zona parcelei.
2. Apasă pe "Elemente grafice”Pictogramă.
3. Verifică "Titlul graficului" cutie.

Schimbați titlul graficului pentru a se potrivi cu datele dvs., iar diagrama dvs. Venn stelară este gata!