Pour exclure dynamiquement les dernières lignes et colonnes vides des formules, nous pouvons utiliser des fonctions telles que ARRAY_CONSTRAIN, INDIRECT et OFFSET dans Google Sheets.
Introduction
Pourquoi voudrait-on exclure dynamiquement les lignes et colonnes vides à la fin d’une plage ? C’est ce que nous allons découvrir. Imaginons que notre plage de données soit A2:I8, et qu’elle puisse s’étendre horizontalement et verticalement à l’avenir. Dans les formules, nous pouvons donc faire référence à A2:Z pour ouvrir les lignes (lignes infinies) ou à A2:1000 pour ouvrir les colonnes (infinies). Une autre option préférée consiste à ouvrir les deux extrémités en utilisant INDIRECT(« A2: »&rows(A2:A)). Toutes ces plages infinies peuvent causer des problèmes dans certaines formules, comme FLATTEN et MMULT. Une telle plage ouverte peut affecter les performances de votre feuille ou renvoyer des erreurs de formule.
Pour résoudre ce problème, nous pouvons utiliser une formule de contrainte de tableau dynamique. En excluant dynamiquement les dernières lignes et colonnes vides des plages de formules, nous pouvons fournir à la formule FLATTEN ou à toute autre formule la plage exacte A2:I8, mais elle inclura également les futures entrées. C’est l’une des meilleures solutions pour éviter les erreurs de formule et garantir la stabilité de votre feuille.
Formulaire ARRAY_CONSTRAIN dynamique
La fonction ARRAY_CONSTRAIN est dédiée au redimensionnement d’une plage de données dans Google Sheets. Elle peut limiter les colonnes, les lignes ou les deux à partir de la fin d’un tableau/plage.
Syntaxe : ARRAY_CONSTRAIN(plage_entrée, nombre_lignes, nombre_colonnes)
Voici un exemple de formule Google Sheets :
=ARRAY_CONSTRAIN(Sample!A2:I8,7,9)
La formule ci-dessus n’est pas dynamique car nous avons spécifié le nombre de lignes (nombre_lignes) et de colonnes (nombre_colonnes) manuellement. Nous allons utiliser des expressions dans nombre_lignes et nombre_colonnes pour rendre la contrainte dynamique.
La première étape consiste à trouver le numéro de ligne de la dernière cellule non vide en ignorant les cellules vides dans la première colonne.
Nombre de lignes utilisées (nombre_lignes) :
=ArrayFormula(MATCH(2,1/(Sample!A2:A<>"")),1)
La prochaine étape consiste à trouver la dernière colonne non vide dans une ligne. Pour cela, nous pouvons considérer la ligne n°2, qui contient les étiquettes des champs.
Nombre de colonnes utilisées (nombre_colonnes) :
=ArrayFormula(match(2,1/(Sample!A2:2<>"")))
En utilisant les éléments ci-dessus, nous pouvons maintenant coder notre formule ARRAY_CONSTRAIN dynamique :
=ArrayFormula(ARRAY_CONSTRAIN(Sample!A2:Z,MATCH(2,1/(Sample!A2:A<>""),1),match(2,1/(Sample!A2:2<>""))))
La formule ci-dessus nous permet d’exclure dynamiquement les dernières lignes et colonnes vides d’une plage infinie dans Google Sheets.
Pour la tester, remplissez les lignes et colonnes vides avec n’importe quelle valeur en dessous du nom « Roger » dans la cellule B10 et à droite de la date 8/6/21 dans la cellule K3. La formule ne renverra pas d’erreur #REF.
Conseils supplémentaires
-
Si vous souhaitez exclure la ligne d’en-tête de la plage, effectuez les deux modifications suivantes :
- Remplacez A2:Z par A3:Z.
- Remplacez MATCH(2,1/(Sample!A2:A<> » »),1) par MATCH(2,1/(Sample!A3:A<> » »),1).
-
Si vous souhaitez exclure la ligne d’en-tête et la première colonne de la plage, effectuez les modifications suivantes :
- Remplacez A2:Z par B3:Z.
- Remplacez MATCH(2,1/(Sample!A2:2<> » » »)) par MATCH(2,1/(Sample!B2:2<> » » »)).
Méthode INDIRECT pour exclure dynamiquement les dernières lignes et colonnes vides
Cette approche INDIRECT est beaucoup plus simple à coder que la méthode précédente.
Formule générique : indirecte(première_cellule_de_la_plage&dernière_lettre_de_colonne_non_vide&dernier_numéro_de_ligne_non_vide)
Formule :
=indirecte(ArrayFormula("Sample!A2:"®EXEXTRACT(adresse(1;match(2;1/(Sample!2:2<>""));4);"[^d]+")&match(2;1/(Sample!A:A<>""))))
Si vous souhaitez exclure la première ligne, remplacez « Sample!A2: » par « Sample!A3: ». Si vous souhaitez exclure la première colonne, remplacez la même partie par « Sample!B3: ».
Lorsque vous utilisez la formule ci-dessus pour exclure dynamiquement les dernières lignes et colonnes vides, vous pouvez rencontrer un problème. Quel est-il ? La référence « Sample!A2: » est fixe. Ainsi, lorsque vous insérez des colonnes avant la première colonne dans la feuille « Sample », cela peut causer des problèmes.
La méthode OFFSET pour ajuster dynamiquement une plage ouverte
Vous avez déjà appris les méthodes dynamiques ARRAY_CONSTRAIN et INDIRECT pour ajuster dynamiquement une plage ouverte dans Google Sheets. Voici la dernière méthode en utilisant OFFSET.
Cette méthode suit la même logique que la méthode ARRAY_CONSTRAIN dynamique.
Syntaxe : OFFSET(référence_cellule; décalage_lignes; décalage_colonnes; [hauteur]; [largeur])
- référence_cellule (première cellule de la plage) : A2
- décalage_lignes (nombre de lignes à décaler) : 0
- décalage_colonnes (nombre de colonnes à décaler) : 0
- hauteur (hauteur des lignes) :
ArrayFormula(MATCH(2,1/(Sample!A2:A<>""),1)) - largeur (largeur des colonnes) :
ArrayFormula(match(2,1/(Sample!A2:2<>"")))
Maintenant, passons à la partie de codage.
=décaler(Sample!A2;0;0;ArrayFormula(MATCH(2,1/(Sample!A2:A<>""),1));ArrayFormula(match(2,1/(Sample!A2:2<>""))))
Comment exclure la ligne d’en-tête et la première colonne ?
Vous pouvez suivre la méthode ARRAY_CONSTRAIN dynamique.
Pour exclure la ligne d’en-tête, apportez les deux modifications suivantes :
- Remplacez « Sample!A2 » par « Sample!A3 »
- Remplacez « ArrayFormula(MATCH(2,1/(Sample!A2:A<> » »),1)) » par « ArrayFormula(MATCH(2,1/(Sample!A3:A<> » »),1)) »
Pour exclure la première colonne :
- Remplacez « Sample!A3 » par « Sample!B3 »
- Remplacez « ArrayFormula(match(2,1/(Sample!A2:2<> » » »)) par « ArrayFormula(match(2,1/(Sample!B2:2<> » » »))
Voilà tout. Merci de nous avoir suivi ! Profitez-en bien !
Pour plus de conseils sur Google Sheets, consultez Crawlan.com.
