Download - Manual_EX-PP-PV
Análisis de datos con PowerPivot y Power View
Manual del Participante
El análisis de datos en los sistemas de información
moderno se ha visto fuertemente apoyado con el
uso de MS-Excel y en esta actividad, las tablas
dinámicas han jugado un papel relevante, sin
embargo, ¿qué podemos hacer cuando el volumen
de datos rebasa con mucho el límite de filas de MS-
Excel?, frente a estos casos nos podemos apoyar de
dos nuevas herramientas integradas en las
versiones recientes de MS-Excel.
M.N. Ing. Jorge Perdomo Rivera
09/05/2013
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 1 de 118 www.infoadmin.com.mx
CONTENIDO Tablas de Datos ................................................................................................................................... 5
Proceso para crear o eliminar una tabla ......................................................................................... 7
Convertir una tabla en un rango de datos ...................................................................................... 8
Seleccionar filas y columnas de una tabla ....................................................................................... 8
Agregar o quitar filas y columnas de tablas de Excel ...................................................................... 9
Activar o desactivar los encabezados de tabla ............................................................................. 12
Dar formato a una tabla ................................................................................................................ 12
Cambiar de nombre de una tabla ................................................................................................. 13
Crear, modificar o quitar una columna calculada de una tabla .................................................... 13
Fila de Totales en las Tablas .......................................................................................................... 14
Utilizar referencias estructuradas con las tablas de Excel ............................................................ 14
Tablas Dinámicas ............................................................................................................................... 22
Elementos básicos del diseño de tablas dinámicas ....................................................................... 22
Requisitos previos ......................................................................................................................... 22
Insertar la tabla dinámica .............................................................................................................. 24
Creación del informe de tabla dinámica ....................................................................................... 25
Agregar filtros de informe ............................................................................................................. 26
Agrupación de datos ..................................................................................................................... 28
Herramienta PowerPivot ................................................................................................................... 31
Modelos de datos .......................................................................................................................... 31
Diferencias entre libros de Excel y PowerPivot ............................................................................. 35
Usar el modelo de datos en Power View ...................................................................................... 35
Agregar una fila en blanco al final de la tabla ..................................................................... 9
Incluir una fila o columna de la hoja de cálculo en una tabla ............................................... 9
Cambiar el tamaño de una tabla ...................................................................................... 10
Insertar una fila o columna de tabla ................................................................................. 10
Eliminar filas o columnas de una tabla ............................................................................. 11
Quitar filas duplicadas de una tabla ................................................................................. 11 Seleccionar un estilo de tabla al crear una tabla ............................................................... 13 Incluir excepciones de columna calculada ........................................................................ 14
Ejemplo de la tabla del departamento de ventas .............................................................. 15
Componentes de una referencia estructurada .................................................................. 15
Nombres de tabla y especificadores de columna .............................................................. 16
Operadores de referencia ................................................................................................ 16
Especificadores de elementos especiales ......................................................................... 17
Ejemplos de uso de referencias estructuradas .................................................................. 17
Calificar referencias estructuradas de columnas calculadas .............................................. 18
Trabajar con referencias estructuradas ............................................................................ 19
Reglas de sintaxis de las referencias estructuradas ........................................................... 20
¿Qué puede hacer con un modelo de datos? .................................................................... 33
Agregar datos actuales y no relacionados a un modelo de datos ....................................... 34
Refinar y extender el modelo de datos en el complemento de PowerPivot ....................... 34 Iniciar el complemento PowerPivot de Excel 2013 ............................................................ 35
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 2 de 118 www.infoadmin.com.mx
Características de PowerPivot para Excel ..................................................................................... 36
Análisis de datos con PowerPivot ................................................................................................. 38
Agregar datos a la ventana de PowerPivot ................................................................................... 38
Componentes de los modelos de datos de PowerPivot ................................................................... 45
Campos calculados ........................................................................................................................ 45
Tipos de datos admitidos en libros PowerPivot ............................................................................ 47
Tabla de conversiones de datos implícitas .................................................................................... 50
Relaciones ..................................................................................................................................... 52
Jerarquías ...................................................................................................................................... 57
Perspectivas .................................................................................................................................. 58
Comportamiento de las tablas para informes de Power View ..................................................... 58
Comportamiento de agrupación predeterminada de las tablas de PowerPivot .......................... 61
Interfaz de usuario de PowerPivot .................................................................................................... 64
Pestaña PowerPivot de las cintas de Excel.................................................................................... 65
Cuadro de diálogo Agregar a modelo de datos ............................................................................. 69
Pestaña Inicio de las cintas de PowerPivot ................................................................................... 70
Este grupo permite copiar y pegar los datos en el libro de PowerPivot actual. ........................... 71
Este grupo le permite cambiar la forma de ver las tablas y columnas. ........................................ 74
Pestaña Diseñar de las cintas de PowerPivot ............................................................................... 75
Pestaña Avanzadas de las cintas de PowerPivot ........................................................................... 77
Pestaña Tablas vinculadas de las cintas de PowerPivot ................................................................ 79
Área de cálculo de PowerPivot ..................................................................................................... 80
Vista de diagrama de PowerPivot ................................................................................................. 81
Accesos directos del teclado ......................................................................................................... 82
Especificaciones de capacidad máxima......................................................................................... 84
Proyecto completo de análisis de datos con PowerPivot ................................................................. 85
Agregar datos a un libro PowerPivot ............................................................................................ 85
Generar una tabla dinámica de PowerPivot en Excel ........................................................ 41 Tipos de campos calculados ............................................................................................. 45 Tipo de datos de tabla ..................................................................................................... 49 Controlar valores en blanco, cadenas vacías y valores cero ............................................... 52
¿Qué es una relación? ..................................................................................................... 52
Claves y columnas ........................................................................................................... 53
Tipos de relaciones .......................................................................................................... 54
Relaciones y rendimiento ................................................................................................ 54
Requisitos para las relaciones .......................................................................................... 54
Conceptos claves para el manejo de relaciones ................................................................ 55
¿Por qué establecer las propiedades del comportamiento de la tabla? ............................. 58
Identificador de fila ......................................................................................................... 59
Propiedad Mantener filas únicas ...................................................................................... 59
Propiedad etiqueta predeterminada ................................................................................ 60
Propiedad imagen predeterminada ................................................................................. 60
Grupo Modelo de datos ................................................................................................... 65
Grupo Cálculos ................................................................................................................ 65
Grupo Alineación de segmentación de datos .................................................................... 65
Grupo Tablas ................................................................................................................... 67
Grupo Relaciones ............................................................................................................ 68
Botón Configuración ........................................................................................................ 68 Portapapeles ................................................................................................................... 71
Obtener datos externos ................................................................................................... 71
Botón Tabla dinámica ...................................................................................................... 71
Grupo Formato ............................................................................................................... 72
Grupo Ordenar y filtrar .................................................................................................... 72
Grupo Cálculos ................................................................................................................ 72
Grupo Ver ....................................................................................................................... 74
Grupo Columnas ............................................................................................................. 75
Grupo Cálculos ................................................................................................................ 76
Grupo Relaciones ............................................................................................................ 76
Propiedades de tabla ....................................................................................................... 76
Marcar como tabla de fechas ........................................................................................... 76
Editar .............................................................................................................................. 76
Mostrar la pestaña Avanzadas ......................................................................................... 77
Grupo Perspectivas ......................................................................................................... 77
Mostrar campos calculados implícitos .............................................................................. 78
Resumir por .................................................................................................................... 78
Botón Conjunto de campos predeterminados .................................................................. 79
Botón Comportamiento de la Tabla ................................................................................. 79
Mostrar el Área de cálculo ............................................................................................... 80
Mostrar u ocultar los campos calculados implícitos en el Área de cálculo .......................... 80
Cambiar el ancho de una celda del Área de cálculo ........................................................... 80
Campos calculados .......................................................................................................... 80
KPI (Key Performance Indicator) ...................................................................................... 81
Navegar por la vista de diagrama ..................................................................................... 81
Para ordenar por perspectiva .......................................................................................... 81
Para restablecer el diseño................................................................................................ 81
Para elegir qué elementos desea mostrar ........................................................................ 81
Agregar datos utilizando el Asistente para la importación de tablas .................................. 85
Agregar datos utilizando una consulta personalizada ....................................................... 87
Agregar datos usando copiar y pegar ............................................................................... 88
Para copiar y pegar desde una hoja de cálculo de Excel externa ........................................ 88
Agregar datos utilizando una tabla vinculada de Excel ...................................................... 89
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 3 de 118 www.infoadmin.com.mx
Crear relaciones entre tablas ........................................................................................................ 89
Crear una columna calculada ........................................................................................................ 92
Crear una jerarquía en una tabla .................................................................................................. 93
Crear una tabla dinámica a partir de los datos PowerPivot .......................................................... 95
Crear un gráfico dinámico a partir de los datos PowerPivot ........................................................ 99
Crear un campo calculado y un KPI ............................................................................................. 101
Creación de un KPI ...................................................................................................................... 102
Crear una perspectiva ................................................................................................................. 103
Usar las segmentaciones y los KPI para analizar los datos PowerPivot ...................................... 104
Power View, herramienta para explorar, visualizar y presentar los datos ..................................... 105
Características generales de Power View ................................................................................... 105
¿Por qué crear relaciones? ............................................................................................... 90
Revisar las relaciones existentes ...................................................................................... 90
Revisar las relaciones existentes ...................................................................................... 90
Crear nuevas relaciones entre los datos a partir de orígenes independientes .................... 90
Crear la primera relación ................................................................................................. 90
Crear más relaciones entre los datos de Access y Excel ..................................................... 91
Crear relaciones en la vista de diagrama .......................................................................... 91
Para crear una relación entre tablas en la vista de diagrama ............................................. 91
Crear una columna calculada para Beneficio total ............................................................ 92
Crear columnas calculadas para datos relacionados ......................................................... 93
Crear una jerarquía desde el menú contextual ................................................................. 94
Crear una jerarquía desde el botón del encabezado de tabla ............................................ 94
Editar una jerarquía ......................................................................................................... 94
Cambiar el nombre de una jerarquía o de un nodo secundario ......................................... 95
Eliminar una jerarquía ..................................................................................................... 95
Eliminar una jerarquía y quitar sus nodos secundarios ..................................................... 95
Agregar una tabla dinámica al análisis ............................................................................. 95
Agregar otra tabla dinámica al análisis ............................................................................. 96
Agregar segmentaciones a una tabla dinámica ................................................................. 96
Pasos para agregar segmentaciones de datos a la tabla dinámica Profit by Category ......... 97
Dar formato a las segmentaciones de datos ..................................................................... 97
Usar segmentaciones de datos para analizar los datos de la tabla dinámica ...................... 97
Ocultar columnas ............................................................................................................ 98
Pasos para ocultar tablas y/o columnas ........................................................................... 98
Agregar un gráfico dinámico al análisis ............................................................................ 99
Agregar otro gráfico dinámico al análisis .......................................................................... 99
Agregar segmentaciones de datos a gráficos dinámicos .................................................. 100
Usar segmentaciones de datos para analizar los datos de gráficos dinámicos .................. 100
Dar formato a las segmentaciones de datos ................................................................... 100
Usar segmentaciones de datos para analizar los datos de gráficos dinámicos .................. 101
Crear un campo calculado que calcule las ventas de las tiendas ...................................... 101
Crear un campo calculado que calcule las ventas del último año ..................................... 102
Crear un campo calculado que calcule el crecimiento anual ............................................ 102
Aplicar un formato a los campos calculados ................................................................... 102 Pasos para crear un KPI ................................................................................................. 102 Pasos para agregar una perspectiva ............................................................................... 103
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 4 de 118 www.infoadmin.com.mx
Crear un informe de Power View ................................................................................................ 112
Optimizar para informes de Power View .................................................................................... 113
Configurar un conjunto de campos predeterminado para informes de Power View................. 117
Configurar propiedades del comportamiento de las tablas para informes Power View ............ 118
Dos versiones de Power View ........................................................................................ 105
Basado en un modelo de datos ...................................................................................... 106
Crear gráficos y otras visualizaciones ............................................................................. 106
Filtrado y resaltado de datos ......................................................................................... 106
Segmentaciones de datos .............................................................................................. 107
Ordenamiento de datos ................................................................................................. 107
Rendimiento ................................................................................................................. 107
Compartir Power View en Excel ..................................................................................... 107
Las hojas de Power View se pueden conectar a distintos modelos de datos .................... 107
Modificar el modelo de datos interno sin abandonar la hoja de Power View ................... 107
Gráficos circulares ......................................................................................................... 108
Mapas ........................................................................................................................... 108
Indicadores clave de rendimiento (KPI) .......................................................................... 109
Jerarquías ..................................................................................................................... 109
Detalle y resumen ......................................................................................................... 109
Formatear informes con estilos, temas y cambio de tamaño del texto ............................ 110
Fondos e imágenes de fondo ......................................................................................... 110
Hipervínculos ................................................................................................................ 110
Impresión ..................................................................................................................... 111
Compatibilidad con los idiomas de derecha a izquierda .................................................. 111
Control de los enteros ................................................................................................... 111
Compatibilidad con versiones anteriores y posteriores de Power View ........................... 111
Power View y los modelos de datos ............................................................................... 111
Power View y Excel Services .......................................................................................... 112
El Generador de informes y el Diseñador de informes .................................................... 112
Establecer los campos predeterminados ........................................................................ 114
Configurar propiedades del comportamiento de las tablas para informes Power View .... 114
Pasos para establecer los campos predeterminados ....................................................... 115
Pasos para establecer el comportamiento de las tablas .................................................. 116
Crear agregados predeterminados ................................................................................. 116
Agregar descripciones ................................................................................................... 117
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 5 de 118 www.infoadmin.com.mx
TABLAS DE DATOS Para simplificar la administración y el análisis de un grupo de datos relacionados, puede convertir
un rango de datos en una tabla de Microsoft Office Excel (denominada anteriormente lista de
Excel).
Una tabla es un conjunto de filas y columnas que contienen datos relacionados que se administran
independientemente de los datos de otras filas y columnas de la hoja de cálculo.
De forma predeterminada, cada columna de la tabla tiene el filtrado habilitado en el encabezado
de fila para que pueda filtrar u ordenar la tabla rápidamente. Puede agregar una fila de totales a la
tabla que proporcione una lista desplegable de funciones de agregado para cada celda de la fila de
totales. Un controlador de tamaño situado en la esquina inferior derecha de la tabla permite
arrastrar la tabla hasta que obtenga el tamaño deseado.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 6 de 118 www.infoadmin.com.mx
Puede utilizar las siguientes características para administrar los datos de la tabla:
Ordenar y filtrar: A la fila de encabezado de una tabla se agregan automáticamente listas
desplegables de filtros. Puede ordenar las tablas en orden ascendente o descendente o por
colores, o puede crear un criterio de ordenación personalizado. Puede filtrar las tablas para que
sólo muestren los datos que satisfacen los criterios que especifique, o puede filtrar por colores.
Aplicar formato a los datos de la tabla: Puede dar formato rápidamente a los datos de la tabla
aplicando un estilo de tabla predefinido o personalizado. Puede elegir también opciones de estilos
rápidos para mostrar una tabla con o sin una fila de encabezado o de totales, para aplicar bandas
de filas o columnas con el fin de facilitar la lectura o para diferenciar la primera o última columna
de otras columnas de la tabla. Para obtener más información sobre cómo aplicar formato a los
datos de una tabla.
Insertar y eliminar filas y columnas de la tabla: Existen varios modos de agregar filas y columnas a
una tabla. Puede agregar una fila en blanco al final de la tabla, incluir filas o columnas adyacentes
a la hoja en la tabla, o insertar filas y columnas de tabla en el lugar que desee. Puede eliminar filas
y columnas cuando sea necesario. También puede quitar rápidamente filas que contengan datos
duplicados de una tabla.
Usar una columna calculada: Para usar una fórmula que se adapte a cada fila de una tabla, puede
crear una columna calculada. La columna se amplía automáticamente para incluir filas adicionales
de modo que la fórmula se extienda inmediatamente a dichas filas.
Mostrar y calcular totales de datos de una tabla: Puede hallar el total rápidamente de los datos
de una tabla mostrando una fila de totales al final de la tabla y utilizando las funciones incluidas en
las listas desplegables para cada una de las celdas de la fila de totales.
Usar referencias estructuradas: En lugar de usar referencias de celdas, como A1 y R1C1, puede
utilizar referencias estructuradas que remitan a nombres de tabla en una fórmula.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 7 de 118 www.infoadmin.com.mx
Garantizar la integridad de los datos: En las tablas que no están vinculadas a listas de SharePoint,
puede utilizar las características de validación de datos integradas de Excel. Por ejemplo, puede
elegir admitir únicamente números o fechas en una columna de una tabla.
Exportar a una lista de SharePoint: Puede exportar una tabla a una lista de SharePoint para que
otras personas puedan ver, modificar y actualizar los datos de la tabla.
Proceso para crear o eliminar una tabla En una hoja de cálculo, seleccione el rango de datos o celdas vacías que desee convertir en una
tabla.
Hay dos formas de comenzar el diseño de una tabla, estos son:
1. En la ficha Insertar, en el grupo Tablas, haga clic en Tabla.
2. En la ficha Inicio, en el grupo Estilos, haga clic en Dar formato como Tabla y seleccione el estilo de su preferencia.
3. Si el rango seleccionado incluye datos que desea mostrar como encabezados de tabla,
active la casilla de verificación La tabla tiene encabezados.
Los encabezados de tabla muestran nombres predeterminados que se pueden cambiar si
no se activa la casilla La tabla tiene encabezados mencionada anteriormente.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 8 de 118 www.infoadmin.com.mx
Una vez creada la tabla, se mostrarán las Herramientas de tabla junto con la ficha Diseño. Las
herramientas que contiene la ficha Diseño se pueden utilizar para personalizar o modificar la tabla.
Convertir una tabla en un rango de datos Haga clic en cualquier punto de la tabla.
En la ficha Diseño, en el grupo Herramientas, haga clic en Convertir en rango.
Seleccionar filas y columnas de una tabla Se puede seleccionar celdas y rangos en una tabla del mismo modo que se seleccionan en una hoja
de cálculo. Sin embargo, la selección de filas y columnas de tabla es diferente al modo en que se
realiza en las hojas de cálculo.
Para seleccionar Haga esto
Una columna de
tabla con o sin
encabezados
Haga clic en el borde superior del encabezado de la columna o en la
columna en la tabla. Aparecerá la flecha de selección siguiente para indicar
que al hacer clic se seleccionará la columna.
Al hacer clic una vez en el borde superior, se seleccionan los datos de
columna de tabla; al hacer clic dos veces se selecciona toda la columna de
la tabla.
También puede hacer clic en cualquier punto de la columna de tabla y, a
continuación, presionar CTRL+BARRA ESPACIADORA. O bien, puede hacer
clic en la primera celda de la columna de tabla y, a continuación, presionar
CTRL+FLECHA ABAJO.
Si presiona CTRL+BARRA ESPACIADORA una vez, se seleccionan los datos
de columna de tabla; si presiona CTRL+BARRA ESPACIADORA dos veces, se
selecciona toda la columna de tabla.
Una columna de
tabla con
encabezados de
hoja de cálculo
Haga clic en el encabezado de columna de hoja de cálculo que muestra el
encabezado de tabla de la columna de tabla que desea seleccionar.
También puede hacer clic en cualquier punto de la columna de tabla y, a
continuación, presionar CTRL+BARRA ESPACIADORA. O bien, puede hacer
clic en la primera celda de la columna de tabla y, a continuación, presionar
CTRL+FLECHA ABAJO.
Una fila de tabla
Haga clic en el borde izquierdo de la fila de tabla. Aparecerá la flecha de
selección siguiente para indicar que al hacer clic se seleccionará la fila.
Puede hacer clic en la primera celda de la fila de tabla y, a continuación,
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 9 de 118 www.infoadmin.com.mx
presionar CTRL+FLECHA DERECHA.
Todas las filas y
columnas de la
tabla
Haga clic en la esquina superior izquierda de la tabla. Aparecerá la flecha
de selección siguiente para indicar que al hacer clic se seleccionará toda la
tabla.
Al hacer clic una vez en la esquina superior izquierda de la tabla, se
seleccionan los datos de la tabla; al hacer clic dos veces, se selecciona toda
la tabla.
También puede hacer clic en cualquier punto de la tabla y, a continuación,
presionar CTRL+E. O bien, puede hacer clic en la celda superior izquierda
de la tabla y, a continuación, presionar CTRL+MAYÚS+FIN.
Al presionar CTRL+E una vez, se seleccionan los datos de la tabla; si se
presiona CTRL+E dos veces, se selecciona toda la tabla.
Agregar o quitar filas y columnas de tablas de Excel Después de crear una tabla de Microsoft Office Excel en la hoja de cálculo, agregar columnas y filas
es muy fácil. Puede agregar una fila en blanco al final de la tabla, incluir filas o columnas de la hoja
de cálculo adyacentes a la tabla, o insertar filas y columnas de tabla en el lugar que desee.
Agregar una fila en blanco al final de la tabla
Seleccione la última celda de la última fila de la tabla y presione TAB.
Al presionar la tecla TAB en la última celda de la última fila también se agrega una fila en blanco al
final de la tabla. Si en la tabla aparece una fila de totales, al presionar la tecla TAB en la última
celda de esa fila no se agrega una nueva fila.
Incluir una fila o columna de la hoja de cálculo en una tabla
Siga uno de los procedimientos siguientes:
Para incluir una fila de hoja de cálculo en la tabla, escriba un valor o texto en una celda que esté situada justo debajo de la tabla.
Para incluir una columna de hoja de cálculo en la tabla, escriba un valor o texto en una celda que sea adyacente a la derecha de la tabla.
Para incluir filas o columnas de la hoja de cálculo usando el mouse (ratón), arrastre el controlador de tamaño de la esquina inferior derecha de la tabla hacia abajo para seleccionar filas y hacia la derecha para seleccionar columnas.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 10 de 118 www.infoadmin.com.mx
Cambiar el tamaño de una tabla
Haga clic en cualquier punto de la tabla.
En la ficha Diseño, en el grupo Propiedades, haga clic en Ajustar el tamaño de la tabla.
En el cuadro Seleccionar el nuevo rango de datos para la tabla, escriba el rango que desea utilizar
en la tabla.
También puede hacer clic en el botón Contraer diálogo a la derecha del cuadro Seleccionar el
nuevo rango de datos para la tabla y, a continuación, seleccionar el rango que desea usar en la
tabla de la hoja de cálculo. Cuando haya terminado, vuelva a hacer clic en el botón Contraer
diálogo para mostrar todo el cuadro de diálogo.
Para cambiar el tamaño de una tabla con el mouse, arrastre el controlador triangular de tamaño
de la esquina inferior derecha de la tabla hacia arriba, hacia abajo, a la derecha o a la izquierda
para seleccionar el rango que desea usar en la tabla.
Insertar una fila o columna de tabla
Siga uno de los procedimientos siguientes:
Para insertar una o varias filas de tabla, seleccione las filas por encima de las cuales desea insertar
las filas en blanco.
Si selecciona la última fila, también puede insertar una fila por encima o por debajo de la fila
seleccionada.
Para insertar una o varias columnas de tabla, seleccione las columnas a la izquierda de las cuales
desea insertar una o varias columnas en blanco.
Si selecciona la última columna, también puede insertar una columna a la izquierda o a la derecha
de la columna seleccionada.
En la ficha Inicio, en el grupo Celdas, haga clic en la flecha que aparece junto a Insertar.
Siga uno de los procedimientos siguientes:
Para insertar filas de tabla, haga clic en Insertar filas de tabla encima.
Para insertar una fila de tabla debajo de la última fila, haga clic en Insertar fila de tabla debajo.
Para insertar columnas de tabla, haga clic en Insertar columnas de tabla a la izquierda.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 11 de 118 www.infoadmin.com.mx
Para insertar una columna de tabla a la derecha de la última columna, haga clic en Insertar columna de tabla a la derecha.
También puede hacer clic con el botón secundario del mouse en una o más filas o columnas y
seleccionar Insertar en el menú contextual y, después, la acción que desee llevar a cabo en la lista
de opciones. O bien, puede hacer clic con el botón secundario del mouse en una o más celdas de
una fila o columna de tabla, seleccionar Insertar y, a continuación, hacer clic en Filas de la tabla o
en Columnas de la tabla.
Eliminar filas o columnas de una tabla
Seleccione las filas o columnas de tabla que desea eliminar.
También puede seleccionar simplemente una o varias celdas en las filas o en las columnas de tabla
que desee eliminar.
En la ficha Inicio, en el grupo Celdas, haga clic en la flecha que aparece junto a Eliminar y, a
continuación, haga clic en Eliminar filas de tabla o en Eliminar columnas de tabla.
También puede hacer clic con el botón secundario del mouse en una o varias filas o columnas,
elegir Eliminar en el menú contextual y, a continuación, hacer clic en Columnas de la tabla o en
Filas de la tabla. O bien, puede hacer clic con el botón secundario del mouse en una o varias celdas
de una fila o columna de tabla, elegir Eliminar y, por último, hacer clic en Filas de la tabla o en
Columnas de la tabla.
Quitar filas duplicadas de una tabla
Haga clic en cualquier punto de la tabla.
En la ficha Diseño, en el grupo Herramientas, haga clic en Quitar duplicados.
En el cuadro de diálogo Quitar duplicados, bajo Columnas, seleccione las columnas que contienen
los duplicados que desea quitar.
También puede hacer clic en Anular selección y, a continuación, seleccionar las columnas que
desee; o hacer clic en Seleccionar todo para seleccionar todas las columnas. Los duplicados que
quite se eliminarán de la hoja de cálculo.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 12 de 118 www.infoadmin.com.mx
Activar o desactivar los encabezados de tabla Cuando se crea una tabla, los encabezados de la misma se agregan automáticamente y se
muestran de forma predeterminada. Los encabezados muestran los nombres predeterminados
que se pueden cambiar en la hoja de cálculo, o bien, si especificó que la tabla contiene
encabezado, muestran los datos de encabezado presentes en la hoja de cálculo. Cuando se
muestran los encabezados de la tabla, éstos permanecen visibles con los datos de las columnas de
la tabla y reemplazan a los encabezados de la hoja de cálculo al desplazarse por una tabla larga.
Si no desea ver los encabezados de la tabla, puede desactivarlos.
Haga clic en cualquier lugar de la tabla.
En el grupo Opciones de estilo de la tabla de la ficha Diseño, desactive o active la casilla de
verificación Fila de encabezado para ocultar o mostrar los encabezados de tabla.
Al desactivar los encabezados de tabla, la función Autofiltro del encabezado de tabla y los filtros
aplicados se quitan de la tabla.
Si se agrega una columna nueva cuando no se muestran los encabezados de tabla, el nombre del
encabezado de tabla nuevo no se puede determinar por una serie de relleno basada en el valor del
encabezado de tabla que se encuentra directamente a la izquierda de la nueva columna. Esto sólo
funciona cuando están visibles los encabezados de tabla. En su lugar, se agrega un encabezado de
tabla predeterminado que se puede cambiar cuando se muestren los encabezados de tabla.
Aunque es posible hacer referencia a encabezados de tabla que están desactivados en fórmulas,
no podrá hacerlo seleccionándolos. Las referencias de una tabla a un encabezado de tabla oculto
devuelven valores cero (0). Sin embargo, los encabezados no cambian y, cuando se muestra de
nuevo el encabezado de tabla oculto, se devuelven los valores de encabezado de tabla. El resto de
referencias (como las referencias de estilo A1 o FC) realizadas en la hoja de cálculo al encabezado
de tabla se ajustan cuando el encabezado de tabla está desactivado y puede producir que las
fórmulas devuelvan resultados inesperados.
Dar formato a una tabla Microsoft Office Excel proporciona un gran número de estilos de tabla (o estilos rápidos)
predefinidos que puede utilizar para dar formato rápidamente a una tabla. Si los estilos de la tabla
predefinida no satisfacen sus necesidades, puede crear y aplicar un estilo de tabla personalizado.
Aunque sólo se pueden eliminar los estilos de tabla personalizados, puede quitar cualquier estilo
de tabla para que ya no se aplique a los datos.
Puede realizar ajustes adicionales en el formato de tabla seleccionando opciones de estilos rápidos
para los elementos de la tabla, como las filas de encabezado y de totales, la primera y la última
columna, y las filas y columnas con bandas.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 13 de 118 www.infoadmin.com.mx
Seleccionar un estilo de tabla al crear una tabla
En la hoja de cálculo, seleccione el rango de celdas al que desea aplicar rápidamente el formato de
tabla.
En la ficha Inicio, en el grupo Estilos, haga clic en Formatear como tabla.
En Claro, Medio u Oscuro, haga clic en el estilo de tabla que desea utilizar.
Cambiar de nombre de una tabla Cuando crea tablas de Excel, Microsoft Office Excel asigna un nombre predeterminado a cada
tabla utilizando la siguiente convención de nombres: Tabla1, Tabla2, etcétera. No obstante, puede
cambiar el nombre de cada tabla para que el significado quede más claro para usted, el ajuste del
nombre de la tabla se puede hacer en la ficha Diseño, en el grupo Propiedades.
Crear, modificar o quitar una columna calculada de una tabla En una tabla de Microsoft Office Excel, se puede crear rápidamente una columna calculada. Este
tipo de columnas utiliza una sola fórmula ajustada a cada fila. La columna se amplía
automáticamente para incluir filas adicionales para que la fórmula se extienda inmediatamente a
dichas filas. Sólo es necesario escribir una vez la fórmula. No hace falta utilizar los comandos
Rellenar o Copiar.
Se puede escribir fórmulas adicionales en una columna calculada como excepciones pero, si es
necesario, Excel notificará cualquier incoherencia para darle la oportunidad de resolverlas.
También puede actualizar la fórmula de una columna calculada editando esta última.
De manera predeterminada, la fórmula que escribas en cualquier celda de una columna vacía se
rellena automáticamente en todas las celdas de la columna, tanto por encima como por debajo de
la celda activa.
Al copiar o rellenar una fórmula en todas las celdas de una columna de tabla en blanco también se
crea una columna calculada.
Si escribe una fórmula en una columna debajo de la tabla, se creará una columna calculada, pero
las filas que se encuentran fuera de la tabla no se podrán utilizar en una referencia de tabla.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 14 de 118 www.infoadmin.com.mx
Si escribe o mueve una fórmula a una columna de tabla que ya contiene datos, no se creará
automáticamente una columna calculada. Sin embargo, se mostrará el botón Opciones de
Autocorrección para ofrecerle la posibilidad de sobrescribir los datos y permitir la creación de la
columna calculada. Si copia una fórmula en una columna de tabla que ya contiene datos, esta
opción no estará disponible.
Puede deshacer rápidamente una columna calculada. Si utilizó el comando Rellenar o
CTRL+ENTRAR para rellenar una columna completa con la misma fórmula, haga clic en el botón
Deshacer de la barra de herramientas de acceso rápido. Si escribió o copió una fórmula en una
celda de una columna en blanco, haga clic dos veces en el botón Deshacer.
Incluir excepciones de columna calculada
Una columna calculada puede incluir fórmulas que son diferentes de la fórmula de columna, lo
que crea una excepción que aparecerá claramente indicada en la tabla. De este modo, se pueden
detectar y resolver fácilmente incoherencias involuntarias.
Las excepciones de columna calculada se crean al realizar las acciones siguientes:
Escribir datos que no son fórmulas en una celda de columna calculada.
Escribir una fórmula en una celda de columna calculada y, a continuación, hacer clic en el botón Deshacer de la barra de herramientas de acceso rápido.
Escribir una nueva fórmula en una columna calculada que ya contiene una o varias excepciones.
Copiar datos en la columna calculada que no coinciden con la fórmula de columna calculada. Si los datos copiados contienen una fórmula, ésta sobrescribirá los datos de la columna
calculada.
Eliminar una fórmula de una o varias celdas de la columna calculada. Esta excepción no se marca.
Mover o eliminar una celda de otra área de hoja de cálculo a la que hace referencia una de las filas de una columna calculada.
Fila de Totales en las Tablas Los datos de una tabla de Microsoft Office Excel se pueden sumar rápidamente. Para ello, muestre
una fila de totales al final de la tabla y, a continuación, utilice las funciones que se incluyen en las
listas desplegables para cada una de las celdas de la fila de totales.
Utilizar referencias estructuradas con las tablas de Excel Las referencias estructuradas hacen que el trabajo con los datos de las tablas sea más fácil y más
intuitivo cuando se utilizan fórmulas que hacen referencia a una tabla, ya sea a partes de una tabla
o a toda la tabla. Son especialmente útiles porque los rangos de datos de la tabla cambian a
menudo y las referencias de celda de referencias estructuradas se ajustan automáticamente. De
este modo, se reduce de forma considerable la necesidad de volver a escribir fórmulas cuando se
agregan o eliminan filas y columnas de una tabla o se actualizan los datos externos.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 15 de 118 www.infoadmin.com.mx
Ejemplo de la tabla del departamento de ventas
A continuación, se muestra un ejemplo, al que se hace referencia a lo largo de todo este artículo,
de una tabla basada en las ventas de un departamento formado por seis empleados, que incluye
los importes y comisiones de ventas más recientes.
1. La tabla completa (A1:E8) 2. Los datos de la tabla (A2:E8) 3. Una columna y un encabezado de columna (D1:D8) 4. Una columna calculada (E1:E8) 5. La fila Totales (A8:E8)
Componentes de una referencia estructurada
Para trabajar con tablas y referencias estructuradas de forma eficaz, es necesario comprender
cómo se crea la sintaxis de las referencias estructuradas al crear las fórmulas. Los componentes de
una referencia estructurada se muestran en el ejemplo siguiente de una fórmula que suma los
importes y las comisiones totales de las ventas.
1. Un nombre de tabla es un nombre significativo que hace alusión a los datos reales de la tabla (excluidas la fila de encabezados y la fila de totales, si las hay).
2. Un especificador de columna se obtiene a partir del encabezado de columna, va encerrado entre corchetes y hace referencia a los datos de la columna (excluidos el encabezado de columna y el total, si los hay).
3. Un especificador de elemento especial es una forma de hacer referencia a partes específicas de la tabla, como la fila Totales.
4. El especificador de tabla es la parte externa de la referencia estructurada que va entre corchetes y a continuación del nombre de la tabla.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 16 de 118 www.infoadmin.com.mx
5. Una referencia estructurada es toda la cadena que comienza con el nombre de la tabla y termina con el especificador de la columna.
Nombres de tabla y especificadores de columna
Cada vez que se inserta una tabla, Microsoft Office Excel crea un nombre de tabla predeterminado
(Tabla1, Tabla2, etc.) en el nivel o ámbito global del libro. Puede cambiar fácilmente este nombre
con el fin de darle un mayor significado. Por ejemplo, para cambiar Tabla1 a DepVentas, puede
utilizar el cuadro de diálogo Editar nombre (en la ficha Diseño, en el grupo Propiedades, edite el
nombre de la tabla en el cuadro Nombre de la tabla).
El nombre de una tabla hace referencia a todo el rango de datos de la tabla, exceptuando las filas
de encabezado y de totales. En el ejemplo de la tabla del departamento de ventas, el nombre de la
tabla, DepVentas, hace referencia al rango de celdas A2:E7.
De forma similar a los nombres de tablas, los especificadores de columna representan referencias
a los datos de toda la columna, a excepción del encabezado de columna y el total. En el ejemplo de
la tabla del departamento de ventas, el especificador de columna [Región] hace referencia al
rango de celdas B2:B7, y el especificador de columna [PctCom] hace referencia al rango de celdas
D2:D7.
Operadores de referencia
Para una mayor flexibilidad cuando especifique rangos de celdas, puede utilizar los operadores de
referencia siguientes para combinar especificadores de columna.
Esta referencia estructurada:
Hace
referencia a: Mediante:
Que, en el
ejemplo,
es el rango
de celdas:
=DepVentas[[Vendedor]:[Región]]
Todas las
celdas de dos
o más
columnas
adyacentes
dos puntos (:)
u operador
de rango A2:B7
=DepVentas[Importe] , DepVentas[CantCom]
Una
combinación
de dos o más
columnas
coma ( , ) u
operador de
unión
C2:C7,
E2:E7
=DepVentas[[Vendedor]:[Importe]]
DepVentas[[Región]:[PctCom]]
La
intersección
de dos o más
columnas
(espacio) u
operador de
intersección B2:C7
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 17 de 118 www.infoadmin.com.mx
Especificadores de elementos especiales
Para una mayor comodidad, también puede utilizar elementos especiales para hacer referencia a
diversas partes de una tabla, como la fila Totales, con el fin de que sea más fácil incluir referencias
a estas partes de la tabla en las fórmulas. A continuación, se muestran los especificadores de
elementos especiales que puede utilizar en una referencia estructurada:
Este especificador de
elemento especial: Hace referencia a:
Que, en el
ejemplo, es
el rango de
celdas:
=DepVentas[#Todo] Toda la tabla, incluidos los encabezados de
columna, datos y totales (si los hay).
A1:E8
=DepVentas[#Datos] Sólo los datos. A2:E7
=DepVentas[#Encabezados] Sólo la fila de encabezado. A1:E1
=DepVentas[#Totales] Sólo la fila del total. Si no hay ninguna, devuelve
un valor nulo.
A8:E8
=DepVentas[#Esta fila]
Sólo la parte de las columnas de la fila actual.
#Esta fila no se puede combinar con ningún
especificador de elemento especial. Utilícela para
forzar una intersección implícita de la referencia o
para invalidar ese comportamiento y hacer
referencia a valores individuales de una columna.
A5:E5 (si la
fila actual es
la fila 5)
Ejemplos de uso de referencias estructuradas
Los elementos especiales se pueden utilizar y combinar con nombres de tablas y referencias de
columnas de muchas formas, como se muestra a continuación:
Esta referencia estructurada: Hace referencia a:
Que, en el
Ejemplo, es el
rango de celdas:
=DepVentas[[#Todo],[Importe]] Todas las celdas de la columna
Importe. C1:C8
=DepVentas[[#Encabezados] ,
[PctCom]]
Encabezado de la columna PctCom. C1
=DepVentas[[#Totales] , [Región]] El total de la columna Región. Si no
hay ninguna fila Totales, devuelve un B8
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 18 de 118 www.infoadmin.com.mx
valor nulo.
=DepVentas[[#Todo] ,
[Importe]:[PctCom]]
Todas las celdas de Importe y
PctCom. C1:D8
=DepVentas[[#Datos] ,
[PctCom]:[ImptCom]]
Sólo los datos de las columnas
PctCom e ImptCom. D2:E7
=DepVentas[[#Encabezados] ,
[Región]:[ImptCom]]
Sólo los encabezados de las
columnas entre Región y PctCom e
ImptCom.
B1:E1
=DepVentas[[#Totales] ,
[Importe]:[ImptCom]]
Totales de las columnas Importe a
ImptCom. Si no hay ninguna fila
Totales, devuelve un valor nulo.
C8:E8
=DepVentas[[#Encabezados] ,
[#Datos][PctCom]]
Sólo el encabezado y los datos de
PctCom. D1:D7
=DepVentas[[#EstaFila], [ImptCom]]
La celda ubicada en la intersección
de la fila actual y la columna
ImptCom.
E5 (si la fila actual
es la fila 5)
Calificar referencias estructuradas de columnas calculadas
Cuando crea una columna calculada, normalmente utiliza una referencia estructurada para crear
la fórmula. Esta referencia estructurada puede tener un nombre no completo o completo. Por
ejemplo, para crear la columna calculada denominada CantCom, que calcula el importe de las
comisiones en dólares, puede utilizar las siguientes fórmulas:
Tipo de
referencia
estructurada Ejemplo Comentario
No calificada
=[Importe]*[PctCom] Multiplica los valores
correspondientes en la fila
actual.
Nombre
completo
=DepVentas[Importe]*DepVentas[PctCom] Multiplica los valores
correspondientes de ambas
columnas para cada fila.
La regla general es la siguiente: si utiliza referencias estructuradas en una tabla, como cuando crea
una columna calculada, puede utilizar una referencia estructurada no calificada, pero si utiliza esta
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 19 de 118 www.infoadmin.com.mx
referencia fuera de la tabla, necesitará utilizar una referencia estructurada con un nombre
completo válido.
Trabajar con referencias estructuradas
Tenga en cuenta lo siguiente cuando trabaje con referencias estructuradas.
Usar Fórmula Autocompletar: Usar Fórmula Autocompletar para escribir referencias
estructuradas es muy útil y, además, garantiza que se emplea la sintaxis correcta.
Decidir si se generan referencias estructuradas para tablas en semiselección: De forma
predeterminada, cuando crea una fórmula si hace clic en un rango de celdas de una tabla se
seleccionan algunas celdas y automáticamente se escribe una referencia estructurada, en vez del
rango de celdas en la fórmula. De este modo, es mucho más fácil escribir una referencia
estructurada. Puede habilitar o deshabilitar este comportamiento activando o desactivando la
casilla de verificación Usar nombres de tabla en las fórmulas en la sección Trabajando con
fórmulas de la categoría Fórmulas del cuadro de diálogo Opciones de Excel.
Convertir un rango en una tabla y viceversa: Cuando convierte una tabla en un rango, todas las
referencias de celda se convierten a las referencias equivalentes de estilo A1. Cuando convierte un
rango en una tabla, Excel no convierte automáticamente ninguna referencia de celda de este
rango en sus nombres de tabla y referencias de columna equivalentes.
Desactivar los encabezados de columna: Si deshabilita los encabezados de columna de una tabla,
esto no afecta a las referencias estructuradas que utilizan estos encabezados y puede seguir
utilizándolas en las fórmulas.
Agregar o eliminar columnas y filas de la tabla: Como los rangos de datos de la tabla cambian con
frecuencia, las referencias de celda de las referencias estructuradas se ajustan automáticamente.
Por ejemplo, si utiliza un nombre de tabla en una fórmula para contar todas las celdas de datos
que contiene la tabla del departamento de ventas, como =CONTARA(DepVentas) en el ejemplo de
la tabla del departamento de ventas, el valor devuelto será 30 porque el rango de datos es A2:E7.
Si, a continuación, agrega una fila de datos, la referencia de celda se ajusta automáticamente a
A2:E8 y el nuevo valor devuelto es 35.
Cambiar el nombre de una tabla o columna: Si cambia el nombre de una columna o tabla, Excel
cambia automáticamente el uso de esa tabla o encabezado de columna en todas las referencias
estructuradas que se utilizan en el libro.
Mover, copiar y rellenar referencias estructuradas:
Cuando se copia o mueve una fórmula se conservan todas las referencias estructuradas que utilice
esa fórmula.
Cuando rellena una fórmula, las referencias estructuradas que tengan nombres completos pueden
ajustar los especificadores de columna como una serie, de la forma mostrada en la tabla siguiente.
Si la dirección de
relleno es:
Y mientras rellena
la tabla, presiona: Entonces:
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 20 de 118 www.infoadmin.com.mx
Arriba o abajo Nada No se ajusta ningún especificador de columna.
Arriba o abajo CTRL Los especificadores de columna se ajustan como una
serie.
Derecha o
izquierda
Nada Los especificadores de columna se ajustan como una
serie.
Derecha o
izquierda
CTRL No se ajusta ningún especificador de columna.
Arriba, abajo,
derecha o
izquierda
MAYÚS Se mueven los valores actuales de las celdas, en vez
de sobrescribirlos, y se insertan especificadores de
columna.
Reglas de sintaxis de las referencias estructuradas
A continuación, se muestra una lista de las reglas de sintaxis que necesita conocer para crear y
editar referencias estructuradas, los nombres de las tablas siguen las mismas reglas que las de los
nombres definidos.
Uso de corchetes en los especificadores
Todos los especificadores de tablas, columnas y elementos especiales deben ir incluidos entre
corchetes ([ ]). Un especificador que contenga otros especificadores requiere corchetes externos
para incluir los corchetes internos de los otros especificadores.
Ejemplo =DepVentas[ [Vendedor]:[Región] ]
Los encabezados de columna son cadenas de texto
Todos los encabezados de columna son cadenas de texto, pero no es necesario que vayan entre
comillas cuando se utilizan en una referencia estructurada. Si un encabezado de columna contiene
números o fechas, como 2004 o 1/1/2004, se siguen considerando como cadenas de texto. Debido
a que los encabezados de columna son cadenas de texto, no se pueden utilizar expresiones entre
corchetes.
Ejemplo =ResumenDepVentasAño[[2004]:[2002]]
Caracteres especiales en encabezados de columna de tablas
Si un encabezado de columna contiene uno de los siguientes caracteres especiales, se debe incluir
todo el encabezado entre corchetes. De hecho, esto significa que los corchetes dobles son
necesarios en un especificador de columna con los caracteres especiales siguientes: espacio,
tabulación, avance de línea, retorno de carro, coma (,), dos puntos (:), punto (.), corchete de
apertura ([), corchete de cierre (]), símbolo de gato (#), comillas simples ('), comillas dobles ("),
llave izquierda ({), llave derecha (}), símbolo de dólar ($), acento circunflejo (^), "y" comercial (&),
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 21 de 118 www.infoadmin.com.mx
asterisco (*), signo más (+), signo igual (=), signo menos (-), signo mayor que (>), signo menor que
(<) y signo de división (/).
Ejemplo =ResumenDepVentasAño[[Importe$Total]]
La única excepción a esta regla es cuando sólo se utiliza el carácter especial de espacio.
Ejemplo =DepVentas[Importe total]
Caracteres especiales en encabezados de columna que requieren la utilización del carácter de
escape
Los caracteres siguientes tienen un significado especial y requieren el uso de comillas simples (')
como un carácter de escape: corchete de apertura ([), corchete de cierre (]), símbolo de número ó
gato (#) y comillas simples (').
Ejemplo =ResumenDepVentasAño['#c]
Utilizar el carácter de espacio para mejorar la legibilidad en una referencia estructurada
Los caracteres de espacio se pueden utilizar para mejorar la legibilidad de la forma siguiente:
Un carácter de espacio después de un corchete de apertura ([) y antes de un corchete de cierre (])
Ejemplo =DepVentas[ [Vendedor]:[Región] ]
Un carácter de espacio después de la coma.
Ejemplo =DepVentas[[#Encabezados], [#Datos], [CantCom]]
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 22 de 118 www.infoadmin.com.mx
TABLAS DINÁMICAS
Elementos básicos del diseño de tablas dinámicas Cuando tengamos una hoja de cálculo con muchos datos y necesitamos saber lo que todos esos
números significan, sin tener que emprender el diseño de grandes fórmulas, los informes de tabla
dinámica pueden ayudar a analizar datos numéricos y responder a preguntas al respecto.
En cuestión de segundos puede ver quién ha vendido más, y dónde. Vea qué trimestres han sido
los más rentables, y qué producto ha sido el más vendido. Haga preguntas y vea las respuestas.
Con los informes de tabla dinámica, puede ver la misma información de maneras distintas con tan
solo unos clics. Los datos se ponen en su sitio, contestan a sus preguntas y le dicen lo que
significan los datos.
Imagine una hoja de cálculo de Excel con datos de ventas con cientos o miles de filas de datos. El
diseño de la hoja de cálculo recoge todos los datos sobre los comerciales de dos países y cuánto
han vendido en días concretos. Pero todo eso es demasiado... (enumerado en fila tras fila y
dividido en múltiples columnas). ¿Cómo puede obtener información sobre la hoja de cálculo?
¿Cómo puede lograr entender todos estos datos?
¿Quién es el que más ha vendido? ¿Quién ha vendido más por trimestre o por año? ¿Qué país ha
vendido más? Podrá responder a todas esas preguntas con los informes de tabla dinámica (es
como encontrar una aguja en un pajar). Un informe de tabla dinámica convierte todos los datos en
informes breves y concisos que le dicen exactamente lo que necesita saber.
Requisitos previos Antes de empezar a trabajar con un informe de tabla dinámica, eche un vistazo a su hoja de
cálculo Excel para comprobar que está bien preparada para el informe.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 23 de 118 www.infoadmin.com.mx
Cuando crea un informe de tabla dinámica, cada columna de sus datos de origen se convierte en
un campo que puede utilizar en el informe. Los campos resumen múltiples filas de información de
los datos de origen.
Los nombres de los campos para el informe proceden de los títulos de las columnas de sus datos
de origen. Compruebe que tiene nombres para cada columna de la primera fila de la hoja de
cálculo de los datos de origen.
En la imagen anterior, los títulos de columnas País, Comercial, Cantidad del pedido, Fecha del
pedido e Id.del pedido se convertirán en nombres de campos. Cuando crea un informe, sabrá, por
ejemplo, que el campo Comercial representa los datos del Comercial de la hoja de cálculo.
Las filas restantes que aparecen debajo de los encabezados deberían contener elementos
similares de la misma columna. Por ejemplo, el texto debería estar en una columna; los números,
en otra; y las fechas en otra. Dicho de otro modo, una columna que contiene números no debería
contener texto, etc.
Para terminar, no debería haber columnas vacías en los datos que está utilizando para el informe
de la tabla dinámica. También recomendamos que no haya filas vacías; por ejemplo, las filas en
blanco que se utilizan para separar un bloque de datos de otro deberían eliminarse.
Algo muy recomendable también es que convierta el rango donde se encuentran los datos en una
tabla de datos de Excel.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 24 de 118 www.infoadmin.com.mx
Insertar la tabla dinámica Cuando los datos están listos, coloque el cursor en cualquier lugar. Eso incluirá todos los datos de
la hoja de cálculo en el informe. O simplemente seleccione los datos que quiere utilizar en el
informe. A continuación, en la ficha Insertar, en el grupo Tablas, haga clic en Tabla dinámica y, a
continuación, vuelva a hacer clic en Tabla dinámica. Aparece el cuadro de diálogo Crear tabla
dinámica.
Seleccione una tabla o un rango ya está seleccionado para usted. La casilla Tabla/Rango muestra el
rango de los datos seleccionados. Nueva hoja de cálculo ya está seleccionada como lugar en el que
se ubicará el informe (puede hacer clic en Hoja de cálculo existente si no desea que el informe se
coloque en una hoja de cálculo nueva).
Esto es lo que ve en la nueva hoja de cálculo tras cerrar el cuadro de diálogo Crear tabla dinámica.
A un lado está la zona de diseño, lista para el informe de tabla dinámica y, al otro lado se
encuentra la lista de campos de tabla dinámica. Esta lista muestra los títulos de las columnas de
los datos de origen. Como decíamos antes, cada título es un campo: País, Comercial, etc.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 25 de 118 www.infoadmin.com.mx
Creación del informe de tabla dinámica Para crear un informe de tabla dinámica, desplace cualquiera de los campos a la zona de diseño
para el informe de tabla dinámica. Puede hacerlo activando la casilla de verificación junto al
nombre del campo, o haciendo clic con el botón secundario del ratón en el nombre de un campo y
seleccionando un lugar al que mover el campo.
1. El área de diseño para el informe de tabla dinámica. 2. La lista de campos de la tabla dinámica.
Si hace clic fuera del área de diseño (de un informe de tabla dinámica), la lista de campos de la
tabla dinámica desaparece. Para recuperar la lista de campos, haga clic dentro del área de diseño
de la tabla dinámica o en el informe.
Ahora ya puede elaborar el informe de tabla dinámica. Los campos que seleccione para el informe
dependen de lo que desee hacer.
Empecemos averiguando cuánto ha vendido cada comercial. Para obtener la respuesta, necesita
datos sobre los comerciales. Así pues, seleccione la casilla de verificación Lista de campos de la
tabla dinámica junto al campo Comercial. También necesita datos sobre cuánto han vendido, así
que seleccione la casilla de verificación junto al campo Cantidad de los pedidos. Tenga en cuenta
que no tiene que utilizar todos los campos de la lista de campos para elaborar un informe.
Cuando selecciona un campo, Excel lo coloca en un área predeterminada del diseño. Puede
desplazar el campo a otra área si lo desea. Por ejemplo, si desea que un campo esté en una
columna en lugar de en una fila. Verá cómo hacerlo en la práctica.
Los datos del campo Comercial (nombres de los comerciales), que no contienen números, se
muestran automáticamente como filas a la izquierda del informe. Los datos del campo Cantidad
de los pedidos, que contiene números, aparece correctamente a la derecha.
1
2
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 26 de 118 www.infoadmin.com.mx
El encabezado sobre los datos del comercial indica "Etiquetas de filas" sobre el campo. El
encabezado sobre las cantidades de los pedidos indica "Suma de cantidades de pedidos"; la parte
"Suma de" del encabezado es porque Excel utiliza el sumatorio para sumar campos con números.
Tenga en cuenta que no importa si activa la casilla de verificación junto el campo Comercial antes
o después del campo Cantidad de los pedidos. Excel los colocará automáticamente en el lugar
correcto cada vez. Los campos sin números acabarán a la izquierda; los campos con números lo
harán a la derecha, independientemente del orden en el que los seleccione.
Eso es todo. Con sólo dos clics sabrá cuánto ha vendido cada comercial. A propósito, podría parar
con tan sólo una pregunta contestada. Puede utilizar un informe de tabla dinámica como un
sistema rápido para obtener la respuesta a una o dos preguntas. El informe no tiene que ser
complejo sino útil.
No se preocupe si elabora un informe de manera incorrecta. Excel facilita el trabajo para ver cuál
es el aspecto de los datos en distintas zonas del informe. Si un informe no es como usted quería al
principio, los datos pueden disponerse de otra manera rápidamente, moviendo partes hasta que
queden como usted desea, o incluso volviendo a empezar.
Agregar filtros de informe Ahora ya sabe cuánto ha vendido cada comercial. Pero los datos de origen distribuyen los datos de
los comerciales en varios países. Así, otra pregunta podría ser: ¿Cuáles son las cantidades de
ventas para cada comercial por país?
Para obtener la respuesta, puede agregar el campo País al informe de tabla dinámica como filtro
de informe. Utiliza el filtro de informe para centrarse en un subconjunto de datos del informe, a
menudo una línea de producto, un período de tiempo o una región geográfica.
Utilizando el campo País como filtro de informe, puede ver un informe para cada país, o puede ver
ventas para un grupo de países conjuntamente.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 27 de 118 www.infoadmin.com.mx
Para agregar este campo como un filtro de informe, haga clic con el botón secundario del mouse
en el campo País de la Lista de campos de la tabla dinámica y, a continuación, haga clic en Agregar
al filtro del informe. El nuevo filtro de informe País se agrega a la parte superior del informe. La
flecha junto al campo País muestra (Todo), y ve los datos de ambos países. Para ver solamente los
datos de EE.UU o de Reino Unido, haga clic en la flecha y seleccione un país o el otro. Para volver a
ver los datos de ambos países, haga clic en la flecha y, a continuación, haga clic en (Todo).
Para eliminar un campo de un informe, deseleccione la casilla de verificación junto al nombre del
campo de la Lista de campos de la tabla dinámica. Para eliminar todos los campos del informe y
poder volver a empezar, en la Cinta, en la ficha Opciones, en el grupo Acciones, haga clic en la
flecha del botón Eliminar y seleccione Eliminar todo.
Los datos de origen originales tienen una columna de información de la Fecha del pedido, por lo
que hay un campo Fecha del pedido en la Lista campo de la tabla dinámica. Eso significa que
puede obtener la respuesta a otra pregunta: ¿Cuáles son las ventas por fecha para cada
comercial? Para obtener la respuesta, seleccione la casilla de verificación junto al campo Fecha del
pedido para agregar el campo al informe.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 28 de 118 www.infoadmin.com.mx
El campo Fecha del pedido se agrega automáticamente a la izquierda, en orientación de la
etiqueta de fila. Esta es la razón por la que el campo no contiene números (las fechas pueden
parecer números, pero su formato es de fechas, no de números). Dado que el campo Fecha del
pedido es el segundo campo no numérico que se agrega al informe, se integra en el campo
Comercial, con sangría a la derecha.
Agrupación de datos Ahora el informe muestra las ventas para cada comercial por fecha, pero son demasiados datos
para verlos en una sola vez. Puede obtener estos datos con facilidad de manera más manejable
agrupando los datos diarios en meses, trimestres o años.
Para agrupar las fechas, haga clic en una fecha cualquiera del informe. A continuación, en la ficha
Opciones, en el grupo Agrupar, haga clic en Agrupar Campo. En el cuadro de diálogo Grupos,
seleccione Trimestres, que parece una buena solución en este caso y, a continuación, haga clic en
Aceptar.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 29 de 118 www.infoadmin.com.mx
Ahora ve los datos de ventas agrupados en cuatro trimestres para cada comercial.
Aunque el informe de tabla dinámica ha respondido a sus preguntas, todavía se tarda un poco en
leer todo el informe; hay que desplazarse hasta el final de la página para ver todos los datos.
Puede desplazar el informe para obtener una visión distinta. Para ello, mueva un campo de la zona
de Etiquetas de filas a la de columnas del informe (llamada Etiquetas de columnas), que es un área
del diseño que no ha utilizado. Cuando desplaza un informe, transpone la vista vertical u
horizontal de un campo, moviendo filas al área de columnas, o moviendo columnas al área de filas.
Es fácil.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 30 de 118 www.infoadmin.com.mx
Para desplazar este informe, haga clic con el botón secundario del ratón en una de las filas
"Trimestre", seleccione Mover y haga clic en Mover "Fecha del pedido" a Columnas. Así se mueve
todo el campo Fecha del pedido del área Etiqueta de fila a la de Columna del informe.
Ahora los nombres de los comerciales están juntos y, encima del primer trimestre de los datos de
ventas pone Etiquetas Columnas, diseñado ahora en columnas en el informe. Además, los totales
para cada trimestre aparecen al final de cada columna. En lugar de tener que desplazarse hasta el
final de la página para ver los datos, puede verlo en un solo vistazo.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 31 de 118 www.infoadmin.com.mx
HERRAMIENTA POWERPIVOT Microsoft PowerPivot para Excel es una herramienta de análisis de datos que ofrece una eficacia
sin igual directamente dentro de la aplicación que ya conoce: Microsoft Excel. La Ayuda de
PowerPivot para Excel le sirve para agilizar su aprendizaje y hacer más productivo su trabajo.
PowerPivot para Excel es un complemento que puede usar para realizar eficaces análisis de datos
en Excel, al tiempo que lleva la inteligencia empresarial de autoservicio a su escritorio. PowerPivot
incluye una ventana para agregar y preparar datos, se integra como una pestaña nueva de la cinta
de opciones de Excel que puede usar para tratar los datos de una hoja de cálculo de Excel.
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 en forma de 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 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
las tablas en la ventana de PowerPivot. Por otra parte, como 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
proporciona todas las funciones de presentación e interactividad para los datos; los datos de
PowerPivot y los objetos de presentación de Excel se encuentran en el mismo archivo de libro.
PowerPivot admite archivos de hasta 2 GB de 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) que 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.
Modelos de datos
Un modelo de datos es un nuevo método para integrar datos de varias tablas y generar de
forma efectiva un origen de datos relacional en un libro de Excel. En Excel, los modelos de
datos se usan de forma transparente y proporcionan datos tabulares utilizados en tablas y
gráficos dinámicos, así como en informes de Power View.
En la mayoría de los casos, ni siquiera se dará cuenta de que el modelo está ahí. En Excel,
un modelo de datos aparece como una colección de tablas en una lista de campos. Para
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 32 de 118 www.infoadmin.com.mx
trabajar directamente con el modelo, necesitará usar el complemento Microsoft Office
PowerPivot para Excel 2013.
Al importar datos relacionales, se crea automáticamente un modelo cuando se seleccionan
varias tablas.
1. En Excel, use Datos > Obtener datos externos para importar datos de una base de
datos relacional de Access (o de otro tipo) que contiene varias tablas relacionadas.
2. Excel le solicitará que seleccione una tabla. Active Habilitar selección de tablas
múltiples.
3. Seleccione dos o más tablas, haga clic en Siguiente y en Finalizar.
4. En Importar datos, elija la opción de visualización de datos que desee, por ejemplo,
una tabla dinámica en una nueva hoja, y genere el informe.
Ahora dispone de un modelo de datos que contiene todas las tablas que importó. Como
seleccionó la opción Informe de tabla dinámica, el modelo se representa en la lista de
campos que utilizará para generar el informe de tabla dinámica.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 33 de 118 www.infoadmin.com.mx
¿Qué puede hacer con un modelo de datos?
Puede usarlo para crear tablas y gráficos dinámicos e informes de Power View en el mismo
libro. Puede modificarlo si agrega o quita tablas y, si usa el complemento de PowerPivot,
puede extender el modelo si agrega columnas calculadas, campos calculados, jerarquías y
KPI.
Al crear un modelo de datos, la opción de visualización es importante. Es conveniente
elegir Informe de tabla dinámica, Gráfico dinámico o Informe de Power View para la
visualización de datos. Estas opciones permiten trabajar con todas las tablas en conjunto. Si
seleccionara Tabla en su lugar, cada tabla importada se colocaría en una hoja
independiente. En esta organización, se pueden usar las tablas individualmente, pero
utilizar todas las tablas juntas requiere una tabla dinámica, un gráfico dinámico o un
informe de Power View.
Los modelos se crean de forma implícita al importar dos o más tablas a la vez en Excel, por otro
lado, los modelos se pueden crean explícitamente cuando se usa el complemento PowerPivot para
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 34 de 118 www.infoadmin.com.mx
importar datos. En el complemento, el modelo se representa en un diseño con pestañas, donde
cada pestaña contiene datos tabulares.
Un modelo puede contener una sola tabla. Para crear un modelo basado en solo una tabla,
seleccione la tabla y haga clic en Agregar a modelo de datos en PowerPivot. Puede hacer esto si
desea usar las características de PowerPivot, como conjuntos de datos filtrados, columnas
calculadas, campos calculados, KPI y jerarquías.
Las relaciones de la tabla pueden crearse automáticamente si se importan tablas relacionadas que tienen relaciones de clave principal y externa. Excel puede usar normalmente la información importada de la relación como base para las relaciones de la tabla en el modelo de datos.
SUGERENCIA: Si un libro tiene datos, pero quizá no sabe si contiene un modelo de datos. Puede
determinar rápidamente el estado del modelo abriendo la ventana de PowerPivot; si aparecen
datos en las pestañas es que existe un modelo.
Un libro de Excel puede contener solo un modelo de datos, pero ese modelo se puede usar
repetidamente en el libro en otra tabla dinámica, otro gráfico dinámico u otro informe de Power
View, para ello, en el momento de insertar una tabla o gráfico dinámico debe seleccionar la opción
de utilizar una fuente de datos externa y marcar una conexión existente, para luego seleccionar
Tablas y a continuación aparece una lista de campos de tabla dinámica, que muestra todas las
tablas del modelo.
Agregar datos actuales y no relacionados a un modelo de datos
Suponga que ha importado o copiado muchos datos que desea usar en un modelo, pero que no
comprobó el cuadro Agregar datos al modelo de datos durante la importación, incluso en estas
condiciones resulta bastante fácil insertar nuevos datos.
Comience con los datos que desea agregar al modelo. Puede ser cualquier intervalo de datos, pero
usar un rango con nombre funciona mejor.
Resalte las celdas que desea agregar o, si los datos están en una tabla o un rango con nombre,
coloque el cursor en una celda, luego haga clic en PowerPivot > Agregar a modelo de datos o haga
clic en Insertar > Tabla dinámica y, a continuación, active Agregar datos al modelo de datos en el
cuadro de diálogo Crear tabla dinámica, esto provocará que el intervalo o la tabla se agrega ahora
al modelo como tabla vinculada.
Refinar y extender el modelo de datos en el complemento de PowerPivot
En Excel, los modelos de datos existen para amplificar y enriquecer la creación de informes,
especialmente cuando esa experiencia incluye tablas dinámicas u otros formatos de informe que
están diseñados para la exploración y el análisis de los datos. Aunque son importantes, los
modelos de datos se mantienen adrede en segundo plano para permitirle centrarse en lo que
desea hacer con ellos.
Pero suponga que trabajar directamente en el modelo es justo lo que desea hacer. Sabiendo que
la lista de campos se basa en un modelo, quizá desee quitar las tablas o los campos porque no son
útiles en la lista. Puede que desee ver todos los datos subyacentes que el modelo proporciona o
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 35 de 118 www.infoadmin.com.mx
agregar KPI, jerarquías y lógica de negocios. Por todas estas razones y otras muchas, le interesará
modificar el modelo de datos directamente.
Para modificar o administrar el modelo de datos, utilice el complemento PowerPivot el cual forma
parte de la edición Office Professional Plus de Excel 2013, pero no está habilitado de forma
predeterminada.
Diferencias entre libros de Excel y PowerPivot Hay diferencias notables entre la ventana de PowerPivot y la ventana de Excel por lo que se refiere
a cómo se trabaja con datos en cada uno. Hay también algunos otras diferencias importantes que
deseamos tratar específicamente:
Los datos PowerPivot pueden estar guardados en libros que tienen los siguientes tipos de archivo:
Libro de Excel (* .xlsx), Libro de Excel habilitado con macros (* .xlsm) y Libro binario de Excel (*
.xlsb). Los datos de PowerPivot no se admiten en libros con otros formatos.
La ventana de PowerPivot no admite Visual Basic para Aplicaciones (VBA) aunque si puede utilizar
VBA en la ventana de Excel de un libro de PowerPivot.
En las tablas dinámicas de Excel, puede agrupar los datos haciendo clic con el botón secundario en
un encabezado de columna y seleccionando Grupo. Esta característica se utiliza a menudo para
agrupar los datos por fecha. En las tablas dinámicas que están basadas en datos de PowerPivot,
utiliza las columnas calculadas para lograr una funcionalidad similar.
En PowerPivot, no puede agregar una fila a una tabla escribiendo directamente en una nueva fila
como en una hoja de cálculo de Excel, pero puede agregar filas con los comandos Pegar y
actualizando los datos.
Los datos de una hoja de cálculo de Excel suelen ser variables y desiguales: es decir, una fila podría
contener datos numéricos y la siguiente podría contener un gráfico o una cadena de texto. Por el
contrario, es más probable que una tabla de PowerPivot esté en una base de datos relacional, en
la que cada fila tiene el mismo número de columnas y la mayoría de las columnas contiene datos.
Usar el modelo de datos en Power View Un modelo de datos se usa como base de un informe de Power View. Mediante el complemento
de PowerPivot, puede aplicar al modelo optimizaciones que mejoran la generación de informes de
Power View. Entre las optimizaciones se incluye: especificar una lista de campos predeterminada,
elegir campos o imágenes representativos para identificar de forma exclusiva filas concretas o
especificar cómo se administran las filas con valores repetidos (como los empleados o los clientes
con el mismo nombre) en una aplicación de generación de informes.
Iniciar el complemento PowerPivot de Excel 2013
PowerPivot de Excel 2013 es un complemento que puede usar para realizar eficaces análisis de
datos en Excel 2013. Este complemento está disponible en Microsoft Office Professional Plus. Está
integrado en Excel 2013 pero no está habilitado.
Para habilitar este complemento, siga estos pasos:
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 36 de 118 www.infoadmin.com.mx
1. Vaya a Archivo > Opciones > Complementos. 2. En el cuadro Administrar, haga clic en Complementos COM> Ir. 3. Active el cuadro Microsoft Office PowerPivot para Excel 2013 y haga clic en Aceptar. Si
tiene instaladas otras versiones del complemento PowerPivot, también se muestran en la lista Complementos COM. Asegúrese de seleccionar el complemento PowerPivot para Excel 2013.
La cinta de opciones tiene ahora una pestaña PowerPivot.
Características de PowerPivot para Excel Vista de diagrama. La vista de diagrama está disponible en la pestaña Inicio de la ventana
de PowerPivot, y permite ver tablas organizadas visualmente y agregar y modificar
fácilmente relaciones y jerarquías.
Jerarquías. Una jerarquía es una lista de nodos secundarios que puede crear a partir de
columnas y poner en el orden que desee, lo que simplifica que los usuarios de clientes de
informes seleccionen y naveguen por las rutas de acceso comunes de datos.
Relaciones en la vista de diagrama. En la vista de diagrama, es fácil crear relaciones entre
columnas de tablas distintas. Las relaciones aparecen visualmente, lo que permite ver
rápidamente cómo se relacionan todas las tablas entre sí.
Varias relaciones. Se pueden importar varias relaciones. La primera relación es la activa, y
las demás relaciones están inactivas y aparecen como líneas de puntos en la vista de
diagrama.
Área de cálculo. El Área de cálculo permite ver campos calculados en un patrón de
cuadrícula, así como crear, editar y administrar fácilmente los campos calculados e
indicadores clave de desempeño (KPI) dentro del modelo.
Pestaña Avanzadas. Las características avanzadas están disponibles en una pestaña
independiente. Estas características incluyen la posibilidad de crear o editar perspectivas,
resumir una columna numérica mediante una función de agregación y configurar
propiedades de informes para una herramienta cliente de generación de informes, como
Power View.
Propiedades de informes En el área Propiedades de informes de la pestaña Avanzadas,
puede establecer el identificador de tabla, agrupar valores basándose en un identificador
de tabla, agregar detalles de tabla, establecer la columna representativa, establecer una
dirección URL de imagen y establecer la imagen representativa para herramientas cliente
de generación de informes como Power View.
Funciones DAX. Incluye una gran variedad de funciones especializadas en el diseño de
Expresiones de Análisis de Datos (DAX) que 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
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 37 de 118 www.infoadmin.com.mx
la de las fórmulas de Excel con una combinación de funciones, operadores y valores y al
igual que en Excel, están agrupadas por categorías.
o Funciones de fecha y hora
o Funciones matemáticas y trigonométricas
o Funciones estadísticas
o Funciones de texto
o Funciones lógicas
o Funciones de filtro
o Funciones de información
Ordenar por otra columna. Si no desea ordenar la columna alfabéticamente, ahora puede
ordenarla por otra columna. Por ejemplo, puede ordenar por una columna de números de
mes que asigne a cada mes su número para ordenar la columna de forma natural.
Agregar valores a filas y columnas. Puede agregar valores a filas y columnas.
Compatibilidad con blobs. Se pueden importar imágenes y blobs (Binary Large Object
Blocks) que permiten la integración de datos de diversos tipos tales como sonido, videos,
etc. Ahora, los datos blob se detectan y aceptan automáticamente como un tipo de datos
binario.
Perspectivas. Son niveles de metadatos que realizan el seguimiento de distintos
segmentos o conjuntos de datos. Las perspectivas suelen definirse para un grupo de
usuarios o un escenario de negocios determinado, facilitando la navegación en conjuntos
de datos grandes.
Indicadores clave de desempeño (KPI) Un indicador clave de desempeño (KPI) se basa en
un campo calculado específico y se ha diseñado para ayudar a evaluar el valor y el estado
actuales de una métrica.
Configuración de tabla de fecha. Puede marcar una tabla como tabla de fecha, lo que le
permitirá aprovechar el filtrado de fechas de Excel.
Mostrar detalles Haga clic con el botón secundario en una celda de una tabla dinámica de
Excel y, a continuación, haga clic en Mostrar detalles. Se abre una hoja de cálculo nueva,
con los datos subyacentes que contribuyen al valor de la celda especificada.
Cambio de tipos de datos. Puede cambiar el tipo de datos de una columna calculada de la
misma forma que puede hacerlo para todas las columnas no calculadas.
Formatos de número para los campos calculados. Puede establecer el tipo de formato de
número (por ejemplo, moneda), especificar el número de posiciones decimales que desea
mostrar, seleccionar un símbolo que desea mostrar con los números, y utilizar un símbolo
de agrupación de dígitos (como un punto) para indicar las separación de millares.
Persistencia del formato. Al aplicar formato a las columnas del entorno de modelado, el
formato se conserva al agregar campos a las áreas de valores de una tabla dinámica.
Lista de campos: descripciones. Agregue descripciones a las tablas, los campos calculados,
y los indicadores claves de desempeño (KPI). Cuando el usuario mantenga el mouse sobre
esas tablas, campos calculados y KPI de la lista de campos, aparece información sobre
herramientas con las descripciones del contexto de cada campo.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 38 de 118 www.infoadmin.com.mx
Análisis de datos con PowerPivot Para poder analizar los datos en PowerPivot, debe preparar los datos para dicho análisis. La
preparación tiene lugar en gran medida en la ventana de PowerPivot y generalmente el flujo de
trabajo de la ventana de PowerPivot se parece al siguiente:
1. Se agregan datos de uno o varios orígenes de datos.
2. Se revisan los datos agregados. Filtre, ordene y examine rápidamente los datos.
3. Agregue nuevas columnas o elimine las columnas innecesarias en los datos importados.
4. Las tablas se conectan creando relaciones, o se revisan las relaciones que se hayan
importado con los datos.
5. Use perspectivas para crear vistas personalizadas y centrarse solo en los datos necesarios.
6. Cree cálculos a partir de los datos.
PowerPivot para Excel es flexible, de modo que no se le requiere que siga un flujo de trabajo
lineal. Por ejemplo, podría agregar datos de nuevos orígenes una vez que haya definido algunas
relaciones entre las tablas existentes.
Agregar datos a la ventana de PowerPivot Una de las formas de integrar datos al PowerPivot es por medio de copiar y pegar, además se
puede usar cualquiera de estas formas de integración de datos en PowerPivot:
1. Agregar datos utilizando tablas vinculadas de Excel
2. Importar datos desde un archivo
3. Importar datos de una base de datos
4. Importar datos de Analysis Services o PowerPivot
5. Importar datos desde un informe de Reporting Services
6. Importar datos de una fuente de distribución de datos
Para presentar los elementos básicos de la interface de usuarios, explicaremos el método de
integración de datos por medio de la técnica de copiar y pegar.
Capture una tabla de en Excel como la que se muestra, asegúrese de integrar dicha información
dentro de un recurso TABLA, para hacerlo, todo lo que necesita es seleccionar cualquier celda del
rango y activar la combinación de teclas CTRL+T o dar clic en el botón TABLA del grupo Tablas de la
cinta Insertar:
FechaVenta Subcategoria Producto Venta Cantidad
01/05/2009 Accesorios Estuche $254,995.00 68
01/05/2009 Accesorios Cargador de batería $1,099.56 44
01/05/2009 Digital Memoria SD $6,512.00 44
01/06/2009 Accesorios Lente tele objetivo $1,662.50 18
01/06/2009 Accesorios Tripod $938.34 18
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 39 de 118 www.infoadmin.com.mx
01/06/2009 Accesorios Cable USB $1,230.25 26
Copie tota la tabla (incluyendo el encabezado) y luego, en la ventana de PowerPivot en la pestaña
Inicio, haga clic en Pegar.
En el cuadro de diálogo Vista previa de pegado, haga clic en Aceptar.
La ventana de PowerPivot se abre sobre la ventana de Excel, con los datos pegados. Para alternar
entre las dos ventanas: haga clic en el botón de Excel de la barra de herramientas de acceso rápido
en la ventana de PowerPivot o de la Ventana de PowerPivot en la pestaña de PowerPivot en la
ventana de Excel. Puede cerrar la ventana de PowerPivot sin cerrar Excel: en la ventana de
PowerPivot, en el botón de PowerPivot, haga clic en Cerrar.
La cinta de opciones de PowerPivot incluye las siguientes pestañas:
La pestaña Inicio, donde puede agregar nuevos datos, copiar y pegar datos de Excel y otras
aplicaciones, aplicar formato y ordenar y filtrar los datos.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 40 de 118 www.infoadmin.com.mx
La pestaña Diseño, donde puede cambiar las propiedades de las tablas, crear y administrar las
relaciones, y modificar las conexiones a los orígenes de datos existentes. También puede agregar
columnas y cambiar cuándo se calculan los valores de columna.
La pestaña Avanzadas, donde puede crear nuevas perspectivas, permite habilitar la visualización
de campos calculados implícitos en el área de cálculo y establecer propiedades que mejoren la
experiencia de diseño de informes en aplicaciones de informes como Power View.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 41 de 118 www.infoadmin.com.mx
La pestaña contextual Tablas vinculadas, donde puede trabajar con tablas que están vinculadas a
las tablas de Excel. Esta pestaña está disponible en la cinta de opciones de PowerPivot únicamente
si selecciona una tabla de PowerPivot que está vinculada a una tabla de Excel. Una tabla vinculada
es una tabla que se ha creado en Excel, pero se ha vinculado a una tabla en la ventana de
PowerPivot. La ventaja de crear y mantener los datos en Excel, en lugar de importarlos, es que
puede continuar modificando los valores en la hoja de cálculo de Excel, utilizando los datos para el
análisis en PowerPivot.
Generar una tabla dinámica de PowerPivot en Excel
Asegúrese que está en la pestaña Inicio en la ventana de PowerPivot.
Haga clic en el botón Tabla dinámica.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 42 de 118 www.infoadmin.com.mx
En el cuadro de diálogo Crear tabla dinámica, haga clic en Aceptar.
Ahora regrese a la ventana de Excel, con la pestaña PowerPivot seleccionada y la Lista de campos
de PowerPivot visible. La pestaña PowerPivot de Excel proporciona herramientas para trabajar con
tablas dinámicas, campos calculados y tablas vinculadas, y para abrir la ventana de PowerPivot.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 43 de 118 www.infoadmin.com.mx
La Lista de campos de PowerPivot proporciona herramientas para trabajar con listas de campos y
segmentaciones de datos, que le permiten filtrar los datos en una tabla dinámica. Agregue campos
a la tabla dinámica seleccionándolos en la lista de campos, como se muestra en el siguiente
gráfico.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 44 de 118 www.infoadmin.com.mx
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 45 de 118 www.infoadmin.com.mx
COMPONENTES DE LOS MODELOS DE DATOS DE POWERPIVOT
Campos calculados Los campos calculados, también conocidos como medidas en las versiones anteriores de
PowerPivot y en los modelos tabulares de Analysis Services, son los cálculos que se usan en el
análisis de datos. Entre los ejemplos que se encuentran normalmente en los informes
empresariales se incluyen: sumas, promedios, valores mínimos o máximos, recuentos o cálculos
más avanzados que se crean con una fórmula de Expresiones de análisis de datos (DAX).
Normalmente en una tabla dinámica, gráfico dinámico o informe, un campo calculado se coloca en
el área de valores, donde las etiquetas de fila y de columna que la rodean determinan el contexto
del valor. Por ejemplo, si está midiendo las ventas por año (en columnas) y región (en filas), el
valor del campo calculado se calcula basándose en un año y una región determinados. El valor de
un campo calculado siempre cambia en respuesta a las selecciones realizadas en filas, columnas y
filtros, permitiendo la exploración de datos en tiempo real.
Aunque los campos calculados y las columnas calculadas son similares en que ambos se basan en
una fórmula, difieren en cómo se utilizan. Los campos calculados se usan con más frecuencia en el
área Valores de una tabla dinámica o un gráfico dinámico. Las columnas calculadas se utilizan
cuando se desea colocar resultados calculados en otra área de una tabla dinámica (como en una
columna o en una fila de una tabla dinámica, o en un eje de un gráfico dinámico).
Un indicador clave de desempeño (KPI) se basa en un campo calculado específico y está diseñado
para ayudar al usuario final a evaluar el valor y el estado actuales de una métrica con respecto a
un destino definido. El KPI mide el rendimiento del valor, definido por un campo calculado base,
con respecto a un valor de destino, también definido por un campo calculado o por un valor
absoluto. En la terminología empresarial, un KPI es un campo calculado cuantificable para
identificar los objetivos empresariales. Por ejemplo, el departamento de ventas de una
organización podría usar un KPI para medir el beneficio bruto mensual frente al beneficio bruto
previsto. El departamento de contabilidad podría medir los gastos mensuales frente a los ingresos
para evaluar los costos y un departamento de recursos humanos podría medir la rotación
trimestral de empleados. Cada uno de ellos es un ejemplo de KPI. Los profesionales de una
empresa suelen usar KPI agrupados en un cuadro de mandos empresarial para obtener un
resumen histórico rápido y preciso de los éxitos empresariales o para identificar tendencias.
Tipos de campos calculados
Los campos calculados son implícitos o explícitos, lo que afecta a la forma en que se usan en un
gráfico dinámico o en una tabla dinámica y en otras aplicaciones que usan un modelo de datos de
PowerPivot como origen de datos.
Campo calculado implícito
Un campo calculado implícito se crea en Excel cuando se arrastra un campo, como Sales Amount,
al área Valores de una lista de campos de tabla dinámica. Dado que Excel genera los campos
calculados, es posible que no tenga constancia de que se ha creado un nuevo campo calculado.
Pero si examina la lista Valores detenidamente, verá que el campo Sales Amount es, de hecho, un
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 46 de 118 www.infoadmin.com.mx
campo calculado denominado Sum of Sales Amount y aparece con este nombre en el área Valores
de la lista de campos de tabla dinámica y en la propia tabla dinámica.
Campo calculado implícito creado en una tabla dinámica
Los campos calculados implícitos solo pueden usar una agregación estándar (SUM, COUNT, MIN,
MAX, DISTINCTCOUNT o AVG) y deben usar el formato de datos definido para esa agregación.
Además, los campos calculados implícitos solo pueden ser usados por la tabla dinámica o el gráfico
para los que se crearon.
Un campo calculado implícito se acopla estrechamente al campo en que se basa y afectan a la
forma en que se elimina o modifica el campo calculado más adelante.
Campo calculado explícito
Un campo calculado explícito se crea al escribir o seleccionar una fórmula en una celda del área de
cálculo o mediante la función de Autosuma en la ventana de PowerPivot. La mayoría de los
campos calculados que se crean serán explícitos.
Campo calculado explícito creado en el área de cálculo de PowerPivot
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 47 de 118 www.infoadmin.com.mx
Los campos calculados explícitos pueden usar cualquier tabla dinámica o gráfico dinámico del libro
y de los informes de Power View. Además, pueden extenderse para convertirse en un KPI o darles
formato con una de las numerosas cadenas disponibles para los datos numéricos. Los comandos
de menú contextual de Crear KPI y Formato solo están disponibles cuando se utiliza un campo
calculado explícito.
NOTA: Una vez que usa un campo calculado como KPI, no la puede utilizar en otros cálculos. Si
desea usar la fórmula también en cálculos, debe hacer una copia.
Ejemplo
Se le ha solicitado a la directora de ventas de Adventure Works que proporcione las previsiones de
ventas de los distribuidores en el próximo año fiscal. Decide basar sus estimaciones en los
importes de ventas del año anterior, con un aumento anual del seis por ciento resultante de las
distintas promociones que se han programado en los próximos seis meses.
Para desarrollar las estimaciones, importa los datos de ventas de los distribuidores del último año
y agrega una tabla dinámica. Busca el campo Importe de venta en la tabla Venta del distribuidor y
lo arrastra al área de valores de la lista de campos de tabla dinámica. El campo aparece en la tabla
dinámica como un único valor que es la suma de todas las ventas de distribuidores del año
anterior. Observa que aunque no ha especificado el cálculo, se ha proporcionado un cálculo
automáticamente, y el nombre del campo ha cambiado a Suma de importe de ventas en la lista de
campos y en la tabla dinámica. Una agregación integrada añadida por Excel,
=SUM('FactResellerSales'[SalesAmount]), proporciona el cálculo. Cambia el nombre del campo
calculado implícito Ventas del último año.
El siguiente cálculo es la previsión de ventas del año siguiente, que se basará en las ventas del
último año multiplicadas por 1,06 para tener en cuenta el aumento esperado del 6 por ciento en el
sector de distribuidores. Para este cálculo, debe crear el campo calculado explícitamente,
utilizando el botón Nuevo campo calculado para crear un cálculo denominado Ventas previstas.
Rellena la fórmula siguiente: =SUM('FactResellerSales'[SalesAmount])*1.06.
El nuevo campo calculado se agrega al área Valores de la lista de campos de tabla dinámica.
También se agrega a la tabla actualmente activa en la lista de campos de tabla dinámica. La tabla
proporciona una ubicación para el campo calculado en el libro. Dado que prefiere tener el campo
calculado en una tabla diferente, edita el campo calculado para cambiar la asociación de la tabla.
Muy rápidamente y con un mínimo esfuerzo por su parte, la directora de ventas tiene la
información básica en su lugar. Ahora puede evaluar mejor las previsiones filtrando en
distribuidores específicos o agregando información de la línea de producto para comprobar que
las promociones futuras corresponden a los productos que el distribuidor comercializa.
Tipos de datos admitidos en libros PowerPivot Los siguientes tipos de datos se admiten para su uso en PowerPivot. Cuando importa datos o usa
un valor en una fórmula, incluso si el origen de datos contiene un tipo de datos distinto, los datos
se convierten a uno de los siguientes tipos de datos. Los datos que se producen como resultado de
las fórmulas también usan estos tipos de datos.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 48 de 118 www.infoadmin.com.mx
En general, estos tipos de datos se implementan para habilitar cálculos precisos en columnas
calculadas y, para mantener la coherencia, se aplican las mismas restricciones al resto de los datos
en PowerPivot.
Los formatos usados para números, moneda, fechas y horas deben seguir el formato de la
configuración regional especificada en el equipo que abre el libro. Se pueden usar las opciones de
formato de la hoja de cálculo para controlar la forma en que se muestra el valor.
Tipo de datos en la
interfaz de usuario
de PowerPivot
Tipo de datos
en DAX
Descripción
Número entero Valor entero de
64 bits (ocho
bytes) 1, 2
Números que no tienen posiciones decimales. Los enteros pueden
ser números positivos o negativos, pero deben ser números
enteros comprendidos entre -9,223,372,036,854,775,808 (-263
) y
9,223,372,036,854,775,807 (263
-1).
Número decimal Número real de
64 bits (ocho
bytes) 1, 2
Los números reales son aquellos que pueden tener posiciones
decimales. Abarcan un amplio intervalo de valores:
Valores negativos desde -1.79*10308
hasta -2.23*10-308
Cero
Valores positivos desde 2.23*10-308
hasta 1.79*10308
Sin embargo, el número de dígitos significativos se limita a 17
dígitos decimales.
TRUE/FALSE Boolean Valor True o False.
Texto Cadena Cadena de datos de carácter Unicode. Pueden ser cadenas,
números o fechas representados en un formato de texto.
La longitud de cadena máxima es 268,435,456 caracteres
Unicode (256 caracteres mega) o 536,870.912 bytes.
Fecha Fecha y hora Fechas y horas en una representación de fecha y hora aceptada.
Las fechas válidas son todas las fechas posteriores al 1 de enero
de 1900.
Moneda Currency El tipo de datos de moneda permite los valores comprendidos
entre -922,337,203,685,477.5808 y 922,337,203,685,477.5807
con cuatro dígitos decimales de precisión fija.
N/D En blanco Un tipo en blanco es un tipo de datos de DAX que representa y
reemplaza los valores NULL de SQL. Un valor en blanco se
puede crear con la función BLANK y se puede comprobar si es
tal con la función lógica ISBLANK.
Las fórmulas DAX no admiten tipos de datos que sean menores que los enumerados
en la tabla.
Si intenta importar datos con valores numéricos muy elevados, es posible que la
importación no se realice correctamente con el error siguiente:
Error de la base de datos en memoria: La columna '<nombre de columna>' de la
tabla '<nombre de la tabla>' contiene un valor, '1.7976931348623157e+308' que no
se admite. La operación se ha cancelado.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 49 de 118 www.infoadmin.com.mx
Este error se produce porque PowerPivot utiliza ese valor para representar los
valores NULL. Los valores de la siguiente lista son sinónimos del valor NULL
mencionado anteriormente:
Valor
9223372036854775807
-9223372036854775808
1.7976931348623158e+308
2.2250738585072014e-308
Debería quitar de nuevo el valor de los datos e intentar volver a importarlo.
Tipo de datos de tabla
Además, DAX usa un tipo de datos de tabla. DAX usa este tipo de datos en muchas funciones,
como agregaciones y cálculos de inteligencia de tiempo. Algunas funciones requieren una
referencia a una tabla y otras devuelven una tabla que se puede usar como entrada para otras
funciones. En algunas funciones que requieren una tabla como entrada, puede especificar una
expresión que se evalúa como una tabla; para otras funciones, se requiere una referencia a una
tabla base.
Conversiones implícitas y explícitas de tipos de datos en fórmulas de DAX
Cada función DAX tiene requisitos concretos acerca de los tipos de datos que se usan como
entradas y salidas. Por ejemplo, algunas funciones requieren enteros para algunos argumentos y
fechas para otros; otras funciones requieren texto o tablas.
Si los datos de la columna que especifique como argumento son incompatibles con el tipo de
datos requerido por la función, en muchos casos DAX devolverá un error. No obstante, siempre
que sea posible DAX intentará convertir implícitamente los datos al tipo requerido. Por ejemplo:
Una fecha se puede escribir como una cadena y DAX la analizará, e intentará convertirla a uno de
los formatos de fecha y hora de Windows.
Se pueden sumar TRUE + 1 y obtener el resultado 2, ya que TRUE se convierte implícitamente al
número 1 y se realiza la operación 1+1.
Si suma los valores de dos columnas y uno está representado como texto ("12") y el otro como
número (12), DAX convierte implícitamente la cadena a un número y, a continuación, realiza la
suma para obtener un resultado numérico. La expresión siguiente devuelve 44: = "22" + 22
Si intenta concatenar dos números, el complemento de PowerPivot los presentará como cadenas
y, a continuación, los concatenará. La siguiente expresión devuelve "1234": = 12 & 34
En la tabla siguiente se resumen las conversiones implícitas de tipo de datos que se realizan en las
fórmulas. En general, PowerPivot se comporta como Microsoft Excel y, siempre que sea posible,
realiza conversiones implícitas cuando lo requiere la operación especificada.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 50 de 118 www.infoadmin.com.mx
Tabla de conversiones de datos implícitas El tipo de conversión que se realiza está determinada por el operador, que convierte los valores
que requiere antes de realizar la operación solicitada. En estas tablas se enumeran los operadores
y se indica la conversión que se lleva a cabo en cada tipo de datos de la columna cuando se
empareja con el tipo de datos de la fila de intersección.
Nota
Los tipos de datos de texto no se incluyen en estas tablas. Cuando un número se representa en
formato de texto, en algunos casos PowerPivot intentará determinar el tipo de número y
representarlo como un número.
Suma (+)
Operador (+) INTEGER CURRENCY REAL Fecha y hora
INTEGER INTEGER CURRENCY REAL Fecha y hora
CURRENCY CURRENCY CURRENCY REAL Fecha y hora
REAL REAL REAL REAL Fecha y hora
Fecha y hora Fecha y hora Fecha y hora Fecha y hora Fecha y hora
Por ejemplo, si se usa un número real en una operación de suma en combinación con datos de
moneda, ambos valores se convierten en REAL y el resultado se devuelve como REAL.
Resta (-)
En la siguiente tabla el encabezado de fila es el minuendo (el lado de la izquierda) y el encabezado
de columna es el substraendo (el lado de la derecha).
Operador (-) INTEGER CURRENCY REAL Fecha y hora
INTEGER INTEGER CURRENCY REAL REAL
CURRENCY CURRENCY CURRENCY REAL REAL
REAL REAL REAL REAL REAL
Fecha y hora Fecha y hora Fecha y hora Fecha y hora Fecha y hora
Por ejemplo, si se usa una fecha en una operación de resta con otro tipo de datos, ambos valores
se convierten en fechas y el valor devuelto también es una fecha.
Nota
PowerPivot también admite el operador unario - (negativo), pero este operador no cambia el tipo
de datos del operando.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 51 de 118 www.infoadmin.com.mx
Multiplicación (*)
Operador (*) INTEGER CURRENCY REAL Fecha y hora
INTEGER INTEGER CURRENCY REAL INTEGER
CURRENCY CURRENCY REAL CURRENCY CURRENCY
REAL REAL CURRENCY REAL REAL
Por ejemplo, si un entero se combina con un número real en una operación de multiplicación,
ambos números se convierten a números reales y el valor devuelto también es REAL.
División (/)
En la siguiente tabla, el encabezado de fila es el numerador y el encabezado de columna es el
denominador.
Operador (/)
(Fila/Columna) INTEGER CURRENCY REAL Fecha y hora
INTEGER REAL CURRENCY REAL REAL
CURRENCY CURRENCY REAL CURRENCY REAL
REAL REAL REAL REAL REAL
Fecha y hora REAL REAL REAL REAL
Por ejemplo, si un entero se combina con un valor de moneda en una operación de división,
ambos valores se convierten a números reales y el resultado también es un número real.
Operadores de comparación
En las expresiones de comparación, los valores booleanos se consideran mayores que los valores
de cadena y los valores de cadena se consideran mayores que los valores numéricos o de fecha u
hora; se considera que los números y valores de fecha u hora tienen el mismo rango. No se realiza
ninguna conversión implícita para los valores booleanos o de cadena; BLANK o un valor en blanco
se convierte en 0/""/false, según el tipo de datos del otro valor comparado.
Las siguientes expresiones de DAX muestran este comportamiento:
=IF(FALSE()>"true","Expression is true", "Expression is false"), devuelve "Expression is true"
=IF("12">12,"Expression is true", "Expression is false"), devuelve "Expression is true"
=IF("12"=12,"Expression is true", "Expression is false"), devuelve "Expression is false"
Las conversiones se realizan implícitamente para los tipos numéricos o de fecha y hora, tal y como
se describe en la siguiente tabla:
Operador de comparación INTEGER CURRENCY REAL Fecha y hora
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 52 de 118 www.infoadmin.com.mx
INTEGER INTEGER CURRENCY REAL REAL
CURRENCY CURRENCY CURRENCY REAL REAL
REAL REAL REAL REAL REAL
Fecha y hora REAL REAL REAL Fecha y hora
Controlar valores en blanco, cadenas vacías y valores cero
La forma en que DAX trata los valores cero, los valores NULL y las cadenas vacías es distinta a
como lo hacen Microsoft Excel y SQL Server.
Lo importante es recordar que en PowerPivot un valor en blanco, una celda vacía o la ausencia de
un valor se representan por el nuevo tipo de valor: BLANK. Depende de cada función el modo en
que se tratan en las operaciones, como suma o concatenación. También se pueden generar
valores en blanco con la función BLANK o comprobar los valores en blanco con la función ISBLANK.
Los valores NULL de base de datos no se admiten en un libro de PowerPivot y se convierten
implícitamente a valores en blanco si en una fórmula DAX se hace referencia a una columna que
contiene un valor NULL.
Definir valores en blanco, valores NULL y cadenas vacías
En la tabla siguiente se resumen las diferencias entre DAX y Microsoft Excel con respecto al modo
en que se tratan los valores en blanco.
Expresión DAX Excel
BLANK + BLANK BLANK 0 (cero)
BLANK +5 5 5
BLANK * 5 BLANK 0 (cero)
5/BLANK Infinito Error
0/BLANK NaN Error
BLANK/BLANK BLANK Error
FALSE OR BLANK FALSE FALSE
FALSE AND BLANK FALSE FALSE
TRUE OR BLANK TRUE TRUE
TRUE AND BLANK FALSE TRUE
BLANK OR BLANK BLANK Error
BLANK AND BLANK BLANK Error
Relaciones
¿Qué es una relación?
Una relación es una conexión entre dos tablas de datos, basada en una o más columnas de cada
tabla (exactamente una columna de cada tabla para PowerPivot). Para ver por qué son útiles las
relaciones, imagine que realiza el seguimiento de los datos de los pedidos de los clientes de su
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 53 de 118 www.infoadmin.com.mx
negocio. Podría realizar el seguimiento de todos los datos en una sola tabla que tiene una
estructura como la siguiente:
CustomerID Nombre EMail DiscountRate OrderID OrderDate Product Quantity
1 Ashton [email protected] .05 256 2010-01-07 Compact
Digital
11
1 Ashton [email protected] .05 255 2010-01-03 SLR Camera 15
2 Jaworski [email protected] .10 254 2010-01-03 Budget Movie-Maker
27
Este enfoque puede funcionar, pero implica almacenar muchos datos redundantes, como la
dirección de correo electrónico del cliente para cada pedido. El almacenamiento es barato, pero
tiene que asegurarse de que actualiza cada fila para ese cliente si la dirección de correo
electrónico cambia. Una solución a este problema es dividir los datos en varias tablas y definir
relaciones entre esas tablas. Este es el enfoque utilizado en las bases de datos relacionales como
SQL Server. Por ejemplo, una base de datos que importe en PowerPivot para Excel podría
representar los datos de los pedidos usando tres tablas relacionadas:
Customers [CustomerID] Nombre Email
1 Ashton [email protected]
2 Jaworski [email protected]
CustomerDiscounts [CustomerID] DiscountRate
1 .05
2 .10
Orders [CustomerID] OrderID OrderDate Product Quantity
1 256 2010-01-07 Compact Digital 11
1 255 2010-01-03 SLR Camera 15
2 254 2010-01-03 Budget Movie-Maker 27
Si importa estas tablas de la misma base de datos, PowerPivot puede detectar las relaciones entre
las tablas en función de las columnas que están entre [corchetes] y puede reproducirlas en la
ventana de PowerPivot y si importa las tablas de varios orígenes, puede crear las relaciones
manualmente.
Claves y columnas
Las relaciones se basan en las columnas de cada tabla que contienen los mismos datos. Por
ejemplo, las tablas Customers y Orders pueden estar relacionadas entre sí porque ambas
contienen una columna que almacena un identificador de cliente. En el ejemplo, los nombres de
columna son los mismos, pero no es obligatorio. Uno puede ser CustomerID y otro puede ser
CustomerNumber, en tanto en cuanto todas las filas de la tabla Orders contengan un identificador
que también esté almacenado en la tabla Customers.
En una base de datos relacional, hay varios tipos de claves, que normalmente son solo columnas
con propiedades especiales. Los siguientes cuatro tipos de claves son los más interesantes para
nuestros propósitos:
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 54 de 118 www.infoadmin.com.mx
Clave principal: identifica en exclusividad una fila de una tabla, como CustomerID en la tabla
Customers.
Clave alternativa (o clave candidata): una columna distinta de la clave principal que es única. Por
ejemplo, una tabla Employees podría almacenar un identificador de empleado y un número de la
seguridad social, ambos exclusivos.
Clave externa: una columna que hace referencia a una columna única de otra tabla, como
CustomerID de la tabla Orders, que hace referencia a CustomerID en la tabla Customers.
Clave compuesta: una clave compuesta de más de una columna. Las claves compuestas no se
admiten en PowerPivot para Excel.
En PowerPivot para Excel, la clave principal o la tecla alternativa se conocen como la columna de
búsqueda relacionada, o simplemente columna de búsqueda. Si una tabla tiene una clave principal
y una alternativa, puede utilizar cualquiera de las dos como columna de búsqueda. La clave
externa se denomina columna de origen o simplemente columna. En nuestro ejemplo, se definiría
una relación entre CustomerID de la tabla Orders (la columna) y CustomerID (la columna de
búsqueda) en la tabla Customers. Si importa datos de una base de datos relacional, PowerPivot
para Excel elige de forma predeterminada la clave externa de una tabla y la clave principal
correspondiente de la otra. Sin embargo, puede utilizar cualquier columna que tenga valores
únicos como columna de búsqueda.
Tipos de relaciones
La relación entre Customers y Orders es una relación uno a varios. Cada cliente puede tener varios
pedidos, pero un pedido no puede tener varios clientes. Los otros tipos de relaciones son de uno a
uno y varios a varios. La tabla CustomerDiscounts, que define una tarifa reducida única para cada
cliente, tiene una relación de uno a uno con la tabla Customers. Un ejemplo de relación de varios a
varios es una relación directa entre Products y Customers, en la que un cliente puede comprar
varios productos y el mismo producto lo pueden comprar varios clientes. PowerPivot para Excel no
admite relaciones de varios a varios en la interfaz de usuario.
En la siguiente tabla se muestran las relaciones entre las tres tablas:
Relación Tipo Columna de búsqueda Columna
Customers-CustomerDiscounts
uno a uno
Customers.CustomerID CustomerDiscounts.CustomerID
Customers-Orders uno a varios
Customers.CustomerID Orders.CustomerID
Relaciones y rendimiento
Una vez creada una relación, la base de datos de PowerPivot para Excel normalmente debe
recalcular las fórmulas en que se usen columnas de las tablas de la relación recién creada. El
proceso puede tardar algún tiempo, en función de la cantidad de datos y la complejidad de las
relaciones.
Requisitos para las relaciones
PowerPivot para Excel tiene varios requisitos que se deben seguir al crear relaciones:
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 55 de 118 www.infoadmin.com.mx
Relación única entre tablas
Varias relaciones podrían producir dependencias ambiguas entre las tablas. Para crear cálculos
precisos, se necesita una única ruta de una tabla a la tabla siguiente. Por lo tanto, puede haber
solo una relación entre cada par de tablas. Si tiene dos tablas y varias relaciones entre ellas,
entonces deberá importar varias copias de la tabla que contenga la columna de búsqueda y crear
una relación entre cada par de tablas.
Una relación para cada columna de origen
Una columna de origen no puede participar en varias relaciones. Si ya ha usado una columna como
columna de origen en una relación, pero desea usar esa columna para conectar con otra columna
de búsqueda relacionada en una tabla diferente, puede crear una copia de la columna y emplearla
para la nueva relación.
Es fácil crear una copia de una columna que tiene los mismos valores exactos usando una fórmula
de DAX en una columna calculada.
Conceptos claves para el manejo de relaciones
Identificador único para cada tabla
Cada tabla debe tener una única columna que identifique de forma única cada fila de esa tabla. A
menudo se hace referencia a esta columna como la clave principal.
Columnas de búsqueda única
Los valores de datos de la columna de búsqueda deben ser únicos. En otras palabras, la columna
no puede contener duplicados. En PowerPivot para Excel, las cadenas nulas y vacías equivalen a un
valor en blanco, que es un valor de datos distinto. Esto significa que no puede tener varios valores
nulos en la columna de búsqueda.
Tipos de datos compatibles
Los tipos de datos de la columna de origen y de la columna de búsqueda deben ser compatibles.
Claves compuestas y columnas de búsqueda
Las claves compuestas no se pueden utilizar en un libro de PowerPivot; siempre debe tener
exactamente una columna que identifique de forma única cada fila de la tabla. Si intenta importar
tablas que tienen una relación existente basada en una clave compuesta, el Asistente para la
importación de tablas omitirá esa relación porque no se puede crear en PowerPivot.
Si desea crear una relación entre dos tablas en PowerPivot, y hay varias columnas que definen las
claves principales y las claves externas, debe combinar los valores para crear una columna de clave
única antes de crear la relación. Puede hacerlo antes de importar los datos, o hacerlo en
PowerPivot creando una columna calculada.
Relaciones varios a varios
PowerPivot para Excel no admite relaciones de varios a varios y no puede agregar simplemente
tablas de unión en PowerPivot. Sin embargo, puede usar funciones de DAX para modelar las
relaciones de varios a varios.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 56 de 118 www.infoadmin.com.mx
Autocombinaciones y bucles
Las autocombinaciones no se permiten en las tablas de PowerPivot. Una autocombinación es una
relación recursiva entre una tabla y ella misma. Las autocombinaciones se utilizan a menudo para
definir las jerarquías de elementos primarios y secundarios. Por ejemplo, podría unir una tabla
Employees a sí misma para generar una jerarquía que muestre la cadena de dirección en un
negocio.
PowerPivot para Excel no permite crear bucles entre relaciones en un libro. En otras palabras, se
prohíbe el conjunto siguiente de relaciones.
Tabla 1, columna a a Tabla 2, columna f
Tabla 2, columna f a Tabla 3, columna n
Tabla 3, columna n a Tabla 1, columna a
Si intenta crear una relación que crearía un bucle, se generará un error.
Detección automática e inferencia de las relaciones
Al importar los datos en la ventana de PowerPivot, el Asistente para la importación de tablas
detecta automáticamente las relaciones existentes entre las tablas. Además, al crear una tabla
dinámica, PowerPivot para Excel analiza los datos de las tablas. Detecta posibles relaciones que no
se han definido y sugiere columnas adecuadas para incluirlas en esas relaciones.
El algoritmo de detección usa datos estadísticos de los valores y metadatos de las columnas para
deducir la probabilidad de las relaciones.
Los tipos de datos de todas las columnas relacionadas deberían ser compatibles. Para la detección
automática, solo se admiten los tipos de datos de texto y números enteros.
Para que la relación se detecte correctamente, el número de claves únicas de la columna de
búsqueda debe ser mayor que los valores de la tabla del lado de "varios". Dicho de otro modo, la
columna de clave del lado de "varios" de la relación no debe contener ningún valor que no esté en
la columna de clave de la tabla de búsqueda. Por ejemplo, suponga que tiene una tabla de
productos con sus identificadores (la tabla de búsqueda) y una tabla de ventas con las ventas de
cada producto (el lado de "varios" de la relación). Si los registros de ventas contienen el
identificador de un producto que no tiene un identificador correspondiente en la tabla de
productos, la relación no se puede crear automáticamente, pero quizás pueda crearla
manualmente. Para que PowerPivot para Excel detecte la relación, primero debe actualizar la tabla
de búsqueda, la tabla de productos, con los identificadores de producto que falten.
Asegúrese de que el nombre de la columna de clave del lado de "varios" es parecido al nombre de
la columna de clave de la tabla de búsqueda. No es necesario que nombres sean exactamente
iguales. Por ejemplo, en las empresas, suele haber variaciones de los nombres de columnas que
contienen prácticamente los mismos datos: Emp ID, EmployeeID, Employee ID, EMP_ID, etcétera.
El algoritmo detecta los nombres parecidos y asigna una probabilidad más alta a las columnas con
nombres parecidos o exactamente iguales. Por consiguiente, para aumentar la probabilidad de
crear una relación, se puede cambiar el nombre de las columnas de los datos que se importen por
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 57 de 118 www.infoadmin.com.mx
nombres parecidos a los de las columnas de las tablas existentes. Si PowerPivot para Excel detecta
varias relaciones posibles, no crea ninguna.
Esta información podría ayudar a entender por qué no se detectan todas las relaciones, o cómo los
cambios realizados en los metadatos (por ejemplo, el nombre de campo y los tipos de datos)
podrían mejorar los resultados de la detección automática de relaciones.
Detección automática para los conjuntos con nombre
Las relaciones no se detectan automáticamente entre los campos relacionados y conjuntos con
nombre en una tabla dinámica. Puede crear estas relaciones manualmente. Si desea usar la
detección automática de relaciones, quite cada conjunto con nombre y agregue directamente los
campos individuales del conjunto con nombre a la tabla dinámica.
Inferencia de relaciones
En algunos casos, las relaciones entre las tablas se encadenan automáticamente. Por ejemplo, si
crea una relación entre los dos primeros conjuntos de tablas del ejemplo siguiente, se deduce que
existe una relación entre las otras dos tablas y se establece una relación automáticamente.
Products and Category -- creado manualmente
Category and SubCategory -- creado manualmente
Products y SubCategory -- la relación se deduce
Para que las relaciones se encadenen automáticamente, las relaciones deben ir en una dirección,
como se mostró antes. Si las relaciones iniciales fueran entre, por ejemplo, Sales y Products, y
Sales y Customers, no se deduciría una relación. Esto se debe a que la relación entre Products y
Customers es una relación de varios a varios.
Jerarquías Una jerarquía es una lista visible, una colección de columnas que crea como niveles secundarios
para colocar en cualquier orden en la jerarquía. Las jerarquías pueden aparecer por separado de
otras columnas en una herramienta de generación de informes, facilitando que los usuarios
seleccionen y naveguen por las rutas de acceso comunes de datos.
Las tablas pueden incluir docenas e incluso centenares de columnas con nombres complejos. Por
eso, los usuarios pueden tener dificultades para encontrar e incluir datos en un informe. El usuario
puede agregar toda la jerarquía (que consta de varias columnas) a un informe con un solo clic. Las
jerarquías también pueden proporcionar una vista sencilla e intuitiva de las estructuras de datos.
Por ejemplo, en una tabla Fecha puede crear una jerarquía Calendario. Año natural se usa como
nivel primario superior, incluyéndose Mes, Semana y Día como niveles secundarios (Año natural-
>Mes->Semana->Día). Esta jerarquía muestra una relación lógica de Año natural con Día.
Es posible incluir jerarquías en perspectivas. Las perspectivas definen subconjuntos visibles de un
modelo que ofrecen puntos de vista centrados, específicos del negocio o específicos de la
aplicación del modelo. Por ejemplo, una perspectiva puede ofrecer a los usuarios una jerarquía
que contiene solo los elementos de datos necesarios para sus requisitos específicos de informes.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 58 de 118 www.infoadmin.com.mx
Perspectivas Una de las ventajas de utilizar el complemento PowerPivot para perfeccionar un modelo de datos
es la capacidad de agregar perspectivas. Las perspectivas proporcionan vistas personalizadas que
se definen para un grupo de usuarios o escenario de negocios determinado, lo que facilita la
navegación en los conjuntos de datos grandes.
Puede incluir cualquier combinación de tablas, de columnas y de campos calculados (incluidos KPI)
en una perspectiva y puede crear perspectivas diferentes para los distintos elementos de los
informes de una organización.
Las perspectivas se pueden utilizar como origen de datos para otras tablas dinámicas e informes,
incluidos los de Power View. Al conectarse a un libro que incluye perspectivas, puede elegir una
perspectiva determinada en la página Seleccionar tablas y vistas del Asistente para la conexión de
datos.
NOTAS:
En el proceso de creación de una perspectiva, incluya siempre al menos un campo; de lo
contrario, quien use la perspectiva verá una lista de campos vacíos.
Elija solo los campos útiles en un informe. No es necesario seleccionar claves o columnas
con fines de navegación o de cálculo. Excel puede utilizar todo el modelo, aunque la
perspectiva no incluya elementos específicos.
Para modificar una perspectiva, active y desactive los campos en la columna de la
perspectiva, lo que agrega y quita campos de la perspectiva.
Al mantener el mouse sobre una celda de una perspectiva, aparecen botones que
permiten eliminar la perspectiva, cambiarle el nombre o copiarla.
Comportamiento de las tablas para informes de Power View El comportamiento de las tablas de PowerPivot permite establecer las propiedades del
comportamiento de las tablas que exponen filas de detalles en un nivel más específico. El
establecimiento de las propiedades del comportamiento de las tablas cambia el comportamiento
de agrupación de las filas de detalle y proporciona una mejor colocación predeterminada de la
información de identificación (como nombres, carnés con fotografía o imágenes de logotipo) en
contenedores, tarjetas y gráficos.
¿Por qué establecer las propiedades del comportamiento de la tabla?
Power View agrupa los elementos automáticamente según los campos y el formato de
presentación que esté usando. En la mayoría de los casos, la agrupación predeterminada genera
un resultado óptimo. Pero para algunas tablas, normalmente las que contienen datos detallados,
el comportamiento de agrupación predeterminado agrupará a veces filas que no deberían estar
agrupadas (por ejemplo, los registros de empleados o clientes que deben enumerarse
individualmente, en especial cuando dos o más personas comparten el mismo nombre y
apellidos). Para estas tablas, puede establecer propiedades que hagan que las filas se enumeren
individualmente en vez de agruparse.
NOTA: No cambie el comportamiento predeterminados en las tablas que actúan como tabla de
búsqueda (por ejemplo, una tabla de fechas, de categorías de producto o de departamentos,
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 59 de 118 www.infoadmin.com.mx
donde la tabla consta de un número relativamente reducido de filas y columnas) ni las tablas de
resumen que contienen filas que solo ofrecen interés cuando se resumen (por ejemplo, los datos
del censo acumulados por sexo, edad o ubicación geográfica). En las tablas de resumen y de
búsqueda, el comportamiento de agrupación predeterminado genera el mejor resultado.
Identificador de fila
En una tabla de PowerPivot, el identificador de fila especifica una columna única que contiene solo
valores únicos y ningún valor en blanco. La propiedad Identificador de fila se utiliza para cambiar la
agrupación de forma que los grupos no se basen en la composición de campos de una fila, sino en
una columna fija que se utilice siempre para identificar de forma única una fila,
independientemente de los campos que se usen en un diseño de informe determinado.
El establecimiento de esta propiedad cambia el comportamiento de agrupación predeterminada
de una agrupación dinámica basada en las columnas presentes en la vista a un comportamiento
fijo de agrupación que resume basándose en el identificador de fila. El cambio del
comportamiento de agrupación predeterminada es pertinente para los diseños de informe como,
por ejemplo, una matriz, que en caso contrario agruparía (o mostraría subtotales) para cada
columna de la fila.
Al establecer un identificador de fila se habilitan las siguientes propiedades adicionales: Mantener
filas únicas, Etiqueta predeterminada e Imagen predeterminada, cada una de las cuales afecta al
comportamiento de campo en Power View.
También puede utilizar Identificador de tabla como una propiedad independiente para habilitar:
El uso de imágenes binarias en los informes. Mediante la eliminación de la
ambigüedad en torno a la singularidad de las filas, Power View puede determinar
cómo asignar imágenes predeterminadas y etiquetas predeterminadas a una
determinada fila.
Quite los subtotales no deseados de un informe de matriz. La agrupación
predeterminada en el nivel de campo crea un subtotal para cada campo. Si desea que
solo se calcule un subtotal en el nivel de fila, con el establecimiento del identificador
de fila obtendrá este resultado.
No puede establecer un identificador de fila para las tablas marcadas como tablas de fechas. En las
tablas de fechas, el identificador de fila se especifica cuando se marca la tabla.
Propiedad Mantener filas únicas
Esta propiedad permite especificar las columnas que contienen información de identidad (como
un nombre de empleado o un código de producto) de manera que se distinga una fila de otra. En
los casos en que las filas parezcan idénticas (como dos clientes con el mismo nombre), las
columnas que especifique para esta propiedad se repetirán en la tabla de informe.
Dependiendo de qué columnas agregue al informe, puede que encuentre filas que se tratan como
filas idénticas porque los valores de cada fila parecen iguales (como dos clientes con el nombre Jon
Yang). Esto podría suceder porque otras columnas que proporcionan diferenciación (como el
segundo nombre, la dirección o la fecha de nacimiento) no están en la vista de informe. En este
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 60 de 118 www.infoadmin.com.mx
escenario, el comportamiento predeterminado consiste en contraer las filas idénticas en una sola,
que resume los valores calculados de las filas combinadas en un solo resultado mayor.
Si establece la propiedad Mantener filas únicas, podrá designar una o varias columnas para que se
repitan siempre, aunque haya instancias duplicadas, siempre que se agregue esa columna a la
vista de informe. Los valores calculados asociados a la fila ahora se asignarán según cada fila
individual en lugar de acumularlos en una sola fila.
NOTA: Debido a que las columnas que el usuario final seleccione pueden afectar a la agrupación,
que cambiará el contexto de filtro en los cálculos de las expresiones, los diseñadores de modelos
deben tener la precaución de crear campos calculados que devuelvan resultados correctos.
Propiedad etiqueta predeterminada
Esta propiedad especifica una etiqueta que aparece de forma destacada en una tarjeta o en un
gráfico, o junto con la imagen predeterminada en la franja de navegación de un informe en
mosaico. Cuando se usa con una imagen predeterminada, la etiqueta predeterminada aparece
debajo de la imagen. Al elegir una etiqueta predeterminada, seleccione la columna que
proporcione más información acerca de la fila (por ejemplo, un nombre).
En el diseño de la franja de pestañas para un contenedor de mosaico, con la franja de navegación
en la parte superior, la etiqueta predeterminada aparece en el área de título debajo de una
imagen, ya como se define mediante la propiedad Imagen predeterminada. Por ejemplo, si tiene
una lista de empleados, podría presentar en forma de mosaico la información de empleado,
usando el carné con la fotografía como imagen predeterminada y el nombre del empleado como
etiqueta predeterminada. La columna de etiqueta predeterminada siempre aparece debajo de la
imagen en la navegación de franja de pestañas de un contenedor de mosaico, incluso si no la
selecciona explícitamente en la lista de campos de informe.
En el diseño de flujo de carátulas de un contenedor de mosaico, con la navegación en la parte
inferior de los mosaicos, la imagen predeterminada aparece sin la etiqueta predeterminada.
En un diseño de tarjeta, la etiqueta predeterminada aparece en una fuente mayor en el área de
mosaico en la parte superior de cada tarjeta. Por ejemplo, si tiene una lista de empleados, podría
crear tarjetas con la información de empleado, usando el carné con la fotografía como imagen
predeterminada y el nombre del empleado como etiqueta predeterminada.
Propiedad imagen predeterminada
Esta propiedad especifica una imagen que se muestra de forma predeterminada en la franja de
navegación de pestañas de un informe en mosaico o de forma destacada bajo la etiqueta
predeterminada en la parte izquierda de una tarjeta. Una imagen predeterminada debe tener
contenido visual. Algunos ejemplos son: un carné con fotografía en la tabla de empleados, un
logotipo de cliente en una tabla de clientes o el contorno de un país en una tabla geográfica.
NOTA: Las imágenes se pueden extraer de direcciones URL en un archivo de imagen de un servidor
web o como datos binarios insertados en el libro. Si la imagen se basa en una dirección URL,
asegúrese también de establecer la columna como tipo de imagen para que Power View recupere
la imagen en lugar de mostrar la dirección URL como datos de texto en el informe.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 61 de 118 www.infoadmin.com.mx
Para indicar las columnas que contienen direcciones URL de imagen, establezca la propiedad
Dirección URL de la imagen de modo que Power View recupere el archivo de imagen. Para las
imágenes binarias, solo tiene que establecer la propiedad Identificador de tabla.
Comportamiento de agrupación predeterminada de las tablas de PowerPivot El comportamiento de agrupación predeterminada produce a veces un resultado que es lo
contrario de lo que se pensó; concretamente, las filas de detalles que están presentes en el
modelo no aparecen en el informe. De forma predeterminada, Power View agrupa las columnas
que se agregan a la vista. Si agrega Nombre de país al informe, cada país aparecerá una vez en la
vista, aunque la tabla base contenga miles de filas que incluyen varias instancias de cada nombre
de país. En este caso, el comportamiento de agrupación predeterminada genera el resultado
correcto.
Sin embargo, considere otro ejemplo en el que tal vez desee que aparezcan varias instancias de
una fila, ya que las filas subyacentes contienen, de hecho, datos sobre entidades distintas. En este
ejemplo, suponga que tiene dos clientes con el mismo nombre, Jon Yang. Si se usa el
comportamiento de agrupación predeterminada, en el informe solo aparecerá una instancia de
Jon Yang. Además, dado que en la lista solo aparece una instancia, el campo calculado Ingresos
anuales será la suma de ese valor para ambos clientes. En esta situación, donde los clientes que
comparten el mismo nombre son realmente personas distintas, el comportamiento de agrupación
predeterminada produce un resultado incorrecto.
Para cambiar el comportamiento de agrupación predeterminada, establezca las propiedades
Identificador de tabla y Mantener filas únicas. En Mantener filas únicas, elija la columna Apellido
para que este valor se repita para una fila, aunque ya aparezca en otra. Después de cambiar las
propiedades y volver a publicar el libro, puede crear el mismo informe, solo que en esta ocasión
verá los de clientes llamados Jon Yang con los Ingresos anuales correctamente asignados a cada
uno de ellos.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 62 de 118 www.infoadmin.com.mx
Por otro lado, cuando se muestra una tabla de detalles en una matriz, la agrupación
predeterminada proporciona un valor resumido para cada columna. Dependiendo de los objetivos
que tenga, podría haber más resúmenes de lo que le gustaría. Para cambiar este comportamiento,
puede establecer Identificador de tabla. No será necesario establecer más propiedades
adicionales; el identificador de fila es suficiente para cambiar la agrupación de forma que los
resúmenes que se calculen para cada fila se basen en su identificador único de fila.
Compare estas imágenes de antes y después que muestran cómo el establecimiento de esta
propiedad afecta al diseño de una matriz.
Antes: agrupación predeterminada basada en los campos de la matriz
Después: agrupación según el identificador de fila
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 63 de 118 www.infoadmin.com.mx
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 64 de 118 www.infoadmin.com.mx
INTERFAZ DE USUARIO DE POWERPIVOT El PowerPivot se puede instalar como un complemento gratuito de Excel 2010 y ya viene integrado
en la versión 2013 de Excel, en cualquiera de los dos casos, se presentará una cinta llamada
PowerPivot.
En la pestaña PowerPivot, haga clic en el botón Ventana de PowerPivot.
Desde esta interface, podrá trabajar con datos en una hoja de cálculo de Excel y en la ventana de
PowerPivot recién abierta. La ventana de la hoja de cálculo incluye características de Excel
conocidas, así como una pestaña de PowerPivot y una lista de campos de PowerPivot. La ventana
de PowerPivot incluye muchas funciones específicas para agregar tablas de datos y crear
relaciones entre ellas. Solo hay una aplicación en ejecución y todos los datos con los que trabaja
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 65 de 118 www.infoadmin.com.mx
están almacenados en el mismo archivo de libro. Sin embargo, las ventanas de las aplicaciones son
independientes. La ventana de PowerPivot se abre sobre Excel y las ventanas se muestran como
elementos independientes en la barra de tareas de Windows. Por el momento, vamos a trabajar
en la ventana de PowerPivot y, más adelante en el tema, volveremos a la ventana de Excel y a la
pestaña de PowerPivot.
Pestaña PowerPivot de las cintas de Excel
La pestaña PowerPivot de Excel proporciona herramientas para trabajar con tablas dinámicas,
campos calculados y tablas vinculadas, y para abrir la ventana de PowerPivot.
Las opciones de la pestaña PowerPivot están organizadas en grupos.
Grupo Modelo de datos
En este grupo sólo está disponible el botón administrar que se usará para iniciar la
ventana de PowerPivot en la cual se podrá entrar en la fase de construcción y
administración del modelo de datos a utilizar.
Grupo Cálculos
Los botones en el grupo Cálculos le permiten crear, ver, modificar y eliminar
campos calculados y KPI al modelo.
Grupo Alineación de segmentación de datos
Las segmentaciones de datos es una nueva manera de filtrar datos de tablas y/o tabla dinámica y
será muy útil porque indica claramente qué datos se muestran en su tabla tras aplicar filtros a los
datos. Básicamente una segmentación de datos es una forma visual de representar los filtros de
una tabla y/o tabla dinámica.
Ejemplo de segmentación aplicada a una tabla
Sin ningún filtro aplicado
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 66 de 118 www.infoadmin.com.mx
Con un filtro aplicado
Ejemplo de segmentación aplicada a una tabla dinámica
Sin ningún filtro aplicado
Con un filtro aplicado
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 67 de 118 www.infoadmin.com.mx
En este grupo sólo en encontramos dos opciones de alineación del recuadro de segmentación,
horizontal y/o vertical.
Grupo Tablas
Con este grupo puede crear y actualizar las tablas vinculadas desde Excel hacía el modelo de datos
de PowerPivot.
Crear tabla vinculada. Haga clic aquí para crear una nueva tabla vinculada en el libro de
PowerPivot. La tabla vinculada usa los datos de la tabla de datos de Excel actual.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 68 de 118 www.infoadmin.com.mx
Actualizar todo. Se usa para actualizar todas las tablas de PowerPivot vinculadas a las tablas de
Excel.
Después de crear una tabla vinculada, use la pestaña Tablas vinculadas de la ventana de
PowerPivot para administrar las tablas.
Grupo Relaciones
En este grupo se muestra el botón Detectar que se usará para detectar las relaciones que puedan
estar entre varias tablas de un modelo de datos de PowerPivot
Botón Configuración
Este botón permite abrir el Cuadro de diálogo Opciones y diagnóstico de PowerPivot. Este cuadro
de diálogo proporcionan opciones para crear un seguimiento que se puede usar para soporte
técnico; para cambiar el idioma que se usa en los menús, cuadros de diálogo y mensajes en la
ventana de PowerPivot; y para proporcionar comentarios que se pueden emplear para mejorar el
producto.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 69 de 118 www.infoadmin.com.mx
Cuadro de diálogo Agregar a modelo de datos El cuadro de diálogo Agregar a modelo de datos se abre si hace clic en el botón con el mismo
nombre de la ventana PowerPivot de Excel y no tiene una tabla seleccionada en una hoja de
cálculo de Excel. Después de hacer clic en Aceptar en este cuadro de diálogo, se da formato a su
selección como una tabla y la tabla vinculada se muestra en la ventana de PowerPivot.
Una tabla vinculada es una tabla que se ha creado en Excel, pero se ha vinculado a una tabla en la
ventana de PowerPivot. La ventaja de crear y mantener los datos en Excel, en lugar de
importarlos, es que puede continuar modificando los valores en la hoja de cálculo de Excel,
utilizando los datos para el análisis en PowerPivot.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 70 de 118 www.infoadmin.com.mx
¿Dónde están los datos de la tabla?: Especifique un intervalo de celdas para la tabla o haga clic en
el icono para abrir el cuadro de diálogo Selección de intervalo.
Mi tabla tiene encabezados: Desactive esta casilla si los datos seleccionados no incluyen
encabezados para la tabla.
La nueva tabla que aparece en la ventana de PowerPivot tiene siempre el mismo nombre que la
tabla de Excel. Por lo tanto, debe dar asignar a la tabla de Excel un nombre significativo antes de
crear la tabla vinculada. De forma predeterminada, Excel genera automáticamente los nombres
para las tablas (Tabla1, Tabla2, etc.), pero puede cambiar el nombre de las tablas con facilidad
utilizando la interfaz de Excel.
Si cambia el nombre de la tabla en Excel una vez se haya creado la tabla vinculada, se interrumpirá
el vínculo entre las tablas. Cuando intente actualizar los datos, aparece el cuadro de diálogo
Errores en las tablas vinculadas. Haga clic en Opciones para seleccionar una opción con el fin de
resolver el error. Si se cambia el nombre en la ventana de PowerPivot no afecta al vínculo.
Si cambia el nombre de los encabezados de columna de la tabla de Excel, los cambios se
actualizarán en la tabla vinculada. Sin embargo, si cambia el nombre de un encabezado de
columna en Excel que se utiliza en una relación de PowerPivot, debe crear de nuevo la relación de
PowerPivot.
Pestaña Inicio de las cintas de PowerPivot La pestaña Inicio le permite agregar nuevos datos, copiar y pegar datos de Excel y otras
aplicaciones, aplicar formato y ordenar y filtrar datos.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 71 de 118 www.infoadmin.com.mx
Portapapeles
Este grupo permite copiar y pegar los datos en el libro de PowerPivot actual.
Pegar. Permite pegar datos del Portapapeles en una nueva tabla en la ventana de
PowerPivot.
Pegar datos anexados. Permite agregar datos del Portapapeles al final de una tabla
existente en la ventana de PowerPivot.
Pegar datos reemplazados. Permite usar datos del Portapapeles para reemplazar datos de
una tabla existente del libro de PowerPivot.
Copiar. Permite copiar los datos seleccionados del libro al 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.
Obtener datos externos
Este grupo le permite conectarse a orígenes de datos externos e importar datos de esos orígenes.
Desde base de datos. Se usa para conectarse con SQL Server, Microsoft Access y cubos de
SQL Server Analysis Services, así como con libros de PowerPivot publicados en SharePoint.
Desde el informe. Se usa para conectarse a una fuente de distribución de datos que un
informe de Reporting Services pone a disposición.
Desde fuentes de distribución de datos. Se usa para conectarse a fuentes de distribución
de datos que se generan desde orígenes de datos en línea.
Desde texto. Permite recibir los datos de archivos de texto, por ejemplo, desde archivos
separados por comas y archivos delimitados por tabuladores.
De otros orígenes. Permite obtener datos de otros orígenes, como libros de Excel y las
siguientes bases de datos: SQL Azure, Almacenamiento de datos paralelo de SQL Server,
DB2, Informix, Oracle, Sybase y Teradata.
Actualizar. Permite actualizar uno o varios orígenes de datos que se usan 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.
El recalculo significa actualizar las columnas y tablas de un libro que contienen fórmulas.
Botón Tabla dinámica
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 datos de la ventana de PowerPivot.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 72 de 118 www.infoadmin.com.mx
Grupo Formato
Este grupo permite dar formato a datos de columnas y trabajar con tipos de datos.
Tipo de datos. Muestra el tipo de datos actual de la columna seleccionada. Haga clic en la
flecha de lista desplegable para ver una lista de tipos de datos y cambiar el tipo de datos.
Si el tipo de datos que elige es incompatible con el contenido de la columna, se muestra
un error.
Formato. Muestra el tipo de datos de la columna actualmente seleccionada. Si hay
disponibles otras opciones de formato, haga clic en la flecha desplegable para seleccionar
un nuevo formato.
Aplicar formato de moneda. Se abra el cuadro de diálogo Formato de moneda y
especificar el formato y el símbolo de moneda.
Aplicar formato de porcentaje. Todos los números de la columna actualmente
seleccionada se muestren como porcentajes.
Separador de miles. Muestra un separador de miles en todos los números de la columna
actualmente seleccionada.
Aumentar decimales y Disminuir decimales. Incrementar o disminuir el número de
posiciones decimales que se muestran para un número. Estas opciones no cambian el
valor ni aumentan su precisión, solo afectan al formato de presentación.
Grupo Ordenar y filtrar
El grupo le permite elegir los valores que se muestran en una tabla aplicando filtros y ordenando.
Ordenar de menor a mayor y Ordenar de A a Z. Permite ordenar de menor a mayor. Si está
ordenando números, se ordenarán de menor a mayor. Si está ordenando texto, se
ordenará de la A a la Z.
Ordenar de mayor a menor y Ordenar de Z a A. Permite ordenar de mayor a menor. Si está
ordenando números, se ordenarán de mayor a menor. Si está ordenando texto, se
ordenará de la Z a la A.
Borrar orden. Se usa para cancelar el orden y mostrar la columna en su orden natural, es
decir, el orden en que se importaron los datos.
Borrar todos los filtros. Quitar los filtros y ver todas las filas de la tabla. Esta opción solo
está disponible cuando se han aplicado filtros a una columna como mínimo.
Nota
Al hacer clic en este botón, se quitan todos los filtros. Para borrar solo los filtros deseados,
haga clic con el botón secundario en el encabezado de la columna que tiene el filtro,
seleccione Filtro y, a continuación, seleccione Borrar filtro de <nombre de columna>.
Grupo Cálculos
Este grupo le permite crear fácilmente algunos campos calculados básicos y crear KPI a partir de
campos calculados.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 73 de 118 www.infoadmin.com.mx
Botón Autosuma. Seleccione una columna y, a continuación, haga clic en Autosuma o en
una de las demás funciones del menú desplegable. El campo calculado aparecerá bajo la
columna en el Área de cálculo.
Cada vez que ponga una calculo con autosuma, vale la pena seleccionar dicho cálculo y
revisar la barra de fórmulas del PowerPivot
Crear KPI. Seleccione cualquier campo calculado en el Área de cálculo y, a continuación,
active el botón Crear KPI. Aparecerá el cuadro de diálogo Indicador clave de rendimiento
(KPI).
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 74 de 118 www.infoadmin.com.mx
En este cuadro de dialogo se configura la forma de presentar el estado del KPI,
incluyendo el valor de destino contra el cual se compara y el formato que se usará.
Grupo Ver
Este grupo le permite cambiar la forma de ver las tablas y columnas.
Vista de datos. Es la vista predeterminada basada en columnas de la ventana de
PowerPivot y se muestran las tablas de datos (cada una en una pestaña diferente) y el
área de Cálculos donde se integran los campos calculados y los KPI del modelo.
Vista de diagrama. Esta vista le permite ver las tablas en un diagrama visual. Esta vista es
ideal para crear y administrar relaciones y jerarquías.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 75 de 118 www.infoadmin.com.mx
Mostrar oculto. Permite para mostrar todos los objetos que están ocultos en las
herramientas cliente.
Área de cálculo. Permite mostrar u ocultar el Área de cálculo, que solo se muestra bajo las
columnas en la Vista de datos.
Pestaña Diseñar de las cintas de PowerPivot En esta cinta es donde puede cambiar propiedades de tablas, crear y administrar relaciones, y
modificar conexiones con orígenes de datos existentes. También puede agregar columnas y
cambiar cuándo se calculan los valores de columna.
Grupo Columnas
El grupo Columnas permite crear nuevas columnas y cambiar la manera en que las columnas se
muestran.
Agregar. Haga clic en esta opción para agregar una nueva columna en el extremo derecho
de la tabla actual.
Eliminar. Haga clic en esta opción para eliminar la columna o columnas seleccionadas
actualmente. No se pueden seleccionar varias columnas mediante Ctrl+clic, pero se
pueden seleccionar haciendo clic y arrastrando.
Inmovilizar y Liberar. Haga clic para mantener visible la columna actual mientras se
desplaza a otra área de la hoja de cálculo.
Ancho. Haga clic aquí para mostrar el cuadro de diálogo Ancho de columna, que permite
especificar el ancho de una columna seleccionada.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 76 de 118 www.infoadmin.com.mx
Grupo Cálculos
El botón Insertar función permite Insertar función y agregar una nueva columna con una función
de DAX determinada.
El botón Opciones de cálculo permite controlar la manera en que el libro realiza los recálcalos de
las fórmulas.
Calcular ahora. Cuando el libro está establecido en el modo de cálculo manual, haga clic
aquí para volver a calcular todo el libro.
Modo de cálculo automático. El valor predeterminado habilita el recalculo automático de
las fórmulas. Cualquier cambio realizado en los datos del libro que provocaría la
modificación del resultado de cualquier fórmula desencadenará el recalculo de toda la
columna que contiene una fórmula.
Modo de cálculo manual. Desactiva el recalculo automático. Haga clic en Calcular ahora
para recalcular las fórmulas. Se recomienda actualizar y validar el libro antes de guardarlo.
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.
El recalculo significa actualizar las columnas y tablas de un libro que contienen fórmulas.
Grupo Relaciones
El grupo Relaciones le permite crear y administrar las relaciones entre las tablas del libro de
PowerPivot.
Crear relación. Abre el Cuadro de diálogo Crear relación, que le permite crear una relación
entre tablas de datos. La relación establece cómo se deben relacionar los datos de las dos
tablas.
Administrar relaciones. Abre el Cuadro de diálogo Administrar relaciones, que le permite
ver, editar o eliminar relaciones existentes.
Propiedades de tabla
Permite abrir el cuadro de diálogo Editar propiedades de tabla, que le permite ver y modificar las
propiedades de tablas. Esto solo se aplica a las tablas que se importaron y no a las que se pegaron
directamente en la ventana de PowerPivot o que se vincularon con Tablas de Excel.
Marcar como tabla de fechas
Se utiliza para habilitar el filtrado de fechas dedicado en los informes.
Editar
Haga clic en Deshacer o en Rehacer según convenga. Si una acción no se puede deshacer o
rehacer, no estará disponible.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 77 de 118 www.infoadmin.com.mx
Pestaña Avanzadas de las cintas de PowerPivot La pestaña Avanzadas le permite crear o editar perspectivas, resumir una columna numérica
mediante una función de agregación y configurar propiedades de informes para una herramienta
cliente de generación de informes, como Power View.
Mostrar la pestaña Avanzadas
La pestaña avanzadas normalmente viene oculta, para activarla siga el siguiente procedimiento:
Haga clic en el botón Archivo situado a la izquierda de la pestaña Inicio. Se abrirá el
menú Archivo.
Haga clic en Cambiar a modo avanzado. Aparecerá la pestaña Avanzadas a la derecha
de la pestaña Diseño.
Grupo Perspectivas
Las perspectivas son niveles de metadatos que realizan el seguimiento de segmentos o conjuntos
de datos diferentes. Las perspectivas suelen definirse para un grupo de usuarios o un escenario de
negocios determinado, facilitando la navegación en conjuntos de datos grandes.
En este grupo podrás hacer uso del botón Crear y Administrar perspectivas y de un selector de
perspectiva previamente definidas.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 78 de 118 www.infoadmin.com.mx
Mostrar campos calculados implícitos
Los campos calculados implícitos son los que se crean arrastrando un campo hacia la lista Valores
de la Lista de campos de PowerPivot en Excel. Al contrario que los campos calculados explícitos,
que se crean como un cálculo personalizado independiente en el libro y puede ser reutilizadas por
varias tablas dinámicas o gráficos dinámicos en el mismo libro. Los campos calculados implícitos
son fáciles de crear y utilizar, pero no admiten el mismo nivel de funcionalidad que los campos
calculados explícitos. Por ejemplo, no puede crear un KPI basado en un campo calculado implícito
ni puede utilizar un campo calculado implícito en un informe de Power View.
De forma predeterminada, un campo calculado implícito no aparece en el Área de cálculo. Si desea
mostrar estos campos calculados en el Área de cálculo, haga clic en Mostrar campos calculados
implícitos. Un campo calculado implícito aparecerá en la misma tabla y bajo la misma columna en
la que basa.
Resumir por
Al seleccionar una función en la lista desplegable se establece el
comportamiento de agregación predeterminado para la Lista de campos de
PowerPivot y las herramientas cliente de generación de informes, como
Power View.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 79 de 118 www.infoadmin.com.mx
Botón Conjunto de campos predeterminados
Puede especificar qué campos se incluyen automáticamente en un informe cada vez que se utilice
la tabla. Elegir los campos, y el orden en el que aparecen, elimina pasos redundantes para los
autores de informes, porque una vez en una tabla, agrega tablas y campos al informe.
Botón Comportamiento de la Tabla
Permite establecer propiedades que afectan al diseño de informes en herramientas de generación
de informes como Power View, para ello hay que cambiar el comportamiento de agrupación
predeterminado de la tabla, y establecer las etiquetas e imágenes predeterminadas utilizadas en
los diseños de informe de tarjeta y gráfico.
Dirección URL de la imagen es una propiedad que puede establecer en una columna que contiene
una dirección URL a una imagen en un sitio de SharePoint o en el web. Si se establece la propiedad
se indica a Power View que recupere el archivo de imagen en lugar de devolver la dirección URL
como datos de texto en el informe.
Pestaña Tablas vinculadas de las cintas de PowerPivot
La pestaña Tablas vinculadas está disponible en la cinta PowerPivot en Herramientas de tabla,
cuando selecciona una tabla de PowerPivot vinculada a una tabla de Excel.
La pestaña Tablas vinculadas contiene las siguientes opciones.
Actualizar todo. Haga clic en esta opción para actualizar todas las tablas de PowerPivot vinculadas
a tablas de Excel.
Actualizar datos seleccionados. Haga clic en esta opción para actualizar solo la tabla de PowerPivot
seleccionada actualmente. Tenga en cuenta que la tabla debe estar vinculada a una tabla de Excel
para que se pueda actualizar; estas opciones no se aplican a las tablas de otros orígenes.
Tabla de Excel. Esta opción se usa para mostrar los nombres de todas las tablas disponibles en el
libro de Excel. Se puede escribir un nombre para ir a una tabla, si el nombre es de una tabla válida
dentro del libro de Excel actual.
Ir a la tabla de Excel. Haga clic en esta opción para pasar al libro de Excel y ver la tabla.
Modo de actualización. Haga clic en esta opción para establecer el modo de actualización para las
tablas vinculadas en Automático o Manual. Esto afecta al modo en que los cambios en una tabla
de Excel afectan a la tabla vinculada en la ventana de PowerPivot.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 80 de 118 www.infoadmin.com.mx
Área de cálculo de PowerPivot El Área de cálculo le permite ver campos calculados en una cuadrícula, y crear, editar y administrar
fácilmente campos calculados e indicadores clave de rendimiento (KPI) dentro del modelo.
Mostrar el Área de cálculo
Debe estar en la Vista de datos. En la pestaña Inicio de la ventana de PowerPivot, haga clic en Área
de cálculo en el área Ver. El Área de cálculo aparece debajo de las columnas de la tabla, y el botón
Área de cálculo está resaltado y activado.
Para ocultar el Área de cálculo, haga clic de nuevo en Cálculo en la pestaña de Inicio para que no
se resalte el botón Área de cálculo.
Mostrar u ocultar los campos calculados implícitos en el Área de cálculo
En la pestaña Avanzadas, el botón Mostrar campos calculados implícitos está resaltado y activado,
lo que indica que todos los campos calculados, incluso los campos implícitos que se crean
arrastrando campos al área Valores en la Lista de campos en Excel, están visibles en el Área de
cálculo. Mediante el botón Mostrar campos calculados implícitos, puede decidir si desea dejar los
campos calculados implícitos visibles en la ventana de PowerPivot. Si oculta los campos calculados
implícitos, todavía formarán parte del libro y se podrá hacer referencia a ellos en fórmulas DAX,
pero no podrá verlos en el Área de cálculo.
Cambiar el ancho de una celda del Área de cálculo
En la Vista de datos, con el Área de cálculo mostrada, apunte a una línea blanca que hay entre los
encabezados de columna de una tabla. El puntero cambiará y adoptará el icono de flecha doble.
Arrastre la línea de columna hacia la izquierda o hacia la derecha. Las celdas del Área de cálculo de
esa columna también cambiarán de ancho.
Para cambiar el alto de la vista del Área de cálculo
En la Vista de datos, con el Área de cálculo mostrada, apunte a la línea gris oscuro que hay entre el
área de columnas de la tabla y el Área de cálculo. El puntero cambiará y adoptará el icono de
flecha doble.
Arrastre la línea de columna hacia arriba o hacia abajo. Las celdas del Área de cálculo con valores
aparecen en la parte superior del Área de cálculo.
Esto es útil si tiene que ver muchos campos calculados.
Nota
La barra de desplazamiento siempre aparece en la parte derecha del Área de cálculo, porque en el
Área de cálculo hay un límite fijo de 100 celdas verticales.
Campos calculados
Un campo calculado (antes conocido como medida) es una fórmula que se crea específicamente
para su uso en una tabla dinámica (o en un gráfico dinámico) que usa datos de PowerPivot. Los
campos calculados pueden estar basados en funciones de agregación estándar, como COUNT o
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 81 de 118 www.infoadmin.com.mx
SUM, o puede definir su propia fórmula mediante DAX. Puede crear campos calculados para
columnas, y se muestran bajo las columnas relacionadas en el Área de cálculo.
Nota
Si crea un campos calculados en la ventana de Excel, dicho campo aparecerá bajo la columna
apropiada en el Área de cálculo de la ventana de PowerPivot cuando la inicie.
KPI (Key Performance Indicator)
Un KPI se basa en un campo calculado y está diseñado para ayudar a evaluar el valor, estado y
tendencia actuales de un campo calculado. El KPI mide el rendimiento del valor, definido por un
campo calculado base, con respecto a un valor de destino. Es posible extender un campo calculado
base a un KPI.
Vista de diagrama de PowerPivot La vista de diagrama le permite ver tablas organizadas visualmente, así como agregar y modificar
fácilmente relaciones y jerarquías.
Para mostrar la vista de diagrama, en la pestaña Inicio de la ventana de PowerPivot, haga clic en
Vista de diagrama en el área Ver.
Navegar por la vista de diagrama
En la barra de herramientas situada en la parte superior de la vista de diagrama puede filtrar por
perspectiva, restablecer el diseño, elegir los elementos que desea mostrar, navegar al minimapa
del diagrama, hacer zoom, ajustar el diagrama a la pantalla y volver al nivel de zoom del 100%
(tamaño original). También puede desplazarse por la vista de diagrama.
Para ordenar por perspectiva
En la lista Seleccionar perspectiva, seleccione una perspectiva por la que desee filtrar. En la vista
de diagrama solo se mostrarán las columnas de la perspectiva seleccionada.
Para volver a ver todas las tablas y columnas, en la lista Seleccionar perspectiva, seleccione
<Predeterminada>.
Para restablecer el diseño
Para restablecer el diseño original, haga clic en Restablecer diseño.
Haga clic en Restablecer diseño en el cuadro de diálogo de confirmación.
Para elegir qué elementos desea mostrar
Desactive Columnas para ocultar las columnas.
Desactive campos calculados para ocultarlos.
Desactive Jerarquías para ocultar las jerarquías.
Desactive KPI para ocultarlos.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 82 de 118 www.infoadmin.com.mx
Para navegar por el minimapa del diagrama: Haga clic en el botón Abrir minimapa o en el número
de porcentaje de zoom (cuando la información sobre herramientas muestre Abrir minimapa). El
minimapa se abrirá debajo del botón.
Arrastre la lente rectangular para navegar por el minimapa para cerrar el minimapa, haga clic
encima del minimapa (cuando la información sobre herramientas muestre Cerrar minimapa).
Para hacer zoom arrastre el control de zoom hacia la izquierda del número de porcentaje de zoom.
Para ajustar el diagrama a la pantalla haga clic en el botón Ajustar a la pantalla. La vista de
diagrama mostrará todo el diagrama en la pantalla.
Para volver al nivel de zoom del 100% haga clic en el botón Tamaño original. La vista de diagrama
volverá al nivel de zoom original.
Para desplazarse por la vista de diagrama cuando el diagrama no quepa en la pantalla, arrastre la
barra de desplazamiento vertical hacia arriba o hacia abajo y arrastre la barra de desplazamiento
horizontal hacia la izquierda o hacia la derecha o bien, cuando apunte al fondo del diagrama, gire
la rueda del mouse hacia delante o hacia atrás.
Para desplazar una tabla si las columnas no caben en la tabla, arrastre la barra de desplazamiento
vertical de la tabla hacia arriba o hacia abajo o bien, cuando apunte a la tabla, gire la rueda del
mouse hacia delante o hacia atrás.
Para crear una jerarquía en una tabla apunte al encabezado de la tabla y, a continuación, haga clic
en el botón Crear jerarquía o bien, haga clic con el botón secundario en el encabezado de la tabla
y, a continuación, haga clic en el botón Crear jerarquía.
Para maximizar el tamaño de una tabla apunte al encabezado de la tabla y, a continuación, haga
clic en el botón Maximizar. El tamaño de tabla llenará el lienzo de la vista de diagrama y las demás
tablas aparecerán más claras y desenfocadas.
Para restaurar la tabla a su tamaño anterior y navegar por el resto de la vista de diagrama, debe
hacer clic en el botón Restaurar.
Para crear una relación arrastre una columna de una tabla a una columna de otra tabla diferente.
Accesos directos del teclado Combinación de teclas Descripción
Clic con el botón
secundario
Se abre el menú contextual para la celda, columna o fila seleccionada.
CTRL+A Se selecciona la tabla completa.
CTRL+C Se copian los datos seleccionados.
CTRL+D Se elimina la tabla.
CTRL+M Se mueve la tabla.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 83 de 118 www.infoadmin.com.mx
CTRL+R Se cambia el nombre de la tabla.
CTRL+S Se guarda el archivo.
CTRL+Y Se rehace la última acción.
CTRL+Z Se deshace la última acción.
CRTL+Espacio Se selecciona la columna actual.
MAYÚS+Espacio Se selecciona la fila actual.
MAYÚS+Re Pág Se seleccionan todas las celdas de la ubicación actual hasta la última
celda de la columna.
MAYÚS+Av Pág Se seleccionan todas las celdas de la ubicación actual hasta la primera
celda de la columna.
MAYÚS+FIN Se seleccionan todas las celdas de la ubicación actual hasta la última
celda de la fila.
MAYÚS+Inicio Se seleccionan todas las celdas de la ubicación actual hasta la primera
celda de la fila.
CTRL+Re Pág Se mueve a la tabla anterior.
CTRL+Av Pág Se mueve a la tabla siguiente.
CRTL+Inicio Se mueve a la primera celda de la esquina superior izquierda de la tabla
seleccionada.
CTRL+Fin Se mueve a la última celda de la esquina inferior derecha de la tabla
seleccionada (la última fila de Agregar columna)
CTRL+Izquierda Se mueve a la primera celda de la fila seleccionada.
CTRL+Derecha Se mueve a la última celda de la fila seleccionada.
CTRL+Flecha arriba Se mueve a la primera celda de la columna seleccionada.
CTRL+Flecha abajo Se mueve a la última celda de la columna seleccionada.
CTRL+Esc Se cierra un cuadro de diálogo o se cancela un proceso, como una
operación de pegado.
ALT+Flecha abajo Se abre el Cuadro de diálogo del menú Autofiltro.
F5 Se abre el Cuadro de diálogo Ir a.
F9 Se recalculan todas las fórmulas de la ventana de PowerPivot.
En la siguiente tabla se especifican los tamaños y números máximos de diversos objetos definidos
en los componentes de PowerPivot.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 84 de 118 www.infoadmin.com.mx
Especificaciones de capacidad máxima
Objeto Especificación / Límite
Longitud del nombre de objeto 100 caracteres
Caracteres no válidos en un nombre . , ; ' ` : / \ * | ? " & % $ ! + = () [] {} < >
Número de tablas por base de datos PowerPivot
(231) - 1 = 2,147,483,647
Número de columnas y columnas calculadas por tabla
(231) - 1 = 2,147,483,647
Número de campos calculados calculados en una tabla
(231) - 1 = 2,147,483,647
Tamaño de la memoria de PowerPivot para guardar un libro
4GB = 4,294,967,296 bytes
Solicitudes simultáneas por libro 6
Conexiones a cubos locales 5
Número de valores distintos en una columna
1,999,999,997
Número de filas de una tabla 1,999,999,997
Longitud de la cadena 536,870,912 bytes (512 MB), equivalente a 268,435,456 caracteres Unicode (256 caracteres mega)
Advertencia Las excepciones al límite de la cadena se aplican a las siguientes funciones, donde las cadenas se limitan a 2,097,152 caracteres Unicode:
CONCATENATE y operador de concatenación de infijo
DATEVALUE
EXACT
FIND
FORMAT
LEFT
LEN
LOWER
MID
REPLACE
REPT, la restricción se aplica al parámetro de entrada y al resultado
RIGHT
SEARCH
SUBSTITUTE, la restricción se aplica al parámetro de entrada y al resultado
TIMEVALUE
TRIM
UPPER
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 85 de 118 www.infoadmin.com.mx
PROYECTO COMPLETO DE ANÁLISIS DE DATOS CON POWERPIVOT En esta sección , completará un escenario para analizar las ventas electrónicas internacionales. La
compañía ficticia, Contoso, se utiliza en todos los ejemplos.
Imagine que es analista de una compañía de electrónica denominada Contoso Electronics. Desea
examinar las ventas a lo largo del tiempo y compararlas según el tipo de producto, el año y el país.
En el transcurso de este tutorial, usará PowerPivot para:
Importar datos de varios orígenes.
Crear datos vinculados.
Crear relaciones entre los datos a partir de orígenes diferentes.
Cambiar nombres de columnas y crear columnas calculadas.
Crear jerarquías.
Crear tablas dinámicas y gráficos dinámicos.
Agregar segmentaciones.
Crear un campo calculado y un KPI.
Crear perspectivas.
Guardar la hoja de cálculo de Excel resultante.
Para seguir el tutorial, necesitará los datos de ejemplo para el tutorial de PowerPivot para Excel,
versión 2. Los datos de ejemplo que se utilizan en este tutorial son de la compañía ficticia Contoso
y están almacenados en bases de datos de Access y hojas de cálculo de Excel.
Agregar datos a un libro PowerPivot Como ocurre con cualquier libro de Excel, en PowerPivot para Excel puede agregar datos desde
muchos orígenes distintos, incluidas bases de datos relacionales, bases de datos
multidimensionales, fuentes de distribución de datos, tablas de Excel o informes de Reporting
Services. También puede agregar datos desde archivos del equipo local y usar datos que descargue
de Internet.
Sin embargo, a diferencia de lo que ocurre en Excel, después puede crear relaciones entre estos
datos para formar un conjunto de datos único y, a continuación, realizar análisis con ellos. No
tiene el límite de un millón de filas: PowerPivot permite agregar y trabajar con millones de filas de
datos de forma local, en función de la memoria física real disponible en su equipo.
Agregar datos utilizando el Asistente para la importación de tablas
Para crear una conexión a una base de datos de Access
1. Navegue hasta la ubicación del equipo donde descargó los ejemplos y abra el archivo
Stores. Utilizará los datos de los almacenes en una lección posterior.
2. En la ventana de Excel, en la pestaña PowerPivot, en el grupo Modelo de datos haga clic
en Administrar.
3. En la ventana de PowerPivot, en la pestaña Inicio, en el grupo Obtener datos externos,
haga clic en Desde base de datos y, a continuación, haga clic en De Access. Se iniciará el
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 86 de 118 www.infoadmin.com.mx
Asistente para la importación de tablas, que le guiará a través del proceso para establecer
una conexión a un origen de datos.
4. En el cuadro Nombre descriptivo de la conexión, escriba ContosoDB desde Access.
5. A la derecha del cuadro Nombre de la base de datos, haga clic en Examinar. Navegue hasta
la ubicación donde descargó los archivos de muestra, seleccione ContosoSales, haga clic
en Abrir y, a continuación, haga clic en Siguiente para continuar.
6. Compruebe que la opción Seleccionar en lista de tablas y vistas para elegir los datos que se
van a importar esté seleccionada y, a continuación, haga clic en Siguiente para mostrar
una lista de todas las tablas de origen dentro de la base de datos.
7. Active la casilla para las siguientes tablas: DimChannel, DimDate, DimProduct,
DimProductSubcategory y FactSales.
Las tablas FactSales y DimProduct que está importando de la base de datos de Access contienen
un subconjunto de datos de la base de datos original Contoso de SQL server: no se incluyen ventas
y productos de dos categorías (games y home appliances). Aplicará un filtro a una de las otras
tablas antes de importarla, para que elimine las mismas categorías. También filtrará algunas de las
columnas de la tabla DimProduct.
Filtrar los datos de las tablas antes de importar
1. Seleccione la fila para la tabla DimProduct y, a continuación, haga clic en Vista previa y
filtro. Se abrirá el cuadro de diálogo Vista previa de la tabla seleccionada mostrando todas
las columnas de la tabla DimProduct.
2. Desplácese hacia la derecha, desactive las casillas situadas encima de las columnas
correspondientes a todas las columnas, desde ClassID hasta StockTypeName (un total de
15 columnas; deténgase antes de UnitCost) y, a continuación, haga clic en Aceptar.
3. Observe que aparece Filtros aplicados en la columna Detalles del filtro de la fila
DimProduct. Si hace clic en ese vínculo, verá una descripción de texto de los filtros recién
aplicados.
4. Ahora seleccione la fila DimProductSubcategory y, a continuación, haga clic en Vista previa
y filtro.
5. Puesto que solo le interesan algunos de los productos, aplicará un filtro para importar
únicamente los datos de estas categorías.
6. Desplácese hacia la derecha. En la parte superior de la columna ProductCategoryKey, haga
clic en la flecha situada en el lado derecho de la celda, desplácese hacia abajo, anule la
selección de 7 y 8 y, a continuación, haga clic en Aceptar. Las categorías 7 y 8 incluyen
juegos y electrodomésticos, y no desea incluirlas en el análisis. La flecha de dicha celda
cambiará a un icono de filtro.
7. En la parte superior de la columna ProductSubcategoryDescription, desactive la casilla
situada en la parte izquierda de la celda. Puesto que las descripciones son casi idénticas a
los nombres, no es necesario importar ambas columnas, y si se eliminan las columnas
innecesarias se reducirá el tamaño del libro y se facilitará la navegación. Haga clic en
Aceptar.
Por último, importe los datos seleccionados. El asistente importará las relaciones entre las tablas
junto con sus datos.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 87 de 118 www.infoadmin.com.mx
Importar los datos de las columnas y las tablas seleccionadas
1. Revise las opciones seleccionadas. Si todo parece correcto, haga clic en Finalizar.
Mientras importa los datos, el asistente muestra cuántas filas se han capturado. Cuando
se hayan importado todos los datos, aparecerá un mensaje para indicarlo. Observe que
importó más de 2 millones de filas de la tabla FactSales.
2. Haga clic en Cerrar.
3. El asistente se cerrará y los datos aparecerán en la ventana de PowerPivot. Cada tabla se
ha agregado como una nueva pestaña en la ventana de PowerPivot. Si los datos cambian
en el origen, puede mantener actualizados los datos importados en la ventana de
PowerPivot si emplea la actualización de datos.
Agregar datos utilizando una consulta personalizada
En esta tarea, se conectará a una base de datos de Microsoft Access y utilizará una consulta
personalizada para importar los datos en el libro de PowerPivot.
Importar los datos de la base de datos de Access implica conectarse a la base de datos,
buscar el archivo de consulta y ejecutar la consulta para importar los datos en el libro de
PowerPivot.
Tenga presente que para utilizar estos datos en el análisis, tendrá que crear relaciones entre
los datos de la base de datos de Access y los demás datos del libro de PowerPivot.
Crear una conexión a una base de datos de Access
En la ventana de PowerPivot, en la pestaña Inicio, haga clic en Desde base de datos y, a
continuación, haga clic en De Access. Aparecerá el Asistente para la importación de tablas,
que le guiará por el proceso de establecer una conexión a un origen de datos.
En el cuadro Nombre descriptivo de la conexión, escriba Base de datos Access de categoría
del producto.
A la derecha del cuadro Nombre de la base de datos, haga clic en Examinar. Navegue hasta
la ubicación donde descargó los archivos de muestra, seleccione ProductCategories y haga
clic en Abrir.
Haga clic en Siguiente.
El procedimiento para utilizar el Asistente para la importación de tablas con el fin de importar
seleccionando elementos en una lista de tablas se describió en la lección anterior de este tutorial.
Así que, en lugar de duplicar esos pasos, utilizará una consulta personalizada para importar este
conjunto de datos de Access.
El Asistente para la importación de tablas le guía por los pasos. Puede importar una consulta,
copiar y pegar el texto de una consulta existente, o escribir una consulta nueva utilizando el
generador de consultas gráfico de PowerPivot. En esta lección, importará una consulta que recibió
del departamento de TI.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 88 de 118 www.infoadmin.com.mx
Usar la herramienta del diseñador de consultas para seleccionar los datos que se van a importar
1. Seleccione la opción Escribir una consulta para especificar los datos que se van a importar
y, a continuación, haga clic en Siguiente.
2. En el cuadro Nombre descriptivo de la consulta, escriba Consulta de la categoría del
producto.
3. Haga clic en Diseño para abrir el cuadro de diálogo Generador de consultas.
4. Haga clic en Importar y, a continuación, navegue hasta la ubicación del equipo donde
guardó los ejemplos.
5. Si no ve el archivo enumerado, haga clic en la flecha abajo del archivo y seleccione Todos
los archivos (*.*).
6. Seleccione SQLQuery y, a continuación, haga clic en Abrir.
7. La instrucción SQL aparecerá en la ventana. Esta consulta selecciona todos los datos de la
tabla ProductCategory salvo GAMES and TOYS y HOME APPLIANCES.
8. Haga clic en Aceptar, en Validar y, a continuación, en Finalizar. Aparecerá un resumen de
las columnas que se van a importar.
9. Cuando la importación haya finalizado, haga clic en Cerrar.
10. Los datos se muestran como una nueva tabla denominada Consulta en el libro de
PowerPivot. Puede mantener estos datos actualizados si efectúa una actualización. Si el
contenido de la base de datos de Access cambia, al actualizar se mantendrán actualizados
los datos de PowerPivot.
11. Cambie el nombre de la nueva tabla; para ello, haga clic con el botón secundario en la
pestaña Consulta y seleccione Cambiar nombre. Escriba ProductCategory y después haga
clic en Entrar. Si aparece un cuadro de diálogo de mensaje, haga clic en Aceptar.
Agregar datos usando copiar y pegar
En esta tarea, agregará datos a su libro de PowerPivot copiándolos de una hoja de cálculo
de Microsoft Excel y pegándolos en la ventana de PowerPivot.
Tenga presente que para utilizar estos datos en el análisis, tendrá que crear relaciones entre
los datos de la base de datos de Access y los demás datos del libro de PowerPivot.
Copiar y pegar desde una hoja de cálculo de Excel externa
El departamento de ventas tiene una hoja de cálculo de Excel que contiene la ubicación de las
áreas donde Contoso vende productos actualmente. Copiará los datos que necesita de esta hoja
de cálculo y los pegará en su libro de PowerPivot.
Para copiar y pegar desde una hoja de cálculo de Excel externa
1. Navegue hasta la ubicación del equipo donde descargó los ejemplos y haga doble clic en el
ejemplo Geography. Se abrirá una nueva hoja de cálculo de Excel y ya no estará en la
ventana de PowerPivot.
2. Resalte y copie las celdas A1 a J675 (incluida la fila de encabezados de columna).
Nota
Asegúrese de seleccionar solo este rango de celdas, y no filas y columnas enteras. Al
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 89 de 118 www.infoadmin.com.mx
seleccionar filas y columnas completas, se importan celdas vacías, lo que podría afectar a
su capacidad de crear las relaciones con estos datos.
3. De nuevo en la ventana de PowerPivot, en la pestaña Inicio, haga clic en Pegar. El cuadro
de diálogo Vista previa de pegado muestra la nueva tabla que se creará.
4. Escriba Geography en el cuadro de texto Nombre de la tabla.
5. Asegúrese de que los datos de la tabla sean correctos, compruebe que la opción Usar
primera fila como encabezados de columna esté seleccionada y, a continuación, haga clic
en Aceptar. La nueva tabla se creará en la ventana de PowerPivot.
Agregar datos utilizando una tabla vinculada de Excel
Una tabla vinculada es una tabla que se ha creado en una hoja de cálculo en la ventana de Excel,
pero que se ha vinculado a una tabla de la ventana de PowerPivot. La ventaja de crear y mantener
los datos en Excel, en lugar de importarlos o pegarlos, es que puede continuar modificando los
valores en la hoja de cálculo de Excel, mientras usa los datos para el análisis en PowerPivot.
1. En la ventana de Excel, apunte a cualquiera de las celdas de la hoja de cálculo Stores y
aplíquele formato de tabla (CTRL+T). Asegúrese de que esté seleccionada la opción Mi
tabla tiene encabezados. Haga clic en Aceptar.
2. La nueva tabla que aparecerá en la ventana de PowerPivot siempre tiene el mismo
nombre que la tabla de Excel. Por consiguiente, debería dar un nombre descriptivo a la
tabla de Excel antes de crear la tabla vinculada en PowerPivot. De forma predeterminada,
Excel genera automáticamente los nombres para las tablas (Tabla1, Tabla2, etc.), pero
puede cambiar fácilmente el nombre de las tablas usando la interfaz de Excel.
1. Mientras sigue en la ventana de Excel, haga clic en la pestaña Diseño.
2. En el área Propiedades, en Nombre de tabla:, escriba Stores.
3. En la ventana de Excel, en la pestaña PowerPivot, en el grupo Tablas haga clic en Agregar a
modelo de datos. Se abrirá la ventana de PowerPivot y se habrá creado una tabla nueva.
Observe el icono de vínculo de la pestaña.
4. En la pestaña Tabla vinculada de la ventana de PowerPivot, haga clic en Ir a la tabla de
Excel para volver a la tabla de origen en la ventana de Excel. Cambie el valor de la celda C2
de 35 a 37.
5. Vuelva a la ventana de PowerPivot. La fila correspondiente también se ha actualizado
ahora al nuevo valor.
Crear relaciones entre tablas Las relaciones de PowerPivot pueden crearse manualmente combinando tablas en la ventana de
PowerPivot o columnas en la Vista de diagrama, o bien de forma automática si PowerPivot para
Excel detecta relaciones existentes al importar datos en un libro de PowerPivot. Una relación se
crea manualmente combinando columnas de tablas diferentes que contengan datos similares o
idénticos. Por ejemplo, las tablas DimProduct y DimProductSubcategory están relacionadas por las
columnas ProductSubcategoryKey que se encuentran en ambas tablas. Las columnas no tienen
que tener el mismo nombre, aunque lo tienen a menudo.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 90 de 118 www.infoadmin.com.mx
¿Por qué crear relaciones?
Para realizar un análisis significativo, los orígenes de datos tienen que tener relaciones entre ellos.
Más específicamente, las relaciones lo habilitan para:
Filtrar los datos de una tabla por las columnas de datos de las tablas relacionadas.
Integrar las columnas de varias tablas en una tabla dinámica o un gráfico dinámico.
Buscar fácilmente los valores de tablas relacionadas utilizando fórmulas de expresiones de
análisis de datos (DAX).
Revisar las relaciones existentes
Ya tiene los datos de tres orígenes diferentes en el libro de PowerPivot:
Datos de ventas y productos importados de una base de datos de Access. Las relaciones
existentes se importaron automáticamente junto con los datos.
Datos de categorías de productos importados de una base de datos de Access.
Datos copiados de, y vinculados a, una hoja de cálculo de Excel que contiene información
del almacén.
Revisar las relaciones existentes
1. En la ventana de PowerPivot, en la pestaña Diseñar, en el grupo Relaciones, haga clic en
Administrar relaciones.
2. En el cuadro de diálogo Administrar relaciones, debería ver las siguientes relaciones, que
se crearon cuando se importó la primera base de datos de Access:
Tabla Tabla de búsqueda relacionada
DimProduct [ProductSubcategoryKey] DimProductSubcategory [ProductSubcategoryKey]
FactSales [channelKey] DimChannel [ChannelKey]
FactSales [DateKey] DimDate [Datekey]
FactSales [ProductKey] DimProduct [ProductKey]
3. Observe que puede crear, editar y eliminar relaciones desde este cuadro de diálogo. Haga
clic en Cerrar.
Crear nuevas relaciones entre los datos a partir de orígenes independientes
Ahora que ha revisado las relaciones que se crearon automáticamente, creará relaciones
adicionales.
Crear la primera relación
1. Haga clic en la pestaña de la tabla Stores.
2. Haga clic con el botón secundario en el encabezado de columna GeographyKey y, a
continuación, haga clic en Crear relación.
3. Los cuadros Tabla y Columna se rellenarán automáticamente.
4. En el cuadro Tabla de búsqueda relacionada, seleccione Geography (la tabla que pegó
desde una hoja de cálculo de Excel y cuyo nombre cambió).
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 91 de 118 www.infoadmin.com.mx
5. En el cuadro Columna de búsqueda relacionada, asegúrese de que esté seleccionada
GeographyKey.
6. Haga clic en Crear.
7. Cuando se crea la relación, se muestra un icono en la parte superior de la columna.
Apunte a la celda para mostrar los detalles de la relación.
Crear más relaciones entre los datos de Access y Excel
1. Haga clic en la pestaña Stores.
2. Seleccione la columna StoreKey.
3. En la pestaña Diseño, haga clic en Crear relación.
4. Los cuadros Tabla y Columna se rellenarán automáticamente.
5. En el cuadro Tabla de búsqueda relacionada, seleccione FactSales.
6. En el cuadro Columna de búsqueda relacionada, asegúrese de que esté seleccionada
StoreKey.
7. Observe el icono de información situado junto al cuadro Columna de búsqueda
relacionada. Esto le indica que esta relación se está creando en el orden equivocado. Al
crear una relación, debe seleccionar una columna con valores únicos para Columna de
búsqueda relacionada.
8. Invierta el orden. Seleccione FactSales en el cuadro Tabla y, a continuación, seleccione
StoreKey en el cuadro Columna. Seleccione Stores como Tabla de búsqueda relacionada y,
a continuación, seleccione StoreKey como Columna de búsqueda relacionada.
9. Haga clic en Crear.
Crear relaciones en la vista de diagrama
En la vista de diagrama, es fácil crear relaciones entre columnas de tablas distintas. Las relaciones
aparecen visualmente, lo que permite ver rápidamente cómo se relacionan todas las tablas entre
sí. En este paso, creará la última relación que necesitará para completar este tutorial utilizando la
vista de diagrama.
Navegar por la vista de diagrama
1. En la ventana de PowerPivot, en la pestaña Inicio, en el grupo Ver, haga clic en Vista de
diagrama. El diseño de hoja de cálculo de la vista de datos cambiará a un diseño de
diagrama visual y las tablas se organizarán automáticamente, según sus relaciones.
2. Para ver todas las tablas en la pantalla, haga clic en el icono Ajustar a la pantalla situado en
la esquina superior derecha de la vista de diagrama.
3. Para organizar una vista cómoda, use el control Arrastrar para zoom, el minimapa y
arrastre las tablas al diseño que prefiera. También puede emplear las barras de
desplazamiento y la rueda del mouse para desplazarse por la pantalla.
4. Apunte a la línea de relación (línea negra con una flecha y un círculo en los extremos) para
resaltar las tablas relacionadas.
Para crear una relación entre tablas en la vista de diagrama
1. Mientras sigue en la vista de diagrama, haga clic con el botón secundario en el diagrama
de la tabla DimProductSubcategory y, a continuación, haga clic en Crear relación. Se abrirá
el cuadro de diálogo Crear relación.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 92 de 118 www.infoadmin.com.mx
2. En el cuadro Columna seleccione ProductCategoryKey, en el cuadro Tabla de búsqueda
relacionada seleccione ProductCategory y en el cuadro Columna de búsqueda relacionada
seleccione ProductCategoryKey.
3. Haga clic en Crear.
4. Compruebe que todas las relaciones se han creado correctamente haciendo clic en
Administrar relaciones en la pestaña Diseño y revisando la lista.
5. Opcionalmente, puede reordenar las tablas de la vista diagrama ajustando la posición y el
tamaño de cada una de ellas, esto le permitirá una comprensión gráfica de su modelo de
datos.
Crear una columna calculada Creará una columna calculada denominada Beneficio total basándose en la información de
ingresos y gastos existente en sus datos. Además, también creará columnas calculadas en la tabla
DimProducts que hagan referencia a valores de categoría de producto de otras tablas.
Posteriormente, utilizará estas columnas relacionadas en una jerarquía nueva que incluya
categoría de product, subcategoría y nombres de producto. Como verá, la acción de agregar
columnas de tablas relacionadas mejora la exploración de tabla dinámica en jerarquías que
incluyen campos de otras tablas.
Crear una columna calculada para Beneficio total
1. En la ventana de PowerPivot, vuelva a Vista de datos y, a continuación, seleccione la tabla
FactSales.
2. En la pestaña Diseño, en el grupo Columnas, haga clic en Agregar.
3. En la barra de fórmulas situada encima de la tabla, escriba la siguiente fórmula.
Autocompletar sirve de ayuda para escribir los nombres completos de columnas y tablas, y
enumera las funciones que están disponibles.
=[SalesAmount] - [TotalCost] - [ReturnAmount]
4. Cuando termine de generar la fórmula, presione ENTRAR para aceptarla.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 93 de 118 www.infoadmin.com.mx
5. Los valores se rellenan para todas las filas de la columna calculada. Si se desplaza hacia
abajo por la tabla, verá que las filas pueden tener valores diferentes para esta columna,
basado en los datos que hay en cada fila.
6. Cambie el nombre de la columna haciendo clic con el botón secundario en
CalculatedColumn1 y seleccionando Cambiar nombre de columna. Escriba TotalProfit y
presione ENTRAR.
Crear columnas calculadas para datos relacionados
1. En la ventana de PowerPivot , en la vista de datos, seleccione la tabla DimProduct .
2. En la pestaña Diseño, en el grupo Columnas, haga clic en Agregar.
3. En la barra de fórmulas situada encima de la tabla, escriba la siguiente fórmula.
La función RELATED devuelve un valor de una tabla relacionada. En este caso, la tabla
ProductCategory incluye los nombres de las categorías de producto, que serán útiles para
que estén en la tabla DimProduct cuando genere una jerarquía que incluya información de
categoría.
=RELATED(ProductCategory[ProductCategoryName])
4. Cuando termine de generar la fórmula, presione ENTRAR para aceptarla.
5. Los valores se rellenan para todas las filas de la columna calculada. Si se desplaza hacia
abajo por la tabla, verá que cada fila tiene ahora un nombre de categoría de producto.
6. Cambie el nombre de la columna haciendo clic con el botón secundario en
CalculatedColumn1 y seleccionando Cambiar nombre de columna. Escriba
ProductCategory y después presione en ENTRAR.
7. En la pestaña Diseño, en el grupo Columnas, haga clic en Agregar.
8. En la barra de fórmulas situada encima de la tabla, escriba la siguiente fórmula y presione
ENTRAR para aceptar la fórmula.
=RELATED(DimProductSubcategory[ProductSubcategoryName])
9. Cambie el nombre de la columna haciendo clic con el botón secundario en
CalculatedColumn1 y seleccionando Cambiar nombre de columna. Escriba Product
Subcategory y después presione en ENTRAR.
Crear una jerarquía en una tabla La mayoría de los modelos incluyen datos que son intrínsecamente jerárquicos. Algunos ejemplos
comunes son los datos de calendario, datos geográficos y categorías de productos. Crear
jerarquías es útil porque puede arrastrar un elemento (la jerarquía) a un informe en lugar de tener
que ensamblarlo y ordenar los mismos campos repetidamente.
Las tablas pueden incluir docenas o incluso centenares de columnas. Por eso, los usuarios del
cliente pueden tener dificultades para encontrar e incluir datos en un informe. El usuario del
cliente puede agregar toda la jerarquía (que consta de varias columnas) a un informe con un solo
clic. Las jerarquías también pueden proporcionar una vista sencilla e intuitiva de las columnas. Por
ejemplo, en una tabla Fecha puede crear una jerarquía Calendario. Año natural se usa como nodo
primario superior, incluyéndose Trimestre, Mes y Día como nodos secundarios (Año natural-
>Trimestre>Mes->Día). Esta jerarquía muestra una relación lógica de Año natural con Día.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 94 de 118 www.infoadmin.com.mx
Es posible incluir jerarquías en perspectivas. Las perspectivas definen subconjuntos visibles de un
modelo que ofrecen puntos de vista centrados, específicos del negocio o específicos de la
aplicación del modelo. Por ejemplo, una perspectiva puede ofrecer a los usuarios una jerarquía
que contiene solo los elementos de datos necesarios para sus requisitos específicos de informes.
Puede crear una jerarquía usando las columnas y el menú contextual de la tabla o usando el botón
Crear jerarquía del encabezado de tabla en la vista de diagrama. Al crear una jerarquía, aparece un
nuevo nodo primario con las columnas seleccionadas como nodos secundarios.
Cuando se crea una jerarquía, se crea un nuevo objeto en el modelo. No mueva las columnas a
una jerarquía, ya que creará objetos adicionales. Es posible agregar una única columna a varias
jerarquías.
Crear una jerarquía desde el menú contextual
1. En la ventana de PowerPivot, cambie a la vista de diagrama. Expanda la tabla DimDate
para poder ver con más facilidad todos los campos.
2. Presione y mantenga presionada la tecla CTRL y haga clic en las columnas CalendarYear,
CalendarQuarter y CalendarMonth.
3. Para abrir el menú contextual, haga clic con el botón secundario en una de las columnas
seleccionadas. Haga clic en Crear jerarquía. Se creará un nodo primario de la jerarquía,
Jerarquía 1, en la parte inferior de la tabla y las columnas seleccionadas se copiarán bajo la
jerarquía como nodos secundarios.
4. Escriba Dates como nombre de la nueva jerarquía.
5. Arrastre la columna FullDateLabel debajo del nodo secundario CalendarMonth de la
jerarquía. Se creará un nodo secundario de las columnas y se colocará debajo del nodo
secundario CalendarMonth.
Crear una jerarquía desde el botón del encabezado de tabla
1. En la vista de diagrama, señale la tabla DimProduct y, a continuación, haga clic en el botón
Crear jerarquía del encabezado de tabla. Aparecerá un nodo primario de jerarquía vacío
en la parte inferior de la tabla.
2. Escriba Product Categories como nombre de la nueva jerarquía.
3. Para crear los nodos secundarios de jerarquía, arrastre las columnas Product Category,
Product Subcategory y ProductName a la jerarquía.
4. Recuerde que en la lección anterior agregó Product Category y Product Subcategory
mediante la creación de columnas calculadas que hacían referencia a estos campos de las
tablas relacionadas. Una de las ventajas de utilizar la función RELATED es que puede
buscar campos en la misma tabla, permitiendo crear jerarquías tales como Categories que
usan valores de otras tablas.
Editar una jerarquía
Puede cambiar el nombre de una jerarquía, cambiar el nombre de un nodo secundario, cambiar el
orden de los nodos secundarios, agregar columnas adicionales como nodos secundarios, quitar un
nodo secundario de una jerarquía, mostrar el nombre del origen de un nodo secundario (el
nombre de columna) y ocultar un nodo secundario si tiene el mismo nombre que el nodo primario
de la jerarquía.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 95 de 118 www.infoadmin.com.mx
Cambiar el nombre de una jerarquía o de un nodo secundario
1. Mientras sigue en la vista de diagrama, en la jerarquía Categories, haga clic con el botón
secundario en el nodo secundario FullDateLabel y, a continuación, haga clic en Cambiar
nombre. Escriba Date.
Observe que al hacer clic con el botón secundario en un nodo secundario de una jerarquía,
tiene varios comandos a su disposición para mover, cambiar de nombre u ocultar un
nombre de columna de origen.
2. Haga doble clic en la jerarquía primaria, Product Categories y cambie el nombre a solo
Categories.
Eliminar una jerarquía
Conserve las jerarquías en el libro para completar el tutorial, pero si desea eliminar una jerarquía
en algún momento, siga estos pasos.
Eliminar una jerarquía y quitar sus nodos secundarios
1. Mientras sigue en la vista de diagrama, en la tabla FactSales, haga clic con el botón
secundario en el nodo de la jerarquía primaria, Hierarchy Example 2 y, a continuación,
haga clic en Eliminar. (También puede hacer clic con el botón secundario en el nodo
primario de la jerarquía y, a continuación, presionar Supr.) Al eliminar la jerarquía también
se quitan todos los nodos secundarios.
2. Haga clic en Eliminar del modelo en el cuadro de diálogo para confirmar la acción.
Crear una tabla dinámica a partir de los datos 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.
NOTA IMPORTANTE: Cree siempre las tablas dinámicas desde la ventana de PowerPivot o desde la
pestaña PowerPivot de la ventana de Excel. También hay un botón Tabla dinámica en la pestaña
Insertar de la ventana Excel, pero las tablas dinámicas estándar de Excel no tienen acceso a sus
datos de PowerPivot.
Agregar una tabla dinámica al análisis
1. En la ventana de PowerPivot, en la pestaña Inicio de PowerPivot, haga clic en Tabla
dinámica.
2. Seleccione Nueva hoja de cálculo.
3. 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.
5. 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.
6. En la lista de campos de PowerPivot, recórrala y busque la tabla FactSales.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 96 de 118 www.infoadmin.com.mx
7. Seleccione el campo SalesAmount. Asegúrese de que este campo se muestra en la ventana
Valores de la lista de campos.
8. En la tabla DimChannel, seleccione el campo ChannelName. Mueva este campo a la
ventana Columna de la lista de campos.
9. En la tabla DimDate, seleccione la jerarquía Dates. Si fuera necesario, desplace esta
jerarquía al cuadro Etiquetas de fila.
10. Cambie el nombre de la tabla dinámica; para ello, haga doble clic en Sum of SalesAmount
en la primera celda, borre el texto actual y escriba Sales by Channel.
La tabla dinámica Sales by Channel ofrece una lista de la suma por trimestres de las ventas para
Contoso y para cada canal de ventas, desde el primer trimestre de 2007 hasta el cuarto trimestre
de 2009.
Expanda cada año para explorar en profundidad las cifras de ventas trimestrales, mensuales y
diarias.
Agregar otra tabla dinámica al análisis
1. En la ventana de PowerPivot, en la pestaña Inicio de PowerPivot, haga clic en Tabla
dinámica.
2. Seleccione Nueva hoja de cálculo.
3. Excel agrega una tabla dinámica vacía a la ubicación que especificó y muestra la lista de
campos de PowerPivot.
4. Seleccione la tabla dinámica vacía.
5. 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.
6. En la lista de campos de PowerPivot, descienda y busque la tabla FactSales.
7. Seleccione el campo TotalProfit. Asegúrese de que este campo se muestra en la ventana
Valores de la lista de campos.
8. En la lista de campos de PowerPivot, busque la tabla DimProduct.
9. Seleccione la jerarquía Categories . Asegúrese de que este campo se muestra en la
ventana Filas de la lista de campos.
10. En la lista de campos de PowerPivot, busque la tabla DimDate.
11. Arrastre el campo CalendarYear desde la lista de campos de tabla dinámica hasta la
ventana Columnas.
12. Cambie el nombre de la tabla dinámica, para ello haga doble clic en Suma de TotalProfit en
la primera celda, borre el texto actual y escriba Profit by Category.
La tabla dinámica Profit by Category ofrece una lista de la suma de los beneficios, por año, para
cada categoría de producto de Contoso.
Se trata de análisis simples de los datos. Para ahondar un poco más, agregará un gráfico dinámico
y segmentaciones de datos.
Agregar segmentaciones a una tabla dinámica
Las segmentaciones de datos son controles de filtrado que funcionan con un clic y reducen la parte
de un conjunto de datos que se muestra en las tablas dinámicas y en los gráficos dinámicos. Las
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 97 de 118 www.infoadmin.com.mx
segmentaciones de datos se pueden usar tanto en los libros Microsoft Excel como en los libros de
PowerPivot, para filtrar y analizar los datos de forma interactiva.
Pasos para agregar segmentaciones de datos a la tabla dinámica Profit by Category
1. Haga clic en cualquier parte dentro de la tabla dinámica Profit by Category para mostrar la
Lista de campos de PowerPivot.
2. En el área de herramientas de tabla dinámica de la cinta de opciones de Excel, dentro de la
cinta Analizar, seleccione el botón Insertar Segmentación de Datos del grupo Filtrar
3. En la ventana Insertar segmentación de datos, busque la tabla Geography y seleccione
ContinentName.
4. En la tabla DimChannel, seleccione ChannelName.
5. En la tabla DimProductSubcategory, seleccione ProductSubcategoryName.
6. Haga clic en Aceptar.
Dar formato a las segmentaciones de datos
1. Organice las segmentaciones de datos para que puedan verse todas. Para mover las
segmentaciones de datos, haga clic en el borde gris y arrástrelas.
Mueva hacia abajo el gráfico dinámico y colóquelo en el lateral para dejar espacio a las
segmentaciones de datos.
2. El título de la segmentación ProductSubcategoryName se trunca. Para dar formato a esta
segmentación de datos, haga clic con el botón secundario en ella y seleccione
Configuración de segmentación de datos.
1. En el cuadro Título, escriba Subcategory.
2. Compruebe que la opción Mostrar encabezado está seleccionada.
3. Haga clic en Aceptar.
3. A veces se debe cambiar el tamaño de las segmentaciones para mostrar su contenido
correctamente. Cambie el tamaño de la segmentación de datos Subcategory agregando
columnas.
1. Haga clic con el botón secundario en Product Subcategory y seleccione Tamaño y
Propiedades.
2. Resalte Posición y diseño.
3. En el desplegable Número de columnas, seleccione 2. Haga clic en Cerrar.
4. Arrastre las esquinas hasta que todo el contenido sea visible.
4. Continúe dando formato a las segmentaciones de datos según convenga.
Usar segmentaciones de datos para analizar los datos de la tabla dinámica
En Contoso deseamos evaluar nuestras tendencias de beneficios de ventas por canal. Basándonos
en los que descubramos, podemos tener que redistribuir los presupuestos de marketing y/o cerrar
canales.
1. En la segmentación de datos ChannelName, seleccione Catalog. Catalog debería ser ahora
el único elemento sombreado en la lista de la segmentación de datos.
2. Examinando la tabla dinámica ve que los beneficios de ventas por catálogo están
disminuyendo.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 98 de 118 www.infoadmin.com.mx
3. De uno en uno, haga clic en Online, Reseller y Store para revelar esas tendencias de
beneficios. Observa que los beneficios en línea están aumentando, los de almacén están
disminuyendo y los de distribuidor disminuyen ligeramente.
4. Borre los filtros que ha establecido haciendo clic en el icono de la esquina superior
derecha de la segmentación de datos.
5. Examine un poco más segmentando los datos de beneficios por subcategoría y continente.
Puede detectar varias cosas podría descubrir:
1. Los beneficios se han más que doblado accesorios de para móviles, televisiones y
lápices de grabación, con el mayor aumento de porcentaje en las ventas en línea.
Mientras que la mayoría de las ventas se realizó en almacenes, el aumento de
porcentaje de beneficios en almacén fue el más bajo. Dado que la mayoría de las
ventas proceden de almacenes ¿qué puede hacerse para maximizar los beneficios
en este sector?
2. Los beneficios de accesorios sufrieron un aumento significativo en 2009 en los
canales Reseller y Store. Antes de ello, el aumento de beneficio fue bastante
inocuo en esos dos canales. ¿A qué se puede atribuir este incremento?
3. Los beneficios generales de ventas de televisiones fueron más del doble en 2007-
2009. Sin embargo, la mayor parte del aumento se produjo en 2008, con muy
pequeño incremento de beneficios en 2009. ¿Por qué hubo tan pocos beneficios
en 2009 y cómo se pueden aumentar?
4. Las ventas de equipos de sobremesa se redujeron de forma significativa. Con unos
beneficios totales de casi 260 millones, solo 21 millones procedieron de ventas por
catálogo. Las ventas por catálogo se redujeron de casi 10 millones (2007) a 4
millones (2009). ¿Quizás Contoso debería cerrar este canal?
5. Los beneficios en Asia están aumentando mientras que en Norteamérica están
disminuyendo.
Contoso puede usar esta información, y mucho más, para tomar decisiones comerciales
inteligentes.
Ocultar columnas
Ahora que ha creado una jerarquía Categories y la ha colocado en DimProduct, ya no necesita
DimProductCategory o DimProductSubcategory en la lista de campos de tabla dinámica. En esta
tarea, aprenderá a ocultar tablas y columnas extrañas que ocupan espacio en la lista de campos de
tabla dinámica. Ocultando las tablas y las columnas, se mejora la experiencia de los informes sin
afectar al modelo que proporciona las relaciones y los cálculos de datos.
Puede ocultar columnas individuales, un intervalo de columnas o la tabla entera. Los nombres de
columna y de tabla se atenúan para reflejar que están ocultos para los clientes de informes que
usan el modelo. Las columnas ocultas se atenúan en el modelo para indicar su estado, pero
seguirán estando visibles en la Vista de datos para que pueda trabajar con ellas.
Pasos para ocultar tablas y/o columnas
1. En PowerPivot, asegúrese de que la vista de datos está seleccionada.
2. En las pestañas de la parte inferior, haga clic con el botón secundario en
DimProductSubcategory y seleccione Ocultar en las herramientas cliente.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 99 de 118 www.infoadmin.com.mx
3. Repita con ProductCategories.
4. Seleccione la tabla DimProduct.
5. Haga clic con el botón secundario en las columnas siguientes y haga clic en Ocultar en las
herramientas de cliente:
ProductKey
ProductLabel
ProductSubcategory
6. Repita este procedimiento con las demás tablas, quitando los identificadores, las claves u
otros detalles que no utilizará en este informe.
Crear un gráfico dinámico a partir de los datos PowerPivot Una vez que ha agregado los datos a un libro de PowerPivot, los gráficos dinámicos le ayudan a
resumir, analizar, explorar y presentar los datos eficazmente. Los gráficos dinámicos proporcionan
una representación gráfica interactiva de los datos y le ayudan a ver comparaciones, modelos y
tendencias.
Agregar un gráfico dinámico al análisis
1. Comience en la hoja de cálculo que contiene la tabla dinámica Sales by Channel.
2. Seleccione la tabla dinámica
3. En la pestaña Analizar de las herramientas de tabla dinámica de Excel, haga clic en el
botón Gráfico dinámico que está en el grupo Herramientas.
4. Seleccione el primer gráfico del grupo Línea y haga clic en Aceptar.
5. El gráfico dinámico y la tabla dinámica muestran ahora los mismos datos, con diseños
diferentes.
6. Dé formato a la presentación de los datos para que sea más sencillo leer y comparar. Haga
clic con el botón secundario en los números de eje y seleccione Formatos de eje.
7. Haga clic en Número y, en la lista Categoría, seleccione Moneda.
8. Configure Posiciones decimales en 0 y, a continuación, haga clic en Cerrar.
9. Haga clic con el botón secundario en el rótulo de eje Sum of SalesAmount y seleccione
Configuración de campo de valor.
10. Cambie Nombre personalizado a Sales by Channel y, a continuación, haga clic en Aceptar.
Agregar otro gráfico dinámico al análisis
1. Comience en la hoja de cálculo que contiene la tabla dinámica Profit by Category.
2. Seleccione la tabla dinámica
3. En la pestaña Analizar de las herramientas de tabla dinámica de Excel, haga clic en el
botón Gráfico dinámico que está en el grupo Herramientas.
4. Descienda y seleccione el primer tipo de gráfico circular y haga clic en Aceptar.
5. En el grupo Estilos de diseño, seleccione el estilo tridimensional con un fondo negro y haga
clic en Aceptar.
6. En el gráfico circular, seleccione el título Total y cámbielo a % de ganancia por categoría.
7. Luego, agregue y dé formato a las etiquetas de datos.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 100 de 118 www.infoadmin.com.mx
8. En el gráfico circular, haga clic con el botón secundario y seleccione Agregar etiquetas de
datos.
9. Vuelva a hacer clic con el botón secundario y seleccione Formato de etiquetas de datos.
10. Resalte Opciones de etiqueta,, active la casilla Porcentaje y anule la selección de Valor.
11. Haga clic en Cerrar. Cambie el tamaño del gráfico para asegurarse de que se muestran
todas las categorías de producto.
Agregar segmentaciones de datos a gráficos dinámicos
1. Haga clic en cualquier parte del gráfico dinámico de Profit by Category por categoría para
mostrar la Lista de campos de PowerPivot.
2. En la Lista de campos de PowerPivot, busque la tabla DimDate.
3. Arrastre CalendarYear y CalendarQuarter hasta el área Segmentaciones de datos
horizontales de la Lista de campos de PowerPivot. En la tabla Geography, seleccione
ContentinentName y arrástrelo hasta el área Segmentaciones de datos verticales de
la Lista de campos de PowerPivot.
Dar formato a las segmentaciones de datos
1. Organice las segmentaciones de datos para que se puedan ver todas ellas. Para mover las
segmentaciones de datos, haga clic en el borde gris y arrástrelas.
2. De forma predeterminada, las segmentaciones de datos se muestran por orden alfabético
y numérico, con los elementos sin datos en último lugar. Para cambiar esta vista:
1. Haga clic con el botón secundario en la segmentación de datos CalendarYear y
seleccione Configuración de segmentación de datos.
2. Desactive Mostrar elementos sin datos al final. Haga clic en Aceptar.
3. Continúe dando formato a las segmentaciones de datos según convenga.
Usar segmentaciones de datos para analizar los datos de gráficos dinámicos
1. Use la segmentación de datos CalendarYear para explorar los beneficios por año. El gráfico
dinámico muestra claramente el aumento de las cuotas de beneficio para COMPUTERS y
para TV and VIDEO (a costa de CAMERAS and CAMCORDERS) de 2007 a 2009. La cuota de
beneficio de otras categorías apenas muestran fluctuación.
2. Para un examen aún más detallado, use la segmentación de datos CalendarMonth.
Descubrirá que CAMERAS and CAMCORDERS tenían la máxima cuota de beneficio en los
últimos meses de 2007.
Dar formato a las segmentaciones de datos
1. Organice las segmentaciones de datos para que se puedan ver todas ellas. Para mover las
segmentaciones de datos, haga clic en el borde gris y arrástrelas.
2. De forma predeterminada, las segmentaciones de datos se muestran por orden alfabético
y numérico, con los elementos sin datos en último lugar. Para cambiar esta vista:
1. Haga clic con el botón secundario en la segmentación de datos CalendarYear y
seleccione Configuración de segmentación de datos.
2. Desactive Mostrar elementos sin datos al final. Haga clic en Aceptar.
3. Continúe dando formato a las segmentaciones de datos según convenga.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 101 de 118 www.infoadmin.com.mx
Usar segmentaciones de datos para analizar los datos de gráficos dinámicos
1. Use la segmentación de datos CalendarYear para explorar los beneficios por año. El gráfico
dinámico muestra claramente el aumento de las cuotas de beneficio para COMPUTERS y
para TV and VIDEO (a costa de CAMERAS and CAMCORDERS) de 2007 a 2009. La cuota de
beneficio de otras categorías apenas muestran fluctuación.
2. Para un examen aún más detallado, use la segmentación de datos CalendarMonth.
Descubrirá que CAMERAS and CAMCORDERS tenían la máxima cuota de beneficio en los
últimos meses de 2007.
Crear un campo calculado y un KPI Crearemos un campo calculado que evalúe las ventas de las tiendas, otro que calcule las ventas de
las tiendas del último año y un tercer capo calculado que utiliza las dos anteriores para calcular el
crecimiento anual. Utilizará este último campo como base para un KPI que indique si el
crecimiento anual es superior, igual o inferior al previsto como objetivo. La creación de los campos
calculados es un requisito para crear un KPI.
Crear un campo calculado que calcule las ventas de las tiendas
1. En la vista de datos de la ventana de PowerPivot, haga clic en la pestaña de la tabla
FactSales en la parte inferior de la ventana. En la práctica, puede colocar medidas en
cualquier tabla, pero para simplificar el proceso, utilizará la tabla FactSales como inicio
lógico para todas las agregaciones que creemos.
2. Muestre el área de cálculo. El área de cálculo es una cuadrícula situada en la parte inferior
de cada tabla. Contendrá los campos calculados implícitos o explícitos que vaya creando.
Para mostrar el Área de cálculo, haga clic en Área de cálculo en la pestaña Inicio.
3. Haga clic en la primera celda del Área de cálculo. Se da la circunstancia de que está debajo
de la columna SalesKey. Los campos calculados que vaya creando serán independientes de
las columnas de la tabla. Elegimos la primera columna de la cuadrícula para mayor
comodidad con el fin de ver más fácilmente nuestros campos calculados sin tener que
desplazarnos a través de la cuadrícula.
4. En la barra de fórmulas, escriba el nombre StoreSales.
5. Después, escriba un signo de dos puntos y empiece a escribir la fórmula =CALCULATE(). A
medida que escriba, las fórmulas relacionadas aparecerán bajo la barra de fórmulas.
6. Haga doble clic en la fórmula CALCULATE. La fórmula se rellenará como =CALCULATE en la
barra de fórmulas. Se mostrará CALCULATE(Expression, *Filter1+, *Filter2+, …) debajo de la
barra de fórmulas.
7. Empiece a escribir SUM. Haga doble clic en SUM cuando la función de autocompletar la
muestre.
8. Escriba FactSales[SalesAmount+), DimChannel*ChannelName+=”Store”) para completar la
fórmula.
9. Compare su fórmula con la fórmula siguiente. Preste mucha la atención a la posición de
los paréntesis y los corchetes para evitar errores de sintaxis, también preste mucha
atención a las comillas dobles ya que si intenta copiar y pegar desde este documento hacia
PowerPivot se podrían pasar mal:
StoreSales:=CALCULATE(SUM(FactSales[SalesAmount]), DimChannel[ChannelName]="Store")
10. Presione ENTRAR para aceptar la fórmula.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 102 de 118 www.infoadmin.com.mx
Crear un campo calculado que calcule las ventas del último año
1. En el Área de cálculo, debajo de la columna SalesKey, haga clic en la segunda celda de la
parte superior (debajo de StoreSales) y, a continuación, en la barra de fórmulas, escriba la
siguiente fórmula:
StoreSalesPrevYr:=CALCULATE([StoreSales], DATEADD(DimDate[Datekey], -1, YEAR))
2. Presione ENTRAR para aceptar la fórmula.
Crear un campo calculado que calcule el crecimiento anual
1. En el Área de cálculo, debajo de la columna SalesKey, haga clic en la tercera celda de la
parte superior (debajo de StoreSalesPrevYr) y, a continuación, en la barra de fórmulas,
escriba la siguiente fórmula:
StoreSalesPrevYr:=CALCULATE([StoreSales], DATEADD(DimDate[Datekey], -1, YEAR))
2. Presione ENTRAR para aceptar la fórmula.
Ahora debe tener tres campos calculados que le servirán para usarlas como base para el KPI.
Aplicar un formato a los campos calculados
3. En el Área de cálculo, debajo de la columna SalesKey, haga clic con el botón secundario en
StoreSales y seleccione Formato.
4. En el cuadro de diálogo Formato, seleccione Moneda y, a continuación, haga clic en
Aceptar.
5. Haga clic con el botón secundario en StoreSalesPrevYr, seleccione Formato, seleccione
Moneda y haga clic en Aceptar.
6. Haga clic con el botón secundario en YOYGrowth, seleccione Formato, seleccione Número
y elija Porcentaje. Haga clic en Aceptar.
Creación de un KPI Uno de los requisitos para crear un Indicador clave de rendimiento (KPI) es crear primero un
campo calculado base que se evalúe como un valor. Después extenderá el campo calculado base a
un KPI. En este tutorial, creará un KPI basándose en el último campo calculado que creó,
YOYGrowth. Utilizará este campo calculado para agregar umbrales que indiquen si el rendimiento
de las tiendas durante el último año ha sido superior, igual o inferior al previsto como objetivo.
Pasos para crear un KPI
1. Asegúrese de que está en la vista de datos de la tabla FactSales. Si el Área de cálculo no se
muestra, en la pestaña Inicio, haga clic en Área de cálculo.
2. En el Área de cálculo, debajo de la columna SalesKey, haga clic con el botón secundario en
la medida YOYGrowth, que servirá de campo calculado base (valor). Dado que este campo
calculado es un porcentaje, utilizará valores absolutos para indicar si el porcentaje es
superior o inferior al previsto como objetivo.
3. En el menú contextual del campo calculado, haga clic en Crear KPI (también puede hacer
clic en Crear KPI en la pestaña Inicio del área Medidas). Aparecerá el cuadro de diálogo
Indicador clave de rendimiento (KPI).
Nota
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 103 de 118 www.infoadmin.com.mx
Crear KPI solo está disponible para las medidas que cree mediante los métodos descritos
anteriormente. Si crea un campo calculado en Excel arrastrando un campo desde un tabla
al área Valores de la lista de campos de PowerPivot, será un campo calculado implícito y
no se podrá utilizar como base de un KPI.
1. En Definir valor de destino, seleccione Valor absoluto y escriba 0.
2. En Definir umbrales de estado, haga clic y deslice el valor de
umbral inferior hasta -0.05
umbral superior hasta 0.05.
Los umbrales de estado indican que el crecimiento negativo del 5% marca el rango
inferior y el crecimiento positivo del 5% marca el principio del rango superior.
3. En Seleccionar estilo de icono, haga clic en el estilo de icono de semáforos.
4. En Seleccionar estilo de icono, haga clic en Descripciones y escriba Crecimiento anual de
las tiendas en el cuadro Descripción de KPI.
5. Haga clic en Aceptar para crear el KPI. Se mostrará el icono de KPI en el lado derecho de la
celda YOYGrowth en el Área de cálculo.
Crear una perspectiva La creación de perspectivas servirá como base para la generación de un informe de ventas de las
tiendas. Las perspectivas son subconjuntos de tablas y columnas del modelo que hacen un
seguimiento de distintos conjuntos de datos. Las perspectivas suelen definirse para un grupo de
usuarios o un escenario de negocios determinado (por ejemplo, para un equipo de ventas),
facilitando la navegación en conjuntos de datos grandes.
Pasos para agregar una perspectiva
1. En la ventana de PowerPivot, asegúrese de que está en modo avanzado.
2. En la pestaña Avanzadas, haga clic en el botón Crear y administrar del grupo Perspectivas.
Aparecerá el cuadro de diálogo Perspectivas.
3. Para agregar una perspectiva nueva, haga clic en Nueva perspectiva.
4. Si crea una perspectiva vacía con todos los objetos de campo, un usuario que use esta
perspectiva verá una lista de campos vacía. Las perspectivas deben contener al menos una
tabla y una columna para que sean útiles.
5. Escriba Perspectiva de ventas como nombre para la nueva perspectiva. El nombre es un
campo obligatorio.
6. Seleccione el campo StoreName de la tabla Stores para incluirla en la perspectiva.
7. Seleccione la jerarquía Categories de la tabla de DimProduct .
8. En la tabla DimDate, seleccione CalendarYear.
9. En la tabla Geography seleccione ContinentName.
10. Haga clic en el botón de expandir situado en el lado izquierdo de la tabla FactSales para
ver las columnas individuales de la tabla y seleccione las columnas siguientes: StoreSales,
StoreSalesPrevYr y YOYGrowth.
11. Haga clic en Aceptar para agregar la nueva perspectiva y cerrar el cuadro de diálogo
Perspectivas.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 104 de 118 www.infoadmin.com.mx
12. Para cambiar el nombre de la perspectiva, haga doble clic en el encabezado de columna (el
nombre de la perspectiva) o haga clic en el botón Cambiar nombre y, a continuación,
cambie el nombre a Informe de ventas.
Usar las segmentaciones y los KPI para analizar los datos PowerPivot En Contoso deseamos evaluar las ventas anuales de las tiendas por territorios. Basándonos en lo
que encontremos, podemos revisar los presupuestos de marketing y/o cerrar tiendas para mejorar
los números.
1. En la segmentación ContinentName, seleccione Asia. Los KPI proporcionan un indicador
visual que nos permite identificar rápidamente qué tiendas están por debajo de los valores
previstos como objetivo.
2. Haga clic en North America para revelar tendencias descendentes de ese mercado. Como
se puede observar a partir de los KPI, parece haber diferencias de mercado que superan el
rendimiento de cada tienda individual, con una reducción ampliamente generalizada para
la mayoría de tiendas.
3. Para analizar más a fondo las tendencias, agreguemos la jerarquía Categories al análisis.
Expanda DimProduct y arrastre Categories al área de filas.
La adición de Categorías de producto nos muestra que para muchas tiendas de
Norteamérica, el sector de audio se encuentra por encima del valor previsto como
objetivo, mientras que otras categorías están de forma habitual por debajo del mismo. ¿A
qué podemos atribuir este patrón?
4. Para Europa, los KPI nos muestran un patrón diferente, con tiendas específicas que
superan o no consiguen el objetivo en todas las categorías. Una investigación más
minuciosa nos indicará si necesitamos cerrar tiendas en esa región, o adoptar las
estrategias de ventas de las tiendas con mejores resultados de forma generalizada en todo
el canal.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 105 de 118 www.infoadmin.com.mx
POWER VIEW, HERRAMIENTA PARA EXPLORAR, VISUALIZAR Y
PRESENTAR LOS DATOS Power View permite una experiencia de exploración de datos, visualización y presentación
interactiva que fomenta la elaboración intuitiva de informes ad hoc. Power View está ahora
disponible en Microsoft Excel 2013. Es también una característica de Microsoft SharePoint Server
2010 y 2013 como parte del complemento SQL Server 2012 Service Pack 1 Reporting Services para
Microsoft SharePoint Server Enterprise Edition.
Con Power View puede interactuar con los datos:
En el mismo libro de Excel que la hoja de Power View.
En los modelos de datos de los libros de Excel publicados en una galería de PowerPivot.
En los modelos tabulares implementados en las instancias de SQL Server 2012 Analysis
Services (SSAS).
Si abre un libro de Excel 2010 en Excel 2013 e intenta insertar una hoja de Power View, es posible
que aparezca un mensaje indicando que el libro tiene un modelo de datos de PowerPivot creado
con una versión anterior del complemento PowerPivot. En este caso, puede actualizar el libro para
poder agregar una hoja de Power View. Sin embargo, no podrá abrir el libro en Excel 2010 después
de actualizarlo.
Características generales de Power View
Dos versiones de Power View
Los informes de Power View en SharePoint son archivos de RDLX. En Excel, las hojas de Power
View forman parte de un libro XLSX de Excel. No puede abrir un archivo RDLX de Power View en
Excel ni abrir archivos XLSX de Excel con las hojas de Power View en SharePoint. Tampoco puede
copiar gráficos u otras visualizaciones del archivo RDLX en el libro de Excel.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 106 de 118 www.infoadmin.com.mx
Sin embargo, puede guardar archivos XLSX de Excel con hojas de Power View en SharePoint, tanto
en instalaciones locales como en Office 365 y, a continuación, abrir dichos archivos en SharePoint.
Ambas versiones de Power View necesitan que Silverlight esté instalado en el equipo.
Un informe de Power View siempre está presentable: puede examinar los datos y mostrarlos en
cualquier momento, porque se trabaja con datos reales. No necesita obtener una vista previa del
informe para ver el aspecto que tiene.
Power View en SharePoint tiene los modos de presentación lectura y pantalla completa, en los que
se ocultan la cinta de opciones y otras herramientas de diseño con el fin de proporcionar más
espacio para las visualizaciones. El informe sigue siendo totalmente interactivo, con la capacidad
de filtrado y resaltado.
Basado en un modelo de datos
En Excel 2013, puede utilizar los datos directamente de Excel como base para Power View en Excel
y SharePoint. Cuando agrega tablas y crea relaciones entre ellas, Excel crea un modelo de datos en
segundo plano. Puede continuar modificando y mejorando el mismo modelo de datos en
PowerPivot de Excel, para crear un modelo de datos más sofisticado para los informes de Power
View.
También puede crear informes de Power View basados en un modelo tabular que se ejecuta en un
servidor de SQL Server 2012 Analysis Services (SSAS).
Los modelos tabulares y de datos actúan como un puente entre las complejidades de los orígenes
de datos back-end y su perspectiva de los datos. El nivel semántico del modelo de datos significa
que todos los elementos de Power View del informe trabajan juntos.
Crear gráficos y otras visualizaciones
En Power View, puede crear rápidamente diversas visualizaciones, desde tablas y matrices a
gráficos circulares, de burbujas y de barras, y conjuntos de varios gráficos. Para cada visualización
que desee crear, comience con una tabla que, a continuación, se convierte con facilidad en otras
visualizaciones, para encontrar cuál ilustra mejor los datos. Para crear una tabla, haga clic en una
tabla o campo en la lista de campos, o arrastre un campo de la lista de campos a la vista. Power
View dibuja la tabla en la vista, muestra los datos reales y agrega automáticamente encabezados
de columna.
Para convertir una tabla en otras visualizaciones, haga clic en un tipo de visualización en la pestaña
Diseño. Power View se habilita solo los gráficos y otras visualizaciones que funcionan mejor para
los datos de esa tabla. Por ejemplo, si Power View no detecta ningún valor numérico agregado,
ningún gráfico está habilitado.
Filtrado y resaltado de datos
Power View proporciona varias maneras de filtrar datos. Power View usa los metadatos del
modelo de datos subyacente para conocer las relaciones entre las diferentes tablas y campos de
un libro o de un informe. Debido a estas relaciones, puede usar una visualización para filtrar y
resaltar todas las visualizaciones en una hoja o en una vista. O bien, puede mostrar el área de
filtros y definir los filtros que se aplican a una visualización individual o a todas las visualizaciones
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 107 de 118 www.infoadmin.com.mx
de una hoja o de una vista. Con Power View en SharePoint, puede dejar el panel Filtro visible u
ocultarlo antes de cambiar al modo de lectura o de pantalla completa.
Segmentaciones de datos
Las segmentaciones de datos en Excel permiten comparar y evaluar los datos desde perspectivas
diferentes. Las segmentaciones de Power View son similares. Si tiene varias segmentaciones en
una vista y selecciona una entrada en una segmentación, esa selección filtra las otras
segmentaciones de datos en la vista.
Ordenamiento de datos
Puede ordenar las tablas, matrices, gráficos de barras y columnas, y conjuntos de múltiplos
pequeños en Power View. Las columnas se ordenan en tablas y matrices, las categorías o valores
numéricos en gráficos, y los diversos campos o valores numéricos en un conjunto de múltiplos. En
cada caso, puede usar el orden ascendente o descendente en los atributos, como Nombre de
producto, o en los valores numéricos, como Ventas totales.
Rendimiento
Para mejorar el rendimiento, Power View solo recupera los datos que necesita en un momento
dado para una visualización de datos. De esta forma, aunque una tabla en la hoja o vista se base
en una tabla subyacente del modelo de datos que contenga millones de filas, Power View captura
solo los datos de las filas visibles en la tabla en un momento determinado. Si arrastra la barra de
desplazamiento al final de la tabla, observe que vuelve a emerger de modo que puede desplazarse
hacia abajo a medida que Power View recupera más filas.
Compartir Power View en Excel
Puede guardar los libros de Excel en un sitio de SharePoint 2013 con Excel Services en SharePoint
o aplicaciones web de Excel, locales o en la nube. Otros pueden ver e interactuar con las hojas de
Power View en los libros que haya guardado allí.
Puede ocultar hojas individuales en un libro de Excel, de modo que podría ocultar el resto de hojas
en un libro y dejar visibles solo las hojas de Power View.
Las hojas de Power View se pueden conectar a distintos modelos de datos
En Excel 2013, cada libro puede contener un modelo de datos interno que se puede modificar en
Excel, en PowerPivot e incluso en una hoja de Power View en Excel. Un libro puede contener solo
un modelo de datos interno y una hoja de Power View puede basarse en el modelo de datos de
ese libro o en un origen de datos externo. Un único libro de Excel puede contener varias hojas de
Power View y cada una de las hojas se puede basar en un modelo diferente de datos.
Cada hoja de Power View tiene sus propios gráficos, tablas y otras visualizaciones. Puede copiar y
pegar el gráfico u otra visualización de una hoja a otra, pero solo si ambas hojas se basan en el
mismo modelo de datos.
Modificar el modelo de datos interno sin abandonar la hoja de Power View
Se pueden crear hojas de Power View y un modelo de datos interno en un libro de Excel 2013, si
basa la hoja de Power View en el modelo de datos interno, puede hacer algunos cambios en el
modelo de datos mientras está en la hoja de Power View. Por ejemplo:
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 108 de 118 www.infoadmin.com.mx
En la Lista de campos de Power View en Excel, puede crear relaciones entre las diferentes
tablas del libro.
Si el modelo de datos de Excel tiene campos calculados, puede crear indicadores clave de
rendimiento (KPI) basados en esos campos y agregarlos al informe de Power View.
Gráficos circulares
Los gráficos circulares son simples o sofisticados en Power View. Puede hacer un gráfico circular
que muestre información detallada al hacer doble clic en un solo segmento o un gráfico circular
que muestre subsegmentos dentro de los segmentos de color mayores. Puede aplicar un filtro
cruzado a un gráfico circular con otro gráfico. Suponga que hace clic en una barra en un gráfico de
barras. La parte del gráfico circular que se aplica a la barra se resalta y el resto del gráfico circular
se atenúa.
Mapas
Los mapas de Power View usan mosaicos de mapas de Bing, de modo que puede hacer zoom y
crear panorámicas igual que con cualquier otro mapa de Bing. Las ubicaciones y los campos son
elementos del mapa: cuanto mayor sea el valor, mayor será el punto. Cuando agrega una serie de
varios valores, obtiene gráficos circulares en el mapa.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 109 de 118 www.infoadmin.com.mx
Indicadores clave de rendimiento (KPI)
Puede agregar indicadores clave de rendimiento (KPI) al informe de Power View para mostrar el
progreso de los objetivos si el modelo de datos en que el informe de Power View se basa los tiene.
Jerarquías
Si el modelo de datos tiene una jerarquía, puede usarla en Power View. Por ejemplo, el modelo de
datos podría tener una jerarquía denominada Location, que consta de los campos Continent >
Country/Region > State/Province > City. En Power View puede agregar un campo a la vez a la
superficie de diseño o puede agregar Location y obtener todos los campos de la jerarquía al mismo
tiempo.
Si el modelo de datos no tiene una jerarquía, también puede crear uno en Power View. Puede
colocar campos en cualquier orden en una jerarquía.
También puede usar las jerarquías de los modelos tabulares de SQL Server Analysis Services.
Detalle y resumen
Puede agregar detalle o resumen a un gráfico o matriz en Power View para que muestre solo un
nivel a la vez. Los lectores del informe exploran en profundidad para obtener detalles o rastrean
agrupando datos para obtener un resumen.
Cuando una matriz tiene varios campos en las filas o las columnas, puede establecerla para
mostrar niveles y así se contrae la matriz para mostrar solo el nivel superior o más externo. Puede
hacer doble clic en un valor de ese nivel para expandir y mostrar los valores debajo de ese en la
jerarquía. O bien, puede hacer clic en la flecha arriba para volver a rastrear agrupando datos.
Los gráficos de barras, de columnas y circulares funcionan de la misma manera. Si un gráfico tiene
varios campos en el cuadro Eje, puede configurarlo para que muestre los niveles y solo verá un
nivel a la vez, comenzando con el nivel superior. La flecha arriba de la esquina lo devuelve al nivel
anterior.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 110 de 118 www.infoadmin.com.mx
Formatear informes con estilos, temas y cambio de tamaño del texto
Power View tiene nuevos temas de informe. Al cambiar el tema, el nuevo tema se aplica a todas
las vistas de Power View en el informe o las hojas del libro.
Power View para SharePoint Server 2010 ofrecía ocho temas de acentos básicos que controlaban
los colores del gráfico.
Power View en Excel 2013 y en SharePoint Server ofrece 39 temas adicionales con varias paletas
de gráficos, así como fuentes y colores de fondo.
También puede cambiar el tamaño de texto para todos los elementos del informe.
Fondos e imágenes de fondo
Puede establecer el fondo de cada vista del blanco al negro, con varias opciones de degradado. En
los fondos más oscuros, el texto cambia de negro al blanco de modo que se resalte mejor.
También puede agregar imágenes de fondo a cada hoja o vista. Busque un archivo de imagen en la
máquina local o en cualquier otro lugar y aplíquelo como imagen de fondo para una hoja o una
vista. Después puede configurarlo para encajarlo, ampliarlo, colocarlo en mosaico o centrarlo, y
establecer su transparencia entre 0 % (invisible) y 100 % (totalmente opaco). El libro de trabajo o
el informe almacena una copia de la imagen.
Puede combinar el fondo y la imagen para lograr diversos efectos.
Hipervínculos
Puede agregar un hipervínculo a un cuadro de texto en una hoja o en una vista. Si un campo del
modelo de datos contiene un hipervínculo, agregue el campo a la hoja o a la vista. Puede
vincularse a cualquier dirección de Internet o de correo electrónico.
En Power View en Excel y en el modo de edición para un informe de Power View en SharePoint,
siga el hipervínculo haciendo clic en él mientras mantiene presionada la tecla CTRL.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 111 de 118 www.infoadmin.com.mx
Puede hacer clic en el vínculo para un libro de Excel en Servicios de Excel u Office 365, o para un
informe de Power View en SharePoint en los modos de lectura y de pantalla completa.
Impresión
Puede imprimir las hojas de Power View en Excel y las vistas en SharePoint. En ambos casos, lo
que imprime es lo que se ve en la hoja o en la vista cuando lo envía a la impresora. Si la hoja o la
vista contienen una región con una barra de desplazamiento, la página impresa contiene la parte
de la región que está visible en la pantalla. Si una hoja o una vista contienen una región con
mosaicos, el mosaico seleccionado es el que se imprime.
Compatibilidad con los idiomas de derecha a izquierda
Power View en Excel y SharePoint admite ahora idiomas que se escriben de derecha a izquierda.
Power View en SharePoint tiene ahora opciones para establecer la dirección predeterminada para
las nuevas vistas y la dirección para una vista específica existente. A menos que la cambie, la
dirección es la misma que la dirección para SharePoint.
Power View en Excel toma el valor de la dirección predeterminada de Excel. Puede cambiar estos
valores. En Excel, vaya a Archivo > Opciones > Avanzadas y busque Dirección predeterminada. En
el mismo cuadro de diálogo también puede cambiar la dirección para una hoja específica, sin
cambiar la dirección predeterminada.
Control de los enteros
En Power View, para convertir una tabla en un gráfico es necesario agregar al menos una columna
de datos.
En Power View de Excel 2013 y en SharePoint Server, Power View agrega tanto los números
decimales como los enteros, de forma predeterminada. El diseñador de modelos de datos todavía
puede especificar otro comportamiento predeterminado, pero ese es el predeterminado.
Compatibilidad con versiones anteriores y posteriores de Power View
Los archivos RDLX de Power View en SharePoint son compatibles con las versiones anteriores, lo
que significa que, si guardó un archivo de Power View con el complemento SQL Server 2012
Reporting Services, puede abrirlo y guardarlo en Power View en SharePoint 2010 o SharePoint
2013 con el complemento SQL Server 2012 Service Pack 1 (SP 1) Reporting Services. Sin embargo,
no funciona a la inversa; es decir, no puede abrir un archivo RDLX de Power View de la versión más
reciente en versiones anteriores de SharePoint con un complemento SQL Server Reporting
Services anterior.
Power View y los modelos de datos
Los modelos de datos de SQL Server Analysis Services y Power View son compatibles con las
versiones anteriores y posteriores entre sí:
Puede basar un archivo de Power View en SharePoint 2010 con el complemento de SQL Server
2012 Reporting Services en un modelo de datos de Excel 2013 o en un modelo tabular de SQL
Server 2012 SP1 Analysis Services, y viceversa. Sin embargo, algunas características como las
jerarquías y los KPI solo están disponibles si basa un informe de Power View en SharePoint Server
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 112 de 118 www.infoadmin.com.mx
con el complemento SQL Server 2012 SP1 Reporting Services en un modelo de datos de Excel 2013
o en un modelo tabular de SQL Server 2012 SP1 Analysis Services.
Power View y Excel Services
Power View y Excel Services son compatibles con las versiones anteriores pero no con las
posteriores:
SharePoint 2013 es compatible con las versiones anteriores de los libros PowerPivot de
Excel 2010. Si carga un libro PowerPivot de Excel 2010 en SharePoint 2013, puede abrirlo
en Servicios de Excel y también basar un informe de Power View en él.
SharePoint 2010 no es compatible con las versiones posteriores de los modelos de datos
de libros de Excel 2013. Si carga un libro de Excel 2013 con un modelo de datos en
SharePoint 2010, podría no funcionar correctamente en Excel Services y no puede basar
un informe de Power View en él.
El Generador de informes y el Diseñador de informes
Power View no reemplaza los productos de informes de Reporting Services existentes.
El Diseñador de informes es un entorno de diseño sofisticado que los desarrolladores y los
profesionales de TI pueden utilizar para crear informes incrustados en las aplicaciones. En el
Diseñador de informes, pueden crear informes de operaciones, orígenes de datos compartidos,
conjuntos de datos compartidos y controles del visor de informes del autor.
En el Generador de informes, los profesionales de TI y los usuarios avanzados pueden crear
informes de operaciones eficaces, así como elementos de informe y conjuntos de datos
compartidos reutilizables.
El Generador de informes y el Diseñador de informes crean informes RDL; Power View crea
informes RDLX. Power View no puede abrir informes RDL y viceversa.
NOTA: Los informes RDL se pueden ejecutar en servidores de informes en modo nativo de
Reporting Services o en modo de SharePoint. Los informes RDLX de Power View solo se pueden
ejecutar en servidores de informes en modo de SharePoint.
El Diseñador de informes y el Generador de informes se incluyen en SQL Server Service Pack 1
2012 Reporting Services, junto con Power View.
Crear un informe de Power View Los informes de tabla dinámica no son el único tipo de informe que se beneficia de un modelo de
datos. Si usa el mismo modelo recién generado, puede agregar una hoja de Power View para
probar algunos de los diseños que proporciona.
1. En Excel, haga clic en Insertar > Power View.
2. En los campos Power View, haga clic en la flecha junto a la tabla FactSales y luego en
SalesAmount.
3. Expanda la tabla Geography y haga clic en RegionCountryName.
4. Seleccione el área del informe de Power View (puedes dar clic encima de cualquier país)
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 113 de 118 www.infoadmin.com.mx
5. Se mostrará la cinta DISEÑAR, en dicha cinta active el botón Mapa del grupo Cambiar
Visualización
Aparece un informe de mapa. Arrastre una esquina para cambiar su tamaño. En el mapa, los
círculos azules de diferente tamaño indican rendimiento de ventas para los distintos países o
regiones.
Bajo estas condiciones, el mapa será 100% interactivos, puedes jugar con el ZOOM y revisar los
detalles de ventas por regiones.
Optimizar para informes de Power View Si realiza unos pocos cambios al modelo, se producirán respuestas más intuitivas a la hora de
diseñar un informe de Power View. En esta tarea, agregará las direcciones URL de sitio web para
varios fabricantes y luego categorizará dichos datos como una dirección URL web para que esta se
muestre como vínculo.
Como primer paso, agregue direcciones URL al libro.
1. En Excel, abra una hoja nueva y copie estos valores:
ManufacturerURL ManufacturerID
http://www.contoso.com Contoso, LTD
http://www.adventure-works.com Adventure Works
http://www.fabrikam.com Fabrikam, Inc.
2. Formatee las celdas como una tabla y luego asigne el nombre URL a la tabla.
3. Cree una relación entre URL y la tabla que contiene los nombres de fabricante,
DimProduct:
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 114 de 118 www.infoadmin.com.mx
1. Haga clic en Datos > Relaciones. Aparecerá el cuadro de diálogo Crear relaciones.
2. Haga clic en Nuevo.
3. En Tabla, seleccione DimProduct.
4. En Columna, seleccione Manufacturer.
5. En Tabla relacionada, seleccione URL.
6. En Columna relacionada (principal), seleccione ManufacturerID.
Para comparar los resultados de antes y después, inicie un nuevo informe de Power View y
agregue FactSales | SalesAmount, dimProduct | Manufacturer, and URL | ManufacturerURL a un
informe. Observe que las direcciones URL aparecen como texto estático.
La representación de una dirección URL como hipervínculo active requiere una categorización.
Para categorizar una columna, usará PowerPivot.
1. En PowerPivot, abra URL.
2. Seleccione ManufacturerURL.
3. Haga clic en Avanzadas > Propiedades de informe > categoría de datos: sin categoría.
4. Haga clic en la flecha abajo.
5. Seleccione Dirección URL de web.
6. En Excel, haga clic en Insertar > Power View.
7. En los campos Power View, seleccione FactSales | SalesAmount, dimProduct |
Manufacturer, y URL | ManufacturerURL. Esta vez, las direcciones URL se muestran como
hipervínculos reales.
Otras optimizaciones de Power View son la definición de un conjunto de campos predeterminados
para cada tabla y establecer las propiedades que determinan si las filas de datos repetidos se
agregan o se muestran de forma independiente.
Establecer los campos predeterminados
Un conjunto de campos predeterminado es una lista predefinida de campos que se agregan
automáticamente a Power View al hacer clic en la tabla primaria en la lista de campos de informes.
Puede crear un conjunto de campos predeterminado para eliminar los pasos redundantes para los
informes que usan muchos campos específicos. Por ejemplo, si sabe que la mayoría de los
informes acerca de contactos de cliente siempre incluyen un nombre de contacto, un número del
teléfono principal, una dirección de correo electrónico y un nombre de compañía, puede
seleccionar previamente esas columnas para que siempre se agreguen a la vista del informe
cuando el autor haga clic en la tabla Customer Contact.
Después de crear un conjunto de campos predeterminado, puede influir aún más en la experiencia
de diseño de informes especificando etiquetas predeterminadas, imágenes predeterminadas, el
comportamiento del grupo predeterminado o si las filas que contienen el mismo valor se agrupan
en una fila o se enumeran individualmente.
Configurar propiedades del comportamiento de las tablas para informes Power View
Si va a usar Power View, puede usar el complemento Power Pivot para establecer las propiedades
del comportamiento de las tablas que exponen filas de detalles en un nivel más específico. El
establecimiento de las propiedades del comportamiento de las tablas cambia el comportamiento
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 115 de 118 www.infoadmin.com.mx
de agrupación de las filas de detalle y proporciona una mejor colocación predeterminada de la
información de identificación (como nombres, carnés con fotografía o imágenes de logotipo) en
contenedores, tarjetas y gráficos.
Necesitará especificar el identificador de fila para poder establecer otras opciones pero, una vez
hecho, puede establecer algunas o todas las propiedades restantes.
¿Por qué establecer las propiedades del comportamiento de la tabla?
Power View agrupa los elementos automáticamente según los campos y el formato de
presentación que esté usando. En la mayoría de los casos, la agrupación predeterminada genera
un resultado óptimo. Pero para algunas tablas, normalmente las que contienen datos detallados,
el comportamiento de agrupación predeterminado agrupará a veces filas que no deberían estar
agrupadas (por ejemplo, los registros de empleados o clientes que deben enumerarse
individualmente, en especial cuando dos o más personas comparten el mismo nombre y
apellidos). Para estas tablas, puede establecer propiedades que hagan que las filas se enumeren
individualmente en vez de agruparse.
NOTA: No cambie el comportamiento predeterminados en las tablas que actúan como tabla de
búsqueda (por ejemplo, una tabla de fechas, de categorías de producto o de departamentos,
donde la tabla consta de un número relativamente reducido de filas y columnas) ni las tablas de
resumen que contienen filas que solo ofrecen interés cuando se resumen (por ejemplo, los datos
del censo acumulados por sexo, edad o ubicación geográfica). En las tablas de resumen y de
búsqueda, el comportamiento de agrupación predeterminado genera el mejor resultado.
Pasos para establecer los campos predeterminados
En la ventana de PowerPivot
1. Haga clic en la pestaña de la tabla DimProduct
2. Haga clic en la pestaña Avanzadas > Conjunto de campos predeterminado.
Los campos predeterminados son los que se agregan a una hoja de Power View al hacer clic en el
nombre de tabla en lugar de expandir la tabla y seleccionar campos específicos.
NOTA: El cuadro de diálogo muestra todos los campos de la tabla, incluso los que se han marcado
para ocultarlos en las herramientas de cliente. Si agrega uno de los campos ocultos al conjunto de
campos predeterminado, no aparecerá en las herramientas del cliente.
Seleccione y agregue estos campos:
ProductName
Product Category
Product Subcategory
UnitCost
En la hoja de Power View en Excel
1. Vuelva a la hoja de Power View en Excel. Aparece un mensaje que indica que el informe de
Power View necesita datos nuevos. Haga clic en Aceptar.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 116 de 118 www.infoadmin.com.mx
2. Haga clic en la hoja en blanco (y no seleccione el mapa que agregó en el otro tutorial) y
haga clic en el nombre de tabla DimProduct.
3. Power View agrega una tabla con los cuatro campos.
Pasos para establecer el comportamiento de las tablas
En la ventana de PowerPivot
1. Selecciona la tabla DimProduct.
2. Haga clic en Comportamiento de la tabla.
3. Para Identificador de fila, haga clic en ProductKey.
Si no establece un identificador de fila, no puede establecer ninguno de los otros valores en este
cuadro de diálogo.
NOTA: El cuadro de diálogo muestra todos los campos de la tabla, incluido ProductKey, que se han
marcado para ocultar en las herramientas de cliente. Si establece uno de los campos ocultos como
etiqueta predeterminada, no aparecerá en las herramientas de cliente.
4. Para Mantener filas únicas, seleccione ProductName.
5. Para Etiqueta predeterminada, seleccione ProductName.
No hay imágenes en estos datos, por lo que no puede establecer una imagen predeterminada.
En la hoja de Power View en Excel
1. Vuelva a la hoja de Power View en Excel y actualice los datos.
Observe en la lista de campos y en el cuadro Campos que el nombre de producto tiene al lado un
icono con forma de tarjeta.
2. Seleccione la tabla que creó con los campos predeterminados en la sección anterior.
3. En la pestaña Diseño, haga clic en la flecha situada bajo Tabla y haga clic en Tarjeta.
Tenga en cuenta que las tarjetas contienen los mismos campos que las tablas, pero se muestran
de forma diferente. Tenga en cuenta que el campo que establecemos como etiqueta
predeterminada, Nombre de producto, se muestra más prominente que el texto de los demás
campos. Puede cambiar la manera en que el otro texto aparece en el paso siguiente.
4. En la pestaña Diseño, haga clic en la flecha situada bajo Tarjeta de visita y haga clic en
Llamada. Todo el texto es grande ahora.
Crear agregados predeterminados
En la ventana de PowerPivot
1. Haga clic en la pestaña de la tabla FactSales.
2. Haga clic en el cuadro situado bajo la columna UnitPrice en el Área de cálculo.
3. En la pestaña Inicio, haga clic en la flecha situada junto a Autosuma > Promedio.
De esta forma se crea un campo calculado que calcula el promedio del precio unitario de un
producto, en función del contexto de cálculo, es decir, de la ubicación del campo en una
visualización.
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 117 de 118 www.infoadmin.com.mx
En la hoja de Power View en Excel
1. Vuelva a la hoja de Power View en Excel y actualice los datos.
2. Expanda la tabla FactSales en la lista de campos.
Observe los diferentes iconos que están junto a los campos. El campo UnitPrice tiene un símbolo
sigma (Σ) al lado. El campo Suma de UnitPrice tiene un pequeño símbolo de calculadora. Ese es el
campo calculado. Haga clic en el lienzo en blanco y seleccione los campos UnitPrice y Suma de
UnitPrice.
3. Los dos valores son iguales.
4. En el área Campos , haga clic en la flecha situada junto al campo UnitPrice. Observe las
diferentes opciones: Suma, Promedio, etc. Haga clic en Promedio.
5. En el área Campos , haga clic en la flecha situada junto al campo Suma de UnitPrice. Tenga
en cuenta que no puede cambiar el agregado porque ha definido la agregación de este
campo en PowerPivot.
SUGERENCIA: Recuerde esto cuando desarrolle el modelo de datos: Power View puede hacer
muchos cálculos sencillos automáticamente sobre la marcha, con gran flexibilidad. Por lo tanto,
deje que Power View realice los cálculos sencillos. Solo puede crear los cálculos más complicados
en el modelo de PowerPivot.
Agregar descripciones
Puede agregar una descripción a los campos y las tablas de PowerPivot. Se muestran en Power
View.
1. En la ventana de PowerPivot
2. En la tabla DimProduct, seleccione la columna ProductName, haga clic con el botón
secundario y, a continuación, haga clic en Descripción.
3. Escriba 'Esto es lo que llamamos el producto' o cualquier otra descripción que desee.
4. Haga clic con el botón secundario en la pestaña de la tabla Geografía y, a continuación,
haga clic en Descripción.
5. Escriba 'Esto es la ubicación' o cualquier otra descripción que desee.
En la hoja de Power View en Excel
1. Vuelva a la hoja de Power View en Excel y actualice los datos.
2. Desplace el puntero sobre el nombre de tabla Geografía y el campo ProductName para ver
las descripciones que ha agregado.
Configurar un conjunto de campos predeterminado para informes de Power View http://office.microsoft.com/es-mx/excel-help/configurar-un-conjunto-de-campos-
predeterminado-para-informes-de-power-view-HA102836617.aspx?CTT=5&origin=HA102922619
M.N. Ing. Jorge Perdomo Rivera Análisis de datos con PowerPivot y Power View
INFOADMIN, Centro de Desarrollo de Habilidades Página 118 de 118 www.infoadmin.com.mx
Configurar propiedades del comportamiento de las tablas para informes Power View http://office.microsoft.com/es-mx/excel-help/configurar-propiedades-del-comportamiento-de-
las-tablas-para-informes-power-view-HA102836731.aspx?CTT=5&origin=HA102922619