Determinar colores en el formato personalizado de números

Uno de los blogs que sigo, Bacon Bits, trae una nota sobre cómo determinar el color de un formato personalizado de números.

En notas anteriores he explicado que podemos determinar el color de un formato de número condicionalmente.

Por ejemplo, si queremos que la fuente de todo número superior a 1000 sea verde, números menores de 500 aparezcan en rojo y los restantes en negro. podemos usar este formato personalizado

[Verde][>1000]General;[Rojo][<500]general;[negro]general

Convengamos que nuestra idea del verde es diferente de la que parece tener Microsoft.

Una solución es renunciar al verde y usar el azul



Otra solución es usar el número de índice del color en la paleta en lugar del nombre explícito del color. De esta manera tenemos total control del color de la fuente.

El único problema a resolver es saber cuál es el número de índice del color que queremos usar. Para averiguar el número de índice del color podemos usar la técnica que mostramos con las macrofunciones de XLM o una macro como ésta:

Sub nr_color()
    Dim cell As Range, rngNrColor As Range
    
    Set rngNrColor = Application.InputBox(prompt:="select range", Type:=8)
    
    For Each cell In rngNrColor
        cell.Offset(0, 1).Interior.ColorIndex = cell.Value
    Next
    
End Sub

En una hoja creamos un rango con una serie de números del 1 al 56 (por ejemplo A1:A56), corremos la macro y obtenemos esta lista (para facilitar la visualización he divido el rango en tres columnas)

Para usar el verde cuyo número de índice (en mi paleta) es el 10, modificamos el formato personalizado de esta manera

[Color10][>1000]General;[Rojo][<500]general;[negro]general>

Es decir, usamos “Color” y el número de índice.

Technorati Tags: MS Excel

Contar condicional de números con celdas en blanco

En ciertas situaciones Excel puede llevarnos a cometer errores difíciles de detectar. Este ejemplo es la “vida real”, es decir, de un compañero de trabajo.

Supongamos que tenemos un rango de nueve celdas, cinco de las cuales contienen números y las cuatro restantes en blanco

La fórmula =SUMA(B2:B10) da como resultado 150. Es decir, las celdas en blanco son consideradas ceros o ignoradas por la función SUMA.

Lo mismo sucede si usamos la función CONTAR para contar cuántos números hay en el rango. =CONTAR(B2:B10) da como resultado 5. Lo mismo con función CONTARA.

Todo esto nos puede llevar a pensar que si queremos contar cuántos números distintos de cero hay el rango, todo lo que tenemos que hacer es usar la función CONTAR.SI de esta manera

=CONTAR.SI(B2:B9,"<>0")

Esta fórmula da como resultado 9. Este resultado es un tanto extraño. Veamos qué pasa si creamos una columna auxiliar con la fórmula =B2<>0 (que copiamos a los largo del rango)

Vemos que obtenemos cinco VERDADERO y cuatro FALSO. Es decir, podríamos esperar que el resultado de CONTAR.SI en nuestro caso sea 5, pero como vemos es 9.

En nuestro caso, es muy fácil percibir que el resultado no es el esperado, pero si analizamos un rango grande, digamos mil celdas, es más que probable que aceptemos el resultado como correcto.

En caso de tener que contar condicionalmente números en un rango que contiene o puede contener celdas en blanco, podemos usar alguna de estas soluciones:

# usar la función CONTAR.BLANCO para “corregir el resultado de CONTAR.SI

=CONTAR.SI(B2:B10,"<>0")-CONTAR.BLANCO(B2:B10)

# usar la fórmula matricial

={SUMA(--(B2:B10<>0))}

(recordemos una vez más que las fórmulas matriciales deben ser introducidas apretando simultáneamente Ctrl+Mayúsculas+Enter)

# usar la función SUMAPRODUCTO:

=SUMAPRODUCTO(--(B2:B10<>0))

Technorati Tags: MS Excel

Formato indeseado de número en Excel

Cuando introducimos un valor en una celda, Excel intenta determinar el tipo de valor y darle el formato adecuado. Por ejemplo, si escribimos 1/2 en una celda Excel lo transforma al primero de febrero del año corriente



Lo mismo sucede si el valor contiene un guión.

Si el número (de hecho, un conjunto de cifras) contiene dos puntos o las letras A o P antecedidas por un espacio, el valor es transformado en hora



Si ingresamos un número que contiene ceros iniciales, Excel elimina los ceros y convierte el valor en número. Además y como ya hemos visto, si el número excede las 12 cifras Excel aplica el formato científico y si se exceden las 15, las cifras supernumerarias son transformadas en ceros. Esto último lo he vivido en carne propia hace varios años atrás cuando tuvimos que ingresar números de tarjetas de crédito (que tienen 16 cifras).

El remedio a este tipo de situaciones es darle el formato adecuado al rango de celdas que vamos a usar, evitando así que Excel decida por su cuenta. Existen también algunos trucos de teclado, en caso que queramos ahorrarnos el proceso de dar formato a las celdas.

# Si queremos ingresar números con ceros iniciales o códigos de tarjetas de crédito debemos darle al rango el formato Texto. Otra alternativa es poner un apóstrofe al comienzo (por ejemplo, '00001509).

# Si queremos ingresar fracciones debemos dar el formato Fracción o escribir la parte entera de la fracción, dejar un espacio y escribir el resto de la expresión. Por ejemplo, para escribir 2 1/3, escribimos 2, apretamos la barra de espacios e inmediatamente 2/3



Nótese que en la barra de las fórmulas el valor aparece como 2,3333333333

Si queremos ingresar una fracción de tipo x/y, por ejemplo 3/4 escribimos en la celda 0 3/4




# Si queremos ingresar una expresión que indica relación entre dos valores, por ejemplo 5:6, debemos formar la celda como Texto o anteceder la expresión con un espacio (o apóstrofe).