Per molti anni c'è stato un divario tra Microsoft Excel e le piattaforme di Business Intelligence di alto livello (BI). I miglioramenti di tabella pivot di Microsoft Excel 2010 insieme a un paio di altre funzionalità di BI hanno reso un vero concorrente per la BI aziendale. Excel è stato tradizionalmente utilizzato per l'analisi standalone e lo strumento standard su cui tutti esportano i loro rapporti finali. La business intelligence professionale è stata tradizionalmente riservata a prodotti come SAS, Business Objects e SAP.
Risultato finale
Microsoft Excel 2010 (con la tabella pivot di Excel 2010) insieme a SQL Server 2008 R2, SharePoint 2010 e il componente aggiuntivo gratuito Microsoft Excel 2010 "PowerPivot" ha prodotto una soluzione di reporting e business intelligence di fascia alta.
Questo tutorial copre uno scenario semplice con una tabella pivot di Excel 2010 collegata a un database di SQL Server 2008 R2 utilizzando una semplice query SQL.
Continua a leggere sotto
02 di 15Inserisci tabella pivot
È possibile inserire una tabella pivot in una cartella di lavoro Excel nuova o esistente. Potresti considerare di posizionare il cursore verso il basso di poche righe dall'alto. Ciò ti darà spazio per un'intestazione o informazioni aziendali nel caso in cui condividi il foglio di lavoro o stampalo.
- Aprire una cartella di lavoro Excel 2010 nuova o esistente e fare clic sulla cella in cui si desidera posizionare l'angolo in alto a sinistra della tabella pivot.
- Fare clic sulla scheda Inserisci e fare clic sul menu a discesa Tabella pivot nella sezione Tabelle. Scegli la tabella pivot. Verrà avviato il modulo di dialogo Crea tabella pivot.
Continua a leggere sotto
03 di 15Connetti tabella pivot a SQL Server
Excel 2010 può recuperare i dati da tutti i principali provider RDBMS (Relational Database Management System). I driver di SQL Server dovrebbero essere disponibili per la connessione per impostazione predefinita. Controllare il loro sito Web se è necessario scaricare i driver ODBC.
Nel caso di questo tutorial, ci stiamo connettendo a SQL Server 2008 R2 (versione gratuita di SQL Express).
- Aprire la maschera Crea tabella pivot. Seleziona "Utilizza un'origine dati esterna" e fai clic sul pulsante Scegli connessione. Lascia la posizione in cui verrà posizionata la tabella pivot.
- Apri il modulo Connessioni esistenti. Fare clic sul pulsante Cerca altro.
- Fare clic sul pulsante Nuova origine per avviare la Connessione guidata dati.
- Scegli Microsoft SQL Server e fai clic su Avanti.
- Immettere il nome del server e le credenziali di accesso. Scegli il metodo di autenticazione appropriato:
- Usa l'autenticazione di Windows: Questo metodo utilizza l'accesso alla rete per accedere ai database di SQL Server.
- Utilizzare il seguente nome utente e password: Questo metodo viene utilizzato quando SQL Server è stato configurato con utenti standalone per accedere ai database.
- Sostituisci la tabella con SQL personalizzato che fornirà esattamente i dati desiderati nella nostra cartella di lavoro di Excel:
- Seleziona il database a cui ti connetteresti. In questo esempio, ci stiamo connettendo al database di esempio AdventureWorks fornito da Microsoft. Seleziona Connetti a una tabella specifica e scegli la prima tabella. Ricorda, non stiamo andando a recuperare i dati da questa tabella.
- Fai clic su Fine per chiudere la procedura guidata e tornare alla cartella di lavoro. Scambiamo la tabella segnaposto per la nostra query SQL personalizzata.
Si tornerà al modulo Crea tabella pivot (A). Clicca OK.
04 di 15Tabella pivot collegata temporaneamente alla tabella SQL
A questo punto, ci si è connessi alla tabella segnaposto e si dispone di una tabella pivot vuota. Puoi vedere a sinistra dove si trova la tabella pivot e, a destra, c'è un elenco di campi disponibili.
Continua a leggere sotto
05 di 15Apri proprietà di connessione
Assicurati di essere nella scheda Opzioni e fai clic su Cambia origine dati nella sezione Dati. Scegli Proprietà di connessione.
Questo fa apparire il modulo delle proprietà di connessione. Fare clic sulla scheda Definizione. Questo mostra le informazioni di connessione per la connessione corrente a SQL Server. Mentre fa riferimento a un file di connessione, i dati sono effettivamente incorporati nel foglio di calcolo.
06 di 15Aggiorna proprietà di connessione con query
Cambiare il tipo di comando da tabella a SQL e sovrascrivere il testo di comando esistente con la query SQL. Ecco la query che abbiamo creato dal database di esempio 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 ProductCategoryFROM Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ONSales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderIDINNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID =Production.Product.ProductID INNER JOIN Sales.Customer ONSales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID ANDSales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID ENNER JOINSales.vIndividualCustomer ON Sales.Customer.CustomerID =Sales.vIndividualCustomer.CustomerID INNER JOINProduction.ProductSubcategory ON Production.Product.ProductSubcategoryID =Production.ProductSubcategory.ProductSubcategoryIDClicca OK.
Continua a leggere sotto
07 di 15Ricevi avviso di connessione
Riceverai una finestra di dialogo di avviso di Microsoft Excel. Questo perché abbiamo cambiato le informazioni di connessione. Quando abbiamo creato la connessione originariamente, ha salvato le informazioni in un file .ODC esterno (ODBC Data Connection). I dati nella cartella di lavoro erano gli stessi del file .ODC finché non siamo passati da un tipo di comando tabella a tipo di comando SQL nel passaggio 6. L'avviso indica che i dati non sono più sincronizzati e il riferimento al file esterno nella cartella di lavoro verrà rimosso. Questo va bene. Fare clic su Sì.
08 di 15Tabella pivot connessa a SQL Server con query
Questo riporta alla cartella di lavoro di Excel 2010 con una tabella pivot vuota. Puoi vedere che i campi disponibili ora sono diversi e corrispondono ai campi nella query SQL. Ora possiamo iniziare ad aggiungere campi alla tabella pivot.
Continua a leggere sotto
09 di 15Aggiungi campi alla tabella pivot
Nell'elenco dei campi di tabella pivot trascinare l'area ProductCategory in Etichette di riga, OrdineOrdine in colonne e TotalDue in valori. Come puoi vedere, il campo della data ha date individuali, quindi la tabella pivot ha creato una colonna per ogni data unica. Excel 2010 ha alcune funzioni integrate che ci aiutano a organizzare i campi delle date.
10 di 15Aggiungi raggruppamento per campi data
La funzione Raggruppamento ci consente di organizzare le date in anni, mesi, trimestri, ecc. Ciò consentirà di riepilogare i dati e facilitare l'interazione con l'utente. Fare clic con il tasto destro del mouse su una delle intestazioni delle colonne della data e selezionare Gruppo che visualizza il modulo Raggruppamento.
Continua a leggere sotto
11 di 15Scegli Raggruppamento per valori
A seconda del tipo di dati che stai raggruppando, il modulo apparirà un po 'diverso. Excel 2010 consente di raggruppare date, numeri e dati di testo selezionati. Stiamo raggruppando OrderDate in questo tutorial in modo che il modulo mostrerà le opzioni relative ai raggruppamenti di date.
Fai clic su Mesi e Anni e fai clic su OK.
12 di 15Tabella pivot raggruppata in anni e mesi
I dati sono raggruppati per anno prima e poi per mese. Ognuno ha un segno più e meno che consente di espandere e comprimere a seconda di come si desidera vedere i dati.
A questo punto, la tabella pivot è piuttosto utile. Ciascuno dei campi può essere filtrato ma il problema è che non c'è un indizio visivo sullo stato corrente dei filtri. Occorrono diversi clic per cambiare la vista.
13 di 15Insert Slicer (Nuovo in Excel 2010)
I slicer sono nuovi in Excel 2010. I slicer sono fondamentalmente l'equivalente di filtri di impostazione visiva dei campi esistenti e la creazione di filtri di report nel caso in cui l'elemento su cui si desidera filtrare non sia nella visualizzazione Tabella pivot corrente. Questa cosa carina di Slicers è che diventa molto semplice per l'utente modificare la visualizzazione dei dati nella tabella pivot e fornire indicatori visivi sullo stato corrente dei filtri.
Per inserire i Slicer, fare clic sulla scheda Opzioni e fare clic su Inserisci Slicer dalla sezione Sort & Filter. Scegli Inserisci Slicer che apre il modulo Inserisci Slicer. Controlla tutti i campi che vuoi avere a disposizione.
14 di 15Tabella pivot con affettatrici user friendly
Come puoi vedere, i Slicer mostrano tutti i dati come selezionati. È molto chiaro all'utente esattamente quali dati sono nella vista corrente della tabella pivot.
15 di 15Scegli i valori dai slicer che aggiornano la tabella pivot
Fare clic su varie combinazioni di valori e vedere come cambia la vista della tabella pivot. È possibile utilizzare il tipico Microsoft facendo clic sui Slicer, il che significa che se è possibile utilizzare Control + Fare clic per selezionare più valori o Maiusc + Fare clic per selezionare un intervallo di valori.
Ogni Slicer visualizza i valori selezionati che rendono davvero ovvio lo stato della tabella pivot in termini di filtri. Puoi modificare gli stili dei Slicer se lo desideri facendo clic sul menu a discesa Stili veloci nella sezione Slicer della scheda Opzioni.
