Scarica la cartella di lavoro di esempio
Questo tutorial dimostrerà come trovare il valore di cella più grande che soddisfa condizioni specifiche in Excel e Fogli Google.
Funzione Max If Array
La funzione MAX identifica il valore più grande in una serie di numeri.
Possiamo usare la funzione MAX combinata con una funzione IF per identificare il valore più grande che soddisfa una condizione specificata.
Si consiglia agli utenti di Fogli Google ed Excel 2022 o versioni successive di utilizzare la funzione MAXIFS più semplice. Questo è spiegato in una sezione successiva.
Questo esempio utilizzerà le funzioni MAX e IF in una formula di matrice per identificare il più grande Dimensione dell'ordine per ciascuno Nome del negozio
| 1 | {=MAX(SE(B3:B8="A",D3:D8))} |
In Office 365 e nelle versioni di Excel successive al 2022, puoi semplicemente inserire la formula sopra come faresti normalmente (premendo INVIO).
Tuttavia, per Excel 2022 e versioni precedenti è necessario immettere la formula premendo CTRL + MAIUSC + INVIO. Dopo averlo fatto, noterai che le parentesi graffe dell'array appaiono attorno alla formula.
Per mostrare come funziona questa formula, suddividiamola in passaggi.
Questa è la nostra formula finale (mostrata senza le parentesi della formula di matrice aggiunte automaticamente):
| 1 | =MAX(SE(B3:B8="A",D3:D8)) |
Innanzitutto, i valori dell'intervallo di celle vengono aggiunti alla formula come matrici:
| 1 | =MAX(SE({"A"; "B"; "A"; "B"; "A"; "B"}="A",{500; 400; 300; 700; 600; 200})) |
Avanti il Nome del negozio La condizione ="A" produce un array di valori VERO/FALSO:
| 1 | =MAX(SE({VERO; FALSO; VERO; FALSO; VERO; FALSO},{500; 400; 300; 700; 600; 200})) |
Quindi la funzione IF cambia tutti i valori VERI nel relativo Dimensione dell'ordine:
| 1 | =MAX({500; FALSO; 300; FALSO; 600; FALSO}) |
La funzione MAX identifica il numero più grande nell'array, ignorando eventuali valori FALSE, per mostrare il più grande Dimensione dell'ordine per il Nome del negozio = “A”:
| 1 | =600 |
Max If - Criteri multipli
Possiamo anche identificare il valore più grande in base a più criteri utilizzando la logica booleana.
Questo esempio mostrerà il più grande Dimensione dell'ordine per ciascuno Nome del negozio, ma per Date dell'ordine entro il 30/4/2021 utilizzando le Funzioni MAX, SE e DATA:
| 1 | {=MAX(SE((B3:B8="A")*(C3:C8 |
Nota che qui moltiplichiamo insieme due serie di criteri VERO/FALSO:
| 1 | (La3:La8="LA") * (La3:La8 |
Se entrambi i criteri sono VERI, la condizione totale verrà calcolata come VERA, ma se uno (o più) criteri è FALSO, verrà calcolata come FALSO.
Utilizzando questa metodologia, è possibile aggiungere molti criteri diversi a questa formula.
Max If - Criteri multipli con riferimenti di cella
Di solito, non è una buona pratica codificare i valori nelle formule. Invece, è più flessibile utilizzare celle separate per definire i criteri.
Per abbinare il Nome del negozio al valore mostrato nella colonna F, possiamo aggiornare la formula in modo che sia:
| 1 | {=MAX(SE((B3:B8=F3)*(C3:C8 |
Riferimenti di cella di blocco
Per rendere le nostre formule più facili da leggere, abbiamo mostrato le formule senza riferimenti di cella bloccati:
| 1 | {=MAX(SE((B3:B8=F3)*(C3:C8 |
Ma queste formule non funzioneranno correttamente se copiate e incollate altrove nel file. Invece, dovresti usare riferimenti di cella bloccati come questo:
| 1 | {=MAX(SE(($B$3:$B$8=F3)*($C$3:$C$8 |
Leggi il nostro articolo sul blocco dei riferimenti di cella per saperne di più.
Funzione MAXIFS
Gli utenti di Fogli Google ed Excel 2022 o versioni successive possono utilizzare la singola funzione MAXIFS per replicare il comportamento delle funzioni MAX e IF mostrato negli esempi precedenti.
Questo prossimo esempio usa le funzioni MAXIFS e DATE per mostrare il più grande Dimensione dell'ordine per ciascuno Nome del negozio per Date dell'ordine entro il 30/4/2021:
| 1 | =MAXIFS(D3:D8,B3:B8,"A",C3:C8,"<"&DATA(2021,4,30)) |
La funzione MAXIFS non richiede all'utente di premere CTRL + MAIUSC + INVIO durante l'immissione della formula.
Max If (valore massimo con condizione) in Fogli Google
Gli esempi mostrati sopra funzionano esattamente allo stesso modo in Fogli Google come in Excel, ma poiché è disponibile la funzione MAXIFS, si consiglia di utilizzare questa singola funzione invece di combinare le funzioni MAX e IF.
Se è necessario utilizzare gli esempi che utilizzano le funzioni MAX e IF, Fogli Google richiede di inserirli come formule di matrice. Invece di mostrare la formula con parentesi graffe di matrice Excel { }, premendo CTRL + MAIUSC + INVIO si aggiunge automaticamente la funzione ARRAYFORMULA attorno alla formula:
| 1 | =FORMULAARRAY(MAX(SE((B3:B8="A")*(C3:C8 |
