Gráficos velocímetro II

Continúa de la nota anterior.

Procedamos a la parte difícil, la aguja de nuestro velocímetro. Esto se hace con un gráfico de dispersión XY (que es distinto a un gráfico de líneas). Para lograr que la aguja marque la proporción exacta en el disco (i. e. la proporción de circunferencia correcta), necesitamos convertir el alcance (siguiendo el ejemplo, 88%) en radianes para posteriormente, con las funciones SENO y COS, calcular las coordenadas x y y del punto correspondiente. Luego simplemente agregamos el punto (0, 0) para que Excel los una con la línea correspondiente.

Recordemos los datos a graficar y la tabla auxiliar que ya hemos hecho para la elaboración del disco:

Calculemos ahora cúantos grados debe medir cada punto porcentual. Para ello escribimos en la celda B8 la fórmula:

=(B3-D2)/(D5-D2)*PI()

Resultando 1.837571.

Ahora elaboramos en A10:C12 otra tabla auxiliar:

En B12 escribimos la fórmula:

=-COS(B8), y en C12:

=SENO(B8)

Con esto tenemos ya las coordenadas de los dos puntos que necesitamos: (0, 0) y (0.2636, 0.9646). El siguiente paso es copiar estas dos puntos en la gráfica. Para ello seleccionamos el rango A10:C12, y damos Edición - Copiar (o Ctrl + Insert). Seleccionamos los datos de la gráfica (el "disco") y damos Edición - Pegado especial... (no clic derecho - Pegado especial...). Excel mostrará el siguiente cuadro de diálogo:

Seleccionamos los valores mostrados y damos Aceptar. La gráfica queda:

Evidentemente no es lo que buscábamos. Seleccionamos la segunda serie de datos (la exterior), y damos clic derecho - Tipo de gráfico - Dispersión XY subtipo Dispersión - Aceptar.

Seleccionamos el Eje Y, clic derecho - Formato de ejes... Escala. En valor mínimo escribimos -1, en valor máximo 1 y en Eje de valores (X) cruza en: establecemos 0. Repetimos los mismo con el eje Y:

Damos clic derecho en la nueva serie de datos, Formato de serie de datos... y en Tramas - línea seleccionamos Personalizada y el estilo, color y grosor preferido. En Marcador seleccionamos Ninguno. Aceptar.

Si queremos reducir el tamaño de la aguja entonces aumentamos la escala de ambos ejes. Finalmente eliminamos ambos ejes y sus etiquetas:

Para obtener los colores exactos de Sales Force, necesitamos modificar la paleta de colores de Excel, tema de otra futura nota.

Gráficos velocímetro

Varias empresas utilizan los servicios de Sales Force (por cierto, uno de los peores softwares que he utilizado. Está a años luz de la flexibilidad y facilidad de uso que nos proporciona Excel. Realmente, en cuanto a confiabilidad de la información es absolutamente pésimo. A menos que el usuario tenga un PhD en bases de datos) para gestionar su información. Sales Force utiliza gráficos tipo velocímetro para mostrar alcances:

Excel no soporta de forma predeterminada este tipo de gráfico. Sin embargo, combinando un par de gráficas, podemos obtener algo prácticamente idéntico. Esto fue lo que logré:

Este gráfico es en realidad un gráfico tipo anillo de cuatro secciones (de las cuales una ocupa el 50% del mismo y no tiene color de línea ni de relleno), combinado con un gráfico de dispersión XY (cuyo origen está en el centro del gráfico y que tiene dos puntos, que al unirlos con una línea, forman la "aguja" del velocímetro), con los formatos adecuados. Seguimos los siguientes pasos:

Comencemos por elaborar la escala (el "disco") del velocímetro. Supongamos que queremos graficar los siguientes datos:

Si deseamos que la escala vaya de 0 a 150%, con divisiones de 50%, elaboramos la siguiente tabla auxiliar:

Es decir, del lado izquierdo tenemos las etiquetas que llevarán los segmentos, y, del lado derecho, los valores proporcionales de cada segmento. Como cada uno de los primeros tres segmentos miden lo mismo (50%), ponemos el mismo valor (0.5, puede ser cualquier otro) a cada uno. El cuarto segmento, como ocupará la mitad del "disco", tiene un valor igual a la suma de los tres primeros (1.5).

Seleccionamos la tabla e iniciamos el Asistente para gráficos. En Tipo de gráfico, seleccionamos Anillos, subtipo Anillos. Clic dos veces en Siguiente... para ir directamente al paso 3. Aquí, en la pestaña Título no ponemos nada, en Leyenda desactivamos Mostrar leyenda y en Rótulos de datos activamos la opción Categoría (sugeridos). Damos clic en Finalizar obteniendo el siguiente gráfico:

A continuación, damos clic derecho en el gráfico, Formato de Serie de datos... y vamos a la pestaña Opciones. En Ángulo del primer sector, establecemos 270, Aceptar.

Finalmente, seleccionamos la sección inferior del gráfico (150%), damos clic derecho, Formato de punto de datos... En la pestaña Tramas, en Borde y en Área seleccionamos ninguno. Movemos las etiquetas, y les damos formato. Quedando:

Continuamos en la siguiente entrada.

Como encontrar Duplicados

Marcar Duplicados
Uno de los problemas mas frecuentes que nos encontramos los contadores en nuestro trabajo es tener duplicidad de elementos en una lista donde no debe de tenerlos.

Cuando estamos trabajando con una lista de unos cuantos elementos no hay problema, saltan a la vista, pero cuando la lista es de 5000 renglones y por algun motivo no es aconsejable ordenar estamos en problemas.


Para esto propongo usar el formato condicional


1.- Format-Contional Formating

2.- “Formula is” y escribir lo siguiente: “=COUNTIF($F$18:$F$22,F18)>1″Donde “$F$18:$F$22″ es el rango donde se pueden repetir los valores y “F18″ es la primera celda de ese rango (Noten que no tiene los signos “$”)

3.- En el boton “Format” agrega los formatos que sean de tu gusto y da click en “Ok” para finalizar
Si esto es de tu agrado y tienes que hacerlo continuamente tienes la opcion de usar VBA para automatizarlo, el codigo seria el siguiente:



Sub Mark_Duplicates()

Dim Rng_Cmplte As String

Dim Rng_Cell As String

Rng_Cmplte = Selection.Address

Rng_Cell_a = Range(ActiveCell.Address).Address

Rng_Cell_b = Replace(Rng_Cell_a, “$”, “”)

Selection.FormatConditions.Delete

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ “=COUNTIF(” & Rng_Cmplte & “,” & Rng_Cell_b & “)>1″

With Selection

.FormatConditions(1).Font.Bold = True

.Italic = False

.ColorIndex = 3

End With

Selection.FormatConditions(1).Interior.ColorIndex = 6

End Sub


Posiblemente este codigo sea sujeto a perfeccionarse pero hasta ahora lo he usado frecuentemente sin ningun problema.

Se aceptan sugerencias, Leonel, algun comentario??

Autor invitado

Agradezco la colaboración de Ricardo López, de IsSolved.com, quien publicó la nota anterior "Adiós a ese molesto Get Pivot Data".

Aprovecho la ocasión para comunicar a mis lectores que, en reciprocidad con Ricardo, estaré publicando algunas notas en su blog. Les comunicaré cuando así sea. Asimismo, si alguno de ustedes desea realizar alguna consulta, profesional o no, puede contactarnos a cualquiera de los dos. Confío en que al ser dos autores, mejore la calidad de las notas y consultoría ofrecidas.

Marcar máximos y mínimos en gráficas

En la nota sobre gráficos anterior, aplicamos formato condicional a los valores máximo y mínimo de una gráfica de barras. Obviamente, aquella técnica no funciona si utilizamos una gráfica de líneas (o de dispersión XY). En estos casos, lo que podemos hacer es marcar y/o identificar los respectivos valores, como en el siguiente ejemplo:

Como en el caso anterior, buscamos que los marcadores sean dinámicos, es decir, que en todo momento señalen el valor máximo y el mínimo, sin tener que hacer ningún ajuste manual. Para lograrlo seguimos los siguienter pasos:

La siguiente es la tabla de datos original y su respectiva gráfica:

Agregamos dos columnas auxiliares a nuestra tabla de datos, como sigue:

La columna C muestra únicamente el valor máximo de la tabla, mientras que la D solo tiene el valor mínimo. En C2 tenemos la fórmula:

=SI(B2=MAX(B$2:B$11), B2, NOD())

Y en D2:

=SI(B2=MIN(B$2:B$11), B2, NOD())

A continuación agregamos las dos columnas (series) al gráfico. Clic derecho en la gráfica, Datos de origen... y cambiamos el rango a A1:D13. Aceptamos el cuadro.

En este punto la gráfica parece mostrar una sola serie de datos, luciendo exactamente igual que la gráfica original. Pero en realidad tenemos tres series de datos, de las cuales dos tienen un solo punto de datos que a su vez se superpone a algún punto de la primera serie. Para distinguirlas, necesitamos realizar los siguientes ajustes:

Seleccionamos la Serie 2 ("Max"). Dado que esto puede ser complicado, ya que está superpuesta en la Serie 1, podemos seleccionarla de la lista desplegable de la barra de herramientas Gráfico, la cual aparece automáticamente al seleccionar la gráfica:

o bién, seleccionamos cualquier otro elemento de la gráfica y pulsamos las teclas de dirección izquierda y derecha hasta que la mencionada lista desplegable muestre "Serie 2".

Clic derecho en la Serie 2 (o bién, tecla de menú contextual), clic en Formato de serie de datos...

En la pestaña Tramas, establecemos el marcador adecuado, y ajustamos estilo, colores y tamaño a nuestro gusto.

En la pestaña Rótulos de datos activamos Valor y Nombre de la serie (sugerido).

Repetimos estos pasos con la Serie 3 ("Min"), resultando la gráfica mostrada al inicio.

Cambiamos algunos valores de la tabla para ver que los marcadores se ajustan adecuadamente.

Como siempre, el lector sabrá mejorar los formatos utilizados.

Adios a ese molesto GetPivotData()

Con frecuencia me cuestionan si las nuevas funcionalidades de Excel tienen la finalidad de hacer la vida mas facil o mas difícil a los usuarios… y mi respuesta casi siempre es que las nuevas caracteristicas tienden a hacer mas facil la vida de los usarios si estos se toman la molestia de pagar el precio de poner un poco de atención a los cuadros de dialogo y razonar sobre cual es la mejor manera de hacerlo.
El ejemplo clasico es la funcion GetPivotData que sin preguntar se activa cada vez que hacemos referencia a un valor que existe en una tabla pivote.
Esto no deja de tener algunas ventajas para un usuario experimentado, pero para la gran mayoría es un dolor de cabeza tener que lidiar con este favor no solicitado que nos hicieron los programadores de Microsoft.









La solucion es muy simple y muy poco conocida también siga estos pasos:
1.- En la barra de herramientas de clic derecho y vaya al menú “Customise” o “Personalizar”























2.- Busque en el tab “Commands” el menú “Data” y busque el comando “Generate Pívot Data”






















3.- Arréstelo a la barra de herramientas de su preferencia y listo.

A partir de ahora esta listo para evitar esos molestos GetPivotData y solo utilizarlo cuando mejor le convenga.

Tal vez ahora se preguntara “mmmm y como para que es bueno el dichoso GetPivotData?” y claro que sirve, de hecho es una funcion que ahorra bastante tiempo y evita errores.

Pero eso sera la proxima vez.

Formato condicional en gráficas

Una usauria me pregunta si es posible dar formato condicional a una gráfica de barras, de forma que la columna con el valor mínimo tenga color rojo, y la que tenga el valor máximo sea verde.

Suponiendo la siguiente tabla de datos:

Si hacemos una gráfica de columnas estándar, obtenemos lo siguiente:

Desde luego, se puede cambiar manualmente el color a los puntos (barras) máximo y mínimo de la serie. La desventaja es que, si los datos son cambiantes, habrá que estar coloreando las barras cada que esto ocurra. Lo que buscamos es un formato condicional, automático, que en todo momento de el color adecuado a cada columna.

Excel no tiene una forma directa de hacer este ajuste. De cualquier forma, podemos simular este efecto, llevando a cabo unos cuantos ajustes en nuestros datos y en nuestra gráfica.
Iniciamos aquí el uso de las series auxiliares o dummy series, imprescindibles para elaborar varios tipos de gráficas avanzadas. Comenzamos por añadir a nuestra tabla de datos tres columnas, como sigue:


En la celda C2 tenemos la fórmula:

=SI(B2=MAX($B$2:$B$13),B2,NOD())

En D2:

=SI(B2=MIN($B$2:$B$13),B2,NOD() )

Y en E2:

=SI(ESNOD(C2)*ESNOD(D2), B2,NOD())

Con estas fórmulas, logramos que la columna C muestre únicamente el valor máximo de la serie, la D el mínimo y la E todos los demás. Las celdas restantes muestran resultados de "error". El objetivo de estas fórmulas es crear una matriz de números y valores #N/A. Recordemos de notas anteriores que al graficar, Excel ignora los valores de error y los interpola de forma automática.

El siguiente paso es ajustar el rango de origen de la gráfica. Damos clic derecho en cualquier parte de la gráfica y seleccionamos Datos de origen... y en Rango de datos escribimos el rango:

=Hoja1!$A$1:$A$13,Hoja1!$C$1:$E$13

Es decir, ignoramos la columna B. Aceptamos el cuadro obteniendo la siguiente gráfica:

Observamos que los valores máximo y mínimo tienen colores diferentes. El efecto se logra ya que en realidad estamos graficando tres series de datos (de las cuales las dos primeras constan de un solo punto), pero formuladas de tal manera que aparentan ser una sola.

Podemos ver también que las columnas o series (que antes eran puntos de una sola serie) no aparecen correctamente centrados en sus respectivas categorías. Para solucionar esto, damos clic derecho en la gráfica, clic en Tipo de gráfico... y cambiamos el subtipo de gráfico a Columna apilada (¿por qué?). Obtenemos:

Por último, dí unos cuanto ajustes cosméticos: cambié el color del área de trazado a blanco, el tipo de las líneas de división a línea punteada, y el color de cada serie. Además cambié, en la escala del eje vertical (y), el valor de la división principal a 2 y el de la secundaria a 1. Eliminé el borde del área del gráfico y la subleyenda Valores de la leyenda principal. El resultado final:

Si en este momento cambiamos los valores, y cambia la posición de los valores máximo y mínimo, veremos que los colores se ajustarán en consecuencia.