Formation Excel avancée : maîtrisez les fonctions et tableaux croisés

En bref. Ce tutoriel vous guide pas à pas dans les fonctions avancées d'Excel : formules imbriquées, tableaux de bord, filtres intelligents. Vous manipulerez des données réelles et construirez des outils professionnels dès votre première session.

1. Les formules imbriquées : combinez logique et calcul

Les formules imbriquées libèrent le potentiel d'Excel. Elles combinent plusieurs fonctions pour automatiser des décisions complexes.

Exemple concret : vous gérez les ventes d'une équipe. Vous voulez afficher « Bonus » si le montant dépasse 5000 euros, sinon « À atteindre ». Utilisez :

=SI(A2>5000;"Bonus";"À atteindre")

Compliquons : vous voulez aussi vérifier que la région n'est pas fermée. Imbriquez :

=SI(ET(A2>5000;B2="Région active");"Bonus";"À atteindre")

Pièges à éviter : fermez chaque parenthèse ; testez avec des données simples d'abord ; lisez le message d'erreur qui indique souvent la parenthèse mal fermée.

Pratiquez en créant une feuille avec colonnes Montant, Région, Résultat. Écrivez la formule une fois, copiez-la vers le bas en doublecliquant sur le petit carré en bas à droite de la cellule.

2. Les fonctions de recherche : trouvez l'information en secondes

RECHERCHEV (ou VLOOKUP en anglais) est votre meilleur ami. Elle cherche une valeur dans un tableau et ramène une donnée associée.

Situation réelle : vous avez une liste de codes clients avec leurs noms. Un commercial vous donne un code, vous voulez afficher automatiquement le nom.

=RECHERCHEV(A2;Base.A:B;2;FAUX)

Décryptage : cherche la valeur en A2 dans la plage Base.A:B (référence à une autre feuille nommée « Base »), ramène la valeur de la colonne numéro 2, FAUX signifie « cherche une correspondance exacte ».

Astuce avancée : si vous devez chercher dans plusieurs colonnes à droite et à gauche, utiliser INDEX et EQUIV ensemble offre plus de flexibilité.

Testez d'abord sur une petite base : trois colonnes, cinq lignes. Vérifiez que vos codes correspondent exactement (pas d'espaces cachés).

3. Les tableaux croisés dynamiques : résumez vos données en deux clics

Un tableau croisé dynamique (TCD) transforme une liste plate en résumé lisible, sans écrire une seule formule. C'est la clé de l'analyse rapide.

Cas d'usage : vous avez mille lignes de transactions (date, catégorie, montant). Vous voulez voir le total par catégorie et par mois.

Marche à suivre :

Sélectionnez votre tableau complet (en-têtes inclus)
Allez à l'onglet Données, cliquez Tableau croisé dynamique
Excel propose un emplacement ; acceptez ou choisissez une nouvelle feuille
Glissez-déposez les champs : Catégorie en Lignes, Mois en Colonnes, Montant en Valeurs

Pièges : vérifiez que vos données n'ont pas de ligne vide au milieu ; les en-têtes doivent être sur la première ligne ; les dates doivent être formatées comme dates (pas du texte).

Une fois créé, le TCD se met à jour automatiquement si vous ajoutez des lignes à votre source.

4. L'automatisation par filtres et mises en forme conditionnelle

Les filtres intelligents et la mise en forme conditionnelle épurent vos données et alertent sur ce qui compte vraiment.

Les filtres avancés : au lieu de trier manuellement, appliquez un filtre automatique. Cliquez sur une cellule de votre tableau, puis Données > Filtre automatique. Des petites flèches apparaissent dans les en-têtes.

Exemple : filtrez pour afficher seulement les ventes au-dessus de mille euros, ou les clients d'une région donnée. Cliquez la flèche, cochez/décochez les valeurs.

La mise en forme conditionnelle : elle colore automatiquement les cellules selon des règles. Sélectionnez une colonne de montants, allez à Accueil > Mise en forme conditionnelle > Nouvelle règle, et écrivez : « Si la valeur est supérieure à 10000, remplissage rouge ».

Cumulez-les pour un tableau tableau de bord visuellement utile : montants en rouge si critiques, en orange si à surveiller, en vert sinon.

5. Construisez votre premier tableau de bord interactif

Combinons tout : vous allez assembler des fonctions avancées pour créer un petit outil décisionnel.

Structure simple :

Feuille 1 : Données brutes (500 lignes de commandes : client, date, montant, région)
Feuille 2 : Tableau croisé montrant totaux par région
Feuille 3 : Tableau de synthèse avec formules RECHERCHEV pour afficher le détail client à la demande

Marche à suivre concrète : entrez des données fictives (noms, chiffres réalistes) dans la feuille 1. Créez un TCD en feuille 2 en quelques clics. En feuille 3, tapez le nom d'un client et écrivez =RECHERCHEV(A1;Données.A:D;3;FAUX) pour afficher son montant total.

Finition : formatez les montants en euros (clic droit > Format), colorez les en-têtes. Testez en changeant les données sources : tout se met à jour.

Vous venez de créer un petit outil réutilisable et maintenable.

Testez-vous

1. Quelle fonction utilisez-vous pour afficher « Urgent » si un montant dépasse 10000 euros, sinon « Normal » ?

La fonction SI teste une condition : SI le montant en A1 est supérieur à 10000, affiche « Urgent », sinon « Normal ». Le symbole > signifie « supérieur à ».

2. Vous cherchez le nom d'un client à partir de son code dans un tableau. Quelle fonction choisissez-vous ?

RECHERCHEV cherche une valeur (le code) et ramène une information associée (le nom). SOMME et MOYENNE calculent des totaux, pas des recherches.

3. Pour résumer rapidement le total des ventes par région à partir de mille lignes, l'outil le plus efficace est :

Le tableau croisé dynamique résume automatiquement en quelques clics, sans écrire de formule. Les deux autres méthodes sont longues et sujettes aux erreurs.

Aller plus loin avec un précepteur personnel

L'ESEAD réunit plus de 120 formations en ligne et un précepteur disponible 24 h/24, pour 49 € par mois tout inclus. Essai gratuit sans carte bancaire.

Essayer gratuitement Voir les formations

Questions fréquentes

J'ai un message d'erreur #REF! dans ma formule. Qu'est-ce que ça signifie ?

Cette erreur indique que votre formule fait référence à une cellule ou plage qui n'existe pas ou a été supprimée. Vérifiez que le nom de la feuille est correct (Base au lieu de base, par exemple), que la plage existe vraiment, et que vous n'avez pas supprimé une colonne dont votre formule dépendait.

Mon tableau croisé dynamique ne se met pas à jour quand j'ajoute des données. Pourquoi ?

Le TCD utilise une plage fixe au moment de sa création. Si vous ajoutez des lignes au-delà de cette plage, elles ne sont pas incluses. Solution : cliquez sur le TCD, allez à l'onglet Données du TCD, puis Actualiser ou élargissez manuellement la source avant de créer un nouveau TCD.

Peut-on combiner RECHERCHEV avec SI pour afficher un message si la recherche ne trouve rien ?

Oui, utilisez IFERROR : =IFERROR(RECHERCHEV(A1;Base.A:B;2;FAUX);"Non trouvé"). Si RECHERCHEV échoue, elle affiche « Non trouvé » au lieu de l'erreur #N/A. C'est très utile pour les listes incomplètes.

Autres tutoriels : Numérique et informatique

Tutoriel rédigé avec l'aide de l'intelligence artificielle par l'équipe pédagogique de l'ESEAD, à visée pédagogique générale. Il ne remplace ni un professionnel qualifié ni un avis médical ou juridique. Une erreur ? Signalez-la.