Laboratórne práce v informatike. Excel Labs

Ministerstvo školstva a vedy

Ruská federácia

Federálna štátna autonómna vzdelávacia inštitúcia

vyššie odborné vzdelanie

Národná výskumná jadrová univerzita MEPhI

Volgodonský inžiniersky a technologický inštitút - pobočka NRNU MEPhI

Vytváranie tabuliek

METODICKÉ POKYNYna laboratórnu prácu

v informatike v programemicrosoftexcel

Volgodonsk 2010

MDT 519,683 (076,5)

Recenzent tech. Sciences Z.O. Kavrišvili

Kompilátor V.A. Mace

Vytváranie tabuliek. Pokyny pre laboratórnu prácu v MicrosortExcel. 2010. 13 s.

Pokyny obsahujú vysvetlenia a odporúčania pre vykonávanie laboratórnych prác na kurze informatiky v programe MicrosortExcel.

_____________________________________________________________________________

ã Volgodonský inštitút národnej výskumnej jadrovej univerzity MEPhI, 2010

ã Bulava V.A., 2010

Laboratórne práce

Vytváranie tabuliek v programeexcelautomatizáciou zadávania údajov.

Cieľ práce. Upevniť nadobudnuté znalosti z tvorby, úpravy a formátovania tabuliek v Exceli.

Formulácia problému.

Vypočítajte hodnotu funkcie r = f(X)/ g(X) pre všetkých X na intervale [ a, b] krok za krokom Komu. Význam funkcií f(X) , g(X) , hodnota koncov intervalu a A b a hodnota kroku Komu je daný z stôl 1 v Dodatku podľa možnosti pre konkrétnu špecializáciu.

Riešenie je potrebné získať vo forme tabuliek "Hlavné" a "Pomocné".

Vypočítané funkčné hodnoty pri kopírovať do stĺpca TO bez vzorcov .

Program Excel sa spúšťa pomocou príkazov Štart → Programy →mikrotriedenieexcel.

Pri vytváraní tabuľky zlúčte bunky A1:H1 v prvom riadku a do stredu umiestnite text "Tabuľky".

V druhom riadku zlúčte bunky A2: E2 a do stredu umiestnite text "Hlavné". Zlúčte bunky G2:H2 a vycentrujte text "Auxiliary"

Do bunky A3 zadajte text "Č. p / p". Do buniek B3:F3 umiestnite názvy stĺpcov, resp. X ; f(X)=…( podľa vášho výberu); g(X)=…( podľa vášho výberu); r= f(X)/ g(X).

Do buniek G3:H3 umiestnite názvy stĺpcov podľa toho: a ; Komu.

Pri automatickom vypĺňaní údajov hlavnej tabuľky vo vzorcoch používajte absolútne, relatívne a zmiešané adresovanie buniek.

V tabuľkách "Hlavná" a "Pomocná" musí byť obsah buniek zarovnaný na stred bunky a musí mať veľkosť písma 12 bodov.

Farba písma názvov tabuliek by mala byť modrá.

Vonkajšie okraje tabuliek zafarbite na modro, vnútorné okraje na zeleno a výplň bunky na žlto.

Formulár na hlásenie.

Výsledok laboratórnej práce uveďte vo forme správy v tlačenej alebo elektronickej forme.

Tlačená verzia správy musí obsahovať:

a) titulná strana

b) účel práce;

c) stanovenie úlohy;

d) výsledok úlohy.

2. Výsledok laboratórnej práce poskytnite v elektronickej podobe na 3,5-palcovej diskete ako súbor s názvom „Tabuľky“.

Kontrolné otázky.

Čo je absolútne, relatívne, zmiešané adresovanie?

Ako automaticky dopĺňa bunky číslami, vzorcami?

Aké sú spôsoby zarovnania obsahu bunky?

Ako môžem zmeniť farbu a hrúbku čiar vonkajšieho a vnútorného okraja tabuľky?

Ako môžem zmeniť farbu pozadia buniek tabuľky?

Typický príklad.

Vypočítajte hodnotu funkcie y \u003d x ∙ sin (x) / (x + 1) na segmente s krokom 0,1. Riešenie je prezentované vo forme tabuľky. Vypočítané funkčné hodnoty pri kopírovať do stĺpca TO bez vzorcov .

Riešenie.

V tomto prípade f(X) = X∙ hriech(X) , g(X) = X+1 , a =0 , b = 2 , k = 0.1

1. V prvom riadku tabuľky vyberte bunky A1:H1. Vykonajte príkaz Formát → Bunky, v okne, ktoré sa otvorí, rozbaľte kartu zarovnanie a vyberte položku zlučovanie buniek. Do stredu zlúčených buniek zadajte text "Tabuľky".

2. Podobne v druhom riadku zlúčte bunky A2: E2 a do stredu umiestnite text „Hlavný“ a zlúčte bunky G2: H2 a do stredu umiestnite text „Pomocné“.

3. Do tretieho riadku v bunke A3 zadajte text č. p / p ( názov prvého stĺpca tabuľky ) , v bunke B3 - X(názov druhého stĺpca tabuľky ), bunka C3 - f(X)= X∙ hriech(X) , v bunke D3 - g(X)= X+1 , v bunke E3 - y=f(X)/ g(X) , v bunke G3 - a, v bunke H3 - k.

4. Do bunky A4 zadajte 1 a vyplňte bunky A5:A24 číslami od 2 do 21. Ak to chcete urobiť, vyberte bunku A4 (urobte ju aktuálnou), zvýrazní sa v čiernom ráme. Umiestnite kurzor myši na značku výplne (čierny krížik v pravom dolnom rohu bunky) a stlačením pravého tlačidla myši ťahajte značku výplne pozdĺž stĺpca A tak, aby čierny rám zakrýval bunky A5:A24. Uvoľnením pravého tlačidla myši v ponuke, ktorá sa otvorí, vyberte položku vyplniť. Bunky A5: A24 budú vyplnené číslami 2; 3; 4 ...

5. Do bunky G4 zadajte hodnotu 0 (hodnota ľavého konca intervalu).

6. Do bunky H4 zadajte hodnotu 0,1 (veľkosť kroku).

7. Vyplňte stĺpec IN hodnoty X:

Do bunky B4 zadajte vzorec =$ G$4 (počiatočná hodnota x), znak $ označuje absolútne adresovanie. Do bunky B5 zadajte vzorec = B4 + $H$4. To znamená, že počiatočná hodnota x sa zvýši o hodnotu kroku;

pomocou metódy automatického dopĺňania vyplňte bunky B5:B24 týmto vzorcom. Vyberte bunku B5. Umiestnite kurzor myši na rukoväť výplne a kliknite vľavo tlačidlo myši, potiahnite rukoväť výplne tak, aby čierny rám pokrýval bunky B5:B24. Stĺpec B bude vyplnený číslami 0; 0,1; 0,2;… a príslušné vzorce budú v riadku vzorcov.

8. Do stĺpca C doplňte hodnoty funkcie f(x)=x∙sin(x). Do bunky C4 zadajte vzorec =B4∙sin(B4). Vyplňte bunky C5:C24 týmto vzorcom pomocou metódy automatického dopĺňania.

9. Do stĺpca D doplňte hodnoty funkcie g(x)=x+1. Do bunky D4 zadajte vzorec =B4+1. Vyplňte bunky D5:D24 týmto vzorcom pomocou metódy automatického dopĺňania.

10. Do stĺpca E doplňte hodnoty funkcie y=f(x)/g(x). Do bunky E4 zadajte vzorec =C4/D4, bunky E5:E24 vyplňte týmto vzorcom pomocou metódy automatického dopĺňania.

11. Zarámujme tabuľky:

12. Zmeňte farbu pozadia buniek hlavnej a pomocnej tabuľky:

vyberte hlavnú tabuľku;

zadajte príkazy ponuky Formát → Bunky → Zobraziť. V okne, ktoré sa otvorí, vyberte žltú farbu. Klikneme na tlačidlo OK.

vyberte pomocnú tabuľku a rovnakým spôsobom zmeňte farbu pozadia buniek.

13. V hlavnej tabuľke hodnoty získané ako výsledok výpočtov pri kopírovať do stĺpca TO bez vzorcov:

vyberte bunky E4:E24;

presuňte ukazovateľ myši nad obrys čierneho rámu tak, aby mal tvar šípky;

stlačením pravého tlačidla myši a bez jeho uvoľnenia presuňte ukazovateľ myši na bunku K4;

uvoľnením pravého tlačidla myši v kontextovej ponuke, ktorá sa otvorí, vyberte položku kopírovať iba hodnoty.

Výsledkom práce sú nasledujúce tabuľky:

Hlavná						Pomocný

Aplikácia

stôl 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 - tg 2 (x)
	4x 2 - 9- lg 2 (x)
	2 čos 2 (x) + 5
		cos2 (x) + x2
	2x2-sin2(x)
	4x 3 - cos 2 (x)
	3ln 2 (x) + x 2
	3 hriechy (x) – x 3	4 + x + cos 2 (x)
	4x 3 - sin 2 (x)
	5x 2 + lg 2 (x)
	2x 3 - x 2 + 7	4 čos 2 (x) + x 2
	3x 2 – 5x cos 2 (x)
	2 hriechy (x) – x 2
	3cos(x) + tg(x)
	5 + x 3 -4 lg 2 (x)
	4x3 – 2x2-7	5 čos 2(x) + 4x

Aplikácia

Tabuľka 1 pokračovala

Úloha pre študentov odboru
	f(X)	g(X)
	3x -sin 2(x)
		1 + x 2 cos 2 (x)
	12x - 3 cos 2 (x)
	5x - x 2 + 3
		5 + x 2 + 10 x
	2cos 2(x) + 5x
	2x2-sin(x)
	9x3 - cos(x2)
	5 sin 2 (x) + x 3
	3 hriechy (x) – x 3
	3x2-sin (x3)
	8x3-x2+1
	2 sin 2 (x 2) - x
	4 cos (x 3) - 3x
	4x3 - 2x2 + 7

ŠTÁTNA AKADÉMIA KOMUNIKÁCIÍ SAMARA

Katedra informatiky

POČÍTAČOVÁ VEDA

Tabuľkový hárok MS Excel

Pokyny na vykonávanie laboratórnych prác

pre študentov špecializácie OPU všetkých foriem vzdelávania

Zostavila: Makarova I.S.

Ermolenko T.I.

Samara 2006

Počítačová veda. Tabuľkový hárok MS Excel. [Text]: metodický návod na vykonávanie laboratórnych prác pre študentov odboru OP všetkých foriem vzdelávania. - 2. časť / zostavovatelia: I.S. Makarova, T.I. Ermolenko. - Samara: SamGAPS, 2006. - 44 s.

Schválené na zasadnutí odboru informatiky dňa 04.06.2006 protokol č.8.

Vydáva sa rozhodnutím redakčnej a vydavateľskej rady akadémie.

Tieto usmernenia sú praktická príručka o zvládnutí techník práce v populárnom tabuľkovom procesore Microsoft Excel. Zohľadňujú sa hlavné prvky rozhrania, techniky a technológie na prácu s údajmi potrebnými na vytváranie tabuliek, vykonávanie výpočtov a vytváranie diagramov. Zohľadňujú sa ďalšie funkcie MS Excel, ako je práca s textovými funkciami, matematické výpočty, analýza údajov. Zvládnuť prácu v tabuľkovom procesore pomôže realizácia navrhnutých praktických úloh, ktoré obsahujú podrobné pokyny krok za krokom získať konečný výsledok.

Použitie týchto pokynov predpokladá, že študenti poznajú základy práce s operačný plášť Windows.

Strih: E.A. Krasnova

Usporiadanie počítača: R.R. Abrahamyan

Podpísané na zverejnenie 15.06.06. Formát 60x90 1/16.

Písací papier. Tlač je funkčná. Konv. p.l. 2,75.

Náklad 200 kópií. Objednávka č. 118.

© Samara štátna akadémia komunikačné prostriedky, 2006

Úvod

Microsoft Excel je pomerne výkonný a ľahko použiteľný elektronický tabuľkový procesor, určený na riešenie širokého spektra plánovacích a ekonomických, účtovných a štatistických, vedeckých, technických, matematických a iných problémov. MS Excel je založený na práci s tabuľkami.

Tabuľkový hárok pozostáva z riadkov a stĺpcov, na priesečníku ktorých sa nachádzajú bunky, a v tomto zmysle je analogický s obyčajnou tabuľkou. Ale na rozdiel od obyčajného, ​​tabuľkový procesor neslúži len na vizuálnu reprezentáciu, ale aj na spracovanie numerických, textových a grafických informácií uložených v pamäti počítača. Excel môže pracovať s bunkami tabuľky rovnakým spôsobom, ako programovacie jazyky pracujú s premennými.

Excel podporuje formáty súborov označené príponou xl* a natívne dokumenty Excelu sa nachádzajú v súboroch s príponou xls.

Excel má vstavaný systém pomoci, ktorý používateľovi poskytuje Detailný popis vlastnosti balíka a ponúka ukážkové príklady na lepšie pochopenie základných princípov ich používania.

Laboratórne práce №1. Základy práce s MS Excel

Cieľ práce: zoznámiť sa so základnými prvkami tabuľkového procesora, metódami zadávania informácií do tabuliek, technikami formátovania

Pri spustení MS Excel ( Štart/Programy/Microsoft Excel ) na obrazovke sa zobrazí tabuľkové okno s načítaným dokumentom, ktorý sa nazýva Pracovný zošit (obr. 1):

Ryža. 1. Okno MS Excel

okno Excel programy obsahuje všetky štandardné prvky obsiahnuté v okne aplikácie Windows:

ikonu programu

titulný riadok;

panel s ponukami

panely nástrojov;

Stavový riadok

posuvníky.

Panel s ponukami programu Excel sa líši od panela s ponukami programu Word príkazom Údaje (namiesto Tabuľka ). Panel nástrojov má špeciálne tlačidlá pre číselné údaje - peňažný a percentuálny formát; oddeľovač tisícok; zvýšenie a zníženie bitovej hĺbky čísla; tlačidlo na zlúčenie a vycentrovanie textu v skupine buniek.

Pod panelom nástrojov je Tabuľka vzorcov, ktorý slúži na zadávanie a úpravu údajov v bunkách. Na ľavej strane riadka vzorcov je rozbaľovací zoznam − Pole s názvom, ktorý zobrazuje adresu aktuálnej bunky. V tom istom riadku sa pri zadávaní vzorcov zobrazia tri tlačidlá na ovládanie procesu zadávania.

Na priesečníku stĺpca s číslami riadkov a riadku s označením stĺpcov je tlačidlo Vybrať všetko, ktorý slúži na výber celého pracovného hárka.

Pod pracovným poľom je čiara s štítky pracovných listov.

Zvážte základné pojmy MS Excel.

Dokument programu Excel sa nazýva pracovný zošit, pozostáva zo zbierky listy. Štandardne každý zošit obsahuje 3 pracovné hárky, ale ich počet je možné zmeniť od 1 do 255. Pracovný hárok má tabuľkovú štruktúru a pozostáva zo 65 536 riadkov a 256 stĺpcov. Riadky sú očíslované a stĺpce sú označené latinkou. abecedy A, B, C, …, Z,AA, AB,AC,…,BA, BB,…,IV.

aktívny list(aktuálny hárok) zošita je hárok, na ktorom používateľ práve pracuje. Karta aktívneho hárka má vždy svetlejšiu farbu pozadia a jej názov je zobrazený tučným písmom. Kliknutím na štítky sa môžete v zošite presúvať z jedného hárka na druhý. Na pohyb v hárkoch zošita môžete použiť aj kombinácie kláves: Ctrl + Page Down a Ctrl + Page Up alebo skupinu štyroch tlačidiel umiestnených v ľavom dolnom rohu pracovného okna Excelu.

Na priesečníku riadku a stĺpca je bunka- najmenšia štruktúrna jednotka pracovného listu. Každá bunka má adresu, ktorý je vytvorený z názvu stĺpca a čísla riadku, na ktorého priesečníku sa nachádza. Adresa bunky C7 teda znamená, že táto bunka sa nachádza na priesečníku stĺpca C a riadku 7 aktuálneho pracovného hárka. V prípadoch, keď je potrebné odkazovať na bunky umiestnené na iných hárkoch, je pred adresou uvedený názov hárka, na ktorom sa nachádzajú (napríklad Hárok4!G9).

Aktívna bunka(aktuálna) je bunka, v ktorej sa nachádza kurzor myši, ktorá má tvar obdĺžnikového rámu. Do aktívnej bunky môžete zadávať údaje a vykonávať s ňou rôzne operácie.

Odkaz- spôsob určenia adresy bunky. Odkazy na bunky sa používajú ako argumenty vo vzorcoch a funkciách. Pri vykonávaní výpočtov sa na miesto prepojenia vloží hodnota v bunke, na ktorú odkaz ukazuje.

Bunkový blok(rozsah) - predstavuje obdĺžnikovú oblasť susedných buniek. Blok buniek môže pozostávať z jednej bunky, riadku (alebo jeho časti), stĺpca (alebo jeho časti) alebo postupnosti riadkov alebo stĺpcov (alebo ich častí). Blokovať adresu je kombináciou adries ľavej hornej a pravej dolnej bunky bloku oddelených dvojbodkou. Napríklad blok s adresou "A3:B5" obsahuje nasledujúcich šesť buniek: A3, A4, A5, B3, B4, B5.

Excel obsahuje viac ako 400 vstavaných funkcií. Na uľahčenie práce so vstavanými funkciami použite Sprievodca funkciou.

ÚLOHA 1. Oboznámenie sa s rozhraním programu Excel

1. Spustite tabuľku Excel . Automaticky sa otvorí dokument s názvom Kniha1.

1. Určite počet listov v Zošite1. Prilepiť cez kontextové menu Pridať… - List dva ďalšie listy. Dávajte pozor na názvy nových listov a ich umiestnenie .

2. Potiahnite záložky hárkov cez lištu kariet tak, aby boli hárky očíslované postupne.

3. Uložte zošit do svojho priečinka ako súbor s názvom tabuľka.xls.

ÚLOHA 2. Výber buniek, riadkov, stĺpcov, blokov a hárkov

2. Vyskúšajte to rôznymi spôsobmi výber fragmentov tabuľky (pozri tabuľku 1).

stôl 1

Vyberte objekt	Technika prevádzky
Bunka	Kliknite na bunku
Linka	Kliknite na príslušné číslo riadku
Stĺpec	Kliknite na príslušné číslo (písmeno) stĺpca
Blok (rozsah) susedných buniek	1. Umiestnite kurzor na začiatok výberu (ľavá horná bunka vybraného bloku). Stlačte ľavé tlačidlo myši. Potiahnite kurzor diagonálne do pravého dolného rohu vybraného bloku 2. Kliknite na bunku krajného rohu vybraného bloku, stlačte kláves Shift a kliknite na bunku v opačnom rohu
Skupina nesusediacich buniek	Vyberte prvú bunku v skupine. Stlačte a podržte kláves Ctrl Vyberte zostávajúce bunky v skupine
Bloky nesusediacich buniek	Vyberte blok susediacich buniek. Stlačte kláves Ctrl Vyberte ďalší blok buniek
Pracovný list	Kliknite na tlačidlo "Vybrať všetko" v ľavom hornom rohu pracovného hárka
Viacero súvislých pracovných listov	Vyberte prvý pracovný hárok. Stlačte kláves Shift a bez jeho uvoľnenia vyberte posledný pracovný hárok
Viaceré nesusediace pracovné hárky	Vyberte prvý pracovný hárok. Stlačte kláves Ctrl a bez jeho uvoľnenia vyberte ďalší pracovný hárok

3. Zrušte výber skupiny hárkov kliknutím na záložku ľubovoľného neaktívneho hárka.

4. Aktivujte List 2 kliknutím na jeho štítok.

5. Vyberte bunku myšou C6. Vráťte sa do cely A1 pomocou kurzorových kláves.

6. Nastaviť ako aktuálny (aktívny) List 5. Odstrániť List 5 pomocou kontextového menu.

7. Vložte nový hárok pomocou príkazu ponuky Vložiť. Pozor! Názov nového listu - List 6.

8. Pomocou myši presuňte kartu List 6 po štítku List 4.

9. Vráťte sa do List 1. Pomenujte ho pomocou kontextového menu Tabuľka.

10. Prejsť na List 2. Zvýraznite čiaru 3. Zrušte výber kliknutím na ľubovoľnú nevybranú bunku ľavým tlačidlom myši.

11. Zvýraznite stĺpec D.

12. Spoločne zvýraznite stĺpce B, C, D. Zrušte výber.

13. Vyberte rozsah buniek (blok) C4:F9 pomocou myši. Zrušte výber.

14. Vyberte blok A2:E11 po stlačení klávesu posun.

15. Vyberte súčasne nesusediace bloky A5:B5, D3:D15, H12, F5:G10.

16. Vyberte celú prácu List 2. Zrušte výber.

ÚLOHA 3. Zadávanie údajov do buniek. Formátovanie buniek

· Pri vypĺňaní buniek informáciami musíte najskôr vybrať bunku, do ktorej sa zadávajú údaje, a potom zadať údaje z klávesnice.

Po zadaní musíte stlačiť kláves Zadajte, alebo Tab, alebo ktorúkoľvek z ovládacích šípok kurzora na opravu údajov v bunke.

Ak chcete zrušiť zadávanie údajov, stlačte kláves Esc.

1. Do bunky A1 list 2 zadajte text Rok založenia školy №147.

2. Do bunky B1 vstúpte do roku založenia školy 1965.

Dôležité!

Textové údaje sú v bunke zarovnané doľava a čísla sú zarovnané doprava.

3. Venujte pozornosť skutočnosti, že text v bunke A1„nezmestil“ a odrezal vpravo. V skutočnosti je celý text stále v bunke A1, môžete si to overiť tak, že vyberiete bunku a pozriete sa na riadok vzorcov nad pracovným hárkom.

4. Zmeňte šírku stĺpca A aby bol v bunke viditeľný celý text . Ak to chcete urobiť, potiahnite pravý oddeľovač v záhlaví stĺpca (medzi písmenami A A IN v hlavičkách stĺpcov) alebo dvakrát kliknite na oddeľovač stĺpcov. Príkazy ponuky sa tiež používajú na zmenu šírky stĺpca. Formát / Stĺpec / Šírka (Šírka automatického prispôsobenia alebo Štandardná šírka).

5. Do bunky A2 zadajte text Aktuálny rok.

6. Do cely AT 2 zadajte hodnotu pre aktuálny rok.

7. Do cely A3 zadajte text Školský vek.

8. Vyberte bunku AT 3, zadajte z klávesnice vzorec na výpočet veku školy = B2- B1. V bunke sa zobrazí číselná hodnota zobrazujúca vek školy v rokoch.

Dôležité!

4Zadávanie vzorcov vždy začína znakom rovnosti = .

4Adresy buniek sa musia zadávať bez medzier latinčina písmená.

4Adresy buniek je možné zadávať do vzorcov bez použitia klávesnice jednoduchým kliknutím na ne.

9. Zmeňte šírku prvého stĺpca tak, aby sa bunka zmestila na šírku približne 10 znakov. Dá sa to urobiť „okom“ pomocou myši alebo kliknutím pravým tlačidlom myši na hlavičku stĺpca (písm A) a spustenie príkazu Šírka stĺpca... (Tým sa znova skráti text v bunkách prvého stĺpca.)

10. Vyberte blok buniek A1:A3 a vykonajte príkaz Formát / Bunky…

Prejsť na záložku zarovnanie a začiarknite políčko Pohybujte sa slovom.

11. Venujte pozornosť okrajom Horizontálne zarovnanie A vertikálne. Oboznámte sa s obsahom rozbaľovacích zoznamov týchto polí a nastavte si napríklad možnosť Vľavo A Vycentrované resp. Kliknite OK. Ako výsledok vzhľad bunky prvého stĺpca sa zlepšia.

12. Znova vyberte blok buniek A1:A3 a spustite príkaz Formát / Bunky…

13. Prejdite na záložku Písmo. Nastavte štýl Tučná kurzíva. Zmeňte farbu písma sami.

14. Prejdite na záložku vyhliadka a vyberte farbu výplne bunky.

15. Vyberte blok buniek A1:B3 a vykonajte príkaz Formát / Bunky…

16. Prejdite na záložku Hranica. Pozrite si možné typy liniek. Vyberte si typ a farbu linky. Potom kliknite Vonkajšie a/alebo Interné na nastavenie hraníc buniek (celkový pohľad je možné vidieť vo vzorovom okne). Kliknite OK.

17. Do cely D1 zadajte text Rok môjho narodenia .

18. Do cely E1 zadajte rok narodenia.

19. Do cely D2 zadajte text Aktuálny rok.

20. Do cely E2 zadajte hodnotu pre aktuálny rok.

21. Do cely D3 zadajte môj vek.

22. Do cely E3 zadajte vzorec na výpočet svojho veku.

23. Určite svoj vek v roku 2025. Ak to chcete urobiť, nahraďte rok v bunke E2 na rok 2025 . Upozorňujeme, že pri zadávaní nových údajov bola tabuľka prepočítaná automaticky.

24. Naformátujte bunky sami a naformátujte ich rovnakým spôsobom ako v predchádzajúcej tabuľke.

25. Premenovať List 2 V Skúste.

26. Uložte svoju prácu.

ÚLOHA 4. Operácie presúvania, kopírovania a odstraňovania obsahu buniek

1. Vyberte bunku A1. Skopírovať bunku A1 pomocou pravého tlačidla myši alebo tlačidla na paneli nástrojov Štandardné. Prilepte obsah bunky A1 do bunky A5 pomocou pravého tlačidla alebo klávesnice. Všimnite si, že sa skopíroval nielen obsah, ale aj prvky formátovania bunky.

2. Znova skopírujte bunku A1 do bunky A7.

3. Presuňte obsah bunky myšou A7 do bunky A9. Ak to chcete urobiť, vyberte bunku A7, presuňte kurzor myši na rám a ťahajte ho so stlačeným ľavým tlačidlom myši.

4. Vráťte obsah bunky A9 do bunky A7.

5. Skopírujte obsah bunky pomocou myši A7 do bunky A9. Ak to chcete urobiť, počas pohybu podržte stlačené tlačidlo. ctrl.

6. Používanie príkazov ponuky Upraviť / Vystrihnúť, a potom Upraviť / Vložiť presunúť obsah bunky A5 do bunky A11.

7. Vyberte bunku A11 a stlačte kláves Odstrániť. Všimnite si, že obsah bunky bol odstránený, ale formátovanie zostalo zachované. Ak ich chcete odstrániť, spustite príkaz Upraviť / Vymazať / Formáty.

8. V cele A7 zmeňte orientáciu textu tak, aby bol text pod uhlom 45° (príkaz ponuky Formát / Bunky , záložka zarovnanie).

9. V cele A9 umiestnite text vertikálne.

10. Uložte svoju prácu.

ÚLOHA 5. Automatické dopĺňanie buniek

1. Aktivujte List 3. Premenujte ho na Automatické dopĺňanie.

2. Do bunky E9 zadajte slovo: streda. Vyberte bunku. Ukážte myšou na značku automatického dopĺňania – štvorec v pravom dolnom rohu rámu. Stlačte ľavé tlačidlo myši a držte ho stlačené a posuňte myš o niekoľko riadkov nadol.

3. Znovu zvýraznite bunku E9 a potiahnite ho za rukoväť o niekoľko stĺpcov doprava.

4. Zopakujte operáciu ťahania bunky E9 pomocou značky ešte dvakrát - hore a doľava.

5. Analyzujte výsledky a vyčistite hárok. Ak to chcete urobiť, kliknite na prázdne tlačidlo v ľavom hornom rohu pracovného hárka a stlačte kláves Odstrániť.

6. Do cely A1 zadajte číslo 1. Potiahnite ho za značku nadol na 10. riadok. Analyzujte výsledok.

7. Do cely V 1 zadajte číslo 1.

8. Do cely AT 2 zadajte číslo 2.

9. Vyberte blok buniek B1:B2, potiahnite ho za rukoväť o 10 riadkov nadol. Analyzujte výsledok.

10. Do cely C3 zadajte číslo 1.

11. Potiahnite ho za značku kliknite pravým tlačidlom myši myšou o 10 riadkov nižšie. Uvoľnite ľavé tlačidlo myši a zobrazí sa kontextové menu. Vyberte príkaz z ponuky Postup…

12. V otvorenom dialógovom okne Progresia typ sady - Aritmetika , krok - 2 . Kliknite OK

13. Do cely D1 zadajte text: január. Vyberte bunku a potiahnite rukoväť o 12 riadkov nadol.

14. Do cely E1 zadajte text VAZ 2101. Potiahnite ho za značku o 12 riadkov nadol. Analyzujte výsledky.

15. Do cely F1 Kopírovať bunky . Analyzujte výsledky.

16. Do cely G1 zadajte text VAZ 2101. Potiahnite ho za značku pravým tlačidlom myši o 12 riadkov nadol. V kontextovej ponuke, ktorá sa otvorí, vyberte príkaz Vyplňte . Analyzujte výsledky.

17. Uložte výsledky.

ÚLOHA 6: Vytvorte zoznam automatického dokončovania

V predchádzajúcom cvičení ste videli, že používanie tokenu automatického dopĺňania vám umožňuje rýchlo vytvárať zoznamy, ako sú dni v týždni alebo mesiace v roku. Tieto zoznamy sú zaradené do tzv zoznamy automatického dopĺňania . Takýto zoznam si môžete vytvoriť sami a následne ho použiť pri vypĺňaní zoznamov.

1. Aktivujte hárok Automatické dopĺňanie.

2. Vykonajte príkaz ponuky Služba / Možnosti .

3. Prejdite na záložku zoznamy.

4. Kliknite na čiaru Nový zoznam v teréne zoznamy. Avšak v teréne Zoznam položiek zobrazí sa textový kurzor.

5. Napíšte na klávesnici priezviská 10 študentov z vašej skupiny (po napísaní každého priezviska stlačte kláves Zadajte). Po dokončení vytáčania stlačte tlačidlo Pridať. Zadaný zoznam bude v poli zoznamy. Kliknite OK.

6. Do cely H1 zadajte ľubovoľné priezvisko z vytvoreného zoznamu a potiahnite ho o niekoľko riadkov nadol vedľa značky. Na pracovnom liste sa zobrazí zoznam študentov.

7. Ak chcete zoznam znova upraviť, vykonajte príkaz ponuky Služba / Možnosti a prejdite na záložku zoznamy.

8. V teréne zoznamy vyberte zoznam, ktorý ste vytvorili (zobrazí sa aj v poli Zoznam položiek na pravej strane okna). Vymažte prvé priezvisko a zadajte namiesto neho priezvisko Barmalejev .

9. Stlačte tlačidlo Pridať, a potom OK.

10. Zoznam v stĺpci H nezmenila. Zamyslite sa prečo. Čo je potrebné urobiť na aktualizáciu zoznamu? Napíšte odpoveď na túto otázku do rámčeka A15.

11. Ukážte výsledok učiteľovi.

12. Odstráňte zoznam, ktorý ste vytvorili, zo zoznamu zoznamov.

13. Uložte svoju prácu.

ÚLOHA 7. Plánovanie

1. Aktivujte hárok 4. Premenujte ho na Rozvrh.

2. Do bunky A1 zadajte text Rozvrh tried pre skupinu # (uveďte číslo vašej skupiny) na aktuálny týždeň.

3. Do buniek A3-A6 zadajte hodiny (8:30 - 10:00, 10:15 - 11:45 atď.)

4. Do buniek B2 - F2 zadajte názvy dní v týždni (použite značku automatického dopĺňania).

5. Do tabuľky doplňte názvy predmetov pomocou kopírovacích techník.

6. Označte bunky prvého riadku A1 - F1 a zlúčte pomocou príkazu ponuky Formát / Bunky (záložka zarovnanie) alebo pomocou tlačidla Zlúčiť a umiestniť do stredu.

7. Pomocou príkazu upravte štýl hlavičky tabuľky Formát / Bunky.

8. Ozdobte hlavné pole rozvrhu pomocou okrajov a výplní.

9. Uložte svoju prácu.

10. Ukážte svoju prácu učiteľovi.

Laboratórne práce excel

Laboratórium č. 1

Vytvorte zoznam zákazníkov

Zadajte zoznam 15 firiem. Firmy sú rozdelené do 5 miest. Po zadaní prvého záznamu kliknite na tlačidlo Pridať.

Formátovanie tabuľky. Pre bunky I2-I14 nastavte percentuálny štýl (ak to chcete urobiť, vyberte tento rozsah a kliknite na tlačidlo Formát percent na paneli nástrojov Formátovanie).

Triedenie údajov. Treba vybrať z ponuky Údaje  Triedenie. V dialógovom okne vyberte prvé kritérium triedenia kód a druhé kritérium Mesto A OK. Filtrovanie údajov. Vyberte si z ponuky Údaje  Filter/Atofilter. Keď kliknete na názov tohto príkazu, v prvom riadku vedľa nadpisu každého stĺpca sa zobrazí tlačidlo so šípkou. Môže sa použiť na otvorenie zoznamu obsahujúceho všetky hodnoty polí v stĺpci. Vyberte názov jedného z miest Mesto. Okrem hodnôt polí obsahuje každý zoznam ďalšie tri prvky: (Všetky), (Prvých 10…) a (Podmienka…). Element (všetky) je určený na obnovenie zobrazenia všetkých záznamov na obrazovke po použití filtra. Element (Prvých 10...) poskytuje automatické zobrazenie prvých desiatich záznamov v zozname. Ak sa zaoberáte zostavovaním všetkých druhov hodnotení, hlavnou úlohou ktorá je určená na určenie prvej desiatky, použite túto funkciu. Posledný prvok sa používa na vytvorenie komplexnejšieho výberového kritéria, v ktorom možno použiť podmienené operátory. A A ALEBO. Umiestnite kurzor do ľubovoľnej vyplnenej bunky a urobte nasledovné: v ponuke Formátovať  Automatický formát  Zoznam 2 .

Vytvorenie zoznamu produktov

Druhý zoznam bude obsahovať údaje o produktoch, ktoré ponúkame.

Laboratórium č. 2

Objednávky hárkov

Premenujte pracovný hárok ListZ adresované objednávky.

Do prvého riadka zadajte nasledujúce údaje, ktoré budú v budúcnosti tvoriť názvy polí:
A1  Mesiac objednávky , V 1  dátum objednávky , S 1  Číslo objednávky , D1  Číslo položky , E1  Názov produktu , F1  Množstvo , G1  cena za jeden ., H1  Kód spoločnosti zákazníka ., ja1  Názov spoločnosti zákazníka , J1  Cena objednávky , K1  zľava (%) , L1  Celkovo zaplatené .

Pre prvý riadok urobte zarovnanie údajov v centre Formátovať bunky zarovnanie transponovať slovami .

Vyberte stĺpce jeden po druhom B, C, D, E, F, G, H, I, J, K, L a vstúpiť do lúka názov mená Dátum, Objednávka, Číslo2, Položka2, Množstvo, Cena2, Kód2, Spoločnosť2, Suma, Zľava2 A Platba .

Zvýraznite stĺpec IN a vykonajte príkaz ponuky Formátovať bunky. V záložke číslo vyberte
Číselný formát dátum a v teréne Typ vyberte formát ako HH.MM.YY. Na konci dialógu
kliknite na tlačidlo OK.

Zvýraznite stĺpceG, J, L a vykonajte príkaz ponuky Formátovať bunky. V záložke číslo
vyberte Číselný formát Peňažné , naznačiť Počet desatinných miest rovná 0 a v poli
Výber označenia $ Angličtina (USA). Na konci dialógového okna kliknite na tlačidlo OK.

Vyberte stĺpec K a vykonajte príkaz ponuky Formátovať bunky. V záložke číslo vyberte
Číselný formátPercento , naznačiť Počet desatinných miest rovný 0. Na konci
dialógové tlačidlo kliknutia OK.

V bunke A2 musíte zadať nasledujúci vzorec:

=AK(ISBLANK($B2)," ";SELECT(MONTH($B2),"Január","Február","Marec","Apríl";"máj"; "jún"; "júl"; "august"; "september"; "október"; "november"; "december")) (3.1)

A vyplňte bunku žltou farbou.

Vzorec (3.1) funguje nasledovne, najprv sa skontroluje podmienka prázdnoty bunky A2. Ak je bunka prázdna, vloží sa medzera, inak pomocou funkcie SELECT vyberte požadovaný mesiac zo zoznamu, ktorého počet je určený funkciou MONTH.

Ak chcete získať vzorec (3.1) urobte nasledovné:

aktivovať bunku A2 a zavolajte funkciu AK;

v okne funkcie IF v poli Booleovský_výraz zadajte ručne $ B2="", V

lúka hodnota_ak_pravda napíš " " , v teréne hodnota_ak_nepravda zavolajte funkciu SELECT;

v okne funkcií VOĽBA v teréne hodnota1 typ " január", v teréne hodnota2 vytlačiť

v teréne index_číslo a zavolajte funkciu MESIAC;

v okne funkcie MONTH v poli dátum_ako_číslo vytočte adresu $ B2 ;

Kliknite na tlačidlo OK.

Do bunky E2 napíšeme nasledujúci vzorec:

=AK($ D2=" "; “ ”;LOOKUP($D2;Číslo produktu; Názov produktu) (3.2)

Pravidlo sady vzorcov:
Kliknite do bunky E2. Umiestnite kurzor na ikonu panela Štandard. Otvorí sa okno Majster funkcie..., vyberte funkciu IF. Postupujte podľa krokov, ktoré vidíte na obrázku

Tie. v pozícii Logický_výraz kliknite na bunku D2 a trikrát stlačte kláves F4 - získajte $D2, napíšte =" ", pomocou klávesu Tab alebo myši prejdite na pozíciu hodnota_ak_pravda a vytočte. " ", prejdite na pozíciu hodnota_ak_nepravda– kliknite na tlačidlo vedľa názvu funkcie a vyberte príkaz Viac funkcií.. → Kategórie → Referencie a polia, v okne Funkcie → VYHLIADKA→ OK → OK.

Otvorí sa okno funkcie VYHLIADKA. V pozícii Lookup_value kliknite na bunku D2 a trikrát stlačte kláves F4 - získajte $D2, pomocou klávesu Tab alebo myši prejdite na pozíciu Zobrazený_vektor a kliknite na štítok listu " Tovar“, vyberte rozsah buniek A2:A12, stlačte kláves F4, prejdite na pozíciu vektor_výsledku– znova kliknite na štítok listu “ Tovar“, vyberte rozsah buniek Q2:W12, stlačte kláves F4 a OK. Ak ste urobili všetko správne, zobrazí sa v bunke # HD.

S

vyplniť bunku žltá farba.

10. Do cely G2 napíšeme nasledujúci vzorec:

=AK($D2=" ";" ";LOOKUP($D2;Číslo produktu; Cena)) (3.3)

Vytvorte výplň bunky žltá farba.

11. Do cely ja2 napíšeme nasledujúci vzorec:
=IF($H2=" ";" ";LOOKUP($H2; kód; Firma)) (3.4)
Vytvorte výplň bunky žltá farba.

12. Do cely J2 napíšeme nasledujúci vzorec:
=IF(F2=" ";" ";F2* G2) (3.5)
Vytvorte výplň bunky žltá farba..

13. Do cely K2 napíšeme nasledujúci vzorec:
=IF($H2=" ";" ";LOOKUP($H2; kód; Zľava)) (3.6)
Vytvorte výplň bunky žltá farba.

14. Do cely L2 napíšeme nasledujúci vzorec:
=IF(J2=" ";" ";J2- J2* K2) (3.7)
Vytvorte výplň bunky žltá farba.

15. Bunky B2, D2 a H2 - v ktorých nie sú žiadne vzorce, vyplňte Modrá farba. Zvýraznite rozsah A2 - L 2 a plniaca rukoväť ( čierny kríž v pravom dolnom rohu bloku ) vytlačiť výplň a vzorce až do 31 zahrnuté linky.

16. Aktivujte bunku AT 2 a potiahnite nadol rukoväť výplne do bunky VZ1 vrátane.

17. Do cely C2 zadajte číslo 2008-01, ktoré bude počiatočným číslom objednávky a potiahnite značku výplne nadol do bunkyCZ1 vrátane.

18. Teraz je potrebné vyplniť stĺpce z klávesnice Q2:W31 , D2: D31 A H2:H31. S AT 2 Autor: O 11 zhromažďujeme januárové dátumy (napríklad 01/2/08, 01/12/08). S O 12 Autor: O 21 zbierame februárové termíny (napríklad 12.02.08, 21.02.08) a od r. B22 Autor: B31 zhromažďujeme marcové dátumy (napríklad 03.05.08, 03.06.08). IN D2: D31 vytáčame čísla tovaru t.j. 101, 102, 103, 104, 201, 202, 203, 204, 301, 302 a 303. Čísla sa môžu opakovať a ísť v ľubovoľnom poradí, podobne v H2:H31 vstúpiť Kódy vaše firmy, ktoré ste napísali na hárok klientov. na stĺpec F vstúpiť dvojciferný čísla.

19.

(SRSP) Laboratórium č. 3

Objednávka

Do bunky H5 zadajte záznam kód a v bunkeja5 vložte vzorec
=AK($E$3=" "; “ ”;ZOBRAZIŤ($E3 doláre;Objednávka; kód2)) Do bunky C7 zadajte vstup Názov produktu. Bunka E7 musí obsahovať vzorec
=AK($ E$3=" "; “ ”;ZOBRAZIŤ($ E3 doláre;Objednávka; Produkt2)),
a bunky E7, F7, G7 priradiť podčiarknutie a centrovanie. Do bunky H7 zadajte znak № a v bunkeja7 - vzorec:
=AK($ E$3=" "; “ ”;ZOBRAZIŤ($ E3 doláre;Objednávka; Číslo 2)) Do bunky C9 zadajte vstup Objednané množstvo. Do bunky E9- vzorec
=AK($ E $3=" "; “ ”;ZOBRAZIŤ($ E3 doláre;Objednávka; Množstvo)) Do bunky F9 – záznam Jednotky podľa ceny a zarovnajte ho do stredu stĺpcov F A G. Bunka H9 musí obsahovať vzorec
=AK($ E $3=" "; “ ”;ZOBRAZIŤ($ E3 doláre;Objednávka; Cena 2)),
táto bunka by mala mať podčiarknutie a štýl meny. Do bunky ja9 – záznam za jednotku Zadajte C11 text Celková hodnota objednávky, a v E11 vložte vzorec
=AK($ E $3=" "; “ ”;ZOBRAZIŤ($ E3 doláre;Objednávka; Súčet)),
Do bunky F11 – záznam zľava (%). Zlatý klinec F11, G11, H11 a kliknite na tlačidlo Zlúčiť a vycentrovať . Do bunky ja11 vložte vzorec
=AK($ E$3=" "; “ ”;ZOBRAZIŤ($ E3 doláre;Objednávka; zľava2)),
a nastavte možnosti formátovania: štýl podčiarknutia a percent. Do bunky C13-text Zaplatiť. A v celeD13 uverejnite nasledujúci vzorec
=AK($ E$3=" "; “ ”;ZOBRAZIŤ($ E3 doláre;Objednávka; platba)),
a nastavte možnosti formátovania: štýl podčiarknutia a meny. Do bunky E13 zadajte vstup Navrhol:, Zlatý klinec E13, F13 a nastavte centrovanie textu. Potom vyberte G13, H13,ja13 a nastavte ich tak, aby boli vycentrované a podčiarknuté. Nakoniec nastavte šírky stĺpcovB A J rovná 1,57, zvýraznenie B2- J14 a nastavte rám pre celý rozsah. Teraz v E3 naznačiť Číslo objednávky a pred vytlačením formulára priezvisko.

Prácu ste úspešne dokončili, odovzdajte ju učiteľovi!

kontingenčnej tabuľky

Bol vytvorený zoznam zákaziek pre praktické využitie a jeho údaje sú predmetom analýzy. Sprievodca kontingenčnou tabuľkou nám pomôže vykonať analýzu.

Kontingenčné tabuľky sa vytvárajú zo zoznamu alebo databázy.

8. Prácu ste úspešne dokončili, odovzdajte ju učiteľovi!

(SRSP) Lab. č. 4. Pobočky

Vytvorte zošit a uložte ho do priečinka ako Pobočky (vaše priezvisko). Začnime príkladom vytvorením tabuľky a zadaním údajov o každej vetve.

Prípravná fáza. Kopírovať do schránky z listu Tovar knihy objednávkyúdaje o tovare, jeho počte a cenách, t.j. kopírovať rozsah buniek A1-C12 list Tovar.

Prejdite na prvú stranu knihy Pobočky a do cely A3 prilepte skopírovaný fragment tabuľky. V 3 poradí do buniekD3, E3, F3 zadajte položky podľa toho Počet objednávok, predané množstvo A Objem predaja. Nastavte centrovanie textu v bunkách a povoľte zalamovanie slov.

Do bunky F4 dajte vzorec: \u003d C4 * E4 a skopírujte ho do buniek F5- F14 .

Napíšte do bunky B15 slovo Celkom: a v bunkeF15 vložte vzorec súčtu alebo kliknite na tlačidlo na paneli nástrojov Štandardné. excel určí rozsah buniek, ktorých obsah by mal byť zhrnutý.

Takýchto hárkov by malo byť toľko, koľko miest máte v hárku klientov. Tento list musíme skopírovať 4-krát.

Ak to chcete urobiť, umiestnite kurzor myši na jeho štítok a stlačte pravé tlačidlo manipulátora. V kontextovej ponuke vyberte príkaz Presunúť/Kopírovať, v zobrazenom dialógovom okne zadajte hárok, pred ktorý sa má kópia vložiť, aktivujte možnosť Vytvorte kópiu a stlačte OK. Kopírovanie pomocou myši je oveľa jednoduchšie: umiestnite ukazovateľ myši na záložku hárka a presuňte ho do polohy kopírovania a vloženia, pričom držte stlačený kláves [ ctrl] .

Názvy pracovných hárkov sa zhodujú s názvami Mestá z listu klientov, Napríklad, Almaty, Astana, Shymkent, Aktau, Karaganda alebo iné mená. Do bunky zadajte názov pobočky zodpovedajúci názvu hárku A1 tento list.

Vyplňte hárok objednávky ešte jeden stĺpec. Do bunky M1 zadajte slovo Mesto. Do bunky M2 zadajte vzorec =IF(ISBLANK($ H 2);“ ;VYHĽADAŤ($ H2; kód; Mesto)) , rozšírte tento vzorec na riadok 31 tohto stĺpca.

Vyberte si z ponuky Údaje  Filter/Atofilter. Vyberte v stĺpci Mesto prvá vetva. Údaje stĺpcaMnožstvo list objednávky zadáte vy do stĺpcaPredané množstvo knižný list pobočky, v riadkoch zodpovedajúcich číslam položiek. Ak sa tovar s rovnakým číslom predáva v rôznych mesiacoch, berie sa jeho celkové množstvo. A tak sa plnia hárky všetkých miest.

Konsolidácia údajov. Kópia z prvej strany knihy Pobočky rozsah A3-B14, prejdite na pracovný hárok 6 a vložte ho do bunky A3.

Prejdime ku konsolidácii. Nastavte ukazovateľ bunky naC3 a vyberte z ponuky Údaje  Konsolidácia.

Uvedené Funkcie prvok musí byť vybraný Sum. Zadajte do vstupného poľa Odkaz rozsah buniek, ktorých údaje sa majú konsolidovať. Je vhodné označiť rozsah buniek myšou.

Umiestnite vstupný kurzor do poľa Odkaz, kliknite na označenie prvého mesta, napr. Almaty, vyberte rozsah buniekD3- F14 a stlačte tlačidlo Pridať okno Konsolidácia. V dôsledku toho sa zadaný rozsah v teréne preusporiada Zoznam rozsahov.

Potom prejdite na list druhého mesta. Rozsah sa zobrazí automaticky, stlačte tlačidlo Pridať a tak 5 krát.

Ak horný riadok a (alebo) ľavý stĺpec obsahujú nadpisy, ktoré je potrebné skopírovať do konečnej tabuľky, aktivujte príslušné možnosti v skupine Používajte štítky. Keďže v našom príklade horný riadok obsahuje nadpisy stĺpcov, musíme túto možnosť aktivovať Na hornom riadku.

Ak sa má vytvoriť dynamický vzťah medzi zdrojovými údajmi a údajmi konsolidovanej tabuľky, povoľte túto možnosť Vytvorte odkazy na zdrojové údaje.

Tlačidlo Preskúmanie by sa mal použiť na výber súboru, ktorý obsahuje údaje, ktoré sa majú konsolidovať.

Kliknite na tlačidlo OK.

Do bunky A1 zadajte názov novej tabuľky Konečné údaje.

Napíšte do bunky B70 význam Celkom:, a v E70 - a stlačte kláves [ Zadajte]

Teraz pristúpime k určeniu podielu na celkovom zisku zo sumy získanej z predaja každého produktu. Zadajte F9 vzorec = E9/$E$70 a skopírujte ho do zvyšku stĺpca F ( do cely F70) .

Formátovať obsah stĺpcaF v percentuálnom štýle. Získané výsledky nám umožňujú vyvodiť závery o popularite konkrétneho produktu.

Pri konsolidácii údajov program zapíše každý prvok do výslednej tabuľky a automaticky vytvorí štruktúru dokumentu, čo umožňuje zabezpečiť, aby sa na obrazovke zobrazovali len potrebné informácie a nepotrebné detaily boli skryté. Symboly štruktúr sú zobrazené naľavo od tabuľky. Čísla označujú úrovne štruktúry (v našom príklade - 1 A 2). Tlačidlo so znamienkom plus umožňuje dešifrovať údaje vyššej úrovne. Kliknite napríklad na tlačidlo pre bunku A9 získať informácie o jednotlivých objednávkach.

Skopírujte vzorec zF9 do buniek F4- F8.

Čísla sa menia na grafy

Prípravné práce. Keďže každý graf potrebuje svoju vlastnú tabuľku, vytvorme novú kontingenčnú tabuľku založenú na údajoch hárka objednávky rovnomenná kniha objednávky. Otvorte predtým vytvorený zošit objednávky. Vytvorte nový zošit a pomenujte jeho prvý hárok Tabuľka . Tento hárok bude obsahovať číselný materiál pre graf. Umiestnite ukazovateľ do bunky AT 3 a vyberte ponuku Údaje  Kontingenčná tabuľka. Vyberte prvý spôsob usporiadania údajov − V zozname alebo databáze Microsoftexcel- stlač tlačidlo Ďalej. V druhom kroku umiestnite vstupný kurzor do poľa Rozsah nasleduje menu okno prejdite na pracovný zošit Objednávky a pracovný list objednávky a zvýraznite rozsahA 1- L 31 . Potom, čo stlačíme tlačidlo Ďalej. Štruktúra by mala byť definovaná kontingenčnej tabuľky. Umiestnite do oblasti linky tlačidlo Názov produktu a v oblasti stĺpci - tlačidlo mesiac. Sum bude vypočítaná podľa poľa Cena objednávky, tie. presuňte toto tlačidlo do oblasti údajov . Kliknite na tlačidlo Pripravený. Zvýraznite rozsahB 4- F 14 . Ak vyberáte rozsah buniek myšou, začnite výber v ktorejkoľvek bunke v rozsahu okrem bunky F 4 A, ktorá obsahuje tlačidlo kontingenčnej tabuľky. Kliknite na tlačidlo Sprievodca grafom na paneli nástrojov Štandardné. V prvom kroku špecifikujte typ grafu, kliknite na tlačidlo Ďalej. Potvrďte v druhom kroku rozsah =Tabuľka!$ B$4:$ F$15. V treťom kroku špecifikujte možnosti grafu (Tituly, Sekery, Legendy atď.).Názov grafu vstúpiť Objem predaja podľa mesiaca,Kategórie (X)- Názov produktu A Význam ( Y ) –Objem predaja (Americký dolár) . Vykonané zmeny sa okamžite prejavia na obrázku v poli. ukážka, kliknite na tlačidlo Ďalej. Kliknite na tlačidlo Pripravený.

Laboratórium č. 1

Účel práce: naučiť sa pracovať s tabuľkami a naučiť sa zostavovať rôzne diagramy.

Stručné teoretické informácie

Excel je tabuľkový procesor.

Rozhranie okna aplikácie Excel je podobné rozhraniu okna aplikácie Word (záhlavie, riadok s ponukami, panely nástrojov, stavový riadok). Ale je pridaný riadok vzorcov, ktorý nie je vo Worde.

Existujú dva typy zobrazenia dokumentu programu Excel – „normálne“ a „rozloženie strany“, ktoré je možné nastaviť v ponuke Zobraziť.

Nastavenia stránky sa konfigurujú v ponuke Súbor/Nastavenia stránky. Tu môžete nastaviť aj hlavičku a pätu na stránke. V hlavičke môžete zadať napríklad číslo skupiny, v dolnej časti - celé meno študenta. Na karte "Hárok" môžete nakonfigurovať poradie, v ktorom sa stránky zobrazujú.

Pracovný zošit. Excelový dokument je pracovný zošit , pozostávajúce zo súpravy listy uložené na disku v jeden súbor . Štandardne má zošit 3 listy. Toto číslo je možné zmeniť (až 255) v záložke Nástroje/Možnosti/Všeobecné. Taktiež môžete do knihy kedykoľvek pridať alebo odstrániť listy (cez kontextové menu s pravým tlačidlom). Listy v knihe je možné lepiť (Kn. Shift + kliknutia na tie listy, ktoré je potrebné prilepiť). Informácie napísané na nalepených listoch sú rovnaké. Ak napríklad potrebujete vytvoriť rovnakú šablónu tabuľky na niekoľkých hárkoch, musíte ich zlepiť, raz vytvoriť tabuľku a potom hárky „rozdeliť“ prostredníctvom kontextovej ponuky. Všetky listy, ktoré boli zlepené, budú mať rovnakú tabuľku.

Okrem pracovných hárkov môže zošit ukladať grafy založené na údajoch z jednej alebo viacerých tabuliek a makrá. Makro je program VisualBasic, ktorý spracováva údaje tabuľky.

Medzi dokumentmi zošita je možné vytvoriť prepojenia a zmeny vykonané v jednej tabuľke sa automaticky zafixujú vo všetkých súvisiacich dokumentoch. Excel spracováva aj dáta pripravené rôznymi aplikáciami Windows.

Pracovný list. Zahŕňa elektronické bunky s adresou: A1, B10 atď. Adresa aktuálnej bunky sa zobrazí v poli názvu (pole úplne vľavo na riadku vzorcov). Pracovné hárky obsahujú 256 stĺpcov a 65536 riadkov. Záhlavia stĺpcov –A…Z,AA…AZ,BA…BZ. Hlavičky riadkov: 1 až 65536.

Mobilné dáta. Je možné zadať dve bunky milý údaje: konštantné hodnoty a vzorce . Konštantné hodnoty sa zadávajú priamo do bunky, pri kopírovaní sa nemenia. Vzorce sa používajú na organizáciu výpočtov. Pri kopírovaní vzorcov dátové hodnoty zmena v bunkách.

Existujú dva zastupovanie údaje bunky: v stroji a na obrazovke . In-machine sa používa na výpočty, sú to vnútorné hodnoty buniek a nezobrazujú sa na obrazovke. Reprezentácia zobrazenia je určená formátom bunky.

Bunky môžu obsahovať nasledovné dátové typy :čísla, text, dátum a čas, boolovské hodnoty, chybové hodnoty.

čísla. Čísla sú uložené v stroji s najvyššou presnosťou. Zobrazenie čísla na obrazovke je určené formátom: Formát/ Bunky/ Číslo/ Číselné formáty. Môžete zadať celé čísla, desatinné miesta alebo čísla v exponenciálnom (exponenciálnom) tvare. Ak je bunka vyplnená znakmi (ostrý), znamená to, že zadané číslo presahuje šírku stĺpca.

Text . Ide o akúkoľvek množinu zadaných znakov, ktoré Excel neinterpretuje ako číslo, dátum a čas, boolovskú hodnotu alebo chybovú hodnotu. Do jednej bunky môžete zadať až 255 znakov textu. Ak chcete zadať číslo ako text do vzorca, musíte ho uzavrieť do úvodzoviek. "45,00".

Formátovanie textu: Formát/Bunky/Zarovnanie kariet, Písmo, Orámovanie, Vzhľad.

dátum a čas .Dátum je v stroji reprezentovaný ako číslo určené počtom dní od systémového dátumu (1900) po dátum uvedený v

bunka. Vidno to, ak v bunke s dátumom vyberiete formát "Všeobecné". Dátum 22. 1. 2005 zodpovedá počtu 38 374 dní od 1. 1. 1900 a dátum 7. 1. 2005 zodpovedá číslu 38 359 dní od 1. 1. 1900. Preto je možné vykonávať operácie sčítania a odčítania na dátumoch (v bunkách s dátumom „01/15/1900“ a číslom „15“ je vzorec =A1-B1, ktorý vypočíta počet dní medzi dátumami „01 /22/2005-01/07/2005". Rozdiel je 15 ). Čas je v stroji znázornený ako zlomok. Môžete to vidieť aj vtedy, ak v bunke v priebehu času vyberiete formát "Všeobecné". Čas 16:14 je ekvivalentný zlomku 0,6763889.

Zobrazenie dátumu a času je tiež definované v menu Formát/Bunky/Číslo/Formáty čísel. Ak chcete rýchlo zadať aktuálny čas do bunky, stlačte Ctrl +<:>a pre aktuálny dátum –Ctrl+<;>.

Booleovské hodnoty mať hodnoty „pravda“ a „nepravda“. Tieto hodnoty sú výsledkom logických a porovnávacích operácií.

Chybné hodnoty sú výsledkom chybných výpočtov. Chybné hodnoty začínajú ostrým znamienkom: n/a! (neplatná hodnota), link! (neplatný odkaz), hodnota! (nesprávny typ argumentu vo funkcii), meno! (nerozumie názvu), číslo! (nedokáže správne interpretovať vzorec v bunke) atď.

Rozsah buniek je skupina po sebe nasledujúcich buniek. Odkazy na rozsah používajú nasledujúce operácie adries:

: (dvojbodka) - umožňuje odkazovať na všetky bunky medzi hranicami rozsahu vrátane

významný (A1:B15);

, (čiarka) - operátor na kombinovanie rozsahov buniek alebo jednotlivých buniek

ب (medzera) je priesečník, ktorý odkazuje na bunky spoločného rozsahu,

Β5:B15 x A7:D7. V tomto príklade je bunka B7 zdieľaná medzi dvoma rozsahmi.

Zadávanie, úprava a formátovanie údajov.

Rozlišujte medzi priamym zadávaním údajov a používaním automatizačných nástrojov na zadávanie.

Priamy – priame zadávanie údajov do aktuálnej bunky. Ak chcete dokončiť zadávanie v aktuálnej bunke a presunúť sa do ďalšej bunky, stlačte jeden z nasledujúcich klávesov

Keď vstúpite rovnaké údaje do rozsahu je potrebné: ​​Vyberte rozsah - Zadajte údaje do aktívnej bunky rozsahu - stlačte Ctrl + Enter.

Automatizácia vstupu.

Úprava.

Úpravy možno rozdeliť do dvoch skupín:

Úprava zavedené do bunky údajov . Obsah buniek je možné upravovať ako priamo v bunke (dvojité kliknutie na bunku), tak aj vo vzorcovom riadku (kliknutie na pravú stranu riadka vzorcov), pričom v stavovom riadku sa zobrazí slovo „Upraviť“. V tomto režime sú dostupné všetky nástroje na úpravu.

Úpravy na úrovni buniek, rozsahov, riadkov, stĺpcov. V podstate ide o editačné príkazy menu „Upraviť“ a „Vložiť“.

Formátovanie.

Všetky príkazy na formátovanie údajov, riadkov, stĺpcov, hárkov atď. sú sústredené v menu "Formát".

Grafy vexcel.

Diagram obsahuje mnoho objektov, z ktorých každý je možné vybrať a upraviť (upraviť a formátovať) samostatne. Pri presúvaní ukazovateľa myši nad graf sa vedľa neho zobrazí popis, ktorý označuje typ objektu, v blízkosti ktorého sa ukazovateľ nachádza.

Objekty grafu .Os(X je os kategórie, Y je os hodnôt). dátový bod– jedna údajová položka, napríklad mzda za január. Rad údajov- súbor údajových bodov (jasne viditeľný na grafe - všetky body radu údajov sú spojené jednou čiarou). Legenda– ikony, vzory, farby používané na rozlíšenie radov údajov. dátová značka– predstavuje dátový bod na mape ako obdĺžnik, sektor, bod atď., typ značky závisí od typu mapy; všetky značky rovnakého dátového radu majú rovnaký tvar a farbu. Text– všetky štítky (názov grafu, hodnoty a kategórie na osiach) a štítky (test spojený s dátovými bodmi); pre titulky môžete použiť ikonu "caption" na paneli kreslenia alebo vytvoriť plávajúce text : kliknite na jeden z riadkov s údajmi - zadajte test (zobrazí sa v riadku vzorcov) - stlačte "Enter".

pravidlá , používa Excel predvolená pri zostavovaní schém.

1. Excel predpokladá, že séria údajov na graf je pozdĺž dlhšej strany vybratého rozsahu buniek.

2. Ak je vybratý štvorcový rozsah buniek alebo zaberá viac buniek na šírku ako na výšku, názvy kategórií budú umiestnené v hornom riadku rozsahu. Ak je viac buniek na výšku ako na šírku, názvy kategórií idú v ľavom stĺpci dole. A ak bunky, ktoré Excel použije ako názvy kategórií, obsahujú čísla (nie text alebo dátumy), potom Excel predpokladá, že tieto bunky obsahujú sériu údajov a očísluje názvy kategórií ako 1, 2, 3, 4 atď.

3. Excel predpokladá, že názvy pozdĺž krátkej strany výberu by sa mali použiť ako popisky legendy pre každý rad údajov. Ak existuje iba jeden rad údajov, Excel použije tento názov ako názov grafu. A ak bunky, ktoré má Excel v úmysle použiť ako menovky legendy, obsahujú čísla (nie text alebo dátumy), potom Excel predpokladá, že tieto bunky obsahujú prvé body radu údajov a každému radu údajov priradí názov: „Series1“, „ Séria 2” atď. d.

Makrá. Slúži na automatizáciu opakujúcich sa operácií v Exceli. Makro pozostáva zo sekvencie interných príkazov Excelu (makro). V Exceli sa makro vytvorí pomocou príkazu Nástroje/Makro/Spustiť nahrávanie. Tento príkaz vám umožňuje vytvoriť makro pomocou záznamníka makier (spôsob nahrávania programu). Paralelne s používateľskými akciami záznamník makier zaznamenáva akcie používateľa a automaticky ich prekladá do vlastného jazyka makier. Týmto spôsobom môžete vytvárať relatívne jednoduché programy, ktoré sa spúšťajú bez zásahu používateľa.

Príklad: použitie makro rekordéra na vytvorenie makra, ktoré vytvorí diagram dynamiky miezd Ivanova A.P. podľa mesiacov. Na to potrebujete:

Zadania na laboratórnu prácu č.1.

Vytvorte tabuľku podľa pokynov učiteľa.

Na základe tejto tabuľky vytvorte dva diagramy:

1. histogram s jednou osou y;

   graf s hlavnou a vedľajšou osou Y, pričom prezentuje dva dátové rady vo forme grafov.

Vytvorte zmiešaný graf, v ktorom je jedna séria údajov prezentovaná ako histogram a druhá séria údajov ako graf. Nastavte rad údajov v editore Word, uložte súbor s príponou .txt a potom importujte tento súbor z programu Excel. Údaje poskytuje inštruktor.

Vytvorte makro (podľa pokynov učiteľa).

Copyright JSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" Federálna agentúra pre vzdelávanie Štátna vzdelávacia inštitúcia vyššieho odborného vzdelávania "Kazanská štátna technologická univerzita" Laboratórne práce na informatike Pokyny MS EXCEL Kazaň 2006 Copyright JSC "Central Design Bureau" BIBCOM" & LLC "Kniga-Service Agency" MDT 658.26:66.094 Zostavil: Assoc. E.S. Vorobjov, doc. E.V. Nikolaeva, Assoc. F.I. Vorobieva Laboratórne práce v informatike. MS Excel: Metóda. inštrukcie / Kazaň. štát technol. un-t; Porov.: E.S. Vorobyov, E.V. Nikolaev, F.I. Vorobjov. - Kazaň, 2006. - 58 s. Sú načrtnuté základné metódy práce v balíku MS Excel, postup a pravidlá tvorby a úpravy tabuľky a grafy, vykonávanie základných výpočtov, triedenie a filtrovanie údajov, analyzovanie a sumarizácia údajov a používanie logických výrazov, súhrnných a distribučných funkcií a maticových operácií. Samostatná laboratórna práca je venovaná hľadaniu riešenia jedno- a dvojparametrovej úlohy. Môžu byť použité pri štúdiu odborov "Informatika", "Využitie počítačov v technike" a "Využitie počítačov vo výpočtoch", môžu slúžiť ako príručka pre mimoškolskú prácu študentov a môžu byť použité aj odborníkmi všetkých predmetná oblasť pre samostatný rozvoj výpočtovej techniky. Určené na plný úväzok a korešpondenčné formuláreškolenia odborov 240802.65 "Základné procesy chemickej výroby a chemickej kybernetiky" a 240801 "Stroje a prístroje chemickej výroby", študenti v odbore 2480800 "Procesy šetriace energiu a zdroje v chemická technológia petrochémia a biotechnológia“. Il. 68, tab. 1, bibliografia. 5 mien Spracované na Katedre všeobecnej chemickej technológie. Publikované rozhodnutím redakčnej a vydavateľskej rady Kazanskej štátnej technologickej univerzity. Recenzenti: B.K. Kurbatov, docent KSTU. NA. Tupoleva E.A. Mukhutdinov, docent, Kazan State Technology University, Kazan State Technology University, 2006 Excelové tabuľky 1. Behajte program Microsoft Excel: kliknite na tlačidlo Štart; v zobrazenej ponuke vyberte položku Programy; z kontextovej ponuky vyberte položku Microsoft Excel. 2. Pozorne si prezrite okno Microsoft Excel (obr. 1). Mnoho horizontálnych položiek ponuky a tlačidiel panela s nástrojmi je rovnakých ako položky ponuky a tlačidlá v okne editora Word. Úplne iný vzhľad má však pracovný priestor, ktorým je označená tabuľka pozostávajúca z buniek rovnakej veľkosti. Jedna z buniek je jasne vybraná (orámovaná čiernym rámom - tabuľkový kurzor). Ako vybrať inú bunku? Ak to chcete urobiť, stačí naň kliknúť myšou a ukazovateľ myši na tomto obr. 1 krát by mal vyzerať ako svetelný kríž. Skúste zvýrazniť rôzne bunky tabuľky. Pomocou posúvačov sa môžete pohybovať po stole. 3. Ak chcete zadať text do jednej z buniek tabuľky, musíte ju vybrať a okamžite (bez čakania, ako v textovom procesore pre textový kurzor) „písať“. Vyberte jednu z buniek v tabuľke a „napíšte“ do nej názov dnešného dňa v týždni. 4. Hlavný rozdiel medzi prácou tabuľkových procesorov a textového procesora je v tom, že po zadaní údajov do bunky ich treba opraviť, t.j. dajte programu jasne najavo, že ste dokončili zadávanie informácií -3- Copyright OJSC "Central Design Bureau" BIBCOM " & LLC "Agency Book-Service" do tejto konkrétnej bunky. Údaje môžete opraviť jedným z nasledujúcich spôsobov: stlačte kláves (Enter); kliknite na inú bunku; použite kurzorové klávesy na klávesnici (šípky). Zaznamenajte si údaje, ktoré ste zadali. 5. Vyberte bunku tabuľky obsahujúcu deň v týždni a použite tlačidlá na zarovnanie odsekov. Ako prebieha zarovnanie? Urobte záver. Po všetkých experimentoch nezabudnite vrátiť pôvodné zarovnanie - doľava, v budúcnosti to bude dôležité. 6. Už ste si všimli, že tabuľka sa skladá zo stĺpcov a riadkov, pričom každý zo stĺpcov má svoj nadpis (A, B, C ...), pričom všetky riadky sú očíslované (1, 2, 3 ...) (obr. 1). Ak chcete vybrať celý stĺpec, stačí kliknúť na jeho hlavičku, ak chcete vybrať celý riadok, kliknite na jeho hlavičku. Vyberte celý stĺpec tabuľky, v ktorom sa nachádza názov dňa v týždni, ktorý ste zadali. Aký je názov tohto stĺpca? Vyberte celý riadok tabuľky, v ktorom sa nachádza názov dňa v týždni. Aký je názov tohto riadku? Pomocou posúvačov určte, koľko riadkov má tabuľka a aký je názov posledného stĺpca. 7. Vyberte bunku tabuľky, ktorá je v stĺpci C a riadku 4. Venujte pozornosť skutočnosti, že v poli "Názov" (obr. 1), umiestnenom nad záhlavím stĺpca A, je adresa vybranej bunky C4. sa objavilo. Zvýraznite inú bunku a uvidíte, že adresa sa zmenila v poli Názov. Aká je adresa bunky obsahujúcej deň v týždni? 8. Predstavme si, že v bunke obsahujúcej deň v týždni je potrebné pridať aj časť dňa. Vyberte bunku obsahujúcu deň v týždni, zadajte z klávesnice názov aktuálnej časti dňa, napríklad „ráno“, a stlačením klávesu (Enter) údaje opravte. Čo sa stalo? Časť dňa sa do bunky „nepridala“ a nové údaje nahradili pôvodné údaje a namiesto dňa v týždni ste dostali časť dňa. To znamená, že ak vyberiete bunku tabuľky obsahujúcu nejaké údaje a zadáte nové údaje z klávesnice, bunka tabuľky bude obsahovať najnovšie informácie. Ako doplniť obsah bunky tabuľky (upraviť) bez prepisovania všetkých údajov? Ak vyberiete bunku obsahujúcu časť dňa, uvidíte, že jej obsah je duplikovaný v "Pane vzorcov" umiestnenom nad nadpismi stĺpcov (obr. 1). Práve vo „Pane vzorcov“ môžete kliknutím myšou nastaviť tradičný textový kurzor, vykonať všetky požadované zmeny a následne opraviť finálnu verziu údajov. Vyberte bunku tabuľky obsahujúcu časť dňa, umiestnite textový kurzor pred text v "Pane vzorcov" a znova zadajte deň v týždni. Opravte údaje. Mali by ste dostať nasledujúci obrázok (obr. 2). -4- Copyright JSC "Central Design Bureau" BIBCOM " & LLC "Agency Book-Service" 9. Je vidieť, že záznam prekročil svoju celu a obsadil v utorok ráno časť susednej. Stáva sa to iba vtedy, keď je susedná bunka prázdna. Zoberme si to Obr. 2 vyplňte a skontrolujte, čo sa mení. Vyberte bunku tabuľky umiestnenú napravo od bunky obsahujúcej vaše údaje (bunka, do ktorej „prišli“) a zadajte do nej ľubovoľný text. Teraz je viditeľná len tá časť vašich údajov, ktorá sa zmestí do bunky (obr. utorok, piatok 3). Ako zobraziť celý príspevok? A Obr. 3 vám opäť príde na pomoc „Formula Bar“. Práve v ňom môžete vidieť celý obsah vybranej bunky. Takže "Panel vzorcov" vám umožňuje: vykonávať zmeny v obsahu vybranej bunky; zobraziť obsah bunky, ak záznam nie je viditeľný celý. Vyberte bunku obsahujúcu deň v týždni a časť dňa a zobrazte celý obsah bunky na paneli vzorcov. 10. Ako môžem zväčšiť šírku stĺpca tak, aby bol v bunke viditeľný súčasne deň v týždni aj časť dňa? Ak to chcete urobiť, presuňte ukazovateľ myši na pravý okraj nadpisu stĺpca, „zachytte“ okamih, keď sa ukazovateľ myši zmení na čiernu dvojitú šípku, a podržaním ľavého tlačidla myši posuňte okraj stĺpca doprava. Stĺpec sa rozšíril. Podobne môžete zmeniť výšku riadku. V tomto prípade kurzor, keď sa priblíži k spodnému okraju hlavičky riadku, nadobudne tvar. Zmeňte šírku stĺpca obsahujúceho deň v týždni a časť dňa tak, aby bol celý zadaný text viditeľný v bunke tabuľky. 11. Často sa stáva, že potrebujete vybrať viac ako jednu bunku a nie celý stĺpec, ale blok buniek (niekoľko buniek umiestnených vedľa seba). 12. Za týmto účelom umiestnite ukazovateľ myši do krajnej bunky výberu a so stlačeným ľavým tlačidlom presuňte myš na opačný okraj výberu (celý vybraný blok je „zakrytý“ rámom, všetky bunky , okrem toho, z ktorého výber začal, sú natreté čiernou farbou) . Upozorňujeme, že počas procesu výberu "Pole názvu" registruje počet riadkov a stĺpcov, ktoré spadajú do výberu. V rovnakom momente, keď uvoľníte ľavé tlačidlo, sa v poli „Názov“ zobrazí adresa bunky, z ktorej výber začal. Vyberte blok buniek, počnúc bunkou A1 a končiac bunkou obsahujúcou „piatok“. Ak chcete vybrať celú tabuľku, použite rohové tlačidlo „prázdne“ nad nadpisom prvého riadku. -5- Copyright JSC "TsKB "BIBCOM" & LLC "Agency Book-Service" Vyberte celú tabuľku. Výber odstránite kliknutím na ľubovoľnú bunku. 13. Ako vymazať obsah bunky? Stačí vybrať bunku (alebo blok buniek) a stlačiť kláves (Delete) alebo použiť príkaz z horizontálneho menu „Upraviť“ ⇒ „Vymazať“. Vymažte všetky svoje záznamy. Cvičenie 2 Použitie základných tabuľkových techník: Zadávanie údajov do bunky. Formátovanie písma. Zmena šírky stĺpca. Automatické dopĺňanie, zadávanie vzorca, orámovanie tabuľky, zarovnanie textu na stred výberu, množina dolných a horných indexov Urobme tabuľku, ktorá vypočíta n-tý člen a súčet aritmetickej postupnosti. Najprv si pripomeňme vzorec pre n-tý člen aritmetickej postupnosti: an = a1 + d (n − 1) a vzorec pre súčet prvých n členov aritmetickej postupnosti: n S n = (a1 + an ) ⋅ , 2 kde a1 je prvý člen postupnosti a d je rozdiel aritmetickej postupnosti. Na obr. 4 je tabuľka na výpočet n-tého členu a súčtu aritmetickej postupnosti, ktorej prvý člen je -2 a rozdiel je 0,725. Ryža. 4 Pred vykonaním cvičenia si vymyslite svoj vlastný aritmetický postup, t.j. e) Zadajte svoj vlastný prvý termín postupu a rozdielu. Cvičenie je možné rozložiť do nasledujúcich krokov: vyberte bunku A1 a zadajte do nej nadpis tabuľky „Výpočet n-tého členu a súčet aritmetickej postupnosti“. Názov bude umiestnený v jednom riadku a zaberie niekoľko buniek napravo od A1; do bunky A2 zadajte "d", do bunky B3 - "n", do C3 - "an", do D3 - "Sn". Ak chcete nastaviť dolné indexy, najprv zadajte celý text, ktorý by mal byť v bunke (napríklad an), potom zadajte „Panel vzorcov“, vyberte text, ktorý by mal byť dolným indexom (napríklad n), otvorte príkaz „Formát " ⇒ "Bunky ..." (v dialógovom okne, ktoré sa otvorí, je len jedna záložka "Písmo") a aktivujte prepínač "dolný index" v skupine "Upraviť"; Výpočet n-tého členu a súčtu aritmetickej progresie 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,1705 -75,05 61,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 12,625 -6- Copyright JSC "Central Design Bureau" &Gency Book Complete "BIBCOM" Pomocou príslušných tlačidiel na paneli nástrojov zväčšite veľkosť písma o 1 bod, vycentrujte ho a použite štýl tučného písma. Hlavička tabuľky je nastavená. Teraz môžete začať s vypĺňaním tabuľky. 1. Do bunky A3 zadajte hodnotu rozdielu aritmetickej postupnosti (v našom príklade je to 0,725). 2. Ďalej je potrebné vyplniť rad nižších buniek s rovnakým číslom. Je nezaujímavé a iracionálne písať do každej bunky rovnaké číslo. V editore Word sme použili techniku ​​kopírovania a vkladania. Excel ešte viac uľahčuje vyplnenie buniek rovnakými údajmi. Vyberte bunku A3, ktorá obsahuje rozdiel aritmetického postupu. Vybraná bunka je ohraničená rámčekom, v ktorého pravom dolnom rohu je malý čierny štvorec – úchytka výplne. Ak presuniete ukazovateľ myši na značku výplne a v momente, keď má ukazovateľ myši podobu čierneho krížika, potiahnite značku výplne o niekoľko buniek nadol (v tomto prípade sa napravo od kurzora zobrazí nápoveda, čo hodnota sa zadá do aktuálnej bunky), potom sa celý riadok vybratých buniek vyplní údajmi nachádzajúcimi sa v prvej bunke. Doplňte teda ďalších deväť buniek pod bunku A3 hodnotou rozdielu aritmetickej progresie. 3. Ďalší stĺpec obsahuje postupnosť čísel od 1 do 10. A opäť nám značka výplne pomôže vyplniť riadok. Zadajte číslo 1 do bunky B3, číslo 2 do bunky B4, vyberte obe tieto bunky a uchopením rukoväte výplne ju potiahnite nadol. Rozdiel oproti vyplneniu rovnakými údajmi je v tom, že výberom dvoch buniek ste naznačili princíp, akým sa majú vyplniť zvyšné bunky. Značku výplne je možné ťahať nielen nadol, ale aj nahor, doľava alebo doprava a výplň sa bude šíriť v rovnakých smeroch. Prvok výplne môže byť nielen vzorec alebo číslo, ale aj text. Do bunky môžete zadať „január“ a vyplnením riadku ďalej doprava získate „február“, „marec“ a „natiahnutím“ značky výplne z bunky „január“ doľava, resp. december“, „november“ atď. Skúste to urobiť mimo tabuľky, ktorú vytvárate. Najdôležitejšie je, že pred rozložením výberu presne vyberte bunku (alebo tie bunky), na ktorých je výplň naformátovaná. 4. Tretí stĺpec obsahuje n-tých členov progresie. Do bunky C3 zadajte hodnotu prvého člena aritmetického postupu. Do bunky C4 musíte vložiť vzorec na výpočet n-tého člena progresie, ktorý spočíva v tom, že každá bunka stĺpca sa líši od predchádzajúcej pridaním rozdielu aritmetickej progresie. Všetky vzorce začínajú znakom rovnosti. Ak chcete zadať vzorec do bunky, musíte: aktivovať bunku; -7- Copyright OJSC "TsKB" BIBCOM " & LLC "Agency Book-Service" zadajte znak "=" z klávesnice alebo kliknite na tlačidlo "Zmeniť vzorec" na riadku vzorcov; zadajte (bez medzier) požadované hodnoty alebo odkazy, ako aj potrebné operátory; opraviť vstup. Adresa bunky sa zadáva do vzorcov v latinke. Ak bol záznam zadaný v ruskom prípade, zobrazí sa chybové hlásenie „#NAME?“. Vyberte bunku C4 a zadajte do nej vzorec = C3 + A4 (nezabudnite prepnúť na latinku a namiesto odkazu na bunku A4 môžete zadať konkrétnu hodnotu rozdielu vášho aritmetického postupu). Z klávesnice nemôžete zadať adresu bunky, na ktorú sa odkazuje. Po zadaní znamienka rovnosti kliknite na bunku C4 a jej adresa sa zobrazí na riadku vzorcov, potom pokračujte v písaní vzorca. V tomto prípade nemusíte prechádzať na latinčinu. Po úplnom zadaní vzorca ho opravte stlačením (Enter), výsledok výpočtu bude v bunke a samotný vzorec v „Pane vzorcov“. Objavila sa ďalšia funkcia „Panel vzorcov“: ak v bunke vidíte výsledok výpočtov pomocou vzorca, potom samotný vzorec možno zobraziť v „Pane vzorcov“ zvýraznením príslušnej bunky. Ak ste vzorec zadali nesprávne, môžete ho opraviť na paneli vzorcov tak, že najprv vyberiete bunku. Vyberte bunku C4 a podobne ako pri vypĺňaní buniek rozdielom postupu vyplňte vzorec potiahnutím rukoväte nadol, riadok buniek pod C4. Vyberte bunku C8 a pozrite sa na riadok vzorcov, aby ste videli, ako vzorec vyzerá, vyzerá ako =C7+A8. Je zrejmé, že odkazy vo vzorci sa zmenili vzhľadom na posun samotného vzorca. 5. Podobne zadajte vzorec = (-2 + C3) * B3 / 2 do bunky D3 na výpočet súčtu prvých n členov aritmetickej postupnosti, kde namiesto -2 by mal byť prvý člen vami vynájdenej aritmetiky progresie. Vyberte bunku D3 a vyplňte spodné bunky vzorcami potiahnutím rukoväte nadol. 6. Teraz sú všetky bunky naplnené údajmi, zostáva ich len usporiadať. Všetky stĺpce majú rovnakú šírku, aj keď obsahujú informácie rôznych veľkostí. Šírku jednotlivých stĺpcov môžete manuálne (pomocou myši) meniť, alebo môžete šírku automaticky upravovať. Ak to chcete urobiť, vyberte všetky bunky tabuľky obsahujúce údaje (nie celé stĺpce, ale iba blok vyplnených buniek bez nadpisu „Vypočítať n-tý člen a súčet aritmetickej postupnosti“) a spustite príkaz „Formátovať“ ⇒ „Stĺpec“ ⇒ "Automatické prispôsobenie šírky". 7. Teraz naformátujme nadpis tabuľky "Výpočet n-tého člena a súčtu aritmetickej postupnosti". Vyberte bunku A1 a použite tučné znaky na obsah bunky. Nadpis sa dosť neesteticky „vyplazí“ doprava až za hranice našej malej platničky. -8- Copyright OJSC "Central Design Bureau "BIBCOM" & LLC "Agentúra Book-Service" Vyberte štyri bunky od A1 do D1 a vykonajte príkaz "Formátovať" ⇒ "Cells ...", vyberte kartu "Zarovnanie" a nastavte prepínače (obr. 6): skupina "Zarovnanie" ⇒ "horizontálne:" do polohy "stred výberu"; skupina "Zobraziť" ⇒ "Zalomiť podľa slov". To vám umožní usporiadať nadpis do niekoľkých riadkov a do stredu vybraného bloku buniek. Stôl bol takmer prinesený k 8. druhu vzorky. Ak v tejto chvíli spustíte zobrazenie „Súbor“ ⇒ „Náhľad“, ukáže sa, že zostáva zarámovať tabuľku. Ak to chcete urobiť, vyberte tabuľku (bez názvu) a vykonajte príkaz „Formátovať“ ⇒ „Bunky ...“. V dialógovom okne, ktoré sa otvorí, vyberte záložku „Okraj“, definujte typ čiary a aktivujte prepínače „Horná“, „Dole“, „Vľavo“, „Vpravo“ (obr. 5). Tento postup platí pre každú z buniek vo vybranej oblasti. Potom vyberte blok buniek súvisiaci s hlavičkou: od A1 do D2 a po vykonaní rovnakých operácií nastavte prepínač "Externé". V tomto prípade sa získa orámovanie okolo všetkých vybratých buniek, nie každej. Vykonajte kontrolu. Ryža. 5 Obr. 6 -9- Copyright OJSC "Central Design Bureau "BIBCOM" & LLC "Agentúra Book-Service" Laboratórna práca č. 2 Cvičenie 1 Upevnenie základných zručností pri práci s tabuľkovými procesormi, oboznámenie sa s pojmami: triedenie údajov, typy zarovnávania textu v bunka, formát čísla Odosielateľ a jeho adresa Príjemca a jeho adresa Registračné číslo Dátum prijatia "___" ___________200__. FAKTÚRA č. 123 zo dňa 15.11.2000 Obchodný dom dodávateľov "Roga and Hooves" Adresa 243100, Klintsy, ul. Pushkina, 23 R / č. účtu 45638078 v MMM-banke, MFO 985435 Prírastky: č. Názov Merná jednotka 1 2 3 4 5 6 SPOLU Vedúci podniku Množstvo Cena Suma Sidorkin A.Yu. Hlavná účtovníčka Ivanova A.N. Cvičenie spočíva vo vytvorení a vyplnení formulára faktúry za tovar. Cvičenie je najlepšie rozdeliť do troch etáp: 1. fáza - vytvorenie tabuľky formulára faktúry; 2. etapa - vyplnenie tabuľky; 3. etapa - registrácia formulára. 1. etapa Spočíva vo vytvorení tabuľky. Hlavnou úlohou je prispôsobiť stôl šírke listu. Postup: prednastavte okraje, veľkosť a orientáciu papiera ("Súbor" ⇒ "Nastavenie strany ..."); - 10 - Copyright OJSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" vykonaním príkazu "Service" ⇒ na karte "Parametre ..." v skupine prepínačov Parameter okna aktivujte prepínač "Automatické stránkovanie" (obr. 7) V dôsledku toho získate pravý okraj vytáčacej lišty vo forme zvislej bodkovanej čiary (ak ju nie je viditeľná, presuňte sa vodorovným posúvačom obr. 7 na vpravo) a spodný okraj vytáčacej lišty vo forme vodorovnej prerušovanej čiary (aby bolo vidieť, ako sa pohybuje s vertikálny pruh posunúť nadol). Automatické stránkovanie vám umožňuje sledovať, ktoré stĺpce sa na stránku zmestia a ktoré nie, už v procese zadávania údajov a formátovania tabuľky. č. 1 2 3 4 5 6 Názov Merná jednotka Množstvo Cena Suma SPOLU Obr. 8 Vytvorte tabuľku podľa navrhnutého vzoru s rovnakým počtom riadkov a stĺpcov (obr. 8). Zarovnajte a naformátujte písmo v bunkách hlavičky, upravte šírku stĺpcov zmenou pomocou myši. Zadajte číslovanie do prvého stĺpca tabuľky pomocou rukoväte výplne. Stôl "vyskladajte" pomocou čiar rôznej hrúbky. Všimnite si, že v poslednom riadku päť susedných buniek nemá vnútorný rám. Najjednoduchší spôsob, ako to dosiahnuť, je nasledujúci spôsob: vyberte celú tabuľku a nastavte rám - "Externý" s hrubou čiarou; - 11 - Copyright JSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service", potom vyberte všetky riadky okrem posledného a nastavte rám tenkou čiarou "Right", "Left", "Top", "Dole" "; potom samostatne vyberte bunku dolného riadku úplne vpravo a nastavte pre ňu tenkou čiarou rám „Ľavý“; zostáva vybrať prvý riadok tabuľky a nastaviť preň rám „Spodný“ hrubou čiarou. Hoci môžete urobiť opak. Najprv "vyskladajte" celý stôl a potom odstráňte nadbytočné čiary rámovania. V tejto fáze je vhodné vykonať príkaz „Súbor“ ⇒ „Náhľad“, aby ste sa uistili, že tabuľka sa po šírke celá zmestí na hárok a že všetky hraničné čiary sú na správnom mieste. 2. etapa Spočíva vo vypĺňaní tabuľky, triedení údajov a používaní rôznych formátov čísel. Podľa želania vyplňte stĺpce „Názov“, „Množstvo“ a „Cena“. Nastavte formát meny čísla v bunkách, kde budú sumy umiestnené, a nastavte požadovaný počet desatinných miest, ak existujú. V našom prípade sú to bunky stĺpcov „Cena“ a Obr. 9 "Suma". Musíte ich vybrať a spustiť príkaz „Formát“ ⇒ „Bunky ...“, vyberte kartu „Číslo“ a vyberte kategóriu „Mena“ (obr. 9). Získate tak rozdelenie na tisíce, aby ste uľahčili navigáciu vo veľkých množstvách. Zadajte vzorec na výpočet sumy, čo je vynásobenie ceny množstvom a vyplňte riadok buniek nadol vzorcom. Zadajte vzorec do bunky pre celkový súčet. Ak to chcete urobiť, vyberte blok buniek, ktoré chcete pridať, a jednu prázdnu bunku pod týmto blokom, do ktorej chcete umiestniť výsledok. Potom kliknite na tlačidlo na paneli s nástrojmi. Skúste zmeniť údaje v jednotlivých bunkách a uvidíte, ako sa zmení výsledok výpočtu. Zoraďte záznamy podľa abecedy. Ak to chcete urobiť, vyberte všetky riadky tabuľky, s výnimkou prvého (hlavička) a posledného ("Celkom"), nemôžete vybrať a číslovanie. Spustite príkaz „Údaje“ ⇒ „Zoradiť...“ (obr. 10), vyberte stĺpec, podľa ktorého chcete údaje zoradiť (v našom prípade ide o stĺpec B, keďže obsahuje zoznam tovaru na triedenie ) a nastavte prepínač do polohy Vzostupne. 3. etapa faktúry vložte Pre ďalšie riadky pred tabuľku. Ak to chcete urobiť, vyberte niekoľko prvých riadkov tabuľky a vykonajte príkaz "Vložiť" ⇒ "Riadky". Ryža. 10 Vloží sa rovnaký počet riadkov, aký ste vybrali. Zadajte požadovaný text pred a za tabuľku. Dávajte pozor na zarovnanie. Upozorňujeme, že text „Dátum prijatia '__'_______200_“ a mená vedúcich podnikov sú uvedené v tom istom stĺpci, v ktorom sa nachádza stĺpec tabuľky "Suma" (stĺpec úplne vpravo na našej tabuľke), použije sa iba zarovnanie vpravo. Do bunky ľavého stĺpca sa zadá text „Číslo účtu ...“ a zarovnanie sa použije na stred výberu (bunky jedného riadka sú vopred vybraté po celej šírke tabuľky účtov). Na tieto bunky v hornej a dolnej časti bolo použité orámovanie. Všetky ostatné textové informácie pred a za tabuľkou sa zadávajú do stĺpca úplne vľavo, zarovnanie doľava. Vykonajte kontrolu. Cvičenie 2 Predstavenie konceptu „absolútnej referencie“, nastavenie presnej hodnoty šírky stĺpca pomocou príkazov horizontálneho menu. Vloženie funkcie pomocou Sprievodcu funkciou Nový koncept „absolútnej referencie“ možno vidieť na konkrétnom príklade. Pripravme si tradičnú tabuľku druhých mocnín dvojciferných čísel (obr. 11), každému takú známu z kurzu algebry. Do bunky A3 zadajte číslo 1, do bunky A4 - číslo 2, označte obe bunky a ťahaním páčky výberu nadol vyplňte stĺpec číslami od 1 do 9. Podobne vyplňte bunky B2 - K2 číslami od 0 do 9 Keď vyplníte riadok číslami od 0 do 9, všetky bunky, ktoré potrebujete k práci, nie sú na obrazovke viditeľné súčasne. Zúžime ich, ale tak, aby všetky stĺpce mali rovnakú šírku (čo sa nedá dosiahnuť zmenou šírky stĺpcov pomocou myši). Ak to chcete urobiť, vyberte stĺpce od A do K a vykonajte príkaz "Formát" ⇒ ​​- 13 - Copyright OJSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" "Stĺpec" ⇒ "Šírka ...", v vstupné pole „Šírka stĺpca » Zadajte hodnotu, napríklad 5. Samozrejme, každý chápe, že do bunky B3 musíte vložiť vzorec, ktorý odmocní číslo tvorené z desiatok uvedených v stĺpci A a jednotiek zodpovedajúcich hodnote umiestnené v riadku 2. Samotné číslo, ktoré by malo byť v bunke B3 odmocnené, môže byť teda špecifikované vzorcom A3 * 10 + B2 (počet desiatok vynásobený desiatimi plus počet jednotiek). Zostáva odmocniť toto číslo. 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 2601 3721 5081 3721 5041 3 5041 3 44 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 836679 602 116 3136 4356 5776 7396 9216 7 289 729 1369 2209 3249 4489 5929 7569 9409 8 324 784 1444 2404 623 7744 9604 9 361 841 1521 2401 3481 4761 6241 7921 9801 11 Skúsme použiť "Sprievodcu funkciou". Za týmto účelom vyberte bunku, do ktorej sa má umiestniť výsledok výpočtu (RT) a spustite príkaz „Vložiť“ ⇒ „Funkcia ...“ (obr. 12). Dialógové okno "Sprievodca funkciou (krok 1 z 2)" (obrázok 12) má dve podokná: "Kategória" a "Funkcia". Keď vyberiete konkrétnu funkciu, v spodnej časti dialógového okna sa zobrazí stručný popis funkcie. Medzi navrhovanými Obr. 12 kategórií funkcií, vyberte "Math", medzi "Functions" - "Stupeň" stlačte tlačidlo Ok. V ďalšom dialógovom okne (obr. 13) zadajte do poľa "Číslo" (základ stupňa) - A3 * 10 + B2 a do poľa "Exponent" - 2. Rovnako ako pri písaní vzorca priamo do bunka tabuľky, nie je potrebné zadávať z klávesnice adresu každej bunky, na ktorú vzorec odkazuje. V dialógovom okne druhého kroku "Sprievodcu funkciou" stačí ukázať myšou na príslušnú bunku tabuľky a jej adresa sa zobrazí vo vstupnom poli "Číslo" dialógového okna. Budete musieť zadať iba aritmetický Obr. 13 znakov (*, +) a číslo 10. V prípadoch, keď na zadávanie argumentov potrebujete vybrať bunky uzavreté oknom, napravo od každého poľa sú tlačidlá na zadávanie argumentov, ktoré umožňujú zbaliť a rozbaliť dialógové okno . Okno „Sprievodca funkciou“ je navyše možné posunúť nabok „uchopením“ záhlavia myšou. V tom istom dialógovom okne (obr. 13) vidíte hodnotu samotného čísla (10) a výsledok výpočtu stupňa (100). Zostáva iba stlačiť tlačidlo OK. V bunke B3 sa objavil výsledok výpočtu. Chcel by som tento vzorec rozšíriť na zvyšok buniek tabuľky. Vyberte bunku B3 a vyplňte susediace bunky potiahnutím rukoväte výplne doprava. Čo sa stalo (obr. 14)? Ryža. 14 Prečo výsledok nesplnil naše očakávania? V bunke C3 nie je číslo viditeľné, pretože sa úplne nezmestí do bunky Rozbaľte myšou stĺpec C. Číslo sa síce objavilo na obrazovke, ale zjavne nezodpovedá druhej mocnine čísla 11 (obr. 15 ). Ryža. 15 Prečo? Faktom je, že keď sme vzorec rozšírili doprava, Excel automaticky zmenil adresy buniek, berúc do úvahy náš posun, na ktorý sa vzorec vzťahuje a v bunke C3 nie je číslo 11 na druhú, ale číslo vypočítané vzorec B3 * 10 + C2. Vo všetkých predchádzajúcich cvičeniach sme sa celkom uspokojili s relatívnymi odkazmi na bunky tabuľky (keď sa vzorec posúva podľa rovnakého zákona, posúvajú sa aj odkazy), tu však vyvstala potreba opraviť určité odkazy, t.j. uveďte, že počet desiatok možno prevziať iba zo stĺpca A a počet jednotiek iba z riadku 2 (aby bol vzorec možný - 15 - Copyright JSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service "rozprestreté). Na tento účel má Excel možnosť nastaviť absolútne a zmiešané odkazy. Absolútny odkaz je odkaz, ktorý sa pri kopírovaní vzorcov nemení. Za týmto účelom sa pred názov stĺpca a číslo riadku pridá znak dolára $ (zadáva sa buď z klávesnice, alebo po zadaní adresy bunky sa stlačí funkčný kláves F4). Zmiešané odkazy sú odkazy, ktoré sú len čiastočne absolútne, t.j. buď stĺpec alebo riadok je pevný. V tomto prípade je znak dolára $ umiestnený buď pred písmenom, v prípade, že stĺpec je pevný, alebo pred číslicou, ak je riadok pevný. Znak dolára $ sa zadáva buď z klávesnice, alebo po zadaní adresy bunky stláčame funkčný kláves F4, kým sa znak $ nenachádza na požadovanom mieste. Keď kopírujete vzorec obsahujúci zmiešaný odkaz, zmení sa iba relatívna časť odkazu. Vráťte šírku stĺpca C na pôvodnú pozíciu a vykonajte nasledovné: Vyberte bunku B3 a nastavením textového kurzora na "Panel vzorcov" opravte existujúci vzorec =POWER(A3*10+B2;2) na správny. =POWER($A3*10+ za 2,2 $). Teraz pomocou služieb značky výplne môžete vyplniť všetky voľné bunky tabuľky týmto vzorcom (najprv potiahnite značku výplne doprava, potom bez odstránenia výberu z výsledného bloku buniek nadol). Na zadávanie odkazov na bunky pre údaje stĺpca A a riadka 2 sme použili zmiešané odkazy. Absolútna referencia v našom príklade by sa dala použiť, ak by sme do vzorca zadali nie číslo 10, ktorým sa vynásobia čísla v stĺpci A, ale adresu bunky, napríklad A15 (kde by sme toto číslo zadali 10). V tomto prípade by sa vzorec v bunke B3 zapísal ako: =POWER($A3*$A$15+B$2) a potom by sa skopíroval aj do zvyšku buniek. Skúste to. Zostáva usporiadať tabuľku: do bunky A1 zadať nadpis, naformátovať ho a vycentrovať na výber, tabuľku orámovať a jednotlivé bunky vyplniť pozadím. Cvičenie 3 Predstavenie pojmu „názov bunky“ Predstavte si, že máte vlastnú spoločnosť, ktorá predáva nejaké produkty a musíte si každý deň vytlačiť cenník s cenami za tovar v závislosti od výmenného kurzu dolára. Pripravte tabuľku pozostávajúcu zo stĺpcov: "Názov produktu"; "Ekvivalentné k USD"; "Cena v rubľoch". - 16 - Copyright JSC "Central Design Bureau "BIBCOM" & LLC "Agentúra Book-Service" Vyplňte všetky stĺpce, krém "Cena v rubľoch". Do stĺpca „Názov produktu“ vyplňte textové údaje (zoznam tovaru podľa vlastného uváženia) a do stĺpca „Ekvivalent v USD“ vyplňte čísla (ceny v $). Je zrejmé, že v stĺpci "Cena v r." vzorec by mal byť umiestnený: „Ekvivalent k USD“ * „Výmenný kurz dolára“. Prečo je v tomto vzorci nepohodlné násobenie konkrétnou hodnotou výmenného kurzu? Áno, pretože pri každej zmene kurzu budete musieť zmeniť svoj vzorec v každej bunke. Jednoduchšie je to vziať pod hodnotu dolára samostatná bunka , ktorý je uvedený vo vzorci. Je jasné, že referencia musí byť absolútna, t.j. hodnotu výmenného kurzu dolára možno prevziať iba z tejto konkrétnej bunky s pevnou adresou. Vyššie sme diskutovali o tom, ako nastaviť absolútne odkazy, existuje však ďalší pohodlný spôsob: odkazovať nie na adresu bunky, ale na názov, ktorý možno bunke priradiť. Keď pomenúvate bunku alebo rozsah buniek, môžete k danej bunke alebo rozsahu pristupovať kedykoľvek a odkiaľkoľvek v tabuľke, aj keď zmenia svoje umiestnenie alebo sú na iných hárkoch. Vyberte bunku, do ktorej bude zadaný kurz dolára (nad tabuľkou), zadajte do nej hodnotu kurzu dolára na dnešný deň a vykonajte príkaz „Vložiť“ ⇒ „Názov“ ⇒ „Priradiť ...“. V dialógovom okne, ktoré sa otvorí (obr. 16), môžete zadať ľubovoľný názov a v poli „Formula“ vybrať rozsah, pre ktorý sa tento názov zadáva. Názov môže mať dĺžku až 255 znakov a môže obsahovať písmená, čísla, podčiarkovníky (_), spätné lomky (\), bodky a otázniky. Prvý znak však musí byť písmeno, podčiarkovník (_) alebo spätná lomka (\). Mená, ktoré sú vnímané ako čísla alebo odkazy na bunky, nie sú povolené. V dialógovom okne, ktoré sa zobrazí, stačí zadať názov bunky (jej presná adresa je už uvedená vo vstupnom poli "Vzorec") a kliknúť na tlačidlo OK. Upozorňujeme, že v "Poli názvu" je teraz namiesto adresy bunky umiestnený jej názov. V bunke umiestnenej naľavo od bunky „Dollar_rate“ môžete zadať text „Dollar rate“. Ryža. 16 Teraz zostáva zadať vzorec na výpočet ceny v rubľoch. Ak to chcete urobiť, vyberte najvrchnejšiu prázdnu bunku stĺpca "Cena v rubľoch" a zadajte vzorec takto: zadajte znak "=", potom kliknite na bunku umiestnenú vľavo (ktorá obsahuje cenu v dolároch), potom zadajte znak "*" a "Exchange_dollar". Vzorec by mal vyzerať asi takto: =B7*kurz_USD. Doplňte vzorec pomocou rukoväte na plnenie. Vyberte príslušné bunky a použite na ne formát čísla meny. Upravte štýl hlavičky tabuľky: zarovnať na stred, použiť štýl tučného písma, rozbaliť riadok a použiť zvislé zarovnanie na stred pomocou príkazu „Formátovať“ ⇒ „Bunky ...“, vyberte kartu „Zarovnanie“ a v skupine „Vertikálne:“ vyberte "V strede." V tom istom dialógovom okne aktivujte prepínač "Zalamovať podľa slov" v prípade, že sa niektorý nadpis nezmestí na jeden riadok. Zmeňte šírky stĺpcov. Vyberte tabuľku a nastavte pre ňu rám. - 18 - Copyright OJSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" Laboratórna práca č. 3 Cvičenie 1 Zmena orientácie textu v bunke, oboznámenie sa s možnosťami databáz excelové dáta. Triedenie údajov podľa viacerých kľúčov Predstavte si, že ste majiteľom malého obchodu. Je potrebné dôsledne evidovať príjem a spotrebu tovaru, mať každý deň na očiach reálny zostatok, vedieť vytlačiť názov tovaru podľa oddelení a pod. Aj pri takejto náročnej úlohe dokáže Excel výrazne uľahčiť prácu. Rozbijeme toto cvičenie pre niekoľko úloh v logickom slede: vytvorenie tabuľky; vyplnenie tabuľky údajmi tradičným spôsobom a pomocou formulára; výber údajov podľa určitého atribútu. 1. Vytvorenie tabuľky Zadajte nadpisy tabuľky v súlade s navrhovaným vzorom. Upozorňujeme, že nadpis sa nachádza v dvoch riadkoch tabuľky: v hornom riadku „Príjmy“, „Výdavky“, „Zostatok“ a ostatné položky nadpisu v riadku nižšie (obr. 17). Zostávajúca suma Zostávajúca Zostávajúca suma Množstvo výdavku Výdavok Cena výdaja Názov produktu Množstvo príjem Oddelenie Cena príjmu Číslo Merná jednotka Príjem 1 2 3 4 5 6 Obr. 17 Zadávanie textu nadpisu je najlepšie začať v druhom riadku. Už ste si všimli, že stĺpec „Prichádzajúce“ pokrýva dve bunky. Slovo „Príjem“ sa napíše do rovnakého stĺpca ako „Cena príjmu“, potom sa vyberú dve susediace bunky a text sa vycentruje na výber (táto operácia sa opakovane zvažovala v predchádzajúcich cvičeniach). Bunky "Výdavky" a "Zostatok" sú naformátované podobne. Vyberte druhý riadok nadpisu a zarovnajte na stred. Môžete tiež vidieť, že aby sa celá tabuľka zmestila na šírku listu, v niektorých bunkách je text „otočený o 90°“. Označte tie bunky, v ktorých chcete text „rozbaliť“ a zvoľte príkaz „Formát“ ⇒ „Cells ...“ na karte „Zarovnanie“ (obr. 18) vyberte „Orientácia textu“ 90o a nezabudnite aktivovať prepnite "Zalomiť slovami" (zvislé zarovnanie ponechajte "Dole"). Pre zostávajúce (nerozbalené) bunky použite vertikálne zarovnanie "Na stred". Nastavte orámovanie tabuľky ("Formát" ⇒ ​​"Bunky ...", záložka "Okraj"). Inštalácia do buniek, obr. 18 obsahujúci ceny, formát peňažného čísla („Formát“ ⇒ „Bunky…“, záložka „Číslo“). Zadajte číslovanie riadkov tabuľky (č. stĺpca) pomocou značky výplne. Zadajte vzorce pre výšku zostatku („Došlé množstvo“ mínus „Množstvo výdavkov“) a sumu zostatku („Zostávajúce množstvo“ krát „Výstupná cena“). Rozšírte tieto vzorce v tabuľke. V procese vykonávania úlohy je v mnohých prípadoch vhodnejšie použiť kontextové menu vyvolané stlačením pravého tlačidla myši. Na formátovanie buniek ich teda stačí vybrať, kliknúť pravým tlačidlom myši v momente, keď je ukazovateľ myši vo výbere a vybrať príkaz „Formátovať“ ⇒ „Bunky ...“. Tým sa dostanete do rovnakého dialógového okna Formát buniek (obrázok 18). Áno, a úprava obsahu bunky (oprava, zmena údajov) nie je v „Pane vzorcov“ vôbec potrebná. Ak na bunku dvakrát kliknete alebo stlačíte kláves F2, zobrazí sa v nej textový kurzor a môžete vykonať potrebné opravy. 2. Vyplnenie tabuľky Premenovať "Hárok1" na "Dostupnosť". Ak to chcete urobiť, kliknite pravým tlačidlom myši na štítok "Hárok1" a vyberte príkaz, ktorý chcete premenovať. Zadajte nový názov a stlačte kláves (Enter). Rozhodnite sa, aký typ produktu budete predávať a aké oddelenia budú vo vašom obchode. Do tabuľky zadajte údaje nie podľa oddelení, ale zmiešané (v poradí príjmu tovaru). Vyplňte všetky bunky okrem tých, ktoré obsahujú vzorce ("Zvyšok"). Nezabudnite nechať posledný riadok tabuľky prázdny (tento riadok však musí obsahovať všetky vzorce a číslovanie). Údaje zadávajte tak, že z toho istého oddelenia je rôzny tovar (nie však v rade) a musí existovať tovar s nulovým zostatkom (všetko predané) (obr. 19). Súhlaste s tým, že tradičný spôsob vyplnenia tabuľky nie je obzvlášť pohodlný. Využime databázy Excelu. 1 2 3 4 5 6 Oddelenie Cukrovinky Mäso Mäso Víno a vodka Názov produktu Marshmallow v čokoláde Syr Klobása Moskva Balyk Vodka "Absolútna" Spotreba Zostávajúce Vstupné množstvo Vstupné množstvo Množstvo výdavkov Množstvo výdavkov Zvyšné množstvo Zvyšné množstvo № Merná jednotka Bal. 20 p. 15 kg. 65 s. 10 kg. 110 r. 20 kg. 120 r. 10 fliaš 2 l. 400 r. 100 25r. 85 s. 120r. 140 r. 450 r. 15 8 15 5 99 0 2 5 5 1 0 0 170 r. 600 r. 700 r. 450 r. 0 r. Ryža. 19 Zvoľte príkaz "Dáta" ⇒ "Formulár..." Získate dátový formulár (obr. 20) obsahujúci statický text (názvy databázových polí) a editačné okná, v ktorých môžete zadávať a upravovať text. Vypočítané polia (v ktorých sú umiestnené vzorce) sa zobrazujú bez editačných okien („Zostávajúce množstvo“ a „Zostávajúce množstvo“). Teraz máte svoju tabuľku akoby vo forme samostatných záznamových kariet (každá z nich predstavuje riadok tabuľky). Ryža. 20 Medzi záznamami sa môžete pohybovať buď pomocou tlačidiel „Predchádzajúci“, „Ďalej“, alebo pomocou kurzorových kláves (hore, dole), alebo posúvaním posúvača na posuvnej lište dátového formulára. Dosahuje až posledný záznam (zámerne sme ho nechali prázdny, ale rozšírili sme oň vzorce a číslovanie), naplňte ho novými údajmi. Medzi editačnými oknami, do ktorých sa zadávajú údaje, sa pohodlne pohybuje klávesom (Tab). Po dokončení celého zadania stlačte tlačidlo (Enter) a automaticky sa dostanete na novú prázdnu vstupnú kartu. Hneď ako vyplníte nový záznam, všetky vami zadané informácie sa automaticky reprodukujú v pôvodnej tabuľke. Vyplňte niekoľko nových záznamov a kliknite na tlačidlo Zavrieť. - 21 - Copyright JSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" Ako vidíte, je celkom pohodlné vyplniť tabuľku v režime formulára. 3. Vyplnenie tabuľky pomocou pripraveného zoznamu údajov Keďže máme obmedzený počet oddelení a ich názvy sú nemenné, je najlepšie pri vypĺňaní tabuľky použiť vopred pripravený zoznam týchto oddelení. Vymažte názvy oddelení zo stĺpca "Oddelenie" a zadajte krátky zoznam, ktorý obsahuje názvy všetkých oddelení raz, mimo tabuľky, napríklad v stĺpci L. Potom vyberte bunky stĺpca "Oddelenie" v tabuľke a vyberte príkaz "Údaje" ⇒ "Skontrolovať". Tým sa zobrazí Obr. 21 dialógové okno „Validácia vstupných hodnôt“ (obr. 21), kde musíme špecifikovať podmienky overenia. V našom príklade si musíme vybrať zo zoznamu (ktorý zadáme do poľa „Typ údajov“). Ak chcete vybrať „Zdroj“ údajov, použite tlačidlo minimalizácie okna. Stlačte ho, zvýraznite zoznam našich oddelení v stĺpci L a vráťte sa do okna pomocou tlačidla maximalizovať okno. Po dokončení týchto krokov kliknite na tlačidlo OK. Teraz pri prechode do buniek stĺpca „Oddelenie“, kde je nastavená podmienka overenia, sa napravo od týchto buniek zobrazí štvorec so šípkou, kliknutím na ktorý môžeme vybrať názov oddelenia, ktoré potrebujeme ( Obr. 22). Ryža. 22 Ak chcete skryť tabuľku oddelení, môžete zmeniť písmo v bunkách stĺpca L na biele alebo skryť celý stĺpec. Ak chcete skryť stĺpec L, vyberte ho, vyberte Formát ⇒ Stĺpec ⇒ Skryť. Ak chcete vrátiť stĺpec L na obrazovku, vyberte stĺpce okolo skrytého stĺpca (stĺpce K a M) a vykonajte príkaz "Formát" ⇒ ​​"Stĺpec" ⇒ "Zobraziť ". Všimnite si, že príkaz "Skryť" možno použiť aj na reťazce. Na tento účel sa vyberie riadok, vyberie sa príkaz "Formát" ⇒ ​​"Riadka" ⇒ "Skryť". Ak chcete vrátiť riadok na obrazovku, musíte vybrať riadky okolo skrytej čiary a spustiť príkaz „Formátovať“ ⇒ „Riadka“ ⇒ „Zobraziť“. Ryža. 23 Zoznam môžete vytvoriť aj na inom hárku. V tomto prípade však nie je možné špecifikovať adresy, ktoré obsahujú názov listu ako „Zdroj“ informácií, t.j. ako adresu musíte zadať názov rozsahu buniek. V minulej lekcii sme sa oboznámili s tým, ako nastaviť názov jednej bunky. Ak chcete nastaviť názov pre rozsahy buniek, musíte pred vykonaním príkazu „Vložiť“ ⇒ „Názov“ ⇒ „Priradiť“ vybrať rozsah buniek, nie iba jednu bunku. Prenesme si náš zoznam oddelení zo stĺpca L Hárok1 do Hárok2 do stĺpca A. Označíme bunky, v ktorých je náš zoznam umiestnený a vykonáme príkaz „Vložiť“ ⇒ „Názov“ ⇒ „Priradiť“. V dialógovom okne, ktoré sa otvorí (obr. 23), môžete zadať ľubovoľný názov, napríklad „Oddelenie“ a do poľa „Vzorec“ vybrať rozsah, pre ktorý sa tento názov zadáva (štandardne adresa rozsahu nami vybrané je umiestnené tu). Potom stlačte tlačidlo OK. Teraz v dialógovom okne príkazu "Skontrolovať ..." ako zdroja (obr. 21) stačí zadať znak "=", následne stlačením klávesu F3 otvorte zoznam dostupných názvov buniek, zvoľte "Oddelenie “ v zozname, ktorý sa otvorí, a stlačením tlačidiel Ok zatvorte dialógové okná. 4. Triedenie údajov Vyplnili ste teda tabuľku v poradí príjmu tovaru, ale chceli by ste mať zoznam tovaru podľa oddelení, na to aplikujeme riadkové triedenie. Vyberte tabuľku s druhým riadkom hlavičky, ale bez prvého stĺpca „Č.“, a zvoľte príkaz „Údaje“ ⇒ „Zoradiť...“ (obr. 24). Ryža. 24 - 23 - Copyright OJSC "TsKB "BIBCOM" & LLC "Agency Kniga-Service" Zvoľte prvý triediaci kľúč: v rozbaľovacom zozname "Zoradiť podľa" zvoľte "Oddelenie" a nastavte prepínač do polohy "Vzostupne" (všetky oddelenia v tabuľke budú umiestnené abecedne). Ak chcete, aby boli všetky produkty v rámci oddelenia zoradené podľa abecedy, vyberte druhý kľúč triedenia: v rozbaľovacom zozname „Potom podľa“ vyberte „Názov produktu“ a prepínač nastavte na „Vzostupne“. Teraz máte úplný zoznam tovar podľa oddelenia. 5. Filtrovanie údajov Pokračujme v oboznamovaní sa s možnosťami databáz Excel. Pripomeňme si, že na dennej báze si potrebujeme vytlačiť zoznam tovaru, ktorý zostal na predajni (s nenulovým zostatkom) alebo zobraziť zostatok v niektorom oddelení, ale na to si najskôr potrebujeme takýto zoznam zaobstarať, t.j. filtrovať údaje. Zvýraznite tabuľku s druhým riadkom hlavičky (ako pred vytvorením údajového formulára). Vyberte príkaz ponuky „Údaje“ ⇒ „Filter...“ ⇒ „Automatický filter“. Zrušte výber tabuľky. Každá bunka hlavičky tabuľky má teraz tlačidlo so šípkou (nie je vytlačené), ktoré umožňuje nastaviť kritérium filtrovania (obr. 25). Ryža. 25 Predpokladajme, že chceme ponechať všetky položky pre „Oddelenie cukroviniek“. Rozbaľte zoznam buniek "Oddelenie" a vyberte "Pečivo". V tomto prípade Excel zmení tabuľku a zobrazí ju v zobrazení, kde budú prítomné len údaje týkajúce sa vybraného oddelenia (obr. 26) a šípka v stĺpci, kde bol aplikovaný automatický filter, sa zmení na modrú. Ryža. 26 - 24 - Copyright OJSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" Rovnakým spôsobom môžete zobraziť údaje pre iné oddelenia alebo vybrať kritérium filtrovania v inom stĺpci. Tabuľku je možné vytlačiť vo filtrovanej forme. Filtrované riadky je možné zvýrazniť farbou písma, pozadím, rámčekmi alebo inak formátovať. Odstránením filtrovania získame veľmi vizuálne rozloženie tabuľky. Vo filtrovanej časti tabuľky môžete počítať sumy, produkty a vykonávať ďalšie operácie, ako keby v tabuľke neboli žiadne ďalšie riadky. Chceme napríklad vypočítať výšku zostatku v oddelení cukroviniek. Za týmto účelom vyberieme údaje v stĺpci „Suma zvyšku“, pričom zachytíme poslednú voľnú bunku, a stlačíme tlačidlo „Automatický súčet“. Vo voľnej bunke sa objaví funkcia SUBTOTALS(9; F2:F8) (obr. 26). V ňom je prvým argumentom číslo matematickej alebo štatistickej operácie (1 výpočet priemeru; 2 a 3 - počítanie počtu čísel a neprázdnych buniek; 4 a 5 - výpočet maxima a minima; 6 - súčin ; 7 a 8 - štandardná odchýlka; 9 - súčet; 10 a 11 sú rozptyl) a druhý je interval výpočtu. Funkcia SUBTOTAL sa nachádza v kategórii matematických a líši sa tým, že počíta hodnoty iba pre viditeľné bunky a neberie do úvahy neviditeľné. Keď zmeníte filtrovanie, zmenia sa aj medzisúčty (obr. 25), pričom zvyčajná súčtová alebo súčinová funkcia zostane nezmenená. Zmeňme trochu stav problému, povedzme, že chceme vidieť údaje o nenulových zvyškoch oddelenia cukroviniek. Ak to chcete urobiť, vyberte položku "Podmienka" v zozname stĺpca "Suma zostatku". Zobrazí sa dialógové okno Vlastný automatický filter (obr. 28). V hornom poli zvoľte "viac" "0.00r.". Získaný výsledok je znázornený na obr. 28. Obr. 27 Povedzme, že sa teraz chceme pozrieť na údaje o nenulových zostatkoch v oddelení cukroviniek a mäsa. Za týmto účelom ponecháme rovnaký filter v stĺpci „Suma zostatku“ a v stĺpci „Oddelenie“ vyberieme „Podmienka“ (obr. 28). V hornom poli zvoľte "rovná sa" Obr. 28 - 25 - Copyright JSC "TsKB "BIBCOM" & LLC "Agentúra Kniga-Service" "Cukrovinky", nižšie "rovná sa" "Mäso" a nastavte ALEBO ako logickú funkciu. Získaný výsledok je znázornený na obr. 29. Obr. 29 Ak chcete znova zobraziť celú tabuľku, musíte kliknúť na šípku v stĺpci, kde bolo použité filtrovanie (zobrazené sú modrými šípkami), v zozname vybrať „Všetko“ alebo ešte raz prejsť na „Filter“ ⇒ Príkaz "Auto Filter" v ponuke "Data" na zrušenie režimu filtrovania. - 26 - Copyright OJSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" Laboratórna práca č. 4 Cvičenie 1 Vytváranie a úprava grafov v Excelový dokument Veľmi často v živote musíte merať závislosť jednej premennej od druhej a tieto závislosti ukázať vo forme grafov. Excel poskytuje možnosť takéhoto vizuálneho zobrazenia číselných elektronických údajov. Predpokladajme, že sa uskutočnil nejaký experiment, napríklad sa merala závislosť niektorého parametra od teploty. Počiatočná teplota bola 10 °C. Krok zmeny je 10оС. Zadajte tieto informácie na strana v Exceli (obr. 30). Ak chcete umiestniť nadpis „Počiatočné údaje“ nad tabuľku počiatočných údajov, ako je znázornené na obr. 30, vyberte dve bunky, vykonajte príkaz „Formátovať“ ⇒ „Formát bunky ...“ a na karte „Zarovnanie“ začiarknite políčko „zabaliť podľa slov“, „zlúčiť bunky“ na displeji a skupina zarovnania "na stred" a horizontálne a vertikálne (pozri laboratórium č. 1). Teraz si pripravíme tabuľku experimentálnych údajov (obr. 31). Zadajte údaje prvého stĺpca - čísla v poradí sa zadávajú pomocou značky výplne (čierna bodka v rohu kurzora tabuľky). Nadpis stĺpca "Teplota" sa zadáva podľa vzorca: =A2 (t.j. vo vzorci je odkaz na adresu bunky, obr. 31, kde je názov parametra umiestnený v tabuľke "Počiatočné údaje" ( Obr. 30)). Počiatočná teplota sa rovná počiatočnej teplote, t.j. =B2. Ďalej sa od predchádzajúceho líši krokom. Preto sa v druhom riadku teplota rovná počiatočnej teplote + kroku, t.j. =E3+$B$3. Ak chcete ďalej použiť rukoväť výplne na kopírovanie vzorca a krok sa musí vykonávať neustále z tej istej bunky, urobíme odkaz naň absolútnym. Takto zavedené informácie nám umožňujú automatizovať prispôsobenie tabuľky experimentálnych údajov zmenám počiatočných podmienok. Skúste namiesto "teploty" v počiatočných údajoch zadať "tlak" a nastaviť počiatočnú hodnotu, napríklad 20. Hodnoty Yekper by mali byť prevzaté z experimentu, takže ich stačí zadať z klávesnice. Na formátovanie titulku "Experiment" je najlepšie použiť možnosť kopírovania formátu. K tomu: - 27 - Copyright OJSC "Central Design Bureau" BIBCOM " & LLC "Agency Book-Service" Obr. 32 Obr. 33 vyberte bunku „Počiatočné údaje“; Kliknite na tlačidlo nástroja Formátovať štandardný panel farby. V tomto prípade Excel skopíruje formát vybranej bunky a ukazovateľ myši sa zmení na štetec, naľavo od neho je znamienko plus; potiahnite ukazovateľ myši cez rozsah buniek, do ktorých chcete skopírovať vybratý formát. Ak chcete vykresliť graf Yexper verzus teplota, vyberte tieto dva stĺpce vrátane ich hlavičiek a vyberte možnosť Vložiť ⇒ Graf alebo kliknite na tlačidlo Sprievodca grafom na paneli nástrojov. Prvé dialógové okno „Sprievodca grafom (1. krok zo 4) – Typ grafu“ (obr. 32) má dve záložky – „Štandardný“ a „Vlastný“. V tomto štádiu sa z dostupných vzoriek vyberie variant diagramu vo výstavbe. Ak chcete vytvoriť graf závislosti jednej veličiny od druhej, musíte vybrať typ grafu „Bod“ a potom ktorýkoľvek z jeho piatich typov. Kreslime len bodky a nie čiary. Kliknite na tlačidlo „Zobraziť výsledok“ (obr. 32). Excel okamžite ukáže, ako budú naše údaje zobrazené na hotovom grafe. Ak chcete prejsť na každý nasledujúci krok Sprievodcu grafom, použite tlačidlo "Ďalej". V druhom okne „Sprievodca grafom (2. krok zo 4): Zdroj údajov grafu“ na karte „Rozsah údajov“ sa zobrazí adresa zvoleného rozsahu zdrojových údajov a ukážka zostavovaného grafu (obr. 33). . Vyberte „v stĺpcoch“ a kliknite na tlačidlo „Ďalej“. - 28 - Copyright JSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" V tretej fáze sprievodcu grafom (v kroku 3) v okne "Parametre grafu" (obr. 34) určite povahu návrh diagramu - vykonajte jeho formátovanie. Tretie okno Sprievodcu grafom má tieto záložky: nadpis - umožňuje zadať text nadpisu grafu a označenia osí; osí - umožňuje definovať Obr. 34 zobrazenie a označenie súradnicových osí; mriežkové čiary - umožňuje definovať typ čiar a charakter zobrazenia mriežky; legenda - umožňuje zobraziť alebo skryť legendu a určiť jej miesto na grafe. Legenda - Obr. 35 je malé podokno na diagrame, ktoré zobrazuje názvy radov údajov a príklady ich vyfarbenia na diagrame (vo forme kľúča Legend); štítky údajov – umožňuje ovládať zobrazenie štítkov zodpovedajúcich jednotlivým údajovým prvkom v grafe; dátová tabuľka - umožňuje pridať alebo skryť dátovú tabuľku použitú na zostavenie grafu na grafe. Posledné (štvrté) okno sprievodcu diagramom (obr. 35) slúži na určenie jeho umiestnenia v zošite. Odporúča sa zvoliť jeho umiestnenie na samostatnom hárku, pretože. v tomto prípade sa diagram ľahšie vkladá do iných dokumentov, neblokuje pôvodné údaje, je čitateľnejší atď. Po zadefinovaní všetkých požadovaných parametrov kliknite na tlačidlo „Dokončiť“. Excel nám zostavil graf (obr. 36) s použitím určitej množiny parametrov, ako je farba oblasti konštrukcie (oblasť, v ktorej je samotný graf zobrazený, bez nadpisov, legiend a iných prvkov), písmo, mierky, veľkosť bodu atď., predvolená. Ak chcete zmeniť jednu alebo druhú možnosť formátovania grafu, musíte na ňu kliknúť pravým tlačidlom myši a vybrať príslušný príkaz v kontextovej ponuke, ktorá sa otvorí. Upravte pozadie vykresľovacej oblasti biele. Ak to chcete urobiť, kliknite pravým tlačidlom myši na oblasť vykresľovania diagramu a vyberte príkaz „Formátovať oblasť vykresľovania“. V dialógovom okne, ktoré sa otvorí (obr. 37), zaškrtnite prepínač v skupine „normálna“ výplň. Kliknite na tlačidlo OK. - 29 - Copyright JSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" Y=f(x) 12 10 8 Y ​​​​6 4 2 0 0 20 40 60 80 100 120 X Yexper Obr. 36 Obr. 37 Obr. 38 Zväčšite veľkosť bodov. Ak to chcete urobiť, kliknite pravým tlačidlom myši na body a vyberte príkaz "Formátovať sériu údajov". V dialógovom okne, ktoré sa otvorí (obr. 38), v skupine „veľkosť“ nastavte veľkosť bodiek, napríklad 8 b. Tu môžete zvoliť aj iné parametre údajov, napríklad zmeniť značku, t.j. typu bodov, nakreslite čiaru výberom jej farby, hrúbky a typu, vyhladzujte čiaru atď. Náš argument funkcie sa mení z 10 na 100 a mierka na osi X má minimálnu hodnotu 0 a maximálnu 120. Okrem toho je písmo štítku s údajmi malé. Ako to zmeniť? Kliknite pravým tlačidlom myši na os X a vyberte možnosť Formátovať os. V dialógovom okne, ktoré sa otvorí (obr. 39), na karte „Mierka“ nastavte minimálnu hodnotu - 10, maximálnu - 100 a cenu hlavných divízií - 10, pretože naše údaje sa menia v krokoch po 10. V tom istom okne na karte Písmo môžete zväčšiť veľkosť písma a zmeniť jeho štýl, napríklad zväčšiť ho na 8 bodov a urobiť kurzívu. Na záhybe "Zarovnanie" môžete nastaviť zvislé písanie podpisov. Podobne môžete - 30 - Copyright JSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" naformátovať os Y. Urobte to. Už ste pochopili princíp formátovania grafu? Potom zväčšite veľkosť písma nadpisu, upravte legendu, nadpisy osí. Príklad naformátovaného grafu je na obrázku (obr. 40). Na predpovedanie hodnôt odozvy - parametra Y na výstupe experimentu z faktora - nezávislých premenných X na vstupe do systému (v našom prípade je to teplota), je potrebné poznať funkčnú závislosť Y = f (X). Excel má schopnosť automaticky vybrať takúto funkciu. Ryža. 39 Yexper Y=f(x) 12 10 8 Y ​​​​6 4 2 100 90 80 70 60 50 40 30 20 10 0 X 40 Kliknite pravým tlačidlom myši na body az kontextového menu vyberte Pridať trendovú čiaru. V dialógovom okne, ktoré sa otvorí (obr. 41), na karte Typ vyberte typ trendovej čiary. Zvyčajne sa na opis systému používa polynomická trendová čiara druhého rádu: Y = a0 + a1 * X + a2 * X 2 , (1) kde ai sú koeficienty rovnice. V prípade potreby môžete zmeniť stupeň na 6. Potom bude mať rovnica tvar: - 31 - Copyright JSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" Y = a0 + a1 * X + a2 * X 2 + a3 * X 3 + + a4 * X 4 + a5 * X 5 + a6 * X6. (2) Obr. 41 Obr. 42 Na karte "Parametre" nastavte prepínače na "zobraziť rovnicu na diagrame" a "umiestniť približnú hodnotu spoľahlivosti do diagramu" (obr. 42). To vám umožní vidieť rovnicu a presnosť aproximácie našich údajov. Rovnicu a presnosť zobrazenú na obrazovke je možné presunúť na ľubovoľné miesto v diagrame (ako aj iné nápisy, napríklad názov diagramu, názvy osí, legendu), „uchopením“ rámu pomocou ľavé tlačidlo myši. Približná konečná podoba našej závislosti je na obr. 43. Uložte si výsledky cvičenia 1, budeme ich potrebovať neskôr (pozri Laboratórium č. 6 nižšie). y = -0,0054x2 + 0,6014x - 5,9667 R2 = 0,9817 Y=f(x) Polynóm Yexp (Yexp) 12 10 8 6 Y 4 2 0 -2 10 20 30 40 50 60 X 43 - 32 - 70 80 90 100 Copyright JSC "TsKB "BIBCOM" & LLC "Agency Kniga-Service" Cvičenie 2 Vytváranie a úprava plôch v dokumente Excel V predchádzajúcom cvičení sme zvažovali možnosť vizualizácie jednoparametrovej závislosti ( funkcia závisí len od jednej premennej). V skutočnosti sú takéto jednoduché závislosti dosť zriedkavé. Častejšie sa musíte zaoberať multiparametrovými funkciami. Ako ich vizualizovať, zvážte príklad dvojparametrového problému. Majme rovnicu: Z = sin X 2 − Y 2 + 5 ⋅ X ⋅ Y , (3) kde X a Y sa menia z -5 na 5 v krokoch po 1. Je potrebné vykresliť povrch získaného Z Najprv musíte zostaviť dátovú maticu (obr. 44). () Ryža. 44 Do bunky B1 zadajte prvú hodnotu Y = -5. Potom vykonajte príkaz "Upraviť" ⇒ "Vyplniť" ⇒ "Progresia ...". V dialógovom okne, ktoré sa otvorí (obr. 45), nastavte: "Umiestnenie" - po riadkoch, "Krok:" rovný 1 a "Hodnota limitu:" rovný 5. Potom kliknite na tlačidlo Ok. Presne rovnakým spôsobom Obr. 45, hodnoty X v stĺpci A sú vyplnené, s jedinou výnimkou, že "Umiestnenie" musí byť v stĺpcoch. Vykonajte to. Po zadaní hodnôt argumentov do tabuľky vyplňte bunku B2 vzorcom na výpočet Z (3). Funkcia Sin je v matematickej kategórii „Sprievodcovia funkciami“. - 33 - Copyright OJSC "TsKB "BIBCOM" & LLC "Agency Kniga-Service" Nezabudnite, že vzorec musí obsahovať zmiešané odkazy, pretože hodnoty X musia byť vždy vybrané zo stĺpca A a hodnoty Y zo stĺpca riadok 1. Na vyplnenie celých tabuliek použite rukoväť na vyplnenie. Údaje na vykreslenie povrchu sú pripravené, zostáva ich iba vykresliť do diagramu. Rovnako ako v predchádzajúcom cvičení použijeme „Sprievodcu grafom“ (obr. 32 - 35). Najprv vyberte maticu funkčných hodnôt (nemusíte vyberať hodnoty X a Y na Obr. 46!), akýmkoľvek známym spôsobom otvorte sprievodcu grafom a vyberte typ grafu „Povrch“ . Ďalej sa konštrukcia povrchu od konštrukcie grafu nelíši. Konečná schéma bude vyzerať ako na obr. 46. ​​Diagram môžete otočiť alebo upraviť v dialógovom okne Formát 3D povrchu (obr. 47), ktoré je znázornené na obr. 47 sa otvorí kliknutím pravým tlačidlom myši na steny plochy a výberom položky kontextového menu „3D pohľad...“. - 34 - Copyright OJSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" Laboratórna práca №5 Cvičenie 1 Booleovské výrazy v Exceli Zadajme vzorec =7>5 do bunky A1. Vráti sa PRAVDA. Skopírujme obsah A1 do A2 a opravíme vzorec v A2: =3>5. Tento vzorec vráti hodnotu FALSE. Pravé časti oboch vzorcov sú výroky, t.j. výroky, o ktorých možno usúdiť, či sú pravdivé alebo nie. Uvažujme o ďalšom príklade. Zadajte číslo 2 do bunky A4 a vzorec =A4>3 do bunky B4. Vzorec vráti hodnotu FALSE. Do A4 zadajte číslo 6. Vzorec vráti hodnotu TRUE. B4 obsahuje predikát, t.j. príkaz s premennými (v tomto prípade existuje len jedna premenná). V závislosti od hodnoty premenných môže predikát nadobúdať hodnoty TRUE a FALSE. V tomto príklade vzorec odpovedá na otázku: „Je číslo (alebo výsledok výpočtov pomocou vzorca) uložené v bunke A4 väčšie ako 3? V závislosti od hodnoty A4 bude odpoveď ÁNO (PRAVDA) alebo NIE (NEPRAVDA). Vo vzorci =A4>3 možno jeho jednotlivé časti (A4 a 3) považovať za aritmetické výrazy, len za veľmi jednoduché. Zložitejší príklad: =(A4^2-1)>(2*A4+1). V tomto výraze môžete vynechať zátvorky, pretože aritmetické operácie majú vyššiu prioritu ako porovnávacie operátory, ale vďaka zátvorkám je vzorec čitateľnejší. Porovnávacie operácie sú zhrnuté v tabuľke. 1. Tabuľka 1 > väčšie ako >= väčšie alebo rovné< <= меньше или равно меньше = <>rovná sa nerovná sa Všimnite si, že symbol vzťahu väčší alebo rovný je reprezentovaný dvoma znakmi: > a =. Dôvodom je, že na klávesnici nie je znak ≥. Výrok a predikát majú spoločný názov – logický výraz. Dostupné logické operácie, ktoré vám umožňujú vytvárať zložité logické výrazy. Tieto operácie sú implementované v Exceli ako funkcie (NOT, AND, OR). O logické funkcie argumenty môžu mať iba dve hodnoty: TRUE a FALSE. Funkcia NOT môže mať iba jeden argument, zatiaľ čo funkcie AND a OR môžu mať dva alebo viac argumentov. Príklad 1 Do bunky A1 (s názvom z) napíšte ľubovoľné číslo. Zistite, či patrí do segmentu. Riešenie. Dajte bunke A1 názov z (Vložiť ⇒ Názov ⇒ Priradiť). Zaveďme do A1 číslo 3. Aby z patrilo segmentu , je potrebné, aby boli súčasne pravdivé dva predikáty: z ≥ 2 a z ≤ 5 . Do bunky B1 umiestnime vzorec =I(z>=2;z<=5). Для ввода в формулу имени ячейки нажмите F3 для открытия списка имен. В В1 получим значение ИСТИНА. Следует предостеречь от неверного решения: формулы =2<=z<=5. Введите эту формулу в С1 и убедитесь, что она возвращает ЛОЖЬ! Коварство этой, на первый взгляд, такой естественной формулы в том, что Excel ничего не сообщает о ее некорректности. Пример 2 В ячейке А1 (с именем z) записано число. Выяснить, принадлежит ли оно одному из лучей на числовой оси: (-∞,2) или (5,∞). Решение. Для того чтобы z принадлежал хотя бы одному из лучей, нужно, чтобы был истинным хотя бы один из предикатов: z < 2 или z >5. Do bunky D1 vložte vzorec =OR(z<2;z>5). A1 obsahuje číslo 3, takže vzorec vráti FALSE. Problém mohol byť vyriešený inak, berúc do úvahy skutočnosť, že pracovný hárok obsahuje vzorec na kontrolu, či číslo z patrí do segmentu . Uvedené dva lúče tvoria pridanie k tomuto segmentu na číselnej osi. Do bunky E1 zadáme vzorec = NOT (B1). Zadaním rôznych čísel do bunky A1 overte, že vzorce v bunkách D1 a E1 dávajú rovnaké výsledky. V praxi sa logické výrazy „vo svojej čistej forme“ spravidla nepoužívajú. Logický výraz slúži ako prvý argument funkcie IF: IF(log_výraz, hodnota_ak_pravda, hodnota_ak_nepravda) Druhý argument obsahuje výraz, ktorý sa vyhodnotí, ak logický_výraz vráti hodnotu TRUE, a tretí argument obsahuje výraz, ktorý sa vyhodnotí, ak logický_výraz vráti FALSE. Príklad 3 1. Zadajte do bunky A2 vzorec, ktorý vráti z+1, ak z >1 a z inak: = IF(z>1;z+1;z). (V Sprievodcovi funkciami je IF v kategórii "Boolean", rovnako ako funkcie AND, OR, NOT.); 2. Ak z > 60, potom v bunke B2 zobrazte hlásenie „Prekročená prahová hodnota“, v opačnom prípade z: =IF(z>60;"Prekročená prahová hodnota";z) Upozorňujeme, že text vo vzorcoch sa zadáva v úvodzovky . 3. Ak z ∈ , potom vráťte z, ak z< 10, то возвращать 10, если z >25, potom vráti 25. Výraz pre túto podmienku bude vyzerať asi takto (vzorec napíšeme 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"), potom sa skopíruje do C23:D23. Túto funkciu je možné použiť na vyriešenie zložitejšieho problému: aké bolo celkové množstvo zrážok v roku 1993 v tých mesiacoch, ktoré boli suché v roku 1994. Riešenie je dané vzorcom = SUMIF(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;IF(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; IF(B3:B14<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))=POČET(A1:A10)1;"zvyšuje sa";"nerastie")) Teraz analyzujme tento vzorec: A2:A10-A1:A9 A8 sa odpočíta od A9 atď.) - tvorí blok pozostávajúci z prvých rozdielov prvkov pôvodného bloku; IF(А2:А10-А1:А9>0;1;0) – tvorí blok ukazovateľov pozitívnych prvých rozdielov; SUM(AK(A2:A10-A1:A9>0;1;0)) – spočíta počet nenulových prvkov v bloku ukazovateľov; COUNT(А1:А10)-1 – vypočíta veľkosť bloku ukazovateľov rovnajúcu sa veľkosti pôvodného bloku zmenšeného o 1; ak sa počet nenulových prvkov v bloku ukazovateľov rovná veľkosti bloku ukazovateľov, potom sa postupnosť zvyšuje, inak nie. Pokúste sa z nich postupne zostaviť zodpovedajúce bloky a konečné funkcie, aby ste dosiahli jasné pochopenie toho, ako sa skladá konečný vzorec. - 43 - Copyright OJSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" Cvičenie 5 Maticové operácie v Exceli Najjednoduchšie operácie, ktoré možno vykonávať s maticami: sčítanie (odčítanie), násobenie číslom, násobenie, transpozícia, kalkulácia inverzná matica . Príklad 12 Sčítanie matice a násobenie matice číslom. Pridajte matice M a N, kde − 1 0 4  2 − 3 7 M = a N =   2 − 3 5 .    − 1 5 6 Riešenie. Matice M a N zavedieme do blokov A1:C2 a E1:G2. V bloku A4:C5 zavedieme tabuľkový vzorec (=A1:C2+E1:G2). Všimnite si, že vybraný blok má rovnaké rozmery ako pôvodné matice. Čo sa stane, ak pred zadaním vzorca vyberiete blok A4:D6? #N/A sa objaví v bunkách „navyše“, t.j. "Nedostupné". A ak vyberiete A4: B5? Zobrazí sa iba časť matice bez akýchkoľvek správ. Skontrolujte. Pomocou názvov je zadávanie vzorca tabuľky oveľa jednoduchšie. Dajte rozsahom A1:C2 a E1:G2 názvy M a N (vykonajte príkaz pre každý blok "Vložiť" ⇒ "Názov" ⇒ "Priradiť"). V bloku E4:G5 zadajte tabuľkový vzorec (=M+N). Výsledok by mal byť samozrejme rovnaký. Teraz vypočítajme lineárnu kombináciu matíc 2M-N. V bloku A7:C8 zavedieme tabuľkový vzorec (=2*M-N). Mali by ste dostať nasledujúce výsledky:  5 − 6 10 1 − 3 11 M +N = a 2 M − N = − 4 13 7  .    1 2 11 Vyššie uvedené príklady nás vedú k záveru, že zvyčajná operácia násobenia vo vzťahu k blokom nie je celkom ekvivalentná násobeniu matíc. Pre maticové operácie v Exceli sú totiž funkcie zahrnuté v kategórii "Matematické": MOPRED - výpočet determinantu matice; MOBR - výpočet inverznej matice; MULTIP - maticové násobenie; TRANSPOSE - transpozícia. Prvá z týchto funkcií vracia číslo, takže sa zadáva ako normálny vzorec. Ostatné funkcie vrátia blok buniek, takže ich treba zadať ako tabuľkové vzorce. Prvé písmeno „M“ v názve troch funkcií je skratkou pre slovo „Matrix“. Príklad 13 Vypočítajte determinant a inverznú maticu pre maticu - 44 - Copyright JSC "Central Design Bureau "BIBCOM" & LLC "Agency Kniga-Service" − 73 78 24 A =  92 66 25 .   − 80 37 10  Skontrolujte správnosť výpočtu inverznej matice jej vynásobením pôvodnou. Opakujte tieto kroky pre rovnakú maticu, ale s prvkom a33=10,01. Riešenie. Pôvodnú maticu umiestnime do bloku A1:C3. Do bunky B5 dáme vzorec na výpočet determinantu = MOPRED (A1: C3). V bloku A7:C9 zavedieme vzorec na výpočet inverznej matice. Ak to chcete urobiť, vyberte blok A7:C9 (má tri riadky a tri stĺpce, ako pôvodná matica). Zavedieme vzorec (=MOBR(A1:C3)). Aj keď používate Sprievodcu funkciami, zadávanie musíte dokončiť stlačením kombinácie kláves Shift+Ctrl+Enter (namiesto kliknutia na tlačidlo OK). Ak ste zabudli vopred vybrať blok A7:C9 a zadali ste vzorec do bunky A7 ako bežný vzorec Excel (dokončíte stlačením klávesu Enter), nemusíte ho zadávať znova: vyberte A7:C9, stlačte F2 (upraviť ), ale nemeňte vzorec, len stlačte klávesy Obr. 54 Shift+Ctrl+Enter. Skopírujte blok A1:C9 do bloku E1:G9. Mierne zmeňte jeden prvok pôvodnej matice: do bunky G3 namiesto 10 zadajte 10.01. Zmeny v determinante a v inverznej matici sú markantné! Tento špeciálne vybraný príklad ilustruje numerickú nestabilitu výpočtu determinantu a inverznej matice: malá porucha na vstupe dáva veľkú poruchu na výstupe. Pre ďalšie výpočty priradíme na pracovnom liste k maticám názvy: A1: C3 - A, A7: C9 - Ainv, E1: G3 - AP, E7: G9 - APinv. Aby sa tieto názvy objavili v už zadaných vzorcoch, vyberte zodpovedajúce vzorce, vyberte položku ponuky „Vložiť“ ⇒ „Názov“ ⇒ „Použiť“, v dialógovom okne vyberte potrebné názvy a kliknite na „OK“. Teraz skontrolujme správnosť výpočtu inverznej matice. V bloku A12:C14 zavedieme vzorec (=MUMNOT(A,Ainv)) a v bloku E12:G14 - vzorec (=MUMNOT(AP,APinv)). Mali by ste dostať výsledok ako na obr. 54. Ako sa očakávalo, výsledné matice sú blízke identite. - 45 - Copyright JSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" Všimnite si, že sada maticových operácií v Exceli je slabá. Ak potrebujete vážne pracovať s maticami, je lepšie uchýliť sa k pomoci takých matematických balíkov, ako sú MatLAB (Matrix LABoratory), Mathematica, Derive. - 46 - Copyright OJSC "Central Design Bureau "BIBCOM" & OOO "Agency Kniga-Service" Laboratórna práca č. 6 Cvičenie 1 Hľadanie riešenia V laboratórnej práci č. 4 sme uvažovali o príklade automatického nájdenia funkčnej závislosti Y = f(X). Pripomeňme, že zistenie takejto závislosti je potrebné na predpovedanie hodnôt odozvy – parametra Y na výstupe experimentu z faktora – nezávislých premenných X na vstupe do systému (pozri Laboratórna práca č. 4). Na niektorých Obr. Prezentovaných 55 prípadov Funkcie Excelu nie je to dosť. Preto je dôležité, aby ste si takúto funkciu mohli zvoliť sami pomocou jednej z metód matematickej optimalizácie, napríklad metódy najmenších štvorcov. Jeho podstatou je minimalizovať súčet druhej mocniny rozdielu medzi experimentálnymi (Yexper) a vypočítanými (Ycalculation) dátami: n ∑ (Yexper,i − Ycalculation,i) 2 , i =1 (4) kde n v našom probléme sa rovnalo 10 Otvorte Lab 4 a pokračujte v dokončovaní pracovného hárka. Experimentálne Y už boli predstavené. Teraz vyplňte tabuľku vypočítaným Y. Na to potrebujeme dodatočnú tabuľku koeficientov, ktorých hodnoty sa najprv rovnajú 1 (obr. 55). Teraz zadajte polynómový vzorec druhého stupňa (1) pre Yvýpočet (obr. 55). Ďalšou úlohou je 56 vyberte koeficienty rovnice tak, aby rozdiel medzi Ycalculation a Yexper bol minimálny. Na to je potrebné zadať vzorec na výpočet druhej mocniny rozdielu (3) a vzorec na výpočet Pearsonovho kritéria na posúdenie presnosti nášho výpočtu (obr. 56). Obidva vzorce sú vstavané do Excelu a sú príkladmi funkcií, pre ktoré sa zaobídete bez zadávania vzorcov tabuľky (pozrite si Laboratórium č. 4 vyššie). Otvorte Sprievodcu funkciami akýmkoľvek spôsobom, ktorý poznáte. V kategórii Matematika vyberte vzorec SUMQDIFF a kliknite na tlačidlo Ok. V druhom okne Sprievodcu funkciami na obr. 57 Ako pole_x zadajte pole Yexper, ako pole_y, pole Ycalculation a kliknite na tlačidlo Ok. Vzorec na výpočet Pearsonovho kritéria je v kategórii "Štatistické" (funkcia PEARSON). V druhom okne Sprievodcu funkciou tiež zadajte pole Yexper ako pole_x, pole Ycalculation ako pole_y a kliknite na tlačidlo Ok. Na nájdenie hodnôt koeficientov má Excel doplnok Riešiteľ, ktorý vám umožňuje riešiť problémy hľadania najväčších a najmenších hodnôt, ako aj riešiť rôzne rovnice. Vyberte bunku, do ktorej je zadaný vzorec na výpočet druhej mocniny rozdielu a vykonajte príkaz „Nástroje“ ⇒ „Hľadať riešenie“. Ak v ponuke „Služba“ takýto príkaz nie je, musíte najskôr vykonať príkaz „Služba“ ⇒ „Doplnky“ a v dialógovom okne, ktoré sa otvorí, prepnite prepínač do stĺpca „Vyhľadať riešenie“ ( Obr. 57) a až potom vykonajte „Servis“ ⇒ „Hľadať riešenie“. V dialógovom okne "Hľadať riešenie" (obr. 58) zadajte nasledujúce parametre: adresu cieľovej bunky s hodnotou, ktorá sa má vybrať (adresa bunky so vzorcom pre súčet druhej mocniny rozdielu) , ak ste ju predtým vybrali, adresa sa umiestni automaticky; v poli "Equal to:" nastavte prepínač na "minimálnu hodnotu"; - 48 - Copyright JSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" v poli "Changing cells" zadajte rozsah buniek pre variabilné koeficienty. Tlačidlo "Parametre" sa používa na zmenu a konfiguráciu parametrov vyhľadávania. Na obr. 59 ich počet zahŕňa: spôsob riešenia úlohy, čas výpočtov a presnosť výsledkov. Vo väčšine prípadov však stačí použiť predvolené nastavenia. Hľadanie riešenia sa vykonáva po kliknutí na tlačidlo "Spustiť". Ak je hľadanie riešenia úspešne ukončené, výsledky výpočtov sa zapíšu do zdrojovej tabuľky a na obrazovke sa zobrazí dialógové okno „Výsledky hľadania riešenia“ (obr. 59), pomocou ktorého môžete uložiť nájdené riešenia do zdrojovej tabuľky, obnoviť pôvodné hodnoty, uložiť výsledky hľadania riešenia vo forme skriptu, vygenerovať správu o výsledkoch operácie vyhľadávania riešenia. Porovnajte výsledné hodnoty koeficientov s koeficientmi v rovnici trendovej čiary. Pridajte vypočítané hodnoty Y do grafu. Ak to chcete urobiť, prejdite do okna diagramu, kliknite pravým tlačidlom myši kdekoľvek v ňom a z kontextovej ponuky vyberte príkaz "Počiatočné údaje". V dialógovom okne s rovnakým názvom, ktoré sa otvorí (obr. 60), prejdite na kartu "Riadok" a kliknite na tlačidlo "Pridať". V poli Názov kliknite na tlačidlo minimalizovať okno, Obr. 60 prejdite na hárok s vašimi údajmi, vyberte bunku hlavičky stĺpca YVýpočet a vráťte sa do okna pomocou tlačidla maximalizácie okna. Podobne Obr. 58 - 49 - Copyright OJSC "TsKB "BIBCOM" & LLC "Agency Book-Service" zadajte "X-values" (rozsah buniek s X-hodnotami alebo teplotou) a "Y-values" (rozsah buniek s vypočítanými hodnoty Y). Po dokončení zadávania stlačte tlačidlo OK. Upozorňujeme, že body výpočtu Y padli na trendovú čiaru, ktorú sme vytvorili skôr (obr. 61). Nakoniec si súbor uložte, použijeme ho v ďalšej lekcii (pozrite si cvičenie č. 7 nižšie). y = -0,0054x2 + 0,6014x - 5,9667 2 R = 0,9817 Y=f(x) Yexp Yvýpočetný polynóm (Yexp) 12 10 8 6 Y 4 2 0 -2 10 20 30 40 50 60 X 61 - 50 - 70 80 90 100 Copyright JSC "Central Design Bureau "BIBCOM" & LLC "Agency Kniga-Service" Laboratórna práca №7 Cvičenie 1 Nájdenie riešenia dvojparametrového problému v Exceli z jednej premennej). V skutočnosti sú takéto jednoduché závislosti dosť zriedkavé. Častejšie sa musíte zaoberať multiparametrovými funkciami. Ako zvládnuť takéto závislosti a ako ich vizualizovať, uvažujme na príklade dvojparametrového problému. Nech sa urobí experiment, napríklad zmeriame závislosť nejakého parametra od teploty a tlaku. Priemerná teplota bola 100°C. Krok zmeny je 50 °C. Priemerný tlak - 2 atm. Krok zmeny je 1 atm. Takýto systém bude opísaný vzťahom: Y = f (X1, X 2) , (5) čo je plocha, ktorá sa často zobrazuje v podobe podobnej vrstevnicovej mape (obr. 62). Ryža. 62 - 51 - Copyright OJSC "TsKB "BIBCOM" & LLC "Agency Kniga-Service" Na nájdenie tejto závislosti pre náš prípad použijeme prípravu lekcií 4 a 6. Ak to chcete urobiť, otvorte svoj uložený súbor a prejdite na dátový hárok. Kliknite na skratku Hárok a vyberte príkaz Presunúť/Kopírovať (obr. 63). V dialógovom okne, ktoré sa otvorí (obr. 64), si môžeme vybrať, kam chceme náš hárok presunúť (skopírovať) (do aktuálnej knihy alebo do novej). Vyberte názov aktuálnej knihy; pred ktorý list chceme umiestniť aktuálny list alebo jeho kópiu. Vyberte „(presunúť na koniec)“. Nezabudnite zaškrtnúť políčko „Vytvoriť kópiu“, inak sa hárok jednoducho presunie na koniec knihy. Potom kliknite na tlačidlo OK. V predvolenom nastavení Excel vytvorí kópiu s názvom aktuálneho hárka, pričom na koniec pripojí číslo kópie v zátvorkách. Pre pohodlnosť si to premenujme. Ak to chcete urobiť, kliknite na štítok listu a vyberte príkaz "Premenovať" (obr. 63); zadajte nový názov, napríklad „Experiment_2“ a stlačte kláves „Enter“. Najprv prebudujeme tabuľku zdrojových údajov, ako je znázornené na obr. 65. Vyberte dve bunky v hornej časti starej tabuľky (tie, v ktorých bol umiestnený názov parametra „Teplota“ a jeho hodnota) a vykonajte príkaz „Vložiť“ ⇒ „Cells ...“. Tým sa otvorí dialógové okno Pridať bunky s návrhom ich umiestnenia (Obrázok 66). Nastavte prepínač do polohy "bunky s posunom nadol" a kliknite na tlačidlo OK. - 52 - Obr. 63 Obr. 64 Obr. 65 Obr. Vyberte prázdny stĺpec C (kliknite na hlavičku tohto stĺpca) a vykonajte príkaz "Vložiť" ⇒ "Stĺpce". Vykonajte potrebné zmeny v tabuľke (obr. 65). Prineste experimentálnu tabuľku podobným spôsobom, ako je znázornené na obr. 67. Pripomeňme, že nadpisy stĺpcov „Teplota“ a „Tlak“ musia byť zadané podľa vzorcov, aby bol obrobok univerzálnejší. Ryža. 67 Teraz vyplňte údaje tabuľky "Pokus". Súradnice bodov 1 - 9 je možné vypočítať podľa obr. 62 podľa nasledujúcich vzorcov: Č. 1 2 3 4 5 6 7 8 9 Tepl. Xav,1-krok Xav,1 Xav,1+krok Xav,1-krok Xav,1 Xav,1+krok Xav,1-krok Xav,1 Xav,1+krok tlak Xav,2-Step Xav,2-Step Xav,2-Step Xav,2 Xav,2 Xav,2 Xav,2-Step Xav,2-Step Xav,2-Step možnosť kopírovania. Musíme vziať hodnoty Yexpera z experimentu. Nech sa rovnajú: číslo bodu Yexper 1 1 2 7 3 5 4 17 5 25 6 15 . - 53 - 7 3 8 10 (6) 9 4 Copyright JSC "Central Design Bureau "BIBCOM" & OOO "Agency Kniga-Service" Pred zadaním vzorca (6) je potrebné upraviť tabuľku koeficientov, ako je uvedené na obr. 68 zadaním počiatočných hodnôt koeficientov 1. Na výber funkcie použijeme metódu minimalizácie súčtu druhých mocnín rozdielu medzi experimentálnymi (Yexper) a vypočítanými (Ycalculation) údajmi, ktoré sme uvažovali v posledná lekcia. Ryža. 68 Vzorce na výpočet druhej mocniny rozdielu a vzorec na výpočet Pearsonovho kritéria už máme na hárku. Teraz stačí opraviť odkazy v nich a vykonať. Hľadanie riešenia prebieha rovnako ako v prípade jednoparametrovej funkcie, ale keďže naša závislosť je zložitejšia, je potrebné otvoriť podokno "Parametre" v dialógu "Hľadať riešenie" box (obr. 69) a nastavte nasledujúce možnosti: tolerancia - 1%; "Automatické škálovanie"; odhady - "kvadratický"; rozdiely - "Centrálne". Ryža. 69 Potom kliknite na tlačidlo OK av okne „Vyhľadať riešenie“ - „Spustiť“. Ak sa na prvý pokus nedosiahne uspokojivá presnosť, možno operáciu hľadania riešenia zopakovať. Nakoniec nám zostáva už len postaviť povrch. Aby sme to urobili, najprv zostavíme dátovú maticu na novom hárku (obr. 70). Prejdite na nový hárok a zadajte názov tabuľky. - 54 - Copyright OJSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" Obr. 70 hodnôt X a Y sa vypočíta pomocou vzorcov. Ak chcete zadať prvú hodnotu pre tlak, zadajte "=", potom prejdite na list "Experiment_2" a kliknite na bunku s hodnotou minimálneho tlaku (v našom prípade je to 1) a stlačte kláves "Enter". Na zadanie minimálnej hodnoty teploty je potrebné vykonať rovnaké kroky. Následné hodnoty teploty a tlaku vypočítame podľa vzorca: Yi = Yi −1 + Ymax − Ymin , l (7) maximálna hodnota teploty a tlaku, respektíve l je mriežkový krok (nech sa rovná 10 ). Zadajte vzorce na výpočet druhej hodnoty tlaku a teploty. Budú vyzerať asi takto: =B4+(Experiment_2!$G$11-Experiment_2!$G$3)/10. Na zadanie tretieho atď. hodnoty teploty a tlaku, použite značku plnenia. Zostáva zadať iba hodnoty funkcie (5). Vezmite hodnoty koeficientov z listu "Experiment_2". Majte na pamäti, že koeficienty by sa mali uvádzať v absolútnych číslach, zatiaľ čo referencie teploty a tlaku by sa mali kombinovať. Vzorec by mal vyzerať približne takto: =Experiment_2!$B$7+Experiment_2!$B$8*$A5+Experiment_2!$B$9*B$4 +Experiment_2!$B$10*$A5^2+Experiment_2!$B$11* $ A5*B$4+ Experiment_2!$B$12*B$4^2 Údaje na vykreslenie povrchu sú pripravené, zostáva ich už len vykresliť do diagramu. - 55 - Copyright JSC "TsKB "BIBCOM" & LLC "Agency Book-Service" Použite "Sprievodcu diagramom" výberom typu diagramu "Povrch" (pozri Laboratórna práca č. 4). Konečná podoba diagramu bude vyzerať podobne ako na obr. 71. Obr. 71 - 56 - Copyright JSC "TsKB "BIBCOM" & OOO "Agentúra Book-Service" 1. 2. 3. 4. 5. Referencie Fulton, D. Ovládajte Microsoft Excel 2000 sami. 10 minút na lekciu. / D. Fulton. - M.: Williams Publishing House, 2001. - 224 s. Levin, A.Sh. Excel je veľmi jednoduchý! / A.Sh. Levin. - Petrohrad: Peter, 2004. - 74 s. Bezručko, V.T. Workshop ku kurzu "Informatika". Práca s Windows 2000, Word, Excel: Proc. príspevok. / V.T. Bezručko. - M.: Financie a štatistika, 2003. - 544 s. Lavrenov, S.M. Excel: Zbierka príkladov a úloh. / CM. Lavrenov - M.: Financie a štatistika, 2004. - 336 s. Vorobyov, E.S. Základy informatiky. Ako pracovať v prostredí MS Office. Proc. príspevok / E.S. Vorobyov, E.V. Nikolaev, Vorobieva F.I., Kazaň. štát technol. un-t. Kazaň, 2005. - 84 s. - 57 - Copyright OJSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" Obsah Laboratórna práca č. 1 ........................ ....................................................... ...... .................. 3 Cvičenie 1: Pochopenie pojmov tabuľky Excel .................. ................ ...................... 3 Cvičenie 2: Použitie základných tabuľkových techník: Zadávanie údajov do bunka. Formátovanie písma. Zmena šírky stĺpca. Automatické dopĺňanie, zadávanie vzorcov, orámovanie tabuľky, zarovnanie textu na stred výberu, množina dolných a horných indexov .................................. ................................... 6 Laboratórna práca číslo 2 .......... ...................................................................... ................................... 10 Cvičenie 1. Upevnenie základných zručností práce s tabuľkami, oboznámenie sa s pojmy: triedenie údajov, typy zarovnania textu v bunke, číselný formát. ................... 10 Cvičenie 2. Predstavenie konceptu "absolútny odkaz", nastavenie presnej hodnoty šírky stĺpca pomocou príkazov horizontálneho menu. Vloženie funkcie pomocou Sprievodcu funkciami ...................................................... ...................................................................... ....... 13 Cvičenie 3. Predstavenie pojmu "názov bunky"...... ........................ ...................... 16 Laboratórium č. 3 ...................... ............................................................. ........................... 19 Cvičenie 1: Zmena orientácie textu v bunke, oboznámenie sa s možnosťami databáz Excel. Triedenie údajov podľa viacerých kľúčov ................................................ ................. ...................... 19 Laboratórium č. 4 ....... ...................................................... ...................................................................... 27 Cvičenie 1: Vytváranie a úprava grafov v dokumente programu Excel ............... 27 Cvičenie 2: Vytváranie a úprava plôch v dokumente programu Excel .. 33 Cvičenie #5 ...... ............................................................ ................................................... .. 35 Cvičenie 1. Boolean výrazy v Exceli ............................................. . ............. 35 Cvičenie 2. Súhrnné funkcie v Exceli ........................ .................................. 37 Cvičenie 3. Tabuľkové vzorce v Exceli ....... ...................................................... 39 Cvičenie 4 Distribučné funkcie v Exceli .................................................. ................... ... 41 Cvičenie 5. Maticové operácie v Exceli ................... ........................... ................. 43 Laboratórium č. 6 .. ............................................................. ................................... ............... 47 Cvičenie 1. Hľadanie riešenia . ............................................................. ............................. 47 Laboratórium č. 7 ................. ...................................................................... ...................................... 51 Cvičenie 1 ........ ............................................. ..................... 57 - 58 - Copyright OJSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" Editor: T.M. Petrova Licencia č. 020404 zo dňa 6. marca 1997. Podpísaná na publikáciu Písomný príspevok. Uch.-ed. l. 2005. Formát tlače 60x84 1/16 arb. rúra l. Náklad 100 kópií. Objednávka „C“ 60 Vydavateľstvo Kazanskej štátnej technologickej univerzity Offsetové laboratórium Kazanskej štátnej technologickej univerzity 420015, Kazaň, K. Marx, 68