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 :

Jour	Ventes (€)
Lundi	500
Mardi	600
Mercredi	750
Jeudi	400
Vendredi	850

• 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 :

Élève	Note
Alice	15
Bob	12
Claire	17
David	10

• 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 :

Produit	Stock
Produit A	20
Produit B	5
Produit C	50
Produit D	8

• 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 :

Mois	Produit	Ventes (€)
Janvier	Produit A	1000
Janvier	Produit B	800
Février	Produit A	1200
Février	Produit B	950

• 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 €.

Produit	Ventes (€)
Produit A	800
Produit B	1200
Produit C	1500
Produit D	500

• 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.

Produit
Produit A
Produit B
Produit C
Produit A
Produit D
Produit B

• 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.

Valeur	Diviseur
10	2
15	0
20	4

• 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 :

Produit	Ventes (€)
Produit A	1000
Produit B	1500
Produit C	800

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.

ID	Nom	Salaire (€)
1	Alice	50,000
2	Bob	60,000
3	Claire	55,000

• 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).

Date Commande	Date Livraison
01/10/2023	15/10/2023
03/10/2023	10/10/2023

• 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.

Produit	Ventes (€)
Produit A	15,000
Produit B	8,000
Produit C	12,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.

Produit	Ventes (€)	Pourcentage (%)
Produit A	50,000	50%
Produit B	30,000	30%
Produit C	20,000	20%

• 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é.

Client	Montant (€)	Fidélité	Réduction
Alice	120	Oui	?
Bob	80	Non	?
Claire	150	Oui	?
David	50	Oui	?

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.

Région	Ventes (€)
Nord	1200
Sud	800
Nord	1500
Est	700
Sud	600
Nord	900

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”.

Commande	Échéance	Statut
C1	15/10/2023	?
C2	10/10/2023	?
C3	18/10/2023	?

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.

Produit	Prix (€)
Produit A	100
Produit B	200
Produit C	150

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 €.

Produit	Ventes (€)
Produit A	800
Produit B	1200
Produit C	1500
Produit D	500

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 :

Note	Coefficient
15	2
12	3
18	1

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.

Produit
Produit A
Produit B
Produit C
Produit A
Produit D

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.