Kako konfigurisati Pivot Tables Excel 2010

by Tim Perdue

01 od 15

Konačni rezultat

Ovo je konačni rezultat ovog uputstva za korak po korak - kliknite na sliku da biste vidjeli punu veličinu.

Postojao je prazan prostor između Microsoft Excel-a i top-level poslovne inteligencije (BI) platformi već dugi niz godina. Poboljšanja Pivot Table Microsoft Excel 2010 zajedno sa još nekoliko drugih funkcija učinili su da je pravi konkurent za preduzeće BI. Excel se tradicionalno koristi za samostalnu analizu i standardni alat za koji svi izvozi svoje završne izveštaje. Profesionalna poslovna inteligencija tradicionalno je rezervisana za SAS, Business Objects i SAP.

Microsoft Excel 2010 (uz Excel 2010 Pivot Table), zajedno sa SQL Server 2008 R2, SharePoint 2010 i besplatnim dodatkom Microsoft Excel 2010 "PowerPivot" rezultirao je visoko rešenjem za poslovnu inteligenciju i izveštavanje.

Ovaj tutorijal pokriva pravolinijski scenario sa Excel 2010 PivotTable povezan sa SQL Server 2008 R2 bazom podataka pomoću jednostavnog SQL upita. Takođe koristim Slicers za vizuelno filtriranje koje je novo u Excel-u 2010. U bliskoj budućnosti ću pokriti složenije BI tehnike koristeći izraze analize podataka (DAX) u programu PowerPivot za Excel 2010. Ovo najnovije izdanje Microsoft Excel 2010 može pružiti stvarnu vrijednost za vašu korisničku zajednicu.

02 od 15

Ubacite pivot tabele

Postavite kursor tačno gde želite svoju stolicu i kliknite na Insert | Pivot Table.

Možete postaviti Pivot Table u novu ili postojeću Excel radnu svesku. Možda biste želeli razmisliti o poziciji svog kursora niz nekoliko redova sa vrha. Ovo će vam dati prostor za zaglavlje ili informacije o preduzeću u slučaju da podijelite radni list ili ga odštampate.

Otvorite novu ili postojeću radnu svesku Excel 2010 i kliknite na ćeliju u kojoj želite da bude gornji levi ugao Pivot Table.
Kliknite na karticu Insert i kliknite na padajući tabelu u tabeli. Izaberite PivotTable. Ovo će pokrenuti formu dijaloga Create PivotTable.

03 od 15

Povezivanje pivot table na SQL Server (ili drugu bazu podataka)

Napravite svoj SQL upit i potom povežite se na SQL Server da biste ugradili string veze veze u Excelovu tabelu.

Excel 2010 može preuzeti podatke iz svih glavnih RDBMS (Relational Database Management System) provajdera. SQL Server drajveri bi trebali biti dostupni za vezu po podrazumevanoj vrednosti. Ali svi glavni softveri za bazu podataka omogućavaju ODBC (Open Database Connectivity) upravljačke programe da omogućavaju povezivanje. Proverite njihovu web stranicu ako želite da preuzmete ODBC drajvere.

U slučaju ovog tutorijala, povezujem se s SQL Server 2008 R2 (SQL Express besplatna verzija).

A - Form Create PivotTable je prvi oblik u kreiranju veze sa SQL Serverom. Izaberite "Koristi spoljni izvor podataka" i kliknite na dugme Izaberi vezu. Ostavite lokaciju na kojoj će biti postavljen Pivot Table, osim ako ne želite da kreirate novi radni list i postavite ga tamo.
B - Forma postojećih veza navodi bilo koje veze u trenutnoj radnoj svesci, na vašem računaru i mreži na koju ste trenutno povezani. Postojeće veze su zapravo samo tekstualne datoteke sa informacijama o konekciji potrebnim za pristup određenom izvoru podataka. U našem slučaju, kreiramo novi izvor podataka. Kliknite na dugme Browse for More.
C - Kliknite na dugme New Source će pokrenuti Čarobnjak za povezivanje podataka.
D - Izaberite Microsoft SQL Server i kliknite na dugme Dalje.
E - Unesite ime servera i prijavite se na akreditive. Izaberite odgovarajući metod autentifikacije. Ako niste sigurni koji metod koristiti, obratite se administratoru baze podataka.
- Koristite Windows Authentication: Ovaj metod koristi mrežni login za pristup SQL Server baze podataka.
- Koristite sledeće korisničko ime i lozinku: Ovaj metod se koristi kada je SQL Server konfigurisan sa samostalnim korisnicima za pristup bazi podataka.
F - U ovom koraku ćemo izabrati tabelu kao mjesto. Mi ćemo zamijeniti tablicu pomoću prilagođenog SQL-a koji će tačno pružiti podatke koje želimo u Excelovoj radnoj svesci.
- Izaberite bazu podataka na koju ćete se povezati. U ovom primeru povezujemo se sa bazom podataka uzorka AdventureWorks koju nudi Microsoft. Provjerite Connect na određenu tablicu i izaberite prvu tablicu. Zapamtite, nećemo izvlačiti podatke iz ove tabele.
- Kliknite na dugme Završi koji će zatvoriti čarobnjaka i vratiti vas u radnu svesku. Mi ćemo zameniti tablicu mesta za naše prilagođene SQL upite.

Biće vam vraćeni u obrazac Create PivotTable (A). Kliknite OK.

04 od 15

Pivot Table je privremeno povezan sa SQL tablicom

PivotTable je povezan na SQL Server sa tablicom mesta.

U ovom trenutku, povezali ste se sa tablicom mesta i imali ste praznu tabelu. Možete vidjeti sa leve strane da li će biti tabelarna tabela i na desnoj strani nalazi se lista dostupnih polja.

05 od 15

Otvorene veze

Form Open Properties Properties.

Pre nego što počnemo da birate podatke za PivotTable, moramo promijeniti vezu sa SQL upitom. Proverite da li ste na kartici "Opcije" i kliknite na Promeni izvor podataka iz opcije Data. Izaberite Properties Properties.

Ovo donosi obrazac Connection Properties. Kliknite na karticu Definicija. Ovo vam prikazuje informacije o vezi za trenutnu vezu sa SQL Serverom. Iako se odnosi na vezu, podaci su zapravo ugrađeni u tabelu.

06 od 15

Ažuriraj svojstva veze s upitom

Promijenite tabelu u SQL upit.

Promenite tip naredbe iz tabele u SQL i prepisite postojeći komandni tekst pomoću SQL upita. Evo upita koji sam kreirao iz baze podataka uzorka AdventureWorks:

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,
Production.Product.Name,
Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,
Sales.Customer.CustomerType,
Production.Product.ListPrice,
Production.Product.ProductLine,
Production.ProductSubcategory.Name AS ProductCategory
OD prodaje. SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID =
Production.Product.ProductID 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
Production.ProductSubcategory ON Production.Product.ProductSubcategoryID =
Production.ProductSubcategory.ProductSubcategoryID

Kliknite OK.

07 od 15

Primite upozorenje o priključenju

Kliknite na Yes to Connection Warning.

Dobićete dijalog za Microsoft Excel Warning. To je zato što smo promijenili informacije o povezivanju. Kada smo originalno kreirali vezu, sačuvali smo informacije u vanjskoj .ODC datoteki (ODBC Data Connection). Podaci u radnoj svesci bili su isti kao i .ODC fajl sve dok se ne promenimo iz tipa naredbe tablice u tip naredbe SQL u koraku # 6. Upozorenje vam govori da se podaci više ne sinhronizuju i skeniranje spoljne datoteke u radnoj svesci će biti uklonjeno. Ovo je u redu. Kliknite na dugme Da.

08 od 15

Pivot Table Connected to SQL Server sa upitom

PivotTable je spreman za dodavanje podataka.

Ovo vraća u radnu svesku Excel 2010 sa praznim tabelama. Možete videti da su dostupna polja sada različita i odgovaraju poljima u SQL upitu. Sada možemo započeti dodavanje polja u PivotTable.

09 od 15

Dodajte polja u Pivot Table

Dodajte polja u PivotTable.

Na listi polja svirne tabele prevucite ProductCategory na područje oznake polja, polje OrderDate to Column Labels i TotalDue to Values. Slika prikazuje rezultate. Kao što vidite, polje datuma ima pojedinačne datume, tako da je PivotTable kreirao kolonu za svaki jedinstveni datum. Na sreću, Excel 2010 ima neke ugrađene funkcije koje nam pomažu da organiziramo polja datuma.

10 od 15

Dodajte grupisanje za datumska polja

Dodajte grupisanje za datumsko polje.

Funkcija Grupisanja nam omogućava da organizujemo datume u godinama, mjesecima, četvrtinama itd. To će pomoći da se sumiraju podaci i olakšaju korisniku da interakciju s njim. Kliknite desnim tasterom miša na jedan od zaglavlja stupca datuma i izaberite grupu koja prikazuje obrazac za grupisanje.

11 od 15

Izaberite grupisanje po vrednostima

Izaberi stavke za grupisanje za datumsko polje.

U zavisnosti od vrste podataka koje grupirate, obrazac će izgledati malo drugačije. Excel 2010 omogućava grupiranje datuma, brojeva i odabranih tekstualnih podataka. U ovom vodiču grupisamo OrderDate, tako da će obrazac prikazati opcije koje se odnose na date grupe.

Kliknite na Meseci i Godine i kliknite na dugme U redu.

12 od 15

Pivot Tabela grupisana po godinama i mesecima

Polja datuma su grupisana po godinama i mesecima.

Kao što možete videti na slici iznad, podaci su grupisani po godinama, a zatim po mesecima. Svaki od njih ima znak plus i minus koji vam omogućava da proširite i srušite u zavisnosti od toga kako želite da vidite podatke.

U ovom trenutku, PivotTable je prilično koristan. Svako od polja može biti filtrirano, ali problem je što nema vizuelnog pojma o trenutnom stanju filtera. Takođe, potrebno je nekoliko klikova za promjenu prikaza.

13 od 15

Umetni slicer (novi u programu Excel 2010)

Dodajte slajdera na vrtilicu.

Rezci su novi u Excel-u 2010. Slicerovi su u suštini ekvivalentni vizuelno podešavanju filtera postojećih polja i kreiranju filtera za izveštaje u slučaju da stavka koju želite da filtrirate nije u trenutnom prikazu vrtoglavice. Ova lepa stvar o Slicers-u je postati vrlo lako da korisnik promeni prikaz podataka u PivotTable-u, kao i da obezbedi vizuelne pokazatelje u odnosu na trenutno stanje filtera.

Da biste ubacili Slicers, kliknite na karticu Opcije i kliknite na Insert Slicer iz odeljka Sort & Filter. Odaberite Insert Slicer koji otvara formu za ubacivanje slajdera. Provjerite što više polja koliko želite. U našem primeru sam dodao Godine, CountryRegionName i ProductCategory. možda ćete morati da pozicionirate Slicerove gde ih želite. Podrazumevano su izabrane sve vrijednosti što znači da nijedan filter nije primijenjen.

14 od 15

Pivot sto sa slajderima pogodnim za korisnike

Slicers olakšava korisnicima da filtriraju PivotTables.

Kao što vidite, Slicers pokazuju sve podatke kao odabrane. Korisniku je vrlo jasno da li su tačni podaci u trenutnom prikazu PivotTable-a.

15 od 15

Odaberite vrijednosti od slicersa koji ažuriraju pivot tabele

Izaberite kombinacije Slicers da promenite prikaz podataka.

Kliknite na razne kombinacije vrijednosti i pogledajte kako se pogled PivotTable-a mijenja. Možete koristiti tipični Microsoft klik na Slicers-u što znači da ako možete koristiti Control + Click za odabir više vrijednosti ili Shift + Click da biste odabrali niz vrijednosti. Svaki Slicer prikazuje izabrane vrednosti, što čini da je stvarno očigledno stanje PivotTable-a u pogledu filtera. Ako želite, možete promeniti stilove Slicersa tako što ćete kliknuti na stavku Brzi stilovi u delu Slicer na kartici Opcije.

Uvođenje Slicers-a je zaista poboljšalo upotrebljivost PivotTables-a i premjestilo je Excel 2010 mnogo bliže profesionalnom alatu za poslovnu inteligenciju. PivotTables se poboljšali dosta u Excelu 2010. i kada se u kombinaciji sa novim PowerPivot stvara analitičko okruženje sa vrlo visokim performansama.