Diferențierea funcțiilor tabelului în Excel. Diferențierea grafică și numerică. Algoritm pentru reprezentarea grafică a derivatei

Diferențierea numerică

Secțiunea nr. 5

Problema calculului aproximativ al derivatei poate apărea în cazurile în care expresia analitică pentru funcția studiată este necunoscută. Funcția poate fi specificată într-un tabel sau se cunoaște doar graficul funcției, obținut, de exemplu, ca urmare a citirilor de la senzori ai parametrilor procesului.

Uneori, la rezolvarea unor probleme pe calculator, din cauza greutății calculelor, poate fi mai convenabil să se calculeze derivatele folosind o metodă numerică decât una analitică. În acest caz, desigur, este necesar să se justifice metoda numerică utilizată, adică să se asigure că eroarea metodei numerice este în limite acceptabile.

Unul dintre metode eficiente Rezolvarea ecuațiilor diferențiale este metoda diferențelor, când în loc de funcția dorită, se ia în considerare un tabel cu valorile sale în anumite puncte, iar derivatele sunt aproximativ înlocuite cu formule de diferență.

Fie cunoscut graficul funcției y = f(X) pe segmentul [ A,b]Puteți construi un grafic al derivatei unei funcții, amintindu-și semnificația geometrică. Să profităm de faptul că derivata unei funcții într-un punct X este egală cu tangentei unghiului de înclinare la axa absciselor tangentei la graficul său în acest punct.

Dacă x = x 0, să găsim la 0 =f(X 0) folosind un grafic și apoi desenați o tangentă AB la graficul funcției în punctul ( X 0 , y 0) (Fig. 5.1). Să desenăm o dreaptă paralelă cu tangenta AB, prin punctul (-1, 0) și găsiți punctul la 1 intersecția sa cu axa ordonatelor. Apoi valoarea la 1 este egal cu tangentei tangentei la axa absciselor, adică derivata funcției f(X)la punct X 0:

la 1 = = tg α = f ¢ ( X 0), și punct M 0 (X 0 , la 1) aparține graficului derivat.

Pentru a reprezenta graficul derivat, trebuie să împărțiți segmentul [ A,b] în mai multe părți cu puncte x i, apoi pentru fiecare punct trasați grafic valoarea derivatei și conectați punctele rezultate cu o curbă netedă folosind modele.

În fig. 5.2 arată construcția a cinci puncte M 1, M 2 ,... , M 5 și grafică derivată.

Algoritm pentru construirea unui grafic derivat:

1. Construiți o tangentă la graficul funcției la= f(X)la moment ( X 1 ,f(X 1)); din punctul (-1, 0) paralel cu tangenta din punctul ( X 1 ,f(X 1)) trageți o linie dreaptă până când se intersectează cu axa ordonatelor; acest punct de intersecție dă valoarea derivatei f ¢ ( X 1).Construiți un punct M 1 (X 1 , f ¢ ( X 1)).

2. Să construim punctele rămase în același mod M 2 ,M 3 , M 4 și M 5 .

3. Conectarea punctelor M 1 ,M 2 ,M 3 ,M 4 ,M 5 curbă netedă.

Curba rezultată este un grafic al derivatei.

Precizie metoda grafica definiția derivatei este scăzută. Oferim o descriere a acestei metode doar în scopuri educaționale.

cometariu. Dacă în algoritmul de trasare a derivatei în loc de punctul (-1, 0) luăm punctul ( -l,0), unde l> 0, atunci graficul va fi reprezentat pe o scară diferită de-a lungul axei y.

5 . 2 .Formule de diferență

A) Formule de diferență pentru derivate obișnuite

Formulele de diferență pentru calculul aproximativ al derivatei sunt sugerate de însăși definiția derivatei. Lăsați valorile funcției în puncte x i indicat de y eu:

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

Considerăm cazul unei distribuții uniforme a punctelor pe segment [ A, b]. Pentru calculul aproximativ al derivatelor la puncte x i puteți folosi următoarele formule de diferență , sau derivate de diferență .

Deoarece limita relației (5.1) la h® 0 este egal cu derivata dreaptă în punct x i, atunci această relație se numește uneori derivată diferență dreaptă la punct x i.Din un motiv similar se numeste relatia (5.2). derivată diferență stângă la punct x i.Relația (5.3) se numește derivată de diferență centrală la punct x i.

Să estimăm formula de eroare a diferenței (5.1)–(5.3), presupunând că funcția f(X) se extinde într-o serie Taylor în vecinătatea punctului x i:

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

Presupunând în (5.4) X= x i+ h sau x = x i- h, primim

Prin înlocuirea directă a expansiunilor (5.5) și (5.6) în formula (5.10), putem obține relația dintre derivata a doua a funcției și formula diferențelor pentru derivata de ordinul doi .

Rezolvarea multor probleme de inginerie necesită adesea calcularea derivatelor. Când există o formulă care descrie procesul, nu există dificultăți: luăm formula și calculăm derivata, așa cum am fost predat la școală, găsim valorile derivatei în diferite puncte și atât. Singura dificultate, probabil, este să ne amintim cum să calculăm derivatele. Dar dacă avem doar câteva sute sau mii de rânduri de date și nicio formulă? Cel mai adesea, acest lucru se întâmplă în practică. Propun două moduri.

Primul este că ne aproximăm setul de puncte functie standard Excel, adică selectăm funcția care se potrivește cel mai bine punctelor noastre (în Excel aceasta este o funcție liniară, logaritmică, exponențială, polinomială și putere). A doua metodă este diferențierea numerică, pentru care avem nevoie doar de capacitatea de a introduce formule.

Să ne amintim ce este un derivat în general:

Derivata unei funcții f (x) la un punct x este limita raportului dintre incrementul Δf al funcției din punctul x și incrementul Δx al argumentului atunci când acesta din urmă tinde spre zero:

Deci vom folosi aceste cunoștințe: vom lua pur și simplu valori foarte mici ale incrementului de argument pentru a calcula derivata, de exemplu. Δx.

Pentru a găsi valoarea aproximativă a derivatei în punctele de care avem nevoie (și punctele noastre sunt sensuri diferite gradul de deformare ε) puteți face acest lucru. Să ne uităm din nou la definiția derivatei și să vedem că atunci când folosim creșteri mici ale argumentului Δε (adică creșteri mici ale gradului de deformare care sunt înregistrate în timpul testării), putem înlocui valoarea derivatei reale în punctul x 0 (f'(x 0)=dy/dx (x 0)) la raportul Δy/Δx=(f (x 0 + Δx) – f (x 0))/Δx.

Deci iată ce se întâmplă:

f’(x 0) ≈(f (x 0 + Δx) – f (x 0))/Δx (1)

Pentru a calcula această derivată în fiecare punct, efectuăm calcule folosind două puncte învecinate: primul cu coordonata ε 0 de-a lungul axei orizontale, iar al doilea cu coordonata x 0 + Δx, adică. unul este derivata în care calculăm și cel din dreapta. Derivata calculată în acest fel se numește diferență derivată la dreapta (înainte) în trepteΔ X.

Putem face invers, luând celelalte două puncte învecinate: x 0 - Δx și x 0, adică cel care ne interesează și cel din stânga. Obținem formula de calcul diferență derivată la stânga (înapoi) cu un pas -Δ X.

f’(x 0) ≈(f (x 0) – f (x 0 – Δx))/Δx (2)

Formulele anterioare au fost „stânga” și „dreapta”, dar există o altă formulă care vă permite să calculați derivată de diferență centrală cu un pas de 2 Δx, și care cel mai adesea folosit pentru diferențierea numerică:

f’(x 0) ≈(f (x 0 + Δx) – f (x 0 – Δx))/2Δx (3)

Pentru a verifica formula, luați în considerare un exemplu simplu cu funcția cunoscută y=x 3 . Să construim un tabel în Excel cu două coloane: x și y, apoi să construim un grafic folosind punctele disponibile.

Derivata functiei y=x 3 este y=3x 2, al carei grafic, i.e. o parabolă, trebuie să obținem folosind formulele noastre.

Să încercăm să calculăm valorile derivatei diferenței centrale în punctele x. Pentru aceasta. În celula din al doilea rând al tabelului nostru introducem formula noastră (3), adică. următoarea formulă în Excel:

Acum construim un grafic folosind valorile existente ale lui x și valorile obținute ale derivatei diferenței centrale:

Și iată micuța noastră parabolă roșie! Deci formula funcționează!

Ei bine, acum putem trece la problema specifică de inginerie despre care am vorbit la începutul articolului - la găsirea modificării dσ/dε cu deformarea crescândă. Prima derivată a curbei efort-deformare σ=f (ε) este numită „rată de întărire prin deformare” în literatura străină, iar „coeficientul de întărire” în literatura noastră. Deci, în urma testelor, avem o matrice de date care constă din două coloane: una cu valorile deformarii ε și cealaltă cu valorile tensiunii σ în MPa. Să luăm deformarea la rece a oțelului 1035 sau a 40G al nostru (vezi tabelul analogilor de oțel) la 20°C.

Iată curba noastră în coordonatele „stres adevărat - deformare adevărată” σ-ε:

Procedăm în același mod ca în exemplul anterior și obținem această curbă:

Aceasta este modificarea vitezei de întărire în timpul deformării. Ce să faci cu ea este o întrebare separată.

Diferențierea grafică începe cu trasarea unui grafic al funcției pe baza unor valori date. Într-un studiu experimental, un astfel de grafic este obținut folosind instrumente de înregistrare. În continuare, se trasează tangente la curbă în poziții fixe și se calculează valorile derivatei în raport cu tangentei unghiului format de tangenta cu axa absciselor.

În fig. 5.8, A Este prezentată curba obţinută experimental pe instalaţie (Fig. 5.6). Determinarea accelerației unghiulare (funcția dorită) se realizează prin diferențiere grafică în funcție de relația:

(5.19)

Tangenta unghiului de înclinare a tangentei la curbă la un punct i reprezentat ca raport de segmente, unde LA– segmentul de integrare selectat (Fig. 5.8, b)

După înlocuirea acestei relații în relația (5.19), obținem

unde este ordonata graficului cererii de accelerație unghiulară;

Scara graficului dorit; unități SI: = mm; = mm/(rad s -2).

Graficul funcției este construit folosind valorile ordonate găsite pentru un număr de poziții. Punctele de pe curbă sunt conectate manual cu o linie netedă și apoi conturate folosind un model.

Diferențierea grafică folosind metoda tangentei luată în considerare are o precizie relativ scăzută. O precizie mai mare se obține prin diferențierea grafică folosind metoda acordurilor (Fig. 5.8, VȘi G).

Construcțiile rămase sunt similare cu cele descrise anterior în diferențierea grafică folosind metoda tangentei. Selectați un segment (mm); conduc razele înclinate la unghiuri până la intersecția cu axa ordonatelor în puncte 1 ", 2 ", 3 „..., care se transferă la ordonatele trase la mijlocul fiecăruia dintre intervale. Punctele rezultate 1 *, 2 *, 3 * sunt puncte ale funcției necesare .

Scalele de-a lungul axelor de coordonate cu această metodă de construcție sunt legate prin aceeași relație (5.21), care a fost derivată pentru cazul diferențierii grafice folosind metoda tangentei.

Diferențierea unei funcții f(x), specificat (sau calculat) sub forma unui tablou de numere, se realizează prin metoda diferențierii numerice folosind un calculator.

Cu cât este mai mic pasul în tabloul de numere, cu atât mai precis puteți calcula valoarea derivatei funcției în acest interval

Exemplul 3: Folosind un filtru automat, selectați studenții care studiază în grupa nr. 5433 cu un nume de familie care începe cu litera C.

Secvențierea

1. Copiați baza de date (Fig. 30) în Foaia 3.

2. Nume de familie.

3. Selectați un articol din listăFiltre de text → Filtru personalizat. În fereastra care apare Filtru automat personalizat selectați criteriul de selecție care începe cu , introduceți litera dorită în câmpul opus (verificați ca aspectul este rusesc). Faceți clic pe OK.

4. Deschideți lista derulantă în coloană Grupa nr.

5. Selectați numărul dorit.

Filtrați înregistrările bazei de date utilizând un filtru avansat

Filtru avansat vă permite să căutați șiruri folosind criterii mai complexe în comparație cu filtrele automate personalizate. Filtrul avansat folosește o serie de criterii pentru a filtra datele.

Când utilizați un filtru avansat, numele coloanelor pe care sunt stabilite condițiile sunt copiate sub tabelul sursă. Criteriile de selecție sunt introduse sub numele coloanelor. După aplicarea unui filtru, doar acele rânduri care îndeplinesc criteriile specificate pot fi afișate pe ecran, iar datele filtrate pot fi copiate într-o altă foaie sau în altă zonă din aceeași foaie de lucru.

Exemplul 4: Selectați toți elevii din grupa nr. 5433 al căror punctaj mediu este mai mare sau egal cu 4,5.

Secvențierea

1. Copiați baza de date (Fig. 30) în Foaia 4.

2. Copiați numele coloanelor Numărul grupului și scorul mediu

în zona de sub tabelul original. Introduceți criteriile de selecție necesare sub numele coloanelor (Fig. 32)

Orez. 32. Fereastra Excel cu filtru avansat

2. În fila Date din bara de instrumente Sortare

și filtru selectați Avansat. Va apărea o casetă de dialog (Fig. 33), în care sunt indicate intervalele de date.

Orez. 33. Fereastra filtru avansat

În câmpul de introducere Gama originală specifică intervalul care conține baza de date sursă. În cazul nostru, intervalul de celule de la A1 la I9 este evidențiat.

În câmpul de introducere Gama de condiții Este evidențiat intervalul de celule de pe foaia de lucru care conține criteriile necesare (C12:D13).

În câmpul de introducere Plasați rezultatul în interval indică intervalul în care sunt copiate liniile care îndeplinesc criteriile

temam. În cazul nostru, este indicată celula de sub zona de criterii, de exemplu A16. Acest câmp este disponibil numai când butonul radio este selectat Copiați rezultatul într-o altă locație.

Caseta de bifat Doar intrări unice conceput pentru a afișa numai linii care nu se repetă.

Tabelul rezultat care satisface criteriile de filtrare este prezentat în Fig. 34.

Orez. 34. Fereastra Excel cu rezultate de filtrare

1. Creați-vă propria bază de date, numărul de înregistrări în care trebuie să fie de cel puțin 15, iar numărul de coloane trebuie să fie de cel puțin 6. De exemplu, o bază de date Lista clienților (Fig. 35).

2. Aplicați trei filtre automate în baza de date (pe foi separate). Numărul de criterii trebuie să fie de cel puțin două.

3. Aplicați trei filtre avansate înregistrărilor bazei de date, fiecare dintre ele trebuie să conțină cel puțin două criterii. Așezați toate filtrele avansate pe o singură foaie sub masa originală.

Orez. 35. Fereastra Excel cu baza de date Lista Clienti

LUCRARE DE LABORATOR Nr 5

Diferențierea numerică și analiza funcției simple

Scopul muncii: Investigați o funcție până la un extrem, învățați să determinați punctul critic.

Dintr-un curs de matematică știm că formula derivatei din vedere generala arata asa:

unde Δx este incrementul argumentului; x este un număr care tinde spre zero. Folosind derivata, puteți determina punctele critice ale unei funcții - minime, maxime sau inflexiuni. Dacă valoarea derivatei unei funcții la o anumită valoare a lui x este egală cu zero, atunci la această valoare a lui x funcția are un punct critic.

Exemplul 1: Funcția f x = x 2 + 2x 3 este dată pe intervalul x 5;5. Investigați comportamentul funcției f(x) .

Secvențierea

1. Fie Δx = 0,00001. În celula A1 introduceți: šDx=Ÿ (Fig. 36). Selectați litera D, faceți clic dreapta pe litera selectată, selectați Formatare celule. În fila Font, selectați fontul Symbol. Litera D se va transforma în litera greacă ѓў. Alinierea într-o celulă se poate face spre dreapta. În celula B1, introduceți valoarea 0,00001.

2. În celulele A2 la F2, creați un antet de tabel, așa cum se arată în Fig. 36.

3. Coloana A, începând cu al treilea rând, va conține valorile x. În celulele A3 până la A13, introduceți valori de la -5 la 5.

4. În celula B3, scrieți formula =A3^2+2*A3-3 și întindeți-o până la valoarea finală x (până la a 13-a linie).

5. Pentru a determina derivata unei funcții și a calcula valorile acesteia pe un interval dat, este necesar să se facă un intermediar

calcule precise. În celula C3, introduceți formula pentru suma argumentului x și incrementul lui Δx. Formula arată astfel: =A3+$B$1. Extindeți-i valoarea până la valoarea finală a argumentului x .

Orez. 36. Fereastra Excel cu un studiu al comportamentului unei functii

6. În celula D3 scrieți formula =C3^2+2*C3-3, care calculează valoarea funcției f din argumentul x Δx. Extindeți valoarea rezultată până la valoarea finală a argumentului.

7. În celula E3, scrieți formula derivată (1), ținând cont de faptul că valorile lui f x sunt în B3, iar valorile lui f x + Δx sunt în D3.

Formula va arăta astfel: =(D3-B3)/$B$1.

8. Determinați comportamentul funcției pe un interval dat (crește, scade sau are un punct critic). Pentru a face acest lucru, trebuie să scrieți independent o formulă în celula F3 pentru a determina comportamentul funcției. Formula conține trei condiții:

9. Desenați grafice pe baza valorilor lui f x și f" (x). Graficul (Fig. 37) arată că, dacă valoarea derivatei unei funcții este zero, atunci în acest moment funcția are un punct critic .

* Din cauza unei erori de calcul prea mari, valoarea lui f"(x) poate să nu fie egală cu 0. Dar este totuși necesar să descriem această situație.

Orez. 37. Diagrama pentru studierea comportamentului unei funcţii

Sarcini pentru munca independentă

Funcția f(x) este dată pe intervalul x. Investigați comportamentul funcției f(x) . Construiți grafice.

LUCRARE DE LABORATOR Nr 6

Construirea unei tangente la graficul unei funcții

Scopul lucrării: Pentru a stăpâni calculul valorilor ecuației unei tangente la graficul unei funcții în punctul x 0.

Ecuația tangentei la graficul funcției y = f(x) într-un punct

Exemplul 1: Funcția y = x 2 + 2x 3 este dată pe intervalul x [ 5; 5 ] . Construiți o tangentă la graficul acestei funcții în punctul x 0 = 1.

Secvențiere:

1. Diferențiați această funcție numeric (vezi Lucrarea de laborator nr. 5). Tabelul de date sursă este prezentat în Fig. 38.

Orez. 38. Tabelul datelor inițiale

2. Determinați locația lui x, x 0, f(x 0) și f" (x 0) în tabel. Evident, x vor fi valorile din

coloana A, începând de la a treia linie (Fig. 38). Dacă x 0 = 1, atunci celula A9 va acționa ca x 0. În consecință, valoarea funcției f în punctul x 0 este în celula B9, iar valoarea lui f" (x 0)

– în celula E9.

3. În coloana F se calculează ecuația tangentei la graficul funcției f(x). Când se calculează ecuația (1), este necesar ca valorile lui x 0, f(x 0) și f" (x 0) să nu se modifice. Prin urmare, în scriere

Pentru a determina adresele celulelor A9, B9 și E9, trebuie să utilizați referințe absolute la aceste celule. Celulele sunt fixate folosind semnul š$Ÿ. Celulele vor arăta astfel: $A$9 , $B$9 și $E$9 .

Orez. 39. Graficul funcției f(x) și tangentei la grafic în punctul x=1

Sarcini pentru munca independentă

Funcția f(x) este definită pe intervalul x. Calculați ecuația tangentei. Construiți o tangentă la graficul funcției într-un punct dat.

1. Vedeneeva, E. A. Funcții și formule Excel 2007. Biblioteca utilizator / E. A. Vedeneeva. – Sankt Petersburg: Peter, 2008. – 384 p.

2. Sviridova, M. Yu. Foi de calcul Excel / M. Yu. Sviridova. – M.: Academia, 2008. – 144 p.

3. Serogodsky, V.V. Grafice, calcule și analize de date

V Excel 2007 / V. V. Serogodsky, R. G. Prokdi, D. A. Kozlov, A. Yu. Druzhinin. – M.: Știință și tehnologie, 2009. – 336 p.

Pe lângă formatarea elementelor de câmp de celule, rând și coloană, este adesea util să folosiți mai multe foi de lucru Excel. Pentru a organiza și a căuta informații într-o carte, este convenabil să atribuiți nume foilor nume proprii, reflectând conținutul lor semantic. De exemplu, „date inițiale”, „rezultatele calculului”, „grafice”, etc. Este convenabil să faceți acest lucru folosind meniul contextual. Faceți clic dreapta pe fila foaie, Redenumiți foaia și faceți clic .

Pentru a adăuga una sau mai multe foi noi, selectați comanda Sheet din meniul Inserare. Pentru a insera mai multe foi deodată, trebuie să selectați filele cu numărul necesar de foi ținând apăsat , apoi din meniul Inserare executați comanda Sheet. Operația inversă de îndepărtare a foilor se efectuează în mod similar. Prin meniul contextual, unde este selectată comanda Delete.

O operațiune utilă pentru mutarea foilor este să apuci fila foii cu butonul stâng al mouse-ului și să o muți în locația dorită. Dacă apăsați , o copie a foii va fi mutată, iar numărul 2 va fi adăugat la numele foii.

Sarcina 7. Schimbați formatul întregii celule B2 la: font – Arial 11; locație - în centru, de-a lungul marginii inferioare; un cuvânt pe rând; format numeric – „0.00”; marginea celulei – linie dublă

2.3. Funcții încorporate

Excel conține peste 150 de funcții încorporate pentru a simplifica calculele și procesarea datelor. Un exemplu de conținut al unei celule cu o funcție: =B2+SIN(C7) , unde B2 și C7 sunt adresele celulelor care conțin numere, iar SIN() este numele funcției. Cele mai utilizate funcții Excel:

SQRT(25) = 5 – calculează rădăcina pătrată a numărului (25) RADIANI(30) = 0,5 – transformă 30 de grade în radiani WHOLE(8,7) = 8 – se rotunjește la cel mai apropiat număr întreg inferior REMAIN(-3,2) ) = 1 – lasă un rest la împărțirea numărului (-3) la

divizor(2). Rezultatul are semnul divizor. IF(E4>0,2;”adițional”;”eroare”)– dacă numărul din celula E4 este mai mic de 0,2,

apoi Excel returnează „extra” (adevărat), în caz contrar „eroare” (fals).

Într-o formulă, funcțiile pot fi imbricate unele în altele, dar nu mai mult de 8 ori.

Când utilizați o funcție, principalul lucru este să definiți funcția în sine și argumentul acesteia. Argumentul, de regulă, specifică adresa celulei în care sunt înregistrate informațiile.

Puteți defini o funcție tastând text (pictograme, numere etc.) în celula dorită sau utilizați Expertul de funcții. Aici, pentru ușurința căutării, toate funcțiile sunt împărțite în categorii: matematice, statistice, logice și altele. În cadrul fiecărei categorii sunt sortate alfabetic.

Expertul de funcții apelat prin comanda de meniu Inserare, Funcție

sau prin apăsarea pictogramei (f x ). În prima fereastră a Expertului Funcție care apare (Fig. 4), determinați Categoria și numele funcției specifice, faceți clic pe . În a doua fereastră (Fig. 5) trebuie să definiți Argumente ale funcției. Pentru a face acest lucru, faceți clic pe butonul din dreapta primului interval de celule (Numărul 1) pentru a „închide” fereastra. Selectăm celulele pe baza cărora va fi efectuat calculul. După aceasta, celulele selectate vor fi introduse în fereastra din primul interval. Apăsați din nou tasta din dreapta. Dacă argumentul este mai multe intervale de celule, atunci repetați acțiunea. Apoi, pentru a finaliza lucrarea, faceți clic . Celula originală va conține rezultatul calculului.

Orez. 4. Vizualizare fereastră Function Wizard

Orez. 5. Fereastra pentru specificarea argumentelor functiei selectate

Sarcina 8. Aflați valoarea medie a unei serii de numere: 2,5; 2,9; 1,8; 3,4; 6,1;

1,0; 4,4.

Soluție. Introduceți numere în celule, de exemplu, C2:C8. Selectați celula C9, în care scriem funcția = MEDIE(C2:C8), apăsați , în C9 obținem valoarea medie a numerelor indicate - 3,15.

Sarcina 9. Folosind funcția IF logică condiționată, creați o formulă pentru redenumirea numerelor impare drept „toamnă” și numerelor pare ca „primăvară”.

Soluție. Selectăm o coloană pentru introducerea datelor inițiale - numere pare (impare), de exemplu, A. În celula B3 scriem formula =IF(REM(A3,2)=0,"greutate","axa"). Copiind celula B3 de-a lungul coloanei B, obținem rezultatele analizei numerelor scrise în coloana A. Rezultatele rezolvării problemei sunt prezentate în Fig. 6.

Orez. 6. Rezolvarea problemei nr. 9

Problema 10. Calculați valoarea funcției y = x3 + sinx – 4ex pentru x = 1,58.

Soluție. Să plasăm datele în celulele A2 – x, B2 – y. Soluția problemei este prezentată în Fig. 7 sub formă numerică în stânga și sub formă de formulă în dreapta. Când rezolvați această problemă, ar trebui să acordați atenție apelării funcțiilor SIN și exponent pentru a introduce un argument (vezi Fig. 8).

Fig.7. Soluție la problema nr. 10

Fig.8. Fereastra pentru introducerea argumentului funcției SIN și EXP

Problema 11. Creați un model matematic al problemei în Excel pentru a calcula funcția y= 1/ ((x- 3) · (x+ 4)), pentru valorile x= 3 și y= -4 afișați „nedefinit”, valori numerice ​a funcției – în alte cazuri.

Problema 12. Creați un model matematic al problemei în Excel: 12.1. pentru calcule cu rădăcini

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

12.2. pentru calcule geometrice a) determinați unghiurile unui triunghi dreptunghic, dacă x este catetul și y este ipotenuza;

b) determinați distanța dintre două puncte din sistemul de coordonate cartezian XYZ folosind formula

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

c) determinați distanța de la punctul (x 0 ,y 0 ) la dreapta a x + b y + c = 0 folosind formula

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

d) determinați aria triunghiului din coordonatele vârfurilor folosind formula

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

3. Rezolvarea problemelor folosind formule și funcții

Există de fapt multe probleme care pot fi rezolvate cu succes folosind formule și funcții Excel. Să luăm în considerare problemele care sunt cel mai des rezolvate în practică folosind foi de calcul: ecuații liniare și sistemele lor, calculul valorilor numerice ale derivatelor și integralelor definite.

Derivata unei funcții y = f(x) este raportul dintre incrementul său ∆y și incrementul corespunzător ∆x al argumentului, când

∆x→ 0

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

Problema .13. Aflați derivata funcției y = 2x 3 + x 2 în punctul x=3.

Soluţie. Derivata calculată prin metoda analitică este 60. Vom calcula derivata în Excel folosind formula (1). Pentru a face acest lucru, efectuăm următoarea secvență de acțiuni:

· Să desemnăm coloanele: X – argumente funcție, Y – valori ale funcției, Y ` – derivată funcție (Fig. 9).

· Tabelați funcția într-o vecinătate a punctului x = 3 cu un pas mic, de exemplu, 0,001, introducem rezultatele în coloana X.

Orez. 9. Tabel pentru calcularea derivatei unei funcții

· În celula B2, introduceți formula pentru calcularea funcției =2*A2^3+A2^2.

· Să copiem formula pe linie 7, obținem valorile funcției la punctele de tabulatură argument.

· În celula C2, introduceți formula de calcul a derivatei =(B3-B2)/ (A3-A2) .

· Să copiem formula pe linie 6, obținem valorile derivatelor la punctele de tabulare ale argumentului.

Pentru valoarea x = 3, derivata funcției este egală cu valoarea 60,019, care este apropiată de valoarea calculată analitic.

metoda trapezului. În metoda trapezoidală, domeniul de integrare este împărțit în segmente cu un anumit pas, iar aria de sub graficul funcției pe fiecare segment este considerată egală cu aria trapezului. Apoi formula de calcul ia următoarea formă

unde h= (b- a)/ N – pas de partiție; N – numărul de puncte de despicare.

Pentru a crește acuratețea, numărul de puncte de partiție este dublat și integrala este calculată din nou. Fragmentarea intervalului inițial este oprită atunci când este atinsă precizia necesară:

integral, efectuăm următoarele acțiuni:

– alegeți N= 5, în celula F2 calculăm pasul de h-partiție (Fig. 10);

Orez. 10. Calculul integralei definite

· În prima coloanăȘi notăm numărul intervalului i;

· În celula B2, scrieți formula =3*(2+F2*A2)^2 pentru a calcula primul termen al formulei (2);

· În celula C2, scrieți formula =3*(2+F2*(A2+1))^2 pentru a calcula al doilea termen;

· „Întindeți” celulele cu formule la 4 rânduri în jos coloane;

· În celula C7 scriem formula și calculăm suma termenilor,

· În celula C8, scrieți formula și calculați SN valoarea dorită a integralei definite 19.02 (valoarea SN obținută analitic

19).

Sarcină. 15. Calculați integrala definită:

3.2. Rezolvarea ecuațiilor liniare

Ecuatii lineareîn Excel poate fi rezolvată folosind funcția Selectarea parametrului. La selectarea unui parametru, valoarea celulei de influență (parametrul) se modifică până când formula care depinde de acea celulă returnează valoarea specificată.

Să luăm în considerare procedura de căutare a unui parametru activat exemplu simplu soluțiile unei ecuații liniare cu unu sunt necunoscute.

Problema 16. Rezolvați ecuația 10 x - 10 / x = 15 .

Soluţie. Pentru valoarea dorită a parametrului – x, selectați celula A3. Să introducem în această celulă orice număr care se află în domeniul de definire al funcției (în exemplul nostru, acest număr nu poate fi egal cu zero). Să fie 3. Această valoare va fi folosită ca valoare inițială. În celula, de exemplu, B3, în conformitate cu ecuația de mai sus, introduceți formula =10*A3-10/A3. Ca rezultat al unei serii de calcule folosind această formulă, va fi selectată valoarea parametrului dorit. Acum, în meniul Instrumente, selectând comanda Selectarea parametrului, Să lansăm funcția de căutare a parametrilor (Fig. 11, a). Să introducem parametrii de căutare:

· În câmp Setați la celulă Să introducem o referință absolută la celula $B$3 care conține formula.

· În câmpul Valoare, introduceți rezultatul dorit 15.

· În câmp Modificarea valorii unei celule introduceți un link către celula A3 care conține valoarea selectată și faceți clic .

La finalizarea funcției Selectarea parametrilor pe ecran va apărea o fereastră Rezultatul selectării parametrilor, care va afișa rezultatele căutării. Parametrul găsit 2.000025 va apărea în celula A3, care i-a fost rezervată.

Atenție la faptul că în exemplul nostru ecuația are două soluții, dar a fost selectat un singur parametru. Acest lucru se întâmplă deoarece parametrul este modificat doar până când este returnată valoarea necesară. Primul argument găsit în acest fel ne este returnat ca rezultat al căutării. Dacă ca

indicați valoarea inițială în exemplul nostru -3, apoi se va găsi a doua soluție a ecuației: -0,5.

Fig. 11. Rezolvarea ecuației: a - intrarea datelor, b - rezultatul soluției

metode: substituție, adunare și scădere de ecuații, folosind matrici. Să considerăm o metodă de rezolvare a sistemului canonic de ecuații liniare (3) folosind matrici.

a1 x + a2 y + b1 = 0

a3 x + a4 y + b2 =0

Se știe că un sistem de ecuații liniare în reprezentare matriceală se scrie sub forma:

unde A este o matrice de coeficienți, X este un vector - o coloană de necunoscute,

B este vectorul coloană al termenilor liberi. Soluția unui astfel de sistem

scris sub forma

X = A-1 B,

unde A -1 este matricea inversă cu A. Acest lucru rezultă din faptul că la rezolvarea ecuațiilor matriceale pentru X, matricea de identitate E trebuie să rămână. Înmulțind din stânga ambele părți ale ecuației AX = B cu A -1, obținem soluția sistem liniar ecuații.

Problema 18. Rezolvați un sistem de ecuații liniare

Soluţie. Pentru un sistem dat de ecuații liniare, valorile matricei și vectorului coloană corespunzătoare au forma:

Pentru a rezolva problema, să efectuăm următorii pași:

· A2:B3 și scrieți în ea elementele matricei A.

· Să selectăm un bloc de celule, de exemplu, C2:C3 și scrieți elementele matricei B în ea.

· Să selectăm un bloc de celule, de exemplu, D2:D3 pentru a plasa rezultatul rezolvării unui sistem de ecuații.

· în celula D2 introduceți formula = MULP(MOBR(A2:B3),C2:C3).

Biblioteca Excel din secțiunea de funcții matematice conține funcții pentru efectuarea de operații pe matrice. În special, acestea sunt funcțiile:

Parametrii acestor funcții pot fi legături de adrese către matrice care conțin valori matrice sau nume de intervale și expresii.

De exemplu, MOBR (A1: B2) sau MOPR (matrice_1).

· Să spunem lui Excel că se efectuează o operație pe matrice prin apăsarea combinației de taste + + , în celulele D2 și D3 rezultatul va fi x = 2,16667; y= - 1,33333.

4. Rezolvarea problemelor de optimizare

Multe probleme de prognoză, proiectare și producție pot fi reduse la o clasă largă de probleme de optimizare. Astfel de sarcini sunt, de exemplu: maximizarea producției de mărfuri cu restricții asupra materiilor prime pentru producerea acestor bunuri; întocmirea personalului pentru a obține cele mai bune rezultate la cel mai mic cost; minimizarea costului de transport al mărfurilor; atingerea calității specificate a aliajului; determinarea dimensiunilor unui anumit recipient, ținând cont de costul materialului pentru a atinge volumul maxim; variat

probleme care implică variabile aleatoare și alte probleme de alocare optimă a resurselor și proiectare optimă.

Problemele de acest tip pot fi rezolvate în EXCEL folosind instrumentul de căutare soluție, care se află în meniul Instrumente. Formularea unor astfel de probleme poate fi un sistem de ecuații cu mai multe necunoscute și un set de restricții asupra soluțiilor. Prin urmare, rezolvarea problemei trebuie să înceapă cu construirea unui model adecvat. Să ne familiarizăm cu aceste comenzi folosind un exemplu.

Problema 20. Să presupunem că decidem să producem două tipuri de lentile A și B. O lentilă de tip A este formată din 3 componente ale lentilei, tipul B - din 4. Se pot produce maximum 1.800 de lentile într-o săptămână. Este nevoie de 15 minute pentru a asambla o lentilă de tip A și 30 de minute pentru o lentilă de tip B. Săptămâna de lucru pentru 4 angajați este de 160 de ore. Câte lentile A și B trebuie produse pentru a obține un profit maxim, dacă o lentilă de tip A costă 3500 de ruble, tipul B costă 4800 de ruble.

Soluţie. Pentru a rezolva această problemă, este necesar să compuneți și să completați un tabel în conformitate cu Fig. 12:

· Redenumiți celula B2 în x, numărul de lentile de tip A.

· Și, în mod similar, să redenumim celula B3 în y.

Funcția țintă Profit = 3500*x+4800*y intra in celula B5. · Costurile pentru ambalare sunt egale cu =3*x+4*y, se introduce in celula B7.

· Costurile de timp sunt egale cu =0,25*x+0,5*y, introduceți în celula B8.

Fig. 12. Completarea tabelului cu date sursă

· Selectați celula B5 și selectați meniul Date, după care activăm comanda Căutare soluție. Să umplem celulele acestei ferestre în conformitate cu Fig. 13.

· Faceți clic<Выполнить >; dacă este făcută corect, soluția va fi ca mai jos.