Diferenciácia tabuľkových funkcií v Exceli. Grafická a numerická diferenciácia. Algoritmus na zostavenie grafu derivácie

Numerická diferenciácia

Sekcia č.5

Problém približného výpočtu derivácie môže nastať v prípadoch, keď analytický výraz pre skúmanú funkciu nie je známy. Funkcia môže byť špecifikovaná v tabuľke alebo je známy len graf funkcie, získaný napríklad ako výsledok odčítania senzorov procesných parametrov.

Niekedy pri riešení určitých problémov na počítači môže byť kvôli ťažkopádnosti výpočtov vhodnejšie vypočítať deriváty numerickou metódou ako analytickou. V tomto prípade je samozrejme potrebné zdôvodniť použitú numerickú metódu, t. j. uistiť sa, že chyba numerickej metódy je v prijateľných medziach.

Jednou z efektívnych metód na riešenie diferenciálnych rovníc je diferenčná metóda, keď sa namiesto požadovanej funkcie berie do úvahy tabuľka jej hodnôt v určitých bodoch, pričom derivácie sú približne nahradené diferenčnými vzorcami.

Nech je známy graf funkcie y = f(X) na segmente [ a,b].Môžete zostaviť graf derivácie funkcie a zapamätať si jej geometrický význam. Využime fakt, že derivácia funkcie v bode X rovná dotyčnici uhla sklonu k osi x dotyčnice k jej grafu v tomto bode.

Ak x = x 0, nájsť pri 0 = f(X 0) pomocou grafu a potom nakreslite dotyčnicu AB ku grafu funkcie v bode ( X 0 , r 0) (obr. 5.1). Nakreslite čiaru rovnobežnú s dotyčnicou AB, cez bod (-1, 0) a nájdite bod pri 1 jej priesečník s osou y. Potom hodnota pri 1 sa rovná dotyčnici sklonu dotyčnice k osi x, t.j. derivácii funkcie f(X) v bode X 0:

pri 1 = = tg α = f ¢ ( X 0), a bodka M 0 (X 0 , pri 1) patrí do grafu derivácie.

Na zostavenie grafu derivácie je potrebné rozdeliť segment [ a,b] na niekoľko častí s bodkami x i, potom graficky zostrojte hodnotu derivácie pre každý bod a získané body spojte hladkou krivkou pomocou vzorov.

Na obr. 5.2 znázorňuje konštrukciu piatich bodov M 1, M 2 ,... , M 5 a graf derivácie.

Algoritmus na zostavenie grafu derivácie:

1. Zostrojíme dotyčnicu ku grafu funkcie pri= f(X) v bode ( X 1 ,f(X 1)); z bodu (-1, 0) rovnobežného s dotyčnicou v bode ( X 1 ,f(X 1)) nakreslite priamku, kým sa nepretína s osou y; tento priesečník udáva hodnotu derivácie f ¢ ( X 1) Budovanie bodu M 1 (X 1 , f ¢ ( X 1)).

2. Podobne zostrojíme zvyšné body M 2 ,M 3 , M 4 a M 5 .

3. Spojte bodky M 1 ,M 2 ,M 3 ,M 4 ,M 5 hladká krivka.

Výsledná krivka je grafom derivácie.

Presnosť grafickej metódy na určenie derivácie je nízka. Popis tejto metódy poskytujeme len na vzdelávacie účely.

Komentujte. Ak v algoritme na zostavenie grafu derivácie namiesto bodu (-1, 0) vezmeme bod ( -l,0), kde l> 0, potom sa graf vykreslí v inej mierke pozdĺž osi y.

5 . 2 .Diferenčné vzorce

a) Diferenčné vzorce pre obyčajné deriváty

Rozdielové vzorce pre približný výpočet derivátu napovedá už samotná definícia derivátu. Nechajte hodnoty funkcie v bodoch x i označené y i:

y i= f(x i),x i = a+ ih,i = 0, 1, ... , n; h=

Uvažujeme prípad rovnomerného rozloženia bodov na segmente [ a, b]. Pre približný výpočet derivácií v bodoch x i môžete použiť nasledujúce rozdielové vzorce , alebo rozdielové deriváty .

Keďže hranica vzťahu (5.1) pri h® 0 sa rovná pravej derivácii v bode x i, potom sa tento vzťah niekedy nazýva derivácia pravého rozdielu v bode x i.Z podobného dôvodu sa nazýva vzťah (5.2). derivácia ľavého rozdielu v bode x i.Vzťah (5.3) sa volá centrálna derivácia rozdielu v bode x i.

Odhadnime chybu diferenčných vzorcov (5.1)–(5.3) za predpokladu, že funkcia f(X) expanduje do Taylorovho radu v blízkosti bodu x i:

f(X)= f(x i)+ . (5.4)

Nastavenie (5.4) X= x i+ h alebo x = x i- h, dostaneme

Priamym dosadením expanzií (5.5) a (5.6) do vzorca (5.10) môžeme získať závislosť medzi druhou deriváciou funkcie a rozdielový vzorec pre deriváciu druhého rádu .

Mnoho inžinierskych problémov si často vyžaduje výpočet derivátov. Keď existuje vzorec, ktorý popisuje proces, nie sú žiadne ťažkosti: vezmeme vzorec a vypočítame deriváciu, ako sme učili v škole, nájdeme hodnoty derivácie v rôznych bodoch a je to. Problém možno spočíva len v tom, aby sme si zapamätali, ako vypočítať deriváty. Čo ak však máme len niekoľko stoviek alebo tisícok riadkov údajov a neexistuje žiadny vzorec? Väčšinu času sa to presne deje v praxi. Ponúkam dva spôsoby.

Prvým je, že aproximujeme našu množinu bodov pomocou štandardnej funkcie Excelu, to znamená, že vyberieme funkciu, ktorá najlepšie vyhovuje našim bodom (v Exceli je to lineárna funkcia, logaritmická, exponenciálna, polynómová a mocnina). Druhým spôsobom je numerická diferenciácia, na ktorú budeme potrebovať len schopnosť zadávať vzorce.

Pripomeňme si, čo je derivát vo všeobecnosti:

Derivácia funkcie f (x) v bode x je limitom pomeru prírastku Δf funkcie v bode x k prírastku Δx argumentu, keď má tendenciu k nule:

Využime teda tento poznatok: na výpočet derivácie jednoducho vezmeme veľmi malé hodnoty prírastku argumentu, t.j. Δx.

Aby sme našli približnú hodnotu derivácie v bodoch, ktoré potrebujeme (a naše body sú rôzne hodnoty stupňa deformácie ε), môžete urobiť nasledovné. Pozrime sa ešte raz na definíciu derivácie a uvidíme, že pri použití malých prírastkov argumentu Δε (teda malých prírastkov v stupni deformácie, ktoré sú zaznamenané počas testovania), môžeme nahradiť hodnotu skutočnej derivácie v bode x 0 (f'(x 0)=dy/dx (x 0)) k pomeru Δy / Δx \u003d (f (x 0 + Δx) - f (x 0)) / Δx.

To znamená, že sa stane toto:

f'(x 0) ≈(f (x 0 + Δx) - f (x 0)) / Δx (1)

Na výpočet tejto derivácie v každom bode vykonáme výpočty pomocou dvoch susedných bodov: prvého so súradnicou ε 0 pozdĺž vodorovnej osi a druhého so súradnicou x 0 + Δx, t.j. jedna - derivácia, v ktorej počítame, a tá, ktorá je správnejšia. Takto vypočítaná derivácia sa nazýva tzv derivácia rozdielu doprava (dopredu) s krokomΔ X.

Môžeme to urobiť naopak, zoberieme ďalšie dva susedné body: x 0 - Δx a x 0, t. j. ten, ktorý nás zaujíma, a ten naľavo. Dostaneme vzorec na výpočet derivácia rozdielu doľava (späť) s krokom -Δ X.

f'(x 0) ≈(f (x 0) - f (x 0 - Δx)) / Δx (2)

Predchádzajúce vzorce boli „vľavo“ a „vpravo“ a existuje ďalší vzorec, ktorý vám umožňuje vypočítať centrálna derivácia rozdielu s krokom 2 Δx, a ktorý najčastejšie sa používa na numerickú diferenciáciu:

f'(x 0) ≈(f (x 0 + Δx) - f (x 0 - Δx)) / 2Δx (3)

Na kontrolu vzorca uvažujme jednoduchý príklad so známou funkciou y=x 3 . Zostavíme tabuľku v Exceli s dvoma stĺpcami: x a y, a potom vytvoríme graf pomocou dostupných bodov.

Derivácia funkcie y=x 3 je y=3x 2, ktorej graf, t.j. parabolu, musíme získať pomocou našich vzorcov.

Skúsme vypočítať hodnoty centrálnej derivácie rozdielu v bodoch x. Pre to. Do bunky druhého riadku našej tabuľky vyplníme náš vzorec (3), t.j. nasledujúci vzorec v Exceli:

Teraz vytvoríme graf pomocou už existujúcich hodnôt x a získaných hodnôt centrálnej derivácie rozdielu:

A tu je naša malá červená parabola! Takže vzorec funguje!

No a teraz môžeme prejsť ku konkrétnemu inžinierskemu problému, o ktorom sa hovorilo na začiatku článku – k nájdeniu zmeny dσ/dε so zvyšujúcim sa namáhaním. Prvá derivácia krivky "napätie-deformácia" σ=f (ε) v zahraničnej literatúre sa nazýva "rýchlosť spevnenia" (rýchlosť deformácie) a v našej - "faktor spevnenia". Ako výsledok testovania máme dátové pole, ktoré pozostáva z dvoch stĺpcov: jeden s hodnotami deformácie ε a druhý s hodnotami napätia σ v MPa. Vezmime si deformáciu ocele 1035 alebo našej 40G za studena (pozri tabuľku analógov ocelí) pri 20°C.

Tu je naša krivka v súradniciach "skutočné napätie - skutočné napätie" σ-ε:

Postupujeme rovnako ako v predchádzajúcom príklade a dostaneme nasledujúcu krivku:

Ide o zmenu rýchlosti tvrdnutia v priebehu deformácie. Čo s tým robiť, je samostatná otázka.

Grafická diferenciácia začína vykreslením funkčného grafu pre dané hodnoty. V experimentálnej štúdii sa takýto graf získa pomocou samonahrávacích zariadení. Potom sa ku krivke nakreslia dotyčnice v pevných polohách a hodnoty derivácie sa vypočítajú s ohľadom na dotyčnicu uhla, ktorú zviera dotyčnica k osi x.

Na obr. 5,8, a je znázornená krivka získaná experimentálne na zariadení (obr. 5.6). Určenie uhlového zrýchlenia (požadovanej funkcie) sa vykonáva grafickou diferenciáciou podľa pomeru:

(5.19)

Tangenta sklonu dotyčnice ku krivke v určitom bode i sú reprezentované ako pomer segmentov , kde Komu- vybraný segment integrácie (obr. 5.8, b)

Po dosadení tohto vzťahu do vzťahu (5.19) dostaneme

kde je ordináta reklamačného grafu uhlového zrýchlenia;

Mierka požadovaného grafu; Jednotky SI: = mm; \u003d mm / (rad s -2).

Graf funkcie je zostavený podľa zistených hodnôt ordinátov pre množstvo pozícií. Body na krivke sú spojené ručne hladkou čiarou a potom zakrúžkované vzorom.

Grafické rozlíšenie uvažovanou metódou dotyčníc má relatívne nízku presnosť. Vyššiu presnosť dosiahneme pri grafickom odlíšení akordovou metódou (obr. 5.8, Obr. v a G).

Zostávajúce konštrukcie sú podobné konštrukciám opísaným vyššie pre grafickú diferenciáciu tangentovou metódou. Vyberte segment (mm); viesť lúče naklonené pod uhlom k priesečníku s osou y v bodoch 1 ", 2 ", 3 " ... , ktoré sa prenesú na súradnice nakreslené v strede každého z intervalov. Výsledné body 1 *, 2 *, 3 * sú body požadovanej funkcie .

Mierky pozdĺž súradnicových osí s touto konštrukčnou metódou súvisia rovnakým vzťahom (5.21), ktorý bol odvodený pre prípad grafického odlíšenia metódou dotyčníc.

Diferenciácia funkcií f(x), daný (alebo vypočítaný) ako pole čísel, sa vykonáva metódou numerickej diferenciácie pomocou počítača.

Čím menší je krok v poli čísel, tým presnejšie môžete vypočítať hodnotu derivácie funkcie v tomto intervale

Príklad 3: Pomocou automatického filtra vyberte študentov študujúcich v skupine č. 5433 s priezviskom začínajúcim na písmeno C.

Sekvenovanie

1. Skopírujte databázu (obr. 30) do listu 3.

2. Priezvisko.

3. Vyberte položku zo zoznamuTextové filtre → Vlastný filter. V okne, ktoré sa zobrazí Vlastný automatický filter vyberte kritérium výberu začína na , do poľa oproti zadajte požadované písmeno (skontrolujte, či je rozloženie v ruštine). Stlačte OK.

4. Otvorte rozbaľovací zoznam v stĺpcičíslo skupiny.

5. Vyberte požadované číslo.

Filtrovanie záznamov v databáze s pokročilým filtrom

Pokročilý filter umožňuje vyhľadávať riadky pomocou zložitejších kritérií ako vlastné automatické filtre. Rozšírený filter používa na filtrovanie údajov interval kritérií.

Pri použití rozšíreného filtra sa názvy stĺpcov, pre ktoré sú špecifikované podmienky, skopírujú pod zdrojovú tabuľku. Kritériá výberu sa zadávajú pod názvami stĺpcov. Po použití filtra je možné na obrazovke zobraziť iba tie riadky, ktoré spĺňajú zadané kritériá, a filtrované údaje možno skopírovať do iného hárka alebo do inej oblasti toho istého hárka.

Príklad 4: Vyberte všetkých študentov zo skupiny č. 5433, ktorých GPA je väčšia alebo rovná 4,5.

Sekvenovanie

1. Skopírujte databázu (obr. 30) do listu 4.

2. Skopírujte názvy stĺpcovČíslo skupiny a priemerné skóre

do oblasti pod pôvodnou tabuľkou. Pod názvami stĺpcov zadajte požadované výberové kritériá (obr. 32)

Ryža. 32. Okno Excel s pokročilým filtrom

2. Na karte Údaje na paneli nástrojov Zoradiť

a filtrovať vyberte Rozšírené. Zobrazí sa dialógové okno (obrázok 33), v ktorom sú špecifikované rozsahy údajov.

Ryža. 33. Okno rozšíreného filtra

Vo vstupnom poli pôvodný sortiment určuje interval obsahujúci zdrojovú databázu. V našom prípade je vybraný rozsah buniek od A1 do I9.

Vo vstupnom poli Rozsah podmienok vyberie sa interval buniek v pracovnom hárku, ktorý obsahuje požadované kritériá (C12:D13).

Do vstupného poľa vložte výsledok do rozsahu označuje interval, v ktorom sa skopírujú riadky, ktoré spĺňajú kritériá

teórie. V našom prípade je bunka označená pod oblasťou kritérií, napríklad A16. Toto pole je dostupné len vtedy, keď je vybratý prepínač. Skopírujte výsledok na iné miesto.

začiarkavacie políčko Iba jedinečné záznamy je navrhnutý tak, aby zobrazoval iba neopakujúce sa riadky.

Výsledná tabuľka, ktorá spĺňa kritériá filtrovania, je znázornená na obr. 34.

Ryža. 34. Okno Excel s výsledkami filtrovania

1. Vytvorte si vlastnú databázu, počet záznamov v ktorej musí byť aspoň 15 a počet stĺpcov aspoň 6. Databáza napr. Zoznam klientov (obr. 35).

2. Použite tri automatické filtre na databázu (na samostatných hárkoch). Počet kritérií musí byť aspoň dve.

3. Na databázové záznamy použite tri pokročilé filtre, z ktorých každý obsahuje aspoň dve kritériá. Umiestnite všetky pokročilé filtre na jeden hárok pod pôvodnú tabuľku.

Ryža. 35. Okno Excel s databázou Zoznam zákazníkov

LAB #5

Numerická derivácia a jednoduchá analýza funkcií

Účel práce: Preskúmať funkciu do extrému, naučiť sa určiť kritický bod.

Z priebehu matematiky je známe, že odvodený vzorec vo všeobecnosti vyzerá takto:

kde Δx je prírastok argumentu; x je číslo smerujúce k nule. Pomocou derivácie môžete určiť kritické body funkcie - minimá, maximá alebo inflexie. Ak sa hodnota derivácie funkcie pri nejakej hodnote x rovná nule, potom pri tejto hodnote x má funkcia kritický bod.

Príklad 1: Funkcia f x = x 2 + 2x 3 je definovaná na intervale x 5;5. Preskúmajte správanie funkcie f(x) .

Sekvenovanie

1. Nech Δx = 0,00001. Do bunky A1 zadajte: šDx=Ÿ (obr. 36). Vyberte písmeno D, kliknite pravým tlačidlom myši na vybrané písmeno a vyberte Formát buniek. Na karte Písmo vyberte písmo Symbol. Písmeno D sa zmení na grécke písmeno ѓў. Zarovnanie v bunke je možné vykonať doprava. Do bunky B1 zadajte hodnotu 0,00001.

2. V bunkách od A2 do F2 usporiadajte hlavičku tabuľky, ako je znázornené na obr. 36.

3. Stĺpec A, začínajúci od tretieho riadku, bude obsahovať x hodnôt. Do buniek A3 až A13 zadajte hodnoty od -5 do 5.

4. Do bunky B3 napíšte vzorec =A3^2+2*A3-3 a rozviňte ho na konečnú hodnotu x (do 13. riadku).

5. Na určenie derivácie funkcie a výpočet jej hodnôt v danom intervale je potrebné vytvoriť medziprodukt

presné výpočty. Do bunky C3 zadajte vzorec pre súčet argumentu x a jeho prírastku Δx. Vzorec je: =A3+$B$1 . Natiahnite jeho hodnotu na konečnú hodnotu argumentu x .

Ryža. 36. Okno Excel so štúdiom správania funkcie

6. Do bunky D3 napíšte vzorec =C3^2+2*C3-3 , ktorý vypočíta hodnotu funkcie f z argumentu x Δx . Roztiahnite výslednú hodnotu na koncovú hodnotu argumentu.

7. Do bunky E3 napíšte odvodený vzorec (1), keďže hodnoty f x sú v B3 a hodnoty f x + Δx sú v D3.

Vzorec bude vyzerať takto: =(D3-B3)/$B$1 .

8. Určte správanie sa funkcie na danom intervale (zvyšuje sa, klesá, alebo existuje kritický bod). Ak to chcete urobiť, musíte do bunky F3 napísať vzorec na určenie správania funkcie. Vzorec obsahuje tri podmienky:

9. Zostrojte grafy pre hodnoty f x a f "(x). Graf (obr. 37) ukazuje, že ak je hodnota derivácie funkcie nulová, potom má funkcia v tomto mieste kritický bod.

* Kvôli príliš veľkej chybe výpočtu sa hodnota f"(x) nemusí rovnať 0. Je však potrebné túto situáciu opísať.

Ryža. 37. Schéma štúdia správania sa funkcie

Úlohy na samostatnú prácu

Funkcia f(x) je definovaná na intervale x . Preskúmajte správanie funkcie f(x) . Vytvárajte grafy.

LAB #6

Konštrukcia dotyčnice ku grafu funkcie

Cieľ práce: Zvládnuť výpočet hodnôt rovnice dotyčnice ku grafu funkcie v bode x 0.

Rovnica dotyčnice ku grafu funkcie y = f(x) v bode

Príklad 1: Funkcia y = x 2 + 2x 3 je definovaná na intervale x [ 5; 5]. Zostrojte dotyčnicu ku grafu tejto funkcie v bode x 0 = 1.

Sekvenovanie:

1. Túto funkciu numericky diferencujte (pozri Laboratórna práca č. 5). Tabuľka počiatočných údajov je znázornená na obr. 38.

Ryža. 38. Tabuľka počiatočných údajov

2. Určite umiestnenie v tabuľke x , x 0 , f (x 0 ) a f "(x 0 ). Je zrejmé, že x budú hodnoty od

stĺpec A, začínajúc od tretieho radu (obr. 38). Ak x 0 = 1, bunka A9 bude fungovať ako x 0 . V súlade s tým je hodnota funkcie f v bode x 0 v bunke B9 a hodnota f" (x 0 )

- v bunke E9.

3. V stĺpci F je vypočítaná rovnica dotyčnice ku grafu funkcie f(x). Pri výpočte rovnice (1) je potrebné, aby sa hodnoty x 0, f (x 0) a f "(x 0) nemenili.

Ak chcete adresovať bunky A9, B9 a E9, musíte použiť absolútne odkazy na tieto bunky. Bunky sú fixované pomocou znamienka š$Ÿ. Bunky budú vyzerať takto: $A$9 , $B$9 a $E$9 .

Ryža. 39. Graf funkcie f(x) a dotyčnice ku grafu v bode x=1

Úlohy na samostatnú prácu

Funkcia f(x) je definovaná na intervale x . Vypočítajte tangentovú rovnicu. Zostrojte dotyčnicu ku grafu funkcie v danom bode.

1. Vedeneeva, E. A. Funkcie a vzorce Excel 2007. Používateľská knižnica / E. A. Vedeneeva. - Petrohrad: Peter, 2008. - 384 s.

2. Sviridová, M. Yu. Tabuľky Excel / M. Yu. Sviridova. - M.: Academia, 2008. - 144 s.

3. Serogodsky, V. V. Grafy, výpočty a analýza údajov

v Excel 2007 / V. V. Serogodsky, R. G. Prokdi, D. A. Kozlov, A. Yu. Druzhinin. - M.: Veda a technika, 2009. - 336 s.

Okrem formátovania prvkov poľa buniek, riadkov a stĺpcov je často užitočné použiť viacero hárkov programu Excel. Na organizáciu a vyhľadávanie informácií v knihe je vhodné priradiť názvy listov vlastné mená, ktoré odrážajú ich sémantický obsah. Napríklad „počiatočné údaje“, „výsledky výpočtov“, „grafy“ atď. Je vhodné to urobiť pomocou obsahové menu. Stlačte pravé tlačidlo myši na karte listu, Premenovať list a kliknite .

Ak chcete pridať jeden alebo viac nových hárkov, vyberte Hárok z ponuky Vložiť. Ak chcete vložiť niekoľko listov naraz, podržaním vyberte záložky pre požadovaný počet listov , potom z ponuky Vložiť vykonajte príkaz Hárok. Opačná operácia na odstránenie listov sa vykonáva podobným spôsobom. cez obsahové menu, kde je vybratý príkaz Odstrániť.

Užitočnou operáciou na presúvanie hárkov je uchopenie záložky hárka ľavým tlačidlom myši a presunutie na požadované miesto. Ak súčasne stlačíte , kópia listu sa presunie a k názvu listu sa pridá číslo 2.

Úloha 7. Zmeňte formát celej bunky B2 na: font - Arial 11; umiestnenie - v strede, pozdĺž spodného okraja; jedno slovo na riadok; formát čísla – „0,00“; ohraničenie bunky - dvojitá čiara

2.3. Vstavané funkcie

Excel obsahuje viac ako 150 vstavaných funkcií na zjednodušenie výpočtov a spracovania údajov. Príklad obsahu bunky s funkciou: =B2+SIN(C7) , kde B2 a C7 sú adresy buniek obsahujúcich čísla a SIN() je názov funkcie. Najpoužívanejšie funkcie Excelu:

SQRT(25) = 5 - Vypočíta druhú odmocninu z (25) RADIANS(30) = 0,5 - Prevedie 30 stupňov na radiány INT(8,7) = 8 - Zaokrúhli nadol na najbližšie celé číslo MOD(-3;2) = 1 - ponecháva zvyšok delenia čísla (-3) podľa

deliteľ (2). Výsledok má deliteľné znamienko. IF(E4>0,2;”dodatočné”;”chyba”)- ak je číslo v bunke E4 menšie ako 0,2,

potom Excel vráti "dodatočné" (pravda), inak - "chyba" (nepravda).

Vo vzorci môžu byť funkcie vnorené do seba, ale nie viac ako 8-krát.

Pri použití funkcie je hlavnou vecou definovanie samotnej funkcie a jej argumentu. Ako argument je spravidla uvedená adresa bunky, v ktorej sú informácie zaznamenané.

Funkciu môžete definovať zadaním textu (ikony, čísla atď.) do požadovanej bunky alebo použiť Sprievodca funkciou. Tu sú pre pohodlie vyhľadávania všetky funkcie rozdelené do kategórií: matematické, štatistické, logické a iné. V rámci každej kategórie sú zoradené podľa abecedy.

Sprievodca funkciou vyvolané príkazom ponuky Vložiť, Funkcia

alebo stlačením ikony (f x ). V prvom okne, ktoré sa zobrazí Sprievodcu funkciou (obr. 4), definujeme kategóriu a názov konkrétnej funkcie, klikneme . V druhom okne (obr. 5) je potrebné určiť Funkcia Argumenty. Ak to chcete urobiť, kliknutím na tlačidlo napravo od prvého rozsahu buniek (číslo 1) „zakryte“ okno. Vyberieme bunky, na základe ktorých sa vykoná výpočet. Potom sa vybrané bunky vložia do prvého okna rozsahu. Znova stlačte pravé tlačidlo. Ak je argumentom niekoľko rozsahov buniek, akcia sa zopakuje. Potom stlačte na dokončenie úlohy. . Zdrojová bunka bude obsahovať výsledok výpočtu.

Ryža. 4. Pohľad na okno Sprievodca funkciami

Ryža. 5. Okno pre nastavenie argumentov vybranej funkcie

Úloha 8. Nájdite priemernú hodnotu radu čísel: 2,5; 2,9; 1,8; 3,4; 6,1;

1,0; 4,4.

Rozhodnutie . Do buniek zadávame čísla, napríklad C2:C8. Vyberte bunku C9, do ktorej napíšeme funkciu = PRIEMER (C2: C8), stlačte , v C9 dostaneme priemernú hodnotu uvedených čísel - 3,15.

Úloha 9. Pomocou podmienenej logickej funkcie IF vytvorte vzorec na premenovanie nepárnych čísel na "jeseň", párne čísla - "jar".

Rozhodnutie . Vyberieme stĺpec na zadanie počiatočných údajov - párne (nepárne) čísla, napríklad A . Do bunky B3 napíšte vzorec =IF(MOD(A3;2)=0,"váha","os"). Skopírovaním bunky B3 pozdĺž stĺpca B získame výsledky analýzy čísel zapísaných v stĺpci A. Výsledky riešenia úlohy sú znázornené na obr. 6.

Ryža. 6. Riešenie úlohy č.9

Úloha 10. Vypočítajte hodnotu funkcie y = x3 + sinx - 4ex pre x = 1,58.

Rozhodnutie . Údaje umiestnime do buniek A2 - x, B2 -y. Riešenie úlohy je znázornené na obr. 7 v číselnej forme vľavo a vo forme vzorca vpravo. Pri riešení tohto problému by ste mali venovať pozornosť volaniu funkcií SIN a exponent na zadanie argumentu (pozri obr. 8).

Obr.7. Riešenie problému číslo 10

Obr.8. Okná pre zadanie argumentu funkcie SIN a EXP

Úloha 11. Vytvorte matematický model úlohy v Exceli a vypočítajte funkciu y= 1/ ((x- 3) (x+ 4)), pre hodnoty x= 3 a y= -4 zobrazte „nedefinované“, číselné hodnoty funkcie - v ostatných prípadoch .

Úloha 12 . Vytvorte matematický model úlohy v Exceli: 12.1. pre výpočet s koreňmi

a) √ x3 y2 z / √ x z; b) (z = z)2; c) 3 √ x2 3 √ x; d) √ 5 x 5 3-1 / √ 20 x 3-1

12.2. pre geometrické výpočty a) určte uhly pravouhlého trojuholníka, ak x je rameno, y je prepona;

b) určte vzdialenosť medzi dvoma bodmi v kartézskom súradnicovom systéme XYZ pomocou vzorca

d = (x2 − x1 )2 + (y2 − y1 )2 + (z2 − z1 )2

c) určte vzdialenosť od bodu (x 0 ,y 0 ) k priamke a x + b y + c = 0 pomocou vzorca

d = ax0 +b y0 +c / √ (a2 +b2 )

d) určte plochu trojuholníka zo súradníc vrcholov pomocou vzorca

S = 1 2 [ (x1 − x3 )(y2 − y3 ) − (x2 − x3 )(y1 − y3 )]

3. Riešenie úloh pomocou vzorcov a funkcií

V skutočnosti existuje veľa úloh, ktoré možno úspešne vyriešiť pomocou vzorcov a funkcií programu Excel. Zvážte úlohy, ktoré sa v praxi najčastejšie riešia pomocou tabuliek: lineárne rovnice a ich systémy, výpočet číselných hodnôt derivácií a určitých integrálov.

Derivácia funkcie y = f(x) je pomer jej prírastku ∆y k zodpovedajúcemu prírastku ∆x argumentu, keď

∆x→ 0

y = f (x + x) − f (x)

Problém .13 . Nájdite deriváciu funkcie y = 2x 3 + x 2 v bode x=3 .

rozhodnutie. Derivát vypočítaný analytickou metódou je 60 . Deriváciu vypočítame v Exceli pomocou vzorca (1). Ak to chcete urobiť, vykonajte nasledujúcu postupnosť akcií:

· Nakreslíme si zápis stĺpcov: Х – argumenty funkcie, Y – hodnoty funkcie, Y ` – derivácia funkcie (obr. 9).

· Funkciu tabelujeme v okolí bodu x \u003d 3 s malým krokom, napríklad 0,001, výsledky sa zapíšu do stĺpca X.

Ryža. 9. Tabuľka na výpočet derivácie funkcie

· Do bunky B2 zadajte vzorec na výpočet funkcie =2*A2^3+A2^2 .

· Skopírujte vzorec až po riadok 7, dostaneme hodnoty funkcie na zarážkach tabulátora argumentu.

· Do bunky C2 zadajte vzorec na výpočet derivácie =(B3-B2)/ (A3-A2) .

· Skopírujte vzorec až po riadok 6, dostaneme hodnoty derivátov na zarážkach tabulátora argumentu.

Pre hodnotu x = 3 sa derivácia funkcie rovná hodnote 60,019, ktorá je blízka hodnote vypočítanej analyticky.

lichobežníková metóda. Pri metóde lichobežníka je integračná oblasť rozdelená na segmenty s určitým krokom a oblasť pod grafom funkcie na každom segmente sa považuje za rovnakú ako oblasť lichobežníka. Potom má výpočtový vzorec nasledujúci tvar

kde h = (b- a)/N je krok rozdelenia; N je počet delených bodov.

Na zlepšenie presnosti sa počet deliacich bodov zdvojnásobí, znova sa vypočíta integrál. Rozdelenie pôvodného intervalu sa zastaví, keď sa dosiahne požadovaná presnosť:

integrál, urobte nasledovné:

– zvoľte N= 5, v bunke F2 vypočítajte h-krok priečky (obr. 10);

Ryža. 10. Výpočet určitého integrálu

· V prvom stĺpci A zapíšeme si číslo intervalu i;

· Do bunky B2 napíšte vzorec =3*(2+F2*A2)^2 na výpočet prvého členu vzorca (2);

· Do bunky C2 napíšte vzorec =3*(2+F2*(A2+1))^2 na výpočet druhého člena;

· „Roztiahnite“ bunky so zapnutými vzorcami 4 riadky po stĺpcoch;

Do bunky C7 napíšeme vzorec a vypočítame súčet členov,

Do bunky C8 napíšeme vzorec a vypočítame SN požadovanú hodnotu určitého integrálu 19,02 (hodnota S N získaná analyticky

19).

Úloha. 15. Vypočítajte určitý integrál:

3.2. Riešenie lineárnych rovníc

Lineárne rovnice v Exceli je možné vyriešiť pomocou funkcie Výber parametrov. Pri výbere parametra sa mení hodnota ovplyvňujúcej bunky (parametra), kým vzorec, ktorý závisí od tejto bunky, nevráti zadanú hodnotu.

Zvážte postup hľadania parametra na jednoduchom príklade riešenia lineárnej rovnice s jednou neznámou.

Úloha 16. Vyriešte rovnicu 10 x - 10 / x = 15 .

rozhodnutie. Pre požadovanú hodnotu parametra - x, vyberte bunku A3. Do tejto bunky zadajte ľubovoľné číslo, ktoré leží v oblasti definície funkcie (v našom príklade sa toto číslo nemôže rovnať nule). Nech je 3. Táto hodnota sa použije ako počiatočná hodnota. V bunke, napríklad B3, v súlade s vyššie uvedenou rovnicou zadajte vzorec =10*A3-10/A3. V dôsledku série výpočtov pomocou tohto vzorca sa vyberie požadovaná hodnota parametra. Teraz v ponuke Nástroje vyberte príkaz Výber parametrov, spustite funkciu vyhľadávania parametrov (obr. 11, a) . Zadáme parametre vyhľadávania:

· V teréne Nastaviť v bunke zadajme absolútny odkaz na bunku $B$3 obsahujúcu vzorec.

· Do poľa Hodnota zadajte požadovaný výsledok 15 .

· V teréne Zmena hodnoty bunky zadajte odkaz na bunku A3 obsahujúcu vybratú hodnotu a kliknite .

Na konci funkcie Výber parametrov na obrazovke sa objaví okno Výsledok výberu parametrov Miesto, v ktorom sa zobrazia výsledky vyhľadávania. Nájdený parameter 2.000025 sa objaví v bunke A3, ktorá mu bola rezervovaná.

Venujte pozornosť skutočnosti, že v našom príklade má rovnica dve riešenia a parameter je vybratý iba jeden. Parameter sa totiž mení len dovtedy, kým sa nevráti požadovaná hodnota. Prvý argument nájdený týmto spôsobom sa nám vráti ako výsledok vyhľadávania. Ak ako

V našom príklade zadajte počiatočnú hodnotu -3, potom sa nájde druhé riešenie rovnice: -0,5.

Obr.11. Riešenie rovnice: a - zadanie údajov, b - výsledok riešenia

spôsoby: substitúcia, sčítanie a odčítanie rovníc, pomocou matíc. Uvažujme o metóde riešenia kanonického systému lineárnych rovníc (3) pomocou matíc.

a1 x + a2 y + b1 = 0

a3 x + a4 y + b2 = 0

Je známe, že systém lineárnych rovníc v maticovej reprezentácii je napísaný ako:

kde A je matica koeficientov, X je vektor - stĺpec neznámych,

B je stĺpcový vektor voľných členov. Riešenie takéhoto systému

sa píše vo forme

X=A-1B,

kde A -1 je matica inverzná vzhľadom k A . Vyplýva to zo skutočnosti, že pri riešení maticových rovníc pre X by mala zostať matica identity E. Vynásobením oboch strán rovnice AX = B zľava A -1 dostaneme riešenie lineárnej sústavy rovníc.

Úloha 18. Vyriešte sústavu lineárnych rovníc

rozhodnutie. Pre daný systém lineárnych rovníc majú hodnoty zodpovedajúcej matice a stĺpcového vektora tvar:

Ak chcete problém vyriešiť, vykonajte nasledujúce akcie:

· A2:B3 a zapíšte do nej prvky matice A.

· Vyberte blok buniek, napr. C2:C3 a zapíšte do nej prvky matice B.

· Vyberte blok buniek, napr. D2:D3 umiestniť výsledok riešenia sústavy rovníc.

Do bunky D2 zadajte vzorec = MULTIPLE(MOBR(A2:B3),C2:C3).

Knižnica Excel v sekcii matematických funkcií obsahuje funkcie na vykonávanie operácií s maticami. Ide najmä o tieto funkcie:

Parametrami týchto funkcií môžu byť odkazy na adresy na polia obsahujúce maticové hodnoty alebo názvy rozsahov a výrazy.

Napríklad MOBR (A1: B2) alebo MOBR (matica_1).

Povedzte Excelu, že sa na poliach vykonáva operácia, stlačením kombinácie klávesov + + , v bunkách D2 a D3 bude výsledok x = 2,16667 ; y= -1,33333.

4. Riešenie optimalizačných problémov

Mnohé problémy predpovedania, dizajnu a výroby sú zredukované na širokú triedu optimalizačných problémov. Takýmito úlohami sú napríklad: maximalizácia produkcie tovarov s obmedzeniami na suroviny na výrobu týchto tovarov; personálne zabezpečenie na dosiahnutie najlepších výsledkov pri najnižších nákladoch; minimalizácia nákladov na prepravu tovaru; dosiahnutie špecifikovanej kvality zliatiny; určenie rozmerov určitej nádoby, berúc do úvahy náklady na materiál na dosiahnutie maximálneho objemu; rôzne

problémy, ktoré zahŕňajú náhodné premenné, a iné problémy optimálnej alokácie zdrojov a optimálneho návrhu.

Riešenie problémov tohto druhu je možné v EXCEL-e vykonať pomocou nástroja Riešiteľ, ktorý sa nachádza v menu Nástroje. Formuláciou takýchto problémov môže byť sústava rovníc s niekoľkými neznámymi a súborom obmedzení riešení. Riešenie problému preto musí začať konštrukciou vhodného modelu. Pozrime sa na tieto príkazy na príklade.

Úloha 20. Predpokladajme, že sa rozhodneme vyrábať dva typy šošoviek A a B. Šošovka typu A pozostáva z 3 komponentov šošovky, typ B - od 4. Za týždeň nie je možné vyrobiť viac ako 1800 šošoviek. Zloženie šošovky typu A trvá 15 minút, šošovky typu B 30 minút. Pracovný týždeň pre 4 zamestnancov je 160 hodín. Koľko šošoviek A a B je potrebné vyrobiť, aby ste dosiahli maximálny zisk, ak šošovka typu A stojí 3 500 rubľov a typu B - 4 800 rubľov.

rozhodnutie. Na vyriešenie tohto problému je potrebné zostaviť a vyplniť tabuľku podľa obr. 12:

· Premenujte bunku B2 v x, počet zorných šošoviek A.

· Legálne premenujme bunku B3 na y .

cieľová funkcia Zisk = 3500*x+4800*y zadajte do bunky B5. · Náklady na výber sa rovnajú =3*x+4*y zadajte do bunky B7.

· Časové náklady sú =0,25*x+0,5*y zadajte do bunky B8.

Obr.12. Vyplnenie tabuľky počiatočnými údajmi

· Vyberte bunku B5 a vyberte ponuku Údaje, potom aktivujte príkaz Hľadať riešenie. Vyplňte bunky tohto okna podľa obr.13.

· Stlačte<Выполнить >; ak je všetko vykonané správne, potom bude riešenie, ako je uvedené nižšie.