Definere og opprette en formel

I denne leksjonen presenterer vi deg for grunnleggende regler for å lage formler og bruke funksjoner. Vi føler at en av de beste måtene å lære er gjennom praksis, så vi gir flere eksempler og forklarer dem i detalj. Emnene vi vil dekke inkluderer:

SCHOOL NAVIGASJON

Hvorfor trenger du formler og funksjoner?
Definere og opprette en formel
Relativ og Absolutt Cellreferanse, og Formatering
Nyttige funksjoner du bør bli kjent med
Oppslag, diagrammer, statistikk og pivottabeller

rader og kolonner
Eksempel matematikkfunksjon: SUM ()
operatører
operatørens forrang
Eksempel finansiell funksjon: PMT (), lån betaling
ved hjelp av en "streng" -funksjon ("streng" er stenografi for "streng av tekst") inne i en formel og nesting funksjoner

Formler er en blanding av "funksjoner", "operatører" og "operander". Før vi skriver noen formler, må vi opprette en funksjon, men før vi kan skape en funksjon, må vi først forstå rad og kolonne notasjon.

Rader og kolonner

For å forstå hvordan du skriver formler og funksjoner, må du vite om rader og kolonner.

Rader løper horisontalt og kolonner løper vertikalt. For å huske hvilken som er, tenk på en kolonne som holder opp en takkolonne, gå opp og ned, så går radene til venstre til høyre.

Kolonner er merket med bokstaver; rader etter tall. Den første cellen i regnearket er A1 som betyr kolonne A, rad 1. Kolonnene er merket A-Z. Når alfabetet løper ut, plasserer Excel et annet brev foran: AA, AB, AC ... AZ, BA, BC, BC, osv.

Eksempel: Funksjon Sum ()

La oss nå vise hvordan du bruker en funksjon.

Du bruker funksjoner ved å skrive dem direkte inn eller bruke funksjonsveiviseren. Funksjonsveiviseren åpnes når du enten velger en funksjon fra "Formler" -menyen fra "Funksjonsbiblioteket." Ellers kan du skrive inn = i en celle og en praktisk rullegardinmeny lar deg velge en funksjon.

Veiviseren forteller deg hvilke argumenter du trenger for å gi hver funksjon. Det gir også en kobling til online instruksjoner hvis du trenger hjelp til å forstå hva funksjonen gjør og hvordan du bruker den. Hvis du for eksempel skriver = sum i en celle, viser in-line veiviseren hvilke argumenter som kreves for SUM-funksjonen.

Når du skriver inn en funksjon, er veiviseren inline eller høyre på fingrene. Når du velger en funksjon fra "Formler" -menyen, er veiviseren en popup-boks. Her er popup-veiviseren for SUM () -funksjonen.

For vår første funksjon, la oss bruke SUM (), som legger til en liste med tall.

Anta at vi har dette regnearket for å inneholde planer for å budsjettere familieferien din:

For å beregne de totale kostnadene kan du skrive = b2 + b3 + b4 + b5, men det er lettere å bruke SUM () -funksjonen.

I Excel, se etter symbolet Σ øverst til venstre i Excel-skjermen for å finne AutoSum-knappen (matematikere bruker gresk bokstav Σ for å legge til en rekke tall).

Hvis markøren er under familiebudsjettnumrene, er Excel smart nok til å vite at du vil summere listen over tall over det du plasserte markøren på, så det fremhever tallene.

Trykk "enter" for å akseptere rekkevidden valgt av Excel eller bruk markøren til å endre hvilke celler som er valgt.

Hvis du ser på hva Excel legger inn i regnearket, kan du se at det skrev denne funksjonen:

I denne formelen summerer Excel tallene fra B2 til B9. Merk at vi forlot et rom under rad 5 slik at du kunne legge til familieferiebudsjettet - kostnaden vil sikkert gå opp som barnelisten over hva de vil gjøre og hvor de vil gå, blir lengre!

Matematiske funksjoner fungerer ikke med bokstaver, så hvis du legger inn bokstaver i kolonnen, vises resultatet som "#NAME?" Som vist nedenfor.

#NAVN? indikerer at det er en slags feil. Det kan være et hvilket som helst antall ting, inkludert:

dårlig celle referanse
bruker bokstaver i matematiske funksjoner
utelat nødvendige argumenter
stavefunksjonens navn feil
ulovlig matteoperasjon som divisjon med 0

Den enkleste måten å velge argumenter på i en beregning er å bruke musen. Du kan legge til eller fjerne fra listen over argumenter til funksjonen ved å forstørre eller redusere boksen som Excel trekker når du beveger musen eller klikker i en annen celle.

Vi har klikket på toppen av torget tegnet av Excel for å ta "flybillett" ut av budsjettet. Du kan se krysshårsymbolet som du kan tegne for å gjøre det valgte området større eller mindre.

Trykk "enter" for å bekrefte resultatene.

Beregningsoperatører

Det er to typer operatører: matte og sammenligning.

Math Operator	Definisjon
+	addisjon
-	subtraksjon eller negasjon, for eksempel 6 * -1 = -6
*	multiplikasjon
/	inndeling
%	prosent
^	eksponent, f.eks. 2⁴= 2 ^ 4 = 2 * 2 * 2 * 2 = 16

Det finnes andre operatører som ikke er relatert til matematikk som "&", noe som betyr at de to strengene er sammenkoblede (delta i end-to-end). For eksempel = "Excel" & "er Fun" tilsvarer "Excel er morsomt".

Nå ser vi på sammenligningsoperatører.

Sammenligningsoperatør	Definisjon
=	tilsvarer for eksempel 2 = 4 eller "b" = "b"
>	større enn for eksempel 4> 2 eller "b"> "a"
<	mindre enn for eksempel 2 < 4 or “a” < “b”
> =	større enn eller lik - en annen måte å tenke på dette er> = betyr enten > eller =.
<=	mindre enn eller lik.
	ikke lik, for eksempel 46

Som du ser ovenfor, jobber sammenligningsoperatører med tall og tekst.

Merk at hvis du skriver inn = "a"> "b" i en celle, vil den si "FALSE" siden "a" ikke er større enn "b." "B" kommer etter "a" i alfabetet, så "a" > "B" eller "B"> "a."

Operator Order Precedence

Ordrepreferanse er en ide fra matematikk. Excel må følge de samme reglene som matematikk. Dette emnet er mer komplisert, så ta et pust og la oss dykke inn.

Ordrepreferanse betyr ordren der datamaskinen beregner svaret. Som vi forklarte i leksjon 1, er området for en sirkel πr², som er det samme som π * r * r. Det er ikke (Πr)².

Så du må forstå rekkefølgen forrang når du skriver en formel.

Vanligvis kan du si dette:

Excel evaluerer først elementer i parentes som arbeider innefra.
Det bruker deretter ordensforrangsregler for matematikk.
Når to elementer har samme forrang, fungerer Excel til venstre.

Forrangene til matematikkoperatører er vist under, i synkende rekkefølge.

(og)	Når parentes brukes, tilsidesetter de de normale regler for forrang. Dette betyr at Excel vil gjøre denne beregningen først. Vi forklarer dette nærmere nedenfor.
-	Negasjon, for eksempel -1. Dette er det samme som å multiplisere et tall med -1. -4 = 4 * (-1)
%	Prosent betyr multiplikasjon med 100. Eksempel, 0,003 = 0,3%.
^	Eksponent, for eksempel 10 ^ 2 = 100
* og /	Multipliser og del. Hvordan kan to operatører ha samme forrang? Det betyr bare at hvis en formel har to operatører med samme forrang, så beregnes det fra venstre til høyre.
+ og -	Addisjon og subtraksjon.

Det er andre prioritetsregler knyttet til strenger og referanseoperatører. For øyeblikket holder vi bare med det vi bare dekket. La oss se på noen eksempler.

Eksempel: Beregning av området i en sirkel

Området i en sirkel er= PI () * radius ^ 2.

Ser vi på bordet over ser vi at eksponenter kommer før multiplikasjon. Så beregner datamaskinen først radius ^ 2 og deretter multipler den som følge av Pi.

Eksempel: Beregning av økning i lønn

La oss si at sjefen din bestemmer deg for å gjøre en god jobb, og han eller hun skal gi deg 10% økning! Hvordan ville du beregne din nye lønn?

Først husk at multiplikasjon kommer før tillegg.

Er det = lønn + lønn 10% eller er det = lønn + (lønn * 10%)?

Anta at lønnen din er $ 100. Med en 10% økning vil din nye lønn være:

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

Du kan også skrive det slik:

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

I det andre tilfellet har vi forklart rekkefølgen ved hjelp av parenteser. Husk at parenteser evalueres før andre operasjoner.

Forresten, den enklere måten å skrive dette på er = lønn * 110%

Parenteser kan nestes inn i hverandre. Så, når vi skriver (3 + (4 * 2)), arbeider fra innsiden til utsiden, beregner vi først 4 * 2 = 8, og legger deretter til 3 + 8 for å få 11.

Noen få eksempler

Her er et annet eksempel: = 4 * 3 / 2. Hva er svaret?

Vi ser fra reglene i tabellen ovenfor at * og / har samme forrang. Så Excel fungerer fra venstre til høyre, 4 * 3 = 12 først, så deler det med 2 for å få 6.

Igjen kan du gjøre det eksplisitt ved å skrive = (4 * 3) / 2

Hva med = 4 + 3 * 2?

Datamaskinen ser både * og + operatører. Så følger prioritetsreglene (multiplikasjon kommer før tillegg) det beregner 3 * 2 = 6 først, deretter legger 4 til å få 10.

Hvis du ønsket å endre rekkefølgen av forrang, ville du skrive = (4 + 3) * 2 = 14.

Hva med denne en = -1 ^ 3?

Da er svaret -3 fordi datamaskinen beregnet = (-1) ^ 3 = -1 * -1 * -1 = -1.

Husk at negative tider negative er positive og negative ganger positive er negativ. Du kan se dette slik (-1 * -1) * -1 = 1 * -1 = -1.

Så det er noen eksempler på matematisk rekkefølge og forrang, vi håper det hjelper med å fjerne noen få ting om hvordan Excel utfører beregninger (og det er nok matte til å vare livet for noen av dere).

Eksempel: Funksjon Lån Betaling (PMT)

La oss se på et eksempel for å beregne en lånebetaling.

Start med å lage et nytt regneark.

Formater tallene med dollarskilt og bruk null desimaler siden vi ikke er interessert i cent akkurat nå, fordi de ikke betyr noe mye når du snakker om dollar (i neste kapittel undersøker vi hvordan du formaterer tall i detalj). For eksempel, for å formatere renten, høyreklikk på cellen og klikk "format celler." Velg prosent og bruk 2 desimaler.

Tilsvarende formatere de andre cellene for "valuta" i stedet for prosentandel og velg "nummer" for låneperioden.

Nå har vi:

Legg til SUM () -funksjonen til "totale" månedlige utgifter.

Merk, boliglån celle er ikke inkludert i totalen. Excel vet ikke at du vil inkludere det nummeret, siden det ikke er noen verdi der. Så vær forsiktig med å utvide SUM () -funksjonen til toppen enten ved å bruke markøren eller skrive E2 hvor det står E3 å inkludere boliglånet i summen.

Sett markøren i betalingscellen (B4).

På menyen Formler velg rullegardinmenyen "Finansiell" og velg deretter PMT-funksjonen. Veiviseren dukker opp:

Bruk markøren til å velge "rate", "nper" (låneperiode), "Pv" ("nåverdi" eller lånebeløp). Legg merke til at du må dividere renten med 12 siden renter beregnes månedlig. Også du må multiplisere lånetiden i år med 12 for å få låneperioden i måneder. Trykk "OK" for å lagre resultatet i regnearket.

Legg merke til at betalingen vises som et negativt nummer: -1013.37062. For å gjøre det positivt og legge det til de månedlige utgiftene, pek på boliglåncellen (E2). Skriv "= -" bruk deretter markøren til å peke på betalingsfeltet. Den resulterende formel er = -B4.

Nå ser regnearket slik ut:

Din månedlige utgift er $ 1,863 - Ouch!

Eksempel: Tekstfunksjon

Her demonstrerer vi hvordan du bruker funksjoner i en formel og tekstfunksjoner.

Anta at du har en liste over studenter som vist nedenfor. For- og etternavnet er i ett felt separert av et komma. Vi må sette de siste og faste navnene i separate celler. Hvordan gjør vi dette?

For å takle dette problemet må du bruke en algoritme - det vil si en trinnvis prosedyre for å gjøre dette.

For eksempel, se på "Washington, George." Prosedyren for å dele det i to ord ville være:

Beregn lengden på strengen.
Finn posisjonen til kommaet (dette viser hvor ett ord slutter og det andre begynner).
Kopier venstre side av strengen opp til komma.
Kopier høyre side av strengen fra komma til slutt.

La oss diskutere hvordan du gjør dette med "George Washington" trinn for trinn i Excel.

Beregn lengden på strengen med funksjonen = LEN (A3) - resultatet er 18.
Finn nå posisjonen til kommaet ved å skrive inn denne funksjonen = FIND (",", A3 ") - resultatet er 11.
Ta nå venstre side av strengen opp til komma og opprett denne nestede formelen ved å bruke resultatet fra trinn 1: = VENSTRE (A3, FIND (",", A3) -1). Merk, vi må trekke 1 fra lengden fordi FIND gir posisjonen til kommaet.

Her ser alt ut når alle funksjonene er plassert sammen i en formel. I celle B3 kan du se at denne formelen tar all informasjon fra celle A3 og skriver inn "Washington" i den.

Så vi har "Washington", nå må vi få "George." Hvordan gjør vi dette?

Merk at vi kunne ha lagret resultatet fra trinn 1 i en celle av seg selv, si, B6, og skriv en enklere formel = VENSTRE (A3, B6-1). Men det bruker opp en celle for det intermittente trinnet.

Husk posisjonen til kommaet eller beregne det igjen.
Beregn lengden på strengen.
Telling tegnene fra slutten av strengen til komma.

Ta antall tegn fra trinn 3 og trekk en for å utelate komma og rom.

La oss gjøre dette trinn for trinn.

Overfra er dette = FIND (",", A3 ")
Lengden på strengen er = LEN (A3)
Du må bruke litt matte for å finne antall tegn som skal tas: = LEN (A3) - FIND (",", A3) - 1
Høyre side av strengen vi ønsker er = RIGHT (A3, LEN (A3) - FIND (",", A3) - 1)

Regnearket ditt skal nå se ut som skjermbildet nedenfor. Vi kopierte formlene som tekst i bunnen av regnearket for å gjøre det lettere å lese og se.

Den ene var litt vanskelig, men du trenger bare å skrive disse formlene en gang.

Nestemann…

Dette avslutter vår leksjon for i dag. Du bør ha en ganske solid forståelse nå med formler og funksjoner, rader og kolonner, og måten dette kan brukes på gjennom flere konkrete eksempler.

Kommer opp neste i leksjon 3, vil vi diskutere cellereferanse og formatering samt flytte og kopiere formler, slik at du ikke trenger å omskrive hver formel igjen og igjen!