Ga naar hoofdinhoud

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:

{=SUM(SMALL(IF(range=criteria,values),{1,2,N}))}
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:

=SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))

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:

{=SUM(SMALL(IF((range1=criteria1)*(range2=criteria2) *(range3=criteria3)…,values),{1,2,N}))}
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:

=SUM(SMALL(IF(($A$2:$A$14=E2)*($B$2:$B$14=F2), $C$2:$C$14),{1,2,3}))

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.
  • 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

Populaire functies: Zoek, markeer of identificeer duplicaten  |  Verwijder lege rijen  |  Combineer kolommen of cellen zonder gegevens te verliezen  |  Ronde zonder formule ...
Super VLookup: Meerdere criteria  |  Meerdere waarde  |  Over meerdere vellen  |  Fuzzy opzoeken...
Gev. Keuzelijst: Gemakkelijke vervolgkeuzelijst  |  Afhankelijke vervolgkeuzelijst  |  Multi-select vervolgkeuzelijst...
Kolom Beheerder: Voeg een specifiek aantal kolommen toe  |  Kolommen verplaatsen  |  Schakel de zichtbaarheidsstatus van verborgen kolommen in  Vergelijk Kolommen met Selecteer dezelfde en verschillende cellen ...
Uitgelichte functies: Raster focus  |  Ontwerpweergave  |  Grote formulebalk  |  Werkmap- en bladbeheer | resource Library (Auto-tekst)  |  Datumkiezer  |  Combineer werkbladen  |  Cellen coderen/decoderen  |  Stuur e-mails per lijst  |  Super filter  |  Speciaal filter (filter vet/cursief/doorhalen...) ...
Top 15 gereedschapsets12 Tekst Tools (toe te voegen tekst, Tekens verwijderen ...)  |  50+ tabel Types (Gantt Chart ...)  |  40+ Praktisch Formules (Bereken leeftijd op basis van verjaardag ...)  |  19 Invoeging Tools (QR-code invoegen, Afbeelding invoegen vanaf pad ...)  |  12 Camper ombouw Tools (Getallen naar woorden, Currency Conversion ...)  |  7 Samenvoegen en splitsen Tools (Geavanceerd Combineer rijen, Excel-cellen splitsen ...)  |  ... en meer

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.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations