Definere og opprette en formel

Innholdsfortegnelse:

Definere og opprette en formel
Definere og opprette en formel

Video: Definere og opprette en formel

Video: Definere og opprette en formel
Video: iPhone 13 How to Turn off without power button - YouTube 2024, Mars
Anonim
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:
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

  1. Hvorfor trenger du formler og funksjoner?
  2. Definere og opprette en formel
  3. Relativ og Absolutt Cellreferanse, og Formatering
  4. Nyttige funksjoner du bør bli kjent med
  5. 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") i en formel og nestingsfunksjoner

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 lage en funksjon, må vi først forstå rad og kolonne notasjon.

Rader og kolonner

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

Rader løper horisontalt og kolonnene løper vertikalt. For å huske hva som er som, tenk på en kolonne som holder opp en takkolonner, gå opp-ned, og dermed 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, etc.
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, etc.

Eksempel: Funksjon Sum ()

La oss nå vise hvordan du bruker en funksjon.

Du bruker funksjoner ved å skrive dem direkte inn eller bruke funksjonsveiviseren. Funksjonsveiviseren åpner 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. Den gir også en kobling til instruksjoner på nettet 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.
Veiviseren forteller deg hvilke argumenter du trenger for å gi hver funksjon. Den gir også en kobling til instruksjoner på nettet 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.
Image
Image

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.
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.
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.
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.
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!
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:
#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 du kan tegne for å gjøre det valgte området større eller mindre.

Trykk "enter" for å bekrefte resultatene.
Trykk "enter" for å bekrefte resultatene.

Beregningsoperatører

Det finnes 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. 24 = 2 ^ 4 = 2 * 2 * 2 * 2 =16

Det er andre operatører som ikke er relatert til matematikk som "&", som betyr at de to strengene er sammenkoblede (delta i ende-til-ende). 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 eller "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 4 <> 6

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

Ordreforrang 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 πr2, som er det samme som π * r * r. Det er ikke (Πr)2.

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

Vanligvis kan du si dette:

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

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 mengde 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 strenge og referanseoperatører. For øyeblikket holder vi bare med det vi bare dekket. La oss se på noen eksempler.

Eksempel: Beregning av sirkelområdet

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 multipliserer resultatet av Pi.

Eksempel: Beregning av lønnsøkning

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 andre tilfelle 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 ene = -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:
Nå har vi:
Legg til SUM () -funksjonen til "totale" månedlige utgifter.
Legg til SUM () -funksjonen til "totale" månedlige utgifter.
Image
Image

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å Formulas-menyen velg rullegardinmenyen "Finansiell" og velg deretter PMT-funksjonen. Veiviseren dukker opp:
På Formulas-menyen 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.
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:
Nå ser regnearket slik ut:
Din månedlige utgift er $ 1,863 - Ouch!
Din månedlige utgift er $ 1,863 - Ouch!

Eksempel: Tekstfunksjon

Her viser 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 å 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:

  1. Beregn lengden på strengen.
  2. Finn posisjonen til kommaet (dette viser hvor ett ord slutter og det andre begynner).
  3. Kopier venstre side av strengen opp til komma.
  4. 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.

  1. Beregn lengden på strengen med funksjonen = LEN (A3) - resultatet er 18.
  2. Finn nå posisjonen til kommaet ved å skrive inn denne funksjonen = FIND (",", A3 ") - resultatet er 11.
  3. Ta nå venstre side av strengen opp til komma og opprett denne nestede formelen ved hjelp av 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?
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.

  1. Husk posisjonen til kommaet eller beregne det igjen.
  2. Beregn lengden på strengen.
  3. Telle 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 steg.

  1. Overfra er dette = FIND (",", A3 ")
  2. Lengden på strengen er = LEN (A3)
  3. Du må bruke litt matte for å finne antall tegn som skal tas: = LEN (A3) - FIND (",", A3) - 1
  4. 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.
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 hele kan bli benyttet gjennom flere bestemte 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!

Anbefalt: