Microsoft Excel est l’un des logiciels de bureautique le plus puissant du marché. Cet outil permet de gérer et d’analyser vos données, que vous soyez débutant ou expert.
Avec plus de 450 fonctions, Excel offre à la fois des fonctionnalités simples et complexes pour exploiter pleinement sa puissance.
Dans ce guide, nous vous présentons les fonctions Excel avancées incontournables (logiques, statistiques, recherches…) avec des exemples et cas pratiques.
Les 12 fonctions Excel avancées à connaître
La fonction SI avec ET / OU
Parmi les fonctions Excel les plus utilisées, l’une d’entre elles reste indétrônable : la fonction SI.
Cette fonction logique renvoie une valeur spécifiée si la condition indiquée est vraie, et une autre si elle est fausse. Sa syntaxe est la suivante :
- =SI(test_logique_, valeur_si_VRAI, valeur_si_FAUX).
Alors que c’est une formule assez basique, une fois combinée à ET ainsi qu’à OU, les formules deviennent très intéressantes pour analyser de grandes quantités de données.
Imaginons que vous souhaitez savoir si vous devez arrêter ou continuer la vente d’un article en particulier. Vos critères pour continuer à en vendre sont les suivants :
- Nombre de ventes supérieur à 15 000 unités
- Taux de satisfaction client supérieur à 60 %
SI associée à OU indiquera qu’il faut continuer la commercialisation de l’article si au moins une condition est remplie, tandis que SI associée à ET indiquera de continuer uniquement si les deux conditions le sont.
Exemple SI combinée à OU
- =SI(OU(C4>15,D4>60), »Continuer », »Arrêter »)
=SI(OU est le début de la formule SI, avec l’ajout de la fonction « OU » (il est toujours nécessaire d’ouvrir une parenthèse pour ajouter des fonctions) ;
(C4>15,D4>60) sont les calculs avec les opérateurs -symboles mathématiques- adaptés ;
« Continuer » est la valeur si VRAIE ;
« Arrêter » est la valeur si FAUX.
Exemple SI combinée à ET
Avec SI + ET, on note qu’il n’y a qu’un seul article qui respecte les deux conditions, donc les autres renvoient une valeur si FAUX :
Les fonctions avancées SOMME et NB : somme.si, somme.si.ens, nb.si, nb.si.ens
Les fonctions SOMME et NB s’imposent également parmi les plus utilisées sur Excel.
Et ce qui est particulièrement intéressant, c’est que le logiciel permet de les combiner à la fonction SI pour réaliser des formules d’additions plus poussées.
SOMME.SI et NB.SI
Ces deux fonctions sont essentielles : SOMME.SI additionne les valeurs de toutes les cellules qui répondent à un critère spécifique, tandis que NB.SI compte le nombre de ces cellules.
Par exemple, supposons que vous souhaitez connaître le montant total des ventes d’un produit et le nombre d’unités vendues.
SOMME.SI vous permet d’additionner le montant des ventes de cet article avec la formule suivante :
- =SOMME.SI(B3:B9; « Produit A »; C3:C9)
B3:B9 est la plage de cellules où se trouve le critère ;
« Produit A » est le critère ;
C3:C9 est la plage de cellules à additionner.
La fonction NB.SI vous permet de compter combien d’unités ont été vendues, et plus concrètement, combien de fois le terme « Produit A » apparaît dans votre plage de cellules :
- =NB.SI(B3:B9; « Produit A »)
SOMME.SI.ENS et NB.SI.ENS
Les fonctions SOMME.SI.ENS et NB.SI.ENS vous permettent d’aller plus loin en additionnant ou comptant les cellules qui ne répondent pas à un, mais à plusieurs critères.
Ainsi, SOMME.SI.ENS peut calculer le total de ventes de l’article en question (critère 1), uniquement dans la région Nord (critère 2) :
- =SOMME.SI.ENS(C3:C9,B3:B9, »Produit A »,D3:D9, »Nord »)
NB.SI.ENS peut, là aussi, compter le nombre de produits C (critère 1) vendues dans la région Sud (critère 2) :
- =NB.SI.ENS(B3:B9, »Produit C »,D3:D9, »Sud »)
SIERREUR / IFERROR
Face à beaucoup de données, il arrive parfois que les formules renvoient diverses erreurs : #N/A, #VALEUR!, #REF!…Dans ses fonctionnalités, Excel propose de gérer ces erreurs avec la fonction SIERREUR (IFERROR en anglais) qui renvoie une valeur spécifique en cas d’erreur.
Imaginons que pour identifier le prix unitaire de chaque produit, vous avez divisé les montants des ventes par le nombre de produits vendus. Vous utilisez alors, sur le Produit A, la formule suivante :
- =C3/D3 (que vous appliquez ensuite à toutes les autres lignes).
Sauf que certains produits ne se sont pas vendus, ce qui mène au message d’erreur #DIV/0. Avec lui, Excel vous indique que le calcul a tenté de diviser un nombre (ou une cellule vide) par 0.
Pour traiter cette situation, vous pouvez utiliser la fonction SIERREUR comme suit :
- =SIERREUR(C3/D3, »Inconnu »)
Vous ajoutez à votre formule de base le « =SIERREUR » au début, et à la fin, le critère à renvoyer en cas d’erreur.
RechercheV / VLOOKUP
RECHERCHEV, pour « Recherche Verticale », est l’une des fonctions Excel les plus utilisées pour extraire des informations. Elle cherche une valeur dans une colonne et renvoie la valeur correspondante d’une autre colonne de la même ligne.
Supposons que vous souhaitez connaître le prix de l’article B. Au lieu de chercher la réponse manuellement, vous pouvez utiliser RECHERCHEV avec la formule qui suit :
- =RECHERCHEV(« Article C »; B4:D8; 2; FAUX)
« Article C » est la valeur recherchée ;
B4:D8 est la plage où la fonction doit effectuer la recherche ;
2 est le numéro de la colonne dans la plage d’où récupérer la valeur (ici, la colonne « 2 » correspond au « Prix ») ;
FAUX indique à Excel que vous souhaitez une correspondance exacte, et non approximative (dans ce cas, il faudrait indiquer « VRAI »).
Pour en savoir plus sur la fonction RECHERCHEV, n’hésitez pas à consulter notre guide complet sur le sujet : faire une RECHERCHEV sur Excel.
ARRONDI (ROUND) + Calculs
Comme son nom l’indique, la fonction ROUND arrondit les valeurs. Elle est très utile pour mettre en forme et exploiter des chiffres financiers ou diverses données, notamment en la combinant avec des calculs.
Pour mieux la comprendre, voici un cas d’utilisation de la fonction ARRONDI :
Vous voulez appliquer une réduction sur le prix de certains de vos articles et souhaitez obtenir un chiffre rond, sans décimales. Vous utilisez alors ARRONDI de la façon suivante :
- =ARRONDI(C4*(1-D4/100),0)
(C4*(1-D4/100) calcule le prix réduit en appliquant le pourcentage de réduction.
,0) indique au logiciel d’arrondir le résultat à 0 décimal pour obtenir un prix final net. Il est possible d’utiliser « 1 » pour un chiffre à un décimal, « 2 » pour deux, etc.
Sans la fonction ARRONDI, le résultat des prix réduits aurait affiché des décimales :
ECARTYPE
La fonction ECARTYPE, ou STDEV en anglais, est très utile pour les analyses mathématiques. Comme son nom l’indique, elle permet d’identifier l’écart type des valeurs d’une plage de données par rapport à une moyenne spécifique.
Supposons que vous souhaitiez évaluer les performances de vos élèves en déterminant à quel point leurs notes s’écartent de la moyenne.
Pour cela, vous utilisez la formule suivante, en indiquant simplement la colonne contenant les valeurs qui doivent être soumis au calcul :
- =ECARTYPE(C3:C9)
L’exemple est basique, mais cette fonction peut être exploitée dans de nombreuses situations, par exemple, pour mesurer la volatilité des actions en bourse.
MODE
MODE permet d’identifier quelle valeur revient le plus souvent dans une table de données. En d’autres termes, elle permet d’identifier les tendances et d’analyser des informations sous l’angle de leur fréquence.
Imaginons qu’afin d’ajuster vos prévisions et promotions, vous souhaitez savoir quel montant de vente a été réalisé le plus souvent. Vous utilisez alors :
- =MODE(C4:C10)
SOUS-TOTAL
SOUS.TOTAL est une fonction avancée sur Excel qui permet de calculer des cellules spécifiques d’une matrice. Vous pouvez filtrer votre tableau et comptabiliser uniquement certaines données, ce qui est intéressant pour analyser et segmenter les informations.
Sa syntaxe est la suivante :
- =SOUS.TOTAL(numero_de_la_fonction, référence_1, référence_2, etc.)
Le premier argument est le plus intéressant de la formule, car il représente le type de calcul à faire.
Supposons que vous souhaitiez connaître le total des ventes d’un employé en particulier, en l’occurrence Alice.
Commencez par écrire =SOUS.TOTAL(
Une fois la parenthèse ouverte, vous voyez apparaître des dizaines de possibilités. Chacune d’elles représente une fonction en particulier et un type de calcul que SOUS.TOTAL vous permet de faire.
Pour connaître les ventes d’Alice, vous devez utiliser la fonction « SOMME » qui correspond à l’option 9 (argument 1 de votre formule SOUS.TOTAL).
Après, vous n’avez plus qu’à sélectionner la plage de cellules du tableau qui présente le total des ventes (argument 2) :
Le résultat qui s’affiche est alors le même que le total. Sauf qu’à présent, si vous filtrez votre tableau, le sous-total change en fonction de vos choix.
Ce n’est qu’un exemple de SOUS.TOTAL associée à SOMME, mais il est également possible de faire d’autres calculs avec d’autres fonctions. Par exemple, identifier la moyenne des ventes de chaque vendeur, avec :
- =SOUS.TOTAL(101, Tableau1[Total des ventes])
À savoir que « 101 » correspond à la fonction « MOYENNE ».
Vous pouvez aller plus loin en utilisant les fonctions ARRONDI.SUP et ARRONDI.INF pour indiquer à Excel si vous souhaitez arrondir un nombre à sa valeur supérieure ou inférieure.
Les autres fonctionnalités d’Excel à maîtriser
Contrôle de saisies et listes déroulantes
Outre les fonctions, Excel propose plusieurs fonctionnalités avancées pour optimiser la gestion des données, notamment les listes déroulantes. Celles-ci permettent aux utilisateurs de sélectionner des éléments dans une liste prédéfinie.
Les listes déroulantes limitent les saisies, ce qui assure des données cohérentes, surtout dans les documents partagés ou les erreurs et les approximations peuvent vite survenir.
Au lieu de saisir chaque information manuellement, l’utilisateur peut la sélectionner directement dans la liste.
Voici comment créer une liste déroulante sur Excel :
- Insérez les éléments que vous souhaitez voir dans votre liste déroulante sur une feuille. Par exemple, saisissez les noms des prestataires dans une feuille dédiée, comme « Prestataires ».
2. Rendez-vous sur la feuille où vous souhaitez afficher la liste déroulante (ici « Fichier de suivi ») et sélectionnez les cellules concernées. En général, vous n’aurez qu’à sélectionner la colonne en question.
3. Dans le menu, cliquez sur l’onglet « Données », puis sur « Validation des données ».
4. Dans la fenêtre de validation, allez au champ « Autoriser » et sélectionnez « Liste ». Cliquez ensuite sur le champ « Source », puis entrez la plage de cellules contenant les éléments de la liste déroulante (dans cet exemple, cela correspond aux noms des prestataires dans « Prestataires »). Une fois terminé, cliquez sur « OK ».
Vous pouvez également entrer des options de listes déroulantes de façon manuelle.
Pour la colonne « Statut de paiement », tapez directement « Payé, Non Payé, En attente » (séparés par des virgules) dans le champ source de la boîte de dialogue « Validation des données ».
Filtres avancés / élaborés
Bien que pratiques, les filtres automatiques d’Excel peuvent parfois être insuffisants. Les filtres avancés, plus personnalisables, offrent davantage de fonctionnalités et permettent de trier, filtrer, organiser et exploiter des informations de manière plus poussée, en utilisant plusieurs critères.
Voici les quatre étapes nécessaires pour réaliser un filtre avancé :
- Copiez les en-têtes de colonnes de votre tableau dans un autre endroit sur Excel (au-dessus ou en dessous de votre tableau, sur une autre feuille, etc).
- Sous ces en-têtes, indiquez les critères de filtrage souhaités. Par exemple, pour filtrer les bébés de sexe féminin, vaccinés et avec suivi postnatal ; ce qui fait trois critères.
3. Sélectionnez ensuite le tableau à filtrer, puis allez dans l’onglet « Données » et cliquez sur « Filtres avancés » (ou juste « Avancé », selon la version du logiciel que vous utilisez).
4. Dans le champ « Zone de critères », sélectionnez la plage contenant vos critères et appuyez sur « OK ».
Votre filtre avancé est désormais en place !
Tableau croisé dynamique
Nous ne pouvons pas parler des fonctionnalités avancées d’Excel sans évoquer les tableaux croisés dynamiques (TCD). Bien que certains pensent que les filtres suffisent, les TCD permettent d’effectuer des calculs directement sur vos données et vous offrent une vision à 360°de votre data, tout en étant 100 % exploitables.
Les TCD peuvent sembler intimidants, mais rassurez-vous : ils sont moins compliqués que vous ne le pensez.
Pour en créer, il vous suffit de sélectionner n’importe quelle cellule de vos données, puis de vous rendre dans l’onglet « Insertion » et de cliquer sur « Tableaux croisés recommandés ».
Excel vous propose des suggestions dans la boîte de dialogue qui s’affiche. Choisissez celle qui vous convient et cliquez sur « OK ». Votre TCD est maintenant créé !
Vous pouvez ajuster les données affichées en structurant les champs de manière dynamique à gauche, selon vos besoins.
Les possibilités qu’offrent les TCD sont réellement remarquables. Pour maîtriser comme il se doit cet outil, consultez notre guide complet à ce sujet : faire un tableau croisé dynamique de A à Z.
Les avantages des fonctions avancées d’Excel
Maîtriser les fonctions plus poussées d’Excel permet principalement deux choses :
- Acquérir une compétence très recherchée sur le marché de l’emploi ;
- Utiliser des fonctionnalités qui améliorent votre efficacité.
Microsoft Excel est un outil utilisé par la majorité des entreprises et (re)connu de manière universelle. Pourtant, très peu de personnes maîtrisent réellement toute sa puissance et se contentent des bases. Or, savoir utiliser pleinement cet outil est une compétence précieuse qui vous sera utile tout au long de votre carrière.
Ces fonctionnalités Excel permettent de simplifier l’exploitation des données et d’automatiser de nombreuses tâches. Cela représente un gain de temps significatif et de meilleures performances à tous les niveaux.
Se former aux fonctions avancées grâce à une formation Excel adaptée
N’attendez plus pour bénéficier d’une formation Excel avancée certifiante, 100 % à distance et éligible au CPF. Le point noir des formations classiques est leur manque de flexibilité, avec des horaires contraignants qui chamboulent parfois votre équilibre vie professionnelle / vie personnelle.
Nous vous proposons une formation dispensée par des experts, avec des cours personnalisés adaptés à vos contraintes et obligations. Profitez de ce parcours sur mesure qui vous permet d’obtenir plusieurs certifications et, surtout, de maîtriser pleinement Excel :
- Formules complexes (statistiques, calculs, recherche…)
- Graphiques techniques
- Commandes macros
- Scénarios, solveur et valeur cible Excel (outils d’analyse)
- etc.
Cette formation est spécialement conçue pour les personnes de niveau intermédiaire qui souhaitent accéder à un niveau supérieur sur Excel.
Autres articles concernant le Compte Personnel de Formation
