Formula di Excel: classifica i valori per mese -

Sommario

Sommario

Per visualizzare un elenco di nomi, classificati in base a un valore numerico, è possibile utilizzare una serie di formule basate su LARGE, INDEX, MATCH, con l'aiuto della funzione TEXT. Nell'esempio mostrato, la formula in G5 è:

=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)

E la formula in G10 è:

=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))

dove client (B5: B17) Data (C5: C17) e quantità (C5: C17) sono chiamati intervalli.

Nota: queste sono formule di matrice e devono essere inserite con CTRL + MAIUSC + INVIO, tranne che in Excel 365.

Spiegazione

Questo esempio è strutturato in due parti per chiarezza: (1) una formula per determinare i primi 3 importi per ogni mese e (2) una formula per recuperare il nome del cliente per ciascuno dei primi 3 importi mensili.

Tieni presente che non esiste una classificazione effettiva nei dati di origine. Invece, stiamo usando la funzione LARGE per lavorare direttamente con gli importi. Un altro approccio consiste nell'aggiungere il rango ai dati di origine con la funzione RANK e utilizzare il valore del rango per recuperare i nomi dei client.

Parte 1: recupera i primi 3 importi ogni mese

Per recuperare i primi 3 importi per ogni settimana, la formula in G5 è:

=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)

Nota: questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO, tranne che in Excel 365.

Lavorando dal fuori all'interno, per prima cosa utilizzare la funzione testo per ottenere i nomi dei mesi per ogni data nell'intervallo denominato date :

TEXT(date,"mmmm") // get month names

Il formato numerico personalizzato "mmmm" restituirà una stringa come "aprile", "maggio", "giugno" per ogni nome nella data dell'intervallo denominato . Il risultato è un array di nomi di mesi come questo:

("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")

La funzione TESTO fornisce questo array alla funzione SE, che è configurata per filtrare le date in un determinato mese testando il nome del mese rispetto al valore in G4 (un riferimento misto, quindi la formula può essere copiata verso il basso e attraverso):

IF(TEXT(date,"mmmm")=G$4,amount) // filter on month

Solo gli importi di aprile sopravvivono e superano IF; tutti gli altri valori sono FALSE:

(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)

Infine, la funzione LARGE utilizza il valore in F5 (anche un riferimento misto) per restituire l '"n-esimo" valore più grande che rimane. Nella cella G5, GRANDE restituisce 18.500, il "primo" valore più grande. Poiché la formula viene copiata lungo la tabella, la funzione GRANDE restituisce i primi 3 importi in ciascuno dei tre mesi.

Ora che conosciamo i primi 3 valori in ogni mese, possiamo utilizzare queste informazioni come una "chiave" per recuperare il nome del cliente per ciascuno.

Parte 2: recuperare i nomi dei client

Nota: questo è un esempio di utilizzo di INDICE e CONFRONTA con più criteri. Se questo concetto è nuovo per te, ecco un esempio di base.

Per recuperare il nome associato ai primi tre valori in G5: I7, usiamo INDEX e MATCH:

=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))

Nota: questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO, tranne che in Excel 365.

Lavorando dall'interno verso l'esterno, la funzione MATCH è configurata per utilizzare la logica booleana in questo modo:

MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)

Il valore di ricerca è 1 e l'array di ricerca è costruito con questa espressione:

(amount=G5)*(TEXT(date,"mmmm")=G$9)

L'espressione che crea l'array di ricerca utilizza la logica booleana per "filtrare" gli importi che sono (1) non in aprile e (2) non il valore in G5 (18.500). Il risultato è un array di 1 e 0 come questo:

(0;0;1;0;0;0;0;0;0;0;0;0;0)

Con un valore di ricerca di 1 e zero per il tipo di corrispondenza (per forzare una corrispondenza esatta) MATCH restituisce 3 direttamente alla funzione INDICE: