Les 10 fonctions les plus utilisées dans Excel en entreprise
Créer des tableaux, réaliser des calculs, mettre en forme des graphiques, intégrer des bases de données, etc. Microsoft Excel, puissant outil de visualisation mathématique, est un logiciel bureautique largement utilisé en entreprise.
Que vous soyez gestionnaire de paie, secrétaire administrative ou chef d’entreprise, Excel est indispensable pour vos tâches quotidiennes.
Les tableaux et visuels que vous créez avec Excel vous permettent de collecter et d’analyser les informations nécessaires pour prendre des décisions éclairées.
Quel que soit votre métier et l’utilisation que vous faites d’Excel, il est crucial de maîtriser ces fonctions essentielles.
1. Groupe de fonctions SOMME, MOYENNE, NB, MAX, MIN
Consolider rapidement les données, effectuer des calculs de totalisation et obtenir des aperçus financiers précis pour des décisions éclairées. Ces fonctions sont parmi les plus utilisées dans Excel, tant elles sont indispensables pour l’analyse de données.
- SOMME : Additionner rapidement une série de valeurs.
- MOYENNE : Calculer la moyenne des valeurs.
- NB : Compter le nombre de cellules contenant des nombres.
- MAX : Trouver la valeur maximale.
- MIN : Identifier la valeur minimale.
Situations métiers d’utilisation des fonctions SOMME, MOYENNE, NB, MAX et MIN :
- Une secrétaire administrative utilise la fonction SOMME pour calculer le total des dépenses mensuelles de l’entreprise. La fonction MOYENNE lui permet de déterminer la dépense moyenne par employé, et les fonctions MAX et MIN pour identifier les dépenses les plus élevées et les plus basses.
- Un gestionnaire de paie utilise SOMME pour calculer le total des salaires à verser chaque mois. La fonction MOYENNE est utilisée pour déterminer le salaire moyen des employés, tandis que MAX et MIN servent à identifier les salaires les plus élevés et les plus bas au sein de l’entreprise.
Ces fonctions sont les plus utilisées dans Excel. Ces fonctions sont tellement utiles que Microsoft a créé un raccourci dédié dans le ruban « Accueil ».
Extrait de la formation Excel initiation animée par Les Savoirs de la Formation
Extrait de la formation Excel avancé animée par Les Savoirs de la Formation
2. Fonction SI : Utiliser des conditions pour comparer
La fonction SI est l’une des plus populaires dans Excel. Elle permet d’établir des comparaisons logiques entre une valeur et le résultat attendu.
Une instruction SI peut avoir deux résultats. Le premier résultat est appliqué si la comparaison est vérifiée, sinon le deuxième résultat est appliqué.
La fonction SI se décompose en trois parties :
- Le test logique : élément 1 + opérateur de comparaison + élément 2.
- La valeur à afficher si le test est vrai
- La valeur à afficher si le test est faux
Exemple de l’utilisation de la fonction SI :
Dans l’exemple ci-dessous d’estimation de budget, la cellule E4 contrôle si l’écart entre la dépense réelle et le budget prévisionnel est positif ou bien négatif. En fonction du résultat, la réponse affichée dans E4 sera « Dans le budget » si la dépense réelle est plus faible que l’estimation ou « Hors budget » si la dépense réelle a été plus importante que l’estimation. La formule complète est donc :
« =SI(D4>0; »Dans le budget »; »Hors budget ») »
Exemple avancé de l’utilisation de la fonction SI :
Pour les plus avancés, la fonction SI peut également être insérer au sein d’une autre fonction SI. Nous parlons de fonctions imbriquées.
Reprenons notre exemple, précédent et allons ajouter un troisème test, lorsque la dépense réelle est parfaitement égale à la dépense prévisionnelle. La logique de cette fonction sera la suivante :
- Si la cellule D4>0
- alors, afficher « Dans le budget »
- Sinon,
- Si la cellule D4=0
- alors, afficher « Budget parfait »
- Sinon (dans tout les autres cas), afficher « Hors budget »
La fonction à implémenter est donc :
« =SI(D4>0; »Dans le budget »;SI(D4=0; »Budget parfait »; »Hors budget »)) »
3. Fonction NB.SI et NB.SI.ENS : Analyser et Compter
La fonction NB.SI est un outil performant pour analyser les données en comptant le nombre de valeurs répondant à des critères prédéfinis. NB.SI.ENS va plus loin en permettant de compter les cellules répondant à plusieurs critères. Ces fonctions sont essentielles dans de nombreux domaines, notamment pour la gestion des effectifs, des ventes et bien plus encore.
Situations métiers :
- Les secrétaires administratives utilisent NB.SI pour compter le nombre de factures payées chaque mois. NB.SI.ENS est utilisé pour compter les factures payées dans un certain délai et pour différentes catégories de dépenses.
- Un gestionnaires de paie peut utiliser NB.SI pour compter le nombre d’employés dans un certain département. NB.SI.ENS est utilisé pour compter les employés ayant travaillé plus de 40 heures et appartenant à un certain niveau hiérarchique.
- Enfin, un chef d’entreprise va utiliser NB.SI pour analyser les performances des ventes en comptant le nombre de produits vendus répondant à un critère spécifique, comme une certaine gamme de produits ou une région géographique. NB.SI.ENS permet de combiner plusieurs critères pour des analyses plus détaillées, comme les ventes de produits haut de gamme dans une région spécifique pendant une période donnée.
4. Fonction SOMME.SI et SOMME.SI.ENS : Conditionner une somme
La fonction SOMME.SI additionne les valeurs qui répondent à un critère donné, tandis que SOMME.SI.ENS permet d’additionner les valeurs répondant à plusieurs critères. Ces fonctions sont essentielles pour optimiser les calculs et obtenir des analyses financières plus précises et conditionnées.
La fonction SI se décompose en trois parties :
- La plage de valeur à comparer : Elements à comparer
- Le critère de comparaison : Elément de référence pour la comparaison
- La plage à additionner : Pour chaque ligne, lorsque la comparaison précédente est vérifier
Situations métiers :
Par exemple, un comptable utilise SOMME.SI pour totaliser les dépenses par type de frais, comme les frais de déplacement. Il utilise SOMME.SI.ENS pour totaliser les dépenses par type de frais et par département, par exemple, les frais de déplacement pour le département commercial au cours du dernier trimestre.
Dans notre exemple à gauche : la fonction SOMME.SI à implémenter en cellule E3 pour « Achat matière première » du tableau de synthèse est :
=SOMME.SI($B$3:$B$14;E3;$C$3:$C$14)
Astuce : le symbole $ devant une coordonnées de ligne ou de colonne permet d’annoncer à MICROSOFT Excel que cette ligne ou colonne doit être toujours la même (car nous allons copier/coller notre formule pour les autres matières premières sans modifier les plages à utiliser, seul le critère de recherche change !).
Egalement, un responsable marketing pourra utiliser SOMME.SI pour additionner les dépenses de campagne publicitaire par canal, comme les publicités en ligne. Il utilise SOMME.SI.ENS pour analyser les dépenses publicitaires par canal et par région, par exemple, les dépenses de publicité en ligne dans la région Nord.
Un responsable des ressources humaines utilise SOMME.SI pour additionner les heures supplémentaires par employé. Il utilise SOMME.SI.ENS pour totaliser les heures supplémentaires par employé et par mois, permettant ainsi d’analyser les pics d’activité et la charge de travail de chaque employé.
Ces fonctions permettent de conditionner des sommes en fonction de critères spécifiques, fournissant ainsi des analyses plus fines et pertinentes pour divers métiers et secteurs.
Extrait de la formation Excel avancé animée par Les Savoirs de la Formation
Extrait de la formation Excel avancé animée par Les Savoirs de la Formation
5. Fonction RECHERCHEV : Rechercher Verticalement une Donnée Clé
La fonction RECHERCHEV est un outil puissant pour faciliter la recherche de données spécifiques dans de grands tableaux. En permettant de chercher une valeur dans la première colonne (obligatoirement) d’un tableau et de renvoyer une valeur dans la même ligne à partir d’une colonne spécifiée, RECHERCHEV simplifie la gestion et l’organisation des données.
La fonction RECHERCHEV se décompose en quatre parties :
- Valeur recherchée : Elements à rechercher dans la première colonne de la matrice
- Matrice de valeur : Plage de cellules qui contient la valeur à rechercher et la valeur à renvoyer
- Index de la colonne de valeur : Numéro de la colonne contenant la valeur à renvoyer dans la matrice de valeur passé en paramètre 2)
- Valeur proche : FAUX ou 0 pour rechercher la valeur exacte (recommandé)
Situations métiers :
Par exemple, un secrétaire administratif utilise RECHERCHEV pour retrouver rapidement les coordonnées des clients à partir d’une liste de contacts. En saisissant le nom du client, la fonction RECHERCHEV renvoie automatiquement l’adresse, le numéro de téléphone ou d’autres informations pertinentes, ce qui accélère les tâches administratives quotidiennes.
L’utilisation de la fonction RECHERCHEV est assez simple. Vous spécifiez la valeur que vous cherchez, la plage de cellules à examiner, le numéro de colonne de la valeur à retourner et une option pour une correspondance exacte ou approximative. Cela permet une flexibilité dans la recherche et la récupération des données nécessaires.
Un gestionnaire de paie utilise également RECHERCHEV pour rechercher des informations salariales spécifiques. Par exemple, il peut entrer l’ID d’un employé pour récupérer instantanément son salaire, son département et d’autres informations pertinentes. Cela facilite grandement la gestion des fiches de paie et le suivi des données salariales.
La fonction RECHERCHEV est donc essentielle pour simplifier et accélérer la recherche de données dans de nombreux contextes professionnels. En utilisant cette fonction, les professionnels peuvent accéder rapidement aux informations dont ils ont besoin, optimisant ainsi leur efficacité et leur productivité au quotidien.
6. Fonction INDEX combinée : Cibler et Accéder aux informations Clés
La fonction INDEX sert à identifier une valeur à la croisée d’une ligne et d’une colonne.
Plus efficace que la fonction RECHERCHEV, voici la façon la plus simple de l’utiliser :
La fonction INDEX se décompose en trois parties :
- Matrice de recherche : Plage de valeur possible.
- Numéro de ligne de la matrice
- Numéro de colonne de la matrice
Pour que cette fonction soit la plus efficace, il faut la combiner à une fonction permettant d’aller rechercher les numéros de ligne et les numéros de colonne de façon dynamique. C’est le rôle de la fonction EQUIV.
La fonction EQUIV se décompose en trois parties :
- Valeur à rechercher
- Matrice de recherche
- Type de recherche (facultatif)
Dans notre exemple, une entreprise a déterminée ses coûts de transport en fonction du département et du type de produit qu’elle transporte.
Une liste déroulante des départements a été positionnée en I7 et une liste déroulante des types de produits en I8.
En combinant les fonction INDEX et EQUIV, la fonction associé au coût du transport est :
=INDEX(B5:F18;EQUIV(I7;A5:A18;0);EQUIV(I8;B4:F4;))
Situations métiers :
Un analyste de données peut utiliser la fonction INDEX pour modéliser des états financiers, extraire rapidement des informations spécifiques d’un grand tableau de données, comme le chiffre d’affaires d’un produit particulier sur une période donnée. Cette fonction permet d’accéder à une valeur précise à l’intersection d’une ligne et d’une colonne, simplifiant ainsi la recherche et l’extraction des données nécessaires pour les rapports et analyses approfondies.
L’utilisation de la fonction INDEX est simple mais puissante. En spécifiant une plage de cellules ainsi que les numéros de ligne et de colonne, vous pouvez extraire une valeur spécifique. Par exemple, si vous avez un tableau avec les ventes par mois et par produit, vous pouvez utiliser INDEX pour obtenir les ventes d’un produit spécifique en juin en fournissant les numéros de ligne et de colonne correspondants.
Un gestionnaire de projet utilise également la fonction INDEX pour suivre les étapes clés d’un projet dans un tableau complexe. Par exemple, il peut extraire le statut actuel d’une tâche spécifique en fonction de sa position dans le tableau, ce qui lui permet de surveiller l’avancement du projet de manière efficace et précise.
La fonction INDEX est donc un outil essentiel pour accéder rapidement et précisément aux informations clés, rendant les processus de recherche et d’analyse de données beaucoup plus efficaces pour divers métiers. En utilisant la fonction INDEX, les professionnels peuvent optimiser leur efficacité et prendre des décisions éclairées en se basant sur des données fiables et facilement accessibles.
Extrait de la formation Excel avancé animée par Les Savoirs de la Formation
7. Fonction CONCATENER : Fusionner les Données Textuelles
La fonction CONCATENER (ou CONCAT dans les versions plus récentes d’Excel) permet de combiner plusieurs chaînes de texte en une seule. C’est un outil précieux pour rassembler des données textuelles provenant de différentes sources ou colonnes, facilitant ainsi la création de rapports ou la génération d’informations personnalisées.
Par exemple, un responsable des ressources humaines utilise CONCATENER pour fusionner les données des employés. Il peut combiner le prénom et le nom des employés avec leurs identifiants uniques pour créer des étiquettes d’identification ou des badges. Par exemple, il peut fusionner les colonnes « Prénom », « Nom » et « ID » pour générer des identifiants comme « Jean_Dupont_1234 ».
L’utilisation de la fonction CONCATENER est simple : vous spécifiez les cellules ou les chaînes de texte que vous souhaitez combiner, et la fonction les fusionne en une seule chaîne. Cela peut inclure du texte brut, des valeurs de cellule, ou une combinaison des deux.
Un responsable de la communication utilise également CONCATENER pour personnaliser les messages destinés aux employés ou aux clients. Par exemple, il peut fusionner des colonnes contenant des salutations, des prénoms, des noms et des informations sur des événements pour créer des invitations personnalisées. Cela permet une communication plus ciblée et engageante.
La fonction CONCATENER est donc essentielle pour fusionner des données textuelles de manière efficace et personnalisée, rendant les processus de création de rapports et de communication plus fluides et plus précis pour divers métiers. En utilisant cette fonction, les professionnels peuvent optimiser la gestion des données textuelles et améliorer la qualité de leurs communications et rapports.
8. Les opérateurs logiques ET et OU : Combiner Plusieurs Conditions
Les opérateurs logiques ET et OU sont essentiels pour créer des formules avancées dans Excel, en permettant de regrouper plusieurs conditions. Ces opérateurs sont à la base des algorithmes et facilitent la prise de décisions complexes dans les calculs.
- Si toutes les conditions doivent être vraies : Utilisez la fonction ET
- Si une seule des conditions doit être vraie : Utilisez la fonction OU
Dans l’exemple suivant, la fonction ET est utilisée en combinaison avec la formule SI pour contrôler la situation des clients en fonction du montant investi. La colonne C indique si le montant investi de chaque client est autorisé ou refusé en fonction des bornes définies :
« =SI(ET(B2>=$E$2;B2<=$F$2); »Montant autorisé »; »Montant refusé ») »
9. Fonction SIERREUR : Fiabiliser les Calculs
La fonction SIERREUR est un outil crucial pour fiabiliser les calculs dans Excel. Elle permet de gérer les erreurs dans les formules en affichant un message personnalisé ou une valeur spécifique lorsque des erreurs sont détectées, au lieu de renvoyer les messages d’erreur par défaut.
La fonction SIERREUR se décompose en deux parties :
- Formule : Formule à exécuter
- Libellé d’erreur : Affiche le libellé en cas d’erreur dans la formule
Reprenons notre exemple d’une entreprise qui cherche à déterminer le coût de transport par département et par type de produit.
Situations métiers :
Par exemple, un gestionnaire de paie utilise la fonction SIERREUR pour éviter les erreurs lors des calculs de salaire. Lorsqu’une division par zéro ou une référence de cellule invalide survient, la fonction SI.ERREUR permet d’afficher un message clair comme « Donnée manquante » au lieu des messages d’erreur standard. Cela permet d’assurer la précision des fiches de paie et de rendre les rapports plus compréhensibles.
Un analyste financier utilise également la fonction SIERREUR pour fiabiliser les rapports financiers. Lors de l’importation de données de différentes sources, il peut y avoir des références manquantes ou des valeurs incorrectes. En utilisant SIERREUR, l’analyste peut remplacer les erreurs par une valeur par défaut comme 0 ou un message plus pertinent, ce qui rend les rapports plus lisibles et professionnels.
La fonction SIERREUR est un moyen efficace de contrôler les résultats et de minimiser les erreurs, assurant ainsi la fiabilité des analyses effectuées. Elle permet aux professionnels de gérer les erreurs de manière proactive, en rendant les rapports et analyses plus précis et plus fiables. En utilisant cette fonction, les utilisateurs peuvent assurer la qualité de leurs données et éviter les interruptions causées par des erreurs inattendues.
Extrait de la formation Excel avancé animée par Les Savoirs de la Formation
10. Fonction AUJOURDHUI et MAINTENANT : Suivre les Horaires et les Mises à Jour
Les fonctions AUJOURDHUI et MAINTENANT sont indispensables pour travailler avec les dates et les heures dans Excel. AUJOURDHUI renvoie la date actuelle, tandis que MAINTENANT renvoie la date et l’heure actuelles. Ces fonctions sont particulièrement utiles pour les tâches nécessitant des mises à jour régulières et des suivis temporels précis.
Par exemple, un responsable logistique utilise la fonction AUJOURDHUI pour suivre les délais de livraison. En incluant la date actuelle dans ses feuilles de calcul, il peut facilement comparer les dates de commande et de livraison prévues, s’assurant ainsi que les expéditions respectent les délais. Cela permet une meilleure gestion des stocks et une optimisation des flux de travail.
Un chef de projet informatique utilise la fonction MAINTENANT pour suivre le temps passé sur les tâches et les projets. En intégrant l’heure actuelle dans ses feuilles de temps, il peut monitorer en temps réel l’avancement des projets et ajuster les plannings en conséquence. Cela permet de garantir que les deadlines sont respectées et que les ressources sont utilisées efficacement.
Les fonctions AUJOURDHUI et MAINTENANT sont donc essentielles pour suivre les horaires et les mises à jour en temps réel. Elles permettent aux professionnels de gérer les délais de manière proactive et d’optimiser la planification et l’exécution des tâches. En utilisant ces fonctions, les utilisateurs peuvent s’assurer que leurs informations sont toujours à jour et prendre des décisions basées sur des données temporelles précises.
Vous recherchez une formation Excel alignée avec vos besoins et enjeux opérationnels ? Nos formations Excel Niveau 1 et Excel Niveau 2, adaptées aux besoins opérationnels des entreprises, visent à renforcer les compétences Excel de vos équipes.
Explorez dès maintenant notre programme de la formation certifiante Excel intégral pour aligner vos collaborateurs avec les outils essentiels pour une efficacité opérationnelle optimale. Maximisez le potentiel de votre entreprise grâce à une maîtrise avancée d’Excel !
