Guide : Exercices Corrigés sur Excel avec Solutions

Ce guide propose une série d’exercices pratiques pour vous entraîner sur Microsoft Excel. Chaque exercice est accompagné d’une solution détaillée pour vous aider à maîtriser des concepts essentiels, tels que les formules, les graphiques, les tableaux dynamiques, et plus encore.

1. Calculer des Totaux avec SOMME()

Exercice :

Une entreprise souhaite connaître le total des ventes réalisées sur une semaine. Voici les données des ventes par jour :

• Objectif : Calculez le total des ventes de la semaine.

Solution :

1. Placez les données dans Excel.
2. Dans une cellule (ex. B7), saisissez la formule suivante : =SOMME(B2:B6)
3. Le total affiché sera : 3100 €.

2. Moyenne des Notes avec MOYENNE()

Exercice :

Un professeur a noté ses élèves. Voici les résultats :

• Objectif : Calculez la moyenne des notes.

Solution :

1. Entrez les données dans Excel.
2. Saisissez la formule dans une cellule (ex. B6) : =MOYENNE(B2:B5)
3. La moyenne sera : 13,5.

3. Appliquer une Formule Conditionnelle avec SI()

Exercice :

Un gestionnaire veut identifier les produits avec des stocks faibles. Voici les données :

• Objectif : Si le stock est inférieur à 10, affichez “Réapprovisionnement”, sinon affichez “OK”.

Solution :

1. Entrez les données dans Excel.
2. Dans une cellule (ex. C2), saisissez la formule suivante : =SI(B2<10,"Réapprovisionnement","OK")
3. Copiez la formule pour les autres lignes.
4. Résultat :
   • Produit B et Produit D afficheront “Réapprovisionnement”.
   • Produit A et Produit C afficheront “OK”.

4. Créer un Tableau Dynamique

Exercice :

Un responsable veut analyser les ventes par produit et par mois. Voici les données :

• Objectif : Créez un tableau dynamique pour totaliser les ventes par mois et par produit.

Solution :

1. Sélectionnez les données.
2. Allez dans Insertion > Tableau Croisé Dynamique.
3. Ajoutez :
   • Lignes : Produit.
   • Colonnes : Mois.
   • Valeurs : Ventes (€).
4. Résultat : Janvier Février Produit A 1000 1200 Produit B 800 950

5. Calculer des Intérêts Simples avec une Formule

Exercice :

Un investisseur place une somme de 10,000 € à un taux d’intérêt annuel de 5% pendant 3 ans.

• Objectif : Calculez les intérêts simples générés.

Solution :

1. Dans Excel, saisissez les valeurs suivantes :
   • A1 : Capital (10,000).
   • A2 : Taux (5%).
   • A3 : Durée (3 ans).
2. Dans une cellule, saisissez la formule : =A1 * A2 * A3
3. Résultat : 1,500 € d’intérêts.

6. Filtrer des Données avec FILTRER() (Office 365)

Exercice :

Affichez uniquement les produits dont les ventes sont supérieures à 1000 €.

• Objectif : Extraire uniquement les ventes >1000.

Solution :

1. Saisissez les données.
2. Dans une cellule, entrez la formule : =FILTRER(A2:B5,B2:B5>1000)
3. Résultat : Produit Ventes (€) Produit B 1200 Produit C 1500

7. Identifier des Doublons avec NB.SI()

Exercice :

Une liste contient plusieurs entrées de produits. Identifiez les doublons.

• Objectif : Marquez “Doublon” pour les produits apparaissant plus d’une fois.

Solution :

1. Entrez les données.
2. Dans une cellule (ex. B2), saisissez : =SI(NB.SI(A:A,A2)>1,"Doublon","Unique")
3. Copiez la formule pour les autres lignes.
4. Résultat :
   • Produit A et Produit B afficheront “Doublon”.
   • Produit C et Produit D afficheront “Unique”.

8. Calculer des Pourcentages avec une Formule

Exercice :

Un étudiant a obtenu 75 points sur un total de 100.

• Objectif : Calculez le pourcentage de réussite.

Solution :

1. Saisissez :
   • A1 : Score obtenu (75).
   • A2 : Score total (100).
2. Dans une cellule, entrez la formule : =(A1 / A2) * 100
3. Résultat : 75%.

9. Gérer des Erreurs avec SIERREUR()

Exercice :

Une liste contient des valeurs à diviser. Évitez les erreurs de division par zéro.

• Objectif : Calculez les résultats tout en évitant les erreurs.

Solution :

1. Dans une cellule (ex. C2), saisissez : =SIERREUR(A2/B2,"Erreur")
2. Résultat : Valeur Diviseur Résultat 10 2 5 15 0 Erreur 20 4 5

10. Générer des Graphiques à partir de Données

Exercice :

Créez un graphique représentant les ventes suivantes :

Solution :

1. Sélectionnez les données.
2. Allez dans Insertion > Graphiques.
3. Choisissez un type de graphique (barres, colonnes, lignes).
4. Votre graphique illustrera les ventes par produit.

Guide : Explications sur les Formules Complexes dans Excel

Les formules complexes dans Excel combinent plusieurs fonctions, références et opérations pour résoudre des problèmes avancés. Elles permettent d’analyser des données, d’automatiser des calculs et d’effectuer des tâches qui seraient fastidieuses manuellement. Voici une explication détaillée des composants et des exemples de formules complexes.

1. Décomposer une Formule Complexe

Exemple : Formule complexe

=SI(ET(A1>50, B1<100), SOMME(A1:A10) / NB.SI(B1:B10,">20"), "Valeur par défaut")

• Étapes de décomposition :
  1. SI() : La structure conditionnelle principale.
     • Si les deux conditions d’ET() sont vraies, exécute le calcul.
     • Sinon, renvoie “Valeur par défaut”.
  2. ET() : Vérifie plusieurs conditions :
     • A1 > 50 : La première condition.
     • B1 < 100 : La seconde condition.
  3. SOMME(A1:A10) : Calcule la somme de la plage.
  4. NB.SI(B1:B10,”>20″) : Compte les valeurs dans une plage respectant un critère.
  5. Division : Divise le total par le nombre d’éléments respectant la condition.

2. Structure Générale des Formules Complexes

Une formule complexe typique combine plusieurs éléments :

• Conditions logiques : Utilisation de SI(), ET(), OU().
• Références dynamiques : Plages nommées ou dynamiques.
• Fonctions imbriquées : Une fonction à l’intérieur d’une autre (ex. SI() avec SOMME()).
• Manipulation de texte et de dates : Avec GAUCHE(), DROITE(), DATEDIF().
• Gestion des erreurs : Avec SIERREUR() ou SI.NON.DISP().

3. Exemples de Formules Complexes

Exemple 1 : Analyse Multi-Critères

• Contexte : Une entreprise souhaite attribuer une prime en fonction des ventes et de l’ancienneté :
  • Si les ventes > 50,000 ET l’ancienneté > 5 ans → Prime = 10%.
  • Sinon, aucune prime.
• Formule : =SI(ET(A1>50000,B1>5),A1*0.1,0)
• Explication :
  • ET(A1>50000, B1>5) : Vérifie deux conditions.
  • A1 * 0.1 : Calcule la prime.
  • 0 : Renvoie 0 si aucune condition n’est remplie.

Exemple 2 : Recherche avec Condition

• Contexte : Trouver le salaire d’un employé en fonction de son ID.

• Formule : =SIERREUR(RECHERCHEV(2,A1:C10,3,FAUX),"Non trouvé")
• Explication :
  • RECHERCHEV(2, A1:C10, 3, FAUX) : Recherche l’ID 2 dans la colonne A et retourne le salaire depuis la 3ᵉ colonne.
  • SIERREUR() : Affiche “Non trouvé” si l’ID n’est pas présent.

Exemple 3 : Gestion des Dates

• Contexte : Calculer le statut d’une commande (livrée, en retard, en cours).

• Formule : =SI(AUJOURDHUI()>B1,"En retard",SI(AUJOURDHUI()=B1,"Livraison aujourd'hui","En cours"))
• Explication :
  • AUJOURDHUI()>B1 : Vérifie si la date actuelle dépasse la date de livraison.
  • AUJOURDHUI()=B1 : Vérifie si la date actuelle correspond à la date de livraison.
  • Sinon, renvoie “En cours”.

4. Formules Dynamiques pour des Données Complexes

Exemple : Extraction Dynamique

• Contexte : Extraire tous les produits avec des ventes > 10,000.

• Formule : =FILTRER(A1:B10,B1:B10>10000)
• Résultat : Produit Ventes (€) Produit A 15,000 Produit C 12,000

5. Formules Complexes pour les Tableaux Croisés Dynamiques

Exemple : Calcul Personnalisé

• Contexte : Ajouter une colonne calculée dans un tableau croisé dynamique pour calculer le pourcentage des ventes.

• Formule : =SOMME(B2:B4) / SOMME.TOTALE(B2:B4)
• Explication :
  • SOMME(B2:B4) : Calcule les ventes totales d’une plage.
  • SOMME.TOTALE(B2:B4) : Calcule les ventes totales globales.

6. Astuces pour Optimiser les Formules Complexes

1. Décomposez les Formules :
   • Testez chaque partie séparément dans une colonne auxiliaire.
2. Utilisez des Noms de Plages :
   • Donnez un nom aux plages pour améliorer la lisibilité.
   • Exemple : =SOMME(Sales) / NB.SI(Region, "Europe")
3. Évitez les Redondances :
   • Stockez les résultats intermédiaires dans des colonnes ou cellules pour éviter les calculs répétés.
4. Gérez les Erreurs :
   • Entourez vos formules avec SIERREUR() pour éviter les interruptions : =SIERREUR(Formule,"Erreur détectée")
5. Affichez les Formules :
   • Appuyez sur CTRL + ` pour afficher toutes les formules dans une feuille.
6. Documentez Vos Formules :
   • Ajoutez des notes pour expliquer des formules complexes.

Exercices Corrigés sur les Formules Complexes dans Excel

Voici une sélection d’exercices pratiques pour s’entraîner à utiliser des formules complexes dans Excel. Chaque exercice est accompagné d’une solution détaillée pour mieux comprendre les concepts et leur application.

Exercice 1 : Analyse Multi-Critères avec SI() et ET()

Contexte :

Un magasin offre une réduction si :

• Le montant des achats est supérieur à 100 €.
• Le client est membre du programme fidélité.

Objectif : Complétez la colonne “Réduction” avec “Oui” ou “Non”.

Solution :

1. Formule à utiliser : =SI(ET(B2>100,C2="Oui"),"Oui","Non")
2. Étapes :
   • Vérifiez que le montant dans B2 est supérieur à 100.
   • Vérifiez que la cellule C2 contient “Oui”.
   • Si les deux conditions sont vraies, renvoyez “Oui”, sinon “Non”.
3. Résultat final : Client Montant (€) Fidélité Réduction Alice 120 Oui Oui Bob 80 Non Non Claire 150 Oui Oui David 50 Oui Non

Exercice 2 : Somme Conditionnelle avec SOMME.SI()

Contexte :

Une entreprise souhaite connaître le total des ventes réalisées par région.

Objectif : Calculez le total des ventes pour la région “Nord”.

Solution :

1. Formule à utiliser : =SOMME.SI(A2:A7,"Nord",B2:B7)
2. Étapes :
   • A2:A7 : Plage contenant les régions.
   • “Nord” : Critère à vérifier.
   • B2:B7 : Plage contenant les montants à additionner.
3. Résultat :
   • Total des ventes pour la région Nord = 3600 €.

Exercice 3 : Gestion des Dates avec SI()

Contexte :

Vous suivez des commandes avec une date d’échéance. Déterminez si chaque commande est “En retard”, “Livrée” ou “En cours”.

Objectif : Complétez la colonne “Statut”.

Solution :

1. Formule à utiliser : =SI(AUJOURDHUI()>B2,"En retard",SI(AUJOURDHUI()<B2,"En cours","Livrée"))
2. Étapes :
   • AUJOURDHUI() : Utilisez la date actuelle.
   • Comparez la date actuelle avec la date d’échéance.
   • Renvoyez “En retard”, “En cours”, ou “Livrée” selon le cas.
3. Résultat (en supposant que la date actuelle est le 16/10/2023) : Commande Échéance Statut C1 15/10/2023 En retard C2 10/10/2023 En retard C3 18/10/2023 En cours

Exercice 4 : Recherche avec RECHERCHEV()

Contexte :

Vous avez une liste de produits avec leurs prix et vous souhaitez trouver le prix d’un produit spécifique.

Objectif : Trouvez le prix de “Produit B”.

Solution :

1. Formule à utiliser : =RECHERCHEV("Produit B",A2:B4,2,FAUX)
2. Étapes :
   • “Produit B” : Élément recherché.
   • A2:B4 : Tableau contenant les données.
   • 2 : Indique que vous voulez retourner la valeur de la deuxième colonne.
   • FAUX : Recherche exacte.
3. Résultat :
   • Le prix de “Produit B” est 200 €.

Exercice 5 : Filtrer des Données Dynamiquement (Office 365)

Contexte :

Vous avez une liste de produits avec leurs ventes. Vous souhaitez extraire uniquement les produits ayant des ventes > 1000 €.

Objectif : Créez une liste filtrée.

Solution :

1. Formule à utiliser : =FILTRER(A2:B5,B2:B5>1000)
2. Étapes :
   • A2:B5 : Plage contenant les données.
   • B2:B5>1000 : Condition de filtrage.
3. Résultat : Produit Ventes (€) Produit B 1200 Produit C 1500

Exercice 6 : Calcul de Moyenne Pondérée avec SOMMEPROD()

Contexte :

Un étudiant a obtenu les notes suivantes avec leurs coefficients respectifs :

Objectif : Calculez la moyenne pondérée.

Solution :

1. Formule à utiliser : =SOMMEPROD(A2:A4,B2:B4) / SOMME(B2:B4)
2. Étapes :
   • SOMMEPROD(A2:A4, B2:B4) : Multiplie chaque note par son coefficient et additionne.
   • SOMME(B2:B4) : Additionne les coefficients.
3. Résultat :
   • Moyenne pondérée = 13,83.

Exercice 7 : Identifier des Doublons avec NB.SI()

Contexte :

Vous avez une liste de produits et vous souhaitez identifier les doublons.

Objectif : Marquez les doublons.

Solution :

1. Formule à utiliser : =SI(NB.SI(A:A,A2)>1,"Doublon","Unique")
2. Étapes :
   • NB.SI(A:A, A2) : Compte combien de fois la valeur de A2 apparaît dans la colonne.
   • Si le résultat est >1, affichez “Doublon”.
3. Résultat : Produit Statut Produit A Doublon Produit B Unique Produit C Unique Produit A Doublon Produit D Unique

Ces exercices couvrent différents aspects des formules complexes dans Excel, du conditionnel à la gestion des données dynamiques.

Please follow and like us:

Autres articles

Guide : Consolidation des données Excel

La consolidation des données dans Excel permet de regrouper et...

Read more

Tableau Journalier de Gestion de Chantier dans...

La gestion d’un chantier est une tâche complexe qui nécessite...

Read more

Guide : Comment Dupliquer une Feuille dans...

Dupliquer une feuille dans Excel est une tâche courante lorsque...

Read more

Guide : Fractionner les Cellules Excel

Fractionner une cellule dans Excel signifie diviser le contenu d’une...

Read more

Guide : Comment Sauter une Ligne dans...

Dans Excel, il est possible d'insérer plusieurs lignes de texte...

Read more

Afficher le Nombre d’Occurrences Lors de la...

L’objectif est de configurer un tableau Excel où, lorsque vous...

Read more