Cum se configurează tabelele pivot pentru Excel 2010

01 din 15

Rezultat final

A existat un decalaj între Microsoft Excel și platformele de nivel superior de business intelligence (BI) de mai mulți ani. Pachetele Pivot Table Microsoft Excel 2010 împreună cu câteva alte caracteristici BI au făcut-o un adevărat concurent pentru BI-ul întreprinderii. Excel a fost în mod tradițional utilizat pentru analiza independentă și instrumentul standard pe care îl exportă toată lumea pentru rapoartele finale. Serviciile de business intelligence au fost în mod tradițional rezervate pentru companii precum SAS, Business Objects și SAP.

Microsoft Excel 2010 (cu pivotul Excel 2010) împreună cu SQL Server 2008 R2, SharePoint 2010 și add-on-ul gratuit "Microsoft PowerPoint" din Microsoft Excel 2010 au dus la o soluție de raportare de afaceri și de raportare.

Acest tutorial acoperă un scenariu direct cu un cont PivotTable din Excel 2010 conectat la o bază de date SQL Server 2008 R2 utilizând o interogare SQL simplă. De asemenea, utilizez Slicers pentru filtrarea vizuală, care este nou în Excel 2010. Voi aborda tehnici mai complexe de BI utilizând Expresii de analiză a datelor (DAX) în PowerPivot pentru Excel 2010 în viitorul apropiat. Această versiune ulterioară a programului Microsoft Excel 2010 poate oferi o valoare reală pentru comunitatea dvs. de utilizatori.

02 din 15

Introduceți tabelul Pivot

Puteți introduce o tabelă Pivot într-un registru de lucru nou sau existent. S-ar putea să vă gândiți să poziționați cursorul pe câteva rânduri din partea de sus. Acest lucru vă va oferi spațiu pentru un antet sau informații despre companie în cazul în care împărțiți foaia de lucru sau tipăriți-o.

• Deschideți un registru de lucru nou sau existent Excel 2010 și faceți clic pe celula în care doriți să fie colțul din stânga sus al tabelului Pivot.
• Faceți clic pe fila Inserare și faceți clic pe pictograma PivotTable din secțiunea Tabele. Alegeți PivotTable. Aceasta va lansa formularul de dialog Creare consolă.

03 din 15

Conectați tabelul Pivot la SQL Server (sau la altă bază de date)

Excel 2010 poate prelua date de la toți furnizorii principali RDBMS (Relational Database Management System) . Driverele SQL Server ar trebui să fie disponibile pentru conectare în mod implicit. Dar toate programele importante de baze de date fac ODBC (Open Database Connectivity) drivere pentru a vă permite să faceți conexiunea. Verificați site-ul dvs. dacă doriți să descărcați driverele ODBC.

În cazul acestui tutorial, mă conectez la SQL Server 2008 R2 (versiunea gratuită SQL Express).

• A - Formularul Create PivotTable este primul formular în crearea conexiunii la SQL Server. Selectați "Utilizați o sursă de date externă" și faceți clic pe butonul Alegeți conexiunea. Lăsați locul în care va fi plasat Pivotul tabelar dacă nu doriți să creați o foaie de lucru nouă și plasați-o acolo.
• B - Formularul Conexiuni existente afișează toate conexiunile din agenda de lucru curentă, de pe calculatorul dvs. și de rețeaua în care sunteți în prezent conectat. Conexiunile existente sunt într-adevăr doar fișiere text cu informațiile de conexiune necesare pentru a accesa o anumită sursă de date. În cazul nostru, vom crea o nouă sursă de date. Faceți clic pe butonul Căutați mai mult.
• C - Faceți clic pe butonul Sursă nouă va lansa Expertul conexiune de date.
• D - Alegeți Microsoft SQL Server și faceți clic pe Următorul.
• E - Introduceți numele serverului și datele de conectare. Alegeți metoda de autentificare adecvată. Dacă nu sunteți sigur ce metodă să utilizați, contactați administratorul bazei de date.
  • Utilizați autentificarea Windows: Această metodă utilizează login-ul de rețea pentru a accesa bazele de date SQL Server.
  • Utilizați următorul nume de utilizator și parolă: Această metodă este utilizată atunci când serverul SQL a fost configurat cu utilizatori independenți pentru a accesa bazele de date.
• F - În acest pas, vom alege un tabel ca înlocuitor. Vom înlocui tabelul cu SQL personalizat, care va furniza exact datele pe care le dorim în registrul nostru de lucru Excel.
  • Selectați baza de date la care vă veți conecta. În acest exemplu, ne conectăm la baza de date AdventureWorks furnizată de Microsoft. Bifați Conectați la o anumită tabelă și alegeți primul tabel. Rețineți că nu vom prelua date din acest tabel.
  • Faceți clic pe Finalizați, care va închide vrăjitorul și vă va întoarce în registrul de lucru. Vom schimba tabela de substituent pentru interogarea SQL personalizată.

Veți fi returnați la formularul Creare pivot (A). Faceți clic pe OK.

04 din 15

Pivot Tabel temporar conectat la tabela SQL

În acest moment, ați conectat la tabela de destinație și aveți un PivotTable gol. Puteți vedea în stânga că PivotTable va fi și în dreapta este o listă cu câmpurile disponibile.

05 din 15

Deschideți Proprietăți conexiune

Înainte de a începe alegerea datelor pentru PivotTable, trebuie să modificăm conexiunea la interogarea SQL. Asigurați-vă că vă aflați în fila Opțiuni și faceți clic pe meniul Modificare sursă de date din secțiunea Date. Alegeți Proprietăți conexiune.

Aceasta afișează formularul Proprietăți conexiune. Faceți clic pe fila Definiție. Aceasta vă arată informațiile de conectare pentru conexiunea curentă la SQL Server. În timp ce se referă la un fișier de conexiune, datele sunt de fapt încorporate în foaia de calcul.

06 din 15

Actualizați proprietățile conexiunii cu ajutorul interogării

Modificați tipul de comandă de la tabel la SQL și suprascrieți textul de comandă existent cu interogarea SQL. Iată interogarea pe care am creat-o din baza de date 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
Din Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Producție.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

Faceți clic pe OK.

07 din 15

Primiți avertizarea conexiunii

Veți primi o casetă de dialog Avertisment Microsoft Excel. Acest lucru se datorează faptului că am schimbat informațiile de conectare. Când am creat inițial conexiunea, a salvat informațiile într-un fișier extern ODC (ODBC Data Connection). Datele din registrul de lucru au fost aceleași cu fișierul .ODC până când am schimbat de la un tip de comandă de tabelă la un tip de comandă SQL în Pasul # 6. Avertizarea vă spune că datele nu mai sunt sincronizate, iar trimiterea la fișierul extern din registrul de lucru va fi eliminată. Este OK. Faceți clic pe Da.

08 din 15

Pivot Tabel conectat la SQL Server cu interogare

Acest lucru ia înapoi la registrul de lucru Excel 2010 cu un PivotTable gol. Puteți vedea că câmpurile disponibile sunt acum diferite și corespund câmpurilor din interogarea SQL. Putem acum să adăugăm câmpuri la PivotTable.

09 din 15

Adăugați câmpuri în tabelul Pivot

În câmpul PivotTable Field, trageți ProductCategory în zona Labels Row, zona Date de ordine pentru etichete coloane și zona TotalDue la valori. Imaginea afișează rezultatele. După cum puteți vedea, câmpul de date are date individuale, astfel încât PivotTable a creat o coloană pentru fiecare dată unică. Din fericire, Excel 2010 are câteva funcții construite pentru a ne ajuta să organizăm domenii de date.

10 din 15

Adăugați gruparea pentru câmpurile de date

Funcția de grupare ne permite să organizăm datele în ani, lună, trimestre etc. Acest lucru va ajuta la rezumarea datelor și la ușurarea interacțiunii cu utilizatorul. Faceți clic dreapta pe una dintre anteturile coloanelor de date și selectați Grupul care a afișat formatul de grupare.

11 din 15

Alegeți Gruparea după valori

În funcție de tipul de date pe care îl grupați, formularul va arăta puțin diferit. Excel 2010 vă permite să grupați date, numere și date text selectate. În acest tutorial grupăm Ordinul Data, astfel încât formularul va afișa opțiuni referitoare la grupările de date.

Faceți clic pe Luni și Anii și faceți clic pe OK.

12 din 15

Pivot Tabelat după ani și luni

După cum puteți vedea în imaginea de mai sus, datele sunt grupate după primul an și apoi pe lună. Fiecare are un semn plus și minus care vă permite să vă extindeți și să vă colapsați în funcție de modul în care doriți să vedeți datele.

În acest moment, PivotTable este destul de util. Fiecare dintre câmpuri poate fi filtrat, dar problema este că nu există un indiciu vizual cu privire la starea actuală a filtrelor. De asemenea, este nevoie de câteva clicuri pentru a schimba vederea.

13 din 15

Insert Slicer (nou în Excel 2010)

Slicerele sunt noi în Excel 2010. Slicerele sunt, în esență, echivalente cu filtrele de setare vizuală ale câmpurilor existente și crearea filtrelor de raport în cazul în care elementul pe care doriți să filtrați nu este în vizualizarea PivotTable curentă. Acest lucru frumos despre Slicers este faptul că devine foarte ușor pentru utilizator să schimbe vizualizarea datelor din PivotTable, precum și să furnizeze indicatori vizați cu privire la starea actuală a filtrelor.

Pentru a insera Slicers, faceți clic pe fila Options și faceți clic pe Insert Slicer din secțiunea Sort & Filter. Alegeți Insert Slicer care deschide formularul Insert Slicers. Verificați cât mai multe câmpuri pe care doriți să le aveți disponibile. În exemplul nostru, am adăugat Ani, CountryRegionName și ProductCategory. s-ar putea să trebuiască să poziționați Slicer-ele acolo unde doriți. În mod prestabilit, toate valorile sunt selectate, ceea ce înseamnă că nu s-au aplicat filtre.

14 din 15

Pivot Tabel cu Slicers usor de utilizat

Dupa cum puteti vedea, Slicers arata toate datele selectate. Este foarte clar pentru utilizator exact ce date se află în vizualizarea curentă a PivotTable.

15 din 15

Alegeți valorile de la distribuitorii care tabelă pivot actualizări

Faceți clic pe diverse combinații de valori și vedeți cum se modifică vizualizarea PivotTable. Puteți utiliza clicurile tipice ale Microsoft în Slicers, ceea ce înseamnă că dacă puteți utiliza Control + Faceți clic pentru a selecta mai multe valori sau Shift + Faceți clic pentru a selecta un interval de valori. Fiecare Slicer afișează valorile selectate, ceea ce face cu adevărat evident ce este starea PivotTable în ceea ce privește filtrele. Puteți schimba stilurile Slicerilor dacă doriți, dând clic pe meniul derulant Styles Quick în secțiunea Slicer din fila Opțiuni.

Introducerea soluției Slicers a îmbunătățit cu adevărat gradul de utilizare a PivotTables și a mutat programul Excel 2010 mult mai aproape de a fi un instrument profesionist de business intelligence. PivotTables s-au îmbunătățit foarte mult în Excel 2010 și când sunt combinate cu noul PowerPivot creează un mediu de analiză foarte performant.