Laboratórne vzorce v exceli. Používanie vzorcov a funkcií v MS Excel. Laboratórne práce v programe Microsoft Excel
Dané: a, b, c, h, l, m, x - ľubovoľné čísla.
Vypočítať:
Výsledok vykonania:
V=1/3*PI()*B1*(B2*B2+B2*B3+B3*B3)
Test 2. Použitie relatívnych a absolútnych odkazov vo vzorcoch. Vytvorenie tabuľky „Nákup tovaru s predsviatočnou zľavou“.
Odpovede na bezpečnostné otázky
1. Čo je vzorec v Exceli? Aká je jeho štruktúra? Aké prvky môže vzorec obsahovať? Aké sú pravidlá pre zadávanie a úpravu vzorcov v Exceli?
Vzorec v Exceli je postupnosť znakov začínajúca znakom rovnosti „=“. Táto postupnosť znakov môže zahŕňať konštantné hodnoty, odkazy na bunky, názvy, funkcie alebo operátory.
Musíte zadať vzorec začínajúci znakom rovnosti. Je to potrebné, aby Excel pochopil, že ide o vzorec a nie údaje, ktoré sa zadávajú do bunky.
3. Ako môžete kopírovať a presúvať vzorce?
Keď presuniete bunku so vzorcom, odkazy obsiahnuté vo vzorci sa nezmenia. Pri kopírovaní vzorca sa odkazy na bunky môžu meniť v závislosti od ich typu.
4. Ako vzorce automaticky dopĺňajú bunky?
Rovnako ako automatické dopĺňanie buniek údajmi. Ak potrebujete vykonať rovnaké výpočty v tabuľke, použite funkciu automatického dopĺňania, aby ste nemuseli zadávať rovnaké vzorce znova a znova.
5. Ako sa upravujú vzorce?
Vykonávanie zmien v hárku, ako aj odstraňovanie chýb, sa vykonáva úpravou vzorcov v bunkách. Režim úpravy buniek je možné aktivovať niekoľkými spôsobmi.
1. Úprava vzorca v riadku vzorcov:
Vyberte bunku so vzorcom, ktorý chcete upraviť
Kliknite myšou a umiestnite kurzor do riadka vzorcov.
2. Úprava vzorca priamo v bunke:
Dvakrát kliknite myšou a umiestnite kurzor do bunky. Táto metódaÚprava vzorca funguje len vtedy, ak je povolená možnosť Upraviť priamo v bunke na karte Upraviť v dialógovom okne Možnosti ponuky Nástroje.
Povoliť „režim úprav“:
Vyberte bunku a stlačte kláves
Po dokončení úpravy vzorca je potrebné vypnúť „režim úprav“ v bunke - stlačte kláves
6. Čo je funkcia v Exceli? Aká je jeho štruktúra?
Funkcie v Exceli sa používajú na vykonávanie štandardných výpočtov v zošitoch. Hodnoty, ktoré sa používajú na vyhodnotenie funkcií, sa nazývajú argumenty. Hodnoty vrátené funkciami ako odpoveď sa nazývajú výsledky. Okrem vstavaných funkcií môžete použiť pri výpočtoch vlastné funkcie, ktoré sú vytvorené pomocou Excelu. Argumenty funkcie sa píšu v zátvorkách bezprostredne za názvom funkcie a navzájom sa oddeľujú bodkočiarkou „;“. Zátvorky umožňujú Excelu určiť, kde začína a končí zoznam argumentov. Argumenty musia byť umiestnené v zátvorkách. Pamätajte, že pri písaní funkcie musia byť otváracie a zatváracie zátvorky a medzi názov funkcie a zátvorky by ste nemali vkladať medzery.
Argumenty môžu byť čísla, text, boolovské hodnoty, polia, chybové hodnoty alebo odkazy. Argumenty môžu byť buď konštanty alebo vzorce. Na druhej strane môžu tieto vzorce obsahovať ďalšie funkcie. Funkcie, ktoré sú argumentom inej funkcie, sa nazývajú vnorené. IN Vzorce programu Excel Môžete použiť až sedem úrovní vnorenia funkcií.
Zadané vstupné parametre musia mať platné hodnoty pre daný argument. Niektoré funkcie môžu mať voliteľné argumenty, ktoré nemusia byť prítomné pri výpočte hodnoty funkcie.
Pre jednoduchosť používania sú funkcie v Exceli rozdelené do kategórií: funkcie správy databázy a zoznamov, funkcie dátumu a času, DDE/Externé funkcie, inžinierske funkcie finančné, informačné, logické, prehliadacie a prepájacie funkcie. Okrem toho sú prítomné nasledujúce kategórie funkcií: štatistické, textové a matematické.
Pomocou textových funkcií je možné spracovať text: extrahovať znaky, nájsť tie, ktoré potrebujete, písať znaky na presne definované miesto v texte a mnoho ďalšieho.
Pomocou funkcií dátumu a času môžete vyriešiť takmer akýkoľvek problém súvisiaci s dátumom alebo časom (napríklad určenie veku, výpočet pracovných skúseností, určenie počtu pracovných dní v akomkoľvek časovom období).
Logické funkcie pomáhajú vytvárať zložité vzorce, ktoré v závislosti od splnenia určitých podmienok budú vykonávať rôzne druhy spracovania údajov.
8. Aký je rozdiel medzi vzorcami a funkciami? Ako vygenerovať funkčný text v dialógovom okne?
Funkcie môžu byť súčasťou vzorcov.
Dialógové okno má dve zoznamové okná a niekoľko tlačidiel. Pre pohodlie sú vstavané funkcie rozdelené do kategórií. Okno s názvom "Kategória" obsahuje zoznam kategórií funkcií. A v okne s názvom „Funkcia“ sa zobrazí abecedné poradie zoznam funkcií, vybraná kategória.
Pod zoznamom funkcií je veľmi zhustená nápoveda o zvýraznenej funkcii. Túto nápovedu si však môžete prečítať iba pomocou JAWS kurzora. Ak kliknete na tlačidlo pomocníka, otvorí sa nové dialógové okno s podrobným pomocníkom k vybranému Excel funkcie.
Kliknutím na tlačidlo „OK“ sa aktivuje druhý krok sprievodcu funkciou – zadanie vstupných parametrov alebo argumentov funkcie. Zvyčajne tu musíte uviesť adresy buniek a rozsahy, ktoré sa podieľajú na výpočtoch tejto funkcie. Po zadaní ďalšieho argumentu musíte stlačiť tabulátor. Na konci musíte stlačiť „Enter“, aby ste aktivovali tlačidlo „Ok“. Obsahuje aj dialógové okno pre zadávanie parametrov referenčné informácie, dostupné len pre JAWS kurzor.
Po zadaní vstupných parametrov vstavanej funkcie Excel vygeneruje text vzorca a umiestni ho do bunky, kde sa nachádzal kurzor pri volaní Sprievodcu funkciou.
9. Ako používať Sprievodcu funkciami?
Aby sme našli vstavanú funkciu Excel, ktorú potrebujeme, musíme prejsť do ponuky "Vložiť" a aktivovať položku "Funkcia". Otvorí sa dialógové okno s názvom „Sprievodca funkciou“.
Závery o vykonanej laboratórnej práci
V procese vykonávania laboratórnych prác som si osvojil techniku práce so vzorcami a funkciami v tabuľkovom procesore Microsoft Office Excel.
Predmet: Excel funkcie
Cieľ:
Spoznajte rôzne triedy funkcií;
Naučte sa používať Sprievodcu funkciami;
Naučte sa používať vnorené funkcie pri práci s tabuľkami.
Funkcie Excel
Funkcia– je závislá premenná veličina, ktorej hodnota sa vypočítava podľa určitých pravidiel na základe hodnôt iných veličín – argumenty funkcie. Excel ponúka veľkú (niekoľko stoviek) sadu štandardných (vstavaných) funkcií, ktoré možno použiť vo vzorcoch, napríklad:
Funkcia – z latinského Functio – prevedenie.
Za názvom funkcie v zátvorkách nasleduje zoznam argumentov oddelených bodkočiarkou. Zoznam argumentov môže pozostávať z čísel, textu, boolovských hodnôt (TRUE alebo FALSE), odkazov, vzorcov, vnorených funkcií. Ak vzorec začína funkciou, pred názvom funkcie je znak " = ».
Na základe povahy argumentov možno vstavané funkcie rozdeliť do troch typov:
So zoznamom argumentov(maximum – 30 argumentov): AVERAGE (A2:C23;E6;200;3) – vráti priemernú hodnotu argumentov
S pevné argumenty: MOC (6,23;4): umocňuje prvý argument (6,24) na mocninu druhého argumentu (4)
Žiadne argumenty:DNES(): Vráti aktuálny dátum.
Zadávanie vzorcov
Postupnosť zadávania funkcie do vzorca:
Názov funkcie;
Otváracia zátvorka;
Zoznam argumentov oddelených bodkočiarkou;
Zátvorka.
Funkciu môžete zadať niekoľkými spôsobmi:
Funkcie a riadok vzorcov
Ak nie sú zadané ručne, argumenty sa zadávajú pomocou riadku vzorcov:
Požadovaný argument zvýraznené tučným písmom – bez neho funkcia nemôže vykonávať spracovanie;
Voliteľný argument sa zobrazuje obvyklým hláskovaním názvu poľa a jeho hodnota sa nemusí zadať. V tomto prípade sa použijú predvolené hodnoty.
Ak vzorec pozostáva z niekoľkých funkcií, na paneli vzorcov sa zobrazia argumenty funkcie, ktoré sú na paneli vzorcov zvýraznené tučným písmom. Ak chcete zobraziť argumenty inej funkcie v riadku vzorcov, musíte kliknúť na jeho názov v riadku vzorcov.
Riadok vzorcov je možné presúvať po obrazovke ťahaním myšou.
Vnorené funkcie
Výsledok vyhodnotenia funkcie možno použiť ako argument inej funkcie. Zavolá sa funkcia použitá ako jeden z argumentov inej funkcie vnorené. Excel podporuje až 7 úrovní vnorenia funkcií.
Napríklad:
IF (A4>0; MAX (A9:B19);0)
Ak chcete zadať funkciu ako argument musíte rozbaliť zoznam v riadku vzorcov a vybrať jeden z nich 10 nedávno použitých alebo kontaktujte do Sprievodcu funkciou pomocou príkazu Ďalšie funkcie..alebo zadajte funkciu manuálne.
Špeciálna vložka
Obsah bunky môže byť reprezentovaný ako kombinácia štyroch vrstiev informácií: vzorec, hodnota, formát a poznámka. Excel umožňuje skopírovať každú vrstvu samostatne. Informácie sa uložia do vyrovnávacej pamäte ako zvyčajne (príkaz Kopírovať) a vkladá sa pomocou príkazu Upraviť\Prilepiť špeciálne…
Ak chcete kopírovať formáty, rovnako ako ostatné aplikácie balíka Office, použite štandardný panel s nástrojmi - Vzorový formát . (Praktická práca "Predpoveď počasia » ).
cvičenie:
Pomocou funkcie vyplňte blok A1:A5 náhodnými číslami v rozsahu [-10,10];
Do bunky B1 zadajte vzorec na výpočet celej časti hodnôt v stĺpci A;
Skopírujte výsledný vzorec do bloku B2:B5;
Použite rovnakú postupnosť operácií na funkcie a bloky:
ABS (A) - C1: C5;
EXP (A) - D1:D5;
SQRT(A) - Ei:E5;
Výpočet zvyšku pri delení 2 – F 1:F 5;
Zaokrúhľovanie od -1 – H1:H5;
Kolo s +1 – G 1:G 5
Napíšte vzorec do bunky A7 sumy prvky prvého stĺpca (A1:A5)
V bunke B7 – aritmetický priemer podľa (B1:B5)
C7 – maximálny prvok z (C1:C6)
D 7 – minimálny prvok (D 1:D 6)
E 7 – počet prvkov (E1:E6)
F 7 – rozptyl hodnôt (F 1:F 6)
Rozsah I 1:I 6 doplňte hodnotami goniometrických funkcií:
I1 - PI
I2 – Sin (A1)
I3 – Cos (A2)
I4 – Tan (A3)
I5 – Atan (A4)
I6 – Asin (A5)
V riadku 10 zadajte nadpisy polí:
Priezvisko\Meno Dátum narodenia Počet dní
Upravte šírku stĺpcov a vycentrujte nadpisy;
Do bloku A12:A17 uveďte priezviská alebo mená svojich priateľov a známych. V bloku B12:B17 sú ich dátumy narodenia. Zadajte dátum v európskom formáte;
Do bunky C9 zadajte aktuálny dátum;
V bunke C12 vzorec na výpočet počtu dní prežitých osobou pre aktuálny dátum;
Medzi stĺpce Dátum narodenia a Počet dní vložte stĺpec Deň v týždni;
Do prvej bunky stĺpca zadajte funkciu na výpočet dňa v týždni podľa dátumu narodenia. Skopírujte výsledný vzorec do všetkých buniek stĺpca;
V stĺpci F napíšte „Mladý“ alebo „Starý“ vedľa každého priezviska pomocou logickej funkcie IF. Zadajte funkciu pomocou Sprievodcu funkciou (AK Počet dní<15000, то «Молодой», иначе «Старый»);
Výslednú tabuľku uložte na disk do osobného priečinka (názov skupiny).
Kontrolné otázky:
Metódy zadávania vzorcov do buniek;
Tabuľka vzorcov;
Povinné a voliteľné argumenty vo vzorcoch;
Postup na vykonávanie vnorených funkcií v programe Microsoft Excel;
Algoritmus špeciálneho vkladania do buniek.
Laboratórne práce z informatiky
Základy práce s počítačom 1
Vytváranie vzorcov pomocou Sprievodcu funkciou 1
Vytváranie vzorcov pomocou tlačidla Suma 2
Úprava vzorcov 2
Zaokrúhľovanie 3
Ukážková suma 3
Umocnenie a odmocnenie 4
Presúvanie a kopírovanie vzorcov 4
Použitie odkazov vo vzorcoch 4
Používanie názvov buniek a rozsahov 6
Kontrola chýb 7
Chyby vo funkciách a argumentoch 7
Sledovanie vzťahov medzi vzorcami a bunkami 8
Používanie logických funkcií 9
Podmienené formátovanie 10
Zvýraznenie hodnôt 10
Zvýraznenie extrémnych hodnôt 12
Formátovanie pomocou histogramu 13
Formátovanie pomocou trojfarebnej škály 13
Formátovanie pomocou sady ikon 14
Správa pravidiel podmieneného formátovania 15
Laboratórna práca č.6
Cvičenie 6: Základy výpočtov v Exceli Vytváranie vzorcov pomocou Sprievodcu funkciou
Funkcie vám umožňujú zjednodušiť vzorce, najmä ak sú dlhé alebo zložité. Funkcie slúžia nielen na priame výpočty, ale aj na prevod čísel, napríklad na zaokrúhľovanie, vyhľadávanie hodnôt, porovnávanie atď.
Na vytváranie vzorcov s funkciami zvyčajne používate skupinu Knižnica funkcií karty Vzorce.
Vyberte bunku, do ktorej chcete zadať vzorec.
Kliknite na tlačidlo pre požadovanú kategóriu funkcií v skupine Knižnica funkcií a vyberte požadovanú funkciu.
V okne Funkcia Argumenty Do príslušných polí zadajte argumenty funkcie. Odkazy na bunky je možné zadať pomocou klávesnice, ale pohodlnejšie je vybrať bunky myšou. Ak to chcete urobiť, umiestnite kurzor do príslušného poľa a vyberte požadovanú bunku alebo rozsah buniek na hárku. Na uľahčenie výberu buniek je okno Funkcia Argumenty možno presunúť alebo zložiť. Ako tooltip okno zobrazuje účel funkcie a v spodnej časti okna je zobrazený popis argumentu, v poli ktorého sa práve nachádza kurzor. Upozorňujeme, že niektoré funkcie nemajú žiadne argumenty.
V okne Funkcia Argumenty kliknite na tlačidlo OK.
Na vloženie funkcie nie je potrebné používať tlačidlá kategórie funkcií v skupine Knižnica funkcií. Na výber požadovanej funkcie môžete použiť Sprievodcu funkciami. Navyše to možno urobiť pri práci na ktorejkoľvek karte.
Názvy funkcií je možné zadávať z klávesnice pri vytváraní vzorcov. Ak chcete zjednodušiť proces vytvárania a znížiť počet preklepov, použite automatické dopĺňanie vzorcov.
Do bunky alebo riadka vzorcov zadajte znak „=“ (rovná sa), za ktorým nasledujú prvé písmená funkcie, ktorú používate. Počas písania sa v rolovacom zozname možných položiek zobrazujú najbližšie hodnoty.
Vyberte požadovanú funkciu dvojitým kliknutím myši na ňu.
Pomocou klávesnice a myši zadajte argumenty funkcie. Potvrďte zadanie vzorca.
Vytváranie vzorcov pomocou tlačidla Súčet
Toto tlačidlo okrem skupiny Knižnica funkcií karty Vzorce(volá sa to tam Autosum), dostupné aj v skupine Úprava karty Domov.
Ak chcete vypočítať súčet čísel v bunkách umiestnených súvisle v jednom stĺpci alebo riadku, vyberte bunku pod alebo napravo od sčítaného rozsahu a kliknite na tlačidlo Sum.
Pre potvrdenie zadania vzorca stlačte kláves Zadajte alebo znova stlačte tlačidlo Sum.
Ak chcete vypočítať súčet náhodne umiestnených buniek, vyberte bunku, v ktorej sa má súčet vypočítať, kliknite na tlačidlo Sum a potom vyberte bunky a/alebo rozsahy buniek, ktoré sa majú sčítať v hárku. Pre potvrdenie zadania vzorca stlačte kláves Zadajte alebo znova stlačte tlačidlo Sum.
Syntax funkcie
SUM),
kde A je zoznam 1 až 30 prvkov, ktoré je potrebné sčítať. Prvok môže byť bunka, rozsah buniek, číslo alebo vzorec. Odkazy na prázdne bunky, text alebo boolovské hodnoty sa ignorujú.
V novej knihe zadajte svoje údaje o akademickom výkone.
Vypočítajte celkové skóre pre každého študenta.
Okrem výpočtu sumy, tlačidlo Sum možno použiť pri výpočte priemernej hodnoty, určovaní počtu číselných hodnôt, zisťovaní maximálnych a minimálnych hodnôt. V tomto prípade musíte kliknúť na šípku tlačidla a vybrať požadovanú akciu: 
Priemerná- výpočet aritmetického priemeru;
číslo- určenie počtu číselných hodnôt;
Maximálne- nájdenie maximálnej hodnoty;
Minimum- zistenie minimálnej hodnoty.
Účelom laboratórnej práce je preštudovať a upevniť zručnosti v zadávaní údajov a používaní vzorcov v Microsoft Excel 2007 .
Zadávanie údajov do tabuľky
Bunky tabuľky môžu obsahovať tri typy údajov: číselné hodnoty (vrátane času a dátumu), text a vzorce. Pracovný hárok, ale v „grafickej vrstve“ v hornej časti hárka, môže obsahovať aj obrázky, grafy, obrázky, tlačidlá a iné objekty.
Zadávanie čísel
Čísla sa zadávajú pomocou horného radu klávesnice alebo numerickej klávesnice. Ako oddeľovač desatinných miest sa používa čiarka alebo bodka, môžete zadať symboly meny. Ak pred číslo zadáte mínus alebo zátvorky, považuje sa to za záporné. Nuly zadané pred číslom program ignoruje. Ak potrebujete získať hodnotu s úvodnými nulami, musí sa interpretovať ako text.
Excel používa na vyjadrenie čísel 15 číslic; keď zadáte 16-miestne číslo, automaticky sa uloží na 15 číslic. Číselné hodnoty sa automaticky zarovnajú k pravému okraju bunky.
Zadávanie dátumov a časov
Excel používa na reprezentáciu dátumov interný systém číslovania dátumov. (Takže najskorší dátum, ktorý program dokáže rozpoznať, je 1. január 1900, tomuto dátumu je priradené poradové číslo 1, ďalšiemu dátumu je priradené poradové číslo 2 atď.). Dátumy sa zadávajú vo formáte známom používateľovi a sú automaticky rozpoznané. Časové hodnoty sa tiež zadávajú v jednom z uznávaných formátov času. Prezentácia dátumu a času priamo na pracovnom hárku je riadená nastavením formátu zobrazenia bunky.
Zadávanie textu
Všetky zadané údaje, ktoré nie sú rozpoznané ako čísla alebo vzorce, sa považujú za textové hodnoty. Textové hodnoty sú zarovnané k ľavému okraju tabuľky. Ak sa text nezmestí do jednej bunky, umiestni sa na vrch susedných buniek, ak sú voľné. Parametre na umiestnenie textu do bunky sa nastavujú pomocou formátu bunky.
Zadanie vzorca
Vzorec je akýkoľvek matematický výraz. Vzorec vždy začína znakom „=“ a môže zahŕňať okrem operátorov a odkazov na bunky aj vstavané funkcie Excelu.
Dátové formáty
Po zadaní údajov do bunky sa Excel automaticky pokúsi určiť jej typ a priradiť bunke príslušný formát – formu prezentácie údajov. Je dôležité priradiť správny formát bunky, aby sa napríklad bunka mohla podieľať na výpočtoch (nie text).
Excel má sadu štandardných formátov buniek, ktoré možno použiť vo všetkých zošitoch (obrázok 2.2.17). Môžete ho aktivovať výberom Domov – Číslo – Formát čísla alebo pomocou kontextového menu pre vybranú bunku na karte Číslo v okne Formát bunky.
Obrázok 2.2.17. Štandardné formáty
Na začiatku majú všetky bunky tabuľky všeobecný formát. Použitie formátov ovplyvňuje spôsob zobrazenia obsahu v bunkách: všeobecné - čísla sa zobrazujú ako celé čísla, desatinné zlomky, ak je číslo príliš veľké, potom ako exponenciálne; numeric – štandardný numerický formát; finančný a peňažný – číslo sa zaokrúhľuje na 2 desatinné miesta, znak meny sa umiestňuje za číslom, peňažný formát umožňuje zobraziť záporné sumy bez znamienka mínus a inou farbou; formát krátkeho dátumu a dlhého dátumu – umožňuje vybrať jeden z formátov dátumu; čas – ponúka niekoľko formátov času na výber; - percento – číslo (od 0 do 1) v bunke sa vynásobí 100, zaokrúhli sa na najbližšie celé číslo a zapíše sa znakom %; zlomkové – používa sa na zobrazenie čísel vo forme obyčajného zlomku a nie desatinného; exponenciálna – určená na zobrazenie čísel ako súčinu dvoch zložiek: čísla od 0 do 10 a mocniny 10 (kladná alebo záporná); text – pri nastavení tohto formátu bude každá zadaná hodnota vnímaná ako text; doplnkové – obsahuje formáty PSČ, PSČ+4, Telefónne číslo, Osobné číslo; všetky formáty – umožňuje vytvárať nové formáty ako vlastnú šablónu.
Používanie nástrojov na urýchlenie zadávania údajov
Pri zadávaní údajov do hárkov tabuľky môžete použiť niektoré techniky na urýchlenie ich zadávania.
1) Automatické dopĺňanie počas písania. Keď zadáte rovnaké hodnoty do viacerých buniek, môžete použiť značku automatického dopĺňania (kríž v pravom dolnom rohu aktívnej bunky) na skopírovanie hodnôt do susedných buniek. Pomocou kontextovej ponuky, ktorá sa otvorí po potiahnutí pravým tlačidlom myši, môžete nastaviť ďalšie parametre automatického dopĺňania (napríklad zadaním čísel 1 a 3 do buniek získate postupnosť čísel v krokoch po 2 pre zvolený rozsah bunky).
2) Použitie progresie. Ak bunka obsahuje číslo, dátum alebo časové obdobie, ktoré môže byť súčasťou série, pri kopírovaní sa jej hodnota zvýši (získa sa aritmetický alebo geometrický postup, zoznam dátumov). Ak chcete nastaviť postupnosť, musíte vybrať tlačidlo Výplň na paneli Úpravy na karte Domov a v zobrazenom dialógovom okne Postup nastaviť parametre pre aritmetickú alebo geometrickú postupnosť.
3) Automatické dokončovanie počas písania. Táto funkcia vám umožňuje automaticky zadávať opakujúce sa textové údaje. Po zadaní textu do bunky si ho Excel zapamätá a pri ďalšom zadávaní po napísaní prvých písmen slova ponúkne možnosť zadanie doplniť. Ak chcete dokončiť zadávanie, stlačte „Enter“. K tomuto príkazu sa dostanete aj výberom položky Vybrať z rozbaľovacieho zoznamu z kontextového menu pomocou pravého tlačidla myši. Funkcia automatického dokončovania funguje len na súvislej sekvencii buniek.
4) Používanie automatických opráv pri písaní. Automatické opravy sú navrhnuté tak, aby pri písaní automaticky nahradili niektoré určené kombinácie znakov inými. Môžete napríklad určiť, že namiesto zadávania viacerých slov zadáte jeden znak. Príkaz je dostupný cez tlačidlo Office – Možnosti Excelu. V Pravopis - Možnosti automatických opráv je potrebné nastaviť text a jeho skratku.
5) Pomocou klávesovej skratky Ctrl+Enter zadajte opakujúce sa hodnoty. Ak chcete zadať rovnaké hodnoty do niekoľkých buniek, môžete ich vybrať, zadať hodnotu do jednej bunky a stlačiť Ctrl+Enter. V dôsledku toho sa do všetkých vybratých buniek zadajú rovnaké údaje.
Overenie údajov pri zadávaní
Ak chcete zabezpečiť, aby sa do hárka zadali správne údaje, môžete zadať kritériá, ktoré sú platné pre jednotlivé bunky alebo rozsahy buniek. Pre nastavenie kontroly spustite príkaz Dáta – Práca s údajmi – Kontrola údajov. V zobrazenom okne (obrázok 2.2.18) nastavte overovacie kritériá na záložke Parametre, text výzvy, ktorú má používateľ zadať na záložke Vstupná správa, a text chybovej správy na záložke Chybové hlásenie. .
Po použití príkazu Dáta – Práca s údajmi – Zakrúžkovať neplatné údaje budú všetky nesprávne údaje zakrúžkované červenou farbou.
Obrázok 2.2.18. Okno pre nastavenie parametrov overovania údajov
Používanie vzorcov
Vzorec v Exceli je matematický výraz, na základe ktorého sa vypočíta hodnota určitej bunky. Vzorce môžu používať: číselné hodnoty; adresy buniek (relatívne, absolútne a zmiešané odkazy); operátory: matematické (+, -, *, /, %, ^), porovnania (=,<, >, >=, <=, < >), textový operátor & (na spojenie niekoľkých textových reťazcov do jedného), operátory vzťahu rozsahu (dvojbodka (:) - rozsah, čiarka (,) - na spojenie rozsahov, medzera - priesečník rozsahov); funkcie.
Zadávanie vzorca vždy začína znakom „=“. Výsledok vzorca sa zobrazí v bunke a samotný vzorec sa zobrazí v riadku vzorcov. Adresy buniek vo vzorci je možné zadať ručne alebo jednoducho kliknutím na požadované bunky.
Po výpočte sa výsledný výsledok zobrazí v bunke a vytvorený vzorec sa zobrazí v riadku vzorcov vo vstupnom okne.
Spôsoby adresovania buniek
Adresa bunky pozostáva z názvu stĺpca a čísla riadku pracovného hárka (napríklad A1, BM55). Vo vzorcoch sú adresy uvedené pomocou odkazov - relatívne, absolútne alebo zmiešané. Vďaka odkazom sa údaje nachádzajúce sa v rôzne časti list, možno použiť vo viacerých vzorcoch súčasne.
Relatívny odkaz označuje umiestnenie požadovanej bunky vzhľadom na aktívnu (t. j. aktuálnu) bunku. Pri kopírovaní vzorcov sa tieto prepojenia automaticky menia v súlade s novou pozíciou vzorca (Príklad zadania odkazu: A2, C10).
Absolútny odkaz poukazuje na presné umiestnenie bunky zahrnuté vo vzorci. Keď skopírujete vzorce, tieto prepojenia sa nezmenia. Ak chcete vytvoriť absolútny odkaz na bunku, umiestnite znak dolára ($) pred označenia stĺpcov a riadkov (Príklad referenčného zápisu: $A$2, $C$10). Na opravu časti adresy bunky zo zmien (podľa stĺpca alebo riadka) pri kopírovaní vzorcov sa používa zmiešaný odkaz s fixáciou požadovaného parametra. (Príklad odkazu: $A2, C$10).
Poznámky
· Aby ste sa vyhli manuálnemu zadávaniu znakov dolára pri písaní odkazov, môžete použiť kláves F4, ktorý vám umožní „pretriediť“ všetky typy odkazov pre bunku.
Vstavané funkcie programu Excel
Každá funkcia má svoju vlastnú syntax a poradie činnosti, ktoré je potrebné dodržiavať, aby boli výpočty správne. Argumenty funkcií sa píšu v zátvorkách a funkcie môžu, ale nemusia mať argumenty, pri ich použití musíte brať do úvahy typ argumentov. Funkcia môže fungovať ako argument inej funkcie, v takom prípade sa nazýva vnorená funkcia. V tomto prípade možno vo vzorcoch použiť až niekoľko úrovní vnorenia funkcií.
Excel 2007 obsahuje matematické, logické, finančné, štatistické, textové a ďalšie funkcie. Názov funkcie vo vzorci je možné zadať ručne z klávesnice (tým sa aktivuje nástroj vzorcov Automatické dokončovanie, ktorý umožňuje vybrať požadovanú funkciu na základe prvých zadaných písmen (obrázok 2.2.19)), alebo ju môžete vybrať v okne Sprievodca funkciami, ktoré sa aktivuje tlačidlom na paneli Knižnica funkcií na karte Vzorce alebo zo skupín funkcií na rovnakom paneli, alebo pomocou tlačidla panela Upraviť na karte Domov.
Obrázok 2.2.19. Vzorce automatického dopĺňania
Vzorce je možné upravovať rovnako ako obsah ktorejkoľvek inej bunky. Ak chcete upraviť obsah vzorca: dvakrát kliknite na bunku so vzorcom alebo stlačte kláves F2, prípadne upravte obsah v riadku na zadanie vzorca.
Pomenovanie a používanie názvov buniek
Excel 2007 poskytuje užitočnú funkciu na pomenovanie buniek alebo rozsahov. To môže byť výhodné najmä pri skladaní vzorcov. Napríklad zadaním názvu Celkom_za_rok pre bunku môžete tento názov použiť namiesto adresy bunky vo všetkých vzorcoch.
Názov bunky môže byť platný v rámci jedného hárka alebo jedného zošita; musí byť jedinečný a nesmie duplicitné názvy buniek. Ak chcete pomenovať bunky, musíte vybrať bunku alebo rozsah a zadať nový názov do záhlavia. Alebo použite tlačidlo Priradiť názov na panel Definované názvy na karte Vzorce a vyvolajte dialógové okno (obrázok 2.2.20) na nastavenie požadovaných parametrov.
Obrázok 2.2.20. Okno na vytvorenie názvu
Ak chcete zobraziť všetky priradené mená, použite príkaz Správca mien. Zoznam všetkých mien s adresami buniek na hárku môžete získať aj príkazom Použiť vo vzorci - Vložiť názvy panela Definované názvy.
Ak chcete vložiť názov do vzorca, môžete použiť príkaz Použiť vo vzorci a vybrať požadovaný názov bunky zo zoznamu.
Komentujte. Názov možno priradiť nielen rozsahom buniek, ale aj vzorcu. To je výhodné pri používaní vnorených vzorcov.
Zobrazenie závislostí vo vzorcoch
Ak chcete pomôcť identifikovať chyby pri vytváraní vzorcov, môžete zobraziť závislosti buniek. Závislosti sa používajú na zobrazenie vzťahov v rámčeku tabuľky medzi bunkami so vzorcami a bunkami s hodnotami, ktoré boli zahrnuté v týchto vzorcoch. Závislosti sa zobrazujú iba v rámci jednej otvorená kniha. Pri vytváraní závislosti používate ovplyvňovacie bunky a závislé bunky.
Ovplyvňujúca bunka je bunka, ktorá odkazuje na vzorec v inej bunke.
Závislá bunka je bunka, ktorá obsahuje vzorec.
Ak chcete zobraziť vzťahy buniek, musíte vybrať príkazy Ovplyvňujúce bunky alebo Závislé bunky na table Závislosti vzorcov na karte Vzorce. Ak chcete nezobrazovať závislosti, použite príkaz Odstrániť šípky z toho istého panela.
Obrázok 2.2.21. Zobrazenie ovplyvňujúcich buniek
Režimy pre prácu so vzorcami
Excel má automatický režim výpočtu, vďaka ktorému sa vzorce na hárkoch okamžite prepočítavajú. Pri umiestnení veľkého počtu (až niekoľko tisíc) zložitých vzorcov na hárok sa rýchlosť práce môže výrazne znížiť v dôsledku prepočtu všetkých vzorcov na hárku. Ak chcete riadiť proces výpočtu pomocou vzorcov, musíte nastaviť manuálny mód výpočty pomocou príkazu Vzorce – Výpočet – Parametre výpočtu – Manuálne. Po vykonaní zmien je potrebné zavolať príkaz Vypočítať (na prepočet údajov na hárku) alebo Prepočítať (na prepočet celého zošita) na paneli Výpočet.
Užitočnou funkciou pre prácu so vzorcami je zobrazenie všetkých vzorcov na hárku. To je možné vykonať pomocou príkazu Vzorce – Závislosti vzorcov – Zobraziť vzorce. Potom sa v bunkách namiesto vypočítaných hodnôt zobrazia napísané vzorce. Ak sa chcete vrátiť normálny režim Musíte znova kliknúť na tlačidlo Zobraziť vzorce.
Ak vzorec vráti nesprávnu hodnotu, Excel vám môže pomôcť identifikovať bunku, ktorá spôsobuje chybu. Na to je potrebné aktivovať príkaz Vzorce – Závislosti vzorcov – Kontrola chýb – Zdroj chýb. Príkaz Check for Errors pomáha identifikovať všetky chybné položky vzorca.
Na ladenie vzorcov existuje nástroj na vyhodnocovanie vzorcov s názvom Vzorce – Závislosti vzorcov – Hodnotiť vzorec, ktorý zobrazuje výpočty krok za krokom v zložitých vzorcoch
Dielňa:.
1. Vytvorte tabuľku na výpočet súčtu radu (možnosti úloh na výpočet súčtu radu - viď nižšie). Pri vytváraní tabuľky používajte vstavané funkcie, absolútne a relatívne adresovanie a automatické dopĺňanie buniek.
2. V závislosti od počtu členov n usporiadajte tabuľku nasledovne:
Tabuľka 19.
| x i | 1 | 2 | … | n | S | Y |
| 0,1 | ||||||
| 0,2 | ||||||
| . | ||||||
| . | ||||||
| 1 |
Tabuľka 20.
| i x | 0,1 | 0,2 | … | 1 |
| 1 | ||||
| 2 | ||||
| . | ||||
| . | ||||
| n | ||||
| S | ||||
| Y |
3. Pomocou podmieneného formátovania zvýraznite záporné čísla modrou, čísla väčšie ako 1,5 červenou.
4. Vytvorte tabuľku. Vzorový dizajn je uvedený nižšie. Krok zmeny x v závislosti od variantu úlohy je 0,1 (alebo Pi/*).
5. Zostrojte grafy s=f(x) a y=f(x) v jednej súradnicovej sieti (na jednom diagrame).
6. Preštudujte si možnosti použitia funkcií (zoznam funkcií nájdete vo voľbe úlohy), uveďte príklad fungovania jednotlivých funkcií.
Tabuľka 21. Možnosti úlohy
1. Otvorte tabuľku programu Microsoft Excel 2007 a vytvorte zošit s názvom Laboratórna práca č.1.
2. Pre zamestnancov firmy je potrebné vytvoriť tabuľku pre výpočet miezd.
3. Pre zjednodušenie zadávania údajov do tabuľky vytvorte rozbaľovací zoznam (obr. 22) obsahujúci celé mená zamestnancov spoločnosti.
Ryža. 22. Rozbaľovací zoznam
4. Pomocou skratky v riadku vložte ďalší hárok do zošita programu Excel Štítok listu.
5. Na novom hárku vytvorte zoznam zamestnancov (obr. 23).
Ryža. 23. Zoznam zamestnancov spoločnosti
6. Ak chcete názvy zoradiť abecedne, spustite príkaz: karta na páse s nástrojmi Údaje skupina Triedenie a filtrovanie tlačidlo .
7. Vyberte rozsah buniek A1:A10 a kliknite na políčko názov na ľavom okraji riadku vzorcov. Zadajte napríklad názov buniek zamestnancov. Stlačte kláves Zadajte.
8. Ak chcete ostatným používateľom zabrániť v zobrazení alebo zmene výsledného zoznamu, chráňte a skryte hárok, na ktorom sa nachádza.
9. Kliknite pravým tlačidlom myši na záložku listu. Vyberte príkaz z kontextového menu.
10. V dialógovom okne Ochrana plechu(Obr. 24) zadajte heslo na deaktiváciu ochrany listu. V kapitole Povoliť všetkým používateľom tohto hárka Zrušte začiarknutie všetkých položiek. Kliknite na tlačidlo OK.
Ryža. 24. Dialógové okno Ochrana listu
11. V dialógovom okne potvrdenie hesla zadajte heslo znova.
12. Kliknite pravým tlačidlom myši na skratku listu a vyberte príkaz z kontextovej ponuky Skryť.
13. Prejdite na List 1 a vytvorte tabuľku Príprava miezd
(obr. 25). Stĺpec Celé meno vyplňte pomocou rozbaľovacieho zoznamu.
Ryža. 25. Štruktúra tabuľky
14. Vyberte rozsah buniek, do ktorých chcete umiestniť rozbaľovací zoznam.
15. Na karte Údaje v skupine Práca s údajmi vybrať tím Kontrola údajov.
16. V dialógovom okne Kontrola údajov uveďte typ údajov a zdroj (obr. 26).
17. Otvorte kartu Správa, ktorá sa má zadať(obr. 27). Vyplňte prázdne polia.
Ryža. 26. Dialógové okno overenia údajov
Ryža. 27. Správa pri zadávaní údajov
18. Prejdite na kartu Chybná správa(obr. 28). Vyplňte polia vyhliadka, Smerovanie A Správa.
Ryža. 28. Správa v prípade chyby pri zadávaní údajov
19. Pre hlavičky stolov nastavte zalamovanie textu(tlačidlo umiestnené na paneli nástrojov Zarovnanie páskové karty Domov).
20. Zmrazte prvé dva stĺpce a riadok hlavičky tabuľky. Ak to chcete urobiť, vyberte rozsah buniek C5:I20 a spustite príkaz: karta na páse s nástrojmi vyhliadka skupina okno tlačidlo .
21. Stĺpec Plat Vyplňte ľubovoľné údaje a nastavte formát meny buniek pomocou príkazu:
karta s páskou Domov panel nástrojov číslo Z rozbaľovacieho zoznamu Formát vyberte Formát meny.
22. Vytvorme si vzorec na výpočet bonusu, ktorý je 20% z platu. Akýkoľvek vzorec začína znakom = , tak poďme do cely F5 a zadajte vzorec = E5*20%(alebo = E5*0,2).
23. Pomocou značky automatického dopĺňania (čierny krížik v pravom dolnom rohu vybranej bunky) skopírujte vzorec do oblasti
F6:F11.
24. Medzi stĺpmi cena A Daň z príjmu vložiť stĺpec Celkovo naakumulované, v ktorej vypočítajte sumu Plat + cena.
25. Vyplňte zvyšné stĺpce tabuľky, pričom berte do úvahy, že daň z príjmu je 13 % z časovo rozlíšenej sumy.
26. Vypočítajte sumu, ktorá sa má vydať v dolároch, za týmto účelom nastavte aktuálny výmenný kurz dolára, napríklad 32, a v bunke J5 zadajte vzorec: = I5/$C$14. Podpísať $ sa vo vzorci používa tak, že pri kopírovaní pomocou značky automatického dopĺňania sa adresovanie buniek nemení.
27. Pre bunky obsahujúce peňažné údaje nastavte vhodný formát.
28. Používanie funkcie SUM, vypočítajte celkovú výšku dane z príjmov. Pre to:
· umiestnite kurzor do bunky H12;
· dajte znamienko =;
· v riadku vzorcov kliknite na tlačidlo;
· v zobrazenom dialógovom okne sprievodcu funkciou (obr. 29) vyberte kategóriu Matematické, funkcia SUM;
· ako argument funkcie SUM vyberte rozsah súčtu H5:H11;
· stlač tlačidlo OK.
29. Podobne vypočítajte celkovú sumu, ktorá sa má vydať, v dolároch a celkovú sumu, ktorá sa má vydať, v rubľoch.
Ryža. 29. Sprievodca funkciou
30. Nájdite priemer ( PRIEMERNÝ), minimum ( MIN) a maximálne ( MAX) mzdy.
31. Pomocou podmieneného formátovania zvýraznite červenou farbou Sumy, ktoré sa majú vydať, menej ako 5 500 rub. Spustite príkaz: karta na páse s nástrojmi Domov skupina Štýly rozbaľovací zoznam Podmienené formátovanie Pravidlá výberu buniek.
32. Zostavte diagram mzda zamestnancov spoločnosti
(obr. 30). Vyberte stĺpce naraz CELÉ MENO. A Suma, ktorá sa má vydať(podržte stlačený kláves Ctrl) a na karte s nástrojmi Vložiť na paneli nástrojov Diagramy vyberte zobrazenie stĺpcový graf.
33. Používanie karty Pás s nástrojmi rozloženie, vložte štítky osí a názov grafu.
Ryža. 30. Príklad návrhu diagramu
34. Zostrojte koláčový graf znázorňujúci vzťah medzi celkovou sumou, ktorá sa má vydať, a celkovou daňou z príjmu (obr. 31).
Ryža. 31. Príklad návrhu koláčového grafu
