Som de kleinste of laagste N-waarden op op basis van criteria in Excel
In de vorige tutorial hebben we besproken: hoe de kleinste n-waarden in een gegevensbereik 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.
Som de kleinste of laagste N-waarden op op basis van criteria in Excel
Stel dat ik een reeks gegevens heb zoals hieronder afgebeeld, nu wil ik de laagste 3 bestellingen van het product Apple optellen.
Als u in Excel de onderste n-waarden in een bereik met criteria wilt optellen, kunt u een matrixformule maken met behulp van de functies SOM, SMALL en IF. De generieke syntaxis is:
Array formula, should press Ctrl + Shift + Enter keys together.
- range=criteria: het cellenbereik dat overeenkomt met de specifieke criteria;
- values: De lijst met de onderste n waarden die u wilt optellen;
- N: De N-de onderste waarde.
Om het bovenstaande probleem op te lossen, past u de onderstaande matrixformule toe in een lege cel:
En druk vervolgens op Ctrl + Shift + Enter sleutels samen om het juiste resultaat te krijgen, zoals hieronder getoond screenshot:
Verklaring van de formule:
=SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))
- IF(($A$2:$A$14=D2), $B$2:$B$14): Als het product in bereik A2:A14 gelijk is aan "Apple", zal het het relatieve nummer uit de bestellijst retourneren (B2:B14); Als het product niet "Apple" is, wordt FALSE weergegeven. U krijgt het resultaat als volgt: {800;FALSE;FALSE;FALSE;1000;230;FALSE;FALSE;1600;FALSE;900;FALSE;500}.
- SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}): Deze SMALL-functie negeert de FALSE-waarden en retourneert de onderste 3 waarden in de array, dus het resultaat is dit: {230,500,800}.
- SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))=SUM({230,500,800}): Ten slotte telt de SUM-functie de getallen in de array op om het resultaat te krijgen: 1530.
Tips: Behandel twee of meer voorwaarden:
Als u de onderste n-waarden moet optellen op basis van twee of meer criteria, hoeft u alleen maar een ander bereik en criteria toe te voegen door * teken binnen de ALS-functie als volgt:
Array formula, should press Ctrl + Shift + Enter keys together.
- Range1=criteria1: het eerste celbereik dat overeenkomt met de eerste criteria;
- Range2=criteria2: het tweede celbereik dat overeenkomt met het tweede criterium;
- Range3=criteria3: het derde celbereik dat overeenkomt met het derde criterium;
- values: De lijst met de onderste n waarden die u wilt optellen;
- N: De N-de onderste waarde.
Ik wil bijvoorbeeld de onderste 3 bestellingen van product Apple dat door Kerry wordt verkocht, optellen, pas de onderstaande formule toe:
En druk vervolgens op Ctrl + Shift + Enter sleutels samen om het resultaat te krijgen zoals je nodig hebt:
Gebruikte relatieve functie:
- SOM:
- De SOM-functie voegt waarden toe. U kunt individuele waarden, celverwijzingen of bereiken toevoegen of een combinatie van alle drie.
- SMALL:
- De Excel SMALL-functie retourneert een numerieke waarde op basis van de positie in een lijst wanneer gesorteerd op waarde in oplopende volgorde.
- IF:
- De ALS-functie test op een specifieke voorwaarde en retourneert de corresponderende waarde die u opgeeft voor WAAR of ONWAAR.
Meer artikelen:
- 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.
- Subtotaal factuurbedragen per leeftijd in Excel
- Om de factuurbedragen op basis van leeftijd op te tellen, zoals onderstaand screenshot, kan een veelvoorkomende taak in Excel zijn.
- Som alle nummercellen op en negeer fouten
- Bij het optellen van een reeks getallen die enkele foutwaarden bevatten, zal de normale SOM-functie niet correct werken. Om alleen getallen op te tellen en de foutwaarden over te slaan, kan de AGGREGATE-functie of de SOM samen met de IFERROR-functies u een plezier doen.
De beste tools voor kantoorproductiviteit
Kutools for Excel - Helpt u zich te onderscheiden van de menigte
Wilt u uw dagelijkse werkzaamheden snel en perfect afronden? Kutools voor Excel biedt 300 krachtige geavanceerde functies (werkmappen combineren, optellen op kleur, celinhoud splitsen, datum converteren, enzovoort ...) en 80% tijd voor u besparen.
- Ontworpen voor 1500 werkscenario's, helpt u 80% Excel-problemen op te lossen.
- Verminder elke dag duizenden toetsenbord- en muisklikken, verlicht uw vermoeide ogen en handen.
- Word een Excel-expert in 3 minuten. U hoeft geen pijnlijke formules en VBA-codes meer te onthouden.
- 30 dagen onbeperkte gratis proefperiode. 60 dagen geld-terug-garantie. Gratis upgrade en ondersteuning gedurende 2 jaar.
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, Firefox en New Internet Explorer.
