17 formule Excel avansate – un lucru obligatoriu pentru toți profesioniștii

Dacă vă treziți deseori îndoiți mâinile și pierzând ore întregi pentru a face lucrurile manual în Excel, pierdeți cât de puternic poate fi Excel dacă știți cum să-l utilizați corect. Microsoft Excel este cunoscut pentru formulele sale care pot funcționa eficient pe un număr mare de date din celulă, fără a fi nevoie de multă implicare manuală în proces.

Am compilat 17 formule Excel avansate care au o abilitate neobișnuită de a transforma sarcini manuale îndelungate, banale, în câteva secunde de muncă. Acestea sunt formule pe care fiecare profesionist ar trebui să le aibă la îndemână pentru a-și economisi timpul prețios sau pentru a-și impresiona șeful într-un moment critic.

Treceți prin listă și spuneți-ne preferatul dvs.!

Cursanții primesc o creștere medie a salariului de 58%, cea mai mare fiind de până la 400%.

1. POTRIVIRE INDEX

Formula = INDEX(C3:E9,POTRIBUIRE(B13; C3:C9,0),POTIRE(B14,C3:E3,0))

O alternativă excelentă la formula VLOOKUP sau HLOOKUP pe Excel, care are unele dezavantaje pentru efectuarea sarcinilor de căutare. INDEX MATCH este o formulă combinată a 2 funcții separate:

INDEX: Această formulă returnează valoarea unei celule dintr-un tabel pe baza coloanei și a numărului rândului.

POTRIVIRE: Această formulă returnează poziția unei celule într-un rând sau coloană.

Un exemplu de combinare a formulelor INDEX și MATCH este: Când căutați în sus și returnați înălțimea unei persoane pe baza numelui acesteia, puteți utiliza această formulă pentru a modifica ambele variabile (în acest caz, numele și înălțimea) folosind formula INDEX MATCH .

Sursă

Să o descompunem pas cu pas:

Cum să combinați INDEX și MATCH

• Tastați INDEX
• Selectați zona pe care doriți să o indexați
• Blocați zona cu F4
• Găsiți rândul din care doriți să căutați datele
• Tastați MATCH cu seria de informații și blocați zona cu F4
• Tastați 0 pentru potrivirea exactă, închideți parantezele
• Apăsați Enter

Idei interesante de proiecte Data Science

Acum aveți un set complet dinamic și funcțional de coloane și rânduri în foaie în care toate pieptănările celulelor și rândurilor pentru datele potrivite se fac automat.

2. MEDIE

Pentru a efectua formula medie pentru a găsi media oricărui interval de numere, iată pașii pe care trebuie să îi urmați:

Introduceți valorile, celulele sau scara celulelor pe care le calculați în format.

Formula va începe cu =AVERAGE(număr1, număr 2 etc.)

Dacă doriți să efectuați media unui interval de celule din foaie, iată pașii pe care trebuie să-i urmați:

• Introduceți valorile, celulele sau scara celulelor pe care le calculați în format, așa cum ați făcut mai sus
• Formula va fi ca =AVERAGE(Valoarea de început: Valoarea finală).

Dacă vă întrebați cum să introduceți valorile pentru regiunea de celule, puteți selecta zona de pe foaia cu mouse-ul și o puteți bloca cu F4. Acest lucru va face ca Excel să facă restul muncii pentru dvs. fără a face nicio sumă și o împărțire a cifrei cu numărul de articole din grup.

3. SUMIF

Această formulă în Excel este notată ca SUMIF(interval, criterii, [interval de sumă]). Aceasta ar rezulta în suma valorilor din intervalul dorit de celule care ar îndeplini cerințele stabilite de dvs. De exemplu, =SUMIF(C3: C12, „>70.000) ar returna suma valorilor dintre celulele C3 și C12 numai din celulele care au valoarea mai mare de 70.000.

În cazul calculelor de finanțe, salarii sau chiar costuri, aveți nevoie de valoarea clienților potențiali care sunt asociate cu anumiți angajați determinate de condiția stabilită de dvs. A face acest lucru manual necesită foarte mult timp și încetinește lucrurile.

Formula pentru condiția de mai sus poate fi =SUMIF(gamă, criterii, [interval_sumă]), unde Interval poate fi definit ca intervalul foii din care trebuie să alegeți valorile.

[sum_range] este definit ca intervalul suplimentar sau opțional pe care îl veți adăuga în plus față de primul interval introdus.

Iată un exemplu:

Sursă

4. OFFSET COMBINAT CU SUM

Singură, funcția OFFSET s-ar putea să nu fie la îndemână, dar atunci când este combinată cu alte servicii, puteți obține o formulă complexă cu rezultate mai rapide dacă doriți să creați un rol dinamic care să însumeze orice număr variabil de celule, formula obișnuită SUM, care este static trebuie combinat cu funcția OFFSET.

Deci, pentru a afla suma valorilor din celulele variabile, formula ar trebui să fie:

=SUMA(B4:OFFSET(B4,0,E2-1))

Sursă

Aici referința de sfârșit a funcției SUM este înlocuită cu funcția OFFSET. Formula SUM care începe în B4 se termină cu o variabilă și este o formulă OFFSET începând cu B4, continuând cu valoarea din E2 (“3”) minus unu. Acest lucru ajută metoda să se deplaseze peste două celule și să însumeze trei ani de date. În referința de mai sus, puteți vedea că celula F7 conține suma celulelor B4: D4 = 15.

5. DACĂ ȘI

Această formulă este utilă în situațiile în care trebuie să creați anumite condiții pentru a parcurge o grămadă de date. Declarația IF ajută la utilizarea funcției avansate Excel IF pentru a crea un câmp nou bazat pe aceste condiții pe o pistă deja existentă.

De exemplu, dacă doriți să marcați angajații cu salarii peste 50.000 și ID-ul angajatului mai mare de 3, atunci formula va fi:

DACĂ(ȘI(E4>3;F4>50000)1,0)

Sursă

Deoarece nu există cazuri reale în datele de mai sus, formula ar returna valoarea 0.

6. CONCATENATE

Dacă aveți o mulțime de șiruri de text în foaia de date și doriți ca datele să apară într-o singură linie, această formulă este cea mai potrivită. De exemplu, dacă doriți să reprezentați ID-ul angajatului și numele angajatului într-o singură coloană, metoda ar trebui să fie:

=CONCATENATE(B3, C3)

Sursă

7. PMT

Această funcție vă va ajuta să aflați plățile viitoare datorate pentru un împrumut, având în vedere o anumită rată a dobânzii, suma principală și durata împrumutului. Iată ce trebuie să începeți:

• Termenul împrumutului
• Principalul de pornire (banii) al împrumutului
• Valoarea viitoare
• Tipul de împrumut

Acum, dacă doriți să găsiți plata lunară pentru o sumă principală, formula pentru aceeași va fi:

= PMT (rata, per, PV, [fv], [tip])

Să înțelegem asta cu un exemplu:

Sursă

=PMT(C2/12.B2.A2)

Și cea mai bună parte este că puteți trage colțul din dreapta jos al celulelor PMT peste câmpuri pentru a calcula automat suma de plată lunară pentru toate câmpurile!

Tipuri populare de locuri de muncă în domeniul științei datelor

8. TRIM

Aceasta este una dintre cele mai folosite formule din excel care curata orice spatiu nedorit din campuri. De exemplu: Dacă trebuie să eliminați spațiile de la începutul unui nume, puteți face asta folosind funcția TRIM:

=TRIM (C6)

Sursă

Acest lucru vă va întoarce cu valoarea Chandan Kale fără spații suplimentare atașate la față sau la capăt.

9. LEN

Aceasta este o funcție care vă spune numărul de caractere dintr-un șir de text. Una dintre cele mai interesante modalități de a utiliza acest lucru este, de exemplu, dacă doriți să evidențiați donatorii care au donat peste 1.000 USD numărând numărul de cifre din coloana de donații, formula va fi:

=LEN(B2)

Sursă

Și dacă doriți să evidențiați toate celulele din coloana Donație, atunci trebuie să:

• Selectați fila Acasă din meniu
• Faceți clic pe Formatare condiționată (în bara de instrumente)
• Selectați Utilizați o formulă pentru a determina ce celule să formatați

Sursă

Aici, dacă faceți condiția „>3”, atunci orice lucru peste 1.000 USD va primi formatarea unică, pe care o puteți alege făcând clic pe opțiunea Format.

10. ALEGE

Aceasta este o funcție excelentă pentru analiza scenariilor în modelarea financiară. Vă permite să alegeți între un anumit număr de opțiuni și să returnați „alegerea” pe care ați selectat-o. De exemplu, dacă aveți trei valori diferite pentru creșterea veniturilor anul viitor pe baza ipotezelor, folosind funcția ALEGE, puteți returna suma conform cerințelor dvs.

Formula este:

=ALEGE(C7;D3;D4;D5)

Sursă

11. CELULA, STÂNGA, MIJLOCUL și DREAPTA

Toate funcțiile de mai sus pot fi combinate în mai multe moduri pentru a obține câteva formule avansate.

• Funcția CELL este utilizată pentru a returna diferite tipuri de informații despre conținutul celulei
• Serviciul LEFT este folosit pentru a înlocui textul de la începutul celulei.
• Funcția MID poate returna text din oricare dintre punctele de pornire ale celulei.
• Funcția RIGHT returnează doar text de la sfârșitul celulei.

Sursă

12. XNPV și XIRR

Pentru toți analiștii care întâlnesc servicii bancare de investiții, cercetare pe acțiuni sau orice altă zonă a finanțelor corporative care necesită reducerea fluxurilor de numerar, atunci aceste formule vă vor economisi cu siguranță mult timp și mormăi!

Întrebări și răspunsuri la interviu Data Science

De exemplu, dacă doriți să calculați valoarea netă prezentă (VAN) pentru orice investiție folosind o rată specificată de actualizare și fluxuri de numerar care apar la intervale neregulate, utilizați următoarea funcție.

=XNPV(rata de reducere, fluxuri de numerar, date)

Sursă

Pe de altă parte, funcția XIRR vă spune rata internă de rentabilitate (unde ieșire = aflux) pentru o serie de fluxuri de numerar care apar la intervale neregulate, cum ar fi:

13. COUNTA ( )

Analiștii se găsesc adesea pe degetele de la picioare chinuindu-se să găsească numărul de celule cu valori (numere, erori, text, valori logice) și cele care sunt goale. Funcția COUNTA( ) vă poate ajuta să aflați numele celulelor negoale dintr-un interval selectat. De exemplu, formula de numărare a valorilor pentru o fișă de date având coloanele A1-A10 este:

=COUNTA(A1: A10)

Sursă

14. FV

Această formulă este utilă dacă vrei să investești bani în ceva și să cunoști valoarea acestuia. Cerințele formulei FV sunt:

• Rata dobânzii la împrumut
• Numărul de plăți
• Plata pentru fiecare perioadă
• Sold curent de pornire
• Tipul de împrumut

De exemplu, dacă doriți să comparați mai multe CD-uri goale și aveți o moștenire de 20.000 USD de investit într-un CD. Ratele dobânzilor sunt reprezentate în format zecimal; plățile sunt zero. Formula scenariului va fi:

=FV(A2/12;B2;C2;D2)

Sursă

Rezultatele vor fi:

Sursă

15. RANDBINTWEEN

Această funcție ajută la selectarea aleatorie a unui număr dintr-un interval predefinit de numere. Odată ce ați introdus cele mai mici și cele mai mari numere în formulă, Excel poate alege datele potrivite din câmpurile la care sunt atașate numele din Interval și poate alege aleatoriu dintre ele. Metoda pentru scenariu este:

=RANDBETWEEN(punctul de pornire, punctul de sfârșit)

Sursă

16. MIC

Funcția MIC din Excel returnează valori numerice pe baza poziției valorii într-o listă care este clasată după importanță. Această funcție ajută la preluarea „cele mai mici valori” dintr-o matrice sau dintr-un interval de celule, cum ar fi cea mai mică valoare, a doua cea mai mică valoare, a treia cea mai mică valoare etc.

Sintaxa pentru formula este

= MIC (ori, interval)

De exemplu,

Sursă

Deoarece funcția SMALL este automată, trebuie să furnizați un interval și un număr întreg pentru „nth” pentru a specifica valoarea clasată. Numele oficiale pentru aceste argumente sunt „matrice” și „k”.

17. CUARTILĂ

Această funcție returnează quartila (fiecare din cele patru grupuri egale) dintr-un set dat de date și poate returna valoarea minimă, primul cuartil, valoarea maximă a celui de-al doilea cuartil. Această funcție aduce cantitatea de quartile a câmpurilor dintr-o matrice. Funcția returnează o valoare numerică în funcție de percentila solicitată.

Sintaxă: =QUARTILE (matrice, quart)

Sursă

CONCLUZIE:

Microsoft Excel este destul de inevitabil când vine vorba de locul de muncă din secolul 21, dar trucul este că nu trebuie să fie atât de descurajantă. Fă-l prietenul tău și urmărește-ți creșterea productivității!