Laboratorieformler i excel. Använda formler och funktioner i MS Excel. Laborationer i Microsoft Excel

Testuppgift 1. Använda formler och funktioner i beräkningar.

Givet: a, b, c, h, l, m, x - alla tal.

Beräkna:

Utföranderesultat:

V=1/3*PI()*B1*(B2*B2+B2*B3+B3*B3)

Test 2. Använda relativa och absoluta referenser i formler. Skapa en tabell "Köpa varor med rabatt före semestern."

Svar på säkerhetsfrågor

1. Vad är en formel i Excel? Vad är dess struktur? Vilka element kan en formel innehålla? Vilka är reglerna för att ange och redigera formler i Excel?

En formel i Excel är en sekvens av tecken som börjar med ett likhetstecken "=". Denna sekvens av tecken kan inkludera konstanta värden, cellreferenser, namn, funktioner eller operatorer.

Du måste ange formeln som börjar med likhetstecknet. Detta är nödvändigt så att Excel förstår att det är en formel och inte data som läggs in i cellen.

3. Hur kan du kopiera och flytta formler?

När du flyttar en cell med en formel ändras inte referenserna i formeln. När du kopierar en formel kan cellreferenser ändras beroende på deras typ.

4. Hur autofyller formler celler?

Samt att autofylla celler med data. Om du behöver göra samma beräkningar i en tabell, använd autoslutförandefunktionen för att undvika att behöva ange samma formler om och om igen.

5. Hur redigeras formler?

Att göra ändringar i kalkylbladet, samt eliminera fel, görs genom att redigera formler i celler. Cellredigeringsläget kan aktiveras på flera sätt.

1. Redigera en formel i formelfältet:

Markera cellen med formeln du vill redigera

Klicka med musen och placera markören i formelfältet.

2. Redigera en formel direkt i en cell:

Dubbelklicka med musen och placera markören inuti cellen. Den här metoden Att redigera en formel fungerar bara om alternativet Redigera direkt i cell på fliken Redigera i dialogrutan Alternativ på Verktyg-menyn är aktiverat.

Aktivera "redigeringsläge":

Välj en cell och tryck på en tangent .

Efter avslutad redigering av formeln måste "redigeringsläge" i cellen stängas av - tryck på knappen eller .

6. Vad är en funktion i Excel? Vad är dess struktur?

Funktioner i Excel används för att utföra standardberäkningar i arbetsböcker. Värdena som används för att utvärdera funktioner kallas argument. De värden som returneras av funktioner som ett svar kallas resultat. Förutom de inbyggda funktionerna kan du använda i beräkningar anpassade funktioner, som skapas med Excel. Funktionsargument skrivs inom parentes omedelbart efter funktionsnamnet och separeras från varandra med semikolon ";". Parenteser låter Excel bestämma var argumentlistan börjar och slutar. Argument måste placeras inom parentes. Kom ihåg att när du skriver en funktion måste det finnas öppnande och avslutande parenteser, och du ska inte infoga mellanslag mellan funktionsnamnet och parenteserna.

Argument kan vara siffror, text, booleaner, matriser, felvärden eller referenser. Argument kan vara antingen konstanter eller formler. Dessa formler kan i sin tur innehålla andra funktioner. Funktioner som är ett argument till en annan funktion kallas kapslade. I Excel formler Du kan använda upp till sju nivåer av kapsling av funktioner.

De angivna ingångsparametrarna måste ha giltiga värden för det givna argumentet. Vissa funktioner kan ha valfria argument som kanske inte finns när funktionens värde beräknas.

För att underlätta användningen är funktionerna i Excel indelade i kategorier: databas- och listhanteringsfunktioner, datum- och tidsfunktioner, DDE/externa funktioner, tekniska funktioner, finansiella, informativa, logiska, bläddrings- och länkfunktioner. Dessutom finns följande kategorier av funktioner: statistisk, text och matematisk.

Med hjälp av textfunktioner är det möjligt att bearbeta text: extrahera tecken, hitta de du behöver, skriva tecken till en strikt definierad plats i texten och mycket mer.

Med hjälp av datum- och tidsfunktionerna kan du lösa nästan alla problem relaterade till datum eller tid (till exempel bestämning av ålder, beräkning av arbetslivserfarenhet, bestämning av antalet arbetsdagar under vilken tidsperiod som helst).

Logiska funktioner hjälper till att skapa komplexa formler som, beroende på uppfyllandet av vissa villkor, kommer att utföra olika typer av databehandling.

8. Vad är skillnaden mellan formler och funktioner? Hur genererar man funktionstext i en dialog?

Funktioner kan vara en del av formler.

Dialogrutan har två listfönster och flera knappar. För enkelhetens skull är de inbyggda funktionerna indelade i kategorier. Fönstret som heter "Kategori" innehåller en lista med funktionskategorier. Och i fönstret som heter "Function" presenteras i alfabetisk ordning lista över funktioner, vald kategori.

Nedanför listan över funktioner finns en mycket sammanfattad hjälp om den markerade funktionen. Men du kan bara läsa denna hjälp med en JAWS-markör. Om du klickar på hjälpknappen öppnas en ny dialogruta med detaljerad hjälp om de valda Excel-funktioner.

Och genom att klicka på "OK"-knappen aktiveras det andra steget i funktionsguiden - inmatningsparametrar eller funktionsargument. Vanligtvis måste du här lista celladresserna och intervallen som är involverade i beräkningarna av denna funktion. Efter att ha angett nästa argument måste du trycka på tab. I slutet måste du trycka på "Enter" för att aktivera knappen "Ok". Dialogrutan för parameterinmatning innehåller också referensinformation, endast tillgänglig för JAWS markör.

Efter att ha specificerat inmatningsparametrarna för den inbyggda funktionen kommer Excel att generera texten i formeln och placera den i cellen där markören var placerad när funktionsguiden anropades.

9. Hur använder man funktionsguiden?

För att hitta den inbyggda Excel-funktionen vi behöver, måste vi gå till "Infoga"-menyn och aktivera "Funktion" -objektet. En dialogruta som heter "Funktionsguide" öppnas.


Slutsatser om utfört laboratoriearbete

I processen med att utföra laboratoriearbete behärskades en teknik för att arbeta med formler och funktioner i en kalkylbladsprocessor. Microsoft Office Excel.

Laboratoriearbete

Ämne: Excel-funktioner

Mål:

    Lär känna de olika klasserna av funktioner;

    Lär dig att använda funktionsguiden;

    Lär dig att använda kapslade funktioner när du arbetar med tabeller.

Funktioner Excel

Fungera– är en beroende variabel kvantitet, vars värde beräknas enligt vissa regler baserade på värden för andra kvantiteter – funktionsargument. Excel erbjuder en stor (flera hundra) uppsättning standardfunktioner (inbyggda) som kan användas i formler, till exempel:

Funktion - från latinets Functio - utförande.

Funktionsnamnet inom parentes följs av en semikolonseparerad lista med argument. Listan med argument kan bestå av siffror, text, booleska värden (TRUE eller FALSE), länkar, formler, kapslade funktioner. Om en formel börjar med en funktion föregås funktionsnamnet av tecknet " = ».

Baserat på argumentens karaktär kan inbyggda funktioner delas in i tre typer:

Med en lista med argument(max – 30 argument): AVERAGE (A2:C23;E6;200;3) – returnerar medelvärdet för argumenten

MED fasta argument: POWER (6,23;4): höjer det första argumentet (6,24) till det andra argumentet (4)

Inga argument:TODAY(): Returnerar det aktuella datumet.

Ange formler

Sekvensen för att mata in en funktion i en formel:

    Funktionsnamn;

    Öppningsparentes;

    Lista över argument separerade med semikolon;

    Avslutande parentes.

Du kan ange en funktion på flera sätt:

Funktioner och formelfält

Om de inte anges manuellt, specificeras argumenten med formelfältet:

Krävs argument markerad med fet stil – utan den kan funktionen inte utföra bearbetning;

Valfritt argument visas med den vanliga stavningen av fältnamnet och dess värde får inte anges. I det här fallet kommer standardvärdena att användas.

Om en formel består av flera funktioner visar formelfältet argumenten för funktionen markerade med fet stil i formelfältet. För att visa argumenten för en annan funktion i formelfältet måste du klicka på dess namn i formelfältet.

Formelfältet kan flyttas runt på skärmen genom att dra det med musen.

Kapslade funktioner

Resultatet av en funktionsutvärdering kan användas som ett argument till en annan funktion. En funktion som används som ett av argumenten till en annan funktion kallas kapslade. Excel stöder upp till 7 nivåer av kapsling av funktioner.

Till exempel:

OM (A4>0; MAX (A9:B19);0)

För att ange en funktion som ett argument du måste utöka listan i formelfältet och antingen välja en av 10 nyligen använda, eller kontakta till funktionsguiden med hjälp av kommandot Andra funktioner..eller skriv in funktionen manuellt.

Specialinsats

Innehållet i en cell kan representeras som en kombination av fyra lager av information: formel, värde, format och anteckning. Excel låter dig kopiera varje lager separat. Information placeras i bufferten som vanligt (kommando Kopiera), och infogas med kommandot Redigera\Klistra in special...

För att kopiera format, precis som andra Office-program, använd standardverktygsfältet - Exempelformat . (Praktiskt arbete "Väderprognos » ).

träning:

    Använd funktionen, fyll block A1:A5 med slumptal i intervallet [-10,10];

    I cell B1, ange en formel för att beräkna hela delen av värdena i kolumn A;

    Kopiera den resulterande formeln till block B2:B5;

    Tillämpa samma sekvens av operationer på funktioner respektive block:

ABS (A) - Cl: C5;

EXP (A) - Dl:D5;

SQRT(A) - E1:E5;

Beräkning av återstoden dividerad med 2 – F 1:F 5;

Avrundning från -1 – H 1:H 5;

Runda med +1 – G 1:G 5

    Skriv formeln i cell A7 belopp element i den första kolumnen (A1:A5)

I cell B7 – aritmetiskt medelvärde enligt (B1:B5)

C7 – maximalt element från (C1:C6)

D 7 – minimumelement (D 1:D 6)

E 7 – antal element (E1:E6)

F 7 – varians av värden (F 1:F 6)

Räckvidd I 1:I 6 fyll i med värdena för trigonometriska funktioner:

I1 - PI

I2 – Sin (A1)

I3 – Cos (A2)

I4 – Tan (A3)

I5 – Atan (A4)

I6 – Asin (A5)

    På rad 10 anger du fältrubrikerna:

Efternamn\Namn Födelsedatum Antal dagar

Justera bredden på kolumnerna och centrera rubrikerna;

    I block A12:A17 anger du efternamn eller förnamn på dina vänner och bekanta. I block B12:B17 finns deras födelsedatum. Ange datum i europeiskt format;

    I cell C9 anger du det aktuella datumet;

    I cell C12, en formel för att beräkna antalet dagar som en person levt för det aktuella datumet;

    Mellan kolumnerna Födelsedatum och Antal dagar, infoga en veckodag-kolumn;

    I den första cellen i kolumnen anger du funktionen för att beräkna veckodagen baserat på födelsedatumet. Kopiera den resulterande formeln till alla celler i kolumnen;

    I en kolumn F skriv "Ung" eller "Gammal" bredvid varje efternamn med den logiska OM-funktionen. Gå in i funktionen med hjälp av funktionsguiden (IF Antal dagar<15000, то «Молодой», иначе «Старый»);

    Spara den resulterande tabellen på disken i en personlig mapp (gruppnamn).

Kontrollfrågor:

    Metoder för att mata in formler i celler;

    Formel bar;

    Obligatoriska och valfria argument i formler;

    Procedur för att köra kapslade funktioner i Microsoft Excel;

    Algoritm för speciell infogning i celler.

Laborationer i datavetenskap

Datorns grunder 1

Skapa formler med hjälp av funktionsguiden 1

Skapa formler med summaknappen 2

Redigera formler 2

Omgång 3

Provsumma 3

Exponentiering och rot 4

Flytta och kopiera formler 4

Använda referenser i formler 4

Använda cell- och intervallnamn 6

Fel vid kontroll 7

Fel i funktioner och argument 7

Spåra samband mellan formler och celler 8

Använda logiska funktioner 9

Villkorlig formatering 10

Markera värden 10

Att lyfta fram extrema värden 12

Formatera med ett histogram 13

Formatering med trefärgsskalan 13

Formatera med ikonuppsättning 14

Hantera regler för villkorlig formatering 15

Laboratoriearbete nr 6

Labb 6: Grunderna för Excel-beräkning Skapa formler med hjälp av funktionsguiden

Funktioner låter dig förenkla formler, särskilt om de är långa eller komplexa. Funktioner används inte bara för direkta beräkningar, utan också för att konvertera tal, till exempel för avrundning, sökning efter värden, jämförelse, etc.

För att skapa formler med funktioner brukar man använda gruppen Funktionsbibliotek flikar Formler.

    Välj cellen där du vill ange formeln.

    Klicka på knappen för önskad funktionskategori i gruppen Funktionsbibliotek och välj önskad funktion.

    I fönstret Funktionsargument I lämpligt fält anger du funktionsargumenten. Cellreferenser kan matas in med tangentbordet, men det är bekvämare att markera celler med musen. För att göra detta, placera markören i lämpligt fält och välj önskad cell eller cellintervall på arket. För att göra det lättare att markera celler, fönstret Funktionsargument kan flyttas eller fällas ihop. Som ett verktygstips visar fönstret syftet med funktionen, och längst ner i fönstret visas en beskrivning av argumentet i vars fält markören för närvarande befinner sig. Observera att vissa funktioner inte har några argument.

    I fönstret Funktionsargument klicka på knappen OK.

För att infoga en funktion är det inte nödvändigt att använda funktionskategoriknapparna i gruppen Funktionsbibliotek. Du kan använda funktionsguiden för att välja önskad funktion. Dessutom kan detta göras när du arbetar på vilken flik som helst.

Funktionsnamn kan anges från tangentbordet när du skapar formler. För att förenkla skapandeprocessen och minska antalet stavfel, använd formelautokomplettering.

    I en cell eller formelfält anger du ett "=" (lika tecken) följt av de första bokstäverna i den funktion du använder. När du skriver visar en rullningslista med möjliga objekt de närmaste värdena.

    Välj önskad funktion genom att dubbelklicka på den med musen.

    Använd tangentbordet och musen för att ange funktionsargumenten. Bekräfta inmatningen av formeln.

Skapa formler med summaknappen

Denna knapp, förutom gruppen Funktionsbibliotek flikar Formler(det heter där Autosumma), även tillgänglig i gruppen Redigering flikar Hem.

För att beräkna summan av siffror i celler som är placerade kontinuerligt i en kolumn eller en rad, välj bara cellen nedanför eller till höger om det summerade intervallet och klicka på knappen Belopp.

För att bekräfta inmatningen av formeln, tryck på knappen Stiga på eller tryck på knappen igen Belopp.

För att beräkna summan av slumpmässigt placerade celler, välj cellen där summan ska beräknas, klicka på knappen Belopp, och välj sedan cellerna och/eller cellintervallen som ska summeras i kalkylbladet. För att bekräfta inmatningen av formeln, tryck på knappen Stiga på eller tryck på knappen igen Belopp.

Funktionssyntax

BELOPP),

där A är en lista med 1 till 30 element som behöver summeras. Elementet kan vara en cell, ett cellintervall, ett tal eller en formel. Referenser till tomma celler, text eller booleska värden ignoreras.

    I den nya boken anger du dina akademiska prestationsdata.

    Beräkna totalpoängen för varje elev.

Förutom att beräkna beloppet, knappen Belopp kan användas för att beräkna medelvärdet, bestämma antalet numeriska värden, hitta max- och minivärden. I det här fallet måste du klicka på knapppilen och välja önskad åtgärd:

Genomsnitt- beräkning av det aritmetiska medelvärdet;

siffra- bestämning av antalet numeriska värden;

Maximal- hitta det maximala värdet;

Minimum- hitta minimivärdet.

Syftet med laborationsarbetet är att studera och befästa färdigheter i datainmatning och användning av formler i Microsoft excel 2007 .

Mata in data i ett kalkylblad

Kalkylbladsceller kan innehålla tre typer av data: numeriska värden (inklusive tid och datum), text och formler. Arbetsbladet, men i ett "grafiklager" ovanpå arket, kan också innehålla bilder, diagram, bilder, knappar och andra objekt.

Ange siffror

Siffror skrivs in med den översta raden på tangentbordet eller det numeriska tangentbordet. Ett kommatecken eller punkt används som decimalavgränsare, du kan ange valutasymboler. Om du anger ett minus eller parentes före ett tal anses det vara negativt. Nollor som skrivs före numret ignoreras av programmet. Om du behöver få ett värde med inledande nollor måste det tolkas som text.

Excel använder 15 siffror för att representera siffror; när du anger ett 16-siffrigt nummer sparas det automatiskt till 15 siffror. Numeriska värden justeras automatiskt till den högra kanten av cellen.

Ange datum och tider

Excel använder ett internt datumnumreringssystem för att representera datum. (Så, det tidigaste datumet som programmet kan känna igen är 1 januari 1900, detta datum tilldelas serienummer 1, nästa datum tilldelas serienummer 2, etc.). Datum skrivs in i ett format som användaren känner igen och känns igen automatiskt. Tidsvärden matas också in i ett av de erkända tidsformaten. Presentationen av datum och tid direkt på kalkylbladet styrs genom att ställa in cellens visningsformat.

Skriver text

All inmatad data som inte känns igen som siffror eller formler behandlas som textvärden. Textvärdena är anpassade till den vänstra kanten av tabellen. Om texten inte får plats i en cell placeras den ovanpå angränsande celler om de är lediga. Parametrar för att placera text i en cell ställs in med cellformatet.



Ange en formel

En formel är vilket matematiskt uttryck som helst. Formeln börjar alltid med tecknet "=" och kan förutom operatorer och cellreferenser innehålla inbyggda Excel-funktioner.

Dataformat

Efter att ha matat in data i en cell försöker Excel automatiskt bestämma dess typ och tilldela cellen det lämpliga formatet - formen för datapresentation. Det är viktigt att tilldela rätt cellformat så att cellen till exempel kan delta i beräkningar (inte vara text).

Excel har en uppsättning standardcellformat som kan användas i alla arbetsböcker (Figur 2.2.17). Du kan aktivera den genom att välja Hem – Tal – Talformat eller använda snabbmenyn för den valda cellen på Nummerfliken i fönstret Cellformat.

Figur 2.2.17. Standardformat

Till en början har alla tabellceller formatet Allmänt. Användningen av format påverkar hur innehållet i cellerna kommer att visas: allmänt - tal visas som heltal, decimalbråk, om talet är för stort, sedan som exponentiellt; numeriskt – standard numeriskt format; finansiellt och monetärt – talet avrundas till 2 decimaler, valutatecknet placeras efter numret, det monetära formatet låter dig visa negativa belopp utan minustecknet och i en annan färg; kort datum och långt datumformat – låter dig välja ett av datumformaten; tid – ger flera tidsformat att välja mellan; - procent – ​​talet (från 0 till 1) i cellen multipliceras med 100, avrundas till närmaste heltal och skrivs med %-tecknet; bråktal – används för att visa tal i form av ett vanligt bråk snarare än en decimal; exponentiell – utformad för att visa tal som produkten av två komponenter: ett tal från 0 till 10 och en potens av 10 (positiv eller negativ); text – när du ställer in detta format kommer alla inmatade värden att uppfattas som text; ytterligare – inkluderar formaten Postnummer, Postnummer+4, Telefonnummer, Personalnummer; alla format – låter dig skapa nya format som en anpassad mall.

Använda verktyg för att påskynda inmatningen av data

När du matar in data i tabellblad kan du använda vissa tekniker för att påskynda inmatningen.

1) Autoslutför medan du skriver. När du anger samma värden i flera celler kan du använda autofyllmarkören (korset i det nedre högra hörnet av den aktiva cellen) för att kopiera värdena till intilliggande celler. Genom att använda snabbmenyn som öppnas genom att högerklicka efter att ha dragit, kan du ställa in ytterligare autofyll-parametrar (till exempel genom att ange siffrorna 1 och 3 i cellerna kan du få en sekvens av nummer i steg om 2 för det valda intervallet av celler).

2) Användning av progression. Om en cell innehåller ett tal, ett datum eller en tidsperiod som kan vara en del av en serie, kommer dess värde att ökas när den kopieras (en aritmetisk eller geometrisk progression, en lista med datum erhålls). För att ställa in en progression måste du välja knappen Fyll i redigeringspanelen på fliken Hem och i dialogrutan Progression som visas, ställa in parametrar för en aritmetisk eller geometrisk progression.

3) Automatisk komplettering medan du skriver. Med den här funktionen kan du automatiskt ange repetitiv textdata. Efter att ha skrivit in text i en cell kommer Excel ihåg den och nästa gång du skriver in den, efter att ha skrivit de första bokstäverna i ordet, erbjuder den ett alternativ för att slutföra inmatningen. För att slutföra inmatningen, tryck på "Enter". Detta kommando kan också nås genom att välja Välj från rullgardinsmenyn från snabbmenyn med höger musknapp. AutoComplete-funktionen fungerar bara på en kontinuerlig sekvens av celler.

4) Använd autokorrigering medan du skriver. AutoCorrect är designat för att automatiskt ersätta vissa specificerade kombinationer av tecken med andra medan du skriver. Du kan till exempel ange att du anger ett enda tecken istället för att skriva in flera ord. Kommandot är tillgängligt via Office-knappen – Excel-alternativ. I Stavning - Autokorrigeringsalternativ måste du ställa in texten och dess förkortning.

5) Använd kortkommandot Ctrl+Enter för att ange upprepade värden. För att ange samma värden i flera celler kan du markera dem, ange värdet i en cell och trycka på Ctrl+Enter. Som ett resultat kommer samma data att matas in i alla valda celler.

Validering av data när du anger

Om du vill vara säker på att rätt data matas in i ett kalkylblad kan du ange kriterier som är giltiga för enskilda celler eller cellintervall. För att ställa in en kontroll, kör kommandot Data – Arbeta med data – Kontrollera data. I fönstret som visas (Figur 2.2.18), ställ in verifieringskriterierna på fliken Parametrar, texten i meddelandet som användaren ska ange på fliken Inmatningsmeddelande och texten i felmeddelandet på fliken Felmeddelande .

Efter att ha använt kommandot Data – Arbeta med data – Cirkel ogiltig data, kommer alla felaktiga data att ringas in i rött.


Figur 2.2.18. Fönster för inställning av dataverifieringsparametrar

Använda formler

En formel i Excel är ett matematiskt uttryck baserat på vilket värdet av en viss cell beräknas. Formler kan använda: numeriska värden; celladresser (relativa, absoluta och blandade referenser); operatorer: matematiska (+, -, *, /, %, ^), jämförelser (=,<, >, >=, <=, < >), textoperator & (för att kombinera flera textsträngar till en), intervallrelationsoperatorer (kolon (:) - intervall, komma (,) - för att kombinera intervall, mellanslag - skärningspunkt mellan intervall); funktioner.

Att ange en formel börjar alltid med tecknet "=". Resultatet av formeln visas i cellen och själva formeln visas i formelfältet. Celladresser i formeln kan matas in manuellt eller helt enkelt genom att klicka på önskade celler.

Efter beräkningen visas det resulterande resultatet i cellen och den skapade formeln visas i formelfältet i inmatningsfönstret.

Celladresseringsmetoder

Celladressen består av kolumnnamnet och radnumret på kalkylbladet (till exempel A1, BM55). I formler anges adresser med länkar - relativa, absoluta eller blandade. Tack vare länkarna finns uppgifterna i olika delar ark, kan användas i flera formler samtidigt.

En relativ referens indikerar platsen för den önskade cellen i förhållande till den aktiva (dvs nuvarande) cellen. Vid kopiering av formler ändras dessa länkar automatiskt i enlighet med formelns nya position (Exempel på länkpost: A2, C10).

En absolut referens pekar på exakt plats celler som ingår i formeln. När du kopierar formler ändras inte dessa länkar. För att skapa en absolut cellreferens, placera ett dollartecken ($) framför kolumn- och radbeteckningarna (Exempel på referensnotation: $A$2, $C$10). För att fixa en del av celladressen från ändringar (efter kolumn eller rad) vid kopiering av formler används en blandad referens med fixering av önskad parameter. (Exempel på länkpost: $A2, C$10).

Anteckningar

· För att undvika att manuellt skriva dollartecken när du skriver länkar kan du använda F4-tangenten, som låter dig "sortera igenom" alla typer av länkar för en cell.

Inbyggda Excel-funktioner

Varje funktion har sin egen syntax och operationsordning som måste följas för att beräkningarna ska bli korrekta. Funktionsargument skrivs inom parentes, och funktioner kan ha eller inte ha argument, när du använder dem måste du ta hänsyn till typen av argument. En funktion kan fungera som ett argument till en annan funktion, i vilket fall den kallas en kapslad funktion. I det här fallet kan upp till flera nivåer av funktionskapsling användas i formler.

Excel 2007 innehåller matematiska, logiska, ekonomiska, statistiska, text- och andra funktioner. Namnet på funktionen i formeln kan matas in manuellt från tangentbordet (detta aktiverar formlerverktyget AutoComplete, som låter dig välja önskad funktion baserat på de första bokstäverna som anges (Figur 2.2.19)), eller så kan du välja det i fönstret Funktionsguide, aktiverad av knappen på panelen Funktionsbibliotek på fliken Formler eller från funktionsgrupper på samma panel, eller med knappen Redigera panel på fliken Hem.

Figur 2.2.19. Autoslutför formler

Formler kan redigeras precis som innehållet i vilken annan cell som helst. Så här redigerar du innehållet i en formel: dubbelklicka på cellen med formeln eller tryck på F2 eller redigera innehållet på formelinmatningsraden.

Namnge och använda cellnamn

Excel 2007 tillhandahåller en användbar funktion för att namnge celler eller intervall. Detta kan vara särskilt praktiskt när du skriver formler. Genom att till exempel ange namnet Totalt_för_år för en cell kan du använda detta namn istället för celladressen i alla formler.

Cellnamnet kan vara giltigt inom ett ark eller en arbetsbok; det måste vara unikt och inte dubbletter av cellnamn. För att namnge celler måste du markera cellen eller området och ange ett nytt namn i namnlisten. Eller använd knappen Tilldela ett namn till panelen Definierade namn på fliken Formler och ta fram dialogrutan (Figur 2.2.20) för att ställa in de nödvändiga parametrarna.

Figur 2.2.20. Fönstret för att skapa namn

För att se alla tilldelade namn, använd kommandot Namnhanterare. Du kan också få en lista över alla namn med celladresser på arket med kommandot Använd i formel - Infoga namn på panelen Definierade namn.

För att infoga ett namn i en formel kan du använda kommandot Använd i formel och välja önskat cellnamn från listan.

Kommentar. Ett namn kan inte bara tilldelas cellområden utan också till en formel. Detta är praktiskt när du använder kapslade formler.

Visar beroenden i formler

För att hjälpa till att identifiera fel när du skapar formler kan du visa cellberoenden. Beroenden används för att visa relationer i en tabellruta mellan celler med formler och celler med värden som var involverade i dessa formler. Beroenden visas bara inom en öppen bok. När du skapar ett beroende använder du påverkande celler och beroende celler.

En påverkande cell är en cell som refererar till en formel i en annan cell.

En beroende cell är en cell som innehåller en formel.

För att visa cellrelationer måste du välja kommandona Influencing Cells eller Dependent Cells i rutan Formelberoende på fliken Formler. För att inte visa beroenden, använd kommandot Ta bort pilar från samma panel.

Figur 2.2.21. Visar påverkande celler

Lägen för att arbeta med formler

Excel har ett automatiskt beräkningsläge, tack vare vilket formler på ark räknas om direkt. När du placerar ett mycket stort antal (upp till flera tusen) komplexa formler på ett ark, kan arbetshastigheten minska märkbart på grund av omräkningen av alla formler på arket. För att styra beräkningsprocessen med formler måste du ställa in manuellt läge beräkningar genom att använda kommandot Formler – Beräkning – Beräkningsparametrar – Manuell. Efter att du har gjort ändringar måste du anropa kommandot Beräkna (för att räkna om data på ett kalkylblad) eller Beräkna om (för att räkna om hela arbetsboken) i beräkningspanelen.

En användbar funktion för att arbeta med formler är att visa alla formler på ett ark. Detta kan göras med kommandot Formler – Formelberoenden – Visa formler. Efter detta kommer de skrivna formlerna att visas i cellerna istället för de beräknade värdena. Att återvända till normalt läge Du måste klicka på knappen Visa formler igen.

Om en formel returnerar ett felaktigt värde kan Excel hjälpa dig att identifiera cellen som orsakar felet. För att göra detta måste du aktivera kommandot Formler – Formelberoenden – Sök efter fel – Felkälla. Kommandot Kontrollera efter fel hjälper till att identifiera alla felaktiga formelposter.

För att felsöka formler finns det en formelutvärderare som heter Formler - Formelberoenden - Utvärdera formel, som visar steg-för-steg-beräkningar i komplexa formler

Verkstad:.

1. Skapa en tabell för att beräkna summan av en serie (alternativ för uppgifter för att beräkna summan av en serie - se nedan). När du skapar en tabell, använd inbyggda funktioner, absolut och relativ adressering och autofyllning av celler.

2. Beroende på antalet termer n, arrangera tabellen enligt följande:

Tabell 19.

x i 1 2 … n S Y
0,1
0,2
.
.
1

Tabell 20.

jag x 0,1 0,2 … 1
1
2
.
.
n
S
Y

3. Använd villkorlig formatering och markera negativa tal i blått, nummer större än 1,5 i rött.

4. Skapa en tabell. Ett exempel på design är nedan. Steget för att ändra x beroende på varianten av uppgiften är 0,1 (eller Pi/*).


5. Konstruera grafer s=f(x) och y=f(x) i ett koordinatnät (på ett diagram).

6. Studera möjligheterna att använda funktioner (för en lista över funktioner, se uppgiftsalternativet), ge ett exempel på hur varje funktion fungerar.

Tabell 21. Uppgiftsalternativ

1. Öppna ett Microsoft Excel 2007-kalkylblad och skapa en arbetsbok med namnet Laboratoriearbete nr 1.

2. Det är nödvändigt att skapa en tabell för beräkning av löner för företagets anställda.

3. För att förenkla datainmatningen i tabellen, skapa en rullgardinslista (Fig. 22) som innehåller de fullständiga namnen på företagets anställda.

Ris. 22. Dropdown-lista

4. Infoga ett annat ark i Excel-arbetsboken med hjälp av genvägen i raden Bladetikett.

5. Skapa en lista över anställda på ett nytt blad (bild 23).

Ris. 23. Förteckning över företagets anställda

6. För att sortera namn i alfabetisk ordning, kör kommandot: fliken menyfliksområdet Data grupp Sortering och filtrering knapp .

7. Välj cellområdet A1:A10 och klicka på rutan namn i den vänstra kanten av formelfältet. Ange till exempel ett namn för cellerna Anställda. Tryck på knappen Stiga på.

8. För att förhindra att andra användare visar eller ändrar den resulterande listan, skydda och dölj arket där det finns.

9. Högerklicka på arkfliken. Välj kommandot från snabbmenyn.

10. I dialogrutan Lakanskydd(Fig. 24) ange ett lösenord för att inaktivera arkskyddet. I kapitel Tillåt alla användare av detta ark Avmarkera alla objekt. Klicka på knappen OK.

Ris. 24. Dialogrutan Arkskydd

11. I dialogrutan lösenordsbekräftelse ange lösenordet igen.

12. Högerklicka på arkgenvägen och välj kommandot från snabbmenyn Dölj.

13. Gå till Blad 1 och skapa en tabell Löneberedning
(Fig. 25). Kolumn Fullständiga namn fyll i med hjälp av rullgardinsmenyn.

Ris. 25. Tabellstruktur

14. Välj intervallet av celler där du vill placera rullgardinsmenyn.

15. På fliken Data i grupp Arbeta med data Välj lag Datakontroll.

16. I dialogrutan Datakontroll ange datatyp och källa (fig. 26).

17. Öppna en flik Meddelande som ska matas in(Fig. 27). Fyll i de tomma fälten.

Ris. 26. Dialogrutan Datavalidering

Ris. 27. Meddelande vid inmatning av data

18. Gå till fliken Felmeddelande(Fig. 28). Fyll i fälten Se, Rubrik Och Meddelande.

Ris. 28. Meddelande vid datainmatningsfel

19. För tabellrubriker, ställ in textomslutning(knappen på verktygsfältet Inriktning bandflikar Hem).

20. Frys de två första kolumnerna och tabellrubrikraden. För att göra detta, välj ett cellintervall C5:I20 och kör kommandot: ribbon tab Se grupp Fönster knapp .

21. Kolumn Lön Fyll med godtyckliga data och ställ in valutaformatet för cellerna med kommandot:



bandfliken Hem verktygsfältet siffra Välj i rullgardinsmenyn för format Valutaformat.

22. Låt oss skapa en formel för att beräkna bonusen, som är 20% av lönen. Varje formel börjar med tecknet = , så låt oss gå till cellen F5 och ange formeln = E5*20 %(eller = E5*0,2).

23. Använd autofyllmarkören (svart kryss nära det nedre högra hörnet av den markerade cellen), kopiera formeln till området
F6:F11.

24. Mellan kolumnerna Pris Och Inkomstskatt infoga kolumn Totalt upplupet, där beräkna beloppet Lön + Pris.

25. Fyll i de återstående kolumnerna i tabellen med hänsyn till att inkomstskatten är 13 % av det upplupna beloppet.

26. Beräkna beloppet som ska ges ut i dollar, för att göra detta, ställ in den aktuella dollarkursen, till exempel 32, och i cellen J5 ange formeln: = I5/$C$14. Skylt $ används i formeln så att celladresseringen inte ändras när du kopierar med autofyllmarkören.

27. För celler som innehåller monetära data, ställ in lämpligt format.

28. Använda funktionen BELOPP, beräkna det totala beloppet för inkomstskatten. För detta:

· placera markören i en cell H12;

· sätt tecknet =;

· i formelfältet, klicka på knappen;

· i funktionsguidens dialogruta som visas (bild 29), välj en kategori Matematisk, funktion BELOPP;

· som funktionsargument BELOPP välj summeringsintervallet H5:H11;

· tryck på knappen OK.

29. Beräkna på samma sätt det totala beloppet som ska ges ut i dollar och det totala beloppet som ska ges ut i rubel.

Ris. 29. Funktionsguide

30. Hitta medelvärdet ( GENOMSNITT), minimum ( MIN) och max ( MAX) löner.

31. Använd villkorlig formatering, markera i rött Belopp som ska utfärdas, mindre än 5 500 rub. Kör kommandot: fliken ribbon Hem grupp Stilar rullgardinsmenyn Villkorlig formatering Cellvalsregler.

32. Bygg ett diagram Lön företagets anställda
(Fig. 30). Välj kolumner på en gång FULLSTÄNDIGA NAMN. Och Belopp som ska utfärdas(håll ned Ctrl-tangenten) och på menyfliken Föra in på verktygsfältet Diagram välj vy stapeldiagram.

33. Använda menyfliken Layout, infoga axeletiketter och diagramtitel.

Ris. 30. Exempel på diagramdesign

34. Konstruera ett cirkeldiagram som visar förhållandet mellan det totala beloppet som ska utfärdas och den totala inkomstskatten (Fig. 31).

Ris. 31. Exempel på en cirkeldiagramdesign