martes, 11 de octubre de 2016

Trabajar con rangos discontinuos en Excel

Aprenderemos hoy un buen truco para poder trabajar sobre rangos discontinuos.., en aquellos casos que los argumentos de nuestras funciones no lo admiten.
La idea surge a partir de la pregunta planteada:
[...]a ver si me explico, cada dato separado por la coma es una celda,

a  b  c   d   e  f  g   h  i  j k  l m n o  p  q
(12,14,15,)16,(17,0,14,)57,(0,36,78,0,87,9,56,0),7

y requiero sacar el promedio de solo los que están dentro del
paréntesis, es decir de los que está dentro que no tome los ceros y
los rangos dentro.[...]


Vemos los datos desplegados en la imagen siguiente... donde necesitamos calcular el promedio de las celdas en amarillo, pero únicamente de aquellos valores distintos de cero!!!.

Trabajar con rangos discontinuos en Excel



Por supuesto, la solución más simple sería sustituir los ceros por vacío, par luego aplicar la función:
=PROMEDIO(A2:C2;E2:G2;I2:P2)
que nos devolverá la media correcta:= 33,8


Vamos a explicar un método sin necesidad de modificar los datos originales.
Recordemos que la media aritmética se calcula como el cociente entre la suma de los valores y el número de valores.

Para ello, por separado, calculamos la suma de los tres rangos amarillos, en la celda S2:
=SUMA(A2:C2;E2:G2;I2:P2)

En la celda T2 calculamos el número total de valores, incluido los valores cero:
=CONTARA(A2:C2;E2:G2;I2:P2)

Por último, en la celda U2 (aquí está el truco), calculamos el número de ceros en esos tres rangos...
Empleamos la función matricial siguiente:
=SUMA(CONTAR.SI(INDIRECTO({"a2:C2";"e2:g2";"i2:p2"});"0"))

De otro modo no es posible contar el número de ceros (o cualquier otra condición, sobre un rango discontinuo.

El uso dentro de la función INDIRECTO de una matriz:
{"a2:C2";"e2:g2";"i2:p2"}
permite lograr nuestro objetivo, y que la función CONTAR.SI realice el cálculo correcto, matricialmente en cada rango.
Podríamos ejecutar la fórmula separadamente:
CONTAR.SI(INDIRECTO({"a2:C2";"e2:g2";"i2:p2"});"0")
y veríamos nos devuelve el conteo de ceros en los tres rangos indicados:=
{0;1;3}
conteo que luego sumaremos para conocer el número de ceros.


Ya solo nos queda sumar, restar y dividir. En la celda U3 añadimos:
=+S2/(T2-U2)

Trabajar con rangos discontinuos en Excel



En definitiva, la suma de todos los valores, lo dividimos por el resultado de restar al número total de valores aquellos que son cero.

Una alternativa matricial más, empleando la función FRECUENCIA:
=SUMA(A2:C2;E2:G2;I2:P2)/INDICE(FRECUENCIA((A2:C2;E2:G2;I2:P2);0);2)

La clave está en que FRECUENCIA con esa configuración, devolvería un rango de dos valores:
1-primer valor: número de ceros (en el ejemplo, 4)
2-segundo valor: número de NO ceros (en el ejemplo 10)
Luego con la función INDICE recuperamos el dato que nos interesa, es decir, el número de valores NO ceros...

El resto del cálculo responde a la misma premisa que la propuesta anterior.

4 comentarios:

  1. Muy buen artículo, gracias por la información.

    Saludos desde la República Dominicana.

    ResponderEliminar
  2. También podría solucionarse por medio de: =PROMEDIO.SI(Rango deseado,">"&0)

    ResponderEliminar
    Respuestas
    1. Gracias David,
      pero en este caso dicha fórmula no funcionaría.. ya que no se admite un rango discontinuo como 'rango deseado'... y tampoco podríamos seleccionar el rango A2:Q2 por que se incluirían valores no deseados en el cálculo
      ;-)

      Un saludo!

      Eliminar