Sappiamo come ottenere il numero che appare più frequentemente in Excel. Usiamo la funzione MODE. Ma se vogliamo elencare i numeri che appaiono più frequentemente, questa formula non funzionerà. Avremo bisogno di una combinazione di funzioni excel per fare questo compito.
Ecco la formula generica per elencare i numeri più frequenti:
| =MODO(SE(1-VAL.NUMERO(CONFRONTA(numeri,intervallo in espansione,0)),numeri)) |
numeri:Questo è l'intervallo o l'array da cui abbiamo bisogno di ottenere i numeri che appaiono più frequentemente.
intervallo_espansione:È un intervallo in espansione che inizia da una cella sopra dove scrivi questa formula. Ad esempio, se la formula excel nella cella B4 è corretta, l'espansione rang sarà $ B $ 3: B3.
Ora, vediamo questa formula in azione.
Esempio: Ottieni 3 valutazioni che appaiono di frequente.
Quindi lo scenario è questo. Abbiamo alcuni dati su un sondaggio in cui gli utenti hanno valutato i nostri servizi. Dobbiamo ottenere le 3 valutazioni più frequenti.
Scrivi questa formula in G3:
| =MODO(SE(1-VAL.NUMERO(CONFRONTA($D$3:$D$11,$G$2:G2,0)),$D$3:$D$11)) |
Questa è una formula di matrice. Usa CTRL+MAIUSC+INVIO. Trascina verso il basso questa formula in tre celle. E ora hai i tuoi 3 numeri più frequenti.
Ora che l'abbiamo fatto, vediamo come funziona.
Come funziona?
Qui l'idea è di escludere i numeri che appaiono più frequentemente già estratti nella prossima esecuzione della funzione MODE. per farlo utilizziamo un aiuto extra da altre funzioni logiche di excel.
Per capirlo diamo un'occhiata alla formula nella cella G4. Che è:
| =MODO(SE(1-VAL.NUMERO(CONFRONTA($D$3:$D$11,$G$2:G3,0)),$D$3:$D$11)) |
La formula inizia con la funzione più interna CONFRONTA.
La funzione CONFRONTA ha un intervallo di ricerca D3: D11 (i numeri). Il valore di ricerca è anche un intervallo G2: G3 (espanso quando viene copiato). Ora, ovviamente, G2 contiene un testo che non è compreso nell'intervallo D3: D11. Successivamente, G3 contiene 6. CONFRONTA cerca 6 nell'intervallo D3: D11 e restituisce una matrice di errore #N/A e posizioni di 6 nell'intervallo, che è {#N/A;2;#N/A;2;2;#N/A;#N/A;#N/A;#N/A}.
Ora, questo array viene inviato alla funzione ISNUMBER. VAL.NUMERO restituisce VERO per i numeri nell'array. Quindi otteniamo un altro array come {FALSO;VERO;FALSO;VERO;VERO;FALSO;FALSO;FALSO;FALSO}.
Successivamente questo array viene sottratto da 1. Come sappiamo FALSE viene trattato come 0 e TRUE come 1. Sottraendo questo array da 1 otteniamo un array invertito come{1;0;1;0;0;1;1;1;1}. Questa è l'immagine speculare esatta dell'array restituita dalla funzione ISNUMBER.
Ora, per ogni funzione 1 (TRUE) MODE viene eseguita. La funzione MODE ottiene un array{2;FALSO;2;FALSO;FALSO;5;4;4;5}.E infine, otteniamo il numero che appare più frequentemente nell'array come 2. (Nota che 5 e 4 si verificano entrambi 2 volte. Ma poiché 2 è apparso per primo, otteniamo 2.
In G3, la funzione VAL.NUMERO restituisce solo un array di FALSE. Quindi l'intera gamma è fornita alla funzione MODE. Quindi la funzione modalità restituisce il numero che appare più frequentemente nell'intervallo come 6.
Un'altra formula
Un altro modo è usare la funzione NOT per invertire il ritorno dell'array con la funzione ISNUMBER.
| {=MODE(IF(NOT(VAL.NUMBER(MATCH($D$3:$D$11,$G$2:G3,0))),$D$3:$D$11))} |
Quindi sì ragazzi, ecco come recuperare i numeri che appaiono più frequentemente in Excel. Spero di essere stato abbastanza esplicativo. In caso di dubbi su qualsiasi funzione utilizzata in questa formula, è sufficiente fare clic su di essi. Ti porterà alla spiegazione di quella funzione. Se hai domande su questo articolo o su qualsiasi altra funzione excel o VBA, chiedimelo nella sezione commenti qui sotto.
Come trovare i numeri ricorrenti | I numeri che compaiono più di frequente possono essere calcolati utilizzando la funzione MODALITÀ di Excel.
Ottieni il testo che appare più frequentemente in Excel | Scopri come ottenere testo che appare frequentemente in Excel.
Cerca testo che appare frequentemente con criteri in Excel | Per ottenere il testo che appare più frequentemente in Excel con criteri, devi solo inserire una condizione if prima della funzione di corrispondenza.
Recupero del primo valore in un elenco maggiore/minore di un valore specificato | Scopri come cercare il primo valore maggiore di un valore specificato. La formula generica da cercare è…
Come recuperare l'intera riga di un valore corrispondente| È facile cercare e recuperare l'intera riga da una tabella. Vediamo come possiamo fare questa ricerca.
Come recuperare l'ultimo prezzo in Excel | Per cercare l'ultimo prezzo da un intervallo è necessario disporre i dati ordinati. Una volta ordinato usa questa formula…
Articoli popolari:
50 scorciatoie di Excel per aumentare la produttività | Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
La funzione CERCA.VERT in Excel | Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
CONTA.SE in Excel 2016 | Conta i valori con le condizioni usando questa fantastica funzione. Non è necessario filtrare i dati per contare un valore specifico. La funzione Countif è essenziale per preparare la tua dashboard.
Come utilizzare la funzione SOMMA.SE in Excel | Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.