Cheatsheet voor Excel-formules: gids voor gevorderden

Cheatsheet voor Excel-formules: gids voor gevorderden

Dus je bent erin geslaagd om de basis van Excel onder de knie te krijgen - een pluim voor jou! Nu heb je besloten om de zaken een tandje bij te zetten en in de meer geavanceerde dingen te duiken, toch? Nou, je hebt geluk, want we hebben speciaal voor jou een handig tussentijds Excel-spiekbriefje samengesteld!

Dit spiekbriefje voor Microsoft Excel-tussenpersonen behandelt een breed scala aan onderwerpen die uw vaardigheden op het gebied van numerieke analyse, tekstverwerking en complexe datum- en tijdlogica naar een hoger niveau zullen tillen.

Door de Excel Intermediate Cheat Sheet als desktopreferentie te hebben, kunt u diep in de krachtigere formules en functies van Microsoft Excel duiken.

Lees nu zeker verder, maar print ook het spiekbriefje hieronder uit en bewaar het!

Laten we beginnen!

Excel-formules-spiekbriefje-tussenpersoon-1 Download

Inhoudsopgave

Intermediaire wiskundige formules

Ons spiekbriefje voor beginners behandelde de meest voorkomende wiskundige functies voor beginners, zoals de SUM-functie. Gevorderde gebruikers moeten vertrouwd raken met deze complexere functies:

  • ABS() retourneert de absolute waarde van een getal.

  • WORTEL() retourneert de vierkantswortel van een getal

  • RAND() levert een willekeurig getal tussen 0 en 1

Voorwaardelijke logica

Gevorderde gebruikers moeten ook weten hoe ze wiskundige berekeningen moeten uitvoeren op basis van meerdere voorwaarden met behulp van deze functies:

  • SOMMEN(som_bereik; criteria_bereik1; criteria1; [….])

  • AANTALLEN.ALS(criteria_bereik1, criteria1, [criteria_bereik2, criteria2], …)

  • GEMIDDELDEN.ALS(criteria_bereik1, criteria1, [criteria_bereik2, criteria2], …)

De functies evalueren de verstrekte criteria en produceren een logische waarde TRUE of FALSE.

Stel dat u de volgende verkoopgegevens in de kolommen A, B en C heeft:

Cheatsheet voor Excel-formules: gids voor gevorderden

Gebruik de SUMIFS-formule om de som van de verkopen voor rode t-shirts te berekenen:

=SOMMEN.ALS(C2:C6, A2:A6, "T-shirt", B2:B6, "Rood")

Gebruik de formule AANTALLEN.ALS om het aantal rijen met rode t-shirts te tellen:

=AANTAL.ALS(C2:C6; A2:A6; "T-shirt", B2:B6; "Rood")

Gebruik de formule GEMIDDELDE.ALS om de gemiddelde verkoop van rode t-shirts te berekenen:

=GEMIDDELDEN.ALS(C2:C6, A2:A6, "T-shirt", B2:B6, "Rood")

Tussenliggende statistische formules

Naast basisfuncties zoals MIN en MAX, heeft Excel een breder scala aan statistische formules voor intermediaire gebruikers. Hier zijn enkele van de meest bruikbare:

  • MIN

  • MAXA

  • AANTAL

  • AANTAL.ALS

1. MINA en MAXA

De vaker gebruikte MIN- en MAX-functies negeren tekstwaarden.

De alternatieve MINA- en MAXA-functies houden rekening met zowel tekst als getallen bij het zoeken naar de hoogste of laagste waarden. Tekstwaarden worden geëvalueerd alsof ze nul zijn.

De verschillen zijn weergegeven in onderstaande afbeelding. De eerste rij evalueert een minimumwaarde van 10, terwijl de tweede rij evalueert als 0 vanwege de aanwezigheid van een tekstwaarde.

2. AANTALA en AANTALALS

De functie AANTALA wordt gebruikt om het aantal niet-lege cellen in een bereik te tellen.

De functie AANTAL.ALS is minder specifiek omdat deze wordt gebruikt om het aantal cellen binnen een bereik te tellen dat aan een specifieke voorwaarde of criteria voldoet.

U kunt deze functies ook gebruiken om het aantal afzonderlijke waarden in een kolom te tellen. Deze video laat zien hoe je dat doet.

Excel-formules voor financiële analyse

Er zijn verschillende formules die u wilt gebruiken bij het uitvoeren van financiële analyses, zoals het voorspellen van investeringen:

  • PRODUCT(getal1, [getal2…])

  • QUOTIËNT(teller; noemer)

  • LOG(getal; [basis])

1. PRODUCT-functie

Stel dat u de toekomstige waarde van een initiële investering van $ 1.000 (cel B1) wilt berekenen tegen een jaarlijkse rentevoet van 4% (cel B2) na vijf jaar (cel B3).

Gebruik de functie PRODUCT om de toekomstige waarde te berekenen met deze formule:

=B1 * PRODUCT(1 + B2)^B3

Deze formule geeft de toekomstige waarde van uw investering terug.

2. QUOTIENT- en LOG-functies

De volgende stap is om te berekenen hoeveel jaar het duurt voordat een investering in waarde verdubbelt bij de gegeven rente.

Gebruik de QUOTIENT-functie in combinatie met de LOG-functie om dit als volgt te berekenen:

=QUOTIENT(LOGB(2) / LOGB(1 + B2), 1)

Deze formule retourneert 17 met de voorbeeldgegevens, wat aangeeft dat het 17 jaar zal duren voordat de investering in waarde is verdubbeld bij een jaarlijkse rente van 4%.

Deze afbeelding toont de formules in een Excel-werkblad :

Cheatsheet voor Excel-formules: gids voor gevorderden

Functies voor investeringsscenario's

Dit zijn enkele functies die geschikt zijn voor specifieke financiële scenario's. Een van hen is misschien precies wat u nodig heeft:

  • NPV - De NPV- functie (Net Present Value) berekent de netto contante waarde van een investering op basis van een reeks toekomstige kasstromen op basis van een disconteringsvoet.

  • ACCRINT - De ACCRINT- functie berekent de opgebouwde rente van een effect dat periodieke rente betaalt. Dit is handig voor het bepalen van de rente die op een waardepapier is verdiend vanaf de laatste betalingsdatum tot een bepaalde afwikkelingsdatum.

  • INTRATE – De functie INTRATE berekent de rentevoet voor een volledig belegd effect.

  • PMT – De PMT- functie berekent de totale betaling op een schuldbewijs.

  • IRR - De IRR- functie geeft de interne koers van een rendement.

  • RENDEMENT – De RENDEMENT- functie geeft het rendement van een waardepapier op basis van rente, nominale waarde en looptijd.

Gemiddelde Excel-datum- en tijdformules

De basisfuncties voor datum en tijd in Excel omvatten de functies NU en VANDAAG voor de huidige datum. Gemiddelde gebruikers moeten ook weten hoe ze componenten van een bepaalde datum kunnen extraheren met behulp van:

  • Dag datum)

  • Maand datum)

  • JAAR(datum)

De formule =MAAND(“23 april 2023″) retourneert een resultaat van 4 voor de 4e maand. Evenzo zullen de functies DAG en JAAR respectievelijk 23 en 2023 retourneren.

Cheatsheet voor Excel-formules: gids voor gevorderden

1. Tussenliggende maandfuncties

Gemiddelde gebruikers hebben soms te maken met het optellen of aftrekken van maanden bij een datum en het vinden van het einde van een maand.

  • EDATE(begindatum; aantal_maanden)

  • EOMONTH(begindatum; aantal_maanden)

De formule =EDATE(“23 april 2023”, 2) berekent bijvoorbeeld de datum twee maanden later.

De formule =EOMONTH(23 april 2023”, 2) berekent het einde van de maand twee maanden later.

Het resultaat is “30 juni 2023”, rekening houdend met het feit dat juni slechts dertig dagen telt. Als u 3 maanden opgeeft, wordt het resultaat "31 juli 2023".

Cheatsheet voor Excel-formules: gids voor gevorderden

2. Tussenweekfuncties

U kunt een combinatie van de functies SUM en WEEKDAY gebruiken om het aantal hele werkdagen binnen een datumbereik te tellen.

De functie WEEKDAY retourneert de dag van de week voor een bepaalde datum. De tweede parameter bepaalt het nummeringssysteem met standaard zondag tot zaterdag.

Als u een reeks datums heeft in een celbereik van B1:B40, gebruikt u deze formule om de werkdagen te berekenen:

=SOM(–(WEEKDAG(B1:B40,2)>5))

3. Tussentijdse functies

De JEARFRAC-functie berekent de fractie van een jaar tussen twee datums.

De functie TIJDWAARDE wordt gebruikt om een ​​tijd weergegeven als tekst om te zetten in een decimaal getal dat de proportie van een dag van 24 uur vertegenwoordigt.

Het resultaat is een Excel-serienummer waarbij 1 staat voor een volledige dag van 24 uur, 0,5 voor 12 uur, 0,25 voor 6 uur, enzovoort.

Stel dat u een tijd in tekstformaat "17:45" in cel A1 heeft en u wilt deze omzetten in een decimaal getal. Dit is de formule:

=TIJDWAARDE(A1)

De waarde 0,74 staat voor het deel van de 24-urige dag dat een kwartier voor zes uur voorbij is gegaan.

Ga naar om de tijdcel te wijzigen van een tijdnotatie naar een tekstnotatie. Misschien wilt u dit voor de duidelijkheid opmaken als een percentage.

Cheatsheet voor Excel-formules: gids voor gevorderden

Excel-functies voor voorwaardelijke en logische algebra

Voorwaardelijke en logische functies zijn essentieel voor de besluitvorming in Excel. De volgende functies kunnen worden gecombineerd met de meer basale Excel-formules voor krachtige logica:

  1. ALS(voorwaarde, waarde_als_waar, waarde_als_onwaar):

  2. EN(voorwaarde1; voorwaarde2; …)

  3. OF(voorwaarde1, voorwaarde2, …)

  4. NIET(voorwaarde1, voorwaarde2, …)

De ALS-functie evalueert een voorwaarde en retourneert verschillende waarden, afhankelijk van of de voorwaarde waar of onwaar is.

De AND-, OR- en NOT-functies controleren welke voorwaarden waar zijn en laten u dienovereenkomstig beslissingen nemen.

Hier zijn enkele voorbeelden op basis van de verkoopgegevens die we eerder gebruikten.

1. IF-functie

Stel dat u de tekst "Lage verkopen" wilt weergeven als de verkopen voor de artikelen minder dan 15 zijn. Als de verkopen hoger zijn, wilt u "Hoge verkopen" weergeven in een nieuwe kolom.

Gebruik deze formule en kopieer deze naar de andere rijen:

=ALS(C2<15, "Lage verkopen", "Hoge verkopen")

2. EN-functie

Stel dat u WAAR of ONWAAR wilt weergeven voor t-shirts met ten minste 15 verkopen. Gebruik deze formule:

=EN(A3=”T-shirt”, C3>=15)

3. OF-functie

Stel dat u meerdere voorwaarden wilt testen en WAAR wilt retourneren als aan een van de voorwaarden is voldaan en anders ONWAAR, dan gebruikt u de functie OF.

Als we bijvoorbeeld scores hebben van een test in cellen A1 en B1, en we willen weten of een van beide scores hoger is dan 80, kunnen we gebruiken: =OR( A1>80, B1>80) . Dit retourneert TRUE als een van de scores (of beide) hoger is dan 80, en FALSE als beide 80 of lager zijn.

4. NIET-functie

Stel dat je wilt checken of het item geen hoodie is. Gebruik deze formule:

=NIET(A2=”Hoodie”)

Cheatsheet voor Excel-formules: gids voor gevorderden

Verwijzen naar cellen

Een beginner zal snel vertrouwd raken met het gebruik van absolute en relatieve referenties zoals $A$1 of B2. Gevorderde gebruikers moeten vertrouwd raken met het gebruik van indirecte verwijzingen, indexen en offsets:

  1. INDIRECT(verwijzing_tekst)

  2. INDEX(bereik, rij_getal, kolom_getal)

  3. OFFSET(referentie_cel, rijen, kolommen):

1. INDIRECTE Functie

De functie INDIRECT retourneert de waarde van de opgegeven celverwijzing die als tekst is ingevoerd. Gebruik deze formule om naar cel B3 te verwijzen:

=INDIRECT(“B3”)

Het voordeel boven het gebruik van eenvoudige celverwijzingen is dat deze functie u een dynamische verwijzing geeft. Dit betekent dat Excel de referentie automatisch bijwerkt wanneer de structuur van het werkblad verandert, bijvoorbeeld als een rij wordt verwijderd.

2. INDEX-functie

De INDEX-functie wordt gebruikt om te verwijzen naar cellen binnen een opgegeven bereik op basis van rij- en kolomnummers.

Gebruik deze formule om te verwijzen naar cel B3 in een tabel van zes rijen:

=INDEX(B1:B6; 3)

3. OFFSET-functie

De functie VERSCHUIVING retourneert een cel of bereik dat een opgegeven aantal rijen en kolommen verwijderd is van een referentiecel.

Gebruik deze formule als u de celwaarde wilt weergeven die twee cellen tegenover en één cel lager is dan A1:

=VERSCHUIVING(A1, 2, 1)

Dit zijn de formules in actie:

Cheatsheet voor Excel-formules: gids voor gevorderden

Tussenliggende tekstformules

Beginners zouden tekstfuncties zoals LINKS moeten kennen, waarmee een of meer tekens aan de linkerkant van een tekenreeks worden geëxtraheerd. Een hoofdgebruiker moet bekend zijn met functies als deze:

  1. TEXTJOIN(scheidingsteken, negeer_leeg, tekst1, [tekst2, …])

  2. REPLACE(oude_tekst, begin_getal, aantal_tekens, nieuwe_tekst)

  3. VERVANGEN(tekst, oude_tekst, nieuwe_tekst, [instantie_getal])

1. TEXTJOIN-functie

De functie TEXTJOIN voegt cellen samen met het scheidingsteken dat u opgeeft. U kunt ook aangeven waar lege cellen moeten worden genegeerd.

Gebruik deze formule om een ​​door komma's gescheiden lijst van enkele items in kolom A te maken:

=TEKSTJOIN(“, “, WAAR, A2:A4)

2. VERVANG Functie

Met de functie Replace kunt u de startpositie en lengte specificeren van de string die u naar het doel wilt verwisselen.

Gebruik deze formule om de eerste twee tekens van een tekenreeks te vervangen door "XX":

=TEKSTJOIN(A2, 1, 2, "XX")

3. VERVANGEN-functie

De functie vervangen vervangt gespecificeerde exemplaren van een tekstreeks binnen een andere tekstreeks door een nieuwe tekstreeks.

Gebruik deze formule om het woord "Jeans" te vervangen door "Leggings":

=VERVANGEN(A3, "Jeans", "Leggings")

Dit zijn de formules in actie:

Cheatsheet voor Excel-formules: gids voor gevorderden

Intermediaire opzoekformules

Algemene opzoekfuncties zoals VLOOKUP en MATCH worden behandeld in het spiekbriefje voor beginners.

Gevorderde gebruikers moeten vertrouwd raken met de CHOOSE-opzoekfunctie:

  • KIEZEN(index_getal; waarde1; [waarde2; …])

De functie KIEZEN retourneert een waarde uit een zoeklijst op basis van een opgegeven indexnummer.

Stel dat u een kolom met T-shirtmaten heeft met het label 1, 2 of 3. U wilt een categorie van respectievelijk small, medium en large in dezelfde rij weergeven. Gebruik deze formule:

=KIES(A1, "Klein", "Medium", "Groot")

Formules voor het afhandelen van Microsoft Excel-fouten

De cheatsheet voor beginners somde de typische foutmeldingen op die u zult zien wanneer u met Excel werkt. Gemiddelde gebruikers zouden formules moeten kunnen gebruiken om de fouten netjes af te handelen.

  1. ALS.FOUT(waarde; als_fout)

  2. IFNA(waarde; waarde_als_nvt)

1. IFERROR-functie

Fouten bij deling door nul komen vaak voor, maar Excel's standaardweergave van #DEEL/0! zal niet voor iedere gebruiker duidelijk zijn.

Gebruik de IFERROR-functie om de standaardfout met "Kan niet delen door nul" te vervangen door deze formule:

=ALS.FOUT(A1/B1; "Kan niet delen door nul")

Deze afbeelding toont de onbewerkte fout in de derde rij en de IFERROR-functie die de vierde rij afhandelt.

Cheatsheet voor Excel-formules: gids voor gevorderden

2. IFNA-functie

De IFNA-functie in Excel wordt gebruikt om #N/A-fouten op te vangen en af ​​te handelen.

Een ander deel van dit spiekbriefje toonde de KIES-functie. Deze functie retourneert een N/A-fout als het indexnummer geen geheel getal is.

Stel dat een t-shirtmaat is ingevoerd als 1,5, waarbij de gebruiker ten onrechte denkt dat dit een categorie ergens tussen small en medium zal weergeven. In plaats daarvan wordt een #N/A- fout weergegeven.

Gebruik deze formule om een ​​meer nuttige fout te geven:

=ALSNA(KIES(A2, "Laag", "Gemiddeld", "Hoog"), "Ongeldige index")

Sneltoetsen voor formules en functies

Er zijn verschillende sneltoetsen waarmee u efficiënter met uw werkblad kunt werken.

  • F2 : Bewerk de actieve cel en plaats de invoegpositie aan het einde van de celinhoud.

  • F9 : Bereken en toon het resultaat van het geselecteerde deel van een formule.

  • Ctrl + Shift + Enter : Voer een matrixformule in.

  • Shift + F3 : Open het dialoogvenster Functie invoegen.

  • Esc : annuleer de invoer van een formule en keer terug naar de oorspronkelijke celinhoud.

Sommige van deze Excel-sneltoetsen zijn mogelijk niet in alle talen of toetsenbordindelingen beschikbaar.

Intermediaire celopmaak

Bij financiële analyse is de juiste opmaak van cellen met getallen, datums en valuta cruciaal. Volg deze stappen om cellen op te maken:

  1. Selecteer de cellen die u wilt opmaken.

  2. Klik met de rechtermuisknop op de geselecteerde cellen en kies "Cellen opmaken".

  3. Selecteer de juiste categorie (bijv. Getal, Valuta, Datum) en pas het gewenste formaat toe.

Enkele veelvoorkomende opmaaktypen die u in de financiële wereld kunt overwegen, zijn:

  • Valuta : $ 1.234,56

  • Percentage : 12,34%

  • Boekhouding : ($ 1.234,00)

  • Datum : 10 mei 2023

Laatste gedachten

Naarmate u meer vertrouwd raakt met de verschillende formules en functies die in dit spiekbriefje worden uitgelicht , zult u merken dat u beter toegerust bent om steeds complexere taken en projecten aan te kunnen.

Oefening is de sleutel tot het beheersen van elke vaardigheid, dus wees niet bang om met deze formules te experimenteren in uw dagelijkse werk. Ze brengen je expertise van beginnersniveau naar gemiddeld niveau.

Dit is slechts een voorproefje van wat de tussentijdse functies van Excel kunnen doen om uw werk te vereenvoudigen, uw analyses te stroomlijnen en u te helpen inzichten uit uw gegevens te halen.

De echte magie vindt plaats wanneer u de functies in ons spiekbriefje begint te combineren en ze aanpast aan uw unieke behoeften. Stop hier dus niet. Blijf ontdekken, blijf experimenteren en je zult ontdekken dat Excel niet alleen een tool is, het is een game-changer!

Heb je het gevoel dat je dingen naar een hoger niveau wilt tillen? Bekijk onze Excel geavanceerde formules Cheat Sheet.


Wat is zelf in Python: voorbeelden uit de echte wereld

Wat is zelf in Python: voorbeelden uit de echte wereld

Wat is zelf in Python: voorbeelden uit de echte wereld

Een RDS-bestand opslaan en laden in R

Een RDS-bestand opslaan en laden in R

Je leert hoe je objecten uit een .rds-bestand in R opslaat en laadt. In deze blog wordt ook besproken hoe je objecten uit R naar LuckyTemplates importeert.

First N Business Days Revisited – Een DAX-coderingstaaloplossing

First N Business Days Revisited – Een DAX-coderingstaaloplossing

In deze tutorial over DAX-coderingstaal leert u hoe u de functie GENERATE gebruikt en hoe u de titel van een maat dynamisch wijzigt.

Breng inzichten onder de aandacht met behulp van de Multi Threaded Dynamic Visuals-techniek in LuckyTemplates

Breng inzichten onder de aandacht met behulp van de Multi Threaded Dynamic Visuals-techniek in LuckyTemplates

Deze zelfstudie behandelt hoe u de Multi Threaded Dynamic Visuals-techniek kunt gebruiken om inzichten te creëren op basis van dynamische gegevensvisualisaties in uw rapporten.

Inleiding tot het filteren van context in LuckyTemplates

Inleiding tot het filteren van context in LuckyTemplates

In dit artikel zal ik de filtercontext doornemen. Filtercontext is een van de belangrijkste onderwerpen waarover elke LuckyTemplates-gebruiker in eerste instantie zou moeten leren.

Beste tips voor het gebruik van de apps in LuckyTemplates Online Service

Beste tips voor het gebruik van de apps in LuckyTemplates Online Service

Ik wil laten zien hoe de online service LuckyTemplates Apps kan helpen bij het beheren van verschillende rapporten en inzichten die uit verschillende bronnen zijn gegenereerd.

Analyseer winstmargeveranderingen in de loop van de tijd - analyse met LuckyTemplates en DAX

Analyseer winstmargeveranderingen in de loop van de tijd - analyse met LuckyTemplates en DAX

Leer hoe u wijzigingen in uw winstmarge kunt berekenen met behulp van technieken zoals vertakking van metingen en het combineren van DAX-formules in LuckyTemplates.

Materialisatie-ideeën voor gegevenscaches in DAX Studio

Materialisatie-ideeën voor gegevenscaches in DAX Studio

Deze tutorial bespreekt de ideeën van materialisatie van datacaches en hoe deze de prestaties van DAX beïnvloeden bij het leveren van resultaten.

Zakelijke rapportage met behulp van LuckyTemplates

Zakelijke rapportage met behulp van LuckyTemplates

Als u tot nu toe nog steeds Excel gebruikt, is dit het beste moment om LuckyTemplates te gaan gebruiken voor uw zakelijke rapportagebehoeften.

Wat is LuckyTemplates Gateway? Alles wat u moet weten

Wat is LuckyTemplates Gateway? Alles wat u moet weten

Wat is LuckyTemplates Gateway? Alles wat u moet weten