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:

Meer artikelen: