Microsoft Excel est bien plus qu’un simple tableur. C’est une véritable machine de traitement et d’analyse de données. Si vous maîtrisez certaines formules avancées, vous pourrez transformer vos données brutes en informations exploitables en un rien de temps. Dans cet article, nous allons explorer les 10 formules Excel avancées qui vous permettront de gagner du temps, de simplifier vos calculs et de mieux maîtriser vos données.
Voir aussi :
- Comment créer les formules calculs en Excel ?
- Comment créer un tableau Excel dynamique ?
- Top 7 formules Excel que tout le monde devrait connaître
- Créer la facture proforma dans Excel à partir de modèle
- Syntaxes fonctions Excel : Guide complet pratique
- Créer document Excel à partir de zéro
- Modifier le format date dans Excel : Guide pratique étape par étape
- Comment rédiger un rapport ?
- Facture Excel 2013-2021 : Créer et Télécharger gratuitement
- Télécharger les modèles de graphique prédéfinis.
- Tutoriel : Créer des graphiques professionnels sur Excel
- Comment automatiser vos tâches avec les macros dans Excel
Alors, découvrons ensemble le top 10 formules Excel avancées :
1. INDEX et EQUIV : Trouver des données dynamiquement
Pourquoi utiliser INDEX et EQUIV ?
Ces deux formules combinées sont une alternative puissante à RECHERCHEV (ou VLOOKUP) et permettent de rechercher des données dynamiquement, même si la colonne de recherche n’est pas la première.
Mais, comment ça marche ?
- INDEX : Renvoie une valeur dans une plage de cellules en fonction d’une ligne et d’une colonne données.
- EQUIV : Trouve la position d’une valeur dans une plage.
Par exemple, supposons le tableau suivant :
| Produit | Prix (€) | Stock |
| Ordinateur | 800 | 12 |
| Smartphone | 500 | 25 |
| Tablette | 300 | 15 |
Ici, l’objectif est de trouver le prix du produit « Smartphone ».
Dans ce cas, il faut appliquer la formule ci-dessous :
=INDEX(B2:B4;EQUIV("Smartphone";A2:A4;0))
EQUIV("Smartphone";A2:A4;0)renvoie la position de « Smartphone » (ligne 2).INDEX(B2:B4;2)renvoie la valeur correspondante dans la colonne « Prix », soit 500.
2. SOMME.SI et SOMME.SI.ENS : Addition conditionnelle
En revanche, pourquoi utiliser aussi la formule SOMME.SI ?
Effectivement, cette formule vous permet de faire la somme des valeurs respectant un ou plusieurs critères.
Par exemple, supposons ce tableau des ventes :
| Vendeur | Région | Ventes (€) |
| Alice | Nord | 10 000 |
| Bob | Sud | 15 000 |
| Claire | Nord | 12 000 |
Alors, ici l’objectif est de calculer le total des ventes dans la région « Nord ».
Pour ce faire, il faut donc utilise la Formule SOMME.SI ci-dessous :
=SOMME.SI(B2:B4;"Nord";C2:C4)
Par contre, utilise la formule SOMME.SI.ENS (plusieurs critères) : Si vous souhaitez additionner les ventes pour la région « Nord » uniquement pour Alice :
=SOMME.SI.ENS(C2:C4;A2:A4;"Alice";B2:B4;"Nord")
3. SI et SI.IMBRIQUÉS : Effectuer des choix logiques
Et alors, pourquoi utiliser SI ? La fonction SI permet d’effectuer des tests logiques pour retourner une valeur si le test est VRAI et une autre si le test est FAUX.
Exemple : Supposons un tableau d’évaluation :
| Étudiant | Note |
| Alice | 15 |
| Bob | 8 |
| Claire | 18 |
Objectif : Attribuer « Admis » si la note est ≥ 10, et « Recalé » sinon.
Et donc, la formule SI à utiliser sera alors :
=SI(B2>=10;"Admis";"Recalé")
Par contre, pour des critères plus complexes, vous pouvez imbriquer plusieurs SI :
=SI(B2>=15;"Très bien";SI(B2>=10;"Admis";"Recalé"))
4. RECHERCHEV (ou VLOOKUP) : Rechercher des données
La fonction RECHERCHEV est l’une des formules les plus populaires pour rechercher une valeur dans une table.
Par exemple :
| Produit | Prix (€) |
| Ordinateur | 800 |
| Smartphone | 500 |
| Tablette | 300 |
L’objectif est de lrouver le prix du produit « Tablette ».
Formule :
=RECHERCHEV("Tablette";A2:B4;2;FAUX)
"Tablette": Valeur recherchée.A2:B4: Plage de recherche.2: Colonne contenant les données à retourner.FAUX: Recherche une correspondance exacte.
5. NB.SI et NB.SI.ENS : Compter des valeurs selon des critères
Pourquoi utiliser NB.SI ? NB.SI est utilisé pour compter le nombre de cellules respectant un critère dans une plage.
Par exemple :
| Vendeur | Région |
| Alice | Nord |
| Bob | Sud |
| Claire | Nord |
En effet, ici, l’objectif est compter combien de vendeurs travaillent dans la région « Nord ».
D’où, la formule NB.SI :
=NB.SI(B2:B4;"Nord")
En somme, pour plusieurs critères, utilisez NB.SI.ENS :
=NB.SI.ENS(B2:B4;"Nord";A2:A4;"Alice")
6. CONCATENER ou CONCAT : Combiner des textes
Pourquoi utiliser CONCAT ? Cette formule permet de fusionner deux ou plusieurs chaînes de texte.
Par exemple :
| Prénom | Nom |
| Alice | Dupont |
| Bob | Martin |
Ainsi donc, l’objectif est créer une colonne « Nom complet ».
Alors, la formule :
=CONCAT(A2;" ";B2)
Cependant, le résultat alors : « Alice Dupont ».
7. TEXTE : Formater vos données
Pourquoi utiliser TEXTE ? La fonction TEXTE est utilisée pour afficher des données dans un format spécifique.
Par exemple :
| Date |
| 01/01/2023 |
Ici l’objectif est d’afficher la date au format « 1er Janvier 2023 ».
Formule :
=TEXTE(A2;"jj mmmm aaaa")
8. DECALER : Référencer des plages dynamiques
Pourquoi utiliser DECALER ? DECALER permet de retourner une plage de cellules qui se déplace par rapport à une référence.
Exemple :
| Mois | Ventes (€) |
| Janvier | 10 000 |
| Février | 12 000 |
| Mars | 14 000 |
Objectif est de trouver la cellule contenant les ventes du mois suivant.
Formule :
=DECALER(B2;1;0)
Résultat : 12 000 (Février).
9. SIERREUR : Gérer les erreurs
Pourquoi utiliser SIERREUR ? SIERREUR est essentiel pour éviter des messages d’erreur tels que #N/A ou #DIV/0!.
Par exemple :
| Valeur 1 | Valeur 2 | Résultat |
| 10 | 2 | ? |
| 5 | 0 | ? |
Objectif : Évitez une erreur lorsqu’une division par zéro se produit.
Formule :
=SIERREUR(A2/B2;"Erreur : Division par zéro")
10. PUISSANCE et RACINE : Calculs mathématiques avancés
Pourquoi utiliser PUISSANCE et RACINE ?
Ces formules sont utiles pour les calculs mathématiques comme les puissances ou les racines carrées.
Exemple :
| Nombre |
| 16 |
Objectif : Calculer la racine carrée.
Formule RACINE :
=RACINE(A2)
Pour élever un nombre à une puissance :
=PUISSANCE(A2;2)
En conclusion, ces 10 formules Excel avancées sont des outils incontournables pour tout utilisateur souhaitant exploiter pleinement les capacités d’Excel. Que vous soyez analyste de données, comptable ou simplement un passionné de tableurs, ces formules vous aideront à :
- Gagner du temps.
- Automatiser vos calculs.
- Maîtriser vos données comme un pro.
Prenez le temps de pratiquer chaque formule sur vos propres données. Avec un peu de pratique, elles deviendront des réflexes et vous permettront d’atteindre des niveaux de productivité impressionnants. Alors, à vos tableurs !
