Formula generica
=INDEX(rng1,MODE(IF(rng2=criteria,MATCH(rng1,rng1,0))))
Sommario
Per trovare il testo che ricorre più di frequente in un intervallo, in base ai criteri forniti, è possibile utilizzare una formula di matrice basata su diverse funzioni di Excel INDICE, CONFRONTA, MODALITÀ e SE. Nell'esempio mostrato, la formula in G5 è:
=INDEX(supplier,MODE(IF(client=F5,MATCH(supplier,supplier,0))))
dove "fornitore" è l'intervallo denominato C5: C15 e "client" è l'intervallo denominato B5: B15.
Nota: questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO.
Spiegazione
Lavorando dall'interno verso l'esterno, usiamo la funzione CONFRONTA per abbinare l'intervallo di testo rispetto a se stesso, dando a CONFRONTA lo stesso intervallo per il valore di ricerca e l'array di ricerca, con zero per il tipo di corrispondenza:
MATCH(supplier,supplier,0)
Poiché il valore di ricerca è un array con 10 valori, MATCH restituisce un array di 10 risultati:
(1;1;3;3;5;1;7;3;1;5;5)
Ogni articolo in questo array rappresenta la prima posizione in cui viene visualizzato il nome di un fornitore nei dati. Questo array viene inserito nella funzione IF, che viene utilizzata per filtrare i risultati solo per il client A:
IF(client=F5,(1;1;3;3;5;1;7;3;1;5;5))
IF restituisce l'array filtrato alla funzione MODE:
(1;FALSE;3;FALSE;5;1;FALSE;FALSE;1;5;FALSE)
Notare che solo le posizioni associate al Cliente A rimangono nell'array. MODE ignora i valori FALSE e restituisce il numero che si verifica più di frequente alla funzione INDEX come numero di riga:
=INDEX(supplier,1)
Infine, con la gamma denominata "fornitore" come matrice, INDICE restituisce "Marrone", il fornitore più frequente per il Cliente A.
