Más sobre formato personalizado de números en Excel

A partir de la nota anterior sobre formato personalizado de números en Excel (también pueden ver esta otra nota), un lector me plantea la siguiente consulta: “como puedo dar formato a una celda tal que si el número no es entero muestre siempre cinco decimales y si el número es entero, ninguno”.

Nuestro lector intento usar el siguiente formato personalizado "#,##0.#####”, el cual tiene el inconveniente de mostrar el separador de miles (coma o punto, dependiendo de las definiciones regionales) aún cuando el número es entero, como puede verse en este ejemplo

Si usamos Excel 2007 la solución del problema es bien sencilla. En lugar de formato personalizado de números usamos Formato Condicional creando dos reglas, una para cuando el número es entero y otra para cuando no lo es.

Suponiendo que el rango de números que queremos formar está en la columna C, para evaluar si el número es entero o no usamos la formula

=C1-TRUNCAR(C1)

Esta fórmula resta del número la parte entera; si el número es entero el resultado es 0. De esta manera podemos crear dos reglas para el formato condicional de las celdas

=(C1-truncar(C1))<>0 que VERDADERO si el número no es entero

=(C1-truncar(C1))=0 que da VERDADERO si el número es entero

Como pueden ver, Excel 2007 tiene una pestaña para dar formato a números en Formato condicional, lo que no existe en las versiones anteriores.

El resultado ahora es el buscado

Con las versiones de Excel 97-2003, no podemos usar formato condicional para formar números

Tampoco podemos usar un formato personalizado condicional, ya que éste no acepta fórmulas. El único camino que nos queda por delante es programar un evento.

Suponiendo que el rango a formar sea la columna C, en el módulo de Vba para la hoja ponemos este código

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngToFormat As Range, test

On Error Resume Next
Set rngToFormat = [C:C]
test = Target.Value - Int(Target)


If Union(Target, rngToFormat).Address = rngToFormat.Address Then
Select Case test
Case 0
Target.NumberFormat = "General"
Case Else
Target.NumberFormat = "#,##0.00000"
End Select
End If
On Error GoTo 0

End Sub


Esta macro se dispara cada vez que introducimos un valor en alguna de las celdas de la columna C; evalúa si el número es entero o no y aplica el formato correspondiente.

Technorati Tags: MS Excel

Indicadores de color con formato condicional (Excel 2003)

En la nota sobre formato condicional en Excel 2007 mostrábamos como crear este efecto

En cada celda de la columna C aparece una fecha cuyo sentido y color resalta el resultado de comparar los resultados con el plan.

Un compañero de trabajo me preguntaba hoy si se puede lograr el mismo efecto en Excel 2003. Mi respuesta es que si, se puede lograr algo parecido y es lo que voy a mostrar en esta nota.
Lo que podemos hacer con facilidad en Excel 2003 es poner un fondo de color de acuerdo al resultado, pero lo que queremos es mostrar las flechas como en Excel 2007.

Empezamos por crear una lista con las figuras de los indicadores. Para esto usamos el menú Insertar-Símbolos

Elegimos los triángulos del subconjunto Figuras Geométricas como sustitutos de las flechas (otra alternativa sería elegir el subconjunto Flechas). Elegimos el primer triángulo de la izquierda y lo ponemos en una celda



Después de introducir los restantes símbolos nuestro cuadro se verá así:



Como pueden ver, los símbolos aparecen en negro. Los colores serán determinados luego con Formato Condicional. Usamos la opción Fórmula y definimos tres condiciones



Es importante notar que al momento de definir las condiciones del formato condicional la celda activa debe ser D2 y la referencia en la fórmula debe ser relativa (sin los símbolos $).

Nuestro próximo paso es poner una fórmula en el rango E2:E6 que dé como resultado el triángulo correspondiente al valor de la celda respectiva en el rango D2:D6. En la celda E2 ponemos esta fórmula

=SI(D2>0,$A$10,SI(D2<0,$a$11,$a$12))>que copiamos al resto del rango. El color de las flechas será determinado por el formato condicional

Para hacer el efecto un poco más parecido al que logramos con Excel2007 podemos poner la columna de las flechas a la izquierda de la columna Control. También podemos quitar las líneas de división, combinar las celdas D1 y C1 para crear la ilusión de que se trata de una única columna y así obtener esta resultado



Otra posibilidad es usar las flechas de Windings, como éstas



O estas otras de Windings3

Technorati Tags: MS Excel

Totales acumulados en tablas dinámicas de Excel.

Crear totales acumulados en tablas dinámicas de Excel es fácil, como mostraremos en esta nota.
Supongamos estos datos de ventas de dos sucursales



A partir de estos datos construimos una tabla dinámica normal



Para lograr que la tabla muestre los totales acumulados, abrimos el menú de configuración de campo, apretamos el botón Opciones, en Mostrar datos como elegimos Total en y en Campo base elegimos Mes



La tabla dinámica muestra ahora el acumulado por mes



Para mostrar el acumulado por Sucursal tenemos que agregar este campo a la tabla. Volvamos al total normal y agreguemos el campo Sucursal en el área de columnas



Ahora al definir Mostrar datos como Total en y Campo base Mes, veremos



Si cambiamos la base a Sucursal, veremos



Ahora los acumulados son por fila, a lo largo del mes.

También podemos arrastrar el campo Sucursal al área de filas, a la izquierda de Mes y poniendo la base a Mes veremos el acumulado por mes de cada sucursal

Technorati Tags: MS Excel