Excel: bereken voorwaardelijke bedragen correct
In Excel kunt u bedragen berekenen die geen rekening houden met alle elementen van het geselecteerde gebied - zogenaamde voorwaardelijke bedragen. We laten u zien hoe u de meest verstandige berekening kunt maken.
Drie verschillende functies voor voorwaardelijke bedragen in Excel
Excel biedt verschillende opties en functies voor voorwaardelijke totalen. De juiste keuze is afhankelijk van het type voorwaarden:
- Als voor elke waarde een vaste voorwaarde van toepassing is, gebruikt u de functie SOMMEN.
- Als met meerdere voorwaarden rekening moet worden gehouden, is er ook de functie SOMMEN vanaf Excel 2007.
- Bij variabele voorwaarden hebt u echter een matrixformule of de functie SOMPRODUCT nodig.
- In de volgende paragrafen lichten we de drie functies toe aan de hand van voorbeelden.
SUMIF: voorwaardelijke bedragen met vaste voorwaarden
- Neem aan dat de gegevens zich in cellen A1 tot A10 en verschillende waarden in cellen B1 tot C10 bevinden die voor voorwaarden worden gebruikt.
- De eerste taak met een vaste voorwaarde betekent dan bijvoorbeeld het optellen van alle waarden in kolom A waarvoor de respectieve waarde in kolom B ongeveer groter is dan 10.
- Gebruik hiervoor de formule »= SUMIF (B1: B10;"> 10 "; A1: A10)«.
- Het eerste argument »B1: B10« definieert het te evalueren gebied, gevolgd door de voorwaarde »"> 10 "«. Merk op dat voorwaarden met symbolen of operatoren tussen aanhalingstekens moeten staan.
- Ten slotte definieert »A1: A10« het gebied dat moet worden opgeteld.
- De bereiken voor voorwaarden en datums kunnen ook identiek zijn, zodat de formule »= SOM (A1: A10;"> 10 ")« voldoende is.
SOM ALS: Voorwaardelijke bedragen met meerdere voorwaarden
Vanaf Excel 2007 breidt de SUMIFS-functie dit principe uit tot verschillende tot maximaal 127 voorwaarden:
- Let op de andere volgorde van de argumenten. Het gebied dat moet worden opgeteld, is hier eerst. Dit wordt gevolgd door een criteriumgebied en de bijbehorende voorwaarde, waarbij de gebieden altijd afzonderlijk moeten worden opgegeven.
- De formule "= SOMMEN (A1: A10; B1: B10;"> 10 "; C1: C10;" <100 "), vat bijvoorbeeld alle waarden in kolom A samen waarvoor de overeenkomstige waarden in kolom B groter zijn dan 10 en de overeenkomstige waarden in kolom C zijn minder dan 100.
SOM PRODUCT: voorwaardelijke som met variabele voorwaarden
Variabele criteria zoals "als de waarde in kolom B groter is dan die in kolom C" vereisen een matrixformule voor evaluatie:
- De in aanmerking te nemen waarden worden geselecteerd door te vermenigvuldigen met een logische waarde.
- Voer hiertoe ongeveer "= SOM (A1: A10 * (B1: B10> C1: C10))" in en sluit het item altijd, zelfs na een wijziging met de toetsencombinatie [Ctrl] + [Shift] + [Enter], zodat de formule tussen accolades staat.
- In dit geval kunt u hetzelfde resultaat bereiken met de functie "= SOM PRODUCT (A1: A10; 1 * (B1: B10> C1: C10))".
- Merk echter op dat u eerst het tweede argument met 1 moet vermenigvuldigen om de logische waarde van de vergelijking in een getal om te zetten.
Opmerking: in vrijwel alle gevallen kunt u het totaal duidelijk maken met een hulpkolom. Dit is ook aan te raden tijdens de ontwikkeling van een berekening om de resultaten van de complexe formules te controleren. Tip: we zullen hier uitleggen hoe u veel cellen in Excel kunt toevoegen.