¿Encontrar el valor más grande, el enésimo más grande y otros anteriores? (ignorando duplicados)

Question

Entonces tengo una columna que dice A y que se ve así:

I
15
0
3
15
M
8
0
8
21
Q
0
5
0
0

Puedo encontrar el valor más grande con=MÁX(A1:A100) Puedo encontrar el valor más pequeño con=PEQUEÑO(A1:A100,1)

¿Cómo encuentro el anterior al más grande y el anterior a ese? Puedo usar Grande para llamar al enésimo valor, pero entonces, ¿cómo puedo saber cuántos N valores cuenta la función para poder saber qué posiciones usa se refieren a los últimos 3 (el más grande y los 2 anteriores)?

Ignorando duplicados: en el ejemplo, el más grande es 21, el anterior es 15 y el anterior es 8. Si hago =LARGE(A1:A100,3), devolverá 15 ya que hay dos de ellos en la columna. Y busco el 8 como tercer valor más grande.

A continuación encontrará personas útiles de Internet que proporcionaron una tabla dinámica, una solución de fórmula de matriz, y finalmente pude hacer una fórmula uno simple.

Puede proporcionar vba para el trabajo si insiste en que tenemos todo; de lo contrario, ya hay suficiente.

Answer 1

Utilice una tabla dinámica para obtener rápidamente una versión sin duplicados de su lista (simplemente agregue su columna aEtiquetas de fila), luego use LARGEy SMALLfunciones en la lista de duplicados:

=LARGE(D4:D9,3)

Si lo desea, puede ordenar la tabla dinámica eliminando laGran totaly cambiandoEncabezados de campodesactivado para que solo muestre su lista de duplicados.

Answer 2

Con datos en columnaAcomo:

EnB1ingresar:

=MAX(A:A)

EnB2ingrese la fórmula matricial:

=MAX(IF(A$1:A$10<B1,A$1:A$10))

Luego copiaB2hacia abajo tanto como sea necesario.

Fórmulas de matrizdebe ingresarse con Ctrl+ Shift+ Enteren lugar de solo la Enterclave.

Answer 3

Con toda la excelente ayuda que he recibido, me hizo pensar si puedo hacer lo que hace la tabla dinámica y se me ocurrió esto: (incluso por mi cuenta LOL)

=IF(COUNTIF($A$1:A1,A1)<=1,A1,"")

Haciendo una columna paralela a los datos de interés se soluciona. (por ejemplo, pegar en C1 y copiar)

Proporciona la lista inicial de duplicados en la que puede basar grandes y pequeños. Como solución simple, no debería interferir con ningún elemento volátil que pueda tener en sus documentos. Y yo, por mi parte, puedo vivir sin vba. Cualquier giro inesperado debe poder contenerse con IFERROR, en caso de que haya tipos de datos mixtos.

Answer 4

@helena4 Prueba esta fórmula matricial

=LARGE(IF(A1:A15 < LARGE(A1:A15,1),A1:A15),3)

Entonces presioneCtrl+Shift+Enter

ignorará los duplicados.