Mapa dinámico en Excel

29 Oct Mapa dinámico en Excel

Crear un mapa dinámico en Excel

En el video tutorial ejemplificamos como podemos crear un mapa dinámico a medida en Excel. Los pasos a seguir son los siguientes:

1. Insertar un gráfico de dispersión vacío en la hoja activa.
2. Ir a formato de área del gráfico y utilizar un relleno con imágen. La imágen se puede obtener de una carpeta personal en la PC o bien del buscador en línea que ofrece Excel. Es importante que chequeemos los derechos de autor de las imágenes utilizadas y su atribución, para poder utilizarlas apropiadamente en nuestros proyectos.
3. En la pestaña de tamaño bloqueamos la relación de aspecto para poder adaptar el tamaño de la imágen al posicionamiento que queremos darle en la hoja activa. A su vez, quitamos los bordes de la imágen y ajustamos la transparencia al valor porcentual deseado para que no dificulte la lectura posterior de las burbujas de datos que construiremos.
4. El paso siguiente será construir una tabla con 4 encabezados, los cuales en el video tutorial son: Coordenada X, Coordenada Y, Región e Ingresos. En las columnas de coordenadas X e Y debemos estimar la ubicación de cada burbuja de datos que queremos colocar por ejemplo utilizando una escala de 1 a 10 para cada eje y colocar de esta forma el grupo de coordenadas para cada uno. Por ejemplo, en el video ubicamos a “América del Norte” en los valores 2 para X y 6,5 para Y, y a Oceanía en 8,5 para X y 2,5 para Y. Posteriormente copiamos en la columna de Región todas las regiones posibles que necesitamos representar.
5. Hacemos clic derecho sobre el gráfico y elegimos “seleccionar datos”, y creamos las dos series X e Y vinculándolas con los datos de las columnas X e Y del paso anterior.
6. Hacemos clic derecho sobre uno de los ejes en el gráfico y elegimos “formato del eje”, y configuramos los rangos de valores/límites entre 0 y 10 (cambiamos el modo automático a modo fijo). Repetimos luego el procedimiento para el otro eje.
7. Seleccionamos y eliminamos todos los ejes, títulos y líneas del gráfico.
8. Creamos una lista de validación sobre una celda en la parte superior del gráfico y la configuramos para que pueda elegirse de entre una lista de los productos disponibles (en el video son “Producto A”, “Producto B” y “Producto C”).
9. Aplicamos la función SUMAR.SI.CONJUNTO sobre la columna de Ingresos de la tabla mencionada en el punto 4 para vincular el gráfico, la tabla y el rango original de datos. En el video se utiliza la siguiente sintaxis: =SUMAR.SI.CONJUNTO($C$2:$C$22;$B$2:$B$22;G2;$A$2:$A$22;$M$2)
10. Hacemos clic derecho sobre el gráfico y elegimos “cambiar tipo de gráfico”, y seleccionamos el gráfico de burbujas.
11. Hacemos clic derecho sobre el gráfico y elegimos “seleccionar datos”, y comprobamos que los ejes X e Y estén bien vinculados a las series correctas. Y cambiamos el tamaño de las burbujas de la serie, por el rango de ingresos creado con la fórmula en la columna Ingresos de la tabla mencionada en el punto anterior.
12. Hacemos doble clic sobre una de las burbujas del gráfico para acceder al formato de serie, y en opciones de serie elegimos una escala de las burbujas en valor porcentual que se acomode visualmente al gráfico creado (evitando superposiciones de las burbujas).
13. Seleccionamos una de las burbujas y agregamos la etiqueta de series, cambiándola a tamaño de burbuja dentro de las opciones del formato. Posteriormente chequeamos en formato de etiqueta que el código de formato esté vinculado al origen (en la sección de Número), y editamos el color, tamaño y formato del texto a moneda.
14. Agregamos una columna adicional a la tabla del punto 4 con encabezado Máximo, y aplicamos una fórmula que nos devuelva sólo el valor más alto de todo el rango de la columna de ingresos cuando dicha fila posea el valor más alto. En el video, la sintaxis utilizada es la siguiente: =SI(H2=MAX($H$2:$H$8);H2;””)
15. Finalmente creamos una nueva serie sobre el gráfico, vinculando los mismos ejes X e Y que las otras columnas de la tabla, y vinculamos el tamaño de la burbuja de esta nueva serie a la columna Máximo creada en el punto anterior. De esta forma, quedará resaltada la burbuja con al valor más alto, distinguiéndose del resto mediante un formato distinto que podemos editar, como ser color, tamaño u otro formato de edición.

Te recomiendo ver el siguiente link con más videos de Tutoriales esenciales de Excel AQUÍ.

Salvo aclaración, todas las fórmulas y macros de este sitio están configuradas para aplicarse sobre la celda A1. Algunas fórmulas se encuentran encerradas entre llaves {} debido a que son fórmulas matriciales. Estas llaves no deben introducirse tecleándolas, sino que se generan automáticamente al aceptar la fórmula pulsando Control+Shift+Enter al mismo tiempo. Las fórmulas de este sitio son compatibles con versiones de Microsoft Excel® 2010 o superiores.