Guide : Astuces pour Maîtriser les Formules Excel

Les formules d’Excel sont des outils essentiels pour analyser des données, automatiser des calculs, et extraire des informations. Que vous soyez débutant ou utilisateur avancé, ce guide vous propose des astuces pour exploiter pleinement les formules Excel.

1. Structure de Base des Formules

Commencez toujours par “=” : Toutes les formules Excel débutent par un signe égal.
Combinez opérateurs :
- Addition : =A1 + B1
- Multiplication : =A1 * B1
- Division : =A1 / B1
- Puissance : =A1 ^ B1

2. Utilisez les Fonctions de Base

a) SOMME()

Additionne une plage de cellules : =SOMME(A1:A10)

b) MOYENNE()

Calcule la moyenne d’une plage : =MOYENNE(B1:B10)

c) MIN() et MAX()

Renvoie la valeur minimale ou maximale : =MIN(C1:C10) =MAX(D1:D10)

3. Travaillez avec des Formules Conditionnelles

a) SI()

Crée une logique conditionnelle : =SI(A1>100,"Grand","Petit")

b) SI.CONDITIONS() (Excel 2016 et versions ultérieures)

Gère plusieurs conditions sans imbriquer des SI() : =SI.CONDITIONS(A1>100,"Grand",A1<50,"Petit",VRAI,"Moyen")

c) SOMME.SI()

Additionne les valeurs selon un critère : =SOMME.SI(A1:A10,">50",B1:B10)

4. Recherchez et Référencez les Données

a) RECHERCHEV()

Recherche une valeur dans une colonne et renvoie une valeur correspondante. =RECHERCHEV(100,A1:B10,2,FAUX)

b) INDEX() et EQUIV()

Combinez-les pour des recherches plus puissantes.
- Trouver une valeur : =INDEX(B1:B10,EQUIV(100,A1:A10,0))

5. Gérez les Dates et Heures

a) AUJOURDHUI() et MAINTENANT()

Renvoie la date ou l’heure actuelle : =AUJOURDHUI() =MAINTENANT()

b) DATEDIF()

Calcule la différence entre deux dates : =DATEDIF(A1,B1,"D") ' Jours =DATEDIF(A1,B1,"M") ' Mois =DATEDIF(A1,B1,"Y") ' Années

c) TEXTE()

Formate une date/heure dans un format spécifique : =TEXTE(A1,"JJ/MM/AAAA")

6. Combinez des Fonctions

Les fonctions imbriquées permettent d’effectuer des calculs complexes.

Exemple : Vérifiez si une valeur est supérieure à la moyenne :

=SI(A1>MOYENNE(A1:A10),"Au-dessus","En dessous")

7. Répétez avec des Références Absolues et Relatives

a) Références Relatives

Changent automatiquement lors de la copie d’une formule. =A1 + B1

b) Références Absolues

Restent fixes grâce au symbole $ : =$A$1 + B1

c) Mélange Absolu/Relatif

Fixe une seule partie de la référence : =$A1 + B$1

8. Utilisez des Noms de Plages

Nommez vos plages pour simplifier les formules :

Sélectionnez une plage (ex. A1:A10).
Cliquez sur Formules > Définir un nom.
Utilisez le nom dans vos formules : =SOMME(Ventes)

9. Identifiez et Corrigez les Erreurs

a) Principales erreurs

#DIV/0! : Division par zéro.
#N/A : Valeur non trouvée.
#VALEUR! : Erreur de type (texte au lieu de nombre).

b) ERREUR.TYPE()

Identifiez l’erreur spécifique.

c) SIERREUR()

Gérez les erreurs dans vos formules : =SIERREUR(A1/B1,"Erreur")

10. Exploitez les Tableaux Dynamiques

Les tableaux dynamiques facilitent la gestion des plages de données. Ils s’adaptent automatiquement à l’ajout de nouvelles données.

Exemple :

Convertissez une plage en tableau : CTRL + T.
Utilisez des références structurées dans vos formules : =SOMME(Tableau1[Colonne1])

11. Analysez et Déboguez Vos Formules

Inspecteur de Formules :
- Allez dans Formules > Auditer les Formules.
Afficher les Formules :
- Utilisez CTRL + ` pour afficher toutes les formules dans une feuille.

12. Protégez Vos Formules

Empêchez la modification accidentelle des formules :

Sélectionnez les cellules.
Faites un clic droit > Format de cellule > Protection > Cochez Verrouillée.
Allez dans Révision > Protéger la feuille.

13. Travaillez avec les Fonctions Textuelles

a) CONCAT()

Combinez des chaînes de texte : =CONCAT(A1," - ",B1)

b) GAUCHE() et DROITE()

Extraient des caractères d’une chaîne : =GAUCHE(A1,5) ' 5 premiers caractères =DROITE(A1,3) ' 3 derniers caractères

c) NBCAR()

Compte le nombre de caractères : =NBCAR(A1)

14. Créez des Formules Personnalisées

Avec Power Query ou VBA, créez des formules spécifiques à vos besoins.

Exemple avec VBA :

Function DoubleValeur(val As Double) As Double
    DoubleValeur = val * 2
End Function

15. Optimisez la Performance des Formules

Réduisez les calculs inutiles : Utilisez des plages définies au lieu de colonnes entières (A1:A100 au lieu de A:A).
Désactivez le calcul automatique temporairement :
- Allez dans Formules > Options de calcul > Manuel.
Évitez les formules volatiles (ex. MAINTENANT(), AUJOURDHUI()).

Voici une suite de conseils et astuces avancés pour maîtriser encore davantage les formules Excel, notamment dans des contextes complexes et pour des utilisateurs avancés.

16. Exploitez les Fonctions Dynamiques (Office 365 et versions récentes)

a) FILTRER()

Permet d’extraire des données correspondant à un critère : =FILTRER(A1:A10, B1:B10>100)
- Ici, les valeurs de A1:A10 sont filtrées selon la condition B1:B10 > 100.

b) UNIQUE()

Renvoie les valeurs uniques d’une plage : =UNIQUE(A1:A10)

c) TRIER()

Trie une plage par ordre croissant ou décroissant : =TRIER(A1:A10, 1, FAUX) ' Trie par la première colonne, ordre décroissant

d) SÉQUENCE()

Génère une liste de nombres séquentiels : =SEQUENCE(10,1,1,1) ' Génère les nombres de 1 à 10

17. Travaillez avec des Matrices

a) Formules matricielles classiques (versions antérieures à Office 365)

Permettent d’effectuer des calculs sur plusieurs valeurs en une seule étape.
Exemple : Produit matriciel avec MATR.PRODUIT() : =MATR.PRODUIT(A1:A3, B1:B3)
Valider avec CTRL + SHIFT + ENTER pour activer le mode matriciel.

b) Formules dynamiques (Office 365 et versions récentes)

Les formules matricielles s’étendent automatiquement sans validation spéciale.
Exemple : =A1:A10 * B1:B10 ' Multiplie chaque élément de A par B

18. Manipulez des Données Multi-Critères

a) SOMME.SI.ENS()

Additionne des valeurs en fonction de plusieurs critères. =SOMME.SI.ENS(C1:C10, A1:A10, ">50", B1:B10, "Ventes")

b) NB.SI.ENS()

Compte les cellules respectant plusieurs critères. =NB.SI.ENS(A1:A10, ">50", B1:B10, "<100")

c) Utilisation avancée de SI.CONDITIONS

Combinez plusieurs tests conditionnels : =SI.CONDITIONS(A1>100,"Excellent",A1>50,"Bon",A1>20,"Moyen",VRAI,"Faible")

19. Connectez des Données Externes

a) Utilisez Importation de données pour automatiser les mises à jour :

Allez dans Données > Obtenir et Transformer :
- Importez des données depuis des fichiers CSV, des bases de données ou des APIs.

b) Actualisation automatique :

Configurez une actualisation périodique :
- Allez dans Données > Actualiser tout.

20. Automatisez des Calculs Financiers

a) Calculs d’intérêts

Intérêt simple : =Montant * Taux * Temps
Intérêt composé : =Montant * (1 + Taux)^Temps

b) Calcul de mensualités (PMT)

Exemple : =PMT(5%/12, 60, -100000)

21. Préparez des Rapports Dynamiquement

a) Créez des graphiques basés sur des formules dynamiques

Associez des formules comme FILTRER et TRIER à des graphiques pour des visualisations interactives.

b) Utilisez les plages nommées dynamiques :

Créez une plage nommée avec une formule dynamique : =DECALER(A1,0,0,NBVAL(A:A),1)
Utilisez cette plage dans vos graphiques.

22. Protégez et Gagnez en Précision

a) Empêchez les erreurs avec SIERREUR

Enveloppez vos formules pour éviter des messages d’erreur : =SIERREUR(A1/B1, "Erreur : Division par zéro")

b) Affichez des messages personnalisés avec SI.NA() :

Exemple : =SI.NA(RECHERCHEV("Valeur", A1:B10, 2, FAUX), "Non trouvé")

23. Améliorez les Temps de Calcul

Privilégiez des plages spécifiques : A1:A100 au lieu de A:A.
Désactivez le calcul automatique lors de la saisie de grandes formules :
- Allez dans Formules > Options de calcul > Manuel.
- Réactivez une fois les modifications terminées.

24. Apprenez à Déboguer les Formules

a) Utilisez l’option Auditer les Formules

Allez dans Formules > Auditer les Formules pour voir les dépendances.

b) Affichez les formules dans les cellules

Activez le mode Afficher les Formules avec `CTRL + “.

25. Combinez Formules et Macros

Pour automatiser des calculs complexes, utilisez VBA pour enrichir les fonctionnalités :

Exemple :

Créez une fonction personnalisée avec VBA :

Function MoyennePonderee(plageValeurs As Range, plagePoids As Range) As Double
    Dim total As Double, sommePoids As Double
    Dim i As Long
    
    For i = 1 To plageValeurs.Count
        total = total + plageValeurs.Cells(i, 1).Value * plagePoids.Cells(i, 1).Value
        sommePoids = sommePoids + plagePoids.Cells(i, 1).Value
    Next i
    
    MoyennePonderee = total / sommePoids
End Function

Utilisez la fonction dans Excel : =MoyennePonderee(A1:A10, B1:B10)

🟨 Voici quelques exemples pratiques d’utilisation des formules dynamiques dans Excel (disponibles principalement dans Office 365 et Excel 2021). Ces formules permettent de gérer efficacement des données dynamiques et de réaliser des calculs complexes sans avoir besoin de copier les formules sur plusieurs lignes.

1. FILTRER() : Extraire des données spécifiques

Exemple : Filtrer les ventes supérieures à 1000

Données : Produit Ventes Produit A 500 Produit B 1500 Produit C 1200
Formule : =FILTRER(A2:B10, B2:B10>1000)
Résultat : Produit Ventes Produit B 1500 Produit C 1200

2. UNIQUE() : Trouver des valeurs uniques

Exemple : Extraire une liste des catégories uniques

Données : Catégorie Fruits Légumes Fruits Boissons
Formule : =UNIQUE(A2:A10)
Résultat : Catégorie Fruits Légumes Boissons

3. TRIER() : Trier une plage de données

Exemple : Trier les ventes par ordre décroissant

Données : Produit Ventes Produit A 500 Produit B 1500 Produit C 1200
Formule : =TRIER(A2:B10, 2, FAUX)
Résultat : Produit Ventes Produit B 1500 Produit C 1200 Produit A 500

4. SÉQUENCE() : Générer une liste dynamique

Exemple : Créer une liste de nombres de 1 à 10

Formule : =SEQUENCE(10,1,1,1)
Résultat : Nombre 1 2 3 … 10

Exemple avancé : Générer un tableau de dates

Formule : =SEQUENCE(5,2,DATE(2023,1,1),1)
Résultat (tableau 5 lignes x 2 colonnes) : Colonne 1 Colonne 2 01/01/2023 02/01/2023 03/01/2023 04/01/2023 … …

5. SOMMEPROD() : Calculer des résultats pondérés

Exemple : Calcul de la moyenne pondérée

Données : Note Poids 80 2 90 3 70 1
Formule : =SOMMEPROD(A2:A4, B2:B4) / SOMME(B2:B4)
Résultat : 83,33 (Moyenne pondérée)

6. RECHERCHEV() Dynamique avec FILTRER()

Exemple : Trouver plusieurs correspondances

Données : Nom Département Alice RH Bob IT Charlie RH
Formule : =FILTRER(A2:B10, B2:B10="RH")
Résultat : Nom Département Alice RH Charlie RH

7. Utilisation Combinée : FILTRER() + UNIQUE() + TRIER()

Exemple : Liste triée des départements uniques

Données : Nom Département Alice RH Bob IT Charlie RH David Marketing
Formule : =TRIER(UNIQUE(B2:B10))
Résultat : Département IT Marketing RH

8. TEXTE() : Manipuler des données textuelles

Exemple : Formater des dates et nombres

Données : Cellule A1 contient la date 01/01/2023.
Formule : =TEXTE(A1, "JJ/MM/AAAA")
Résultat : 01/01/2023

Exemple avancé : Combiner texte et nombre

Formule : ="Le total est " & TEXTE(1234.56, "#,##0.00 €")
Résultat : Le total est 1 234,56 €

9. Utiliser les plages dynamiques dans des graphiques

Exemple : Créer un graphique interactif avec FILTRER()

Filtrez les données dynamiquement : =FILTRER(A2:B10, B2:B10>500)
Insérez un graphique basé sur cette plage filtrée.

Le graphique se mettra automatiquement à jour lorsque les conditions changent.

10. SOMME.SI.ENS() : Somme avec plusieurs critères

Exemple : Somme des ventes de “Produit A” dans la région “Nord”

Données : Produit Région Ventes Produit A Nord 500 Produit B Sud 300 Produit A Nord 200
Formule : =SOMME.SI.ENS(C2:C10, A2:A10, "Produit A", B2:B10, "Nord")
Résultat : 700

Ces exemples montrent à quel point les formules dynamiques d’Excel peuvent simplifier des tâches complexes, offrir une meilleure flexibilité et automatiser la gestion des données.