Kā konfigurēt Excel 2010 pivot tabulas

by Tim Perdue

01 no 15

Gala rezultāts

Šis ir šī soli pa soļa apmācības galīgais rezultāts. Noklikšķiniet uz attēla, lai skatītu pilnizmēra versiju.

Daudzus gadus starp Microsoft Excel un augstākā līmeņa biznesa izlūkošanas (BI) platformām pastāv plaisa. Microsoft Excel 2010 Pivot Table papildinājumi kopā ar dažām citām BI funkcijām ir padarījuši to par patiesu konkurentu uzņēmuma BI. Excel tradicionāli tiek izmantota neatkarīgai analīzei un standarta rīks, ko ikviens eksportē savus galīgos pārskatus. Tradicionāli profesionāla biznesa informācija ir rezervēta SAS, biznesa objektu un SAP patīkamām lietām.

Microsoft Excel 2010 (kopā ar Excel 2010 Pivot Table) kopā ar SQL Server 2008 R2, SharePoint 2010 un bezmaksas Microsoft Excel 2010 papildinājumu PowerPivot ir radījis augsta līmeņa biznesa izlūkošanas un pārskatu risinājumu.

Šī apmācība ietver tiešu scenāriju ar Excel 2010 PivotTable savienojumu ar SQL Server 2008 R2 datu bāzi, izmantojot vienkāršu SQL vaicājumu. Es arī izmantoju Slicers vizuālajai filtrēšanai, kas ir jauna programmā Excel 2010. Es tuvākajā laikā aptvertu sarežģītākas BI metodes, izmantojot datu analīzes izteiksmes (DAX) PowerPivot programmā Excel 2010. Šis jaunākais Microsoft Excel 2010 izlaidums var sniegt patiesu vērtību jūsu lietotāju kopienai.

02 no 15

Ievietojiet pivot tabulu

Novietojiet kursoru tieši tajā vietā, kur vēlaties mainīt galdu, un noklikšķiniet uz Ievietot | Pivot tabula.

Jūs varat ievietot pivot tabulu jaunā vai esošajā Excel darbgrāmatā. Jūs varētu vēlēties apsvērt iespēju novietot kursoru pa dažām rindām no augšas. Tas dos jums vietu galvenes vai uzņēmuma informācijai, ja jūs kopīgojat darblapu vai izdrukājat to.

Atveriet jaunu vai jau esošo Excel 2010 darbgrāmatu un noklikšķiniet uz šūnas, kurā vēlaties būt Pivot tabulas augšējā kreisajā stūrī.
Noklikšķiniet uz cilnes Insert un noklikšķiniet uz pivotTable nolaižamajā sadaļā Tabulas. Izvēlieties pivotTable. Tas aktivizēs dialoglodziņu Izveidot PivotTable.

03 no 15

Pivot tabulas pievienošana SQL Server (vai cita datu bāze)

Izveidojiet SQL vaicājumu un pēc tam izveidojiet savienojumu ar SQL Server, lai iegultu savienojuma datu virkni Excel izklājlapā.

Excel 2010 var izgūt datus no visiem galvenajiem RDBMS (Relāciju datu bāzes pārvaldības sistēmas) pakalpojumu sniedzējiem. Pēc noklusējuma SQL Server draiveriem jābūt pieejamiem savienojumam. Bet visas galvenās datubāzes programmatūras draiveri ODBC (Open Database Connectivity) ļauj jums izveidot savienojumu. Pārbaudiet viņu vietni, ja jums ir nepieciešams lejupielādēt ODBC draiverus.

Šīs apmācības gadījumā es savienoju ar SQL Server 2008 R2 (bezmaksas versija SQL Express).

A - veidlapa Create PivotTable ir pirmā veidlapa, veidojot savienojumu ar SQL Server. Izvēlieties "Izmantot ārēju datu avotu" un noklikšķiniet uz pogas Izvēlieties savienojumu. Atstājiet atrašanās vietu, kur tiks ievietota pivot tabula, ja vien nevēlaties izveidot jaunu darblapu un ievietot to tur.
B - esošo savienojumu veidlapā ir norādīti visi savienojumi pašreizējā darbgrāmatā, datorā un tīklā, kurā pašlaik esat izveidojis savienojumu. Esošie savienojumi patiešām ir tikai teksta faili ar savienojuma informāciju, kas vajadzīga, lai piekļūtu konkrētam datu avotam. Šajā gadījumā mēs izveidosim jaunu datu avotu. Noklikšķiniet uz pogas Pārlūkot tālāk.
C - Noklikšķiniet uz pogas Jauns avots, tiks atvērts vednis datu savienojuma izveidei.
D - izvēlieties Microsoft SQL Server un noklikšķiniet uz Tālāk.
E - ievadiet servera nosaukumu un pierakstieties akreditācijas datos. Izvēlieties atbilstošo autentifikācijas metodi. Ja neesat pārliecināts, kādu metodi izmantot, sazinieties ar datu bāzes administratoru.
- Izmantojiet Windows autentifikāciju: šī metode izmanto jūsu tīkla pieteikšanos, lai piekļūtu SQL Server datu bāzēm.
- Izmantojiet šādu lietotāja vārdu un paroli: šī metode tiek izmantota, kad SQL Server ir konfigurēts ar atsevišķiem lietotājiem, lai piekļūtu datu bāzēm.
F - šajā solī mēs izvēlamies tabulu kā vietturētāju. Mēs aizstāsim tabulu ar pielāgotu SQL, kas precīzi sniegs datus, kurus mēs gribam, savā Excel darbgrāmatā.
- Atlasiet datu bāzi, ar kuru jūs izveidojat savienojumu. Šajā piemērā mēs izveidojam savienojumu ar AdventureWorks paraugu datu bāzi, ko nodrošina Microsoft. Pārbaudiet savienojumu ar noteiktu tabulu un izvēlieties pirmo tabulu. Atcerieties, ka mēs nevarēsim izgūt datus no šīs tabulas.
- Noklikšķiniet uz Pabeigt, kas aizvērs vedni un atgriezīsies darbgrāmatā. Mēs pielāgosim vietturu tabulu mūsu pielāgotajam SQL vaicājumam.

Jūs tiksiet atgriezts veidlapas Create PivotTable veidlapā (A). Noklikšķiniet uz Labi.

04 no 15

Pivot tabula, kas uz laiku pieslēgta SQL tabulai

PivotTable ir pievienots SQL Server ar vietturu tabulu.

Šajā brīdī esat izveidojis savienojumu ar vietturu tabulu un jums ir tukšs PivotTable. Kreisajā pusē var redzēt PivotTable, bet labajā pusē ir pieejamo lauku saraksts.

05 no 15

Atvērt savienojuma rekvizītus

Atvērt savienojuma rekvizītu veidlapu.

Pirms sākam izvēlēties PivotTable datu datus, mums ir jāmaina savienojums ar SQL vaicājumu. Pārliecinieties, ka esat cilne Opcijas un noklikšķiniet uz Mainīt datu avotu nolaižamajā izvēlnē Dati. Izvēlieties savienojuma rekvizītus.

Tas parādīs savienojuma rekvizītu formu. Noklikšķiniet uz cilnes Definīcija. Tas parāda jums savienojuma informāciju par pašreizējo savienojumu ar SQL Server. Kaut arī tā atsaucas uz savienojuma failu, dati faktiski tiek ievietoti izklājlapā.

06 no 15

Savienojuma rekvizītu atjaunināšana ar vaicājumu

Mainīt tabulu SQL vaicājumam.

Mainiet komandu tipu no tabulas uz SQL un pārrakstīt esošo komandu tekstu ar savu SQL vaicājumu. Šeit ir vaicājums, ko esmu izveidojis no AdventureWorks paraugu datu bāzes:

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
NO Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID =
Ražošana. Produkts. Produkts INNER JOIN Sales.Customer ON
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID UN
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOIN
Sales.vIndividualCustomer ON Sales.Customer.CustomerID =
Sales.vIndividualCustomer.CustomerID INNER JOIN
Ražošana.ProduktsSubcategory ON Ražošana.Produkts.ProduktsSubcategoryID =
Ražošana.Produkta kategorija.ProduktsSubcategoryID

Noklikšķiniet uz Labi.

07 no 15

Saņemiet savienojuma brīdinājumu

Noklikšķiniet uz Jā, lai pievienotu brīdinājumu.

Jūs saņemsiet Microsoft Excel brīdinājuma dialoglodziņu. Tas ir tāpēc, ka mēs mainījām savienojuma informāciju. Kad mēs sākotnēji izveidojām savienojumu, tā saglabāja informāciju ārējā .ODC failā (ODBC datu savienojums). Dati darbgrāmatā bija tādi paši kā .ODC failam, kamēr mēs mainījāmies no tabulas komandu veida uz SQL komandu tipu 6. solī. Brīdinājums parāda, ka dati vairs nav sinhronizēti, un atsauci uz ārējo failu darbgrāmatā tiks noņemti. Tas ir labi. Noklikšķiniet uz Jā.

08 no 15

Pivot tabula savienota ar SQL Server ar vaicājumu

PivotTable ir gatavs, lai jūs varētu pievienot datus.

Tas atgriežas Excel 2010 darbgrāmatā ar tukšu PivotTable. Varat redzēt, ka pieejamie lauki tagad ir atšķirīgi un atbilst laukiem SQL vaicājumā. Tagad mēs varam sākt pievienot laukus PivotTable.

09 no 15

Pievienot laukus pivot tabulā

Pievienot laukus PivotTable.

PivotTable lauku sarakstā velciet ProductCategory uz rindu iezīmju apgabalu, OrderDate uz kolonnu marķējuma apgabalu un TotalDue to Values apgabalā. Attēlā redzami rezultāti. Kā redzat, datuma laukam ir atsevišķi datumi, tāpēc PivotTable ir izveidojis kolonnu katram unikālajam datumam. Par laimi, programmā Excel 2010 ir iebūvētas funkcijas, kas palīdz organizēt datumu laukus.

10 no 15

Pievienojiet grupu datumu laukiem

Pievienojiet grupu datuma laukam.

Grupēšanas funkcija ļauj mums organizēt datumus uz gadiem, mēnešiem, ceturkšņiem u.tml. Tas palīdzēs apkopot datus un atvieglos lietotāja saskarsmi ar to. Ar peles labo pogu noklikšķiniet uz vienas no datuma slejas galvenēm un izvēlieties Grupu, kurā tiek atvērta Grupēšanas veidlapa.

11 no 15

Izvēlieties grupēšanu pēc vērtības

Izvēlieties datumu lauka grupu vienumus.

Atkarībā no datu veida, ko esat grupējuši, forma izskatīsies nedaudz atšķirīgi. Excel 2010 ļauj grupēt datumus, skaitļus un atlasītos teksta datus. Šajā apmācībā mēs grupējam OrderDate, tādēļ veidlapā tiks parādītas opcijas, kas attiecas uz datumu grupēšanu.

Noklikšķiniet uz Mēneši un gadi un noklikšķiniet uz Labi.

12 no 15

Pivot tabula grupēta pēc gadiem un mēnešiem

Datuma lauki ir sagrupēti pēc gadiem un mēnešiem.

Kā redzat attēlā iepriekš, dati tiek grupēti pēc gada, bet pēc mēneša. Katrai no tām ir plus un mīnus zīme, kas ļauj paplašināt un sabrukt, atkarībā no tā, kā jūs vēlaties redzēt datus.

Šajā brīdī PivotTable ir diezgan noderīga. Katru no laukiem var filtrēt, bet problēma nav vizuāla izpratne par pašreizējo filtru stāvokli. Turklāt, lai mainītu skatu, nepieciešams vairāki klikšķi.

13 no 15

Ievietot slicers (jauns programmā Excel 2010)

Pievienot šķēlītes uz PivotTable.

Slicers ir jauni programmā Excel 2010. Slīpētāji būtībā ir ekvivalents esošo lauku vizuāli iestatītajiem filtriem un ziņojuma filtru izveide gadījumā, ja objekts, kuru vēlaties filtrēt, nav redzams pašreizējā pivotTable. Šī jauka lieta par Slicers ir tas, ka lietotājam ir ļoti viegli mainīt PivotTable datu pārskatu, kā arī sniegt vizuālos rādītājus par pašreizējo filtru stāvokli.

Lai ievietotu Slicers, noklikšķiniet uz cilnes Opcijas un noklikšķiniet uz Ievietot slicer no sadaļas Kārtot un filtrēt. Izvēlieties Ievietot slicers, kas atver veidlapu Ievietot slicers. Pārbaudiet tik daudz lauku, cik vēlaties. Mūsu piemērā es pievienoju Gadi, CountryRegionName un ProductCategory. jums, iespējams, būs jānovieto Slicers, kur vēlaties tos. Pēc noklusējuma tiek atlasītas visas vērtības, kas nozīmē, ka neviens filtrs nav piemērots.

14 no 15

Pivot tabula ar lietotājam draudzīgu sasmalcinātāju

Slicers ļauj lietotājiem vieglāk filtrēt PivotTables.

Kā redzat, Slicers parādīs visus atlasītos datus. Lietotājam ir ļoti skaidrs, tieši kādi dati ir pašreizējā PivotTable skatījumā.

15 no 15

Izvēlieties vērtību no sliceriem, kuru atjauninājumi ir pamatvirsma

Izvēlieties slicers kombinācijas, lai mainītu datu skatu.

Noklikšķiniet uz dažādām vērtību kombinācijām un skatiet, kā mainās PivotTable skats. Jūs varat izmantot tipisku Microsoft klikšķi Slicers nozīmē, ka, ja jūs varat izmantot Control + Click, lai atlasītu vairākas vērtības vai Shift + Click, lai izvēlētos vērtību diapazonu. Katrs slicers parāda atlasītās vērtības, kas padara patiešām skaidru, kāds ir PivotTable stāvoklis attiecībā uz filtriem. Jūs varat mainīt Slicers stilus, ja vēlaties, noklikšķinot uz nolaižamā izvēlne Ātrie stili cilnē Opcijas sadaļas Slicer.

Slicers ieviešana ir patiešām uzlabojusi PivotTables lietojamību, un ir tuvinājusi Excel 2010 programmu kā profesionālu biznesa izlūkošanas rīku. PivotTables ir diezgan uzlabojies programmā Excel 2010, un apvienojumā ar jauno PowerPivot izveidota ļoti augsta veiktspējas analītiskā vide.