Comment utiliser les opérateurs de trimrange et de référence pour ranger une feuille de calcul Excel

Vous cherchez un moyen de ranger votre feuille de calcul et de réduire sa taille sans avoir à utiliser des formules de plage dynamique comme le décalage, l'index ou le tocol? TrimRange détecte les cellules que vos données occupent et se développe automatiquement et se contractent.

L'opérateur de référence de Trim est une version raccourci plus simple de la fonction TRIMRANGE, et je le couvrirai plus tard dans l'article.

La syntaxe de trimrange

La fonction TRIMRANGE a trois arguments:

=TRIMRANGE(a,b,c)

où

• un (requis) est la plage à couper,
• b (Facultatif) détermine les lignes à couper (0 = pas de lignes, 1 = lignes de tête, 2 = lignes de fuite et 3 = lignes de tête et de fuite), et
• c (Facultatif) Détermine les colonnes à couper (0 = pas de colonnes, 1 = colonnes de tête, 2 = colonnes de fuite et 3 = colonnes de tête et de fuite).

Si vous omettez les arguments b et / ou cExcel va par défaut à la réduction des lignes et / ou des colonnes principales et traînantes.

Exemple 1: trimrange avec une somme

Dans cet exemple, je souhaite soustraire les valeurs de la colonne B de celles de la colonne C pour produire un profit.

Je sais que j'ajouterai des lignes supplémentaires en bas à un moment donné dans le futur, donc je veux qu'Excel les ramasse automatiquement dans le cadre de la formule de soustraction. Pour faire ça, je pourrait taper:

=(C2:C200)-(B2:B200)

dans la cellule D2, car cela renversait les références aux 200 premières lignes. Cependant, cela entraînerait une feuille de calcul en désordre avec beaucoup de calculs vides. De plus, si j'utilisais des références en colonne entière, le calcul se répandait tout au bas de la feuille de calcul. Cela entraînerait plus d'un million de calculs, augmentant ainsi considérablement la taille de ma feuille de calcul et le ralentissant.

Au lieu de cela, je vais taper:

=TRIMRANGE(C2:C200)-TRIMRANGE(B2:B200)

dans la cellule D2, qui couperait efficacement les cellules redondantes au bas de mes données pour empêcher Excel de devoir travailler trop dur et garder ma feuille de calcul en vue.

J'ai omis des arguments b et c Dans les références de trimrange ci-dessus, car le défaut de par défaut de coupe et les lignes et les lignes de fin par Excel fonctionnent bien dans cet exemple, car il n'y a pas de lignes ou de colonnes vierges de premier plan, et il n'y a pas de données à droite de la colonne D.

Maintenant, lorsque j'ajoute d'autres rangées de données en bas, les cellules correspondantes de la colonne de profit se calculent automatiquement.

Exemple 2: trimrange avec xlookup

Dans ce deuxième exemple, j'ai une liste de joueurs de football, et je vais utiliser la fonction xlookup pour me dire de quel maillot couleur je dois leur fournir, en fonction de l'équipe dans laquelle ils sont.

Je sais aussi que je vais recruter cinq autres joueurs, donc quand j'ajoute ma formule Xlookup, je dois l'étendre à la ligne 22. Donc, dans la cellule E2, je pourrait taper:

=XLOOKUP(B2:B22,$G$2:$G$7,$H$2:$H$7)

Où B2: B22 est la gamme, $ G 2: $ G 7 $ est le tableau de recherche, et $ H 2: $ H 7 $ est le tableau de retour. J'ai utilisé $ symboles pour dire à Excel que ce sont des références cellulaires absolues (fixes). Cependant, comme avec l'exemple précédent, cela laisserait les cinq rangées de données vides en désordre en raison de l'erreur # n / a. En outre, Excel travaille plus dur qu'il ne le faut, un problème qui pourrait affecter les performances si vous avez beaucoup de lignes vierges incluses dans votre formule.

je pourrait Utilisez également le décalage, l'index ou le TOCOL pour créer des formules de plage dynamique, mais ce sont des moyens beaucoup plus compliqués d'obtenir le même résultat que la méthode de trimestre beaucoup plus simple.

Donc, dans la cellule E2, je vais taper:

=XLOOKUP(TRIMRANGE(B2:B22,2),$G$2:$G$7,$H$2:$H$7)

qui est exactement la même formule que ci-dessus, sauf que j'ai fait référence à la plage (B2: B22) dans la fonction TrimRange. Remarquez comment, cette fois, j'ai décidé d'inclure le deuxième argument (« 2 »), qui dit à Excel que je veux couper les lignes vierges de fin. Cependant, je n'ai pas inclus de troisième argument, car ma gamme ne couvre qu'une colonne.

Cette fois, Excel a réduit mon résultat xlookup, et lorsque j'ajoute plus de lignes de données en bas, la cellule de la colonne E remplit automatiquement.

Utilisation des opérateurs de référence de garniture

Certes, certaines des formules que j'ai utilisées dans les exemples ci-dessus sont assez complexes. C'est pourquoi Microsoft a également introduit des opérateurs de référence de TRIM, une version sténographique de la fonction de trimrange. En d'autres termes, les opérateurs de référence de garniture vous évitent d'avoir à intégrer le trimrange dans d'autres fonctions. De plus, les opérateurs de référence de garniture ne vous obligent pas à spécifier si vous coupez des lignes ou des colonnes, car ils coupent automatiquement les deux.

L'utilisation de l'opérateur de référence de garniture implique d'ajouter une période (.) Sur l'un ou l'autre côté du côlon dans votre formule:

Étant donné que les opérateurs de référence de garniture sont un seul « point » dans votre formule, ils peuvent être difficiles à repérer lorsque vous passez en revue votre travail ou la feuille de calcul de quelqu'un d'autre. C'est quelque chose à surveiller si les choses ne fonctionnent pas comme vous pouvez vous y attendre!

En utilisant les mêmes données de football que dans l'exemple 2, dans la cellule E2, je vais taper:

=XLOOKUP(B2:.B22,$G$2:$G$7,$H$2:$H$7)

Remarquez comment j'ai ajouté une période après le côlon en B2: .b22. C'est parce que je veux Excel pour couper les blancs. La capture d'écran ci-dessous révèle le même résultat, même si j'ai utilisé une formule beaucoup plus simple sans nicher la fonction TrimRange dans ma formule.

Comme lorsque j'utilise la fonction TrimRange, je sais que je peux ajouter plus de lignes de données en bas, et la fonction XLookup s'appliquera à ces données.

Pourquoi utiliser des références de trimrange et de coupe plutôt que des tables structurées?

Vous pouvez à juste titre affirmer que vous devez plutôt présenter vos données dans des tables Excel formatées et utiliser des références structurées, qui s'étendent automatiquement pour inclure de nouvelles données et tous les calculs pertinents que vous créez. Dans la plupart des situations, je serais d'accord.

Cependant, les tableaux de déversement (et certains lambdas) ne peuvent pas aller dans des tables structurées, donc dans ces situations, l'utilisation de Trimrange est une alternative pratique pour avoir votre manche. De plus, dans certains scénarios, vous préférerez peut-être placer une plage à l'extérieur d'un tableau structuré (par exemple, si vous souhaitez formater vos données de manière unique), et la fonction de trimestre vous aide à garder les données non structurées.

---

Parallèlement aux opérateurs de fonction de trimRange et de finition d'Excel, il existe d'autres moyens de nettoyer vos données dans Excel, comme utiliser la requête de puissance ou tirer le meilleur parti de l'IA via Copilot.