Lista celor mai bune 10 formule și funcții avansate Excel
# 1 - Formula VLOOKUP în Excel
Această funcție Excel avansată este una dintre cele mai utilizate formule în Excel. Se datorează în principal simplității acestei formule și aplicației sale în căutarea unei anumite valori din alte tabele, care are o variabilă comună în aceste tabele. Să presupunem că aveți două tabele cu detalii despre salariul și numele angajaților unei companii, iar ID-ul angajatului este o coloană principală. Doriți să obțineți salariul din Tabelul B din Tabelul A.
Puteți utiliza VLOOKUP după cum urmează.
Va rezulta în tabelul de mai jos atunci când vom aplica această formulă Excel avansată în alte celule din coloana Salariul angajaților.
Trageți Formula în restul celulelor.
Există trei delimitări majore ale VLOOKUP:
- Nu puteți avea o coloană primară în dreapta coloanei pentru care doriți să completați valoarea dintr-un alt tabel. În acest caz, coloana Salariul angajaților nu poate fi înaintea ID-ului angajatului.
- În valorile duplicate din coloana principală din tabelul B, prima valoare va fi populată în celulă.
- Dacă introduceți o nouă coloană în baza de date (Ex. Inserați o nouă coloană înainte de salariul angajaților în tabelul B), rezultatul formulei poate fi diferit pe baza unei poziții pe care ați menționat-o în formulă (În cazul de mai sus, rezultatul ar fi gol)
# 2 - Formula INDEX în Excel
Această formulă Excel avansată este utilizată pentru a obține valoarea unei celule dintr-un tabel dat, specificând numărul de rânduri, coloane sau ambele. Ex. Pentru a obține numele unui angajat la a 5- a observație, mai jos sunt datele.
Putem folosi formula excel avansată după cum urmează:
Aceeași formulă INDEX poate fi utilizată pentru a obține valoare de-a lungul rândului. Atunci când se utilizează atât rândul, cât și numărul coloanei, sintaxa ar arăta ca așa:
Formula de mai sus ar întoarce „Rajesh Ved”.
Notă: dacă introduceți un alt rând în al 5- lea rând, formula va întoarce „Chandan Kale”. Prin urmare, ieșirea ar depinde de orice schimbări care se întâmplă în tabelul de date în timp.
# 3 - MATCH Formula în Excel
Această formulă avansată Excel returnează numărul rândului sau al coloanei atunci când există un anumit șir sau număr în intervalul dat. În exemplul de mai jos, încercăm să găsim „Rajesh Ved” în coloana Nume angajat.
Formula ar fi cea prezentată mai jos:
Funcția MATCH va returna 5 ca valoare.
3 - lea argument este folosit pentru potrivirea exactă. De asemenea, puteți utiliza +1 și -1 pe baza cerințelor dvs.
Notă: Se pot combina INDEX și MATCH pentru a depăși limitarea VLOOKUP.
# 4 - IF ȘI Formula în Excel
Există multe cazuri când trebuie să creați steaguri pe baza unor constrângeri. Cu toții suntem familiarizați cu sintaxa de bază a IF. Folosim această funcție Excel IF avansată pentru a crea un câmp nou bazat pe anumite constrângeri asupra unui câmp existent. Dar ce se întâmplă dacă trebuie să luăm în considerare mai multe coloane în timp ce creăm un steag. Ex. În cazul de mai jos, dorim să semnalăm toți angajații al căror salariu este mai mare de 50K, dar ID-ul angajatului mai mare de 3.
Am folosi IF ȘI în astfel de cazuri. Vă rugăm să găsiți mai jos captura de ecran pentru același lucru.
Ar întoarce rezultatul ca 0.
Putem avea multe condiții sau constrângeri pentru a crea un steag bazat pe mai multe coloane folosind ȘI.
# 5 - IF OR Formula în Excel
În mod similar, putem folosi funcția SAU în excel în loc de ȘI dacă trebuie să îndeplinim una dintre condițiile multora.
În cazurile de mai sus, dacă una dintre condiții este satisfăcută, vom avea celula populată ca 1 altul 0. De asemenea, putem înlocui 1 sau 0 cu unele șiruri de caractere cu ghilimele duble („”).
# 6 - Formula SUMIF în Excel
În unele analize, poate fi necesar să filtrați unele observații atunci când aplicați funcția sumă sau numărare. În astfel de cazuri, această funcție avansată SUMIF excel în Excel este în ajutorul nostru. Filtrează toate observațiile pe baza anumitor condiții date în această formulă Excel avansată și le rezumă. Ex. Ce se întâmplă dacă vrem să știm suma salariilor numai pentru acei angajați care au un ID de angajat mai mare de 3.
Prin aplicarea formulei SUMIFS:
Formula returnează rezultatele ca 322000.
De asemenea, putem număra numărul de angajați din organizație care au un ID de angajat mai mare de 3 atunci când se utilizează COUNTIF în loc de SUMIF.
# 7 - CONCATENEAZĂ Formula în Excel
Această funcție avansată excel este una dintre formulele care pot fi utilizate cu mai multe variante. Această formulă Excel avansată ne ajută să unim mai multe șiruri de text într-un singur șir de text. De exemplu, dacă dorim să afișăm ID-ul angajatului și numele angajatului într-o singură coloană.
Putem folosi această formulă CONCATENATE pentru a face acest lucru.
Formula de mai sus va avea ca rezultat „1Aman Gupta”.
Putem avea încă o variantă punând o singură cratimă între ID și NAME. Ex. CONCATENATE (B3, ”-“, C3) va avea ca rezultat „1-Aman Gupta”. Putem folosi acest lucru și în VLOOKUP când LOOKUP în valoare Excel este un amestec de mai multe variabile.
# 8 - Formula STÂNGA, MIDI și DREAPTA în Excel
Putem folosi această formulă Excel avansată pentru a extrage un anumit sub șir dintr-un șir dat. Ar putea fi utilizat pe baza cerințelor noastre. Ex. Dacă dorim să extragem primele 5 caractere din Nume angajat, putem folosi formula STÂNGA în excel cu numele coloanei și al doilea parametru ca 5.
Ieșirea este dată mai jos:
Aplicarea formulei DREAPTA în excel este, de asemenea, aceeași. Doar că ne-am uita la personajul din dreapta șirului. Cu toate acestea, în cazul unei funcții MID în excel, trebuie să oferim poziția de pornire a șirului de text cerut și lungimea șirului.
# 9 - Formula OFFSET în Excel
Această funcție Excel avansată, cu o combinație de alte funcții, cum ar fi SUM sau MEDIE, ar putea da o notă dinamică calculelor. Este cel mai bine utilizat în cazurile în care inserăm rânduri continue într-o bază de date existentă. OFFSET Excel ne oferă o gamă în care trebuie să menționăm celula de referință, numărul de rânduri și coloane. Ex. Dacă vrem să calculăm media primilor 5 angajați din companie unde avem un salariu al angajaților sortat după ID-ul angajatului, putem face următoarele. Calculul de mai jos ne va oferi întotdeauna salariați.
- Ne va da suma salariilor primilor 5 angajați.
# 10 - Formula TRIM în Excel
Această formulă excel avansată este utilizată pentru a curăța spațiile neimportante din text. Ex. Dacă vrem să eliminăm spații la începutul unui nume, îl putem folosi folosind funcția TRIM în excel ca mai jos:
Rezultatul rezultat ar fi „Chandan Kale” fără spațiu înainte de Chandan.
