Download het voorbeeldwerkboek
Deze zelfstudie laat zien hoe u het gemiddelde van een reeks getallen kunt berekenen terwijl lege waarden of categorieën in Excel en Google Spreadsheets worden genegeerd.
Negeer lege waarden met de GEMIDDELDE functie
De functie GEMIDDELDE negeert automatisch alle cellen die leeg zijn of tekst bevatten.
In dit voorbeeld wordt de functie GEMIDDELDE gebruikt om de te berekenen Gemiddelde score terwijl de tekstwaarde van de . wordt genegeerd Scoren van Team B en de blanco waarde van de Scoren van Team C:
| 1 | =GEMIDDELDE(C3:C7) |
De GEMIDDELDE functie gebruikt alleen de numerieke waarden die horen bij: Teams A, D en E
Behandel tekstwaarden als nul met de AVERAGEA-functie
Om tekstwaarden in de berekening als nul te behandelen, kan de functie GEMIDDELDEA worden gebruikt. Lege celwaarden worden door deze functie genegeerd.
In dit voorbeeld wordt de AVERAGEA-functie gebruikt om de . te berekenen Gemiddelde score terwijl je de blanco negeert Scoren waarde van Team C en het behandelen van de tekst Scoren waarde van Team B als zijnde 0:
| 1 | =GEMIDDELDEA(C3:C7) |
Dit voorbeeld berekent het gemiddelde van de waarden 4, 0, 5 en 3
Negeer lege categoriewaarden met AVERAGEIFS
In dit voorbeeld wordt de AVERAGEIFS-functie gebruikt om de te berekenen Gemiddelde score voor teams met niet-lege namen en negers Scores dat zijn tekstwaarden
| 1 | =GEMIDDELDE.ALS(C3:C7,B3:B7,"") |
Lege categoriewaarden negeren - Array
Als u geen toegang hebt tot de AVERAGEIF-functie, kunt u een geneste AVERAGE- en IF-functie gebruiken.
Dit voorbeeld berekent de Gemiddelde score voor teams met niet-lege namen en negeert elke Scores dat is een tekstwaarde
| 1 | =GEMIDDELDE(ALS(B3:B7"",C3:C7)) |
Deze formule gebruikt de ALS-functie om te evalueren of de naam van elk Team is niet leeg en gebruikt alleen Scores van teams met niet-lege namen in de functie GEMIDDELDE. De score van 100 wordt genegeerd als de Team naam is leeg. De score van 'Geen informatie' wordt genegeerd omdat het geen numerieke waarde is.
In versies van Excel vanaf 2022 en eerder moet deze formule als matrixformule worden ingevoerd door op CTRL + SHIFT + ENTER te drukken. Dit is niet vereist in latere versies
Dit voorbeeld kan ook worden berekend met behulp van een AVERAGEIFS-functie zoals hieronder weergegeven:
Negeer lege categoriewaarden en behandel tekst als 0
Om tekstwaarde te behandelen Scores als 0, terwijl ik nog steeds negeer teams met lege namen kunnen we een geneste AVERAGEA en IF-functie gebruiken:
| 1 | =GEMIDDELDEA(ALS(B3:B7"",C3:C7)) |
Deze formule gebruikt de ALS-functie om te evalueren of de naam van elk Team is niet leeg en gebruikt alleen Scores van teams met niet-lege namen in de AVERAGEA-functie. De score van 100 wordt genegeerd als de Team naam is leeg. De score van 'Geen informatie' wordt behandeld als 0 door de AVERAGEA-functie en dus wordt een gemiddelde van 3,0 berekend.
In versies van Excel vanaf 2022 en eerder moet deze formule als matrixformule worden ingevoerd door op CTRL + SHIFT + ENTER te drukken. Dit is niet vereist in latere versies
Gemiddeld zo niet leeg in Google Spreadsheets
De meeste van deze formulevoorbeelden werken in Google Spreadsheets op dezelfde manier als in Excel, maar met enkele uitzonderingen:
Wanneer een ALS-functie is genest in een andere functie en verwijst naar een celbereik, moet Google Spreadsheets de formule behandelen als een matrixformule met behulp van de ARRAYFORMULA-functie:
| 1 | =ArrayFormule(GEMIDDELDE(ALS(B3:B7"",C3:C7))) |
Helaas werkt de AVERAGEA-functie in Google Spreadsheets anders dan in Excel en kan het voorbeeld van =AVERAGEA(IF(B3:B7””,C3:C7)) niet worden gebruikt. Dit komt omdat wanneer de IF-functie een lege waarde in het B3: B7-bereik vindt, deze een null-waarde produceert, maar deze wordt vervolgens geteld als een 0 door de Google Spreadsheets AVERAGEA-functie, in plaats van deze waarde te negeren, zoals het geval zou zijn in Excell.
