Excel is een geweldig hulpmiddel voor het rapporteren, analyseren, organiseren en automatiseren van gegevens. De Excel-functies helpen veel bij het werken met gegevens. De functies zoals AANTALLEN.ALS, SUMIFS, VERT.ZOEKEN, enz. zijn de krachtigste en meest gebruikte functies sinds hun oprichting in de Excel-wereld.
Hoewel de functies die beschikbaar zijn in Excel 2016 en ouder voldoende zijn om elke vorm van berekening en automatisering uit te werken, worden de formules soms lastig. Als u bijvoorbeeld bij sommige voorwaarden de maximale waarde niet kunt vinden, moet u enkele trucs gebruiken in de oudere Excel-versie van 2016. Dit soort kleine maar belangrijke dingen worden opgelost in Excel 2019 en 365.
Er zijn 10+ nieuwe functies in Excel 2019 en 365 die de menselijke inspanning en complexiteit van de formules verminderen.
1. De MAXIFS-functie
Als u in Excel 2016 en ouder de maximale waarde in een bereik wilt krijgen wanneer een of meer voorwaarden overeenkomen, moet u MAX met IF gebruiken met enkele trucs. Dat is niet zo moeilijk, maar het kost veel tijd en is voor sommigen verwarrend.
De Excel 2019 introduceert een nieuwe functie met de naam MAXIFS-functie. Deze functie retourneert de maximale waarde van een array wanneer aan alle gegeven voorwaarden is voldaan.
De syntaxis van de functie is:
| =MAXIFS(max_bereik,criteria_bereik1,criteria1,criteria_bereik2,criteria2… ) |
Max_bereik1: Het is het numerieke bereik dat de maximale waarde bevat.
Criteria_bereik1: Het is het criteriumbereik dat u wilt filteren voordat u de maximale waarde krijgt.
Criteria1: Het zijn de criteria of het filter dat u op criteria_range wilt plaatsen voordat u de maximale waarde krijgt.
Stel dat je het maximale aantal punten van klas 3 moet halen, dan is de formule
| =MAXIFS(cijfers,klasse,3) |
Hier zijn markeringen het benoemde bereik dat de markeringen bevat en klasse is het benoemde bereik dat de klasse bevat.
Lees hier meer over de MAXIFS-functie.
2. De MINIFS-functie
Hetzelfde als de MAXIFS-functie, wordt de MINIFS-functie gebruikt om de minimumwaarde uit het gegeven bereik te halen wanneer aan alle gegeven voorwaarden is voldaan.
De syntaxis van de functie is:
| =MINIFS(min_bereik,criteria_bereik1,criteria1,criteria_bereik2,criteria2… |
Min_bereik1: Het is het numerieke bereik dat de minimumwaarde bevat.
Criteria_bereik1: Het is het criteriumbereik dat u wilt filteren voordat u een minimale waarde krijgt.
Criteria1: Het zijn de criteria of het filter dat u op criteria_range wilt plaatsen voordat u de minimumwaarde krijgt.
Stel dat je de minimumcijfers van klasse 3 moet halen, dan is de formule
| =MINIFS(tekens,klasse,3) |
Hier is "markeringen" het benoemde bereik dat de tekens bevat en "klasse" is het benoemde bereik dat de klasse bevat.
Lees hier meer over de MAXIFS-functie.
Lees dit om de minimumwaarde te vinden binnen het bereik met de voorwaarden in Excel 2016 en ouder.
3. De IFS-functie
Omdat de geneste Ifs een speciale plaats heeft in ons dagelijkse werkleven, vinden we het erg leuk. Maar voor sommige nieuwe leerlingen is het complex. Met de geneste ifs kunnen we meerdere voorwaarden controleren en een andere waarde retourneren wanneer aan een van de voorwaarden wordt voldaan. De formules worden complex met steeds meer IF in functie.
De Excel 2019 en Excel 365 gebruiken nu de IFS-functie. Het kan meerdere voorwaarden controleren en voor elke voorwaarde verschillende waarden retourneren.
Syntaxis van de IFS-functie:
| =IFS (voorwaarde1, Waarde1_Als_True, [voorwaarde2, Waarde2_Als_True],… ) |
Voorwaarde1:De eerste voorwaarde.
Waarde1_If_True: Waarde als de eerste voorwaarde waar is.
[Conditie2]: Dit is optioneel. De tweede voorwaarde, als je die hebt.
[Waarde1_Als_Waar]: Waarde als de tweede voorwaarde waar is.
U kunt zoveel combinaties van voorwaarden en waarden hebben als u wilt. Er is een limiet, maar die limiet hoef je nooit te bereiken.
Laten we zeggen dat je de cijfers aan de studenten moet geven door hun cijfers. Voor cijfers van meer dan 80, klasse A, B voor meer dan 60, C voor meer dan 40 en F voor minder dan of gelijk aan 40.
| =IFS (A1>80, "A", A1>60, "B",A1>40, "C",A1<=40, "F") |
Een gedetailleerde uitleg van de IFS-functie vindt u hier.
4. De SWITCH-functie
De schakelfunctie retourneert verschillende waarden, afhankelijk van de resultaten van de ene uitdrukking. Klinkt als IFS? Het is een soort van. Deze functie is eigenlijk bedoeld om een ander soort geneste IF-formules te vervangen.
In tegenstelling tot de IFS-functie die waarden retourneert op basis van TRUE, FALSE; de functie SWITCH retourneert waarden op basis van WAARDEN die door de expressie worden geretourneerd.
| =SWITCH (uitdrukking, waarde1, resultaat1, [standaard of waarde2, resultaat2],… ) |
Uitdrukking: Dit kan elke geldige expressie zijn die enkele waarden retourneert. Een celverwijzing, een formule of statische waarde.
Waarde1, resultaat1: De waarde en het resultaat zijn gekoppeld. Als de waarde die wordt geretourneerd door deuitdrukking is waarde1 dan wordt resultaat1 geretourneerd.
[Standaard of waarde2, resultaat2]: Als u een standaardwaarde wilt retourneren, definieert u deze hier. Definieer anders de waarde2 en resultaat2. Het is optioneel.
Als u bijvoorbeeld een formule hebt die de namen van dieren retourneert. Nu, afhankelijk van de geretourneerde naam van het dier, wilt u het kenmerkende geluid van dat dier retourneren.
| =SCHAKELAAR (A1, "Hond", "Bow Wow", "Kat", "Miauw", "Spreekt") |
Ik heb de SWITCH-functie hier uitgebreid uitgelegd.
5. De FILTER-functie
De functie FILTER wordt gebruikt om gegevens te filteren op basis van enkele criteria. We hebben de filteroptie van het starttabblad in Excel gebruikt. De functie FILTER werkt hetzelfde als de filteroptie. Het retourneert gewoon de gefilterde gegevens met behulp van een functie. Deze gefilterde gegevens kunnen worden gebruikt als gegevensbron voor andere formules.
De syntaxis van de functie FILTER is:
| =FILTER(matrix,inclusief,[indien_leeg]) |
Reeks: Dit is de array die u wilt filteren. Het kan eendimensionaal of tweedimensionaal zijn.
Erbij betrekken:Het is het filter dat u op de array wilt plaatsen. Zoals, kleuren = "rood".
[indien_leeg]:Dit is optioneel. Definieer een willekeurige tekst of uitdrukking als het filter niets retourneert.
De onderstaande formule retourneert alle vruchten waarvan de kleur rood is.
| =FILTER(fruit,kleur="rood", "geen fruit gevonden") |
Hier worden fruit en kleur genoemd bereiken die respectievelijk namen van de vruchten en hun kleuren bevatten.
U kunt hier in detail lezen over de FILTER-functie.
6. De SORT-functie
In Excel 2016 en ouder was het erg lastig om een gesorteerde array te krijgen met behulp van een formule. Dit proces is vereenvoudigd in Excel 2019 en 365.
De Excel 2019 introduceert de functie SORTEREN. De functie SORTEREN sorteert de gegeven array in oplopende of aflopende volgorde op de gegeven kolom/rij.
De syntaxis van de functie SORTEREN is:
| =SORT (matrix,[sort_index],[sort_order],[by_col]) |
Reeks:Het is de referentie van de array of het bereik dat u wilt sorteren.
[sort_index]:Het kolomnummer in de tweedimensionale matrix waarop u het bereik wilt sorteren. Standaard is dit 1.
[sorteervolgorde]:De volgorde waarin u de array wilt sorteren. Voor oplopend is het 1 en voor aflopend is het -1. Standaard is dit 1.
[door_col]:Stel het in op True(1) als u een horizontale array wilt sorteren. Standaard is dit False(0) voor verticale gegevens.
Stel dat u waarden in het bereik A2:A11 oplopend wilt sorteren. dan wordt de formule.
| =SORTEREN(A2:A11) |
Ik heb de SORT-functie hier in detail uitgelegd.
7. De SORTEREN-functie
De functie SORTEREN is vergelijkbaar met de functie SORTEREN. Het enige verschil is dat de sorteerarray niet het deel van de gesorteerde array in de SORTBY-functie hoeft te zijn.
| =SORTEREN OP(matrix,sorteer_matrix1,[volgorde],… ) |
Reeks:Dit is de array die u wilt sorteren.
Sorteer_array1:Dit is de array waarop u de array wilt sorteren. De afmeting van deze array moet compatibel zijn met de reeks.
[volgorde]:Optioneel. Stel deze in op -1 als u wilt dat de volgorde aflopend is. Standaard is deze oplopend(1).
Stel dat u het bereik A2:A11 wilt sorteren op bereik B2:B11, in aflopende volgorde. Dan wordt de formule in Excel 2019 of 365:
| =SORTEREN(A2:A11,B2:B11,-1) |
Ik heb de SORTBY-functie hier in detail uitgelegd.
8. De UNIEKE Functie
In Excel 2016 en ouder hebben we een aantal functies in combinatie gebruikt om alle unieke waarden uit de gegeven lijst te krijgen. De gebruikte formule is vrij complex en moeilijk te begrijpen.
De Excel 2019 en 365 introduceren één eenvoudige UNIEKE functie die alle unieke waarden van een bepaalde array retourneert.
De syntaxis van de functie UNIQUE is:
| =UNIEK(matrix,[per_col],[exact_eenmaal]) |
Array: De array waaruit u unieke waarden wilt extraheren:
[door_col]: Stel het in op TRUE(1) als de array horizontaal is. Standaard is dit ONWAAR voor verticale gegevens.
[precies_eenmaal]: stel het in op TRUE(1) als u waarden wilt extraheren die slechts eenmaal in de array voorkomen. Standaard is het FALSE(0) om alle unieke waarden te extraheren.
Laten we zeggen dat ik slechts één exemplaar van elke waarde uit het bereik A2: A11 wil krijgen, dan is de formule:
| =UNIEK(A2:A11) |
Om meer te lezen over de UNIQUE-functie, kunt u hier klikken.
9. De SEQUENTIE-functie
Om een reeks getallen in Excel 2016 en ouder te krijgen, gebruiken we een combinatie van functies. De oplossing werkt wel, maar is complex.
De Excel 2019 en 365 bieden de oplossing in de vorm van de SEQUENCE functie. De reeksfunctie retourneert eenvoudig de reeks van het getal.
De syntaxis van de functie SEQUENTIE is:
| =VOLGORDE(rijen,[kolommen],[start],[stap]) |
Rijen:Het aantal rijen waarnaar u de reeks wilt overslaan.
[kolom]:Het aantal kolommen waarnaar u de reeks wilt overslaan. De getallen vullen eerst de kolommen en daarna de rijen. De kolom is optioneel. Standaard is dit 1.
[begin]:Optioneel. Het startnummer van de reeks. Standaard is dit 1.
[stap]:Dit is het oplopende nummer voor het volgende nummer. Standaard is dit 1.
Het eenvoudige voorbeeld is het krijgen van een reeks van 1 tot 10. De formule is:
| =VOLGORDE(10) |
Lees dit om de SEQUENTIE-functie in Excel 365 in detail te begrijpen.
10. De RANDARRAY-functie
Dit is een andere dynamische matrixformule die een matrix met willekeurige getallen retourneert. Het is een combinatie van de RAND- en RANDBETWEEN-functie. U kunt fractionele willekeurige getallen of gehele getallen krijgen. U kunt het gewenste aantal willekeurige getallen opgeven. Even rijen en kolommen waarin u deze getallen wilt verdelen.
De syntaxis van de functie RANDARRAY is:
| =RANDARRAY([rijen],[kolommen],[min],[max],[geheel getal]) |
Alle argumenten in deze functie zijn optioneel. Standaard werkt het als de RAND-functie.
[rijen]:Het aantal getallen dat u verticaal wilt hebben (aantal rijen dat u wilt vullen).
[kolommen]:Het aantal getallen dat u horizontaal wilt hebben (aantal kolommen dat u wilt vullen).
[min]:Het startnummer of de minimumwaarde van het/de willekeurige nummer(s).
[maximaal]:Het maximale bereik van het nummer.
[geheel getal]:Stel het in op waar, als u wilt dat de willekeurige getallen hele getallen zijn. Standaard is het onwaar en geeft het fractionele willekeurige getallen terug.
De onderstaande functie retourneert rijsgewijs vijf willekeurige fractionele getallen:
| =RANDARRAY(5) |
Lees hier meer over de RANDARRAY-functie.
11. De CONCAT-functie
In Excel 2016 en ouder is het niet eenvoudig om meer dan één cel of bereik samen te voegen met één formule.
Met de Excel 2019 en 365 is het probleem opgelost met de functie CONCAT. De functie kan meerdere cellen, bereiken als argumenten aannemen.
De syntaxis van de functie CONCAT is:
| =CONCAT(tekst1,[tekst2],… ) |
Tekst 1 : De tekst1 kan elke tekst of elk bereik zijn dat u wilt samenvoegen.
[tekst2]: Dit is optioneel. Dit kan ook een willekeurige tekst of bereik zijn.
Laten we zeggen dat als u elke cel in het bereik A2:A11 wilt samenvoegen, de formule zal zijn:
| =CONCAT(A2:A11) |
Klik hier om de CONCAT-functie in detail te verkennen.
12. De TEXTJOIN-functie
De bovenstaande functie voegt alle cellen in een bereik samen, maar voegt de cellen niet samen met een gespecificeerd scheidingsteken. Laten we zeggen dat als u een bestand voorbereidt voor CSV-indeling, u de cellen moet samenvoegen met komma's. In dat geval werken CONCATENATE en CONCAT, beide mislukken.
Hier werkt de TEXTJOIN-functie wonderbaarlijk en voegt de gegeven teksten samen met het gegeven scheidingsteken.
| =TEXTJOIN(scheidingsteken, negeer_lege_cellen,tekst1,[tekst2],… ) |
scheidingsteken:Dit is het scheidingsteken dat u wilt gebruiken als scheidingsteken tussen afzonderlijke teksten. Het kan een komma (,), puntkomma (;) of iets anders zijn, zelfs niets.
Negeer_lege_cellen:Dit is een binaire variabele. Stel het in op TRUE als u lege cellen in bereiken wilt negeren, anders op FALSE om de lege cellen op te nemen.
Tekst 1:Dit is de tekst waaraan u wilt deelnemen. Dit kunnen afzonderlijke teksten, cellen of hele bereiken zijn.
Laten we zeggen dat ik bereik A2:A11 wil samenvoegen met een komma, waarbij ik de lege cellen negeer.
| =TEXTJOIN(",",1,A2:A11) |
Klik hier om deze functie in detail te begrijpen.
Dit artikel was slechts een introductie op de nieuwe functie van Excel 365 en 2019. Deze functies heb ik in aparte artikelen uitgebreid toegelicht. U kunt op de links voor elke functie in het artikel klikken om de functie volledig te begrijpen. Er zijn andere functies zoals XLOOKUP die nog niet zijn vrijgegeven.
Als je twijfels hebt over Excel- of VBA-onderwerpen, vraag het dan in de comments hieronder. Vertel ons hoe we kunnen verbeteren. We stellen uw suggestie op prijs en horen graag van u.
Maak een VBA-functie om array terug te geven | Om een array van een door de gebruiker gedefinieerde functie te retourneren, moeten we deze declareren wanneer we de UDF een naam geven.
Arrays in Excel Formul|Leer wat arrays zijn in Excel.
Hoe een door de gebruiker gedefinieerde functie te creëren via VBA | Leer hoe u door de gebruiker gedefinieerde functies in Excel kunt maken
Een door de gebruiker gedefinieerde functie (UDF) uit een andere werkmap gebruiken met VBA in Microsoft Excel | Gebruik de door de gebruiker gedefinieerde functie in een andere werkmap van Excel
Retourneer foutwaarden van door de gebruiker gedefinieerde functies met VBA in Microsoft Excel | Leer hoe u foutwaarden kunt retourneren vanuit een door de gebruiker gedefinieerde functie
Populaire artikels:
Excel-blad splitsen in meerdere bestanden op basis van kolom met behulp van VBA | Deze VBA-code splitst Excel-blad op basis van unieke waarden in een opgegeven kolom. Download het werkbestand.
Schakel waarschuwingsberichten uit met VBA in Microsoft Excel 2016 | Om waarschuwingsberichten uit te schakelen die de actieve VBA-code onderbreken, gebruiken we de klasse Application.
Nieuwe werkmap toevoegen en opslaan met VBA in Microsoft Excel 2016 | Om werkmappen toe te voegen en op te slaan met VBA gebruiken we Workbooks class. Workbooks.Add voegt eenvoudig een nieuwe werkmap toe, maar…