Fórmula genérica
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Resumen
Para ordenar y extraer dinámicamente valores únicos de una lista de datos, puede usar una fórmula de matriz para establecer un rango en una columna auxiliar y luego usar una fórmula INDICE y COINCIDIR especialmente construida para extraer valores únicos. En el ejemplo que se muestra, la fórmula para establecer el rango en C5: C13 es:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
donde "datos" es el rango con nombre B5: B13.
Nota: esta es una fórmula de matriz de varias celdas, ingresada con control + shift + enter.
Explicación
Nota: la idea central de esta fórmula está adaptada de un ejemplo en el excelente libro Control + Shift + Enter de Mike Girvin.
El ejemplo que se muestra utiliza varias fórmulas, que se describen a continuación. En un nivel alto, la función MMULT se usa para calcular un rango numérico en una columna auxiliar (columna C), y este rango luego es usado por una fórmula INDICE y MATCH en la columna G para extraer valores únicos.
Clasificación de valores de datos
La función MMULT realiza la multiplicación de matrices y se utiliza para asignar un rango numérico a cada valor. La primera matriz se crea con la siguiente expresión:
--(data>TRANSPOSE(data))
Aquí, usamos la función TRANSPONER para crear una matriz horizontal de datos , y todos los valores se comparan entre sí. En esencia, cada valor se compara con cualquier otro valor para responder a la pregunta "¿es este valor mayor que cualquier otro valor"? Esto da como resultado una matriz bidimensional, 9 columnas x 9 filas, rellena con valores VERDADERO y FALSO. El doble negativo (-) se utiliza para convertir los valores VERDADERO FALSO en 1 y ceros. Puede visualizar la matriz resultante de esta manera:
La matriz de 1s y ceros anterior se convierte en array1 dentro de la función MMULT. Array2 se crea con esta expresión:
ROW(data)^0
Aquí, cada número de fila en "datos" se eleva a la potencia de cero para crear una matriz unidimensional, 1 columna x 9 filas, llena con el número 1. MMULT luego devuelve el producto de la matriz de las dos matrices, que se convierte en el valores que se ven en la columna de clasificación.
Obtenemos las 9 clasificaciones al mismo tiempo en una matriz, por lo que debemos colocar los resultados en diferentes celdas a la vez. De lo contrario, cada celda solo mostrará el primer valor de clasificación en la matriz que se devuelve.
Nota: esta es una fórmula de matriz de varias celdas, ingresada con control + shift + enter, en el rango C5: C13.
Manejo de celdas en blanco
Las celdas vacías se manejan con esta parte de la fórmula de clasificación:
=IF(data="",ROWS(data)
Aquí, antes de ejecutar MMULT, verificamos si la celda actual en "datos" está en blanco. Si es así, asignamos un valor de rango que es igual al recuento de filas en los datos. Esto se hace para forzar las celdas en blanco al final de la lista, donde se pueden excluir fácilmente más adelante a medida que se extraen valores únicos (se explica a continuación).
Contando valores únicos
Para contar valores únicos en los datos, la fórmula en E5 es:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Dado que la fórmula de clasificación anterior asigna un rango numérico a cada valor, podemos usar la función FRECUENCIA con SUMA para contar valores únicos. Esta fórmula se explica en detalle aquí. Luego restamos 1 del resultado si hay celdas vacías en los datos:
-(blank>0)
donde "en blanco" es el rango con nombre E8 y contiene esta fórmula:
=COUNTBLANK(data)
Básicamente, reducimos el recuento único en uno si hay celdas en blanco en los datos, ya que no las incluimos en los resultados. El recuento único en la celda E5 se denomina "único" (para recuento único) y lo utiliza la fórmula INDICE y COINCIDIR para filtrar las celdas en blanco (que se describen a continuación).
Extrayendo valores únicos
Para extraer valores únicos, G5 contiene la siguiente fórmula, copiada:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Antes de ejecutar la fórmula INDICE y COINCIDIR, primero verificamos si el recuento de filas actual en el área de extracción es mayor que el recuento único del rango denominado "único" (E5):
=IF(ROWS($G$5:G5)>unique,"",
Si es así, terminamos de extraer valores únicos y devolvemos una cadena vacía (""). Si no, ejecutamos la fórmula de extracción:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Tenga en cuenta que aquí hay dos funciones COINCIDIR, una dentro de la otra. El MATCH interno usa un rango de expansión para una matriz y el rango con nombre "datos" para el valor de búsqueda:
MATCH(data,$G$4:G4,0)
Observe que el rango de expansión comienza en la "fila superior", fila 4 en el ejemplo. El resultado del MATCH interno es una matriz que, para cada valor en los datos, contiene una posición numérica (el valor ya se ha extraído) o el error # N / A (el valor aún no se ha extraído). Luego usamos IF e ISNA para filtrar estos resultados y devolvemos el valor de clasificación para todos los valores en "datos" aún no extraídos:
IF(ISNA(results),rank))
Esta operación da como resultado una matriz, que se alimenta a la función MIN para obtener el "valor de rango mínimo" para los valores de datos aún no extraídos. La función MIN devuelve este valor a la PARTIDA externa como un valor de búsqueda y el rango denominado "rango" como la matriz:
MATCH(min_not_extracted,rank)),rank,0)
Finalmente, COINCIDIR devuelve la posición del valor de rango más bajo a INDICE como un número de fila, e INDICE devuelve el valor de datos en la fila actual del rango de extracción.
