Blog

Plages dynamiques dans Excel : quand utiliser OFFSET ou un tableau

Actualisez les totaux et les listes déroulantes lorsque vous ajoutez des lignes. Découvrez OFFSET, COUNTA, l’effet des identifiants manquants et les tableaux Excel.

La plage en surbrillance s’étend vers le bas quand une ligne est ajoutée.

Vous ajoutez un produit dans Excel, mais le total et les options de la liste déroulante restent inchangés. Si la référence est toujours A2:A10, la onzième ligne reste en dehors de la plage. Une plage dynamique laisse Excel repérer la fin de la liste sans modifier les formules à chaque mise à jour.

Calculer la hauteur et la largeur avec OFFSET

Si les données sont contiguës, comptez les en-têtes et les identifiants pour fournir les dimensions à OFFSET. Créez l’exemple sur Sheet1 et saisissez la formule de somme sur une autre feuille. Gardez les en-têtes japonais tels quels pour les références structurées ci-dessous.

Cellule Colonne A Colonne B Colonne C
Ligne 1 商品ID (identifiant du produit) 商品名 (nom du produit) 数量 (quantité)
Ligne 2 A01 Stylo 4
Ligne 3 A02 Cahier 6
=SUM(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),COUNTA(Sheet1!$A$1:$Z$1)))

Partez de A1 sans décalage. Le nombre de valeurs en A donne la hauteur et le nombre d’en-têtes sur la ligne 1 donne la largeur. La plage de trois lignes sur trois colonnes est A1:C3 avec un total de 10. Ajoutez A03, Règle et 2 à la ligne 4. La plage devient A1:C4 et le total 12. Les identifiants sont du texte et les plages comptées ne contiennent aucune autre donnée. Référence Microsoft pour OFFSET

Effacez maintenant l’identifiant de A3 uniquement. Trois cellules comptent en A : l’en-tête A1 et les identifiants en A2 et A4. OFFSET renvoie donc les trois premières lignes, A1:C3. La quantité 6 de la ligne 3 reste incluse, mais la quantité 2 de la ligne 4 est exclue. Le total revient à 10.

A3 est vide. OFFSET couvre A1:C3 et donne 10, avec le 6 de la ligne 3 et sans le 2 de la ligne 4.
Le cadre vert montre la plage A1:C3 renvoyée par OFFSET dans Excel pour Windows.

Un en-tête vide au milieu peut aussi laisser les dernières colonnes hors de la plage. COUNTA compte les cellules avec des formules qui renvoient une chaîne vide (""). Dans un classeur existant, gardez les en-têtes et les identifiants contigus, puis vérifiez les limites du comptage et les cellules vides. Une quantité vide ne modifie pas la hauteur si l’identifiant est renseigné. Référence Microsoft pour COUNTA

Si les en-têtes commencent en ligne 2

Si la ligne 1 contient le titre du rapport, commencez à A2 et comptez aussi la hauteur et la largeur à partir de la ligne 2. En déplaçant l’exemple d’une ligne vers le bas, on obtient A2:C4 pour les en-têtes et les deux produits. Le total reste 10.

=SUM(OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000),COUNTA(Sheet1!$A$2:$Z$2)))

Utiliser un tableau qui s’étend quand on ajoute des lignes

Pour une nouvelle liste, sélectionnez la plage avec les en-têtes et choisissez Insertion → Tableau. Nommez-le Items. La formule suivante additionne la colonne des quantités depuis une cellule extérieure. La plage de la somme s’étend avec les nouvelles lignes. Guide Microsoft sur les références structurées

=SUM(Items[数量])

Additionner uniquement les lignes comportant un identifiant

Une fois A1:C4 converti en tableau Items, la formule ci-dessus additionne 4 + 6 + 2 et renvoie 12, même si A3 est vide. Pour compter seulement les lignes ayant un identifiant, utilisez SUMIF avec la condition que l’identifiant ne soit pas vide. Dans l’exemple, les lignes 2 et 4 donnent 6 (4 + 2). Référence Microsoft pour SUMIF

=SUMIF(Items[商品ID],"<>",Items[数量])

Synchroniser une liste déroulante avec le tableau

Pour proposer les noms de produit sur une autre feuille, ouvrez Formules → Gestionnaire de noms dans Excel de bureau. Créez ItemChoices à l’échelle du classeur et indiquez =Items[商品名] dans Fait référence à. Dans la validation des données de la cellule de saisie, sélectionnez Liste, puis indiquez =ItemChoices comme source. L’en-tête est exclu et les nouvelles lignes en fin de tableau ajoutent des choix. Renseignez le nom des nouveaux produits pour éviter les choix vides. Guide Microsoft pour créer une liste déroulante