CREAR UN GRÁFICO TIPO TERMÓMETRO CON EXCEL PARA MOSTRAR INDICADORES

Hola a todos, siguiendo con los temas de Gráficos, ahora hablaré sobre el gráfico tipo termómetro para mostrar los valores de ciertos indicadores, pueden ser de producción, de gestión, de logística, en general todos, no interesando el área que se mida. En este caso vamos a ver un valor en formato de porcentaje y que se representará en dicho gráfico..

image

En la figura vemos el termómetro que no es si no la combinación de un gráfico de columnas y de un dibujo que consta de 4 formas dos círculos y dos rectángulos con bordes redondeados. En la siguiente figura veremos las partes del termómetro.

image

Para crear el gráfico de columna, sólo necesitamos el valor en porcentaje obtenido para el indicador, este valor debe estar en una celda. Luego se le da la presentación y el formato como para que sólo se vean la escala del 0% al 100% y la columna.

image

Luego se arma el termómetro para luego combinarlo con el gráfico de columna, hay que usar siempre las herramientas para organizar las formas y el gráfico, traer adelante, traer al frente,enviar al fondo y enviar atrás.

image

Finalmente, vemos una flecha que nos dice si el indicador esta creciendo o decreciendo, esto se calcula en base a un porcentaje mínimo permitido. Tanto el valor del indicador como la flecha están en celdas, la flecha tiene un formato Wingdings, cuyo sentido cambia de acuerdo al resultado de la fórmula condicional insertada en dicha celda.

image

Para ver en detalle la construcción de dicho gráfico adjunto el documento. Aprovechenlo y apliquenlo a sus reportes y Dashboards.

Gráfico Tipo Termómetro.xlsx

Anuncios

CÓMO CREAR UN GRÁFICO TIPO ECUALIZADOR EN EXCEL PARA MEDIR EL VOLUMEN DE VENTAS

En cualquier empresa es necesario medir las ventas en base a las metas u objetivos trazados, dichos valores pueden ser representados mediante gráficos de diferentes tipos, como gráficos de columnas, circulares, barras, etc. En esta oportunidad veremos como representar los volúmenes de ventas reales vs los volúmenes objetivo de la empresa pero con un gráfico muy particular que simula un ecualizador de audio.

image

En la figura vemos que el tablero consta de dos partes, a la izquierda las celdas donde se ingresan los valores mínimo y máximo de la escala y los valores de las ventas objetivo y real de distintos servicios o productos (Q1,Q2, Q3 y Q4). A la derecha está el gráfico tipo ecualizador cuyo diseño se basa en formas, ¡Sí!, está diseñado con formas y no con los gráficos de Excel, “No es un gráfico de columnas”. Las formas usadas son 3, el rectángulo, el rectángulo redondeado y cuadros de texto.

Es necesario también cálculos en celdas auxiliares, donde se hacen referencias a los valores colocados en el tablero, para luego usarlos para el cálculo de líneas totales de cada columna del ecualizador y su repartición por colores (verde, amarillo y rojo). La repartición se logra con una fórmula que da resultados proporcionales a la escala máxima para un número de líneas máxima de 13. El máximo número de líneas para la franja verde es de 10, para el amarillo 2 y el rojo 1. Para las líneas del ecualizador se trabaja con el caracter “pipe” ( | ), que conjuntamente con la función de Excel “Repetir” y el número proporcional obtenido por cada color, se observará la variación en el número de líneas de cada columna del ecualizador. este punto se entenderá mejor con el ejemplo del enlace que colocaré debajo de este contenido.

image

Si analizamos como está diseñado el gráfico, veremos que son formas, por ejemplo cada columna está formada por 3 cuadros de texto, cada uno por un color, y las líneas están haciendo referencia con las celdas donde se encuentran los pipes.

image

La referencia que vemos en la barra de fórmulas es la del cuadro de texto donde están las líneas verdes, pero el detalle es que hay que darle sentido vertical a las líneas y no como la vemos en las celdas auxiliares que están de forma horizontal.

Así mismo los valores del eje vertical y los volúmenes de ventas que se encuentran en el eje horizontal, están referenciados en cuadros de texto desde las celdas del tablero. Los colores y los efectos de las columnas del ecualizador son trabajados con la ficha Formato de cada forma.

image

Para propósitos de poder ver variaciones constantes de los volúmenes de ventas y el comportamiento del gráfico como ecualizador, se añadieron valores aleatorios con la función aleatorio, en un rango cualquiera, pero dentro del rango establecido en la escala. Para ver dicho comportamiento se debe mantener apretado la tecla F9, así como en el gráfico siguiente.

Ecualizador

Aquí el enlace. Aprovechen, espero comentarios. Hasta la próxima.

Gráfico Ecualizador.xlsx

CÓMO CREAR UN VELOCÍMETRO EN EXCEL PARA ANÁLISIS ABC

El análisis ABC es un método que se basa en el principio de Pareto y que se usa con mayor frecuencia para la gestión de inventarios, en donde la clase A son los artículos que representan el 80% del stock total, la clase B el 15% y la Clase C el 5%. Una forma de medir estos valores es mediante un gráfico tipo velocímetro.

image

Por ejemplo, tenemos un análisis ABC de Clientes, los valores obtenidos lo tenemos en la siguiente tabla:

image Para construir el velocímetro se deben seguir los siguientes pasos:

image

Vemos en el recorte anterior que se deben llenar dos tablas, primero con los valores para crear el Dial y el segundo con los valores para crear la aguja del velocímetro. Además debemos calcular el ángulo del dial en radianes.

Las fórmulas se podrán ver con mayor detalle en el enlace que colocaré debajo de éste contenido.

1RA PARTE: Creación del Dial

– Se escoge un gráfico de anillo y se colocan los valores de la primera tabla como una sola serie de datos. Se quita el título y las leyendas.

image

– Se hace un giro de 270° al anillo y luego se dan nuevos colores a los segmentos, el segmento turquesa se cambia a sin relleno, el azul se cambia a rojo, el rojo a amarillo y finalmente el verde a verde más intenso. Asimismo el tamaño del agujero se reduce.

image

– Se colocan las etiquetas de datos, pero mostrando las categorías y quitándoles las líneas guía. Se reacomoda para que se encuentren en los bordes de cada segmento.

image

2DA PARTE: Creación de la aguja

– Se quitan el relleno y contorno del área de gráfico, agrandamos el gráfico y las etiquetas de datos. Par el diseño de la aguja creamos una segunda serie dentro del gráfico anterior, pero luego cambiamos el gráfico de anillo resultante a un gráfico de dispersión sin marcadores. Se quitan las líneas de cuadricula.

image

– Se modifican las escalas tanto del eje x como del y para poder centrar la aguja y parezca que está marcando sobre el dial. Se cambia el grosor y la forma de la línea. Se escoge una forma de flecha y con tipo inicial de flecha redondo.

image

– Finalmente se quitan los ejes y listo está el velocímetro.

El velocímetro creado es un o básico, en entradas posteriores veremos más tipos de velocímetros y por supuesto otros gráficos como son los de forma de termómetro, semáforo y ecualizador. Espero sus comentarios.

EJEMPLO DEL VELOCÍMETRO EN EXCEL

FUNCIONES PERSONALIZADAS EN EXCEL

Excel dispone de más de 350 funciones en 13 categorías, sin embargo muchas veces deseamos funciones más específicas para nuestro trabajo y dependiendo del área laboral al que nos desempeñemos. Excel permite mediante codificación en Visual Basic crear nuestras propias funciones, las cuales por defecto son almacenadas en una 14° categoría llamada “Definida por el usuario”.

Para crear la función personalizada es necesario abrir el editor de Visual Basic desde la Ficha Desarrollador o presionando las teclas ALT + F11, se inserta un Módulo y luego un procedimiento pero con la opción de Función, se coloca el nombre de la función y finalmente el código.

image

Una función tiene la siguiente sintaxis:

image

Veremos los códigos de algunas funciones personalizadas:

  •  Función Factorial

image

Ejemplo: Factorial(4) = 24

  • Función para invertir el orden de un valor numérico o texto

image

Ejemplo: Invertir(24578) = 87542 , Invertir(ATR-2) = 2-ATR

  • Función para limpiar un valor alfanumérico

image

Ejemplo: Limpia(ABC-124, 1) = 124 , Limpia(ABC-124 , 2) = ABC- , Limpia(ABC-124 , 3) = ABC

  • Función para Concatenar un rango de valores

image

Ejemplo: 

Función Concat

  • Función para obtener las iniciales de nombres propios

image

Ejemplo: 

Iniciales

Y así podemos crear infinidad de funciones según nuestra necesidad, la próxima entrada veremos más funciones personalizadas.

FITXURE BRASIL 2014 CON EXCEL

Pelota de fútbol  Aprovechando estas fechas en las que se vive la fiebre del Mundial de Futbol, en esta entrada tenemos un fitxure del mundial hecho 100% en Excel. Consta de un Menú inicial de donde se puede acceder a las diferentes Tablas pos cada Grupo, además de una Tabla General de la 1ra Ronda y de un Cuadro donde se colocan los resultados de Octavos, Cuartos, Semifinal y Final.

Fitxure

Para cada grupo se observan datos de la sede, los partidos y el estado, con celdas para colocar los resultados de cada partido.

Grupos

Los cálculos de partidos ganados, partidos empatados, partidos perdidos, goles a favor, goles en contra, diferencia de goles, puntos totales y partidos jugados salen automáticamente y de forma fácil de acuerdo a los resultados de cada partido.

Final

Se trata realmente de una aplicación hecha a base de fórmulas y funciones para los cálculos, formas o dibujos para los vínculos entre hoja y hoja, imágenes para los estadios, mascota y logo del mundial  y formatos de celdas para el diseño general como son colores, bordes, tipo y color de fuente, etc.

Debajo ´se encuentra el vínculo para descargar ésta útil aplicación que les servirá para hacer un buen seguimiento al mundial, espero les sirva, sobre todo a los fanáticos del futbol.

Pelota de fútbol  DESCARGAR FITXURE BRASIL 2014  Pelota de fútbol

LAS TABLAS DINÁMICAS

Una tabla dinámica es una de las herramientas más poderosas de Excel, pero también es una de las herramientas que más usuarios de Excel no usan o no lo usan correctamente. .

Las tablas dinámicas permiten resumir y analizar fácilmente grandes cantidades de información con tan sólo arrastrar y soltar los diferentes campos que formarán parte del reporte. Si todavía no lo usas, posiblemente estas perdiendo mucho tiempo en hacer reportes tediosos, cuando fácilmente se podrían hacer usando las tan prácticas Tablas Dinámicas.

Reportes flexibles

Es verdad que puedes formar muy buenos reportes con lo que ya sabes de Excel, pero imagina la siguiente situación. Ya has creado un gran reporte que compara el total de ventas en las diferentes regiones del país, pero ahora tus superiores han pedido que hagas otro reporte que compare las ventas de cada región con los tipos de productos vendidos. Y por si fuera poco, después de terminar el segundo reporte, te piden un tercero para comparar las ventas de los productos pero ahora por cada ciudad del país. Es muy probable que tengas que empezar desde cero para crear los nuevos reportes.

Afortunadamente Excel tiene la funcionalidad de tablas dinámicas que ayuda a resolver este problema. Al utilizar una tabla dinámica podrás crear los reportes sin escribir una sola fórmula, pero lo más notable será que podrás arreglar el reporte de una manera dinámica de acuerdo a tus necesidades.

El verdadero problema de las tablas dinámicas

Muchos usuarios de Excel evitan el uso de las tablas dinámicas porque parecieran muy complicadas a primera vista. Aunque las tablas dinámicas pueden parecer desafiantes, la realidad es que el problema radica en que muy pocas veces se explican adecuadamente. Realmente si nos ponemos a comparar, las funciones son más complicadas de entender que una Tablas dinámica, con lo que no quiero decir que las Tablas dinámicas reemplazan en su totalidad a las funciones, si no que dichas Tablas permiten con mucha más rapidez la presentación de un reporte o informe.

Como crear una Tabla Dinámica

Para la creación de una Tablas dinámica necesitamos una información en forma de Tabla o Base de datos es decir con un encabezado de títulos en la fila superior y con datos en celdas únicas, no se permiten celdas combinadas por ningún motivo. Un ejemplo puede ser como en la imagen:

image

Las Tablas dinámicas para armar un informe reconoce dos conceptos importantes dentro de la información: Campos (columnas del rango, tabla o base de datos), Elementos de campo ( valores sin repetición de cada fila o registro de un campo) .

En la imagen un Campo esPaís Destinatario”, y sus Elementos de campo son Argentina, Brasil, España y Reino Unido. Así mismo sucede para los demás campos.

Por ejemplo si queremos hacer un Informe del importe total vendido a cada cliente por país destinatario sólo de las categorías de productos bebidas de la base de datos mostrada, procedemos de la siguiente manera:

1. Colocándonos en la primera celda de la Base de datos, vamos a la ficha Insertar, y presionamos el icono de Tabla dinámica y saldrá un cuadro de dialogo donde aparecerá por defecto el rango de nuestra información, presionamos aceptar y veremos en una hoja nueva el área donde colocaremos nuestros campos para formar el informe.TD2

2.. Para colocar los campos dentro del informe tenemos  una lista de Campos que se encontrará en la parte derecha de la hoja de cálculo, el acomodo de los campos será de acuerdo a la siguiente imagen animada.

TD3

3. Finalmente para una mejor presentación debemos usar las herramientas de Tabla dinámica que sale inmediatamente en la banda de opciones cuando se inserta la Tabla dinámica, dichas herramientas están formadas por dos fichas: Opciones y Diseño. Vea la imagen animada siguiente.

TD4

4. Siempre que cambiemos alguna información en el Origen de datos, para poder ver los resultados en la Tabla dinámica, debemos ir a la ficha Opciones, y presionar el botón Actualizar.

TD5

En una próxima entrada veremos más propiedades y herramientas de Tablas dinámicas e inclusive con sus propios gráficos, que por que cuyos valores de series provienen de una Tabla dinámica, se denominan Gráficos dinámicos.

GRAFICOS ESPECIALES PARA MOSTRAR INDICADORES

En el mundo empresarial son fundamentales los indicadores, también conocidos como KPI’s para decidir el rumbo de la empresa. Los gráficos son una inestimable ayuda para ello, pues facilitan al usuario la transición entre el “conocimiento” y la “toma de decisiones”.

Los gráficos que tiene Excel son: de columnas, barras, líneas, circulares, de dispersión, área, etc. Pero también podemos combinar éstos gráficos con formas y con ciertos cálculos con fórmulas matemáticas y funciones de Excel para obtener gráficos especiales que nos interpretarán mejor los indicadores. Realmente Excel nos sorprende con la cantidad de variantes en gráficos que se puede crear con él, entre tantos podemos nombrar los siguientes:

GRAFICO TIPO VELOCÍMETRO: Un ejemplo de tipo de gráfico que ayuda a interpretar correctamente y de un vistazo el conocimiento que se infiere a la información, es el gráfico de tipo velocímetro. Se trata de un gráfico que compara dos magnitudes, una de las cuales representa un objetivo a alcanzar y la otra el valor realmente conseguido hasta el momento. Existen varios diseños como el que tiene un dial lineal y el que tiene un dial circular.

image

GRAFICO TIPO TERMÓMETRO:  Este tipo de gráficos nos permiten visualizar fácilmente indicadores de cumplimiento, medir el alcance que se obtuvo en los presupuestos diseñados en el área de ventas, costos, contabilidad compras etc., ofreciéndonos una percepción gráfica muy rápida de la situación que se tiene.

image

GRAFICO TIPO ECUALIZADOR: Este tipo de gráficos nos permiten comparar valores actuales y las metas u objetivos trazadas, como por ejemplo volúmenes de ventas mensuales, semanales, etc. 

image

GRAFICO TIPO SEMAFORO: Estos gráficos nos dan una alarma visual y rápida sobre el estado actual de la empresa en base a un objetivo trazado. Nos indican con tres colores verde, amarillo y rojo si el indicador analizado esta por encima de la meta, esta dentro de la meta o por debajo de ella.

image

La aplicación más importante de estos gráficos es para el diseño de Tableros de mandos, Dashboards y Balanced Scorecard como parte del Plan Estratégico de la empresa.