Excel avancé – Les fonctions modernes et les formules

Les fonctions modernes et les tableaux croisés dynamiques servent à calculer juste, puis à résumer et à présenter les résultats.

Cette formation de deux journées vous apprend d’abord à écrire des formules claires avec Microsoft 365 : recherches, calculs conditionnels, tableaux dynamiques, texte, LET et LAMBDA. Elle enchaîne ensuite sur les calculs avancés des tableaux croisés dynamiques, les graphiques croisés et l’assemblage d’un tableau de bord filtrable.

À travers des exercices concrets, vous repartez avec des formules plus fiables et un tableau de bord que vous pouvez actualiser.

Aisance avec les bases d’Excel : saisie, formules simples (SOMME, SI) et création d’un tableau croisé dynamique.

1. Préparer des données pour des formules fiables

• Rappeler les références relatives, absolues et mixtes
• Convertir une plage en tableau Excel
• Utiliser les références structurées
• Repérer les erreurs courantes (#N/A, #VALEUR!, #DIV/0!)
• Exercice pratique : mettre en forme un tableau de suivi prêt pour les formules

2. Rechercher avec RECHERCHEX

• Comprendre les limites de RECHERCHEV
• Écrire une recherche avec RECHERCHEX
• Renvoyer plusieurs colonnes et chercher depuis la droite
• Choisir une correspondance exacte ou approximative avec EQUIVX
• Prévoir une valeur lorsque rien n’est trouvé
• Exercice pratique : remplacer des RECHERCHEV par RECHERCHEX

3. Calculer selon plusieurs conditions

• Écrire des tests avec SI et SI.CONDITIONS
• Sécuriser un calcul avec SIERREUR
• Additionner, compter et moyenner avec SOMME.SI.ENS, NB.SI.ENS et MOYENNE.SI.ENS
• Combiner plusieurs critères dans une même formule
• Exercice pratique : calculer des totaux par catégorie et par période

4. Filtrer et réorganiser avec les tableaux dynamiques

• Comprendre le débordement d’une formule dans les cellules voisines
• Extraire des lignes avec FILTRE
• Trier avec TRIER et TRIERPAR
• Obtenir les valeurs distinctes avec UNIQUE
• Choisir des colonnes avec CHOISIRCOLS et limiter un résultat avec PRENDRE
• Empiler des plages avec ASSEMB.V et ASSEMB.H
• Exercice pratique : produire une liste filtrée et triée sans copier les données

5. Découper et recomposer du texte

• Extraire une partie d’un libellé avec TEXTE.AVANT et TEXTE.APRES
• Séparer une cellule en plusieurs colonnes avec FRACTIONNER.TEXTE
• Réunir des éléments avec JOINDRE.TEXTE
• Nettoyer les espaces et les formats parasites
• Exercice pratique : séparer des noms complets et recomposer une adresse

6. Rendre les formules lisibles

• Nommer les étapes d’un calcul avec LET
• Éviter de répéter le même calcul dans une formule
• Créer une fonction réutilisable avec LAMBDA
• Enregistrer cette fonction dans le gestionnaire de noms
• Résumer des données avec GROUPER.PAR
• Exercice pratique : réécrire une formule imbriquée en étapes nommées

7. Cas pratique sur les formules

• Préparer le tableau source
• Rechercher les informations manquantes
• Filtrer et trier les lignes utiles
• Calculer des indicateurs selon plusieurs critères
• Simplifier les formules avec LET

8. Aller plus loin avec le tableau croisé dynamique

• Changer le mode de synthèse : somme, nombre, moyenne, pourcentage du total
• Afficher un écart avec la période précédente, un cumul ou un rang
• Créer un champ calculé
• Regrouper des dates et des montants par tranches
• Afficher le détail d’un montant
• Exercice pratique : comparer des résultats par catégorie et par période

9. Créer un graphique croisé dynamique

• Choisir le type de graphique adapté au tableau croisé
• Lier le graphique aux données du tableau croisé dynamique
• Filtrer le graphique avec un segment et une chronologie
• Mettre en forme les séries, les axes et les étiquettes
• Exercice pratique : montrer l’évolution mensuelle d’un indicateur

10. Assembler un tableau de bord

• Disposer plusieurs tableaux croisés et graphiques sur une même feuille
• Relier un segment à plusieurs tableaux et graphiques
• Extraire un chiffre clé avec LIREDONNEESTABCROISDYNAMIQUE
• Mettre en avant quelques indicateurs
• Vérifier que le tableau de bord s’actualise après un changement des données
• Exercice pratique : construire un tableau de bord filtrable en un clic

11. Cas pratique de synthèse

• Préparer la liste source et un indicateur calculé
• Résumer les données dans plusieurs tableaux croisés dynamiques
• Ajouter les graphiques croisés
• Relier les segments
• Contrôler l’ensemble du tableau de bord