Metode Numerice – Semestrul II – Laborator 2 1

LABORATOR Nr.2: Folosirea funcţiilor definite de utilizator sub EXCEL

Aplicaţie: Calculul volumului de apă dislocat de navă. 1. Baze teoretice. Metoda de calcul tradiţională este foarte simplă. Ea constă în aplicarea metodei de integrare numerică aşa

numită "a trapezelor".

Fig.1. Fig.2.

Conform figurii 1, integrala funcţiei f pe domeniul a-b poate fi scrisă sub forma aproximativă:

(∫ ∑−

=−+

++=

b

a

n

iixixififdxxf

1

112

1)( ) (1)

Această valoare reprezintă mărimea ariei dintre curba funcţiei f şi axa ox. Se observă că prin acest procedeu

această arie este divizată în arii elementare cuprinse între axa x şi arcul de curbă S dintre verticalele de mărime fi şi fi+1. La rândul lor aceste arii sunt înlocuite de ariile trapezelor formate prin înlocuirea arcului de curbă S cu coarda C a curbei. Eroarea metodei este dată de mica arie cuprinsă între arcul de curbă S şi coarda C.

Cu cât diviziunea intervalului a-b este mai fină, cu atât eroarea este mai mică. Pentru a calcula volumul de apă dislocuit de navă se procedează în următorii paşi: - Se consideră volumul de sub apă al navei tăiat în "felii" verticale denumite "cuple" (figura 2), la anumite

intervale pe lungimea navei, lungime măsurată pe axa x orientată de la prova către pupa. Fiecare dintre aceste cuple are un contur curb definit prin "semilăţimi" măsurate pe o axă y orientată de la planul longitudinal vertical central (numit plan diametral) către bord. Abscisa curbei cuplei este măsurată după o axă z orientată pe verticală de la fundul navei către punte;

- Se calculează ariile cuplelor prin integrarea funcţiei contur a fiecărie cuple în parte. Astfel, rezultă o serie de suprafeţe Ai, i=1,...,n ale cuplelor. Aceste valori Ai pot fi considerate ca descriind o "funcţie a ariilor imerse" (scufundate) de variabilă x;

- Se calculează volumul de sub apă al navei prin integrarea pe lungimea x a ariilor imerse. 2. Realizarea aplicaţiei sub EXCEL Se vor parcurge următoarele etape de lucru: - Se deschide un document EXCEL în care se desenează tabelul din figura 3. În celulele marcate cu culoarea

gri se vor introduce formule, restul celulelor fiind folosite pentru scrierea datelor şi a inscripţiilor; - După introducerea datelor în domeniile de celule C4:N10 şi D12:N12 se generează diagrama din figura 4,

numită "transversal al planului de forme al navei". Procedura de lucru pentru crearea acestei diagrame este următoarea:

= Se selectează domeniul de celule C4:D10; = Se apasă butonul Chart Wizard de pe bara de instrumente Standard; = Din cutia de dialog Chart Wizard Step 1 of 4 se selectează tipul Scatter, subtipul Scatter with data

points connected by lines without markers; = Din cutia de dialog Chart Wizard Step 2 of 4 se selectează pagina Data Range în care se bifează

Series in Columns. Apoi se comută în pagina Series. Dacă am lăsa datele aşa cum le acceptă automat Excel, cuplele navei ar apare culcate pe orizontală. De aceea, trebuie inversate seriile de valori x şi y între ele.

Dumitru Dragomir 2

Pentru aceasta, se selectează referinţele de celule din câmpul X Values şi se mută cursorul, selectând domeniul de celule D4:D10. Apoi se selectează referinţele de celule din câmpul Y Values şi se reselectează celulele din domeniul C4:C10. Pentru ca numele de identificare a diagramei astfel construite să nu fie cel acordat automat (adică Series1) se introduce cursorul în câmpul Name şi se scrie C0;

= Se pot introduce acum serii noi de date folosind butonul Add şi procedând la introducerea datelor în câmpurile X Values, Y Values şi Name la fel ca mai sus;

= Se apasă butonul Next pentru a se trece la pasul 3 al cutiei de dialog Chart Wizard. Aici, dacă se doreşte se pot introduce inscripţii de identificare a diagramei. În figura 4 aceste facilităţi nu au fost folosite aşa încât se poate trece la pasul 4 unde se alege opţiunea As new Sheet.

Fig.3.

Fig.4.

- Se aplică comanda Tools>Macro>Visual Basic Editor pentru a se deschide editorul Visual Basic; - În editorul Visual Basic se crează o foaie de module în care se scriu funcţiile de mai jos: - O funcţie pentru calculul ariei imerse a unei cuple. Această funcţie preia valorile de diviziune ale axei z

prin introducerea domeniului de celule care conţine aceste valori şi, analog, valorile semilăţimilor. Deoarece aceste valori sunt situate pe coloane, se foloseşte proprietatea Count a subobiectului Rows a obiectului Range pentru a se determina numărul de rânduri ale domeniului.

Se compară egalitatea lungimii domeniilor abscise - valori ale funcţiei contur al cuplei.

Metode Numerice – Semestrul II – Laborator 2 3

Dacă aceste lungimi nu sunt egale (ca o consecinţă a unei selectări greşite, funcţia returnează ca rezultat şirul de caractere "Eroare".

Dacă lungimile sunt egale se efectuează calculul integralei într-un ciclu For. Deoarece aria astfel calculată este numai jumătate din aria efectivă (considerând şi partea din bordul simetric) valoarea obţinută este multiplicată cu 2 şi este returnată de funcţie ca rezultat.

Function Arie_cupla(Dom_z As Range, Dom_y As Range) lungz = Dom_z.Rows.Count lungy = Dom_y.Rows.Count If lungz = lungy Then Arie_cupla = 0 For i = 1 To lungz - 1 z1 = Dom_z(i, 1) z2 = Dom_z(i + 1, 1) y1 = Dom_y(i, 1) y2 = Dom_y(i + 1, 1) Arie_cupla = Arie_cupla + 0.5 * (y2 + y1) * (z2 - z1) Next i Arie_cupla = 2 * Arie_cupla Else Arie_cupla = "Eroare" End If End Function

- O funcţie pentru calculul volumului de apă dislocuit. Această funcţie este concepută similar, cu

următoarele deosebiri: = Datele sunt preluate din domenii de celule organizate pe rânduri, motiv pentru care este folosită

proprietatea Count a subobiectului Columns a obiectului Range; = Nu se mai face multiplicare cu 2 deoarece volumul imers rezultă direct din calcul.

Function Volum_imers(Dom_x As Range, Dom_Arii As Range) lungx = Dom_x.Columns.Count lungar = Dom_Arii.Columns.Count If lungx = lungar Then Volum_intre_cuple = 0 For i = 1 To lungar - 1 x1 = Dom_x(1, i) x2 = Dom_x(1, i + 1) ar1 = Dom_Arii(1, i) ar2 = Dom_Arii(1, i + 1) Volum_ imers = Volum_ imers + 0.5 * (ar2 + ar1) * (x2 - x1) Next i Else Volum_imers = "Eroare" End If End Function Pentru a se putea folosi aceste funcţii se procedează astfel: - Mai întâi se selectează domeniul de celule C4:C10; - Se aplică comanda Insert>Name>Define; - În cutia de dialog Define Name se va observa: existenţa în câmpul Refers to a referinţei de câmp care ar

putea să apară sub forma =Sheet1!$C$4:$C$10. Dacă referinţa este incorectă se va selecta şirul de caractere al referinţei şi se va repeta selecţia câmpului referit din foaia de lucru; existenţa în câmpul Names in Workbook a unei denumiri - în cazul de faţă notaţia z culeasă automat de către EXCEL din prima celulă de deasupra câmpului selectat. Vom şterge această denumire şi vom scrie în locul ei domz;

Folosirea formulei de calcul a unei arii imerse se face astfel: - Se plantează cursorul în celula D11; - Se apasă butonul fx de pe bara de scule Standard; - După deschiderea cutiei de dialog Paste function se selectează categoria de funcţii User Defined iar din

lista de funcţii se selctează funcţia Arie_cupla; - După ce se apasă pe butonul OK şi este afişată cutia de dialog Arie_cupla se introduc, prin selecţie cu

mouse-ul, domeniile de celule pentru valorile z (Dom_z) şi pentru valorile semilăţimilor (Dom_y);

Dumitru Dragomir 4

- O dată ce prima celulă pentru funcţia de arie a fost completată, celelalte celule se autocompletează folosind metoda extinderii prin tragere, astfel: se selectează celula completată (D11), se "apucă" cu cursorul mouse de "mânerul de extindere" (din partea din dreapta-jos a celulei) şi se "trage" peste celulele E1:N11;

- În sfârşit, pentru a calcula volumul imers, se selectează cursorul în celula N13 şi se apasă pe butonul fx de unde se selectează funcţia Volum_imers;

- În cutia de dialog a funcţiei Volum_imers se introduc domeniile de celule D12:N12 pentru abscisele x şi D11:N11 pentru mărimile ariilor transversale imerse.

Observaţie: În aplicaţia de mai sus valorile au fost exprimate în m pentru lungimi, m2 pentru arii şi m3 pentru

volum. Din acest motiv deplasamentul navei rezultă imediat în tone din înmulţirea volumului cu greutatea specifică a apei.