Chaque analyste financier Le guide Analyst Trifecta® Le guide ultime pour devenir un analyste financier de classe mondiale. Voulez-vous être un analyste financier de classe mondiale? Cherchez-vous à suivre les meilleures pratiques de l'industrie et à vous démarquer de la foule? Notre processus, appelé The Analyst Trifecta®, se compose d'analyses, de présentations et de compétences générales et passe plus de temps dans Excel qu'ils ne veulent l'admettre. Basés sur des années et des années d'expérience, nous avons compilé les formules Excel les plus importantes et les plus avancées que tout analyste financier de classe mondiale doit connaître.
1. MATCH INDEX
Formule: = INDEX (C3: E9, MATCH (B13, C3: C9,0), MATCH (B14, C3: E3,0))
C'est une alternative avancée aux formules RECHERCHEV ou RECHERCHEH (qui présentent plusieurs inconvénients et limitations). INDEX MATCH Index Match Formula La combinaison des fonctions INDEX et MATCH est une formule de recherche plus puissante que VLOOKUP. Apprenez à utiliser INDEX MATCH dans ce didacticiel Excel. Index renvoie la valeur d'une cellule dans un tableau en fonction de la colonne et du numéro de ligne et Match renvoie la position d'une cellule dans une ligne ou une colonne. Apprenez à les utiliser dans ce guide est une puissante combinaison de formules Excel qui fera passer votre analyse financière et votre modélisation financière au niveau supérieur.
INDEX renvoie la valeur d'une cellule dans un tableau en fonction de la colonne et du numéro de ligne.
MATCH renvoie la position d'une cellule dans une ligne ou une colonne.
Voici un exemple des formules INDEX et MATCH combinées. Dans cet exemple, nous recherchons et renvoyons la taille d'une personne en fonction de son nom. Puisque le nom et la hauteur sont tous deux des variables dans la formule, nous pouvons les changer tous les deux!
Index Match Formula La combinaison des fonctions INDEX et MATCH est une formule de recherche plus puissante que VLOOKUP. Apprenez à utiliser INDEX MATCH dans ce didacticiel Excel. Index renvoie la valeur d'une cellule dans un tableau en fonction de la colonne et du numéro de ligne et Match renvoie la position d'une cellule dans une ligne ou une colonne. Apprenez à les utiliser dans ce guide
Pour une explication étape par étape ou comment utiliser cette formule, veuillez consulter notre guide gratuit sur la façon d'utiliser INDEX MATCH dans Excel Index Match Formula La combinaison des fonctions INDEX et MATCH est une formule de recherche plus puissante que VLOOKUP. Apprenez à utiliser INDEX MATCH dans ce didacticiel Excel. Index renvoie la valeur d'une cellule dans un tableau en fonction de la colonne et du numéro de ligne et Match renvoie la position d'une cellule dans une ligne ou une colonne. Apprenez à les utiliser dans ce guide.
2. SI combiné avec AND / OR
Formule: = SI (ET (C2> = C4, C2 <= C5), C6, C7)
Quiconque a passé beaucoup de temps à faire différents types de modèles financiers Types de modèles financiers Les types de modèles financiers les plus courants sont: le modèle à 3 relevés, le modèle DCF, le modèle M&A, le modèle LBO, le modèle budgétaire. Découvrez le top 10 des types sachant que les formules IF imbriquées peuvent être un cauchemar. Combiner IF avec la fonction AND ou OR peut être un excellent moyen de garder les formules plus faciles à auditer et plus faciles à comprendre pour les autres utilisateurs. Dans l'exemple ci-dessous, vous verrez comment nous avons utilisé les fonctions individuelles en combinaison pour créer une formule plus avancée.
Pour une description détaillée de la façon d'exécuter cette fonction dans Excel, veuillez consulter notre guide gratuit sur la façon d'utiliser IF avec AND / OR IF Statement Between Two Numbers Téléchargez ce modèle gratuit pour une instruction IF entre deux nombres dans Excel. Dans ce didacticiel, nous vous montrons étape par étape comment calculer IF avec l'instruction AND. Apprenez à créer une instruction IF qui testera si une cellule contient une valeur entre deux nombres, puis produira le résultat souhaité si cette condition est remplie. 
Instruction IF entre deux nombres Téléchargez ce modèle gratuit pour une instruction IF entre deux nombres dans Excel. Dans ce didacticiel, nous vous montrons étape par étape comment calculer IF avec l'instruction AND. Apprenez à créer une instruction IF qui testera si une cellule contient une valeur entre deux nombres, puis produira le résultat souhaité si cette condition est remplie
3. OFFSET combiné avec SUM ou MOYENNE
Formule: = SOMME (B4: OFFSET (B4,0, E2-1))
La fonction OFFSET Fonction OFFSET La fonction OFFSET est classée sous les fonctions de recherche et de référence Excel. OFFSET renverra une plage de cellules. Autrement dit, il renverra un nombre spécifié de lignes et de colonnes à partir d'une plage initiale qui a été spécifiée. en soi, ce n'est pas particulièrement avancé, mais lorsque nous le combinons avec d'autres fonctions comme SUM ou AVERAGE, nous pouvons créer une formule assez sophistiquée. Supposons que vous souhaitiez créer une fonction dynamique pouvant additionner un nombre variable de cellules. Avec la formule SOMME régulière, vous êtes limité à un calcul statique, mais en ajoutant OFFSET, vous pouvez déplacer la référence de cellule.
Comment cela fonctionne: Pour que cette formule fonctionne, nous remplaçons la cellule de référence de fin de la fonction SOMME par la fonction OFFSET. Cela rend la formule dynamique et la cellule référencée E2 est l'endroit où vous pouvez indiquer à Excel le nombre de cellules consécutives que vous souhaitez additionner. Maintenant, nous avons des formules Excel avancées!
Ci-dessous, une capture d'écran de cette formule légèrement plus sophistiquée en action.
Comme vous le voyez, la formule SOMME commence dans la cellule B4, mais elle se termine par une variable, qui est la formule OFFSET commençant à B4 et se poursuivant par la valeur dans E2 («3»), moins un. Cela déplace la fin de la formule de somme sur 2 cellules, additionnant 3 années de données (y compris le point de départ). Comme vous pouvez le voir dans la cellule F7, la somme des cellules B4: D4 est de 15, ce que nous donne la formule de décalage et de somme.
Apprenez à créer cette formule étape par étape dans notre cours avancé sur Excel.
4. CHOISISSEZ
Formule: = CHOISIR (choix, option1, option2, option3)
La fonction CHOOSE Fonction CHOOSE La fonction CHOOSE est classée sous les fonctions de recherche et de référence Excel. Il renverra une valeur d'un tableau correspondant au numéro d'index fourni. La fonction renverra la nième entrée dans une liste donnée. En tant qu'analyste financier, la fonction CHOISIR est utile pour sélectionner à partir d'un ensemble de données donné. Par exemple, nous sommes parfaits pour l'analyse de scénarios dans la modélisation financière. Il vous permet de choisir entre un nombre spécifique d'options et de renvoyer le «choix» que vous avez sélectionné. Par exemple, imaginez que vous ayez trois hypothèses différentes pour la croissance des revenus l'année prochaine: 5%, 12% et 18%. En utilisant la formule CHOOSE, vous pouvez retourner 12% si vous indiquez à Excel que vous voulez le choix n ° 2.
En savoir plus sur l'analyse de scénario dans Excel Analyse de scénario L'analyse de scénario est une technique utilisée pour analyser les décisions en spéculant divers résultats possibles dans les investissements financiers. Dans la modélisation financière, ceci.
Pour voir une démonstration vidéo, consultez notre cours Advanced Excel Formulas.
5. XNPV et XIRR
Formule: = XNPV (taux d'actualisation, flux de trésorerie, dates)
Si vous êtes un analyste travaillant dans la banque d'investissement Parcours de carrière dans la banque d'investissement Guide de carrière dans la banque d'investissement - planifiez votre parcours professionnel à l'IB. Découvrez les salaires des banques d'investissement, comment être embauché et ce qu'il faut faire après une carrière à l'IB. La division des services bancaires d'investissement (IBD) aide les gouvernements, les entreprises et les institutions à lever des capitaux et à réaliser des fusions et acquisitions (M&A). , recherche sur les actions, planification et analyse financières (FP&A FP&A Analyst Devenez analyste FP&A dans une société. Nous décrivons le salaire, les compétences, la personnalité et la formation dont vous avez besoin pour les emplois FP&A et une carrière financière réussie. responsable de fournir aux dirigeants l'analyse et les informations dont ils ont besoin), ou tout autre domaine de la finance d'entreprise nécessitant une actualisation des flux de trésorerie,alors ces formules sont une bouée de sauvetage!
En termes simples, XNPV et XIRR vous permettent d'appliquer des dates spécifiques à chaque flux de trésorerie individuel actualisé. Le problème avec les formules NPV et TRI de base d'Excel est qu'elles supposent que les périodes entre les flux de trésorerie sont égales. En tant qu'analyste, vous aurez régulièrement des situations où les flux de trésorerie ne sont pas synchronisés de manière uniforme, et cette formule vous permet de résoudre ce problème.
Pour une ventilation plus détaillée, consultez notre guide gratuit des formules IRR vs XIRR XIRR vs IRR Pourquoi utiliser XIRR vs IRR. XIRR attribue des dates spécifiques à chaque flux de trésorerie individuel, ce qui le rend plus précis que l'IRR lors de la création d'un modèle financier dans Excel. ainsi que notre guide XNPV.
6. SUMIF et COUNTIF
Formule: = COUNTIF (D5: D12, "> = 21")
Ces deux formules avancées sont de grandes utilisations des fonctions conditionnelles. SUMIF ajoute toutes les cellules qui répondent à certains critères et COUNTIF compte toutes les cellules qui répondent à certains critères. Par exemple, imaginons que vous souhaitiez compter toutes les cellules supérieures ou égales à 21 ans (l'âge légal pour boire aux États-Unis) pour connaître le nombre de bouteilles de champagne dont vous avez besoin pour un événement client. Vous pouvez utiliser COUNTIF comme solution avancée, comme indiqué dans la capture d'écran ci-dessous.
Dans notre cours avancé sur Excel, nous décomposons ces formules de manière encore plus détaillée.
7. PMT et IPMT
Formule: = PMT (taux d'intérêt, nombre de périodes, valeur actuelle)
Si vous travaillez dans la banque commerciale Profil de carrière dans la banque commerciale Un cheminement de carrière dans la banque commerciale consiste à fournir aux clients des produits de crédit tels que des prêts à terme, des marges de crédit renouvelables, des facilités syndiquées, des services de gestion de trésorerie et d'autres produits à revenu fixe. En tant qu'analyste de crédit ou directeur de compte, vous fournissez des conseils financiers, l'immobilier, FP&A FP&A Financial Planning and Analysis (FP&A) est une fonction importante dans une entreprise. Les professionnels de la FP&A soutiennent la prise de décision exécutive pour le PDG, le directeur financier et le conseil d'administration par la planification, l'analyse et la modélisation. Apprenez ce qu'implique le travail d'un analyste, d'un gestionnaire ou d'un directeur FP&A - salaire, exigences, formation, compétences ou tout poste d'analyste financier qui traite des échéanciers de la dette, vous voudrez comprendre ces deux formules détaillées.
La formule PMT vous donne la valeur des paiements égaux sur la durée d'un prêt. Vous pouvez l'utiliser en conjonction avec l'IPMT (qui vous indique les paiements d'intérêts pour le même type de prêt), puis séparer les paiements du principal et des intérêts.
Voici un exemple d'utilisation de la fonction PMT pour obtenir le paiement hypothécaire mensuel d'une hypothèque de 1 million de dollars à 5% pendant 30 ans.
8. LEN et TRIM
Formules: = LEN (texte) et = TRIM (texte)
Ce sont des formules un peu moins courantes, mais certainement très sophistiquées. Ces applications sont idéales pour les analystes financiers. Le guide Analyst Trifecta® Le guide ultime pour devenir un analyste financier de classe mondiale. Voulez-vous être un analyste financier de classe mondiale? Cherchez-vous à suivre les meilleures pratiques de l'industrie et à vous démarquer de la foule? Notre processus, appelé The Analyst Trifecta®, comprend des analyses, des présentations et des compétences générales qui ont besoin d'organiser et de manipuler de grandes quantités de données. Malheureusement, les données que nous obtenons ne sont pas toujours parfaitement organisées et parfois il peut y avoir des problèmes comme des espaces supplémentaires au début ou à la fin des cellules
Dans l'exemple ci-dessous, vous pouvez voir comment la formule TRIM nettoie les données Excel.
9. CONCATENER
Formule: = A1 & "plus de texte"
Concaténer n'est pas vraiment une fonction en soi - c'est juste un moyen innovant de joindre des informations de différentes cellules et de rendre les feuilles de calcul plus dynamiques. Il s'agit d'un outil très puissant pour les analystes financiers effectuant de la modélisation financière (voir notre guide gratuit de modélisation financière Guide de modélisation financière gratuit apprendre encore plus).
Dans l'exemple ci-dessous, vous pouvez voir comment le texte «New York» plus «,» est joint à «NY» pour créer «New York, NY». Cela vous permet de créer des en-têtes et des étiquettes dynamiques dans des feuilles de calcul. Désormais, au lieu de mettre à jour directement la cellule B8, vous pouvez mettre à jour les cellules B2 et D2 indépendamment. Avec un ensemble de données volumineux, c'est une compétence précieuse à mettre à votre disposition.
10. Fonctions CELL, LEFT, MID et RIGHT
Ces fonctions Excel avancées peuvent être combinées pour créer des formules très avancées et complexes à utiliser. La fonction CELL peut renvoyer diverses informations sur le contenu d'une cellule (comme son nom, son emplacement, sa ligne, sa colonne, etc.). La fonction GAUCHE peut renvoyer du texte depuis le début d'une cellule (de gauche à droite), MID renvoie du texte à partir de n'importe quel point de départ de la cellule (de gauche à droite) et RIGHT renvoie du texte de la fin de la cellule (de droite à gauche).
Voici une illustration de ces trois formules en action.
Pour voir comment ceux-ci peuvent être combinés de manière puissante avec la fonction CELL, nous le décomposons pour vous étape par étape dans notre classe de formules Excel avancées.
Plus de formation sur les formules Excel
Nous espérons que ces 10 formules Excel avancées vous ont été utiles. Ils devraient grandement améliorer vos compétences en analyse financière et en modélisation financière Compétences en modélisation financière Apprenez les 10 compétences les plus importantes en modélisation financière et ce qui est nécessaire pour être bon en modélisation financière dans Excel. Compétences les plus importantes: comptabilité.
Vous trouverez ci-dessous d'autres ressources financières pour vous aider à devenir un utilisateur avancé d'Excel:
- Aide-mémoire pour les formules Excel Feuille de triche pour les formules Excel La feuille de triche pour les formules Excel de Finance vous donnera toutes les formules les plus importantes pour effectuer une analyse et une modélisation financières dans des feuilles de calcul Excel. Si vous souhaitez devenir un maître de l'analyse financière Excel et un expert en création de modèles financiers, vous êtes au bon endroit.
- Raccourcis clavier Excel Raccourcis Excel Raccourcis PC Mac Excel - Liste des raccourcis MS Excel les plus importants et les plus courants pour les utilisateurs de PC et Mac, les professions financières et comptables. Les raccourcis clavier accélèrent vos compétences en modélisation et vous font gagner du temps. Apprenez l'édition, la mise en forme, la navigation, le ruban, le collage spécial, la manipulation de données, l'édition de formules et de cellules et d'autres raccourcis
- Cours accéléré Excel gratuit
- Cours Excel avancé
- Excel pour la modélisation financière
