Excel formler og funktioner

Formler og funktioner er brød og smør i Excel. De driver næsten alt interessant og nyttigt, du nogensinde vil gøre i et regneark. Denne artikel introducerer de grundlæggende begreber, du skal kende for at være dygtig til 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

 
=6/3 // returns 2

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

Cellereferencer

I eksemplerne ovenfor er værdier 'hardcoded'. 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 oplysninger og gør det sværere at vedligeholde et regneark.

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

 
=A1+A2+A3 // returns 9

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

Alle formler returnerer et resultat

Alle formler i Excel returnerer et resultat, selvom 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:

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 IFERROR -funktion og vise en mere venlig besked (eller slet intet).

Kopier og indsæt formler

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

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

Samme formel indsat til E3. Celleadresser opdateres igen:

Relative og absolutte referencer

Ovenstående cellereferencer kaldes i forhold referencer. Det betyder, at referencen er i forhold til den celle, den lever i. Formlen i E1 ovenfor er:

 
=B1+C1+D1 // formula in E1

Bogstaveligt talt betyder det 'celle 3 kolonner tilbage' + 'celle 2 kolonner venstre' + 'celle 1 kolonne tilbage'. Det er derfor, når formlen kopieres ned til celle E2, fortsætter den med at fungere 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 i nedenstående skærm vil vi gange hver værdi i kolonne D med 10, som er angivet i A1. Ved at bruge en absolut reference til A1 'låser' vi referencen, så den ikke ændres, når formlen kopieres til E2 og E3:

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

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

Bemærk referencen til D1 -opdateringer, når formlen kopieres, men referencen til A1 ændres aldrig. Nu kan vi nemt ændre værdien i A1, og alle tre formler genberegner. Nedenfor har værdien i A1 ændret sig fra 10 til 12:

Dette enkle eksempel viser også, hvorfor det ikke giver mening at hardcode -værdier i en formel. Ved at gemme værdien i A1 ét sted og henvise til A1 med en absolut reference , værdien kan til enhver tid ændres, og alle tilknyttede formler opdateres øjeblikkeligt.

Tip: du kan skifte mellem relativ og absolut syntaks med F4 nøgle .

Sådan indtaster du en formel

Sådan indtaster du en formel:

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

I stedet for at skrive cellereferencer kan du pege og klikke, som det ses herunder. Bemærk referencer er farvekodede:

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

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, hører du ordene 'formel' og 'funktion', der bruges ofte, nogle gange om hverandre. De er nært beslægtede, men ikke helt ens. Teknisk set er en formel nogen udtryk, der begynder med et lighedstegn (=).

En funktion er derimod en formel med et særligt navn og formål. I de fleste tilfælde har funktioner navne, der afspejler deres påtænkte anvendelse. For eksempel kender du sikkert SUM -funktion allerede, hvilket returnerer summen af ​​givne referencer:

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

Det GENNEMSNIT 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 vigtige 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 en matchende åbning og lukning af parenteser (). Mønsteret ser sådan ud:

 
=FUNCTIONNAME(argument1,argument2,argument3)

For eksempel TÆLLE -funktion tæller celler, der opfylder kriterier, og tager to argumenter, rækkevidde og kriterier :

 
= COUNTIF (range,criteria) // two arguments

På nedenstående skærm er intervallet A1: A5 og kriterierne 'røde'. Formlen i C1 er:

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

Video: Sådan bruges funktionen TÆLLE

Ikke alle argumenter kræves. Argumenter vist firkantede parenteser er valgfrie. For eksempel YEARFRAC funktion returnerer brøkdel af år mellem en startdato og slutdato og tager 3 argumenter:

 
= YEARFRAC (start_date,end_date,[basis])

Startdato og slutdato er nødvendige argumenter, grundlag er et valgfrit argument. Se nedenfor for et eksempel på, hvordan du bruger YEARFRAC til at beregne nuværende alder baseret på fødselsdato.

Sådan indtaster du en funktion

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

1. Indtast lighedstegn (=) og begynd at skrive. Excel viser en liste over matchende funktioner, mens du skriver:

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. Indtast tabulatortasten for at acceptere en funktion. Excel vil fuldføre funktionen:

3. Udfyld nødvendige argumenter:

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

Kombination af funktioner (indlejring)

Mange Excel -formler bruger mere end én 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:

Det YEARFRAC funktion beregner år med startdato og slutdato:

Vi kan bruge B1 til startdato, og derefter bruge TODAY funktion at levere slutdatoen:

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

 
= YEARFRAC (B1, TODAY ())

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

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

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 billederne ovenfor er 22. februar 2019.
2. Indlejret IF -funktioner er et klassisk eksempel på indlejringsfunktioner .
3. Det TODAY funktion er en sjælden Excel -funktion uden nødvendige argumenter.

Nøgletagning: Output af enhver formel eller funktion kan føjes direkte til en anden formel eller funktion.

Matematikoperatører

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

Logiske operatorer

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

Video: Hvordan man bygger logiske formler

Driftsorden

Når du løser en formel, følger Excel en sekvens kaldet 'rækkefølge for operationer'. Først evalueres eventuelle udtryk i parentes. Næste Excel løser eventuelle 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 den er til stede.

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

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

Konverter formler til værdier

Nogle gange vil du slippe af med formler og kun efterlade værdier 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 special> Værdier. Dette overskriver formlerne med de værdier, de returnerer. Du kan bruge en tastaturgenvej for at indsætte værdier, eller brug menuen Indsæt på fanen Startside på båndet.

Video: Indsæt særlige 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 Excel's formler og funktioner. Vi tilbyder også online video træning .

• 29 tip til arbejde med formler og funktioner ( videoversion her )
• 500 formeleksempler med fuld forklaring
• 101 vigtige Excel -funktioner
• Vejledning til alle Excel -funktioner (arbejde der er i gang)
• Excel formelfejl (eksempler og rettelser)
• Formelkriterier - 50 eksempler
• Formler til betinget formatering
• Sådan bruges F9 til fejlfinding af en formel (video)
• Excel formelfejl og rettelser (video)