Formation Excel Intermédiaire Gratuite
Maîtrisez RECHERCHEX, les tableaux croisés dynamiques et les dashboards. Le niveau qui fait la différence sur un CV et au quotidien en entreprise.
Ce programme suppose que vous maîtrisez les bases d'Excel (niveau débutant). Si vous débutez, commencez par là. Si vous êtes à l'aise avec SOMME, SI simple et les filtres, vous êtes au bon endroit.
Objectif de ce niveau : Être capable de construire des rapports automatiques, de rechercher des données dans des bases volumineuses et de créer un premier tableau de bord. Ce sont les compétences les plus demandées dans les offres d'emploi tertiaires.
Ce que vous maîtriserez
=RECHERCHEX : la formule de recherche moderne
RECHERCHEX (XLOOKUP en anglais) remplace RECHERCHEV. Elle est plus puissante, plus simple et cherche dans n'importe quelle direction. C'est la formule indispensable du niveau intermédiaire.
Exemple : trouver le salaire d'un employé
❌ Ancienne méthode RECHERCHEV
Ne cherche qu'à droite. Fragile si on insère une colonne. Renvoie #N/A si non trouvé.
✅ Nouvelle méthode RECHERCHEX
Cherche dans toutes les directions. Robuste. Gère les erreurs nativement.
- Dans une zone séparée, créez un champ de recherche "Rechercher un employé :"
- Avec RECHERCHEX, affichez automatiquement le département, le salaire et la date pour l'employé saisi
- Gérez le cas "employé non trouvé" avec le 4e argument de RECHERCHEX
- Bonus : cherchez à la fois le prénom ET le nom (concaténez les deux colonnes dans la recherche)
💡 Voir les formules
Département : =RECHERCHEX(G2;A2:A16;C2:C16;"Non trouvé")
Pour chercher Prénom + Nom : =RECHERCHEX(G2&" "&H2;A2:A16&" "&B2:B16;C2:C16;"Non trouvé") validez avec Ctrl+Maj+Entrée si votre version Excel le demande.
🎯 À vous de jouer
En G2, retrouvez le prix du produit dont la référence est en F2.
En G2, cherchez la référence de F2 et renvoyez son stock, ou le texte "Inconnu" si elle n'existe pas.
=INDEX + EQUIV : la recherche la plus flexible
La combinaison INDEX + EQUIV est plus ancienne que RECHERCHEX mais reste indispensable car elle fonctionne sur toutes les versions d'Excel et permet des recherches bidirectionnelles avancées.
Utilisation avancée : En imbriquant deux EQUIV, vous pouvez créer une recherche qui trouve à la fois la bonne ligne ET la bonne colonne idéal pour des matrices de données complexes. C'est impossible avec RECHERCHEV seul.
- Créez une matrice 5 vendeurs × 6 mois (Jan à Juin) avec des chiffres d'affaires fictifs
- Créez deux listes déroulantes : une pour choisir le vendeur, une pour choisir le mois
- Avec INDEX + EQUIV double, affichez le CA correspondant à la sélection
💡 Voir la formule
En supposant que les noms de vendeurs sont en A2:A6 et les mois en B1:G1 :
=INDEX(B2:G6 ; EQUIV(I2 ; A2:A6 ; 0) ; EQUIV(J2 ; B1:G1 ; 0))
où I2 = vendeur sélectionné et J2 = mois sélectionné.
🎯 À vous de jouer
En G2, trouvez la position du produit écrit en F2 dans la liste B2:B6.
En G2, renvoyez le prix du produit écrit en F2 en combinant INDEX et EQUIV.
=SOMME.SI et =NB.SI : calculer selon des critères
Ces formules permettent de calculer uniquement sur les lignes qui correspondent à un critère. Elles sont au cœur de tout reporting en entreprise.
=SOMME.SI additionner selon un critère
=SOMME.SI.ENS plusieurs critères simultanés
| Formule | Utilité | Exemple |
|---|---|---|
| =SOMME.SI | Total avec 1 critère | =SOMME.SI(B:B;"Paris";D:D) |
| =SOMME.SI.ENS | Total avec plusieurs critères | =SOMME.SI.ENS(D:D;B:B;"Paris";C:C;"Jan") |
| =NB.SI | Compter avec 1 critère | =NB.SI(B:B;"Paris") |
| =NB.SI.ENS | Compter avec plusieurs critères | =NB.SI.ENS(B:B;"Paris";C:C;"Jan") |
| =MOYENNE.SI.ENS | Moyenne avec critères | =MOYENNE.SI.ENS(D:D;B:B;"Paris") |
- Calculez le CA total par région avec SOMME.SI
- Calculez le CA de chaque vendeur sur le mois de Janvier avec SOMME.SI.ENS
- Comptez le nombre de ventes supérieures à 1 000€ avec NB.SI
- Calculez la vente moyenne par région avec MOYENNE.SI.ENS
🎯 À vous de jouer
En G2, calculez le CA total de la région Nord.
En F2, comptez le nombre de ventes supérieures à 10 000 €.
En G2, calculez le CA du Nord au trimestre T2 avec SOMME.SI.ENS.
SI imbriqués : gérer plusieurs conditions
Quand une seule condition ne suffit pas, on imbrique plusieurs SI. Mais attention : au-delà de 3 niveaux, il vaut mieux utiliser SI.CONDITIONS ou une autre approche.
=SI.CONDITIONS l'alternative moderne
Attention à l'ordre : Excel teste les conditions de gauche à droite et s'arrête dès que l'une est vraie. Commencez toujours par le seuil le plus élevé, sinon vos conditions se court-circuitent.
- Créez un tableau de 10 commerciaux avec leur CA annuel et leur objectif
- Calculez le taux d'atteinte (CA / Objectif × 100)
- Avec SI.CONDITIONS, affichez : "🏆 Champion" si ≥120%, "✅ Atteint" si ≥100%, "⚠️ À améliorer" si ≥80%, "❌ En retard" sinon
- Appliquez une MFC sur la colonne Statut pour colorier chaque résultat
🎯 À vous de jouer
En C2, affichez "Excellent" si le CA ≥ 15 000, "Bon" si ≥ 10 000, sinon "À améliorer".
En D2, affichez "Prime" si le CA ≥ 10 000 et la satisfaction ≥ 90, sinon "Non".
Tableaux croisés dynamiques : analyser en quelques clics
Le Tableau Croisé Dynamique (TCD) est l'outil le plus puissant d'Excel pour analyser des données. Sans écrire une seule formule, vous pouvez en quelques secondes calculer des totaux, des moyennes, et croiser des dimensions.
Créer un TCD étapes
- Cliquez dans votre tableau de données (n'importe quelle cellule)
- Insertion → Tableau croisé dynamique → OK (nouvelle feuille)
- Dans le panneau à droite, faites glisser vos champs dans les zones :
Lignes = ce que vous voulez voir en lignes (ex: Région)
Colonnes = ce que vous voulez en colonnes (ex: Mois)
Valeurs = ce que vous voulez calculer (ex: Somme du CA) - Modifiez le calcul (clic droit → Résumer par → Moyenne, Nb, etc.)
Astuce : Si vos données changent, clic droit sur le TCD → Actualiser pour le mettre à jour. Pour des mises à jour automatiques, transformez d'abord vos données en Tableau Excel (Ctrl + T).
Segments filtrer visuellement un TCD
Les segments (slicers) sont des boutons de filtre visuels que vous posez sur votre feuille. Un clic = un filtre appliqué instantanément.
- Cliquez sur votre TCD → onglet "Analyse du tableau croisé dynamique"
- Cliquez sur "Insérer un segment" et choisissez les champs à filtrer
- Positionnez les segments à côté de votre TCD
- Créez un TCD : Régions en lignes, Mois en colonnes, Somme du CA en valeurs
- Ajoutez un champ calculé "CA moyen" = CA / Nb de ventes
- Ajoutez 2 segments : Région et Vendeur
- Formatez les valeurs en monétaire et appliquez un style TCD professionnel
🎯 À vous de jouer
Remettez dans l'ordre les étapes pour obtenir le CA par région et par trimestre.
Le TCD affiche le CA Sud / T2. Vérifiez-le en G2 avec une formule.
Mise en forme conditionnelle avancée
Au niveau intermédiaire, vous apprenez à créer des règles MFC basées sur des formules ce qui permet une flexibilité totale pour colorier n'importe quelle cellule selon n'importe quelle logique.
MFC avec formule personnalisée
- Sélectionnez votre plage (ex: A2:D50)
- Accueil → Mise en forme conditionnelle → Nouvelle règle
- Choisissez "Utiliser une formule pour déterminer les cellules à mettre en forme"
- Entrez votre formule elle doit renvoyer VRAI ou FAUX
| Objectif | Formule MFC (sur ligne 2) |
|---|---|
| Colorier toute la ligne si colonne C = "Urgent" | =$C2="Urgent" |
| Colorier si le CA dépasse l'objectif | =$D2>$E2 |
| Alterner les couleurs de ligne | =MOD(LIGNE();2)=0 |
| Colorier les doublons | =NB.SI($A$2:$A$50;$A2)>1 |
- Créez un tableau de projets : Projet, Responsable, Statut, Date limite, % avancement
- MFC : ligne entière en rouge si le Statut = "En retard"
- MFC : ligne entière en vert si % avancement = 100
- MFC : date limite en orange si elle est dans les 7 prochains jours (utiliser
=ET($E2-AUJOURD'HUI()<=7;$E2>=AUJOURD'HUI()))
🎯 À vous de jouer
En E2, écrivez la formule de règle qui renvoie VRAI si la facture est en retard (jours en C2 > 30) et non payée (D2 = "Non").
Remettez dans l'ordre les étapes pour colorier toute la ligne d'une facture en retard.
Graphiques dynamiques : votre premier dashboard
Un graphique connecté à un TCD et à des segments devient interactif : un clic sur le segment met à jour le graphique automatiquement. C'est la base d'un dashboard Excel professionnel.
Créer un graphique croisé dynamique
- Cliquez sur votre TCD existant
- Onglet "Analyse" → "Graphique croisé dynamique"
- Choisissez le type de graphique (barres groupées pour les comparaisons, courbes pour les tendances)
- Le graphique se met à jour automatiquement quand vous filtrez le TCD ou les segments
Astuce dashboard : Créez une feuille dédiée "Dashboard". Déplacez-y vos graphiques et segments. Masquez les feuilles de données brutes. Vous obtenez un rapport professionnel clé en main que n'importe qui peut utiliser sans toucher aux données.
- Créez 3 TCD : CA par région, CA par vendeur, Évolution CA par mois
- Créez 3 graphiques : histogramme, barres horizontales, courbe d'évolution
- Ajoutez 2 segments partagés entre les 3 TCD : Région et Mois
- Rassemblez tout sur une feuille "Dashboard", soignez la mise en page
- Ajoutez 3 KPI en haut : CA total, Meilleur vendeur (RECHERCHEX), Croissance
💡 Conseil pour les segments partagés
Pour qu'un segment contrôle plusieurs TCD : clic droit sur le segment → "Connexions aux rapports" → cochez tous les TCD à connecter. Ainsi, un seul clic filtre tous les graphiques du dashboard simultanément.
🎯 À vous de jouer
En G2, calculez le CA du vendeur choisi en F2 pour alimenter un graphique dynamique.
Remettez dans l'ordre pour qu'un graphique suive automatiquement les nouvelles données.
🎓 Terminer le niveau Intermédiaire
Réussissez toutes les activités avec au moins 60 % des points pour télécharger votre certificat. Ensuite, entraînez-vous sur un cas réel avec les ateliers pratiques.