Begin met het gebruik van AANTAL.ALS, SOM.ALS en GEMIDDELDE.ALS in Excel

Bij het leren van Microsoft Excel draait het erom steeds meer formules en functies toe te voegen aan je gereedschapsriem. Combineer genoeg van deze, en je kunt praktisch alles doen met een spreadsheet.

In deze zelfstudie leert u hoe u drie krachtige Excel-formules gebruikt: SUMIF, AANTAL.ALS, en AVERAGEIF.

Hier hebben we een lijst met transacties voor de maand met enkele gegevens over uitgaven.

In de bovenstaande schermafbeelding kunt u zien dat we een lijst met transacties aan de linkerkant hebben. Als ik mijn bestedingen in de gaten wil houden, kan ik deze drie formules gebruiken om het te controleren. 

Aan de rechterkant, de Uit eten box gebruikt de drie formules om me te helpen mijn uitgaven bij te houden:

• COUNTIF - Gebruikt om het aantal keren te tellen dat "Restaurant" in de lijst verschijnt.
• SUMIF - Berekent de totale uitgaven voor items met het label 'Restaurant'.
  
• AVERAGIEF - Middelen uit al mijn uitgaven voor 'Restaurant' in de lijst.
  

Generieker is dit wat elk van deze formules voor u doet en hoe u ze in uw voordeel kunt gebruiken:

• SUMIF - Voeg waarden toe als aan een voorwaarde is voldaan, zoals het optellen van alle aankopen uit één categorie.
• COUNTIF - Tel het aantal items op dat aan een voorwaarde voldoet, zoals het tellen van het aantal keren dat een naam in een lijst wordt weergegeven.
• AVERAGEIF - Voorwaardelijke gemiddelde waarden; Je kunt bijvoorbeeld je cijfers voor alleen examens gemiddelde.

Met deze formules kunt u logica aan uw spreadsheet toevoegen. Laten we eens kijken naar hoe elke formule te gebruiken.

AANTAL.ALS, SOM.ALS en GEMIDDELDE.ALS in Excel (Quick Video Tutorial)

Screencasts zijn een van de beste manieren om een ​​nieuwe vaardigheid te bekijken en te leren, inclusief aan de slag te gaan met deze drie hoofdformules in Microsoft Excel. Bekijk de video hieronder om te zien hoe ik in Excel werk. Zorg ervoor dat u de gratis voorbeeldwerkmap downloadt om deze tutorial te gebruiken.

 

Als u liever leert met schriftelijke, stapsgewijze instructies, blijf dan lezen. Ik zal tips geven voor het gebruik van deze formules en ideeën voor de reden waarom ze nuttig zijn.

SUMIF gebruiken in Excel

Gebruik de tab SUMIF in de gratis voorbeeldwerkmap voor dit gedeelte van de zelfstudie.

Denk aan SUMIF als een manier om waarden toe te voegen die aan een regel voldoen. We kunnen een lijst met waarden uit een bepaalde categorie optellen of alle waarden groter of kleiner dan een bepaald aantal.

Hier is hoe de SUMIF-formule werkt:

= SOM.ALS (cellen die moeten worden gecontroleerd, waarop moet worden gecontroleerd, som van cellen die aan de regels voldoen)

Laten we teruggaan naar het voorbeeld van mijn restaurantkost om de SUMIF-formule te leren. Hieronder laat ik een lijst met mijn transacties voor de maand zien.

Ik heb een lijst met transacties en ik ga SUMIF gebruiken om mijn uitgaven in de gaten te houden.

Ik wil twee dingen weten:

1. Het totaal van wat ik in de maand aan restaurants heb uitgegeven.
2. Alle aankopen van meer dan $ 50 voor de maand, uit elke categorie.

In plaats van handmatig gegevens toe te voegen, kunnen we er twee schrijven SUMIF formules om het proces te automatiseren. Ik zal de resultaten in het groen zetten Restaurant Onkosten vak aan de rechterkant. Laten we kijken naar hoe.

Total Restaurant Expense

Om mijn totale restaurantkosten te vinden, som ik alle waarden op met het onkostype 'Restaurant', dat zich in kolom B bevindt.

Dit is de formule die ik voor dit voorbeeld zal gebruiken:

= SOM.ALS (B2: B17, "restaurant", C2: C17)

Merk op dat elke sectie wordt gescheiden door een komma. Deze formule heeft drie belangrijke dingen:

• Kijkt naar wat er in de cellen B2 tot en met B17 voor de categorie kosten staat
• Gebruikt "Restaurant" voor de criteria om samen te vatten
• Gebruikt de waarden in cellen C2 tot en met C17 om de bedragen op te tellen

In dit voorbeeld som ik alle waarden op van het onkostentype 'Restaurant'.

Wanneer ik op Enter druk, berekent Excel het totaal van mijn restaurantuitgaven. Met SUMIF is het eenvoudig om deze snelle statistieken te maken om u te helpen bij het monitoren van gegevens van bepaalde typen.

Aankopen groter dan $ 50

We hebben voor een specifieke categorie gecontroleerd, maar laten we nu alle waarden samenvatten die dat zijn groter dan een bedrag uit elke categorie. In dit geval wil ik alle aankopen vinden die meer dan $ 50 waren.

Laten we een eenvoudige formule schrijven om de som te vinden van alle aankopen van meer dan $ 50:

= SOM.ALS (C2: C17, "> 50")

In dit geval is de formule een beetje eenvoudiger: aangezien we dezelfde waarden die we testen (C2 tot en met C17) samenvatten, hoeven we alleen die cellen op te geven. Vervolgens voegen we een komma en "> 50" toe aan alleen somwaarden van meer dan $ 50.

Verzamel alle aankopen van meer dan $ 50 met een eenvoudige Excel-formule.

In dit voorbeeld wordt een groter dan teken gebruikt, maar voor bonuspunten: probeer alle kleine aankopen samen te vatten, zoals alle aankopen van $ 20 of minder.

AANTAL.ALS gebruiken in Excel

Gebruik de tab met de naam AANTAL.ALS in de gratis voorbeeldwerkmap voor dit gedeelte van de zelfstudie.

Terwijl SUMIF wordt gebruikt om waarden toe te voegen die aan een bepaalde voorwaarde voldoen, COUNTIF zal het aantal keren tellen dat iets in een gegeven reeks gegevens verschijnt.

Hier is het algemene formaat voor de COUNTIF formule:

= AANTAL.ALS (cellen om te tellen, criteria om te tellen)

Laten we met behulp van dezelfde set gegevens twee belangrijke stukjes informatie tellen:

• Het aantal kledingaankopen dat ik in een maand heb gemaakt
• Het aantal aankopen $ 100 of meer

Aantal kledingaankopen

Mijn eerste AANTAL.ALS zal naar het onkostentype kijken en het aantal "Kleding" -aankopen in mijn transacties optellen.

De uiteindelijke formule zal zijn:

= AANTAL.ALS (B2: B17, "kleding")

Die formule kijkt naar de kolom "Uitgavenoort", telt het aantal keren dat hij kleding ziet op en telt deze. Het resultaat is 2.

De AANTAL.ALS-formule telt het aantal uitgaven gemarkeerd als "Kleding" en telt deze.

Aantal $ 100 + aankopen

Laten we nu het aantal transacties tellen dat $ 100 of meer in mijn lijst was. 

Dit is de formule die ik gebruik:

= AANTAL.ALS (C2: C17 "> 100")

Dit is een eenvoudige tweedelige formule: wijs eenvoudigweg Excel naar de lijst met te tellen gegevens en de regel om te tellen. In dit geval controleren we cellen C2 tot C17, voor alle waarden die groter zijn dan $ 100. 

Tel het aantal transacties $ 100 + met deze Excel AANTAL.ALS-formule.

Hoe gebruik je AVERAGEIF in Excel

Gebruik de tab AVERAGEIF in de gratis voorbeeldwerkmap voor dit gedeelte van de zelfstudie.

Laten we tot slot kijken hoe we een kunnen gebruiken AVERAGEIF formule. Inmiddels zou het geen verrassing moeten zijn dat AVERAGEIF kan worden gebruikt voor het gemiddelde van specifieke waarden, op basis van een voorwaarde die we Excel zullen geven.

Het formaat voor een AVERAGEIF-formule is:

= GEMIDDELDEIF (Cellen om te controleren, wat te controleren, Gemiddeld aantal cellen dat aan de regels voldoet)

Het formaat van de AVERAGEIF-formule lijkt het meest op de SUMIF-formule.

Laten we de formule AVERAGEIF gebruiken om twee belangrijke statistieken over mijn uitgaven te berekenen:

1. Het gemiddelde van mijn restaurantuitgaven.
2. Het gemiddelde van alle uitgaven van minder dan $ 25.

Gemiddelde van restaurantaankopen

Om mijn restaurantuitgaven te berekenen, Ik schrijf een AVERAGEIF-formule om alle bedragen te berekenen op basis van de categorie.

= GEMIDDELDE.ALS (B2: B17, "restaurant", C2: C17)

Deze formule bestaat uit drie delen, elk gescheiden door een komma:

• B2: B17 geeft de cellen aan om een ​​voorwaarde voor te controleren. Aangezien het onkostende type is gespecificeerd in deze kolom.
• "Restaurant" geeft de formule iets om naar te zoeken.
• Ten slotte zijn C2: C17 de cellen om te berekenen in onze berekening.

Hier gebruik ik een AVERAGEIF om de kosten van mijn jaarlijkse restaurantuitstap te berekenen.

Uiteindelijk haalt Excel de kosten van mijn restaurantuitstapjes uit. Ik heb het die formule gegeven.

U kunt deze formule ook uitproberen door 'Restaurant' te vervangen door een andere categorie, zoals 'Kleding'.

Gemiddelde van uitgaven minder dan $ 25

Als ik mijn kleinere aankopen in de gaten houd en mijn gemiddelde wil weten, kan ik een AVERAGEIF schrijven voor alle aankopen van minder dan een bedrag.

Hier is de formule die ik zal gebruiken om dat te doen:

= GEMIDDELDE.ALS (C2: C17,"<25")

Deze eenvoudige formule controleert gewoon de waarden in kolom C en middelt alle waarden groter dan $ 25.

Eenvoudige Excel AVERAGEIF-formule voor uitgaven van minder dan $ 25.

Samenvatten en blijven leren

In deze zelfstudie hebt u geleerd om drie voorwaardelijke wiskundige formules te gebruiken om uw gegevens te bekijken. Of u nu bezig bent met optellen, tellen of middelen, deze functies zijn geavanceerde Excel-vaardigheden die u goed kunt gebruiken.

Voor alle "ALS" -formules in deze zelfstudie is de belangrijkste afhaalmogelijkheid dat u voorwaarden kunt toepassen op uw berekeningen in Excel.

Leren leidt tot leren. Hier zijn nog drie Excel-zelfstudies om te blijven groeien:

• Naast AANTAL.ALS, SOM.ALS en GEMIDDELDE.ALS, zijn er ook algemene "ALS" -verklaringen die voor andere omstandigheden kunnen worden gebruikt. Bekijk onze tutorial over Eenvoudige IF-verklaringen gebruiken.
• Leer hoe u Excel-datums en -tijden in combinatie met deze formules kunt gebruiken om te werken met waarden op basis van datum.
• De Excel VLOOKUP-functie kan worden gebruikt om waarden uit meerdere lijsten met elkaar te matchen. Meer informatie over het gebruik van de Excel VLOOKUP-functie in deze zelfstudie.

Kun je andere manieren bedenken om deze formules te gebruiken om logica en voorwaarden toe te voegen aan je spreadsheet? Laat het me weten met een reactie hieronder.