Formule laboratorijskega dela v excelu. Uporaba formul in funkcij v MS Excelu. Laboratorijske vaje v programu Microsoft Excel

Preizkusna naloga 1. Uporaba formul in funkcij pri izračunih.

Dano: a, b, c, h, l, m, x - poljubna števila.

Izračunajte:

Rezultat izvedbe:

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

Test 2. Uporaba relativnih in absolutnih referenc v formulah. Izdelava tabele »Nakup blaga s predprazničnim popustom«.

Odgovori na varnostna vprašanja

1. Kaj je formula v Excelu? Kakšna je njegova struktura? Katere elemente lahko vsebuje formula? Kakšna so pravila za vnos in urejanje formul v Excelu?

Formula v Excelu je zaporedje znakov, ki se začne z enačajem »=«. To zaporedje znakov lahko vključuje stalne vrednosti, sklice na celice, imena, funkcije ali operatorje.

Vnesti morate formulo, ki se začne z znakom enačaja. To je potrebno, da Excel razume, da je v celico vnesena formula in ne podatki.

3. Kako lahko kopirate in premikate formule?

Ko premaknete celico s formulo, se sklicevanja v formuli ne spremenijo. Ko kopirate formulo, se lahko sklice na celice spremenijo glede na njihovo vrsto.

4. Kako formule samodejno zapolnijo celice?

Kot tudi samodejno polnjenje celic s podatki. Če morate narediti iste izračune v tabeli, uporabite funkcijo samodokončanja, da se izognete vedno znova vnašanju istih formul.

5. Kako se urejajo formule?

Spremembe delovnega lista in odpravljanje napak potekajo z urejanjem formul v celicah. Način urejanja celice lahko aktivirate na več načinov.

1. Urejanje formule v vrstici s formulami:

Izberite celico s formulo, ki jo želite urediti

Kliknite miško in postavite kazalec v vrstico s formulami.

2. Urejanje formule neposredno v celici:

Dvokliknite miško in postavite kazalec znotraj celice. Ta metoda Urejanje formule deluje le, če je omogočena možnost Uredi neposredno v celici na zavihku Uredi v pogovornem oknu Možnosti menija Orodja.

Omogoči "način urejanja":

Izberite celico in pritisnite tipko .

Po končanem urejanju formule mora biti “način urejanja” v celici izklopljen - pritisnite tipko oz .

6. Kaj je funkcija v Excelu? Kakšna je njegova struktura?

Funkcije v Excelu se uporabljajo za izvajanje standardnih izračunov v delovnih zvezkih. Vrednosti, ki se uporabljajo za vrednotenje funkcij, se imenujejo argumenti. Vrednosti, ki jih vrnejo funkcije kot odgovor, se imenujejo rezultati. Poleg vgrajenih funkcij lahko uporabite v izračunih funkcije po meri, ki so ustvarjeni s pomočjo programa Excel. Argumenti funkcije so zapisani v oklepaju takoj za imenom funkcije in med seboj ločeni s podpičjem “;”. Oklepaji omogočajo Excelu, da določi, kje se začne in konča seznam argumentov. Argumenti morajo biti v oklepaju. Ne pozabite, da morajo biti pri pisanju funkcije odprti in zapiralni oklepaji, med imenom funkcije in oklepaji pa ne smete vstavljati presledkov.

Argumenti so lahko števila, besedilo, logične vrednosti, nizi, vrednosti napak ali reference. Argumenti so lahko konstante ali formule. Po drugi strani pa lahko te formule vsebujejo druge funkcije. Funkcije, ki so argument drugi funkciji, se imenujejo ugnezdene. IN Excelove formule Uporabite lahko do sedem ravni gnezdenja funkcij.

Podani vhodni parametri morajo imeti veljavne vrednosti za dani argument. Nekatere funkcije imajo lahko neobvezne argumente, ki morda niso prisotni pri izračunu vrednosti funkcije.

Zaradi lažje uporabe so funkcije v Excelu razdeljene v kategorije: funkcije za upravljanje baze podatkov in seznamov, funkcije za datum in čas, DDE/zunanje funkcije, inženirske funkcije, finančne, informacijske, logične, brskalne in povezovalne funkcije. Poleg tega so prisotne naslednje kategorije funkcij: statistične, besedilne in matematične.

Z besedilnimi funkcijami je mogoče obdelati besedilo: izvleči znake, poiskati tiste, ki jih potrebujete, napisati znake na strogo določeno mesto v besedilu in še veliko več.

Z uporabo funkcij datuma in časa lahko rešite skoraj vsako težavo, povezano z datumom ali uro (na primer določitev starosti, izračun delovnih izkušenj, določitev števila delovnih dni v katerem koli časovnem obdobju).

Logične funkcije pomagajo ustvariti zapletene formule, ki bodo glede na izpolnjevanje določenih pogojev izvajale različne vrste obdelave podatkov.

8. Kakšna je razlika med formulami in funkcijami? Kako ustvariti funkcijsko besedilo v pogovornem oknu?

Funkcije so lahko del formul.

Pogovorno okno ima dva seznamska okna in več gumbov. Zaradi udobja so vgrajene funkcije razdeljene v kategorije. Okno z imenom "Kategorija" vsebuje seznam kategorij funkcij. In v oknu z imenom "Funkcija" je predstavljena v po abecednem vrstnem redu seznam funkcij, izbrana kategorija.

Pod seznamom funkcij je zelo zgoščena pomoč o označeni funkciji. Toda to pomoč lahko preberete samo s kazalcem JAWS. Če kliknete gumb za pomoč, se odpre novo pogovorno okno s podrobno pomočjo o izbranem Excelove funkcije.

S klikom na gumb "V redu" se aktivira drugi korak čarovnika za funkcije - vnos vhodnih parametrov ali argumentov funkcije. Običajno morate tukaj navesti naslove in obsege celic, ki so vključeni v izračune te funkcije. Ko vnesete naslednji argument, morate pritisniti tabulatorko. Na koncu morate pritisniti "Enter", da aktivirate gumb "V redu". Pogovorno okno za vnos parametrov vsebuje tudi referenčne informacije, na voljo samo za kazalec JAWS.

Po določitvi vhodnih parametrov vgrajene funkcije bo Excel generiral besedilo formule in ga postavil v celico, kjer se je nahajal kazalec ob klicu čarovnika za funkcije.

9. Kako uporabljati čarovnika za funkcije?

Če želite najti vgrajeno Excelovo funkcijo, ki jo potrebujemo, moramo iti v meni "Vstavi" in aktivirati element "Funkcija". Odpre se pogovorno okno z imenom "Čarovnik za funkcije".


Zaključki o opravljenem laboratorijskem delu

V procesu izvajanja laboratorijskih vaj smo osvojili tehniko dela s formulami in funkcijami v tabelnem procesorju. Microsoft Office Excel.

Laboratorijsko delo

Predmet: Excelove funkcije

Tarča:

    Spoznajte različne razrede funkcij;

    Naučite se uporabljati čarovnika za funkcije;

    Naučite se uporabljati ugnezdene funkcije pri delu s tabelami.

Funkcije Excel

funkcija– je odvisna spremenljiva količina, katere vrednost se izračuna po določenih pravilih na podlagi vrednosti drugih količin – argumenti funkcije. Excel ponuja velik (nekaj sto) nabor standardnih (vgrajenih) funkcij, ki jih je mogoče uporabiti v formulah, na primer:

Funkcija - iz latinščine Functio - izvedba.

Imenu funkcije v oklepajih sledi seznam argumentov, ločenih s podpičjem. Seznam argumentov je lahko sestavljen iz številk, besedila, logičnih vrednosti (TRUE ali FALSE), povezav, formul, ugnezdenih funkcij. Če se formula začne s funkcijo, je pred imenom funkcije znak " = ».

Glede na naravo argumentov lahko vgrajene funkcije razdelimo na tri vrste:

S seznamom argumentov(največ – 30 argumentov): AVERAGE (A2:C23;E6;200;3) – vrne povprečno vrednost argumentov

Z fiksni argumenti: MOČ (6,23;4): dvigne prvi argument (6,24) na potenco drugega argumenta (4)

Brez argumentov:TODAY(): Vrne trenutni datum.

Vnos formul

Zaporedje vnosa funkcije v formulo:

    Ime funkcije;

    Odpiranje oklepaja;

    Seznam argumentov, ločenih s podpičji;

    Zaključni oklepaj.

Funkcijo lahko vnesete na več načinov:

Funkcije in vrstica s formulami

Če niso vneseni ročno, so argumenti določeni z uporabo vrstice s formulo:

Potreben argument označeno s krepkim tiskom – brez tega funkcija ne more izvesti obdelave;

Izbirni argument je prikazano z običajnim črkovanjem imena polja in njegove vrednosti morda ne boste vnesli. V tem primeru bodo uporabljene privzete vrednosti.

Če je formula sestavljena iz več funkcij, vrstica s formulami prikaže argumente funkcije, označene s krepko pisavo v vrstici s formulami. Če želite prikazati argumente druge funkcije v vrstici s formulo, morate klikniti njeno ime v vrstici s formulo.

Vrstico formule lahko premikate po zaslonu tako, da jo povlečete z miško.

Ugnezdene funkcije

Rezultat vrednotenja funkcije se lahko uporabi kot argument drugi funkciji. Pokliče se funkcija, ki se uporablja kot eden od argumentov druge funkcije ugnezdeni. Excel podpira do 7 ravni gnezdenja funkcij.

Na primer:

ČE (A4>0; MAX (A9:B19);0)

Za vnos funkcije kot argumenta morate razširiti seznam v vrstici s formulo in bodisi izbrati enega od 10 nedavno uporabljenih, ali kontakt v čarovnika za funkcije z uporabo ukaza Druge lastnosti..ali vnesite funkcijo ročno.

Poseben vložek

Vsebino celice lahko predstavimo kot kombinacijo štirih plasti informacij: formula, vrednost, oblika in opomba. Excel omogoča kopiranje vsake plasti posebej. Informacije so shranjene v medpomnilnik kot običajno (ukaz Kopirati), in se vstavi z ukazom Uredi\Prilepi posebno …

Za kopiranje formatov, tako kot druge Officeove aplikacije, uporabite standardno orodno vrstico - Vzorčna oblika . (Praktično delo"Vremenska napoved » ).

vaja:

    S funkcijo zapolni blok A1:A5 z naključnimi števili v območju [-10,10];

    V celico B1 vnesite formulo za izračun celotnega dela vrednosti v stolpcu A;

    Dobljeno formulo kopirajte v blok B2:B5;

    Enako zaporedje operacij uporabite za funkcije oziroma bloke:

ABS (A) - C1: C5;

EXP (A) - D1:D5;

SQRT(A) - E 1:E 5;

Izračun ostanka pri deljenju z 2 – F 1:F 5;

Zaokroževanje od -1 – H 1:H 5;

Krog z +1 – G 1:G 5

    Zapišite formulo v celico A7 zneski elementi prvega stolpca (A1:A5)

V celici B7 – aritmetična sredina glede na (B1:B5)

C7 – največji element od (C1:C6)

D 7 – najmanjši element (D 1:D 6)

E 7 – število elementov (E1:E6)

F 7 – varianca vrednosti (F 1:F 6)

Razpon I 1:I 6 dopolnimo z vrednostmi trigonometričnih funkcij:

I1 - PI

I2 – Greh (A1)

I3 – Cos (A2)

I4 – Tan (A3)

I5 – Atan (A4)

I6 – Asin (A5)

    V vrstico 10 vnesite naslove polj:

Priimek\Ime Datum rojstva Število dni

Prilagodite širino stolpcev in centrirajte naslove;

    V polje A12:A17 vpišite priimke ali imena svojih prijateljev in znancev. V bloku B12:B17 so njihovi rojstni datumi. Vnesite datum v evropski obliki;

    V celico C9 vnesite trenutni datum;

    V celici C12 je formula za izračun števila dni, ki jih oseba živi za trenutni datum;

    Med stolpca Datum rojstva in Število dni vstavite stolpec Dan v tednu;

    V prvo celico stolpca vnesite funkcijo za izračun dneva v tednu glede na datum rojstva. Kopirajte nastalo formulo v vse celice stolpca;

    V kolumni F Zraven vsakega priimka napišite »Mlad« ali »Star« z uporabo logične funkcije ČE. Vnesite funkcijo s čarovnikom za funkcije (IF Število dni<15000, то «Молодой», иначе «Старый»);

    Nastalo tabelo shranite na disk v osebni mapi (Ime skupine).

Kontrolna vprašanja:

    Metode vnašanja formul v celice;

    vrstica formule;

    Obvezni in neobvezni argumenti v formulah;

    Postopek za izvajanje ugnezdenih funkcij v Microsoft Excelu;

    Algoritem za posebno vstavljanje v celice.

Laboratorijske vaje iz računalništva

Osnove računalništva 1

Ustvarjanje formul s čarovnikom za funkcije 1

Ustvarjanje formul z uporabo gumba za vsoto 2

Urejanje formul 2

Zaokroževanje 3

Vzorčna vsota 3

Potenciranje in koren 4

Premikanje in kopiranje formul 4

Uporaba referenc v formulah 4

Uporaba imen celic in obsegov 6

Preverjanje napak 7

Napake v funkcijah in argumentih 7

Sledite razmerjem med formulami in celicami 8

Uporaba logičnih funkcij 9

Pogojno oblikovanje 10

Označevanje vrednosti 10

Označevanje skrajnih vrednosti 12

Oblikovanje s histogramom 13

Oblikovanje s tribarvno lestvico 13

Oblikovanje z uporabo nabora ikon 14

Upravljanje pravil pogojnega oblikovanja 15

Laboratorijsko delo št. 6

Lab 6: Osnove računanja v Excelu Ustvarjanje formul s čarovnikom za funkcije

Funkcije vam omogočajo poenostavitev formul, še posebej, če so dolge ali zapletene. Funkcije se uporabljajo ne samo za neposredne izračune, ampak tudi za pretvorbo števil, na primer za zaokroževanje, iskanje vrednosti, primerjavo itd.

Za ustvarjanje formul s funkcijami običajno uporabite skupino Knjižnica funkcij zavihki Formule.

    Izberite celico, v katero želite vnesti formulo.

    Kliknite gumb za želeno kategorijo funkcij v skupini Knjižnica funkcij in izberite želeno funkcijo.

    V oknu Argumenti funkcije V ustrezna polja vnesite argumente funkcije. Sklice na celice lahko vnesete s tipkovnico, vendar je bolj priročno izbrati celice z miško. Če želite to narediti, postavite kazalec v ustrezno polje in izberite želeno celico ali obseg celic na listu. Za lažjo izbiro celic okno Argumenti funkcije se lahko premakne ali strne. Okno kot namig prikaže namen funkcije, na dnu okna pa je prikazan opis argumenta, v polju katerega se trenutno nahaja kazalec. Upoštevajte, da nekatere funkcije nimajo argumentov.

    V oknu Argumenti funkcije kliknite gumb v redu.

Za vstavljanje funkcije ni treba uporabiti gumbov kategorij funkcij v skupini Knjižnica funkcij. Za izbiro želene funkcije lahko uporabite čarovnika za funkcije. Poleg tega je to mogoče storiti pri delu v katerem koli zavihku.

Pri ustvarjanju formul lahko imena funkcij vnesete s tipkovnico. Če želite poenostaviti postopek ustvarjanja in zmanjšati število tipkarskih napak, uporabite samodejno dokončanje formule.

    V celico ali vrstico s formulo vnesite »=« (enačaj), ki mu sledijo prve črke funkcije, ki jo uporabljate. Med tipkanjem se na drsečem seznamu možnih postavk prikažejo najbližje vrednosti.

    Želeno funkcijo izberite tako, da nanjo dvokliknete z miško.

    S tipkovnico in miško vnesite argumente funkcije. Potrdite vnos formule.

Ustvarjanje formul z uporabo gumba Sum

Ta gumb poleg skupine Knjižnica funkcij zavihki Formule(tam se imenuje Samovsota), na voljo tudi v skupini Urejanje zavihki domov.

Če želite izračunati vsoto števil v celicah, ki so neprekinjeno v enem stolpcu ali eni vrstici, samo izberite celico pod ali desno od seštetega obsega in kliknite gumb vsota.

Za potrditev vnosa formule pritisnite tipko Vnesite ali znova pritisnite gumb vsota.

Za izračun vsote naključno lociranih celic izberite celico, v kateri želite izračunati vsoto, kliknite na gumb vsota in nato izberite celice in/ali obsege celic, ki jih želite sešteti na delovnem listu. Za potrditev vnosa formule pritisnite tipko Vnesite ali znova pritisnite gumb vsota.

Sintaksa funkcije

SUM),

kjer je A seznam od 1 do 30 elementov, ki jih je treba sešteti. Element je lahko celica, obseg celic, število ali formula. Sklici na prazne celice, besedilo ali logične vrednosti so prezrti.

    V novo knjigo vnesite podatke o učni uspešnosti.

    Izračunajte skupni rezultat za vsakega učenca.

Poleg izračuna zneska gumb vsota se lahko uporablja pri izračunu povprečne vrednosti, določanju števila številskih vrednosti, iskanju največjih in najmanjših vrednosti. V tem primeru morate klikniti puščico gumba in izbrati želeno dejanje:

Povprečje- izračun aritmetične sredine;

številka- določitev števila številčnih vrednosti;

Največ- iskanje največje vrednosti;

Najmanjša- iskanje najmanjše vrednosti.

Namen laboratorijskih vaj je preučiti in utrditi veščine vnosa podatkov in uporabe formul v Microsoft Excel 2007 .

Vnos podatkov v preglednico

Celice preglednice lahko vsebujejo tri vrste podatkov: številske vrednosti (vključno z uro in datumom), besedilo in formule. Delovni list, vendar v "grafični plasti" na vrhu lista, lahko vsebuje tudi slike, grafikone, slike, gumbe in druge predmete.

Vnos številk

Številke se vnašajo z uporabo zgornje vrstice tipkovnice ali številske tipkovnice. Kot decimalno ločilo se uporablja vejica ali pika, vnesete lahko simbole valut. Če pred številko vnesete minus ali oklepaj, se šteje za negativno. Ničle, vnesene pred številko, program prezre. Če želite dobiti vrednost z začetnimi ničlami, jo je treba interpretirati kot besedilo.

Excel za predstavljanje števil uporablja 15 števk; ko vnesete 16-mestno številko, bo samodejno shranjena na 15 števk. Številske vrednosti so samodejno poravnane na desni rob celice.

Vnos datumov in ur

Excel za predstavitev datumov uporablja notranji sistem številčenja datumov. (Torej, najzgodnejši datum, ki ga program lahko prepozna, je 1. januar 1900, temu datumu je dodeljena zaporedna številka 1, naslednjemu datumu je dodeljena zaporedna številka 2 itd.). Datumi so vneseni v obliki, ki je uporabniku znana, in so samodejno prepoznani. Časovne vrednosti se vnesejo tudi v enem od priznanih formatov časa. Predstavitev datuma in časa neposredno na delovnem listu je nadzorovana z nastavitvijo oblike prikaza celice.

Vnos besedila

Vsi vneseni podatki, ki niso prepoznani kot številke ali formule, se obravnavajo kot besedilne vrednosti. Besedilne vrednosti so poravnane na levi rob tabele. Če se besedilo ne prilega v eno celico, se postavi na vrh sosednjih celic, če so proste. Parametri za postavitev besedila v celico so nastavljeni z uporabo formata celice.



Vnos formule

Formula je vsak matematični izraz. Formula se vedno začne z znakom »=« in lahko poleg operatorjev in referenc celic vključuje tudi vgrajene Excelove funkcije.

Formati podatkov

Po vnosu podatkov v celico poskuša Excel samodejno določiti njeno vrsto in celici dodeliti ustrezno obliko - obliko prikaza podatkov. Pomembno je, da dodelite pravilen format celice, tako da na primer celica lahko sodeluje pri izračunih (ne pa besedilo).

Excel ima nabor standardnih formatov celic, ki jih je mogoče uporabiti v vseh delovnih zvezkih (slika 2.2.17). Aktivirate ga lahko tako, da izberete Domov – Število – Oblika številke ali uporabite kontekstni meni za izbrano celico na zavihku Število v oknu Oblika celice.

Slika 2.2.17. Standardni formati

Na začetku imajo vse celice tabele splošni format. Uporaba formatov vpliva na to, kako bo prikazana vsebina v celicah: splošno - števila so prikazana kot cela števila, decimalni ulomki, če je število preveliko, pa kot eksponentno; numeric – standardna številska oblika; finančni in denarni – število je zaokroženo na 2 decimalni mesti, znak valute je postavljen za številom, denarni format omogoča prikaz negativnih zneskov brez znaka minus in v drugi barvi; kratki in dolgi format datuma – omogoča izbiro enega od formatov datuma; čas – ponuja več formatov časa, med katerimi lahko izbirate; - odstotek – število (od 0 do 1) v celici pomnožimo s 100, zaokrožimo na najbližje celo število in zapišemo z znakom %; ulomek – uporablja se za prikaz števil v obliki navadnega ulomka namesto decimalke; eksponentno – zasnovano za prikaz števil kot zmnožek dveh komponent: števila od 0 do 10 in moči 10 (pozitivne ali negativne); besedilo – pri nastavitvi te oblike bo vsaka vnesena vrednost zaznana kot besedilo; dodatno – vključuje formate Poštna številka, Poštna številka+4, Telefonska številka, Kadrovska številka; vsi formati – omogoča ustvarjanje novih formatov kot predloge po meri.

Uporaba orodij za pospešitev vnosa podatkov

Pri vnašanju podatkov v tabele lahko uporabite nekaj tehnik za pospešitev njihovega vnosa.

1) Samodokončaj med tipkanjem. Ko vnesete iste vrednosti v več celic, lahko uporabite oznako za samodejno izpolnjevanje (križ v spodnjem desnem kotu aktivne celice), da kopirate vrednosti v sosednje celice. S kontekstnim menijem, ki se odpre z desnim klikom po vlečenju, lahko nastavite dodatne parametre samodejnega izpolnjevanja (npr. z vnosom številk 1 in 3 v celice dobite zaporedje številk v korakih po 2 za izbrani obseg celice).

2) Uporaba napredovanja. Če celica vsebuje številko, datum ali časovno obdobje, ki je lahko del serije, potem se pri kopiranju njena vrednost poveča (pridobi se aritmetična ali geometrijska progresija, seznam datumov). Če želite nastaviti napredovanje, morate izbrati gumb Fill na plošči za urejanje zavihka Domov in v pogovornem oknu Progression, ki se prikaže, nastaviti parametre za aritmetično ali geometrijsko napredovanje.

3) Samodokončanje med tipkanjem. Ta funkcija vam omogoča samodejni vnos ponavljajočih se besedilnih podatkov. Po vnosu besedila v celico si Excel to zapomni in ob naslednjem vnosu, po vpisu prvih črk besede, ponudi možnost dokončanja vnosa. Za dokončanje vnosa pritisnite “Enter”. Do tega ukaza lahko dostopate tudi tako, da v kontekstnem meniju z desnim gumbom miške izberete Izberi s spustnega seznama. Funkcija samodokončanja deluje samo na neprekinjenem zaporedju celic.

4) Uporaba samodejnega popravka med tipkanjem. Samopopravki so zasnovani tako, da med tipkanjem samodejno zamenjajo določene kombinacije znakov z drugimi. Določite lahko na primer, da namesto več besed vnesete en znak. Ukaz je na voljo prek gumba Office – Excel Options. V Črkovanje - Možnosti samopopravkov morate nastaviti besedilo in njegovo okrajšavo.

5) Uporaba bližnjice na tipkovnici Ctrl+Enter za vnos ponavljajočih se vrednosti. Če želite vnesti enake vrednosti v več celic, jih lahko izberete, vnesete vrednost v eno celico in pritisnete Ctrl+Enter. Posledično bodo isti podatki vneseni v vse izbrane celice.

Preverjanje podatkov ob vnosu

Če želite biti prepričani, da so v delovni list vneseni pravilni podatki, lahko podate merila, ki veljajo za posamezne celice ali obsege celic. Za nastavitev preverjanja zaženite ukaz Podatki – Delo s podatki – Preverjanje podatkov. V oknu, ki se prikaže (slika 2.2.18), na zavihku Parametri nastavite merila preverjanja, na zavihku Vhodno sporočilo besedilo sporočila, ki ga uporabnik vnese, na zavihku Sporočilo o napaki pa besedilo sporočila o napaki. .

Po uporabi ukaza Podatki – Delo s podatki – Obkroži neveljavne podatke bodo vsi nepravilni podatki obkroženi z rdečo barvo.


Slika 2.2.18. Okno za nastavitev parametrov preverjanja podatkov

Uporaba formul

Formula v Excelu je matematični izraz, na podlagi katerega se izračuna vrednost določene celice. Formule lahko uporabljajo: številske vrednosti; naslovi celic (relativne, absolutne in mešane reference); operatorji: matematični (+, -, *, /, %, ^), primerjave (=,<, >, >=, <=, < >), besedilni operator & (za združevanje več besedilnih nizov v enega), operatorji relacije obsega (dvopičje (:) - obseg, vejica (,) - za združevanje obsegov, presledek - presečišče obsegov); funkcije.

Vnos formule se vedno začne z znakom »=«. Rezultat formule je prikazan v celici, sama formula pa je prikazana v vrstici s formulami. Naslove celic v formulo lahko vnesete ročno ali preprosto s klikom na želene celice.

Po izračunu se dobljeni rezultat prikaže v celici, ustvarjena formula pa se prikaže v vrstici s formulami v vnosnem oknu.

Metode naslavljanja celic

Naslov celice je sestavljen iz imena stolpca in številke vrstice delovnega lista (na primer A1, BM55). V formulah so naslovi označeni s povezavami - relativnimi, absolutnimi ali mešanimi. Zahvaljujoč povezavam se podatki, ki se nahajajo v različne dele list, se lahko uporablja v več formulah hkrati.

Relativna referenca označuje lokacijo želene celice glede na aktivno (tj. trenutno) celico. Pri kopiranju formul se te povezave samodejno spremenijo glede na novo pozicijo formule (Primer vnosa povezave: A2, C10).

Absolutna referenca kaže na točno lokacijo celice, vključene v formulo. Ko kopirate formule, se te povezave ne spremenijo. Če želite ustvariti absolutno referenco celice, postavite znak za dolar ($) pred oznake stolpcev in vrstic (Primer zapisa sklicevanja: $A$2, $C$10). Za popravek dela naslova celice zaradi sprememb (po stolpcu ali vrstici) pri kopiranju formul se uporabi mešana referenca s fiksacijo želenega parametra. (Primer vnosa povezave: $A2, C$10).

Opombe

· Da bi se izognili ročnemu tipkanju znakov za dolar pri pisanju povezav, lahko uporabite tipko F4, ki vam omogoča "razvrščanje" vseh vrst povezav za celico.

Vgrajene Excelove funkcije

Vsaka funkcija ima svojo sintakso in vrstni red delovanja, ki ju je treba upoštevati, da so izračuni pravilni. Argumenti funkcije so zapisani v oklepajih, funkcije pa lahko imajo argumente ali pa tudi ne, pri uporabi pa je treba upoštevati vrsto argumentov. Funkcija lahko deluje kot argument drugi funkciji, v tem primeru se imenuje ugnezdena funkcija. V tem primeru lahko v formulah uporabite do več ravni gnezdenja funkcij.

Excel 2007 vključuje matematične, logične, finančne, statistične, besedilne in druge funkcije. Ime funkcije v formuli lahko vnesete ročno s tipkovnice (s tem aktivirate orodje AutoComplete formulas, ki omogoča izbiro želene funkcije glede na prve vnesene črke (slika 2.2.19)) ali pa jo izberete v oknu čarovnika za funkcije, ki ga aktivirate z gumbom na plošči Knjižnica funkcij na zavihku Formule ali iz funkcijskih skupin na isti plošči ali z uporabo gumba plošče Uredi na zavihku Domov.

Slika 2.2.19. Samodokončanje formul

Formule je mogoče urejati tako kot vsebino katere koli druge celice. Za urejanje vsebine formule: dvokliknite celico s formulo ali pritisnite F2 ali uredite vsebino v vrstici za vnos formule.

Poimenovanje in uporaba imen celic

Excel 2007 nudi uporabno funkcijo za poimenovanje celic ali obsegov. To je lahko še posebej priročno pri sestavljanju formul. Na primer, če za celico določite ime Total_for_year, lahko to ime uporabite namesto naslova celice v vseh formulah.

Ime celice je lahko veljavno znotraj enega lista ali enega delovnega zvezka; biti mora edinstveno in ne sme biti podvojena imena celic. Če želite poimenovati celice, morate izbrati celico ali obseg in v naslovno vrstico vnesti novo ime. Ali pa uporabite gumb Dodeli ime na plošči Definirana imena na zavihku Formule in prikličite pogovorno okno (slika 2.2.20), da nastavite zahtevane parametre.

Slika 2.2.20. Okno za ustvarjanje imena

Za ogled vseh dodeljenih imen uporabite ukaz Name Manager. Seznam vseh imen z naslovi celic na listu lahko dobite tudi z ukazom Uporabi v formuli – Vstavi imena plošče Definirana imena.

Če želite vstaviti ime v formulo, lahko uporabite ukaz Uporabi v formuli in s seznama izberete želeno ime celice.

Komentiraj. Ime lahko dodelite ne samo obsegom celic, ampak tudi formuli. To je priročno pri uporabi ugnezdenih formul.

Prikaz odvisnosti v formulah

Za lažje odkrivanje napak pri ustvarjanju formul lahko prikažete odvisnosti celic. Odvisnosti se uporabljajo za ogled odnosov v polju tabele med celicami s formulami in celicami z vrednostmi, ki so bile vključene v te formule. Odvisnosti so prikazane samo znotraj enega odprta knjiga. Pri ustvarjanju odvisnosti uporabite vplivne celice in odvisne celice.

Vplivna celica je celica, ki se sklicuje na formulo v drugi celici.

Odvisna celica je celica, ki vsebuje formulo.

Če želite prikazati razmerja med celicami, morate izbrati ukaze Vplivne celice ali Odvisne celice v podoknu Odvisnosti formul na zavihku Formule. Če ne želite prikazati odvisnosti, uporabite ukaz Odstrani puščice na isti plošči.

Slika 2.2.21. Prikaz vplivnih celic

Načini za delo s formulami

Excel ima samodejni način izračuna, zahvaljujoč kateremu se formule na listih takoj preračunajo. Pri postavljanju zelo velikega števila (do nekaj tisoč) zapletenih formul na list se lahko hitrost dela opazno zmanjša zaradi ponovnega izračuna vseh formul na listu. Če želite nadzorovati postopek izračuna z uporabo formul, morate nastaviti ročni način izračune z ukazom Formule – Izračun – Parametri izračuna – Ročno. Po opravljenih spremembah morate poklicati ukaz Izračunaj (za ponovni izračun podatkov na listu delovnega lista) ali Ponovni izračun (za ponoven izračun celotnega delovnega zvezka) plošče Izračun.

Uporabna funkcija za delo s formulami je prikaz vseh formul na listu. To lahko storite z ukazom Formule – Odvisnosti formul – Prikaži formule. Po tem bodo v celicah namesto izračunanih vrednosti prikazane zapisane formule. Za vrnitev v normalen način Ponovno morate klikniti gumb Prikaži formule.

Če formula vrne napačno vrednost, vam lahko Excel pomaga prepoznati celico, ki povzroča napako. Za to morate aktivirati ukaz Formule – Odvisnosti formul – Preveri napake – Vir napak. Ukaz Preveri napake pomaga prepoznati vse napačne vnose formule.

Za odpravljanje napak v formulah je na voljo ocenjevalnik formul, imenovan Formule – Odvisnosti formul – Oceni formulo, ki prikazuje izračune po korakih v kompleksnih formulah.

Delavnica:.

1. Izdelajte tabelo za izračun vsote vrste (možnosti nalog za izračun vsote vrste – glej spodaj). Pri izdelavi tabele uporabite vgrajene funkcije, absolutno in relativno naslavljanje ter samodejno izpolnjevanje celic.

2. Glede na število členov n uredite tabelo takole:

Tabela 19.

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

Tabela 20.

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

3. S pogojnim oblikovanjem označite negativna števila z modro, števila, večja od 1,5, z rdečo.

4. Ustvarite tabelo. Vzorec načrta je spodaj. Korak spremembe x glede na varianto naloge je 0,1 (ali Pi/*).


5. Zgradite grafa s=f(x) in y=f(x) v eni koordinatni mreži (na enem diagramu).

6. Preučite možnosti uporabe funkcij (za seznam funkcij glejte opcijo naloga), navedite primer delovanja posamezne funkcije.

Tabela 21. Možnosti nalog

1. Odprite preglednico Microsoft Excel 2007 in ustvarite delovni zvezek z imenom Laboratorijsko delo št. 1.

2. Treba je izdelati tabelo za izračun plač za zaposlene v podjetju.

3. Za poenostavitev vnosa podatkov v tabelo ustvarite spustni seznam (slika 22), ki vsebuje polna imena zaposlenih v podjetju.

riž. 22. Spustni seznam

4. Vstavite drug list v Excelov delovni zvezek z uporabo bližnjice v vrstici Oznaka lista.

5. Na novem listu ustvarite seznam zaposlenih (slika 23).

riž. 23. Seznam zaposlenih v podjetju

6. Če želite imena razvrstiti po abecedi, zaženite ukaz: ribbon tab podatki skupina Razvrščanje in filtriranje gumb .

7. Izberite obseg celic A1:A10 in kliknite polje Ime na levem robu vrstice formule. Vnesite na primer ime za celice Zaposleni. Pritisnite tipko Vnesite.

8. Če želite drugim uporabnikom preprečiti ogled ali spreminjanje nastalega seznama, zaščitite in skrijte list, na katerem se nahaja.

9. Z desno miškino tipko kliknite zavihek lista. V kontekstnem meniju izberite ukaz.

10. V pogovornem oknu Zaščita pločevine(Slika 24) vnesite geslo, da onemogočite zaščito lista. V poglavju Dovoli vsem uporabnikom tega lista Odkljukajte vse elemente. Kliknite gumb v redu.

riž. 24. Pogovorno okno Zaščita lista

11. V pogovornem oknu potrditev gesla znova vnesite geslo.

12. Z desno miškino tipko kliknite bližnjico do lista in v kontekstnem meniju izberite ukaz Skrij se.

13. Pojdi na List 1 in ustvarite tabelo Priprava plačilne liste
(slika 25). Stolpec Polno ime izpolnite s spustnim seznamom.

riž. 25. Struktura tabele

14. Izberite obseg celic, v katere želite postaviti spustni seznam.

15. Na zavihku podatki v skupini Delo s podatki izberite ekipo Preverjanje podatkov.

16. V pogovornem oknu Preverjanje podatkov navedite tip podatkov in vir (slika 26).

17. Odprite zavihek Sporočilo, ki ga je treba vnesti(slika 27). Izpolnite prazna polja.

riž. 26. Pogovorno okno za preverjanje podatkov

riž. 27. Sporočilo ob vnosu podatkov

18. Pojdite na zavihek Obvestilo o napaki(Slika 28). Izpolnite polja Pogled, Naslov in Sporočilo.

riž. 28. Sporočilo v primeru napake pri vnosu podatkov

19. Za glave tabel nastavite prelom besedila(gumb v orodni vrstici Poravnava zavihki na traku domov).

20. Zamrznite prva dva stolpca in vrstico glave tabele. Če želite to narediti, izberite obseg celic C5:I20 in zaženite ukaz: ribbon tab Pogled skupina Okno gumb .

21. Stolpec Plača Izpolnite s poljubnimi podatki in nastavite obliko valute celic z ukazom:



jeziček na traku domov orodna vrstica številka Na spustnem seznamu oblik izberite Format valute.

22. Ustvarimo formulo za izračun bonusa, ki znaša 20% plače. Vsaka formula se začne z znakom = , torej gremo v celico F5 in vnesite formulo = E5*20%(ali = E5*0,2).

23. Z oznako za samodejno izpolnjevanje (črni križ blizu spodnjega desnega kota izbrane celice) kopirajte formulo v območje
F6:F11.

24. Med stolpci Nagrada in Davek na prihodek vstavi stolpec Skupaj obračunano, v kateri izračunati znesek Plača + Nagrada.

25. Izpolnite preostale stolpce tabele, pri čemer upoštevajte, da dohodnina znaša 13 % od obračunanega zneska.

26. Izračunajte znesek, ki ga želite izdati v dolarjih, za to nastavite trenutni tečaj dolarja, na primer 32, in v celici J5 vnesite formulo: = I5/$C$14. Podpis $ se uporablja v formuli, tako da se pri kopiranju z oznako za samodejno izpolnjevanje naslavljanje celice ne spremeni.

27. Za celice, ki vsebujejo denarne podatke, nastavite ustrezno obliko.

28. Uporaba funkcije SUM, izračunajte skupni znesek dohodnine. Za to:

· postavite kurzor v celico H12;

· postavite znak =;

· v vrstici s formulo kliknite gumb ;

· v pogovornem oknu čarovnika za funkcije, ki se prikaže (slika 29), izberite kategorijo matematične, funkcija SUM;

· kot argument funkcije SUM izberite obseg seštevanja H5:H11;

· pritisni gumb V REDU.

29. Podobno izračunajte skupni znesek za izdajo v dolarjih in skupni znesek za izdajo v rubljih.

riž. 29. Čarovnik za funkcije

30. Poiščite povprečje ( POVPREČJE), najmanj ( MIN) in največ ( MAKS) plače.

31. Z uporabo pogojnega oblikovanja označite z rdečo Zneski za izdajo, manj kot 5500 rub. Zaženite ukaz: zavihek traku domov skupina Slogi spustni seznam Pogojno oblikovanje Pravila izbire celic.

32. Zgradite diagram Plača zaposlenih v podjetju
(slika 30). Izberite stolpce naenkrat POLNO IME. in Znesek za izdajo(držite tipko Ctrl) in na zavihku traku Vstavi na orodni vrstici Diagrami izberite pogled Stolpični diagram.

33. Uporaba zavihka traku postavitev, vstavite oznake osi in naslov grafikona.

riž. 30. Primer oblikovanja diagrama

34. Sestavite tortni grafikon, ki prikazuje razmerje med skupnim zneskom za izdajo in celotnim davkom na dohodek (slika 31).

riž. 31. Primer zasnove tortnega grafikona