Laboratorijske vaje iz računalništva. Excel Labs

Ministrstvo za izobraževanje in znanost

Ruska federacija

Zvezna državna avtonomna izobraževalna ustanova

visoka strokovna izobrazba

Nacionalna raziskovalna jedrska univerza "MEPhI"

Volgodonski inženirski in tehnični inštitut - podružnica Nacionalne raziskovalne jedrske univerze MEPhI

Ustvarjanje tabel

METODOLOŠKA NAVODILAk laboratorijskemu delu

računalništva v programuMicrosoftexcel

Volgodonsk 2010

UDK 519.683(076.5)

Recenzent dr. tehn. znanosti Z.O. Kavrišvili

Sestavil V.A. Mace

Ustvarjanje tabel. Navodila za laboratorijsko delo v programu MicrosortExcel. 2010. 13 str.

Smernice vsebujejo pojasnila in priporočila za izvajanje laboratorijskih vaj pri predmetu računalništvo v programu MicrosortExcel.

_____________________________________________________________________________

ã Volgodonski inštitut NRNU MEPhI, 2010

ã Bulava V.A., 2010

Laboratorijsko delo

Izdelava tabel v programuExcelz avtomatiziranim vnosom podatkov.

Cilj dela. Utrditi pridobljeno znanje pri ustvarjanju, urejanju in oblikovanju tabel v Excelu.

Oblikovanje problema.

Izračunaj vrednost funkcije l = f(x)/ g(x) za vse X na intervalu [ a, b] v korakih Za. Pomen funkcij f(x) , g(x) , vrednost koncev intervala a in b in vrednost koraka Za je podano iz tabela 1 v prilogi glede na možnost za posamezno specialnost.

Rešitev je treba dobiti v obliki "Glavne" in "Pomožne" tabele.

Izračunane vrednosti funkcij pri kopiraj v stolpec TO brez formul .

Excel se zažene z ukazi Začetek → Programi →MicrosortExcel.

Ko ustvarjate tabelo, v prvi vrstici združite celice A1:H1 in na sredino postavite besedilo »Tabele«.

V drugi vrstici združite celice A2:E2 in na sredino postavite besedilo »Main«. Spojite celice G2:H2 in na sredino postavite besedilo »Pomožno«.

V celico A3 vnesite besedilo »Št. V celice B3:F3 ustrezno postavite imena stolpcev: X ; f(x)=…( po vaši izbiri); g(x)=…( po vaši izbiri); l= f(x)/ g(x).

V celice G3:H3 ustrezno postavite imena stolpcev: a ; Za.

Pri samodejnem izpolnjevanju podatkov iz glavne tabele v formulah uporabite absolutno, relativno in mešano naslavljanje celic.

V tabelah »Glavna« in »Pomožna« mora biti vsebina celic poravnana na sredino celice in imeti velikost pisave 12 pt.

Barva pisave imen tabel naj bo modra.

Zunanje obrobe tabel obarvajte modro, notranje obrobe zeleno in polnilo celic rumeno.

Obrazec za poročanje.

Rezultate laboratorijskega dela posredujte v obliki poročila v tiskani ali elektronski obliki.

Tiskana verzija poročila mora vsebovati:

a) naslovna stran;

b) namen dela;

c) navedbo problema;

d) rezultat opravljene naloge.

2. Rezultat laboratorijskega dela posredujte v elektronski obliki na 3,5-palčni disketi v obliki datoteke z imenom »Tabele«.

Kontrolna vprašanja.

Kaj je absolutno, relativno, mešano naslavljanje?

Kako se celice samodejno izpolnijo s številkami in formulami?

Kakšni so različni načini za poravnavo vsebine celice?

Kako lahko spremenim barvo in debelino črt zunanjih in notranjih robov tabele?

Kako lahko spremenim barvo ozadja celic tabele?

Tipičen primer.

Izračunajte vrednost funkcije y = x∙sin(x)/(x+1) na segmentu s korakom 0,1. Rešitev podajte v obliki tabele. Izračunane vrednosti funkcij pri kopiraj v stolpec TO brez formul .

rešitev.

V tem primeru f(x) = x∙ greh(x) , g(x) = x+1 , a =0 , b = 2 , k = 0.1

1. V prvi vrstici tabele izberite celice A1:H1. Izvršimo ukaz Oblika → Celice, v oknu, ki se odpre, razširite zavihek izravnavanje in izberite predmet združevanje celic. Na sredino združenih celic vnesite besedilo »Tabele«.

2. Podobno v drugi vrstici združite celice A2:E2 in na sredino postavite besedilo »Glavno« ter združite celice G2:H2 in na sredino postavite besedilo »Pomožno«.

3. V tretjo vrstico celice A3 vnesite besedilo Ne. ( ime prvega stolpca tabele ) , v celici B3 – X(ime drugega stolpca tabele ), celica C3 – f(x)= x∙ greh(x) , v celici D3 – g(x)= x+1 , v celici E3 – y=f(x)/ g(x) , v celici G3 – a, v celici H3 – k.

4. V celico A4 vpišemo 1 in izpolnite celice A5:A24 s številkami od 2 do 21. Če želite to narediti, izberite celico A4 (naj bo trenutna), označena bo v črnem okvirju. Kazalec miške premaknite nad oznako polnila (črni križec v spodnjem desnem kotu celice) in s pritiskom na desni gumb miške povlecite oznako polnila po stolpcu A tako da črni okvir pokriva celice A5:A24. Ko spustite desni gumb miške, izberite element v meniju, ki se odpre napolniti. Celice A5: A24 bodo zapolnjene s številkami 2;3;4...

5. V celico G4 vnesite vrednost 0 (vrednost levega konca intervala).

6. V celico H4 vnesite vrednost 0,1 (velikost koraka).

7. Izpolnite stolpec IN vrednote X:

V celico B4 vnesemo formulo =$ G$4 (začetna vrednost x), znak $ označuje absolutno naslavljanje. V celico B5 vnesemo formulo =B4+$H$4. To pomeni, da se bo začetna vrednost x povečala za količino koraka;

Z metodo samodejnega izpolnjevanja izpolnite celice B5:B24 s to formulo. Izberite celico B5. Premaknite miško nad oznako za polnjenje in kliknite levo gumb miške, povlecite oznako polnila tako, da črni okvir pokriva celice B5:B24. Stolpec B bo zapolnjen s številkami 0; 0,1; 0,2;… in ustrezne formule bodo v vrstici s formulami.

8. Izpolnite stolpec C z vrednostmi funkcije f(x)=x∙sin(x). V celico C4 vnesite formulo =B4∙sin(B4). Napolnimo celice C5:C24 s to formulo z uporabo metode samodejnega izpolnjevanja.

9. Izpolnite stolpec D z vrednostmi funkcije g(x)=x+1. V celico D4 vnesite formulo =B4+1. Izpolnimo celice D5:D24 s to formulo z uporabo metode samodejnega izpolnjevanja.

10. Izpolnite stolpec E z vrednostmi funkcije y=f(x)/g(x). V celico E4 vnesite formulo =C4/D4 in napolnite celice E5:E24 s to formulo z uporabo metode samodejnega izpolnjevanja.

11. Uokvirimo tabele:

12. Spremenite barvo ozadja celic glavne in pomožne tabele:

izberite glavno tabelo;

vnesite menijske ukaze Oblika → Celice → Pogled. V oknu, ki se odpre, izberite rumeno barvo. Kliknite na gumb OK.

Izberite pomožno tabelo in podobno spremenite barvo ozadja celic.

13. V glavni tabeli so vrednosti, dobljene kot rezultat izračunov pri kopiraj v stolpec TO brez formul:

izberite celice E4:E24;

premaknite kazalec miške nad obris črnega okvira, tako da ima obliko puščice;

s pritiskom na desni gumb miške in ne da bi ga spustili premaknite kazalec miške v celico K4;

Ko spustite desni gumb miške, v kontekstnem meniju, ki se odpre, izberite element kopirajte samo vrednosti.

Kot rezultat dela dobimo naslednje tabele:


Glavni						Pomožni

Aplikacija

Tabela 1



	x 2 – 1+ cos 2 (x)
	3 – x- sin 2 (x)

	12x – 3- lg 2 (x)
	5x + 6cos 2 (x)


	5x - x 3 - cos 2 (x)
		3 + x 2 cos 2 (x)
	3 + x 3 - tan 2 (x)
	4x 2 – 9- lg 2 (x)

	2 cos 2 (x)+ 5
		cos 2 (x) + x 2

	2x 2 – greh 2 (x)
	4x 3 – cos 2 (x)
	3ln 2 (x) + x 2

	3sin(x) – x 3	4 + x + cos 2 (x)
	4x 3 – greh 2 (x)
	5x 2 + lg 2 (x)
	2x 3 – x 2 + 7	4 cos 2 (x) + x 2
	3x 2 – 5x cos 2 (x)
	2sin(x) – x 2



	3cos(x) + tan(x)


	5 + x 3 -4 log 2 (x)
	4x 3 – 2x 2 -7	5 cos 2 (x) + 4x

Aplikacija

Nadaljevanje tabele 1

Naloga za študente specialnosti
	f(x)	g(x)

	3x –greh 2 (x)
		1 + x 2 cos 2 (x)
	12x - 3 cos 2 (x)



	5x – x 2 + 3
		5 + x 2 + 10x



	2cos 2(x)+5x


	2x 2 – sin(x)
	9x 3 – cos(x 2)
	5sin 2 (x) + x 3

	3sin(x) – x 3
	3x 2 –greh(x 3)

	8x 3 – x 2 + 1

	2sin 2 (x 2) – x

	4cos(x 3) – 3x





	4x 3 – 2x 2 + 7

DRŽAVNA AKADEMIJA ZA KOMUNIKACIJE SAMARA

Oddelek za računalništvo

RAČUNALNIŠTVO

Procesor za preglednice MS Excel

Navodila za izvajanje laboratorijskega dela

za študente specialnosti OPU vseh oblik študija

Sestavila: Makarova I.S.

Ermolenko T.I.

Samara 2006

Računalništvo. Procesor za preglednice MS Excel. [Besedilo]: smernice za izvajanje laboratorijskega dela za študente specialnosti izobraževalnih ustanov vseh oblik izobraževanja. - 2. del / sestavil: I.S. Makarova, T.I. Ermolenko. – Samara: SamGAPS, 2006. – 44 str.

Potrjen na seji Oddelka za informatiko dne 04.06.2006, zapisnik št. 8.

Izšlo po sklepu uredniškega in založniškega sveta Akademije.

Te smernice predstavljajo praktični vodnik o obvladovanju tehnik dela v priljubljenem procesorju za preglednice Microsoft Excel. Upoštevani so glavni elementi vmesnika, tehnike in tehnologije za delo s podatki, potrebnimi za ustvarjanje tabel, izvajanje izračunov in gradnjo diagramov. Upoštevane so dodatne funkcije MS Excela, kot so delo z besedilnimi funkcijami, matematični izračuni in analiza podatkov. Obvladati delo v procesorju preglednic, izpolniti predlagane praktične naloge, ki vsebujejo podrobno navodila po korakih, kar vam omogoča, da dobite končni rezultat.

Uporaba teh smernic predpostavlja, da učenci poznajo osnove dela z operacijska membrana Windows.

Urednik: E.A. Krasnova

Računalniška postavitev: R.R. Abrahamjan

Podpisano v objavo 15.6.2006. Format 60x90 1/16.

Pisalni papir. Tiskanje je učinkovito. Pogojno p.l. 2.75.

Naklada 200 izvodov. Naročilo št. 118.

Uvod

Microsoft Excel je precej zmogljiv in enostaven za uporabo elektronski tabelni procesor, zasnovan za reševanje širokega spektra problemov ekonomskega načrtovanja, računovodskih in statističnih, znanstvenih, tehničnih, matematičnih in drugih problemov. MS Excel temelji na delu s preglednicami.

Preglednica je sestavljena iz vrstic in stolpcev, na presečišču katerih se nahajajo celice, in je v tem smislu analogna navadni tabeli. Toda za razliko od navadne, preglednica ne služi samo za vizualno predstavitev, ampak tudi za obdelavo numeričnih, besedilnih in grafičnih informacij, shranjenih v računalniškem pomnilniku. Excel lahko deluje s celicami tabele na enak način, kot programski jeziki delujejo s spremenljivkami.

Excel podpira formate datotek, označene s pripono xl*, Excelovi lastni dokumenti pa se nahajajo v datotekah s pripono xls.

Excel ima vgrajen sistem pomoči, ki uporabniku nudi natančen opis značilnosti paketa in ponuja demo primere za boljše razumevanje osnovnih načel njihove uporabe.

Laboratorijsko delo št. 1. Osnove dela z MS Excel

Cilj dela: spozna osnovne elemente tabelnega procesorja, tehnike vnašanja informacij v tabele, tehnike oblikovanja.

Ko zaženete MS Excel ( Start/Programi/Microsoft Excel ) se na zaslonu prikaže okno procesorja preglednic z naloženim dokumentom, ki se imenuje delovni zvezek (slika 1):

riž. 1. Okno programa MS Excel

Okno Excel programi vsebuje vse standardne elemente, ki so del okna aplikacije Windows:

· ikona programa;

· naslovna vrstica;

· menijska vrstica;

· orodne vrstice;

· statusna vrstica;

· drsni trakovi.

Menijska vrstica Excel se razlikuje od menijske vrstice Word z ukazom podatki (namesto Tabela ). Orodna vrstica ima posebni gumbi za numerične podatke – denarni in odstotni formati; ločilo tisočic; povečevanje in zmanjševanje bitne globine števila; gumb za spajanje in središče besedila v skupini celic.

Spodaj se nahaja orodna vrstica Vrstica formule, ki se uporablja za vnos in urejanje podatkov v celicah. Na levi strani vrstice s formulami je spustni seznam − Polje z imenom, ki prikaže naslov trenutne celice. V isti vrstici se med vnašanjem formul prikažejo trije gumbi za nadzor postopka vnosa.

Na presečišču stolpca s številkami vrstic in vrstice z oznakami stolpcev je gumb Izberi vse, ki služi za izbiro celotnega delovnega lista.

Pod delovnim poljem je črta z oznake delovnih listov.

Razmislimo osnovni pojmi MS Excel.

Običajno se imenuje Excelov dokument delovni zvezek, sestavljena je iz zbirke delovni listi. Vsak delovni zvezek privzeto vsebuje 3 delovne liste, vendar se lahko število spremeni od 1 do 255. Delovni list ima tabelarično strukturo in je sestavljen iz 65.536 vrstic in 256 stolpcev. Vrstice so oštevilčene, stolpci pa označeni z latiničnimi črkami abeceda A,B,C, …, Z, AA, AB, AC,…, BA, BB,…, IV.

Aktivni list(trenutni list) delovnega zvezka je list, s katerim uporabnik trenutno dela. Bližnjica aktivnega lista ima vedno svetlejšo barvo ozadja, ime pa je prikazano krepko. S klikom na oznake se lahko premikate z enega lista na drugega v delovnem zvezku. Za premikanje po listih delovnega zvezka lahko uporabite tudi kombinacije tipk: Ctrl+Page Down in Ctrl+Page Up ali skupino štirih gumbov, ki se nahajajo v spodnjem levem kotu Excelovega delovnega okna.

Na presečišču vrstice in stolpca se nahaja celica– najmanjša strukturna enota delovnega lista. Vsaka celica ima naslov, ki je sestavljen iz imena stolpca in številke vrstice, na presečišču katerih se nahaja. Tako naslov celice C7 pomeni, da se ta celica nahaja na presečišču stolpca C in vrstice 7 trenutnega delovnega lista. V primerih, ko se je treba sklicevati na celice, ki se nahajajo na drugih delovnih listih, je pred naslovom navedeno ime delovnega lista, na katerem se nahajajo (na primer Sheet4!G9).

Aktivna celica(trenutno) je celica, v kateri se nahaja kazalec miške, oblikovan kot pravokoten okvir. V aktivno celico lahko vnesete podatke in na njej izvajate različne operacije.

Povezava– način določanja naslova celice. Sklici na celice se uporabljajo kot argumenti v formulah in funkcijah. Pri izvajanju izračunov se vrednost, ki se nahaja v celici, na katero kaže povezava, vstavi na mesto povezave.

Celični blok(razpon) – predstavlja pravokotno območje sosednjih celic. Blok celic je lahko sestavljen iz ene celice, vrstice (ali njenega dela), stolpca (ali njegovega dela) ali zaporedja vrstic ali stolpcev (ali njihovih delov). Blokiraj naslov je kombinacija naslovov zgornje leve in spodnje desne celice bloka, ločenih z dvopičjem. Na primer, blok z naslovom "A3:B5" vsebuje naslednjih šest celic: A3, A4, A5, B3, B4, B5.

Excel vsebuje več kot 400 vgrajenih funkcij. Za lažje delo z vgrajenimi funkcijami uporabite Čarovnik za funkcije.

NALOGA 1. Spoznavanje vmesnika Excel

1. Zaženite Excelovo preglednico . Dokument z imenom Book1 se samodejno odpre.

1. Določite število listov v Book1. Prilepite prek kontekstnega menija Dodaj… - List dva dodatna lista. Bodite pozorni na imena novih listov in na to, kje so nameščeni .

2. Povlecite zavihke listov čez vrstico z zavihki, tako da bodo listi oštevilčeni v zaporedju.

3. Shranite delovni zvezek v svojo mapo kot datoteko z imenom tabl.xls.

NALOGA 2. Izbira celic, vrstic, stolpcev, blokov in listov

2. Preizkusite različne načine izbiranje fragmentov preglednice (glej tabelo 1).

Tabela 1

Izbirni predmet	Tehnika operacije
Celica	Kliknite na celico
Linija	Kliknite na ustrezno številko vrstice
Stolpec	Kliknite na ustrezno številko stolpca (črka)
Blok (razpon) sosednjih celic	1. Kazalec postavite na začetek izbire (zgornja leva celica izbranega bloka). Pritisnite levi gumb miške. Povlecite kazalec diagonalno v spodnji desni kot izbranega bloka 2. Kliknite na skrajno zunanjo kotno celico izbranega bloka, pritisnite tipko Shift in kliknite na nasprotno kotno celico
Skupina nesosednjih celic	Izberite prvo celico skupine. Pritisnite in držite tipko Ctrl Izberite preostale celice skupine
Bloki nesosednjih celic	Izberite blok sosednjih celic. Pritisnite tipko Ctrl Izberite naslednji blok celic
Delovni list	Kliknite gumb »Izberi vse« v zgornjem levem kotu delovnega lista
Več sosednjih delovnih listov	Izberite prvi delovni list. Pritisnite tipko Shift in, ne da bi jo spustili, izberite zadnji delovni list
Več nesosednjih delovnih listov	Izberite prvi delovni list. Pritisnite tipko Ctrl in izberite naslednji delovni list, ne da bi jo spustili

3. Prekličite izbiro skupine listov s klikom na jeziček katerega koli neaktivnega lista.

4. Naj bo aktiven List 2 s klikom na njegovo bližnjico.

5. Z miško izberite celico C6. Vrni se v celico A1 s pomočjo smernih tipk.

6. Naj bo trenutno (aktivno) List 5. Odstrani List 5 z uporabo kontekstnega menija.

7. Z menijskim ukazom vstavite nov list Vstavi. Pozor! Ime novega lista je List 6.

8. Z miško premaknite bližnjico List 6 po nalepki List 4.

9. Vrnite se na List 1. Poimenujte ga s pomočjo kontekstnega menija Tabela.

10. Pojdi na List 2. Izberite vrstico 3. Prekličite izbor tako, da z levim gumbom miške kliknete katero koli neizbrano celico.

11. Izberite stolpec D.

12. Izberite Stolpci skupaj B, C, D. Prekličite izbiro.

13. Izberite obseg celic (blok) C4:F9 uporabo miške. Prekličite izbiro.

14. Izberite blok A2:E11 ko je tipka pritisnjena Shift.

15. Izberite nesosednje bloke hkrati A5:B5, D3:D15, H12, F5:G10.

16. Izberite celotnega delavca List 2. Prekličite izbiro.

NALOGA 3. Vnos podatkov v celice. Oblikovanje celic

· Pri polnjenju celic z informacijami morate najprej izbrati celico, v katero vnašate podatke, nato pa podatke vnašati s tipkovnice.

· Po vnosu morate pritisniti tipko Vnesite, oz Tab ali katero koli od puščic za nadzor kazalca, da popravite podatke v celici.

Če želite zavrniti vnos podatkov, morate pritisniti tipko Esc.

1. V celico A1 list 2 vnesite besedilo Letnica ustanovitve šole št. 147.

2. V celico B1 vpišite letnico ustanovitve šole 1965.

Pomembno!

Besedilni podatki so poravnani na levi rob celice, številke pa na desni rob.

3. Opazite, da je besedilo v celici A1"ni ustrezal" in je bil odrezan na desni. Pravzaprav je vse besedilo še vedno v celici A1, lahko to preverite tako, da izberete celico in pogledate vrstico s formulami nad delovnim listom.

4. Spremenite širino stolpca A tako da je v celici vidno celotno besedilo . Če želite to narediti, povlecite desno ločilo v glavi stolpca (med črkami A in IN v naslovih stolpcev) ali dvokliknite ločilo stolpcev. Ukaze menija lahko uporabite tudi za spreminjanje širine stolpca Oblika / Stolpec / Širina (Samodejna izbira širine oz Standardna širina).

5. V celico A2 vnesite besedilo Tekoče leto.

6. V celico NA 2 vnesite vrednost tekočega leta.

7. V celico A3 vnesite besedilo Šolska starost.

8. Izberite celico NA 3, S tipkovnice vnesite formulo za izračun šolske starosti =B2-B1. V celici se pojavi številska vrednost, ki označuje starost šole v letih.

Pomembno!

4Vnos formul se vedno začne z enačajem = .

4Naslove celic je treba vnesti brez presledkov latinsko pisma.

4Naslove celic lahko vnesete v formule brez uporabe tipkovnice, preprosto s klikom nanje z miško.

9. Spremenite širino prvega stolpca, tako da bo celica široka približno 10 znakov. To lahko storite "na oko" z miško ali z desnim klikom na glavo stolpca (črka A) in zagon ukaza Širina stolpca ... (V tem primeru bo besedilo v celicah prvega stolpca ponovno obrezano.)

10. Izberite blok celic A1:A3 in zaženite ukaz Oblika / Celice ...

Pojdi na zaznamek Poravnava in potrdite polje Ovijte po slov.

11. Bodite pozorni na robove Vodoravna poravnava in navpično. Preglejte vsebino spustnih seznamov teh polj in nastavite npr. možnost levo in Središče oz. Kliknite v redu. Kot rezultat videz celice prvega stolpca se bodo izboljšale.

12. Ponovno izberite blok celic A1:A3 in zaženite ukaz Oblika / Celice ...

13. Pojdite na zaznamek Pisava. Nastavite slog Krepko poševno. Sami spremenite barvo pisave.

14. Pojdite na zaznamek Pogled in izberite barvo polnila celice.

15. Izberite blok celic A1:B3 in zaženite ukaz Oblika / Celice ...

16. Pojdite na zaznamek Meja. Preverite razpoložljive vrste linij. Izberite vrsto in barvo črte. Nato kliknite Zunanji in/ali Domače za nastavitev meja celic (splošen pogled je viden v vzorčnem oknu). Kliknite v redu.

17. V celico D1 vnesite besedilo Letnica mojega rojstva .

18. V celico E1 Vnesite svojo letnico rojstva.

19. V celico D2 vnesite besedilo Tekoče leto.

20. V celico E2 vnesite vrednost tekočega leta.

21. V celico D3 vnesite Moja starost.

22. V celico E3 Vnesite formulo za izračun svoje starosti.

23. Določite svojo starost leta 2025. Če želite to narediti, zamenjajte leto v celici E2 za 2025 . Upoštevajte, da se ob vnosu novih podatkov tabela ponovno izračuna samodejno.

24. Sami oblikujte celice in jih uredite po analogiji s prejšnjo tabelo.

25. Preimenuj List 2 V poskusite

26. Shranite svoje delo.

NALOGA 4. Operacije premikanja, kopiranja in brisanja vsebine celice

1. Izberite celico A1. Kopiraj celico A1 z uporabo desne tipke miške ali gumba v orodni vrstici Standardno. Prilepi vsebino celice A1 v celico A5 z uporabo desne tipke ali tipkovnice. Upoštevajte, da ni bila kopirana le vsebina celice, ampak tudi elementi za oblikovanje celice.

2. Ponovno kopirajte celico A1 v celico A7.

3. Z miško premaknite vsebino celice A7 v celico A9.Če želite to narediti, izberite celico A7, Kazalec miške premaknite na okvir in ga povlecite s pritisnjeno levo tipko miške.

4. Vrnite vsebino celice A9 v celico A7.

5. Z miško kopirajte vsebino celice A7 v celico A9.Če želite to narediti, morate med premikanjem držati tipko Ctrl.

6. Uporaba menijskih ukazov Uredi/Izreži, in potem Uredi / prilepi premakniti vsebino celice A5 v celico A11.

7. Izberite celico A11 in pritisnite tipko Izbriši. Opazite, da je bila vsebina celice odstranjena, vendar je oblikovanje ostalo. Če jih želite odstraniti, morate zagnati ukaz Uredi / Počisti / Oblikuj.

8. V celici A7 spremenite orientacijo besedila tako, da je besedilo pod kotom 45° (menijski ukaz Oblika / Celice , zaznamek Poravnava).

9. V celici A9 postavite besedilo navpično.

10. Shranite svoje delo.

NALOGA 5. Samodejno izpolnjevanje celic

1. Naj bo aktiven List 3. Preimenuj ga v Samodokončanje.

2. V celico E9 vnesite besedo: sreda. Izberite celico. Z miško usmerite na oznako za samodejno izpolnjevanje - kvadrat v spodnjem desnem kotu okvirja. Pritisnite levi gumb miške in ga držite pritisnjenega, premaknite miško nekaj vrstic navzdol.

3. Ponovno izberite celico E9 in ga povlecite za oznako nekaj stolpcev v desno.

4. Ponovite operacijo vlečenja celice E9 z oznako še dvakrat - gor in levo.

5. Analizirajte rezultate in počistite list. To storite tako, da kliknete prazen gumb v zgornjem levem kotu delovnega lista in pritisnete tipko Izbriši.

6. V celico A1 vnesite številko 1. Povlecite jo za oznako navzdol do 10. vrstice. Analizirajte rezultat.

7. V celico V 1 vnesite številko 1.

8. V celico NA 2 vnesite številko 2.

9. Izberite blok celic B1:B2, ga povlecite za oznako 10 vrstic navzdol. Analizirajte rezultat.

10. V celico C3 vnesite številko 1.

11. Povlecite ga za oznako desni klik miško 10 vrstic navzdol. Spustite levi gumb miške in prikazal se bo kontekstni meni. V meniju izberite ukaz Napredovanje ...

12. V pogovornem oknu, ki se odpre Napredovanje vrsta kompleta - Aritmetika , korak - 2 . Kliknite v redu

13. V celico D1 vnesite besedilo: januar. Izberite celico in povlecite oznako 12 vrstic navzdol.

14. V celico E1 vnesite besedilo VAZ 2101. Povlecite ga z oznako 12 vrstic navzdol. Analizirajte svoje rezultate.

15. V celico F1 Kopiraj celice . Analizirajte svoje rezultate.

16. V celico G1 vnesite besedilo VAZ 2101. Povlecite ga z oznako z desnim gumbom miške 12 vrstic navzdol. V kontekstnem meniju, ki se odpre, izberite ukaz Izpolnite . Analizirajte svoje rezultate.

17. Shranite rezultate.

NALOGA 6. Ustvarite seznam za samodokončanje

V prejšnji dejavnosti ste videli, da uporaba oznake za samodokončanje omogoča hitro ustvarjanje seznamov, kot so dnevi v tednu ali meseci v letu. Ti seznami so vključeni v t.i samodokončanje seznamov . Takšen seznam lahko ustvarite sami in ga nato uporabite pri izpolnjevanju kontrolnih seznamov.

1. Naredite list aktiven Samodokončanje.

2. Izvedite menijski ukaz Storitev / Možnosti .

3. Pojdite na zaznamek Seznami.

4. Kliknite na vrstico Nov seznam na terenu Seznami. Hkrati pa na terenu Elementi seznama Prikazal se bo besedilni kazalec.

5. S tipkovnico vnesite imena 10 študentov iz vaše skupine (po vnosu vsakega imena pritisnite tipko Vnesite). Po končanem izbiranju pritisnite tipko Dodaj. V polju se prikaže vtipkani seznam Seznami. Kliknite v redu.

6. V celico H1 Vnesite kateri koli priimek s seznama, ki ste ga ustvarili, in ga povlecite za oznako nekaj vrstic navzdol. Na delovnem listu se bo pojavil seznam študentov.

7. Za urejanje seznama znova izvedite menijski ukaz Storitev / Možnosti in pojdite na zaznamek Seznami.

8. Na terenu Seznami izberite seznam, ki ste ga ustvarili (pojavil se bo tudi v Elementi seznama na desni strani okna). Izbrišite prvi priimek in namesto njega vnesite priimek Barmalejev .

9. Pritisnite gumb Dodaj, in potem v redu.

10. Seznam v stolpcu n ni spremenila. Pomisli zakaj. Kaj je treba narediti za posodobitev seznama? Odgovor na to vprašanje vpiši v polje A15.

11. Pokaži rezultat učitelju.

12. Odstranite seznam, ki ste ga ustvarili, s seznama seznamov.

13. Shranite svoje delo.

NALOGA 7. Razporejanje

1. Naredite List aktiven 4. Preimenujte ga v Urnik.

2. V celico A1 vnesite besedilo Št. urnika skupinskega pouka (navedite številko skupine) za tekoči teden.

3. V celice A3-A6 vnesite razredne ure (8:30 - 10:00, 10:15 - 11:45 itd.)

4. V celice B2 - F2 vnesite imena dni v tednu (uporabite oznako za samodejno izpolnjevanje).

5. Izpolni tabelo z imeni predmetov s tehnikami prepisovanja.

6. Označite celice prve vrstice A1 – F1 in jih združite z menijskim ukazom Oblika / Celice (zaznamek Poravnava) ali z uporabo gumba Združite in postavite na sredino.

7. Oblikujte naslov tabele z ukazom Oblika / Celice.

8. Oblikujte glavno polje razporeda z obrobami in polnili.

9. Shranite svoje delo.

10. Pokažite svoje delo učitelju.

Laboratorijska dela Excel

Laboratorijsko delo št. 1

Ustvarjanje seznama strank

Vnesite seznam 15 podjetij. Podjetja razdelite med 5 mest. Ko vnesete prvi vnos, kliknite na gumb Dodaj.

Oblikovanje mize. Za celice

Odstotna oblika

Oblikovanje

Razvrščanje podatkov. Izbrati ga je treba v meniju podatki  Razvrščanje. V pogovornem oknu izberite prvi kriterij razvrščanja Koda in drugo merilo Mesto in V REDU.

Filtriranje podatkov. Izberite v meniju podatki  Filter/Atofilter. Po kliku na ime tega ukaza se bo v prvi vrstici poleg naslova vsakega stolpca prikazal puščični gumb. Z njim lahko odprete seznam, ki vsebuje vse vrednosti polj v stolpcu. Izberite ime enega od mest v Mesto. Poleg vrednosti polj vsak seznam vsebuje še tri elemente: (Vse), (Prvih 10...) in (Pogoj...). Element (vse) je zasnovan za obnovitev prikaza vseh vnosov na zaslonu po uporabi filtra. Element (Prvih 10 ...) omogoča samodejni prikaz prvih desetih vnosov na seznamu. Če se ukvarjate s sestavljanjem vseh vrst ocen, glavna naloga ki naj bi določil prvih deset, uporabite to funkcijo. Zadnji element se uporablja za oblikovanje bolj zapletenega izbirnega kriterija, v katerem je mogoče uporabiti pogojne operatorje IN in ALI.

Kazalec postavite v poljubno zapolnjeno celico in naredite naslednje: v meniju Oblika  Samodejno oblikovanje  Seznam 2 .

Ustvarjanje seznama izdelkov

Drugi seznam bo vseboval podatke o izdelkih, ki jih ponujamo.

Laboratorijsko delo št. 2

Naročila listov

Preimenujte delovni list ListZ naslovljen Naročila.

V prvo vrstico vnesite naslednje podatke, ki bodo v prihodnje uporabljeni kot imena polj:
A1  Mesec naročila , V 1  datum naročila , Z 1  Številka naročila , D1  Številka artikla , E1  Ime izdelka , F1  Količina , G1  cena za eno ., H1  Šifra podjetja stranke ., jaz1  Ime podjetja stranke , J1  Cena naročila , K1  Popust (%) , L1  Skupaj plačano .

Za prvo vrstico naredite usklajevanje podatkov v središču Oblika Celice Poravnava prevesti po slov .

Izberite stolpce enega za drugim B, C, D, E, F, G, H, I, J, K, L in vstopite polje ime imena Datum, Naročilo, Številka2, Izdelek2, Količina, Cena2, Koda2, Podjetje2, Znesek, Popust2 in Plačilo .

Izberite stolpec IN in izvedite menijski ukaz Oblika Celice. V zavihku številka izberite
Oblika številke datum, in na terenu Vrsta izberite obliko, kot je HH.MM.YY. Na koncu dialoga
kliknite gumb V REDU.

Izberite stolpceG, J, L in izvedite menijski ukaz Oblika Celice. V zavihku številka
izberite Oblika številke denarna , prosim navedite Število decimalnih mest enako 0, in v polju
Izberite oznako $ angleščina (ZDA). Na koncu pogovornega okna kliknite gumb v redu.

Izberite stolpec K in izvedite menijski ukaz Oblika Celice. V zavihku številka izberite
Oblika številkeOdstotek , prosim navedite Število decimalnih mest enaka 0. Končno
pogovorno okno kliknite gumb v redu.

V celici A2 vnesti morate naslednjo formulo:

=IF(EBLANK($B2),“ ”,SELECT(MONTH($B2), “januar”, “februar”, “marec”, “april”,“Maj”; “Junij”; “Julij”; “Avgust”; “September”; “Oktober”; “November”; “December”)) (3.1)

In napolnite celico rumeno.

Formula (3.1) deluje takole: najprej se preveri pogoj za prazno celico A2. Če je celica prazna, vstavite presledek, sicer pa s funkcijo IZBERI s seznama izberite želeni mesec, katerega številko določi funkcija MESEC.

Če želite vnesti formulo (3.1) sledite tem korakom:

naredi celico aktivno A2 in pokličite funkcijo ČE;

v oknu funkcije IF v polju logični_izraz ročno vnesite $ B2= "", V

polje vrednost_če_true pokliči " " , na terenu vrednost_če_napačna pokličite funkcijo SELECT;

v funkcijskem oknu IZBIRA na terenu vrednost1 tip " januar", na terenu vrednost2 tiskanje

na terenu številka_indeksa in pokličite funkcijo MESEC;

v oknu funkcije MESEC v polju Datum_kot_številka klicni naslov $ B2 ;

Kliknite gumb v redu.

V celico E2 vnesite naslednjo formulo:

=ČE($ D2=" "; “ ”;POGLED($D2;Številka izdelka;Ime izdelka) (3.2)

Pravilo za tipkanje formule:
Kliknite celico E2. Kazalec postavite na ikono standardne plošče. Odpre se okno Mojster funkcije..., izberite funkcijo IF. Sledite korakom, ki jih vidite na sliki

Tisti. v položaju Logično izražanje kliknite na celico D2 in trikrat pritisnite tipko F4 - pridobite $D2, vnesite = " ", uporabite tipko Tab ali miško, da se premaknete na položaj Vrednost_če_resnično in pokličite. " ", pojdi na položaj Vrednost_če_napačno– kliknemo na gumb ob imenu funkcije in izberemo ukaz Druge funkcije.. → Kategorije → Povezave in polja, v oknu Funkcije → OGLED→ OK→ OK.

Odprlo se bo funkcijsko okno OGLED. V položaju Search_value kliknite na celico D2 in trikrat pritisnite tipko F4 - pridobite $D2, uporabite tipko Tab ali miško, da se premaknete na položaj Ogledan_vektor in kliknite bližnjico do lista " Blago", izberite obseg celic A2:A12, pritisnite tipko F4, pojdite na položaj Vektorski_rezultati– ponovno kliknite na oznako lista " Blago", izberite obseg celic B2:B12, pritisnite tipko F4 in OK. Če ste vse naredili pravilno, se bo pojavilo v celici # HD.

napolni celico rumena barva.

10. V celico G2 vnesite naslednjo formulo:

=ČE($D2=“ ”;“ ”;POGLED($D2;Številka artikla; Cena)) (3,3)

Napolni celico rumena barva.

11. V celico jaz2 vnesite naslednjo formulo:
=IF($H2=“ ”;“ ”; POGLED($H2;Koda; trdno)) (3,4)
Napolni celico rumena barva.

12. V celico J2 vnesite naslednjo formulo:
=ČE(F2=“ ”;“ ”;F2* G2) (3.5)
Napolni celico rumena barva..

13. V celico K2 vnesite naslednjo formulo:
=IF($H2=“ ”;“ ”; POGLED($H2;Koda; Popust)) (3,6)
Napolni celico rumena barva.

14. V celico L2 vnesite naslednjo formulo:
=ČE(J2=“ ”;“ ”;J2- J2* K2) (3.7)
Napolni celico rumena barva.

15. Celice B2, D2 in H2 – v katerih ni formul, izpolnite modra barva. Izberite obseg A2 – L 2 in marker za polnjenje ( črni križ v spodnjem desnem kotu bloka ) raztegljivo polnilo in formule do 31 linije vključno..

16. Naredi celico aktivno NA 2 in povlecite oznako za polnjenje navzdol do celice VZ1 vključno.

17. V celico C2 vnesite številko 2008-01, ki bo začetna vrstna številka, in povlecite oznako za polnjenje navzdol v celicoCZ1 vključno.

18. Zdaj morate s tipkovnico izpolniti stolpce B2:B31 , D2: D31 in H2: H31. Z NA 2 Avtor: OB 11 Vtipkamo januarske datume (na primer 01/2/08, 01/12/08). Z PRI 12 Avtor: PRI 21 vtipkamo februarske datume (na primer 02/12/08, 02/21/08) in od B22 Avtor: B31 Vtipkamo marčevske datume (na primer 03/5/08, 03/6/08). IN D2: D31 pokličite številke izdelkov, tj. 101, 102, 103, 104, 201, 202, 203, 204, 301, 302 in 303. Številke se lahko ponavljajo in v poljubnem vrstnem redu, podobno kot v H2: H31 vstopiti Kode vaša podjetja, ki ste jih vpisali na list Stranke. V stolpec F vstopiti dvomestno število številke.

19.

(SRSP) Laboratorijsko delo št. 3

Obrazec za naročilo

V celico H5 vnesite vnos Koda, in v celico

jaz

=ČE($

$3=“ ”; “ “;POGLED($

$3;Naročilo; Koda2))

V celico C7 vnesite vnos Ime izdelka.

=ČE($

$3=“ ”; “ “;POGLED($

$3;Naročilo; Izdelek2)),

V celico H7 vnesite znak № , in v celico

jaz

=ČE($

$3=“ ”; “ “;POGLED($

$3;Naročilo; številka 2))

V celico C9 vnesite vnos Naročena količina.

V celico E9– formula
=ČE($ E

$3=“ ”; “ “;POGLED($

$3;Naročilo; Količina))

enote po ceni

Celica H9 mora vsebovati formulo
=ČE($ E

$3=“ ”; “ “;POGLED($

$3;Naročilo; Cena 2)),

Tej celici je treba dodeliti podčrtaj in slog valute.

jaz

na enoto

Vstopi C11 besedilo Skupni stroški naročila, in v E11 postavite formulo
=ČE($ E

$3=“ ”; “ “;POGLED($

$3;Naročilo; vsota)),

Popust (%)

11,

11, N11

Združite in postavite na sredino

jaz

=ČE($

$3=“ ”; “ “;POGLED($

$3;Naročilo; Popust2)),

in nastavite možnosti oblikovanja: podčrtani in odstotni slog.

V celico C13-besedilo Plačati. In v celici

=ČE($

$3=“ ”; “ “;POGLED($

$3;Naročilo; Plačilo)),

in nastavite možnosti oblikovanja: podčrtano in slog valute.

V celico E13 vnesite vnos Oblikovano od:, označite E13,

13, H13,

jaz

Na koncu nastavite širino stolpcev

Zdaj noter E3 prosim navedite Številka naročila, in preden natisnete obrazec priimek.

Nalogo ste uspešno opravili, predajte jo učitelju!

Vrteča miza

Za praktično uporabo je bil ustvarjen seznam naročil, njegovi podatki pa so predmet analize. Čarovnik za vrtilne tabele nam bo pomagal pri izvedbi analize.

Vrtilne tabele so ustvarjene iz seznama ali baze podatkov.

8. Nalogo ste uspešno opravili, predajte jo učitelju!

(SRSP) Lab. Št. 4. Podružnice

Ustvarite delovni zvezek in ga shranite v svojo mapo pod imenom Podružnice (vaš priimek). Začnimo primer z ustvarjanjem tabele in vnosom podatkov o vsaki veji.

Pripravljalna faza. Kopiraj v odložišče z lista Blago knjige Naročila podatke o blagu, njegovem številu in cenah, tj. kopirajte obseg celic A1-C12 list Blago.

Pojdite na prvo stran knjige Podružnice in v celico A3 prilepite kopirani fragment tabele. V 3. nastajanje v celicahD3, E3, F3 vnesite ustrezne vnose Število naročil, prodana količina in Obseg prodaje. Nastavite centriranje besedila v celicah in dovolite, da se besedilo zavije okoli besed.

V celico F4 vnesite formulo: =C4*E4 in ga kopirajte v celice F5- F14 .

Vnesite celico B15 beseda Skupaj:, in v celicoF15 vstavite formulo vsote ali kliknite gumb orodne vrstice Standardno. Excel samodejno določi obseg celic, katerih vsebino je treba sešteti.

Takšnih listov naj bo toliko, kot ste imeli mest na listu Stranke. Ta list moramo kopirati 4-krat.

Če želite to narediti, postavite kazalec miške na njegovo bližnjico in pritisnite desni gumb manipulatorja. V kontekstnem meniju izberite ukaz Premakni/Kopiraj, v pogovornem oknu, ki se prikaže, določite list, pred katerega želite vstaviti kopijo, aktivirajte možnost Ustvarite kopijo in pritisnite v redu. Veliko lažje je kopirati z miško: postavite kazalec miške na bližnjico do lista in ga premaknite na položaj za vstavljanje kopije, medtem ko držite tipko [ Ctrl] .

Imena delovnih listov se ujemajo z naslovi mesta od pogleda Stranke, na primer Almati, Astana, Šimkent, Aktau, Karaganda ali druga imena. Vnesite ime veje, ki ustreza imenu lista in v celico A1 tega lista.

Izpolnite list Naročilaše en stolpec. V celico M1 vnesite besedo Mesto. V celico M2 vnesite formulo =ČE(ZAPEZEN($ H 2);“ ”;POGLED($ H2;Koda; mesto)) , razširi to formulo na 31. vrstico tega stolpca.

Izberite v meniju podatki  Filter/Atofilter. Izberite v stolpcu Mesto prva veja. Podatki stolpcaKoličina list Naročila boste vpisali v stolpecProdana količina list knjige Podružnice, v vrsticah, ki ustrezajo številkam izdelkov. Če se blago z isto številko proda v različnih mesecih, se upošteva njihova skupna količina. In tako so izpolnjeni listi vseh mest.

Konsolidacija podatkov. Kopiraj s prve strani knjige Podružnice obseg A3-B14, pojdite na delovni list 6 in prilepite v celico A3.

Začnimo s konsolidacijo. Nastavite kazalec celice naC3 in izberite v meniju podatki  Utrjevanje.

Na seznamu Funkcije element je treba izbrati vsota Vnesite v polje za vnos Povezava obseg celic, katerih podatki naj bodo podvrženi procesu konsolidacije. Z miško je priročno označiti obseg celic.

Kazalec za vnos postavite v polje Povezava, kliknite na prvo mestno bližnjico, na primer – Almaty, izberite obseg celicD3- F14 in pritisnite gumb Dodaj okno Utrjevanje. Posledično bo navedeni obseg prerazporejen v polju Seznam obsegov.

Nato pojdite na list drugega mesta. Razpon se samodejno prikaže, pritisnite gumb Dodaj in tako naprej 5x.

Če zgornja vrstica in/ali levi stolpec vsebuje naslove, ki jih želite kopirati v končno tabelo, omogočite ustrezne možnosti v skupini Uporabite oznake. Ker v našem primeru zgornja vrstica vsebuje naslove stolpcev, moramo to možnost omogočiti Na zgornji vrstici.

Če želite vzpostaviti dinamično razmerje med izvornimi podatki in podatki konsolidirane tabele, omogočite možnost Ustvarite povezave z izvornimi podatki.

gumb Pregled uporabite za izbiro datoteke, ki vsebuje podatke za konsolidacijo.

Kliknite gumb V REDU.

V celico A1 vnesite ime nove tabele Zbirni podatki.

Vnesite celico B70 pomen Skupaj:, in v E70 - in pritisnite tipko [ Vnesite]

Zdaj nadaljujemo z določitvijo deleža celotnega dobička zneska, prejetega od prodaje vsakega izdelka. Vstopi F9 formula = E9/$E$70 in ga kopirajte v preostale celice stolpcev F ( v celico F70) .

Oblikuj vsebino stolpcaF v odstotnem slogu. Dobljeni rezultati nam omogočajo sklepanje o priljubljenosti določenega izdelka.

Pri konsolidaciji podatkov program zabeleži vsak element v končno tabelo in samodejno ustvari strukturo dokumenta, ki vam omogoča, da na zaslonu prikažete le potrebne informacije in skrijete nepotrebne podrobnosti. Simboli strukture so prikazani na levi strani tabele. Številke označujejo nivoje strukture (v našem primeru - 1 in 2). Gumb z znakom plus omogoča dešifriranje podatkov višje ravni. Kliknite na primer gumb za celico A9 za informacije o posameznih naročilih.

Kopirajte formulo izF9 v celice F4- F8.

Številke se spremenijo v diagrame

Pripravljalna dela. Ker vsak grafikon potrebuje svojo tabelo, ustvarimo novo vrtilno tabelo na podlagi podatkov delovnega lista Naročila istoimenska knjiga Naročila.

Odprite predhodno ustvarjen delovni zvezek Naročila. Ustvarite nov delovni zvezek in poimenujte njegov prvi list Tabela . Ta list bo vseboval numerični material za grafikon.

Postavite kazalec v celico NA 3 in izberite meni podatki  Vrteča miza.

Izberite prvi način urejanja podatkov – Na seznamu ali bazi podatkov

Microsoft

Excel

Nadalje.

V drugem koraku postavitev kazalca vnosa v polje Razpon sledi z uporabo menija Okno pojdite v delovni zvezek Naročila in na delovni list Naročila in označite obseg

Nadalje

Treba je določiti strukturo vrteča miza. Postavite v območje vrstice gumb Ime izdelka, in na območje stolpce – gumb mesec. vsota se bo izračunalo po področjih Cena naročila, tiste. premaknite ta gumb na območje podatke . Kliknite gumb pripravljena.

Izberite obseg

Kliknite gumb Čarovnik za grafikone v orodni vrstici Standardno.

V prvem koraku določite vrsta grafikona, kliknite na gumb Nadalje.

V drugem koraku potrdite obseg =Tabela!$

$4:$

$15.

V tretjem koraku označite parametri grafikona (Naslovi, osi, legende itd.).Naslov grafikona vstopiti Obseg prodaje po mesecih,Kategorija (X)- Ime izdelka in Pomen (

)

Obseg prodaje (

ameriški dolar

)

vzorec,

Nadalje.

Kliknite na gumb pripravljena

Laboratorijsko delo št. 1

Namen dela: naučiti se delati s preglednicami in se naučiti sestavljati različne diagrame.

Kratke teoretične informacije

Excel je program za izračune s preglednicami.

Vmesnik okna aplikacije Excel je podoben vmesniku okna aplikacije Word (naslovna vrstica, menijska vrstica, orodne vrstice, vrstica stanja). Toda dodana je vrstica formule, ki je ni v Wordu.

Obstajata dve vrsti prikaza Excelovih dokumentov – “normal” in “page layout”, ki ju lahko nastavite v meniju Pogled.

Nastavitve strani se konfigurirajo v meniju File/Page Setup. Tukaj lahko nastavite glavo in nogo na strani. V glavi lahko navedete na primer številko skupine, v nogi - polno ime študenta. V zavihku »List« lahko konfigurirate zaporedje izpisa strani.

Delovni zvezek. Excelov dokument je delovni zvezek , sestavljen iz kompleta delovni listi , shranjeno na disku v ena datoteka . Privzeto ima delovni zvezek 3 liste. To število lahko spremenite (do 255) v zavihku Orodja/Možnosti/Splošno. Knjigi lahko kadar koli dodate ali odstranite liste (preko kontekstnega menija z desnim gumbom). Liste v knjigi lahko zlepimo skupaj (Shift+klik na liste, ki jih je treba zlepiti). Podatki, zapisani na lepljenih listih, so enaki. Na primer, če morate ustvariti isto predlogo tabele na več listih, jih morate zlepiti skupaj, enkrat ustvariti tabelo in nato s kontekstnim menijem "razdružiti" liste. Vsi listi, ki so bili zlepljeni skupaj, bodo imeli isto tabelo.

Poleg delovnih listov lahko delovni zvezek shrani grafikone, ki temeljijo na podatkih iz ene ali več tabel, in makre. Makro je program VisualBasic, ki obdeluje podatke tabele.

Med dokumenti delovnega zvezka lahko organizirate povezavo, spremembe v eni tabeli pa se samodejno zabeležijo v vse povezane dokumente. Excel obdeluje tudi podatke, ki jih pripravljajo različne Windows aplikacije.

Delovni list. Vsebuje elektronske celice z naslovom: A1, B10 itd. Naslov trenutne celice se prikaže v polju z imenom (skrajno levo polje vrstice s formulami). Delovni listi vsebujejo 256 stolpcev in 65536 vrstic. Naslovi stolpcev so A...Z,AA...AZ,BA...BZ. Glave vrstic: od 1 do 65536.

Celični podatki. V celice lahko vnesete dva prijazen podatki: konstantne vrednosti in formule . Konstantne vrednosti se vnesejo neposredno v celico, pri kopiranju se ne spremenijo. Formule se uporabljajo za organiziranje izračunov. Pri kopiranju formul vrednosti podatkov v celicah spremeniti.

Obstajata dve reprezentanca celični podatki: v stroju in na zaslonu . In-machine se uporablja za izračune; to so notranje vrednosti celic, ki niso prikazane na zaslonu. Zaslonska predstavitev je določena s formatom celice.

Celice lahko vsebujejo naslednje vrste podatkov :številke, besedilo, datum in čas, logične vrednosti, vrednosti napak.

Številke. Številke so shranjene v stroju z največjo natančnostjo. Predstavitev števila na zaslonu je določena s formatom: Oblika/Celice/Številka/Oblike števil. Vnesete lahko cela števila, decimalke ali števila v potenčni (eksponentni) obliki. Če je celica zapolnjena z ostrimi pikami, to pomeni, da vneseno število presega širino stolpca.

Besedilo . To je kateri koli nabor vnesenih znakov, ki jih Excel ne interpretira kot število, datum in čas, logično vrednost ali vrednost napake. V celico lahko vnesete do 255 znakov besedila. Če želite v formulo vnesti številke kot besedilo, jih morate dati v narekovaje. =”45,00”.

Oblikovanje besedila: Oblika/Celice/Poravnava zavihkov, Pisava, Obroba, Videz.

datum in čas .Datum je v napravi predstavljen kot številka, določena s številom dni od sistemskega datuma (1900) do tistega, ki je predstavljen v

celica. To lahko vidite, če v datumski celici izberete obliko »Splošno«. Datum 01/22/2005 je enakovreden številu 38374 dni od 01/01/1900, datum 01/07/2005 pa je enakovreden številu 38359 dni od 01/01/1900. Zato lahko operacije seštevanja in odštevanja izvajamo na datumih (v celicah z datumom “01/15/1900” in številko “15” je formula =A1-B1, ki izračuna število dni med datumoma “01 /22/2005-01/07/2005”. Razlika je 15 ). Čas je v stroju predstavljen kot ulomek. To je mogoče videti tudi, če v celici čez čas izberete obliko »Splošno«. Čas 16:14 je enak ulomku 0,6763889.

Ekransko predstavitev datuma in časa določimo tudi v meniju Oblika/Celice/Številka/Oblike števil. Za hiter vnos trenutnega časa v celico pritisnite Ctrl+<:>, za trenutni datum pa – Ctrl+<;>.

Logične vrednosti vzemite vrednosti "true" in "false". Te vrednosti so rezultat izvajanja logičnih in primerjalnih operacij.

Napačne vrednosti so posledica napačnih izračunov. Napačne vrednosti se začnejo z ostro: n/a! (neveljavna vrednost), povezava! (neveljaven sklic), vrednost! (nepravilen tip argumenta v funkciji), ime! (ne razumem imena), število! (ne morem pravilno interpretirati formule v celici) itd.

Razpon celic– skupina zaporednih celic. Sklici na obseg uporabljajo naslednje naslovne operacije:

: (dvopičje) – omogoča sklicevanje na vse celice med mejami obsega, vključno z

strogo (A1:B15);

, (vejica) – operator za združevanje obsegov celic ali posameznih celic

ب (presledek) – presečni operator, ki se nanaša na celice skupnega obsega,

Β5:B15î A7:D7. V tem primeru je celica B7 skupna dvema obsegoma.

Vnos, urejanje in oblikovanje podatkov.

Razlikujemo med neposrednim vnosom podatkov in uporabo orodij za avtomatizacijo med vnosom.

Neposredno – neposreden vnos podatkov v trenutno celico. Če želite dokončati vnos v trenutno celico in se premakniti v naslednjo celico, pritisnite eno od naslednjih tipk

Pri vstopu iste podatke v obseg morate: Izbrati obseg – Vnesti podatke v aktivno celico obsega – pritisniti Ctrl+Enter.

Avtomatizacija vnosa.

Urejanje.

Operacije urejanja lahko razdelimo v naslednji dve skupini:

Urejanje predstavljen v celico podatke . Vsebino celic lahko urejate neposredno v celici (dvokliknite celico) ali v vrstici s formulo (kliknite na desni strani vrstice s formulo), v vrstici stanja pa se prikaže beseda »Uredi«. V tem načinu so na voljo vsa orodja za urejanje.

Urejanje na ravni celic, obsegov, vrstic, stolpcev. To sta predvsem ukaza za urejanje menija Uredi in Vstavi.

Oblikovanje.

Vsi ukazi za oblikovanje podatkov, vrstic, stolpcev, listov itd. so skoncentrirani v meniju »Oblika«.

Diagrami vExcel.

Diagram vključuje številne objekte, od katerih je mogoče vsakega posebej izbrati in spremeniti (urediti in formatirati). Ko premaknete kazalec miške po diagramu, se poleg njega prikaže opis orodja, ki označuje vrsto predmeta, v bližini katerega je kazalec.

Objekti grafikona .os(X je os kategorij, Y je os vrednosti). Podatkovna točka– en podatkovni element, na primer plača za januar. Serije podatkov– niz podatkovnih točk (jasno vidne na grafu – vse točke niza podatkov so povezane z eno črto). Legenda– ikone, vzorci, barve, ki se uporabljajo za razlikovanje nizov podatkov. Označevalnik podatkov– predstavlja podatkovno točko na grafikonu v obliki pravokotnika, sektorja, točke itd., tip markerja je odvisen od tipa grafikona; Vsi označevalci v eni seriji podatkov imajo enako obliko in barvo. Besedilo– vse oznake (naslov grafikona, vrednosti in kategorije na oseh) in napisi (test, povezan s podatkovnimi točkami); za podpise lahko uporabite ikono »napis« na plošči za risanje ali ustvarite lebdeči besedilo : kliknite eno od nizov podatkov - vnesite test (pojavil se bo v vrstici s formulo) - pritisnite "Enter".

Pravila , uporabljal Excel privzeto pri konstruiranju diagramov.

1. Excel predpostavlja, da niz podatkov za grafikon poteka vzdolž dolge stranice izbranega obsega celic.

2. Če je izbran kvadratni obseg celic ali zavzema več celic v širino kot v višino, se bodo imena kategorij nahajala v zgornji vrstici obsega. Če je celic več v višino kot v širino, gredo imena kategorij navzdol v levi stolpec. In če celice, ki jih bo Excel uporabil kot imena kategorij, vsebujejo številke (ne besedila ali datumov), potem Excel predpostavlja, da te celice vsebujejo vrsto podatkov in oštevilči imena kategorij kot 1, 2, 3, 4 itd.

3. Excel predpostavlja, da je treba naslove vzdolž kratke strani izbora uporabiti kot oznake legende za vsako podatkovno serijo. Če obstaja samo ena serija podatkov, potem Excel to ime uporabi kot naslov grafikona. In če celice, ki jih Excel namerava uporabiti kot oznake legende, vsebujejo številke (ne besedila ali datumov), potem Excel predpostavlja, da te celice vsebujejo prve točke niza podatkov, in vsakemu nizu podatkov dodeli ime: "Series1", "Series2" , itd. d.

Makri. Uporablja se za avtomatizacijo ponavljajočih se operacij v Excelu. Makro je sestavljen iz zaporedja notranjih Excelovih ukazov (makro ukaz). V Excelu se makro ustvari z ukazom “Orodja / Makro / Začni snemanje”. Ta ukaz vam omogoča, da ustvarite makro z uporabo snemalnika makrov (način snemanja programa). Vzporedno z dejanji uporabnika snemalnik makrov beleži dejanja uporabnika in jih samodejno prevede v lasten jezik makra. Na ta način lahko ustvarite razmeroma preproste programe, ki delujejo brez posredovanja uporabnika.

Primer: ustvarite makro z uporabo snemalnika makrov, ki gradi diagram dinamike plač za Ivanova A.P. po mesecih. Za to potrebujete:

Naloge za laboratorijsko delo št. 1.

Ustvarite preglednico po navodilih učitelja.

Na podlagi te tabele sestavite dva diagrama:

histogram z eno osjo Y;
diagram z glavno in pomožno osjo Y, ki predstavlja dve seriji podatkov v obliki grafov.

Sestavite mešani grafikon, v katerem je ena serija podatkov predstavljena kot histogram, druga serija podatkov pa je predstavljena kot graf. V urejevalniku Word nastavite serijo podatkov, shranite datoteko s pripono .txt, nato pa to datoteko uvozite iz Excela. Podatke posreduje učitelj.

Ustvarite makro (po navodilih učitelja).

Avtorske pravice JSC "CDB "BIBKOM" & LLC "Agency Book-Service" Zvezna agencija za izobraževanje Državna izobraževalna ustanova visokega strokovnega izobraževanja "Kazanska državna tehnološka univerza" Laboratorijsko delo v računalništvu MS EXCEL Smernice Kazan 2006 Copyright JSC "CDB "BIBKOM" & LLC "Agencija Book-Service" UDK 658.26:66.094 Sestavil: izr. E.S. Vorobiev, izredni profesor E.V. Nikolaeva, izredna profesorica F.I. Vorobyova Laboratorij za računalništvo. MS Excel: Metoda. navodila / Kazan. država tehn. univerza; Comp.: E.S. Vorobyov, E.V. Nikolaeva, F.I. Vorobyova. – Kazan, 2006. – 58 str. Orisane so osnovne tehnike dela v paketu MS Excel, opisani so postopek in pravila ustvarjanja in urejanja. preglednice in diagramov, izvajanje osnovnih izračunov, razvrščanje in filtriranje podatkov, analiziranje in povzemanje podatkov, kot tudi uporaba logičnih izrazov, sumarniških in distribucijskih funkcij ter matričnih operacij. Ločeno laboratorijsko delo je namenjeno iskanju rešitve eno- in dvoparametrskega problema. Lahko se uporablja pri študiju disciplin "Računalništvo", "Uporaba računalnikov v tehnologiji" in "Uporaba računalnikov v izračunih", lahko služi kot vodnik za izvenšolsko delo študentov in ga lahko uporabljajo tudi strokovnjaki katerega koli predmetno področje za samostojni razvoj računalniških tehnologij. Zasnovan za polni delovni čas in dopisni obrazci specialnosti za usposabljanje 240802.65 "Osnovni procesi kemijske proizvodnje in kemijska kibernetika" in 240801 "Stroji in aparati za kemijsko proizvodnjo", študenti v smeri 2480800 "Procesi za varčevanje z energijo in viri v kemijska tehnologija, petrokemija in biotehnologija«. Il. 68, tab. 1, bibliogr. 5 naslovov Pripravljeno na Katedri za splošno kemijsko tehnologijo. Objavljeno s sklepom uredniškega in založniškega sveta Kazanske državne tehnološke univerze. Recenzenti: B.K. Kurbatov, izredni profesor KSTU poimenovan po. NA. Tupoleva E.A. Mukhutdinov, izredni profesor KSU Kazanska državna tehnološka univerza, 2006 Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency Laboratorijsko delo št. 1 Vaja 1 Osnovni koncepti, povezani z delovanjem elektronskih Excel tabele 1. Zagon Microsoftov program Excel: kliknite na gumb Start; v meniju, ki se prikaže, izberite Programi; V pojavnem meniju izberite Microsoft Excel. 2. Pozorno preglejte okno programa Microsoft Excel (slika 1). Številni vodoravni elementi menija in gumbi orodne vrstice so enaki elementom menija in gumbom v oknu urejevalnika Word. Vendar ima delovni prostor popolnoma drugačen videz, ki je označena tabela, sestavljena iz celic enake velikosti. Ena od celic je jasno poudarjena (uokvirjena s črnim okvirjem - kazalec tabele). Kako izbrati drugo celico? Če želite to narediti, samo kliknite nanj z miško, s kazalcem miške na tej sliki. Prvič mora izgledati kot lahek križ. Poskusite označiti različne celice tabele. Za premikanje po mizi uporabite drsne trakove. 3. Če želite vnesti besedilo v eno od celic tabele, jo morate izbrati in takoj (brez čakanja na kazalec besedila v urejevalniku besedil) "napisati". Izberite eno od celic tabele in vanjo "vpišite" ime današnjega dneva v tednu. 4. Glavna razlika med delom preglednic in urejevalnikom besedil je v tem, da je treba podatke po vnosu v celico zabeležiti, tj. sporočite programu, da ste končali z vnosom informacij -3- Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency v to posebno celico. Podatke lahko beležite na enega od naslednjih načinov: pritisnite tipko (Enter); kliknite drugo celico; uporabite gumbe za upravljanje kazalca na tipkovnici (puščice). Zabeležite podatke, ki ste jih vnesli. 5. Izberite celico tabele, ki vsebuje dan v tednu, in uporabite gumbe za poravnavo odstavkov. Kako pride do poravnave? Potegnite zaključek. Po vseh poskusih se prepričajte, da vrnete prvotno poravnavo na levo, to bo pomembno v prihodnosti. 6. Opazili ste že, da je tabela sestavljena iz stolpcev in vrstic, pri čemer ima vsak stolpec svoj naslov (A, B, C...), vse vrstice pa so oštevilčene (1, 2, 3...) (slika 1). Če želite izbrati celoten stolpec, samo kliknite njegovo glavo; če želite izbrati celotno vrstico, morate klikniti njeno glavo. Izberite celoten stolpec tabele, v katerem se nahaja ime dneva v tednu, ki ste ga vnesli. Kakšen je naslov te kolumne? Izberite celotno vrstico tabele, ki vsebuje ime dneva v tednu. Kakšen naslov ima ta vrstica? Z drsnimi trakovi določite, koliko vrstic ima tabela in kakšno je ime zadnjega stolpca. 7. Izberite celico tabele, ki se nahaja v stolpcu C in vrstici 4. Upoštevajte, da se v polju »Ime« (slika 1), ki se nahaja nad naslovom stolpca A, pojavi naslov izbrane celice C4. Izberite drugo celico in videli boste, da se je naslov v polju Ime spremenil. Kakšen je naslov celice, ki vsebuje dan v tednu? 8. Predstavljajmo si, da morate v celico, ki vsebuje dan v tednu, dodati tudi del dneva. Izberite celico, ki vsebuje dan v tednu, s tipkovnico vnesite ime trenutnega dela dneva, na primer "jutro", in zabeležite podatke s pritiskom na tipko Enter. Kaj se je zgodilo? Del dneva ni bil »dodan« v celico, ampak so novi podatki nadomestili prvotne podatke in namesto dneva v tednu ste prejeli del dneva. To pomeni, da če izberete celico tabele, ki vsebuje nekaj podatkov, in s tipkovnico vnesete nove podatke, se bodo v celici tabele pojavile najnovejše informacije. Kako lahko dodate (uredite) vsebino celice tabele, ne da bi znova vtipkali vse podatke? Če izberete celico, ki vsebuje del dneva, boste videli, da je njena vsebina podvojena v "Vrstici s formulo", ki se nahaja nad naslovi stolpcev (slika 1). V vrstici »Formula Bar« lahko kliknete običajni besedilni kazalec, naredite vse zahtevane spremembe in nato potrdite končno različico podatkov. Izberite celico tabele, ki vsebuje del dneva, postavite kazalec besedila pred besedilo v vrstici s formulo in ponovno vnesite dan v tednu. Zabeležite podatke. Morali bi dobiti naslednjo sliko (slika 2). -4- Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency 9. Vidi se, da je zapis presegel meje svoje celice in zasedel del torkovega jutra sosednje. To se zgodi le, ko je sosednja celica prazna. Dajmo ji Fig. 2 izpolnite in preverite, kaj se je spremenilo. Izberite celico tabele, ki se nahaja desno od celice, ki vsebuje vaše podatke (celica, v katero so se "zapeljali"), in vanjo vnesite poljubno besedilo. Zdaj je viden samo tisti del vaših podatkov, ki se prilega celici (slika torek, petek 3). Kako si lahko ogledam celoten posnetek? In sl. 3 spet vam bo na pomoč priskočila "Formula Bar". V njej si lahko ogledate celotno vsebino izbrane celice. Torej, "Vrstica s formulo" vam omogoča, da: spreminjate vsebino izbrane celice; ogled vsebine celice, če celoten vnos ni viden. Izberite celico, ki vsebuje dan v tednu in del dneva, in si oglejte celotno vsebino celice v vrstici s formulami. 10.Kako lahko povečam širino stolpca, da bosta v celici vidna dan v tednu in del dneva hkrati? To storite tako, da kazalec miške premaknete na desno obrobo glave stolpca, "ujamete" trenutek, ko se kazalec miške spremeni v črno dvojno puščico in, medtem ko držite levi gumb miške, premaknete obrobo stolpca v desno. Kolona se je razširila. Podobno lahko spremenite višino črte. V tem primeru, ko se premakne na spodnji rob glave vrstice, ima kazalec obliko: Spremenite širino stolpca, ki vsebuje dan v tednu in del dneva, tako da bo celotno vneseno besedilo vidno v celici tabele. 11. Pogosto ni treba izbrati ene celice ali celotnega stolpca, temveč blok celic (več celic, ki se nahajajo v bližini). 12. To storite tako, da kazalec miške postavite v skrajno zunanjo celico izbora in med držanjem leve tipke premaknete miško na nasprotni rob izbora (celoten izbrani blok je »pokrit« z okvir, so vse celice razen tiste, iz katere se začne izbor, obarvane črno) . Upoštevajte, da med postopkom izbire »Polje z imenom« beleži število vrstic in stolpcev, ki spadajo v izbor. V istem trenutku, ko spustite levo tipko, se v “Imenskem polju” prikaže naslov celice, iz katere ste začeli z izbiro. Izberite blok celic, ki se začne s celico A1 in konča s celico, ki vsebuje "petek". Če želite izbrati celotno tabelo, uporabite kotni gumb "prazno", ki se nahaja nad glavo prve vrstice. -5- Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency Izberite celotno tabelo. Prekličite izbiro s klikom na katero koli celico. 13. Kako izbrišem vsebino celice? To storite tako, da izberete celico (ali blok celic) in pritisnete tipko (Delete) ali uporabite vodoravni menijski ukaz “Uredi” ⇒ “Počisti”. Izbrišite vse svoje vnose. 2. vaja Uporabite osnovne tehnike preglednic: vnašanje podatkov v celico. Oblikovanje pisave. Spreminjanje širine stolpca. Samodokončanje, vnos formule, uokvirjanje tabele, poravnava besedila na sredino izbire, nastavitev indeksov in zgornjih indeksov. Ustvarimo tabelo, ki izračuna n-ti člen in vsoto aritmetične progresije. Za začetek si oglejmo formulo za n-ti člen aritmetičnega napredovanja: an = a1 + d (n − 1) in formulo za vsoto prvih n členov aritmetičnega napredovanja: n S n = (a1 + an) ⋅ , 2 kjer je a1 prvi člen progresije, d – razlika aritmetične progresije. Na sl. Slika 4 prikazuje tabelo za izračun n-tega člena in vsote aritmetične progresije, katere prvi člen je enak -2, razlika pa je enaka 0,725. riž. 4 Pred izvedbo vaje si omislite svojo aritmetično progresijo, tj. To pomeni, da določite svoj prvi člen napredovanja in razlike. Vajo lahko razdelimo na naslednje korake: izberemo celico A1 in vanjo vnesemo naslov tabele »Izračun n-tega člena in vsote aritmetične progresije«. Naslov bo postavljen v eno vrstico in bo zasedel več celic desno od A1; V celico A2 vnesite "d", v celico B3 - "n", v C3 - "an", v D3 - "Sn". Če želite nastaviti indekse, najprej vnesite vse besedilo, ki naj bo v celici (na primer an), nato pojdite na »Vrstica s formulo«, izberite besedilo, ki naj bo indeks (na primer n), odprite ukaz » Format” ⇒ “Celice” ...” (v pogovornem oknu, ki se odpre, je samo en zavihek “Pisava”) in aktivirajte stikalo “podnaslov” v skupini “Spreminjanje”; Izračun n-tega člena in vsote aritmetične progresije d n an Sn 0,725 1 -2 -2 0,725 2 -1,275 -3,275 0,725 3 -0,55 -3,825 0,725 4 0,175 -3,65 0,725 5 0,9 -2,75 0,725 6 1,625 -1 ,125 0,725 7 2,35 1,225 0,725 8 3,075 4,3 0,725 9 3,8 8,1 0,725 10 4,525 12,625 -6- Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency Izberite izpolnjene štiri celice. Z ustreznimi gumbi v orodni vrstici povečajte velikost pisave za 1 točko, jo poravnajte na sredino in uporabite krepko pisavo. Glava tabele je oblikovana. Zdaj lahko začnete izpolnjevati tabelo. 1. V celico A3 vpišemo vrednost razlike aritmetične progresije (v našem primeru je 0,725). 2. Nato morate zapolniti vrsto spodnjih celic z isto številko. Vnašanje iste številke v vsako celico je nezanimivo in neracionalno. V urejevalniku Word smo uporabili tehniko kopiraj-prilepi. Excel omogoča še enostavnejše polnjenje celic z istimi podatki. Izberite celico A3, ki vsebuje razliko aritmetične progresije. Izbrana celica je obdana z okvirjem, v spodnjem desnem kotu katerega je majhen črn kvadrat - polnilni marker. Če kazalec miške premaknete na oznako polnila in v trenutku, ko kazalec miške prevzame obliko črnega križa, povlecite oznako polnila navzdol za več celic (hkrati se desno od kazalca prikaže namig, katera vrednost je vnesena v trenutno celico), bo celotna vrstica izbranih celic zapolnjena s podatki, ki se nahajajo v prvi celici. Tako zapolnimo še devet celic pod celico A3 z vrednostjo razlike aritmetične progresije. 3. Naslednji stolpec vsebuje zaporedje številk od 1 do 10. In spet nam bo oznaka za polnjenje pomagala izpolniti vrstico. V celico B3 vnesite številko 1, v celico B4 številko 2, izberite obe celici in zgrabite oznako za polnjenje ter jo povlecite navzdol. Razlika od polnjenja z enakimi podatki je v tem, da ste z izbiro dveh celic navedli, po katerem principu naj se polnijo preostale celice. Označevalnik polnila lahko »vlečete« ne le navzdol, ampak tudi navzgor, levo ali desno, polnilo pa se bo širilo v enakih smereh. Element za polnjenje je lahko ne le formula ali številka, ampak tudi besedilo. V celico lahko vnesete »januar« in tako, da izpolnite vrstico bolj desno, dobite »februar«, »marec«, z »raztezanjem« oznake za polnjenje iz celice »januar« na levo pa bo v skladu s tem dobil »december«, »november« itd. Poskusite to zunaj tabele, ki jo ustvarjate. Najpomembnejša stvar je, da pred širjenjem izbora izberete natančno celico (ali celice), na kateri je polnilo oblikovano. 4. Tretji stolpec vsebuje n-ti izrazi napredovanje. Vnesite vrednost prvega člena aritmetične progresije v celico C3. V celico C4 morate postaviti formulo za izračun n-tega člena progresije, ki je sestavljena iz dejstva, da se vsaka celica stolpca razlikuje od prejšnje z dodajanjem razlike aritmetičnega napredovanja. Vse formule se začnejo z znakom enačaja. Za vnos formule v celico morate: aktivirati celico; -7- Copyright JSC "CDB "BIBKOM" & LLC "Agency Kniga-Service" vnesite znak enako "=" s tipkovnice ali kliknite na gumb "Spremeni formulo" v vrstici s formulami; vnesite (brez presledkov) potrebne vrednosti ali reference ter potrebne operaterje; potrdi vnos. Naslov celice se v formule vnese v latinici. Če je bil vnos opravljen v ruskem registru, se prikaže sporočilo o napaki »#NAME?«. Izberite celico C4 in vanjo vnesite formulo =C3+A4 (ne pozabite preklopiti na latinico in namesto sklicevanja na celico A4 lahko vnesete določeno vrednost razlike vaše aritmetične progresije). Ni vam treba vnesti naslova celice, na katero se sklicujete, s tipkovnice. Ko vnesete enačaj, kliknite celico C4 in njen naslov se bo prikazal v vrstici s formulo, nato pa nadaljujte s tipkanjem formule. V tem primeru vam ni treba preklopiti na latinico. Ko v celoti vnesete formulo, jo popravite s pritiskom na (Enter), rezultat izračuna se prikaže v celici, sama formula pa se prikaže v vrstici s formulo. Tukaj je še ena funkcija »Vrstice s formulo«: če v celici vidite rezultat izračunov s formulo, si lahko samo formulo ogledate v »Vrstici s formulo« tako, da izberete ustrezno celico. Če ste formulo vnesli napačno, jo lahko popravite v vrstici s formulo tako, da najprej izberete celico. Izberite celico C4 in, podobno kot pri polnjenju celic z razlikami v napredovanju, napolnite s formulo tako, da "povlečete" oznako za polnjenje navzdol po vrsti celic pod C4. Izberite celico C8 in poglejte v »Vrstico s formulo«, da vidite, kako izgleda formula, izgleda kot =C7+A8. Opazno je, da so se sklicevanja v formuli spremenila glede na odmik same formule. 5. Podobno vnesite formulo =(-2+С3)*B3/2 v celico D3, da izračunate vsoto prvih n členov aritmetične progresije, kjer naj bo namesto -2 prvi člen vaše izmišljene aritmetike napredovanje. Izberite celico D3 in napolnite spodnje celice s formulami tako, da povlečete ročico za polnjenje navzdol. 6. Sedaj so vse celice napolnjene s podatki, ostane le še, da jih formatiramo. Vsi stolpci so enako široki, vendar vsebujejo različne količine informacij. Širino posameznih stolpcev lahko spreminjate ročno (z miško) ali pa širino prilagodite samodejno. To storite tako, da izberete vse celice tabele, ki vsebujejo podatke (ne celih stolpcev, ampak samo blok izpolnjenih celic brez naslova »Izračun n-tega člena in vsote aritmetične progresije«) in izvedete ukaz »Oblika« ⇒ »Stolpec ” ⇒ “Širina samodejnega prilagajanja”. 7. Zdaj pa oblikujmo naslov tabele »Izračun n-tega člena in vsote aritmetične progresije.« Izberite celico A1 in na vsebino celice uporabite krepke znake. Naslov precej neestetsko »štrli« na desno stran izven meja našega malega znaka. -8- Copyright JSC "CDB "BIBKOM" & LLC "Agency Kniga-Service" Izberite štiri celice od A1 do D1 in izvedite ukaz "Format" ⇒ "Cells...", izberite zavihek "Alignment" in nastavite stikala (slika 6): skupina “Poravnava” ⇒ “vodoravno:” na položaj “središče izbire”; skupina “Prikaz” ⇒ “Prelom besed”. To bo omogočilo, da bo glava postavljena v več vrstic in na sredini izbranega bloka celic. Tabela se je skoraj skrčila na 8. tip vzorca. Če v tem trenutku pogledate »Datoteka« ⇒ »Predogled«, boste ugotovili, da vam preostane le okvirjanje tabele. Če želite to narediti, izberite tabelo (brez glave) in izvedite ukaz “Oblika” ⇒ “Celice ...”. V pogovornem oknu, ki se odpre, izberemo zavihek »Border«, določimo vrsto črte in aktiviramo stikala »Top«, »Bottom«, »Left«, »Right« (slika 5). Ta postopek velja za vsako od celic izbranega območja. Nato izberite blok celic, povezanih z glavo: od A1 do D2 in po enakih operacijah nastavite stikalo »Zunanje«. V tem primeru prejmete okvir okoli vseh izbranih celic in ne vsake posebej. Izvedite predogled. riž. 5 sl. 6 -9- Copyright JSC "CDB "BIBKOM" & LLC "Agency Kniga-Service" Laboratorijsko delo št. 2 Vaja 1 Utrjevanje osnovnih veščin pri delu s preglednicami, seznanitev s koncepti: razvrščanje podatkov, vrste poravnave besedila v celici , oblika številke Pošiljatelj in njegov naslov Prejemnik in njegov naslov Za registrsko št. Datum prejema “___”___________200__ RAČUN št. 123 z dne 15. novembra 2000 Dobavitelj Trgovska hiša “Horns and Hooves” Naslov 243100, Klintsy, st. Puškina, 23 Račun št. 45638078 v banki MMM, MFO 985435 Dodatki: Št. Ime Merska enota 1 2 3 4 5 6 SKUPAJ Vodja podjetja Količina Cena Znesek Sidorkin A.Yu. Glavni računovodja Ivanova A.N. Vaja vključuje izdelavo in izpolnjevanje obrazca računa za izdelek. Najbolje je, da vajo razdelite na tri stopnje: 1. stopnja – izdelava tabele obrazca računa; 2. stopnja – izpolnjevanje tabele; 3. stopnja – izpolnjevanje obrazca. 1. stopnja je sestavljena iz ustvarjanja tabele. Glavna naloga je prilagoditi mizo širini lista. To storite tako: najprej nastavite robove, velikost papirja in orientacijo (»Datoteka« ⇒ »Nastavitve strani ...«); - 10 - Copyright JSC "CDB "BIBKOM" & LLC "Agency Kniga-Service" z izvedbo ukaza "Service" ⇒ na "Možnosti...", zavihek "Pogled" v skupini stikal Parametri okna, aktivirajte stikalo " Samodejno paginiranje" (slika 7) Posledično boste prejeli desno obrobo številčne vrstice v obliki navpične pikčaste črte (če ni vidna, se premaknite z vodoravnim drsnim trakom v desno) in spodnji meja številčnice v obliki vodoravne pikčaste črte (da vidite, da se premika z navpični trak pomaknite navzdol). Samodejno ostranjevanje vam omogoča, da med postopkom zbiranja podatkov in oblikovanja tabele spremljate, kateri stolpci ustrezajo strani in kateri ne. Št. 1 2 3 4 5 6 Naziv Enota Količina Cena Znesek SKUPAJ Sl. 8 Ustvarite tabelo po predlaganem modelu z enakim številom vrstic in stolpcev (slika 8). Poravnajte in formatirajte pisavo v celicah glave, izberite širino stolpcev in jo spremenite z miško. Vnesite številko v prvi stolpec tabele z oznako za polnjenje. Obrobite mizo s črtami različnih debelin. Upoštevajte, da v zadnji vrstici pet sosednjih celic nima notranje meje. To najlažje dosežemo na naslednji način: označimo celotno tabelo in postavimo okvir – »Zunanji« s krepko črto; - 11 - Copyright JSC "CDB "BIBKOM" & LLC "Agency Kniga-Service" nato izberite vse vrstice razen zadnje in nastavite okvir s tanko črto "Desno", "Levo", "Zgoraj", "Spodaj" ; po tem ločeno izberite skrajno desno celico spodnje vrstice in s tanko črto nastavite okvir »Levo«; Vse kar ostane je, da izberete prvo vrstico tabele in ji s krepko črto nastavite okvir »Spodaj«. Čeprav lahko storite nasprotno. Najprej "obrobite" celotno mizo in nato odstranite dodatne okvirne črte. Na tej stopnji je priporočljivo zagnati ukaz “Datoteka” ⇒ “Predogled”, da se prepričate, da se celotna tabela prilega listu po širini in da so vse okvirne črte na pravem mestu. 2. stopnja: izpolnjevanje tabele, razvrščanje podatkov in uporaba različnih formatov številk. Po želji izpolnite stolpce »Ime«, »Količina« in »Cena«. Nastavite obliko valute števila v celicah, v katere bodo vneseni zneski, in nastavite potrebno število decimalnih mest, če obstajajo. V našem primeru so to celice stolpcev »Cena« in Sl. 9 "Znesek". Izbrati jih morate in izvesti ukaz “Oblikuj” ⇒ “Celice...”, izberite zavihek “Številka” in izberite kategorijo “Denar” (slika 9). Tako boste razdeljeni na tisoče, da boste lažje krmarili po velikih zneskih. Vnesite formulo za izračun zneska, ki je sestavljen iz množenja cene s količino, in izpolnite vrstico celic navzdol s formulo. Vnesite formulo v celico za vsoto. Če želite to narediti, izberite blok celic, ki jih je treba dodati, in eno prazno celico pod tem blokom, v katero želite postaviti rezultat. Po tem kliknite gumb orodne vrstice. Poskusite spremeniti podatke v posameznih celicah in si oglejte, kako se spremeni rezultat izračuna. Vnose razvrstite po abecedi. Če želite to narediti, izberite vse vrstice tabele, razen prve (naslov) in zadnje (»Skupaj«), ne morete izbrati oštevilčenja. Izvedite ukaz “Podatki” ⇒ “Razvrščanje...” (slika 10), izberite stolpec, po katerem želite sortirati podatke (v našem primeru je to stolpec B, saj je v njem seznam blaga, ki ga želite sortirati). ) in nastavite stikalo v položaj "Naraščajoče". 3. stopnja fakturiranja, vstavite dodatne vrstice pred tabelo. Če želite to narediti, izberite prvih nekaj vrstic tabele in izvedite ukaz “Vstavi” ⇒ “Vrstice”. riž. 10 Vstavljeno bo enako število vrstic, kot ste jih izbrali. Vnesite zahtevano besedilo pred in za tabelo. Pazi na poravnavo. Upoštevajte, da je besedilo "Datum prejema "__"_______200_." in imena direktorjev podjetja so vnesena v isti stolpec, v katerem se nahaja stolpec tabele »Znesek« (skrajni desni stolpec naše tabele), uporabljena je le desna poravnava. V celico skrajno levega stolpca se vnese besedilo “RAČUN Št...” in poravnava se izvede na sredino izbora (celice ene vrstice so vnaprej izbrane po celotni širini tabele faktur). Za te celice je bila na vrhu in na dnu uporabljena obroba. Vse ostale besedilne informacije pred in za tabelo se vnesejo v skrajni levi stolpec, poravnano levo. Izvedite predogled. Vaja 2 Predstavitev koncepta "absolutne povezave", nastavitev natančne vrednosti širine stolpca z ukazi vodoravnega menija. Vstavljanje funkcije s pomočjo čarovnika za funkcije Novi koncept "absolutne reference" je mogoče preučiti na posebnem primeru. Pripravimo tradicionalno tabelo kvadratov dvomestnih števil (slika 11), tako znano vsem iz tečaja algebre. V celico A3 vnesite številko 1, v celico A4 - številko 2, izberite obe celici in povlecite izbirno oznako navzdol, da zapolnite stolpec s številkami od 1 do 9. Podobno izpolnite celice B2 - K2 s številkami od 0 do 9 Ko napolnite vrstico s številkami od 0 do 9, potem vse celice, ki jih potrebujete za delo, niso vidne na zaslonu hkrati. Zožimo jih, vendar tako, da imajo vsi stolpci enako širino (česar ne moremo doseči s spreminjanjem širine stolpcev z miško). Če želite to narediti, izberite stolpce od A do K in izvedite ukaz “Format” ⇒ - 13 - Copyright JSC “CDB “BIBKOM” & LLC “Agency Kniga-Service” “Column” ⇒ “Width...”, v vnosu polje »Širina stolpca« » vnesite vrednost, na primer 5. Seveda vsi razumejo, da morate v celico B3 postaviti formulo, ki kvadrira število, sestavljeno iz desetic, navedenih v stolpcu A, in enot, ki ustrezajo postavljeni vrednosti v 2. vrstici. Tako lahko samo število, ki ga je treba kvadrirati v celici B3, določite s formulo A3*10+B2 (število desetic, pomnoženo z desetico, plus število enot). Vse, kar ostane, je kvadriranje tega števila. 1 2 3 4 5 6 7 8 9 0 100 400 900 1600 2500 3600 4900 6400 8100 1 121 441 961 1681 2601 3721 5041 6561 8281 KVADRATNA TABELA 2 3 4 5 144 169 196 225 484 529 576 625 1024 1089 1156 1225 1764 1849 1936 2025 2704 2809 2916 3025 3844 3969 4096 4225 5184 5329 5476 5625 6724 6889 7056 7225 8464 8649 8836 9025 6 256 676 1 296 2 116 3136 4356 5776 7396 9216 7 289 729 1369 2209 3249 4489 5929 7569 9409 8 324 784 1444 2304 3364 4624 6084 7744 9604 9 361 841 1521 2401 3481 4761 6241 7921 9801 Sl. 11 Poskusimo uporabiti “Čarovnika za funkcije”. To naredimo tako, da izberemo celico, v katero naj bo rezultat izračuna (VZ) in izvedemo ukaz “Vstavi” ⇒ “Funkcija...” (slika 12). Pogovorno okno »Čarovnik za funkcije (korak 1 od 2)« (slika 12) ima dve podokni: »Kategorija« in »Funkcija«. Ko izberete določeno funkcijo, se na dnu pogovornega okna prikaže njen kratek opis. Med predlaganimi sl. 12 kategorij funkcij, izberite “Matematične”, med “Funkcije” - “Stopnja”, pritisnite gumb Ok. V naslednjem pogovornem oknu (slika 13) vnesite v polje »Število« (osnova stopnje) – A3*10+B2 in v polje »Eksponent« – 2. Tako kot pri vnašanju formule neposredno v celico preglednice, ni potrebe po vnosu naslova vsake celice, na katero se sklicuje formula, s tipkovnico. V - 14 - Copyright JSC "CDB "BIBKOM" & LLC "Agency Kniga-Service" v pogovornem oknu drugega koraka "Čarovnika za funkcije" samo pokažite z miško na ustrezno celico preglednice in njen naslov se bo prikazal prikažejo v vnosnem polju "Številka" pogovornega okna. Vnesti boste morali samo aritmetično sliko. 13 znakov (*, +) in številko 10. V primerih, ko morate za vnos argumentov izbrati celice, ki jih pokriva okno, se desno od vsakega polja za vnos argumentov nahajajo gumbi, s katerimi lahko strnete in razširite pogovorno okno . Poleg tega lahko okno čarovnika za funkcije premaknete na stran tako, da z miško »zgrabite« naslovno vrstico. V istem pogovornem oknu (slika 13) si lahko ogledate vrednost samega števila (10) in rezultat izračuna stopnje (100). Vse kar ostane je, da kliknete gumb V redu. Rezultat izračunov se pojavi v celici B3. To formulo bi rad razširil na preostale celice tabele. Izberite celico B3 in zapolnite sosednje celice tako, da povlečete ročico za polnjenje v desno. Kaj se je zgodilo (sl. 14)? riž. 14 Zakaj rezultat ni izpolnil naših pričakovanj? V celici C3 številka ni vidna, ker se ne prilega v celoti celici. Z miško razširite stolpec C. Številka se prikaže na zaslonu, vendar očitno ne ustreza kvadratu števila 11 (slika 15). ). riž. 15 Zakaj? Dejstvo je, da ko smo formulo razširili v desno, je Excel samodejno spremenil naslove celic, pri čemer je upošteval naš odmik, na katerega se formula nanaša, in v celici C3 ni na kvadrat številka 11, ampak število, izračunano po formuli B3 * 10 + C2. Pri vseh prejšnjih vajah smo bili kar zadovoljni z relativnimi povezavami na celice tabele (pri premikanju formule se tudi povezave premikajo po istem zakonu), tu pa je bilo treba popraviti določene povezave, tj. navedite, da je število desetic mogoče vzeti samo iz stolpca A, število enot pa samo iz vrstice 2 (tako da je formula lahko - 15 - Copyright OJSC Central Design Bureau BIBKOM & LLC Agency Book-Service podaljšana navzdol). Za to ima Excel možnost nastavitve absolutnih in mešanih povezav. Absolutna referenca je referenca, ki se ne spremeni, ko se formule kopirajo. To storite tako, da pred ime stolpca in številko vrstice dodate znak za dolar $ (bodisi vnesite s tipkovnico bodisi po vnosu naslova celice pritisnite funkcijsko tipko F4). Mešane povezave so povezave, ki so le delno absolutne, tj. Stolpec ali vrstica sta fiksna. V tem primeru se znak za dolar $ postavi pred črko, če je stolpec fiksen, ali pred številko, če je vrstica fiksirana. Znak za dolar $ vnesemo s tipkovnice ali pa po vnosu naslova celice pritisnemo funkcijsko tipko F4, dokler ni znak $ na želenem mestu. Ko kopirate formulo, ki vsebuje mešan sklic, se spremeni le relativni del sklica. Vrnite širino stolpca C na prvotni položaj in izvedite naslednje korake: Izberite celico B3 in tako, da postavite kazalec besedila v vrstico s formulo, popravite obstoječo formulo =DEGREE(A3*10+B2;2) na pravilno =DEGREE($A3*10+ v $2,2). Zdaj lahko s storitvami polnilnega označevalca zapolnite vse proste celice tabele s to formulo (najprej povlecite polnilno oznako v desno, nato pa navzdol, ne da bi odstranili izbor iz nastalega bloka celic). Za vnos referenc celic za podatke stolpca A in vrstice 2 smo uporabili mešane sklice. Absolutno povezavo v našem primeru bi lahko uporabili, če v formulo ne bi vnesli števila 10, s katerim se množijo števila v stolpcu A, ampak naslov celice, na primer A15 (kamor bi vnesli to število 10). V tem primeru bi formulo v celici B3 zapisali kot: =POWER($A3*$A$15+B$2) in jo nato tudi kopirali v preostale celice. Poskusite to. Preostane le še oblikovanje tabele: celici A1 vnesemo naslov, jo formatiramo in centriramo glede na izbor, tabelo uokvirimo in zapolnimo ozadje posameznih celic. Vaja 3 Uvod v koncept "ime celice" Predstavljajte si, da imate svoje podjetje, ki prodaja kateri koli izdelek in vsak dan morate natisniti cenik s cenami blaga glede na tečaj dolarja. Pripravite tabelo, sestavljeno iz stolpcev: "Ime izdelka"; "ekvivalent v ameriških dolarjih"; "Cena v rubljih." - 16 - Copyright JSC "CDB "BIBKOM" & LLC "Agency Kniga-Service" Izpolnite vse stolpce, smetana "Cena v rubljih." Izpolnite stolpec »Ime izdelka« z besedilnimi podatki (seznam izdelkov po vaši presoji) in stolpec »Protivrednost v ameriških dolarjih« s številkami (cene v $). Jasno je, da v stolpcu "Cena v rubljih" Formula bi morala biti: »Ekvivalent $ US« * »Menjalni tečaj dolarja«. Zakaj je v tej formuli neprijetno pomnožiti z določeno vrednostjo menjalnega tečaja? Da, ker vsakič, ko se stopnja spremeni, boste morali spremeniti svojo formulo v vsaki celici. Lažje ga je pripisati vrednosti menjalnega tečaja dolarja ločena celica , na katerega se sklicuje formula. Jasno je, da mora biti povezava absolutna, tj. vrednost menjalnega tečaja dolarja je mogoče vzeti samo iz te posebne celice s fiksnim naslovom. O tem, kako nastaviti absolutne povezave, smo razpravljali zgoraj, vendar obstaja še en priročen način: ne nanašajte se na naslov celice, temveč na ime, ki ga lahko dodelite celici. Ko poimenujete celico ali obseg celic, lahko do te celice ali obsega dostopate kadar koli in od koder koli v tabeli, tudi če spremeni lokacijo ali je na različnih listih. Izberite celico, v katero bo vpisan tečaj dolarja (nad tabelo), vanjo vnesite vrednost današnjega tečaja dolarja in izvedite ukaz “Vstavi” ⇒ “Ime” ⇒ “Dodeli...”. V pogovornem oknu, ki se odpre (slika 16), lahko vnesete poljubno ime in v polju »Formula« izberete obseg, za katerega se to ime vnese. Ime je lahko dolgo do 255 znakov in vsebuje črke, številke, podčrtaje (_), poševnice nazaj (\), pike in vprašaje. Vendar mora biti prvi znak črka, podčrtaj (_) ali poševnica nazaj (\). Imena, ki se razlagajo kot številke ali sklice na celice, niso dovoljena. V pogovornem oknu, ki se prikaže, morate samo vnesti ime celice (njen točen naslov je že podan v vnosnem polju “Formula”) in pritisniti gumb Ok. Upoštevajte, da je v polju »Ime« namesto naslova celice zdaj postavljeno njeno ime. V celico, ki se nahaja levo od celice »Dollar_Rate«, lahko vnesete besedilo »Dollar Rate«. riž. 16 Zdaj je treba vnesti formulo za izračun cene v rubljih. Če želite to narediti, izberite zgornjo prazno celico v stolpcu »Cena v rubljih« in vnesite formulo na naslednji način: vnesite znak »=« in kliknite miško - 17 - Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency v celico na levi (v kateri je cena v dolarjih), nato vnesite znak "*" in "Dollar_rate". Formula bi morala izgledati nekako takole: =B7*Dollar_rate. Izpolnite formulo z oznako za polnjenje. Izberite ustrezne celice in zanje uporabite obliko številke valute. Oblikujte glavo tabele: sredinsko poravnajte, uporabite krepko pisavo, razširite črto in uporabite navpično sredinsko poravnavo z ukazom “Oblika” ⇒ “Celice ...”, izberite zavihek “Poravnava” in v razdelku “Navpično: «, izberite » V središču.« V istem pogovornem oknu aktivirajte stikalo »Prelomi po besedah«, če se kakšen naslov ne prilega v eno vrstico. Spremenite širino stolpcev. Izberite tabelo in ji nastavite rob. - 18 - Copyright JSC "CDB "BIBKOM" & LLC "Agency Kniga-Service" Laboratorijsko delo št. 3 Vaja 1 Spreminjanje orientacije besedila v celici, seznanitev z zmogljivostmi baz podatkov Excelovi podatki. Razvrščanje podatkov po več ključih Predstavljajte si, da ste lastnik majhne trgovine. Treba je voditi strogo evidenco prevzema in porabe blaga, imeti vsak dan realno stanje pred očmi, imeti možnost izpisa imena blaga po oddelkih itd. Tudi pri tako težki nalogi vam lahko Excel precej olajša delo. Razčlenimo ga ta vaja za več nalog v logičnem zaporedju: izdelava tabele; polnjenje tabele s podatki na klasičen način in z obrazcem; izbor podatkov na podlagi določenega kriterija. 1. Ustvarjanje tabele Vnesite naslove tabele v skladu s predlaganim primerom. Upoštevajte, da se naslov nahaja v dveh vrsticah tabele: v zgornji vrstici »Potrdilo«, »Stroški«, »Stanje« in vrstici pod ostalimi naslovnimi postavkami (slika 17). Znesek stanja Preostalo Količina stanja Količina odhodka Odhodek Cena odhodka Ime izdelka Količina prejem Oddelek Cena prejem Št. Merska enota Prejem 1 2 3 4 5 6 Sl. 17 Besedilo naslova je bolje začeti vnašati iz druge vrstice. Opazili ste že, da stolpec »Župnija« zajema dve celici. Beseda »Potrdilo« je vnesena v isti stolpec kot »Cena potrdila«, nato sta izbrani dve sosednji celici in besedilo je osredotočeno na izbor (to operacijo smo večkrat obravnavali v prejšnjih vajah). Celici »Stroški« in »Preostali« sta oblikovani podobno. Izberite drugo vrstico glave in jo poravnajte na sredino. Vidite lahko tudi, da je besedilo v nekaterih celicah "obrnjeno za 90°", da se celotna tabela prilega širini lista. Izberite tiste celice, v katerih želite “razširiti” besedilo in izberite ukaz “Format” ⇒ “Cells...” na zavihku “Alignment” (slika 18) izberite “Text Orientation” 90o in obvezno aktivirajte Stikalo »Prelomi v besede« (navpično poravnavo pustite na »Dno«). Za preostale (nerazširjene) celice uporabite navpično poravnavo »Na sredino«. Nastavite obrobo tabele (Oblika ⇒ Celice…, zavihek Obroba). Namestite v celice, sl. 18, ki vsebuje cene, denarni format števila (“Oblika” ⇒ “Celice...”, zavihek “Število”). Oštevilčenje vrstic tabele (št. stolpca) vnesite z označevalnikom za polnjenje. Vnesite formuli za znesek stanja (»Količina prejemkov« minus »Količina stroškov«) in znesek stanja (»Količina stanja«, pomnožena s »Ceno stroškov«). Te formule porazdelite po mizi. Med izvajanjem naloge je v mnogih primerih bolj priročno uporabiti kontekstni meni, ki ga prikličete s pritiskom na desni gumb miške. Če želite oblikovati celice, jih preprosto izberite, z desno miškino tipko kliknite, medtem ko je miškin kazalec znotraj izbora, in izberite ukaz “Oblikuj” ⇒ “Celice ...”. To vas bo pripeljalo do istega pogovornega okna Oblikuj celice (slika 18). In sploh ni potrebno urejati vsebine celice (popravljati, spreminjati podatkov) v vrstici Formula. Če dvokliknete na celico ali pritisnete tipko F2, se v njej pojavi besedilni kazalec in lahko opravite vse potrebne popravke. 2. Izpolnjevanje tabele Preimenujte »Sheet1« v »Availability«. To storite tako, da z desno miškino tipko kliknete bližnjico »Sheet1« in izberete ukaz Preimenuj. Vnesite novo ime in pritisnite Enter. Odločite se, kakšno vrsto izdelka boste prodajali in kateri oddelki bodo v vaši trgovini. V tabelo vnesite podatke ne po oddelkih, ampak naključno (v vrstnem redu prejema blaga). Izpolnite vse celice razen tistih, ki vsebujejo formule (»Ostanek«). Zadnjo vrstico tabele pustite prazno (vendar mora ta vrstica vsebovati vse formule in oštevilčenje). Podatke vnesite tako, da so različni izdelki iz istega oddelka (vendar ne zaporedoma) in da so vedno izdelki z ničelnim stanjem (vsi prodani) (slika 19). Strinjam se, da tradicionalni način izpolnjevanja tabele ni posebej priročen. Uporabimo zmožnosti podatkovnih baz Excel. 1 2 3 4 5 6 Oddelek Slaščice Mlečni izdelki Meso Meso Vino-vodka Ime izdelka Marshmallow v čokoladi Sir Klobasa Moskva Balyk Vodka “Absolut” Poraba Preostala Cena računa Količina računa Cena porabljene Količina porabe Količina preostanka Preostala količina Št. meritev Prihodni paket. 20 rub. 15 kg. 65 rubljev. 10 kg. 110 rubljev. 20 kg. 120 rubljev. 10 steklenic 2 l. 400 rubljev. 100 25 rub. 85 rubljev. 120 rubljev. 140 rubljev. 450 rubljev. 15 8 15 5 99 0 2 5 5 1 0 0 r. 170 rubljev. 600 rubljev. 700 rubljev. 450 rubljev. 0 rub. riž. 19 Izberemo ukaz “Podatki” ⇒ “Obrazec...” Prejeli boste podatkovni obrazec (slika 20), ki vsebuje statično besedilo (imena polj podatkovne baze) in urejevalna okna, v katera lahko vnašate in urejate besedilo. Izračunana polja (v katerih so formule) se prikažejo na zaslonu brez urejevalnih oken (“Preostali znesek” in “Preostali znesek”). Sedaj imate tabelo v obliki ločenih kartic (vsaka predstavlja vrstico tabele). riž. 20 Med zapisi se lahko premikate z uporabo gumbov »Prejšnji«, »Naprej« ali s smernimi tipkami (gor, dol) ali s premikanjem drsnika na drsnem traku podatkovnega obrazca. Ko je dosegel zadnji vnos (namenoma smo ga pustili praznega, vendar smo mu razširili formule in številčenje), ga napolnimo z novimi podatki. Med okni za urejanje, v katera vnašamo podatke, je priročno premikanje s tipko (Tab). Ko zaključite s celotnim vnosom, pritisnite tipko Enter in samodejno boste preusmerjeni na novo prazno vnosno kartico. Ko dokončate nov zapis, bodo vsi podatki, ki ste jih vnesli, samodejno reproducirani v izvirni tabeli. Izpolnite nekaj novih vnosov in kliknite gumb Zapri. - 21 - Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency Kot lahko vidite, je izpolnjevanje tabele v načinu obrazca zelo priročno. 3. Izpolnjevanje tabele s pomočjo že pripravljenega seznama podatkov Ker imamo omejeno število oddelkov in so njihova imena stalna, je pri izpolnjevanju tabele najbolje uporabiti vnaprej pripravljen seznam teh oddelkov. Izbrišite imena oddelkov iz stolpca »Oddelek« in vnesite kratek seznam, ki vključuje imena vseh oddelkov enkrat, zunaj tabele, na primer v stolpcu L. Nato izberite celice stolpca »Oddelek« v tabeli in izberite ukaz "Podatki" ⇒ "Preveri". V tem primeru se prikaže sl. 21 pogovorno okno “Preverjanje vnesenih vrednosti” (slika 21), kjer moramo določiti pogoje preverjanja. V našem primeru moramo izbrati s seznama (kar vnesemo v polje »Vrsta podatkov«). Za izbiro »Vir« podatkov uporabite gumb za strnitev okna. Kliknite nanj, označite seznam naših oddelkov v stolpcu L in se vrnite v okno z gumbom za povečaj okna. Ko končate te korake, kliknite V redu. Zdaj, ko gremo v celice stolpca »Oddelek«, kjer je nastavljen pogoj preverjanja, se bo desno od teh celic pojavil kvadrat s puščico, s klikom na katerega lahko izberemo ime oddelka, ki ga potrebujemo. (Slika 22). riž. 22 Če želite skriti tabelo oddelkov, lahko naredite pisavo v celicah stolpca L belo ali skrijete celoten stolpec. Če želite skriti stolpec L, ga izberite in izberite Oblika ⇒ Stolpec ⇒ Skrij. Za vrnitev stolpca L na zaslon, - 22 - Copyright JSC "CDB "BIBKOM" & LLC "Agency Kniga-Service" morate izbrati stolpce okoli skritega stolpca (stolpca K in M) in izvesti ukaz "Format" ⇒ "Stolpec" ⇒ "Zaslon" Upoštevajte, da lahko ukaz Skrij uporabite tudi za vrstice. Če želite to narediti, izberite vrstico in izberite ukaz “Oblika” ⇒ “Vrstica” ⇒ “Skrij”. Če želite vrstico vrniti na zaslon, morate izbrati črte okoli skrite črte in izvesti ukaz “Format” ⇒ “Row” ⇒ “Display”. riž. 23 Seznam lahko ustvarite tudi na drugem listu. Vendar pa je v tem primeru nemogoče določiti naslove, ki vključujejo ime lista kot »Vir« informacij, tj. Kot naslavljanje morate vnesti ime obsega celic. V zadnji lekciji smo se naučili poimenovati posamezno celico. Če želite poimenovati obsege celic, morate izbrati obseg celic, ne samo eno celico, preden izvedete ukaz “Vstavi” ⇒ “Ime” ⇒ “Dodeli”. Premaknimo naš seznam oddelkov iz stolpca L lista1 v list2 v stolpcu A. Izberite celice, v katerih je naš seznam, in izvedite ukaz “Vstavi” ⇒ “Ime” ⇒ “Dodeli”. V pogovornem oknu, ki se odpre (slika 23), lahko vnesete poljubno ime, na primer »Oddelek« in v polju »Formula« izberete obseg, za katerega je to ime vneseno (privzeto je naslov obsega, ki ga izbrano je postavljeno tukaj). Po tem kliknite gumb V redu. Zdaj v pogovornem oknu ukaza »Preveri ...« kot vir (slika 21) samo vnesite znak »=«, nato pritisnite tipko F3, da odprete seznam razpoložljivih imen celic, izberite »Oddelek« v seznam, ki se odpre, in pritisnite gumb V redu, da zaprete pogovorna okna. 4. Razvrščanje podatkov Torej, tabelo ste izpolnili po vrstnem redu prejema blaga, vendar bi želeli imeti seznam blaga po oddelkih, za to bomo uporabili razvrščanje po vrsticah. Izberite tabelo z drugo vrstico glave, vendar brez prvega stolpca »Ne« in izberite ukaz »Podatki« ⇒ »Razvrščanje ...« (slika 24). riž. 24 - 23 - Copyright JSC "CDB "BIBKOM" & LLC "Agency Kniga-Service" Izberite prvi ključ za razvrščanje: v spustnem seznamu "Razvrsti po" izberite "Oddelek" in nastavite stikalo v položaj "Naraščajoče". (vsi oddelki v tabeli bodo razvrščeni po abecedi). Če želite, da so vsi izdelki znotraj oddelka razvrščeni po abecedi, potem izberite drugi ključ za razvrščanje: v spustnem seznamu “Then By” izberite “Product Name”, stikalo nastavite na položaj “Naraščajoče”. Zdaj imate celoten seznam blaga po oddelkih. 5. Filtriranje podatkov Nadaljujmo s seznanjanjem z zmogljivostmi podatkovnih baz Excel. Spomnimo se, da moramo vsak dan natisniti seznam blaga, ki je ostalo v trgovini (ki ima stanje, ki ni nič), ali prikazati stanja v katerem koli oddelku, vendar moramo za to najprej pridobiti tak seznam, tj. filtrirajte podatke. Izberite tabelo z drugo vrstico glave (kot pred ustvarjanjem podatkovnega obrazca). Izberite menijski ukaz “Podatki” ⇒ “Filter ...” ⇒ “Samodejni filter”. Prekliči izbiro tabele. Vsaka celica glave tabele ima zdaj gumb s puščico (ni natisnjen), ki omogoča nastavitev kriterijev filtra (slika 25). riž. 25 Recimo, da želimo vse evidence pustiti za »Slaščičarski oddelek«. Razširite seznam celic »Oddelek« in izberite »Slaščice«. V tem primeru bo Excel spremenil tabelo in jo prikazal v obliki, kjer bodo prisotni samo podatki, ki se nanašajo na izbrani oddelek (slika 26), puščica v stolpcu, kjer je bil uporabljen avtofilter, pa bo postala modra. riž. 26 - 24 - Copyright OJSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency Na enak način si lahko ogledate podatke za druge oddelke ali izberete kriterij filtriranja v drugem stolpcu. Tabelo lahko natisnete v filtrirani obliki. Filtrirane vrstice je mogoče označiti z barvo pisave, ozadjem, okvirji ali kako drugače oblikovati. Z odstranitvijo filtriranja dobimo zelo jasno postavitev tabele. V filtriranem delu tabele lahko izračunate vsote, zmnožke in izvajate druge operacije, kot da v tabeli ne bi bilo drugih vrstic. Na primer, želimo izračunati bilančni znesek za slaščičarski oddelek. Če želite to narediti, izberemo podatke v stolpcu »Preostali znesek«, zgrabimo zadnjo prosto celico in kliknemo gumb »Samodejna vsota«. V prosti celici se pojavi funkcija SUBTOTAL(9; F2:F8) (slika 26). V njem je prvi argument številka matematične ali statistične operacije (1 - izračun povprečne vrednosti; 2 in 3 - štetje števila števil in nepraznih celic; 4 in 5 - izračun največje in najmanjše; 6 - zmnožek; 7 in 8 - standardni odklon; 9 - vsota ; 10 in 11 – disperzija), drugi pa interval izračuna. Funkcija SUBTOTAL se nahaja v matematični kategoriji in se razlikuje po tem, da izračuna vrednosti samo iz vidnih celic in ne upošteva nevidnih. Ko spremenite filtriranje, se spremenijo tudi vmesne vsote (slika 25), medtem ko običajna funkcija vsote ali produkta ostane nespremenjena. Malce spremenimo pogoj problema, recimo, da želimo pogledati podatke o neničelnih stanjih slaščičarskega oddelka. Če želite to narediti, na seznamu v stolpcu »Znesek stanja« izberite postavko »Pogoj«. Prikaže se pogovorno okno »Custom AutoFilter« (slika 28). V zgornjem polju izberite »več« »0,00 rub.«. Dobljeni rezultat je predstavljen na sl. 28. Sl. 27 Zdaj pa recimo, da si želimo ogledati podatke o neničelnih bilancah v oddelkih slaščic in mesa. Če želite to narediti, pustimo isti filter v stolpcu »Znesek stanja« in v stolpcu »Oddelek« izberemo »Pogoj« (slika 28). V zgornjem polju izberite »enako« Fig. 28 - 25 - Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency “Confectionery”, spodaj “equal” to “Meat”, in nastavite OR kot logično funkcijo. Dobljeni rezultat je predstavljen na sl. 29. Sl. 29 Če želite ponovno videti celotno tabelo, morate klikniti puščico v stolpcu, kjer je bilo uporabljeno filtriranje (prikazane so z modrimi puščicami), na seznamu izbrati »Vse« ali iti na »Filter« ⇒ »Samodejni filter ” znova v meniju “Podatki”, da prekličete način filtriranja. - 26 - Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency Laboratorijsko delo št. 4 Vaja 1 Ustvarjanje in urejanje grafov v Excel dokument Zelo pogosto morate v življenju izmeriti odvisnost ene spremenljivke od druge in te odvisnosti prikazati v obliki grafov. Excel ponuja možnost takšnega vizualnega prikaza numeričnih elektronskih podatkov. Recimo, da so izvedli nek poskus, na primer izmerili so odvisnost nekega parametra od temperature. Začetna temperatura je bila 10°C. Stopnja menjave je 10°C. Vnesite te podatke na Excel list (slika 30). Če želite postaviti glavo »Izvorni podatki« nad tabelo izvornih podatkov, kot je prikazano na sl. 30, kot je prikazano na sliki, izberite dve celici, izvedite ukaz “Format” ⇒ “Cell Format...” in na zavihku “Poravnava” izberite izbirni gumb v poljih “prelom besed”, “spoj celic” v prikaz in poravnava »centralne« skupine ter vodoravno in navpično (glej laboratorijsko delo št. 1). Zdaj pa pripravimo tabelo eksperimentalnih podatkov (slika 31). Vnesite podatke prvega stolpca - številke se vnašajo po vrstnem redu s pomočjo oznake za polnjenje (črna pika v kotu kazalca tabele). Naslov stolpca »Temperatura« se vnese s formulo: =A2 (tj. v formuli je povezava do naslova celice, slika 31, kjer je ime parametra postavljeno v tabeli »Začetni podatki« ( Slika 30)). Naša začetna temperatura je enaka začetni temperaturi, tj. =B2. Nato se od prejšnjega razlikuje po koraku. Zato je v drugi vrstici temperatura enaka začetni temperaturi + korak, tj. =E3+$B$3. Za nadaljnjo uporabo ročaja za polnjenje za kopiranje formule in je treba korak izvajati nenehno iz iste celice, naredimo sklicevanje nanj absolutno. Tako vnesene informacije nam omogočajo avtomatsko prilagajanje tabele eksperimentalnih podatkov spremembam začetnih pogojev. Poskusite namesto "temperature" v začetnih podatkih vnesti "tlak" in nastavite začetno vrednost, na primer 20. Vrednosti Yexperja je treba vzeti iz poskusa, zato jih preprosto vnesite s tipkovnice. Če želite oblikovati glavo »Poskus«, je najbolje uporabiti možnost kopiranja formata. Če želite to narediti: - 27 - Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency Fig. 32 sl. 33 izberite celico »Začetni podatki«; V orodni vrstici kliknite gumb »Oblikuj po standardnem vzorcu«. V tem primeru Excel kopira obliko izbrane celice, kazalec miške pa se spremeni v čopič z znakom plus na levi strani; Povlecite kazalec miške čez obseg celic, v katere želite kopirati izbrano obliko. Če želite narisati odvisnost Yexperja od temperature, izberite ta dva stolpca, vključno z njunima naslovoma, in izvedite ukaz »Vstavi« ⇒ »Diagram« ali kliknite gumb Čarovnik za grafikone v orodni vrstici. Prvo pogovorno okno »Čarovnik za grafikone (korak 1 od 4) - vrsta grafikona« (slika 32) ima dva zavihka - »Standardno« in »Po meri«. Na tej stopnji se iz razpoložljivih vzorcev izbere različica diagrama, ki se gradi. Če želite narisati odvisnost ene vrednosti od druge, morate izbrati vrsto grafikona "Scatter" in nato katero koli od njegovih petih vrst. Narišimo samo točke in ne rišemo črt. Kliknite na gumb “Ogled rezultata” (slika 32). Excel bo takoj pokazal, kako bodo naši podatki prikazani na končnem grafikonu. Če se želite premakniti na vsak naslednji korak čarovnika za grafikone, uporabite gumb »Naprej«. V drugem oknu »Čarovnik za grafikone (korak 2 od 4): vir podatkov grafikona« na zavihku »Obseg podatkov« sta prikazana naslov izbranega obsega izvornih podatkov in vzorec grafikona, ki se gradi (slika 33). . Izberite "v stolpcih" in kliknite gumb "Naprej". - 28 - Copyright JSC "CDB "BIBKOM" & LLC "Kniga-Service Agency" Na tretji stopnji čarovnika za grafikone (pri koraku 3) v oknu "Parametri grafikona" (slika 34) določite naravo grafikona oblikovanje - formatirajte. Tretje okno čarovnika za grafikone ima naslednje zavihke: naslov – omogoča vnos besedila naslova grafikona in oznak osi; os – omogoča določitev Sl. 34 prikaz in označevanje koordinatnih osi; mrežne črte – omogoča določitev vrste črt in narave prikaza mreže; legenda – omogoča, da prikažete ali skrijete legendo in določite njeno mesto v diagramu. Legenda - sl. 35 je majhno podokno na grafikonu, kjer so prikazana imena nizov podatkov in primeri njihovega barvanja na grafikonu (v obliki ključa Legenda); podatkovne oznake – omogoča nadzor nad prikazom oznak, ki ustrezajo posameznim podatkovnim elementom na diagramu; podatkovna tabela - omogoča dodajanje ali skrivanje podatkovne tabele, uporabljene za ustvarjanje grafikona v grafikonu. Zadnje (četrto) okno čarovnika za diagram (slika 35) služi za določitev njegove umestitve v delovni zvezek. Priporočljivo je, da izberete njegovo postavitev na ločenem listu, ker ... v tem primeru je diagram lažje vstaviti v druge dokumente, ne zakriva izvirnih podatkov, je bolj berljiv ipd. Ko določite vse zahtevane parametre, kliknite gumb »Dokončaj«. Excel nam je sestavil grafikon (slika 36) z uporabo določenega nabora parametrov, kot so barva območja risanja (območje, v katerem je prikazan sam grafikon, brez naslovov, legend in drugih elementov), pisava, merila, točka velikost itd., privzeto. Če želite spremeniti določeno možnost oblikovanja grafikona, jo morate klikniti z desno tipko miške in v kontekstnem meniju, ki se odpre, izbrati ustrezen ukaz. Naj bo ozadje območja grafikona belo. To storite tako, da z desno miškino tipko kliknete območje za izris diagrama in izberete ukaz »Oblikuj območje za izris«. V pogovornem oknu, ki se odpre (slika 37), izberite izbirni gumb v skupini »običajno« polnjenje. Kliknite V redu. - 29 - Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency Y=f(x) 12 10 8 Y 6 4 2 0 0 20 40 60 80 100 120 X Yexper Sl. 36 sl. 37 sl. 38 Povečajte velikost pik. To storite tako, da z desno miškino tipko kliknete točke in izberete ukaz »Oblikuj niz podatkov«. V pogovornem oknu, ki se odpre (slika 38), v skupini »velikost« nastavite velikost pike, na primer 8 pt. Tukaj lahko izberete druge parametre podatkov, na primer spremenite oznako, tj. vrsto točk, narišite črto, izberite njeno barvo, debelino in vrsto, zgladite črto itd. Argument naše funkcije se spreminja od 10 do 100, lestvica osi X pa ima najmanjšo vrednost 0 in največjo 120. Poleg tega je pisava podatkovnega podpisa premajhna. Kako lahko to spremenim? Z desno miškino tipko kliknite os X in izberite Oblikuj os. V pogovornem oknu, ki se odpre (slika 39), na zavihku »Scale« nastavite najmanjšo vrednost na 10, največjo na 100 in ceno glavnih delitev na 10, ker naši podatki se spreminjajo v korakih po 10. V istem oknu na zavihku »Pisava« lahko povečate velikost pisave in spremenite njen slog, na primer povečate jo na 8 pt in jo naredite ležečo. V pregibu “Poravnava” lahko nastavite navpični zapis podpisov. Na podoben način lahko - 30 - Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency formatirate os Y. Naredite to. Ali že razumete princip oblikovanja grafikona? Nato povečajte velikost pisave naslova, prilagodite legendo in naslove osi. Primer oblikovanega diagrama je prikazan na sliki (slika 40). Za napoved vrednosti odziva - parametra Y na izhodu eksperimenta od faktorja - neodvisnih spremenljivk X na vhodu v sistem (v našem primeru je to temperatura), je potrebno poznati funkcionalno odvisnost Y = f (X). Excel ima možnost samodejne izbire takšne funkcije. riž. 39 Yexper Y=f(x) 12 10 8 Y 6 4 2 100 90 80 70 60 50 40 30 20 10 0 X sl. 40 Z desno miškino tipko kliknite točke in v kontekstnem meniju izberite Dodaj trendno črto. V pogovornem oknu, ki se odpre (slika 41), na zavihku »Vrsta« izberite vrsto trendne črte. Običajno se za opis sistema uporablja polinomska trendna črta drugega reda: Y = a0 + a1 * X + a2 * X 2 , (1) kjer so ai koeficienti enačbe. Če je potrebno, lahko spremenite stopnjo na 6. Potem bo enačba v obliki: - 31 - Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency Y = a0 + a1 * X + a2 * X 2 + a3 * X 3 + + a4 * X 4 + a5 * X 5 + a6 * X 6 . (2) sl. 41 sl. 42 Na zavihku »Parametri« nastavite stikali na »prikaži enačbo na diagramu« in »postavite vrednost aproksimacijske zanesljivosti na diagram« (slika 42). Tako boste lahko videli enačbo in natančnost prileganja našim podatkom. Enačbo in natančnost, prikazano na zaslonu, lahko premaknete na poljubno mesto v grafikonu (kot tudi druge oznake, na primer naslov grafikona, naslove osi, legendo) tako, da z levim gumbom miške »zagrabite« okvir. Približna končna oblika naše odvisnosti je predstavljena na sl. 43. Shranite rezultate 1. vaje, potrebovali jih bomo pozneje (glejte laboratorij št. 6 spodaj). y = -0,0054x2 + 0,6014x - 5,9667 R2 = 0,9817 Y=f(x) Yexperjev polinom (Yexper) 12 10 8 6 Y 4 2 0 -2 10 20 30 40 50 60 X Sl. 43 - 32 - 70 80 90 100 Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency Vaja 2 Ustvarjanje in urejanje površin v dokumentu Excel V prejšnji vaji smo si ogledali možnosti vizualizacije odvisnosti enega parametra (funkcija odvisno samo od ene spremenljivke). V resnici so takšne preproste odvisnosti precej redke. Pogosteje se morate ukvarjati s funkcijami z več parametri. Poglejmo, kako jih vizualiziramo na primeru problema z dvema parametroma. Naj imamo enačbo: Z = sin X 2 − Y 2 + 5 ⋅ X ⋅ Y , (3) kjer se X in Y spreminjata od -5 do 5 s korakom 1. Potrebno je narisati površino dobljene Vrednosti Z. Za to morate najprej zgraditi podatkovno matriko (slika 44). () Riž. 44 V celico B1 vnesite prvo vrednost Y = -5. Nato izvedite ukaz “Edit” ⇒ “Fill” ⇒ “Progression...”. V pogovornem oknu, ki se odpre (Slika 45), nastavite: “Lokacija” - po vrsticah, “Korak:” enako 1 in “Mejna vrednost:” enako 5. Po tem kliknite gumb Ok. Na povsem enak način sl. 45 vrednosti X v stolpcu A so izpolnjene, le da mora biti »Lokacija« po stolpcu. Naredi. Ko so vrednosti argumentov vnesene v tabelo, izpolnite celico B2 s formulo za izračun Z (3). Funkcija Sin se nahaja v matematični kategoriji "Čarovniki za funkcije". - 33 - Copyright JSC "CDB "BIBKOM" & LLC "Agency Kniga-Service" Ne pozabite, da mora formula vsebovati mešane reference, saj morajo biti vrednosti X vedno izbrane iz stolpca A, vrednosti Y pa iz vrstica 1. Če želite izpolniti celotne tabele, uporabite oznako za polnjenje. Podatki za izdelavo površine so pripravljeni, ostane le še, da jih narišemo na diagram. Tako kot v prejšnji vaji bomo uporabili »Čarovnika za diagrame« (sl. 32 – 35). Najprej izberite matriko funkcijskih vrednosti (ni treba izbrati vrednosti X in Y na sliki 46!), Odprite čarovnika za diagram na kateri koli način, ki vam je znan, in izberite vrsto diagrama "Površina". Poleg tega se izdelava površine ne razlikuje od izdelave grafa. Končni diagram bo podoben tistemu, prikazanemu na sl. 46. Diagram lahko zasukate ali prilagodite v pogovornem oknu »Format 3D Surface« (slika 47), ki je prikazano na sl. 47 odprete tako, da z desno miškino tipko kliknete na stene površine in izberete postavko kontekstnega menija “Volume view...”. - 34 - Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency Laboratorijsko delo št. 5 Vaja 1 Logični izrazi v Excelu V celico A1 vnesite formulo =7>5. Vrnilo se bo TRUE. Kopirajmo vsebino A1 v A2 in popravimo formulo v A2: =3>5. Ta formula bo vrnila FALSE. Desni strani obeh formul predstavljata izjave, tj. izjave, ki jih je mogoče oceniti kot resnične ali napačne. Poglejmo še en primer. V celico A4 vpišimo številko 2, v celico B4 pa formulo =A4>3. Formula vrne FALSE. V A4 vpišimo številko 6. Formula vrne vrednost TRUE. B4 vsebuje predikat, tj. stavek s spremenljivkami (v tem primeru je samo ena spremenljivka). Glede na vrednost spremenljivk lahko predikat prevzame vrednosti TRUE in FALSE. V tem primeru se zdi, da formula odgovarja na vprašanje: »Ali je število (ali rezultat izračuna formule), shranjeno v celici A4, večje od 3?« Odvisno od vrednosti A4 bo odgovor DA (TRUE) ali NE (FALSE). V formuli =A4>3 lahko njene komponente (A4 in 3) štejemo za aritmetične izraze, le zelo preproste. Bolj zapleten primer: =(A4^2-1)>(2*A4+1). Oklepaje v tem izrazu lahko izpustite, ker imajo aritmetične operacije prednost pred primerjalnimi operacijami, vendar je zaradi oklepajev formula jasnejša. Primerjalne operacije povzemamo v tabeli. 1. Tabela 1 > večje od >= večje ali enako< <= меньше или равно меньше = <>enako ni enako Upoštevajte, da je simbol večje ali enako predstavljen z dvema znakoma: > in =. Razlog je v tem, da na tipkovnici ni znaka ≥. Stavek in predikat imata skupno ime – logični izraz. Na voljo logične operacije, ki vam omogočajo sestavljanje kompleksnih logičnih izrazov. Te operacije so v Excelu implementirane kot funkcije (NE, IN, ALI). U logične funkcije argumenti imajo lahko samo dve vrednosti: TRUE in FALSE. Funkcija NOT ima lahko samo en argument, funkciji AND in OR pa imata lahko dva ali več argumentov. Primer 1 V celico A1 (z imenom z) vpišite poljubno število. Ugotovite, ali spada v segment. rešitev. Dodelimo celici A1 ime z (“Vstavi” ⇒ “Ime” ⇒ “Dodeli”). V A1 vpeljimo število 3. Da bi z pripadal segmentu, morata biti hkrati resnična dva predikata: z ≥ 2 in z ≤ 5. V celico B1 bomo postavili - 35 - Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency formulo =I(z>=2;z<=5). Для ввода в формулу имени ячейки нажмите F3 для открытия списка имен. В В1 получим значение ИСТИНА. Следует предостеречь от неверного решения: формулы =2<=z<=5. Введите эту формулу в С1 и убедитесь, что она возвращает ЛОЖЬ! Коварство этой, на первый взгляд, такой естественной формулы в том, что Excel ничего не сообщает о ее некорректности. Пример 2 В ячейке А1 (с именем z) записано число. Выяснить, принадлежит ли оно одному из лучей на числовой оси: (-∞,2) или (5,∞). Решение. Для того чтобы z принадлежал хотя бы одному из лучей, нужно, чтобы был истинным хотя бы один из предикатов: z < 2 или z >5. V celico D1 postavite formulo =ALI(z<2;z>5). A1 vsebuje številko 3, zato formula vrne FALSE. Nalogo bi lahko rešili tudi drugače, ob upoštevanju dejstva, da je na delovnem listu zapisana formula za preverjanje, ali število z pripada odseku. Omenjena žarka predstavljata komplement tega segmenta na številski osi. V celico E1 vnesemo formulo =NE(B1). Z vnosom različnih števil v celico A1 se prepričajte, da dajeta formuli v celici D1 in E1 enake rezultate. V praksi se logični izrazi praviloma ne uporabljajo "v čisti obliki". Logični izraz služi kot prvi argument funkcije IF: IF(logični_izraz, vrednost_če_true, vrednost_če_napačna) Drugi argument je izraz, ki bo ovrednoten, če logični_izraz vrne TRUE, tretji argument pa je izraz, ki bo ovrednoten, če logični_izraz vrne FALSE. Primer 3 1. V celico A2 vnesite formulo, ki vrne z+1, če je z >1, in z v nasprotnem primeru: = IF(z>1;z+1;z). (V čarovniku za funkcije je IF v kategoriji »Logično«, tako kot funkcije IN, ALI, NE.); 2. Če je z > 60, potem v celici B2 prikaži sporočilo »Vrednost praga presežena«, sicer prikaži z: =IF(z>60; »Vrednost praga presežena«; z) Upoštevajte, da je besedilo v formulah vneseno v citati. 3. Če je z ∈ , potem vrni z, če je z< 10, то возвращать 10, если z >25, nato vrnite 25. Izraz za ta pogoj bo videti nekako takole (zapišimo formulo v C2): =IF(z<10;10;ЕСЛИ(z<=25;z;25)) Теперь попробуйте менять значение z в ячейке А1, следя за тем как меняются значения в ячейках с формулами. - 36 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Упражнение 2 Итоговые функции в Excel Некоторые функции Excel возвращают одно значение, в то время как аргументом имеют блок или несколько блоков. Такие функции будем называть итоговыми. Наиболее часто используемой из таких функций является СУММ − для ее ввода на панели «Стандартная» даже размещена специальная кнопка. Эта функция как бы подводит итог колонке чисел − отсюда и название для всей группы. Эти функции входят в категории «Статистические» и «Математические». Функция СУММ допускает до 30 аргументов. Поэтому с ее помощью можно находить сумму чисел из нескольких блоков. Допустима, например, такая формула =СУММ(В2:В9;12;-4.96;А4:С18). Если в блоке в какой-либо ячейке находится текстовое значение, то оно считается равным нулю. Кроме суммы к итоговым функциям относятся, например: МАКС и МИН − вычисление максимального и минимального значений, СРЗНАЧ − среднее арифметическое значение и т.д. Прочие итоговые функции вы можете найти в «Справке». К итоговым можно отнести функции И и ИЛИ. Пример 4 данные Имеются метеостанции – количество осадков (в мм) (Рис. 48). Необходимо получить суммарное, максимальное, минимальное и среднемесячное количество осадков. Решение. Введите формулы: в В17 =СУММ(В3:В14); в В18: =МАКС(В3:В14); в В19: =МИН(В3:В14); в В20: =СРЗНАЧ(В3:В14). Далее эти формулы скопированы в С17:D20. В блоке Е17:Е20 подведены итоги за три года. В Е17 формула =СУММ(В17:D17), в Е18: т.д. На =MAKC(B18:D18) и Рис. 48 Рис. 49 - 37 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» результаты наложен формат: одна цифра после десятичного разделителя (кнопка «Уменьшить разрядность»). Существует две полезные функции, примыкающие к итоговым: СЧЕТЕСЛИ и СУММЕСЛИ. Их названия показывают, что они считают и суммируют не все данные, а только удовлетворяющие некоторому критерию. Функция СЧЕТЕСЛИ (интервал; критерий) подсчитывает в интервале (т.е. блоке) количество значений, удовлетворяющих критерию. Пример 5 Вычислим количество засушливых месяцев, т.е. месяцев, когда выпадало менее 10 мм осадков (Рис. 49). В ячейке В22 формула =СЧЕТЕСЛИ(В3:В14;"<10"). Критерий взят в двойные кавычки, как текстовая строка. Формула скопирована в C22:D22. В Е22 подсчитана сумма. Функция СУММЕСЛИ(интервал;критерий;сумм_интервал) устроена сложнее. Значения, удовлетворяющие критерию, выбираются из блока, заданного первым аргументом, суммируются соответствующие значения из сумм_интервал, заданного третьим аргументом. Если третий аргумент опущен, то суммируются ячейки в аргументе интервал. Предположим, нужно вычислить суммарные осадки, которые выпали в незасушливые месяцы. Дополним таблицу (Рис. 49). В ячейку В23 введена формула =СУММЕСЛИ(B3:B14;">=10"), nato kopirate v C23:D23. S to funkcijo lahko rešite težji problem: kolikšna je bila skupna količina padavin v letu 1993 v tistih mesecih, ki so bili sušni v letu 1994. Rešitev je podana s formulo = SUMNIF(D3 :D14;"<10"; С4:С15), которая возвращает значение 128,6. Поместите ее в ячейку В24. К итоговым можно отнести еще две функции: НАИБОЛЬШИЙ(блок;k) и НАИМЕНЬШИЙ(блок;k). Первая из этих функций возвращает k-e наибольшее значение из множества данных, а вторая − наименьшее. Пример 6 В ячейки A1:D1 введем набор чисел. В блок А2:А5 введем формулы, которые показаны в соседнем столбце Рис. 50 (Рис. 50). Для понимания работы функции важно отметить, что третье наибольшее значение в блоке не 2, как можно было бы подумать, а 6, т.е. совпадает со вторым наибольшим значением. И еще: если в блоке n элементов, то функция НАИБОЛЬШИЙ(блок,n) возвращает минимальное значение, что мы и видим в примере. Обратите внимание, что в А2:А5 получен исходный массив чисел, отсортированный по убыванию. Если в исходном блоке изменить какое-либо число, - 38 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» то блок А2:А5 будет автоматически перестроен. В этом отличие от операции сортировки, которая проводится самим пользователем при необходимости. Обязательно сохраните результаты примеров 4-6, так как мы будем их использовать позднее. Упражнение 3 Табличные формулы в Excel Табличные формулы − очень мощное средство Excel, позволяющее в формулах обращаться с блоками, как с обычными ячейками, давать компактные решения сложных задач. В некоторых задачах вообще невозможно обойтись без табличных формул. Пример 7 Пусть нам известна сумма прихода и сумма расхода. Необходимо вычислить доход как разность этих величин. Введите исходные данные (Рис. 51). Рис. 51 В ячейку D2 можно набрать формулу: =В2-С2, а затем скопировать ее в D3:D5. В этих ячейках появятся формулы =В3-С3 и т.д. Однако здесь фактически из вектор-столбца В2:В5 вычитается вектор-столбец С2:С5. Поэтому можно непосредственно вычесть из вектора вектор одной формулой, а не создавать отдельные формулы для компонент вектора. Создание имен. Для наглядности дадим векторам имена. Выделите диапазон со вторым и третьим столбцами таблицы (В1:С5) и дайте команду меню «Вставка» ⇒ «Имя» ⇒ «Создать». Диапазон В2:В5 получит имя «Приход», а диапазон С2:С5 имя «Расход». Ввод табличной формулы с использованием имен диапазонов. Прежде мы вводили формулу в отдельную ячейку. А сейчас введем ее в диапазон. Подробно опишем шаги. Выделим блок D2:D5. В этом блоке активна ячейка D2. Наберем знак равенства =. Нажмем функциональную клавишу F3. Появится диалоговое окно «Вставка имени». Выберем имя «Приход» и щелкнем Оk. Формула примет вид: =Приход. Наберем знак минус -. Вновь нажмем клавишу F3. В диалоговом окне «Вставка имени» выберем имя «Расход» и щелкнем Ok. Формула примет вид: =Приход-Расход. Нажмем сочетание клавиш Shift+Ctrl+Enter. Во всех ячейках блока появится формула {=Приход-Расход}. - 39 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Прокомментируем шаги. На третьем и пятом шаге мы выбирали имя из списка имен. Можно было ввести имя непосредственно с клавиатуры, но предложенный метод проще и нет риска ошибиться в наборе имени. На шестом шаге мы нажимаем не Enter, как ранее при вводе формулы, a Shift+Ctrl+Enter (при нажатии клавиши Enter должны быть нажаты обе клавиши Shift и Ctrl). Это очень важно. Если бы мы нажали Enter, то формула была бы введена только в активную ячейку блока D2 (Проверьте!). Фигурные скобки, окружающие формулу, говорят о том, что это табличная формула. Эти скобки нельзя набирать вручную (формула будет воспринята как текст). Ввод табличной формулы. Разумеется, табличную формулу можно вводить и без использования имен. Скопируйте блок А1:С5 в А8:С12. Повторите все шаги. Выделите блок D9:D12. В этом блоке активной ячейкой является D12. Наберите знак равенства =. Выделите блок В9:В12, наберите знак минус -, выделите блок С9:С12, нажмите сочетание клавиш Shift+Ctrl+Enter. Во всех ячейках блока появится формула {=В9:В12-С9:С12}. Мы получили две идентичные таблицы. Выделение блока с табличной формулой. Выделите одну из ячеек блока и нажмите клавишу F5 (эквивалент пункта меню «Правка» ⇒ «Перейти»). В диалоговом окне щелкните по кнопке «Выделить», установите переключатель «Текущий массив». Изменение табличной формулы. Попытайтесь очистить одну из ячеек, занятую табличной формулой. Например, выделите ячейку D8 и нажмите клавишу Del. В этом случае должно появится сообщение «Нельзя изменять часть массива». Удалить блок можно только целиком. Отредактировать формулу можно так: выделить блок с формулой, нажать функциональную клавишу F2, внести изменения в формулу, нажать сочетание клавиш Shift+Ctrl+Enter. (Попробуйте, например, ввести формулу {=Приход-Расход-1}, потом отмените это.) Коррекция табличной формулы при увеличении блока. Добавьте в обе таблицы на рабочем листе строку с данными: Год - 1996, приход - 240, расход – 200. Необходимо посчитать прибыль за 1996. Раньше, когда формулы записывались в отдельные ячейки, мы бы поступили просто: скопировали бы формулу из ячейки D5 в D6. Проделаем это для первой таблицы. Вместо ожидаемого 40 получим результат 50, т.е. число из первой ячейки блока с табличной формулой. Та же операция для второй таблицы даст правильный результат 40, но в строке формул мы увидим {=В13:В16-С13:С16} – образовался второй блок, что вовсе не входило в наши планы. Удалим формулы в ячейках D6 и D13. Правильное решение для первой и второй таблиц разное. Для первой таблицы изменим именованные блоки (выделим В1:С6 и «Вставка» ⇒ «Имя» ⇒ «Создать», для каждого имени Excel задаст вопрос: «Заменить» существующее определение имени?» Отвечаем «Да»). Выделяем D2:D6, нажимаем клавишу F2 (редактирование) и, ничего не изменяя в формуле, нажимаем клавиши Shift+ Ctrl+Enter. Для второй таблицы выделяем D8:D13, нажимаем клавишу F2 и редактируем формулу. Выделим в формуле подстроку В8:В12 и выделим блок В8:В13, также поступим с блоком С8:С12 либо просто заменим в адресах блоков цифру 2 на цифру 3. Нажимаем сочетание клавиш Shift+Ctrl+Enter. - 40 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Коррекция табличной формулы при уменьшении блока. Теперь мы хотим удалить в каждой из таблиц строку для 1996 г. Для первой таблицы вновь изменяем поименованные блоки (в ячейке D6 результат отображается как #Н/Д - недоступно). Выделяем блок с табличной формулой, нажимаем клавишу F2 и добавляем в самое начало формулы апостроф (он расположен на клавише с буквой "Э"). Формула превращается в текст. Вводим этот текст во все ячейки (клавиши Ctrl+Enter). Табличная формула прекратила существование. Очищаем последнюю строку таблицы. Выделяем блок D2:D5, нажимаем клавишу F2, удаляем апостроф, нажимаем клавиши Shift+Ctrl+Enter. Аналогично поступаем со второй таблицей. Как видим, процедура непростая и неприятная. Для решения задачи проще было воспользоваться простыми формулами. Но применение табличных формул, как мы убедимся, дает такие дополнительные возможности, что с неудобствами, связанными с изменениями этих формул, придется смириться. Упражнение 4 Дистрибутивные функции в Excel В Excel можно к блоку применить функцию (большое множество функций Excel), с тем, чтобы она вернула новый блок, содержащий значения функции для элементов исходного блока. Пример 8 Пусть в блоке А1:А4 записаны числа 1, 4, 9, 16. Поместим в B1:В4 табличную формулу {=КОРЕНЬ(А1:А4)}. Будет выведен столбец значений: 1, 2, 3, 4. Можно считать, что функция КОРЕНЬ была применена к вектору из четырех компонент и вернула новый вектор. (Разумеется, тот же результат можно было получить, записав в В1 формулу =КОРЕНЬ(А1) и скопировав ее в блок В2:В4.) Теперь рассмотрим функцию, которую нельзя применять к блоку. Поместим в D1:D2 логические значения ИСТИНА и ЛОЖЬ. В блок Е1:Е2 запишем табличную формулу {=И(D1:D2;”ИСТИНА”)}. Эта формула вернет значение ЛОЖЬ во всех ячейках блока Е1:Е2. Получается, что функцию КОРЕНЬ можно применять к массиву, а функцию И – нет. Функции, которые можно применять к списку, называются дистрибутивными. Продолжим пример с вычислением квадратного корня от элементов блока. Мы хотим вычислить сумму корней ∑ ai . Поместим в ячейку В5 формулу =СУММ(В1:В4). Результат, разумеется, 10. А теперь вычислим эту же сумму, не используя промежуточный блок В1:В4. Поместим в ячейку А5 табличную формулу {=СУММ(КОРЕНЬ(А1:А4))}. Обратите внимание, хотя формула возвращает значение в одной ячейке, она должна вводиться как табличная, т.е. ее ввод заканчивается нажатием комбинации клавиш Shift+Ctrl+Enter. Для сравнения введите в А6 эту формулу как обычную, – она вернет сообщение об ошибке #ЗНАЧ!. - 41 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Итак, к массиву, возвращаемому дистрибутивной функцией, можно применить итоговую функцию, которая возвращает всего одно значение. Но вводить такую формулу нужно как табличную. Пример 9 Рис. 52 Функцию ИЛИ можно использовать в табличных формулах, но как итоговую, т.е. если ИЛИ имеет всего один аргумент и этот аргумент – блок. Пусть в блоке А1:В2 находятся названия планет (Рис. 52). В ячейку D2 введем табличную формулу {=ИЛИ(СОВПАД(А1:В2;D1))}. Функция СОВПАД возвращает логическое значение ИСТИНА или ЛОЖЬ в зависимости от совпадения или несовпадения своих аргументов – текстовых строк. Если бы в А4:В5 была введена формула массива {=СОВПАД(А1:В2;D1)}, то она вернула бы четыре значения (Рис. 52). Функция ИЛИ(А4:В5) возвращает значение ИСТИНА. Эти две формулы мы объединяем в одну табличную формулу, которую и ввели в D2. Пример 10 Вернемся к задаче обработки данных метеостанции (пример 4 и пример 5). Для расчета количества засушливых месяцев, т.е. месяцев, когда выпало менее 10 мм осадков, очень удобно использовать функцию СЧЕТЕСЛИ. Однако с ее помощью нельзя получить количество месяцев, на протяжении которых количество осадков лежало бы в диапазоне от 20 до 80 (назовем такие месяцы нормальными). Для этого необходимо использовать дистрибутивные функции. Скопируйте текст из ячейки А22 в ячейку А25 и откорректируйте его: «Количество нормальных месяцев». Сначала подсчитаем месяцы с нормальным количеством осадков. Будем использовать вспомогательный блок F3:H14 тех же размеров, что и блок с исходными данными. В ячейку F3 вводим формулу =ЕСЛИ(И(В3>20;B3<80);1;0) и копируем ее в остальные ячейки блока F3:H14. В блоке выводятся нули и единицы. Введенная формула является индикаторной функцией множества нормальных месяцев, т.е. 1 – выводится, когда количество осадков лежит в пределах между 20 и 80 мм и 0 – в противном случае. Остается подсчитать сумму таких месяцев. Для этого введем в ячейку F25 формулу =СУММ(F3:F14) и скопируем ее в блок G25:H25. А теперь решим эту же задачу без использования вспомогательного блока. Введем в В25 табличную формулу {=СУММ(ЕСЛИ(В4:В15>20; ČE (B4: B15<80;1;0);0))} и скопируем ее в C25:D25. (Таким образом, машина, перебирая значения в указанном блоке, проверяет больше или меньше текущее число 20 и, если оно меньше, прибавляет к исходному число 0. В противном случае – проверяет выполнение второго условия (менее 80). Если и второе условие выполняется, то к исходному числу прибавляет 1. В противном - 42 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» случае – 0). Формула {=СУММ(ЕСЛИ(И(В4>20;B4<80);1;0))} не приведет к успеху, так как функция И не является дистрибутивной). Этот пример очень важен. Он Рис. 53 дает рецепт, как подсчитать в множестве количество элементов, удовлетворяющих определенному критерию. Нужно составить на основе вложенных функций ЕСЛИ индикаторную функцию подмножества и применить к ней итоговую функцию СУММ, введя формулу как табличную. Теперь вычислим суммарное количество осадков, выпавших в эти месяцы Введите в А26 текст «Осадки в нормальные месяцы», в В26 – табличную формулу {=СУММ(ЕСЛИ(В3:В14>20; ČE(V3:V14<80;В3:В14;0);0))} и скопируйте ее в C26:D26. В E25 и Е26 введите формулы для суммирования значений в строках (выделите блок В25:Е26 и щелкните кнопку «Автосумма»). Вы получите блок, показанный на рис. 53. Пример 11 В блоке А1:А10 записана числовая последовательность. Проверьте, является ли она возрастающей. Решение. Перейдите на новый лист. Запишите любую последовательность чисел в блок А1:А10. Окончательное решение можно записать одной формулой (поместите его в ячейку A12). {=ЕСЛИ(СУММ(ЕСЛИ(А2:А10-А1:А9>0;1;0))=COUNT(A1:A10)1;"narašča";"se ne povečuje")) Analizirajmo to formulo: A2:A10-A1:A9 (tj. A9 se odšteje od A10, A8 se odšteje od A9 itd.) – tvori blok, sestavljen iz prvih razlik elementov izvirnega bloka; IF(A2:A10-A1:A9>0;1;0) – sestavlja blok indikatorjev pozitivnih prvih razlik; SUM(IF(A2:A10-A1:A9>0;1;0)) – prešteje število neničelnih elementov v indikatorskem bloku; COUNT(A1:A10)-1 – izračuna velikost indikatorskega bloka, ki je enaka velikosti izvirnega bloka, zmanjšani za 1; če je število neničelnih elementov v indikatorskem bloku enako velikosti indikatorskega bloka, se zaporedje povečuje, sicer pa ne. Poskusite korak za korakom sestaviti ustrezne bloke in iz njih nastale funkcije, da boste dosegli jasno razumevanje, kako je sestavljena končna formula. - 43 - Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency Vaja 5 Matrične operacije v Excelu Najenostavnejše operacije, ki jih je mogoče izvesti z matrikami: seštevanje (odštevanje), množenje s številom, množenje, prenos, izračun inverzna matrika . Primer 12 Seštevanje matrik in množenje matrike s številom. Seštejemo matriki M in N, kjer je − 1 0 4  2 − 3 7 M = in N =   2 − 3 5 .    − 1 5 6 Rešitev. Vstavimo matriki M in N v bloka A1:C2 in E1:G2. V blok A4:C5 vnesemo tabelarično formulo (=A1:C2+E1:G2). Upoštevajte, da je bil izbran blok, ki ima enake dimenzije kot izvirne matrice. Kaj se zgodi, če pred vnosom formule izberete blok A4:D6? #N/A bo prikazan v "dodatnih" celicah, tj. "Ni na voljo." In če izberete A4:B5? Izpisan bo le del matrike, brez sporočil. Preverite. Z uporabo imen je vnos formule v preglednico veliko lažji. Območjem A1:C2 in E1:G2 dajte imena M oziroma N (izvedite ukaz za vsak blok “Vstavi” ⇒ “Ime” ⇒ “Dodeli”). V blok E4:G5 vnesite formulo tabele (=M+N). Rezultat bi seveda moral biti enak. Zdaj pa izračunajmo linearno kombinacijo matrik 2M-N. V blok A7:C8 vnesemo tabelarično formulo (=2*M-N). Dobili bi morali naslednje rezultate:  5 − 6 10 1 − 3 11 M +N = in 2 M − N = − 4 13 7  .    1 2 11 Obravnavani primeri nas pripeljejo do ideje, da običajna operacija množenja, ki se uporablja za bloke, ni povsem enakovredna matričnemu množenju. Dejansko za matrične operacije v Excelu obstajajo funkcije, vključene v kategorijo »Matematični«: MOPRED - izračun determinante matrike; MOBR – izračun inverzne matrike; MUMULT – matrično množenje; TRANSPOSE – transpozicija. Prva od teh funkcij vrne število, zato se vnese kot običajna formula. Preostale funkcije vrnejo blok celic, zato jih je treba vnesti kot formule tabele. Prva črka "M" v imenih treh funkcij je okrajšava za besedo "Matrix". Primer 13 Izračunajte determinanto in inverzno matriko za matriko - 44 - Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency − 73 78 24 A =  92 66 25 .   − 80 37 10  Preverite pravilnost izračuna inverzne matrike tako, da jo pomnožite z izvirno. Ponovite te korake za isto matriko, vendar z elementom a33=10,01. rešitev. Prvotno matriko postavimo v blok A1:C3. V celico B5 postavimo formulo za izračun determinante =MOPRED(A1:C3). V bloku A7:C9 vnesemo formulo za izračun inverzne matrike. Če želite to narediti, izberite blok A7:C9 (ima tri vrstice in tri stolpce, kot izvirna matrika). Vnesemo formulo (=MOBR(A1:C3)). Tudi če uporabljate čarovnika za funkcije, morate dokončati vnos s pritiskom na kombinacijo tipk Shift+Ctrl+Enter (namesto s klikom na gumb “V redu”). Če ste pozabili vnaprej izbrati blok A7:C9 in ste formulo vnesli v celico A7 kot navadno formulo v Excelu (zaključno s pritiskom na Enter), vam je ni treba znova vnesti: izberite A7:C9, pritisnite F2 ( uredi), vendar ne spreminjajte formule, samo pritisnite tipke Fig. 54 Shift+Ctrl+Enter. Kopirajte blok A1:C9 v blok E1:G9. Nekoliko spremenite en element prvotne matrike: v celico G3 namesto 10 vnesite 10.01. Spremembe v determinanti in inverzni matriki so osupljive! Ta posebej izbrani primer ponazarja numerično nestabilnost izračuna determinante in inverzne matrike: majhna motnja na vhodu povzroči veliko motnjo na izhodu. Za nadaljnje izračune bomo matrikam na delovnem listu dodelili imena: A1:C3 - A, A7:C9 - Ainv, E1:G3 - AP, E7:G9 - APinv. Če želite, da se ta imena prikažejo v že vnesenih formulah, izberite ustrezne formule, v meniju izberite “Vstavi” ⇒ “Ime” ⇒ “Uporabi”, v pogovornem oknu izberite želena imena in kliknite “V redu”. Zdaj pa preverimo pravilnost izračuna inverzne matrike. V blok A12:C14 vnesemo formulo (=MUMULT(A,Ainv)), v blok E12:G14 pa formulo (=MUMNOT(AP,APinv)). Morali bi dobiti rezultat kot na sl. 54. Kot je bilo pričakovano, so bile nastale matrike skoraj enake. - 45 - Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency Upoštevajte, da je nabor matričnih operacij v Excelu slab. Če morate resno delati z matrikami, je bolje, da se zatečete k pomoči takih matematičnih paketov, kot so MatLAB (Matrix LABoratory), Mathematica, Derive. - 46 - Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency Laboratorijsko delo št. 6 Vaja 1 Iskanje rešitve V laboratorijskem delu št. 4 smo si ogledali primer avtomatskega iskanja funkcionalne odvisnosti Y = f(X). Spomnimo se, da je iskanje takšne odvisnosti potrebno za napovedovanje vrednosti odziva - parametra Y na izhodu eksperimenta iz faktorsko neodvisnih spremenljivk X na vhodu v sistem (glej laboratorijsko delo št. 4). V nekaterih sl. 55 primerov, predstavljenih v Excelove funkcije včasih ni dovolj. Zato je pomembno, da znate takšno funkcijo izbrati sami, z uporabo ene od metod matematične optimizacije, na primer metode najmanjših kvadratov. Njegovo bistvo je minimizirati vsoto kvadratov razlike med eksperimentalnimi (Yexper) in izračunanimi (Ycalculation) podatki: n ∑ (Yexper,i − Y izračun,i) 2 , i =1 (4) kjer je bil n v našem problemu enak do 10 Odprite laboratorijsko nalogo št. 4 in nadaljujte z izpolnjevanjem tabele. Eksperimentalni Y-ji so že bili predstavljeni. Zdaj izpolnimo tabelo z izračunanim Y. Za to bomo potrebovali dodatno tabelo koeficientov, katerih vrednosti bomo najprej enačili z 1 (slika 55). Zdaj vnesite formulo polinoma druge stopnje (1) za izračun Y (slika 55). Naslednja naloga je, da sl. 56 izberite koeficiente enačbe tako, da je razlika med Ycalculation in Yexpert minimalna. Za to morate vnesti formulo za izračun kvadratne razlike (3) in formulo za izračun Pearsonovega kriterija za oceno točnosti našega izračuna (slika 56). Obe formuli sta vgrajeni v Excel in služita kot primera funkcij, za katere lahko storite brez vnašanja formul preglednice (glejte Lab #4 zgoraj). Odprite čarovnika za funkcije na poljuben način. V kategoriji "Matematika" izberite formulo SUMVARIEF in kliknite V redu. V drugem oknu čarovnika za funkcije na sl. 57 vnesite matriko Yexpert kot array_x in matriko Ycalculation kot array_y in kliknite V redu. Formula za izračun Pearsonovega testa je v kategoriji »Statistični« (funkcija PEARSON). V drugo okno čarovnika za funkcije prav tako vnesite matriko Yexpert kot array_x in matriko Ycalculation kot array_y in kliknite V redu. Za iskanje vrednosti koeficientov ima Excel dodatek Solver, ki vam omogoča reševanje problemov iskanja največjih in najmanjših vrednosti ter reševanje različnih enačb. Izberemo celico, kjer je vpisana formula za izračun kvadratne razlike in izvedemo ukaz “Orodja” ⇒ “Išči rešitev”. Če v meniju »Storitev« ni takega ukaza, morate najprej izvesti ukaz »Storitev« ⇒ »Dodatki« in v pogovornem oknu, ki se odpre, izberite stikalo v stolpcu »Iskanje rešitve« ( Slika 57) in šele nato izvedite ukaz “Servis” ⇒ “Iskanje rešitve.” V pogovorno okno Iskanje rešitve (slika 58) vnesite naslednje parametre: naslov ciljne celice z izbrano vrednostjo (naslov celice s formulo za vsoto kvadratov razlike), če ste ga izbrali vnaprej, se naslov postavi samodejno; v polju “Equal to:” nastavite izbirni gumb na “minimum value”; - 48 - Copyright JSC "CDB "BIBKOM" & LLC "Agency Kniga-Service" v polje "Spreminjanje celic" vnesite obseg celic spreminjajočih se koeficientov. Gumb »Možnosti« se uporablja za spreminjanje in konfiguracijo parametrov iskanja. Na sl. 59 njihovo število vključuje: način reševanja problema, čas izračunov in točnost rezultatov. Vendar v večini primerov zadostuje uporaba privzetih nastavitev. Iskanje rešitve se izvede po kliku na gumb »Zaženi«. Če je iskanje rešitve uspešno zaključeno, se rezultati izračuna vnesejo v izvorno tabelo in na zaslonu se prikaže pogovorno okno »Rezultati iskanja rešitev« (slika 59), s katerim lahko shranite rešitve, ki jih najdete v viru. tabelo, obnovite prvotne vrednosti in shranite rezultate iskanja rešitve v obliki skripta, ustvarite poročilo o rezultatih operacije iskanja rešitve. Primerjajte dobljene vrednosti koeficientov s koeficienti v enačbi trendne črte. Dodajte izračunane vrednosti Y na graf. Če želite to narediti, pojdite v okno grafikona, z desno tipko miške kliknite kjer koli na njem in v kontekstnem meniju izberite ukaz »Izvorni podatki«. V istoimenskem pogovornem oknu, ki se odpre (slika 60), pojdite na zavihek »Vrstica« in kliknite gumb »Dodaj«. V polju »Ime« kliknite gumb za pomanjšanje okna, sl. 60 pojdite na list s svojimi podatki, izberite celico glave stolpca Ycalculation in se vrnite v okno z uporabo gumba za povečanje okna. Podobno sl. 58 - 49 - Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency vnesite »Vrednosti X« (obseg celic z vrednostmi X ali temperature) in »Vrednosti Y« (obseg celic z izračunanimi vrednostmi Y). Ko končate z vnosom, pritisnite gumb Ok. Upoštevajte, da točke Y izračuna ležijo na trendni črti, ki smo jo zgradili prej (slika 61). Na koncu obvezno shranite svojo datoteko, uporabili jo bomo v naslednji lekciji (glejte Lab #7 spodaj). y = -0,0054x2 + 0,6014x - 5,9667 2 R = 0,9817 Y=f(x) Yexper Y Računski polinom (Yexper) 12 10 8 6 Y 4 2 0 -2 10 20 30 40 50 60 X Sl. 61 - 50 - 70 80 90 100 Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency Laboratorijsko delo št. 7 Vaja 1 Iskanje rešitve problema z dvema parametroma v Excelu V prejšnjih lekcijah smo si ogledali možnosti obdelave in vizualizacija odvisnosti enega parametra (funkcija je odvisna samo od ene spremenljivke). V resnici so takšne preproste odvisnosti precej redke. Pogosteje se morate ukvarjati s funkcijami z več parametri. Razmislimo, kako obdelati takšne odvisnosti in kako jih vizualizirati na primeru problema z dvema parametroma. Naj se izvede poskus, na primer, izmerimo odvisnost nekega parametra od temperature in tlaka. Povprečna temperatura je bila 100°C. Stopnja menjave je 50°C. Povprečni tlak - 2 atm. Korak menjave je 1 atm. Tak sistem bomo opisali z razmerjem: Y = f (X1, X 2), (5) ki je površina, ki je pogosto prikazana v obliki, podobni konturni karti (slika 62). riž. 62 - 51 - Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency. Da bi našli to odvisnost za naš primer, bomo uporabili predlogo iz lekcij 4 in 6. Če želite to narediti, odprite shranjeno datoteko in pojdite na list z podatke. Kliknemo na bližnjico List in izberemo ukaz »Premakni/Kopiraj« (slika 63). V pogovornem oknu, ki se odpre (slika 64), lahko izberemo, kam želimo premakniti (kopirati) svoj list (v trenutno knjigo ali novo). Izberite naslov trenutne knjige; pred kateri list želimo postaviti trenutni list ali njegovo kopijo. Izberite "(premakni na konec)". Ne pozabite potrditi potrditvenega polja »Ustvari kopijo«, sicer se bo list preprosto premaknil na konec knjige. Nato kliknite V redu. Excel privzeto ustvari kopijo z imenom trenutnega delovnega lista in doda številko kopije na koncu v oklepajih. Zaradi udobja ga preimenujmo. To storite tako, da kliknete oznako lista in izberete ukaz »Preimenuj« (slika 63); Vnesite novo ime, na primer "Eksperiment_2" in pritisnite tipko "Enter". Najprej obnovimo tabelo izvornih podatkov, kot je prikazano na sl. 65. Označite dve celici na vrhu stare tabele (tisti, v katerih sta bila postavljena ime parametra “Temperatura” in njegova vrednost) in izvedite ukaz “Vstavi” ⇒ “Celice...”. S tem se odpre pogovorno okno »Dodaj celice«, ki predlaga njihovo lokacijo (slika 66). Stikalo nastavite na položaj "celice s premikom navzdol" in kliknite gumb V redu. - 52 - Sl. 63 sl. 64 sl. 65 sl. 66 Copyright JSC Central Design Bureau BIBKOM & LLC Kniga-Service Agency Izberite prazen stolpec C (kliknite na glavo tega stolpca) in izvedite ukaz “Vstavi” ⇒ “Stolpci”. Naredite potrebne spremembe v tabeli (slika 65). Na podoben način prinesite tabelo poskusa v obliko, prikazano na sl. 67. Naj vas spomnimo, da je treba naslove stolpcev "Temperatura" in "Tlak" vnesti z uporabo formul, da bo obdelovanec bolj univerzalen. riž. 67 Dopolnimo podatke v tabeli »Poskus«. Koordinate točk 1 – 9 lahko izračunamo v skladu s sl. 62 po naslednjih formulah: Št. 1 2 3 4 5 6 7 8 9 Temp. Xsr,1-Step Xsr,1 Xsr,1+Step Xsr,1-Step Xsr,1 Xsr,1+Step Xsr,1-Step Xsr,1-Step Xsr,1+Step Press. Xsr,2-Step Xsr,2-Step Xsr,2-Step Xsr,2 Xsr,2 Xsr,2 Xsr,2-Step Xsr,2-Step Xsr,2-Step Ko vnašate formule, ne pozabite narediti trajnih povezav uporabiti možnost kopiranja. Iz poskusa moramo vzeti vrednosti Yexpert. Naj bosta enaka: Število točk Yexper 1 1 2 7 3 5 4 17 5 25 6 15 Yizračun je treba izračunati po formuli: Yizračun = A0 + A1 X 1 + A2 X 2 + A11 X 12 + A12 X 1 X 2 + A22 X 22 . - 53 - 7 3 8 10 (6) 9 4 Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency Pred vnosom formule (6) je potrebno spremeniti tabelo koeficientov, kot je prikazano na sl. 68, vnos začetnih vrednosti koeficientov 1. Za izbiro funkcije bomo uporabili metodo minimiziranja vsote kvadratov razlike med eksperimentalnimi (Yexper) in izračunanimi (Ycalculation) podatki, ki smo jih obravnavali v zadnjem lekcija. riž. 68 Formuli za izračun kvadratne razlike in formulo za izračun Pearsonovega kriterija že imamo na našem listu. Zdaj morate samo popraviti povezave v njih in izvesti. Iskanje rešitve poteka na enak način kot pri funkciji z enim parametrom, a ker je naša odvisnost kompleksnejša, je potrebno v pogovornem oknu »Iskanje rešitve« odpreti podokno »Parametri«. polje (slika 69) in nastavite naslednje možnosti: dovoljeno odstopanje – 1 %; "Samodejno skaliranje"; ocene – “kvadratne”; razlike – “Central”. riž. 69 Nato kliknite gumb V redu in v oknu »Išči rešitev« - »Zaženi«. Če v prvem poskusu ni dosežena zadovoljiva natančnost, lahko operacijo iskanja rešitve ponovimo. Končno, vse kar moramo storiti je, da zgradimo površino. Če želite to narediti, najprej zgradite podatkovno matriko na novem listu (slika 70). Pojdite na nov list in vnesite naslov tabele. - 54 - Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency Fig. 70 Vrednosti X in Y se izračunajo z uporabo formul. Za vnos prve vrednosti za tlak vnesite “=”, nato pojdite na list “Experiment_2” in kliknite na celico z minimalno vrednostjo tlaka (v našem primeru je to 1) in pritisnite tipko “Enter”. Za vnos najnižje vrednosti temperature morate slediti istim korakom. Naslednje vrednosti temperature in tlaka se izračunajo po formuli: Yi = Yi −1 + Ymax − Ymin , l (7) kjer sta Xi, Yi trenutni vrednosti temperature oziroma tlaka, Xmin, Ymin sta najmanjše vrednosti temperature in tlaka, Xmax, Ymax - največja vrednost temperature in tlaka, l je korak mreže (naj bo enak 10). Vnesite formule za izračun druge vrednosti tlaka in temperature. Videti bodo nekako takole: =B4+(Poskus_2!$G$11-Poskus_2!$G$3)/10. Za vstop v tretje itd. vrednosti temperature in tlaka uporabite oznako za polnjenje. Ostane le še vnos vrednosti funkcije (5). Vzemite vrednosti koeficientov iz lista "Poskus_2". Ne pozabite, da morajo biti sklicevanja na koeficiente absolutna, sklicevanja na vrednosti temperature in tlaka pa mešana. Formula bi morala izgledati nekako takole: =Poskus_2!$B$7+Poskus_2!$B$8*$A5+Poskus_2!$B$9*B$4 +Poskus_2!$B$10*$A5^2+Poskus_2!$B$11* $ A5*B$4+ Eksperiment_2!$B$12*B$4^2 Podatki za izdelavo ploskve so pripravljeni, ostane le še, da jih vrišemo na diagram. - 55 - Copyright OJSC "CDB "BIBKOM" & LLC "Kniga-Service Agency" Uporabite "Diagram Wizard" tako, da izberete vrsto diagrama "Surface" (glej laboratorijsko delo št. 4). Končni diagram bo podoben tistemu, prikazanemu na sl. 71. Sl. 71 - 56 - Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency 1. 2. 3. 4. 5. Bibliografija Fulton, D. Samostojno obvladajte Microsoft Excel 2000. 10 minut na lekcijo. / D. Fulton. – M.: Založba Williams, 2001. – 224 str. Levin, A.Sh. Excel je zelo enostaven! / A.Sh. Levin. – Sankt Peterburg: Peter, 2004. – 74 str. Bezručko, V.T. Delavnica pri predmetu “Informatika”. Delo z Windows 2000, Word, Excel: učbenik. dodatek. / V.T. Brez rok. – M.: Finance in statistika, 2003. – 544 str. Lavrenov, S.M. Excel: Zbirka primerov in nalog. / CM. Lavrenov – M.: Finance in statistika, 2004. – 336 str. Vorobyov, E.S. Osnove računalništva. Tehnike dela v okolju MS Office. Učbenik dodatek / E.S. Vorobyov, E.V. Nikolaeva, Vorobyova F.I., Kazan. država tehn. univ. Kazan, 2005. – 84 str. - 57 - Copyright OJSC "CDB "BIBKOM" & LLC "Agency Kniga-Service" Vsebina Laboratorijsko delo št. 1 ....................... ... ................................................. ... .................. 3 Vaja 1. Osnovni pojmi, povezani z delom z Excelovimi preglednicami................. .. ........................... 3 Vaja 2. Uporaba osnovnih tehnik za preglednice: vnos podatkov v celico. Oblikovanje pisave. Spreminjanje širine stolpca. Samodokončanje, vnos formule, uokvirjanje tabele, poravnava besedila na sredino izbire, nabor indeksov in nadnapisov........................ ........... 6 Laboratorijska naloga št. 2 ................................. ................... ............................... ................ 10 Vaja 1. Krepitev osnovnih veščin dela s preglednicami, seznanitev s pojmi: razvrščanje podatkov, vrste poravnave besedila v celici, oblika števila. ................... 10 Vaja 2. Predstavitev pojma “absolutna povezava”, nastavitev natančne vrednosti širine stolpca z vodoravnimi menijskimi ukazi. Vstavljanje funkcije s čarovnikom za funkcije.................................. ................. ................................. 13 Vaja 3. Predstavitev pojma »ime celice«...................................................... .......... 16 Laboratorijsko delo št. 3. ................................. ........................ ........................ ................ 19 Vaja 1: Spreminjanje orientacije besedila v celici, seznanitev z zmožnostmi podatkovnih baz Excel. Razvrščanje podatkov po več ključih............................................. ................................................................. 19 Laboratorijske vaje št. 4 ............................................. .......... ............................................ ..... .................. 27 Vaja 1. Ustvarjanje in urejanje grafov v Excelovem dokumentu......... 27 Vaja 2. Ustvarjanje in urejanje površin v Excelovem dokumentu .. 33 Laboratorijsko delo št. 5 ............................................ .. ................................................ ........ .. 35 Vaja 1. Logični izrazi v Excelu ................................ .............................. 35 Vaja 2. Skupne funkcije v Excelu.................. ...................... ............................ .... 37 Vaja 3. Formule tabel v Excelu .......... ............................ ................... 39 Vaja 4 Distribucijske funkcije v Excelu ........................ ............................ ... 41 Vaja 5. Matrične operacije v Excelu ........... .............................. ................. 43 Laboratorijska vaja št. 6 . ............................ ...................... ................................................. 47 Vaja 1. Iskanje rešitve............................................ ................................................ 47 Laboratorij delo št. 7 .............................................. .... .............................................. .... 51 Vaja 1. Iskanje rešitve problema z dvema parametroma.................................. 51 Bibliografija ............................................................. .................................................. 57 - 58 - Copyright JSC Central Design Bureau BIBKOM & LLC Book-Service Agency Urednik: T.M. Petrova Licenca št.020404 z dne 06.03.1997 Podpisano za tisk Pisalni papir. akademska ur. l. 2005. Format tiska 60x84 1/16 konvencionalni. pečica l. Naklada 100 izvodov. Naročilo "C" 60 Založba Kazanske državne tehnološke univerze Offset Laboratorij Kazanske državne tehnološke univerze 420015, Kazan, K. Marksa, 68