De nombreux économistes ou financiers consacrent une grande partie de leur temps à travailler sur un ordinateur avec l’application de bureau MS Excel. Ce programme possède un nombre considérable de fonctions destinées à la création de rapports, à l’analyse de données, aux plans d’information, aux calculs mathématiques, etc. La connaissance des formules Excel les plus importantes et la combinaison de diverses fonctions facilitent grandement la résolution des problèmes pratiques et réduisent le temps et les efforts consacrés à ces tâches.
Fonctions INDEX et MATCH
IF fonction combinée avec la fonction AND
Combinaison des fonctions SUM et OFFSET
Fonctions SUMIF et COUNTIF
Fonction MODE.SNGL
Fonctions INDEX et MATCH
Dans les calculs financiers, une combinaison des fonctions INDEX et MATCH est souvent utilisée. Leur action commune est similaire au fonctionnement de la fonction VLOOKUP, mais présente de nombreux avantages par rapport à celle-ci. Tout d’abord, nous examinerons ces fonctions séparément.
Fonction INDEX
La fonction INDEX recherche la valeur d’un élément d’un bloc de données par le numéro de ligne et le numéro de colonne spécifiés. En général, sa structure ressemblera à ceci:
INDEX (array; row_number; [column_number]), où
- array – c’est un bloc de cellules où la recherche sera effectuée.
- row_number – c’est le numéro de séquence de la ligne dans laquelle la valeur est trouvée. Ce paramètre est requis si le numéro de colonne n’est pas spécifié.
- column_number – c’est le numéro de série de la colonne dans laquelle se trouve la valeur souhaitée. Si aucun numéro de ligne n’est spécifié dans la formule, ce paramètre est obligatoire.
Si un numéro de ligne et un numéro de colonne sont spécifiés, la fonction renvoie la valeur de la cellule située à l’intersection de ces données.
Prenons un exemple. Il existe un tableau avec une liste des numéros de produits et leurs dimensions. Pour trouver la longueur du numéro de produit 2, vous devez écrire une formule du type =INDEX(B2:D6;3;2)
Dans cet exemple, le résultat de la fonction sera le contenu de la cellule à l’intersection de la troisième ligne et de la deuxième colonne de ce tableau.
Malheureusement, le plus souvent dans les calculs, le numéro de ligne ou de colonne est inconnu. Ensuite, la fonction MATCH vient à la rescousse.
Fonction MATCH
L’action de la fonction MATCH est similaire à celle de la fonction INDEX, mais MATCH ne renvoie pas la valeur de la cellule, mais la position de la cellule dans la plage spécifiée. En général, la formule ressemblera à:
MATCH (search_value; array; [match_type]), où
- search_value – c’est ce que vous devez trouver. Il peut s’agir non seulement d’une valeur textuelle ou numérique, mais également d’une valeur logique, ainsi que d’un lien vers une cellule.
- array – c’est la plage de cellules en cours de visualisation.
- match_type – ce paramètre optionnel peut être réglé sur “1”, “0” ou “-1”. Il indique à la fonction quelle valeur rechercher: exacte ou approximative. Si les données du tableau sont en train de diminuer dans l’ordre croissant, le paramètre “1” indiquera qu’il est nécessaire de sélectionner la valeur maximale inférieure ou égale à celle souhaitée. Le paramètre “-1” est spécifié pour un tableau décroissant. Dans ce cas, la fonction sélectionnera la valeur minimale supérieure ou égale à celle souhaitée. Le paramètre “0” trouve la première valeur égale à celle désirée. C’est cette valeur du type correspondant qui est utilisée dans la combinaison des fonctions INDEX et MATCH.
Pour illustrer le fonctionnement de cette fonction, considérons un exemple. Dans le tableau ci-dessus, nous trouvons dans la colonne «Product Number» le compte correspondant au produit 2. Pour cela, nous écrivons la formule: =MATCH(2;B2:B6;0)
Pourquoi ai-je besoin de connaître la position d’un élément dans un tableau? Il s’avère qu’il est très pratique d’utiliser cette valeur comme argument de la fonction INDEX.
Combinaison des fonctions INDEX et MATCH
Si nous analysons les deux fonctions, il apparaît clairement que la fonction INDEX recherche les valeurs de cellule par numéro de ligne et par numéro de colonne. Simultanément, la fonction MATCH recherche les numéros de ligne et les numéros de colonne. Ainsi, si vous utilisez ces deux fonctions dans la même formule, MATCH recherchera la position relative de la valeur souhaitée et la fonction INDEX utilisera ces valeurs et renverra le contenu des cellules calculées.
Prenons un exemple. Dans le tableau ci-dessus, nous trouvons la largeur du produit au numéro 3. Pour cela, nous utilisons la formule suivante: =INDEX($D$2:$D$6;MATCH(3;$B$2:$B$6;0))
Il est recommandé d’utiliser des liens absolus pour ces formules afin que les plages de recherche ne soient pas confondues lors de la copie de formules.
Pourquoi est-il préférable d’utiliser une combinaison de fonctions INDEX et MATCH plutôt que d’utiliser la fonction VLOOKUP? Premièrement, une formule basée sur INDEX et MATCH vous permet de rechercher les données souhaitées dans la plage spécifiée de gauche à droite et de droite à gauche, tandis que lorsque vous utilisez VLOOKUP, la valeur souhaitée doit toujours figurer dans la colonne la plus à gauche de la plage.
Deuxièmement, lorsque vous utilisez la fonction VLOOKUP, vous ne pouvez pas supprimer ou ajouter de colonnes à la table. Sinon, le résultat de la formule sera incorrect. En effet, la syntaxe de cette fonction implique de spécifier la plage entière et le numéro de colonne spécifique à partir duquel les données seront extraites. Lorsque vous utilisez les fonctions INDEX et MATCH, vous pouvez supprimer ou ajouter autant de colonnes que vous le souhaitez.
Troisièmement, lorsque vous utilisez une combinaison de fonctions INDEX et MATCH, la taille de la valeur de recherche ne fait l’objet d’aucune restriction, tandis que la fonction VLOOKUP limite le nombre de caractères de la valeur de recherche à 255 caractères.
Quatrièmement, lorsque vous effectuez des calculs dans de grands tableaux de données, l’utilisation des fonctions INDEX et MATCH réduit considérablement le temps nécessaire à la recherche de valeurs par rapport à la fonction VLOOKUP. En effet, la fonction VLOOKUP est appelée pour chaque valeur de la plage de données spécifiée. En revanche, une formule basée sur les fonctions INDEX et MATCH effectue simplement une recherche et renvoie un résultat.
IF fonction combinée avec la fonction AND
La fonction IF boolean vérifie si le contenu des cellules remplit certaines conditions. Si cela correspond, la fonction renvoie l’une des valeurs définies par l’utilisateur. En cas de divergence, renvoie une autre valeur définie. La syntaxe de la fonction est la suivante:
=IF(expression_logique; valeur_si_vrai; valeur_si_faux), où
- expression_logique – ce sont les données à vérifier et les conditions de vérification. Par exempleА2>10.
- valeur_si_vrai – c’est l’entrée qui apparaîtra si la valeur de la cellule remplit la condition spécifiée.
- valeur_si_faux – c’est l’enregistrement qui apparaîtra si la valeur de la cellule ne satisfait pas la condition donnée.
De nombreux utilisateurs ayant effectué des calculs financiers complexes savent à quel point il est difficile de comprendre les formules utilisant des boucles imbriquées à l’aide de l’instruction IF. Il s’avère que ces formules peuvent être simplifiées si vous utilisez la fonction IF en combinaison avec les fonctions AND / OR. La combinaison des fonctions AND et IF fonctionne comme suit. Si A = 1 et A = 2, la formule renvoie la valeur B, sinon elle renvoie C. Pour la fonction OR, la formule ne fonctionne pas de cette manière. Si A = 1 ou A = 2, la formule renvoie la valeur B, sinon – la valeur C.
Prenons un exemple. Créez une formule qui vérifie le contenu de la cellule C2, égale à 110. Si le nombre est compris entre 90 et 300, le résultat sera 1, sinon 0. La formule ressemblera à ceci: =IF(AND(C2>=C4;C2<=C5);C7;C8)
Comme on peut le voir sur la figure, une valeur égale à 1 sera placée dans la cellule avec le résultat de la fonction. Le nombre 110 est en réalité compris entre 90 et 300.
La valeur totale peut être non seulement un nombre, mais également un texte, par exemple les mots “OUI” ou “Non” ou toute autre phrase.
Combinaison des fonctions SUM et OFFSET
La fonction OFFSET elle-même est rarement utilisée, mais sa combinaison avec d’autres fonctions peut produire de très bons résultats. Par exemple, l’utilisation combinée des fonctions SUM et OFFSET vous permet de créer des formules assez complexes lors de la création d’une fonction dynamique qui additionne un nombre variable de cellules. Pour résoudre ce problème, la fonction SUM est utilisée et, au lieu de la cellule finale, la fonction OFFSET est spécifiée, c’est-à-dire que la formule devient dynamique.
La formule résultante ressemblera à ceci:
= SUM (start_range:OFFSET (référence, nombre de lignes, nombre de colonnes)), où
- start_range – c’est le point de départ de la plage de cellules utilisée par la fonction SUM.
- référence – Il s’agit d’une référence à la cellule utilisée pour calculer le point final de la plage.
- nombre de lignes – c’est le nombre de lignes utilisées dans le calcul du décalage de cellule. Cette valeur peut être positive, négative et égale à zéro.
- nombre de colonnes – c’est le nombre de colonnes à droite ou à gauche de la référence de cellule donnée. Utilisé lors du calcul du décalage. Lorsque décalé vers la gauche, cette valeur est négative. Lorsque décalé vers la droite, la valeur est positive. Si les données calculées se trouvent dans la même colonne, ce paramètre est égal à zéro.
Prenons un exemple. Il existe un tableau avec les numéros de série des jours du mois et le revenu perçu chaque jour. Chaque jour, les informations du tableau sont mises à jour en ajoutant une ligne avec le revenu reçu par jour. Nous composons la formule dans la cellule finale après le quatrième jour de vente: =SUM(B2:OFFSET(B6;-1;0))
Afin d’ajouter des informations sur le cinquième jour de vente, vous devez ajouter une ligne vide après le quatrième jour et y entrer les informations nécessaires. Dans ce cas, la formule prendra la forme: =SUM(B2:OFFSET(B7;-1;0)), et le revenu total augmentera du montant du revenu reçu le cinquième jour.
Fonctions SUMIF et COUNTIF
Ces deux fonctions sont très souvent utilisées dans les calculs financiers. SUMIF trouve la somme dans une plage donnée de cellules par une certaine condition. COUNTIF compte toutes les cellules correspondant à la condition donnée.
La fonction SUMIF a la structure suivante:
SUMIF(gamme; critère; sum_range), où
- gamme – Il s’agit d’un tableau de cellules dans lequel la conformité aux critères spécifiés sera vérifiée.
- critère – c’est une condition pour la sélection des cellules. Cela peut être un nombre, un texte, une expression ou une référence de cellule.
- sum_range – Ceci est un paramètre optionnel. Si vous ne le spécifiez pas, la sommation sera effectuée en tenant compte de l’argument “Gamme “.
L’action de cette fonction est facile à comprendre avec un exemple. Il y a un tableau avec une liste de biens et leur quantité. Vous devez trouver la quantité de “Product 1”. La formule pour le calcul sera la suivante: =SUMIF(B2:B7;”Product 1″;C2:C7)
Dans le même exemple, vous pouvez calculer le nombre de produits excluant le «Product 1» à l’aide de la formule suivante: =SUMIF(B2:B7;”<>Product 1″;C2:C7)
La fonction COUNTIF a la structure suivante:
COUNTIF (plage; critère), où
la plage et le critère sont similaires à SUMMES.
Dans le même exemple, nous comptons le nombre de lignes «Product 1». La formule pour le calcul ressemblera à ceci: =COUNTIF(B2:B7;”Product 1″).
À l’aide de cette formule, vous pouvez calculer le nombre de lignes satisfaisant certaines conditions. Par exemple, pour calculer le nombre de lignes de cette table avec un nombre de produits supérieur à 10, vous devez créer la formule suivante: =COUNTIF(C2:C7;”>10″).
Fonction MODE.SNGL
La fonction statistique MODE.SNGL et sa version obsolète MODE repèrent la valeur la plus fréquente dans la plage de données (tableau) et renvoient cette valeur. Syntaxe de la fonction:
=MODE((numéro1; [numéro2]; …), où
- numéro1– Il s’agit d’un argument requis, qui est un nombre, une référence à une cellule de nombre, un tableau ou une plage de cellules.
- numéro2– argument optionnel. Il peut y avoir de 1 à 255 tels arguments.
Les arguments qui ne peuvent pas être convertis en nombres provoquent une erreur de formule. S’il n’y a pas de nombres identiques dans la plage spécifiée, le résultat de la fonction sera la valeur d’erreur #N/A.
Pourquoi puis-je utiliser cette fonction dans les calculs financiers? Par exemple, pour savoir quels produits sont le plus souvent achetés sur la base de données récapitulatives. Le critère de sélection peut être le prix du produit, la taille, le volume, les dimensions, etc. À titre d’illustration, nous considérons un tableau qui reflète les ventes de biens avec la date de vente, la taille du produit et son prix. Pour savoir quelles tailles de produits sont le plus souvent vendues, nous utilisons la formule suivante: =MODE(C2:C6)
La figure montre que les produits d’une taille de 36 sont les plus demandés. Ainsi, la fonction MODE détermine l’événement le plus fréquent dans la plage d’événements.
Ainsi, l’utilisation des fonctions financières de l’application bureautique Excel facilite grandement la conduite des règlements financiers et commerciaux des prêts et emprunts, l’analyse financière et des investissements et le travail avec les titres. Un avantage significatif de l’application est la possibilité de définir un mot de passe pour protéger les documents importants. Cela empêche des tiers non autorisés d’accéder aux rapports financiers et économiques. Malheureusement, les mots de passe sont parfois perdus, mais la retirer mot de passe Excel est possible à l’aide de programmes spéciaux.