Top 10 belangrijkste excel-functies
1. ALS-functie
De functie ALS retourneert een waarde als de voorwaarde die u opgeeft TRUE is, anders een andere waarde. Simpel gezegd kan de ALS-functie eerst een voorwaarde testen en een waarde retourneren op basis van het resultaat van die voorwaarde.
Syntaxis
ALS(logische_test, waarde_if_true, waarde_if_false)
Argumenten
- logische_test: de voorwaarde die u wilt evalueren.
- value_if_true: De waarde die u wilt verkrijgen als deze voorwaarde TRUE is.
- value_if_false: De waarde die u wilt verkrijgen als deze voorwaarde FALSE is.
Opmerkingen
- Het maximale aantal geneste voorwaarden dat u kunt uitvoeren is 64.
- U kunt vergelijkingsoperatoren gebruiken om een voorwaarde te evalueren.
Voorbeeld
In het onderstaande voorbeeld hebben we een vergelijkingsoperator gebruikt om verschillende omstandigheden te evalueren.
- We hebben specifieke tekst gebruikt om het resultaat te krijgen of aan de voorwaarde is voldaan of niet.
- U kunt ook WAAR en ONWAAR gebruiken om het resultaat te krijgen.
- Als u het opgeven van een waarde overslaat om het resultaat te krijgen als de voorwaarde WAAR is, wordt nul geretourneerd.
- En als u het opgeven van een waarde overslaat om het resultaat te krijgen als de voorwaarde FALSE is, wordt nul geretourneerd.
In het onderstaande voorbeeld hebben we de ALS-functie gebruikt om een nestformule te maken.
We hebben een voorwaarde gespecificeerd en als die voorwaarde onwaar is, hebben we een andere IF gebruikt om een andere voorwaarde te evalueren en een taak uit te voeren, en als die voorwaarde ONWAAR is, hebben we een andere IF gebruikt.
Op deze manier hebben we IF vijf keer gebruikt om een nestformule te maken. Je kunt hetzelfde 64 keer gebruiken voor een nestformule.
2.IFERROR-functie
De functie IFERROR retourneert een specifieke waarde als er een fout optreedt. In eenvoudige bewoordingen kan het de waarde testen en als die waarde een fout is, retourneert het de door u opgegeven waarde.
Syntaxis
IFERROR(waarde; waarde_als_fout)
Argumenten
- waarde: de waarde die u op fouten wilt testen.
- value_if_error: De waarde die u wilt terugkrijgen wanneer er een fout optreedt.
Opmerkingen
- De IFERROR-functie heeft betrekking op het optreden van een fout, niet op het type fout.
- Als u de waarde of value_if_error negeert, wordt in het resultaat 0 geretourneerd.
- Het kan #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? testen. en #NULL!.
- Als u een array evalueert, retourneert deze een array met resultaten voor elk opgegeven element.
Voorbeeld
In het onderstaande voorbeeld hebben we de IFERROR-functie gebruikt om de #DIV/0! met betekenisvolle tekst.
IFERROR is alleen compatibel met versies 2007 en eerder. Om dit probleem op te lossen, kunt u ISERROR gebruiken.
3.TRUNC-functie
De functie TRUNC retourneert een geheel getal na het afkappen van het oorspronkelijke getal. In eenvoudige bewoordingen verwijdert het decimalen van een getal met een specifieke precisie en retourneert vervolgens het gehele deel van het resultaat.
Syntaxis
TRUNC(getal, [getal_cijfers])
Argumenten
- nummer: het nummer dat u wilt afkappen.
- [num_digits]: een getal om de precisie op te geven voor het afkappen van een getal.
Opmerkingen
- Als u het opgeven van meerdere negeert, wordt er een fout geretourneerd.
- Er wordt afgerond vanaf nul.
- Als u twee veelvouden op dezelfde afstand heeft, wordt het hogere veelvoud geretourneerd van het getal dat u afrondt.
Voorbeeld
In het onderstaande voorbeeld hebben we TRUNC gebruikt om de gegevens in te korten om de tijd uit de datums te verwijderen.
4. SUMIF-functie
De functie SUMIF retourneert de som van getallen die voldoen aan de voorwaarde die u opgeeft . Simpel gezegd: het beschouwt en berekent alleen de som van de waarden die aan de voorwaarde voldoen.
Syntaxis
SOM.ALS(bereik; criteria, [som_bereik])
Argumenten
- bereik: Een celbereik waarvan u de criteria wilt controleren.
- criterium: een criterium dat een getal, een tekst, een uitdrukking, een celverwijzing of een functie kan zijn.
- [sum_range]: Een celbereik met de waarden die u wilt optellen.
Opmerkingen
- Als som_bereik wordt weggelaten, worden de cellen in het bereik opgeteld.
- Zorg ervoor dat u dubbele aanhalingstekens gebruikt om tekstcriteria op te geven of om wiskundige symbolen op te geven, die tussen dubbele aanhalingstekens moeten staan.
- De grootte van het criteriumbereik en het sombereik moeten dezelfde grootte hebben.
Voorbeeld
In het onderstaande voorbeeld hebben we A1:A9 gespecificeerd als het criteriumbereik en B1:B9 als het sombereik en daarna hebben we de criteria in A12 gespecificeerd die de waarde C hebben.
U kunt criteria ook rechtstreeks in de functie invoegen. In het onderstaande voorbeeld hebben we een jokerteken met een asterisk gebruikt om een criterium op te geven met het alfabet “S”.
En als u het sombereik negeert, krijgt u de som van het criteriabereik. Maar dit is alleen mogelijk als het criteriabereik numerieke waarden heeft.
5. INDEX-functie
De INDEX-functie retourneert een waarde uit een lijst met waarden op basis van het indexnummer. Simpel gezegd retourneert INDEX een waarde uit een lijst met waarden en u moet de positie van die waarde opgeven.
Syntaxis
INDEX heeft twee verschillende syntaxis. In het eerste geval kunt u een arrayvorm van een index gebruiken om eenvoudigweg een waarde uit een lijst te halen met behulp van de positie ervan.
INDEX(matrix, rij_getal, [kolom_getal])
In het tweede geval kunt u een sponsorformulier gebruiken dat in de praktijk minder wordt gebruikt, maar u kunt dit wel gebruiken als u meer dan één assortiment wilt promoten.
INDEX(referentie, rijnummer, [kolomnummer], [gebiednummer])
Argumenten
- array: Een bereik van cellen of een arrayconstante.
- referentie: een bereik van cellen of meerdere bereiken.
- rij_nummer: Rijnummer waaruit u de waarde wilt halen.
- [col_number]: het nummer van de kolom waaruit u de waarde wilt halen.
- [gebiedsnummer]: Als u verwijst naar meerdere celbereiken (met behulp van referentiesyntaxis), geef dan een getal op om naar één bereik uit alle cellen te verwijzen.
Opmerkingen
- Wanneer de argumenten row_num en column_num worden opgegeven, wordt de waarde in de cel op het snijpunt van de twee geretourneerd.
- Als u row_num of column_num opgeeft als 0 (nul), wordt de reeks waarden voor respectievelijk de hele kolom of rij geretourneerd.
- Wanneer row_num en column_num buiten bereik zijn, retourneert het #REF! fout.
- Als gebiedsnummer groter is dan de getalsbereiken die u hebt opgegeven, wordt #REF! geretourneerd.
Voorbeeld 1 – ARRAY gebruiken om een waarde uit een lijst te halen
In het onderstaande voorbeeld hebben we de INDEX-functie gebruikt om het aantal voor de maand juni te verkrijgen. In de lijst staat Jun op de 6e positie (6e rij). Daarom heb ik 6 opgegeven in rijnummer. INDEX retourneerde de waarde 1904 in het resultaat.
En als u verwijst naar een bereik met meer dan één kolom, moet u het kolomnummer opgeven.
Voorbeeld 2 – REFERENCE gebruiken om de waarde van meerdere lijsten te verkrijgen
In het onderstaande voorbeeld heb ik, in plaats van het hele bereik in één keer te selecteren, het in drie verschillende bereiken geselecteerd. In het laatste argument hebben we 2 gespecificeerd in area_number, waarmee het te gebruiken bereik uit deze drie verschillende bereiken wordt gedefinieerd.
Nu verwijzen we in de tweede rij naar de 5e rij en de 1e kolom. INDEX retourneert de waarde 172 die zich in de 5e rij van de 2e rij bevindt.
6.VLOOKUP-functie
De functie VERT.ZOEKEN zoekt naar een waarde in de eerste kolom van een tabel en retourneert de waarde in dezelfde rij van de overeenkomstige waarde met behulp van het indexnummer. In eenvoudige bewoordingen voert het een verticale zoekopdracht uit.
Syntaxis
VERT.ZOEKEN(opzoekwaarde,tabelmatrix,kolomindexnummer,bereikopzoeken)
Argumenten
- lookup_value: een waarde waarnaar u in een kolom wilt zoeken. U kunt verwijzen naar een cel die de zoekwaarde bevat, of die waarde rechtstreeks in de functie invoeren.
- table_array: Een celbereik, een benoemd bereik waaruit u de waarde wilt vinden.
- col_index_num: Een getal vertegenwoordigt het kolomnummer waaruit u de waarde wilt ophalen.
- range_lookup: Gebruik false of 0 voor een exacte match en true of 1 voor een goede match. De standaardwaarde is Waar.
Opmerkingen
- Als VERT.ZOEKEN de waarde die u zoekt niet vindt, retourneert het een #N/A.
- VLOOKUP kan u alleen de waarde geven die zich rechts van de opzoekwaarde bevindt. Als je vanaf de rechterkant wilt kijken, kun je daarvoor INDEX en MATCH gebruiken.
- Als u exacte overeenkomst gebruikt, komt deze alleen overeen met de waarde die als eerste in de kolom staat.
- U kunt ook jokertekens gebruiken met VERT.ZOEKEN .
- U kunt TRUE of 1 gebruiken als u een goede match wilt, en FALSE of 0 voor een exacte match.
- Als u een juiste overeenkomst gebruikt (True): Er wordt de op een na kleinste waarde in de lijst geretourneerd als er geen exacte overeenkomst is.
- Als de waarde die u zoekt kleiner is dan de kleinste waarde in de lijst, retourneert VERT.ZOEKEN #N/A.
- Als er een exacte waarde is waarnaar u op zoek bent, krijgt u die exacte waarde.
- Zorg ervoor dat u de lijst in oplopende volgorde heeft gesorteerd.
Voorbeeld
1. VERT.ZOEKEN gebruiken voor categorieën
In het onderstaande voorbeeld hebben we een lijst met studenten met cijfers die ze hebben gekregen, en in de opmerkingenkolom willen we een cijfer op basis van hun cijfers.
In de merkenlijst hierboven willen we opmerkingen toevoegen volgens het onderstaande categoriebereik.
Hierin hebben we twee opties om te gebruiken.
De EERSTE is het maken van een nestformule met IF, wat wat tijd kost, en de TWEEDE optie is het maken van een formule met VERT.ZOEKEN met een geschikte match. En de formule zal zijn:
=VERZOEKEN(B2,$E$2:$G$5,3,WAAR)
Hoe het werkt
Ik gebruik de kolom ‘MIN MARKS’ om de opzoekwaarde te matchen en de waarde terug te halen uit de kolom ‘Opmerkingen’.
Ik heb al gezegd dat wanneer je TRUE gebruikt en er geen exacte zoekwaarde is, de volgende kleinste waarde van de zoekwaarde wordt geretourneerd. Als we bijvoorbeeld in de categorietabel naar een waarde 77 zoeken, is 65 de kleinste waarde na 77.
Daarom scoorden wij bij de opmerkingen ‘Goed’.
2. Foutafhandeling in de functie VERT.ZOEKEN
Een van de meest voorkomende problemen die zich voordoen bij het gebruik van VERT.ZOEKEN is dat u #N/A krijgt wanneer er geen overeenkomst wordt gevonden. Maar de oplossing voor dit probleem is eenvoudig en gemakkelijk. Ik zal het u laten zien met een eenvoudig voorbeeld.
In het onderstaande voorbeeld hebben we een lijst met namen en hun leeftijden en in cel E6 gebruiken we de functie VERT.ZOEKEN om naar een naam in de lijst te zoeken. Elke keer als ik een naam typ die niet in de lijst staat, krijg ik #N/A.
Maar wat ik hier wil is een betekenisvol bericht weergeven in plaats van de fout. De formule wordt: =IFNA(VLOOKUP(D6,Blad3!$A$1:$B$14,2,0),,”Niet gevonden”)
Hoe het werkt : IFNA kan een waarde testen voor #N/A en als er een fout is, kunt u een waarde opgeven in plaats van de fout.
7. IFNA-functie
De IFNA-functie retourneert een specifieke waarde als er een #N/B-fout optreedt. In tegenstelling tot IFERROR evalueert het alleen de fout #N/A en retourneert het de door u opgegeven waarde.
Syntaxis
IFNA(waarde; waarde_if_na)
Argumenten
- waarde: de waarde die u wilt testen op #N/A-fout.
- waarde_if_na: De waarde die u wilt retourneren als er een fout is opgetreden.
Opmerkingen
- Als u geen argumenten opgeeft, zal IFNA dit behandelen als een lege tekenreeks (“”).
- Als een waarde een array is, wordt het resultaat als een array geretourneerd.
- Het negeert alle andere fouten #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? en #NULL!.
Voorbeeld
In de functie VERT.ZOEKEN komt #N/B voor wanneer de opzoekwaarde niet in het opzoekbereik ligt. Hiervoor hebben we een betekenisvol bericht gespecificeerd met behulp van IFNA.
Let op: IFNA is geïntroduceerd in Excel 2013 en is dus niet beschikbaar in eerdere versies.
8. RAND-functie
De RAND-functie retourneert een willekeurig getal tussen 0 en 1. In eenvoudige bewoordingen kunt u een willekeurig getal tussen 0 en 1 genereren (de waarde wordt bijgewerkt elke keer dat u een wijziging aanbrengt in het werkblad).
Syntaxis
RAND()
Argumenten
- Er zijn geen argumenten om op te geven in RAND-functies
Opmerkingen
- Als u nul in een veelvoud invoert, zal het resultaat nul opleveren.
- Als u het opgeven van meerdere negeert, wordt er een fout geretourneerd.
- Er wordt afgerond vanaf nul.
- Als u twee veelvouden op dezelfde afstand heeft, wordt het hogere veelvoud geretourneerd van het getal dat u afrondt.
Voorbeeld
Naast getallen tussen 0 en 1, kun je ook RAND gebruiken voor willekeurige getallen tussen twee specifieke getallen. In het onderstaande voorbeeld heb ik het gebruikt om een formule te maken die een willekeurig getal tussen 50 en 100 genereert.
Wanneer u deze formule in een cel invoert, retourneert deze een getal tussen 100 en 50 door de geretourneerde waarden te vermenigvuldigen met de RAND met de vergelijking die we hebben gebruikt. Om deze formule te begrijpen, moeten we deze in drie delen verdelen:
- Ten eerste, wanneer het lagere getal van het hogere getal detecteert, krijg je het verschil tussen de twee.
- Vervolgens wordt dit verschil vermenigvuldigd met het willekeurige getal dat na het aftrekken wordt geretourneerd.
- En ten derde: voeg dit getal toe met het laagste overgebleven getal in het derde deel van de vergelijking.
Gerelateerd: Snel willekeurige letters genereren in Excel
9. SOM-functie
De functie SOM retourneert de som van de opgegeven waarden . Simpel gezegd: met de functie SOM kunt u de som van een lijst met waarden berekenen (u kunt rechtstreeks een waarde in de functie invoeren of naar een celbereik verwijzen.
Syntaxis
SOM(getal1,[getal2],…)
Argumenten
- getal1 : getal, celbereik dat getallen bevat, of een enkele cel die een getal bevat.
- [getal2] : getal, celbereik dat getallen bevat, of een enkele cel die een getal bevat.
Opmerkingen
- Het negeert tekstwaarden.
Voorbeeld
In het onderstaande voorbeeld kunt u getallen rechtstreeks in de functie invoegen, met komma’s ertussen.
U kunt ook gewoon naar een bereik verwijzen om de som van getallen te berekenen en als er tekst, logische waarde of lege cel is, worden deze genegeerd.
Als er een foutwaarde staat in een cel waarnaar u verwijst, wordt in het resultaat #N/A geretourneerd.
Als u numerieke waarden heeft die zijn opgemaakt als tekst, worden deze genegeerd. Het wordt aanbevolen om ze naar getallen te converteren voordat u SUM gebruikt.
10. OF-functie
De OR-functie retourneert een Booleaanse waarde (TRUE of FALSE) na het testen van de voorwaarden die u opgeeft. In eenvoudige bewoordingen kunt u meerdere voorwaarden testen met de functie AND. Deze retourneert WAAR als een van deze voorwaarden (of alle) WAAR is en retourneert alleen FALSE als al deze voorwaarden ONWAAR zijn.
Syntaxis
OF(logisch1, [logisch2], …)
Argumenten
- logisch1: voorwaarde die u wilt controleren.
- [logisch2]: Aanvullende voorwaarden die u wilt controleren.
Opmerkingen
- Waarden worden genegeerd als de referentiecel of -tabel een lege cel of tekst bevat.
- Het resultaat van de voorwaarden moet een logische waarde zijn (TRUE of FALSE).
- Er wordt een fout geretourneerd als er geen logische waarde wordt geretourneerd.
Voorbeeld
In het onderstaande voorbeeld hebben we met de functie ALS een voorwaarde gecreëerd: als een leerling 60 punten hoger scoort in een van de twee vakken, retourneert de formule WAAR.
In het onderstaande voorbeeld hebben we een getal gebruikt om logische waarden in een formule te krijgen. U kunt de bovenstaande voorwaarde ook in omgekeerde volgorde uitvoeren.
U kunt WAAR en ONWAAR gebruiken in plaats van getallen. De OR-functie behandelt deze logische waarden als getallen.
Meer tutorials
Statistische functies / Datumfuncties / String – Tekstfuncties / Financiële functies