01 de 15
Resultat final
Hi ha hagut un buit entre Microsoft Excel i les plataformes d'intel·ligència empresarial (BI) durant molts anys. Les millores de la taula dinàmica de Microsoft Excel 2010, juntament amb un parell d'altres funcions de BI, ho han convertit en un veritable competidor per a empreses BI. Excel s'ha utilitzat tradicionalment per a l'anàlisi independent i l'eina estàndard que tothom exporta els seus informes finals. La intel·ligència professional ha estat tradicionalment reservada per a empreses com SAS, Business Objects o SAP.
Microsoft Excel 2010 (amb la Taula dinàmica Pivot 2010) juntament amb SQL Server 2008 R2, SharePoint 2010 i el complement gratuït Microsoft Excel 2010 "PowerPivot" ha donat lloc a una solució d'informes i intel·ligència empresarial d'alt nivell.
Aquest tutorial cobreix un escenari senzill amb una taula dinàmica PivotTable 2010 connectada a una base de dades SQL Server 2008 R2 mitjançant una consulta SQL senzilla. També estic utilitzant Slicers per al filtratge visual que és nou en Excel 2010. Vaig a cobrir les tècniques de BI més complexes utilitzant Expressions d'anàlisi de dades (DAX) en PowerPivot per a Excel 2010 en un futur pròxim. Aquesta versió més recent de Microsoft Excel 2010 pot proporcionar un valor real per a la vostra comunitat d'usuaris.
02 de 15
Insereix la taula dinàmica
Podeu inserir una taula dinàmica en un llibre de treball Excel nou o existent. És possible que vulgueu considerar col·locar el cursor en unes quantes files des de la part superior. Això us donarà espai per obtenir informació sobre capçalera o empresa en cas que compartiu el full de càlcul o imprimiu-lo.
- Obriu un nou o existent llibre d'Excel 2010 i feu clic a la cel·la on vulgueu que es trobi la cantonada superior esquerra de la taula dinàmica.
- Feu clic a la pestanya Insereix i feu clic a la llista desplegable de taula dinàmica a la secció Taules. Trieu taula dinàmica. Això iniciarà el formulari de diàleg Crear quadre dinàmic.
03 de 15
Connecteu la taula dinàmica a SQL Server (o una altra base de dades)
Excel 2010 pot recuperar dades de tots els principals proveïdors RDBMS (Relational Database Management System) . Els controladors de SQL Server haurien d'estar disponibles per a la connexió de manera predeterminada. Però tots els principals programes de base de dades fan que els controladors ODBC (Open Database Connectivity) els permetin fer la connexió. Consulteu el lloc web si necessiteu descarregar els controladors ODBC.
En el cas d'aquest tutorial, estic connectant a SQL Server 2008 R2 (versió gratuïta de SQL Express).
- A: el formulari Crear tauleta dinàmica és el primer formulari a crear la connexió a SQL Server. Seleccioneu "Utilitzeu un origen de dades extern" i feu clic al botó Triar connexió. Deixeu la ubicació d'on es col·locarà la taula dinàmica, tret que vulgueu crear un full de treball nou i col·locar-lo allà.
- B: El formulari Connexions existents mostra una llista de les connexions del llibre actual, a l'ordinador i la xarxa amb la qual està connectat. Les connexions existents són només fitxers de text amb la informació de connexió necessària per accedir a una font de dades particular. En el nostre cas, anem a crear una nova font de dades. Feu clic al botó Navega per més.
- C - Feu clic al botó Nou Origen que iniciarà l'Auxiliar per a la connexió de dades.
- D - Trieu Microsoft SQL Server i feu clic a Següent.
- E: introduïu el nom del servidor i iniciï les credencials. Trieu el mètode d'autenticació adequat. Si no esteu segur de quin mètode heu d'utilitzar, poseu-vos en contacte amb l'administrador de la vostra base de dades.
- Utilitzeu l'autenticació de Windows: aquest mètode utilitza la vostra connexió de xarxa per accedir a bases de dades de SQL Server.
- Utilitzeu el següent nom d'usuari i contrasenya: aquest mètode s'utilitza quan SQL Server s'ha configurat amb usuaris independents per accedir a bases de dades.
- F - En aquest pas, anem a triar una taula com a marcador de posició. Anem a substituir la taula amb SQL personalitzat que proporcionarà exactament les dades que desitgem en el nostre llibre de treball Excel.
- Seleccioneu la base de dades a la qual us connectareu. En aquest exemple, ens connectem a la base de dades d'exemple AdventureWorks proporcionada per Microsoft. Marqueu la connexió a una taula específica i trieu la primera taula. Recordeu que no anem a recuperar dades d'aquesta taula.
- Feu clic a Finalitza que tancarà l'assistent i us tornarà al llibre. Anem a intercanviar la taula de marcador d'espai per a la nostra consulta SQL personalitzada.
Se us retornarà al formulari Crear tauleta dinàmica (A). Feu clic a Acceptar.
04 de 15
Taula de taula temporal connectada a taula SQL
En aquest punt, heu connectat a la taula de marcador d'espai i teniu una taula dinàmica buida. Podeu veure a l'esquerra la taula dinàmica i, a la dreta, hi ha una llista dels camps disponibles.
05 de 15
Obriu les propietats de connexió
Abans d'iniciar la selecció de dades per a la taula dinàmica, necessitem canviar la connexió a la consulta SQL. Assegureu-vos que esteu a la pestanya Opcions i feu clic a Canviar origen de dades desplegable de la secció Dades. Trieu Propietats de connexió.
Això mostra el formulari Propietats de connexió. Feu clic a la pestanya Definició. Això us mostra la informació de connexió de la connexió actual a SQL Server. Tot i que fa referència a un fitxer de connexió, les dades s'insereixen en el full de càlcul.
06 de 15
Actualitzeu les propietats de connexió amb la consulta
Canvieu el tipus de comanda de la taula a SQL i sobreescriure el text de comandament existent amb la vostra consulta SQL. Aquí teniu la consulta que he creat a partir de la base de dades d'exemple 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
FROM Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID =
Producció.Product.ProductID INNER UNEIX Sales.Customer ON
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID AND
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER UNEU
Sales.vIndividualCustomer ON Sales.Customer.CustomerID =
Sales.vIndividualCustomer.Customer ID INNER UNEU
Production.ProductSubcategory ON Production.Product.ProductSubcategoryID =
Production.ProductSubcategory.ProductSubcategoryID
Feu clic a Acceptar.
07 de 15
Rebeu l'avís de connexió
Rebreu un quadre de diàleg d'advertència de Microsoft Excel. Això és perquè hem canviat la informació de la connexió. Quan originalment hem creat la connexió, guardava la informació en un fitxer extern. ODC (Connexió de dades ODBC). Les dades del llibre eren iguals al fitxer. ODC fins que canviem d'un tipus de comanda de taula al tipus de comanda SQL al Pas núm. 6. L'advertència us indica que les dades ja no estan sincronitzades i que es retirarà la referència al fitxer extern del llibre. Això està bé. Feu clic a Sí.
08 de 15
Taula de pivot connectada a SQL Server amb consulta
Això torna al llibre de treball Excel 2010 amb una taula dinàmica buida. Podeu veure que els camps disponibles són ara diferents i corresponen als camps de la consulta SQL. Ara podem començar a afegir camps a la taula dinàmica.
09 de 15
Afegiu camps a la taula dinàmica
A la llista de camps de taula dinàmica, arrossegueu la categoria ProductCategory to Row Labels, OrderDate a l'àrea d'etiquetes de columnes i TotalDue to Values area. La imatge mostra els resultats. Com podeu veure, el camp de dates té dates individuals, de manera que la taula dinàmica ha creat una columna per a cada data única. Afortunadament, Excel 2010 té funcions integrades per ajudar-nos a organitzar camps de dates.
10 de 15
Afegiu l'agrupació per als camps de dates
La funció d'agrupació ens permet organitzar dates en anys, mesos, trimestres, etc. Això ajudarà a resumir les dades i facilitar-ne l'interacció amb l'usuari. Feu clic amb el botó dret sobre un dels encapçalaments de la columna de data i seleccioneu Grup que mostra el formulari Agrupació.
11 de 15
Trieu Agrupar per valors
Depenent del tipus de dades que agrupeu, el formulari es mostrarà una mica diferent. Excel 2010 us permet agrupar dates, números i dades de text seleccionades. Agrupem OrderDate en aquest tutorial perquè el formulari mostri opcions relacionades amb grups de dates.
Feu clic a "Meses i anys" i feu clic a Acceptar.
12 de 15
Taula de taula agrupada per anys i mesos
Com podeu veure a la imatge anterior, les dades s'agrupen per primer any i després per mes. Cadascun d'ells té un signe més i menys que us permetrà expandir-se i reduir-lo depenent de com vulgueu veure les dades.
En aquest punt, la taula dinàmica és bastant útil. Cadascun dels camps es pot filtrar però el problema és que no hi ha una pista visual quant a l'estat actual dels filtres. A més, es triga diversos clics per canviar la vista.
13 de 15
Inserció de tallador (nou en Excel 2010)
Les rodanxes són noves en Excel 2010. Els robatori són, bàsicament, equivalents als filtres de configuració visual dels camps existents i la creació de filtres d'informes en el cas que l'element que voleu filtrar no es troba a la taula actual de taula dinàmica. Això interessant de Slicers és que resulta molt fàcil que l'usuari canviï la vista de les dades a la taula dinàmica, a més de proporcionar indicadors visuals quant a l'estat actual dels filtres.
Per inserir Slicers, feu clic a la pestanya Opcions i feu clic a Insereix el tallador de la secció Ordenar i filtre. Trieu Insereix llesctador que obre el formulari Insereix llesques. Comproveu quants dels camps voleu que estigueu disponibles. En el nostre exemple, he afegit Years, CountryRegionName i ProductCategory. és possible que hàgiu de col·locar els talladors a on vulgueu. Per defecte, tots els valors es seleccionen, el que significa que no s'han aplicat filtres.
14 de 15
Taula de pivot amb talladores de fàcil ús
15 de 15
Trieu valors de les màquines de xat que actualitzen la taula dinàmica
Feu clic a diverses combinacions de valors i vegeu com canvia la vista de la taula dinàmica. Podeu fer clics típiques a Microsoft en els Slicers, el que significa que si podeu utilitzar Control + Feu clic per seleccionar diversos valors o Maj + clic per seleccionar un rang de valors. Cada Slicer mostra els valors seleccionats que fan que sigui realment evident quin és l'estat de la taula dinàmica en termes de filtres. Podeu canviar els estils de les rodanxes si voleu fer clic al menú desplegable Estils ràpids a la secció de tallador de la pestanya Opcions.
La introducció de Slicers ha millorat molt la usabilitat de les taules dinàmiques i ha mogut Excel 2010 molt més a prop de ser una eina professional d'intel ligència empresarial. Les taules dinàmiques s'han millorat bastant a Excel 2010 i, quan es combinen amb el nou PowerPivot, es crea un entorn analític d'alt rendiment.