Laboratoriearbeidsformler i excel. Bruke formler og funksjoner i MS Excel. Laboratoriearbeid i Microsoft Excel

Testoppgave 1. Bruke formler og funksjoner i beregninger.

Gitt: a, b, c, h, l, m, x - alle tall.

Regne ut:

Utførelsesresultat:

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

Test 2. Bruke relative og absolutte referanser i formler. Opprette en tabell "Kjøpe varer med rabatt før ferien."

Svar på sikkerhetsspørsmål

1. Hva er en formel i Excel? Hva er dens struktur? Hvilke elementer kan en formel inneholde? Hva er reglene for å legge inn og redigere formler i Excel?

En formel i Excel er en sekvens av tegn som starter med et likhetstegn "=". Denne sekvensen av tegn kan inkludere konstante verdier, cellereferanser, navn, funksjoner eller operatorer.

Du må skrive inn formelen som starter med likhetstegnet. Dette er nødvendig for at Excel skal forstå at det er en formel og ikke data som legges inn i cellen.

3. Hvordan kan du kopiere og flytte formler?

Når du flytter en celle med en formel, endres ikke referansene i formelen. Når du kopierer en formel, kan cellereferanser endres avhengig av typen.

4. Hvordan fyller formler celler automatisk?

Samt autofyll celler med data. Hvis du trenger å gjøre de samme beregningene i en tabell, bruk autofullfør-funksjonen for å unngå å måtte angi de samme formlene om og om igjen.

5. Hvordan redigeres formler?

Å gjøre endringer i regnearket, samt eliminere feil, gjøres ved å redigere formler i celler. Celleredigeringsmodus kan aktiveres på flere måter.

1. Redigere en formel i formellinjen:

Velg cellen med formelen du vil redigere

Klikk med musen og plasser markøren i formellinjen.

2. Redigere en formel direkte i en celle:

Dobbeltklikk med musen og plasser markøren inne i cellen. Denne metoden Redigering av en formel fungerer bare hvis alternativet Rediger direkte i celle på Rediger-fanen i dialogboksen Alternativer på Verktøy-menyen er aktivert.

Aktiver "redigeringsmodus":

Velg en celle og trykk på en tast .

Etter å ha fullført redigeringen av formelen, må "redigeringsmodus" i cellen slås av - trykk på tasten eller .

6. Hva er en funksjon i Excel? Hva er dens struktur?

Funksjoner i Excel brukes til å utføre standardberegninger i arbeidsbøker. Verdiene som brukes til å evaluere funksjoner kalles argumenter. Verdiene som returneres av funksjoner som en respons kalles resultater. I tillegg til de innebygde funksjonene kan du bruke i beregninger tilpassede funksjoner, som er opprettet ved hjelp av Excel. Funksjonsargumenter skrives i parentes umiddelbart etter funksjonsnavnet og atskilt fra hverandre med semikolon ";". Parentes lar Excel bestemme hvor argumentlisten begynner og slutter. Argumenter må plasseres innenfor parentesen. Husk at når du skriver en funksjon, må det være åpne- og lukkeparenteser, og du skal ikke sette inn mellomrom mellom funksjonsnavnet og parentesene.

Argumenter kan være tall, tekst, booleaner, matriser, feilverdier eller referanser. Argumenter kan enten være konstanter eller formler. I sin tur kan disse formlene inneholde andre funksjoner. Funksjoner som er et argument for en annen funksjon kalles nestede. I Excel-formler Du kan bruke opptil syv nivåer av nesting av funksjoner.

De angitte inngangsparametrene må ha gyldige verdier for det gitte argumentet. Noen funksjoner kan ha valgfrie argumenter som kanskje ikke er til stede når funksjonens verdi beregnes.

For enkel bruk er funksjoner i Excel delt inn i kategorier: database- og listeadministrasjonsfunksjoner, dato- og klokkeslettfunksjoner, DDE/eksterne funksjoner, ingeniørfunksjoner, økonomiske, informative, logiske, surfe- og koblingsfunksjoner. I tillegg finnes følgende kategorier av funksjoner: statistisk, tekst og matematisk.

Ved hjelp av tekstfunksjoner er det mulig å behandle tekst: trekke ut tegn, finne de du trenger, skrive tegn til et strengt definert sted i teksten, og mye mer.

Ved å bruke dato- og klokkeslettfunksjonene kan du løse nesten alle problemer knyttet til dato eller klokkeslett (for eksempel å bestemme alder, beregne arbeidserfaring, bestemme antall arbeidsdager i en hvilken som helst tidsperiode).

Logiske funksjoner bidrar til å lage komplekse formler som, avhengig av oppfyllelsen av visse betingelser, vil utføre ulike typer databehandling.

8. Hva er forskjellen mellom formler og funksjoner? Hvordan generere funksjonstekst i en dialog?

Funksjoner kan være en del av formler.

Dialogen har to listevinduer og flere knapper. For enkelhets skyld er de innebygde funksjonene delt inn i kategorier. Vinduet kalt "Kategori" inneholder en liste over funksjonskategorier. Og i vinduet som heter "Funksjon" presenteres i alfabetisk rekkefølge liste over funksjoner, valgt kategori.

Nedenfor listen over funksjoner er en svært kortfattet hjelp om den uthevede funksjonen. Men du kan bare lese denne hjelpen ved å bruke en JAWS-markør. Hvis du klikker på hjelpeknappen, åpnes en ny dialogboks med detaljert hjelp om de valgte Excel-funksjoner.

Og ved å klikke på "OK"-knappen aktiveres det andre trinnet i funksjonsveiviseren - angi inndataparametere eller funksjonsargumenter. Vanligvis må du her liste opp celleadressene og områdene som er involvert i beregningene av denne funksjonen. Etter å ha lagt inn neste argument, må du trykke på tab. På slutten må du trykke "Enter" for å aktivere "Ok"-knappen. Dialogboksen for parameterinntasting inneholder også referanse informasjon, kun tilgjengelig for JAWS-markøren.

Etter å ha spesifisert inngangsparametrene til den innebygde funksjonen, vil Excel generere teksten til formelen og plassere den i cellen der markøren var plassert når funksjonsveiviseren ble kalt.

9. Hvordan bruker jeg funksjonsveiviseren?

For å finne den innebygde Excel-funksjonen vi trenger, må vi gå til "Sett inn"-menyen og aktivere elementet "Funksjon". En dialog kalt "Funksjonsveiviser" åpnes.


Konklusjoner om utført laboratoriearbeid

I prosessen med å utføre laboratoriearbeid ble en teknikk for å arbeide med formler og funksjoner i en regnearkprosessor mestret. Microsoft Office Utmerke.

Laboratoriearbeid

Emne: Excel-funksjoner

Mål:

    Bli kjent med de forskjellige funksjonsklassene;

    Lær å bruke funksjonsveiviseren;

    Lær å bruke nestede funksjoner når du arbeider med tabeller.

Funksjoner utmerke

Funksjon– er en avhengig variabel mengde, hvis verdi beregnes i henhold til visse regler basert på verdiene til andre mengder – funksjonsargumenter. Excel tilbyr et stort (flere hundre) sett med standard (innebygde) funksjoner som kan brukes i formler, for eksempel:

Funksjon - fra latin Functio - utførelse.

Funksjonsnavnet i parentes etterfølges av en semikolonseparert liste med argumenter. Listen over argumenter kan bestå av tall, tekst, boolske verdier (TRUE eller FALSE), lenker, formler, nestede funksjoner. Hvis en formel begynner med en funksjon, innledes funksjonsnavnet av tegnet " = ».

Basert på arten av argumentene kan innebygde funksjoner deles inn i tre typer:

Med en liste over argumenter(maksimalt – 30 argumenter): AVERAGE (A2:C23;E6;200;3) – returnerer gjennomsnittsverdien til argumentene

MED faste argumenter: POWER (6,23;4): hever det første argumentet (6,24) til kraften til det andre argumentet (4)

Ingen argumenter:TODAY(): Returnerer gjeldende dato.

Legge inn formler

Sekvensen for å legge inn en funksjon i en formel:

    Funksjonsnavn;

    Åpningsparentes;

    Liste over argumenter atskilt med semikolon;

    Avslutningsparentes.

Du kan angi en funksjon på flere måter:

Funksjoner og formellinje

Hvis det ikke legges inn manuelt, spesifiseres argumentene ved hjelp av formellinjen:

Nødvendig argument uthevet med fet skrift – uten det kan ikke funksjonen utføre behandling;

Valgfritt argument vises med vanlig stavemåte for feltnavnet, og verdien kan ikke angis. I dette tilfellet vil standardverdiene bli brukt.

Hvis en formel består av flere funksjoner, viser formellinjen argumentene til funksjonen uthevet med fet skrift i formellinjen. For å vise argumentene til en annen funksjon i formellinjen, må du klikke på navnet i formellinjen.

Formellinjen kan flyttes rundt på skjermen ved å dra den med musen.

Nestede funksjoner

Resultatet av en funksjonsevaluering kan brukes som et argument til en annen funksjon. En funksjon som brukes som ett av argumentene til en annen funksjon kalles nestet. Excel støtter opptil 7 nivåer av nesting av funksjoner.

For eksempel:

HVIS (A4>0; MAKS (A9:B19);0)

For å legge inn en funksjon som et argument du må utvide listen i formellinjen og enten velge en av 10 nylig brukt, eller ta kontakt til funksjonsveiviseren ved å bruke kommandoen Andre funksjoner..eller skriv inn funksjonen manuelt.

Spesialinnsats

Innholdet i en celle kan representeres som en kombinasjon av fire lag med informasjon: formel, verdi, format og notat. Excel lar deg kopiere hvert lag separat. Informasjon legges i bufferen som vanlig (kommando Kopiere), og settes inn ved hjelp av kommandoen Rediger\Lim inn spesial...

For å kopiere formater, akkurat som andre Office-applikasjoner, bruk standardverktøylinjen - Eksempelformat . (Praktisk jobb "Værmelding » ).

trening:

    Bruk funksjonen, fyll blokk A1:A5 med tilfeldige tall i området [-10,10];

    I celle B1, skriv inn en formel for å beregne hele delen av verdiene i kolonne A;

    Kopier den resulterende formelen inn i blokk B2:B5;

    Bruk samme sekvens av operasjoner på henholdsvis funksjoner og blokker:

ABS (A) - Cl: C5;

EXP (A) - D1:D5;

SQRT(A) - E 1:E 5;

Beregning av resten ved delt på 2 – F 1:F 5;

Avrunding fra -1 – H 1:H 5;

Rund med +1 – G 1:G 5

    Skriv formelen i celle A7 beløp elementer i den første kolonnen (A1:A5)

I celle B7 – aritmetisk gjennomsnitt i henhold til (B1:B5)

C7 – maksimalt element fra (C1:C6)

D 7 – minimumselement (D 1:D 6)

E 7 – antall elementer (E1:E6)

F 7 – varians av verdier (F 1:F 6)

Område I 1:I 6 fyll ut med verdiene for trigonometriske funksjoner:

I1 - PI

I2 – Sin (A1)

I3 – Cos (A2)

I4 – Tan (A3)

I5 – Atan (A4)

I6 – Asin (A5)

    I linje 10 skriver du inn feltoverskriftene:

Etternavn\Navn Fødselsdato Antall dager

Juster bredden på kolonnene og sentrer overskriftene;

    I blokk A12:A17 skriver du inn etternavn eller fornavn til dine venner og bekjente. I blokk B12:B17 er fødselsdatoene deres. Skriv inn datoen i europeisk format;

    Skriv inn gjeldende dato i celle C9;

    I celle C12, en formel for å beregne antall dager levd av en person for gjeldende dato;

    Sett inn en ukedag-kolonne mellom kolonnene Fødselsdato og Antall dager;

    I den første cellen i kolonnen skriver du inn funksjonen for å beregne ukedagen basert på fødselsdatoen. Kopier den resulterende formelen til alle cellene i kolonnen;

    I en kolonne F skriv "Ung" eller "Gammel" ved siden av hvert etternavn ved å bruke den logiske HVIS-funksjonen. Gå inn i funksjonen ved hjelp av funksjonsveiviseren (HVIS Antall dager<15000, то «Молодой», иначе «Старый»);

    Lagre den resulterende tabellen på disken i en personlig mappe (gruppenavn).

Kontrollspørsmål:

    Metoder for å legge inn formler i celler;

    Formel bar;

    Nødvendige og valgfrie argumenter i formler;

    Prosedyre for å utføre nestede funksjoner i Microsoft Excel;

    Algoritme for spesiell innsetting i celler.

Laboratoriearbeid i informatikk

Grunnleggende databehandling 1

Opprette formler ved hjelp av funksjonsveiviseren 1

Lage formler ved hjelp av Sum-knappen 2

Redigere formler 2

runde 3

Eksempelsum 3

Eksponentiering og rot 4

Flytte og kopiere formler 4

Bruke referanser i formler 4

Bruke celle- og områdenavn 6

Feil ved kontroll 7

Feil i funksjoner og argumenter 7

Spor forhold mellom formler og celler 8

Bruke logiske funksjoner 9

Betinget formatering 10

Fremheve verdier 10

Fremheve ekstreme verdier 12

Formatering ved hjelp av et histogram 13

Formatering ved hjelp av trefargeskalaen 13

Formatering ved hjelp av ikonsett 14

Administrere regler for betinget formatering 15

Laboratoriearbeid nr. 6

Lab 6: Grunnleggende om Excel-beregning Lage formler ved hjelp av funksjonsveiviseren

Funksjoner lar deg forenkle formler, spesielt hvis de er lange eller komplekse. Funksjoner brukes ikke bare for direkte beregninger, men også for å konvertere tall, for eksempel for avrunding, søk etter verdier, sammenligning osv.

For å lage formler med funksjoner bruker du vanligvis gruppen Funksjonsbibliotek faner Formler.

    Velg cellen der du vil skrive inn formelen.

    Klikk på knappen for ønsket funksjonskategori i gruppen Funksjonsbibliotek og velg ønsket funksjon.

    I vinduet Funksjonsargumenter Skriv inn funksjonsargumentene i riktig felt. Cellereferanser kan legges inn ved hjelp av tastaturet, men det er mer praktisk å velge celler med musen. For å gjøre dette, plasser markøren i det aktuelle feltet og velg ønsket celle eller celleområde på arket. For å gjøre det lettere å velge celler, vinduet Funksjonsargumenter kan flyttes eller slås sammen. Som et verktøytips viser vinduet formålet med funksjonen, og nederst i vinduet vises en beskrivelse av argumentet i hvis felt markøren befinner seg. Vær oppmerksom på at noen funksjoner ikke har noen argumenter.

    I vinduet Funksjonsargumenter klikk på knappen OK.

For å sette inn en funksjon er det ikke nødvendig å bruke funksjonskategoriknappene i gruppen Funksjonsbibliotek. Du kan bruke funksjonsveiviseren til å velge ønsket funksjon. Dessuten kan dette gjøres når du arbeider i en hvilken som helst fane.

Funksjonsnavn kan legges inn fra tastaturet når du lager formler. For å forenkle opprettelsesprosessen og redusere antall skrivefeil, bruk formelautofullføring.

    I en celle eller formellinje skriver du inn et "=" (likhetstegn) etterfulgt av de første bokstavene i funksjonen du bruker. Mens du skriver, viser en rulleliste over mulige elementer de nærmeste verdiene.

    Velg ønsket funksjon ved å dobbeltklikke på den med musen.

    Bruk tastaturet og musen til å angi funksjonsargumentene. Bekreft å skrive inn formelen.

Lage formler ved å bruke Sum-knappen

Denne knappen, i tillegg til gruppen Funksjonsbibliotek faner Formler(det heter der Autosum), også tilgjengelig i gruppen Redigering faner hjem.

For å beregne summen av tall i celler som ligger kontinuerlig i én kolonne eller én rad, velg bare cellen under eller til høyre for det summerte området og klikk på knappen Sum.

For å bekrefte inntastingen av formelen, trykk på tasten Tast inn eller trykk på knappen igjen Sum.

For å beregne summen av tilfeldig plasserte celler, velg cellen der summen skal beregnes, klikk på knappen Sum, og velg deretter cellene og/eller celleområdene som skal summeres i regnearket. For å bekrefte inntastingen av formelen, trykk på tasten Tast inn eller trykk på knappen igjen Sum.

Funksjonssyntaks

SUM),

der A er en liste med 1 til 30 elementer som må summeres. Elementet kan være en celle, et celleområde, et tall eller en formel. Referanser til tomme celler, tekst eller boolske verdier ignoreres.

    I den nye boken, skriv inn dine akademiske prestasjonsdata.

    Regn ut totalpoengsummen for hver elev.

I tillegg til å beregne beløp, knappen Sum kan brukes til å beregne gjennomsnittsverdien, bestemme antall numeriske verdier, finne maksimums- og minimumsverdier. I dette tilfellet må du klikke på knappepilen og velge ønsket handling:

Gjennomsnitt- beregning av det aritmetiske gjennomsnittet;

Antall- bestemmelse av antall numeriske verdier;

Maksimum- finne den maksimale verdien;

Minimum- finne minimumsverdien.

Hensikten med laboratoriearbeidet er å studere og konsolidere ferdigheter i dataregistrering og bruk av formler i Microsoft Excel 2007 .

Legge inn data i et regneark

Regnearkceller kan inneholde tre typer data: numeriske verdier (inkludert tid og dato), tekst og formler. Regnearket, men i et "grafikklag" på toppen av arket, kan også inneholde bilder, diagrammer, bilder, knapper og andre objekter.

Tast inn tall

Tall legges inn ved hjelp av den øverste raden på tastaturet eller det numeriske tastaturet. Et komma eller punktum brukes som desimalskilletegn; du kan angi valutasymboler. Hvis du skriver inn et minus eller parentes foran et tall, regnes det som negativt. Nuller skrevet før nummeret ignoreres av programmet. Hvis du trenger å få en verdi med innledende nuller, må den tolkes som tekst.

Excel bruker 15 sifre for å representere tall; når du skriver inn et 16-sifret tall, vil det automatisk lagres til 15 sifre. Numeriske verdier justeres automatisk til høyre kant av cellen.

Legge inn datoer og klokkeslett

Excel bruker et internt datonummereringssystem for å representere datoer. (Så den tidligste datoen som programmet kan gjenkjenne er 1. januar 1900, denne datoen er tildelt serienummer 1, neste dato er tildelt serienummer 2 osv.). Datoer legges inn i et format som er kjent for brukeren og gjenkjennes automatisk. Tidsverdier legges også inn i et av de anerkjente tidsformatene. Presentasjonen av dato og klokkeslett direkte på regnearket styres ved å angi cellens visningsformat.

Skriver inn tekst

Alle inntastede data som ikke gjenkjennes som tall eller formler, behandles som tekstverdier. Tekstverdier er justert til venstre kant av tabellen. Hvis teksten ikke passer i én celle, plasseres den på toppen av tilstøtende celler hvis de er ledige. Parametre for å plassere tekst i en celle angis ved hjelp av celleformatet.



Angi en formel

En formel er et hvilket som helst matematisk uttrykk. Formelen begynner alltid med "="-tegnet og kan inkludere, i tillegg til operatorer og cellereferanser, innebygde Excel-funksjoner.

Dataformater

Etter å ha lagt inn data i en celle, prøver Excel automatisk å bestemme typen og tilordne cellen det riktige formatet - formen for datapresentasjon. Det er viktig å tildele riktig celleformat slik at for eksempel cellen kan delta i beregninger (ikke være tekst).

Excel har et sett med standard celleformater som kan brukes i alle arbeidsbøker (Figur 2.2.17). Du kan aktivere den ved å velge Hjem – Tall – Tallformat, eller bruke kontekstmenyen for den valgte cellen på Tall-fanen i Celleformat-vinduet.

Figur 2.2.17. Standard formater

Til å begynne med har alle tabellceller formatet Generelt. Bruken av formater påvirker hvordan innholdet i cellene vil vises: generelt - tall vises som heltall, desimalbrøker, hvis tallet er for stort, så som eksponentielt; numerisk – standard numerisk format; finansiell og monetær - tallet er avrundet til 2 desimaler, valutategnet er plassert etter tallet, det monetære formatet lar deg vise negative beløp uten minustegnet og i en annen farge; kort dato og lang datoformat – lar deg velge ett av datoformatene; tid – gir flere tidsformater å velge mellom; - prosent – ​​tallet (fra 0 til 1) i cellen multipliseres med 100, avrundet til nærmeste hele tall og skrevet med %-tegnet; brøk – brukes til å vise tall i form av en vanlig brøk i stedet for en desimal; eksponentiell – designet for å vise tall som et produkt av to komponenter: et tall fra 0 til 10 og en potens på 10 (positiv eller negativ); tekst – når du angir dette formatet, vil enhver inntastet verdi bli oppfattet som tekst; tillegg – inkluderer formatene Postnummer, Postnummer+4, Telefonnummer, Personalnummer; alle formater – lar deg lage nye formater som en egendefinert mal.

Bruke verktøy for å øke hastigheten på dataregistrering

Når du legger inn data i tabellark, kan du bruke noen teknikker for å øke hastigheten på oppføringen.

1) Autofullfør mens du skriver. Når du skriver inn de samme verdiene i flere celler, kan du bruke autofyll-markøren (krysset i nedre høyre hjørne av den aktive cellen) for å kopiere verdiene til tilstøtende celler. Ved å bruke kontekstmenyen som åpnes ved å høyreklikke etter å ha trukket, kan du angi flere autofyllparametere (for eksempel ved å skrive inn tallene 1 og 3 i cellene, kan du få en tallsekvens i trinn på 2 for det valgte området av celler).

2) Bruk av progresjon. Hvis en celle inneholder et tall, en dato eller en tidsperiode som kan være en del av en serie, vil verdien økes når den kopieres (en aritmetisk eller geometrisk progresjon, en liste over datoer oppnås). For å angi en progresjon, må du velge Fyll-knappen på redigeringspanelet på Hjem-fanen og i dialogboksen Progresjon som vises, angi parametere for en aritmetisk eller geometrisk progresjon.

3) Autofullføring mens du skriver. Denne funksjonen lar deg automatisk legge inn repeterende tekstdata. Etter å ha skrevet inn tekst i en celle, husker Excel den, og neste gang du skriver den inn, etter å ha skrevet inn de første bokstavene i ordet, tilbyr den et alternativ for å fullføre oppføringen. For å fullføre oppføringen, trykk "Enter". Denne kommandoen kan også nås ved å velge Velg fra rullegardinlisten fra kontekstmenyen ved å bruke høyre museknapp. Autofullfør-funksjonen fungerer bare på en kontinuerlig sekvens av celler.

4) Bruk autokorrektur mens du skriver. Autokorrektur er designet for automatisk å erstatte noen spesifiserte kombinasjoner av tegn med andre mens du skriver. Du kan for eksempel angi at du skriver inn et enkelt tegn i stedet for å skrive inn flere ord. Kommandoen er tilgjengelig via Office-knappen – Excel-alternativer. I Stavekontroll - Autokorrekturalternativer må du angi teksten og dens forkortelse.

5) Bruk Ctrl+Enter-snarveien for å angi gjentatte verdier. For å legge inn de samme verdiene i flere celler, kan du velge dem, angi verdien i en celle og trykke Ctrl+Enter. Som et resultat vil de samme dataene legges inn i alle valgte celler.

Validering av data når du skriver inn

Hvis du vil være sikker på at riktige data er lagt inn i et regneark, kan du angi kriterier som er gyldige for individuelle celler eller celleområder. For å sette opp en sjekk, kjør kommandoen Data – Arbeide med data – Sjekke data. I vinduet som vises (Figur 2.2.18), setter du verifiseringskriteriene på Parameters-fanen, teksten i ledeteksten som brukeren skal skrive inn på Input Message-fanen, og teksten til feilmeldingen på Feilmelding-fanen .

Etter å ha brukt kommandoen Data – Arbeide med data – Sirkle ugyldige data, vil alle feil data bli ringt inn med rødt.


Figur 2.2.18. Vindu for innstilling av dataverifiseringsparametere

Bruke formler

En formel i Excel er et matematisk uttrykk basert på hvilken verdien av en bestemt celle beregnes. Formler kan bruke: numeriske verdier; celleadresser (relative, absolutte og blandede referanser); operatorer: matematiske (+, -, *, /, %, ^), sammenligninger (=,<, >, >=, <=, < >), tekstoperator & (for å kombinere flere tekststrenger til én), områderelasjonsoperatorer (kolon (:) - område, komma (,) - for å kombinere områder, mellomrom - skjæring av områder); funksjoner.

Å angi en formel begynner alltid med tegnet "=". Resultatet av formelen vises i cellen, og selve formelen vises i formellinjen. Celleadresser i formelen kan legges inn manuelt, eller ganske enkelt ved å klikke på de ønskede cellene.

Etter beregningen vises resultatet i cellen, og den opprettede formelen vises i formellinjen i inndatavinduet.

Celleadresseringsmetoder

Celleadressen består av kolonnenavnet og radnummeret til regnearket (for eksempel A1, BM55). I formler er adresser indikert ved hjelp av lenker - relative, absolutte eller blandede. Takket være lenkene ligger dataene i forskjellige deler ark, kan brukes i flere formler samtidig.

En relativ referanse indikerer plasseringen av den ønskede cellen i forhold til den aktive (dvs. nåværende) cellen. Ved kopiering av formler endres disse lenkene automatisk i henhold til den nye posisjonen til formelen (Eksempel på lenkeoppføring: A2, C10).

En absolutt referanse peker på nøyaktig posisjon celler inkludert i formelen. Når du kopierer formler, endres ikke disse koblingene. For å opprette en absolutt cellereferanse, plasser et dollartegn ($) foran kolonne- og radbetegnelsene (Eksempel på referansenotasjon: $A$2, $C$10). For å fikse deler av celleadressen fra endringer (ved kolonne eller rad) ved kopiering av formler, brukes en blandet referanse med fiksering av ønsket parameter. (Eksempel på lenkeinngang: $A2, C$10).

Notater

· For å unngå å manuelt skrive dollartegn når du skriver lenker, kan du bruke F4-tasten, som lar deg "sortere gjennom" alle typer lenker for en celle.

Innebygde Excel-funksjoner

Hver funksjon har sin egen syntaks og operasjonsrekkefølge som må følges for at beregningene skal bli korrekte. Funksjonsargumenter er skrevet i parentes, og funksjoner kan ha argumenter eller ikke; når du bruker dem, må du ta hensyn til typen argumenter. En funksjon kan fungere som et argument for en annen funksjon, i så fall kalles den en nestet funksjon. I dette tilfellet kan opptil flere nivåer av funksjonsnesting brukes i formler.

Excel 2007 inkluderer matematikk, logisk, økonomisk, statistisk, tekst og andre funksjoner. Navnet på funksjonen i formelen kan legges inn manuelt fra tastaturet (dette aktiverer Formler-verktøyet Autofullfør, som lar deg velge ønsket funksjon basert på de første bokstavene som legges inn (Figur 2.2.19)), eller du kan velge det i funksjonsveiviser-vinduet, aktivert av knappen på funksjonsbibliotek-panelet i Formler-fanen eller fra funksjonsgrupper på samme panel, eller ved å bruke knappen Rediger panel på Hjem-fanen.

Figur 2.2.19. Autofullfør formler

Formler kan redigeres akkurat som innholdet i alle andre celler. For å redigere innholdet i en formel: dobbeltklikk på cellen med formelen, eller trykk på F2, eller rediger innholdet i formelinndatalinjen.

Navngi og bruke cellenavn

Excel 2007 gir en nyttig funksjon for å navngi celler eller områder. Dette kan være spesielt praktisk når du skriver formler. For eksempel, ved å angi navnet Totalt_for_år for en celle, kan du bruke dette navnet i stedet for celleadressen i alle formler.

Cellenavnet kan være gyldig i ett ark eller én arbeidsbok; det må være unikt og ikke dupliserte cellenavn. For å navngi celler må du velge cellen eller området og skrive inn et nytt navn i tittellinjen. Eller bruk knappen Tilordne et navn til panelet Definerte navn i kategorien Formler og åpne dialogboksen (Figur 2.2.20) for å angi de nødvendige parameterne.

Figur 2.2.20. Navneopprettingsvindu

For å se alle tildelte navn, bruk Navnebehandling-kommandoen. Du kan også få en liste over alle navn med celleadresser på arket ved å bruke kommandoen Bruk i formel - Sett inn navn på panelet Definerte navn.

For å sette inn et navn i en formel, kan du bruke kommandoen Bruk i formel og velge ønsket cellenavn fra listen.

Kommentar. Et navn kan ikke bare tildeles celleområder, men også til en formel. Dette er praktisk når du bruker nestede formler.

Viser avhengigheter i formler

For å hjelpe med å identifisere feil når du oppretter formler, kan du vise celleavhengigheter. Avhengigheter brukes til å se relasjoner i en tabellboks mellom celler med formler og celler med verdier som var involvert i disse formlene. Avhengigheter vises bare innenfor én åpen bok. Når du oppretter en avhengighet, bruker du påvirkningsceller og avhengige celler.

En påvirkningscelle er en celle som refererer til en formel i en annen celle.

En avhengig celle er en celle som inneholder en formel.

For å vise celleforhold må du velge kommandoene Influencing Cells eller Dependent Cells i Formelavhengigheter-panelet i Formler-fanen. For ikke å vise avhengigheter, bruk Fjern piler-kommandoen fra samme panel.

Figur 2.2.21. Vise påvirkende celler

Modi for å jobbe med formler

Excel har en automatisk beregningsmodus, takket være hvilken formler på ark beregnes på nytt umiddelbart. Når du plasserer et veldig stort antall (opptil flere tusen) komplekse formler på et ark, kan arbeidshastigheten reduseres merkbart på grunn av omberegning av alle formler på arket. For å kontrollere beregningsprosessen ved hjelp av formler, må du sette manuell innstilling beregninger ved å bruke kommandoen Formler – Beregning – Beregningsparametere – Manuell. Etter å ha gjort endringer, må du kalle opp Beregn-kommandoen (for å beregne data på nytt på et regneark) eller Beregne på nytt (for å beregne hele arbeidsboken på nytt) i Beregningspanelet.

En nyttig funksjon for å jobbe med formler er å vise alle formler på et ark. Dette kan gjøres ved å bruke kommandoen Formler – Formelavhengigheter – Vis formler. Etter dette vil de skrevne formlene vises i cellene i stedet for de beregnede verdiene. Å gå tilbake til normal modus Du må klikke på Vis formler-knappen igjen.

Hvis en formel returnerer en feil verdi, kan Excel hjelpe deg med å identifisere cellen som forårsaker feilen. For å gjøre dette, må du aktivere kommandoen Formler – Formelavhengigheter – Se etter feil – Kilde til feil. Kommandoen Se etter feil hjelper deg med å identifisere alle feilaktige formeloppføringer.

For å feilsøke formler finnes det en formelevaluator kalt Formler - Formelavhengigheter - Evaluer formel, som viser trinnvise beregninger i komplekse formler

Verksted:.

1. Lag en tabell for å beregne summen av en serie (alternativer for oppgaver for å beregne summen av en serie - se nedenfor). Når du oppretter en tabell, bruk innebygde funksjoner, absolutt og relativ adressering og autofylling av celler.

2. Avhengig av antall ledd n, ordner tabellen som følger:

Tabell 19.

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

Tabell 20.

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

3. Bruk betinget formatering, uthev negative tall i blått, tall større enn 1,5 i rødt.

4. Lag en tabell. Et eksempeldesign er nedenfor. Trinnet for å endre x avhengig av varianten av oppgaven er 0,1 (eller Pi/*).


5. Konstruer grafene s=f(x) og y=f(x) i ett koordinatrutenett (på ett diagram).

6. Studer mulighetene for å bruke funksjoner (for en liste over funksjoner, se oppgavealternativet), gi et eksempel på hvordan hver funksjon fungerer.

Tabell 21. Oppgavealternativer

1. Åpne et Microsoft Excel 2007-regneark og lag en arbeidsbok med navnet Laboratoriearbeid nr. 1.

2. Det er nødvendig å lage en tabell for beregning av lønn for ansatte i bedriften.

3. For å forenkle dataregistrering i tabellen, lag en nedtrekksliste (fig. 22) som inneholder de fulle navnene på selskapets ansatte.

Ris. 22. Nedtrekksliste

4. Sett inn et annet ark i Excel-arbeidsboken ved å bruke snarveien i raden Arketikett.

5. Lag en liste over ansatte på et nytt ark (fig. 23).

Ris. 23. Liste over ansatte i selskapet

6. For å sortere navn alfabetisk, kjør kommandoen: båndfanen Data gruppe Sortering og filtrering knappen.

7. Velg celleområdet A1:A10 og klikk i boksen Navn på venstre kant av formellinjen. Skriv inn et navn for cellene, for eksempel Ansatte. Trykk på tasten Tast inn.

8. For å forhindre at andre brukere viser eller endrer den resulterende listen, beskytter og skjuler du arket det er plassert på.

9. Høyreklikk på arkfanen. Velg kommandoen fra kontekstmenyen.

10. I dialogboksen Ark beskyttelse(Fig. 24) angi et passord for å deaktivere arkbeskyttelse. I kapittel Tillat alle brukere av dette arket Fjern merket for alle elementer. Klikk på knappen OK.

Ris. 24. Dialogboksen Arkbeskyttelse

11. I dialogboksen Passord bekreftelse skriv inn passordet på nytt.

12. Høyreklikk på arksnarveien og velg kommandoen fra hurtigmenyen Gjemme seg.

13. Gå til Ark 1 og lag en tabell Utarbeidelse av lønn
(Fig. 25). Kolonne Fullt navn fyll ut ved hjelp av rullegardinlisten.

Ris. 25. Tabellstruktur

14. Velg celleområdet der du vil plassere rullegardinlisten.

15. På fanen Data i gruppe Arbeid med data velge lag Datakontroll.

16. I dialogboksen Datakontroll angi datatype og kilde (fig. 26).

17. Åpne en fane Melding som skal legges inn(Fig. 27). Fyll ut de tomme feltene.

Ris. 26. Dialogboks for datavalidering

Ris. 27. Melding ved inntasting av data

18. Gå til fanen Feilmelding(Fig. 28). Fyll ut feltene Utsikt, Overskrift Og Beskjed.

Ris. 28. Melding ved datainntastingsfeil

19. For tabelloverskrifter, sett tekstbryting(knappen på verktøylinjen Justering båndfaner hjem).

20. Frys de to første kolonnene og tabelloverskriften. For å gjøre dette, velg et celleområde C5:I20 og kjør kommandoen: båndfanen Utsikt gruppe Vindu knappen.

21. Kolonne Lønn Fyll med vilkårlige data og angi valutaformatet til cellene ved å bruke kommandoen:



båndfane hjem verktøylinjen Antall Velg fra rullegardinlisten for format Valutaformat.

22. La oss lage en formel for å beregne bonusen, som er 20 % av lønnen. Enhver formel begynner med et tegn = , så la oss gå til cellen F5 og skriv inn formelen = E5*20 %(eller = E5*0,2).

23. Bruk autofyll-markøren (svart kryss nær det nedre høyre hjørnet av den valgte cellen), kopier formelen inn i området
F6:F11.

24. Mellom kolonnene Premie Og Inntektsskatt sett inn kolonne Totalt påløpt, der beregne beløpet Lønn + Premie.

25. Fyll ut de resterende kolonnene i tabellen, ta hensyn til at inntektsskatt er 13 % av påløpt beløp.

26. Beregn beløpet som skal utstedes i dollar, for å gjøre dette, sett gjeldende dollarkurs, for eksempel 32, og i cellen J5 skriv inn formelen: = I5/$C$14. Skilt $ brukes i formelen slik at celleadresseringen ikke endres ved kopiering med autofyllmarkøren.

27. For celler som inneholder pengedata, angi riktig format.

28. Bruk av funksjonen SUM, beregne det totale beløpet for inntektsskatt. For dette:

· Plasser markøren i en celle H12;

· sette tegnet =;

· i formellinjen, klikk på knappen;

· i funksjonsveiviser-dialogboksen som vises (fig. 29), velg en kategori Matematisk, funksjon SUM;

· som et funksjonsargument SUM velg summeringsområdet H5:H11;

· trykk på knappen OK.

29. Beregn på samme måte det totale beløpet som skal utstedes i dollar og det totale beløpet som skal utstedes i rubler.

Ris. 29. Funksjonsveiviser

30. Finn gjennomsnittet ( GJENNOMSNITT), minimum ( MIN) og maksimum ( MAKS) lønn.

31. Bruk betinget formatering, uthev i rødt Beløp som skal utstedes, mindre enn 5500 gni. Kjør kommandoen: båndfanen hjem gruppe Stiler nedtrekksliste Betinget formatering Regler for cellevalg.

32. Bygg et diagram Lønn selskapets ansatte
(Fig. 30). Velg kolonner med en gang FULLT NAVN. Og Beløp som skal utstedes(hold nede Ctrl-tasten), og på båndfanen Sett inn på verktøylinjen Diagrammer velg visning stolpediagram.

33. Bruke båndfanen Oppsett, sette inn akseetiketter og diagramtittel.

Ris. 30. Eksempel på diagramdesign

34. Konstruer et kakediagram som viser forholdet mellom det totale beløpet som skal utstedes og den totale inntektsskatten (fig. 31).

Ris. 31. Eksempel på et sektordiagramdesign