Macro para determinar valores extremos
Necesito una macro que me indique los valores extremos de una variable (outliers). Para poder detectarlos se establecen dos límites:
Límite inferior = Cuartil 1 - 1,5 * (Cuartil 3 - Cuartil 1)
Límite superior = Cuartil 3 + 1,5 * (Cuartil 3 - Cuartil 1)
Los datos por debajo del límite inferior y por encima del límite superior serían outliers.
Por ejemplo, para la variable "a"
El límite inferior es 6,75 y el límite superior es 100,75 (los cuartiles 1 y 3 son 42 y 65,5 respectivamente).
El resultado que estoy buscando es algo así:
En este caso he aplicado la función "si":
Columna C: SI(B2<CUARTIL($B$2:$B$21;1)-1,5*(CUARTIL($B$2:$B$21;3)-CUARTIL($B$2:$B$21;1));"Si";"no")
Columna D: SI(B2>CUARTIL($B$2:$B$21;3)+1,5*(CUARTIL($B$2:$B$21;3)-CUARTIL($B$2:$B$21;1));"Si";"no")
El problema es que no tengo una sola variable (en el ejemplo la "a"), sino cientos de ellas ("b", "c", "d"..) y de cada variable tengo un número distinto de observaciones (en el caso de la "a" han sido 20, en el de la "b" 24 y así), por lo que creo que una macro podría ayudarme en esta tarea.
Lo he resuelto sin necesidad de programar, sólo con fórmulas. No sé si te servirá..
El resultado es este:
He formulado las columnas de la C hasta la I.
En C2 esto:
=A2
... y arrastro hasta abajo.
En D2 esto:
=FILA(A2)
... y arrastro hasta abajo.
En E2 esto:
{=MAX(SI(C2=$A$2:$A$27;FILA($A$2:$A$27);0))}
OJO! Es fórmula de matriz (la formulas y en lugar de pulsar ENTER debes pulsar CONTROL + SHIFT + ENTER), si no se hace así no funciona. Observa que la referencia solamente llega hasta la fila 27, en tu caso deberás coger todos los datos.
... y arrastro hasta abajo.
En F2 esto:
{=MIN(SI(C2=$A$2:$A$27;$D$2:$D$27))}
De nuevo fórmula de matriz y con la referencia a las filas que sean necesarias.
... y arrastro hasta abajo.
En G2 esto:
="B"&F2&":"&"B"&E2
Finalmente adapto tus fórmulas para recoger las nuevas referencias que he creado;
En H2 formulo así:
=SI(B2<CUARTIL(INDIRECTO($G$2);1)-1,5*(CUARTIL(INDIRECTO($G$2);3)-CUARTIL(INDIRECTO($G$2);1));"Si";"no")
y en I2 así:
=SI(B2>CUARTIL(INDIRECTO($G$2);3)+1,5*(CUARTIL(INDIRECTO($G$2);3)-CUARTIL(INDIRECTO($G$2);1));"Si";"no")
.. y arrastro hacia abajo.
Hola Víctor, perdona la tardanza, no he podido comprobarlo hasta ahora.
He seguido los pasos tal y como dices y no salía bien al principio. Lo he solucionado (creo) quitando el $ delante del 2 en las últimas dos fórmulas.
Estoy trabajando con más de 65000 filas y cada vez que aplico una de las fórmulas matriciales tarda mucho en procesar, incluso se me ha llegado a bloquear el Excel en varias ocasiones, así que lo que hago es, después de aplicar cada fórmula y que termine de procesar, una copia de valores y parece que así va mejor :)
Muchas gracias por tu respuesta, hasta ahora no sabía de la existencia de las funciones matriciales. Tendré que investigar porque parece muy interesante y útil.
Un saludo.
Ahá! Correcto, sobraban los $ de los indirectos!
Con tanto dato quizá lo sencillo sería realizar lo en access o en su defecto con una macro que seguro sería más eficiente. De todos modos investiga las fórmulas de matriz, abren un nuevo horizonte en la formulación en excel.
Si te decides a hacerlo en access te puedo ayudar si no sabes cómo.
