Formula generica
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Sommario
Per ordinare ed estrarre dinamicamente valori univoci da un elenco di dati, è possibile utilizzare una formula di matrice per stabilire un rango in una colonna helper, quindi utilizzare una formula INDICE e CONFRONTA appositamente costruita per estrarre valori univoci. Nell'esempio mostrato, la formula per stabilire il rango in C5: C13 è:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
dove "dati" è l'intervallo denominato B5: B13.
Nota: questa è una formula di matrice multicella, inserita con CTRL + MAIUSC + INVIO.
Spiegazione
Nota: l'idea centrale di questa formula è adattata da un esempio nell'eccellente libro di Mike Girvin Control + Shift + Enter.
L'esempio mostrato utilizza diverse formule, descritte di seguito. Ad un livello elevato, la funzione MMULT viene utilizzata per calcolare un rango numerico in una colonna helper (colonna C), e questo rango viene quindi utilizzato da una formula INDICE e CONFRONTA nella colonna G per estrarre valori univoci.
Classificazione dei valori dei dati
La funzione MMULT esegue la moltiplicazione di matrici e viene utilizzata per assegnare un rango numerico a ciascun valore. Il primo array viene creato con la seguente espressione:
--(data>TRANSPOSE(data))
Qui, usiamo la funzione TRANSPOSE per creare una matrice orizzontale di dati e tutti i valori vengono confrontati tra loro. In sostanza, ogni valore viene confrontato con ogni altro valore per rispondere alla domanda "questo valore è maggiore di ogni altro valore". Ciò si traduce in un array bidimensionale, 9 colonne x 9 righe, riempito con valori VERO e FALSO. Il doppio negativo (-) viene utilizzato per forzare i valori VERO FALSO a 1 e zero. Puoi visualizzare l'array risultante in questo modo:
La matrice di 1 e zero sopra diventa array1 all'interno della funzione MMULT. Array2 viene creato con questa espressione:
ROW(data)^0
Qui, ogni numero di riga in "dati" viene elevato alla potenza di zero per creare un array unidimensionale, 1 colonna x 9 righe, riempito con il numero 1. MMULT quindi restituisce il prodotto di matrice dei due array, che diventano il valori visualizzati nella colonna del rango.
Otteniamo indietro tutte e 9 le classifiche contemporaneamente in un array, quindi dobbiamo inserire i risultati in celle diverse contemporaneamente. In caso contrario, ogni cella mostrerà solo il primo valore di classificazione nella matrice restituita.
Nota: questa è una formula di matrice multicella, inserita con CTRL + MAIUSC + INVIO, nell'intervallo C5: C13.
Gestire le celle vuote
Le celle vuote vengono gestite con questa parte della formula di classificazione:
=IF(data="",ROWS(data)
Qui, prima di eseguire MMULT, controlliamo se la cella corrente in "dati" è vuota. In tal caso, assegniamo un valore di rango che è uguale al conteggio delle righe nei dati. Questo viene fatto per forzare le celle vuote in fondo all'elenco, dove possono essere facilmente escluse in seguito man mano che vengono estratti valori univoci (spiegato di seguito).
Conteggio di valori unici
Per contare valori univoci nei dati, la formula in E5 è:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Poiché la formula di classificazione sopra assegna un rango numerico a ciascun valore, possiamo utilizzare la funzione FREQUENZA con SOMMA per contare valori univoci. Questa formula è spiegata in dettaglio qui. Quindi sottraiamo 1 dal risultato se ci sono celle vuote nei dati:
-(blank>0)
dove "vuoto" è l'intervallo denominato E8 e contiene questa formula:
=COUNTBLANK(data)
In sostanza, riduciamo il conteggio univoco di uno se ci sono celle vuote nei dati, poiché non le includiamo nei risultati. Il conteggio univoco nella cella E5 è denominato "unico" (per conteggio univoco) e viene utilizzato dalla formula INDICE e CONFRONTA per filtrare le celle vuote (descritto di seguito).
Estrazione di valori univoci
Per estrarre valori univoci, G5 contiene la seguente formula, copiata in basso:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Prima di eseguire la formula INDICE e CONFRONTA, controlliamo innanzitutto se il conteggio delle righe correnti nell'area di estrazione è maggiore del conteggio univoco dell'intervallo denominato "unico" (E5):
=IF(ROWS($G$5:G5)>unique,"",
In tal caso, abbiamo finito di estrarre valori univoci e restituiamo una stringa vuota (""). In caso contrario, eseguiamo la formula di estrazione:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Nota che qui ci sono due funzioni MATCH, una dentro l'altra. Il MATCH interno utilizza un intervallo in espansione per un array e l'intervallo denominato "dati" per il valore di ricerca:
MATCH(data,$G$4:G4,0)
Notare che l'intervallo di espansione inizia dalla "riga sopra", riga 4 nell'esempio. Il risultato del MATCH interno è un array che, per ogni valore nei dati, contiene una posizione numerica (il valore è già stato estratto) o l'errore # N / A (il valore non è stato ancora estratto). Quindi utilizziamo IF e ISNA per filtrare questi risultati e restituiamo il valore di rango per tutti i valori in "dati" non ancora estratti:
IF(ISNA(results),rank))
Questa operazione produce un array, che viene inserito nella funzione MIN per ottenere il "valore di rango minimo" per i valori dei dati non ancora estratti. La funzione MIN restituisce questo valore al MATCH esterno come valore di ricerca e l'intervallo denominato "rank" come array:
MATCH(min_not_extracted,rank)),rank,0)
Infine, CONFRONTA restituisce la posizione del valore di rango più basso a INDICE come numero di riga e INDICE restituisce il valore dei dati nella riga corrente dell'intervallo di estrazione.
