Kako ustvariti Vennov diagram v Excelu - brezplačen prenos predloge

Ta vadnica bo pokazala, kako ustvariti Vennov diagram v vseh različicah Excela: 2007, 2010, 2013, 2016 in 2022.

Vennov diagram je grafikon, ki primerja dva ali več nizov (zbirk podatkov) in ponazarja razlike in skupne značilnosti med njima s prekrivajočimi se krogi. Takole deluje: krog predstavlja vse elemente v danem nizu, križišča pa označujejo elemente, ki hkrati pripadajo več nizom.

Diagram pomaga prikazati vse možne odnose med množicami in zagotavlja analitične podatke brez dna-zato se je široko uporabljal v številnih panogah.

Na žalost ta vrsta grafikona ni podprta v Excelu, zato jo boste morali sami zgraditi iz nič. Prav tako ne pozabite preveriti našega dodatka za ustvarjanje grafikonov, orodja za ustvarjanje osupljivih naprednih grafikonov Excel, medtem ko komaj dvignete prst.

Do konca te vadnice po korakih se boste naučili, kako v Excelu popolnoma od začetka zgraditi dinamični Vennov diagram z dvema ali tremi kategorijami.

Začetek

Najprej potrebujemo nekaj surovih podatkov za delo. Da bi vam prikazali vrvi, kot ste morda uganili iz zgornjega diagrama, bomo analizirali rezultate izmišljene raziskave, v kateri smo ocenili preference blagovnih znamk 1624 anketirancev, ki dnevno pijejo kola. Zadevne blagovne znamke so Coca-Cola, Pepsi in Dr Pepper.

Od vseh teh ljubiteljev brezalkoholnih pijač so se nekateri izkazali za evangeliste blagovnih znamk, ki so se držali izključno ene znamke, drugi pa so se odločili za dve ali tri blagovne znamke hkrati.

Ob vsem tem upoštevajte naslednjo tabelo, ki vsebuje rezultate raziskave:

Tu je kratek pregled elementov tabele:

• Blagovna znamka - Ta stolpec opredeljuje sklope v anketi in vse možne kombinacije sklopov.
• Anketiranci - Ta stolpec predstavlja ustrezne vrednosti za vsak niz na splošno in vsako kombinacijo posebej.
• Relativni delež (neobvezno) - Morda boste želeli svoje podatke primerjati na druge načine, na primer določiti relativni delež vsakega niza glede na največji niz. Ta stolpec obstaja za tak namen. Ker je Coca Cola največji otrok v bloku, celica B2 bo naša referenčna vrednost (100%).

Torej, brez odlašanja, se lotimo dela.

Podatki pripravljalnega grafikona

Preden lahko narišete svoj Vennov diagram, morate izračunati vse potrebne podatke grafikona. Prvih šest korakov obsežno obravnava, kako določiti koščke sestavljanke. Do konca 6. koraka bi morali biti vaši podatki videti tako:

Za tiste, ki želite preskočiti teoretični del in začeti z dejanji, je tukaj naslednji algoritem:

1. 1. korak - Izračunajte vrednosti grafikona:
   2. Vrsta “= B2-B5-B6+B8«V celico D2.
   3. Vrsta “= B3-B5-B7+B8«V celico D3.
   4. Vnesite "= B4-B6-B7+B8 ” v celico D4.
2. Korak 2 - Izračun vrednosti grafikona nadaljevanje:
   7. Vnesite "= B5- $ B $ 8”V D5 in kopiraj formulo navzdol D6 in D7.
3. Korak #3 - Izračun vrednosti grafikona nadaljevanje:
   8. Celico D8 z vnosom nastavite na B8 "= B8" v D8.
4. 4. korak - Postavite mehurčke:
   1. Dodajte konstantne vrednosti za X in Y na svoj delovni list, tako kot vidite na zgornjem posnetku zaslona (E2: F4).
5. Korak #5 - Izračunajte velikosti kroga:
   1. Vnesite “= 250*C2”V G2 in ga kopirajte v naslednji dve celici (G3:G4).
6. Korak #6 - Ustvarite oznake diagrama:
   1. Kopirati "= A2 & CHAR (10) & BESEDILO (D2, “#, ##”)”V H2 in kopirajte formulo v naslednji dve celici (H3: H4).

Zdaj pa vsak korak podrobneje razčlenimo.

1. korak: Poiščite število elementov, ki pripadajo izključno enemu nizu.

Na žalost ne morete preprosto vzeti številk iz prvotne podatkovne tabele in teči z njimi. Če bi to storili, bi nekatere odgovore v anketi dejansko večkrat prešteli in tako učinkovito izkrivili podatke.

Zakaj je temu tako? No, ker je Vennov diagram sestavljen iz presekajočih se področij, ki so med seboj tesno povezana, je lahko kateri koli niz hkrati del več drugih nizov, kar ima za posledico podvojene vrednosti.

Na primer komplet "Coca-Cola" (B2) vključuje vse anketiranci, ki so glasovali za blagovno znamko, in povlečejo odgovore iz štirih različnih kategorij:

• Cola-Cola in Pepsi
• Coca-Cola in Dr Pepper
• Coca-Cola & Pepsi & Dr Pepper
• Samo Coca-Cola

Zato morate ločiti pšenico od plev in poiskati ustrezno vrednost grafikona za vsako možnost raziskovanja iz prvotne tabele.

Ob tem je naš prvi korak razlikovanje anketirancev, ki so izbrali samo eno znamko kole, od vseh drugih. Za te ljudi so na diagramu značilne naslednje številke:

• 357 za Coca-Colo
• 217 za Pepsi
• 349 za dr. Pepperja

Iz dejanskih podatkov izberite ustrezno vrednost (B5) in od nje odštejemo vrednost, ki predstavlja območje, kjer se vsi trije krogi prekrivajo (B8).

Ugotovimo, koliko anketirancev je dejansko odgovorilo "Coca-Cola in Pepsi " z vnosom te formule v celico D5:

1	= B5- $ B $ 8

Na enak način uporabite te formule za iskanje vrednosti grafikona za kategorije "Pepsi" (A3) in "Dr Pepper" (A4):

• Za "Pepsi, "Vnesite"= B3-B5-B7+B8”V D3.
• Za "Dr Pepper, "Tip"= B4-B6-B7+B8”V D4.

2. korak: Izračunajte vrednosti grafikona za območja presečišč dveh krogov.

Pri izračunu števil, ki predstavljajo presečišče dveh nizov-kjer vrednosti znotraj danega območja presečišča hkrati pripadajo dvema nizoma-se pojavi isto vprašanje podvojenih vrednosti.

Na srečo bi morali za rešitev težave odpraviti le trenutek. Še enkrat, te številke iščemo:

Iz dejanskih podatkov izberite ustrezno vrednost (B5) in od nje odštejemo vrednost, ki predstavlja območje, kjer se vsi trije krogi prekrivajo (B8).

Ugotovimo, koliko anketirancev je dejansko odgovorilo "Coca-Cola in Pepsi " z vnosom te formule v celico D5:

1	= B5- $ B $ 8

Kopirajte formulo v celice D6 in D7 tako, da povlečete ročico za polnjenje navzdol.

3. korak: Kopirajte številko, povezano s presečiščem treh nizov, v stolpec vrednost grafikona.

Edina vrednost, ki jo lahko vzamete iz izvirnih podatkov, je tista na sredini diagrama.

Dopolnite stolpec vrednosti grafikona tako, da v sosednjo celico prepišete število anketirancev, ki so izbrali vse tri blagovne znamke (D8). Preprosto vnesite "= B8”V D8.

4. korak: Očrtajte vrednosti osi x in y za kroge Vennovega diagrama.

V prazni celici blizu tabele z vašimi podatki preslikajte koordinate osi x in y, ki bodo uporabljene kot središča krogov.

Naslednje vrednosti so konstante, ki določajo položaj krogov Vennovega diagrama na grafikonu, kar vam daje popoln nadzor nad tem, kako daleč drug od drugega bodo krogi postavljeni.

V 13. koraku se boste naučili, kako spremeniti velikost prekrivajočih se področij s spreminjanjem teh vrednosti.

Zaenkrat jih morate le kopirati v preglednico:

Vrednost X	Y Vrednost
38	35
50	65
62	35

Za Vennove diagrame samo z dvema kategorijama uporabite te koordinate:

Vrednost X	Y Vrednost
39	50
66	50

5. korak: Poiščite vrednosti velikosti kroga, ki ponazarjajo relativne deleže (neobvezno).

Če teh podatkov ne potrebujete, pojdite na Korak #7.

Da bi bili krogi sorazmerni z referenčno vrednostjo (C2), nastavite stolpec z oznako »Velikost kroga«In kopirajte to formulo v G2.

1

= 250*C2

Za zapisnik, številka "250”Je stalna vrednost, ki temelji na koordinatah in pomaga pri prekrivanju krogov.

Izvedite formulo za preostali dve celici (G3: G4), ki z vlečenjem ročice za polnjenje označujeta druga dva kroga diagrama.

6. korak: Ustvarite podatkovne oznake po meri.

Preden lahko končno nadaljujemo z gradnjo diagrama, hitro oblikujmo podatkovne oznake po meri, ki bodo vsebovale ime določenega niza skupaj z ustrezno vrednostjo, ki šteje število anketirancev, ki so izbrali samo eno znamko kole.

Nastavite drug lažni stolpec (stolpec H) in vnesite naslednjo formulo H2:

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

Izvedite formulo za preostale celice (H3: H4).

Ko sledite vsem korakom, opisanim zgoraj, bi morali biti vaši podatki videti tako:

Korak #7: Ustvarite prazen grafikon razpršitve XY.

Končno imate vse podatke iz grafikona za izdelavo osupljivega Vennovega diagrama. Kot izhodišče nastavite prazno razpršilno ploskev.

1. Izberite poljubno prazno celico.
2. Pojdite na Vstavi zavihek.
3. Kliknite na “Vstavite razpršilec (X, Y) ali mehurček”.
4. Izberite “Razpršilo.”

8. korak: Dodajte podatke grafikona.

Dodajte vrednosti osi x in y, da označite položaj krogov. Z desno miškino tipko kliknite grafikon in izberite »Izberite Podatki”Iz menija, ki se prikaže.

V Izberite vir podatkov pogovorno okno, izberite »Dodaj.”

Ko ste tam, dodajte novo podatkovno serijo z imenom »Coca-Cola:”

1. Za "Ime serije,”Označi celico B2.
2. Za "Vrednosti serije X,”Izberite celico E2.
3. Za "Vrednosti serije Y,”Izberite celico F2.

Uporabite to metodo za ustvarjanje Serija “Pepsi” (B3, E3, F3) in Serija “Dr Pepper” (B4, E4, F4).

9. korak: spremenite obseg vodoravne in navpične osi.

Prilagodite osi, da se začnejo pri 0 in končajo pri 100, da označite podatke na sredini sredine območja grafikona.

Z desno miškino tipko kliknite navpično os in izberite »Oblika osi.”

V Oblika osi v podoknu opravil naredite naslednje:

1. Pomaknite se do Možnosti osi zavihek.
2. Nastavite Najmanjše meje do “0.”
3. Nastavite Največje meje do “100.”

Skočite na vodoravno os in ponovite isti postopek.

10. korak: Odstranite osi in mrežne črte.

Očistite grafikon tako, da izbrišete osi in mrežne črte. Z desno miškino tipko kliknite vsak element in izberite »Izbriši.”

Zdaj bi bil pravi čas, da svoj grafikon povečate, da boste lahko bolje videli svoj novi Vennov diagram. Izberite grafikon in povlecite ročice, da ga povečate.

Tukaj je tisto, kar bi morali imeti na tem mestu-minimalizem v najboljšem primeru:

11. korak: Povečajte označevalce podatkov.

Za poenostavitev postopka povečajte označevalce ali pa se zakopljejo pod kroge naprej po cesti.

Z desno miškino tipko kliknite katerega koli od treh podatkovnih označevalnikov in izberite »Oblikujte niz podatkov.”

V podoknu opravil sledite tem preprostim korakom:

• Kliknite na “Fill & Line”.
• Preklopite na Marker zavihek.
• Spodaj "Možnosti označevalcev,"Preverite"Vgrajeno”Izbirni gumb in nastavite Velikost vrednost na "10.”
• Spodaj "Izpolni,"Izberite"Trdno polnilo.”
• Kliknite na “Barva polnila”, Da odprete barvno paleto in izberete Črna.

Izperite in ponovite za vsako serijo podatkov.

12. korak: Narišite kroge.

Brez zapiranja zavihka se pomaknite navzdol do Meja razdelka in ustvarite kroge za Vennov diagram po naslednjih navodilih:

1. Kliknite »Trdna črta.”
2. Odprite barvno paleto in izberite različne barve za vsak podatkovni marker:
   1. Za Serija “Coca-Cola,”Pick zlato.
   2. Za Serija “Pepsi,”Izberite zelena.
   3. Za Serija “Dr Pepper,”Izberite modra.
3. Spremeni "Preglednost"Do"40%.”
4. Spremeni "Premer”Za vsako serijo podatkov do ustreznih vrednosti v stolpec Velikost kroga (stolpec G):
   1. Za Serija “Coca-Cola,”Vnesite "250" (G2).
   2. Za Serija “Pepsi," vtipkaj “230” (G3).
   3. Za Serija “Dr Pepper,”Nastavite vrednost na "200" (G4).

OPOMBA: Če želite, da so krogi enaki, preprosto nastavite Premer vrednost na "250" za vsakogar.

Korak #13: spremenite koordinate osi x in y, da prilagodite prekrivajoča se območja (neobvezno).

Običajno morate pustiti koordinate X in Y, ki določata položaj krogov, nespremenjene. Za tiste, ki si želite prilagoditi način prekrivanja krogov, pa to storite tako.

Če želite osvežiti spomin, si oglejte koordinate, na katerih temelji Vennov diagram, uporabljen v vadnici.

Vrednost X	Y Vrednost
38	35
50	65
62	35

To sta vrednosti osi x in y, ki postavljata prvo (Serija "Coca-Cola"), drugi (Serija "Pepsi") in tretji (Serija "Dr Pepper") krog. Takole deluje:

Kot ste morda uganili, lahko brez težav spremenite, koliko se krogi prekrivajo, tako da spremenite koordinate-ki vam omogočajo premikanje krogov levo in desno (vrednosti osi x) ter gor in dol (vrednosti osi y), kakor se vam zdi primerno .

Na primer, potisnimo krog, ki predstavlja Serija "Coca-Cola" nekoliko navzgor, s čimer se poveča prekrivajoče območje med Serija "Coca-Cola" in Serija "Pepsi"-Ker imamo razmeroma veliko anketirancev, ki pijejo samo ti dve znamki.

Če želite izvedeti trik, preprosto spremenite ustrezno vrednost osi y.

Uporaba te metode bo vaše Vennove diagrame dvignila na povsem novo raven, zaradi česar so neverjetno vsestranski.

Korak 14: Dodajte podatkovne oznake.

Preostali koraki bodo namenjeni povečanju diagrama.

Za vsako vrsto podatkov ponovite postopek, opisan v korakih 14 in 15.

Najprej dodajmo podatkovne oznake. Z desno tipko miške kliknite oznako podatkov, ki predstavlja Serija "Pepsi" in izberite "Dodajte podatkovne oznake.”

Korak 15: Prilagodite podatkovne oznake.

Privzete vrednosti zamenjajte z oznakami po meri, ki ste jih predhodno oblikovali. Z desno miškino tipko kliknite katero koli oznako podatkov in izberite »Oblikujte podatkovne oznake.”

Ko se prikaže podokno opravil, naredite naslednje:

1. Pojdite na Možnosti oznak zavihek.
2. Kliknite »Vrednost iz celic.”
3. Označite ustrezno celico iz stolpca Label (H2 za Coca-Colo, H3 za Pepsi in H4 za dr. Pepperja).
4. Kliknite »V REDU.”
5. Počistite polje »Y Vrednost" škatla.
6. Počistite polje »Pokaži vodilne vrstice" škatla.
7. Spodaj "Položaj nalepke,”Izberite naslednje:
   1. Za Serija “Pepsi,"Kliknite"Zgoraj.”
   2. Za Serija “Coca-Cola,"Izberite"Levo.”
   3. Za Serija “Dr Pepper,"Izberi"Prav.”
8. Prilagodite velikost pisave/krepkost/barvo pod Domov > Pisava.

Ko končate ta korak, bi moral biti vaš diagram videti tako:

Korak #16: Dodajte besedilna polja, povezana z vrednostmi presečišč.

Za vsak prekrivajoči se del diagrama vstavite štiri besedilna polja, da prikažete ustrezno število ljudi, ki so se odločili za več blagovnih znamk hkrati.

1. Izberite območje ploskve.
2. Pojdite na Vstavi zavihek.
3. Kliknite na “Besedilno polje”Gumb.
4. Ustvarite besedilno polje.
5. Vnesite "=" v Vrstica formule.
6. Označite ustrezno celico od stolpec Vrednost grafikona (stolpec D).
7. Velikost pisave/krepkost/barvo prilagodite drugim oznakam pod Domov > Pisava.

Na enak način v grafikon dodajte preostale vrednosti prekrivanja. Tu je še en posnetek zaslona, ​​ki vam bo pomagal krmariti po postopku.

Korak #17: Skrijte označevalce podatkov.

Ne potrebujete več podatkovnih označevalcev, ki podpirajo diagram, zato jih naredimo nevidne. Z desno miškino tipko kliknite kateri koli podatkovni označevalec, odprite Oblikujte niz podatkov zavihek in naredite naslednje:

1. Kliknite na “Fill & Line”.
2. Preklopite na Marker zavihek.
3. Spodaj "Izpolni,"Izberite"Brez polnjenja.”

Ponovite postopek za vsako oznako podatkov.

18. korak: Dodajte naslov grafikona.

Na koncu dodajte naslov grafikona:

1. Izberite območje ploskve.
2. Kliknite na “Elementi grafikona”.
3. Preverite »Naslov grafikona" škatla.

Spremenite naslov grafikona tako, da ustreza vašim podatkom, in vaš zvezdni Vennov diagram je pripravljen!