Somwaarden per groep in Excel
Soms moet u mogelijk waarden optellen op basis van groep in een tabel. Ik heb bijvoorbeeld een lijst met producten met de bijbehorende bedragen in een andere kolom, nu wil ik het subtotaal voor elk product krijgen, zoals hieronder wordt weergegeven. Deze zelfstudie introduceert enkele formules voor het oplossen van deze taak in Excel.
- Somwaarden per groep - subtotalen in originele gegevenstabel
- Somwaarden per groep – subtotalen op een andere locatie
Somwaarden per groep - subtotalen in originele gegevenstabel
Hier zou de generieke syntaxis om waarden op basis van groep in Excel op te tellen als volgt moeten zijn:
- group_name: De cel met de groepsnaam die u wilt optellen;
- cell_above_group_name: De cel boven de groepsnaam;
- group_range: Het cellenbereik bevat de groepsnamen;
- sum_range: Het cellenbereik dat overeenkomt met de opgegeven groepsnaam om bij elkaar op te tellen.
Als de gegevens zijn gesorteerd op de groeperingskolom, wordt hetzelfde product samen gerangschikt zoals hieronder afgebeeld. Als u gegevens per groep wilt subtotalen, kunt u formules maken op basis van de ALS- en SUMIF-functies.
1. Kopieer en plak de volgende formule in een lege cel naast uw gegevens:
- Note: In de formule:
- A1 is de kopcel, en A2 is de eerste cel die de productnaam bevat die u wilt gebruiken;
- A2: A13 bevat de lijst de productnamen waarop u wilt optellen;
- B2: B13 is de kolomgegevens waarvan u het subtotaal wilt krijgen.
2. En sleep vervolgens de vulgreep naar de cellen die u deze formule wilt gebruiken, en de subtotalen worden berekend op basis van elke productnaam, zie screenshot:
Verklaring van de formule:
=IF(A2=A1,"",SUMIF($A$2:$A$13,A2,$B$2:$B$13))
- SUMIF($A$2:$A$13,A2,$B$2:$B$13): Deze SUMIF-functie zal alleen de waarden in bereik B2:B13 optellen als hun corresponderende waarden in bereik A2:A13 gelijk zijn aan de criteria A2.
- IF(A2=A1,"",SUMIF($A$2:$A$13,A2,$B$2:$B$13)): Deze ALS-functie controleert elke waarde in kolom A of de waarde gelijk is aan de waarde in de bovenstaande cel. Als de celwaarde A2 bijvoorbeeld gelijk is aan cel A1, wordt er niets ("" ) geretourneerd, als het niet overeenkomt, wordt het resultaat van de SUMIF-functie geretourneerd.
Somwaarden per groep – subtotalen op een andere locatie
Als de waarden van elke groep niet bij elkaar zijn gerangschikt en willekeurig in de kolom worden vermeld, om de overeenkomende waarden op basis van groep of categorie op te tellen, moet u elke groepsnaam uit de productnamen halen en vervolgens het subtotaal krijgen op basis van deze groepsnaam als onderstaande screenshot getoond.
1. Extraheer eerst de unieke groepsnaam met behulp van de volgende matrixformule en druk vervolgens op Ctrl + Shift + Enter toetsen samen om het eerste resultaat te krijgen.
- Note: In de formule:
- A2: A13 is het cellenbereik waar alle unieke waarden moeten worden geëxtraheerd;
- D1 is de cel boven uw ingevoerde formule.
2. En selecteer vervolgens de formulecel en sleep de vulgreep naar de cellen totdat alle productnamen worden weergegeven, zie screenshot:
3. Nu kunt u de waarden optellen op basis van de groepsnaam die u hebt geëxtraheerd. In dit geval wordt hier een SUMIF-functie gebruikt. Voer de onderstaande formule in een lege cel in, in dit voorbeeld zal ik deze in cel E2 plaatsen.
4. Sleep vervolgens de vulgreep naar beneden om deze formule te kopiëren om de totale volgorde van andere groepen te retourneren, zie screenshot:
Gebruikte relatieve functie:
- SUMIF:
- De SUMIF-functie kan helpen om cellen op te tellen op basis van één criterium.
- IF:
- De ALS-functie test op een specifieke voorwaarde en retourneert de corresponderende waarde die u opgeeft voor WAAR of ONWAAR.
Meer artikelen:
- Subtotaal factuurbedragen per leeftijd
- Om de factuurbedragen op basis van leeftijd op te tellen, zoals onderstaand screenshot, kan een veelvoorkomende taak in Excel zijn.
- Som kleinste of laagste N-waarden
- In Excel kunnen we gemakkelijk een celbereik optellen met behulp van de SOM-functie. Soms moet u mogelijk de kleinste of onderste 3, 5 of n getallen in een gegevensbereik optellen, zoals hieronder wordt weergegeven. In dit geval kan de SOMPRODUCT samen met de SMALL-functie u helpen dit probleem in Excel op te lossen.
- Som kleinste of laagste N-waarden op basis van criteria
- In de vorige zelfstudie hebben we besproken hoe u de kleinste n-waarden in een gegevensbereik kunt optellen. In dit artikel zullen we een verdere geavanceerde bewerking uitvoeren - om de laagste n-waarden op te tellen op basis van een of meer criteria in Excel.
De beste tools voor kantoorproductiviteit
Kutools for Excel - Helpt u zich te onderscheiden van de menigte
Kutools voor Excel beschikt over meer dan 300 functies, Ervoor zorgen dat wat u nodig heeft slechts één klik verwijderd is...
Office-tabblad - Schakel lezen en bewerken met tabbladen in Microsoft Office in (inclusief Excel)
- Een seconde om te schakelen tussen tientallen geopende documenten!
- Verminder elke dag honderden muisklikken voor u, zeg maar dag tegen muishand.
- Verhoogt uw productiviteit met 50% bij het bekijken en bewerken van meerdere documenten.
- Brengt efficiënte tabbladen naar Office (inclusief Excel), net als Chrome, Edge en Firefox.
