Trikovi u Excelu 86. deo – Uočite obrasce kretanja i ekstremne vrednosti

Grafikoni rasipanja pružaju raznovrsnost ali zahtevaju različit tok rada

PROBLEM KORISNIKA: Kako da napravim grafikon rasipanja s dve serije?

REŠENJE: Napravite grafikon s jednom serijom. Zatim odaberite drugu seriju i upotrebite Paste Special da te podatke unesete u grafikon.

Većina grafikona koje koristite u Excel-u imaju u koloni 1 oznake za osu kategorija, u koloni 2 podatke za prvu seriju, u koloni 3 podatke za treću seriju i tako dalje. Microsoft je ugurao grafikon rasipanja u isti program koji se koristi za izradu običnih grafikona pa to malo otežava navođenje druge serije.

U grafikonu rasipanja prva kolona se koristi za navođenje numeričke lokacije duž x ose. Druga kolona se koristi da se navede numerička lokacija duž y ose. Zbog toga su grafikoni rasipanja poznati i kao X-Y grafikoni.

 Naučnici koriste grafikone rasipanja da upoređuju dve promenljive. Ako imate neku promenljivu koju možete da kontrolišete, stavite je na x osu. Na istu osu stavite i drugu promenljivu koja zavisi od prve. Rezultujuća slika na grafikonu omogućava vam da uočite obrasce kretanja i ekstremne vrednosti. Vešti korisnici Excel-a koriste grafikone rasipanja zato što im rešavaju niz problema. Jedini način da se na x osi prikažu sati i minuti jeste preko grafikona rasipanja. Ti grafikoni su i zaista dobar način povlačenja linije na određenom mestu na grafikonu.

 Ja koristim grafikone rasipanja da upoređujem dve različite populacije podataka. Taj konkretni grafikon je užasno teško napraviti u jednom koraku.

Koji god da je razlog, grafikon rasipanja mi se gotovo uvek nameće kada hoću da kupim polovan automobil. Počećemo od tog scenarija.

 Pregledao sam jedan sajt za prodaju polovnih automobila i pronašao sve “Alfa Romeo Spider Veloce” modele koji se prodaju. Posle pravljenja liste, upoređivanja pređene kilometraže i cene, hteo sam da vidim kakav je odnos između kilometraže i cene. Kilometražu sam stavio u kolonu 1 a cenu u kolonu 2. Iz razloga koji će kasnije biti jasni, naslov za kolonu 2 treba da bude “Alfa”.

1. Izaberite te dve kolone uključujući i naslove.

2. Izaberite opcije Insert, Scatter, pa Scatter With Only Markers.

3. Izaberite opcije Layout, Chart Title, pa None

4. Zatim izaberite Layout, Axis Titles, Primary Horizontal Axis Title, pa Title Below Axis.

5. Izaberite Axis Title pa unesite “Milja” (000). Pritisnite Enter.

6. Zatim izaberite Layout, Axis Titles, Primary Vertical Axis Title, pa Title Below Axis.

7. Izaberite Axis Title pa unesite “Cena”. Pritisnite Enter.

8. Zatim izaberite Layout, Legend, pa Show Legend at Top.

Sada imate grafikon (Slika 1).

Očekivali biste da tačke opadaju od gornjeg levog do donjeg desnog ugla. Kako se kilometraža povećava, cena bi trebalo da opada. Tačke približno i opadaju po tom obrascu, ali ima i ekstremnih vrednosti.

Automobil s najvećom cenom je onaj koji je prešao samo 13.000 milja. To je impresivno za automobil star 20-30 godina. Ali ima još jedan automobil po istoj ceni sa pređenih 113.000 milja. Ta tačka je ekstremna vrednost. Ostali automobili s tolikom kilometražom koštaju upola manje. Ili je taj automobil netaknut i potpuno sređen ili vlasnik nema osećaj za realnost.

Što se tiče ovog grafikona, hteo sam da uporedim “Alfa Romeo” s modelom “Fiat Spider”. Oba automobila imaju sličan dizajn karoserije koji je napravila kompanija Carozzeria Pinifarina. Napravio sam drugi par kolona za sve “fijate” koji su u prodaji. U kolonu 1 stavio sam milje a u kolonu 2 cenu. Naslov za kolonu 2 je “Fiat”.

 Da biste postojećem grafikonu dodali drugu seriju (Slika 2). primenite sledeće korake:

1. Izaberite novi opseg podataka u dve kolone. Obuhvatite i naslove.

2. Pritisnite Ctrl+C da ga iskopirate.

3. Pritisnite taster miša jednom na grafikonu.

4. Na kartici Home otvorite padajući meni Paste i izaberite opciju Paste Special. Umesto ovoga možete i da pritisnete Alt+E+S. Excel će prikazati verziju grafikona prema opciji Paste Special.

5. U opciji Add Cells As promenite postavku od New Points u New Series.

6. Neka vrednosti – Values (Y) budu u kolonama (Columns).

7. Pošto ste u 1. koraku obuhvatili i naslove, izaberite opciju Series Names in First Row.

8. Izaberite opciju Category (X Values) in First Column.

9. Nemojte da uključite opciju
Replace Existing Categories.

10. Pritisnite dugme OK.

Rezultat je grafikon rasipanja koji daje poređenje opcija “Alfa” i “Fiat” (Slika 3). “Fiat” pruža najskuplji kao i najjeftiniji izbor.

 Dijalog Paste Special traži više pritisaka na taster miša ali mnogo olakšava dodavanje naknadnih serija.

JOŠ POJEDINOSTI: Kod serija u grafikonu rasipanja uobičajeno je da u svakoj seriji postoji različit broj tačaka. To bi bilo neobično kod linijskog, stupčastog ili trakastog grafikona.

Radi kontrasta, izvukao sam i podatke za druga dva automobila snova: “Avanti” i “Corvette Stingray”. Bilo je previše “korveta” da bih ih sve stavio na listu, pa sam ih uzorkovao tako što sam uzeo po jednu vrednost sa svake strane web sajta. Pomoću pomenutih 10 koraka za svaki tip kola dolazite do grafikona koji daje poređenje sva četiri modela (Slika 4).

Za automobile s malom kilometražom cene se krežu od 15.000 do 35.000 $. Međutim, kada se kilometraža približi do 100.000 milja, možete naći poneki primerak svakog automobila u cenovnom rasponu od 5.000 do 10.000 $ (pogledajte zaokružene tačke na grafikonu). Ako crtate samo oznake a ne i linije između oznaka, onda podatke nije potrebno sortirati.

Grafikoni rasipanja su bolji izbor kada imate vremenske podatke gde tačke nisu u fiksnim intervalima. Recimo da počnete sa 100 $ 31. decembra i da svakog dana dodajete po jedan dolar. Novac brojite samo na nekoliko meseci. Ta linija bi trebalo da bude sasvim prava jer nikada ne propuštate da dodate po jedan dolar.

Donja slika (Slika 5) daje poređenje dve vrste linijskog grafikona i grafikona rasipanja s linijom. Prvi izbor, s tekstualnom osom, stavlja svaku tačku na jednaku udaljenost i ne daje tačan prikaz. Drugi izbor koristi datumsku osu. To bi trebalo da bude tačno, ali ta linija nije prava. Treći izbor koristi grafikon rasipanja. On je jedini koji prikazuje sasvim pravu liniju.

Kada pratite podatke po vremenu, grafikon rasipanja je zaista jedini izbor. Ako pokušate da koristite linijski grafikon, opcija datumske ose uneće sva vremena u jednu kolonu i vratiti sve tačke na ponoć tog dana. Grafikon rasipanja je jedini način za prikaz stvarnog kretanja tokom vremena (Slika 6).

PAŽNJA: U Excel-u je stavljanje oznaka u grafikone rasipanja veoma teško. Ako treba da označite pojedinačne tačke, potražite pomoćni program Chart Labeler koji je napravio Rob Bovey. Besplatan je i radi upravo ono što vam je potrebno.

Grafikoni rasipanja su sjajan način za crtanje po grafikonu. Da li vam treba prava linija? Za crtanje linije su potrebne samo dve tačke.

Da biste dodali linije na vrednostima 70 i 90 na grafikonu, primenite sledeće korake:

1. Za prvu liniju su vam potrebne dve tačke podataka. Planirajte da se x osa proteže od 0 do 1 (zamislite to kao 0 do 100% širine grafikona). Unesite opseg koji prikazuje da je u tački 0 visina 70 a u tački 100 isto 70. To će biti prava linija preko celog grafikona na visini od 70. To je uneto kao opseg od dva reda i dve kolone ćelija.

2. Unesite opseg za drugu liniju. Unesite 0,90;1,90 u četiri ćelije.

3. Izaberite prvi opseg četiri ćelije. Pritisnite Ctrl+C da ga iskopirate.

4. Izaberite grafikon pa opciju Paste Special (Slika 7). Izaberite opcije New Series. Columns pa Categories in First Column. Nemojte da uključite druge dve opcije.

Stvari već sad počinju da izgledaju loše (Slika 8). Ta nova serija je dodata kao naslagana serija na vrhu A i B.

1. Izaberite tu novu seriju. Na kartici Design izaberite opciju Change Chart Type. Izaberite grafikon rasipanja s linijom. Sada se vaša nova serija prikazuje kao linija, ali ne tako dobra. Ne ide preko celog grafikona i nije ni blizu 70 na levoj osi.

2. Problem je što nova serija koristi sekundarnu vertikalnu osu koja ide od 0 do 80 i novu sekundarnu horizontalnu osu koja ide od 0 do 1,5. Treba da prepravite obe te ose da biste promenili minimalnu i maksimalnu vrednost (Slika 9).

1. Izaberite sekundarnu vertikalnu osu. Izaberite opciju Format Selection. Promenite minimum na Fixed i 50 da odgovara levoj vertikalnoj osi. Zatim promenite maksimum na Fixed i 100. Nemojte još da zatvorite dijalog Format.

2. Kliknite na osu na vrhu grafikona. U dijalogu Format izaberite minimum Fixed 0 i maksimum Fixed 1 (pogledajte 1. korak gde je plan bio da raspon x ose bude od 0 do 1). Sada se linija pruža preko celog grafikona.

3. Izaberite opseg od četiri ćelije za drugu liniju. Iskopirajte te ćelije. Kliknite na grafikon. Izaberite opciju Paste Special. Upotrebite iste postavke kao na Slici 4.

Sada imate grafikon s linijama koje idu skroz preko grafikona na pravom mestu (Slika 10).

Da biste se oslobodili dodatnih osa i očistili linije, primenite sledeće korake:

1. Kliknite na brojeve na desnoj osi i pritisnite Delete.

2. Zatim kliknite na brojeve na gornjoj osi i pritisnite Delete.

3. Izaberite liniju na 70. Izaberite opcije Layout, Data Labels pa None. Na kartici Format upotrebite padajući meni Shape Outline da promenite boju u crvenu, debljinu na ¼ tačke a tip linije na isprekidanu. U dijalogu Format izaberite Marker Style pa None.

4. Ponovite 11. korak i za liniju na 90.

Tako ste dobili grafikon s linijama koje idu skroz preko njega (Slika 11).

Serija podataka grafikona rasipanja može da se koristi da se nacrta strelica koja uvek pokazuje na visinu jednog stubića (Slika 12). Da bi strelica pokazivala na stubić E, mislio sam da bi dobra polazna tačka bila na 2/7 puta preko celog grafikona. Krajnja tačka bi bila na 4,5/7 puta preko celog grafikona. Za polaznu visinu sam odabrao 95. Za krajnju visinu sam upotrebio formulu da dodam 1 tački podataka za E. Dodajte seriju podataka kao što je opisano u gornjem tekstu. Da biste nacrtali vrh na strelici izaberite liniju, pa opcije Format, Shape Outline i Arrow pa izaberite liniju koja se završava strelicom.

Rezultat: Kada se promeni vrednost za stubić E, strelica i dalje pokazuje na vrh stubića (Slika 13).