Excel-formules en -functies: leer met basisVOORBEELDEN
Charlotte Molenaar
Formules en functies zijn de bouwstenen voor het werken met numerieke gegevens in Excel. In dit artikel maak je kennis met formules en functies.
Tutorials Gegevens
Voor deze tutorial werken we met de volgende datasets.
Budget voor huishoudelijke artikelen
| S / N | ITEM | QTY | PRIJS | SUBTOTAAL | Is het betaalbaar? |
|---|---|---|---|---|---|
| 1 | mango's | 9 | 600 | ||
| 2 | Sinaasappels | 3 | 1200 | ||
| 3 | tomaten | 1 | 2500 | ||
| 4 | Kook olie | 5 | 6500 | ||
| 5 | Tonic water | 13 | 3900 |
Projectschema woningbouw
| S / N | ITEM | BEGIN DATUM | EINDDATUM | DUUR (DAGEN) |
|---|---|---|---|---|
| 1 | Onderzoek land | 04/02/2015 | 07/02/2015 | |
| 2 | Leggen Foundation | 10/02/2015 | 15/02/2015 | |
| 3 | Dakwerk | 27/02/2015 | 03/03/2015 | |
| 4 | Schilderwerk | 09/03/2015 | 21/03/2015 |
Wat zijn formules in Excel?
FORMULES IN EXCEL is een expressie die werkt op waarden in een bereik van celadressen en operatoren. Bijvoorbeeld, =A1+A2+A3, die de som vindt van het bereik van waarden van cel A1 tot cel A3. Een voorbeeld van een formule die is samengesteld uit discrete waarden zoals =6*3.
=A2 * D2 / 2
HIER,
"="vertelt Excel dat dit een formule is en dat deze moet worden geëvalueerd."A2" * D2"verwijst naar celadressen A2 en D2 en vermenigvuldigt vervolgens de waarden die in deze celadressen worden gevonden."/"is de rekenkundige operator voor deling"2"is een discrete waarde
Formules praktische oefening
Om het subtotaal te berekenen, gaan we aan de slag met de voorbeeldgegevens van het woonbudget.
- Maak een nieuwe werkmap in Excel
- Voer de gegevens in die hierboven in het budget voor woningbenodigdheden staan vermeld.
- Uw werkblad moet er als volgt uitzien.
We zullen nu de formule schrijven die het subtotaal berekent
Stel de focus in op cel E4
Voer de volgende formule in.
=C4*D4
HIER,
"C4*D4"gebruikt de rekenkundige operator vermenigvuldiging (*) om de waarde van de celadressen C4 en D4 te vermenigvuldigen.
Druk op de enter-toets
U krijgt het volgende resultaat
De onderstaande geanimeerde afbeelding laat zien hoe u automatisch het celadres kunt selecteren en dezelfde formule op andere rijen kunt toepassen.
Fouten die u moet vermijden bij het werken met formules in Excel
- Denk aan de regels van Brackets van delen, vermenigvuldigen, optellen en aftrekken (BODMAS). Dit betekent dat expressies tussen haakjes als eerste worden geëvalueerd. Voor rekenkundige operatoren wordt de deling als eerste geëvalueerd, gevolgd door vermenigvuldiging en optellen en aftrekken als laatste. Met behulp van deze regel kunnen we de bovenstaande formule herschrijven als =(A2 * D2) / 2. Dit zorgt ervoor dat A2 en D2 eerst worden geëvalueerd en vervolgens door twee worden gedeeld.
- Excel-spreadsheetformules werken meestal met numerieke gegevens. U kunt gebruikmaken van gegevensvalidatie om aan te geven welk type gegevens door een cel moet worden geaccepteerd, bijvoorbeeld alleen getallen.
- Om er zeker van te zijn dat u werkt met de juiste celadressen waarnaar in de formules wordt verwezen, kunt u op F2 op het toetsenbord drukken. Hierdoor worden de celadressen die in de formule worden gebruikt gemarkeerd en kunt u controleren of dit de gewenste celadressen zijn.
- Wanneer u met veel rijen werkt, kunt u serienummers voor alle rijen gebruiken en een recordtelling onderaan het werkblad hebben. U moet de serienummertelling vergelijken met het recordtotaal om ervoor te zorgen dat uw formules alle rijen bevatten.
Check Out
Top 10 Excel-spreadsheetformules
Wat is functie in Excel?
FUNCTIE IN EXCEL is een vooraf gedefinieerde formule die wordt gebruikt voor specifieke waarden in een bepaalde volgorde. De functie wordt gebruikt voor snelle taken zoals het vinden van de som, het aantal, het gemiddelde, de maximale waarde en de minimumwaarden voor een celbereik. Cel A3 hieronder bevat bijvoorbeeld de SOM-functie die de som van het bereik A1:A2 berekent.
- SOM voor het optellen van een reeks getallen
- GEMIDDELDE voor het berekenen van het gemiddelde van een gegeven reeks getallen
- COUNT voor het tellen van het aantal items in een bepaald bereik
Het belang van functies
Functies verhogen de gebruikersproductiviteit bij het werken met Excel. Stel dat u het totaalbedrag voor het bovenstaande huishoudbudget wilt weten. Om het eenvoudiger te maken, kunt u een formule gebruiken om het totaalbedrag te krijgen. Met behulp van een formule moet u de cellen E4 tot en met E8 één voor één raadplegen. U moet de volgende formule gebruiken.
= E4 + E5 + E6 + E7 + E8
Met een functie zou je de bovenstaande formule schrijven als
=SUM (E4:E8)
Zoals je kunt zien aan de hand van de bovenstaande functie die wordt gebruikt om de som van een celbereik te berekenen, is het veel efficiënter om een functie te gebruiken om de som te berekenen dan de formule te gebruiken die naar veel cellen moet verwijzen.
Algemene functies
Laten we eens kijken naar enkele van de meest gebruikte functies in MS Excel-formules. We beginnen met statistische functies.
| S / N | FUNCTIE | CATEGORIE | PRODUCTBESCHRIJVING | GEBRUIK |
|---|---|---|---|---|
| 01 | SOM | Math & Trig | Voegt alle waarden in een celbereik toe | = SUM (E4: E8) |
| 02 | MIN | Statistisch | Vindt de minimumwaarde in een celbereik | =MIN(E4:E8) |
| 03 | MAX | Statistisch | Vindt de maximale waarde in een celbereik | =MAX(E4:E8) |
| 04 | GEMIDDELDE | Statistisch | Berekent de gemiddelde waarde in een celbereik | = GEMIDDELDE (E4: E8) |
| 05 | COUNT | Statistisch | Telt het aantal cellen in een celbereik | = AANTAL (E4: E8) |
| 06 | LEN | Tekst | Retourneert het aantal tekens in een tekenreekstekst | = LEN (B7) |
| 07 | SUMIF | Math & Trig |
Voegt alle waarden toe in een celbereik die aan een bepaald criterium voldoen. =SUM.ALS(bereik,criteria,[som_bereik]) |
=SUMIF(D4:D8,”>=1000″,C4:C8) |
| 08 | AVERAGEIF | Statistisch |
Berekent de gemiddelde waarde in een celbereik dat aan de opgegeven criteria voldoet. = GEMIDDELDEIF (bereik, criteria, [gemiddelde_bereik]) |
=GEMIDDELDIF(F4:F8,”Ja”,E4:E8) |
| 09 | DAGEN | Datum en tijd | Retourneert het aantal dagen tussen twee datums | =DAGEN(D4,C4) |
| 10 | NOW | Datum en tijd | Retourneert de huidige systeemdatum en -tijd | = NU () |
Numerieke functies
Zoals de naam al doet vermoeden, werken deze functies op numerieke data. De volgende tabel toont enkele van de meest voorkomende numerieke functies.
| S / N | FUNCTIE | CATEGORIE | PRODUCTBESCHRIJVING | GEBRUIK |
|---|---|---|---|---|
| 1 | ISNUMMER | Info | Retourneert True als de opgegeven waarde numeriek is en False als deze niet numeriek is | =ISGETAL(A3) |
| 2 | RAND | Math & Trig | Genereert een willekeurig getal tussen 0 en 1 | = RAND () |
| 3 | ROUND | Math & Trig | Rondt een decimale waarde af op het opgegeven aantal decimalen | = RONDE (3.14455,2) |
| 4 | MEDIAAN | Statistisch | Retourneert het getal in het midden van de set van gegeven getallen | =MEDIAAN(3,4,5,2,5) |
| 5 | PI | Math & Trig | Geeft de waarde van de wiskundige functie PI(π) terug | =PI() |
| 6 | POWER | Math & Trig |
Retourneert het resultaat van een getal verheven tot een macht. VERMOGEN(getal, macht ) |
=VERMOGEN(2,4) |
| 7 | MOD | Math & Trig | Geeft de restwaarde terug wanneer u twee getallen deelt | =MODE(10,3) |
| 8 | ROMAN | Math & Trig | Converteert een getal naar Romeinse cijfers | =ROMEIN(1984) |
String-functies
Deze basisfuncties van Excel worden gebruikt om tekstgegevens te manipuleren. De volgende tabel toont enkele van de meest voorkomende tekenreeksfuncties.
| S / N | FUNCTIE | CATEGORIE | PRODUCTBESCHRIJVING | GEBRUIK | COMMENTAAR |
|---|---|---|---|---|---|
| 1 | LINKS | Tekst | Retourneert een aantal opgegeven tekens vanaf het begin (linkerkant) van een tekenreeks | =LINKS(“GURU99”,4) | Links 4 karakters van “GURU99” |
| 2 | RECHTS | Tekst | Retourneert een aantal opgegeven tekens vanaf het einde (rechterkant) van een tekenreeks | =RECHTS(“GURU99”,2) | Rechts 2 tekens van “GURU99” |
| 3 | MID | Tekst |
Haalt een aantal tekens op uit het midden van een string vanaf een opgegeven startpositie en lengte. =MID (tekst, start_getal, aantal_tekens) |
=MID(“GURU99”,2,3) | Karakters 2 t/m 5 ophalen |
| 4 | ISTEKST | Info | Retourneert True als de opgegeven parameter Tekst is | =ISTEXT(waarde) | waarde – De waarde die moet worden gecontroleerd. |
| 5 | VINDEN | Tekst |
Retourneert de startpositie van een teksttekenreeks binnen een andere teksttekenreeks. Deze functie is hoofdlettergevoelig. =ZOEKEN(vind_tekst, binnen_tekst, [start_getal]) |
=ZOEKEN(“oo”,,”Dakbedekking”,1) | Zoek oo in “Dakbedekking”, resultaat is 2 |
| 6 | VERVANGEN | Tekst |
Vervangt een deel van een string door een andere gespecificeerde string. =VERVANGEN (oude_tekst, start_getal, aantal_tekens, nieuwe_tekst) |
=VERVANG(“Dakbedekking”,2,2,”xx”) | Vervang “oo” door “xx” |
Datum Tijd Functies
Deze functies worden gebruikt om datumwaarden te manipuleren. De volgende tabel toont enkele van de meest voorkomende datumfuncties
| S / N | FUNCTIE | CATEGORIE | PRODUCTBESCHRIJVING | GEBRUIK |
|---|---|---|---|---|
| 1 | DATUM | Datum en tijd | Retourneert het getal dat de datum in Excel-code vertegenwoordigt | = DATUM (2015,2,4) |
| 2 | DAGEN | Datum en tijd | Zoek het aantal dagen tussen twee datums | =DAGEN(D6,C6) |
| 3 | MAAND | Datum en tijd | Retourneert de maand uit een datumwaarde | =MAAND(“4-2-2015”) |
| 4 | MINUUT | Datum en tijd | Retourneert de minuten van een tijdwaarde | =MINUUT(“12:31”) |
| 5 | JAAR | Datum en tijd | Retourneert het jaar uit een datumwaarde | =JAAR(“04/02/2015”) |
VERT.ZOEKEN functie
De VERT.ZOEKEN functie wordt gebruikt om verticaal omhoog te zoeken in de meest linkse kolom en een waarde in dezelfde rij te retourneren uit een kolom die u opgeeft. Laten we dit in lekentaal uitleggen. Het budget voor huishoudelijke artikelen heeft een kolom met serienummers die elk item in het budget op unieke wijze identificeert. Stel dat u het serienummer van het artikel heeft en u wilt de artikelbeschrijving weten, dan kunt u de functie VERT.ZOEKEN gebruiken. Hier ziet u hoe de functie VERT.ZOEKEN zou werken.
=VLOOKUP (C12, A4:B8, 2, FALSE)
HIER,
"=VLOOKUP"roept de verticale opzoekfunctie aan"C12"specificeert de waarde die moet worden opgezocht in de meest linkse kolom"A4:B8"specificeert de tabelarray met de gegevens"2"specificeert het kolomnummer met de rijwaarde die moet worden geretourneerd door de functie VERT.ZOEKEN"FALSE,"vertelt de functie VERT.ZOEKEN dat we op zoek zijn naar een exacte overeenkomst met de opgegeven opzoekwaarde
De geanimeerde afbeelding hieronder toont dit in actie
Download de bovenstaande Excel-code
Samenvatting
Met Excel kunt u de gegevens manipuleren met behulp van formules en/of functies. Functies zijn over het algemeen productiever vergeleken met het schrijven van formules. Functies zijn ook nauwkeuriger vergeleken met formules, omdat de marge om fouten te maken zeer minimaal is.
Hier is een lijst met belangrijke Excel-formules en -functies
- SOM-functie =
=SUM(E4:E8)
- MIN-functie =
=MIN(E4:E8)
- MAX-functie =
=MAX(E4:E8)
- GEMIDDELDE functie =
=AVERAGE(E4:E8)
- COUNT-functie =
=COUNT(E4:E8)
- DAGEN-functie =
=DAYS(D4,C4)
- VLOOKUP-functie =
=VLOOKUP (C12, A4:B8, 2, FALSE)
- DATUM-functie =
=DATE(2020,2,4)