Excel

Excel formler og funktioner

Excel Formulas Functions

Formler og funktioner er Excel og brød og smør. De kører næsten alt interessant og nyttigt, du nogensinde vil gøre i et regneark. Denne artikel introducerer de grundlæggende begreber, du har brug for at vide for at være dygtige med formler i Excel. Flere eksempler her .

Hvad er en formel?

En formel i Excel er et udtryk, der returnerer et specifikt resultat. For eksempel:



 
=1+2 // returns 3

Grundformeleksempel - 1 + 3 = 3



 
=6/3 // returns 2

Grundformeleksempel - 6/3 = 2

Bemærk: alle formler i Excel skal begynde med et ligetegn (=).



Cellehenvisninger

I eksemplerne ovenfor er værdierne 'hårdkodede'. Det betyder, at resultaterne ikke ændres, medmindre du redigerer formlen igen og ændrer en værdi manuelt. Generelt betragtes dette som dårlig form, fordi det skjuler information og gør det sværere at vedligeholde et regneark.

Brug i stedet cellereferencer, så værdier kan ændres når som helst. På nedenstående skærmbillede indeholder C1 følgende formel:

 
=A1+A2+A3 // returns 9

Formel med cellehenvisninger



Bemærk, fordi vi bruger cellereferencer til A1, A2 og A3, kan disse værdier ændres når som helst, og C1 viser stadig et nøjagtigt resultat.

Alle formler returnerer et resultat

Alle formler i Excel returnerer et resultat, selv når resultatet er en fejl. Nedenfor bruges en formel til at beregne procentændring. Formlen returnerer et korrekt resultat i D2 og D3, men returnerer et # DIV / 0! fejl i D4, fordi B4 er tom:

Formelresultatet kan være en fejl

Der er forskellige måder at håndtere fejl på. I dette tilfælde kan du angive den manglende værdi i B4 eller 'fange' fejlen med FEJL-funktion og vise en mere venlig besked (eller slet ingenting).

Kopier og indsæt formler

Skønheden ved cellereferencer er, at de automatisk opdateres, når en formel kopieres til en ny placering. Dette betyder, at du ikke behøver at indtaste den samme grundlæggende formel igen og igen. På nedenstående skærmbillede er formlen i E1 kopieret til udklipsholderen med Control + C:

Formel i E1 kopieret til udklipsholderen

Nedenfor: formel indsat i celle E2 med Control + V. Bemærkning cellehenvisninger er ændret:

Formel i E1 indsat på E2

Samme formel indsat til E3. Celleadresser opdateres igen:

Formel i E1 indsat på E3

Relative og absolutte referencer

Cellehenvisningerne ovenfor kaldes i forhold referencer. Dette betyder, at referencen er relativ til den celle, den lever i. Formlen i E1 ovenfor er:

 
=B1+C1+D1 // formula in E1

Bogstaveligt betyder dette 'celle 3 kolonner tilbage' + 'celle 2 kolonner tilbage' + 'celle 1 kolonne tilbage'. Derfor, når formlen kopieres ned til celle E2, fortsætter den med at arbejde på samme måde.

Relative referencer er yderst nyttige, men der er tidspunkter, hvor du ikke ønsker, at en cellereference skal ændres. En cellereference, der ikke ændres, når den kopieres, kaldes en absolut reference . For at gøre en reference absolut skal du bruge dollarsymbolet ($):

 
=A1 // relative reference =$A // absolute reference

For eksempel på skærmen nedenfor ønsker vi at gange hver værdi i kolonne D med 10, som er indtastet i A1. Ved at bruge en absolut reference til A1 låser vi den reference, så den ikke ændres, når formlen kopieres til E2 og E3:

Absolut referenceeksempel

Her er de endelige formler i E1, E2 og E3:

 
=D1*$A // formula in E1 =D2*$A // formula in E2 =D3*$A // formula in E3

Bemærk henvisningen til D1-opdateringer, når formlen kopieres, men henvisningen til A1 ændres aldrig. Nu kan vi nemt ændre værdien i A1, og alle tre formler genberegnes. Under værdien i A1 er ændret fra 10 til 12:

Absolut referenceeksempel efter værdi i A1 er ændret

Dette enkle eksempel viser også, hvorfor det ikke giver mening at indstille værdier til en formel. Ved at gemme værdien i A1 ét sted og henvise til A1 med et absolut reference , kan værdien ændres når som helst, og alle tilknyttede formler opdateres med det samme.

Tip: Du kan skifte mellem relativ og absolut syntaks med F4-tast .

Sådan indtastes en formel

Sådan indtaster du en formel:

  1. Vælg en celle
  2. Indtast et ligetegn (=)
  3. Skriv formlen, og tryk på enter.

I stedet for at skrive cellereferencer kan du pege og klikke som vist nedenfor. Henvisningshenvisninger er farvekodede:

Indtastning af en formel med peg og klik-referencer

Alle formler i Excel skal begynde med et ligetegn (=). Intet ligetegn, ingen formel:

Glemt at indtaste et ligetegn betyder ingen formel, kun tekst

Sådan ændres en formel

For at redigere en formel har du 3 muligheder:

  1. Vælg cellen, rediger i formellinje
  2. Dobbeltklik på cellen, rediger direkte
  3. Vælg cellen, tryk på F2 , rediger direkte

Uanset hvilken indstilling du bruger, skal du trykke på Enter for at bekræfte ændringer, når du er færdig. Hvis du vil annullere og lade formlen være uændret, skal du klikke på Escape-tasten.

Video: 20 tip til indtastning af formler

Hvad er en funktion?

Når du arbejder i Excel, vil du høre ordene 'formel' og 'funktion' bruges ofte, undertiden om hverandre. De er tæt beslægtede, men ikke nøjagtigt de samme. Teknisk set er en formel nogen udtryk, der begynder med et ligetegn (=).

En funktion er derimod en formel med et specielt navn og formål. I de fleste tilfælde har funktioner navne, der afspejler deres tilsigtede anvendelse. For eksempel kender du sandsynligvis SUM-funktion allerede, som returnerer summen af ​​givne referencer:

 
= SUM (1,2,3) // returns 6 = SUM (A1:A3) // returns A1+A2+A3

Det GEMIDDELIG funktion , som du ville forvente, returnerer gennemsnittet af givne referencer:

 
= AVERAGE (1,2,3) // returns 2

Og MIN- og MAX-funktionerne returnerer henholdsvis minimums- og maksimumværdier:

 
= MIN (1,2,3) // returns 1 = MAX (1,2,3) // returns 3

Excel indeholder hundredvis af specifikke funktioner . For at komme i gang, se 101 Nøgle Excel-funktioner .

Funktionsargumenter

De fleste funktioner kræver input for at returnere et resultat. Disse input kaldes 'argumenter'. En funktions argumenter vises efter funktionsnavnet inden for parenteser adskilt af kommaer. Alle funktioner kræver tilsvarende matchende parenteser (). Mønsteret ser sådan ud:

 
=FUNCTIONNAME(argument1,argument2,argument3)

For eksempel COUNTIF-funktion tæller celler, der opfylder kriterierne, og tager to argumenter, rækkevidde og kriterier :

 
= COUNTIF (range,criteria) // two arguments

I nedenstående skærmbillede er rækkevidden A1: A5, og kriterierne er 'røde'. Formlen i C1 er:

 
= COUNTIF (A1:A5,'red') // returns 2

COUNTIF kræver to argumenter, rækkevidde og kriterier

Video: Sådan bruges COUNTIF-funktionen

Ikke alle argumenter kræves. Argumenter, der vises firkantede parenteser er valgfri. For eksempel YEARFRAC-funktion returnerer brøkantal år mellem startdato og slutdato og tager 3 argumenter:

 
= YEARFRAC (start_date,end_date,[basis])

Startdato og slutdato er krævede argumenter, basis er et valgfrit argument. Se nedenfor for et eksempel på, hvordan man bruger YEARFRAC til at beregne den aktuelle alder baseret på fødselsdato.

Sådan åbnes en funktion

Hvis du kender navnet på funktionen, skal du bare begynde at skrive. Her er trinene:

1. Indtast ligetegn (=), og begynd at skrive. Excel viser en liste over matchende funktioner, når du skriver:

Når du skriver, viser Excel matchende funktioner

Når du ser den ønskede funktion på listen, skal du bruge piletasterne til at vælge (eller bare fortsætte med at skrive).

2. Skriv Tab-tasten for at acceptere en funktion. Excel fuldfører funktionen:

Tryk på Tab for at åbne den valgte funktion

3. Udfyld de krævede argumenter:

Indtast krævede argumenter

4. Tryk på Enter for at bekræfte formlen:

Tryk på Enter for at bekræfte og gå ind i funktionen

Kombination af funktioner (indlejring)

Mange Excel-formler bruger mere end en funktion, og funktioner kan være ' indlejret 'inde i hinanden. For eksempel har vi nedenfor en fødselsdato i B1, og vi vil beregne den aktuelle alder i B2:

Brug for en formel til at beregne den aktuelle alder i B2

Det YEARFRAC-funktion beregner år med en startdato og slutdato:

YEARFRAC beregner år med startdato og slutdato

Vi kan bruge B1 til startdato og derefter bruge I DAG-funktion at levere slutdatoen:

B1 for startdato, TODAY-funktionen til levering af slutdato

Når vi trykker på Enter for at bekræfte, får vi den aktuelle alder baseret på dagens dato:

 
= YEARFRAC (B1, TODAY ())

YEARFRAC og TODAY funktioner til beregning af den aktuelle alder

Bemærk, at vi bruger TODAY-funktionen til at føje en slutdato til YEARFRAC-funktionen. Med andre ord kan TODAY-funktionen nestes inde i YEARFRAC-funktionen for at give slutdatoargumentet. Vi kan tage formlen et skridt videre og bruge INT-funktion at hugge decimalværdien af:

 
= INT ( YEARFRAC (B1, TODAY ()))

YEARFRAC og I DAG inde i INT

Her returnerer den originale YEARFRAC-formel 20.4 til INT-funktionen, og INT-funktionen returnerer et slutresultat på 20.

Bemærkninger:

  1. Den aktuelle dato i ovenstående billeder er 22. februar 2019.
  2. Indlejret IF-funktioner er et klassisk eksempel på indlejringsfunktioner .
  3. Det I DAG-funktion er en sjælden Excel-funktion uden krævede argumenter.

Key takeaway: Outputtet fra en hvilken som helst formel eller funktion kan tilføres direkte til en anden formel eller funktion.

Matematikoperatører

Tabellen nedenfor viser de standard matematiske operatører, der er tilgængelige i Excel:

Symbol Operation Eksempel
+ Tilføjelse = 2 + 3 = 5
- Subtraktion = 9-2 = 7
* Multiplikation = 6 * 7 = 42
/ Division = 9/3 = 3
^ Eksponentiering = 4 ^ 2 = 16
() Parenteser = (2 + 4) / 3 = 2

Logiske operatører

Logiske operatorer understøtter sammenligninger som 'større end', 'mindre end' osv. De logiske operatorer, der er tilgængelige i Excel, vises i nedenstående tabel:

Operatør Betyder Eksempel
= Svarende til =A1=10
Ikke lig med = A110
> Bedre end =A1>100
< Mindre end =A1<100
> = Større end eller lig med =A1>=75
<= Mindre end eller lig med =A1<=0

Video: Hvordan man bygger logiske formler

Rækkefølge for operationer

Når man løser en formel, følger Excel en sekvens kaldet 'rækkefølge for operationer'. For det første evalueres eventuelle udtryk i parentes. Næste Excel løser alle eksponenter. Efter eksponenter udfører Excel multiplikation og division, derefter addition og subtraktion. Hvis formlen involverer sammenkædning , dette vil ske efter standard matematiske operationer. Endelig vil Excel evaluere logiske operatører , hvis til stede.

  1. Parenteser
  2. Eksponenter
  3. Multiplikation og division
  4. Addition og subtraktion
  5. Sammenkædning
  6. Logiske operatører

Tip: Du kan bruge Evaluer funktion at se Excel løse formler trin for trin.

Konverter formler til værdier

Nogle gange vil du slippe af med formler og kun lade værdier være på deres sted. Den nemmeste måde at gøre dette på i Excel er at kopiere formlen og derefter indsætte ved hjælp af Indsæt speciel> Værdier. Dette overskriver formlerne med de værdier, de returnerer. Du kan bruge en tastaturgenvej til at indsætte værdier, eller brug menuen Sæt ind på fanen Hjem på båndet.

Video: Indsæt specielle genveje

hvordan man finder første kvartil i Excel

Hvad er det næste?

Nedenfor er guider, der hjælper dig med at lære mere om Excels formler og funktioner. Vi tilbyder også online videotræning .

Forfatter Dave Bruns


^