Cel mai semnificativ avantaj în utilizarea ET este capacitatea de a calcula cu formule bazate pe valorile introduse în foaia de lucru, precum și pe instrumente de calcul încorporate (funcții) care efectuează calcule financiare și statistice complexe, ajută la luarea deciziilor sau cuvânt prelucrare. Utilizarea funcțiilor încorporate reduce probabilitatea de erori.

formule

Formulele sunt instrucțiuni pentru Excel cu privire la modul de calculare a datelor. Componentele lor sunt valori și operații (+, -, /, *, ^,%). Valorile (operanzii) sunt constante, valori constante sau argumente.

Formula folosește adrese de celule, cum ar fi C1 sau D4, care se referă la valorile acestor celule. Se folosesc, de asemenea, nume de zone de celule.

Formula începe întotdeauna cu un semn "=", altfel Excel consideră text. Se folosesc doar paranteze mici, folosind „:” este pentru a defini o zonă, iar „,” este pentru a lista adresele celulelor sau zonele celulei. Fiecare consolă de deschidere trebuie să corespundă cu una de închidere.

În majoritatea cazurilor, adresele de celule relative sunt utilizate în construcția de formule.

La copierea formulei, adresele celulelor implicate în ea sunt corectate pentru a se referi la adresa noii celule, deoarece celulele din formula din vechea celulă se refereau la adresa acesteia.

Dacă vă aflați în C5 și descrieți adresa C3 din formulă, Excel își imaginează folosind o celulă care se află la două rânduri deasupra celei actuale și la patru coloane la stânga.

Adresele absolute sunt utile dacă doriți ca formula să utilizeze date din celule specifice în loc să le adapteze la poziția noii celule.

Formulele cu adrese absolute indică întotdeauna spre ele, indiferent unde se află.

Adresele absolute și relative pot fi combinate în adrese mixte. Apoi coloana este absolută și ordinea este relativă sau invers. Când introduceți $ A 1, când copiați, rândul se va schimba și coloana nu .

Dacă vă referiți la alte foi de lucru, introduceți numele foii în fața adresei, urmat de „!” .

Foaia 1! A1 este o referință la celula A1 din foaia Foaia1. Dacă există spații goale în numele foii, încadrați-l între ghilimele.

Se pot utiliza foi de lucru din alte fișiere. Adresa celulei arată astfel: „[nume fișier]” Foaie1! A1 .

Operatorii efectuează calcule aritmetice cu formule, procesare de text, comparații și relații între referințele celulei.

-operatori aritmetici: adunare (+), scădere (-), înmulțire (*), diviziune (/), procent (%), scalare (^);

-operatori de text: lipirea textului (&);

-operatori de comparație: egal (=), mai puțin (), mai puțin și egal (=) și diferiți (<>) .

Ordinea de execuție a operatorilor urmează prioritatea pentru executarea operațiilor matematice. Poate fi schimbat folosind paranteze mici. Fiecare consolă de deschidere corespunde cu una de închidere.

Excel tratează datele și orele ca numere. Pentru a face acest lucru, acestea trebuie introduse în formatul utilizat de Excel, încadrându-le între ghilimele.

Diferența dintre două date este descrisă după cum urmează: „2/2/1998” - „27/05/1998” și este egală cu 6.

Dacă datele introduse nu sunt recunoscute ca dată sau oră, sunt luate ca text și rezultatul formulei este un mesaj de eroare #VALUE. În acest caz, puteți utiliza funcția DATE (an; lună; zi) și utilizați-o pentru a scădea datele: DATE (1998; 06; 02) - DATE (1998; 05; 27).

Când utilizați adrese de celule în formule și funcții, acestea nu furnizează informații despre ceea ce se află în spatele datelor din acele celule. Utilizarea unei zone denumite de celule face informațiile din formulă mai clare și mai expresive. (de exemplu = NALICHNO - SOLD, dacă este vorba de scăderea a două cantități, deoarece suprafețele celulelor sunt denumite în prealabil cu Insert/Define/Name - vezi Subiectul 3)

Excel oferă următorul set de funcții: matematică, financiară, statistică și bază de date, dată, oră și informații, logică, căutare și adresare, text și trigonometric.

Figura 11.1

Fiecare funcție constă din semnul "=", nume și argumente - adrese de celule, care sunt utilizate în calcule. Rezultatul funcției este vizibil în celula activă. Cel mai adesea, argumentele sunt numere, dar pot fi text, valori, date, ore și tablouri.

Puteți introduce funcția în mai multe moduri:

-intrare directă (Figura 11.1);

-introduceți o funcție din butonul f x> Function Wizard;

-introduceți o funcție prin intermediul meniului Insert/Function/Paste Function (Figura 11.2) .

Figura 11.2

Figura 11.3

În ultimele două cazuri se face în același mod. În fereastra Lipire funcție, în câmpul Categorie funcție, apar categoriile, iar în câmpul Listă nume funcții, funcțiile categoriei curente sunt listate în ordine alfabetică. Argumentul poate fi o celulă, o zonă a celulei sau o altă funcție. Căptușit cu virgulă sau punct și virgulă (în funcție de setarea Windows) .

După selectarea funcției, apare o a doua fereastră cu câmpurile pentru argumente, o scurtă descriere a funcției și un loc pentru rezultat (Formula rezultat =) (Figura 11.3). În același timp, este afișat în bara de formule .

După ce faceți clic pe butonul OK>, fereastra se închide și rezultatul calculului funcției apare în celulă .

Dacă formula este incompletă sau incorectă, poate apărea un mesaj de eroare precum # NAME sau #NUM. În unele cazuri, se află partea de formulă în care Excel presupune că există o eroare.

Puteți edita o funcție deja introdusă utilizând funcția Paste sau puteți face ajustări direct la bara de formule.

Dacă utilizați funcția Paste:

- Activați celula care conține funcția.

-Selectați Insert/Function. Apare o casetă de dialog pentru introducerea argumentelor.

-Efectuați ajustările necesare .

Dacă efectuați ajustări manuale:

-Activați celula care conține funcția .

-Efectuați ajustările necesare .

-Apăsați Enter> sau faceți clic pe butonul de bifare.

Dacă aveți nevoie de ajutor pentru implementarea caracteristicilor:

-Faceți clic pe Găsiți .

-Introduceți funcția după categorie și, în câmpul de jos, faceți dublu clic pe funcțiile Foaie de lucru listate pe categorii. Se obține o listă de funcții sortate după categorie. Funcțiile sunt descrise în cele mai detaliate sintaxă, sfaturi și exemple de aplicații.

Încărcați fișierul Student.xls. Activați tabelul Student2. Calculați nota medie a fiecărui elev, numele disciplinei în care nota medie este maximă și numele cursului cu o medie minimă .

Activați tabelul Student3. Completați celulele din coloana% BURSA în funcție de venitul și succesul elevului astfel: un student care are un venit mediu primește o bursă socială în cuantum de 60% din salariul minim, indiferent de succes; altfel cu succes> = 4,50 și succes = 5,00 și succes 5,50 - 75% .

SUMA DE BURSĂ calculează ca% BURSA din salariul minim, care este introdus într-o celulă separată.

Definiți clasamentul ca un clasament în funcție de succesul mediu al Student2 .

2 Faceți clic pe Student2 și activați-l.

3. Puteți calcula câmpul SUCCES MEDIU în două moduri:

3.1.Utilizarea funcției SUM:

3.1.1 Activați celula G3 .

3.1.2 Faceți clic pe butonul AutoSum>. Apare = SUM (A3: F3) .

3.1.3 Faceți corecții la argumentele B3: F3 (faceți clic pe linia formulă din fața lui A, ștergeți cu tasta Del> și tastați B).

3.1.4 Puneți „/” (semnul diviziunii) și 5, adică. formula își asumă forma = SUM (B3: F3)/5 .

3.1.5 Apăsați tasta Enter> sau faceți clic pe tasta Enter> .

3.1.6 În celula G3 se calculează succesul mediu al fiecărui elev.

3.2 Folosind funcția MEDIE pentru a calcula media aritmetică .

3.2.1.Activați celula G3 .

3.2.2 Faceți clic pe Paste Function sau selectați I nsert/Function .

3.2.3 Din categoria Cele mai utilizate recent sau din categoria Statistică, selectați MEDIE .

3.2.4 Faceți clic pe butonul OK>.

3.2.5 În câmpul Number1 tastați B3: F3 (dacă se află pe masă caseta de dialog a funcției, poate fi mutată trăgând în jos pentru a nu interfera).

3.2.6.În câmpul Rezultate formula = 3.40 .

3.2.7 Dacă sunteți de acord, faceți clic pe butonul OK>

3.2.8 Succesul mediu este calculat în celula G3 .

3.3. Copiați conținutul celulei G3 din G3 în G13 în modul familiar din Tema4. Adresele care participă la formulă sunt relative și formula este calculată corect în întreaga coloană SUCCES MEDIU .

4. În caseta A15 scrie SUCCES MEDIU PE DISCIPLINE.

5. Selectați Format/Celule/Alignment/Wrap Text; în câmpul Orizontal: Centru; în caseta Vertical: Center și faceți clic pe OK> .

6.1.În celula B15 scrieți manual (sau în cele două moduri prezentate mai sus) = MEDIE (B3: B13). Zona B3: B13 poate fi indicată prin glisarea cursorului mouse-ului peste el.

6.2.În celula B15 obțineți 4.181818 .

6.3. Copiați formula din celula B15 pentru fiecare disciplină. În partea fracționată a numerelor din linia 15 se obțin 6 cifre .

Figura 11.4

6.4.Marcați zona B15 până la F15.

6.5 Selectați Format/Celule/Număr/Număr. În câmpul Locuri zecimale, tastați 2 și faceți clic pe butonul OK> (Figura 11.4) .

De asemenea, puteți selecta meniul Format/Celule din meniul contextual făcând clic dreapta după evidențiere .

7. În celula A16 scrieți: DISCIPLINA CU MAX. Miercuri SUCCES și aliniați conținutul în modul de celula A15.

8. În celula B16, scrieți următoarea formulă complexă: = INDEX (B2: F13; 1; MATCH (MAX (B15: F15); B15: F15; 0)) .

8.1 Funcția MAX returnează valoarea maximă de la B15 la F15 ca rezultat .

8.2.1. Funcția MATCH (valoare de căutare; zonă de căutare; tip de comparație (0 pentru =, 1 pentru =)) returnează numărul coloanei în care se află succesul maxim.

8.2.2 În acest caz, aceasta este coloana 5 .

8.3 Funcția INDEX (zonă; rând; coloană) returnează conținutul celulei cu adresă (rând, coloană) ca rezultat (Figura 11.5).

Figura 11. 5

9. În celula A17 scrieți DISCIPLINĂ CU MINIM. SUCCES MEDIU. (Copiați conținutul A16 și editați).

10. În celula B17, scrieți următoarea formulă (sau copiați conținutul B16 și editați): = INDEX (B2: F13; 1; MATCH (MIN (B15: F15); B15: F15; 0)). Caseta B17 citește LAAG (Figura 11.6).

Figura 11. 6

11. Activați foaia de lucru Student3 .

12.1.Activați celula F2 .

12.2 În celula F2 scrie MINIM. SALARIZAȚI și aliniați conținutul cu Format/Celule/Aliniere/Wrap Text; Câmp vertical: Centru; Câmp orizontal: Centru; faceți clic pe buton .

12.3 În celula F3, introduceți un salariu minim de 60.000 BGN. (din Format/Celule/Număr/Monedă/Simbol: BGN).

13. Activați celula B3 .

14. În celula B3, scrieți o formulă folosind funcția IF (condiție; dacă condiția este adevărată; dacă condiția este greșită) și funcția = ȘI (condiția1, condiția2.), Care conectează mai multe condiții și returnează adevărat dacă toate condițiile sunt îndeplinite și se află altfel

= IF (ȘI (Student2! G3> = 4,5; Student2! G3 = 5; Student2! G3 5,5; 75%; IF (Student1! F3) (figura 11.7) .

Figura 11.7

Note privind formula:

14.1 Descărcarea de gestiune a Student2! indică foaia de lucru unde se află adresa G3 .

14.2. Adresa absolută $ 3 $ este utilizată pentru a se referi la salariul minim, deoarece salariul minim este întotdeauna în celula F3 .

14.3.Când scrieți formula complexă, trebuie să aveți grijă: numărul parantezelor de deschidere este egal cu numărul celor care se închid.

15. Copiați formula de la B3 la B13.

15.1 Dacă datele din această zonă apar ca numerele 0,60, 0,75 etc., evidențiați zona și faceți clic pe buton

15.2 Numerele sunt formatate în procente.

16.1.Activați celula C3 .

16.2 Scrieți formula = B3 * $ F $ 3 (în celula $ F $ 3 este salariul minim constant).

16.3 În celula C3 obțineți suma de 0,00.

16.4 Copiați formula de la C3 la C13. Primiți suma bursei pentru fiecare student.

17. Pentru a completa coloana CLASAMENT DE SUCCES, activați Student2 .

17.1. Marcați tabelul fără titlu și extrase.

17.5. Foaia 4 redenumire în Klasir .

Figura 11.8

17.6 Selectați Date/Sortare/Sortare după: CP. SUCCES/Descending și faceți clic pe buton. Tabelul este sortat în ordine descrescătoare în funcție de performanța medie a elevilor.

17.7.Activați tabelul Student3.

17.8.Activați celula D3.

17.9. Tipăriți funcția: = MATCH (A3; Klasir! $ A $ 2: $ A $ 12; 0) (figura 11.8) .

17.10 În coloana NUMĂR FACIAL al tabelului Klasir, funcția MATCH caută numărul facultății secvențiale de la Student3 și returnează ca rezultat pe ce rând relativ se află. Această ordine corespunde ordinii din clasament.

Creați un tabel al Fondului de pensii care conține date despre contribuțiile la fondul de pensii al unui grup de angajați, cu următoarele coloane:

-DATA CHITANȚEI

1) Introduceți datele pentru zece angajați introducând data angajării folosind funcția DATE.

2) Introduceți data curentă în dreapta titlului tabelului utilizând funcția TODAY.

3) Calculați contribuția utilizând funcțiile IF, ȘI și SAU după cum urmează: dacă rata contribuției este diferită de 0 ȘI SAU vechimea în serviciu este> 2 ani SAU vârsta angajatului este> = 28 de ani, contribuția este calculată ca procent din salariul de bază, altfel este 0.

4) Găsiți venitul total în fondul de pensii.

1. Dacă sunteți în Excel și ați lucrat cu un alt tabel, selectați Fișier/Nou/Caiet de lucru și faceți clic pe buton .

2. După cum este descris în subiectele anterioare, completați titlul tabelului, numele coloanelor și numele de familie ale angajaților de la A2 la A12 .

3. Activați celula B3.

4. Faceți clic pe Paste Function sau selectați Insert/Function/Paste Function .

5. Din categoria de funcții, selectați Data și ora. Din funcția Listă - dată și faceți clic pe buton .

6. Apare o casetă de dialog, tastând 1970 în câmpul An, 05 în câmpul Lună și 08 în câmpul Zi. În celula B3 este scris 08.05.1970 (este posibil să existe un alt format pentru scrierea datei - 08/05/1970) (figura 11.9).

7. Copiați funcția și în celelalte celule, editând argumentele.

8. Completați casetele VÂRSTĂ și SALARIU DE BAZĂ la alegere.

9. Activați celula H2. Scrieți DATA ACTUALĂ.

10. Activați celula H3. Tastați = AZI (). Apare data curentă.

11. Activați celula F3. În funcție de starea sarcinii, scrieți formula: = IF ((% tranșă> 0; SAU (experiență de lucru> = 28; vârstă> = 2));% tranșă * salariu de bază; 0) .

În realitate, formula arată astfel: IF (AND (E3> 0; OR (YEAR ($ H $ 3) - YEAR (B3)> 2; C3> = 28)); E3 * D3; 0) .

Explicații: Funcția YEAR este utilizată pentru a lua doar anul celor două date. Se utilizează o adresă absolută pentru data curentă, deoarece data curentă este aceeași pentru toate celulele care conțin formula.

12. Marcați zona F3: F12 și setați formatarea la BGN (Format/Celule/Număr/Monedă)

13. Evidențiați coloana AGE (C3: C12) .

14. Selectați Format/Celule/Număr/Personalizat. În câmpul Tip, tastați ## ”d.” și faceți clic pe buton. Zona adoptă formatarea în ani.

15. Activați celula A14. Scrieți VENITUL TOTAL ÎN FONDUL DE PENSII.

16. Activați celula F14. Tastați funcția = SUM (F3: F12) (puteți face clic pe AutoSum). Caseta F14 calculează venitul total al fondului de pensii.

17. Marcați zona $ A $ 2: $ F $ 12 .

18. Închideți zona evidențiată cu Format/Border sau faceți clic pe butonul Borders> .

19. Evidențiați zona de $ A $ 14: $ F $ 14. Și în același mod înconjoară-l cu o linie selectată.

20. Activați celula F14. Colorează cu culoarea dorită făcând clic pe butonul Umplere culoare .