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).

Números grandes en Excel – segunda nota

Excel guarda los números con un límite de precisión de 15 cifras. Esto se hace evidente cuando introducimos un número de más de 15 cifras. Veamos qué pasa cuando introducimos el número 123456789123456789 (18 cifras) en una celda a la cual le hemos de antemano el formato de número para evita que Excel ponga el formato científico automáticamente




Al apretar Enter todas las cifras a la derecha de la posición 15 son convertidas en ceros.
El mismo problema existe cuando la parte fraccional de un número supera las 15 cifras.


Por lo general esta limitación no es significativa, en especial si usamos Excel para cálculos financieros. Si necesitamos operar con mayor precisión de la que permite Excel dos posibilidades son:


# - descargar el complemento xlPrecision


# - utilizar los conceptos esbozados por Tushar Metha en esta nota



La ventaja del complemento xlPrecision es que también soluciona el problema precisión y redondeo que existe en Excel.



Es este el problema de la conversión binaria (no sólo de Excel) que se hace evidente con este ejemplo: restamos 4.0 de 4.1

Aparentemente el resulta es el correcto: 0.1. Veamos qué pasa cuando formamos la celda para que muestre más de 15 decimales

El resultado no es exactamente 0.1.

Technorati Tags: MS Excel