Tutoriel Excel : fonctions avancées
Rédigé par Hanane Mouqqadim, Publié le 16 Mai 2019, Mise à jour le Lundi, 28 Décembre 2020 15:20![](/images/thumb/29/id-3204.png)
Chaque analyste financier passe plus de temps dans Excel qu'il ne le souhaite. En se basant sur leurs utilisations, j’ai compilé les formules Excel les plus importantes et les plus avancées que tous les analystes financiers de classe mondiale doivent connaître. Ce tutoriel peut être votre point d’entrée pour l’apprentissage des fonctions Microsoft Excel. Il répertorie les fonctions essentielles du texte, mathématiques, statistiques, logiques et autres, ainsi que des exemples de formules et des liens vers des ressources connexes.
Excel fournit un nombre énorme de fonctions pour analyser, auditer et calculer des données. Certains d’entre eux sont utilisés quotidiennement par de nombreux utilisateurs d’Excel, alors que d’autres sont tellement spécifiques que seul un petit groupe de spécialistes des finances, des statistiques ou de l’ingénierie en comprend et a besoin.
Dans ce tutoriel, nous n’avons pas pour objectif d’explorer tout et chaque fonction disponible dans Microsoft Excel. Sur cette page, vous trouverez les fonctions Excel les plus utiles et les plus utilisées, que nous avons déjà traitées en détail dans des tutoriels individuels, étape par étape.
La maîtrise des formules Excel peut prendre des années par essais et erreurs, en ramassant des conseils de collègues ou en cherchant des informations qu’on peut trouver sur le web. C’est un processus lent avec des résultats incohérents. J’ai conçu ce tutoriel pour vous aider à vous familiariser rapidement avec les fonctions qui vont vous donner les plus grands gains d'efficacité. La plupart des fonctions sont considérées comme avancées, mais lorsque vous allez terminer ce tutoriel, vous saurez comme je le fais, que tout est facile une fois que vous savez comment.
La plupart des fonctions répertoriées ci-dessous sont des fonctions de feuille de calcul Excel utilisées dans les formules d'une cellule. Sélectionnez une catégorie pour accéder à la liste des fonctions avec une brève description et des exemples d'utilisation.
Table des matières
- Fonction TEXTE
- Fonction CONCATENER
- Fonction SUPPRESPACE
- Fonction SUBSTITUE
- Fonction CNUM
- Fonction EXACT
- Fonctions pour changer la casse du texte (MAJUSCULE, MINUSCULE, NOMPROPRE)
- Extraire les caractères du texte (CELLULE, GAUCHE, DROITE, STXT)
- La fonction ESTLOGIQUE
- La fonction ESTVIDE
- Les fonctions ET, OU, OUX
- La fonction NON
- La fonction SI
- Fonctions SIERREUR et SI.NON.DISP
- Trouver la somme des cellules
- Générer des nombres aléatoires (ALEA et ALEA.ENTRE.BORNES)
- Les fonctions d'arrondi
- Obtenir le reste après la division (fonction MOD)
- Fonction RECHERCHEV
- La fonction INDEX
- La fonction EQUIV
- Correspondance d'index
- La fonction INDIRECT
- La fonction DECALER
- DECALER combiné avec SOMME ou MOYENNE
- La fonction CHOISIR
- La fonction TRANSPOSE
- La fonction LIEN_HYPERTEXTE
- Création de dates
- Date et heure actuelles
- Extraction de dates et de composants de date
- Calculer la différence de date
- Calculer les jours de travail
- Fonctions de temps Excel
- Création de formules complexes avec des constantes et des opérateurs mathématiques
- Les formules Excel avec fonctions imbriquées
Nous savons tous que les gens doivent faire beaucoup de travail ces temps-ci et qu'ils veulent le faire intelligemment pour que leur temps soit économisé et que leur travail soit également effectué de manière organisée. Par conséquent, de nos jours, les utilisateurs travaillent sur MS Excel pour pouvoir effectuer leur travail facilement et gagner du temps. MS Excel propose de nombreuses fonctions et formules qui facilitent le travail et vous aident à stocker de grandes quantités de données à un endroit spécifié. Tout le monde peut travailler sur MS Excel de n’importe où et à partir de n’importe quel appareil. MS Excel fait désormais partie de la vie de tous, qu’il s’agisse d’un homme d’affaires, d’un employé ou d’un simple étudiant. Pour utiliser MS Excel de la meilleure façon possible, il est important que vous connaissiez ses formules avancées ainsi que leur utilisation.
Les formules Microsoft Excel - les bases
Dans MS Excel, les formules sont des équations qui effectuent divers calculs dans vos feuilles de calcul. Bien que Microsoft ait introduit une poignée de nouvelles fonctions au fil des ans, le concept de formules de feuille de calcul Excel est le même dans toutes les versions d'Excel 2016, Excel 2013, Excel 2010, Excel 2007 et versions antérieures.
Toutes les formules Excel commencent par un signe égal (=).
Après le symbole égal, vous entrez un calcul ou une fonction. Par exemple, pour ajouter des valeurs contenues dans les cellules B1 à B5, vous pouvez :
Tapez l'équation entière : = B1 + B2 + B3 + B4 + B5
Ou utilisez la fonction SOMME : = SOMME (B1: B5)
Appuyez sur la touche Entrée pour compléter la formule. Terminé !
Eléments de formules Microsoft Excel
Lorsque vous créez une formule dans Excel, vous pouvez utiliser différents éléments pour fournir les données source à la formule et indiquer les opérateurs à utiliser pour ces données. Selon le type de formule que vous créez, il peut inclure tout ou partie des éléments suivants :
Constantes : nombres ou valeurs de texte que vous entrez directement dans une formule, comme = 2 * 3.
Références de cellule : référence à une cellule contenant la valeur que vous souhaitez utiliser dans votre formule Excel, par exemple.
= Somme (A1 ; A2 ; B5).
Pour faire référence à des données dans deux cellules contiguës ou plus, utilisez une référence de plage telle que A1: A5. Par exemple, pour additionner les valeurs de toutes les cellules entre A1 et A5 inclus, utilisez cette formule : = SOMME (A1: A5).
Noms : nom défini pour une plage de cellules, une constante, une table ou une fonction, par exemple = SOMME (nom_mon_source).
Fonctions : formules prédéfinies dans Excel qui effectuent des calculs en utilisant les valeurs fournies dans leurs arguments.
Opérateurs : symboles spéciaux spécifiant le type d'opération ou de calcul à effectuer.
Fonctions de texte Excel (fonctions de chaîne)
Il existe de nombreuses fonctions Microsoft Excel permettant de manipuler des chaînes de texte. Voici les plus essentiels :
Fonction TEXTE
TEXTE (valeur ; format_texte) est utilisé pour convertir un nombre ou une date en une chaîne de texte au format spécifié, où :
La valeur est une valeur numérique que vous souhaitez convertir en texte.
Format_texte est le format souhaité.
Les formules suivantes illustrent la fonction Excel TEXTE en action :
=TEXTE(A2;"mm/jj/aaaa")- convertit une date de la cellule A1 en une chaîne de texte au format de date américain traditionnel, telle que "01/01/2015" (mois / jour / année).
=TEXTE(A4;"###0,00€")- convertit un nombre en A1 en chaîne de texte monétaire telle que "3,00 €".
Fonction CONCATENER
Concaténer n'est pas une fonction en soi, c'est simplement une façon innovante de réunir des informations provenant de différentes cellules et de rendre les feuilles de calcul plus dynamiques. Ceci est un outil très puissant pour les analystes financiers effectuant la modélisation financière.
Cette fonction avancée Excel est l’une des formules pouvant être utilisées avec plusieurs variantes. Cette formule Excel avancée nous aide à joindre plusieurs chaînes de texte en une seule.
CONCATENER (texte1, [texte2],…) permet de joindre plusieurs morceaux de texte ou de combiner les valeurs de plusieurs cellules en une seule. Un résultat analogue peut être obtenu en utilisant l’opérateur Excel &, comme illustré dans la capture d'écran suivante.
Fonction SUPPRESPACE
SUPPRESPACE (texte) supprime les espaces de début et de fin ainsi que les espaces en excès entre les mots. Où texte est soit une chaîne de texte, soit une référence à la cellule contenant le texte à partir duquel vous souhaitez supprimer des espaces. La capture d'écran suivante illustre un exemple d'utilisation :
Fonction SUBSTITUE
SUBSTITUE (texte ; ancien_texte, nouveau_texte ; [no_position]) remplace un jeu de caractères par un autre dans une cellule spécifiée ou une chaîne de texte. La syntaxe de la fonction SUBSTITUE est la suivante :
Texte - la chaîne de texte d'origine ou la référence à une cellule où vous souhaitez remplacer certains caractères.
ancien_texte - les caractères que vous souhaitez remplacer.
Nouveau_texte - les caractères avec lesquels vous souhaitez remplacer l'ancien texte.
No_position - paramètre facultatif qui spécifie l'occurrence de « ancien_texte » que vous souhaitez remplacer par nouveau_text. Si omis, chaque occurrence de l'ancien texte sera remplacée par le nouveau texte.
Par exemple, la formule SUBSTITUTE suivante remplace toutes les virgules de la cellule A2 par des points-virgules :
=SUBSTITUE (A2 ;"," ; ";")
Fonction CNUM
CNUM (texte) - convertit une chaîne de texte en nombre.
Cette fonction est très utile pour convertir des valeurs au format texte représentant les nombres en nombres pouvant être utilisés dans d’autres formules et calculs Excel.
Fonction EXACT
EXACT (text1 ; text2) compare deux chaînes de texte et renvoie la valeur VRAI si les deux valeurs sont exactement identiques, y compris la casse, FAUX sinon.
Par exemple, si A2 correspond à "pommes" et B2 à "Pommes", la formule =EXACT(A2 ; B2) renverra FAUX, car elles ne correspondent pas exactement.
La fonction EXACT est rarement utilisée seule, mais elle est utile dans des tâches plus complexes telles que la réalisation d'une opération RECHERCHEV sensible à la casse dans Excel.
Fonctions pour changer la casse du texte (MAJUSCULE, MINUSCULE, NOMPROPRE)
Microsoft Excel fournit 3 fonctions de texte pour convertir les majuscules, minuscules et majuscules.
MAJUSCULE (text) - convertit tous les caractères d'une chaîne de texte spécifiée en majuscules.
MINUSCULE (text) - modifie toutes les lettres majuscules d'une chaîne de texte en minuscules.
NOMPROPRE (texte) - met en majuscule la première lettre de chaque mot et convertit toutes les autres lettres en minuscules (plus précisément, il met en majuscule les lettres qui suivent un caractère autre qu'une lettre).
Dans les trois fonctions, l'argument de texte peut être une chaîne de texte entourée de guillemets, une référence à une cellule contenant le texte ou une formule qui renvoie le texte.
Extraire les caractères du texte (CELLULE, GAUCHE, DROITE, STXT)
Ces fonctions Excel avancées peuvent être combinées pour créer des formules très avancées et complexes à utiliser. La fonction CELLULE peut renvoyer diverses informations sur le contenu d'une cellule (telles que son nom, son emplacement, sa ligne, sa colonne, etc.). La fonction GAUCHE peut renvoyer du texte du début d'une cellule (de gauche à droite), STXT renvoie du texte de n'importe quel point de départ de la cellule (de gauche à droite) et DROITE renvoie du texte de la fin de la cellule (de droite à gauche).
Nous pouvons utiliser cette formule Excel avancée si nous voulons extraire une certaine sous-chaîne d'une chaîne donnée. Les formules mentionnées pourraient être utilisées en fonction de nos besoins Si vous avez besoin d’une formule pour renvoyer un certain nombre de caractères d’une chaîne de texte, utilisez l’une des fonctions Excel suivantes :
GAUCHE (texte ; [no_car]) - renvoie un nombre spécifié de caractères à partir du début d'une chaîne de texte.
DROITE (texte ; [no_car]) - renvoie un nombre spécifié de caractères à partir de la fin d'une chaîne de texte.
STXT (texte ; no_départ ; no_car) - renvoie un nombre spécifique de caractères d'une chaîne de texte, commençant à n'importe quelle position spécifiée.
Dans ces fonctions, vous fournissez les arguments suivants :
Texte - une chaîne de texte ou une référence à une cellule contenant les caractères à extraire.
no_départ - indique par où commencer (c'est-à-dire la position du premier caractère à extraire).
No_car - le nombre de caractères que vous souhaitez extraire.
Les fonctions logiques dans Excel
Microsoft Excel fournit une poignée de fonctions logiques qui évaluent une ou plusieurs conditions spécifiées et renvoient la valeur correspondante.
La fonction ESTLOGIQUE
Cette fonction indique fondamentalement si la valeur d'une cellule spécifique est logique ou non. Il vérifie la valeur dérivée de la formule. Lorsque vous utiliserez cette formule, elle indiquera VRAI si la valeur est logique, sinon elle affichera FAUX. Supposons qu'il y ait une cellule vide ou que n'importe quelle valeur aléatoire soit écrite ; Cette formule affichera alors le résultat comme FAUX. La syntaxe de cette fonction est la suivante :
= ESTLOGIQUE (adresse de cellule)
La fonction ESTVIDE
Cette formule de MS Excel vous aide à déterminer si la cellule est vide ou non, que ce soit pour des valeurs alphabétiques ou des valeurs numériques. Il écrira FAUX si la cellule n'est pas vide et VRAIE si la cellule est vide. La syntaxe de cette commande est la suivante :
= ESTVIDE (adresse de cellule)
Les fonctions ET, OU, OUX
ET(Valeur_logique1 ; [Valeur_logique2] ; …) - renvoie VRAI si tous les arguments sont évalués à VRAI, sinon FAUX.
OU(Valeur_logique1 ; [Valeur_logique 2] ; …) - renvoie la valeur VRAI si au moins un des arguments est vrai.
OUX(Valeur_logique 1 ; [Valeur_logique 2] ;…) - renvoie un exclusif logique ou de tous les arguments. Cette fonction a été introduite dans Excel 2013 et n'est pas disponible dans les versions antérieures.
La fonction NON
NON (valeur_logique) - inverse une valeur de son argument, c'est-à-dire que si la logique est évaluée à FAUX, la fonction NON renvoie VRAI et vice versa.
Par exemple, les deux formules suivantes renverront FAUX :
=NON(VRAI)
=NON(2*2=4)
La fonction SI
Il existe de nombreux cas où il est nécessaire de créer des indicateurs basés sur certaines contraintes. Nous connaissons tous la syntaxe de base de SI. Nous utilisons cette fonction avancée Excel si pour créer un nouveau champ basé sur une contrainte sur un champ déjà existant. La fonction SI Excel est parfois appelée "fonction conditionnelle" car elle renvoie une valeur en fonction de la condition que vous spécifiez. La syntaxe de SI est la suivante :
SI (test_logique ; [valeur_si_vrai] ; [valeur_si_faux])
Une formule SI teste la ou les conditions exprimées dans l'argument « test_logique » et renvoie une valeur ( valeur_si_vrai) si la condition est remplie et une autre valeur ( valeur_si_faux) si la condition n'est pas remplie.
Par exemple, la formule =SI(A1<>"" ; "bon" ; "mauvais") renvoie "bon" s'il existe une valeur dans la cellule A1, "mauvais" sinon.
Et voici un exemple de formule SI imbriquée qui "déchiffre" le score de l'examen dans la cellule A2:
=SI(A2>80; "Brilliant"; SI(A2>50; "Bien"; SI(A2>30 ; "Passable" ; "Faible")))
Fonctions SIERREUR et SI.NON.DISP
Les deux fonctions permettent de vérifier si une formule donnée est considérée comme une erreur et, le cas échéant, les fonctions MS Excel renvoient à la place une valeur spécifiée.
SI.NON.DISP (valeur ; valeur_si_erreur) - vérifie si la formule ou l'expression est évaluée comme une erreur. Si tel est le cas, la formule retourne la valeur fournie dans l'argument valeur_si_erreur . Sinon, le résultat de la formule est renvoyé. Cette fonction traite toutes les erreurs Excel possibles, y compris VALEUR, N / A, NOM, REF, NUM, etc. Il est disponible dans Excel 2007 et supérieur.
SI.NON.DISP (valeur, valeur_si_na) - introduit dans Excel 2013, fonctionne de manière similaire à SIERREUR, mais ne traite que les erreurs # N / A.
Les fonctions mathématiques Excel
Excel a une tonne de fonctions de base et avancées pour effectuer des opérations mathématiques, calculer des exponentielles, des logarithmes, des factorielles et autres. Il faudrait plusieurs pages pour publier la liste des fonctions. Alors, ne discutons que de quelques fonctions mathématiques de base qui pourraient s’avérer utiles pour résoudre vos tâches quotidiennes.
Trouver la somme des cellules
Vous trouverez ci-dessous quatre fonctions Excel essentielles pour additionner les valeurs de cellules dans une plage spécifiée.
Fonction SOMME
SOMME (nombre1 : [numbre2] ;…) renvoie la somme de ses arguments. Les arguments peuvent être des nombres, des références de cellules ou des valeurs numériques basées sur des formules.
Par exemple, la formule mathématique la plus simple =SOMME(A1:A3 ; 1) additionne les valeurs des cellules A1, A2 et A3, puis ajoute 1 au résultat.
Fonctions SOMME.SI et SOMME.SI.ENS (somme conditionnelle)
Dans certaines analyses, il peut être nécessaire de filtrer certaines observations lors de l’application de la fonction somme ou NB. Dans de tels cas, ces deux fonction avancée Excel sont à notre secours. Il filtre toutes les observations en fonction de certaines conditions données dans ces formule Excel avancée et les résume.
Les deux fonctions additionnent les cellules dans une plage spécifiée répondant à une certaine condition. La différence est que SOMME.SI ne peut évaluer qu'un seul critère, alors que SOMME.SI.ENS, introduit dans Excel 2007, autorise plusieurs critères. S'il vous plaît faites attention que l'ordre des arguments est différent dans chaque fonction :
SOMME.SI (plage ; critères ; [somme_plage]) SOMME.SI.ENS (plage_somme ; plage_critère1 ; critère1, …)
plage / plage_critère - la plage de cellules à évaluer par les critères correspondants.
critères - la condition qui doit être remplie.
Somme_plage - les cellules à additionner si la condition est remplie.
La capture d'écran suivante donne une idée de la manière dont les fonctions SOMME.SI et SOMME.SI.ENS peuvent être utilisées sur des données réelles :
La fonction SOMMEPROD
SOMMEPROD (matrice1 ; matrice2,…) est l’une des rares fonctions Microsoft Excel à gérer les tableaux. Il multiplie les composants de tableau fournis et renvoie la somme des produits.
Générer des nombres aléatoires (ALEA et ALEA.ENTRE.BORNES)
Microsoft Excel fournit 2 fonctions pour générer des nombres aléatoires. Les deux sont des fonctions volatiles, ce qui signifie qu'un nouveau nombre est renvoyé chaque fois que la feuille de calcul calcule s.
ALEA () - renvoie un nombre réel aléatoire (décimal) compris entre 0 et 1.
ALEA.ENTRE.BORNES (min ; max) - renvoie un entier aléatoire entre les nombres en bas et en haut que vous spécifiez.
Les fonctions d'arrondi
Il existe une variété de fonctions pour arrondir les nombres dans Excel,
ARRONDI - arrondissez le nombre au nombre de chiffres spécifié.
ARRONDI.SUP - arrondissez le nombre au-dessus du nombre spécifié de chiffres.
ARRONDI.INF - arrondissez le nombre inférieur au nombre de chiffres spécifié.
ARRONDI.AU.MULTIPLE - arrondit le nombre à la hausse ou à la baisse au multiple spécifié.
PLANCHER - arrondissez le nombre jusqu'au multiple spécifié.
PLAFOND - arrondissez le nombre jusqu'au multiple spécifié.
ENT - arrondissez le nombre jusqu'au nombre entier le plus proche.
TRONQUE - tronque le nombre à un nombre spécifié de décimales.
PAIR - arrondissez le nombre jusqu'au nombre entier pair le plus proche.
IMPAIR - arrondissez le nombre au nombre entier impair le plus proche.
Obtenir le reste après la division (fonction MOD)
MOD (nombre, diviseur) renvoie le reste après la division de l'argument nombre par un diviseur.
Fonctions statistiques dans Excel
Parmi une variété de fonctions statistiques Excel très spécifiques, il y en a quelques unes que tout le monde peut comprendre et exploiter pour une analyse de données professionnelle.
Trouver les valeurs les plus grandes, les plus petites et moyennes
- MIN (nombre1 ; [nombre2] ;…) - renvoie la valeur minimale de la liste des arguments.
- MAX (nombre1 ; [nombre2] ;…) - renvoie la valeur maximale de la liste des arguments
- MOYENNE (nombre1 ; [nombre2] ;…) - renvoie la moyenne des arguments.
- PETITE.VALEUR (matrice ; k) - renvoie la k-ième valeur la plus petite du tableau.
- GRANDE.VALEUR (matrice ; k) - renvoie la k-ème valeur la plus grande du tableau.
La capture d'écran suivante illustre les fonctions statistiques de base en action.
Comptage de cellules
Vous trouverez ci-dessous une liste de fonctions Excel qui vous permettent de compter les cellules contenant un certain type de données ou basées sur les conditions que vous spécifiez.
NB (valeur1 ; [valeur2] ;…) - renvoie le nombre de valeurs numériques (nombres et dates) dans la liste des arguments.
NBVAL (valeur1 ; [valeur2] ;…) - renvoie le nombre de cellules non vides dans la liste des arguments. Il compte les cellules contenant toutes les informations, y compris les valeurs d'erreur et les chaînes de texte vides ("") renvoyées par d'autres formules.
NB.VIDE (range) - compte le nombre de cellules vides dans une plage spécifiée. Les cellules avec des chaînes de texte vides ("") sont également comptées comme des cellules vides.
NB.SI (plage, critère) - compte le nombre de cellules de la plage qui répondent aux critères spécifiés.
NB.SI.ENS (plage_critère1 ; critère1, ; [plage_critère2 ; critère2]…) - compte le nombre de cellules qui répondent à tous les critères spécifiés.
La capture d'écran suivante montre les fonctions de comptage de cellules en action :
Remarque : Comme les dates sont stockées sous forme de chiffres dans Excel, elles sont également comptabilisées par les fonctions statistiques d'Excel. Par exemple, la formule
=NB.SI (A2:A9 ; ">5") compte la date dans la cellule A8 de la capture d'écran ci-dessus, car elle est stockée sous le code 42005 dans le système Excel interne.
Fonctions de recherche et de référence dans Excel
Ces fonctions MS Excel sont pratiques lorsque vous devez rechercher certaines informations dans un tableau en fonction d'une valeur dans une colonne ou renvoyer une référence à une cellule donnée.
Fonction RECHERCHEV
Cette fonction Excel avancée est l’une des formules les plus utilisées dans Excel. Cela est principalement dû à la simplicité de cette formule et à son application dans la recherche de la valeur déterminée d'autres tables, qui possède une variable commune à toutes les tables.
La fonction RECHERCHEV recherche une valeur spécifiée dans la première colonne et extrait les données correspondantes de la même ligne dans une autre colonne. Il nécessite les arguments suivants :
RECHERCHEV (Valeur_cherché ; table_matrice ; no_index_col ; [Valeur_proche])
Valeur_cherché - la valeur à rechercher.
table_matrice - deux colonnes ou plus de données.
no_index_col - le numéro de la colonne à partir de laquelle extraire les données.
Valeur_proche - détermine s'il convient d'effectuer une recherche avec correspondance exacte (FAUX) ou approximative (VRAI ou omise).
Par exemple, la formule =RECHERCHEV ("Pommes" ; A2:B6 ; 2) recherche "Pommes" dans les cellules A2 à A6 et renvoie une valeur correspondante dans la colonne B :
La fonction INDEX
Cette formule Excel avancée permet d’obtenir la valeur d’une cellule dans un tableau donné en spécifiant le nombre de lignes, de colonnes ou les deux.
INDEX (matrice ; no_ligne ; [no_col]) - renvoie une référence à une cellule du tableau en fonction des numéros de ligne et de colonne que vous spécifiez.
Voici une formule INDEX simple: =INDEX(A1:B6 ; 3 ;2) qui effectue une recherche dans les cellules A1 à B6 et renvoie une valeur à l'intersection de la 3ème ligne et de la 2ème colonne, qui correspond à la cellule B3.
La fonction EQUIV
Cette formule avancée Excel renvoie le numéro de la ligne ou de la colonne lorsqu'il y a correspondance d'une certaine chaîne ou d'un certain nombre dans la plage donnée.
EQUIV (Valeur_cherché ; Tableau_recherche ; [type]) - recherche « Valeur_cherché » dans « tableau_recherche », puis renvoie la position relative de cet élément dans la plage.
La combinaison des fonctions EQUIV et INDEX peut être utilisée comme une alternative plus puissante et plus polyvalente de la fonction RECHERCHEV d'Excel, comme le montre le
Correspondance d'index
Formule: = INDEX(C3:E9;EQUIV(B13;C3:C9;0);EQUIV(B14;C3:E3;0))
Il s’agit d’une alternative avancée aux formules RECHERCHEV ou RECHERCHEH (qui présentent plusieurs inconvénients et limitations). INDEX EQUIV une puissante combinaison de formules Excel permet à votre analyse et votre modélisation financières de passer au niveau supérieur.
INDEX renvoie la valeur d'une cellule dans une table en fonction du numéro de colonne et de ligne.
EQUIV renvoie la position d'une cellule dans une ligne ou une colonne.
Voici un exemple de formules INDEX et EQUIV combinées. Dans cet exemple, nous recherchons et renvoyons la taille d'une personne en fonction de son nom. Comme le nom et la hauteur sont deux variables dans la formule, nous pouvons les changer tous les deux !
La fonction INDIRECT
INDIRECT (ref_text ; [a1]) - renvoie une référence de cellule ou de plage spécifiée par une chaîne de texte.
Voici un exemple de la formule INDIRECT la plus simple pour avoir une idée générale :
Dans les feuilles de calcul réelles, les formules INDIRECTes sont souvent utilisées pour faire référence de manière dynamique à une autre feuille ou à un autre classeur, pour verrouiller une référence de cellule ou pour créer des listes déroulantes dépendantes.
La fonction DECALER
Cette fonction Excel avancée associée à d’autres fonctions telles que SOMME ou MOYENNE peut s’avérer utile pour donner une touche dynamique aux calculs. Il est préférable de l'utiliser dans les cas où nous insérons des lignes continues dans une base de données existante. DECALER nous donne une plage dans laquelle nous devons mentionner la cellule de référence, le nombre de lignes et de colonnes.
DECALER (référence ; lignes ; colonnes ; [hauteur] ; [largeur]) : renvoie une référence à une plage de cellules décalée d'une cellule de départ ou d'une plage de cellules par le nombre spécifié de lignes et de colonnes.
Par exemple, =DECALER(A1 ; 1 ; 1) renvoie la valeur de la cellule B2, car il s'agit d'une ligne vers le bas et d’une colonnes à gauche de A1.
DECALER combiné avec SOMME ou MOYENNE
Formule : = SOMME (B4: DECALER (B4 ;0 ; E2-1))
La fonction OFFSET seul n'est pas particulièrement avancée, mais lorsque nous le combinons avec d'autres fonctions telles que SOMME ou MOYENNE, 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 standard, vous êtes limité à un calcul statique, mais en ajoutant DECALER, vous pouvez déplacer la référence de cellule.
Fonctionnement : Pour que cette formule fonctionne, nous substituons la cellule de référence de fin de la fonction SOMME à la fonction DECALER. 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. Nous avons maintenant des formules Excel avancées !
Comme vous le voyez, la formule SOMME commence dans la cellule B4, mais se termine par une variable, qui est la formule DECALER 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, en additionnant 3 années de données (point de départ compris). Comme vous pouvez le constater dans la cellule F7, la somme des cellules B4:D4 est égale à 15, ce qui correspond à la formule de décalage et de somme.
La fonction CHOISIR
Formule : = CHOISIR (choix ; option1 ; option2 ; option3)
La fonction est parfaite pour l’analyse de scénarios en 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 trois hypothèses différentes pour la croissance des revenus l’année prochaine : 5%, 12% et 18%. En utilisant la formule CHOISIR, vous pouvez retourner 12% si vous dites à Excel que vous voulez le choix n ° 2.
La fonction TRANSPOSE
TRANSPOSE (tableau) - transforme une plage horizontale de cellules en une plage verticale et inversement, c’est-à-dire convertit les lignes en colonnes et les colonnes en lignes.
La fonction LIEN_HYPERTEXTE
LIEN_HYPERTEXTE (Emplacement_lien ; [nom_conviviale]) - Crée un lien hypertexte vers un document stocké sur un réseau local ou sur Internet.
Les fonctions financières Excel
Microsoft Excel fournit une multitude de fonctions pour simplifier le travail des responsables de la comptabilité, des analystes financiers et des spécialistes des services bancaires.
La fonction VC
VC (taux ; npm ; vpm ; [va] ; [type]) - calcule la valeur future d'un investissement sur la base d'un taux d'intérêt constant.
Les fonctions VAN.PAIEMENTS et TRI.PAIEMENTS
Formule : = VAN.PAIEMENTS (taux d'actualisation ; flux de trésorerie ; dates)
Si vous êtes un analyste travaillant dans la banque, dans la recherche sur les actions, ou planification et analyse financières ( ), ou tout autre domaine de la finance d'entreprise nécessitant l'actualisation des flux de trésorerie, ces formules sont donc une bouée de sauvetage!
En termes simples, VAN.PAIEMENTS et TRI.PAIEMENTS vous permettent d’appliquer des dates spécifiques à chaque flux de trésorerie actualisé. Le problème avec les formules de base de la VAN et du TRI d’Excel est qu’elles supposent que les périodes de temps entre les flux de trésorerie sont égales. En tant qu'analyste, vous rencontrerez régulièrement des situations dans lesquelles les flux de trésorerie ne sont pas chronométrés de manière uniforme, et cette formule vous permet de résoudre ce problème.
Les fonctions VPM et INTPER
Formule : = VPM (taux d'intérêt ; nombre de périodes ; valeur actuelle)
Si vous travaillez dans la banque dans, de l'immobilier, des services ou tout poste d'analyste financier qui traite des échéanciers de dette, vous aurez envie de comprendre ces deux formules détaillées.
La formule VPM vous donne la valeur de paiements égaux sur la durée d'un prêt. Vous pouvez l'utiliser conjointement avec INTPER (qui vous indique les paiements d'intérêts pour le même type de prêt), puis séparez les paiements de principal et d'intérêts.
Voici un exemple d'utilisation de la fonction PMT pour obtenir le paiement hypothécaire mensuel d'un prêt hypothécaire d'un million de dollars à 5% sur 30 ans.
Les fonctions de date Excel
Cette partie fournit uniquement la liste des fonctions de dates pour tous ceux qui traitent régulièrement des dates Excel.
Création de dates
DATE - renvoie le numéro de série d'une date spécifiée.
DATEVAL - convertit une chaîne de texte représentant le format de date à date.
Date et heure actuelles
AUJOURDHUI - renvoie la date actuelle.
MAINTENANT - renvoie la date et l'heure actuelles.
Extraction de dates et de composants de date
JOUR - renvoie le jour du mois.
MOIS - convertit un numéro de série en mois.
ANNEE - convertit un numéro de série en année.
FIN.MOIS - renvoie le dernier jour du mois.
JOURSEM - renvoie le jour de la semaine.
NO.SEMAINE - convertit un numéro de série en un numéro représentant l’ordre de la semaine dans l’année.
Calculer la différence de date
MOIS.DECALER - renvoie une date correspondant au nombre de mois spécifié avant ou après la date de début.
FRACTION.ANNEE - calcule la fraction de l'année entre 2 dates.
Calculer les jours de travail
SERIE.JOUR.OUVRE - calcule une date correspondant à un nombre spécifié de jours ouvrables avant ou après la date de début.
SERIE.JOUR.OUVRE.INTL - calcule une date correspondant à un nombre spécifié de jours de semaine avant ou après la date de début, avec des paramètres de week-end personnalisés.
NB.JOURS.OUVRES - renvoie le nombre de jours ouvrables entre deux dates.
NB.JOURS.OUVRES.INTL - renvoie le nombre de jours de travail entre deux dates avec des week-ends personnalisés.
Fonctions de temps Excel
Vous trouverez ci-dessous une liste des principales fonctions Excel permettant de travailler avec le temps.
TEMPS (heure ; minute ; seconde) - renvoie un numéro de série représentant l'heure.
TEMPSVAL (heure_text) - convertit une heure entrée sous la forme d'une chaîne de texte en un numéro de série représentant l'heure.
MAINTENANT () - renvoie le numéro de série correspondant à la date et à l'heure actuelles.
HEURE (numéro_de_série) - convertit un numéro de série spécifié en une heure.
MINUTE (numéro_de_série) - convertit un numéro de série spécifié en minutes.
SECONDE (numéro_de_série) - convertit un numéro de série spécifié en secondes.
Comment créer des formules avancées dans Excel
Lorsque vous avez une certaine expérience des formules Excel simples, vous pouvez effectuer plusieurs calculs au sein d’une même formule. Et les exemples suivants montrent comment vous pouvez le faire.
Création de formules complexes avec des constantes et des opérateurs mathématiques
Pour qu'une formule Excel complexe puisse calculer correctement, certaines opérations doivent être effectuées avant d'autres. L'ordre des opérations par défaut dans les formules Excel est le suivant :
- Opérations mathématiques entre parenthèses
- Puissance de (calculs exponentiels)
- Multiplication et division, selon la première éventualité dans une formule
- Addition et soustraction, selon la première éventualité dans une formule
Par exemple, vous pouvez utiliser les formules suivantes pour calculer le total et la commission :
Et maintenant, décomposons ces formules pour voir comment Microsoft Excel les calcule :
Formule totale : = B2 $ * $ D2 + B2 $ * $ D2 * $ C2
1ère multiplication : $ B2 * $ D2 (prix * quantité = montant)
2ème et 3ème multiplications : $ B2 * $ D2 * $ C2 (prix * quantité * TVA% = montant de la TVA)
Addition : montant + montant TVA = total
Formule de commission : = (B2 $ * D2 $ + B2 $ * D2 $ * C2 $) * 10%
Pour calculer la commission de 10%, vous devez multiplier le total par 10%. Vous devez donc placer le calcul précédent entre parenthèses et obtenir le résultat souhaité.
Bien entendu, rien ne vous empêche de multiplier le total déjà calculé dans la colonne E par 10%. Dans ce cas, la formule se réduirait à un simple calcul = E2 * 10%. Cependant, dans les grandes feuilles de calcul, il est logique d'écrire des formules calculées indépendamment, de sorte que supprimer une colonne avec une formule ne casse pas les autres.
Les formules Excel avec fonctions imbriquées
Dans les formules Microsoft Excel, imbriquer une fonction dans une autre signifie utiliser une fonction en tant qu'argument d'une autre fonction. Dans les versions modernes d'Excel 2016, 2013, 2010 et 2010, vous pouvez utiliser jusqu'à 64 fonctions imbriquées. Dans les versions antérieures d'Excel 2003 et versions antérieures, seuls 7 niveaux de fonctions sont autorisés.
Voici un exemple très simple de formule Excel imbriquée incluant la fonction SOMME pour trouver le total et la fonction RONDE pour arrondir ce nombre à l'entier le plus proche (0 décimale) :
= ARRONDI (SOMME (B2: B6) ; 0)
Bien entendu, Microsoft Excel possède bien plus de fonctions que celles énumérées sur cette page. Espérons que ces fonctions essentielles vous seront utiles dans votre travail quotidien. Quoi qu'il en soit, il est bon d'avoir des connaissances de base pour un début que vous pourrez approfondir par la suite. Les formules rendent Excel intelligent. Sans eux, Excel n’est qu’un outil de conservation de données. Mais en utilisant des formules, vous pouvez traiter des données, les analyser et obtenir des réponses aux questions les plus complexes. Tout le monde peut utiliser une simple formule SOMME ou SI, mais un utilisateur expérimenté pourra écrire et combiner de manière transparente des formules telles que des formules SOMME.SI.ENS, SOMMEPROD, INDEX, EQUIV, RECHERCHEV. En plus de connaître les formules, les utilisateurs expérimentés d'Excel savent comment les déboguer, les auditer et comment utiliser quelle formule pour quelle occasion (et ils connaissent également peu d'alternatives pour un problème de formule donné).