Een formule definiëren en maken

In deze les introduceren we u basisregels voor het maken van formules en het gebruik van functies. We zijn van mening dat een van de beste manieren om te leren het is om te oefenen, dus we geven verschillende voorbeelden en leggen deze in detail uit. De onderwerpen die we zullen behandelen zijn:

SCHOOLNAVIGATIE

Waarom heeft u formules en functies nodig??
Een formule definiëren en maken
Relatieve en absolute celverwijzing en opmaak
Handige functies die u moet leren kennen
Opzoeklijsten, grafieken, statistieken en draaitabellen

rijen en kolommen
voorbeeld wiskunde functie: SUM ()
operators
operator voorrang
voorbeeld financiële functie: PMT (), uitbetaling van een lening
gebruik van een "string" -functie ("string" is een afkorting voor "reeks van tekst") in een formule en nesten functies

Formules zijn een mengeling van 'functies', 'operators' en 'operands'. Voordat we een paar formules schrijven, moeten we een functie maken, maar voordat we een functie kunnen maken, moeten we eerst de rij- en kolomnotatie begrijpen.

Rijen en kolommen

Als u wilt begrijpen hoe u formules en functies schrijft, moet u informatie hebben over rijen en kolommen.

Rijen worden horizontaal uitgevoerd en kolommen lopen verticaal. Om te onthouden welke is, denk aan een kolom die een dak omhoog houdt - kolommen gaan omhoog en rijen gaan naar links en rechts.

Kolommen worden gelabeld door letters; rijen voor nummers. De eerste cel in de spreadsheet is A1, dit betekent kolom A, rij 1. De kolommen hebben het label A-Z. Wanneer het alfabet op is, plaatst Excel nog een letter vooraan: AA, AB, AC ... AZ, BA, BC, BC, enz.

Voorbeeld: functie som ()

Laten we nu eens laten zien hoe een functie te gebruiken.

U gebruikt functies door ze rechtstreeks in of met behulp van de functie-wizard in te voeren. De functiewizard wordt geopend wanneer u een functie kiest in het menu "Formules" van de "Functiebibliotheek". Anders kunt u = in een cel typen en een handig vervolgkeuzemenu geeft u de mogelijkheid om een functie te selecteren.

De wizard vertelt u welke argumenten u voor elke functie moet opgeven. Het biedt ook een link naar online-instructies als u hulp nodig hebt om te begrijpen wat de functie doet en hoe u deze moet gebruiken. Als u bijvoorbeeld = sum in een cel typt, toont de in-line wizard u welke argumenten nodig zijn voor de SOM-functie.

Wanneer u een functie typt, staat de wizard in lijn of recht op uw vingers. Wanneer u een functie selecteert in het menu "Formules", is de wizard een pop-upvenster. Hier is de pop-upwizard voor de functie SUM ().

Laten we voor onze eerste functie SUM () gebruiken, die een lijst met getallen toevoegt.

Stel dat we deze spreadsheet hebben om plannen te bevatten voor het budgetteren van de vakantie van uw gezin:

Om de totale kosten te berekenen, kunt u schrijven = b2 + b3 + b4 + b5 maar het is eenvoudiger om de functie SOM () te gebruiken.

Zoek in Excel naar het symbool Σ in de linkerbovenhoek van het Excel-scherm om de knop AutoSum te vinden (wiskundigen gebruiken de Griekse letter Σ voor het toevoegen van een reeks getallen).

Als de cursor lager is dan de gezinsbudgetnummers, is Excel slim genoeg om te weten dat u de lijst met nummers hierboven wilt optellen waar u de cursor hebt geplaatst, zodat de cijfers worden gemarkeerd.

Druk op "enter" om het door Excel geselecteerde bereik te accepteren of gebruik de cursor om te wijzigen welke cellen zijn geselecteerd.

Als u kijkt naar wat Excel in de spreadsheet heeft geplaatst, ziet u dat deze deze functie heeft geschreven:

In deze formule telt Excel de getallen van B2 tot B9. Let op, we hebben wat ruimte onder rij 5 achtergelaten, zodat je aan het gezinsvakantiebudget kunt toevoegen - de kosten zullen zeker stijgen als de kinderlijst van wat ze willen doen en waar ze naartoe willen langer worden!

Wiskundige functies werken niet met letters, dus als u letters in de kolom plaatst, wordt het resultaat weergegeven als "#NAAM?" Zoals hieronder wordt getoond.

#NAAM? geeft aan dat er een fout is opgetreden. Het kan een aantal dingen zijn, waaronder:

slechte celverwijzing
letters gebruiken in wiskundige functies
vereiste argumenten weglaten
spelfunctie naam fout
illegale wiskundige bewerkingen zoals delen door 0

De eenvoudigste manier om de argumenten in een berekening te selecteren, is om de muis te gebruiken. U kunt toevoegen aan of verwijderen uit de lijst met argumenten voor de functie door het vak groter of kleiner te maken dat Excel tekent wanneer u de muis verplaatst of in een andere cel klikt.

We hebben op de bovenkant van het vierkant geklikt dat door Excel is getekend om 'vliegtickets' buiten het budget te houden. U kunt het dradenkruis-symbool zien dat u kunt tekenen om het geselecteerde bereik groter of kleiner te maken.

Druk op "enter" om de resultaten te bevestigen.

Rekenoperatoren

Er zijn twee soorten operators: wiskunde en vergelijking.

Math Operator	Definitie
+	toevoeging
-	aftrekken of negatie, bijvoorbeeld 6 * -1 = -6
*	vermenigvuldiging
/	divisie
%	procent
^	exponent, b.v. 2⁴= 2 ^ 4 = 2 * 2 * 2 * 2 = 16

Er zijn andere operatoren die geen verband houden met wiskunde zoals "&", wat betekent dat twee snaren worden samengevoegd (join end-to-end). Bijvoorbeeld: "Excel" en "Fun" is gelijk aan "Excel is leuk".

Nu kijken we naar vergelijkingsoperatoren.

Vergelijkingsoperator	Definitie
=	gelijk aan bijvoorbeeld 2 = 4 of "b" = "b"
>	groter dan, bijvoorbeeld 4> 2 of "b"> "a"
<	minder dan bijvoorbeeld 2 < 4 or “a” < “b”
> =	groter dan of gelijk aan - een andere manier om hieraan te denken is> = betekent een van beide > of =.
<=	minder dan of gelijk aan.
	niet gelijk aan bijvoorbeeld 46

Zoals je hierboven kunt zien, werken vergelijkingsoperatoren met getallen en tekst.

Let op: als u = "a"> "b" in een cel invoert, zal het "ONWAAR" zeggen, aangezien "a" niet groter is dan "b." "B" komt na "a" in het alfabet, dus "a" > "B" of "B"> "a."

Prioriteit operatororder

Orderprioriteit is een idee uit de wiskunde. Excel moet dezelfde regels volgen als wiskunde. Dit onderwerp is ingewikkelder, dus adem even in en duik erin.

Orderprioriteit betekent de volgorde waarin de computer het antwoord berekent. Zoals we in les 1 hebben uitgelegd, is het gebied van een cirkel πr², wat hetzelfde is als π * r * r. Het is niet (Πr)².

U moet dus de prioriteit van de volgorde begrijpen wanneer u een formule schrijft.

Over het algemeen kun je dit zeggen:

Excel evalueert eerst items tussen haakjes die binnenstebuiten werken.
Vervolgens worden de regels voor voorrang van de volgorde van wiskunde gebruikt.
Wanneer twee items dezelfde prioriteit hebben, werkt Excel van links naar rechts.

De voorrang van wiskundige operators wordt hieronder weergegeven, in aflopende volgorde.

(en)	Wanneer haakjes worden gebruikt, overschrijven ze de normale voorrangsregels. Dit betekent dat Excel deze berekening eerst zal uitvoeren. We leggen dit hieronder verder uit.
-	Ontkenning, bijv. -1. Dit is hetzelfde als het vermenigvuldigen van een getal met -1. -4 = 4 * (-1)
%	Percentage betekent vermenigvuldigd met 100. Bijvoorbeeld, 0,003 = 0,3%.
^	Exponent, bijvoorbeeld 10 ^ 2 = 100
* en /	Vermenigvuldigen en delen. Hoe kunnen twee operatoren dezelfde prioriteit hebben? Het betekent alleen dat als een formule twee extra operators heeft met dezelfde prioriteit, de berekening van links naar rechts wordt uitgevoerd.
+ en -	Optellen en aftrekken.

Er zijn andere voorrangsregels die betrekking hebben op strings en referentie-operators. Voorlopig houden we vast aan wat we zojuist hebben behandeld. Laten we nu eens naar enkele voorbeelden kijken.

Voorbeeld: het gebied van een cirkel berekenen

Het gebied van een cirkel is= PI () * straal ^ 2.

Als we naar de bovenstaande tabel kijken, zien we dat exponenten vóór vermenigvuldiging komen. Dus de computer berekent eerst de straal ^ 2 en vermenigvuldigt vervolgens dat resultaat met Pi.

Voorbeeld: berekening van een salarisverhoging

Laten we zeggen dat je baas beslist dat je het geweldig doet en hij of zij je 10% verhoogt! Hoe zou u uw nieuwe salaris berekenen??

Ten eerste onthoud dat vermenigvuldiging vóór toevoeging komt.

Is het = salaris + salaris * 10% of is het = salaris + (salaris * 10%)?

Stel dat uw salaris $ 100 is. Met een verhoging van 10% wordt uw nieuwe salaris:

= 100 + 100 * 10% = 100 + 10 = 110

Je kunt het ook zo schrijven:

= 100 + (100 * 10%) = 100 + 10 = 110

In het tweede geval hebben we de volgorde van prioriteit expliciet gemaakt door haakjes te gebruiken. Onthoud dat haakjes vóór een andere bewerking worden beoordeeld.

Overigens, de gemakkelijkste manier om dit te schrijven is = salaris * 110%

Haakjes kunnen in elkaar worden genest. Dus als we schrijven (3 + (4 * 2)), werkt het van binnen naar buiten berekenen eerst 4 * 2 = 8, voeg dan 3 + 8 toe om 11 te krijgen.

Nog een paar voorbeelden

Hier is nog een voorbeeld: = 4 * 3 / 2. Wat is het antwoord??

We zien aan de regels in de bovenstaande tabel dat * en / dezelfde prioriteit hebben. Dus Excel werkt van links naar rechts, 4 * 3 = 12 eerst, en deelt dat vervolgens met 2 om 6 te krijgen.

Wederom zou je dat expliciet kunnen maken door te schrijven = (4 * 3) / 2

Hoe zit het met = 4 + 3 * 2?

De computer ziet zowel * als + operators. Dus na het volgen van de voorrangsregels (vermenigvuldiging komt vóór optelling) berekent het eerst 3 * 2 = 6, dan voegt het 4 toe om 10 te krijgen.

Als u de volgorde van de prioriteit wilt wijzigen, schrijft u = (4 + 3) * 2 = 14.

Hoe zit het met deze = -1 ^ 3?

Dan is het antwoord -3 omdat de computer berekend = (-1) ^ 3 = -1 * -1 * -1 = -1.

Onthoud dat negatieve tijden negatief positief zijn en een negatieve tijd dat een positief negatief is. Je kunt dit als volgt zien (-1 * -1) * -1 = 1 * -1 = -1.

Dus er zijn een paar voorbeelden van wiskundige volgorde en voorrang, we hopen dat dit helpt om een paar dingen duidelijk te maken over hoe Excel berekeningen uitvoert (en dat is waarschijnlijk genoeg wiskunde om een leven lang mee te gaan voor sommigen van jullie).

Voorbeeld: functie leenbetaling (PMT)

Laten we een voorbeeld bekijken om een uitbetaling van een lening te berekenen.

Begin met het maken van een nieuw werkblad.

Formatteer de getallen met dollartekens en gebruik nul decimalen, omdat we nu niet geïnteresseerd zijn in centen, omdat ze er niet veel toe doen als je over dollars praat (in het volgende hoofdstuk bekijken we hoe je getallen in detail kunt opmaken). Als u bijvoorbeeld de rentevoet wilt opmaken, klikt u met de rechtermuisknop op de cel en klikt u op 'cellen opmaken'. Kies een percentage en gebruik twee plaatsen na de komma.

Formatteer ook de andere cellen voor "valuta" in plaats van percentage en kies "nummer" voor de uitleentermijn.

Nu hebben we:

Voeg de functie SOM () toe aan de "totale" maandelijkse kosten.

Merk op hypotheek cel is niet inbegrepen in het totaal. Excel weet niet dat u dat nummer wilt opnemen, omdat er geen waarde is. Dus wees voorzichtig om de SUM () functie naar de top uit te breiden, hetzij door de cursor te gebruiken, of door E2 te typen waar het E3 vermeldt om de hypotheek in de som op te nemen.

Plaats de cursor in de betalingscel (B4).

Selecteer in het menu Formules de vervolgkeuzelijst "Financieel" en selecteer vervolgens de PMT-functie. De wizard verschijnt:

Gebruik de cursor om "rate", "nper" (leentermijn), "Pv" ("contante waarde" of geleende hoeveelheid) te selecteren. Merk op dat u de rentevoet met 12 moet delen omdat de rente maandelijks wordt berekend. Ook moet u de looptijd van de lening in jaren vermenigvuldigen met 12 om de looptijd van de lening in maanden te krijgen. Druk op "OK" om het resultaat in het werkblad op te slaan.

Merk op dat de betaling wordt weergegeven als een negatief getal: -1013.37062. Om het positief te maken en toe te voegen aan de maandelijkse uitgaven, wijs naar de hypotheekcel (E2). Typ "= -" en gebruik vervolgens de cursor om naar het betalingsveld te wijzen. De resulterende formule is = -B4.

Nu ziet de spreadsheet er als volgt uit:

Uw maandelijkse uitgaven zijn $ 1.863 - Ouch!

Voorbeeld: tekstfunctie

Hier laten we zien hoe functies binnen een formule en tekstfuncties kunnen worden gebruikt.

Stel dat je een lijst met studenten hebt, zoals hieronder getoond. De voor- en achternaam bevinden zich in een veld gescheiden door een komma. We moeten de laatste en definitieve namen in afzonderlijke cellen plaatsen. Hoe doen we dit?

Om dit probleem aan te pakken, moet u een algoritme gebruiken, d.w.z. een stapsgewijze procedure om dit te doen.

Kijk bijvoorbeeld naar "Washington, George." De procedure om dat in twee woorden te splitsen zou zijn:

Bereken de lengte van de string.
Zoek de positie van de komma (dit geeft aan waar het ene woord eindigt en het andere begint).
Kopieer de linkerkant van de draad tot de komma.
Kopieer de rechterkant van de string van de komma naar het einde.

Laten we bespreken hoe u dit met stap-voor-stap in Excel kunt doen met "George Washington".

Bereken de lengte van de string met de functie = LEN (A3) - het resultaat is 18.
Zoek nu de positie van de komma door deze functie = FIND (",", A3 ") in te voeren - het resultaat is 11.
Neem nu de linkerzijde van de string tot de komma en maak deze geneste formule met behulp van het resultaat uit stap 1: = LEFT (A3, FIND (",", A3) -1). Let op, we moeten 1 van de lengte aftrekken omdat FIND de positie van de komma aangeeft.

Dit is hoe dat allemaal eruit ziet als alle functies samen in een formule worden geplaatst. In cel B3 kun je zien dat deze formule alle informatie uit cel A3 neemt en "Washington" daarin invoert.

Dus we hebben "Washington", nu moeten we "George" krijgen. Hoe doen we dit?

Merk op dat we het resultaat van Stap 1 in een cel alleen hadden kunnen opslaan, bijvoorbeeld B6, en vervolgens een eenvoudiger formule = LINKS (A3, B6-1) hebben geschreven. Maar dat verbruikt één cel voor de intermitterende stap.

Denk aan de positie van de komma of bereken hem opnieuw.
Bereken de lengte van de string.
Tel de karakters vanaf het einde van de string tot aan de komma.

Neem het aantal tekens uit stap 3 en trek er een af om de komma en spatie weg te laten.

Laten we dit stap voor stap doen.

Van boven is dit = FIND (",", A3 ")
De lengte van de string is = LEN (A3)
U moet wat wiskunde gebruiken om het aantal te gebruiken tekens te vinden: = LEN (A3) - FIND (",", A3) - 1
De rechterkant van de snaar die we willen is = RECHTS (A3, LEN (A3) - FIND (",", A3) - 1)

Uw spreadsheet zou nu moeten lijken op de onderstaande schermafbeelding. We hebben de formules als tekst naar de onderkant van de spreadsheet gekopieerd om het gemakkelijker te kunnen lezen en zien.

Die was een beetje moeilijk, maar je hoeft deze formules maar één keer te schrijven.

Volgende volgende ...

Dit is onze les voor vandaag. U moet nu redelijk goed op de hoogte zijn van formules en functies, rijen en kolommen, en de manier waarop dit allemaal kan worden gebruikt via verschillende definitieve voorbeelden.

Volgend in les 3 bespreken we celverwijzing en -formattering, evenals het verplaatsen en kopiëren van formules, zodat je niet elke formule steeds opnieuw hoeft te herschrijven!