Formule avansate Excel (cuprins)
- Introducere în formulele avansate în Excel
- Exemple de formulă avansată în Excel
Introducere în formulele avansate în Excel
După ce atingeți nivelul intermediar în excel, trebuie să muncești din greu pentru a trece la nivelul avansat. Pentru a trece la nivelul avansat, trebuie să fiți conștienți de unele dintre formulele avansate utilizate frecvent. În acest articol, voi acoperi primele 10 formule avansate pe care trebuie să le cunoască elevii excelenți. Urmați acest articol pentru a afla și explora.
Exemple de formulă avansată în Excel
Haideți să înțelegem cum să utilizați Formulele avansate în Excel cu câteva exemple.
Puteți descărca acest șablon cu formule avansate de formule aici - șablonul cu formule avansate de formulareExemplul # 1 - Funcție parțială VLOOKUP
Trebuie să fi întâlnit o situație în care VLOOKUP produce o eroare, chiar dacă a existat o ușoară potrivire a ratării în valoarea de căutare. De exemplu, dacă găsiți pentru salariul numelui Abhishek Sinha și dacă aveți doar Abhishek, atunci VLOOKUP nu vă poate aduce datele.
Cu toate acestea, folosind caracterul asterisc la capătul valorii de căutare, putem prelua datele pentru o valoare parțială de căutare.
În tabelul 1 am nume complet și salariu, dar în tabelul 2 am doar nume parțiale și trebuie să găsesc salariul fiecărui angajat.
Pasul 1: Deschideți formula VLOOKUP în celula E3. Înainte de a selecta valoarea de căutare, puneți un asterisc (*) de o parte și de alta a valorii de căutare.
Pasul 2: Ca de obicei, puteți completa formula VLOOKUP acum și vom avea rezultatele.
După utilizarea formulei de mai sus, ieșirea este prezentată mai jos.
Aceeași formulă este folosită și în alte celule.
Exemplul # 2 - COUNTIFS cu simboluri operator
Trebuie să fi folosit funcția COUNTIF & IFS pentru a număra lucrurile din listă. De asemenea, putem conta pe baza simbolurilor operatorului ca mai mare decât (>) mai mic decât (<) și semn egal (=).
Acum, uitați-vă la datele de mai jos, de exemplu. Dacă doriți să numărați numărul total de facturi pentru regiunea SUD după data de 10 ianuarie 2018, cum contați?
Exemplul # 3 - Condiția IF cu condiții AND & OR
Funcțiile logice fac parte din activitățile noastre de zi cu zi. Trebuie să le stăpânești pentru a trece la nivelul următor. Dacă calculați bonusul pe baza mai multor condiții, trebuie să faceți cuibul AND sau OR cu condiție IF pentru a finaliza treaba.
Presupunem că trebuie să calculați suma bonusului pentru fiecare departament în funcție de departamentul și anii de serviciu de care aveți nevoie de aceste funcții. Pe baza criteriilor de mai jos, trebuie să calculăm bonusul.
Dacă serviciul depășește 4 ani și departamentul este fie bonus de vânzări, fie de asistență este de 50000, altfel bonus de 25000.
Aplicați formula de mai jos pentru a obține suma bonusului.
După utilizarea formulei de mai sus, ieșirea prezentată mai jos.
Aceeași formulă se aplică în celula E3 până la E9.
Exemplul nr. 4 - Funcția TEXT pentru a vă face direcționarea dinamică
Să spunem că mențineți un tabel de vânzări zilnic și trebuie să actualizați tabelul în fiecare zi. La începutul tabelului, aveți o rubrică care spune „Date de vânzări consolidate de la DD-MM-AAAA până la DD-MM-AAAA”. Ca și când se actualizează tabelul, trebuie să schimbați data antetului. Nu este o sarcină frustrantă să faci același lucru din nou? Putem face acest titlu dinamic folosind funcțiile TETX, MIN și Max împreună cu simbolul operatorului concatenat ampersand (&).
Exemplul # 5 - INDEX + MATCH + MAX pentru a găsi cea mai mare persoană de vânzări
Aveți o listă cu personalul de vânzări și vânzările pe care le-au făcut pentru numele lor. Cum vă spuneți cine este cel mai bun sau cel mai mare vânzător din listă? Desigur, avem mai multe alte tehnici pentru a spune rezultatul, dar această funcție poate returna cel mai mare vânzător din lot.
Aplicați funcția de mai jos pentru a obține cel mai mare nume de vânzător.
După utilizarea formulei de mai sus, ieșirea prezentată mai jos.
Exemplul # 6 - Obțineți un număr de valori unice din listă
Dacă aveți multe valori duplicate și vi se cere să spuneți câte valori unice există în ceea ce spuneți? Puteți spune prin eliminarea valorii dublate, dar aceasta nu este modul dinamic de a spune numărul unic de valori.
Folosind această funcție de matrice, putem spune valorile unice din lot.
Aplicați formula de mai jos pentru a obține lista de valori unice.
Exemplul nr. 7 - Folosiți intervalul denumit pentru a face dinamicul dropdown
Dacă lucrați adesea cu o listă derulantă și faceți lista derulantă actualizată, trebuie să reveniți la gama de surse pentru a șterge sau adăuga valori. După aceea, trebuie să reveniți la celulele derulante și să actualizați din nou gama listei derulante.
Cu toate acestea, dacă puteți crea o gamă numită pentru lista dvs. derulantă, puteți face lista derulantă dinamică și actualizată.
Creați un interval de nume așa cum se arată în imaginea de mai jos.
Acum mergeți la celula derulantă și deschideți caseta de dialog derulantă.
La apăsarea tastei F3, va afișa toate numele definite, selectați numele listei derulante.
Ok, o listă derulantă este gata și va actualiza automat valorile atunci când există o modificare a intervalului drop-down.
Exemplul # 8 - Scapă de valorile de eroare folosind funcția IFERROR
Sunt sigur că ați înregistrat valori de eroare în timp ce lucrați cu calcule VLOOKUP, Divizie. Gestionarea acestor valori de eroare este o sarcină obositoare. Dar putem scăpa de aceste valori de eroare folosind funcția IFERROR în formulă.
După utilizarea formulei de mai sus, ieșirea prezentată mai jos.
Aceeași formulă folosită în alte celule.
Exemplul # 9 - Utilizați funcția PMT pentru a crea propriul grafic EMI
În prezent, opțiunea EMI nu este un lucru ciudat pentru noi toți. În excel, putem estima propria diagramă EMI folosind funcția PMT. Urmați pașii de mai jos pentru a crea propriul grafic.
Aplicați formula de mai jos în celula B4 pentru a obține cantitatea EMI.
Exemplul # 10 - INDEX + MATCH ca alternativă la funcția VLOOKUP
Sper că sunteți la curent cu limitarea funcției VLOOKUP. O limitare majoră este VLOOKUP poate prelua datele de la stânga la dreapta nu de la dreapta la stânga. Nu toate datele din timp sunt bine organizate și gata de utilizare. De multe ori coloana de valori pe care o căutăm se află în partea stângă a valorii de căutare, astfel încât VLOOKUP nu reușește să ajute în aceste cazuri.
O combinație de funcție INDEX + MATCH servește ca o alternativă la funcția VLOOKUP.
După utilizarea formulei de mai sus, ieșirea este prezentată mai jos.
Aceeași formulă este folosită și în alte celule.
Pe baza ID-ului produsului, trebuie să extragem valori de vânzări. În tabelul principal, ID-ul produsului este în partea dreaptă a coloanei de vânzări. Deci VLOOKUP nu poate prelua datele. Utilizați formula de mai jos pentru a obține datele.
Articole recomandate
Acesta este un ghid pentru Formule avansate în Excel. Aici vom discuta despre cum să utilizați Formule avansate în Excel, împreună cu exemple practice și șablon Excel descărcabil. De asemenea, puteți parcurge și alte articole sugerate -
- Cum se utilizează funcția Excel INDEX?
- Excel avansat | Funcția bazei de date
- Exemple despre formula TRIM în Excel
- Ghid pentru Formula Excel OFFSET