1
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
16-2-2011
PowerPivot es una marca registrada por Microsoft | Autor Luis Muñiz
COMO UTILIZAR EL NUEVO
COMPONENTE
POWERPIVOT
EN EXCEL 2010 PARA CREAR
TABLAS DINÁMICAS
2
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Autor Luis Muñiz
[email protected]
www.sistemacontrolgestion.com
Nota del Autor
Este documento tiene como objetivo dar a conocer el contenido del complemento PowePivot a los usuarios dicho software se integra en Excel 2010 y está basado en la información que proporciona Microsoft en la ayuda del producto que por supuesto pueden consultar los usuarios y es mucho más amplia, y en los datos de los diferentes apartados de la web de Microsoft. Agradezco desde aquí la información que nos proporciona la web de www.powerpivot.com, en la que podemos encontrar ejemplos e imágenes del producto. Adicionalmente la experiencia del autor en la utilización de las tablas dinámicas como autor de libros en Excel 2007 y Excel 2010 permite añadir un valor añadido en este documento, adicionalmente disponen de más información en www.sistemacontrolgestion.com/tds
Bibliografía utilizada : ayuda de PowerPivot
y la web:
www.powerpivot.com
Como utilizar el nuevo
componente
PowerPivot
en Excel
3
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Índice
Pág.
Introducción
5
1 Introducción a PowerPivot con Excel 2010
6
1.1 Que es PowerPivot para Excel 2010
6
1.2 Como instalar el complemento de PowePivot
8
2 Menús de PowerPivot
10
2.1 Ventana de PowerPivot
10
2.2 Página Principal
Portapapeles
Obtener Datos Externos
Informes
Formato
Ordenar y Filtrar
Ver
11
2.3 Diseño
Columnas
Cálculos
Conexiones
Relaciones
Propiedades
Editar
77
2.4 Tablas Vinculadas
96
3. Ventana PowerPivot
98
3.1 Medidas
98
3.2 Informe de tabla dinámica
104
3.3 Datos de Excel
108
3.4 Opciones de Configuración
109
3.5 Mostrar u ocultar la lista de campos
111
4
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Pág.
4. Ventajas de la utilización de PowerPivot
122
5. Ejemplo de PowerPivot
126
5.1 Datos iniciales del ejemplo práctico
126
5.2 Desarrollo
129
ANEXOS:
Información sobre el libro Dominar las Tablas
Dinámicas en Excel 2007-2010 Aplicadas a la Gestión
Empresarial
Artículo: Las nuevas Tablas Dinámicas de Excel como
sistema de información de los ERP y CRM
Entrevista publicada en It-Spain.net sobre el libro
Dominar las tablas dinámicas en Excel 2007-2010
5
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Introducción
Desde que salió al mercado el Excel 2010 ha incorporado de forma
gratuita un complemento que se llama
PowerPivot
y que nos permite
gestionar diferentes bases de datos desde Excel para crear con ellas las
tablas dinámicas, indicadores y gráficos interactivos, es por ello que he
intentado confeccionar un documento de libre distribución para que los
usuarios puedan utilizar esta herramienta de una forma ágil y
práctica, para ello en la web:
www.powerpivot.com
pueden analizar
el funcionamiento y descargar este complemento de forma
gratuita
, sólo hay que tener instalado el Excel 2010.
Esta publicación complementa el libro Dominar las Tablas Dinámicas
para mejorar la gestión empresarial,
PowerPivot
tiene una ayuda
muy completa que puede dar respuesta a las necesidades de cada
usuario en todo momento, pero este documento le ayudará sin duda a
conocer el funcionamiento global de esta excelente herramienta para
gestionar las diferentes fuentes de información.
6
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
1. Introducción a PowerPivot con
Excel 2010
1.1 Que es PowerPivot para Excel
2010
PowerPivot para Excel 2010 es una herramienta de análisis de datos que proporciona un potencial de cálculo de tiempo inigualable directamente desde el software que los usuarios ya conocen y aprecian: Microsoft Excel. ©
Con este complemento de Excel, puede transformar enormes cantidades de datos en información significativa a una velocidad considerable para obtener las respuestas que necesita.
Puede compartir fácilmente sus conclusiones con otras personas. PowerPivot puede incluso ayudar a su departamento de TI a mejorar la eficacia operativa a través de herramientas de administración basadas en SharePoint también. PowerPivot para Excel 2010, es un complemento de Microsoft Excel 2010 que permite crear eficaces análisis mediante una rápida manipulación de millones de filas de datos dentro de un libro de trabajo de Excel y utilizar Microsoft Office 2010 para compartir y colaborar en tus ideas con otras personas.
Se puede combinar la funcionalidad nativa de Excel 2010 con PowerPivot en el motor de memoria para permitir a los usuarios explorar interactivamente y realizar cálculos en grandes conjuntos de datos. Además, es fácil racionalizar el proceso de integración de datos de múltiples fuentes, incluyendo bases de datos corporativas, hojas de cálculo, informes y alimentación de datos.
Compartir y colaborar con la confianza de fácilmente poder publicar su análisis a SharePoint 2010 y otros usuarios poder disfrutar las capacidades de la misma corta y rápida consulta cuando trabaja con su Servicio de informes con Excel.
PowerPivot para Excel es un complemento que puede usar para realizar eficaces análisis de datos en Excel 2010, al tiempo que lleva la inteligencia empresarial de autoservicio a su escritorio. PowerPivot para Excel incluye una ventana para agregar y preparar datos, y una ficha de PowerPivot en la cinta de opciones de Excel que puede usar para tratar los datos de una hoja de cálculo de Excel.
7
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
PowerPivot para Excel también incluye un asistente que puede utilizar para importar los datos de orígenes diferentes, desde grandes bases de datos corporativas de la intranet, a fuentes de distribución de datos públicas u hojas de cálculo y archivos de texto de un equipo. Los datos se importan en PowerPivot para Excel como tablas. Estas tablas se muestran como hojas independientes en la ventana de PowerPivot, de forma similar a las hojas de cálculo de un libro de Excel. Pero PowerPivot para Excel proporciona una funcionalidad significativamente diferente de la que está disponible en una hoja de cálculo de Excel.
Los datos con los que opera en la ventana de PowerPivot están almacenados en una base de datos de análisis dentro del libro de Excel y un eficaz motor local carga, consulta y actualiza los datos de dicha base de datos. Los datos de PowerPivot se pueden mejorar todavía más creando relaciones entre tablas en la ventana de PowerPivot. Y dado que los datos de PowerPivot están en Excel, están inmediatamente disponibles para las tablas dinámicas, gráficos dinámicos y otras características de Excel que se utilizan para agregar datos e interactuar con ellos. Excel 2010 proporciona todas las capacidades de presentación e interactividad de datos; por otra parte, los datos de PowerPivot y los objetos de presentación de Excel se encuentran en mismo archivo de libro (.xlsx, .xlsb o .xlsm). PowerPivot admite archivos de hasta 2 GB en tamaño y le permite trabajar con hasta 4 GB de datos en memoria.
Además de las herramientas gráficas que le ayudan a analizar los datos, PowerPivot incluye DAX (Expresiones de análisis de datos). El lenguaje DAX (Expresiones de análisis de datos) es un nuevo lenguaje de fórmulas que amplía las capacidades de tratamiento de datos de Excel para habilitar agrupaciones, cálculos y análisis más sofisticados y complejos. La sintaxis de las fórmulas de DAX es muy parecida a la de las fórmulas de Excel con una combinación de funciones, operadores y valores.
PowerPivot permitirá a Office Excel 2010 la posibilidad de analizar grandes cantidades de datos desde orígenes diversos como puedan ser los servicios de análisis o de reportes de Microsoft SQL Server
8
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
1.2 Como instalar el complemento de
PowePivot
En primer lugar según la versión se puede proceder a descargar el
complemento desde la siguiente url:
http://www.microsoft.com/downloads/es-es
:
Instrucciones:
PowerPivot para Excel 2010 (32 bit):
http://go.microsoft.com/fwlink/?LinkID=168583&clcid=0xC0A
PowerPivot para Excel 2010 (64 bit):
http://go.microsoft.com/fwlink/?LinkID=168584&clcid=0xC0A
Importante:
1) Tenga en cuenta que PowerPivot para Microsoft® Excel solo funciona con la versión más reciente de Microsoft® Office 2010. Tenga en cuenta que PowerPivot para Microsoft® Excel solo funciona con la versión más reciente de Microsoft® Office 2010.
2) Esta es la copia de los términos de licencia Microsoft® SQL Server® 2008 R2 - PowerPivot para Microsoft® Excel 2010 - RTM
para que los revise.
9
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Requisitos del sistema
Sistemas operativos compatibles: Windows 7
Windows Server 2008 R2 (64 bits),
Windows XP con SP3 (32 bits),
Windows Vista con SP2 (32 o 64 bits)*,
Windows Server 2008 con SP2 (32 o 64 bits)*,
Windows Server 2003 R2 (32 o 64 bits) con MSXML 6.0 instalado+,
Se admite Terminal Server y Windows en Windows (WOW) (que le permite la
instalación de versiones de 32 bits de Office 2010 en sistemas operativos de 64
bits).
* También se requiere la
Actualización de plataforma para Windows
Vista/Windows Server 2008.
+ Solo se admite Microsoft®Office 2010/PowerPivot para Microsoft® Excel
2010 de 32 bits en Windows Server 2003 R2 de 64 bits.
Procesador de 32 o 64 bits a 500 MHz o superior
1 GB de RAM como mínimo (se recomienda 2 GB o más).
3,5 MB de espacio disponible en disco duro.
Acceso a Internet
Instrucciones
Para instalar PowerPivot para Microsoft® Excel 2010, complete los pasos
siguientes por este orden:
1. Si ejecuta un sistema operativo que no sea Microsoft® Windows® 7 o
Microsoft® Windows® Server 2008 R2, instale
.NET Framework 3.5 SP1
.
2. Si está ejecutando Windows® Vista o Windows® Server 2008, instale la
Actualización de plataforma
3. Instale
Microsoft® Office 2010
.
Importante: si desea realizar una instalación personalizada de la versión beta
de Microsoft Office 2010, debe instalar Excel 2010 y las características
compartidas de Office para usar PowerPivot para Excel.
4. Instale PowerPivot para Microsoft® Excel
Importante: si instala la versión de 32 bits de Excel, use la versión de 32 bits
de PowerPivot. Si instala la versión de 64 bits de Excel, use la versión de 64
bits de PowerPivot.
Después de instalar PowerPivot para Microsoft® Excel, tendrá acceso a la
documentación desde la ventana de PowerPivot con un clic en el botón Ayuda
o presionando F1. Le recomendamos que use la versión más reciente de la
documentación, que está disponible desde la
Ayuda en pantalla de PowerPivot
para Microsoft® Excel.
Puede obtener acceso a otros artículos, blogs y
contenidos de la comunidad desde la página
PowerPivot TechNet
.
Nota:
1. Al iniciar Microsoft® Office 2010 Excel por primera vez, se le pedirá
permiso- PowerPivot para Microsoft® Excel 2010.
10
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
2. Menús de PowePivot
2.1 Ventana de PowerPivot
Esta sección proporciona ayuda sobre la ficha y cuadros de diálogo de PowerPivot que se usan en la ventana de Excel cuando se instala PowerPivot para Excel. Para obtener información general de la interfaz de usuario. La ficha PowerPivot de Excel proporciona herramientas para trabajar con tablas dinámicas, medidas y tablas vinculadas, y para abrir la ventana de PowerPivot.
Ventana de PowerPivot
Pulsando el botón anterior accederá a los menús de trabajo de PowerPivot que son los siguientes:
Página Principal:
Para entrar en
PowePivot
pulsar
11
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Diseño:
Si utilizamos
tablas vinculadas
surge este menú:
2.2 Página Principal
En esta sección se proporciona ayuda en la ficha Página Principal de la ventana de PowerPivot, incluidos los cuadros de diálogo que están disponibles desde esta ficha. Para obtener información general acerca de la interfaz de usuario e instrucciones para abrir la ventana de PowerPivot.
12
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
La ficha Página Principal le permite agregar nuevos datos, copiar y pegar datos de Excel y otras aplicaciones, aplicar formato y ordenar y filtrar datos.
Nota:
En Windows Vista y Windows 7, las características de la ventana de PowerPivot están disponibles en una cinta de opciones, que se explica en esta sección. En Windows XP, las características están disponibles de un conjunto de menús. Si usa Windows XP y desea ver cómo se relacionan los comandos de menú con los comandos de la cinta de opciones, vea Interfaz de usuario de PowerPivot en Windows XP.
Opciones de la Página Principal
13
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Las opciones de la ficha Página Principal se organizan en grupos.
Portapapeles
Este grupo permite copiar y pegar los datos en el libro de PowerPivot actual.
Pegar. Pega datos desde el Portapapeles en una nueva tabla de la ventana de PowerPivot.
Pegar y anexar. Pega datos desde el Portapapeles al final de una tabla existente en la ventana de PowerPivot.
Pegar y reemplazar. Usa los datos del Portapapeles para reemplazar datos de una tabla existente del libro de PowerPivot.
Copiar. Copia datos seleccionados del libro en el Portapapeles.
Puede copiar datos en forma de tabla de aplicaciones externas y pegarlos en un libro de PowerPivot. Los datos que pega del Portapapeles deben estar en formato HTML, como los datos que se copian de Excel o Word. Para obtener más información.
14
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Copiar y pegar datosPuede copiar los datos como en las tablas de las aplicaciones externas y pegarlos en la ventana de PowerPivot. Los datos que pega del Portapapeles deben estar en formato HTML, como los datos que se copian de Excel o Word. También se puede copiar y pegar una tabla entera o un intervalo en PowerPivot. PowerPivot detectará automáticamente los tipos de datos y los aplicará a los datos pegados. Sin embargo, también puede modificar el tipo de datos o formato para mostrar de una columna de forma manual. Para obtener más información,
Hay algunas restricciones al pegar los datos:
Al usar Pegar datos anexados o Pegar datos reemplazados, los nuevos datos deben contener exactamente el mismo número de columnas que los antiguos. Si es posible, las columnas de datos que se pegan o anexan también deberían ser de los mismos tipos de datos que los de la tabla de destino, o ser de un tipo de datos compatible. En algunos casos puede utilizar un tipo de datos diferente, pero normalmente se mostrará un error No coinciden los tipos.
Las opciones Pegar y anexar y Pegar y reemplazar solo están disponibles al trabajar con una tabla que se creó inicialmente pegando datos en el Portapapeles. No se puede usar Pegar datos anexados ni Pegar datos reemplazados al agregar datos a una tabla vinculada o a una tabla de datos importados.
Para pegar datos en la ventana de PowerPivot:
En la ventana de PowerPivot, haga clic en la ficha Inicio y en el grupo Portapapeles haga clic en uno de los siguientes botones:
Haga clic en Pegar para pegar el contenido del Portapapeles en una tabla PowerPivot nueva.
Haga clic en Pegar datos anexados para pegar el contenido del
Portapapeles como filas adicionales en la tabla seleccionada. Las nuevas filas se agregarán al final de la tabla.
Haga clic en Pegar datos reemplazados para reemplazar la tabla seleccionada con el contenido del Portapapeles. Todos los nombres de encabezado de columna existentes permanecerán en la tabla y las relaciones se conservarán de modo que no tenga que copiar los encabezados de columna del origen original.
15
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Obtener datos externosEste grupo le permite conectarse a orígenes de datos externos e importar datos de esos orígenes. Para obtener información acerca de los orígenes de datos admitidos.
Orígenes de datos admitidos en libros de PowerPivot:
PowerPivot para Excel puede importar datos desde gran variedad de orígenes. En este tema se describen los tipos de orígenes de datos que se pueden utilizar, así como información orientativa para identificar los que funcionan mejor en un libro de PowerPivot.
-Orígenes de datos compatibles
Puede importar datos de los orígenes de datos de la siguiente tabla. PowerPivot para Excel no instala los proveedores enumerados para cada origen de datos. Algunos proveedores ya podrían estar instalados con otras aplicaciones en el equipo; en otros casos tendrá que descargar e instalar el proveedor.
También puede vincular a tablas en Excel y copiar y pegar los datos desde aplicaciones, como Excel y Word, que usan un formato HTML para el Portapapeles. Para obtener más información,
16
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Origen Versiones Tipo de
archivo Proveedores
1
Bases de datos de
Access Microsoft Access 2003, 2007, 2010.
.accdb o
.mdb Proveedor OLE DB de ACE 14 Bases de datos relacionales de SQL Server Bases de datos de Microsoft SQL Server2005, 2008, 2008 R2; Microsoft SQL Azure 2 (no
aplicable) Proveedor OLE DB para SQL Server Proveedor OLE DB de SQL Server Native Client Proveedor OLE DB de SQL Server Native Client 10.0 Proveedor de datos de .NET Framework para SQL Client Almacenamiento de datos paralelo de SQL Server (PDW) 3 2008 R2 (no
aplicable) Proveedor OLE DB para SQL Server PDW
Bases de datos relacionales de Oracle
Oracle 9i, 10g,
11g. (no aplicable) Proveedor OLE DB de Oracle Proveedor de datos de .NET Framework para cliente de Oracle Proveedor de datos de .NET Framework para SQL Server Proveedor MSDAORA OLE DB 4 OraOLEDB MSDASQL Bases de datos relacionales de Teradata Teradata V2R6,
V12 (no aplicable) Proveedor OLE DB TDOLEDB Proveedor de datos .NET para Teradata
Bases de datos relacionales de Informix
(no
17
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Bases de datos relacionales de IBM DB2 8.1 (no aplicable) DB2OLEDB Bases de datos relacionales de Sybase (noaplicable) Proveedor OLE DB de Sybase Otras bases de
datos relacionales (no aplicable) (no aplicable) Proveedor OLE DB o controlador ODBC
Archivos de texto (no aplicable) .txt, .tab,
.csv Proveedor OLE DB ACE 14 para Microsoft Access Archivos de
Microsoft Excel Excel 97-2003, 2007, 2010 .xlsx, xlsm, .xlsb, .xltx, .xltm
Proveedor OLE DB de ACE 14
Libro de PowerPivot Microsoft SQL Server 2008 R2 Analysis Services .xlsx, xlsm, .xlsb, .xltx, .xltm ASOLEDB 10.5 (solo se usa con libros de PowerPivot que se publican en granjas de servidores de SharePoint que tienen instalado PowerPivot para SharePoint) Cubo de Analysis
Services Microsoft SQL Server 2005, 2008, 2008 R2 Analysis Services (no aplicable) ASOLEDB 10 Fuentes de distribución de datos (se usa para
importar datos de informes de Reporting Services, documentos de servicio de Atom y fuente de distribución de datos única) Formato Atom 1.0 Cualquier base de datos que se exponga como un servicio ADO.Net Data Services Framework, como un informe de Microsoft SQL Server 2005, 2008, 2008, 2008 R2 Reporting Services .atomsvc para un documento de servicio que define una o más fuentes .atom para un documento de fuente web de Atom Proveedor de fuentes de distribución de datos de Microsoft para PowerPivot Proveedor de datos de fuentes de distribución de datos de .NET Framework para PowerPivot
18
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Archivos de OfficeDatabase Connection
.odc
1 También puede utilizar el Proveedor OLE DB para ODBC.
2 para obtener más información sobre SQL Azure, vea del sitio web SQL
Azure.
3 Para obtener más información acerca de SQL Server PDW, vea el sitio web
sobre el almacén de datos paralelo de SQL Server 2008 R2.
4 En algunos casos, el uso del proveedor OLE DB MSDAORA puede producir
errores de conexión, particularmente con versiones más recientes de Oracle. Si encuentra cualquier error, le recomendamos que utilice otro de los proveedores enumerados para Oracle.
-Orígenes no compatibles
El siguiente origen de datos no se admite actualmente:
Los documentos de servidor publicados, como bases de datos de Access
que ya se han publicado en SharePoint, no se pueden importar. Sugerencias para elegir los orígenes de datos
1. La importación de tablas desde bases de datos relacionales ahorra trabajo porque durante la importación se usan relaciones de clave
externa para crear relaciones entre las hojas de cálculo de PowerPivot.
2. La importación de varias tablas y la eliminación posterior de las que no necesite también ahorra trabajo. Si importa tablas de una en una, aún será necesario crear manualmente las relaciones entre las tablas. 3. Las columnas que contienen datos similares en orígenes de datos
diferentes son la base de la creación de relaciones dentro de la ventana de PowerPivot. Cuando use orígenes de datos heterogéneos, elija tablas con columnas que se puedan asignar a tablas de otros orígenes de datos que contengan datos idénticos o similares.
4. Para admitir la actualización de datos para un libro que se publique en SharePoint, elija orígenes de datos accesibles tanto para las estaciones de trabajo como para los servidores. Después de publicar el libro, puede configurar una programación de actualización de datos para actualizar automáticamente la información en el libro.
19
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
La utilización de orígenes de datos que están disponibles en servidores de red posibilita la actualización de datos. Para obtener más información.
5. En ocasiones, los proveedores OLE DB pueden proporcionar un rendimiento más rápido para datos de mayor escala. Cuando deba elegir entre diferentes proveedores para el mismo origen de datos, pruebe en primer lugar el proveedor OLE DB.
Importar datos Desde una base de datos
Conéctese con SQL Server, Microsoft Access y cubos de SQL Server Analysis Services, así como libros de PowerPivot publicados en SharePoint. Para obtener más información, vea Importar datos de una base de datos y
Importar datos de Analysis Services o PowerPivot. Conéctese con otros orígenes relacionales haciendo clic en De otros orígenes.
Importar datos de una base de datos
En PowerPivot para Excel, puede importar los datos de diversas bases de datos relacionales y orígenes multidimensionales usando el Asistente para la importación de tablas. El Asistente para importación de tablas está disponible en la ventana de PowerPivot, en la ficha Inicio, en el grupo Obtener datos externos. Para conectarse a un origen de datos, debe tener instalado en el equipo el proveedor adecuado. Para obtener más información acerca de los orígenes de datos y los proveedores admitidos, vea Orígenes de datos admitidos en libros de PowerPivot.
Nota:
En Windows Vista y Windows 7, las características de la ventana de PowerPivot están disponibles en una cinta de opciones, que se explica en este tema. En Windows XP, las características están disponibles de un conjunto de menús. Si usa Windows XP y desea ver cómo se relacionan los comandos de menú con los comandos de la cinta de opciones, vea Interfaz de usuario de PowerPivot en Windows XP.
La siguiente tabla describe, para cada tipo de base de datos, cómo tener acceso al Asistente para la importación de tablas en el grupo Obtener datos externos. Complete los pasos del asistente para conectar con una base de datos e importar datos. En la página Elegir cómo importar los datos, puede
20
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
seleccionar desde una lista de tablas o vistas o escribir una consulta que especifique los datos que importar.
Origen de datos Acceso al Asistente para la importación de tablas
Base de datos de Microsoft SQL Server
Haga clic en Desde base de datos y, a continuación, haga clic Desde SQL Server. Bases de datos de Microsoft
Access Haga clic en Desde base de datos y, a continuación, haga clic en Desde Access. Cubo de Microsoft SQL
Server Analysis Services Haga clic en Desde base de datos y, a continuación, haga clic en De Analysis Services y PowerPivot. Para obtener más información, vea Importar datos de Analysis Services o PowerPivot.
Cubo de Microsoft
PowerPivot (hospedado en SharePoint)
Haga clic en Desde base de datos y, a continuación, haga clic en De Analysis Services y PowerPivot. Para obtener más información, vea Importar datos de Analysis Services o PowerPivot. Microsoft SQL Azure Almacenamiento de datos paralelo de Microsoft SQL Server 2008 R2 Oracle Teradata Sybase Informix IBM DB2 OLE DB/ODBC
Importar datos de Analysis Services o PowerPivot
En PowerPivot para Excel, puede utilizar una base de datos de Analysis Services como un origen de datos para un libro PowerPivot. Esa base de datos puede ser un cubo tradicional, compilado usando SQL Server Analysis Services u otro libro de PowerPivot publicado en un servidor de SharePoint.
Requisitos previos:
Los cubos de Analysis Services deben ser de la versión SQL Server 2005, SQL Server 2008 o SQL Server 2008 R2.
21
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Los libros de PowerPivot que usa como orígenes de datos se deben publicar en un sitio de SharePoint 2010. El sitio de SharePoint se debe estar ejecutando en un equipo diferente del que emplea para importar datos.
Debe tener permisos Ver en el sitio de SharePoint para importar datos de los libros de PowerPivot.
Elegir un método de importación
Puede usar cualquiera de los siguientes métodos para trabajar con Analysis Services o datos de PowerPivot en un libro de Excel.
Aplicación Método Vínculo
PowerPivot
para Excel Haga clic en De Analysis Services o PowerPivot para importar datos de un cubo de Analysis Services.
Cómo
PowerPivot
para Excel Haga clic en De Analysis Services o PowerPivot para importar los datos de un libro de PowerPivot publicado en un servidor de SharePoint.
Cómo
Excel Haga clic en De otros orígenes en el grupo Obtener datos externos para establecer una conexión con un libro de PowerPivot publicado en un servidor de SharePoint.
Cómo
-Importar datos desde un cubo
Cualquier dato contenido en una base de datos de SQL Server Analysis Services se puede importar en un libro PowerPivot. Puede extraer todo el contenido o parte de una dimensión, u obtener segmentaciones y agregados del cubo, como la suma de ventas, mes por mes, durante el año actual. Sin embargo, debería tener presente las siguientes
restricciones:
Todos los datos que importa de un cubo u otro libro de PowerPivot se reducen. Por consiguiente, si define una consulta que recupera medidas a lo largo de varias dimensiones, los datos se importarán con cada dimensión en una columna independiente.
Los datos son estáticos una vez importados. No se actualizan desde el servidor de Analysis Services a petición. Si desea actualizar un libro para captar los cambios en la base de datos de Analysis Services, debe crear una programación de la actualización de datos una vez publicado
22
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
el libro en SharePoint. Alternativamente, puede actualizar manualmente los datos en PowerPivot para Excel. Para obtener más información.
El siguiente procedimiento muestra cómo obtener un subconjunto de datos de un cubo tradicional en una instancia de Analysis Services. Este procedimiento utiliza la base de datos de ejemplo de AdventureWorksDW2008R2 para explicar cómo importar un subconjunto de un cubo. Si tiene acceso a un servidor de Analysis Services que tiene la base de datos de ejemplo AdventureWorksDW2008R2, puede seguir estos pasos para aprender a importar datos de Analysis Services.
1. En la ventana de PowerPivot, en el grupo Obtener datos externos, haga clic en Desde base de datos y seleccione De Analysis Services o PowerPivot.
Se inicia el Asistente para la importación de tablas.
2. En la página Conectarse con Microsoft SQL Server Analysis Services, en Nombre descriptivo de la conexión, escriba un nombre descriptivo para la conexión de datos.
3. En Nombre de servidor o archivo, escriba el nombre del equipo que hospeda la instancia y el nombre de instancia, por ejemplo, Contoso-srv\CONTOSO.
4. Opcionalmente, haga clic en Opciones avanzadas para abrir un cuadro de diálogo donde puede configurar las propiedades que son específicas del proveedor. Haga clic en Aceptar.
5. Haga clic en la flecha abajo a la derecha de la lista Nombre de la base de datos y seleccione una base de datos de Analysis Services en la lista.
6. Haga clic en Probar conexión para comprobar que el servidor de Analysis Services está disponible.
7. Haga clic en Siguiente.
8. En la página Especificar una consulta MDX, haga clic en Diseño para abrir un generador de consultas MDX.
23
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
En este paso, arrastre en el área de diseño de consulta grande todas las medidas, atributos de dimensión, jerarquías y miembros calculados que desea importar en el libro de PowerPivot. Si tiene una instrucción de MDX que desea usar, péguela en el cuadro de texto y haga clic en Validar para asegurarse de que la instrucción funcionará. Para obtener más información acerca de cómo generar consultas MDX, vea Diseñador de consultas MDX de Analysis Services (PowerPivot).
Haga clic en Aceptar y revise la consulta MDX que se creó con el diseñador de consultas.
9. Escriba un nombre descriptivo para el conjunto de datos. Este nombre se usará como nombre de tabla en el libro. Si no asigna un nuevo nombre, de forma predeterminada los resultados de la consulta se guardan en una nueva tabla denominada Query.
10.Haga clic en Finalizar.
11.Cuando los datos han terminado de cargarse, haga clic en Cerrar. Después de importar los datos en la ventana de PowerPivot, puede comprobar el tipo de datos seleccionando cada columna y viendo Tipo de datos en el grupo Formato en la cinta de opciones. Asegúrese de comprobar el tipo de datos de las columnas que contienen datos numéricos o financieros. PowerPivot a veces cambiará el tipo de datos a texto si encuentra valores vacíos. Puede utilizar la opción Tipo de datos para corregir el tipo de datos si los datos numéricos o financieros tienen asignado un tipo equivocado.
Importar datos desde un libro de PowerPivot
1. En la ventana de PowerPivot, en el grupo Obtener datos externos, haga clic en Desde base de datos y seleccione De Analysis Services o PowerPivot.
Se inicia el Asistente para la importación de tablas.
2. En Conectarse con Microsoft SQL Server Analysis Services, en Nombre descriptivo de la conexión, escriba un nombre descriptivo para la conexión de datos. Usar nombres descriptivos para la conexión puede ayudarle a recordar cómo se usa la conexión.
3. En Nombre de servidor o archivo:, escriba la dirección URL del archivo .xlsx publicado. Por ejemplo, http://Contoso-srv/Shared Documents/ContosoSales.xlsx.
24
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Nota:
No puede usar un libro de PowerPivot local como un origen de datos; el libro de PowerPivot se debe publicar en un sitio de SharePoint.
4. Opcionalmente, haga clic en Opciones avanzadas para abrir un cuadro de diálogo donde puede configurar las propiedades que son específicas del proveedor. Haga clic en Aceptar.
5. Haga clic en Probar conexión para comprobar que el libro de PowerPivot está disponible.
6. Haga clic en Siguiente. 7. Haga clic en Diseño.
8. Compile la consulta arrastrando medidas, atributos de dimensión o jerarquías hacia el área de diseño grande. Opcionalmente, use el panel de filtro en la esquina superior derecha para seleccionar un subconjunto de datos para la importación. Consulte los pasos de la sección anterior
para obtener un ejemplo de cómo compilar la consulta. 9. Haga clic en Aceptar.
10.Haga clic en Validar. 11.Haga clic en Finalizar.
Los datos de PowerPivot se copian en el libro y de almacenan en un formato comprimido, separado del libro original. Una vez importados los datos, se cierra la conexión con el libro. Para volver a consultar los datos originales, puede actualizar el libro. Para obtener más información,
Conectar con un libro de PowerPivot como un origen de datos externo Puede utilizar los datos de PowerPivot como un origen de datos externo en Excel sin incrustar los datos en el libro. No necesita PowerPivot para Excel para este escenario, pero debe tener la versión correcta del proveedor OLE DB de Analysis Services. Para obtener la última versión del proveedor, descargue e instale Microsoft SQL Server 2008 R2 Analysis Services OLE DB Provider en la página SQL Server 2008 Feature Pack en el sitio web de Microsoft.
1. En la pestaña Datos de Excel, en el grupo Obtener datos externos, haga clic en De otros orígenes.
25
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
3. En el cuadro Nombre del servidor, escriba la dirección del libro de PowerPivot. La dirección debe incluir el archivo .xlsx que contiene los datos (por ejemplo, http://constoso-srv/team site/shared documents/contoso-quarterly-sales.xlsx).
Nota:
Si obtiene "error de análisis XML en línea 1, columna 1", probablemente no tiene la versión correcta del proveedor OLE DB de Analysis Services. Puede instalar PowerPivot para Excel o descargar e instalar el proveedor OLE DB de Analysis Services de R2 de Microsoft SQL Server 2008 en la página SQL Server 2008 Feature Pack en el sitio web de Microsoft.
4. Haga clic en Siguiente.
5. En Seleccionar base de datos y tabla, haga clic en Finalizar.
6. En Importar datos, especifique cómo desea que aparezcan los datos (por ejemplo, elija informe de tabla dinámica).
7. Haga clic en Propiedades y a continuación, abra la ficha Definición para comprobar que la cadena de conexión especifica
Provider=MSOLAP .4. Este paso comprueba que tiene el proveedor OLE DB correcto.
8. Haga clic en Aceptar y, a continuación, haga clic en Finalizar para preparar la conexión.
Una lista de campos de tablas dinámicas aparece en el área de trabajo que contiene campos del libro PowerPivot.
Interacción de PowerPivot con cubos de Analysis Services
Cuando usa el asistente para conectarse con un origen de datos de Analysis Services, el motor VertiPaq de PowerPivot crea una consulta MDX para la base de datos Analysis Services y, a continuación, descarga los datos en el libro. Los datos no se pueden actualizar y no se actualizan automáticamente cuando los datos del cubo cambian.
Los datos que importa a un libro de PowerPivot son autónomos tras la fase de importación. En lugar de considerar un libro de PowerPivot como una superficie de exploración para un cubo existente, debería considerarlo como un área de trabajo donde puede obtener subconjuntos de datos del cubo útiles y derivar de ahí nuevos análisis que son independientes del cubo y de otros orígenes de datos.
26
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Si desea ver las sentencias MDX que genera el libro PowerPivot durante importación, puede crear un archivo de seguimiento. Para obtener información acerca de cómo crear un archivo de seguimiento.
Si está acostumbrado a trabajar con cubos de Analysis Services en Excel, debería saber que algunas características de Excel no se pueden usar con libros de PowerPivot. Al conectarse a un cubo de PowerPivot, no se admiten las características de Excel siguientes:
Cubos sin conexión
Agrupación en tablas dinámicas
Comando de obtención de detalles
Importar datos de informe.
Conéctese a una fuente de distribución de datos que un informe de
Reporting Services ponga a su disposición. Para obtener más
información.
27
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
De fuentes de datos.
Conéctese a fuentes de distribución de datos generadas desde orígenes
de datos en línea. Para obtener más información.
Importar datos de una fuente de distribución de datos
Las fuentes de distribución de datos son uno o varios flujos de datos XML que se generan a partir de un origen de datos en línea y se transmiten a un documento o aplicación de destino. En PowerPivot para Excel, puede importar una fuente de distribución de datos en el libro de PowerPivot mediante el Asistente para importación de tablas. Después de importar una fuente de distribución de datos en un libro, puede utilizarlo posteriormente en cualquier operación de actualización de datos que programe.
28
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Importar datos de una fuente de otros orígenes29
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Actualizar
Actualice uno o varios orígenes de datos usados en el libro actual.
Observe que en PowerPivot para Excel hay dos operaciones similares
aunque distintas: La actualización de datos significa la obtención de
datos actualizados de los orígenes de datos externos
-Actualizar o cambiar los datos importados
Al importar los datos de un origen de datos externo en un libro, puede
decidir mantener los datos tal cual, capturar un momento dado para
realizar un análisis más extenso o actualizar los datos del origen
siempre que lo decida.
La última opción es práctica si va a obtener los datos de las bases de
datos relacionales que contiene la información de ventas actuales, o
fuentes de distribución de datos que se actualizan varias veces al día.
Hay dos tipos de actualizaciones de datos:
-Actualización manual. Puede actualizar los datos del libro
manualmente en cualquier momento. Se pueden actualizar todos los
datos, que es el valor predeterminado, o elegir manualmente las tablas
y columnas que se van a actualizar en orígenes de datos individuales.
-Actualización automática o programada. Si ha publicado un libro
en una Galería de PowerPivot o en un sitio de Sharepoint que admita
PowerPivot, usted o el administrador de Sharepoint pueden crear una
programación para actualizar los datos del libro automáticamente.
Además de obtener los datos actualizados de un origen existente,
tendrá que actualizar los datos del libro cada vez que efectúe cambios
en el esquema del origen de datos. Por ejemplo, uno de estos cambios
puede ser agregar columnas o tablas, o cambiar las filas que se
importan.
30
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Nota:
Tenga en cuenta que la suma de datos, modificación de datos o
edición de filtros activa siempre el recalculo de cualquier columna o
fórmula que dependan de ese origen de datos. Estas operaciones
pueden llevar bastante tiempo. Para obtener más información, vea
Recalcular las fórmulas.
-Actualizar manualmente un origen de datos existente:
Si necesita actualizar metadatos de columnas u obtener datos recientes
para diseñar nuevas fórmulas, puede actualizar los datos manualmente
en cualquier momento. En los siguientes temas se dan instrucciones
para actualizar un origen de datos manualmente y editar sus
propiedades.
-Programar actualizaciones de datos mediante Sharepoint
Puede programar la actualización de datos para que se produzca a
intervalos periódicos cuando guarde un libro PowerPivot en un sitio de
SharePoint. Si se usa información de conexión en el libro o en la
programación, el servidor se conectará a los orígenes de datos y
recuperará los datos actualizados en su nombre.
-Cambiar un origen de datos
Si tiene que cambiar los datos asociados a un libro, puede usar las
herramientas del complemento de PowerPivot para modificar la
información de conexión o actualizar la definición de las tablas y
columnas que se utilizan en los datos de PowerPivot.
Los cambios que puede hacer en los orígenes de datos existentes son
los siguientes:
Conexiones
Tablas
Columnas
Editar el nombre del
servidor o de la base
de datos
Cambiar el nombre del
archivo de texto, hoja
Agregar o quitar
un filtro en los
datos
Cambiar los
criterios de filtro
Cambiar los
nombres de
columna
Agregar
nuevas
31
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
de cálculo o fuente de
distribución de datos
de origen
Cambiar la ubicación
del origen de datos
En el caso de los
orígenes de datos
relacionales, cambiar
el catálogo
predeterminado o el
catálogo inicial
Cambiar el método de
autenticación o las
credenciales utilizadas
para tener acceso a
los datos
Editar las propiedades
avanzadas del origen
de datos
Agregar o quitar
tablas
Cambiar los
nombres de tabla
Modificar las
asignaciones
entre las tablas
del origen y las
tablas del libro
Seleccionar otras
columnas del
origen
columnas
Eliminar
columnas del
libro (no
afecta al
origen de
datos)
Para realizar modificaciones, siga estos pasos en la ventana de
PowerPivot:
En la ficha Diseño, haga clic en el grupo Conexiones, haga clic
en el botón Conexiones existentes, elija una conexión y use los
editores que se proporcionan. Para obtener más información, vea
Editar las propiedades de un origen de datos existente
.
32
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Haga clic en la ficha que contiene la tabla que desea modificar y,
a continuación, en la ficha Diseño, en el grupo Propiedades,
haga clic en el botón Propiedades de tabla.
Seleccione la columna que desea modificar, para ello, haga clic en
su encabezado y, a continuación, en la ficha Inicio, use los
controles del grupo Formato para modificar las propiedades de la
columna.
Cambiar las filas que se importan
Al importar un origen de datos, puede definir los filtros para restringir
el número total de filas que se importan. Puede resultar útil al empezar
a desarrollar un conjunto de datos para su análisis y se desea trabajar
solo con un subconjunto de los datos para mejorar el rendimiento.
También se pueden crear libros diferentes para regiones diferentes: por
ejemplo, se podría usar el mismo origen de datos, pero filtrar por una
región determinada.
Si posteriormente se decide agregar más datos, no es necesario volver
a crear el modelo con otro filtro; en lugar de ello, no hay más que
editar los filtros que se aplican al origen de datos para obtener más o
menos datos.
El recalculo significa actualizar las columnas y tablas de un libro que contienen fórmulas.
Recalcular las fórmulas
Al trabajar con los datos de un libro de PowerPivot para Excel, de vez en cuando podría tener que actualizar los datos del origen, recalcular las fórmulas que ha creado en columnas calculadas o asegurarse de que los datos que se presentan en una tabla dinámica están actualizados.
En este tema se explica la diferencia entre actualizar los datos y recalcular los datos, se proporciona información general acerca de qué hacer para recalcular los datos y se describen opciones para ello.
PowerPivot usa tanto la actualización como el recálculo de datos:
Actualización de datos significa obtener datos actualizados de los orígenes de datos externos. PowerPivot no detecta automáticamente los cambios en orígenes de datos externos, pero los datos pueden actualizarse manualmente
33
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
desde el libro de PowerPivot o automáticamente si el libro se comparte en SharePoint. Para obtener más información
Recálculo significa actualizar todas las columnas, tablas, gráficos y tablas dinámicas del libro que contienen fórmulas. Puesto que el recálculo de una fórmula incurre en un mayor rendimiento, es importante comprender las dependencias asociadas a cada cálculo.
Importante:
Nunca debería guardar o publicar el libro hasta que las fórmulas que contiene se hayan actualizado.
Recálculo manual y automático
De forma predeterminada, PowerPivot recalcula automáticamente según sea necesario y optimiza el tiempo de procesamiento requerido. Aunque el recalculo puede tardar tiempo, es una tarea importante porque mientras se realiza, se comprueban las dependencias de las columnas y se le notificará si una columna ha cambiado, si los datos no son válidos o si ha aparecido un error en una fórmula que funcionaba. Sin embargo, puede decidir prescindir de la validación y únicamente actualizar los cálculos manualmente, sobre todo si trabaja con fórmulas complejas o con conjuntos de datos muy grandes, y desea controlar el tiempo de las actualizaciones.
Ambos modos, el manual y el automático, tienen ventajas; sin embargo, recomendamos encarecidamente que utilice el modo de recalculo automático. Este modo mantiene los metadatos de PowerPivot sincronizados y evita los problemas que ocasiona la eliminación de datos, los cambios en los nombres o los tipos de datos, o las dependencias perdidas.
-Usar el recálculo automático
Al usar el modo automático de recálculo, cualquier cambio realizado en el libro que provocaría la modificación del resultado de alguna fórmula desencadenará el recálculo de la columna completa que contenga una fórmula. Los siguientes cambios siempre requieren el recálculo de las fórmulas:
Los valores de un origen de datos externo se han actualizado.
La definición de la fórmula ha cambiado.
Han cambiado los nombres de las tablas o columnas a las que se hace referencia en una fórmula.
34
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Las relaciones entre las tablas se han agregado, modificado o eliminado.
Se han agregado nuevas medidas o columnas calculadas. Se han realizado cambios en otras fórmulas dentro del libro de
PowerPivot, de modo que las columnas o los cálculos que dependen de ese cálculo deberían actualizarse.
Se han insertado o eliminado filas.
Ha aplicado un filtro que requiere la ejecución de una consulta para actualizar el conjunto de datos. El filtro se aplicó a una fórmula o como parte de un tabla dinámica o gráfico dinámico.
-Usar el recálculo manual
Puede usar el recálculo manual para evitar incurrir en el costo de calcular los resultados de las fórmulas hasta que sea conveniente. El modo manual es especialmente útil en estas situaciones:
Va a diseñar una fórmula mediante una plantilla y desea cambiar los nombres de las columnas y tablas utilizadas en la fórmula antes de validarla.
Sabe que algunos datos del libro han cambiado pero trabaja con una columna que no ha cambiado, por lo que desea posponer un recálculo.
Está trabajando en un libro que tiene muchas dependencias y desea posponer el recálculo hasta estar seguro de que se han realizado todas las modificaciones necesarias.
Tenga en cuenta que, siempre que el libro esté establecido en el modo de cálculo manual, PowerPivot para Excel no realiza ninguna validación o comprobación de fórmulas, con las siguientes consecuencias:
Cualquier nueva fórmula que agregue al libro se marcará como una fórmula que contiene un error.
35
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Informes
El botón Tabla dinámica le permite insertar en el libro de Excel una o varias tablas dinámicas que están basadas en los datos de la ventana de PowerPivot.
Crear un informe de tabla dinámica o gráfico dinámico
Al trabajar en un libro de Excel que tiene PowerPivot para Excel, puede crear tablas dinámicas y gráficos dinámico en dos lugares diferentes: en la ventana de PowerPivot de la pestaña Inicio y en la ventana de Excel de la pestaña PowerPivot. Si desea usar los datos de la ventana de PowerPivot para generar una tabla dinámica o un gráfico, debe usar una de estas opciones.
El botón de tabla dinámica que está en la pestaña de inserción de la ventana de Excel también puede crear tablas dinámicas y gráficos dinámicos pero esas tablas y gráficos no pueden usar datos de PowerPivot, solo datos que estén almacenados en hojas de cálculo de un libro de Excel.
En este tema se supone que ya sabe cómo usar tablas dinámicas y gráficos dinámicos. Si no es así, consulte los siguientes temas en Microsoft Office Online para obtener una introducción:
Al crear tablas dinámicas que incluyen datos de PowerPivot, también tiene acceso a las siguientes características:
El uso de un lenguaje de fórmulas, Expresiones de análisis de datos (DAX), que proporciona características de inteligencia y otras funciones. Para obtener más información,
36
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
La capacidad de crear relaciones entre las tablas, buscar datos relacionados y filtrar mediante relaciones. Para obtener más información,
La capacidad de aplicar filtros dinámicamente en función del contexto actual, o de aplicar filtros en varias tablas relacionadas. Para obtener más información,
El uso de segmentaciones de datos mejoradas. Puede agregar
rápidamente varios informes de tablas dinámicas y gráficos dinámicos al mismo tiempo. Al usar la lista de campos de PowerPivot para agregar segmentaciones de datos, dichas segmentaciones filtrarán
automáticamente todos los objetos del informe. Para obtener más información,
Para crear un informe de tabla dinámica o gráfico dinámico
1. Haga clic en la flecha abajo del botón Tabla dinámica en uno de los siguientes lugares.
En la ventana de PowerPivot, en la pestaña Inicio, en el grupo Informes.
O bien
En la ventana de Excel, en la pestaña PowerPivot, en el grupo Ver.
2. Seleccione en la lista de opciones. Si lo desea, puede agregar informes de tabla dinámica y de gráfico dinámico vinculados al mismo tiempo.
37
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Opción Descripción
Tabla dinámica
única Crea una tabla dinámica en blanco en una nueva hoja de cálculo o en la hoja de cálculo que seleccione.
Gráfico dinámico
único Crea un gráfico dinámico en blanco en una nueva hoja de cálculo o en la hoja de cálculo que seleccione.
Gráfico y tabla
(horizontal) Crea una tabla dinámica y un gráfico dinámico en blanco en una nueva hoja de cálculo que elija y los coloca uno al lado del otro.
Los datos del gráfico y de la tabla son
independientes. Con todo, las segmentaciones de datos se aplican a los dos.
Gráfico y tabla
(vertical) Crea un gráfico dinámico y una tabla dinámica en blanco en una nueva hoja de cálculo o en la hoja de cálculo que seleccione, y los coloca con el gráfico por encima de la tabla. Las posiciones se pueden cambiar posteriormente.
Los datos del gráfico y de la tabla son
independientes. Con todo, las segmentaciones de datos se aplican a los dos.
Dos gráficos
(horizontal) Crea dos gráficos dinámicos en blanco en una nueva hoja de cálculo o en la hoja de cálculo que seleccione, y los coloca uno al lado del otro. Los gráficos son independientes. Con todo, las segmentaciones de datos se aplican a los dos. Dos gráficos
(vertical) Crea dos gráficos dinámicos en blanco en una nueva hoja de cálculo o en la hoja de cálculo que seleccione, y los coloca con un gráfico por encima del otro. Las posiciones se pueden cambiar
posteriormente.
Los gráficos son independientes. Con todo, las segmentaciones de datos se aplican a los dos. Cuatro gráficos Crea cuatro gráficos dinámicos en blanco en una
nueva hoja de cálculo o en la hoja de cálculo que seleccione.
Los gráficos son independientes. Sin embargo, las segmentaciones de datos se aplican a los cuatro. Tabla dinámica sin
información de estructura
jerárquica: plana
Crea una tabla dinámica en blanco. En lugar de organizar algunos valores de datos como
encabezados de columna y otros como
38
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
para cada campo que agrega y una fila de Totales se inserta después de cada grupo.
3. Para especificar una ubicación, elija entre:
Si desea colocarlo en una nueva hoja de cálculo a partir de la celda A1, haga clic en Nueva hoja de cálculo.
Para colocarlo en una hoja de cálculo existente, seleccione Hoja
de cálculo existente y, a continuación, especifique la primera celda del rango de celdas donde desee colocarlo.
4. Haga clic en Aceptar.
Excel agrega el informe seleccionado en la ubicación seleccionada y muestra la Lista de campos de PowerPivot, para que pueda agregar campos y personalizarlo.
Crear una tabla dinámica a partir de los datos de PowerPivot
Una vez agregados los datos a un libro de PowerPivot, las tablas dinámicas le ayudan a analizar eficazmente los datos en detalle. Puede realizar comparaciones, detectar patrones y relaciones, así como detectar tendencias.
Para crear esta tabla dinámica, tiene que haber completado las tareas anteriores de este tutorial.
Agregar una tabla dinámica al análisis
Seguirá utilizando el libro de PowerPivot que creó en la tarea anterior. Ya tiene importados los datos y creadas las relaciones. Ahora agregará una tabla dinámica.
Agregar una tabla dinámica al análisis
1. En la ventana de PowerPivot, en la cinta de opciones de Inicio de PowerPivot, haga clic en Tabla dinámica.
39
Como utilizar el nuevo Componente de PowerPivot en
Excel 2010 para crear Tablas Dinámicas
PowerPivot © Microsoft
l.muniz@sistemacontrolgestion,com
www.sistemacontrolgestion/tds
Hay también un botón de tabla dinámica en la cinta de opciones de Excel, pero las tablas dinámicas de Excel no pueden tener acceso a los datos de PowerPivot.
2. Seleccione Tabla dinámica única.
3. Seleccione Nueva hoja de cálculo.
Excel agrega una tabla dinámica vacía a la ubicación que especificó y muestra la lista de campos de PowerPivot. La lista de campos muestra dos secciones: una de campos en la parte superior para agregarlos y quitarlos, y otra en la parte inferior para reorganizarlos y cambiar su posición.
4. Seleccione la tabla dinámica vacía.
Si obtiene un mensaje de error que le indica que la lista de datos ya no es válida, haga clic con el botón secundario en la tabla y seleccione Actualizar datos.
