Come si fa a escludere il voto più basso e calcolare la media o il totale dei valori in Excel?
Quando lavori con un elenco di voti o punteggi in Excel, potresti dover calcolare il voto finale di uno studente escludendo il punteggio più basso — o addirittura gli *n* punteggi più bassi — prima di calcolare la media o la somma dei valori rimanenti. Si tratta di un’esigenza comune negli ambienti scolastici, dove agli studenti viene spesso consentito di scartare le prestazioni peggiori per tenere conto di anomalie o garantire maggiore equità. Eseguire questa operazione manualmente può rivelarsi noioso e dispendioso, soprattutto con set di dati ampi o quando i calcoli richiedono aggiustamenti frequenti. Fortunatamente, Excel offre diversi metodi flessibili per ottenere questo risultato, dalle formule semplici all’automazione tramite VBA per elaborazioni in blocco.
Escludi il voto più basso e ottieni la media o la somma con le formule
Escludi il voto più basso e ottieni la media o la somma con le formule
Se desideri escludere il valore più basso o gli n valori più bassi da una riga o da un elenco di dati per poi calcolare, ad esempio, la media o la somma dei numeri rimanenti, le formule integrate di Excel offrono una soluzione pratica e immediata. Questi approcci basati su formule sono ideali soprattutto quando devi elaborare un numero contenuto di righe o preferisci un metodo trasparente e facilmente personalizzabile.
Di seguito troverai spiegazioni distinte per entrambi i metodi, relative al calcolo della somma e della media, complete di istruzioni dettagliate e suggerimenti pratici per un’applicazione flessibile.
Somma i numeri escludendo il più basso o gli N più bassi:
Per calcolare la somma per ogni riga o elenco escludendo il valore più basso, utilizza il seguente metodo:
1. Seleziona una cella vuota in cui vuoi visualizzare la somma della prima riga (ad esempio, la cella I2 se i tuoi dati sono in B2:H2), quindi inserisci la seguente formula:
=SUM(B2:H2)-SMALL(B2:H2,1)2. Premi Invio per confermare, quindi trascina il quadratino di riempimento verso il basso per applicare la formula alle altre righe secondo necessità. In questo modo verranno sommati tutti i numeri in ciascuna riga, escludendo il valore più basso.
Ecco uno screenshot di riferimento:
Note e suggerimenti:
- Per escludere i due, tre o più valori più bassi, puoi estendere la formula sottraendo ulteriori risultati della funzione PICCOLO. Ad esempio:
=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)-...-SMALL(B2:H2,n)- In queste formule, B2:H2 è l'intervallo che desideri sommare, mentre i numeri 1, 2, 3, ecc. indicano quanti dei valori più piccoli escludere. Modifica n in base al numero di voti più bassi che vuoi scartare.
- Fai attenzione a non impostare n maggiore o uguale al numero totale di valori; altrimenti, otterrai errori o risultati indesiderati.
- Queste formule operano in modo indipendente su ogni riga. Se i tuoi dati sono organizzati in colonne anziché in righe, adatta di conseguenza gli intervalli.
- Se il tuo set di dati contiene duplicati del valore più basso, PICCOLO(B2:H2;1) escluderà solo un’occorrenza per riferimento. Per escludere più occorrenze, ripeti la funzione PICCOLO con valori k incrementali, come mostrato sopra.
Calcola la media dei numeri escludendo il più basso o gli N più bassi:
Per calcolare la media escludendo il valore più basso o gli n valori più bassi, puoi utilizzare le seguenti formule. Questo approccio è particolarmente utile nei sistemi di valutazione in cui i risultati anomali peggiori non devono influenzare il punteggio medio.
1. Seleziona una cella per il risultato della media (ad esempio, J2 se i tuoi punteggi sono in B2:H2) e inserisci la formula:
=(SUM(B2:H2)-SMALL(B2:H2,1))/(COUNT(B2:H2)-1)2. Dopo aver premuto Invio, trascina la formula verso il basso per calcolare la media delle righe aggiuntive, escludendo ogni volta il punteggio più basso nell’intervallo corrispondente a ciascuna riga.
Note e indicazioni importanti:
- Per calcolare la media escludendo più di un voto più basso, estendi la formula sottraendo ulteriori termini PICCOLOe riducendo il divisore di conseguenza:
=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2))/(COUNT(B2:H2)-2)=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3))/(COUNT(B2:H2)-3)=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)-...-SMALL(B2:H2,n))/(COUNT(B2:H2)-n)- Ancora una volta, B2:H2 è l'intervallo da mediare e n indica quanti valori più bassi non saranno inclusi nel calcolo.
- Se cerchi di sottrarre più numeri di quanti ne esistano nell’intervallo, le formule restituiranno un errore #NUM!, a indicare che non ci sono abbastanza valori per calcolare la media. Assicurati sempre che n sia inferiore al conteggio dei numeri.
- Si consiglia di verificare attentamente che i valori più bassi non siano critici o essenziali per il tuo calcolo prima di escluderli, poiché potrebbero influenzare i risultati finali.
- Per set di dati molto grandi o per escludere dinamicamente gli n valori più bassi, valuta una soluzione automatizzata o basata su matrici.
Sblocca la magia di Excel con KUTOOLS AI
- Esecuzione intelligente: Esegui operazioni sulle celle, analizza i dati e crea grafici, il tutto con comandi semplici e intuitivi.
- Formule personalizzate: crea formule su misura per ottimizzare i tuoi flussi di lavoro.
- Codifica VBA: Scrivi e implementa codice VBA in modo semplice e immediato.
- Interpretazione delle formule: Comprendi con facilità anche le formule più complesse.
- Traduzione del testo: Superate le barriere linguistiche direttamente nei vostri fogli di calcolo.
Codice VBA – Escludi il voto più basso o gli n voti più bassi e calcola automaticamente la somma o la media
Per gestire set di dati ampi o in continua evoluzione, o per automatizzare l’esclusione degli *n* voti più bassi e il calcolo di somme o medie su numerose righe, VBA si rivela uno strumento estremamente efficace. Grazie a una macro VBA, basta indicare l’intervallo di dati e il numero di voti più bassi da escludere: il codice elaborerà automaticamente tutte le righe selezionate in modo rapido e preciso, con un solo clic.
Questo approccio automatico è ideale per gli insegnanti che gestiscono fogli di più classi o per chiunque voglia ridurre al minimo l’inserimento manuale di formule e gli errori correlati. La soluzione seguente ti consente di regolare in modo semplice sia il numero di esclusioni sia la funzione di destinazione (somma o media).
Prima di iniziare, salva la cartella di lavoro: l’esecuzione delle macro non può essere annullata.
1. Fai clic su Sviluppo > Visual Basic. Nella finestra Microsoft Visual Basic, Applications Edition, fai clic su Inserisci > Modulo e inserisci quindi il seguente codice:
Sub DropLowestNandCalculate()
Dim WorkRng As Range
Dim OutputRng As Range
Dim n As Integer
Dim FuncType As String
Dim i As Integer, j As Integer, k As Integer
Dim Arr() As Variant, TempArr() As Double
Dim RowSum As Double
Dim RowCount As Integer
Dim MinIdx() As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the score range (rows to process):", xTitleId, WorkRng.Address, Type:=8)
Set OutputRng = Application.InputBox("Select output cells (top-left for results):", xTitleId, WorkRng.Offset(0, WorkRng.Columns.Count).Cells(1, 1).Address, Type:=8)
n = Application.InputBox("Number of lowest grades to drop (n):", xTitleId, "1", Type:=1)
FuncType = Application.InputBox("Type 'SUM' to calculate total or 'AVG' to calculate average (not case sensitive):", xTitleId, "AVG", Type:=2)
For i = 1 To WorkRng.Rows.Count
Arr = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(WorkRng.Rows(i).Value))
RowCount = UBound(Arr)
ReDim TempArr(1 To RowCount)
For j = 1 To RowCount
TempArr(j) = Arr(j)
Next j
' Mark n lowest values as used by setting to very high number
For k = 1 To n
Dim MinVal As Double, MinPos As Integer
MinVal = Application.WorksheetFunction.Min(TempArr)
For j = 1 To RowCount
If TempArr(j) = MinVal Then
TempArr(j) = 1E+308
Exit For
End If
Next j
Next k
RowSum = 0
Dim ValidCount As Integer
ValidCount = 0
For j = 1 To RowCount
If TempArr(j) <> 1E+308 Then
RowSum = RowSum + Arr(j)
ValidCount = ValidCount + 1
End If
Next j
If UCase(FuncType) = "AVG" Then
If ValidCount = 0 Then
OutputRng.Cells(i, 1).Value = "N/A"
Else
OutputRng.Cells(i, 1).Value = RowSum / ValidCount
End If
Else
OutputRng.Cells(i, 1).Value = RowSum
End If
Next i
End Sub2. Dopo aver aggiunto il codice, fai clic sul pulsante 
o premi F5 per eseguirlo.
3. Segui le istruzioni visualizzate:
- Seleziona l’intervallo dei voti da elaborare, assicurandoti che i voti di ciascuno studente siano disposti su una riga.
- Scegli la prima cella a sinistra dell’Area di posizionamento dell’elenco (l’output verrà popolato verso il basso in base al numero di righe).
- Inserisci il numero di voti più bassi da escludere (ad esempio,)1 per escludere solo il voto più basso in ciascuna riga).
- Digita SOMMA per ottenere il totale (escludendo i voti scartati) oppure MEDIA per calcolare la media aggiornata (escludendo i voti scartati).
La macro elabora ogni riga dell’area punteggi specificata e inserisce la somma o la media (a seconda della tua scelta) nella tua area di posizionamento dell’elenco. Se tutti i punteggi di una riga vengono esclusi, il risultato viene contrassegnato come N/D per evitare errori.
- Assicurati che l’intervallo di input corrisponda alla struttura dei tuoi dati: un voto per studente su ogni riga.
- Le celle non numeriche (ad esempio, vuote o contenenti testo) vengono ignorate automaticamente.
- Questo codice VBA accelera notevolmente i calcoli ripetitivi per intere classi e consente di regolare in modo flessibile il numero di voti da escludere.
- Se esegui spesso queste operazioni, potresti voler assegnare la macro a un pulsante sul foglio di lavoro per un accesso ancora più rapido.
Se riscontri problemi come output errati o errori, verifica nuovamente che gli intervalli siano specificati correttamente e che «n» non sia maggiore o uguale al numero totale di voti disponibili per ciascuna riga.
Per esigenze di automazione analoghe, come escludere contemporaneamente il punteggio più alto e quello più basso o gestire colonne anziché righe, è sufficiente apportare lievi modifiche alla logica del codice VBA.
Migliori Strumenti per la Produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un’efficienza mai vista prima.Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e Risparmia tempo.Clicca qui per ottenere la funzionalità di cui hai più bisogno...
Office Tab Porta l'interfaccia a schede in Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede all’interno della stessa finestra, invece che in finestre separate.
- Aumenta la tua produttività del 50 % e risparmia centinaia di clic del mouse ogni giorno!
Tutti i componenti aggiuntivi di Kutools in un unico programma di installazione.
Kutools for Office è la suite che include componenti aggiuntivi per Excel, Word, Outlook e PowerPoint, oltre a Office Tab Pro: la soluzione ideale per i team che lavorano su diverse app di Office.
- Suite completa— componenti aggiuntivi per Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Un unico programma di installazione, una sola licenza— configurazione in pochi minuti (pronto per MSI)
- Funziona meglio insieme— produttività ottimizzata tra le app di Office
- Prova gratuita di 30 giorni con tutte le funzionalità— nessuna registrazione, nessuna carta di credito
- Miglior rapporto qualità-prezzo— risparmia rispetto all’acquisto dei singoli componenti aggiuntivi