Definiera och skapa en formel

I denna lektion introducerar vi dig till grundläggande regler för att skapa formler och använda funktioner. Vi anser att ett av de bästa sätten att lära sig är genom övning, så vi ger flera exempel och förklarar dem i detalj. De ämnen vi kommer att täcka omfattar:

SCHOOL NAVIGATION

Varför behöver du formler och funktioner?
Definiera och skapa en formel
Relativ och Absolut Cellreferens och Formatering
Användbara funktioner du bör lära känna
Sökningar, diagram, statistik och pivottabeller

rader och kolumner
Exempel matematik funktion: SUM ()
operatörer
operatörens företräde
Exempel ekonomisk funktion: PMT (), lån betalning
med hjälp av en "sträng" -funktion ("sträng" är stenografi för "textsträng") inom en formel och nestningsfunktioner

Formlerna är en blandning av "funktioner", "operatörer" och "operander". Innan vi skriver några formler behöver vi skapa en funktion, men innan vi kan skapa en funktion måste vi först förstå rad och kolumnnotation.

Rader och kolumner

För att förstå hur man skriver formler och funktioner måste man veta om rader och kolumner.

Raderna går horisontellt och kolumnerna körs vertikalt. För att komma ihåg vad som är vilket, tänk på en kolumn som håller upp en takkolumn går upp och ner så går raden åt vänster-höger.

Kolumner är märkta med bokstäver; rader med siffror. Den första cellen i kalkylbladet är A1 som betyder kolumn A, rad 1. Kolumnerna är märkta A-Z. När alfabetet löper ut lägger Excel ett annat brev framför: AA, AB, AC ... AZ, BA, BC, BC, etc.

Exempel: Funktions Summa ()

Låt oss nu visa hur man använder en funktion.

Du använder funktioner genom att skriva in dem direkt eller använda funktionsguiden. Funktionsguiden öppnas när du antingen väljer en funktion från menyn "Formler" från "Funktionsbiblioteket". Annars kan du skriva = i en cell och en praktisk rullgardinsmeny låter dig välja en funktion.

Guiden berättar vilka argument du behöver för varje funktion. Det ger också en länk till online-instruktioner om du behöver hjälp med att förstå vad funktionen gör och hur du använder den. Om du till exempel skriver = summan i en cell, visar guiden i linje vilka argument som krävs för SUM-funktionen.

När du skriver en funktion är guiden inline eller höger på fingrarna. När du väljer en funktion från menyn "Formler" är guiden en popup-box. Här är popup-guiden för SUM () -funktionen.

För vår första funktion, låt oss använda SUM (), som lägger till en lista med siffror.

Antag att vi har det här kalkylbladet för att innehålla planer för att budgetera din familjes semester:

För att beräkna de totala kostnaderna kan du skriva = b2 + b3 + b4 + b5 men det är lättare att använda SUM () funktionen.

I Excel letar du efter symbolen Σ längst upp till vänster på Excel-skärmen för att hitta AutoSum-knappen (matematiker använder det grekiska bokstaven Σ för att lägga till en serie siffror).

Om markören ligger under familjebudgetnumren är Excel smart nog att veta att du vill summera listan över nummer ovanför där du placerat markören, så det markerar numren.

Tryck på "Enter" för att acceptera det intervall som valts av Excel eller använd markören för att ändra vilka celler som väljs.

Om du tittar på vad Excel lägger i kalkylbladet kan du se att det skrev den här funktionen:

I denna formel summerar Excel numren från B2 till B9. Observera, vi lämnade något rum under rad 5 så att du kunde lägga till familjeferiebudgeten - kostnaden kommer säkert att gå upp som barnlistan över vad de vill göra och var de vill gå längre!

Matematiska funktioner fungerar inte med bokstäver, så om du lägger bokstäver i kolumnen visas resultatet som "#NAME?" Som visas nedan.

#NAMN? indikerar att det finns något slags fel. Det kan vara ett antal saker, inklusive:

dålig cellreferens
Använda bokstäver i matematiska funktioner
utelämna nödvändiga argument
stavningsfunktionsnamn fel
olaglig matematikoperation som uppdelning med 0

Det enklaste sättet att välja argumenten i en beräkning är att använda musen. Du kan lägga till eller ta bort från listan med argument till funktionen genom att förstora eller minska den ruta som Excel drar när du flyttar musen eller klickar i en annan cell.

Vi har klickat på toppen av torget ritat av Excel för att ta "flygbiljetter" ur budgeten. Du kan se korshårsymbolen som du kan rita för att göra det valda området större eller mindre.

Tryck på "Enter" för att bekräfta resultaten.

Beräkningsoperatörer

Det finns två typer av operatörer: matematik och jämförelse.

Math Operatör	Definition
+	tillägg
-	subtraktion eller negation, t ex 6 * -1 = -6
*	multiplikation
/	division
%	procent
^	exponent, t.ex. 2⁴= 2 ^ 4 = 2 * 2 * 2 * 2 = 16

Det finns andra operatörer som inte är relaterade till matematik som "&", vilket innebär att de två strängarna sammanfogas (sluta i slutet). Till exempel = "Excel" & "Fun är lika med" Excel är kul ".

Nu tittar vi på jämförelseoperatörer.

Jämförelseoperatör	Definition
=	motsvarar exempelvis 2 = 4 eller "b" = "b"
>	större än t ex 4> 2 eller "b"> "a"
<	mindre än t ex 2 < 4 or “a” < “b”
> =	större än eller lika med - ett annat sätt att tänka på detta är> = betyder antingen > eller =.
<=	mindre än eller lika med.
	inte lika med exempelvis 46

Som du kan se ovan fungerar jämförelseoperatörer med siffror och text.

Obs, om du anger = "a"> "b" i en cell så kommer det att säga "FALSE" eftersom "a" inte är större än "b." "B" kommer efter "a" i alfabetet, så "a" > "B" eller "B"> "a."

Operator Order Precedence

Orderpreferens är en ide från matematik. Excel måste följa samma regler som matematik. Detta ämne är mer komplicerat, så ta andan och låt oss dyka in.

Ordningspreferens betyder den ordning i vilken datorn beräknar svaret. Som vi förklarade i lektion 1 är området för en cirkel πr², vilket är detsamma som π * r * r. Det är inte (Πr)².

Så du måste förstå ordningsförrangen när du skriver en formel.

I allmänhet kan du säga detta:

Excel utvärderar först föremål inom parentes som arbetar inuti.
Det använder då matematikens ordningens prioritetsregler.
När två objekt har samma prioritet fungerar Excel vänster mot höger.

Företräde för matematikoperatörer visas nedan, i fallande ordning.

(och)	När parentes används, överstyrar de de normala prioritetsreglerna. Detta innebär att Excel gör denna beräkning först. Vi förklarar detta vidare nedan.
-	Negation, t ex -1. Detta är detsamma som att multiplicera ett tal med -1. -4 = 4 * (-1)
%	Procent, medel multipliceras med 100. Till exempel 0,003 = 0,3%.
^	Exponent, t ex 10 ^ 2 = 100
* och /	Multiplicera och dela upp. Hur kan två operatörer ha samma prioritet? Det betyder bara att om en formel har två operatörer med samma företräde, så görs beräkningen från vänster till höger.
+ och -	Addition och subtraktion.

Det finns andra prioritetsregler relaterade till strängar och referensoperatörer. För tillfället kommer vi bara hålla fast vid vad vi bara täckt. Låt oss nu titta på några exempel.

Exempel: Beräkning av en cirkels yta

Området i en cirkel är= PI () * radie ^ 2.

Titta på tabellen ovan ser vi att exponenter kommer före multiplikation. Så beräknar datorn först raden ^ 2 och sedan multiplar den som resultat av Pi.

Exempel: Beräkning av en höjning i lön

Låt oss säga att din chef bestämmer att du gör ett bra jobb och han eller hon kommer att ge dig en 10% ökning! Hur skulle du beräkna din nya lön?

Först kom ihåg att multiplikationen kommer före tillsättning.

Är det = lön + lön * 10% eller är det = lön + (lön * 10%)?

Antag att din lön är $ 100. Med en ökning på 10% kommer din nya lön att vara:

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

Du kan också skriva det så här:

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

I det andra fallet har vi bestämt prioritetsordningen genom att använda parenteser. Kom ihåg att parentes utvärderas före någon annan operation.

Förresten, desto lättare sätt att skriva detta är = lön * 110%

Parenteser kan nästas inuti varandra. Så när vi skriver (3 + (4 * 2)) arbetar vi från insidan till utsidan, beräknar vi först 4 * 2 = 8, lägg sedan till 3 + 8 för att få 11.

Några fler exempel

Här är ett annat exempel: = 4 * 3 / 2. Vad är svaret?

Vi ser från reglerna i tabellen ovan att * och / har lika företräde. Så Excel fungerar från vänster till höger, 4 * 3 = 12 först, delar sedan det med 2 för att få 6.

Återigen kan du göra det explicit genom att skriva = (4 * 3) / 2

Vad sägs om = 4 + 3 * 2?

Datorn ser både * och + operatörer. Så följer reglerna för prioritet (multiplikation kommer före tillägg) det beräknas 3 * 2 = 6 först, sedan läggs 4 för att få 10.

Om du ville ändra prioriteringsordningen skulle du skriva = (4 + 3) * 2 = 14.

Vad sägs om den här = -1 ^ 3?

Då är svaret -3 eftersom datorn beräknas = (-1) ^ 3 = -1 * -1 * -1 = -1.

Kom ihåg att negativa tider negativa är positiva och negativa tider positiva är negativa. Du kan se detta så här (-1 * -1) * -1 = 1 * -1 = -1.

Så det finns några exempel på matematisk ordning och prioritet, vi hoppas det hjälper till att rensa några saker om hur Excel utför beräkningar (och det är förmodligen tillräckligt med matte för att vara en livstid för vissa av er).

Exempel: Function Loan Payment (PMT)

Låt oss titta på ett exempel för att beräkna ett lån.

Börja med att skapa ett nytt arbetsblad.

Formatera siffrorna med dollartecken och använd noll decimaler eftersom vi inte är intresserade av cent just nu eftersom de inte spelar någon roll så mycket när du pratar om dollar (i nästa kapitel utforskar vi hur du formaterar siffror i detalj). Till exempel, för att formatera räntan, högerklicka på cellen och klicka på "format celler". Välj procent och använd 2 decimaler.

På samma sätt formaterar du de andra cellerna för "valuta" istället för procent och väljer "nummer" för låneperioden.

Nu har vi:

Lägg till funktionen SUM () till "totala" månatliga utgifter.

Notera inteckning cellen ingår ej i summan. Excel vet inte att du vill inkludera det numret, eftersom det inte finns något värde där. Var därför försiktig med att förlänga SUM () -funktionen till toppen antingen genom att använda markören eller skriva E2 där det står E3 att inkludera hypotekslånet i summan.

Placera markören i betalningscellen (B4).

På menyn Formler välj rullgardinsmenyn "Financial" och välj sedan PMT-funktionen. Trollkarlen dyker upp:

Använd markören för att välja "rate", "nper" (låneperiod), "Pv" ("nuvärde" eller lånebelopp). Observera att du måste dela räntan med 12 eftersom räntan beräknas varje månad. Du måste också multiplicera låneperioden i år med 12 för att få låneperioden i månader. Tryck på "OK" för att spara resultatet i kalkylbladet.

Observera att betalningen visas som ett negativt nummer: -1013.37062. För att göra det positivt och lägga till det i månadsutgifterna, peka på hypotekslånet (E2). Skriv "= -" använd sedan markören för att peka på betalningsfältet. Den resulterande formeln är = -B4.

Nu ser kalkylbladet ut så här:

Dina månatliga utgifter är $ 1,863 - Ouch!

Exempel: Textfunktion

Här visar vi hur man använder funktioner inom en formel och textfunktioner.

Antag att du har en lista med studenter som visas nedan. För- och efternamn är i ett fält åtskilda av ett komma. Vi måste sätta de sista och fasta namnen i separata celler. Hur gör vi det här?

För att ta itu med detta problem behöver du använda en algoritm - det vill säga ett steg för steg för att göra detta.

Titta till exempel på "Washington, George." Förfarandet att dela upp det i två ord skulle vara:

Beräkna längden på strängen.
Hitta kommans position (detta visar var ett ord slutar och det andra börjar).
Kopiera strängens vänstra sida upp till kommatecken.
Kopiera den högra sidan av strängen från kommatecken till slutet.

Låt oss diskutera hur man gör det här med "George Washington" steg för steg i Excel.

Beräkna längden på strängen med funktionen = LEN (A3) - resultatet är 18.
Hitta nu kommas position genom att ange denna funktion = FIND (",", A3 ") - resultatet är 11.
Ta nu strängens vänstra sida upp till kommatecken och skapa den här kapslade formeln med resultatet från steg 1: = VÄNSTER (A3, FIND (",", A3) -1). Observera, vi måste subtrahera 1 från längden eftersom FIND ger kommateckenets position.

Här är vad som alla ser ut när alla funktioner är placerade i en formel. I cell B3 kan du se att denna formel tar all information från cell A3 och matar in "Washington" i den.

Så vi har "Washington", nu behöver vi få "George." Hur gör vi det här?

Observera att vi kunde ha sparat resultatet från steg 1 i en cell i sig, säg, B6, skriv sedan en enklare formel = VÄNSTER (A3, B6-1). Men det använder upp en cell för det intermittenta steget.

Kom ihåg kommuns position eller beräkna den igen.
Beräkna längden på strängen.
Räkna karaktärerna från slutet av strängen till kommatecken.

Ta antalet tecken från steg 3 och dra ett av för att släppa kommatecken och mellanslag.

Låt oss göra detta steg för steg.

Ovanifrån är detta = FIND (",", A3 ")
Strängens längd är = LEN (A3)
Du måste använda lite matte för att hitta antalet tecken att ta: = LEN (A3) - FIND (",", A3) - 1
Den högra sidan av strängen vi vill ha är = RIGHT (A3, LEN (A3) - FIND (",", A3) - 1)

Ditt kalkylblad ska nu likna skärmdumpen nedan. Vi kopierade formlerna som text till botten av kalkylbladet för att underlätta och läsa.

Den där var lite svår, men du behöver bara skriva dessa formler en gång.

Kommer härnäst…

Detta avslutar vår lektion för idag. Du borde ha en ganska fast förståelse nu om formler och funktioner, rader och kolumner, och hur det hela kan användas genom flera bestämda exempel.

När vi kommer fram nästa i lektion 3 diskuterar vi cellreferens och formatering samt flyttar och kopierar formler så att du inte behöver skriva om varje formel om och om igen!