Als u de 3 waarden in Excel wilt optellen, kunt u dit doen door eenvoudig de top 3 waarden te berekenen met behulp van een LARGE-functie en deze op te tellen.
Maar dat is niet de enige manier. U kunt de hoogste N-waarden in Excel optellen met behulp van meerdere formules. Ze gebruiken allemaal de LARGE-functie als toetsfunctie. Zie de afbeelding hierboven. Laten we ze allemaal verkennen.
Som van Top 3- of Top N-waarden met behulp van de SUMIF-functie
Generieke formule
| =SOM.ALS(bereik,">="&GROOT(bereik,n)) |
Bereik:Het bereik is het bereik waarvan u de hoogste N-waarden wilt optellen.
N:Dit is het aantal topwaarden dat u wilt optellen. Als je de top 3 waarden wilt optellen, danNis 3.
Dus laten we deze formule in actie zien.
Voorbeeld 1: SUMIF gebruiken om de top 3 verkopen op te tellen.
We hebben gegevens die de verkopen in verschillende maanden bevatten. We moeten de top 3 verkopen samenvatten. We gebruiken de generieke formule hierboven.
De bereik is C2:C13 enN is 3. Dan is de formule:
| =SOM.ALS(C2:C13,">="&GROOT(C2:C13,3)) |
Dit keert terug naar 696020, wat correct is. Als je de topvariabele N-waarden wilt samenvatten en N is geschreven in D2, dan is de formule:
| =SOM.ALS(C2:C13,">="&GROOT(C2:C13,D2)) |
het somt alle waarden op die groter zijn dan of gelijk zijn aan de waarde van de hoogste N-waarde.
Hoe werkt dit?
De LARGE-functie retourneert de bovenste N-de waarde in het opgegeven bereik. In de bovenstaande formule retourneert de functie LARGE de derde grootste waarde in het bereikC2:C13dat is 212940. Nu is de formule:
| =SOM.ALS(C2:C13,">="&212940) |
Nu retourneert de SUMIF-functie eenvoudig de SOM van waarden groter dan of gelijk aan 212940, wat 696020 is.
Als u meerdere voorwaarden wilt toevoegen om top 3- of N-waarden op te tellen, gebruikt u de functie SOMMEN.
Som van Top 3- of Top N-waarden met behulp van SUM-functie
De bovenstaande methode is vrij eenvoudig en snel te gebruiken als het gaat om het samenvatten van de top 3 of N-waarden uit de lijst. Maar het wordt moeilijk als het gaat om het optellen van de hoogste willekeurige N-waarden, zeg de bovenste 2e, 4e en 6e waarde uit een Excel-bereik. Hier krijgt de functie SOM of SOMPRODUCT het werk gedaan.
Generieke formule:
| =SOM(GROOT(bereik,{1,2,3})) |
of
| =SOMPRODUCT(GROOT(bereik,{1,2,3})) |
Bereik:Het bereik is het bereik waaruit u de hoogste N-waarden wilt optellen.
{1,2,3}: Dit zijn de topwaarden die u wilt samenvatten. Hier is het 1e, 2e en 3e.
Dus laten we deze formule gebruiken in het bovenstaande voorbeeld.
Voorbeeld 2: Tel de grootste 1e, 2e en 3e waarden op uit een Excel-bereik.
We gebruiken dus dezelfde tabel die we in het bovenstaande voorbeeld hebben gebruikt. De bereik is nog steeds C2:C13 en de top 3 waarden die we willen optellen zijn de 1e, 2e en 3e grootste waarden in de bereik.
| =SOMPRODUCT(GROOT(C2:C13,{1,2,3})) |
Het retourneert hetzelfde resultaat als voorheen, namelijk 696020. Als u de 2e en 4e bovenste waarden wilt optellen, is de formule:
| =SOMPRODUCT(GROOT(C2:C13,{2,4})) |
En het resultaat is 425360.
In het bovenstaande geval kunt u de functie SOMPRODUCT vervangen door de functie SOM. Maar als u naar topwaarden wilt verwijzen, moet u CTRL+SHIF+ENTER gebruiken om er een matrixformule van te maken.
| {=SOM(GROOT(C2:C13,E2:E4))} |
Hoe werkt het?
De functie LARGE accepteert een reeks sleutelwaarden en retourneert een reeks van die topwaarden. Hier retourneert de grote functie de waarden {241540;183820;38740} als bovenste 2e, 4e en 6e waarden. Nu wordt de formule:
| =SOM({241540;183820;38740}) |
of
| =SOMPRODUCT({241540;183820;38740}) |
Dit somt de waarden op die worden geretourneerd door de LARGE-functie.
Een groot aantal topwaarden optellen in Excel
Stel dat u de top 10-2o waarden wilt optellen, alles inclusief. In dit geval worden de bovengenoemde methoden lastig en langer. De beste manier is om de onderstaande generieke formule te gebruiken om de topwaarden samen te vatten:
| =SOMPRODUCT(GROOT(bereik,RIJ(INDIRECT("begin:einde")))) |
bereik:Het bereik is het bereik waarvan u de hoogste N-waarden wilt optellen.
"begin het einde":Het is de tekst van het bereik van topwaarden die u wilt samenvatten. Als u een aantal top 3 tot top 6 waarden wilt, dan is start:end "3:6".
Laten we deze formule in actie bekijken:
Voorbeeld 3: Tel de top 3 tot 6 waarden van verkopen op:
We hebben dus dezelfde tabel als het bovenstaande voorbeeld. De enige vraag is anders. Met behulp van de bovenstaande generieke formule schrijven we deze formule:
| =SOMPRODUCT(GROOT(C2:C13,RIJ(INDIRECT("3:6")))) |
Dit retourneert de waarde 534040. Dit is de som van de 3e, 4e, 5e en 6e grootste waarde uit het bereik C2:C13.
Hoe werkt dit?
Ten eerste verandert de INDIRECT-functie de tekst "3:6" in het werkelijke bereik 3:6. Verder retourneert de ROW-functie een array met rijnummers in het bereik 3:6, wat {3,4,5,6} zal zijn. Vervolgens retourneert de functie LARGE de 3e, 4e, 5e en 6e grootste waarden uit het bereik C2:C13. Ten slotte somt de SOMPRODUCT-functie deze waarden op en krijgen we ons resultaat.
| =SOMPRODUCT(GROOT(C2:C13,RIJ(INDIRECT("3:6"))))
=SOMPRODUCT(GROOT(C2:C13,{3,4,5,6})) =SOMPRODUCT({212940;183820;98540;38740}) = 534040 |
Bonus: som de hoogste N-waarden op met behulp van de SEQUENCE-functie
De functie SEQUENTIE is geïntroduceerd in Excel 2019 en 365. Het retourneert een reeks getallen. We kunnen het gebruiken om de hoogste N-waarden te krijgen en deze vervolgens samen te vatten.
Generieke formule:
| =SOMPRODUCT(GROOT(bereik,VOLGORDE(aantal_waarden,,[start_getal], [stappen])))) |
bereik:Het bereik is het bereik waarvan u de hoogste N-waarden wilt optellen.
aantal_waarden: Het is het aantal topwaarden dat u wilt optellen.
[start_num]:Het is het startnummer van de serie. Het is optioneel. Als je dit weglaat, begint de reeks bij 1.
[stappen]: Het is het verschil tussen het volgende nummer en het huidige nummer. Standaard is dit 1.
Als we deze generieke formule gebruiken om hetzelfde resultaat te krijgen als in het vorige voorbeeld, wordt de formule:
| =SOM(GROOT(C2:C13;VOLGORDE(4,,3))) |
Het retourneert de waarde 534040.
Hoe werkt het?
Het is simpel. De functie SEQUENCE retourneert een reeks van 4 waarden die beginnen met 3 met interval 1. Het retourneert de array {3;4;5;6}. De functie LARGE retourneert nu de grootste waarden van overeenkomstige getallen. Ten slotte somt de SOM-functie deze waarde op en krijgen we ons resultaat als 534040.
| =SOM(GROOT(C2:C13;VOLGORDE(4,,3)))
=SOM(GROOT(C2:C13,{3;4;5;6})) =SOM({212940;183820;98540;38740}) =534040 |
Dus ja jongens, zo kun je de top 3, 5, 10, … N-waarden in Excel samenvatten. Ik probeerde uitleg te geven. Ik hoop dat het je helpt. Als je twijfels hebt over dit onderwerp of een ander Excel VBA-gerelateerd onderwerp, vraag het dan in de comments hieronder. Ik beantwoord regelmatig vragen.
Som indien groter dan 0 in Excel | Om alleen positieve waarden op te tellen, moeten we waarden optellen die groter zijn dan 0. Om dit te doen, gebruiken we de SUMIF-functie van Excel.
Hoe de afgelopen 30 dagen verkopen in Excel optellen | Soms wordt het ook samengevat met een standaardinterval van 30 dagen. In dat geval is het een goed idee om een formule te hebben die de som van de afgelopen 30 dagen dynamisch weergeeft. In dit artikel leren we hoe u de waarden van de afgelopen 30 dagen in Excel kunt optellen.
Hoe de laatste n kolommen op te tellen in Excel | Soms een voorwaarde gegeven, bijvoorbeeld wanneer we de som van de laatste 5 of n getallen uit de kolom moeten halen. U kunt de oplossing voor dit probleem eenvoudig uitvoeren met behulp van de Excel-functies.
Hoe te optellen door rij en kolom te matchen in Excel | Hoe tellen we waarden op als we kolom en rij moeten matchen. In dit artikel zullen we leren hoe u de som van overeenkomende rijen en kolommen kunt maken.
Populaire artikels:
50 Excel-snelkoppelingen om uw productiviteit te verhogen | Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
Hoe de Excel VERT.ZOEKEN-functie te gebruiken| Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de Excel te gebruiken? AANTAL.ALS-functie| Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om een specifieke waarde te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? | Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.
Populaire artikels:
50 Excel-snelkoppelingen om uw productiviteit te verhogen | Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
Hoe de Excel VERT.ZOEKEN-functie te gebruiken| Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de Excel te gebruiken? AANTAL.ALS-functie| Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om een specifieke waarde te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? | Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.
