Formula generica
(=MODE(IF(1-ISNUMBER(MATCH(data,exp_rng,0)),data)))
Sommario
Per elencare i numeri che si verificano più di frequente in una colonna (cioè il più comune, il secondo più comune, il terzo più comune, ecc.), Puoi una formula di matrice basata su quattro funzioni di Excel: SE, MODO, CONFRONTA e NUMERO ISTANTANEO. Nell'esempio mostrato, la formula in D5 è:
(=MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)))
dove "dati" è l'intervallo denominato B5: B16. La formula viene quindi copiata nelle righe sotto D5 per produrre l'elenco desiderato dei numeri più frequenti.
Nota: questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO.
Spiegazione
Il nucleo di questa formula è la funzione MODE, che restituisce il numero più frequente in un intervallo o in una matrice. Il resto della formula costruisce solo un array filtrato per MODE da utilizzare in ogni riga. L'intervallo in espansione $ D $ 4: D4 consente di escludere i numeri già emessi in $ D $ 4: D4.
Lavorare dall'interno verso l'esterno:
- La funzione CONFRONTA viene utilizzata prima controlla tutti i numeri nell'intervallo denominato "dati" rispetto ai numeri esistenti nell'intervallo in espansione $ D $ 4: D4
- ISNUMBER converte i valori corrispondenti in TRUE e i valori non corrispondenti in FALSE
- 1-NUMBER inverte la matrice e l'operazione matematica restituisce uno e zero
- IF utilizza l'output della matrice di # 3 sopra per filtrare l'elenco originale di valori, esclusi i numeri già in $ D $ 4: D4
- La funzione MODE restituisce il numero più frequente nell'output dell'array al punto 4
Nella cella D5, non si verifica alcun filtro e l'output di ogni passaggio precedente è simile al seguente:
(#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A) (FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE) (1;1;1;1;1;1;1;1;1;1;1;1) (93;92;93;94;95;96;97;98;99;93;97;95) 93
Nella cella D6, con 93 già in D5, l'output è simile a questo:
(2;#N/A;2;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;2;#N/A;#N/A) (TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE) (0;1;0;1;1;1;1;1;1;0;1;1) (FALSE;92;FALSE;94;95;96;97;98;99;FALSE;97;95) 95
Gestione degli errori
La funzione MODE restituirà l'errore # N / A quando non è presente una modalità. Mentre copi la formula nelle righe successive, probabilmente ti imbatterai nell'errore # N / D. Per intercettare questo errore e restituire una stringa vuota (""), puoi utilizzare SE.ERRORE in questo modo:
=IFERROR(MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)),"")
