4.5Fonctions avancées : DECALER, INDIRECT, AGREGAT
L'essentiel en 30 secondes
- DECALER = plage qui bouge : référence décalée et dimensionnable (ex. 12 derniers mois glissants) — mais volatile, à limiter sur gros classeurs.
- INDIRECT = texte qui devient référence : =INDIRECT("Ventes!B"&L1) pointe une adresse calculée — puissant mais volatile et fragile au renommage.
- Réflexe de Wassim : pour totaliser malgré erreurs et filtres, AGREGAT (non volatile, ignore erreurs/masqués) — et chercher d'abord une alternative robuste (INDEX, tableau structuré).
Mini-cas filé — Wassim totalise un tableau filtré plein d'erreurs
Wassim, comptable à Alger, doit totaliser une colonne de montants qui contient des #N/A, avec un tableau filtré sur Alger : =SOMME renvoie #N/A. Il utilise =AGREGAT(9;6;B2:B100) pour ignorer les erreurs, puis l'option lignes visibles pour le filtré. Pour sa synthèse glissante des 12 derniers mois, il construit une plage DECALER — puis la remplace par un tableau structuré, moins coûteux. Dernier jalon du fil rouge : robuste là où SOMME plantait, sans ralentir le classeur.
Dernier cas du fil rouge 4.1-4.5 — Wassim boucle son niveau : importer, modéliser, dynamiser, automatiser, fiabiliser.
1. DECALER : une plage qui bouge
DECALER construit une référence déplacée par rapport à un point de départ (n lignes, n colonnes), et de taille paramétrable. Utile pour des plages dynamiques (les 12 derniers mois glissants de Wassim). Mais c'est une fonction volatile : elle recalcule sans cesse et peut ralentir un gros classeur — souvent remplaçable par un tableau structuré ou INDEX.
Forme type : =DECALER(point_départ;lignes;colonnes;hauteur;largeur). On la cantonne aux cas où la taille de la plage change vraiment, et on mesure l'impact sur les gros fichiers.
2. INDIRECT : référence par texte
INDIRECT transforme un texte en référence : =INDIRECT("Ventes!B"&L1) pointe une cellule dont l'adresse est calculée — pratique pour des références construites (par feuille, par nom). Mais lui aussi volatile et fragile (un renommage de feuille casse le texte, sans mise à jour auto).
À réserver aux cas où rien d'autre ne convient : quand une référence doit se construire dynamiquement et qu'INDEX ou les références structurées ne suffisent pas.
3. AGREGAT : agréger en ignorant
AGREGAT fait des agrégations (somme, moyenne, grand…) en pouvant ignorer les erreurs et/ou les lignes masquées par un filtre. Ex. sommer une colonne qui contient des #N/A sans planter, ou totaliser uniquement le visible après filtrage. Une alternative robuste et non volatile à des bricolages.
Lecture : AGREGAT(no_fonction;option;plage) — 9 = SOMME, 6 = ignorer les erreurs, 5 = ignorer les masqués, 7 = les deux. Wassim retient 9;6 et 9;5 par cœur.
🔎 Exemple concret — 🔎 Exemple concret — Totaliser malgré des erreurs et un filtre
Colonne B de Wassim : des nombres, des #N/A, et le tableau est filtré :
=SOMME(B2:B100) → renvoie #N/A (bloque) ❌
=AGREGAT(9;6; B2:B100) → somme en ignorant les erreurs ✅
# 9 = SOMME ; 6 = ignorer les erreurs
=AGREGAT(9;5; B2:B100) → somme des seules lignes visibles (5)
// Plage des N derniers mois (N en D1) :
=DECALER($A$1; NBVAL($A:$A)-$D$1; 0; $D$1; 1)Lecture : AGREGAT combine une opération (9=somme) et une option (ignorer erreurs/masqués).
Leçon : une fonction robuste évite les bricolages fragiles autour des erreurs et filtres.
Vocabulaire de la section
- DECALER
- Construit une référence décalée et dimensionnable (plages dynamiques). Volatile.
Exemple simple : Wassim cadre ses 12 derniers mois glissants avec DECALER.
Ce que ce n'est pas : une plage figée qui suit les insertions toute seule.
Réflexe : plage de taille variable : DECALER, puis envisager tableau structuré.
- INDIRECT
- Transforme un texte en référence de cellule ou de plage. Volatile.
Exemple simple : Wassim pointe =INDIRECT("Ventes!B"&L1) vers une ligne calculée.
Ce que ce n'est pas : une référence directe mise à jour au renommage.
Réflexe : adresse calculée : INDIRECT en dernier recours.
- AGREGAT
- Agrège (somme, moyenne…) en ignorant erreurs et/ou lignes masquées.
Exemple simple : Wassim somme sa colonne à #N/A avec AGREGAT(9;6;...).
Ce que ce n'est pas : une SOMME qui bloque sur la première erreur.
Réflexe : données sales ou filtrées : penser AGREGAT.
- Fonction volatile
- Se recalcule à chaque modification du classeur, même sans rapport — peut ralentir.
Exemple simple : Trop de DECALER/INDIRECT ralentit le classeur de clôture de Wassim.
Ce que ce n'est pas : une fonction qui ne recalcule que si ses entrées changent.
Réflexe : gros classeur : compter les volatiles, préférer INDEX.
En pratique — En pratique — Robuste plutôt que fragile
- Sommez une plage contenant un #N/A avec AGREGAT (option ignorer erreurs).
- Filtrez le tableau et totalisez seulement le visible avec AGREGAT (option masqués).
- Construisez une plage des N derniers mois (DECALER) puis discutez d'une alternative INDEX.
- Notez pourquoi limiter INDIRECT/DECALER sur un gros classeur.
Script prêt à dire — justifier AGREGAT en clôture (30 s)
« C'est Wassim : la colonne contient des #N/A et le tableau est filtré, donc SOMME plante. J'ai mis AGREGAT avec l'option ignorer les erreurs, et une version lignes visibles pour le filtré. Pour la plage glissante, j'ai limité DECALER au strict nécessaire — ce sont des fonctions volatiles. »
À dire en revue de clôture : montrez le avant/après SOMME vs AGREGAT.
Modèle copier-coller — Le trio robuste
=AGREGAT(9;6; B2:B100)
=AGREGAT(9;5; B2:B100)
=AGREGAT(9;7; B2:B100)
=DECALER($A$1; NBVAL($A:$A)-$D$1; 0; $D$1; 1)
=INDIRECT("Ventes!B" & L1)Colonne avec erreurs
Comme Wassim : =AGREGAT(9;6;plage) somme en ignorant les #N/A, sans nettoyer la source.
Tableau filtré
=AGREGAT(9;5;plage) totalise uniquement les lignes visibles après filtrage.
Synthèse glissante
DECALER dimensionné par NBVAL pour les N derniers mois — puis tableau structuré si lenteurs.
Référence construite
INDIRECT vers une feuille ou une ligne calculée, en dernier recours et documenté.
✓ Bonnes pratiques
- Préférer AGREGAT pour agréger malgré erreurs/filtres.
- Limiter DECALER/INDIRECT (volatiles) sur gros classeurs.
- Chercher une alternative (INDEX, tableau structuré) d'abord.
✗ Pièges fréquents
- Truffer un gros classeur de fonctions volatiles → lenteurs.
- INDIRECT vers un nom de feuille renommé → #REF!.
- Utiliser DECALER là où un tableau structuré suffirait.
Points clés à retenir
- DECALER : plage mobile/dimensionnable (volatile).
- INDIRECT : texte → référence (volatile, fragile).
- AGREGAT : agrège en ignorant erreurs et/ou lignes masquées.
- Volatile = recalcul permanent ; préférer des alternatives robustes.
Défi 48h — appliquez demain
Sur une vraie colonne à erreurs : remplacez SOMME par AGREGAT(9;6;...), testez en filtré (option 5), puis auditez vos DECALER/INDIRECT et proposez une alternative.
- Je sais agréger malgré erreurs et lignes masquées (AGREGAT).
- Je sais construire une plage mobile (DECALER) et une référence par texte (INDIRECT).
- Je sais limiter les volatiles et proposer des alternatives robustes.
Questions fréquentes
Pourquoi éviter les fonctions volatiles ?
Elles se recalculent à chaque changement du classeur (même sans rapport), ce qui alourdit les gros fichiers. DECALER et INDIRECT sont volatiles ; quand une alternative non volatile existe (INDEX, tableau structuré, SOUS.TOTAL/AGREGAT), on la préfère.
AGREGAT ou SOUS.TOTAL ?
SOUS.TOTAL ignore les lignes masquées par un filtre ; AGREGAT va plus loin en ignorant aussi les erreurs et davantage d'options. Pour des données « sales » (erreurs) et filtrées, AGREGAT est plus robuste.
Quelle fonction somme une colonne contenant des #N/A sans planter ?