4.5Fonctions avancées : DECALER, INDIRECT, AGREGAT
Cette section rassemble l'artillerie des cas tordus. DECALER construit des plages mobiles (« les 12 derniers mois glissants »). INDIRECT transforme un texte en référence réelle — pratique pour consolider des feuilles nommées par mois. AGREGAT est une SOMME/MOYENNE/MAX qui sait ignorer les erreurs et les lignes masquées, là où SOMME classique renverrait #N/A.
Vous apprendrez aussi SOUS.TOTAL, la fonction à utiliser au-dessus d'un tableau filtré : elle ne compte que les lignes visibles. Et une mise en garde de praticien : DECALER et INDIRECT sont volatiles (recalculées en permanence) — puissantes mais à doser sur les gros fichiers.
Vocabulaire de la section
- DECALER
- =DECALER(départ;lignes;colonnes;[hauteur];[largeur]) : renvoie une plage décalée/dimensionnée dynamiquement depuis un point de départ.
- INDIRECT
- Convertit un texte en référence : =INDIRECT("'"&A1&"'!B2") lit la cellule B2 de la feuille dont le nom est en A1.
- AGREGAT
- =AGREGAT(n°fonction;options;plage) : 19 fonctions (somme, moyenne, max…) avec options pour ignorer erreurs, lignes masquées et sous-totaux.
- SOUS.TOTAL
- Agrégations qui respectent les filtres : =SOUS.TOTAL(9;plage) somme uniquement les lignes visibles.
- Fonction volatile
- Recalculée à CHAQUE modification du classeur (DECALER, INDIRECT, AUJOURDHUI…) : à limiter dans les très gros fichiers.
Quel format de fichier peut contenir des macros ?
En pratique — Des totaux qui respectent les filtres et survivent aux erreurs
- Sur votre tableau Ventes filtrable, au-dessus de la colonne Montant : =SOUS.TOTAL(9;Ventes[Montant]).
- Filtrez sur une ville : le total affiché ne compte QUE les lignes visibles — comparez avec une SOMME classique.
- Introduisez volontairement une erreur dans la colonne (tapez =1/0 dans une cellule) : la SOMME tombe en #DIV/0!.
- Remplacez par =AGREGAT(9;6;Ventes[Montant]) : le total revient, l'erreur est ignorée (option 6).
Points clés à retenir
- SOUS.TOTAL(9;…) au-dessus d'un tableau filtré : le total suit le filtre.
- AGREGAT = l'agrégation blindée : elle ignore erreurs et lignes masquées à la demande.
- INDIRECT consolide des feuilles à partir de leur nom en cellule — mais fige la référence (pas de suivi si la feuille est renommée).
- DECALER et INDIRECT sont volatiles : parfaites à petite dose, coûteuses par milliers.
Questions fréquentes
Pourquoi préférer AGREGAT à SOMME + SIERREUR partout ?
AGREGAT traite le problème en une fonction et peut aussi ignorer les lignes masquées. SIERREUR cellule par cellule reste utile, mais alourdit les fichiers quand on l'applique en masse.
DECALER est-elle remplaçable par les formules dynamiques ?
Souvent oui : FILTRE, PRENDRE/EXCLURE (Excel 365) et les références structurées couvrent la plupart des besoins de plages mobiles, sans volatilité.