Definirea și crearea unei formule

Cuprins:

Video: Definirea și crearea unei formule

Video: Definirea și crearea unei formule
Video: iPhone 13 How to Turn off without power button 2024, Martie
Definirea și crearea unei formule
Definirea și crearea unei formule
Anonim
Î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ă, oferim câteva exemple și le explicăm în detaliu. Temele pe care le vom include includ:
Î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ă, oferim câteva exemple și le explicăm în detaliu. Temele pe care le vom include includ:

ȘCOALĂ NAVIGAȚIE

  1. De ce aveți nevoie de formule și funcții?
  2. Definirea și crearea unei formule
  3. Referință celulară absolută și absolută și formatare
  4. Funcții utile pe care ar trebui să le cunoașteți
  5. Căutări, diagrame, statistici și tabele pivot
  • rânduri și coloane
  • exemplu funcția matematică: SUM ()
  • operatori
  • operator de prioritate
  • exemplu funcția financiară: PMT (), plata împrumutului
  • folosind o funcție de "șir" ("șir" este o stenogramă pentru "șir 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ând și coloană.

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.
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 atunci 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ă funcția și cum să o utilizați. De exemplu, dacă tastați = sumă într-o celulă, expertul in-line vă arată ce argumente sunt necesare pentru funcția SUM.
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ă funcția și cum să o utilizați. 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 dreaptă sau 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 ().
Când tastați o funcție, expertul este în linie dreaptă sau 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.
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 ().
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 adăugarea unei serii de numere).

Dacă cursorul este sub numărul de buget al familiei, Excel este suficient de inteligent pentru a ști că doriți să însumați lista de numere de deasupra unde ați plasat cursorul, astfel încât să evidențieze numerele.
Dacă cursorul este sub numărul de buget al familiei, Excel este suficient de inteligent pentru a ști că doriți să însumați lista de numere de deasupra 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 schimba celulele selectate.
Apăsați "enter" pentru a accepta intervalul selectat de Excel sau utilizați cursorul pentru a schimba celulele 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 mai mult!
Î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 mai mult!

Funcțiile matematice nu funcționează cu litere, așa că 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:
#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 matematice
  • 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 desenată de către Excel pentru a scoate "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.
Apăsați "enter" pentru a confirma rezultatele.

Operatorii 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. 24 = 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 (join end-to-end) două șiruri de caractere. De exemplu, "Excel" și "Fun" este egal cu "Excel este distractiv".

Acum ne uităm la operatorii de comparare.

Comparator Operator 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 sau "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 egale cu, de exemplu, 4 <6

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

Rețineți că dacă introduceți "a"> "b" într-o celulă se va spune "FALSE" deoarece "a" nu este mai mare decât "b". B apare după litera "a" în alfabet, > "B" sau "B"> "a."

Precedenta comenzii operatorului

Ordinea precedentă este o idee din matematică. Excel trebuie să urmeze aceleași reguli ca și matematica. Acest subiect este mult 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 πr2, care este același cu π * r * r. Este nu (Πr)2.

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

În general, puteți spune acest lucru:

  1. Excel evaluează mai întâi articolele din paranteze care lucrează în interior.
  2. Apoi utilizează regulile precedente ale matematicii.
  3. 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. Aceasta este aceeași cu multiplicarea 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ă ne uităm la 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, rețineți 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

Puteți scrie, de asemenea, în felul următor:

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

În al doilea caz, am făcut explicit ordinea de prioritate folosind paranteze. Amintiți-vă că parantezele sunt evaluate înainte de orice altă operațiune.

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

Parantezele pot fi imbricate unul în celălalt. Deci, atunci când scriem (3 + (4 * 2)), lucrăm din interior spre exterior, mai întâi calculează 4 * 2 = 8, apoi 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 cu 2 pentru a obține 6.

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

Ce zici = 4 + 3 * 2?

Calculatorul 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 de prioritate, 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 și o perioadă negativă este pozitivă negativă. Puteți vedea 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 împrumutului 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 utilizaț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 "formatarea celulelor". Alegeți procentul și utilizați 2 zecimale.

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

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

Notă: credit ipotecar celula nu este inclusă în total. Excel nu știe că doriți să includeți numărul respectiv, deoarece nu există 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 drop-down "Financiar" și apoi selectați funcția PMT. Vrăjitorul apare:
În meniul Formule, selectați meniul drop-down "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.
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:
Acum, foaia de calcul arată astfel:
Cheltuielile dvs. lunare sunt de 1.863 $ - Ouch!
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 și 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.
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:

  1. Calculați lungimea șirului.
  2. Aflați poziția virgulei (aceasta arată unde se termină un cuvânt și celălalt începe).
  3. Copiați partea stângă a șirului până la virgulă.
  4. 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.

  1. Calculați lungimea șirului cu funcția = LEN (A3) - rezultatul este 18.
  2. Acum găsiți poziția virgulei introducând această funcție = FIND (",", A3 ") - rezultatul este 11.
  3. Acum, luați partea stângă a șirului până la virgulă și creați această formulă imbricată folosind 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 trebuie să luăm "George". Cum facem asta?
Așa că avem "Washington", acum trebuie să luăm "George". Cum facem asta?

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

  1. Amintiți-vă poziția virgulei sau calculați-o din nou.
  2. Calculați lungimea șirului.
  3. 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.

  1. De mai sus, aceasta este = FIND (",", A3 ")
  2. Lungimea șirului este = LEN (A3)
  3. Va trebui sa folositi cateva matematici pentru a gasi numarul de caractere care trebuie luate: = LEN (A3) - FIND (",", A3) - 1
  4. 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.

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

Urmeaza …

Aceasta ne încheie lecția pentru ziua de azi. Ar trebui să aveți o înțelegere destul de fermă cu privire la formule și funcții, rânduri și coloane și modul în care toate acestea pot fi folosite prin câteva exemple concrete.

Veniți î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 trebuie să rescrieți fiecare formulă de mai multe ori!

Recomandat: