Mejorar gráficos estándar de Excel

Hace un tiempo que leo el blog de BizViz de Jorge Camoes, que trata sobre visualización de información. Es un blog interesante, que recomiendo leer a aquellos que usen Excel para elaborar y presentar información. El blog está escrito en portugués, lo cual no representa una barrera para el lector de habla castellana.
En una de las últimas notas, Jorge Camoes da un ejemplo de cómo mejorar los gráficos estándar de Excel.

Veamos este ejemplo. Dada una tabla con dos columnas de datos, años y ventas, el gráfico de columnas que Excel construye es el siguiente:




Más que empezar a analizar los defectos de este gráfico, veamos como lo podemos mejorar, sin mucho esfuerzo

En primer lugar borramos la leyenda, que coincide con el título (tenemos sólo una serie)



Luego quitamos el fondo del área del gráfico y el borde



En ambos ejes, elegimos una fuente más pequeña y quitamos la marca de Autoescala. De esta forma también logramos que todos los puntos en el eje e las X tengan un rótulo.



En el menú de formato de líneas de división, elegimos una línea más delicada



Ahora es el turno de cambiar la escala del eje del eje de las Y



Finalmente, podemos cambiar el color estándar de Excel a uno de nuestro agrado. El resultado final



Si estamos satisfechos del resultado, podemos guardar el gráfico como tipo personalizado definido por el usuario. De esta manera, no tendremos que volver sobre el proceso de mejorar el gráfico estándar de Excel, cada vez que queramos producir un gráfico de columnas.
Seleccionamos el gráfico y abrimos el menú de Tipo de Gráfico. En la pestaña Tipos Personalizados señalamos Definido por el Usuario y pulsamos Agregar




En el diálogo que se abre definimos un nombre y una descripción para el gráfico



A partir de ahora tendremos en la pestaña de Tipos Personalizados, un nuevo modelo de gráfico que podremos elegir.

Technorati Tags: MS Excel

Extraer el ultimo elemento de un texto en una celda en Excel

Ayer un compañero de trabajo me trajo el siguiente problema. Había recibido una lista productos de nuestra empresa como esta




Mi compañero quería poner los números de catálogo en una columna y las descripciones de los productos en otra.
Como pueden ver el problema consiste en que el número de catálogo es la última palabra en la celda y el número de palabras por celda es variable. Esto hace que no podamos emplear Datos—Texto en Columnas.

La solución consiste en hacer algunas manipulaciones con funciones de Texto. Las funciones que usaremos en este caso son: DERECHA, ENCONTRAR, SUSTITUIR y LARGO.

Nuestro objetivo es construir una fórmula DERECHA(A1, número de caracteres). El problema es establecer el número de caracteres para cada celda.

El primer paso consiste en identificar el separador de las palabras; en nuestro caso es el espacio (que en fórmulas de Excel representamos " "). El número de caracteres será el número total de caracteres en la celda menos el número de caracteres hasta la última palabra, es decir hasta el último espacio.

Tomemos el texto de la celda A1



Usamos la formula SUSTITUIR(A1," ","") que da como resultado

DARKBLUENIPPLEWITHOUTTRAY022052801

Sencillamente hemos eliminado los espacios reemplazando " " por "".


Este resultado lo usamos como argumento en la función LARGO

LARGO(SUSTITUIR(A1," ","")) que da como resultado 34

Ahora usamos la fórmula

SUSTITUIR(A1," ","*",LARGO(A1)-LARGO(SUSTITUIR(A1," ","")))

Que da como resultado

DARK BLUE NIPPLE WITHOUT TRAY*022052801

Es decir, hemos puesto un asterisco * entre la anteúltima y la última palabra.
El truco consiste en que LARGO(A1) calcula el total de caracteres en la celda incluyendo los espacios en blanco (39); la fórmula LARGO(SUSTITUIR(A1," ","")) hace el cálculo sin los espacios en blanco (34). La diferencia (5) es la posición del último espacio.

Ahora podemos encontrar la posición de * usando
ENCONTRAR("*",SUSTITUIR(A1," ","*",LARGO(A1)-LARGO(SUSTITUIR(A1," ",""))))

Que da como resultado 30

Ahora sabemos que el largo del texto es 39, y que nuestro separador de la última palabra se encuentra en la posición 30. Por lo tanto el largo de la última palabra es 9.

Así que usamos la fórmula

DERECHA(A1,LARGO(A1)-ENCONTRAR("*",SUSTITUIR(A1," ","*",LARGO(A1)-LARGO(SUSTITUIR(A1," ","")))))

Que nos da el resultado esperado.

Esta solución fue propuesta por Ken Wright y mencionada por David McRitchie

Para separar la descripción usamos la función IZQUIERDA de esta manera

=IZQUIERDA(A1,LARGO(A1)-LARGO(B1))

cuando en B1 hemos puesto la fórmula que nos da el número de catálogo.


Technorati Tags: MS Excel

Corrigiendo la posición del signo menos en números importados

Una tarea bastante corriente en Excel es importar datos de fuentes externas, como sistemas ERP (SAP, Oracle,etc) o bases de datos. Suele suceder que al importar los datos a Excel los números negativos aparezcan con el signo menos a la derecha del número; por ejemplo 550-, en lugar de -550.
El problema en estos casos no es tanto la estética como el hecho que Excel no interpreta estos datos como números, sino como texto.
Mostraremos como corregir esta situación con una fórmula.

Supongamos que hemos importado esta lista de un sistema externo



Si agregamos en la columna B la fórmula =ESNUMERO() vemos que aquellos con el signo menos a la derecha son considerados por Excel como texto.



En la celda B2 ponemos esta fórmula

=VALOR(SUSTITUIR(DERECHA(ESPACIOS(A2);1)&A2;"-";"";2))

el resultado es el valor corregido



Pasemos a explicar como funciona esta fórmula, lo que hacemos de "adentro" hacia "afuera".

ESPACIOS(A2) todo lo que hace es asegurarnos que no hay espacios en blanco después del signo menos.

DERECHA(ESPACIOS(A2);1)&A2 extrae el signo menos a la derecha del número y "lo pone" por delante de él. El resultado de esta fórmula es que el signo menos aparece por delante y por detrás del número



SUSTITUIR(DERECHA(ESPACIOS(A2);1)&A2;"-";"";2) sustituye el segundo signo menos en -5119- por un blanco, con lo que obtenemos el resultado deseado.

Finalmente usamos la función VALOR para que el resultado de SUSTITUIR sea numérico.

Esta fórmula tiene un serio problema. Si la copiamos a lo largo del rango de números a corregir, los números positivos serán deformados



Esto se soluciona agregando una función SI a nuestra fórmula

=SI(DERECHA(ESPACIOS(A3);1)="-";VALOR(SUSTITUIR(DERECHA(ESPACIOS(A3);1)&A3;"-";"";2));A3)

Lo que hace la fórmula es evaluar si el valor en la celda a corregir tiene un signo "-" a la derecha; si lo tiene usa la fórmula; si no, copia la celda como tal.



Si no queremos usar columnas auxiliares, podemos usar esta macro

Sub sustituir_menos()
Dim Celda As Range

For Each Celda In Selection
If Trim(Right(Celda, 1)) = "-" Then
Celda.Value = Val("-" & Left(Celda, Len(Celda) - 1))
End If
Next

End Sub

Para que esta macro esté siempre disponible hay que copiarla a un módulo del Personal.xls usando el editor Vba.

Technorati Tags: MS Excel