Dacă sunteți începător în Excel pentru web, veți descoperi în curând că este mai mult decât o grilă în care introduceți numere în coloane sau rânduri. Da, puteți utiliza Excel pentru web pentru a găsi totalurile pentru o coloană sau un rând de numere, dar puteți, de asemenea, să calculați ratele unui împrumut, să rezolvați probleme de matematică sau de inginerie sau să găsiți un scenariu cu cel mai bun caz pe baza diverselor numere pe care le conectați.
Excel pentru web face acest lucru utilizând formule în celule. O formulă efectuează calcule sau alte acțiuni cu datele din foaia de lucru. O formulă începe întotdeauna cu semnul egal (=), care poate fi urmat de numere, operatori matematici (cum ar fi un semn plus sau minus) și funcții, care pot extinde cu adevărat puterea unei formule.
De exemplu, următoarea formulă înmulțește 2 cu 3, apoi adună 5 la rezultat pentru a ajunge la răspuns, 11.
=2*3+5
Următoarea formulă utilizează funcția PMT pentru a calcula o rată a unui credit ipotecat (1073,64 lei), care se bazează pe o rată a dobânzii de 5 procente (5% împărțit la 12 luni este egal cu rata lunară a dobânzii) pe o perioadă de 30 de ani (360 de luni) pentru un împrumut de 200.000 lei:
=PMT(0,05/12;360;200000)
Iată câteva exemple suplimentare de formule care se pot introduce într-o foaie de lucru.
-
=A1+A2+A3 Adună valorile din celulele A1, A2 și A3.
-
=SQRT(A1) Utilizează funcția SQRT pentru a returna rădăcina pătrată a valorii din A1.
-
=TODAY() Returnează data curentă.
-
=UPPER("salut") Efectuează conversia textului „salut” în „SALUT” utilizând funcția de foaie de lucru UPPER.
-
=IF(A1>0) Testează celula A1 pentru a determina dacă aceasta conține o valoare mai mare ca 0.
Părțile unei formule
O formulă mai poate conține una sau toate dintre următoarele: funcții, referințe, operatori și constante.
1. Funcții: funcția PI() returnează valoarea lui pi: 3,142...
2. Referințe: A2 returnează valoarea din celula A2.
3. Constante: numere sau valori text introduse direct într-o formulă, cum ar fi 2.
4. Operatori: operatorul ^ (accent circumflex) ridică un număr la putere, iar operatorul * (asterisc) înmulțește numere.
Utilizarea constantelor în formule
O constantă este o valoare care nu este calculată;aceasta rămâne întotdeauna la fel. De exemplu, data 09.10.2008, numărul 210 și textul „Câștiguri trimestriale” sunt constante. Un expresie sau o valoare care rezultă dintr-o expresie nu este o constantă. Dacă utilizați constante într-o formulă în loc de referințe la celule (de exemplu, =30+70+110), rezultatul se modifică doar dacă modificați formula.
Utilizarea operatorilor de calcul în formule
Operatorii specifică tipul de calcul pe care doriți să îl efectuați cu elementele din formulă. Există o ordine implicită în care se produc calculele (urmează reguli matematice generale), dar ea poate fi modificată utilizând paranteze.
Tipuri de operatori
Există patru tipuri diferite de operatori de calcul: aritmetici, de comparare, de concatenare a textului și de referință.
Operatori aritmetici
Pentru a efectua operații matematice de bază, cum sunt adunarea, scăderea, înmulțirea sau împărțirea, combinări de numere sau producerea de rezultate numerice, utilizați următorii operatori aritmetici.
|
Operator aritmetic |
Semnificație |
Exemplu |
|
+ (semnul plus) |
Adunare |
3+3 |
|
– (semnul minus) |
Scădere |
Între 3 și 1 |
|
* (asterisc) |
Înmulțire |
3*3 |
|
/ (linie oblică) |
Împărțire |
3/3 |
|
% (semnul procent) |
Procent |
20% |
|
^ (accent circumflex) |
Ridicare la putere |
3^2 |
Operatori de comparare
Aveți posibilitatea să comparați două valori cu operatori următorii. Când două valori sunt comparate utilizând acești operatori, rezultatul este o valoare logică, fie TRUE, fie FALSE.
|
Operator de comparare |
Semnificație |
Exemplu |
|
= (semn egal) |
Egal cu |
A1=B1 |
|
> (semnul mai mare) |
Mai mare decât |
A1>B1 |
|
< (semnul mai mic) |
Mai mic decât |
A1<B1 |
|
>= (semnul mai mare sau egal) |
Mai mare sau egal cu |
A1>=B1 |
|
<= (semnul mai mic sau egal) |
Mai mic sau egal cu |
A1<=B1 |
|
<> (semnul nu este egal cu) |
Nu este egal cu |
A1<>B1 |
Operator de concatenare text
Utilizați caracterul ampersand (&) pentru a uni sau a concatena unul sau mai multe șiruri de text pentru a realiza un singur text.
|
Operator text |
Semnificație |
Exemplu |
|
& (ampersand) |
Conectează sau concatenează două valori pentru a produce o valoare de text continuă |
"North"&"wind" are ca rezultat "Northwind" |
Operatori de referință
Combină zone de celule pentru calcule cu următorii operatori.
|
Operator de referință |
Semnificație |
Exemplu |
|
: (două puncte) |
Operator de zonă care produce o referință către toate celulele dintre două referințe, inclusiv către respectivele referințe. |
B5:B15 |
|
. (punct) |
Operator de uniune care combină referințele multiple într-o singură referință |
SUM(B5:B15;D5:D15) |
|
(spațiu) |
Operator de intersecție, care produce o referință la celulele comune celor două referințe |
B7:D7 C6:C8 |
Ordinea în care Excel pentru web efectuează operații în formule
În anumite cazuri, ordinea în care se execută calculul poate afecta rezultatul unei formule, așadar este important să înțelegeți cum este stabilită ordinea și cum aveți posibilitatea să modificați ordinea pentru a obține rezultatele dorite.
Ordinea operațiilor
Formulele calculează valorile într-o anumită ordine. O formulă începe întotdeauna cu un semn egal (=). Excel pentru web interpretează caracterele care urmează după semnul egal ca pe o formulă. După semnul egal urmează elementele de calculat (operanzii), cum ar fi constantele sau referințele la celule. Acestea sunt separate de operatorii de calcul. Excel pentru web calculează formula de la stânga la dreapta, conform unei ordini specifice pentru fiecare operator din formulă.
Prioritatea operatorilor
În cazul în care combinați mai mulți operatori într-o singură formulă, Excel pentru web efectuează operațiunile în ordinea afișată în tabelul următor. Dacă o formulă conține operatori cu aceeași prioritate, de exemplu, dacă o formulă conține atât operatorul de înmulțire, cât și operatorul de împărțire, Excel pentru web evaluează operatorii de la stânga la dreapta.
|
Operator |
Descriere |
|
: (două puncte) (un spațiu) , (virgulă) |
Operatori de referință |
|
– |
Negație (ca în –1) |
|
% |
Procent |
|
^ |
Ridicare la putere |
|
* și / |
Înmulțire și împărțire |
|
+ și – |
Adunare și scădere |
|
& |
Unește două șiruri de text (concatenare) |
|
= |
Comparare |
Utilizarea parantezelor
Pentru a modifica ordinea evaluării, încadrați în paranteze partea formulei care doriți să fie calculată prima. De exemplu, următoarea formulă produce 11, deoarece Excel pentru web efectuează înmulțirea înaintea adunării. Formula înmulțește 2 cu 3 și adună 5 la rezultat.
=5+2*3
În schimb, dacă utilizați paranteze pentru a modifica sintaxa, Excel pentru web adună 5 și 2, apoi înmulțește rezultatul cu 3 pentru a produce 21.
=(5+2)*3
În exemplul următor, parantezele care încadrează prima parte a formulei impun Excel pentru web să calculeze întâi B4+25, apoi să împartă rezultatul la suma valorilor din celulele 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 utilizând anumite valori, numite argumente, într-o anumită ordine sau structură. Funcțiile pot fi utilizate pentru efectuarea unor calcule simple sau complexe.
Sintaxa funcțiilor
Următorul exemplu al funcției ROUND, care rotunjește un număr din celula A10, ilustrează sintaxa unei funcții.
1. Structura. Structura unei funcții începe cu semnul egal (=), urmat de numele funcției, o paranteză deschisă, argumentele funcției separate prin virgulă și o paranteză de închidere.
2. Numele funcției. Pentru o listă de funcții disponibile, faceți clic pe o celulă, apoi apăsați SHIFT+F3.
3. Argumente. Argumentele pot fi numere, text, valori logice ca TRUE sau FALSE, matrice, valori de eroare ca #N/A sau referințe la celule. Argumentul precizat trebuie să producă o valoare corectă pentru acel argument. Argumentele pot fi și constante, formule sau alte funcții.
4. Sfat ecran pentru argumente. La tastarea funcției apare un sfat ecran al sintaxei și al argumentelor. De exemplu, când tastați =ROUND( apare sfatul ecran al funcției. Sfaturile ecran apar numai pentru funcțiile predefinite.
Introducerea funcțiilor
La crearea unei formule care conține o funcție, puteți să utilizați caseta de dialog Inserare funcție pentru a introduce funcții în foaia de lucru. Pe măsură ce se introduce o funcție în formulă, caseta de dialog Inserare funcție afișează numele funcției, fiecare argument al acesteia, o descriere a funcției și a fiecărui argument, rezultatul curent al funcției și rezultatul curent al întregii formule.
Pentru a simplifica crearea și editarea formulelor și pentru a reduce la minimum erorile de tastare și de sintaxă, utilizați caracteristica de Completare automată formulă. După ce tastați o literă = (semnul egal) și literele de început sau un declanșator de afișare, Excel pentru web afișează sub celulă o listă verticală dinamică de funcții valide, argumente și nume care se potrivesc cu literele sau cu declanșatorul. Apoi puteți insera în formulă un element din lista verticală.
Funcții imbricate
În anumite cazuri, poate fi necesar să utilizați o funcție drept unul dintre argumentele altei funcții. De exemplu, formula următoare utilizează o funcție AVERAGE imbricată și compară rezultatul cu valoarea 50.
1. Funcțiile AVERAGE și SUM sunt imbricate în funcția IF.
Returnări valide Atunci când o funcție imbricată este utilizată ca argument, funcția imbricată trebuie să returneze același tip de valoare utilizat de argument. De exemplu, dacă argumentul returnează o valoare TRUE sau FALSE, funcția imbricată trebuie să returneze o valoare TRUE sau FALSE. În caz contrar, Excel pentru web afișează o #VALUE! .
Limitele nivelului de imbricare O formulă poate conține până la șapte niveluri de funcții imbricate. Atunci când o funcție (o vom denumi Funcția B) este utilizată ca un argument în altă funcție (o vom denumi Funcția A), funcția B se comportă ca o funcție de nivelul al doilea. De exemplu, funcția AVERAGE și funcția SUM sunt ambele de nivelul al doilea dacă sunt argumente ale funcției IF. O funcție imbricată în interiorul funcției AVERAGE ar fi o funcție de nivelul al treilea etc.
Utilizarea referințelor în formule
O referință identifică o celulă sau o zonă de celule dintr-o foaie de lucru și îi spune Excel pentru web unde să caute valorile sau datele pe care doriți să le utilizați într-o formulă. Puteți să utilizați referințe pentru a folosi date conținute în diferite părți ale unei foi de lucru sau să utilizați valoarea dintr-o celulă în mai multe formule. De asemenea, puteți face referire la celule din alte foi ale aceluiași registru de lucru și la alte registre de lucru. Referințele la celule din alte registre de lucru se numesc legături sau referințe externe.
Stilul de referință A1
Stilul de referință implicit În mod implicit, Excel pentru web utilizează stilul de referință A1, care se referă la coloane cu litere (de la A la XFD, pentru un total de 16.384 de coloane) și se referă la rânduri cu 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 urmată de numărul rândului. De exemplu, B2 se referă la celula de la intersecția coloanei B cu rândul 2.
|
Pentru a face referire la |
Utilizați |
|
Celula din coloana A și rândul 10 |
A10 |
|
Zona de celule din coloana A și rândurile de la 10 până la 20 |
A10:A20 |
|
Zona de celule din rândul 15 și coloanele de la B la E |
B15:E15 |
|
Toate celulele din rândul 5 |
5:5 |
|
Toate celulele din rândurile de la 5 până la 10 |
5:10 |
|
Toate celulele din coloana H |
H:H |
|
Toate celulele din coloanele de la H la J |
H:J |
|
Zona de celule din coloanele de la A la E și din rândurile de la 10 la 20 |
A10:E20 |
Referința la o altă foaie de lucru În următorul exemplu, funcția AVERAGE din foaia de lucru calculează valoarea medie pentru zona B1:B10 de pe foaia de lucru numită Marketing, din același registru de lucru.
1. Se referă la foaia de lucru denumită Marketing
2. Se referă la zona de celule între B1 și B10, inclusiv
3. Separă referința la foaia de lucru de referința la zona de celule
Diferența dintre referințele absolute, referințele relative și referințele mixte
Referințe relative O referință relativă la o celulă dintr-o formulă, cum ar fi A1, se bazează pe poziția celulei care conține formula și pe celula la care se referă referința. Dacă se modifică poziția celulei care conține formula, se modifică și referința. Dacă veți copia sau veți umple formula pe mai multe rânduri sau coloane verticale, referința se reglează în mod automat. În mod implicit, formulele noi utilizează referințe relative. De exemplu, dacă veți copia sau umple o referință relativă în celulele B2 și B3, ea se reglează automat de la =A1 la =A2.
Referințe absolute O referință absolută la o celulă dintr-o formulă, cum ar fi $A$1, se referă întotdeauna la o celulă dintr-o anumită locație. Dacă se modifică poziția celulei care conține formula, referința absolută nu se modifică. Dacă veți copia sau umple formula pe mai multe rânduri sau coloane verticale, referința absolută nu se reglează. În mod implicit, formulele noi utilizează referințe relative, deci poate fi necesar să le comutați în referințe absolute. De exemplu, dacă veți copia sau umple o referință absolută în celulele B2 și B3, ea va rămâne neschimbată în ambele celule =$A$1.
Referințele mixte O referință mixtă are fie o coloană absolută și un rând relativ, fie un rând absolut și o coloană relativă. O referință de coloană absolută ia forma $A1, $B1 și așa mai departe. O referință de rând absolută ia forma $A1, $B1 și așa mai departe. În cazul în care se modifică poziția celulei care conține formula, referința relativă se modifică, iar referința absolută nu se modifică. Dacă veți copia sau umple formula pe mai multe rânduri sau coloane verticale, referința relativă se reglează automat, iar referința absolută nu se reglează. De exemplu, dacă veți copia sau umple o referință relativă din celula A2 în celula B3, aceasta se reglează de la =A$1 la =B$1.
Stilul de referință 3D
Faceți referire cu ușurință la mai multe foi de lucru Dacă doriți să analizați datele din aceeași celulă sau o zonă de celule din mai multe foi de lucru din registrul de lucru, utilizați o referință 3-D. O referință 3-D include referința la o celulă sau la o zonă de celule, precedată de o zonă de nume de foi de lucru. Excel pentru web utilizează toate foile de lucru stocate între numele de început și de sfârșit ale referinței. De exemplu, =SUM(Foaie2:Foaie13!B5) adaugă toate valorile conținute în celula B5 pe toate foile de lucru dintre Foaie 2 și Foaie 13, inclusiv.
-
Aveți posibilitatea să utilizați referințe 3-D pentru a face referire la celulele din alte foi, pentru a defini nume și pentru a crea formule utilizând următoarele funcții: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV.P, STDEV.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA și VARPA.
-
În formule matrice nu se pot utiliza referințe 3-D.
-
Referințele 3-D nu se pot utiliza cu operator intersecție (un spațiu) sau în formule care utilizează intersecție implicită.
Ce se întâmplă când mutați, copiați, inserați sau ștergeți foi de lucru Următoarele exemple arată ce se întâmplă când mutați, copiați, inserați sau ștergeți foi de lucru care sunt incluse în referințe 3-D. Exemplele utilizează formula =SUM(Foaie2:Foaie6!A2:A5) pentru a adăuga celulele de la A2 la A5 pe foile de lucru de la 2 la 6.
-
Inserare sau copiere Dacă inserați sau copiați foi între Foaie2 și Foaie6 (punctele finale din acest exemplu), Excel pentru web include toate valorile din celulele de la A2 la A5 din foile adăugate în calcule.
-
Ștergere Dacă ștergeți foile dintre Foaie2 și Foaie6, Excel pentru web elimină valorile lor din calcul.
-
Mutare Dacă mutați foile dintre Foaie2 și Foaie6 într-o locație aflată în afara intervalului de foi la care se face referire, Excel pentru web elimină valorile lor din calcul.
-
Mutarea unui punct final Dacă mutați Foaie2 sau Foaie6 în altă locație din același registru de lucru, Excel pentru web ajustează calculul pentru a include intervalul nou de foi de lucru dintre ele.
-
Ștergerea unui punct final Dacă ștergeți Foaie2 sau Foaie6, Excel pentru web ajustează calculul pentru a include intervalul de foi dintre ele.
Stilul de referință R1C1
De asemenea, puteți utiliza un stil de referință în care atât rândurile, cât și coloanele din foaia de lucru sunt numerotate. Stilul de referințe R1C1 este util pentru calculul poziționărilor rândurilor și coloanelor în macrocomenzi. În stilul R1C1, Excel pentru web indică locația unei celule cu un "R" urmat de un număr de rând și un "C" urmat de un număr de coloană.
|
Referință |
Semnificație |
|
R[-2]C |
O referință relativă la celula cu două rânduri mai sus în aceeași coloană |
|
R[2]C[2] |
O referință relativă la celula cu două rânduri mai jos și două coloane la dreapta |
|
R2C2 |
O referință absolută la celula din rândul al doilea și coloana a doua |
|
R[-1] |
O referință relativă la întregul rând de deasupra celulei active |
|
R |
O referință absolută la rândul curent |
Când înregistrați o macrocomandă, Excel pentru web înregistrează unele comenzi utilizând stilul de referință R1C1. De exemplu, dacă înregistrați o comandă, cum ar fi un clic pe butonul Însumare automată pentru a insera o formulă care adaugă o zonă de celule, Excel pentru web înregistrează formula utilizând referințe în stil R1C1, nu în stil A1.
Utilizarea numelor în formule
Puteți crea nume definite pentru a reprezenta celule, zone de celule, formule, constante sau tabele Excel pentru web. Numele este un instrument semnificativ care facilitează înțelegerea scopului unei referințe de celulă, a unei constante, a unei formule sau a unui tabel, fiecare dintre acestea fiind destul de dificil de înțeles dintr-o singură privire. Următoarele informații arată exemple obișnuite de nume și cum utilizarea lor poate îmbunătăți claritatea și înțelegerea.
|
Tip de exemplu |
Exemplu cu utilizarea zonelor în locul numelor |
Exemplu cu utilizarea numelor |
|
Referință |
=SUM(A16:A20) |
=SUM(Vânzări) |
|
Constantă |
=PRODUCT(A12;9,5%) |
=PRODUCT(Preț;Taxe) |
|
Formulă |
=TEXT(VLOOKUP(MAX(A16;A20);A16:B20;2;FALSE),"m/dd/yyyy") |
=TEXT(VLOOKUP(MAX(Vânzări);InfoVânzări;2;FALSE);"m/dd/yyyy") |
|
Tabel |
A22:B25 |
=PRODUCT(Preț;Tabel1[@Taxe]) |
Tipuri de nume
Există mai multe tipuri de nume pe care aveți posibilitatea să le creați și să le utilizați.
Nume definit Un nume care reprezintă o celulă, o zonă de celule, o formulă sau o valoare constantă. Puteți să vă creați propriul nume definit. De asemenea, Excel pentru web creează uneori un nume definit pentru dvs., cum ar fi atunci când setați o zonă de imprimare.
Nume tabel Un nume pentru un Excel pentru web tabel, care este o colecție de date despre un anumit subiect care sunt stocate în înregistrări (rânduri) și câmpuri (coloane). Excel pentru web creează un nume implicit de tabel Excel pentru web "Tabel1", "Tabel2" etc., de fiecare dată când inserați un tabel Excel pentru web, dar puteți modifica aceste nume pentru a le face mai semnificative.
Crearea și introducerea numelor
Creați un nume utilizând Creați un nume din selecție. Puteți crea cu ușurință nume din etichetele de coloană sau de rând existente, utilizând o selecție de celule din foaia de lucru.
Notă: În mod implicit, numele utilizează referințe absolute la celule.
Un nume se poate introduce:
-
Tastarea De exemplu, numele se tastează ca argument la o formulă.
-
Utilizând Completare automată formulă Utilizați lista verticală Completare automată formulă, în care se listează automat nume valide.
Utilizând formule matrice și constante matrice
Excel pentru web nu acceptă crearea formulelor matrice. Puteți vizualiza rezultatele formulelor matrice create în aplicația desktop Excel, dar nu puteți să le editați sau să le recalculați. Dacă aveți aplicația desktop Excel, faceți clic pe Deschidere în Excel pentru a lucra cu matricele.
Următoarea matrice calculează valoarea totală a unei matrice de acțiuni și cotații, fără a utiliza un rând de celule pentru a calcula și afișa valorile individuale pentru fiecare cotație.
Când introduceți formula ={SUM(B2:D2*B3:D3)} ca formulă matrice, aceasta înmulțește Acțiunile și Cotațiile, apoi însumează rezultatele pentru acele calcule.
Pentru a calcula mai multe rezultate Unele funcții ale foii de lucru returnează matrice de valori sau necesită o matrice de valori ca argument. Pentru a calcula mai multe rezultate cu o formulă matrice, trebuie să introduceți matricea într-o zonă de celule care are același număr de rânduri și coloane ca argumentele matricei.
De exemplu, dacă se dă o serie de trei cifre de vânzări (în coloana B) pentru o serie de trei luni (în coloana A), funcția TREND determină valorile în linie dreaptă pentru cifrele de vânzări. Pentru a afișa toate rezultatele formulei, introduceți matricea în trei celule din coloana C (C1:C3).
Când introduceți formula =TREND(B1:B3;A1:A3) ca formulă matrice, aceasta produce trei rezultate separate (22196, 17079 și 11962), bazate pe cele trei cifre de vânzări și pe cele trei luni.
Utilizarea constantelor matrice
Într-o formulă obișnuită, aveți posibilitatea să introduceți o referință la o celulă care conține o valoare sau valoarea în sine, denumită și constantă. În mod asemănător, într-o formulă matrice aveți posibilitatea să introduceți o referință la o matrice sau să introduceți o matrice de valori conținute în celulele, denumită și constantă matrice. Formulele matrice acceptă constante în același fel ca și formulele care nu sunt matrice, dar trebuie să introduceți constantele matrice într-un anumit format.
Constantele matrice pot conține numere, text, valori logice ca TRUE sau FALSE, sau valori de eroare ca #N/A. Tipuri diferite de valori se pot utiliza în aceeași constantă matrice , cum ar fi {1,3,4;TRUE,FALSE,TRUE}. Numerele dintr-o constantă matrice pot fi numere întregi, zecimale sau în format științific. Textul trebuie închis între ghilimele, cum ar fi „marți”.
Constantele matrice nu pot conține referințe la celule, coloane sau rânduri de lungimi inegale, formule sau caractere speciale $ (dolar), paranteze sau % (procent).
Când formatați constante matrice, asigurați-vă că:
-
Le includeți între acolade ( { } ).
-
Separați valorile din coloane diferite cu ajutorul virgulelor (,). De exemplu, pentru a reprezenta valorile 10, 20, 30 și 40, introduceți {10,20,30,40}. Această constantă matrice este cunoscută ca matrice 1-pe-4 și este echivalentă cu o referință 1-rând-pe-4-coloane.
-
Separați valorile pe rânduri diferite, prin punct și virgulă (;). De exemplu, pentru a reprezenta valorile 10, 20, 30 și 40 pe un rând și 50, 60, 70 și 80 pe rândul de dedesubt, introduceți o constantă matrice 2-pe-4: {10,20,30,40;50,60,70,80}.
