Excluyendo ceros en cálculos con Excel

lunes, marzo 05, 2007

De acuerdo a Wikipedia, el cero pertenece al conjunto de los números enteros. Sea lo que fuere, en ciertos cálculos, el cero puede ser bastante molesto, en especial si lo hacemos con Excel.

Por ejemplo, supongamos esta lista de números





Si queremos contar cuántos elementos hay en la lista, sin incluir los ceros, podemos hacerlo fácilmente usando la función CONTAR.SI. Definamos un nombre que contenga el rango de números

lista_num: =Hoja1!$A$1:$A$20

la siguiente fórmula nos da como resultado 20: =CONTAR(lista_num)

Si queremos contar la cantidad de números en la lista, sin considerar los ceros, usaremos la fórmula

=CONTAR.SI(lista_num;"<>0")

Esta fórmula da como resultado 17.

Los problemas empiezan cuando queremos encontrar el mínimo de la lista, lo que hacemos usando la función MIN. Esta fórmula da como resultado 0

=MIN(lista_num)

Si queremos encontrar el mínimo de la lista, sin incluir los ceros, usamos esta fórmula

=SI(MIN(lista_num)<>0;MIN(lista_num);K.ESIMO.MENOR(lista_num;CONTAR.SI(lista_num;"=0")+1))

Esta fórmula realiza el cálculo en dos etapas. Primero comprueba si el mínimo es cero. Si no hay ningún cero en la lista, usa la fórmula MIN(lista_num).
Si el mínimo es cero, pasa a usar la segunda parte de la función SI, la función K.ESIMO.MENOR combinado con CONTAR.SI.

La sintaxis de la función K.ESIMO.MENOR es: K.ESIMO.MENOR(matriz;k)
Donde matriz es el conjunto de datos y k determina a partir de qué valor se calculará el mínimo. En nuestra fórmula k es determinado por la fórmula CONTAR.SI(lista_num;"=0")+1 que da como resultado, en nuestro ejemplo, 4.

Un problema similar, simétrico diría, tenemos si nuestra lista está formada sólo por números negativos.
En ese caso, calcular el máximo de la lista (supongamos la misma lista, pero con valores negativos), daría 0. Para calcular el máximo, sin tomar en cuenta los ceros usamos una fórmula similar (al rango le hemos dado el nombre lista_neg)

=SI(MAX(lista_neg)<>0;MAX(lista_neg);K.ESIMO.MAYOR(lista_neg;CONTAR.SI(lista_neg;"=0")+1))

Otro problema con ceros es calcular promedios. Excel ignora las celdas vacías al calcular promedios con la función PROMEDIO. Pero los ceros son tenidos en cuenta. Por ejemplo, la fórmula =PROMEDIO(lista_num) da como resultado 39,25. Pero si calculamos el promedio sin incluir los ceros, obtenemos 46,176.

Para calcular promedios sin tomar en cuenta los ceros tenemos varias posibilidades:

=SUMA(lista_num)/CONTAR.SI(lista_num;"<>0")

El problema con esta fórmula es que toma en cuenta las celdas vacías. Para evitar que esto suceda podemos usar la fórmula

=SUMA(lista_num)/(CONTAR(lista_num)-CONTAR.SI(lista_num;0))

Otra alternativa es usar la función SUMAPRODUCTO de esta manera

=SUMA(lista_num)/SUMAPRODUCTO(--(lista_num<>0))

EL doble signo "--" tiene como función forzar el cálculo de la expresión lista_num<> y en su lugar se puede, sencillamente, multiplicar la expresión por 1

=SUMA(lista_num)/SUMAPRODUCTO((lista_num<>0)*1)


Technorati Tags:

24 comments:

Anónimo,  08 marzo, 2007 18:07  

HOLA JORGE!! NUEVAMENTE TE EESCRIBO, ESTA VEZ PARA CONSULTARTE SI EN EXCEL EXISTE ALGUNA FORMA DE SABER EL GENERO DE UN NOMBRE, POR EJEMPLO GOYTIA GOMEZ MARCELO - MASCULINO, COMO SIEMPRE CONFIO EN QUE PUEDAS AYUDARME. MIL GRACIAS DE ANTEMANO

MARCELO

Jorge L. Dunkelman 09 marzo, 2007 18:10  

Hola Marcelo,
sólo si tienes una lista en otra hoja de Excel donde en una columna aparecen los nombres propios, que son los que determinan el sexo, y en la columna contigua el sexo. Por ejemplo:
José----Masculino
Juan----Masculino
Ana-----Femenino

Luego tendrías que usar una fórmula que resuelva el sexo de acuerdo al nombre.

Jorge 01 julio, 2007 03:39  

Jorge, he tratado de aplicar tu formula, pero me señala que son muchos datos, pues la estoy tratando de aplicar en una planilla de calificaciones de mis alumnos, cuyos promedios estan en celdas discontinuas, como lo podria solucionar.
Gracias de antemano

Jorge L. Dunkelman 01 julio, 2007 17:23  

Hola
la solución más obvia es reordenar la hoja de manera que las celdas queden en un rango contiguo.
Ora posibilidad es que me mandes el archivo (jorgedun@gmail.com) para ver que otra solucuón es posible.

Anónimo,  08 diciembre, 2009 20:33  

BUEN DIA TENGO UN ARCHICO CON DATOS DISCONTINUOS LOS CUALES QUEIRO ORDENAR, SE PUEDE?

EJEMPLO:

NOMBRE
FELIPE
JUAN
JOSE

NOMBRE
JULIAN
FRANCISCO

NOMBRE
ALDO
LUIS

ASI APARECEN SOLO QUE CON MUCHOS DATOS Y QUISIERA SABER SI SE PUEDEN ORDENAR POR ALFABETICO SOLO LOS NOMBRES SIN AFECTAR LAS CELDAS QUE DICEN NOMBRE, PORQUE DE ESTA MATRIZ SE JALAN DATOS A OTRAS HOJAS?

Jorge L. Dunkelman 08 diciembre, 2009 21:39  

Anónimo,
por favor lee la nota sobre cómo solicitar ayuda en el blog (en la pestaña Ayuda en la parte superior del blog). Tu consulta no está relacionada con la nota de la entrada (recuerda, esto es un blog, no un foro).

C_Felipe 25 febrero, 2010 19:27  

Estimado, tengo la sgte celda,
=PROMEDIO(C7;C24;C41;C58;C75;C92;C109;C126;C143;C160;C177;C194;C211;C228;C245;C262;C279;C296;C313;C330;C347;C364;C381;C398;C415;C432;C449;C466)

donde claramente me tira un error por datos vacíos o cero, así que intenté realizar las formulas que colocaste, pero sin un buen resultado (creo que es por la cantidad de caracteres). Así que te quería pedir un consejo de como puedo arreglarlo.
saludos

Jorge L. Dunkelman 26 febrero, 2010 09:46  

Tu problema es el rango discontinuo. CONTAR.SI no puede resolver este tipo de rangos. Una solución, como puse en un comentario anterior, es cambiar el diseño de la hoja de manera que el rango de celdas a eveluar sea continuo.
Otra solución es usar la función ARRAY.JOIN del complemento que desarrolló Lorent Longre, como muestro en esta nota

Anónimo,  08 septiembre, 2010 13:19  

Hola Jorge,

Estos días estaba mirando este tema en relación al uso de MIN y he visto que tenías esta entrada. También encontré esta otra solución que puede ser más corta y que tú has aplicado en alguna ocasión pero creo que para casos distintos a MIN:

{=MIN(SI(lista_num<>0;lista_num;""))}

o incluso quitando el tercer argumento de la función SI también funcionaría:

{=MIN(SI(lista_num<>0;lista_num))}

Un saludo,
Sergio

Anónimo,  28 abril, 2011 19:08  

GENIAL!! ME SACARON DE UN APRIETO EN EL TRABAJO, JEJEJ, GRACIAS

Flavio 25 noviembre, 2011 19:58  

=SI($B9="","",PROMEDIO.SI(TAB!H7:AF7,"<>0"))

Anónimo,  22 febrero, 2012 11:41  

Cómo podría hacer para que me contara el segundo mínimo, en lugar del primero, sin contar los ceros????

Anónimo,  22 febrero, 2012 11:58  

Encontrada la solución, gracias por un blog geniaL.

Un saludo José.

Anónimo,  11 abril, 2013 20:20  

Hola Jorge,
Mi problema es realizar un Promedio en :
Rango discontinuo
Sin que tenga en cuenta ceros

Esto mismo me pasa con la función Min.

Que me propone, gracias
Miguel Angel

Jorge Dunkelman 13 abril, 2013 23:06  

Supongamos, como me pones en el mail, esta situación: A2=10; C2=30; G2=0; I2=5. Suponiendo que las restantes celdas del rango están en blanco, puedes usar esta fórmula

=SUMA(A2:I2)/(CONTAR.SI(A2:I2,"<>0")-CONTAR.BLANCO(A2:I2))

También se puede usar esta otra

=SUMA(A2:I2)/SUMAPRODUCTO(--((A2:I2)>0))

Para encontrar el mínimi distinto de 0, siguiendo con el mismo ejemplo, podrías usar esta fórmula matricial (se introduce pulsando Ctrl+Mayúsculas+Enter)

=MIN(SI(A2:I2=0,A2:I2="#",A2:I2))

Anónimo,  08 mayo, 2013 18:04  

Buen dia,

Para la formula "MATRICIAL" es posible encontrar en min en un rango partido?

"A1:A20;A22;A27:A30"

Cuando exponen "lista_num" hacen referencia a un rango con inicio y fin sin separar...

{=MIN(SI(lista_num<>0;lista_num;""))}

AYUDA!!! No encuentro la formula... Me toca validar como mínimo 3 particiones antes de utilizar la formula expuesta...

Jorge Dunkelman 10 mayo, 2013 18:41  

La fórmula matricial trabajo solo con rangos continuos.

Luis Fernando Jaimes Rivera 18 agosto, 2015 07:24  

Perdon Corrijo el anterior comentario: Buenas Buenas, excelente labor la que haces, de ante mano te agradezco. Tengo un caso algo particular... tengo una columna (A) con 10 valores (negativos y también ceros), necesito saber la jerarquía (de menor a mayor) de estos números en la columna (B), pero sin que en el conteo se me repita por ejemplo 8 8 8 (debido digamos a los 3 ceros que tengo en la lista), Seria PERFECTO si me dejara la celda en blanco en la jerarquia para el caso de los ceros. He intentado colocando en (B) JERARQUIA.EQV(A1;$A$1:$A$1;1) y nada... Gracias compañero por la ayuda

Jorge Dunkelman 18 agosto, 2015 11:42  

Luis, ¿la idea es que si un valor se repite sólo la primer instancia reciba el número de orden?

Anónimo,  27 mayo, 2016 22:11  

Hola Luis,

Al igual que en este caso, hay alguna manera de calcular la desviación estándar sin los ceros? Gracias.

Anónimo,  27 mayo, 2016 22:12  

Corrijo: Jorge (no Luis del comentario anterior)

Jorge Dunkelman 30 mayo, 2016 07:17  

Si, usando una fórmula matricial como =DESVEST(SI(rango<>0,rango))
Las fórmulas matriciales se ingresan apretando simultáneamente Ctrl-Mayúsculas-Enter.

Anónimo,  18 abril, 2017 16:58  

hola buenos dias como hallar la moda sin q excel tome "0" gracias

Jorge Dunkelman 18 abril, 2017 19:09  

Con esta formula matricial (ingresarla apretando Ctrl-Mayusculas-Enter)

=MODA.UNO(SI(matriz=0,"#",matriz))

donde "matriz" es el rango de los datos.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP