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