Ha habido una brecha entre Microsoft Excel y las plataformas de inteligencia empresarial (BI) de primer nivel durante muchos años. Las mejoras de la tabla dinámica de Microsoft Excel 2010 junto con un par de otras características de BI lo han convertido en un verdadero competidor para el BI empresarial. Tradicionalmente, Excel se ha utilizado para el análisis independiente y la herramienta estándar en la que todos exportan sus informes finales. La inteligencia empresarial profesional se ha reservado tradicionalmente para los gustos de SAS, Business Objects y SAP.
Resultado final
Microsoft Excel 2010 (con la tabla dinámica de Excel 2010) junto con SQL Server 2008 R2, SharePoint 2010 y el complemento gratuito de Microsoft Excel 2010 "PowerPivot" ha dado como resultado una solución de informes e inteligencia empresarial de alto nivel.
Este tutorial cubre un escenario sencillo con una tabla dinámica de Excel 2010 conectada a una base de datos de SQL Server 2008 R2 mediante una consulta SQL simple.
Continuar leyendo a continuación
02 de 15Insertar tabla de pivote
Puede insertar una tabla dinámica en un libro de Excel nuevo o existente. Es posible que desee considerar colocar su cursor hacia abajo unas pocas filas desde la parte superior. Esto le dará espacio para un encabezado o información de la compañía en caso de que comparta la hoja de trabajo o la imprima.
- Abra un libro de Excel 2010 nuevo o existente y haga clic en la celda donde desea que se encuentre la esquina superior izquierda de la tabla dinámica.
- Haga clic en la pestaña Insertar y haga clic en la tabla desplegable de tabla dinámica en la sección Tablas. Elija la tabla dinámica. Esto abrirá el formulario de diálogo Crear tabla dinámica.
Continuar leyendo a continuación
03 de 15Conectar tabla dinámica a SQL Server
Excel 2010 puede recuperar datos de todos los proveedores principales de RDBMS (Sistema de administración de bases de datos relacionales). Los controladores de SQL Server deberían estar disponibles para la conexión de forma predeterminada. Compruebe su sitio web si necesita descargar controladores ODBC.
En el caso de este tutorial, nos estamos conectando a SQL Server 2008 R2 (versión gratuita de SQL Express).
- Abra el formulario Crear tabla dinámica. Seleccione "Usar un origen de datos externo" y haga clic en el botón Elegir conexión. Deje la ubicación donde se colocará la tabla dinámica.
- Abra el formulario Conexiones existentes. Haga clic en el botón Buscar más.
- Haga clic en el botón Nueva fuente para iniciar el Asistente de conexión de datos.
- Elija Microsoft SQL Server y haga clic en Siguiente.
- Ingrese el nombre del servidor y las credenciales de inicio de sesión. Elija el método de autenticación apropiado:
- Utilice la autenticación de Windows: Este método utiliza su inicio de sesión de red para acceder a las bases de datos de SQL Server.
- Utilice el siguiente nombre de usuario y contraseña: Este método se usa cuando SQL Server se ha configurado con usuarios independientes para acceder a las bases de datos.
- Reemplace la tabla con SQL personalizado que proporcionará exactamente los datos que queremos en nuestro libro de Excel:
- Seleccione la base de datos a la que se conectará. En este ejemplo, nos estamos conectando a la base de datos de muestra AdventureWorks proporcionada por Microsoft. Verifique Conectarse a una tabla específica y elija la primera tabla. Recuerde, no vamos a recuperar datos de esta tabla.
- Haga clic en Finalizar que cerrará el asistente y lo regresará al libro de trabajo. Cambiaremos la tabla de marcadores de posición para nuestra consulta SQL personalizada.
Volverá al formulario Crear tabla dinámica (A). Haga clic en Aceptar.
04 de 15Tabla dinámica conectada temporalmente a la tabla SQL
En este punto, se ha conectado a la tabla de marcadores de posición y tiene una tabla dinámica vacía. Puede ver a la izquierda donde estará la tabla dinámica, y a la derecha, hay una lista de campos disponibles.
Continuar leyendo a continuación
05 de 15Abrir propiedades de conexión
Asegúrese de estar en la pestaña Opciones y haga clic en el menú desplegable Cambiar origen de datos de la sección Datos. Elija propiedades de conexión.
Esto abre el formulario Propiedades de conexión. Haga clic en la pestaña Definición. Esto le muestra la información de conexión para la conexión actual a SQL Server. Si bien hace referencia a un archivo de conexión, los datos están realmente incrustados en la hoja de cálculo.
06 de 15Actualizar las propiedades de conexión con consulta
Cambie el tipo de comando de la tabla a SQL y sobrescriba el texto de comando existente con su consulta SQL. Aquí está la consulta que creamos desde la base de datos de ejemplo de AdventureWorks:
SELECCIONE 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,Producción.Producto.Nombre,Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,Sales.Customer.CustomerType,Producción.Producto.ListaPrecio,Producción.Producto.ProductoLínea,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 INNER JOINSales.vIndividualCustomer ON Sales.Customer.CustomerID =Sales.vIndividualCustomer.CustomerID INNER JOINProduction.ProductSubcategory ON Production.Product.ProductSubcategoryID =Production.ProductSubcategory.ProductSubcategoryIDHaga clic en Aceptar.
Continuar leyendo a continuación
07 de 15Recibir advertencia de conexión
Recibirá un cuadro de diálogo de advertencia de Microsoft Excel. Esto se debe a que cambiamos la información de conexión. Cuando creamos originalmente la conexión, guardó la información en un archivo .ODC externo (Conexión de datos ODBC). Los datos en el libro de trabajo eran los mismos que en el archivo .ODC hasta que cambiamos de un tipo de comando de tabla a tipo de comando SQL en el Paso # 6. La advertencia le indica que los datos ya no están sincronizados y que se eliminará la referencia al archivo externo en el libro de trabajo. Esto esta bien. Haga clic en Sí.
08 de 15Tabla de pivote conectada a SQL Server con consulta
Esto lleva al libro de Excel 2010 con una tabla dinámica vacía. Puede ver que los campos disponibles ahora son diferentes y corresponden a los campos en la consulta SQL. Ahora podemos comenzar a agregar campos a la tabla dinámica.
Continuar leyendo a continuación
09 de 15Añadir campos a la tabla dinámica
En la lista de campos de tabla dinámica, arrastre ProductCategory al área de etiquetas de fila, área OrderDate a etiquetas de columna y área TotalDue a valores. Como puede ver, el campo de fecha tiene fechas individuales, por lo que la tabla dinámica ha creado una columna para cada fecha única. Excel 2010 tiene algunas funciones integradas que nos ayudan a organizar los campos de fecha.
10 de 15Añadir agrupación para campos de fecha
La función de agrupación nos permite organizar las fechas en años, meses, trimestres, etc. Esto ayudará a resumir los datos y facilitará que el usuario interactúe con ellos. Haga clic con el botón derecho en uno de los encabezados de columna de fecha y elija Grupo que muestra el formulario de agrupación.
Continuar leyendo a continuación
11 de 15Elija Agrupar Por Valores
Según el tipo de datos que esté agrupando, el formulario se verá un poco diferente. Excel 2010 le permite agrupar fechas, números y datos de texto seleccionados. Estamos agrupando OrderDate en este tutorial para que el formulario muestre las opciones relacionadas con las agrupaciones de fechas.
Haga clic en Meses y Años y haga clic en Aceptar.
12 de 15Tabla de pivote agrupada por años y meses
Los datos se agrupan por año primero y luego por mes. Cada uno tiene un signo más y menos que le permite expandirse y contraerse dependiendo de cómo desee ver los datos.
En este punto, la tabla dinámica es bastante útil. Cada uno de los campos se puede filtrar, pero el problema es que no hay una pista visual sobre el estado actual de los filtros. Se necesitan varios clics para cambiar la vista.
13 de 15Insert Slicer (Nuevo en Excel 2010)
Las segmentaciones son nuevas en Excel 2010. Las segmentaciones son básicamente el equivalente a configurar visualmente los filtros de los campos existentes y crear Filtros de informe en caso de que el elemento que desea filtrar no se encuentre en la vista de tabla dinámica actual. Lo bueno de Slicers es que resulta muy fácil para el usuario cambiar la vista de los datos en la tabla dinámica, así como proporcionar indicadores visuales sobre el estado actual de los filtros.
Para insertar segmentaciones, haga clic en la pestaña Opciones y haga clic en Insertar segmentación en la sección Ordenar y filtrar. Elija Insert Slicer que abre el formulario Insert Slicers. Marque tantos campos como desee tener disponibles.
14 de 15Mesa pivotante con cortadores fáciles de usar
Como puede ver, las segmentaciones muestran todos los datos como seleccionados. Es muy claro para el usuario exactamente qué datos se encuentran en la vista actual de la tabla dinámica.
15 de 15Elija los valores de las divisiones que actualizan la tabla dinámica
Haga clic en varias combinaciones de valores y vea cómo cambia la vista de la tabla dinámica. Puede usar el clic típico de Microsoft en las segmentaciones de datos, lo que significa que si puede usar Control + clic para seleccionar varios valores o Mayús + clic para seleccionar un rango de valores.
Cada Slicer muestra los valores seleccionados, lo que hace que sea realmente obvio cuál es el estado de la tabla dinámica en términos de filtros. Puede cambiar los estilos de las Rebanadoras si lo desea haciendo clic en el menú desplegable Estilos rápidos en la sección Rebanadora de la pestaña Opciones.
