Formule avansate Excel - 10 formule pe care trebuie să le cunoașteți!

Fiecare analist financiar The Analyst Trifecta® Guide Ghidul final pentru a fi un analist financiar de talie mondială. Vrei să fii un analist financiar de talie mondială? Căutați să urmați cele mai bune practici de top din industrie și să ieșiți din mulțime? Procesul nostru, numit The Analyst Trifecta® constă în analize, prezentări și abilități ușoare petrece mai mult timp în Excel decât ar dori să admită. Pe baza anilor și anilor de experiență, am compilat cele mai importante și avansate formule Excel pe care trebuie să le cunoască fiecare analist financiar de talie mondială.

1. MATCH INDICE

Formula: = INDEX (C3: E9, MATCH (B13, C3: C9,0), MATCH (B14, C3: E3,0))

Aceasta este o alternativă avansată la formulele VLOOKUP sau HLOOKUP (care au mai multe dezavantaje și limitări). INDEX MATCH Index Match Formula Combinând funcțiile INDEX și MATCH este o formulă de căutare mai puternică decât VLOOKUP. Aflați cum să utilizați INDEX MATCH în acest tutorial Excel. Index returnează valoarea unei celule dintr-un tabel pe baza numărului de coloană și rând și Match returnează poziția unei celule într-un rând sau coloană. Aflați cum să le găsiți în acest ghid este o combinație puternică de formule Excel care vă vor duce la analiza financiară și la modelarea financiară la nivelul următor.

INDEX returnează valoarea unei celule dintr-un tabel pe baza numărului de coloană și rând.

MATCH returnează poziția unei celule într-un rând sau coloană.

Iată un exemplu al formulelor INDEX și MATCH combinate împreună. În acest exemplu, privim în sus și returnăm înălțimea unei persoane în funcție de numele acesteia. Deoarece numele și înălțimea sunt ambele variabile în formulă, le putem schimba pe ambele!

Formule avansate Excel - potrivire indexFormula de potrivire a indexului Combinând funcțiile INDEX și MATCH este o formulă de căutare mai puternică decât VLOOKUP. Aflați cum să utilizați INDEX MATCH în acest tutorial Excel. Index returnează valoarea unei celule dintr-un tabel pe baza numărului de coloană și rând și Match returnează poziția unei celule într-un rând sau coloană. Aflați cum să le faceți în acest ghid

Pentru o explicație pas cu pas sau cum să utilizați această formulă, vă rugăm să consultați ghidul nostru gratuit despre cum să utilizați INDEX MATCH în Excel Index Match Formula. Combinarea funcțiilor INDEX și MATCH este o formulă de căutare mai puternică decât VLOOKUP. Aflați cum să utilizați INDEX MATCH în acest tutorial Excel. Index returnează valoarea unei celule dintr-un tabel pe baza numărului de coloană și rând și Match returnează poziția unei celule într-un rând sau coloană. Aflați cum să le faceți în acest ghid.

2. DACĂ este combinat cu ȘI / SAU

Formula: = IF (AND (C2> = C4, C2 <= C5), C6, C7)

Oricine a petrecut mult timp realizând diferite tipuri de modele financiare Tipuri de modele financiare Cele mai comune tipuri de modele financiare includ: modelul cu 3 declarații, modelul DCF, modelul M&A, modelul LBO, modelul bugetar. Descoperiți primele 10 tipuri știe că formulele IF imbricate pot fi un coșmar. Combinația IF cu funcția ȘI sau SAU poate fi o modalitate excelentă de a păstra formulele mai ușor de auditat și mai ușor de înțeles de către ceilalți utilizatori. În exemplul de mai jos, veți vedea cum am folosit funcțiile individuale în combinație pentru a crea o formulă mai avansată.

Pentru o detaliere a modului de realizare a acestei funcții în Excel, vă rugăm să consultați ghidul nostru gratuit despre cum să folosiți IF cu ȘI / SAU Declarație între două numere Descărcați acest șablon gratuit pentru o declarație IF între două numere în Excel. În acest tutorial, vă arătăm pas cu pas cum să calculați IF cu instrucțiunea AND. Aflați cum să creați o instrucțiune IF care va testa dacă o celulă conține o valoare între două numere și apoi va afișa rezultatul dorit dacă respectiva condiție este îndeplinită. Formule avansate Excel - IF ANDDeclarație IF între două numere Descărcați acest șablon gratuit pentru o declarație IF între două numere în Excel. În acest tutorial, vă arătăm pas cu pas cum să calculați IF cu instrucțiunea AND. Aflați cum să creați o instrucțiune IF care va testa dacă o celulă conține o valoare între două numere și apoi afișează rezultatul dorit dacă respectiva condiție este îndeplinită

3. OFFSET combinat cu SUMĂ sau MEDIE

Formula: = SUM (B4: OFFSET (B4,0, E2-1))

Funcția OFFSET Funcția OFFSET Funcția OFFSET este clasificată în funcțiile de căutare și referință Excel. OFFSET va returna o gamă de celule. Adică, va returna un număr specificat de rânduri și coloane dintr-un interval inițial care a fost specificat. pe cont propriu nu este deosebit de avansat, dar atunci când îl combinăm cu alte funcții precum SUM sau AVERAGE putem crea o formulă destul de sofisticată. Să presupunem că doriți să creați o funcție dinamică care poate însuma un număr variabil de celule. Cu formula SUM obișnuită, sunteți limitat la un calcul static, dar adăugând OFFSET puteți face referința celulei să se deplaseze.

Cum funcționează: Pentru ca această formulă să funcționeze, înlocuim celula de referință finală a funcției SUM cu funcția OFFSET. Acest lucru face ca formula să fie dinamică, iar celula la care se face referire ca E2 este locul unde puteți spune Excel câte celule consecutive doriți să adăugați. Acum avem câteva formule Excel avansate!

Mai jos este o captură de ecran a acestei formule puțin mai sofisticate în acțiune.

formula de compensare a sumei

După cum vedeți, formula SUM începe în celula B4, dar se termină cu o variabilă, care este formula OFFSET începând de la B4 și continuând cu valoarea în E2 („3”), minus una. Aceasta mută sfârșitul formulei de sumă pe 2 celule, însumând 3 ani de date (inclusiv punctul de plecare). După cum puteți vedea în celula F7, suma celulelor B4: D4 este 15, ceea ce ne oferă formula de compensare și sumă.

Aflați cum să construiți această formulă pas cu pas în cursul nostru avansat Excel.

4. ALEGE

Formula: = ALEGE (alegere, opțiune1, opțiune2, opțiune3)

Funcția CHOOSE Funcția CHOOSE Funcția CHOOSE este clasificată în funcțiile Excel Search and Reference. Acesta va returna o valoare dintr-o matrice corespunzătoare numărului de index furnizat. Funcția va returna a n-a intrare dintr-o listă dată. În calitate de analist financiar, funcția ALEGE este utilă în selectarea dintr-un set dat de date. De exemplu, suntem minunați 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, imaginați-vă că aveți trei ipoteze diferite pentru creșterea veniturilor anul viitor: 5%, 12% și 18%. Folosind formula ALEGE puteți restitui 12% dacă spuneți Excel că doriți alegerea # 2.

Citiți mai multe despre analiza scenariilor în Excel Analiza scenariilor Analiza scenariilor este o tehnică utilizată pentru a analiza deciziile prin specularea diferitelor rezultate posibile în investițiile financiare. În modelarea financiară, aceasta.

Funcția ALEGE

Pentru a vedea o demonstrație video, consultați cursul nostru avansat de formule Excel.

5. XNPV și XIRR

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

Dacă sunteți un analist care lucrează în domeniul investițiilor bancare Calea carierei în domeniul investițiilor bancare Ghidul carierei în domeniul investițiilor bancare - planificați-vă cariera IB Aflați despre salariile bancare de investiții, cum să vă angajați și ce să faceți după o carieră în IB. Divizia de investiții bancare (IBD) ajută guvernele, corporațiile și instituțiile să strângă capital și să finalizeze fuziunile și achizițiile (M&A). , cercetare de capital, planificare și analiză financiară (FP&A FP&A Analyst Deveniți analist FP&A la o corporație. Vă prezentăm salariul, abilitățile, personalitatea și pregătirea de care aveți nevoie pentru locuri de muncă FP&A și o carieră financiară de succes. Analiștii FP&A, managerii și directorii sunt responsabil pentru furnizarea directorilor cu analiza și informațiile de care au nevoie) sau orice alt domeniu de finanțare corporativă care necesită reducerea fluxurilor de numerar,atunci aceste formule sunt un salvator!

Pur și simplu, XNPV și XIRR vă permit să aplicați date specifice fiecărui flux de numerar individual care este actualizat. Problema cu formulele de bază NPV și IRR ale Excel este că presupun că perioadele de timp dintre fluxul de numerar sunt egale. În mod obișnuit, ca analist, veți avea situații în care fluxurile de numerar nu sunt temporizate în mod egal, iar această formulă este modul în care remediați acest lucru.

Formula de finanțare avansată XNPV în Excel

Pentru o defalcare mai detaliată, consultați ghidul nostru gratuit de formule IRR vs XIRR XIRR vs IRR De ce să folosiți XIRR vs IRR. XIRR atribuie date specifice fiecărui flux de numerar individual, făcându-l mai precis decât IRR atunci când construiește un model financiar în Excel. precum și ghidul nostru XNPV.

6. SUMIF și COUNTIF

Formula: = COUNTIF (D5: D12, ”> = 21”)

Aceste două formule avansate sunt mari utilizări ale funcțiilor condiționale. SUMIF adaugă toate celulele care îndeplinesc anumite criterii, iar COUNTIF numără toate celulele care îndeplinesc anumite criterii. De exemplu, imaginați-vă că doriți să numărați toate celulele mai mari sau egale cu 21 (vârsta legală de băut în SUA) pentru a afla de câte sticle de șampanie aveți nevoie pentru un eveniment client. Puteți utiliza COUNTIF ca soluție avansată, așa cum se arată în captura de ecran de mai jos.

Formula COUNTIF

În cursul nostru avansat Excel, descompunem aceste formule și mai detaliat.

7. PMT și IPMT

Formula: = PMT (rata dobânzii, numărul de perioade, valoarea actualizată)

Dacă lucrați în domeniul bancar comercial Profil de carieră în sectorul bancar comercial. O carieră în domeniul comerțului comercial oferă clienților produse de credit, cum ar fi împrumuturi pe termen lung, linii de credit revolving, facilități sindicalizate, servicii de gestionare a numerarului și alte produse cu venit fix. În calitate de analist de credit sau manager de cont, oferiți sfaturi financiare, imobiliare, FP&A FP&A Financial Planning and Analysis (FP&A) este o funcție importantă la o corporație. Profesioniștii FP&A sprijină luarea deciziilor executive pentru CEO, CFO și Consiliul de Administrație cu planificare, analiză și modelare. Aflați ce presupune slujba unui analist, manager sau director FP&A - salariu, cerințe, educație, abilități sau orice poziție de analist financiar care se ocupă de graficele datoriilor, veți dori să înțelegeți aceste două formule detaliate.

Formula PMT vă oferă valoarea plăților egale pe durata de viață a unui împrumut. Îl puteți utiliza împreună cu IPMT (care vă indică plățile dobânzii pentru același tip de împrumut), apoi separați plățile de principal și dobânzi.

Iată un exemplu de utilizare a funcției PMT pentru a obține plata ipotecară lunară pentru o ipotecă de 1 milion de dolari la 5% timp de 30 de ani.

8. LEN și TRIM

Formule: = LEN (text) și = TRIM (text)

Acestea sunt formule puțin mai puțin frecvente, dar cu siguranță foarte sofisticate. Aceste aplicații sunt excelente pentru analiștii financiari Ghidul Analist Trifecta® Ghidul final pentru a fi un analist financiar de talie mondială. Vrei să fii un analist financiar de talie mondială? Căutați să urmați cele mai bune practici de top din industrie și să ieșiți din mulțime? Procesul nostru, numit The Analyst Trifecta® constă în analize, prezentări și abilități ușoare care trebuie să organizeze și să manipuleze cantități mari de date. Din păcate, datele pe care le obținem nu sunt întotdeauna perfect organizate și uneori pot apărea probleme precum spații suplimentare la începutul sau la sfârșitul celulelor

În exemplul de mai jos, puteți vedea cum formula TRIM curăță datele Excel.

Advanced Advanced - TRIM

9. CONCATENAT

Formula: = A1 și „mai mult text”

Concatenarea nu este într-adevăr o funcție pe cont propriu - este doar un mod inovator de a uni informații din diferite celule și de a face foile de lucru mai dinamice. Acesta este un instrument foarte puternic pentru analiștii financiari care efectuează modelarea financiară (consultați ghidul nostru gratuit de modelare financiară Ghidul gratuit de modelare financiară Acest ghid de modelare financiară acoperă sfaturile Excel și cele mai bune practici cu privire la ipoteze, factori, prognoză, conectarea celor trei declarații, analiza DCF, mai Aflați mai multe).

În exemplul de mai jos, puteți vedea cum textul „New York” plus „,„ este asociat cu „NY” pentru a crea „New York, NY”. Acest lucru vă permite să creați anteturi dinamice și etichete în foile de lucru. Acum, în loc să actualizați direct celula B8, puteți actualiza celulele B2 și D2 independent. Cu un set mare de date, aceasta este o abilitate valoroasă pe care o aveți la dispoziție.

Concatenează formula Excel Exemplu

10. Funcții CELL, STÂNGA, MID și DREPT

Aceste funcții avansate Excel pot fi combinate pentru a crea câteva formule foarte avansate și complexe de utilizat. Funcția CELL poate returna o varietate de informații despre conținutul unei celule (cum ar fi numele, locația, rândul, coloana și multe altele). Funcția STÂNGA poate returna text de la începutul unei celule (de la stânga la dreapta), MID returnează text din orice punct de pornire al celulei (de la stânga la dreapta), iar DREAPTA returnează text de la sfârșitul celulei (de la dreapta la stânga).

Mai jos este o ilustrare a acestor trei formule în acțiune.

Pentru a vedea cum acestea pot fi combinate într-un mod puternic cu funcția CELL, o descompunem pas cu pas în clasa noastră de formule Excel avansate.

Formule CELULARE, STÂNGA, MIDI și DREAPTA

Mai multe formule Excel de formare

Sperăm că aceste 10 formule avansate Excel au fost utile pentru dvs. Acestea ar trebui să parcurgă un drum lung spre îmbunătățirea abilităților de analiză financiară și de modelare financiară Abilități de modelare financiară Aflați cele mai importante 10 abilități de modelare financiară și ce este necesar pentru a fi bun la modelarea financiară în Excel. Cele mai importante abilități: contabilitate.

Mai jos sunt mai multe resurse financiare pentru a vă ajuta să deveniți un utilizator cu experiență Excel:

  • Foaie de cheat cu formule Excel Foaie de cheat cu formule Excel Finanțe cu foile de calcul Excel vă vor oferi toate cele mai importante formule pentru a efectua analize financiare și modelări în foi de calcul Excel. Dacă doriți să deveniți un maestru al analizei financiare Excel și un expert în construirea de modele financiare, atunci ați ajuns în locul potrivit.
  • Comenzi rapide de la tastatură Excel Comenzi rapide Excel PC Comenzi rapide Excel - Listă dintre cele mai importante și comune comenzi rapide MS Excel pentru utilizatorii de PC și Mac, finanțe, profesii contabile. Comenzile rapide de la tastatură vă accelerează abilitățile de modelare și economisiți timp. Aflați editarea, formatarea, navigarea, panglica, lipirea specială, manipularea datelor, editarea formulelor și celulelor și alte deficiențe
  • Curs gratuit Excel Excel
  • Curs avansat Excel
  • Excel pentru modelare financiară

Postări recente