Configurați tabelele Pivot Excel 2010

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. Business intelligence profesional a fost în mod tradițional rezervată pentru companii precum SAS, Business Objects și SAP.

01 din 15

Rezultat final

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

Acest tutorial acoperă un scenariu direct cu un PivotTable Excel 2010 conectat la o bază de date SQL Server 2008 R2 utilizând o interogare SQL simplă.

Continuați să citiți mai jos

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ă.

Continuați să citiți mai jos

03 din 15

Conectați tabelul Pivot la serverul SQL

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. Verificați site-ul dvs. dacă doriți să descărcați driverele ODBC.

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

1. Deschideți formularul Creare pivot. 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 Pivot Table.
2. Deschideți formularul Conexiuni existente. Faceți clic pe butonul Căutați mai mult.
3. Faceți clic pe butonul Sursă nouă va lansa Expertul de conectare date.
4. Alegeți Microsoft SQL Server și faceți clic pe Următorul.
5. Introduceți numele serverului și datele de conectare. Alegeți metoda de autentificare adecvată:
   1. Utilizați autentificarea Windows: Această metodă utilizează datele dvs. de conectare pentru a accesa bazele de date SQL Server.
   2. 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.
6. Înlocuiți tabelul cu SQL personalizat, care va furniza exact datele pe care le dorim în registrul nostru de lucru Excel:
   1. 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.
   2. 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 unde va fi PivotTable și în partea dreaptă există o listă cu câmpurile disponibile.

Continuați să citiți mai jos

05 din 15

Deschideți Proprietăți conexiune

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. Aici este 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 ProductCategoryDin Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ONSales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderIDINNER JOIN Producție.Product ON Sales.SalesOrderDetail.ProductID =Production.Product.ProductID INNER JOIN Sales.Customer ONSales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID ANDSales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOINSales.vIndividualCustomer ON Sales.Customer.CustomerID =Sales.vIndividualCustomer.CustomerID INNER JOINProduction.ProductSubcategory ON Production.Product.ProductSubcategoryID =Production.ProductSubcategory.ProductSubcategoryID

Faceți clic pe OK.

Continuați să citiți mai jos

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.

Continuați să citiți mai jos

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. După cum puteți vedea, câmpul de date are date individuale, astfel încât PivotTable a creat o coloană pentru fiecare dată unică. Excel 2010 are câteva funcții încorporate care ne ajută 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 cu butonul din dreapta pe una dintre anteturile coloanelor date și selectați Grupul care a afișat formatul de grupare.

Continuați să citiți mai jos

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

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. Este nevoie de câteva clicuri pentru a modifica vizualizarea.

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.

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.