Organiser des feuilles de calcul Excel à l'aide des opérateurs TRIMRANGE et Trim Ref
Excel est un outil puissant pour gérer les données, mais l’organisation de grands tableaux peut être difficile sans les bons outils. Les opérateurs TRIMRANGE et Trim Ref fournissent une solution efficace pour organiser les données et réduire l'encombrement dans vos tables. Dans cet article, nous vous expliquerons comment utiliser ces déclencheurs pour améliorer l'organisation de vos feuilles de calcul Excel, vous aidant ainsi à améliorer votre efficacité au travail et à simplifier la gestion des données. Si vous cherchez des moyens d’améliorer votre expérience Excel, voici les étapes à suivre pour y parvenir.
Vous cherchez un moyen d'organiser et de réduire la taille de votre feuille de calcul sans avoir à utiliser des formules de plage dynamique telles que OFFSET, INDEX ou TOCOL ? TRIMRANGE détecte les cellules occupées par vos données et les agrandit et les contracte automatiquement avec elles.
ملاحظة
L'opérateur Trim Ref est une version plus simple et raccourcie de la fonction TRIMRANGE, et je l'aborderai plus tard dans l'article.
STRUCTURE DE LA GAMME DE TRIM
La fonction TRIMRANGE a trois arguments :
=RANGEE DE GARNITURE(a,b,c)
Où
- a (obligatoire) est la plage à couper,
- b (facultatif) Spécifie les lignes à couper (0 = aucune ligne, 1 = lignes de début, 2 = lignes de fin et 3 = lignes de début et de fin), et
- c (facultatif) Spécifie les colonnes à couper (0 = aucune colonne, 1 = colonnes de début, 2 = colonnes de fin et 3 = colonnes de début et de fin).
Si vous omettez les arguments b et/ou c, Excel coupera par défaut les lignes et/ou les colonnes de début et de fin.
ملاحظة
Au moment de la rédaction (janvier 2025), TRIMRANGE ne vous permet pas de couper les lignes ou les colonnes vides entre les données existantes. Vous ne pouvez l'utiliser que pour couper des données avant ou après vos données. Utilisez plutôt d’autres méthodes pour supprimer les lignes de données vides entre les lignes remplies de données.
Exemple 1 : TRIMRANGE avec SUM
Dans cet exemple, je souhaite soustraire les valeurs de la colonne B de celles de la colonne C pour produire un bénéfice.
Je sais que j'ajouterai des lignes supplémentaires en bas à un moment donné dans le futur, je veux donc qu'Excel récupère automatiquement ces lignes dans le cadre de la formule de soustraction. Pour ce faire, je peux écrire :
=(C2:C200)-(B2:B200)
Dans la cellule D2, cela renversera les références aux 200 premières lignes. Cependant, cela créerait une feuille de calcul désordonnée avec beaucoup de calculs vides. De plus, si vous utilisez des références de colonnes complètes, le calcul fuira dans la feuille de calcul. Cela entraînera plus d’un million de calculs, augmentant ainsi considérablement la taille de la feuille de calcul et la ralentissant.
Au lieu de cela, j'écrirai :
=RANGEE DE COMPENSATION(C2:C200)-RANGEE DE COMPENSATION(B2:B200)
Dans la cellule D2, cela réduirait efficacement les cellules en double au bas de mes données pour empêcher Excel de travailler trop dur et garder ma feuille de calcul bien rangée.
ملاحظة
J'ai omis les arguments b et c dans les références TRIMRANGE ci-dessus, car le paramètre par défaut d'Excel consistant à rogner les colonnes et les lignes de début et de fin fonctionne bien dans cet exemple, car il n'y a pas de lignes ou de colonnes de début vides et aucune donnée à droite de la colonne D .
Désormais, lorsque j'ajoute un plus à partir des lignes de données en bas, les cellules correspondantes dans la colonne des bénéfices seront automatiquement calculées.
Exemple 2 : TRIMRANGE avec XLOOKUP
Dans ce deuxième exemple, j'ai une liste de joueurs de football et j'utiliserai la fonction XLOOKUP pour me dire quelle couleur de maillot je dois leur fournir, en fonction de l'équipe à laquelle ils appartiennent.
Je sais aussi que je vais recruter cinq joueurs supplémentaires, donc lorsque j'ajoute la formule XLOOKUP, je dois l'étendre jusqu'à la ligne 22. Ainsi, dans la cellule E2, je peux taper :
=XLOOKUP(B2:B22,$G$2:$G$7,$H$2:$H$7)
où B2:B22 est la plage, $G$2:$G$7 est le tableau de recherche et $H$2:$H$7 est le tableau de retour. J'ai utilisé des symboles $ pour indiquer à Excel qu'il s'agit de références de cellules absolues (fixes). Cependant, comme dans l'exemple précédent, les cinq lignes de données vides sembleront non ordonnées en raison de l'erreur #N/A. De plus, Excel travaille plus dur qu’il ne le devrait, ce qui constitue un problème qui peut affecter les performances si vous avez beaucoup de lignes vides incluses dans votre formule.
Je peux également utiliser OFFSET, INDEX ou TOCOL pour créer des formules de plage dynamique, mais ce sont des moyens plus complexes pour obtenir le même résultat que la méthode TRIMRANGE plus simple.
Donc, dans la cellule E2, je vais taper :
=XLOOKUP(TRIMRANGE(B2:B22,2),$G$2:$G$7,$H$2:$H$7)
C'est exactement la même formule que ci-dessus, sauf que j'ai référencé la plage (B2:B22) à l'intérieur de la fonction TRIMRANGE. Notez que cette fois, j’ai décidé d’inclure le deuxième argument (« 2 »), qui indique à Excel que je souhaite supprimer les lignes vides de fin. Cependant, je n’ai pas inclus de troisième argument, car mon champ d’application ne s’étend qu’à une seule colonne.
Cette fois, Excel a tronqué mon résultat XLOOKUP, et lorsque j'ai ajouté des lignes de données supplémentaires en bas, la cellule de la colonne E a été automatiquement remplie.
Utilisation des opérateurs Trim Ref
Certaines des formules que j’ai utilisées dans les exemples ci-dessus sont certainement assez complexes. C'est pourquoi Microsoft a également introduit les opérateurs Trim Ref, une version raccourcie de la fonction TRIMRANGE. En d'autres termes, les opérateurs Trim Ref vous évitent d'avoir à inclure TRIMRANGE dans d'autres fonctions. De plus, les opérateurs Trim Ref ne nécessitent pas que vous spécifiiez si vous rognez des lignes ou des colonnes, car ils rognent automatiquement les deux.
L'utilisation de l'opérateur Trim Ref consiste à ajouter un point (.) à l'un ou aux deux côtés des deux points dans votre formule :
|
|
||
|---|---|---|---|
|
|
||
|
|
||
|
Espaces supplémentaires au début et à la fin |
ملاحظة
Étant donné que les opérateurs Trim Ref constituent un « point » unique dans votre formule, ils peuvent être difficiles à repérer lors de la révision de votre travail ou de la feuille de calcul de quelqu'un d'autre. C'est quelque chose à surveiller si les choses ne se passent pas comme prévu !
En utilisant les mêmes données de joueur de football que dans l'exemple 2, dans la cellule E2, je taperais :
=XLOOKUP(B2:.B22,$G$2:$G$7,$H$2:$H$7)
Remarquez comment j'ai ajouté un point après les deux points dans B2:.B22. C'est parce que je veux qu'Excel supprime les espaces de fin. La capture d'écran ci-dessous montre le même résultat, bien que j'aie utilisé une formule plus explicite sans inclure la fonction TRIMRANGE dans ma formule.
Par exemple, lorsque j'utilise la fonction TRIMRANGE, je sais que je peux ajouter des lignes de données supplémentaires en bas et que la fonction XLOOKUP sera appliquée à ces données.
Pourquoi utiliser TRIMRANGE et Trim Refs plutôt que des tables structurées ?
Vous pourriez à juste titre affirmer que vous devriez plutôt présenter vos données dans des tableaux Excel formatés et utiliser des références structurées, qui s’étendent automatiquement pour inclure de nouvelles données et tous les calculs associés que vous créez. Dans la plupart des situations, je suis d’accord.
Cependant, les matrices de déversement (et certains LAMBDAS) ne peuvent pas être placées dans des tables structurées, donc dans ces situations, l'utilisation de TRIMRANGE est une alternative utile que vous pouvez utiliser. De plus, dans certains scénarios, vous préférerez peut-être placer une plage en dehors d'un tableau structuré (par exemple, si vous souhaitez formater vos données de manière unique), et la fonction TRIMRANGE vous aide à organiser les données non structurées.
Outre la fonction TRIMRANGE d'Excel et les opérateurs Trim Refs, il existe d'autres moyens de nettoyer vos données dans Excel, comme l'utilisation de Power Query ou l'exploitation complète de l'IA avec Copilot.