01 de 15
Resultado final
Houbo unha brecha entre Microsoft Excel e as plataformas de inteligencia de negocios (BI) por moitos anos. As melloras da táboa dinámica de Microsoft Excel 2010 xunto con algunhas outras características de DNI converteuse nun verdadeiro competidor para a empresa DNI. Excel foi utilizado tradicionalmente para a análise independente e a ferramenta estándar que todos exportan os seus informes finais. A intelixencia comercial profesional tradicionalmente foi reservada para os gustos de SAS, Business Objects e SAP.
Microsoft Excel 2010 (coa táboa dinámica Pivot 2010) xunto con SQL Server 2008 R2, SharePoint 2010 e o complemento gratuíto de Microsoft Excel 2010 "PowerPivot" deu lugar a unha solución de informes e alto nivel de información empresarial.
Este tutorial cobre un escenario sinxelo cunha PivotTable Excel 2010 conectada a unha base de datos SQL Server 2008 R2 usando unha consulta SQL sinxela. Tamén estou usando Slicers para o filtrado visual que é novo en Excel 2010. Cubrirás técnicas de DNI máis complexas usando Expresións de Análise de Datos (DAX) en PowerPivot para Excel 2010 nun futuro próximo. Este último lanzamento de Microsoft Excel 2010 pode proporcionar un valor real para a túa comunidade de usuarios.
02 de 15
Inserir a táboa dinámica
Pode inserir unha táboa dinámica nun libro de Excel novo ou existente. Pode querer considerar colocar o cursor nunhas poucas filas da parte superior. Isto lle dará espazo para unha información de cabeceira ou empresa en caso de que comparta a folla de cálculo ou imprímea.
- Abra un novo ou xa existente libro de Excel 2010 e faga clic na cela onde desexa que estea a esquina superior esquerda da táboa dinámica.
- Fai clic na pestana Inserir e fai clic na pestaña Táboa dinámica na táboa Táboas. Escolla Táboa dinámica. Isto abrirá o formulario de diálogo Crear táboa dinámica.
03 de 15
Conecta a táboa dinámica ao servidor SQL (ou a outra base de datos)
Excel 2010 pode recuperar datos de todos os principais provedores RDBMS (Relational Database Management System) . Os controladores de SQL Server deberían estar dispoñibles para a conexión por defecto. Pero o software de base de datos principal converte os controladores de ODBC (Open Database Connectivity) para que poida facer a conexión. Consulte o seu sitio web se precisa descargar controladores ODBC.
No caso deste tutorial, estou conectando a SQL Server 2008 R2 (versión gratuita de SQL Express).
- A - O formulario Create PivotTable é o primeiro formulario na creación da conexión a SQL Server. Seleccione "Usar unha fonte de datos externa" e faga clic no botón Seleccionar Conexión. Deixar a localización de onde se colocará a táboa dinámica se non desexa crear unha nova folla de cálculo e colocala alí.
- B - O formulario Conexións existentes enumera todas as conexións no caderno de traballo actual, no seu computador e na rede coa que está conectado actualmente. As conexións existentes son realmente só ficheiros de texto coa información de conexión necesaria para acceder a unha fonte de datos particular. No noso caso, imos crear unha nova fonte de datos. Faga clic no botón Buscar para máis.
- C - Prema no botón Novo Fonte iniciarase o asistente de conexión de datos.
- D - Elixe Microsoft SQL Server e fai clic en Seguinte.
- E - Introduza o nome do servidor e rexístrate as credenciais. Elixe o método de autenticación axeitado. Se non estás seguro de que método usar, ponte en contacto co administrador da túa base de datos.
- Use a autenticación de Windows: este método usa o inicio de sesión da rede para acceder ás bases de datos de SQL Server.
- Use o seguinte nome de usuario e contrasinal: este método úsase cando o SQL Server configurouse con usuarios autónomos para acceder a bases de datos.
- F - Neste paso, imos escoller unha táboa como marcador de posición. Imos substituír a táboa con SQL personalizado que proporcionará exactamente os datos que queremos no noso libro de Excel.
- Seleccione a base de datos á que se conectará. Neste exemplo, estamos conectando á base de datos de mostra de AdventureWorks proporcionada por Microsoft. Comprobe a conexión a unha táboa específica e elixa a primeira táboa. Lembra que non imos recuperar datos desta tabela.
- Faga clic en Finalizar que pechará o asistente e devolve ao libro. Cambiaremos a táboa de marcador de posición para a nosa consulta SQL personalizada.
Volverás ao formulario Create PivotTable (A). Faga clic en Aceptar.
04 de 15
Táboa de pivotes temporalmente conectada á táboa SQL
Neste punto, tes conectado á táboa de marcador de posición e tes unha táboa dinámica baleira. Podes ver á esquerda cando a Táboa dinámica estea e á dereita hai unha lista de campos dispoñibles.
05 de 15
Abrir as propiedades da conexión
Antes de comezar a seleccionar datos para a Táboa dinámica, necesitamos cambiar a conexión coa consulta SQL. Asegúrese de estar na pestaña Opcións e prema en "Cambiar fonte de datos desplegable" na sección Datos. Escolla Propiedades de Conexión.
Isto mostra o formulario Propiedades de conexión. Fai clic na pestana Definición. Isto amosará a información de conexión da conexión actual a SQL Server. Aínda que fai referencia a un ficheiro de conexión, os datos están realmente incrustados na folla de cálculo.
06 de 15
Actualiza as propiedades da conexión coa consulta
Cambia o tipo de comando da táboa a SQL e sobrescribe o texto de comando existente coa túa consulta SQL. Aquí está a consulta que creou da base de datos de mostra de AdventureWorks:
SELECCIONAR 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 Categoría de produto
FROM Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID =
Production.Product.ProductID INNER ÚNETE Sales.Customer ON
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID AND
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID IDNER UNNER
Sales.vIndividualCustomer ON Sales.Customer.CustomerID =
Sales.vIndividualCustomer.CustomerID INNER JOIN
Production.ProductSubcategory ON Production.Product.ProductSubcategoryID =
Production.ProductSubcategory.ProductSubcategoryID
Faga clic en Aceptar.
07 de 15
Recibir avisos de conexión
Recibirá un cadro de diálogo Aviso de Microsoft Excel. Isto é porque cambiamos a información da conexión. Cando creamos orixinalmente a conexión, salvou a información nun ficheiro .ODC externo (ODBC Data Connection). Os datos no caderno de traballo foron os mesmos que o ficheiro .ODC ata que cambiamos dun tipo de comando de tabulación ao tipo de comando SQL no Paso 6. A advertencia está dicindo que os datos xa non están en sincronía e eliminarase a referencia ao ficheiro externo no libro. Está ben. Faga clic en Si.
08 de 15
Táboa dinámica conectada a SQL Server con consulta
Isto leva de volta ao libro de Excel 2010 cunha táboa dinámica baleira. Podes ver que os campos dispoñibles agora son diferentes e corresponden aos campos da consulta SQL. Agora podemos comezar a engadir campos á táboa dinámica.
09 de 15
Engadir campos para pivotar a táboa
Na lista de campos de táboa dinámica, arrastre a área de categorías de produtos a área de etiquetas de filas, área de PedidosData a área de etiquetas de columnas e área TotalDue a valores. A imaxe mostra os resultados. Como podes ver, o campo de data ten datas individuais polo que a Táboa dinámica creou unha columna para cada data única. Afortunadamente, Excel 2010 ten algunhas funcións incorporadas que nos axudan a organizar os campos de data.
10 de 15
Engadir agrupamento para os campos de data
A función de Agrupación permítenos organizar datas en anos, meses, trimestres, etc. Isto axudará a resumir os datos e facilitar que o usuario interactúe con el. Fai clic co botón dereito sobre un dos encabezados da columna de datas e selecciona Grupo que mostra o formulario de agrupamento.
11 de 15
Escolla Agrupamento por valores
Dependendo do tipo de datos que estea agrupando, o formulario será un pouco diferente. Excel 2010 permítelle agrupar datas, números e datos de texto seleccionados. Agrupamos OrderDate neste tutorial para que o formulario amose opcións relacionadas cos grupos de datas.
Fai clic en Meses e Anos e fai clic en Aceptar.
12 de 15
Táboa dinámica agrupada por anos e meses
Como se pode ver na imaxe anterior, os datos agrúpanse ao ano primeiro e despois ao mes. Cada un ten un signo máis e menos que permite expandir e colapsar en función de como desexa ver os datos.
Neste punto, a Táboa dinámica é moi útil. Cada un dos campos pode ser filtrado pero o problema é que non hai unha pista visual sobre o estado actual dos filtros. Ademais, leva varios clics para cambiar a vista.
13 de 15
Inserir Slicer (Novo en Excel 2010)
As cortadoras son novas en Excel 2010. Os slicers son basicamente o equivalente a establecer filtros de configuración visual dos campos existentes e crear filtros de informes no caso de que o elemento que desexa filtrar non estea na vista actual de táboas dinámicas. Esta boa cousa sobre Slicers é que é moi doado para o usuario cambiar a vista dos datos na Táboa dinámica e tamén proporcionar indicadores visuais sobre o estado actual dos filtros.
Para inserir Slicers, prema na pestana Opcións e prema en Inserir Slicer desde a sección Ordenar e filtrar. Escolla Inserir Slicer que abre o formulario Inserir formadores. Comprobe cales son os campos que desexa dispoñer. No noso exemplo, agregue Years, CountryRegionName e ProductCategory. pode ter que colocar os Slicers onde queiramos. Por defecto, selecciónanse todos os valores, o que significa que non se aplicaron filtros.
14 de 15
Mesa pivotante con cortadores de uso fácil
15 de 15
Escolla valores de slicers que actualicen a táboa dinámica
Fai clic en varias combinacións de valores e vexa como cambia a vista da Táboa dinámica. Pódese usar clics típicos de Microsoft nos Slicers, o que significa que se pode usar Control + Click para seleccionar varios valores ou Shift + Click para seleccionar un rango de valores. Cada Slicer mostra os valores seleccionados o que fai que sexa realmente evidente o que o estado da Táboa dinámica ten en función de filtros. Pode cambiar os estilos dos Slicers se quere facer clic no menú desplegable Estilos Rápidos na sección Slicer da pestana Opcións.
A introdución de Slicers realmente mellorou a usabilidade das Tablas dinámicas e moveu Excel 2010 moito máis preto de ser unha ferramenta profesional de intelixencia de negocios. As táboas dinámicas melloraron bastante en Excel 2010 e cando combinadas co novo PowerPivot crea un ambiente analítico de alto rendemento.