Formule EXCEL cu exemple - Instructiuni de utilizare. Diferențele dintre referințele absolute, relative și mixte

30.10.2019 Sfat

Pentru a înțelege cum să utilizați acest program, trebuie să luați în considerare formulele EXCEL cu exemple.

Dacă plasați cursorul mouse-ului pe orice celulă și faceți clic pe elementul „selectați funcție”, va apărea vrăjitorul pentru funcții.

Cu ajutorul lui, puteți găsi formula necesară cât mai repede posibil. Pentru a face acest lucru, puteți introduce numele și utiliza categoria.

Excel este foarte convenabil și ușor de utilizat. Toate funcțiile sunt împărțite în categorii. Dacă categoria funcției necesare este cunoscută, atunci selecția acesteia se efectuează în funcție de aceasta.

Dacă funcția este necunoscută utilizatorului, acesta poate seta categoria „listă alfabetică completă”.

De exemplu, sarcina este dată de a găsi funcția SUMIFS. Pentru a face acest lucru, trebuie să mergeți la categoria de funcții matematice și să găsiți acolo pe cea de care aveți nevoie.

Funcția VLOOKUP

Folosind funcția VLOOKUP, puteți extrage informațiile necesare din tabele. Esența scanării verticale este de a găsi valoarea în coloana din stânga a unui interval dat.

După care valoarea totală este returnată din celulă, care este situată la intersecția rândului și coloanei selectate.

Calculul VLOOKUP poate fi urmărit folosind un exemplu care oferă o listă de nume de familie. Sarcina este de a găsi numele de familie folosind numărul dat.

Folosind funcția VLOOKUP

Formula arată că primul argument al funcției este celula C1.

Al doilea argument A1:B10 este domeniul în care se efectuează căutarea.

Al treilea argument este numărul ordinal al coloanei din care ar trebui returnat rezultatul.

Calculați un nume de familie dat folosind funcția CĂUTARE V

În plus, puteți căuta un nume de familie chiar dacă lipsesc unele numere de serie.

Dacă încercați să găsiți un nume de familie dintr-un număr inexistent, formula nu va da o eroare, dar va da rezultatul corect.

Căutarea unui nume de familie cu numere lipsă

Acest fenomen se explică prin faptul că funcția VLOOKUP are un al patrulea argument, cu ajutorul căruia puteți seta vizualizarea pe intervale.

Are doar două semnificații - „fals” sau „adevărat”. Dacă argumentul nu este specificat, acesta este setat la adevărat în mod implicit.

Rotunjirea numerelor folosind funcții

Funcțiile programului vă permit să rotunjiți cu precizie orice număr fracționar în sus sau în jos.

Și valoarea rezultată poate fi folosită în calcule în alte formule.

Numerele sunt rotunjite folosind formula ROUNDUP. Pentru a face acest lucru, trebuie să completați celula.

Primul argument este 76.375, iar al doilea este 0.

Rotunjiți un număr folosind o formulă

În acest caz, numărul a fost rotunjit în sus. Pentru a rotunji valoarea în jos, selectați funcția ROUND DOWN.

Rotunjirea are loc la cel mai apropiat număr întreg. În cazul nostru, până la 77 sau 76.

Excel ajută la simplificarea oricăror calcule. Folosind o foaie de calcul, puteți finaliza teme superioare de matematică.

Programul este utilizat cel mai activ de către designeri, antreprenori și studenți.

Întregul adevăr despre formulele Microsoft Excel 2007

Formule EXCEL cu exemple - Instructiuni de utilizare

3. Rezultatul calculelor din celula C1 va fi:

4. Ce comandă ar trebui să utilizați pentru a introduce numere de la 1 la 10500 într-o coloană? 1) comanda „Umplere” din meniul „Editare”.

5. Ce formatare este aplicabilă celulelor în Excel 4) toate opțiunile sunt corecte

6. Care operator nu este inclus în grupul operatorilor aritmetici? 3)&

7. Care dintre următoarele nu este o caracteristică a unei celule? Marimea 3

8. Ce valoare poate lua o celulă? 4) toate cele de mai sus

9. Ce poate fi un argument de funcție? 4) toate opțiunile sunt corecte

10. Specificarea unei adrese de celulă într-o formulă este apelată 1) link

11. Excel este folosit pentru 2) crearea de foi de calcul

12. Cu ce caracter începe o formulă în Excel? 1)=

13. Pe ce se bazează orice diagramă? 4) date din tabel

14. Care opțiune indică corect succesiunea de execuție a operatorilor în formulă? 3) operatori de referință apoi operatori de comparație

15. Componenta minimă a tabelului este 1) celulă

16. Pentru ce este folosită funcția SUM? 2) pentru a obține suma numerelor specificate

17. Câte tipuri de adrese de celule există în Excel? 2) doi

18. Ce face Excel dacă există o eroare în formulă? 2) afișează un mesaj despre tipul de eroare ca valoare a celulei

19. Pentru ce este folosită fereastra de comandă „Form...”? 1) pentru a completa intrările din tabel

20. Care legătură este absolută? 3) 5 USD

21. Se numește aranjarea valorilor unui interval de celule într-o anumită secvență 4) Sortarea

22. Adresarea celulelor din foile de calcul, care păstrează o legătură către o anumită celulă sau zonă, se numește 3) absolut

26. Se selectează intervalul de celule A1:D3 din foaia de calcul MS EXCEL. Gama conține 4) 12 celule

27. Gama de criterii este utilizată în MS Excel când 1) folosind un filtru avansat

2). 1, 2, 4

29. Pentru a rezolva o ecuație cu o necunoscută în MS Escel, puteți folosi opțiunea 3) selectarea parametrilor

Procesor de cuvinte

1. Dacă în dialogul „Setări pagină” setați scara paginii la „nu mai mult de 1 pagină lățime și 1 pagină înălțime”, atunci când imprimați, dacă foaia este mai mare decât această dimensiune, ...

1). pagina va fi decupată la aceste dimensiuni

2). pagina va fi redusă la această dimensiune

3). pagina nu se va imprima

4). pagina va fi mărită la această dimensiune

2. Microsoft Word este: 3) editor de text

3. Deschideți Microsoft Word: 3) Start - Programe - Microsoft Word

4. Într-un editor de text, parametrii principali la setarea unui font sunt 1) font, dimensiune, stil

5. În timpul procesului de formatare, textul se modifică 2) parametrii de paragraf

6. Într-un editor de text, parametrii principali la setarea parametrilor de paragraf sunt 2) indentare, interval

7. Într-un editor de text, o condiție necesară pentru efectuarea operației Copiere este 4) selectarea unei porțiuni de text

8. Într-un editor de text, când setați parametrii paginii, 3)margini, orientare

9. În timpul procesului de editare, textul se modifică 3) succesiune de caractere, cuvinte, paragrafe

10. Obiectul minim folosit într-un editor de text este 4) familiaritate (simbol)

11. Într-un editor de text, operația Copiere devine posibilă după 4) evidențierea unei bucăți de text

12. Pentru a activa modul de personalizare a meniului în editorul de text MS Word, trebuie să rulați comanda 4) Service-Setări

2)2 - b

4)2, 4, 5

17. Grupul de control din bara de instrumente „Review” include elemente pentru 1) crearea, vizualizarea și ștergerea notelor

18. Comutarea ciclică între modurile de inserare și de înlocuire la introducerea caracterelor de la tastatură se realizează prin apăsarea tastei 4)Inserare

19. Creați un document: 1)Fișier - Creați

20. Deschideți un document 4) Start – Documente

22. Documentele sunt de obicei salvate: 2) În folderul "" Documentele mele ""

23. Selectați modul de vizualizare a documentului care este utilizat special pentru introducerea textului: 1) regulat

24. Selectați algoritmul corect de imprimare a documentului: 3) Efectuați o previzualizare, Fișier - Imprimare - Selectați imprimanta - Specificați numărul de copii - Ok

25. Care listă se numește „bulleted”: 2). fiecare linie începe cu un marker - un anumit caracter

26. Care bară de instrumente este concepută pentru a lucra cu tabele: 2) Mese și chenare

Dacă nu ați mai lucrat cu Excel Online înainte, veți afla în curând că nu este doar o foaie de calcul pentru introducerea numerelor. Da, Excel Online poate fi utilizat pentru a căuta totaluri pe coloane sau rânduri, dar puteți, de asemenea, să calculați o plată ipotecară, să rezolvați probleme de matematică sau de inginerie și să găsiți scenarii optime pe baza valorilor variabilelor specificate.

În Excel Online, toate acestea sunt implementate folosind formule din celule. O formulă efectuează calcule sau alte acțiuni asupra datelor dintr-o foaie de lucru. O formulă începe întotdeauna cu un semn egal (=), care poate fi urmat de numere, operatori matematici (cum ar fi semnul plus sau minus) și funcții care îmbunătățesc foarte mult capacitățile formulei.

Mai jos este un exemplu de formulă care înmulțește 2 cu 3 și adaugă 5 la rezultat pentru a obține 11.

Următoarea formulă folosește funcția PLT pentru a calcula plata ipotecii (1.073,64 USD) la 5% dobândă (5% împărțit la 12 luni este egală cu dobânda lunară) pentru o perioadă de 30 de ani (360 de luni) cu un împrumut de 200.000 USD:

PMT(0,05/12;360;200000)

Mai jos sunt exemple de formule care pot fi folosite pe foile de lucru.

=A1+A2+A3 Calculează suma valorilor din celulele A1, A2 și A3.

=ROOT(A1) Utilizează funcția RĂDĂCINĂ pentru a returna rădăcina pătrată a numărului din celula A1.

=AZI() Returnează data curentă.

=UPPERCAP(„bună ziua”) Convertește textul „hello” în „HELLO” folosind funcția CAPITAL.

= Dacă (A1>0) Analizează celula A1 și verifică dacă valoarea acesteia este mai mare decât zero.

Elemente ale formulei

O formulă poate conține, de asemenea, unul sau mai multe dintre următoarele elemente: funcții, referințe, operatori și constante.

1. Funcții. Funcția PI() returnează valoarea lui Pi: 3,142...

3. Constante. Numerele sau valorile textului introduse direct într-o formulă, cum ar fi 2.

4. Operatori. Operatorul ^ ("cap") este folosit pentru a ridica un număr la o putere, iar operatorul * ("asterisc") este folosit pentru a multiplica.

Utilizarea constantelor în formule

O constantă este o valoare gata făcută (necalculată) care rămâne întotdeauna neschimbată. De exemplu, data 10/09/2008, numărul 210 și textul „Profit pentru trimestrul” sunt constante. o expresie sau valoarea ei nu sunt constante. Dacă o formulă dintr-o celulă conține constante, dar nu referințe la alte celule (de exemplu, arată ca =30+70+110), valoarea într-o astfel de celulă se schimbă numai după modificarea formulei.

Utilizarea operatorilor în formule

Operatorii definesc operatiile care trebuie efectuate asupra elementelor unei formule. Calculele sunt efectuate într-o ordine standard (corespunzătoare regulilor de bază ale aritmeticii), dar aceasta poate fi schimbată folosind paranteze.

Tipuri de operatori

Microsoft Excel acceptă patru tipuri de operatori: operatori aritmetici, de text, de comparare și de referință.

Operatori aritmetici

Operatorii aritmetici sunt utilizați pentru a efectua operații aritmetice de bază, cum ar fi adunarea, scăderea, înmulțirea, împărțirea sau combinarea numerelor. Rezultatele operațiunilor sunt numere. Operatorii aritmetici sunt prezentați mai jos.

Operatori de comparare

Operatorii de comparație sunt utilizați pentru a compara două valori. Rezultatul comparației este o valoare logică: TRUE sau FALSE.

Operator de concatenare a textului

Ampersand ( & ) este folosit pentru a concatena (concatena) unul sau mai multe șiruri de text într-unul singur.

Operatori de legături

Puteți utiliza următorii operatori pentru a defini referințe la intervale de celule:

Ordinea acțiunilor în formule în Excel Online

În unele cazuri, ordinea de calcul poate afecta valoarea returnată de o formulă, de aceea este important să înțelegeți ordinea standard a calculelor și cum o puteți modifica pentru a obține rezultatele dorite.

Ordinea de calcul

Formulele calculează valorile într-o anumită ordine. Formula începe întotdeauna cu un semn egal ( = ). Excel Online interpretează semnele care urmează semnului egal într-o formulă. După semnul egal, puteți evalua elemente (operanzi) precum constantele sau referințele de celule. Acestea sunt separate prin operatori de calcul. Excel Online evaluează formula de la stânga la dreapta în funcție de o anumită ordine pentru fiecare operator din formulă.

Prioritatea operatorului

Când sunt utilizați mai mulți operatori într-o singură formulă, Excel Online efectuează operațiunile în ordinea prezentată în tabelul de mai jos. Dacă o formulă conține operatori care au aceeași prioritate, cum ar fi operatorii de împărțire și înmulțire, Excel Online îi rulează de la stânga la dreapta.

Folosind paranteze

Pentru a schimba ordinea în care o formulă este evaluată, includeți partea din formula care trebuie executată mai întâi între paranteze. De exemplu, următoarea formulă ar avea ca rezultat numărul 11, deoarece Excel Online face înmulțirea înainte de adunare. În această formulă, numărul 2 este înmulțit cu 3, iar apoi numărul 5 este adăugat la rezultat.

Dacă modificați sintaxa folosind paranteze, Excel Online adaugă 5 și 2 și apoi înmulțește rezultatul cu 3; rezultatul acestor acțiuni va fi numărul 21.

În exemplul de mai jos, parantezele care includ prima parte a formulei specifică următoarea ordine de calcul pentru Excel Online: se determină valoarea B4 + 25, iar rezultatul rezultat este împărțit la suma valorilor din celule D5, E5 și F5.

=(B4+25)/SUM(D5:F5)

Utilizarea funcțiilor și funcțiilor imbricate în formule

Funcțiile sunt formule predefinite care efectuează calcule pe valori date, numite argumente și într-o ordine specificată. Aceste funcții vă permit să efectuați atât calcule simple, cât și complexe.

Sintaxa funcției

Mai jos este un exemplu de funcție RUNDĂ rotunjirea unui număr din celula A10 demonstrează sintaxa funcției.

1. Structura. Structura unei funcții începe cu un semn egal (=), urmat de numele funcției, o paranteză de deschidere, argumentele funcției separate prin virgule și o paranteză de închidere.

2. Numele funcției. Pentru a afișa o listă de funcții disponibile, faceți clic pe orice celulă și apăsați SHIFT+F3.

3. Argumente. Există diferite tipuri de argumente: numere, text, valori booleene (adevărat și fals), matrice, valori de eroare (cum ar fi #N/A) sau referințe de celule. Argumentul folosit trebuie să returneze o valoare care este validă pentru argumentul dat. Constantele, formulele și alte funcții sunt, de asemenea, folosite ca argumente.

4. Indicator pentru argumente. Când introduceți o funcție, apare un tooltip cu sintaxa și argumentele. De exemplu, un sfat explicativ apare după ce introduceți expresia =ROUND(. Sfaturile instrumente apar numai pentru funcțiile încorporate.

Intrarea în funcții

Fereastra de dialog Funcția de inserare facilitează introducerea de funcții la crearea formulelor care le conțin. Când introduceți o funcție într-o formulă din caseta de dialog Funcția de inserare Sunt afișate numele funcției, toate argumentele acesteia, o descriere a funcției și a fiecărui argument, rezultatul curent al funcției și întreaga formulă.

Pentru a facilita crearea și editarea formulelor și pentru a minimiza numărul de greșeli de scriere și de sintaxă, utilizați completarea automată a formulei. După ce introduceți un semn egal (=) și literele inițiale sau declanșatorul de afișare în Excel Online, sub celulă apare o listă derulantă dinamică de funcții, argumente și nume disponibile care se potrivesc cu acele litere sau declanșatoare. Elementul din lista derulantă poate fi apoi inserat în formulă.

În unele cazuri, poate fi necesar să utilizați o funcție ca unul dintre argumentele unei alte funcții. De exemplu, formula de mai jos folosește o funcție imbricată pentru a compara rezultatul cu valoarea 50 IN MEDIE.

1. Funcțiile AVERAGE și SUM sunt imbricate în funcția IF.

Tipuri de valori calculate valide O funcție imbricată folosită ca argument trebuie să returneze tipul de date corespunzător. De exemplu, dacă argumentul trebuie să fie boolean, adică TRUE sau FALSE, funcția imbricată trebuie să returneze și o valoare booleană (TRUE sau FALSE). În caz contrar, TE102825393 va genera eroarea „#VALUE!”

Limitați numărul de niveluri de imbricare a funcției. Puteți utiliza până la șapte niveluri de funcții imbricate în formule. Dacă funcția B este un argument pentru funcția A, funcția B se află la al doilea nivel de imbricare. De exemplu, în exemplul de funcție de mai sus IN MEDIEȘi SUMĂ sunt funcții de nivel al doilea, deoarece ambele sunt argumente ale funcției DACĂ. O funcție imbricată ca argument într-o funcție IN MEDIE, va fi o funcție de nivel al treilea etc.

Utilizarea referințelor în formule

Referința indică o celulă sau un interval de celule dintr-o foaie de lucru și îi spune Excel Online unde sunt valorile sau datele pe care doriți să le utilizați în formulă. Folosind referințe într-o singură formulă, puteți utiliza date care se află în diferite părți ale foii de lucru, precum și valoarea unei celule din mai multe formule. De asemenea, puteți seta referințe la celule din diferite foi ale unui registru de lucru sau la celule din alte registre de lucru. Legăturile către celulele din alte registre de lucru se numesc legături sau legături externe.

Stilul link A1

Stilul de link implicitÎn mod implicit, Excel Online folosește stilul de referință A1, în care coloanele sunt identificate prin litere (de la A la XFD, pentru maximum 16.384 de coloane) iar rândurile sunt identificate prin numere (de la 1 la 1.048.576). Aceste litere și numere se numesc titluri de rând și de coloană. Pentru a face referire la o celulă, introduceți litera coloanei și apoi numărul rândului. De exemplu, linkul B2 puncte la celula situată la intersecția coloanei B și a rândului 2.

Diferențele dintre referințele absolute, relative și mixte

Legături relative. O referință relativă într-o formulă, cum ar fi A1, se bazează pe poziția relativă a celulei care conține formula și a celulei la care se face referire. Când modificați poziția celulei care conține formula, referința se schimbă și ea. Când copiați sau completați o formulă de-a lungul rândurilor și de-a lungul coloanelor, legătura este ajustată automat. În mod implicit, formulele noi folosesc referințe relative. De exemplu, atunci când copiați sau completați o referință relativă din celula B2 în celula B3, aceasta se schimbă automat de la =A1 la =A2.

Legături absolute. O referință de celulă absolută într-o formulă, cum ar fi $A$1, se referă întotdeauna la o celulă situată într-o anumită locație. Când modificați poziția unei celule care conține o formulă, referința absolută nu se modifică. Când copiați sau completați o formulă pe rânduri și coloane, referința absolută nu este ajustată. În mod implicit, formulele noi folosesc referințe relative, dar pentru a utiliza referințe absolute trebuie să activați opțiunea corespunzătoare. De exemplu, când copiați sau completați o referință absolută din celula B2 în celula B3, aceasta rămâne aceeași în ambele celule: =$A$1.

Legături mixte O referință mixtă conține o coloană absolută și un rând relativ și un rând absolut și o coloană relativă. O referință absolută de coloană are forma $A 1, $B 1 etc. O referință absolută de rând are forma $1, B $1 etc. Dacă modificați poziția celulei care conține formula, referința relativă se va schimba , dar referința absolută nu se va schimba. Dacă copiați sau completați o formulă în rânduri sau coloane, referința relativă este ajustată automat, dar referința absolută nu se modifică. De exemplu, atunci când copiați și completați o referință mixtă din celula a2 în celula B3, aceasta este ajustată de la =A$1 la =B$1.

Stil de legătură 3D

Mod convenabil de a lega mai multe foi Referințele 3D sunt folosite pentru a analiza datele din aceeași celulă sau din aceeași gamă de celule pe mai multe foi din același registru de lucru. O referință 3D conține o referință de celulă sau interval precedată de nume de foi. Excel Online folosește toate foile specificate între numele de început și de sfârșit în link. De exemplu, formula =SUM(Sheet2:Sheet13!B5) însumează toate valorile conținute în celula B5 pe toate foile de la Foaia 2 până la Foaia 13, inclusiv.

Folosind referințe 3D, puteți face referire la celule de pe alte foi, puteți defini nume și puteți crea formule folosind următoarele funcții: SUM, AVERAGE, AVERAGE, COUNT, COUNTA, MAX, MAX, MIN, MIN, PRODUCT, STDEV.Y, STDEV.V , STDEV, STDEV, DISP, DISP.V, DISPA și DISP.

Ce se întâmplă când mutați, copiați, lipiți sau ștergeți foi. Următoarele exemple explică ce modificări au loc referințelor 3D atunci când mutați, copiați, lipiți sau ștergeți foile către care indică referințele. Exemplele folosesc formula =SUM(Sheet2:Sheet6!A2:A5) pentru a suma valorile din celulele de la A2 la A5 din foile de la două la șase.

Lipiți sau copiați Dacă inserați foi între foile 2 și 6, Excel Online va adăuga conținutul celulelor de la A2 la A5 din foile adăugate la total.

Îndepărtarea Dacă ștergeți foile dintre foile 2 și 6, Excel Online nu va folosi valorile acestora în calcule.

In miscare Dacă mutați foile între Foaia 2 și Foaia 6, astfel încât acestea să ajungă în fața foii 2 sau în spatele foii 6, Excel Online va scădea din total conținutul celulelor din foile mutate.

Mutați foaia de sfârșit Dacă mutați foaia 2 sau 6 într-o altă locație din registrul de lucru, Excel Online va ajusta cantitatea pentru a reflecta modificarea intervalului de foi.

Îndepărtarea unei foi de capăt Dacă ștergeți foaia 2 sau 6, Excel Online va ajusta suma pentru a reflecta modificarea intervalului de foi.

Stilul link R1C1

Puteți utiliza un stil de referință în care atât rândurile, cât și coloanele sunt numerotate. Stilul de referință R1C1 este util pentru calcularea poziției coloanelor și rândurilor în macrocomenzi. Când utilizați acest stil, poziția unei celule în Excel Online este indicată de litera R urmată de numărul rândului și litera C urmată de numărul coloanei.

Când înregistrați o macrocomandă în Excel Online, unele comenzi folosesc stilul de legătură R1C1. De exemplu, dacă este scrisă comanda de a face clic pe un element Autosumă Pentru a adăuga o formulă care însumează o serie de celule, Excel Online va folosi stilul de referință R1C1 mai degrabă decât A1 atunci când scrie formula.

Utilizarea numelor în formule

Puteți crea nume specifice pentru a identifica celule, intervale de celule, formule, constante și tabele în Excel Online. Un nume este o scurtătură semnificativă care explică scopul unei referințe la o celulă, constantă, formulă sau tabel, deoarece poate fi dificil de înțeles esența lor la prima vedere. Mai jos sunt exemple de nume și modul în care utilizarea acestora face formulele mai ușor de înțeles.

Tipuri de nume

Există mai multe tipuri de nume care pot fi create și utilizate.

Nume specific Numele folosit pentru a reprezenta o celulă, un interval de celule, o formulă sau o constantă. Vă puteți crea propriile nume definite. Uneori (de exemplu, la setarea zonei de imprimare), Excel Online creează automat un anumit nume.

Numele tabelului Numele unui tabel Excel Online care este o colecție de date despre un anumit subiect, stocate în înregistrări (rânduri) și câmpuri (coloane). Excel Online creează un nume implicit de tabel Excel Online pentru „Tabel1”, „Tabel2” etc., de fiecare dată când inserați un tabel Excel Online, dar puteți modifica aceste nume pentru a le face mai semnificative.

Crearea și introducerea numelor

Numele este creat folosind comanda creați un nume din selecție. Puteți crea în mod convenabil nume din nume de rânduri și coloane existente folosind o selecție dintr-o foaie de lucru.

Notă:În mod implicit, numele folosesc referințe absolute de celule.

Vă puteți introduce numele folosind următoarele metode.

Chei Introduceți un nume, de exemplu, ca argument de formulă.

Completarea automată a formulelor. Utilizați lista derulantă Completare automată a formulei, care afișează automat nume valide.

Folosind formule matrice și constante matrice

Excel Online nu acceptă formule matrice. Puteți vizualiza rezultatele formulelor matrice create pe desktopul Excel, dar nu le veți putea edita sau recalcula. Dacă aveți aplicația desktop Excel instalată pe computer, faceți clic Deschideți în Excel pentru a trece la lucrul cu matrice.

Exemplul de formulă matrice de mai jos calculează prețul total al acțiunilor; rândurile de celule nu sunt utilizate la calcularea și afișarea valorilor individuale pentru fiecare stoc.

Când introduceți formula „=(SUM(B2:D2*B3:D3))” ca formulă matrice, se calculează mai întâi valoarea stocului și prețului pentru fiecare schimb, apoi se calculează suma tuturor rezultatelor.

Calcularea valorilor multiple. Unele funcții returnează matrice de valori sau necesită o matrice de valori ca argument. Pentru a calcula mai multe valori folosind o formulă matrice, trebuie să introduceți matricea într-un interval de celule care are același număr de rânduri sau coloane ca și argumentele matricei.

De exemplu, având în vedere o serie de trei valori de vânzări (în coloana B) timp de trei luni (în coloana A), funcția TENDINŢĂ determină continuarea seriei liniare a volumelor de vânzări. Pentru a putea afișa toate rezultatele formulei, aceasta este introdusă în trei celule din coloana C (C1:C3).

Formula „=TREND(B1:B3;A1:A3)”, introdusă ca formulă matrice, returnează trei valori (22.196, 17.079 și 11.962) calculate din trei volume de vânzări pe parcursul a trei luni.

Utilizarea constantelor de matrice

Într-o formulă obișnuită, puteți introduce o referință la o celulă cu o valoare sau la valoarea în sine, numită și constantă. În mod similar, într-o formulă de matrice, puteți introduce o referință la o matrice sau o matrice de valori conținute în celule (uneori numită o constantă de matrice). Formulele matrice acceptă constante la fel ca alte formule, dar constantele matrice trebuie introduse într-un format specific.

Constantele de matrice pot conține numere, text, valori booleene, cum ar fi TRUE sau FALSE, sau valori de eroare, cum ar fi „#N/A”. O constantă de matrice poate conține valori de diferite tipuri, de exemplu (1,3,4;TRUE,FALSE,TRUE). Numerele din constantele matricei pot fi numere întregi, zecimale sau în format exponențial. Textul trebuie să fie cuprins între ghilimele duble, de exemplu „marți”.

Când formatați constantele matricei, asigurați-vă că sunt îndeplinite următoarele cerințe:

Constantele sunt incluse în acolade ( { } ).

Coloane separate prin virgule ( , ). De exemplu, pentru a reprezenta valorile 10, 20, 30 și 40, introduceți (10,20,30,40). Această constantă de matrice este o matrice de 1 pe 4 și corespunde unei referințe cu un rând și patru coloane.

Valorile celulelor de pe diferite rânduri sunt separate prin punct și virgulă ( ; ). De exemplu, pentru a reprezenta valorile 10, 20, 30, 40 și 50, 60, 70, 80 în celule stivuite, puteți crea o constantă de matrice de 2 pe 4: (10,20,30,40,50 , 60,70,80).

Formule

Calcule în tabelele de programe excela efectuat folosind formule O formulă poate conține constante numerice, referințe de celule și Funcții Excel legate prin simboluri ale operațiilor matematice. Parantezele vă permit să schimbați ordinea standard a acțiunilor. Dacă o celulă conține o formulă, foaia de lucru afișează rezultatul curent al acelei formule. Dacă faceți o celulă curentă, formula în sine este afișată în bara de formule.

Reguli de utilizare a formulelor într-un program excela este că dacă valoarea celulei într-adevăr depinde de alte celule de tabel, Mereu trebuie folosită o formulă chiar dacă operația poate fi efectuată cu ușurință în minte. Acest lucru asigură că editarea ulterioară a tabelului nu va încălca integritatea acestuia și corectitudinea calculelor efectuate în acesta.

Formula poate conține link-uri, adică adresele celulelor al căror conținut este folosit în calcule. Aceasta înseamnă că rezultatul formulei depinde de numărul din altă celulă. Celula care conține formula este așadar dependent. Valoarea afișată într-o celulă de formulă este recalculată atunci când valoarea celulei la care se face referire se modifică.

O referință de celulă poate fi specificată în moduri diferite. În primul rând, adresa celulei poate fi introdusă manual. O altă modalitate este să faceți clic pe celula dorită sau să selectați intervalul a cărui adresă doriți să o introduceți. Celula sau intervalul este evidențiat cu un cadru punctat.

Toate casetele de dialog ale programului excela, care necesită specificarea numerelor de celule sau a intervalelor conțin butoane atașate câmpurilor corespunzătoare. Când faceți clic pe un astfel de buton, caseta de dialog este minimizată la cea mai mică dimensiune posibilă, ceea ce face mai ușor să selectați celula dorită (interval) făcând clic sau trăgând (Fig. 5.3).

Orez. 5.3. Caseta de dialog extinsă și restrânsă

Pentru a edita o formulă, faceți dublu clic pe celula corespunzătoare. În acest caz, celulele (intervalele) de care depinde valoarea formulei sunt evidențiate pe foaia de lucru cu cadre colorate, iar legăturile în sine sunt afișate în celulă și în bara de formule în aceeași culoare. Acest lucru facilitează editarea și verificarea corectitudinii formulelor.

În mod implicit, referințele de celule din formule sunt tratate ca relativ. Aceasta înseamnă că atunci când copiați o formulă, adresele din referințe se schimbă automat în funcție de locația relativă a celulei originale și a copiei care este creată.

De exemplu, să presupunem că celula B2 conține o legătură către celula A3. În vedere relativă, putem spune că referința indică o celulă care este o coloană la stânga și un rând sub cea dată. Dacă formula este copiată într-o altă celulă, atunci această indicație de referință relativă va rămâne. De exemplu, atunci când copiați o formulă în celula EA27, linkul va continua să indice celula din stânga și dedesubt, în acest caz, celula DZ28.

La adresare absolută Adresele link-urilor nu se schimbă atunci când sunt copiate, astfel încât celula către care indică legătura este tratată ca non-tabulară. Pentru a schimba metoda de adresare atunci când editați o formulă, selectați referința de celulă și apăsați F4. Elementele de număr de celule care utilizează adresarea absolută sunt precedate de un caracter $. De exemplu, când apăsați succesiv tasta F4, numărul celulei A1 va fi scris ca A1, $A$ 1, A$ 1 și $A1. În ultimele două cazuri, una dintre componentele numărului de celule este tratată ca absolută, iar cealaltă ca relativă.

Copierea conținutului celulei

Copierea și mutarea celulelor într-un program excela se poate face folosind metoda drag-and-drop sau prin clipboard. Când lucrați cu un număr mic de celule, este convenabil să utilizați prima metodă; atunci când lucrați cu intervale mari, este convenabil să utilizați a doua.

Metoda de glisare și plasare. Pentru a copia sau muta celula curentă (intervalul selectat) împreună cu conținutul acesteia folosind glisare și plasare, deplasați cursorul mouse-ului peste cadrul celulei curente (va lua forma unei săgeți cu săgeți suplimentare). Acum, celula poate fi trasă oriunde în foaia de lucru (punctul de inserare este marcat cu un sfat instrument).

Pentru a selecta metoda de efectuare a acestei operațiuni, precum și pentru un control mai fiabil asupra acesteia, se recomandă utilizarea tragere specială folosind butonul drept al mouse-ului. În acest caz, când eliberați butonul mouse-ului, apare un meniu special în care puteți selecta operația specifică care trebuie efectuată.

Folosind clipboard-ul. Transferarea informațiilor prin clipboard are în program excela anumite caracteristici asociate cu complexitatea controlului asupra acestei operațiuni. Mai întâi, trebuie să selectați intervalul de copiat (tăiat) și să dați comanda pentru a o plasa în clipboard: Editare copiere sau Editare tăiere. Lipirea datelor într-o foaie de lucru este posibilă numai imediat după ce acestea sunt plasate în clipboard. Atentat, încercare efectuarea oricărei alte operații anulează procesul de copiere sau mutare început. Cu toate acestea, nu există nicio pierdere de date, deoarece datele tăiate sunt eliminate din locația inițială numai atunci când are loc inserarea.

Locația de inserare este determinată prin selectarea unei celule care corespunde colțului din stânga sus al intervalului plasat pe clipboard, sau prin selectarea unui interval care are exact aceeași dimensiune cu cel copiat (mutat). Inserarea se realizează folosind comanda Edit Paste. Puteți folosi comanda Edit Paste Special pentru a controla modul în care lipiți. În acest caz, regulile de inserare a datelor din clipboard sunt setate în caseta de dialog care se deschide.

Automatizarea intrărilor

Deoarece tabelele conțin adesea date duplicat sau similare, programul excela conține instrumente de automatizare a intrărilor. Fondurile oferite includ: completare automată,completare automată numereȘi completare automatăformule.

Completare automată. Pentru a automatiza introducerea datelor text, se folosește metoda auto-completare. Este folosit atunci când introduceți șiruri de text, dintre care unele sunt repetate, în celulele unei coloane a unei foi de lucru. În timp ce introduceți date text în celula următoare, programul excela verifică dacă caracterele introduse se potrivesc cu șirurile din această coloană de mai sus. Dacă se găsește o potrivire clară, textul introdus este completat automat. Apăsarea tastei ENTER confirmă operația de autocompletare, în caz contrar poți continua introducerea fără să fii atent la opțiunea propusă.

Puteți întrerupe completarea automată lăsând o celulă goală într-o coloană. În schimb, pentru a utiliza funcțiile de completare automată, celulele umplute trebuie să fie consecutive, fără spații între ele.

Completare automată cu numere. Când se lucrează cu numere, se folosește metoda completare automată. Există un pătrat negru în colțul din dreapta jos al cadrului actual al celulei - marker de umplere. Când treceți cu mouse-ul peste el, indicatorul mouse-ului (de obicei arată ca o cruce albă groasă) ia forma unei cruci subțiri negre. Tragerea unui mâner de umplere este considerată o operație de „multiplicare” a conținutului unei celule fie orizontal, fie vertical.

Dacă o celulă conține un număr (inclusiv o dată, o sumă monetară), atunci tragerea marcatorului copiază celulele sau le umple cu o progresie aritmetică. Pentru a selecta metoda de completare automată, trebuie să efectuați o glisare și plasare specială folosind butonul din dreapta al mouse-ului.

De exemplu, să presupunem că celula A1 conține numărul 1. Treceți mouse-ul peste mânerul de umplere, faceți clic dreapta și trageți mânerul de umplere astfel încât cadrul să acopere celulele A1, B1 și C1 și eliberați butonul mouse-ului. Dacă acum selectați Copy Cells în meniul care se deschide, toate celulele vor conține numărul 1. Dacă selectați Fill, celulele vor conține numerele 1, 2 și 3.

Pentru a formula cu precizie condițiile de umplere a celulelor, ar trebui să dați comanda Edit Fill Progression. În caseta de dialog Progresie care se deschide, selectați tipul de progresie, dimensiunea pasului și valoarea limită. După ce faceți clic pe butonul OK, programul excela umple automat celulele în conformitate cu 1 reguli specificate.

Formule de completare automată. Această operație este efectuată în același mod ca și numerele de completare automată. Particularitatea sa este necesitatea de a copia referințe la alte celule. În timpul autocompletării, se ia în considerare natura legăturilor din formulă: legăturile relative se modifică în funcție de locația relativă a copiei, iar legăturile originale, absolute, rămân neschimbate.

De exemplu, să presupunem că valorile din a treia coloană a foii de lucru (coloana C) sunt calculate ca sumă a valorilor din celulele corespunzătoare din coloanele A și B. Introduceți formula =A1+B1 în celula C1 . Acum să copiem această formulă folosind metoda de completare automată în toate celulele celei de-a treia coloane a tabelului. Adresarea relativă permite ca formula să fie corectă pentru toate celulele dintr-o coloană dată.

ÎN
Tabelul 5.1 prezintă regulile de actualizare a legăturilor la completarea automată de-a lungul unui rând sau de-a lungul unei coloane.

Tabelul 5.1. Reguli pentru actualizarea linkurilor în timpul autocompletare

Utilizarea caracteristicilor standard

În program sunt utilizate funcții standard excela numai în formule. Apelfuncții constă în indicarea în formulă numele funcției, după care se indică între paranteze lista de parametri. Parametrii individuali sunt separați în listă prin punct și virgulă. Parametrul poate fi un număr, o adresă de celulă sau o expresie arbitrară, care poate fi calculată și folosind funcții.

În modul de introducere a formulei, în partea stângă a barei de formule apare o listă derulantă de funcții, unde se afla obișnuit numărul de celule curente. Conține cele mai recente zece funcții utilizate, precum și Mai multe funcții.

Utilizarea Expertului de funcții. Când selectați Alte funcții, pornește Expertul pentru funcții, ușurând selectarea funcției dorite. În lista derulantă Categorie, selectați categoria căreia îi aparține funcția (dacă este dificil să determinați categoria, utilizați elementul Lista alfabetică completă), iar în lista Selectați o funcție - o funcție specifică a acestei categorii. După ce faceți clic pe butonul OK, numele funcției este introdus în bara de formule împreună cu parantezele care limitează lista de parametri. Cursorul de text este poziționat între aceste paranteze. Puteți apela mai ușor Expertul pentru funcții făcând clic pe butonul Inserare funcție din bara de formule.

A
argumente ale funcției. Odată ce este selectat un nume de funcție, pe ecran apare caseta de dialog Argumente ale funcției (în versiunile anterioare ExelAcest fereastra era văzută ca paleta de formule). Această fereastră, în special, conține valoarea care va fi obținută dacă terminați imediat introducerea formulei (Fig. 5.4).

Orez. 5.4. Bara de formule și caseta de dialog Argumente ale funcției

Regulile pentru calcularea formulelor care conțin funcții nu diferă de regulile pentru calcularea formulelor mai simple. Referințele de celule utilizate ca parametri ai funcției pot fi, de asemenea, relative sau absolute, ceea ce este luat în considerare la copierea formulelor folosind metoda AutoComplete.