Kako konfigurirati Excel 2010 Pivot tablice

by Tim Perdue

01 od 15

Završni rezultat

Ovo je konačni rezultat ovog vodiča Step by Step - kliknite sliku da biste vidjeli verziju pune veličine.

Došlo je do jaza između Microsoft Excela i najviših poslovnih inteligencija (BI) platformi već dugi niz godina. Poboljšanja Pivot tablice programa Microsoft Excel 2010 zajedno s još nekoliko BI značajki učinila su to pravi konkurent za BI. Excel se tradicionalno koristio za samostalnu analizu i standardni alat koji svatko izvozi svoja završna izvješća. Profesionalna poslovna inteligencija tradicionalno je rezervirana za ljubitelje SAS-a, poslovnih objekata i SAP-a.

Microsoft Excel 2010 (uz Excel 2010 Pivot Table) uz SQL Server 2008 R2, SharePoint 2010 i besplatni Microsoft Excel 2010 dodatak "PowerPivot" rezultirali su vrhunskim rješenjem poslovne inteligencije i izvješćivanja.

Ovaj vodič pokriva ravno naprijed scenarij s Excel 2010 PivotTable povezanom s bazom podataka SQL Server 2008 R2 pomoću jednostavnog SQL upita. Također koristim Slicere za vizualno filtriranje, što je novo u programu Excel 2010. U bližoj budućnosti ću obuhvatiti složenije BI tehnike pomoću Data Analysis Expressions (DAX) u programu PowerPivot za Excel 2010. Ovo najnovije izdanje programa Microsoft Excel 2010 može pružiti stvarnu vrijednost za vašu korisničku zajednicu.

02 od 15

Umetni stožernu tablicu

Postavite kursor točno na mjesto gdje želite svoju stožernu tablicu i kliknite na Umetni | Pivotna tablica.

Možete umetnuti Pivot tablicu u novu ili postojeću radnu knjigu programa Excel. Preporučujemo da razmotrite pozicioniranje pokazivača niz nekoliko redaka s vrha. To će vam dati prostor za zaglavlje ili informacije o tvrtki u slučaju da podijelite radni list ili ispišete.

Otvorite novu ili postojeću radnu knjigu programa Excel 2010 i kliknite na ćeliju u kojoj želite da gornji lijevi kut Pivot tablice bude.
Kliknite karticu Umetni i kliknite padajući izbornik za podešavanje tablice u odjeljku Tablice. Odaberite tablicu za podešavanje. Ovo će pokrenuti obrazac za dijalog Stvaranje tablice za zakretanje.

03 od 15

Pivot tablica povezivanja na SQL Server (ili drugu bazu podataka)

Izradite SQL upit, a zatim se povežite s SQL Serverom kako biste ugradili niz podataka veze u proračunsku tablicu programa Excel.

Excel 2010 može dohvatiti podatke svih glavnih pružatelja RDBMS (Relational Database Management System) . Vozači za SQL Server trebali bi biti dostupni za povezivanje prema zadanim postavkama. No, svi glavni softver baze podataka čine upravljačke programe ODBC (Open Database Connectivity) kako bi vam omogućili povezivanje. Provjerite svoje web stranice ako trebate preuzeti ODBC upravljačke programe.

U ovom se vodiču povezujem s SQL Server 2008 R2 (besplatna verzija SQL Expressa).

A - obrazac Stvaranje tablice za podešavanje prvi je oblik pri stvaranju veze s SQL Serverom. Odaberite "Upotrijebite vanjski izvor podataka" i kliknite gumb Odaberi vezu. Ostavite mjesto gdje će se postaviti Pivot tablica ako ne želite stvoriti novi radni list i smjestiti ga tamo.
B - Obrazac postojećih veza sadrži sve veze u trenutačnoj radnoj knjizi, na računalu i mreži s kojom ste trenutačno povezani. Postojeće veze zapravo su samo tekstualne datoteke s informacijama o vezi koje su potrebne za pristup određenom izvoru podataka. U našem slučaju stvaramo novi izvor podataka. Kliknite gumb Potraži za više.
C - Kliknite gumb Novi izvor da će pokrenuti Čarobnjak za povezivanje podataka.
D - Odaberite Microsoft SQL Server i kliknite Dalje.
E - Unesite naziv poslužitelja i prijavite se vjerodajnicama. Odaberite odgovarajući način provjere autentičnosti. Ako niste sigurni koju metodu koristite, obratite se administratoru baze podataka.
- Koristite autentifikaciju sustava Windows: ova metoda koristi vašu prijavu na mrežu za pristup SQL Server bazama podataka.
- Koristite sljedeće korisničko ime i lozinku: Ova se metoda koristi kada je SQL Server konfiguriran s samostalnim korisnicima za pristup bazama podataka.
F - U ovom koraku birali smo tablicu kao rezervirano mjesto. Zamijenit ćemo stol s prilagođenim SQL-om koji će pružiti točno željene podatke u našoj Excel radnoj knjizi.
- Odaberite bazu podataka na koju ćete se povezati. U ovom primjeru povezujemo se s bazom podataka AdventureWorks uzorka koje pruža Microsoft. Provjerite povezivanje s određenom tablicom i odaberite prvu tablicu. Ne zaboravite da nećemo dohvaćati podatke iz ove tablice.
- Kliknite Završi koji će zatvoriti čarobnjaka i vratiti vas u radnu knjigu. Izbrisat ćemo tablicu rezerviranja za naš prilagođeni SQL upit.

Vratit ćete se u obrazac Stvaranje tablice za zakretanje (A). Kliknite U redu.

04 od 15

Pivot tablica privremeno povezana sa SQL tablicom

PivotTable je povezan s SQL Serverom sa tablicom rezerviranog mjesta.

U ovom trenutku povezali ste se sa tablicom rezerviranog mjesta i imate praznu tabličnu tablicu. Na lijevoj strani možete vidjeti kako će biti PivotTable i na desnoj strani nalazi se popis raspoloživih polja.

05 od 15

Otvori svojstva veze

Otvori obrazac Svojstva veze.

Prije nego počnemo odabrati podatke za PivotTable, moramo promijeniti vezu s SQL upitom. Provjerite jeste li na kartici Opcije i kliknite padajući izbornik Podatci o promjeni izvor podataka. Odaberite Svojstva veze.

Time se pojavljuje obrazac Svojstva veze. Kliknite karticu Definicija. Ovo prikazuje informacije veze za trenutačnu vezu s SQL Serverom. Dok se odnosi na datoteku veze, podaci su zapravo ugrađeni u proračunsku tablicu.

06 od 15

Ažurirajte svojstva veze uz upit

Promjena tablice u SQL upit.

Promijenite vrstu naredbe iz tablice u SQL i prebrisajte postojeći naredbeni tekst pomoću SQL upitnika. Evo upita koji sam stvorio iz baze podataka AdventureWorks uzorka:

SELECT Sales.SalesOrderHeader.SalesOrderID,
Sales.SalesOrderHeader.OrderDate,
Sales.SalesOrderHeader.ShipDate,
Sales.SalesOrderHeader.Status,
Sales.SalesOrderHeader.SubTotal,
Sales.SalesOrderHeader.TaxAmt,
Sales.SalesOrderHeader.Freight,
Sales.SalesOrderHeader.TotalDue,
Sales.SalesOrderDetail.SalesOrderDetailID,
Sales.SalesOrderDetail.OrderQty,
Sales.SalesOrderDetail.UnitPrice,
Sales.SalesOrderDetail.LineTotal,
Proizvodnja.Proizvoda.Name,
Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,
Sales.Customer.CustomerType,
Proizvodnja.Produkt.ListPrice,
Production.Product.ProductLine,
Proizvodnja.ProdukcijaSkategorija.Name AS ProductCategory
FROM Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Proizvodnja.Produkt ON Sales.SalesOrderDetail.ProductID =
Proizvodnja.Proizvod.Proizvod ID INNER JOIN Sales.Customer ON
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID AND
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOIN
Sales.vIndividualCustomer ON Sales.Customer.CustomerID =
Sales.vIndividualCustomer.CustomerID INNER JOIN
Proizvodnja.ProizvodnjaSubkategorija ON ProizvodnjaProizvod.ProizvodiSategorija ID =
Production.ProductSubcategory.ProductSubcategoryID

Kliknite U redu.

07 od 15

Primite upozorenje veze

Kliknite Yes to Connection Warning (Upozorenje o vezi).

Dobit ćete dijaloški okvir upozorenja Microsoft Excel. To je zbog toga što smo promijenili podatke veze. Kada smo izvorno stvorili vezu, spremili smo podatke u vanjsku .ODC datoteku (ODBC podatkovna veza). Podaci u radnoj knjizi bili su isti kao i .ODC datoteke sve dok smo se u stupnju # 6 promijenili iz tablice naredbe tablice u SQL naredbu. Upozorenje vam govori da podaci više nisu sinkronizirani i uklonit će se reference na vanjsku datoteku u radnoj knjizi. Ovo je u redu. Kliknite Da.

08 od 15

Pivot tablica povezana s SQL Serverom sa upitom

PivotTable je spreman za dodavanje podataka.

Ovo se vraća u radnu knjigu programa Excel 2010 s praznim tablama. Možete vidjeti da su raspoloživa polja sada različita i odgovaraju poljima u SQL upitu. Sada možemo dodati polja u tablicu za zaokretanje.

09 od 15

Dodaj polja u tablicu zaokretanja

Dodajte polja u tablicu za podešavanje.

Na popisu polja zaokretne tablice povucite područje ProductCategory do redaka oznake, područje OrderDate do oznake stupaca i područje TotalDue to Values. Slika prikazuje rezultate. Kao što vidite, polje s datumima ima pojedinačne datume, tako da je tablična tablica stvorila stupac za svaki pojedinačni datum. Srećom, Excel 2010 ima neke ugrađene funkcije kako bi nam pomogli organizirati datumska polja.

10 od 15

Dodaj grupiranje za polja s datumima

Dodajte grupe za polje datuma.

Funkcija grupiranja omogućuje nam organiziranje datuma u godinama, mjesecima, četvrtinama itd. To će pomoći sažeti podatke i olakšati korisnicima interakciju s njom. Desni klik na jednu od zaglavlja stupaca datuma i odaberite Skupina koja prikazuje obrazac Grupiranje.

11 od 15

Odaberite Grupiranje po vrijednostima

Odabir stavki grupiranja za polje s datumom.

Ovisno o vrsti podataka koje grupirate, obrazac će izgledati malo drugačije. Excel 2010 omogućuje grupiranje datuma, brojeva i odabranih tekstualnih podataka. Grupiranje OrderDatea u ovom vodiču tako da obrazac prikazuje opcije vezane uz grupiranje datuma.

Kliknite Mjeseci i godine i kliknite U redu.

12 od 15

Pivotna tablica grupirana po godinama i mjesecima

Polja s podacima grupirani su prema godinama i mjesecima.

Kao što možete vidjeti na gornjoj slici, podaci se grupiraju po prvi put, a zatim prema mjesecu. Svaka od njih ima plus i minus znak koji vam omogućuje proširenje i kolaps, ovisno o tome kako želite vidjeti podatke.

U ovom trenutku, pivot tablica je prilično korisna. Svako od polja može se filtrirati, ali problem je da ne postoji vizualni trag o trenutnom stanju filtara. Isto tako, potrebno je nekoliko klikova za promjenu prikaza.

13 od 15

Umetni Slicer (novo u programu Excel 2010)

Dodajte Slicere u tablicu za poništavanje.

Sliceri su novi u programu Excel 2010. Sliceri su u osnovi isti kao vizualni postavci filtara postojećih polja i stvaranje filtara za izvješća u slučaju da stavka na koju želite filtrirati nije u trenutnom prikazu tablice. Ova lijepa stvar o Slicerima postaje vrlo jednostavna za korisnika da promijeni prikaz podataka u PivotTableu, kao i pružaju vizualne pokazatelje o trenutnom stanju filtara.

Da biste umetnuli Slicere, kliknite karticu Mogućnosti i kliknite Umetni Slicer iz odjeljka Poredaj i filtriranje. Odaberite Umetni Slicer koji otvara obrazac Umetni Sliceri. Provjerite onoliko polja koliko želite imati na raspolaganju. U našem primjeru dodala sam godine, CountryRegionName i ProductCategory. možda ćete morati položiti Slicere gdje ih želite. Prema zadanim postavkama odabiru se sve vrijednosti, što znači da nisu primijenjeni filtri.

14 od 15

Pivotna tablica s korisničkim slicicama

Sliceri olakšavaju korisnicima filtriranje Pivot Tablica.

Kao što možete vidjeti, Sliceri prikazuju sve podatke kao što je odabrano. Vrlo je jasno korisniku točno ono što se nalazi u trenutnom prikazu tablice za podešavanje.

15 od 15

Izaberite vrijednosti od slicera koji ažuriraju tablicu zaokretanja

Odaberite kombinacije Slicera za promjenu prikaza podataka.

Kliknite različite kombinacije vrijednosti i pogledajte kako se mijenja prikaz tablice zaobilazeće tablice. Možete upotrijebiti tipični Microsoftov klik na Slicere što znači da ako možete koristiti Control + Kliknite za odabir više vrijednosti ili Shift + Click za odabir raspona vrijednosti. Svaki Slicer prikazuje odabrane vrijednosti što ga čini očiglednim što je stanje Pivot tablice u smislu filtara. Ako želite, možete promijeniti stilove Slicera klikom na padajući izbornik Brzi stilovi u odjeljku Slicer na kartici Mogućnosti.

Uvođenje Slicers stvarno je poboljšalo upotrebljivost PivotTables i premjestio Excel 2010 bliže kao profesionalni alat za poslovnu inteligenciju. Pivot tablice su se u Excelu uvelike popravile i kada su u kombinaciji s novom PowerPivot-om stvorili vrlo visoku izvedbu analitičkog okruženja.