Formules en functies zijn de bouwstenen voor het werken met numerieke gegevens in Excel. In dit artikel maakt u kennis met formules en functies.
In dit artikel behandelen we de volgende onderwerpen.
- Wat zijn formules in Excel?
- Te vermijden fouten bij het werken met formules in Excel
- Wat is functie in Excel?
- Het belang van functies
- Gemeenschappelijke functies
- Numerieke functies
- String-functies
- Datum / tijd-functies
- V Lookup-functie
Tutorials-gegevens
Voor deze tutorial werken we met de volgende datasets.
Home levert budget
| S / N | ITEM | AANTAL | PRIJS | SUBTOTAAL | Is het betaalbaar? |
|---|---|---|---|---|---|
| 1 | Mango's | 9 | 600 | ||
| 2 | Sinaasappels | 3 | 1200 | ||
| 3 | Tomaten | 1 | 2500 | ||
| 4 | Kokende olie | 5 | 6500 | ||
| 5 | Tonic Water | 13 | 3900 |
Schema woningbouwproject
| S / N | ITEM | BEGIN DATUM | EINDDATUM | DUUR (DAGEN) |
|---|---|---|---|---|
| 1 | Onderzoek land | 04/02/2015 | 07/02/2015 | |
| 2 | Leg de fundering | 10/02/2015 | 15/02/2015 | |
| 3 | Dakbedekking | 27/02/2015 | 03/03/2015 | |
| 4 | Schilderen | 09/03/2015 | 21/03/2015 |
Wat zijn formules in Excel?
FORMULES IN EXCEL is een uitdrukking die werkt op waarden in een reeks celadressen en operatoren. Bijvoorbeeld = A1 + A2 + A3, waarmee de som wordt gevonden van het waardenbereik van cel A1 tot cel A3. Een voorbeeld van een formule die bestaat uit discrete waarden zoals = 6 * 3.
=A2 * D2 / 2
HIER,
vertelt Excel dat dit een formule is en dat het deze moet evalueren."A2" * D2"verwijst naar celadressen A2 en D2 en vermenigvuldigt vervolgens de waarden die in deze celadressen zijn gevonden."/"is de rekenkundige operator voor delen"2"is een discrete waarde
Formules praktische oefening
We werken met de voorbeeldgegevens voor het thuisbudget om het subtotaal te berekenen.
- Maak een nieuwe werkmap in Excel
- Voer de gegevens in die hierboven in het budget voor woonartikelen staan.
- Uw werkblad zou er als volgt uit moeten zien.
We gaan 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 het celadres C4 en D4 te vermenigvuldigen.
Druk op de invoertoets
U krijgt het volgende resultaat
De volgende geanimeerde afbeelding laat zien hoe u het celadres automatisch selecteert en dezelfde formule toepast op andere rijen.
Te vermijden fouten bij het werken met formules in Excel
- Onthoud de regels van Brackets of Division, Multiplication, Addition, & Subtraction (BODMAS). Dit betekent dat uitdrukkingen die haakjes zijn, eerst worden geëvalueerd. Voor rekenkundige operatoren wordt de deling eerst geëvalueerd, gevolgd door vermenigvuldiging en vervolgens is optellen en aftrekken de laatste die moet worden geëvalueerd. 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 profiteren van gegevensvalidatie om het type gegevens op te geven dat door een cel moet worden geaccepteerd, dwz alleen cijfers.
- Om ervoor te zorgen 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 een kruis controleren om er zeker van te zijn dat dit de gewenste celadressen zijn.
- Als u met veel rijen werkt, kunt u serienummers gebruiken voor alle rijen en een recordtelling onderaan het blad hebben. U moet het aantal serienummers vergelijken met het recordtotaal om ervoor te zorgen dat uw formules alle rijen bevatten.
Bekijk 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 functie SOM 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
- AANTAL voor het tellen van het aantal items in een bepaald bereik
Het belang van functies
Functies verhogen de productiviteit van de gebruiker bij het werken met Excel . Stel dat u het totaalbedrag van het bovenstaande budget voor huishoudelijke artikelen wilt ontvangen. Om het eenvoudiger te maken, kunt u een formule gebruiken om het eindtotaal te krijgen. Als u een formule gebruikt, moet u een voor een naar de cellen E4 tot en met E8 verwijzen. U zou de volgende formule moeten 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 krijgen, is het veel efficiënter om een functie te gebruiken om de som te krijgen dan de formule te gebruiken die naar veel cellen moet verwijzen.
Gemeenschappelijke 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 | OMSCHRIJVING | GEBRUIK |
|---|---|---|---|---|
| 01 | SOM | Math & Trig | Telt alle waarden in een celbereik op | = SOM (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 | AANTAL | Statistisch | Telt het aantal cellen in een celbereik | = AANTAL (E4: E8) |
| 06 | LEN | Tekst | Retourneert het aantal tekens in een tekenreeks | = LEN (B7) |
| 07 | SUMIF | Math & Trig | Voegt alle waarden toe in een celbereik die voldoen aan een opgegeven criterium. = SOM.ALS (bereik, criteria, [optelbereik]) | = SUMIF (D4: D8, "> = 1000", C4: C8) |
| 08 | GEMIDDELDE.ALS | Statistisch | Berekent de gemiddelde waarde in een celbereik dat aan de opgegeven criteria voldoet. = GEMIDDELDE.ALS (bereik, criteria, [gemiddeld_bereik]) | = GEMIDDELDE.ALS (F4: F8, "Ja", E4: E8) |
| 09 | DAGEN | Datum Tijd | Retourneert het aantal dagen tussen twee datums | = DAGEN (D4, C4) |
| 10 | NU | Datum Tijd | Retourneert de huidige systeemdatum en -tijd | = NU () |
Numerieke functies
Zoals de naam suggereert, werken deze functies op numerieke gegevens. De volgende tabel toont enkele van de algemene numerieke functies.
| S / N | FUNCTIE | CATEGORIE | OMSCHRIJVING | GEBRUIK |
|---|---|---|---|---|
| 1 | ISNUMMER | Informatie | Geeft True terug 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 | RONDE | 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 reeks opgegeven getallen | = MEDIAAN (3,4,5,2,5) |
| 5 | PI | Math & Trig | Geeft als resultaat de waarde van wiskundige functie PI (π) | = PI () |
| 6 | VERMOGEN | Math & Trig | Retourneert het resultaat van een getal verheven tot een macht. POWER (getal, vermogen) | = VERMOGEN (2,4) |
| 7 | MOD | Math & Trig | Retourneert de rest als u twee getallen deelt | = MOD (10,3) |
| 8 | ROMEINS | Math & Trig | Converteert een getal naar Romeinse cijfers | = ROMAN (1984) |
String-functies
Deze basis Excel-functies worden gebruikt om tekstgegevens te manipuleren. De volgende tabel toont enkele veelvoorkomende tekenreeksfuncties.
| S / N | FUNCTIE | CATEGORIE | OMSCHRIJVING | GEBRUIK | COMMENTAAR |
|---|---|---|---|---|---|
| 1 | LINKS | Tekst | Retourneert een aantal opgegeven tekens vanaf het begin (linkerkant) van een tekenreeks | = LEFT ("GURU99", 4) | Links 4 tekens van ‘GURU99’ |
| 2 | RECHTSAF | Tekst | Retourneert een aantal opgegeven tekens vanaf het einde (rechterkant) van een tekenreeks | = RECHTS ("GURU99"; 2) | Rechts twee 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, begin_getal, aantal_tekens) | = MID ("GURU99"; 2,3) | Tekens 2 tot 5 ophalen |
| 4 | ISTEKST | Informatie | Geeft True terug als de opgegeven parameter Text is | = ISTEXT (waarde) | waarde: de waarde die moet worden gecontroleerd. |
| 5 | VIND | Tekst | Retourneert de startpositie van een tekstreeks binnen een andere tekstreeks. Deze functie is hoofdlettergevoelig. = FIND (zoek_tekst, binnen_tekst, [start_getal]) | = FIND ("oo", "Roofing", 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) | = REPLACE ("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 algemene datumfuncties
| S / N | FUNCTIE | CATEGORIE | OMSCHRIJVING | GEBRUIK |
|---|---|---|---|---|
| 1 | DATUM | Datum Tijd | Retourneert het getal dat de datum in Excel-code vertegenwoordigt | = DATUM (2015,2,4) |
| 2 | DAGEN | Datum Tijd | Zoek het aantal dagen tussen twee datums | = DAGEN (D6, C6) |
| 3 | MAAND | Datum Tijd | Retourneert de maand vanaf een datumwaarde | = MONTH ("2-4-2015") |
| 4 | MINUUT | Datum Tijd | Retourneert de minuten van een tijdwaarde | = MINUUT ("12:31") |
| 5 | JAAR | Datum Tijd | Retourneert het jaar vanaf een datumwaarde | = JAAR ("04/02/2015") |
VERT.ZOEKEN functie
De functie VERT.ZOEKEN wordt gebruikt om een verticale zoekactie uit te voeren in de meest linkse kolom en een waarde in dezelfde rij te retourneren vanuit een kolom die u opgeeft. Laten we dit in de taal van een leek uitleggen. Het budget voor huishoudelijke benodigdheden heeft een serienummerkolom die elk item in het budget uniek identificeert. Stel dat u het artikelserienummer heeft en u wilt de artikelomschrijving weten, dan kunt u de functie VERT.ZOEKEN gebruiken. Hier is 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 tabelmatrix 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 laat dit in actie zien
Download de bovenstaande Excel-code
Overzicht
Met Excel kunt u de gegevens manipuleren met behulp van formules en / of functies. Functies zijn over het algemeen productiever dan het schrijven van formules. Functies zijn ook nauwkeuriger in vergelijking met formules omdat de foutmarge minimaal is.
Hier is een lijst met belangrijke Excel-formule en -functie
- SOM-functie =
=SUM(E4:E8)
- MIN-functie =
=MIN(E4:E8)
- MAX-functie =
=MAX(E4:E8)
- GEMIDDELDE, functie =
=AVERAGE(E4:E8)
- AANTAL functie =
=COUNT(E4:E8)
- DAGEN, functie =
=DAYS(D4,C4)
- VERT.ZOEKEN, functie =
=VLOOKUP (C12, A4:B8, 2, FALSE)
- DATUM, functie =
=DATE(2020,2,4)