Definirea și crearea unei formule

În această lecție, vă prezentăm regulile de bază pentru crearea formulelor și utilizarea funcțiilor. Ne simțim una dintre cele mai bune căi de a învăța prin practică, așa că oferim mai multe exemple și le explicăm în detaliu. Subiectele pe care le vom include includ:

ȘCOLAREA NAVIGAȚIEI

De ce aveți nevoie de formule și funcții?
Definirea și crearea unei formule
Referință celulară relativă și absolută și formatare
Funcții utile pe care ar trebui să le cunoașteți
Căutări, diagrame, statistici și tabele pivot

rânduri și coloane
exemplu matematică funcție: SUM ()
operatori
operator de prioritate
exemplu funcția financiară: PMT (), plata împrumutului
folosind o funcție de "șir" ("șir" este o stenogramă pentru "șirul de text") în interiorul unei funcții de formulare și cuibărire

Formulele sunt un amestec de "funcții", "operatori" și "operanzi". Înainte de a scrie câteva formule, trebuie să creăm o funcție, dar înainte de a putea crea o funcție, trebuie mai întâi să înțelegem notația rândului și coloanei.

Rânduri și coloane

Pentru a înțelege cum să scrieți formule și funcții, trebuie să știți despre rânduri și coloane.

Rândurile rulează pe orizontală, iar coloanele rulează vertical. Amintiți-vă care este care, gândiți-vă la o coloană care susține un acoperiș - coloanele merg sus în jos și astfel rândurile merg spre stânga-dreapta.

Coloanele sunt etichetate cu litere; rânduri prin numere. Prima celulă din foaia de calcul este A1 care înseamnă coloana A, rândul 1. Coloanele sunt etichetate A-Z. Când alfabetul se execută, Excel plasează o altă literă în față: AA, AB, AC ... AZ, BA, BC, BC etc..

Exemplu: Funcția Sumă ()

Acum, să arătăm cum să folosim o funcție.

Utilizați funcții introducându-le direct sau utilizând expertul funcțional. Expertul funcțional se deschide când alegeți o funcție din meniul "Formule" din "Biblioteca funcții". În caz contrar, puteți tasta = într-o celulă și un meniu derulant la îndemână vă va permite să alegeți o funcție.

Expertul vă spune ce argumente aveți nevoie pentru fiecare funcție. De asemenea, oferă o legătură cu instrucțiunile online dacă aveți nevoie de ajutor pentru a înțelege ce funcționează și cum se utilizează funcția. De exemplu, dacă tastați = sumă într-o celulă, expertul in-line vă arată ce argumente sunt necesare pentru funcția SUM.

Când tastați o funcție, expertul este în linie sau în dreapta la degete. Când selectați o funcție din meniul "Formule", expertul este o casetă pop-up. Iată expertul pop-up pentru funcția SUM ().

Pentru prima noastră funcție, să folosim SUM (), care adaugă o listă de numere.

Să presupunem că avem această foaie de calcul pentru a conține planuri pentru bugetarea vacanței familiei:

Pentru a calcula costurile totale puteți scrie = b2 + b3 + b4 + b5 dar este mai ușor să utilizați funcția SUM ().

În Excel, căutați simbolul Σ în colțul din stânga sus al ecranului Excel pentru a găsi butonul AutoSum (matematicienii folosesc litera greacă Σ pentru a adăuga o serie de numere).

Dacă cursorul este sub numerele bugetului familiei, Excel este suficient de inteligent pentru a ști că doriți să însumați lista numerelor de mai sus, unde ați plasat cursorul, astfel încât să evidențieze numerele.

Apăsați "enter" pentru a accepta intervalul selectat de Excel sau utilizați cursorul pentru a modifica ce celule sunt selectate.

Dacă te uiți la ce a pus Excel în foaia de calcul puteți vedea că a scris această funcție:

În această formulă, Excel însumează numerele de la B2 la B9. Rețineți că am lăsat o cameră sub rândul 5, astfel încât să puteți adăuga bugetul pentru vacanța de familie - costul va crește cu siguranță, deoarece lista copiilor a ceea ce vor să facă și unde vor să meargă crește!

Funcțiile matematice nu funcționează cu litere, așadar dacă dați litere în coloană, rezultatul este afișat ca "#NAME?" După cum se arată mai jos.

#NUME? indică faptul că există un fel de eroare. Ar putea fi orice număr de lucruri, inclusiv:

raportul celular rău
folosind litere în funcțiile de matematică
omiterea argumentelor necesare
numele funcției de scriere greșită
operațiuni matematice ilegale cum ar fi diviziunea cu 0

Cea mai ușoară modalitate de a selecta argumentele într-un calcul este utilizarea mouse-ului. Puteți adăuga sau elimina din lista de argumente a funcției prin mărirea sau micșorarea casetei pe care o desenește Excel atunci când mutați mouse-ul sau faceți clic pe o altă celulă.

Am făcut clic pe partea superioară a pătratului tras de către Excel pentru a lua "bilete de avion" din buget. Puteți vedea simbolul părului pe care îl puteți desena pentru a face gama selectată mai mare sau mai mică.

Apăsați "enter" pentru a confirma rezultatele.

Operatori de calcul

Există două tipuri de operatori: matematică și comparație.

Operatorul de matematică	Definiție
+	plus
-	scăderea sau negarea, de exemplu, 6 * -1 = -6
*	multiplicare
/	diviziune
%	la sută
^	exponent, de ex. 2⁴= 2 ^ 4 = 2 * 2 * 2 * 2 = 16

Există și alți operatori care nu au legătură cu matematica, cum ar fi "&", ceea ce înseamnă concatenare (se alăture final-la-sfârșit) două șiruri de caractere. De exemplu, "Excel" și "Fun" este egal cu "Excel is Fun".

Acum ne uităm la operatorii de comparare.

Operatorul de comparare	Definiție
=	egală, de exemplu, 2 = 4 sau "b" = "b"
>	mai mare decât, de exemplu, 4> 2 sau "b"> "a"
<	mai puțin decât, de exemplu, 2 < 4 or “a” < “b”
> =	mai mare sau egal cu - un alt mod de a gândi acest lucru este> = înseamnă fie > sau =.
<=	mai mic sau egal cu.
	nu egal cu, de exemplu, 46

După cum puteți vedea mai sus, operatorii de comparare lucrează cu numere și text.

Notați, dacă introduceți = "a"> "b" într-o celulă se va spune "FALSE" deoarece "a" nu este mai mare decât "b." "B" > "B" sau "B"> "a."

Precedentul comenzii operatorului

Ordinea precedentă este o idee din matematică. Excel trebuie să urmeze aceleași reguli ca și matematica. Acest subiect este mai complicat, deci respirați și să ne aruncăm în apă.

Ordinea precedentă înseamnă ordinea în care calculatorul calculează răspunsul. Așa cum am explicat în Lecția 1, aria unui cerc este πr², care este același cu π * r * r. Este nu (Πr)².

Deci, trebuie să înțelegeți ordinea precedentă când scrieți o formulă.

În general, puteți spune acest lucru:

Excel evaluează mai întâi articolele din paranteze care lucrează în interior.
Apoi utilizează regulile de prioritate a ordinii matematice.
Când două elemente au aceeași prioritate, Excel lucrează din stânga la dreapta.

Prioritatea operatorilor de matematică este prezentată mai jos, în ordine descrescătoare.

( și )	Atunci când se utilizează paranteze, ele depășesc regulile normale de prioritate. Aceasta înseamnă că Excel va face primul calcul. Vă explicăm mai jos.
-	Negarea, de exemplu, -1. Acest lucru este același cu înmulțirea unui număr cu -1. -4 = 4 * (-1)
%	Procentul înseamnă înmulțirea cu 100. De exemplu, 0,003 = 0,3%.
^	Exponent, de exemplu, 10 ^ 2 = 100
* și /	Multiplicați și împărțiți. Cum pot doi operatori să aibă aceeași prioritate? Pur și simplu înseamnă că, dacă o formulă are încă doi operatori cu aceeași prioritate, atunci calculul se face din stânga la dreapta.
+ și -	Adunare si scadere.

Există și alte reguli de prioritate legate de șiruri de caractere și operatori de referință. Pentru moment, vom rămâne doar cu ceea ce tocmai am acoperit. Acum, să vedem câteva exemple.

Exemplu: Calculul zonei unui cerc

Zona unui cerc este= PI () * raza ^ 2.

Privind la tabelul de mai sus vedem că exponenții vin înainte de multiplicare. Deci calculatorul calculează mai întâi raza ^ 2 și apoi multiplică rezultatul lui Pi.

Exemplu: Calculul creșterii salariului

Să presupunem că șeful tău decide că faci o treabă bună și el sau ea îți va da o creștere de 10%! Cum ți-ai calcula salariul nou?

Mai întâi, amintiți-vă că multiplicarea vine înainte de adăugare.

Este = salariu + salariu * 10% sau este = salariu + (salariu * 10%)?

Să presupunem că salariul tău este de 100 $. Cu o majorare de 10%, salariul dvs. nou va fi:

= 100 + 100 * 10% = 100 + 10 = 110

De asemenea, puteți scrie astfel:

= 100 + (100 * 10%) = 100 + 10 = 110

În cel de-al doilea caz, am făcut ca ordinea de prioritate să fie explicită prin utilizarea parantezelor. Amintiți-vă că parantezele sunt evaluate înainte de orice altă operație.

Apropo, modul mai simplu de a scrie acest lucru este = salariu * 110%

Parantezele pot fi imbricate unul în celălalt. Atunci când scriem (3 + (4 * 2)), lucrăm din interior spre exterior, mai întâi calculează 4 * 2 = 8, apoi se adaugă 3 + 8 pentru a obține 11.

Mai multe exemple

Iată un alt exemplu: = 4 * 3 / 2. Care este răspunsul?

Din regulile din tabelul de mai sus vedem că * și / au precedență egală. Deci, Excel funcționează de la stânga la dreapta, 4 * 3 = 12 în primul rând, apoi împarte că cu 2 pentru a obține 6.

Din nou, puteți face acest lucru explicit scriind = (4 * 3) / 2

Ce zici = 4 + 3 * 2?

Computerul vede atât operatorii *, cât și operatorii +. Deci, urmând regulile de prioritate (multiplicarea vine înainte de adăugare), ea calculează mai întâi 3 * 2 = 6, apoi adaugă 4 pentru a obține 10.

Dacă doriți să schimbați ordinea precedenței, ați scrie = (4 + 3) * 2 = 14.

Ce zici de asta = -1 ^ 3?

Apoi răspunsul este -3 deoarece calculatorul calculat = (-1) ^ 3 = -1 * -1 * -1 = -1.

Amintiți-vă că timpii negativi negativi sunt pozitivi, iar pozițiile negative negative sunt pozitive. Puteți vedea acest lucru astfel (-1 * -1) * -1 = 1 * -1 = -1.

Deci, există câteva exemple de ordine și precedență matematică, sperăm că vă ajută să clarificați câteva lucruri despre modul în care Excel efectuează calcule (și probabil că este suficient de multă matematică pentru a dura o viață pentru unii dintre voi).

Exemplu: Plata creditului funcțional (PMT)

Să examinăm un exemplu pentru a calcula o plată a împrumutului.

Începeți prin crearea unei foi de lucru noi.

Formați numerele cu semne de dolar și folosiți zecimale zero, deoarece nu ne interesează centii chiar acum, deoarece nu contează prea mult când vorbiți despre dolari (în capitolul următor vom explora modul de formare a cifrelor în detaliu). De exemplu, pentru a formata rata dobânzii, faceți clic dreapta pe celulă și faceți clic pe "formate celule". Pick procent și utilizați 2 zecimale.

În mod similar, formatați celelalte celule pentru "monedă" în loc de procent și alegeți "număr" pentru termenul de împrumut.

Acum avem:

Adăugați funcția SUM () la "total" cheltuieli lunare.

Notă: credit ipotecar celula nu este inclusă în total. Excel nu știe că doriți să includeți numărul respectiv, deoarece nu există nicio valoare acolo. Așadar, aveți grijă să extindeți funcția SUM () în partea superioară fie cu ajutorul cursorului, fie tastând E2 unde scrie E3 pentru a include ipoteca în sumă.

Puneți cursorul în celula de plată (B4).

În meniul Formule, selectați meniul derulant "Financiar" și apoi selectați funcția PMT. Vrăjitorul apare:

Utilizați cursorul pentru a selecta "rata", "nper" (termenul de împrumut), "Pv" ("valoarea actuală" sau suma creditului). Observați că trebuie să împărțiți rata dobânzii cu 12, deoarece dobânda este calculată lunar. De asemenea, trebuie să multiplicați termenul de împrumut în ani cu 12 pentru a obține termenul de împrumut în luni. Apăsați "OK" pentru a salva rezultatul în foaia de calcul.

Observați că plata este afișată ca număr negativ: -1013.37062. Pentru a face pozitiv și a adăuga-o la cheltuielile lunare, indicați celula ipotecară (E2). Introduceți "= -" apoi utilizați cursorul pentru a indica câmpul de plată. Formula rezultată este = -B4.

Acum, foaia de calcul arată astfel:

Cheltuielile dvs. lunare sunt de 1.863 $ - Ouch!

Exemplu: Funcția text

Aici vom demonstra cum să folosim funcții în interiorul unei funcții de formulare și text.

Să presupunem că aveți o listă de elevi așa cum este arătat mai jos. Numele și prenumele sunt într-un câmp separat printr-o virgulă. Trebuie să punem ultimele nume ferme în celule separate. Cum facem asta??

Pentru a rezolva această problemă trebuie să utilizați un algoritm - adică o procedură pas cu pas pentru a face acest lucru.

De exemplu, uitați-vă la "Washington, George". Procedura de împărțire în două cuvinte ar fi:

Calculați lungimea șirului.
Aflați poziția virgulei (aceasta arată unde se termină un cuvânt, iar celălalt începe).
Copiați partea stângă a șirului până la virgulă.
Copiați partea dreaptă a șirului de la virgulă la sfârșit.

Să discutăm cum să faceți acest lucru cu "George Washington" pas cu pas în Excel.

Calculați lungimea șirului cu funcția = LEN (A3) - rezultatul este 18.
Acum găsiți poziția virgulei introducând această funcție = FIND (",", A3 ") - rezultatul este 11.
Acum, luați partea stângă a șirului până la virgulă și creați această formulă imbricată utilizând rezultatul de la Pasul 1: = LEFT (A3, FIND (",", A3) -1). Notați, trebuie să scăpăm 1 din lungime, deoarece FIND dă poziția virgulei.

Iată ce arată toate când toate funcțiile sunt plasate împreună într-o formulă. În celula B3, puteți vedea că această formulă preia toate informațiile din celula A3 și introduce "Washington" în ea.

Așa că avem "Washington", acum avem nevoie de "George". Cum facem asta??

Rețineți că am fi putut salva rezultatul de la Pasul 1 într-o celulă în sine, să spunem, B6, apoi să scrie o formulă mai simplă = STÂNGA (A3, B6-1). Dar asta folosește o celulă pentru pasul intermitent.

Amintiți-vă poziția virgulei sau calculați-o din nou.
Calculați lungimea șirului.
Contorizați caracterele de la sfârșitul șirului la virgulă.

Luați numărul de caractere de la Pasul 3 și scădeți unul pentru a omite virgulă și spațiu.

Să facem acest lucru pas cu pas.

De mai sus, aceasta este = FIND (",", A3 ")
Lungimea șirului este = LEN (A3)
Va trebui sa folositi cateva matematici pentru a gasi numarul de caractere care trebuie luate: = LEN (A3) - FIND (",", A3) - 1
Partea dreaptă a șirului dorit este = DREAPTA (A3, LEN (A3) - Găsiți (",", A3) - 1)

Foaia dvs. de calcul ar trebui să pară asemănătoare cu ecranul de mai jos. Am copiat formulele ca text în partea de jos a foii de calcul pentru a le face mai ușor de citit și de văzut.

Asta a fost un pic dificil, dar trebuie doar să scrieți aceste formule o dată.

Urmeaza…

Aceasta ne încheie lecția pentru ziua de azi. Ar trebui să aveți o înțelegere destul de fermă a formulelor și funcțiilor, a rândurilor și coloanelor și a modului în care toate acestea pot fi folosite prin câteva exemple concrete.

Urmează în continuare în Lecția 3, vom discuta despre referințele și formatarea celulelor, precum și formulele de mișcare și copiere, astfel încât să nu fie nevoie să rescrieți fiecare formulă de mai multe ori!