Formule Excel & Funcții: învățați cu EXEMPLE de bază

Cuprins:

Anonim

Formulele și funcțiile sunt elementele fundamentale ale lucrului cu date numerice în Excel. Acest articol vă prezintă formule și funcții.

În acest articol, vom aborda următoarele subiecte.

  • Ce este Formulele în Excel?
  • Greșeli de evitat atunci când lucrați cu formule în Excel
  • Ce este funcția în Excel?
  • Importanța funcțiilor
  • Funcții comune
  • Funcții numerice
  • Funcții de șir
  • Funcțiile Date Time
  • V Funcția de căutare

Date de tutoriale

Pentru acest tutorial, vom lucra cu următoarele seturi de date.

Buget pentru consumabile pentru casă

S / N ARTICOL QTY PREȚ SUBTOTAL Este accesibil?
1 Mango 9 600
2 Portocale 3 1200
3 Roșii 1 2500
4 Ulei de gatit 5 6500
5 Apa tonica 13 3900

Programul proiectului de construcție a casei

S / N ARTICOL DATA DE ÎNCEPUT DATA DE ÎNCHEIERE DURATA (ZILE)
1 Terenul de cercetare 04/02/2015 07/02/2015
2 Fundație laică 10/02/2015 15/02/2015
3 Acoperișuri 27/02/2015 03/03/2015
4 Pictura 03.03.2015 21/03/2015

Ce este Formulele în Excel?

FORMULĂ ÎN EXCEL este o expresie care operează pe valori dintr-o gamă de adrese și operatori de celule. De exemplu, = A1 + A2 + A3, care găsește suma intervalului de valori de la celula A1 la celula A3. Un exemplu de formulă alcătuită din valori discrete precum = 6 * 3.

=A2 * D2 / 2

AICI,

  • spune Excel că aceasta este o formulă și ar trebui să o evalueze.
  • "A2" * D2" face referire la adresele de celule A2 și D2 apoi înmulțește valorile găsite în aceste adrese de celule.
  • "/" este operatorul aritmetic al diviziunii
  • "2" este o valoare discretă

Exerciții practice de formule

Vom lucra cu datele de eșantionare pentru bugetul de acasă pentru a calcula subtotalul.

  • Creați un nou registru de lucru în Excel
  • Introduceți datele afișate în bugetul consumabilelor de mai sus.
  • Foaia dvs. de lucru ar trebui să arate după cum urmează.

Vom scrie acum formula care calculează subtotalul

Setați focalizarea pe celula E4

Introduceți următoarea formulă.

=C4*D4

AICI,

  • "C4*D4" folosește multiplicarea operatorului aritmetic (*) pentru a multiplica valoarea adresei celulei C4 și D4.

Apăsați tasta Enter

Veți obține următorul rezultat

Următoarea imagine animată vă arată cum să selectați automat adresa celulei și să aplicați aceeași formulă altor rânduri.

Greșeli de evitat atunci când lucrați cu formule în Excel

  1. Amintiți-vă regulile parantezelor de divizare, multiplicare, adunare și scădere (BODMAS). Aceasta înseamnă că expresiile sunt parantezele evaluate mai întâi. Pentru operatorii aritmetici, diviziunea este evaluată mai întâi, urmată de înmulțire, apoi adunarea și scăderea este ultima care trebuie evaluată. Folosind această regulă, putem rescrie formula de mai sus ca = (A2 * D2) / 2. Acest lucru va asigura că A2 și D2 sunt mai întâi evaluate, apoi împărțite la două.
  2. Formulele de foi de calcul Excel funcționează de obicei cu date numerice; puteți profita de validarea datelor pentru a specifica tipul de date care ar trebui acceptate de o celulă, adică numai numere.
  3. Pentru a vă asigura că lucrați cu adresele de celule corecte la care se face referire în formule, puteți apăsa F2 pe tastatură. Aceasta va evidenția adresele de celulă utilizate în formulă și puteți verifica încrucișat pentru a vă asigura că acestea sunt adresele de celulă dorite.
  4. Când lucrați cu mai multe rânduri, puteți utiliza numerele de serie pentru toate rândurile și puteți avea un număr de înregistrări în partea de jos a foii. Ar trebui să comparați numărul de serie cu totalul înregistrării pentru a vă asigura că formulele dvs. au inclus toate rândurile.

Consultați primele 10 formule ale foii de calcul Excel

Ce este funcția în Excel?

FUNCȚIA ÎN EXCEL este o formulă predefinită care este utilizată pentru valori specifice într-o anumită ordine. Funcția este utilizată pentru activități rapide, cum ar fi găsirea sumei, numărului, valorii medii, valorii maxime și valorilor minime pentru o gamă de celule. De exemplu, celula A3 de mai jos conține funcția SUM care calculează suma intervalului A1: A2.

  • SUMA pentru însumarea unei game de numere
  • MEDIE pentru calcularea mediei unui anumit interval de numere
  • COUNT pentru numărarea numărului de articole dintr-un interval dat

Importanța funcțiilor

Funcțiile măresc productivitatea utilizatorilor atunci când lucrați cu excel . Să presupunem că ați dori să obțineți marele total pentru bugetul de mai sus pentru consumabile. Pentru ao simplifica, puteți utiliza o formulă pentru a obține marele total. Folosind o formulă, va trebui să faceți referire la celulele E4 până la E8 una câte una. Ar trebui să utilizați următoarea formulă.

= E4 + E5 + E6 + E7 + E8

Cu o funcție, ați scrie formula de mai sus ca

=SUM (E4:E8)

După cum puteți vedea din funcția de mai sus utilizată pentru a obține suma unui interval de celule, este mult mai eficient să utilizați o funcție pentru a obține suma decât folosind formula care va trebui să facă referire la multe celule.

Funcții comune

Să ne uităm la unele dintre funcțiile cele mai frecvent utilizate în formulele ms excel. Vom începe cu funcții statistice.

S / N FUNCŢIE CATEGORIE DESCRIERE UTILIZARE
01 SUMĂ Math & Trig Adaugă toate valorile dintr-un interval de celule = SUMĂ (E4: E8)
02 MIN Statistic Găsește valoarea minimă într-un interval de celule = MIN (E4: E8)
03 MAX Statistic Găsește valoarea maximă într-un interval de celule = MAX (E4: E8)
04 IN MEDIE Statistic Calculează valoarea medie într-un interval de celule = MEDIE (E4: E8)
05 NUMARA Statistic Numără numărul de celule dintr-un interval de celule = COUNT (E4: E8)
06 LEN Text Returnează numărul de caractere dintr-un text șir = LEN (B7)
07 SUMIF Math & Trig Adaugă toate valorile dintr-un interval de celule care îndeplinesc un criteriu specificat. = SUMIF (interval, criterii, [sum_range]) = SUMIF (D4: D8, "> = 1000", C4: C8)
08 MEDIEIF Statistic Calculează valoarea medie într-un interval de celule care îndeplinesc criteriile specificate. = AVERAGEIF (interval, criterii, [interval_mediu]) = AVERAGEIF (F4: F8, "Da", E4: E8)
09 ZILE Data și ora Returnează numărul de zile între două date = ZILE (D4, C4)
10 ACUM Data și ora Returnează data și ora curentă a sistemului = ACUM ()

Funcții numerice

După cum sugerează și numele, aceste funcții operează pe date numerice. Tabelul următor prezintă câteva dintre funcțiile numerice comune.

S / N FUNCŢIE CATEGORIE DESCRIERE UTILIZARE
1 ISNUMBER informație Returnează True dacă valoarea furnizată este numerică și False dacă nu este numerică = ISNUMBER (A3)
2 RAND Math & Trig Generează un număr aleatoriu între 0 și 1 = RAND ()
3 RUNDĂ Math & Trig Rotunjește o valoare zecimală la numărul specificat de zecimale = ROUND (3.14455,2)
4 MEDIAN Statistic Returnează numărul din mijlocul setului de numere date = MEDIAN (3,4,5,2,5)
5 PI Math & Trig Returnează valoarea funcției Math PI (π) = PI ()
6 PUTERE Math & Trig Returnează rezultatul unui număr ridicat la o putere. PUTERE (număr, putere) = PUTERE (2,4)
7 MOD Math & Trig Returnează restul când împărțiți două numere = MOD (10,3)
8 ROMÂN Math & Trig Convertește un număr în cifre romane = ROMAN (1984)

Funcții de șir

Aceste funcții Excel de bază sunt utilizate pentru a manipula date text. Tabelul următor prezintă unele dintre funcțiile comune ale șirurilor.

S / N FUNCŢIE CATEGORIE DESCRIERE UTILIZARE COMETARIU
1 STÂNGA Text Returnează un număr de caractere specificate de la începutul (partea stângă) a unui șir = STÂNGA ("GURU99", 4) Au rămas 4 caractere din „GURU99”
2 DREAPTA Text Returnează un număr de caractere specificate de la capătul (partea dreaptă) a unui șir = DREAPTA ("GURU99", 2) Dreapta 2 caractere din „GURU99”
3 MID Text Preluează un număr de caractere din mijlocul unui șir dintr-o poziție și lungime de pornire specificate. = MID (text, start_num, num_chars) = MID ("GURU99", 2,3) Recuperarea caracterelor 2-5
4 ISTEXT informație Returnează True dacă parametrul furnizat este Text = ISTEXT (valoare) valoare - Valoarea de verificat.
5 GĂSI Text Returnează poziția inițială a unui șir de text într-un alt șir de text. Această funcție este sensibilă la majuscule și minuscule. = FIND (find_text, within_text, [start_num]) = FIND ("oo", "Acoperiș", 1) Găsiți oo în „Acoperiș”, rezultatul este 2
6 A INLOCUI Text Înlocuiește o parte a unui șir cu un alt șir specificat. = REPLACE (text_vechi, numere_început, num_caruri, text_nou) = REPLACE ("Acoperiș", 2,2, "xx") Înlocuiți „oo” cu „xx”

Funcții Data și Ora

Aceste funcții sunt utilizate pentru a manipula valorile datei. Tabelul următor prezintă câteva dintre funcțiile comune de dată

S / N FUNCŢIE CATEGORIE DESCRIERE UTILIZARE
1 DATA Data și ora Returnează numărul care reprezintă data în codul Excel = DATA (2015,2,4)
2 ZILE Data și ora Găsiți numărul de zile dintre două date = ZILE (D6, C6)
3 LUNĂ Data și ora Returnează luna de la o valoare de dată = LUNĂ („02.04.2015”)
4 MINUT Data și ora Returnează minutele dintr-o valoare de timp = MINUT („12:31”)
5 AN Data și ora Returnează anul de la o valoare de dată = AN („04/02/2015”)

Funcția VLOOKUP

Funcția VLOOKUP este utilizată pentru a efectua o privire verticală în sus în coloana din stânga și pentru a returna o valoare în același rând dintr-o coloană pe care o specificați. Să explicăm acest lucru într-un limbaj laic. Bugetul consumabilelor pentru casă are o coloană cu numărul de serie care identifică în mod unic fiecare articol din buget. Să presupunem că aveți numărul de serie al articolului și doriți să cunoașteți descrierea articolului, puteți utiliza funcția VLOOKUP. Iată cum ar funcționa funcția VLOOKUP.

=VLOOKUP (C12, A4:B8, 2, FALSE)

AICI,

  • "=VLOOKUP" apelează funcția de căutare verticală
  • "C12" specifică valoarea care trebuie căutată în coloana din stânga
  • "A4:B8" specifică tabelul cu datele
  • "2" specifică numărul coloanei cu valoarea rândului care trebuie returnată de funcția VLOOKUP
  • "FALSE," spune funcției VLOOKUP că căutăm o potrivire exactă a valorii de căutare furnizate

Imaginea animată de mai jos arată acest lucru în acțiune

Descărcați codul Excel de mai sus

rezumat

Excel vă permite să manipulați datele folosind formule și / sau funcții. Funcțiile sunt, în general, mai productive în comparație cu formulele de scriere. Funcțiile sunt, de asemenea, mai precise în comparație cu formulele, deoarece marja de a greși este foarte minimă.

Iată o listă de importante formule și funcții Excel

  • Funcția SUMA = =SUM(E4:E8)
  • Funcția MIN = =MIN(E4:E8)
  • Funcția MAX = =MAX(E4:E8)
  • Funcția MEDIE = =AVERAGE(E4:E8)
  • Funcția COUNT = =COUNT(E4:E8)
  • Funcția ZILE = =DAYS(D4,C4)
  • Funcția VLOOKUP = =VLOOKUP (C12, A4:B8, 2, FALSE)
  • Funcția DATE = =DATE(2020,2,4)