Ovaj članak će govoriti o tome kako analizirati podatke koristeći zaokretnu tablicu. Za obuku možete koristiti tabelu dostupnu na ovom linku (jednostavna tablica.xlsx).
Prva stvar koju ćete možda trebati analizirati koristeći zaokretnu tablicu je da zbrojite međuzbirove. U našem primjeru, ovo može biti potreba za izračunavanjem obima prodaje za sve trgovine za svaki datum.
Da biste to učinili, kliknite na zaglavlje bilo kojeg reda zaokretne tablice (u našem primjeru, to su polja Datum, prodajno mjesto i Marka telefona), i na otvorenim karticama Rad sa pivot tabelama idite na karticu Opcije. Morate pritisnuti dugme na njemu. Opcije polja u grupi aktivno polje.
U prozoru koji se otvori, prva kartica će biti kartica.
Nepostojanje takve oznake znači da niste odabrali zaglavlje reda, odnosno da je kursor postavljen na ćeliju sa numeričkom vrijednošću.
Bookmark Međuzbroji i filteri Možete odabrati uvjet za prikaz međuzbirova. Nude se sledeći uslovi:
Postavljanjem automatskog međuzbroja dobijamo sljedeću tabelu koja sadrži međuzbrojeve za svaki uslov:
Ako se naredbom postavljaju podzbroji Opcije polja, ne daje vidljive rezultate, provjerite postavke za prikaz međuzbirova pomoću naredbe Međuzbirovi grupe Layout tabs Konstruktor.
Recimo da želimo prikazati međuzbirove samo za datume, skrivajući međuzbroje za prodajna mjesta. Da biste to učinili, kliknite na bilo koje polje tablice s nazivom trgovine i pozovite kontekstni izbornik. U njemu morate poništiti izbor uvjeta Međuzbroj: prodajno mjesto. Kao što vidimo, međuzbirovi ostaju samo za datume:
Često je potrebno sortirati podatke u zaokretnoj tabeli da biste ih bolje razumjeli. Da biste to učinili, jednostavno odaberite polje po kojem želite sortirati, idite na karticu Generale, u grupi Uređivanje kliknite na dugme Sortiraj i filtriraj i postavite uslove sortiranja koji su vam potrebni.
Vrlo korisna karakteristika za analizu informacija u pivot tabeli je mogućnost grupisanja podataka. Na primjer, našu prodaju moramo grupisati po sedmicama u mjesecu. Da biste to učinili, odaberite datume koji su uključeni u prvu sedmicu (15.05-21.05):
Imajte na umu da smo radi lakšeg odabira skupili podatke za pojedinačne trgovine korištenjem + dugmeta na lijevoj strani ćelije s nazivom trgovine.
Zatim morate pokrenuti naredbu Grupirajte po izboru grupe Grupa tabs Opcije. U tabeli će se pojaviti nova kolona u kojoj je polje Grupa1će spojiti polja koja smo odabrali.
Sve što ostaje je preimenovati ime grupe jednostavnim uređivanjem ćelije:
Da razgrupirate, samo koristite naredbu Razgrupiraj se iz iste grupe, nakon odabira polja koje treba razgrupisati. Imajte na umu da ne možete razgrupisati polje koje smo uključili u uslov izrade zaokretne tabele - na primjer, polje Mjestu prodaje ili datum.
Razmotrimo još jedan način prikaza podataka koji će nam pomoći da analiziramo informacije iz naše tabele. Na primjer, moramo znati obim prihoda ne u novčanom smislu, već kao postotak ukupnog prihoda za cijeli period prodaje.
Da biste to učinili, odaberite bilo koju ćeliju u koloni Prihod naše zaokretne tablice. Nakon toga morate pokrenuti naredbu Opcije polja u grupi aktivno polje tabs Opcije.
U dijaloškom okviru koji se otvori idite na karticu Dodatni proračuni i izaberite iz padajućeg menija Procenat ukupnog iznosa po koloni. Nakon što kliknete na dugme OK, naša tabela će izgledati ovako:
Ako podaci u tabeli neće biti prikazani u procentima, provjerite postavke za format brojeva ćelija (ovo se može učiniti odmah u dijaloškom okviru Opcije polja klikom na dugme Numerički format, ili pozivanjem odgovarajućeg prozora iz kontekstnog menija).
Za analizu lista podataka (tabela podataka) u programu Excel 2007 koje imaju mnogo redova ili zapisa, često se koriste alati kao što su zaokretne tabele. Zaokretne tabele znatno olakšavaju pregled, manipulaciju i sumiranje podataka u Excel 2007 listama.
Da bi Excel radio sa unesenim podacima kao tabela podataka, a ne kao jednostavan skup podataka, mora biti formatiran kao tabela. Da biste to uradili, kliknite na dugme Tabela na kartici Umetanje. U dijaloškom okviru Kreiraj tabelu (slika 1), navedite planirani opseg u koji će tabela biti smeštena i potvrdite izbor u polju za potvrdu Tabela sa zaglavljima.
Rice. jedan.
Formatiranje će se primijeniti na ovaj raspon, tj. primenjuje se brzi stil tabele, podrazumevano postavljen, dok su aktivirani kontekstualni alati pod opštim nazivom "Rad sa tabelama" koji su uključeni u kontekstualnu karticu Dizajn. Također možete koristiti alat Formatiraj kao tablicu na kartici Početak za formatiranje tabele.
Rice. 2.
Za izradu izvještaja o ovoj tabeli, preporučljivo je koristiti moćni alat „Pivot Table“. Da biste primijenili ovaj alat na liste podataka ili tablice podataka, jedna od ćelija tablice podataka mora biti aktivirana, na primjer, ćelija u tabeli "Zalihe na zalihama". Zatim kliknite na dugme "Pivot Table" koje se nalazi na kartici "Insert" u grupi "Table" (Slika 3).
Rice. 3.
U dijaloškom okviru Kreiraj zaokretnu tabelu koji se otvori, potrebno je da izaberete (istaknete) tabelu ili opseg i naznačite gde želite da postavite izveštaj (po mogućnosti na novi list), a zatim kliknite na OK. Otvoriće se poseban čarobnjak za pivot table (slika 4).
Rice. četiri.
Slika izvještaja (PivotTable1) se prikazuje na lijevoj strani radnog lista, a alati za kreiranje pivot tablice nalaze se na desnoj strani lista: četiri prazna područja i lista polja. Da biste napravili izvještaj, na desnoj strani prevucite potrebna polja u odgovarajuća područja zaokretne tablice: "Filter izvještaja", "Nazivi kolona", "Nazivi redova" i "Vrijednosti".
Na primjer, ako odaberete polja: Broj skladišta, Naziv, Cijena (UAH) i prevučete ih u odgovarajuća područja: "Nazivi kolona", "Nazivi redova" i "Vrijednosti", oni će biti prikazani u ovim područjima na desna strana. U tom slučaju, pivot tabela ili izvještaj će biti napravljeni na lijevoj strani radnog lista (slika 5).
Rice. 5.
Treba napomenuti da se u oblasti "Vrijednosti" vrše neki matematički proračuni, na primjer, sumiranje (Zbir po polju Cijena). Da biste promijenili vrstu obračuna, u području "Vrijednosti" kliknite lijevom tipkom miša na polje "Iznos po cijeni" i odaberite naredbu "Postavke polja vrijednosti" u meniju koji se otvori, a zatim u dijaloškom okviru "Postavke polja vrijednosti" , odaberite željenu funkciju i kliknite na dugme OK.
Da biste promijenili strukturu zaokretne tablice, na desnoj strani lista prevucite polja u drugo područje zaokretne tablice ili ih izbrišite. Treba napomenuti da da biste izbrisali polje, morate ga prevući izvan tabele.
Moćni alati za analizu podataka u programu Excel 2007 su:
Alati za analizu šta ako se postavljaju na karticu Podaci u grupi Alati za podatke, a Pronađi rješenja na karticu Podaci u grupi Analiza.
Izbor parametara daje rješenje problema odabira parametra za funkciju jednog argumenta. Scenario Manager je dizajniran za kreiranje scenarija (prvo dodajte nekoliko scenarija za različite slučajeve), a zatim pregledajte scenarije kako biste predvidjeli proces i generirali izvještaj o scenariju.
Program "Traganje za rješenjima" namijenjen je rješavanju složenih sistema jednačina, linearnih i nelinearnih optimizacijskih problema. Dodatak Solver je baziran na iterativnim metodama.
Excel nije samo uređivač proračunskih tablica, već i moćan alat za razne matematičke i statističke proračune. Aplikacija ima ogroman broj funkcija dizajniranih za ove zadatke. Međutim, nisu sve ove funkcije omogućene po defaultu. Skup alata pripada takvim skrivenim funkcijama. "Analiza podataka". Hajde da saznamo kako se to može omogućiti.
Da biste iskoristili prednosti koje pruža funkcija "Analiza podataka", morate aktivirati grupu alata "Paket analiza" slijedeći određene korake u postavkama programa Microsoft Excel. Algoritam ovih radnji je skoro isti za verzije programa iz 2010., 2013. i 2016. godine, a ima samo male razlike u verziji iz 2007. godine.
Nakon dovršetka ovih koraka, navedena funkcija će se aktivirati, a njen alat će biti dostupan na Excel traci.
Sada možemo pokrenuti bilo koji od alata u grupi "Analiza podataka".
Rad u svakoj funkciji ima svoj algoritam akcija. Koristeći neke od alata grupe "Analiza podataka" opisano u zasebnim lekcijama.
Kao što vidite, iako je kutija sa alatima "Paket analiza" i nije aktiviran po defaultu, proces omogućavanja je prilično jednostavan. Istovremeno, bez poznavanja jasnog algoritma akcija, malo je vjerovatno da će korisnik moći brzo aktivirati ovu vrlo korisnu statističku funkciju.
ZADATAK #1
Statistička analiza podataka u MS Excel-u
Svrha rada: naučiti kako obraditi statističke podatke koristeći ugrađene funkcije MS Excel-a; istražite karakteristike paketa analize i njegovih alata: " Generacija slučajnih brojeva», "Trakasti grafikon" , " Deskriptivna statistika" na primjeru mjerenja brzine obrade.
U skladu sa smjernicama za laboratorijski rad "Mjerenje brzine vozila" (u disciplini "Istraživanje i projektovanje puteva"), obraditi eksperimentalne mjerne podatke metodama matematičke statistike u programu Excel. Za što:
1. Izračunajte statističke karakteristike koristeći ugrađene funkcije: - minimalnu vrijednost brzine kretanja Vmin;
Maksimalna vrijednost brzine kretanja Vmax; - prosječna vrijednost brzine kretanja Vav;
Standardna devijacija S;
Standardna devijacija srednje vrijednosti Sp;
Studentov koeficijent (za određivanje intervala povjerenja) t; - interval povjerenja za R = 0,95.
2. Dobijte statističke karakteristike pomoću alata "Deskriptivna statistika"iz dodatnog paketa" Analiza podataka ".
3. Konstruirajte histogram distribucije brzine.
4. Konstruirajte kumulativnu krivu (kriva kumulativna frekvencija).
5. Konstruirati teorijsku krivulju za raspodjelu brzine kretanja.
Da biste dobili dovoljnu količinu početnih podataka (rezultata mjerenja brzine), koristite simulacijski eksperiment koristeći " Generisanje slučajnih brojeva» dodaci "Analiza podataka".
Prilikom izvođenja p.p. 3 i 4, odaberite interval brzine ("džep" - u Excel terminologiji), koji vam omogućava da dobijete najsimetričniji histogram koji pokazuje zakon normalne distribucije.
Primjer izvršenja je dat u priloženoj datoteci BasicsPC1-Student.xls.
Smjernice
Pretpostavimo da smo uradili seriju od 10 eksperimenata, mjereći neku vrijednost X. Tabela 1. Približan prikaz lista "Obrada eksperimenta"
Unosi u kolone D i E su savjeti koji će vam pomoći da shvatite koje ćemo karakteristike izračunati. Kolona F bi za sada trebala biti prazna, u nju će biti smještene naše formule.
Počnimo s obradom rezultata izračunavanjem broja eksperimenata n.
Za određivanje broja vrijednosti koristi se posebna funkcija pod nazivom COUNT. Da biste unijeli formulu sa funkcijama, koristite Čarobnjak za funkcije, koji se pokreće naredbom "Insert Function" preko menija "Insert" - "Function" ili dugmeta na traci sa alatkama sa oznakom fx.
Kliknimo na ćeliju F6, gdje bi trebao biti rezultat i pokrenimo čarobnjak za funkcije.
Prvi korak rada (slika 1) služi za odabir željene funkcije.
Statističke funkcije se koriste za obradu eksperimentalnih podataka. Stoga, prije svega, na listi kategorija odaberite kategoriju "Statistički". U drugom prozoru pojavljuje se lista statističkih funkcija.
Lista funkcija je sortirana po abecednom redu, što olakšava pronalaženje funkcije COUNT koja nam je potrebna („Broji broj brojeva na listi argumenata“).
Kada je ova funkcija označena, pritisnite dugme Ok i idite na korak 2.
Drugi korak (slika 2) se koristi za postavljanje argumenata funkcije.
Funkcija COUNT mora odrediti koje brojeve treba brojati ili u kojim ćelijama se ti brojevi nalaze. Sljedeće dvije faze obrade serije eksperimenata izvode se na sličan način.
U ćeliji F7, koristeći funkciju AVERAGE, izračunava se srednja vrijednost uzorka, u ćeliji F8, standardna devijacija uzorka, koristeći STDEV funkciju. .
Isti raspon ćelija služi kao argument za ove funkcije.
Za izračunavanje intervala povjerenja potrebno je odrediti Studentov koeficijent. Zavisi od vjerovatnoće greške (sa uobičajeno datom pouzdanošću od 95%, vjerovatnoća greške je 5%) i od broja stupnjeva slobode n-1).
Za pronalaženje Studentovog koeficijenta koristi se statistička Excel funkcija STUDISTRIP ("Studentova distribucija inverzna"). Karakteristika ove funkcije je da se prvi argument, broj 5% (ili 0,05) unosi u odgovarajući prozor sa tastature. Za drugu, navedite adresu ćelije u kojoj se nalazi vrijednost n, a zatim dodajte “-1” u prozor. Dobijamo unos "F6-1".
Uobičajena formula za množenje koristi se za pronalaženje intervala povjerenja. Naravno, umesto slova treba da budu adrese ćelija gde se nalaze Studentov koeficijent i standardna devijacija srednje vrednosti. Po pravilu, vrijednost intervala povjerenja se zaokružuje na jednu značajnu cifru, isti red okoline treba biti i za srednju vrijednost. Stoga se konačni rezultat može zapisati na sljedeći način: sa 95% pouzdanosti, X = 14,80 ± 0,05. Zaključno, izračunavamo relativnu grešku u određivanju X: = CI / X cf (formula: “=F11/F7”). Vrijednost relativne greške obično se izražava u procentima, imamo 0,3%.
Zadaci 2 i 3 se izvode pomoću dodatka "Paket analize" (iz menija Alati .Analiza podataka Histogram).
Da biste instalirali dodatak, pozovite meni Alati Dodaci i sa predložene liste dodataka dostupnih za instalaciju izaberite „Paket analize“ (pogledajte sl. Instaliranje dodataka
Excel na računaru.doc).
