Cheatsheet voor Excel-formules: handleiding voor gevorderden

Dus je hebt de basis doorgenomen, de tussenliggende dingen aangepakt en nu ben je klaar om te worstelen met de grote wapens - de geavanceerde formules van Excel! Excel heeft veel geavanceerde functies en formules voor geavanceerde berekeningen, dus het is handig om een ​​gids te hebben die aangeeft welke je nodig hebt.

Dit spiekbriefje met Excel-formules behandelt geavanceerde prognoseformules, statistische analyse, functies voor gegevensmanipulatie, foutafhandeling en meer.

Deze referentie zal u voorzien van de kennis over het gebruik van deze geavanceerde functies. Elke formule gaat vergezeld van duidelijke uitleg, syntaxis en praktische voorbeelden om halfgevorderde Excel-gebruikers te helpen gevorderde ervaren gebruikers te worden.

Download het spiekbriefje, print het uit en bewaar het bij de hand.

Oké, laten we beginnen.

Excel-formules-spiekbriefje-geavanceerd-1 Download

Laten we eerst eens kijken naar matrixformules.

Inhoudsopgave

• Matrixformules
  • 1. UNIEKE Functie
  • 2. SORT-functie
  • 3. FILTER-functie
• Willekeurige Excel-functies
  • 1. RANDBETWEEN-functie
  • 2. RANDARRAY-functie
• Geavanceerde prognoseformules in Microsoft Excel
  • 1. VOORSPELLING.ETS Functie
  • 2. TREND-functie
• Geavanceerde statistische formules
  • 1. PERCENTIEL-functie
  • 2. KWARTIEL-functie
• Geavanceerde formules voor gegevensanalyse en manipulatie
  • 1. TRANSPOSE-functie
  • 2. FREQUENTIE-functie
  • 3. GETPIVOTDATA-functie
• Geavanceerde foutafhandeling
• Geavanceerde opzoekformules
  • 1. XLOOKUP-functie
  • 2. XMATCH-functie
• Laatste gedachten

Matrixformules

Ons spiekbriefje voor beginners laat zien hoe u uw gegevens handmatig kunt sorteren en filteren. Gevorderde gebruikers doen dit programmatisch met matrixformules.

Met matrixformules kunt u berekeningen op meerdere cellen tegelijk uitvoeren. Dit zijn drie belangrijke functies:

1. UNIEK

2. SOORT

3. FILTER

Sommige van deze functies zijn alleen beschikbaar in de meest recente versies van Microsoft Excel.

1. UNIEKE Functie

De functie UNIQUE accepteert een bereik en retourneert een lijst met unieke waarden.

Stel, u heeft verkoopgegevens van kledingartikelen. Gebruik deze formule om de unieke items in kolom A te vinden:

=UNIEK(A2:A6)

2. SORT-functie

De functie SORT sorteert de inhoud van een bereik. De syntaxis is:

SORTEREN(matrix, [sorteerindex], [sorteervolgorde], [op_col])

• array : het bereik van waarden om te sorteren.

• sort_index : de te sorteren kolom (standaard 1)

• sort_order : 1 voor oplopend (standaard) of 2 voor aflopend).

• by_col : TRUE om op kolom te sorteren (standaard) of FALSE om op rij te sorteren.

De laatste drie argumenten zijn optioneel en de standaardwaarden zijn meestal wat u wilt.

Gebruik deze formule om de voorbeeldgegevens op de eerste kolom te sorteren:

=SORTEREN(A2:C6)

3. FILTER-functie

Met de functie FILTER kunt u een bereik op een specifieke voorwaarde filteren. Dit is de syntaxis:

=FILTER(matrix, opnemen, [indien_leeg])

• array : het bereik om te filteren.

• include : de voorwaarde die bepaalt welke waarden moeten worden gefilterd.

• if_empty : geeft aan wat er moet worden geretourneerd als er geen waarden voldoen aan de filtercriteria (standaard is "").

Stel dat u de rijen in de voorbeeldgegevens wilt filteren om alleen weer te geven waar de verkoopwaarde hoger is dan € 15. Gebruik deze formule:

=FILTER(A2:C6, C2:C6>15)

Willekeurige Excel-functies

Onze tussentijdse cheat laat zien hoe je de RAND-functie gebruikt die een willekeurig getal tussen 0 en 1 produceert.

Gevorderde Excel-gebruikers weten hoe ze de randomisatiefuncties moeten gebruiken om snel voorbeeldgegevens te genereren.

1. RANDBETWEEN-functie

De functie RANDBETWEEN is flexibeler dan RAND omdat u de onderste en bovenste getallen kunt specificeren als iets anders dan 0 en 1.

Voer deze formule in cel A1 in om gegevens te genereren met getallen tussen 1 en 100:

=ASELECTTUSSEN(1; 100)

Kopieer vervolgens de cel naar zoveel rijen en kolommen als u wilt. Het duurt seconden om een ​​raster van willekeurige getallen te maken:

2. RANDARRAY-functie

U denkt misschien dat het leuk zou zijn om de handmatige kopie van de RANDBETWEEN-functie te vermijden. Om supergeavanceerd te worden, kunt u de nieuwe RANDARRAY-functie gebruiken in de nieuwste versie van Microsoft Excel.

De syntaxis is:

RANDARRAY([rijen], [kolommen], [min], [max], [geheel getal])

• rijen : aantal rijen

• kolommen : aantal kolommen

• min : laagste getal

• max : hoogste aantal

• geheel getal : standaard ingesteld op WAAR, anders worden decimale getallen gebruikt.

Gebruik deze formule om willekeurige getallen tussen 1 en 100 over zes rijen en negen kolommen te genereren:

=RANDM.ARRA.(6; 9; 1; 100; WAAR)

Geavanceerde prognoseformules in Microsoft Excel

De prognosefuncties van Excel worden gebruikt voor het voorspellen van toekomstige waarden op basis van bestaande gegevenstrends. Deze functies helpen bij het identificeren van patronen en projecttrends op basis van uw gegevens.

1. VOORSPELLING.ETS Functie

De oudere FORECAST-functie is vervangen door een reeks nieuwere functies in Excel 2016.

U kiest de functie op basis van het specifieke prognosemodel dat u wilt. De functie FORECAST.ETS gebruikt bijvoorbeeld het algoritme Exponential Smoothing .

De syntaxis is:

VOORSPELLING.ETS(streefdatum, waarden, tijdlijn)

• target_date : de datum waarvoor u een berekende waarde wilt.

• waarden : de historische gegevens.

• tijdlijn : een reeks datums

Stel dat u datums van 1 tot 5 januari in kolom A heeft en verkoopbedragen in kolom B. Deze formule voorspelt het volgende verkoopbedrag:

=VOORSPELLING.ETS(“6 januari 2023”, B2:B6, A2:A6)

2. TREND-functie

De functie TREND projecteert een reeks waarden op basis van de methode van de kleinste kwadraten. Het retourneert een array. De syntaxis is:

TREND(bekende_y, [bekende_x], [nieuwe_x], [const])

• bekende_y : bereik van y-waarden

• bekende_x : bereik van x-waarden

• new_x : bereik van berekende waarden

Vaak zijn de bekende_y de gegevenspunten, terwijl de bekende_x de datums zijn.

Met dezelfde gegevens als in het vorige voorbeeld kunt u de onderstaande formule invoeren in cel C2. Er wordt een reeks waarden gegenereerd.

=TREND(B2:B6; A2:A6)

Geavanceerde statistische formules

De geavanceerde statistische functies omvatten het berekenen van percentielen en kwartielen. Sommige wiskundige functies zijn beschikbaar voor achterwaartse compatibiliteit, maar het wordt aanbevolen om de meest recente versies te gebruiken.

1. PERCENTIEL-functie

Deze functie berekent het percentage datapunten dat onder een bepaalde waarde valt. De syntaxis is:

PERCENTIEL.INC(matrix, k)

• array : het celbereik

• k : het percentiel van 0 tot 1

Stel dat u het 70e percentiel van gegevens in kolom B wilt berekenen. Gebruik deze formule:

=PERCENTIEL.INC(B2:B6; 0,7)

2. KWARTIEL-functie

Deze functie is een variatie op de percentielfunctie, maar gebruikt kwartalen om de gegevens te delen. Dit is de syntaxis:

KWARTIEL.INC(matrix, kwart)

• array : het gegevensbereik

• kwart : 1 voor het 25e percentiel, 2 voor het 50e, 3 voor het 75e en 4 voor het maximum.

De onderstaande formule berekent het eerste kwartiel van gegevens in kolom A.

=KWARTIEL.INC(A2:A5; 1)

Geavanceerde formules voor gegevensanalyse en manipulatie

Met verschillende geavanceerde functies kunt u het formaat van gegevens wijzigen, frequentieverdelingen analyseren en gegevens uit draaitabellen halen.

1. TRANSPONEREN

2. FREQUENTIE

3. GETPIVOTDATA

1. TRANSPOSE-functie

Soms wilt u de gegevens in uw rijen verplaatsen naar kolommen en vice versa. U kunt dit handmatig doen of in plaats daarvan de TRANSPOSE-functie gebruiken.

Stel dat u de items "T-shirt", "Hoodie" en "Jeans" in de cellen A2, A3 en A4 hebt. U wilt hier kolomkoppen van maken. Deze functie retourneert de waarden in een enkele rij:

=VERPLAATSEN(A2:A4)

2. FREQUENTIE-functie

Deze functie berekent de frequentieverdeling van waarden binnen een dataset. Dit is de syntaxis:

FREQUENTIE(gegevens_array; bins_array)

• data_array : bereik van waarden.

• bins_array : de te gebruiken intervallen.

Stel dat u verkoopgegevens in kolom B heeft en u de frequentieverdeling van de waarden wilt analyseren op basis van het aantal bedragen:

• onder de 20.

• van 20 tot 80.

• boven de 80.

Dat staat voor drie bakken en kan worden berekend met deze formule:

=FREQUENTIE(A2:A6; {20,80})

Bekijk deze video voor meer informatie over frequentieverdelingen in Excel:

3. GETPIVOTDATA-functie

Met deze functie kunt u samengevatte informatie uit draaitabellen halen. Dit is de syntaxis:

GETPIVOTDATA(gegevens_veld, draaitabel, [veld1, item1], [veld2, item2], …)

• data_field : het gegevensveld of de waarde die u uit de draaitabel wilt ophalen.

• pivot_table : een verwijzing naar de draaitabel.

• field1, item1, etc. : de veld/itemparen om op te filteren.

Stel dat u een draaitabel heeft op basis van de kleur van de verkochte artikelen. Gebruik deze formule om de verkoop voor rode artikelen te extraheren:

=GETPIVOTDATA(“Verkoop”, A1, “Kleur”, “Rood”)

Geavanceerde foutafhandeling

Zelfs de meest elementaire Excel-formules kunnen fouten opleveren. Gevorderde gebruikers moeten weten hoe ze ISERROR moeten gebruiken om fouten af ​​te handelen. Gevorderde gebruikers moeten ook bekend zijn met de ERROR.TYPE-functie voor foutidentificatie.

De functie ERROR.TYPE helpt bij het identificeren van het specifieke type fout binnen een cel of formule.

Het retourneert een numerieke waarde die overeenkomt met verschillende soorten fouten, zoals #N/B, #WAARDE!, #VERW!, en meer.

Stel dat u een fout hebt in cel A1 en u wilt het fouttype identificeren. De volgende formule retourneert het nummer dat overeenkomt met de specifieke fout:

=FOUT.TYPE(A1)

U kunt dit combineren met meerdere functies om verschillend te reageren, afhankelijk van het type fout. Dit zijn de meest voorkomende fouten en hun waarden:

1. #NUL! (geen gemeenschappelijke cel gevonden in een bereik)

2. #DEEL/0! ( delen door nul of een lege cel)

3. #WAARDE! (ongepast gegevenstype of argument in een formule)

4. #REF! (een cel waarnaar wordt verwezen is verwijderd of er is een kringverwijzing )

5. #NAAM? (Excel herkent de functie of het bereik niet)

6. #GETAL! (ongeldige numerieke waarde)

7. #N.v.t. (waarde kan niet worden gevonden)

Stel dat u drie specifieke soorten fouten wilt afhandelen. Gebruik deze formule om een ​​specifiek foutbericht weer te geven op basis van het type:

=IF(ISERROR(C2), IF(ERROR.TYPE(C2)=2, "Delen door nul fout", IF(ERROR.TYPE(C2)=3, "Ongeldige waarde fout", IF(ERROR.TYPE(C2 )=7, “Waarde niet gevonden fout”, “Andere fout”))), C2)

Geavanceerde opzoekformules

Onze spiekbriefjes voor beginners en gevorderden behandelden een selectie van opzoekfuncties . Hier zijn enkele geavanceerde opties:

1. XLZOEKEN

2. XMATCH

1. XLOOKUP-functie

Met deze opzoekfunctie kunt u naar een waarde in een bereik zoeken en een overeenkomstige waarde uit een andere kolom of bereik retourneren.

Het biedt meer veelzijdigheid dan eenvoudigere opzoekfuncties zoals VERT.ZOEKEN . Dit is de syntaxis:

XLOOKUP(opzoekwaarde, opzoekmatrix, retourmatrix, [overeenkomstmodus], [zoekmodus], [indien_niet_gevonden])

• lookup_value : de waarde waarnaar u wilt zoeken.

• lookup_array : het bereik voor de zoekopdracht.

• return_array : het bereik dat de overeenkomstige waarde zal tonen.

• match_mode : exacte overeenkomst (0), volgende kleinere (1), volgende grotere (-1), of jokertekenovereenkomst (2).

• search_mode : -1 voor van boven naar beneden, 1 voor van onder naar boven, of 2 voor binair zoeken.

• if_not_found : stelt de waarde in die moet worden geretourneerd als er geen overeenkomst wordt gevonden.

Stel dat u een reeks gegevens wilt doorzoeken op de eerste keer dat een kledingstuk voorkomt en de verkoopbedragen wilt retourneren. Deze formule zoekt naar de tekst "Hoodie" en retourneert de waarde in de aangrenzende cel indien gevonden:

=XLZOEKEN(“Hoodie”, A2:A6, B2:B6)

2. XMATCH-functie

Met deze functie kunt u de positie van een opgegeven waarde binnen een bereik of een array vinden. Dit is de syntaxis:

XMATCH(opzoekwaarde; opzoekmatrix; [overeenkomsttype]; [zoekmodus])

• lookup_value : de waarde die u wilt vinden.

• lookup_array : Het bereik dat u wilt doorzoeken.

• match_type : exacte overeenkomst (0), volgende kleinste (-1), volgende grootste (1).

• search_mode : binair zoeken (1) of lineair zoeken (2).

Stel dat u de eerste keer dat een geel item voorkomt in een bereik binnen kolom B wilt zoeken. Gebruik deze formule:

=XMATCH(“Geel”, B2:B6, 0)

Laatste gedachten

Dit spiekbriefje heeft een breed scala aan functies behandeld, van statistische analyse, opzoekformules, technieken voor gegevensmanipulatie en strategieën voor foutafhandeling.

De verstrekte voorbeelden en uitleg helpen deze geavanceerde formules te demystificeren, waardoor ze zelfs toegankelijk zijn voor mensen met beperkte ervaring.

Naarmate u ze begint op te nemen in uw Excel-taken, bent u op weg om uw Excel-vaardigheden naar een hoger niveau te tillen.

Maar vergeet niet dat dit spiekbriefje slechts het topje van de ijsberg is. De echt verbazingwekkende dingen gebeuren als je creatief wordt, deze formules mixt en matcht en ze aanpast om jouw unieke uitdagingen op te lossen. Excel is als een canvas, en deze formules zijn jouw palet - dus ga je gang, schilder je meesterwerk!