Come eliminare il voto più basso e ottenere la media o il totale dei valori in Excel?
Quando lavori con un elenco di voti o punteggi in Excel, potresti aver bisogno di calcolare il voto finale di uno studente escludendo il loro punteggio più basso, o anche i punteggi più bassi, prima di calcolare la media o la somma dei valori rimanenti. Questo è un requisito comune negli ambienti educativi, dove agli studenti potrebbe essere permesso di eliminare le loro peggiori prestazioni per tener conto di anomalie o per garantire equità. Eseguire questa operazione manualmente potrebbe essere noioso, specialmente con grandi quantità di dati o frequenti aggiustamenti nei calcoli. Fortunatamente, Excel offre diversi modi flessibili per raggiungere questo obiettivo, che vanno da formule semplici all'automazione con VBA per operazioni in blocco.
Elimina il voto più basso e ottieni la media o la somma con le formule
Elimina il voto più basso e ottieni la media o la somma con le formule
Se desideri escludere il valore più basso o i valori più bassi n da una riga o lista di dati e poi eseguire calcoli, come fare la media o la somma dei numeri rimanenti, le formule incorporate di Excel offrono un approccio pratico. Queste soluzioni basate su formule sono particolarmente utili se hai un numero moderato di righe da elaborare o preferisci un approccio basato su formule per trasparenza e facilità di modifica.
Di seguito, troverai spiegazioni separate per entrambi i metodi di somma e media, inclusi istruzioni dettagliate e suggerimenti pratici per un'applicazione flessibile.
Somma i numeri ma elimina il valore più basso o i valori più bassi N:
Per calcolare la somma per ogni riga o lista omettendo il valore più basso, utilizza il seguente metodo:
1. Seleziona una cella vuota dove vuoi che appaia il risultato della somma per la prima riga (ad esempio, nella 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 questa formula alle altre righe, se necessario. Questo sommerà tutti i numeri in ogni 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 PICCOLI. 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 vuoi sommare, e i numeri 1, 2, 3, ecc., specificano i numeri più piccoli n da escludere. Regola n in base a quanti voti più bassi vuoi eliminare.
- Fai attenzione a non impostare n maggiore o uguale al numero totale di valori; altrimenti, incontrerai errori o risultati indesiderati.
- Queste formule funzionano in modo indipendente per le righe. Se i tuoi dati si estendono su colonne invece che su righe, regola gli intervalli di conseguenza.
- Se il tuo set di dati contiene duplicati del numero più basso, PICCOLO(B2:H2,1) eliminerà solo una occorrenza per riferimento. Per eliminare più occorrenze, ripeti il termine PICCOLO con numeri k incrementati come mostrato sopra.
Calcola la media dei numeri ma elimina il valore più basso o i valori più bassi N:
Per calcolare la media ignorando il valore più basso o i valori più bassi n, puoi usare le formule qui sotto. Questo calcolo è particolarmente utile nei sistemi di valutazione in cui le prestazioni basse anomale non devono essere considerate nella media.
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 secondo necessità per calcolare la media di righe aggiuntive, omettendo ogni volta il punteggio più basso nell'intervallo per quella riga.
Note e indicazioni importanti:
- Per calcolare la media eliminando più di un punteggio più basso, espandi la formula sottraendo ulteriori termini PICCOLI e riducendo di conseguenza il divisore:
=(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 rappresenta quanti valori più bassi non saranno inclusi nel calcolo.
- Se tenti di sottrarre più numeri di quanti ne esistano nell'intervallo, le formule restituiranno un #NUM! errore, indicando che ci sono valori insufficienti per la media. Assicurati sempre che n sia minore del conteggio dei numeri.
- Si consiglia di verificare attentamente che i valori più bassi non siano critici o necessari per il tuo calcolo prima di escluderli, poiché ciò potrebbe influire sui risultati finali.
- Per set di dati estremamente grandi o per eliminazioni dinamiche dei valori più bassi n, considera una soluzione automatizzata o basata su array.
Sblocca la Magia di Excel con Kutools AI
- Esecuzione Intelligente: Esegui operazioni sulle celle, analizza i dati e crea grafici — tutto guidato da semplici comandi.
- Formule Personalizzate: Genera formule su misura per ottimizzare i tuoi flussi di lavoro.
- Codifica VBA: Scrivi e implementa codice VBA senza sforzo.
- Interpretazione delle Formule: Comprendi facilmente formule complesse.
- Traduzione del Testo: Superare le barriere linguistiche all'interno dei tuoi fogli di calcolo.
Codice VBA - Elimina il voto più basso o i voti più bassi n e calcola automaticamente la somma o la media
Per situazioni che coinvolgono grandi quantità di dati o dati che cambiano frequentemente, o quando è necessario automatizzare l'eliminazione dei voti più bassi n e il calcolo delle somme o delle medie su molte righe, l'uso di VBA può semplificare notevolmente il lavoro ripetitivo. Con una macro VBA, puoi specificare l'intervallo di dati e quanti voti più bassi escludere, e il codice elaborerà tutte le righe selezionate in modo efficiente in un unico passaggio.
Questo approccio automatico è particolarmente utile per gli insegnanti che gestiscono fogli per più classi, o chiunque voglia minimizzare l'inserimento manuale di formule e potenziali errori. La soluzione seguente ti permette di regolare facilmente sia il numero di esclusioni che la funzione target (somma o media).
Prima di iniziare, salva la tua cartella di lavoro, poiché l'esecuzione di macro non può essere annullata direttamente.
1. Clicca su Sviluppatore > Visual Basic. Nella finestra Microsoft Visual Basic for Applications, clicca su Inserisci > Modulo, e poi inserisci 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, clicca sul 
pulsante o premi F5 per eseguire.
3. Segui le istruzioni che appaiono:
- Seleziona l'intervallo dei punteggi che desideri elaborare (assicurati che i punteggi di ogni studente siano su una riga).
- Scegli la cella in alto a sinistra dell'intervallo di output (l'output verrà popolato verso il basso in base al conteggio delle righe).
- Inserisci il numero di punteggi più bassi da eliminare (ad esempio, 1 per escludere solo il voto più basso in ogni riga).
- Digita SOMMA per ottenere il totale (escludendo i voti eliminati) o MEDIA per ottenere la media ricalcolata (escludendo i voti eliminati).
La macro elabora ogni riga dall'area dei punteggi specificata e inserisce la somma o la media (come scelto) nell'intervallo di output. Se tutti i punteggi vengono eliminati in una riga, il risultato è contrassegnato come N/D per evitare errori.
- Assicurati che l'intervallo di input corrisponda alla struttura dei tuoi dati (i voti di uno studente per riga).
- Le celle non numeriche (ad esempio, vuote o testuali) verranno ignorate per impostazione predefinita.
- Questo codice VBA accelera enormemente i calcoli ripetitivi per intere classi e supporta un aggiustamento flessibile del numero di voti da eliminare.
- Se esegui frequentemente tali operazioni, potresti voler assegnare questa macro a un pulsante sul tuo foglio di lavoro per un accesso ancora più rapido.
Se incontri problemi come output errati o errori, verifica attentamente che gli intervalli siano specificati correttamente e che "n" non sia maggiore o uguale al numero totale di voti disponibili per ogni riga.
Per requisiti di automazione simili, come eliminare sia i voti più alti che quelli più bassi o gestire colonne invece che righe, possono essere apportati piccoli aggiustamenti alla logica del codice VBA.
I 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 oltre300 funzionalità avanzate per aumentare la produttività e farti risparmiare tempo. Clicca qui per ottenere la funzione di cui hai più bisogno...
Office Tab porta le schede su Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra invece che in nuove finestre.
- Aumenta la produttività del50% e riduce centinaia di clic del mouse ogni giorno!
Tutti gli add-in Kutools. Un solo programma di installazione
La suite Kutools for Office include add-in per Excel, Word, Outlook & PowerPoint più Office Tab Pro, ideale per i team che lavorano su più app di Office.
- Suite tutto-in-uno — Add-in per Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un solo programma di installazione, una sola licenza — configurazione in pochi minuti (pronto per MSI)
- Funzionano meglio insieme — produttività ottimizzata su tutte le app Office
- Prova completa30 giorni — nessuna registrazione, nessuna carta di credito
- Massimo risparmio — costa meno rispetto all’acquisto singolo degli add-in