lunes, marzo 05, 2007

Excluyendo ceros en cálculos con Excel

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 comentarios:

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

    ResponderBorrar
  2. 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.

    ResponderBorrar
  3. 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

    ResponderBorrar
  4. 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.

    ResponderBorrar
  5. 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?

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

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

    ResponderBorrar
  8. 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

    ResponderBorrar
  9. 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

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

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

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

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

    Un saludo José.

    ResponderBorrar
  14. 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

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

    ResponderBorrar
  16. 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...

    ResponderBorrar
  17. La fórmula matricial trabajo solo con rangos continuos.

    ResponderBorrar
  18. 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

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

    ResponderBorrar
  20. Hola Luis,

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

    ResponderBorrar
  21. Corrijo: Jorge (no Luis del comentario anterior)

    ResponderBorrar
  22. 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.

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

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

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

    donde "matriz" es el rango de los datos.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.