TABLEROS DE MANDO CON EXCEL 2013
Cómo crear un panel resumen desde cero
Excel permite un análisis de datos muy completo junto con la posibilidad de la presentación de los mismos en un cuadro de mando para toma de decisiones. Está guía resume las principales funcionalidades para lograr un panel básico de información.
Javier Roig Garro
Tableros de mando con Excel 2013
Javier Roig Garro
Índice Índice .................................................................................................. 1 Presentación ........................................................................................ 2 ¿Qué es un DashBoard? ...................................................................... 3 Conceptos implicados en un panel de mando .................................... 3 Tablas dinámicas ................................................................................. 6 Tablas dinámicas y Power Pivot ........................................................ 7 Creación de la tabla dinámica desde Excel ........................................ 9 Cálculos en una tabla dinámica ...................................................... 13 ¿Qué es un campo calculado y un elemento calculado? ................... 14 Crear un campo calculado .............................................................. 15 Insertar un elemento calculado ....................................................... 16 Filtrar información ............................................................................ 18 Filtrar tablas dinámicas por segmentación ...................................... 18 Desplegables y otros controles de Programación .............................. 19 Gráficos dinámicos ............................................................................ 23 Graficos dinámicos y Power View .................................................... 23 Creación de un Gráfico Dinámico .................................................... 24 Presentación de un gráfico con dos escalas diferentes ..................... 25 Integración de Elementos en un panel ............................................... 29 Funciones de Importación de datos ................................................. 29 Un poco de código para automatizar todo ........................................ 30 Formatos Condicionales, formatos, fuentes gráficas ........................ 31 Proteger Libro, Ocultar Hoja ........................................................... 32
1 Índice
Tableros de mando con Excel 2013
Javier Roig Garro
Presentación Excel dispone de herramientas muy potentes para crear un “Dashboard”, “Tablero de Mandos” o “Cuadro de control” para analizar los datos de una forma completa y ajustada a las necesidades de la empresa. Este curso hace hincapié en aquellas funciones, controles, elementos y diseño necesario para presentar un panel funcional y atractivo que haga fácilmente entendible el análisis de los datos. Objetivos Generales Plantear el análisis de datos desde una perspectiva gráfica
Crear elementos intermedios que analicen la información a representar
Adquirir un conocimiento profundo de Excel.
Objetivos Específicos Analizar datos con tablas dinámicas
Utilización de desplegables para filtrar información
Automatizar la presentación de datos con funciones
Uso de gráficos dinámicos
Requisitos previos Tener un conocimiento alto de Excel, principales funciones y gráficos Dirigido a Toda aquella persona que tenga que resumir información para una posterior toma de decisión.
y
analizar
Temas relacionados Excel PowerPivot
2 Presentación
Tableros de mando con Excel 2013
Javier Roig Garro
¿Qué es un DashBoard? Una de las funcionalidades de Excel es el análisis y representación de la información resumen de grandes cantidades de datos. Los “Dashboard”, “Tablero de Mandos” o “Cuadro de control” son libros de Excel pensados principalmente para este cometido. Resumir y sintetizar la información para conseguir mediante unas gráficas o identificadores claros, una información que con pocos clics del ratón podamos visualizar de cada apartado relevante desde el punto de vista del análisis. Es la herramienta perfecta para “reportar” información a dirección o similar ya que en vez de presentar grandes cantidades de datos que necesitan tiempo de análisis u explicaciones, se presenta un resumen que se explica (o debería explicarse) por sí solo. Como siempre, en este tipo de herramientas, una vez conocidos las funciones y utilidades que nos ofrece Excel, lo más complicado es pensar que datos queremos representar.
Conceptos implicados en un panel de mando No hay una forma estándar de crear estos paneles, pero distribuir la información, los cálculos y la presentación en las diferentes hojas de un libro, nos pueden ayudar a crear tableros claros y fáciles de entender.
Lista de datos
Presntación con Filtros Cálculos y Tablas
3 ¿Qué es un DashBoard?
Tableros de mando con Excel 2013
Javier Roig Garro
Lista de datos La hoja de lista de datos, debe contener el listado base para realizar los cálculos que queremos presentar. Es importante estudiar que datos son los que necesitamos para que la información base sea coherente y se pueda relacionar para crear indicadores válidos. Por ejemplo, tener en la misma hoja datos de venta por cliente a la vez que datos de producción, que no podamos vincular, lo único que va conseguir es tener un libro más grande con información que no podamos presentar de forma conjunta. En este caso, lo mejor es crear dos libros, para analizar por un lado la información de ventas y por otro la de producción. Cálculos y Tablas A partir de un listado completo de información, el siguiente paso es crear la estructura de cálculos que nos sirvan para resumir la información. La herramienta clave va a ser las Tablas Dinámicas. Estas tablas facilitan la agrupación y el cálculo de la información, en formato numérico y porcentual. Junto con las funciones básicas de las tablas dinámicas, podemos crear campos calculados que completen la información que luego queramos plantear. En esta hoja de cálculos podemos crear listados con la información que luego queramos presentar. Estos listados son clave cuando utilizamos controles de programación como cuadros desplegables. Presentación de datos Esta hoja tiene que estar diseñada para poder visualizar los datos de una forma sencilla a la vez que dé respuesta a las dudas principales. Tiene que contener Filtros para seleccionar que datos queremos presentar. Estos filtros pueden ser entre otros, selectores, cuadros desplegables o casillas de verificación Los gráficos de presentación pueden ser dinámicos o gráficos estándar según las necesidades. También podemos utilizar casillas que presenten información resumida que junto con el formato condicional permitirá realzar la información. Ocultar y proteger la información Una práctica habitual en este tipo de libros de Excel es la de ocultar y proteger las hojas con los datos y con los cálculos. De tal manera que la persona que reciba la Excel pueda realizar los filtros que hayamos establecidos, pero no tenga un acceso directo a los datos de origen o a los cálculos. 4 ¿Qué es un DashBoard?
Tableros de mando con Excel 2013
Javier Roig Garro
Las hojas ocultas siguen siendo totalmente funcionales, de tal manera que los filtros van a seguir funcionando perfectamente, pero sin distraer el análisis con más información de la debida.
5 ¿Qué es un DashBoard?
Tableros de mando con Excel 2013
Javier Roig Garro
Tablas dinámicas Tablas dinámicas, definiciones, datos de origen Una tabla dinámica es un informe resumido generado a partir de una base de datos. La base de datos en la que se basa, puede estar en la hoja en forma de tabla o en un archivo de datos externo. El aspecto más innovador de las tablas dinámicas está en su interactividad, es decir, tras su creación podemos recolocar la información en cualquier forma, e incluso insertar fórmulas especiales que realicen nuevos cálculos, y crear posteriormente grupos de elementos de resumen. Sin embargo, las tablas dinámicas no se actualizan automáticamente cuando modificamos la información en el origen de datos, aunque este problema se soluciona haciendo clic en el botón Actualizar que hace que la tabla dinámica se actualice con los últimos datos. Origen de datos de la tabla dinámica La tabla dinámica necesita que los datos en los que se basa estén en una base de datos rectangular. En general, los campos de una tabla de base de datos pueden ser de dos clases: Datos: contienen un valor de datos. Por ejemplo, el campo Cantidad es un campo de datos. •
Categoría: describe los datos. Por ejemplo, los campos Fecha, TipoCuenta, Oficina o Cliente (tratando el tema de una cuenta bancaria), son campos de Categoría porque describen los datos del campo Cantidad.
Para especificar los datos sobre los que basar la tabla dinámica:
1. 2. 3. 4.
Si los datos están en el rango de una hoja de cálculo seleccionar cualquier celda de ese rango. Ir a la ficha Insertar. Dentro del grupo Tablas elegir la opción Tabla dinámica. En el cuadro de diálogo Crear tabla dinámica seleccionar los datos en los que se va a basar la tabla dinámica y su ubicación. 6
Tablas dinámicas
Tableros de mando con Excel 2013
Javier Roig Garro
Tablas dinámicas y Power Pivot En las versiones 2013 y 2016 se integra el uso de Modelo de Datos dentro de los libros de Excel. En la versión 2010 se tiene que instalar el complemento PowerPivot para dotar de la misma funcionalidad. Excel apuesta por la creación de un Modelo de Datos dentro de los libros de Excel permitiendo la gestión de los datos de múltiples tablas utilizando esa información en Tablas Dinámicas y Gráficos Dinámicos. Esta información se puede extender con campos calculados, columnas calculadas, jerarquías de datos y KPIs Excel almacena una copia de la información permitiendo trabajar con archivos de 2 GB de tamaño en disco mediante un motor local de base de datos integrado en el archivo. Cada tabla del modelo de datos, a través de sus propiedades, contiene información del origen de la conexión Las tablas de Excel y el modelo de datos Excel tiene que reconocer la información que contiene como tabla para poder crear las relaciones entre las mismas. A partir de un rango de datos podemos crear una tabla, especificando las columnas por separado y dándole un nombre que la identifique. Otra forma de añadir tablas al modelo de datos es importando la información desde diferentes orígenes de datos. Si la importación se realiza desde un modelo relacional, podemos importar datos y relaciones de forma automática. Si esta importación se realiza desde otros formatos como archivos de texto plano, crea sólo las tablas teniendo, posteriormente, que enlazarlas mediante relaciones. 7 Tablas dinámicas
Tableros de mando con Excel 2013
Javier Roig Garro
Diferencias entre Excel y PowerPivot Existen tres diferencias importantes entre estas dos herramientas. Las PowerPivot se pueden guardar solo en Libros de Excel, (.xlsx), Libros de Excel con Macros (.xlsm) y Libros de Excel Binarios (.xlsb). No se pueden almacenar en ningún otro tipo de archivo. La ventana de modelo de datos no soporta Visual Basic para aplicaciones, pero si se puede usar en la ventana de Excel de un libro con PowerPivot En las tablas de Excel se puede agrupar de forma automática desde la cabecera de un campo, pero en PowerPivot se crean tablas relacionadas o campos calculados para realizar esta agrupación. Crear una Tabla o Gráfico Dinámico desde Power Pivot Podemos crearlas desde la pestaña de PowerPivot de Excel o desde la pestaña de Inicio de la ventana PowerPivot.
Inserta en el libro de Excel diferentes formatos de tablas dinámicas
Tabla dinámica en blanco Gráfico dinámico en blanco Tabla y gráfico en formato horizontal. Son independientes, pero por defecto la segmentación se aplica a los dos Tabla y gráfico en vertical. Son independientes pero la segmentación se aplica a los dos Dos gráficos en horizontal. Los gráficos son independientes Dos gráficos en vertical. Los gráficos son independientes Cuatro gráficos. Los gráficos son independientes pero la segmentación se aplica a todos Tabla Dinámica plana. Se añade una nueva columna para cada campo que se seleccione 8
Tablas dinámicas
Tableros de mando con Excel 2013
Javier Roig Garro
Al seleccionar el tipo de objeto que queremos insertar, muestra la ventana para determinar dónde ubicarla. A diferencia con las tablas en Excel no pregunta por los datos de origen ya que hace referencia al modelo de datos de Power Pivot
El resultado final es similar a las tablas dinámicas de Excel, aunque con mayor control de la información
Creación de la tabla dinámica desde Excel Cuando aparece la Lista de campos de tabla dinámica se puede configurar el diseño real de la tabla dinámica utilizando una de las técnicas siguientes: 9 Tablas dinámicas
Tableros de mando con Excel 2013 • • •
Javier Roig Garro
Arrastrando los nombres de los campos de la parte superior a uno de los recuadros de la Lista de campos de tabla dinámica. Colocando una marca de verificación al lado del elemento de la parte superior de la Lista de campos de tabla dinámica. Haciendo clic en un nombre de campo en la parte superior de la lista de campos de tabla dinámica y seleccionando su ubicación desde el menú desplegable.
Formato de Tabla Dinámica La tabla dinámica utiliza el formato de número General. Para cambiar el formato de número utilizado hacer clic con el botón derecho sobre cualquier valor y seleccionar Formato de número del menú emergente, y elegir el formato deseado dentro del cuadro de diálogo Formato de celdas. Para aplicar un estilo a la tabla dinámica hay que seleccionar cualquier celda de la tabla y dentro de la ficha contextual Herramientas de tabla dinámica en la ficha Diseño dentro del grupo Estilos de tabla dinámica elegir el estilo deseado.
10 Tablas dinámicas
Tableros de mando con Excel 2013
Javier Roig Garro
También podemos utilizar los controles del grupo Diseño dentro de la ficha Diseño de la ficha contextual Herramientas de tabla dinámica para controlar los diferentes elementos de la tabla.
El grupo Mostrar de la ficha Opciones dentro de la ficha contextual Herramientas de tabla dinámica contiene las opciones que afectan a la apariencia de la tabla dinámica.
Terminología • Campo columna: un campo que tiene orientación de columna. Cada elemento del campo ocupa una columna. • Total, General: una fila o columna que muestra los totales de todas las celdas de una fila o columna. • Grupo: un conjunto de elementos que se trata como un único elemento. Se pueden agrupar elementos de manera manual o automáticamente. • Elemento: un elemento de un campo que aparece como un encabezado de fila o columna en una tabla dinámica.
11 Tablas dinámicas
Tableros de mando con Excel 2013 • • • • •
•
Javier Roig Garro
Actualizar: recalcula la tabla después de que se hayan producido cambios en los datos de origen. Campo fila: un campo que tiene una orientación de fila en la tabla dinámica. Cada elemento del campo ocupa una fila. Datos de origen: los datos empleados para crear una tabla dinámica. Subtotales: una fila o columna que muestra subtotales de una tabla dinámica. Filtro de tabla: un campo que tiene una orientación de página en la tabla dinámica similar a una porción de un cubo tridimensional. Puede mostrar un elemento o todos los elementos de campo de página cada vez. Área de valores: las celdas de una tabla dinámica que contienen los datos del informe.
Modificar una tabla dinámica • Para eliminar un campo seleccionarlo en la parte inferior de la Lista de campos de tabla dinámica y arrastrarlo fuera. • Para modificar el orden de los campos si la zona tiene más de un campo, arrastrar los nombres de los campos a su nueva ubicación. • Para eliminar temporalmente un campo eliminar la casilla de verificación del nombre de campo en la parte superior de la Lista de campos de tabla dinámica. • Si añadimos un campo a la sección Filtro de informe, los elementos del campo aparecen en la lista desplegable, lo que nos permite filtrar los datos mostrados por uno o dos elementos. Ordenar Datos • Seleccionar la columna por la que queremos ordenar y pulsar el orden en los botones de la Ficha de Tabla Dinámica. • Seleccionar el botón Ordenar
12 Tablas dinámicas
Tableros de mando con Excel 2013
Javier Roig Garro
Cálculos en una tabla dinámica Podemos visualizar los datos utilizando diferentes técnicas de resumen. Para ello: 1. 2. 3. 4. 5. 6.
Seleccionar cualquier celda de la zona Valores. Ir a la ficha contextual Herramientas de tabla dinámica. Elegir la ficha Opciones. Desplegar el icono Campo activo. Elegir la opción Configuración de campo. En el cuadro de diálogo Configuración de campo de valor utilizar la ficha Resumen para seleccionar una función diferente de resumen. Para desplegar sus valores de una forma diferente, utilizar el control desplegable en la ficha Mostrar valores como.
Agrupar elementos de las tablas dinámicas La tabla dinámica permite combinar elementos en grupos. Los elementos que podemos agrupar son los que aparecen como Rótulos de fila o Rótulos de columna. Excel ofrece dos formas de agrupar elementos: •
Manualmente: para ello seleccionar los elementos que van a ser agrupados y elegir la opción Agrupar selección de la ficha 13
Tablas dinámicas
Tableros de mando con Excel 2013
•
Javier Roig Garro
Opciones dentro de la ficha contextual Herramientas de tabla dinámica. También se puede realizar esta operación haciendo clic con el botón derecho del ratón y seleccionando la opción Agrupar del menú desplegable. Automáticamente: si los elementos son numéricos o de tipo fecha, podemos utilizar el cuadro de diálogo Agrupar para especificar cómo deseamos agrupar los elementos. Para ello seleccionar los elementos que van a ser agrupados y elegir la opción Agrupar selección de la ficha Opciones dentro de la ficha contextual Herramientas de tabla dinámica. También se puede realizar esta operación haciendo clic con el botón derecho del ratón y seleccionando la opción Agrupar del menú desplegable.
¿Qué es un campo calculado y un elemento calculado? •
•
Campo calculado: un nuevo campo creado a partir de otros campos de la tabla dinámica. Si el origen de una tabla dinámica es una tabla de hoja de cálculo, una alternativa para usar un campo calculado es agregar una columna nueva a la tabla y crear una fórmula para realizar el cálculo deseado. Un campo calculado debe encontrarse en la zona Valores. Elemento calculado: un elemento calculado utiliza el contenido de otros elementos de un campo de la tabla dinámica. Si el origen de una tabla dinámica es una tabla de hoja de cálculo, una alternativa es insertar una o más filas y escribir fórmulas que utilicen valores de otras filas. Un elemento calculado debe encontrarse en los Rótulos de fila o en un Filtro de informe. No puede encontrarse en la zona Valores. 14
Tablas dinámicas
Tableros de mando con Excel 2013 •
Javier Roig Garro
Las fórmulas utilizadas para crear campos calculados y elementos calculados no se introducen en celdas. En su lugar introducimos estas fórmulas en un cuadro de diálogo y se almacenan junto con los datos de la tabla dinámica.
Crear un campo calculado Como una tabla dinámica es un tipo especial de rango de datos no permite insertar filas o columnas en ella, por lo que no se pueden insertar fórmulas para realizar los cálculos. Sin embargo, se puede crear un campo calculado que consiste en un cálculo que puede incluir otros campos. Para crear un campo calculado: 1. 2. 3. 4. 5.
6.
7. 8.
Seleccionar cualquier celda de la tabla dinámica. Ir a la ficha contextual Herramientas de la tabla dinámica. Seleccionar la ficha Opciones. Desplegar el icono Cálculos y elegir Campos, elementos y conjuntos. Seleccionar la opción Campo calculado.
En el cuadro de diálogo Insertar campo calculado introducir un nombre descriptivo en el recuadro Nombre y la fórmula en el campo Fórmula. La fórmula puede utilizar funciones de hoja y otros campos de origen. Se puede crear la fórmula de forma manual escribiéndola o haciendo doble clic en un elemento del cuadro de la lista Campos. Por ejemplo: =Ventas / ‘Unidades’ Hacer clic en Sumar para agregar este nuevo campo. Hacer clic en Aceptar para cerrar el cuadro de diálogo.
15 Tablas dinámicas
Tableros de mando con Excel 2013
Javier Roig Garro
Tras crear el campo calculado Excel lo agrega a la zona Valores de la tabla dinámica, pero también aparece en la Lista de campos de la tabla dinámica.
Etiquetas de fila Ene Feb Mar Abr May Sep Nov Dic Oct Total general
Etiquetas de columna CLi1 CLi2 N Ventas Un Ventas/Un N Ventas 3.200,00 € 300 10,67
CLi3 Un Ventas/Un N Ventas 750,00 €
658,00 €
78
45
16,67
3.020,00 € 350
8,63
2.500,00 € 245
10,20
9,52 6.270,00 € 640
9,80
1.250,00 € 125
10,00
3.520,00 € 420
8,38
8,44
950,00 € 855,00 € 167 4.713,00 € 545
Total N Ventas Total Un Total Ventas/Un Un Ventas/Un
56
5,12 8,65 5.720,00 € 601
16,96
3.200,00 € 750,00 € 1.250,00 € 658,00 € 3.520,00 € 3.020,00 € 950,00 € 2.500,00 € 855,00 € 16.703,00 €
300 45 125 78 420 350 56 245 167 1786
10,67 16,67 10,00 8,44 8,38 8,63 16,96 10,20 5,12 9,35
Insertar un elemento calculado Un elemento calculado es una alternativa para agregar una fila nueva al origen de datos, es decir, una fila que contiene una fórmula que hace referencia a otras filas. Para crear un elemento calculado: 1. 2. 3. 4. 5. 6.
Mover el puntero de celda a la zona Rótulo de fila o Rótulo de columna. Ir a la ficha contextual Herramientas de la tabla dinámica. Seleccionar la ficha Opciones. Desplegar el icono Cálculos y elegir Campos, elementos y conjuntos. Seleccionar la opción Elemento calculado. En el cuadro de diálogo Insertar elemento calculado introducir un nombre descriptivo en el recuadro Nombre y la fórmula en el campo Fórmula. La fórmula puede utilizar elementos de otros campos, pero no puede utilizar funciones de hoja. Por ejemplo: =10%*(Ene+Feb+Mar)
16 Tablas dinámicas
Tableros de mando con Excel 2013
7. 8. 9. 10.
Javier Roig Garro
Hacer clic en Sumar. Repetir los pasos 6 y 7 para crear más elementos calculados. Por ejemplo: =10%*(Abr+May+Jun) Hacer clic en Aceptar. Mover el campo en la posición deseada
Etiquetas de fila Ene Feb Mar 1Trimestre Abr May Sep Nov Dic Oct Total general
Etiquetas de columna CLi1 CLi2 N Ventas Un Ventas/Un N Ventas 3.200,00 € 300 10,67
320,00 € 658,00 €
30 78
855,00 € 167 5.033,00 € 575
10,67 8,44
CLi3 Ventas/Un N Ventas
Un
Ventas/Un
750,00 €
45
16,67
75,00 €
4,5
16,67
3.020,00 €
350
8,63
2.500,00 €
245
10,20
9,53 6.345,00 € 644,5
9,84
1.250,00 € 125,00 €
125 12,5
10,00 10,00
3.520,00 €
420
8,38
950,00 €
56
16,96
5,12 8,75 5.845,00 € 613,5
Total N Ventas Total Un Total Ventas/Un Un
3.200,00 € 750,00 € 1.250,00 € 520,00 € 658,00 € 3.520,00 € 3.020,00 € 950,00 € 2.500,00 € 855,00 € 17.223,00 €
300 45 125 47 78 420 350 56 245 167 1833
10,67 16,67 10,00 11,06 8,44 8,38 8,63 16,96 10,20 5,12 9,40
17 Tablas dinámicas
Tableros de mando con Excel 2013
Javier Roig Garro
Filtrar información Filtrar tablas dinámicas por segmentación Una segmentación es un control interactivo que hace que sea más fácil filtrar datos en una tabla dinámica. 1. 1. 2. 3. 4. 5. 6.
7.
Para añadir una segmentación a una hoja de cálculo: Seleccionar una celda de la tabla dinámica. Ir a la ficha Herramienta de tabla dinámica. Seleccionar la ficha Opciones. Desplegar el icono Insertar segmentación de datos. Elegir la opción Insertar segmentación de datos. En el cuadro de diálogo Insertar segmentación de datos en el que aparece una lista de todos los campos de la tabla dinámica, colocar una marca de verificación al lado de la segmentación que se desee. Hacer clic en Aceptar.
La segmentación se puede mover, cambiar de tamaño y cambiar su aspecto.
Para eliminar los efectos de filtrado por una segmentación concreta hacer clic en el icono en la esquina superior derecha de la segmentación. Para utilizar una segmentación para filtrar datos simplemente hay que hacer en un botón. Para visualizar varios valores hay que pulsar la tecla Control mientras se hace clic en los botones con una segmentación.
18 Filtrar información
Tableros de mando con Excel 2013
Javier Roig Garro
Desplegables y otros controles de Programación En ocasiones los controles de segmentación se pueden sustituir o completar con herramientas de selección de datos como son los controles de formulario. Estos controles suelen estar asociados a programación, aunque en la mayoría de las ocasiones se puede conseguir una gran funcionalidad con las propiedades y si es necesario con pequeñas macros. Mostar la pestaña Programación Tanto los controles de formulario como el editor de macros, están en la pestaña desarrollador. Esta pestaña no está visible por defecto y la tenemos que activar. 1. Desde la pestaña Archivo, seleccionar “Opciones”. 2. En la ventana que se abre, seleccionar en panel de la izquierda “Personalizar cinta de opciones”. 3. En el panel de la derecha, activar la casilla “Desarrollador”
19 Filtrar información
Tableros de mando con Excel 2013
Javier Roig Garro
Utilizar desplegables Un desplegable es un control que contiene una lista de valores, pero que sólo los muestra cuando se despliega. Al seleccionar un elemento de la lista, este valor se puede almacenar en la celda que se configure en las propiedades del control. Para insertar el control: 1. Ir a la pestaña Desarrollador, en el apartado Controles 2. Seleccionar de la opción Insertar de la categoría Cuadro de control, el elemento Cuadro combinado 3. Con el cursor del ratón en forma de cruz, pinchar y arrastrar delimitando la zona que queremos que ocupe el control Asignar Valores 1. Para acceder al control, seleccionarlo con el botón de derecho del ratón y en el desplegable que se muestra, seleccionar “Formato del Control” 2. En la pestaña Control, indicar la lista de valores a mostrar en la celda “Rango de Entrada”. 3. En la misma pestaña, indicar la celda dónde mostrar el índice del valor seleccionado. No devuelve el valor seleccionado sino la posición que ocupa en la lista.
20 Filtrar información
Tableros de mando con Excel 2013
Javier Roig Garro
DESREF. Calcular el valor a partir del índice devuelto En muchas ocasiones vamos a necesitar volver a calcular cual es el valor seleccionado a partir de la posición en la lista, DESREF es la función perfecta para solucionar este problema Devuelve el valor a partir de una celda de referencia, calculando un desplazamiento de filas y columnas. Este valor puede ser el de una celda o un rango de celdas. En este caso, con el nombre es suficiente En la ayuda de la función podemos ver la Sintaxis: DESREF(ref, filas, columnas, [alto], [ancho])
Referencia Obligatorio. Es la posición inicial en la que desea basar la desviación. La referencia debe referirse a una celda o un rango de celdas adyacentes; en caso contrario, DESREF devuelve el valor de error #¡VALOR!. Filas Obligatorio. Es el número de filas, hacia arriba o hacia abajo. Si el argumento filas es 5, es que la fila es 5 filas por debajo de la celda especificada en el apartado Referencia. Filas puede ser positivo (lo que significa que está por debajo de la referencia de inicio) o negativo (por encima). Columnas Obligatorio. Es el número de columnas, hacia la derecha o izquierda, al que desea que haga referencia Columnas puede ser positivo (lo que significa a la derecha de la referencia de inicio) o negativo (a la izquierda). Alto Opcional. Es el alto, en número de filas, que se desea que tenga la referencia devuelta. El alto debe ser un número positivo. Ancho Opcional. Es el ancho, en número de columnas, que se desea que tenga la referencia devuelta. El argumento ancho debe ser un número positivo.
Con la lista de valores en A2:A6, y la función =DESREF(A2;B2;0;1;1) en B3
Los valores del ejemplo, significa, que la celda base para hacer cálculo es la celda A2 (Agente) y que la cantidad de filas hacia abajo a ser lo que contenga la celda B2 (la posición del valor seleccionado el cuadro combinado), como no queremos que cambie de columna,
el va en se 21
Filtrar información
Tableros de mando con Excel 2013
Javier Roig Garro
pone 0, y cรณmo solo queremos seleccionar una celda, los valores alto y ancho es 1
22 Filtrar informaciรณn
Tableros de mando con Excel 2013
Javier Roig Garro
Gráficos dinámicos Un gráfico dinámico es una representación gráfica de un resumen de datos mostrados en una tabla dinámica. Este tipo de gráficos está basado siempre en una tabla dinámica, pero mantiene todas las operaciones y funciones de gráfico de Excel.
Graficos dinámicos y Power View Power View es una experiencia interactiva de exploración, visualización y presentación de datos que fomenta la elaboración intuitiva de informes a demanda. Power View es una característica de Microsoft Excel 2013 y 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. Power View tiene dos versiones: En Excel, las hojas de Power View forman parte del archivo XLSX de Excel. Los informes de Power View en SharePoint Server son archivos RDLX. Ambas versiones de Power View necesitan que Silverlight esté instalado en el equipo. No se puede abrir un archivo RDLX de Power View en Excel ni abrir un archivo XLSX de Excel con hojas de Power View en Power View en SharePoint. Tampoco se pueden copiar gráficos u otras visualizaciones del archivo RDLX al libro de Excel. Orígenes de datos Excel 2013 permite usar los datos directamente en Excel como base para Power View para Excel y SharePoint. Al agregar tablas y crear relaciones entre ellas, Excel crea un modelo de datos en segundo plano. Un modelo de datos es una colección de tablas y sus relaciones que reflejan las relaciones reales entre procesos y funciones empresariales. Puede continuar modificando y mejorando el mismo modelo de datos en Power Pivot para Excel, para crear un modelo de datos más sofisticado para informes de Power View. Power View permite 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 modelos tabulares implementados en instancias de SQL Server 2012 Analysis Services (SSAS). 23 Gráficos dinámicos
Tableros de mando con Excel 2013
Javier Roig Garro
Creación de un Gráfico Dinámico Podemos crear un gráfico dinámico de dos formas: •
•
Seleccionar cualquier celda de la tabla dinámica y elegir la opción Gráfico dinámico de la ficha Opciones dentro de la ficha contextual Herramientas de tabla dinámica. Dentro de la ficha Insertar desplegar el icono Tabla dinámica y elegir la opción Gráfico dinámico.
Un gráfico dinámico incluye botones de campo que le permiten filtrar los datos del gráfico. Se pueden eliminar los botones de campos haciendo clic con el botón derecho sobre un botón y seleccionar el comando Ocultar del menú emergente. Cuando se selecciona un gráfico dinámico la Cinta de opciones despliega una nueva ficha llamada Herramientas de gráfico dinámico con comandos prácticamente idénticos a los de un gráfico estándar de Excel. Si se modifica la tabla dinámica subyacente, el gráfico se ajusta automáticamente para mostrar el nuevo resumen de datos.
24 Gráficos dinámicos
Tableros de mando con Excel 2013
Javier Roig Garro
A la hora de crear el gráfico dinámico hay que tener en cuenta una serie de puntos: •
•
•
• •
•
Una tabla dinámica y un gráfico dinámico están unidos por un enlace de doble sentido, es decir, si se realizan cambios estructurales o de filtro a uno, el otro también cambia. Los botones de campo en un gráfico dinámico contienen los mismos controles que los encabezados de los campos de la tabla dinámica, permitiendo filtrar los datos de la tabla y del gráfico. Si se hacen cambios al gráfico utilizando estos botones, estos cambios también se reflejan en la tabla dinámica. Si tenemos un gráfico dinámico y eliminamos la tabla subyacente el gráfico permanece, ya que las fórmulas Series del gráfico contienen los datos almacenadas en matrices. Los gráficos dinámicos están incrustados en la hoja que contiene la tabla dinámica. Se pueden crear varios gráficos dinámicos a partir de una tabla dinámica, permitiendo dar formato y manejar los gráficos por separado. Pero los gráficos muestran los mismos datos. La segmentación también funciona con los gráficos dinámicos.
Filtros y segmentación en gráficos dinámicos. • Los filtros que se aplican en la tabla dinámica son los mismos que en los gráficos. • Estos filtros se pueden aplicar desde los botones de los gráficos. Estos botones se pueden activar desde la pestaña Analizar de la ficha Herramientas de gráfico dinámico. • Al igual que en las tablas, las gráficas se pueden segmentar desde la ficha Analizar. • Si el gráfico se basa en una tabla que ya tiene segmentación, el gráfico la hereda.
Presentación de un gráfico con dos escalas diferentes En más de una ocasión he necesitado mostrar en una gráfica el mismo dato, utilizando los valores numéricos y los porcentuales para mejorar la visualización de la información a la hora de analizar un dato. El problema, es que en muchos casos estos dos datos son muy dispares y no quedan nada bien en una sola gráfica. 25 Gráficos dinámicos
Tableros de mando con Excel 2013
Javier Roig Garro
Por ejemplo, para analizar un parcial, quiero saber cuántos alumnos han aprobado, cuantos han sacado un bien, cuantos han suspendido, etc. Imaginemos qué tenemos 10 aprobados de 30 alumnos o lo que es lo mismo un 33%. Si lo intentamos presentar en un gráfico de barras, con una sola escala, queda raro ya que los valores numéricos van de 0 a 30, mientras que los porcentuales van de 0 a 100 Datos de Origen Parto de una tabla donde he almacenado para cada alumno, la nota que ha sacado y que utilizando la función "Promedio" me calcula la media por alumno. En esta tabla tengo también una columna dónde transformo esa nota media en un texto utilizando varias funciones "Sii" anidadas (unas dentro de otras)
Para analizar mejor los datos, tengo una segunda tabla que he llamado de "Indicadores" y que utilizar la función "Contar.Si.Conjunto" me recuenta cuantos suspensos, aprobados, etc, tengo en la primera tabla. Esta tabla la completo con una columna de porcentajes
Si selecciono la tabla (menos la fila de Total) e inserto un gráfico de barras, me muestra las dos series a la vez ( Nº de alumnos y % de alumnos) por cada tipo de nota, pero, el gráfico es poco claro, incluso añadiendo los valores para cada serie
26 Gráficos dinámicos
Tableros de mando con Excel 2013
Javier Roig Garro
Utilizar un eje secundario para los datos Lo primero que tenemos que hacer es seleccionar una de las dos series e indicar en las propiedades del formato, que queremos que la represente en el eje secundario. Lo más sencillo, es ir a la cinta de botones, en la pestaña Formato y seleccionar "Serie Nº" y pulsar sobre "Aplicar formato a la selección"
Se abre la ventana de propiedades con el título "Formato de serie de datos" y ahí es donde tenemos que indicar que queremos utilizar el Eje secundario. Si no tenemos seleccionada previamente la serie, no va a mostrar esta opción.
Jugando con las opciones de "Ancho del intervalo" podemos indicar el grosor de la serie, hasta encontrar el que nos parezca mejor para el gráfico. 27 Gráficos dinámicos
Tableros de mando con Excel 2013
Javier Roig Garro
Como las escalas las pone de forma automática, podemos forzar para que la escala secundaria refleje siempre los 30 alumnos y así tengamos un gráfico más claro. Para establecer los valores en la escala secundaria, tenemos que seleccionarla primero, bien pinchando en el gráfico o bien desde la cinta de opciones.
Una vez seleccionada, pulsamos "Aplicar formato a la selección" y nos muestra la ventana de propiedades, dónde podemos establecer un valor fijo a la escala, en este caso, 30.
Resultado Final Una vez hechos los cambios en el gráfico (indicar a la serie número que utilice el eje secundario, cambiar el grosor de la serie, añadir un valor fijo a la escala secundaria, mostrar valores) tenemos un gráfico que, a mi entender, expresa de forma más clara toda la información de la tabla de indicadores.
28 Gráficos dinámicos
Tableros de mando con Excel 2013
Javier Roig Garro
Integración de Elementos en un panel Partiendo de los datos y utilizando tablas auxiliares debemos presentar la información de forma fácil de interpretar. No hay una estructura fija, aunque lo mejor es mostrar los filtros, datos resumen y uno o varios gráficos que se ajusten a la información Para mejorar la presentación, podemos utilizar filtros por segmentación, filtros con desplegables, funciones que importen datos desde tablas dinámicas y formatos condicionales para resaltar elementos clave. Una vez preparada la presentación de la información, puede ser recomendable ocultar las hojas de datos y bloquear el libro para evitar que un uso no adecuado de las fórmulas, desvirtúen los datos a presentar.
Funciones de Importación de datos Las tablas dinámicas son muy útiles para resumir la información, pero en ocasiones, sólo queremos algún dato en concreto de la tabla dinámica y no toda la información. Disponemos de la función IMPORTARDATOSDINAMICOS() para obtener un valor en concreto de una tabla. Se puede insertar rápidamente una fórmula IMPORTARDATOSDINAMICOS sencilla escribiendo = (signo igual) en la celda a la que desea devolver el valor y haciendo clic en la celda del informe de tabla dinámica que contiene los datos que desea devolver. Sintaxis IMPORTARDATOSDINAMICOS(camp_datos, tabla_dinámica, [campo1, elemento1, campo2, elemento2], ...)
Camp_datos Obligatorio. Es el nombre, entre comillas, del campo de datos que contiene los datos que deseamos recuperar.
Tabla_dinámica Obligatorio. Es una referencia a cualquier celda, rango de celdas o rango de celdas con nombre en un informe de tabla dinámica. Esta información se usa para determinar qué informe de tabla dinámica contiene los datos que desea recuperar.
Campo1, Elemento1, Campo2, Elemento2 Opcional. De 1 a 126 parejas de nombres de campo y elemento que describen los datos que desea recuperar. Las parejas pueden estar en cualquier
29 Integración de Elementos en un panel
Tableros de mando con Excel 2013
Javier Roig Garro
orden. Los nombres de campo y elemento que no son fechas ni números van entre comillas. Si tenemos una tabla dinámica con los totales de ventas por zona y año Totales de ventas por Año y region Total Ventas Region Andalucia Cantabrico Centro Mediterraneo
Año 2014 355386 264581 248664 321913
2015 Total general 636184 991570 470501 735082 445339 694003 582934 904847
Podemos presentar un resumen de los datos en otra hoja utilizando la función =IMPORTARDATOSDINAMICOS("Ventas";Aux!$A$10;"Año";2014) Esta función devuelve el valor del campo Ventas de la tabla dinámica que empieza en la celda A10 de la hoja Aux, dónde el campo Año tiene el valor 2014
Un poco de código para automatizar todo Aunque no es necesario, conocer unas pequeñas macros, van a permitir un resultado mucho más completo del panel. Cuando visualizamos una misma información en diferentes gráficos y tablas, es interesante no tener que ir gráfico a gráfico para aplicar los filtros. Este problema se puede corregir en parte con la segmentación, ya que podemos enlazar un control de segmentación con varios gráficos y tablas, pero en algunos casos, vamos a necesitar pequeñas macros que enlazaremos con controles de programación como los desplegables y los botones de radio o los controles de validación y así conseguir el objetivo que deseemos. Algunas instrucciones que nos pueden servir
Sheets(“nombrehoja”).Select Indicamos que seleccionamos una hoja dentro del libro. A partir de ese momento va a ser la hoja activa 30
Integración de Elementos en un panel
Tableros de mando con Excel 2013
Javier Roig Garro
ActiveSheet.PivotTables(). Para trabajar con todas las tablas dinámicas de la hoja activa. Para poner un filtro a un campo utilizar la propiedad.PivotFields(“nombre del campo”).CurrentPage=”Filtro” Por ejemplo si a filtro ponemos “ALL” significa que queremos mostrar todos los datos, si en filtro ponemos Range(“F34”).Value significa que le va a asignar el valor de la celda F34 al filtro
Para acelerar la macro y quitarnos el refresco de pantalla incomodo podemos utilizar: o Refrescar la pantalla Application. .ScreenUpdating = False/Tue o Quitar la actualización automática y pasarla a manual o viceversa Application.Calculation = xlCalculationManual y para activarla de nuevo xlCalculationAutomatic o Desactivar lo eventos de Excel conv.EnableEvents = False/True
Formatos Condicionales, formatos, fuentes gráficas Un recurso muy interesante es utilizar el formato condicional para destacar valores, clave. Este recurso hay que utilizarlo con cabeza ya que, si abusamos de los colores, nos podemos encontrar con tableros demasiado recargados Por ejemplo, si queremos presentar la variación de ventas de un año a otro, podemos utilizar formato condicional para valores >0 y <0 utilizando el verde y el rojo.
Para resaltar más la presentación de los datos, crear formatos de celda personalizados, permiten mostrar la información de las celdas con las 31 Integración de Elementos en un panel
Tableros de mando con Excel 2013
Javier Roig Garro
características que queramos. Por ejemplo, si queremos para los números positivos poner un + delante y para los negativos un – el formato sería “+ 0,0%; - 0,0%”
Otra forma de presentar información en un formato gráfico puede ser utilizando fuentes de letra gráfica para mostrar valores de forma menos convencional.
Proteger Libro, Ocultar Hoja Estas tareas serían las últimas a realizarse, ya que están pensadas para impedir la modificación incorrecta de los datos Ocultar hojas Una vez completado el diseño, es recomendable ocultar las pestañas de datos y de cálculos para evitar confusiones.
32 Integración de Elementos en un panel
Tableros de mando con Excel 2013
Javier Roig Garro
El proceso de ocultar una pestaña es sencillo. Seleccionar con el botón derecho del ratón la hoja a ocultar y en el menú contextual que muestra, pinchar sobre “Ocultar”. Para volver a mostrar las hojas ocultadas, seleccionar cualquier pestaña con el botón derecho del ratón y en el menú contextual seleccionar “Mostrar…” y a continuación seleccionar las hojas a mostrar.
Una vez ocultadas las hojas que no queramos que se puedan acceder y para evitar que se puedan volver a mostrar, deberíamos proteger el libro y la hoja para no se modifiquen las celdas con las fórmulas Proteger Libro Para proteger el libro ir a la solapa Revisar y activar “Proteger Libro”, se abre una nueva ventana dónde se puede introducir una contraseña. Si se protege el libro con contraseña, no se podrá desproteger sin introducir nuevamente esa contraseña.
33 Integración de Elementos en un panel
Tableros de mando con Excel 2013
Javier Roig Garro
Esta funcionalidad evita que se puedan volver a mostrar las hojas ocultadas, eliminar hojas existentes o que se puedan añadir nuevas hojas al libro. Proteger Hoja Por defecto, todas las celdas se bloquean cuando se activa la protección por hoja, por lo que tenemos que seleccionar aquellas celdas o controles que queramos se puedan modificar y desactivar esta opción de protección. Para desactivar el bloqueo cuando se active la protección de hoja, ir a ventana de formato de celda y en la pestaña “Proteger” desactivar la casilla “Bloqueada”
Una vez que estén todas las celdas configuradas, ya podemos ir a la pestaña “Revisar”, al grupo “Cambios” y activar el botón “Proteger hoja”. Al igual que en el caso del libro, podemos introducir una contraseña.
34 Integración de Elementos en un panel