Kako konfigurirati Pivot Tables za Excel 2010

by Tim Perdue

01 od 15

Končni rezultat

To je končni rezultat tega vodiča po korakih - kliknite na sliko, da si ogledate polno različico.

Obstaja vrzel med Microsoft Excel in vrhunskimi platformami poslovne inteligence (BI) že vrsto let. Izboljšave Pivot Table Microsoft Excel 2010 skupaj z nekaterimi drugimi funkcijami BI so postale pravi konkurent podjetja BI. Excel se tradicionalno uporablja za samostojno analizo in standardno orodje, v katerem vsakdo izvozi končna poročila v. Profesionalna poslovna inteligenca je bila tradicionalno rezervirana za SAS, Business Objects in SAP.

Microsoft Excel 2010 (s pivot tabelo Excel 2010) skupaj z SQL Server 2008 R2, SharePoint 2010 in brezplačnim dodatkom Microsoft PowerPivot za Microsoft Excel 2010 je prinesla visoko rešitev za poslovno obveščanje in poročanje.

Ta vadnica zajema neposreden scenarij z vrtljivo tabelo Excel 2010, ki je povezana z bazo podatkov SQL Server 2008 R2 z uporabo preproste SQL poizvedbe. Uporabljam Slicers za vizualno filtriranje, ki je nov v Excelu 2010. V bližnji prihodnosti bom zajemal bolj zapletene tehnike BI z uporabo izrazov za analizo podatkov (DAX) v programu PowerPivot za Excel 2010. Ta najnovejša izdaja Microsoft Excel 2010 lahko zagotovi resnično vrednost za vašo uporabniško skupnost.

02 od 15

Vstavi pivot tabelo

Postavite kazalec točno tam, kjer želite svojo pivot tabelo in kliknite na Vstavi | Vrteča miza.

Pivot tabelo lahko vstavite v novo ali obstoječo delovno knjigo Excel. Morda boste želeli razmisliti o položaju kazalca navzdol po nekaj vrsticah z vrha. To vam bo dalo prostor za podatke o glavi ali podjetju, če delite delovni list ali ga natisnete.

Odprite novo ali obstoječo Delovni zvezek Excel 2010 in kliknite na celico, kjer želite, da je zgornji levi kot Pivot Table.
Kliknite kartico Vstavi in v spustnem seznamu kliknite Spustno tabelo. Izberite PivotTable. S tem boste začeli pogovorno okno Create PivotTable.

03 od 15

Povežite pivotno tabelo s strežnikom SQL Server (ali drugo bazo podatkov)

Ustvarite poizvedbo SQL in se nato povežite s strežnikom SQL Server za vdelavo podatkovnega niza povezave v preglednico Excel.

Excel 2010 lahko pridobi podatke iz vseh glavnih RDBMS (Relational Database Management System) ponudnikov. Gonilnik SQL Server mora biti privzeto na voljo za povezavo. Toda vsa pomembna programska oprema za bazo podatkov omogoča, da gonilniki ODBC (Open Database Connectivity) omogočajo povezavo. Če želite prenesti gonilnike ODBC, preverite njihovo spletno mesto.

V primeru tega vadnice se povezujem z SQL Server 2008 R2 (brezplačno različico SQL Express).

A - Obrazec Create PivotTable je prva oblika ustvarjanja povezave s strežnikom SQL Server. Izberite "Uporabi zunanji vir podatkov" in kliknite gumb Izberi povezavo. Pustite mesto, kjer bo postavljena Pivot Table, razen če želite ustvariti nov delovni list in ga tam postaviti.
B - Obstoječa povezava vsebuje vse povezave v trenutni delovni zvezki, v računalniku in omrežju, s katerim ste trenutno povezani. Obstoječe povezave so resnično samo besedilne datoteke z informacijami o povezavi, ki so potrebne za dostop do določenega vira podatkov. V našem primeru bomo ustvarili nov vir podatkov. Kliknite gumb Prebrskaj več.
C - Kliknite gumb Novo vir, da zaženete čarovnika za povezavo podatkov.
D - Izberite Microsoft SQL Server in kliknite Naprej.
E - Vnesite ime strežnika in se prijavite s poverilnicami. Izberite ustrezen način preverjanja pristnosti. Če niste prepričani, katero metodo uporabite, se obrnite na skrbnika zbirke podatkov.
- Uporaba preverjanja pristnosti z operacijskim sistemom Windows: ta način uporablja vašo omrežno prijavo za dostop do baz podatkov SQL Server.
- Uporabite naslednje uporabniško ime in geslo: ta način se uporablja, ko je SQL Server konfiguriran s samostojnimi uporabniki za dostop do podatkovnih baz.
F - V tem koraku bomo izbrali mizo kot mesto. Namesto tega bomo zamenjali tabelo s prilagojenim SQL-jem, ki bo v našem Excelovem delovnem zvezku zagotovila točno podatke, ki jih želimo.
- Izberite bazo podatkov, s katero se boste povezali. V tem primeru se povezujemo z vzorčno zbirko podatkov AdventureWorks, ki jo ponuja Microsoft. Preverite povezavo z določeno tabelo in izberite prvo tabelo. Ne pozabite, da ne bomo pridobivali podatkov iz te tabele.
- Kliknite Dokončaj, ki bo zaprl čarovnika in vas vrnil v delovni zvezek. Za našo poizvedbo po meri SQL bomo zamenjali tabelo namesto vas.

Vrnili se boste v obrazec Create PivotTable (A). Kliknite V redu.

04 od 15

Pivot tabela je začasno povezana s tabelo SQL

PivotTable je povezana z SQL Server s tabelo namiznih mest.

Na tej točki ste se povezali z mizo namesto vas in imate prazno vrtilno tabelo. Na levi strani si lahko ogledate, kje bo vrtilna tabela in na desni strani je seznam razpoložljivih polj.

05 od 15

Odpri lastnosti povezave

Oblika Open Connection Properties.

Preden začnemo izbrati podatke za PivotTable, moramo spremeniti povezavo s poizvedbo SQL. Prepričajte se, da ste na kartici Možnosti in kliknite Spremeni vir podatkov, ki se spusti iz razdelka Podatki. Izberite lastnosti povezave.

S tem se vzpostavi obrazec Connection Properties. Kliknite kartico Definicija. Na tem se prikažejo podatki o povezavi za trenutno povezavo s strežnikom SQL Server. Medtem ko se sklicuje na povezovalno datoteko, so podatki dejansko vdelani v preglednico.

06 od 15

Posodobi lastnosti povezave s poizvedbo

Spremeni tabelo v poizvedbo SQL.

Spremenite vrsto ukaza iz tabele v SQL in prepišite obstoječe besedilo ukaza z vašo poizvedbo SQL. Tukaj je poizvedba, ki sem jo ustvarila iz vzorčne baze podatkov 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 IN
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 V redu.

07 od 15

Prejmi povezavo Opozorilo

Kliknite Yes to the Warning Connection.

Prejeli boste pogovorno okno z opozorilom Microsoft Excel. To je zato, ker smo spremenili podatke o povezavi. Ko smo prvotno ustvarili povezavo, so podatke shranili v zunanjo datoteko .ODC (ODBC Data Connection). Podatki v delovnem zvezku so bili enaki kot datoteka .ODC, dokler se v koraku št. 6 ne spremenimo iz vrste ukazne tabele v vrsto ukaza SQL. Opozorilo vam pove, da podatki niso več sinhronizirani in da bo sklicevanje na zunanjo datoteko v delovnem zvezku odstranjeno. To je v redu. Kliknite Da.

08 od 15

Pivot tabela povezana z SQL Server z poizvedbo

PivotTable je pripravljen za dodajanje podatkov.

To vrne v delovni zvezek Excel 2010 s prazno vrtilno tabelo. Vidite, da so razpoložljiva polja zdaj različna in ustrezajo poljem v poizvedbi SQL. Zdaj lahko začnemo dodajati polja v vrtilno tabelo.

09 od 15

Dodaj polja v vrtilno tabelo

Dodaj polja v vrtilno tabelo.

Na seznamu polj PivotTable povlecite ProductCategory na območje vrstice vrstic, območje oznak OrderDate to Column Labels in TotalDue to Values. Slika prikazuje rezultate. Kot lahko vidite, polje z datumi ima posamezne datume, zato je PivotTable ustvaril stolpec za vsak edinstven datum. Na srečo ima Excel 2010 nekaj vgrajenih funkcij, ki nam pomagajo organizirati datumska polja.

10 od 15

Dodajanje skupine za polja z datumi

Dodaj skupino za datumsko polje.

Funkcija združevanja nam omogoča, da organiziramo datume v leta, mesecih, četrtletjih itd. To bo pomagalo povzeti podatke in uporabniku olajšati interakcijo z njo. Desni klik na enega od naslovov stolpcev datumov in izberite Skupina, ki vzpostavi obrazec za združevanje.

11 od 15

Izberite skupino po vrednostih

Izberi postavke za skupine za datumsko polje.

Glede na vrsto podatkov, ki jih združujete, bo obrazec nekoliko drugačen. Excel 2010 vam omogoča, da združite datume, številke in izbrane besedilne podatke. V tej vaja združujemo OrderDate, tako da bo obrazec prikazal možnosti, ki se nanašajo na datumske skupine.

Kliknite na Mesece in leta ter kliknite V redu.

12 od 15

Pivot Tabela razvrščena po letih in mesecih

Datumska polja so razvrščena po letih in mesecih.

Kot vidite na zgornji sliki, so podatki razvrščeni po letih, nato pa po mesecih. Vsak ima znak plus in minus, ki vam omogoča razširitev in propadanje, odvisno od tega, kako želite videti podatke.

V tem trenutku je PivotTable zelo uporaben. Vsako polje je mogoče filtrirati, vendar je težava, da trenutno stanje filtrov ni vidno. Za spremembo pogleda potrebujete tudi več klikov.

13 od 15

Vstavi slicer (novo v programu Excel 2010)

Dodajanje rezkarjev na vrtilno tabelo.

Rezalci so novi v Excelu 2010. Reševalci so v bistvu enakovredni filtrom za vizualno nastavljanje obstoječih polj in ustvarjanju filtrov za poročila v primeru, da element, ki ga želite filtrirati, ni v trenutnem pogledu v vrtilni tabeli. Ta lepa stvar o Slicers-u je, da je uporabniku zelo enostavno spremeniti pogled podatkov v vrtilno tabelo in prikazati vizualne kazalnike glede trenutnega stanja filtrov.

Če želite vstaviti Slicers, kliknite zavihek Možnosti in v razdelku Razvrsti & filtrirajte Vstavi slicer. Izberite Insert Slicer, ki odpre obrazec Vstavi slicers. Preverite toliko polj, kot želite, da jih imate na voljo. V našem primeru sem dodal Years, CountryRegionName in ProductCategory. boste morda morali postaviti Slicers, kjer jih želite. Privzeto so izbrane vse vrednosti, kar pomeni, da niso bili uporabljeni filtri.

14 od 15

Pivot tabela z uporabnikom prijazno rezanje

Slicers olajšajo uporabnikom, da filtrirajo vrtilne tabele.

Kot lahko vidite, Slicers prikazujejo vse podatke, kot so bili izbrani. Uporabniku je zelo jasno, kateri podatki so v trenutnem pogledu vrtilne tabele.

15 od 15

Izberite vrednosti iz rezkarjev, ki posodabljajo vrtilno tabelo

Izberite kombinacijo rezkarjev, da spremenite pogled podatkov.

Kliknite na različne kombinacije vrednosti in si oglejte, kako se pogled vrtilne tabele spremeni. Uporabite tipičen Microsoftov klik v Slicers, kar pomeni, da če lahko uporabite Control + Click, da izberete več vrednosti ali Shift + Click, da izberete vrsto vrednosti. Vsak Slicer prikaže izbrane vrednosti, zaradi česar je resnično očitno, kaj je stanje vrtilne tabele v smislu filtrov. Če želite, lahko spremenite sloge slicersov tako, da v razdelku Slicer na zavihku Možnosti kliknete Hitri slogi.

Uvedba Slicers-a je resnično izboljšala uporabnost PivotTables in se je Excel 2010 precej približala profesionalnemu orodju za poslovno inteligenco. PivotTables se je v Excel 2010 nekoliko izboljšalo in ko skupaj z novim PowerPivot ustvarja zelo visoko zmogljivo analitično okolje.