Beräkningar i Excel

En formel är ett matematiskt uttryck som skapas för att beräkna ett resultat och som kan bero på innehållet i andra celler. En formel i en cell kan innehålla data, referenser till andra celler och en indikation på åtgärden som ska utföras.

Genom att använda cellreferenser kan formelresultat räknas om när innehållet i celler som ingår i formler ändras.

I Excel börjar formler med ett =-tecken. Parenteser () kan användas för att specificera ordningen på matematiska operationer.

Excel stöder följande operatörer:

• Aritmetiska operationer:
  • addition (+);
  • multiplikation (*);
  • hitta procenten (%);
  • subtraktion (-);
  • division (/);
  • exponent (^).
• Jämförelseoperatörer:
  • = lika;
  • < меньше;
  • > mer;
  • <= меньше или равно;
  • >= större än eller lika;
  • <>inte jämnlikt.
• Telekomoperatörer:
  • : intervall;
  • ; Union;
  • & text join-operatör.

Tabell 22. Formelexempel

Träning

Infoga formel -25-A1+AZ

Ange eventuella siffror i förväg i cellerna A1 och A3.

1. Välj önskad cell, till exempel B1.
2. Börja ange en formel med tecknet =.
3. Ange siffran 25 följt av operatören (tecken -).
4. Ange en referens till den första operanden, till exempel genom att klicka på önskad cell A1.
5. Ange följande operator (+-tecken).
6. Klicka i cellen som är den andra operanden i formeln.
7. Avsluta inmatningen av formeln genom att trycka på knappen Stiga på. Få resultatet i cell B1.

Automatisk summering

Knapp Autosumma (Autosumma)- ∑ kan användas för att automatiskt skapa en formel som summerar arean av angränsande celler som är direkt vänster i denna linje och direkt högre i den här kolumnen.

1. Markera cellen där du vill placera resultatet av summeringen.
2. Klicka på knappen Autosumma - ∑ eller tryck på kortkommandot Alt+=. Excel kommer att bestämma vilket område som ska inkluderas i summeringsintervallet och markera det med en prickad rörlig ruta som kallas en kantlinje.
3. Klick Stiga på för att acceptera området som Excel har valt, eller använd musen för att välja ett nytt område och tryck sedan på Retur.

Autosumma-funktionen transformeras automatiskt när celler läggs till och tas bort inom området.

Träning

Skapa en tabell och beräkna med formler

1. Ange numeriska data i cellerna, som visas i tabellen. 23.

Tabell 23. Initial datatabell

1. Välj cell B7, där den vertikala summan kommer att beräknas.
2. Klicka på knappen Autosumma - ∑ eller klicka Alt+=.
3. Upprepa steg 2 och 3 för cellerna C7 och D7.

Beräkna antalet anställda utan högre utbildning (med B7-B3-formeln).

1. Välj cell B8 och skriv tecknet (=).
2. Klicka i cell B7, som är den första operanden i formeln.
3. Ange (-)-tecknet på tangentbordet och klicka i OT-cellen, som är den andra operanden i formeln (formeln kommer att anges).
4. Klick Stiga på(i cell B8 kommer resultatet att beräknas).
5. Upprepa steg 5-8 för att beräkna motsvarande formler i cellerna C8 och 08.
6. Spara filen med namnet Education_Employees.x1s.

Tabell 24Beräkningsresultat

Duplicera formler med hjälp av fyllningshandtaget

Ett cellområde (cell) kan replikeras genom att använda fyllningsmarkör. Som visas i föregående avsnitt är fyllningshandtaget brytpunkten i det nedre högra hörnet av den markerade cellen.

Ofta är det nödvändigt att multiplicera inte bara data, utan också formler som innehåller adresslänkar. Processen att replikera formler med hjälp av ett fyllningshandtag låter dig färglägga en formel samtidigt som du ändrar adressreferenserna i formeln.

1. Markera cellen som innehåller formeln som du vill replikera.
2. Drag fyllningsmarkör i rätt riktning. Formeln kommer att dupliceras i alla celler.

Vanligtvis används denna process när du kopierar formler inom rader eller kolumner som innehåller data av samma typ. Vid replikering av formler med en fyllningsmarkör ändras de så kallade relativa celladresserna i formeln (relativa och absoluta referenser kommer att beskrivas i detalj senare).

Träning

Replikering av formler

1.Öppna filen Education_Employees.x1s.

1. Ange i cell E3 formeln för autosumma celler = SUMMA (OT: 03).
2. Dra och släpp fyllningshandtaget för att kopiera formeln till cellerna E4:E8.
3. Se hur de relativa celladresserna ändras i de resulterande formlerna (tabell 25) och spara filen.

Tabell 25. Ändra celladresser vid replikering av formler

Relativa och absoluta länkar

Formler som implementerar beräkningar i tabeller använder så kallade länkar för att adressera celler. Cellreferens kan vara relativ eller absolut.

Användningen av relativa referenser liknar att ange färdriktningen längs gatan - "gå tre kvarter norrut, sedan två kvarter västerut." Att följa dessa instruktioner från olika startplatser kommer att leda till olika destinationer.

Till exempel, en formel som summerar siffrorna i en kolumn eller rad kopieras sedan ofta till andra rad- eller kolumnnummer. Dessa formler använder relativa referenser (se föregående exempel i Tabell 25).

En absolut cellreferens. Go-cellområdet kommer alltid att referera till samma rad- och kolumnadress. Jämfört med vägbeskrivningarna till gatorna blir det ungefär så här: "Gå till korsningen mellan Arbat och Boulevardringen." Oavsett utgångspunkt kommer detta att leda till samma plats. Om formeln kräver att celladressen förblir oförändrad när den kopieras, måste en absolut referens (postformat $A$1) användas. Till exempel, när en formel beräknar bråkdelar av en total, får referensen till cellen som innehåller summan inte ändras när den kopieras.

Ett dollartecken ($) kommer att visas före både en kolumnreferens och en radreferens (t.ex. $C$2). Om du trycker på F4 i följd lägger du till eller tar bort ett tecken före kolumn- eller radnumret i referensen (C$2 eller $C2 - så kallade blandade länkar).

1. Skapa en tabell som liknar den nedan.

Tabell 26. Lön

1. I SZ-cellen anger du formeln för att beräkna Ivanovs lön \u003d V1 * VZ.

När du replikerar formeln i detta exempel med relativa referenser i cell C4, visas ett felmeddelande (#VALUE!), eftersom den relativa adressen för cell B1 kommer att ändras och formeln =B2*B4 kopieras till cell C4;

1. Ställ in den absoluta referensen till cell B1 genom att placera markören i formelfältet på B1 och trycka på tangenten F4. Formeln i cell C3 kommer att se ut som =$B$1*B3.
2. Kopiera formeln till cellerna C4 och C5.
3. Spara filen (tabell 27) under namnet Lön.xls.

Tabell 27. Löneresultat

Namn i formler

Namn i formler är lättare att komma ihåg än celladresser, så du kan använda namngivna omfång (enkla eller flera celler) istället för absoluta referenser. Följande regler måste följas när du skapar namn:

• namn kan vara upp till 255 tecken långa;
• namn måste börja med en bokstav och får innehålla vilket tecken som helst utom ett mellanslag;
• namn ska inte se ut som länkar, som OT, C4;
• namn ska inte använda Excel-funktioner som t.ex SUMMA OM och så vidare.

På menyn Infoga, Namn Det finns två olika kommandon för att skapa namngivna områden: Skapa och Tilldela.

Team Skapa låter dig ställa in (ange) önskat namn ( bara en), Tilldela kommando använder etiketterna på kalkylbladet som områdesnamn (det är tillåtet att skapa flera namn samtidigt).

Att skapa ett namn

1. Välj cell B1 (tabell 26).
2. Välj från menyn Insert, Name (Insert, Name) kommandot Tilldela (Definiera).
3. Ange ditt namn Timpris och klicka på OK.
4. Välj cell B1 och se till att namnfältet säger timtaxa.

Skapa flera namn

1. Välj celler ВЗ:С5 (tabell 27).
2. Välj från menyn Kommandot Infoga, Namn (Infoga, Namn) Skapa (Skapa), kommer en dialogruta att visas. Skapa namn(Fig. 88).
3. Se till att alternativknappen i kolumnen till vänster är markerad och klicka OK.
4. Markera cellerna OT:NW och se till att namnfältet säger Ivanov.

Ris. Figur 88. Dialogrutan Skapa namn

Du kan infoga ett namn i en formel istället för en absolut referens.

1. I formelfältet placerar du markören där namnet kommer att läggas till.
2. Välj från menyn Insert, Name (Insert, Name) kommandot Klistra in (Klistra in), Dialogrutan Infoga namn visas.

1. Välj önskat namn i listan och klicka på OK.

Fel i formler

Om ett fel görs när du anger formler eller data, visas ett felmeddelande i den resulterande cellen. Det första tecknet i alla felvärden är #. Felvärdena beror på vilken typ av fel som gjorts.

Excel kan känna igen långt ifrån alla fel, men de som hittas måste kunna rätta till.

Fel # # # # visas när det angivna numret inte får plats i cellen. Öka i så fall kolumnbredden.

Fel #DIV/0! visas när ett försök görs att dividera med noll i en formel. Detta händer oftast när divisorn är en cellreferens som innehåller ett noll- eller tomt värde.

Fel #N/A!är en förkortning för "odefinierad data". Det här felet indikerar användningen av en tom cellreferens i formeln.

Fel #NAME? visas när ett namn som används i en formel har tagits bort eller inte har definierats tidigare. För att korrigera, definiera eller korrigera dataområdets namn, funktionsnamn etc.

Fel #BLANK! visas när skärningspunkten mellan två regioner är specificerad, som faktiskt inte har gemensamma celler. Oftast indikerar felet att ett fel gjordes när cellintervallsreferenser angavs.

Fel #NUMBER! visas när ett ogiltigt format eller argumentvärde används i en funktion med ett numeriskt argument.

Fel #VALUE! visas när ett ogiltigt argument eller operandtyp används i en formel. Till exempel skrivs text i stället för ett numeriskt eller booleskt värde för en operator eller funktion.

Utöver de fel som anges ovan kan en cirkulär referens visas när formler skrivs in.

En cirkulär referens uppstår när en formel direkt eller indirekt inkluderar referenser till sin egen cell. En cirkulär referens kan orsaka förvrängning i kalkylbladsberäkningar och anses därför vara en bugg i de flesta applikationer. Vid inmatning av en cirkulär referens visas ett varningsmeddelande (bild 89).

För att rätta till felet, ta bort cellen som orsakade den cirkulära referensen, redigera eller skriv in formeln igen.

Funktioner i Excel

Mer komplexa beräkningar i Excel-tabeller utförs med hjälp av specialfunktioner (bild 90). Listan över funktionskategorier är tillgänglig när du väljer kommandot Fungera i menyn Infoga (Infoga, Funktion).

Finansiella funktioner utför sådana beräkningar som att beräkna betalningsbeloppet på ett lån, beloppet för betalning av vinst på investeringar, etc.

Datum- och tidsfunktionerna låter dig arbeta med datum- och tidsvärden i formler. Du kan till exempel använda det aktuella datumet i en formel genom att använda funktionen I DAG.

Ris. 90. Funktionsguide

Matematiska funktioner fungerar enkla och komplexa matematiska beräkningar, som att beräkna summan av ett cellområde, absolutvärdet av ett tal, avrundning av tal, etc.

Statistiska funktioner låter dig utföra statistisk analys av data. Du kan till exempel bestämma medelvärdet och variansen för ett urval och mycket mer.

Databasfunktioner kan användas för att utföra beräkningar och för att välja poster efter villkor.

Textfunktioner ge användaren möjlighet att bearbeta text. Du kan till exempel sammanfoga flera strängar med funktionen ANSLUTA.

Booleska funktioner är utformade för att testa ett eller flera villkor. Till exempel IF-funktionen låter dig avgöra om det angivna villkoret är sant, och returnerar ett värde om villkoret är sant och ett annat värde om det är falskt.

Funktioner Kontrollera egenskaper och värdenär avsedda att definiera data som lagras i cellen. Dessa funktioner kontrollerar värdena i cellen enligt tillståndet och returnerar värdena beroende på resultatet. Sant eller falskt.

För tabellberäkningar med inbyggda funktioner rekommenderar vi att du använder funktionsguiden. Dialogrutan Funktionsguide är tillgänglig när du väljer kommandot Funktion i menyn Infoga eller trycka på en knapp , på standardverktygsfältet. Under dialogen med guiden är det nödvändigt att ställa in argumenten för den valda funktionen; för detta är det nödvändigt att fylla i fälten i dialogrutan med motsvarande värden eller adresser för tabellcellerna.

Träning

Beräkning av medelvärdet för varje rad i filen Education.xls.

1. Markera cell F3 och klicka på knappen Funktionsguide.
2. I den första dialogrutan i funktionsguiden, från kategorin Statistisk, välj en funktion GENOMSNITT, klicka på knappen Ytterligare.
3. Argument måste anges i den andra dialogrutan i funktionsguiden. Inmatningsmarkören finns i inmatningsfältet för det första argumentet. I detta fält som argumentnummer! ange adressen för området B3:D3 (bild 91).
4. Klick OK.
5. Kopiera den resulterande formeln till cellerna F4:F6 och spara filen (tabell 28).

Ris. 91 Mata in ett argument i funktionsguiden

Tabell 28. Tabell över beräkningsresultat med hjälp av funktionsguiden

För att ange ett cellintervall i funktionsguidens fönster kan du ringa in detta område på tabellens kalkylblad (i exemplet B3:D3). Om fönstret Funktionsguide täcker de önskade cellerna kan du flytta dialogrutan. Efter att ha valt ett cellintervall (B3:D3), kommer en löpande prickad ram att dyka upp runt den, och adressen till det valda cellområdet visas automatiskt i argumentfältet.