Fonctions Excel: Le top 8 des fonctions Excel pour la finance et la comptabilité
Adrian Frei
Adrian Frei, M.A. HSG, Directeur et propriétaire d’exact construct GmbH, Frauenfeld. exact construct est spécialisé dans la création d’outils Excel et la programmation de macros.
Aides de travail appropriées
ET/OU
Nous avons inclus cette formule dans notre collection des 8 meilleures formules parce qu’elle est très puissante lorsqu’elle est combinée avec d’autres. Les formules «ET» et «OU» permettent de relier différentes formules en générant une proposition logique. Une proposition logique est une proposition qui peut avoir la valeur VRAI ou FAUX. En comparant les instructions fournies dans leurs paramètres, les résultats donnent soit VRAI soit FAUX. L’utilisateur peut ainsi vérifier différents critères l’un après l’autre.
La formule ET ne renvoie VRAI que si toutes les propositions logiques fournies sont VRAIES. Si au moins une proposition logique est FAUX, le résultat est FAUX. La formule OU fonctionne différemment. Si toutes les propositions logiques fournies sont FAUX, alors le résultat est FAUX. Cette formule donne VRAI s’il y a au moins une instruction logique qui est VRAIE.
SI
La formule SI fait partie, selon nous, des formules les plus importantes. Elle est souvent l’une des premières formules apprises, car son apprentissage est intuitif (voir Illustration 1). L’utilisation de la formule SI n’est pas si compliquée, l’utilisateur doit seulement comprendre les trois éléments de la formule:
=si(condition;résultat_1;résultat_2)
La formule doit être lue de gauche à droite et signifie: si la condition est remplie, alors s’affiche le Résultat_1, si non le Résultat_2. Cette formule peut maintenant être complétée avec les valeurs suivantes.
Condition: A1 = «pomme»
Résultat_1: «fruit»
Résultat_2: «légume
Cela se présente comme suit:
=si(A1 = «Pomme»;«Fruit»;«Légume»)
La formule se lira maintenant comme suit: s’il y a Pomme dans la cellule A1, alors s’affichera Fruit ou, si non, Légume.
Dans la première partie Condition, nous pouvons maintenant réutiliser la formule OU() que nous avons déjà apprise, en vérifiant simultanément plusieurs critères. Par exemple, si A1 = pomme ou A2 = poire. Si l’un des deux critères est correct, le résultat serait un fruit, sinon un légume.
CONNECTER
Si vous avez différents mots que vous souhaitez combiner, la formule CONNECTER est appropriée. Par exemple, si la rue se trouve dans une colonne et le numéro dans l’autre, vous pouvez les fusionner avec cette fonction. La copie manuelle de telles valeurs est non seulement coûteuse, mais aussi sujette aux erreurs. C’est pourquoi Excel dispose de diverses possibilités pour relier différentes cellules.
Une variante est la «formule de concaténation». Cette formule permet de relier entre elles 30 cellules au maximum. L’exemple suivant est la combinaison de valeurs de cellules et d’une chaîne de caractères.
=CONNECTER(Texte1;Texte2;Texte3)
Le texte peut être une saisie manuelle ou une cellule.
Texte1: C3 = «Compte 1»
Texte2: «a un état de »
Texte3: E3 = «1622,72»
Vous pouvez également connecter les cellules à l’aide de «&». C’est facile à utiliser pour un petit nombre de cellules, mais s’il y en a beaucoup, il vaut mieux utiliser CONNECTER. Dans cet exemple, «=C3&» a une position «&E3» (voir figure 2).
MAX et MIN
Ces formules figurent dans la liste du Top 8 parce qu’elles peuvent être utilisées en combinaison avec d’autres formules, par exemple la formule SI. En outre, la formule permet de déterminer, dans une liste de notes, quelle est la meilleure ou la moins bonne note ou quel produit est le moins ou le plus vendu. La formule MIN est utilisée pour trouver la valeur la plus basse ou minimale dans une série de valeurs, tandis que la formule MAX est utilisée pour trouver la valeur la plus haute ou maximale dans une série de valeurs. Les deux formules ont les mêmes paramètres.
=MIN(NOMBRE1;NOMBRE2;NOMBRE3)
Les paramètres de la formule peuvent être soit une zone liée (voir chiffre1), soit un chiffre que vous écrivez dans la formule (voir chiffre2), soit vous pouvez définir un nom pour une zone avec le gestionnaire de noms et faire référence à ce nom avec la formule.
NOMBRE1: E3:E7 → domaine
NOMBRE2: 10.5 → valeur
NOMBRE3: Nom du test → Nom défini
L’illustration 3 présente un exemple de détermination du montant maximal et du montant minimal dans un domaine.
Recommandations de produits
SOMME.SI/SOMME.SI.ENS
Supposons que vous ayez un magasin et que vous souhaitiez connaître non seulement le chiffre d’affaires total, mais aussi le chiffre d’affaires de chaque produit par mois. Comme ces formules peuvent calculer des sommes en tenant compte de conditions, nous considérons que cette formule est très puissante et très importante pour le domaine financier.
La formule SOMME.SI.ENS calcule la somme en fonction du fait que les critères de la condition indiquée sont remplis ou non. La différence entre les deux réside dans le fait que dans le premier cas, un seul critère est contrôlé, alors que dans l’autre variante, plus d’un critère peut être contrôlé.
Pour la formule SOMME.SI, le domaine des critères et les critères sont les deux premiers paramètres. Le domaine de la somme vient en dernier dans la formule.
SOMME.SI
=SOMME.SI(DOMAINE;critère de recherche; [Somme_Domaine])
Le critère de recherche ne doit pas nécessairement être une référence, il peut aussi s’agir d’une saisie manuelle. Il est toutefois recommandé de faire référence à une cellule.
DOMAINE: D3:D12 → les critères sont examinés dans ce domaine
CRITERES DE RECHERCHE: H4 = «Produit A», il s’agit ici du critère, c’est-à-dire que nous aimerions connaître le chiffre d’affaires du produit A.
SOMME DOMAINE: E3:E12 → les valeurs de ce domaine sont additionnées.
SOMME.SI.ENS
L’autre formule est SOMME.SI.ENS, où l’on commence par la plage de totaux et où l’on saisit ensuite un critère après l’autre. Comme la formule SOMME.SI.ENS peut avoir plus d’un critère, la plage de critères suivante et les critères peuvent être facultatifs.
= SOMME.SI.ENS (Somme_Domaine;Critères_Domaine1;Critères1;Critères_Domaine2;Critère2)
Le critère de recherche ne doit pas nécessairement être une cellule, il peut également s’agir d’une saisie manuelle.
Somme_Domaine: F3:F12 → les valeurs de ce domaine sont additionnées.
Critère_Domaine1: D3:D12 = Le Critère 1 est cherché dans ce domaine.
Critère1: H3 = «Produit A» → Le critère pour le premier examen est le Produit A. Celui-ci est vérifié dans Critère_Domaine1.
Critère_Domaine2: E3:E12 = Le Critère 2 est recherché dans ce domaine.
Critère1: M2 = «Janvier» → Le critère pour le deuxième examen est janvier. Celui-ci sera examiné dans Critèren_Domaine2.
Avec SOMME.SI.ENS vous pouvez par exemple calculer le chiffre d’affaires par mois et par produit et établir ainsi un dashboard (voir Illustration 4).
MOYENNE.SI/MOYENNE.SI.ENS
Les formules MOYENNE.SI et SOMME.SI.ENS fonctionnent de manière très similaire à la formule SOMME.SI.ENS (voir figure 5). Les paramètres de cette formule sont pratiquement identiques.
= MOYENNE.SI (Domaine;Critère; Domaine moyen)
SOMME.SI.ENS(Valeur moyenne_Domaine;Critère_Domaine1;Critère1;Critère_Domaine2;Critère2)
L’unique différence par rapport à la formule SOMME.SI.ENS est que le domaine n’est pas additionné, mais que le système calcule la valeur moyenne sur la base de certains critères.
=Moyenne.si(D4:D13;H4;F4:F13)
=Moyenne.si.ens(F4:F13;D4:D13;L4;E4:E13;M3)
COMPTE.SI/COMPTE.SI.ENS
Partons du principe que vous souhaitez savoir combien de ventes vous avez réalisées. Cela signifie que vous voulez connaître le nombre d’entrées par produit ou par produit et par mois. La formule COMPTE.SI/COMPTE. SO.ENS compte les cellules de la plage qui répondent aux critères spécifiques. La formule fonctionne de la même manière que les formules précédentes, la seule différence étant que le nombre est calculé en tenant compte des critères saisis (voir figure 6). Il n’y a qu’un seul critère pour COMPTE.SI et plusieurs critères pour COMPTE.SI.ENS. Les paramètres de cette formule sont la plage et les critères.
=COMPTE.SI(DOMAINE;critères de recherche)
DOMAINE: D3:D12 → Le critère est examiné dans ce domaine.
CRITERE DE RECHERCHE: H3 = «Produit A», il s’agit ici du critère, c’est-à-dire que nous aimerions savoir le nombre de lignes avec Produit A.
=COMPTE.SI(Critère_Domaine1;Critère1;Critère_Domaine2;Critère2)
Critère_Domaine1: D3:D12 = Le critère 1 est recherché dans ce domaine.
Critère1: L3 = «Produit A» → Le critère pour le premier examen est le Produit A. Celui-ci est examiné dans Critère_Domaine1.
Critère_Domaine2: E3:E12 = Le critère 2 est recherché dans ce domaine.
Critère1: M2 = «Janvier» → Le critère pour le deuxième examen est Janvier. Ce dernier est examiné dans Critère_Domaine2.
Ces formules sont souvent utilisées dans les tableaux de bord pour analyser le nombre de ventes.
RECHERCHEV
Cette formule fait partie des formules les plus utilisées dans Excel et on ne saurait donc pas l’omettre ici. En anglais, elle s’appelle «VLOOKUP». Comme son nom l’indique, il s’agit d’une formule qui recherche une valeur dans une plage. Le «V» signifie vertical.
=RECHERCHEV(Critère de recheche;Matrice;Index colonne; [Domaine_Recherche])
Critère de recherche: I3 = 1003 → Ce critère est recherché dans une autre liste.
MATRICE: B3:E8 (ou nom du tableau) → Il s’agit du tableau dans lequel le critère de recherche doit être recherché.
Index colonne: 4 = Colonne Montant → La première colonne de la matrice doit toujours être la clé (critère), l’index détermine la colonne qui doit être éditée, c’est-à-dire que le nombre de colonnes est compté.
Domaine_Recherche: FAUX → Le critère de recherche doit correspondre exactement (VRAI ou laisser vide signifie que l’on recherche la correspondance la plus exacte possible).
L’exemple de la figure 7 recherche une valeur. Ceci est très utile pour une requête. Cela signifie qu’il est possible de créer un aperçu, par exemple par compte, de changer de compte et d’afficher le résultat pour le nouveau compte. Dans l’exemple, seul le numéro de compte doit être remplacé.
Si vous devez par exemple ajouter les cours de change à une liste de comptes, la formule peut également être appliquée directement dans un tableau pour toutes les entrées. Il est recommandé que la liste des cours soit disponible dans une feuille Excel séparée. Pour que la formule puisse être réduite dans la colonne Cours, la zone matricielle doit être fixée avec $ (p. ex.: $A$1:$D$12) ou des colonnes doivent être sélectionnées dans des tableaux Excel (voir figures 7 et 8).
VRAI est surtout utilisé pour répartir des valeurs dans des plages, par exemple une fourchette de salaires. Faux est toujours utilisé lorsque la concordance du critère de recherche doit être de 100%.
Résumé
Il s’agit des huit formules que nous utilisons le plus souvent en collaboration avec les collaborateurs des départements financiers. Outre les formules mentionnées dans cet article, il existe de nombreuses autres formules utiles proposées par Excel. Il est également évident que cette liste est quelque peu arbitraire. Néanmoins, si vous maîtrisez ces huit formules, vous n’aurez aucun mal à utiliser les autres formules disponibles dans Excel. De notre point de vue, la formule la plus sous-estimée est la formule SOMME.SI. Si vous n’utilisez pas encore cette formule lorsque vous réalisez des analyses, cela vaut vraiment la peine de l’apprendre.
