Formato de Números en Gráficos de Excel

En notas anteriores hemos hablado sobre formato de números en Excel, en especial la posibilidad de crear formatos personalizados.
En varias de las últimas entradas he hablado sobre gráficos en Excel. Ahora podemos combinar ambos temas y hablar de formato de números en gráficos de Excel.

Supongamos esta tabla que nos muestra el saldo de una cuenta imaginaria al final de cada mes

Creamos un gráfico de tipo Líneas



Los valores del eje de las Y son numéricos, y por lo tanto podemos aplicar formato de números, tal como lo hacemos con los rangos de los valores numéricos en las hojas de cálculo.
Por ejemplo, si queremos resaltar los valores negativos, haciéndolos aparecer en rojo, podemos aplicar el siguiente formato: #,##0;[Rojo]-#,##0

Los pasos a seguir son sencillos:

1 – Apuntando al eje de las Y abrimos el menú de formato del eje



2 – En la pestaña "Número" elegimos la opción Personalizado y seleccionamos el formato deseado



Podemos enriquecer un poco más la representación de los valores usando la opción de formato condicional en el patrón de números. Excel permite un formato condicional básico en el formato personalizado de acuerdo al siguiente patrón:

[condición]número positivo;[condición]número negativo;cero

Por ejemplo, si queremos que los números negativos aparezcan en rojo, los números positivos hasta 5,000 aparezcan en negro y los números mayores de 5,000 aparezcan en verde, aplicamos este formato personalizado:

[Verde][>5000] #,##0; [Rojo][<0]-#,##0>

Ahora supongamos que el límite inferior deseado del saldo es -6,000 y el máximo 6,000. Podemos agregar dos líneas que marquen estos límites

Para esto agregamos una columna de valores para cada límite en nuestra tabla para crear dos nuevas series



Seleccionamos el rango C2:D7, aplicamos Ctrl+C, seleccionamos el gráfico y en el menú de pegado especial marcamos "nueva serie" y "columnas", cambiamos un poco el formato estándar de Excel y obtenemos

El toque final que podemos dar es colorear el área definida por las líneas de límite, para enfatizar los puntos que caen en la "zona permitida".

Para hacer esto agregamos dos nuevas series, usando los datos ya existentes en el rango C2:D7 y les cambiamos el tipo de gráfico a "columnas apiladas".

Los pasos son los siguientes:

1 - Seleccionamos el rango C2:D7 y pulsamos Ctrl+C

2 - Seleccionamos el gráfico y en el menú Edición activamos Pegado Especial. En el diálogo marcamos "nueva series" y "columnas".

3 - Las nuevas series aparecen como líneas que se superponen a las ya existentes. Para seleccionarlas apuntamos a los marcadores, pulsamos el botón derecho y en el menu que se abre activamos "tipo de gráfico". Elegimos "columnas apiladas". Hacemos lo mismo con la segunda serie y obtenemos este gráfico

4 - Seleccionamos una de las series y abrimos el menú de formato de series de datos. En la pestaña "opciones" fijamos el ancho del rango en 0 (cero),



y en la pestaña "Tramas" seleccionamos la opción "ninguna" para Bordes y cambiamos el color del fondo de manera que coincida con el de la otra serie



Elegimos la otra serie y en el menú de formato de datos de serie, seleccionamos la opción "ninguna" para Bordes.



5 - Ahora tenemos que corregir la definición del eje de la X, para evitar el espacio en blanco entre el 0 y el primer valor de las categorías. Para esto, señalamos el eje de las X y on el botón derecho del mouse abrimos el menú de formato de ejes. En la pestaña "Escala" quitamos la marca de la opción "Eje de valores (Y) cruza entre categorías"



Finalmente obtenemos este gráfico.



Los valores de los límites, y el área comprendida entre ellos, son dinámicos. A medida que cambiamos los valores de los límites, las líneas y el área se irán adaptando.

Categorías: Graficos_

Technorati Tags: Excel, Graficos en Excel

Gráficos Excel – Líneas o XY (Dispersión).

Entre los tipos de gráficos de Excel se encuentran el tipo Líneas y el XY (Dispersión).




Ambos tipos, que parecen similares y pueden producir gráficos idénticos en apariencia son, sin embargo, muy distintos.

La diferencia entre ambos tipos reside en que en el gráfico de tipo Líneas los valores del eje de las X (las categorías) son consideradas como cadenas de texto, mientras que en el gráfico de tipo XY, los valores del eje de las X son considerados números.
Existe una excepción a esta regla, que consideraremos más adelante.

Resaltaremos las diferencias entre estos tipos de gráficos con un ejemplo.

Supongamos esta tabla



Construimos un gráfico de tipo Líneas, seleccionando el rango A2:B7, borramos la leyenda y el fondo



Si prestan atención verán que los valores en el eje de las X están ordenados por orden de aparición en la tabla y que las distancias entre ellos son constantes y equivalentes.
Es decir, Excel trata a estos datos como texto, a pesar de ser números.

Ahora volvamos a usar nuestra tabla, pero esta vez construimos un gráfico de tipo XY (Dispersión) con puntos de datos conectados por líneas



Como pueden ver, obtenemos un gráfico completamente distinto. En este tipo de gráficos, Excel considera los valores de X como números y los ordena en forma creciente a partir del origen. Los valores del eje de las X son números calculados por Excel de acuerdo a los valores de la tabla y no rótulos, como en el caso anterior.

En resumen, cuando los valores del eje de las X sean categorías (cadenas de texto, aún cuando tengan forma de número, como los números de catálogo por ejemplo), debemos usar el gráfico de tipo Líneas. Cuando los valores del eje de las X son números, usaremos el gráfico de tipo XY.

Como decíamos al principio, hay una excepción a esta regla en lo que hace a los gráficos de tipo Líneas. Cuando Excel reconoce los valores de las X como fechas, aplica al gráfico de Líneas un tipo especial de eje X, el eje de tiempo, un eje numérico que representa fechas.

Como ya hemos explicado, Excel representa las medidas de tiempo como una serie de números, donde la parte entera representa los días y la fracción decimal las horas, minutos y segundos de ese día.
Cambiemos los valores de nuestra tabla por números que Excel pueda reconocer como fechas:



Los valores de X son fechas con formato [$-C0A]d-mmm;@

El gráfico de Líneas se convierte ahora a:



Como pueden ver, Excel está tratando a los valores del eje de las X como fechas, es decir, como valores numéricos. Para estudiar esto un poco más de cerca, abrimos el menú de formato del eje de las X (categorías)



En la pestaña Escala, podemos ver que Excel llama ahora al eje "eje del tiempo". Los valores "mínimo" y "máximo" aparecen con formato de fecha, y en las definiciones de unidad principal y secundaria tenemos una ventanilla que nos permite elegir entre "días", "meses" y "años".

De esto se desprende una limitación del eje de tiempo: no podemos representar intervalos menores a un día completo.
Por ejemplo, si tenemos esta tabla, donde los valores de X son intervalos de 6 horas dentro de un mismo día(los valores de X tienen formato [$-C0A]d-mmm hh:mm;@)



Todos los puntos aparecerán sobrepuestos sobre el mismo punto de la X (la misma fecha).



En cambio, la misma tabla representada en un gráfico de tipo XY, dara




Fuente para esta nota: Jon Peltier en Tech Trax


Categorías: Graficos_

Technorati Tags: Excel, Graficos en Excel

Tabla de Posiciones para campeonato de fútbol con MS Excel

Después de publicar la tabla de posiciones en mi nota sobre como construir una tabla de posiciones para un campeonato de fútbol con Excel he recibido algunos pedidos de agregar un método de manera que al ingresar los resultados de los partidos la tabla se vaya actualizando automáticamente.

La tabla que muestro en esta nota, y que pueden descargar aquí, permite construir una tabla de posiciones para un campeonato de fútbol en el que participan un mínimo de seis equipos y un máximo de veinte.
(Actualización 07/11/2006: dado problemas con Excel 2003, he reemplazado el archivo)

(Actualización 09/11/2007: la tabla ampliada para 40 equipos se puede descargar aquí)

Las reglas para determinar las posiciones son:
1 – 3 puntos por partido ganado, 1 por empate y 0 por derrotas.
2 – en caso de igualdad de puntos el primer criterio es diferencia de goles; en caso de persistir la igualdad se define por mayor cantidad de goles a favor.

Primero daré las instrucciones para el uso del modelo y luego, para los que se interesen, una explicación detallada de cómo funciona.

El cuaderno consta de tres hojas: Tabla de posiciones, Resultados y Parámetros.

El primer paso a dar es ir a la hoja Parámetros







y fijar las reglas de puntaje (rango B2:B4), fijar la cantidad de equipos que participarán en el torneo (lista desplegable en la celda B6) e introducir los nombres de los equipos (reemplazando "Equipo 1", "Equipo 2", en la columna D).

El segundo paso es abrir la hoja Resultados




e introducir los resultados de los partidos jugados hasta el momento. Importante: los resultados deben introducirse de acuerdo al patrón "goles equipos local-goles equipo visitante". Por ejemplo "3-2" para una victoria local o "2-3" para una victoria del equipo visitante. Los resultados son introducidos como texto.
La tabla en la hoja Tabla de Posiciones se irá actualizando automáticamente a medida que se anoten los resultados.

Por ejemplo, en la primera fecha del torneo Equipo 1 venció a Equipo 2 por 2 goles a 1; Equipo 3 perdió frente a Equipo 4 por 4 goles a 2. En la hoja resultados anotamos




La tabla de posiciones se actualizará a:



He ocultado las columnas que muestran las estadísticas como local y visitante de cada equipo, por claridad. La versión completa de la tabla es



Hasta aquí todo lo que hay que saber para usar el modelo.


Ahora daré una explicación detallada del modelo.

El cuaderno contiene cinco hojas, las tres ya mencionadas y dos ocultas: Posiciones Auxiliar y Resultados Auxiliar. He definido la propiedad "Visible" de estas dos hojas como xlSheetVeryHidden y no por lo tanto se las puede volver visibles con el menú "Formato--Hoja--Mostrar". Una explicación de cómo volver estas hojas visibles se puede leer en mi nota sobre como ocultar hojas en Excel.

La hoja Resultados Auxiliar contiene una matriz de 20 filas por 20 columnas y está ligada a la hoja Resultados por medio de la macrofunción EVALUAR. Este tipo de funciones (funciones macro Excel 4) funcionan en la versiones posteriores a Excel 4 cuando están incluidas en nombres. En nuestro caso hemos definido el nombre "resultado" con la fórmula

=SI(ESBLANCO(Resultados!D3),"",EVALUAR(Resultados!D3))




Lo que hace =EVALUAR(Resultados!D3) es transformar la cadena de texto del resultado en el resultado de la operación (en nuestro caso, 2-1 = 1).
Esto nos permite establecer quien es el ganador, el local o el visitante, o si hubo empate. La regla es sencilla: si el resultado de EVALUAR(Resultados!##) es positivo, el ganador ha sido el local; si EVALUAR da negativo, el ganador ha sido el visitante; si el resultado es 0, se trata de un empate. Esto nos permitirá luego contar cuantos partidos ganados, perdido y empatados ha tenido cada equipo como local y como visitante.
La función de la parte =SI(ESBLANCO(Resultados!D3),""… es evitar resultados #VALOR!, ya que EVALUAR no puede evaluar un celda vacía.

La hoja Posiciones Auxiliar es el "motor" del modelo. Aquí realizamos todos los cálculos que nos permiten establecerla posición de cada equipo en la tabla y sus estadísticas.

La tabla cuenta con un rango para las estadísticas de los equipos como locales, otra para las estadísticas de los equipos como visitantes y la suma de las columnas respectivas de ambos rangos, lo que nos da el total para cada equipo.
La fórmula en la columna A calcula la posición de cada equipo con la función JERARQUIA, basada en el resultado de la fórmula de la celda correspondiente en la columna AA. Más adelante explicaremos estas fórmulas.

Las celdas en la columna C contienen la fórmula =CONTARA(Resultados!C3:H3), que de acuerdo a la cantidad de resultados anotados en la hoja Resultados calcula la cantidad de partidos jugados como local.

Las celdas en la columna D contienen la fórmula

=CONTAR.SI('Resultados Auxiliar'!B3:G3,">0")

para calcular la cantidad de partidos ganados como local, contamos cuantos números positivos hay en la fila correspondiente del equipo en la hoja Resultados Auxiliar.
La cantidad de partidos empatados en la columna E se obtiene con la misma fórmula, cambiando la condición ">0" por "=0".
Los partidos perdidos se pueden calcular cambiando ahora la condición a "menor que cero" o simplemente como diferencia entre los ganados y los empatados.

Para calcular los goles a favor como local, sumamos los números a la izquierda del guión en los resultados anotados en la fila del equipo en la hoja Resultados. Para esto usamos la fórmula matricial

={SUMA(SI(ESNUMERO(--IZQUIERDA(Resultados!C3:H3,ENCONTRAR("-",Resultados!C3:H3)-1)),--IZQUIERDA(Resultados!C3:H3,ENCONTRAR("-",Resultados!C3:H3)-1)))}

Los goles en contra como local son calculados, de manera similar, con la fórmula matricial

={SUMA(SI(ESNUMERO(--DERECHA(Resultados!C3:H3,(LARGO(Resultados!C3:H3)-ENCONTRAR("-",Resultados!C3:H3)))),--DERECHA(Resultados!C3:H3,(LARGO(Resultados!C3:H3)-ENCONTRAR("-",Resultados!C3:H3)))))}

La diferencia de goles en la columna I es calculada por la obvia fórmula =G3-H3.

Finalmente, el puntaje es calculado por la fórmula

=D3*Partido_ganado+E3*Partido_empatado+F3*Partido_perdido

donde Partido_ganado, Partido_empatado y Partido_perdido son nombres que contienen las celdas de la hoja Parámetros donde definimos las reglas de puntaje.

El rango de las estadísticas como visitante (columnas K:R) contiene fórmulas distintas, ya que nos basamos en las columnas de la matriz en la hoja Resultados y no en las filas.

La cantidad de partidos jugados como visitante es calculada en la columna K por la fórmula:

=CONTARA(INDICE(Resultados!$A$3:$V$22,,FILA()))

Los partidos ganados como visitante los calcula en la columna L la fórmula

=CONTAR.SI(INDICE('Resultados Auxiliar'!$B$3:$U$22,,FILA()-2),"<0") es ="JERARQUIA(AA3,$AA$3:$AA$22)" es ="SI(S3=" s3="0,-2000000" rmula ="BUSCARV($A3,Posiciones_Auxiliar,COLUMNA(),0)" rango ="'Posiciones" class="category">Categorías: Funciones&Formulas_


Technorati Tags: Excel