Maîtriser les formules de plages dynamiques dans Google Sheets

7 août 2026

Les plages figées du type A2:A100 se périment dès que quelqu'un colle la ligne 101. Les formules de plages dynamiques grandissent (ou rétrécissent) avec les données : références ouvertes, FILTER, schémas de dernière ligne avec INDEX et COUNTA, SEQUENCE pour des grilles générées, et ARRAYFORMULA sur toute une colonne. Cet article est le compagnon pratique de Maîtriser les formules matricielles dans Google Sheets — centré sur jusqu'où une formule doit s'étendre, pas seulement sur le calcul.

Plages ouvertes (A2:A)

Dans Google Sheets, A2:A signifie « de A2 jusqu'à la dernière ligne de la feuille ». De nombreuses fonctions acceptent cette forme :

=SUM(A2:A)
=COUNTA(B2:B)
=UNIQUE(C2:C)

Avantages :

• Les nouvelles lignes sont incluses automatiquement
• Plus besoin de retoucher chaque mois A2:A5000 → A2:A8000

Coûts :

• Les formules peuvent parcourir une longue queue vide (souvent acceptable ; attention aux calculs matriciels lourds ou volatils)
• Les lignes vides au milieu restent dans la plage pour certaines fonctions

Préférez A2:A à la colonne entière A:A lorsque la ligne 1 est un en-tête à exclure.

FILTER — la plage dynamique que vous voulez en général

FILTER ne renvoie que les lignes qui satisfont une condition. Le résultat est un tableau dynamique — graphiques, COUNT et formules en aval ne voient que le bloc correspondant.

=FILTER(A2:C, C2:C="Actif")
=FILTER(A2:B, B2:B>=DATE(2026,1,1))
=FILTER(A2:D, A2:A<>"")

Gestion d'un critère sans résultat :

=IFERROR(FILTER(A2:C, C2:C=G1), "Aucune ligne")

FILTER est l'ossature des schémas de Utiliser FILTER + XLOOKUP pour des tableaux de bord dynamiques.

INDEX + COUNTA — plages bornées à la dernière ligne

Parfois vous avez besoin d'une référence de plage (source de graphique, logique de plage nommée, ou fonction qui n'accepte pas toute une colonne ouverte). Construisez « de la première cellule de données jusqu'à la dernière non vide » avec INDEX et COUNTA :

=A2:INDEX(A:A, COUNTA(A:A))

Avec un en-tête en A1 et des valeurs contiguës en A2:A100, COUNTA(A:A) renvoie 100, donc INDEX(A:A, 100) vaut A100 et la plage se résout en A2:A100.

S'il n'y a pas de vides dans la colonne :

=SUM(A2:INDEX(A:A, COUNTA(A:A)))

Plus robuste quand la colonne peut contenir des vides au milieu — trouvez la dernière cellule non vide avec :

=A2:INDEX(A:A, MAX(FILTER(ROW(A:A), A:A<>"")))

Gardez COUNTA pour les colonnes propres, sans trous (identifiants, horodatages ajoutés en bas).

Bloc dynamique sur deux colonnes

=A2:INDEX(B:B, COUNTA(A:A))

Cela couvre A et B jusqu'à la dernière ligne impliquée par les non-vides de la colonne A — source fréquente de graphique ou de FILTER.

SEQUENCE — générer la taille dont vous avez besoin

SEQUENCE construit des nombres sans colonne d'aide :

=SEQUENCE(10)
=SEQUENCE(10, 1, 2026, 1)
=SEQUENCE(COUNTA(A2:A))

Associé à d'autres fonctions :

=ARRAYFORMULA(DATE(2026, 1, SEQUENCE(31)))
=INDEX(A2:A, SEQUENCE(5))

Quand la longueur doit suivre les données :

=SEQUENCE(COUNTA(A2:A))

Souvent plus propre qu'une cellule manuelle « nombre de lignes ».

Formules de colonne avec ARRAYFORMULA

Une seule formule en ligne 2 qui remplit la colonne :

=ARRAYFORMULA(IF(A2:A="",, B2:B*C2:C))
=ARRAYFORMULA(IF(A2:A="",, XLOOKUP(A2:A, SKU!A:A, SKU!B:B)))

Le A2:A ouvert dans ARRAYFORMULA inclut automatiquement les nouvelles lignes. Protégez avec IF(A2:A="",, …) pour que les lignes vides restent vides au lieu d'afficher des zéros ou des #N/A.

Les schémas matriciels plus avancés — BYROW, MAP, règles de déversement — sont dans Maîtriser les formules matricielles dans Google Sheets.

OFFSET — puissant mais volatil (à utiliser avec parcimonie)

OFFSET renvoie une plage décalée depuis une cellule de départ :

=SUM(OFFSET(A2, 0, 0, COUNTA(A2:A), 1))

Cela somme une hauteur égale au nombre de valeurs à partir de A2. Cela fonctionne — et c'est une habitude fréquente venue d'Excel — mais OFFSET est volatil : il se recalcule souvent, ce qui peut ralentir les gros classeurs.

Préférez :

=SUM(A2:INDEX(A:A, COUNTA(A:A)))

ou simplement :

=SUM(A2:A)

quand la fonction accepte les plages ouvertes. Réservez OFFSET aux cas de mise en page rares qu'un vrai INDEX n'exprime pas proprement.

INDIRECT est aussi volatil ; évitez de construire des plages dynamiques en chaînes (INDIRECT("A2:A"&n)) quand INDEX suffit.

Schémas pratiques

Cumul qui s'étend

=ARRAYFORMULA(IF(A2:A="",, SUMIF(ROW(A2:A), "<="&ROW(A2:A), B2:B)))

Ou avec SCAN et LAMBDA si vous préférez un accumulateur explicite :

=ARRAYFORMULA(IF(A2:A="",, SCAN(0, B2:B, LAMBDA(acc, x, acc+IF(x="", 0, x)))))

Source de liste déroulante sans vides

Validation ou graphiques veulent souvent « seulement les libellés remplis » :

=FILTER(Lists!A2:A, Lists!A2:A<>"")

N dernières lignes d'un journal

=FILTER(A2:B, ROW(A2:A)>COUNTA(A2:A)+1-10)

Ajustez selon les en-têtes et les éventuels trous.

Erreurs fréquentes

• A:A qui inclut l'en-tête dans un agrégat numérique. Utilisez A2:A ou soustrayez l'en-tête volontairement.
• COUNTA avec des vides au milieu. Le calcul de dernière ligne est trop court ; utilisez un vrai schéma de dernière non-vide ou gardez des colonnes append-only propres.
• Tout envelopper dans OFFSET « parce qu'Excel le faisait ». Préférez plages ouvertes, FILTER et INDEX.
• ARRAYFORMULA non borné sans garde sur les lignes vides. Des milliers de zéros ou d'erreurs.
• Oublier que FILTER renvoie #N/A sans correspondance. Enveloppez avec IFERROR ou IFNA pour les tableaux de bord — voir Gérer les erreurs dans Google Sheets.

Quelle approche choisir ?

| Objectif | Approche | | --- | --- | | Sommer/compter toutes les données sous un en-tête | Plage ouverte A2:A | | Lignes correspondant à une condition | FILTER | | Référence de plage bornée pour graphiques | A2:INDEX(…, COUNTA(…)) | | Générer 1..n ou grilles de dates | SEQUENCE | | Champ calculé sur toute une colonne | ARRAYFORMULA + plage ouverte | | À éviter sauf nécessité | OFFSET, INDIRECT |

Pour aller plus loin

Appuyez-vous sur Maîtriser les formules matricielles dans Google Sheets et le câblage de tableaux de bord dans Utiliser FILTER + XLOOKUP pour des tableaux de bord dynamiques. Références : FILTER, INDEX, COUNTA, SEQUENCE, ARRAYFORMULA, OFFSET.