Vous devez savoir ce que fait le signe de hachage dans les formules Excel
Résumé
L'opérateur de plage renversée Excel (#) permet aux formules de s'adapter automatiquement aux modifications de la taille de la plage versée.
Vous devez utiliser Excel pour Microsoft 365 sur Windows ou Mac pour utiliser cet outil pratique.
Des fonctions bien connues comme Unique, Countif et Sortby peuvent être utilisées avec l'opérateur de plage renversée pour générer des listes dynamiques et triables.
L'utilisation d'un symbole de hachage (#) – également connu sous le nom d'un opérateur de gamme renversé – dans une formule Excel est un moyen de dire au programme de considérer tous les résultats dans une plage renversée. En conséquence, même si la gamme renversée se développe ou se rétrécit, la formule contenant le # reflétera automatiquement ce changement.
Vous ne pouvez profiter de l'opérateur de plage renversée d'Excel que si vous utilisez Excel pour Microsoft 365 sur Windows ou Mac.
Imaginons que vous dirigez un sanctuaire d'animaux, et votre feuille de calcul contient une table Excel formatée appelée Animals_aditted, qui montre les animaux actuellement sous votre garde.
Pour que vous puissiez faire le meilleur usage de l'espace que vous avez au sanctuaire, vous devez savoir combien de chaque type d'animal vous avez et combien de types d'animaux différents que vous avez dans l'ensemble.
Parce que les fonctions que vous êtes sur le point d'utiliser produisent des tableaux renversés et que les tableaux renversés ne fonctionnent pas dans des tables Excel formatées, vous devez taper les formules dans les zones de votre feuille de calcul qui sont pas formaté comme une table Excel.
Pour voir combien de chaque animal se trouvent actuellement dans votre sanctuaire, dans les cellules D1 et E1 (les cellules au-dessus d'où ira vos premiers tableaux renversés), tapez les en-têtes de colonne Animal et Compterrespectivement.
Maintenant, dans la cellule D2, type:
=UNIQUE(Animals_Admitted[Animal])
où UNIQUE est la fonction Excel qui répertorie les éléments uniques dans une gamme, Animals_ADMIT est le nom du tableau où se trouve les données d'origine, et [Animal] est le nom attribué à la troisième colonne de ce tableau.
Lorsque vous appuyez sur Entrée, vous obtenez un tableau renversé qui répertorie chaque élément unique dans la colonne Animal. Vous savez qu'il s'agit d'un réseau renversé car une ligne bleue entoure le résultat chaque fois que vous sélectionnez l'une des cellules affectées.
Le résultat de la fonction unique est trié en fonction de l'ordre dans lequel chaque élément est apparu pour la première fois dans les données d'origine.
Maintenant, il est temps de faire en sorte que Excel compte le nombre de chaque animal dans votre table d'origine, et c'est là que l'utilisation du hacher Le panneau rend la vie beaucoup plus facile.
Pour ce faire, vous devrez utiliser la fonction COUNFIF. Cependant, parce que vous voulez Excel tous Les animaux renvoyés par la fonction unique de la colonne D – même si certains animaux sont ajoutés ou supprimés de cette liste plus tard – vous devez ajouter un signe de hachage après la référence des critères.
Donc, dans la cellule E2, type:
=COUNTIF(Animals_Admitted[Animal],D2#)
où Countif est la fonction qui compte le nombre d'occurrences, Animals_ADMIT[Animal] est la gamme contenant chaque animal dans votre table d'origine, et D2 # Dit Excel que les critères de la fonction Countif sont un réseau renversé commençant dans la cellule D2 et, par conséquent, peuvent changer la taille.
Au lieu de taper la formule manuellement, si vous utilisez votre souris pour sélectionner les cellules pour chaque argument, la formule adoptera automatiquement les noms de colonne (également appelés références structurées) et, le cas échéant, ajouteront le signe de hachage.
Maintenant, imaginons qu'un hérisson est amené dans votre sanctuaire, et ce hérisson sera le seul actuellement sous votre garde.
Pour ajouter une ligne supplémentaire à une table formatée, cliquez et faites glisser la poignée dans le coin inférieur droit vers le bas.
Étant donné que vous avez référencé une table de table formatée dans la colonne D et utilisé l'opérateur de plage renversée dans votre formule Countif dans la colonne E, le hérisson est automatiquement ajouté à la liste de la colonne D, et le nombre de hérissons dans la colonne E affiche correctement comme “1.”.
Maintenant, vous voulez créer une liste qui trie les animaux par compte.
Après avoir tapé les en-têtes ci-dessus dans les cellules F1 et G1, en type F2 cellulaire:
=SORTBY(D2#:E2#,E2#,-1)
où TRIER PAR est la fonction Excel qui trie une plage en fonction des valeurs dans une autre plage ou tableau, D2 #: e2 # dit à Excel que le tableau – qui occupe les colonnes D et E – contient deux colonnes de tableaux renversés à partir des cellules D2 et E2, E2 # est le tableau renversé sur lequel trier, et -1 dit à Excel de trier les données dans l'ordre descendant.
Le résultat de la fonction SORTBY est d'abord trié en fonction de l'ordre que vous avez spécifié dans la formule SORTBY. Cependant, si des variables ont les mêmes valeurs après ce tri initial, elles seront ensuite triées en fonction de l'ordre dans lequel chaque élément est apparu pour la première fois dans les données d'origine.
Parce que vous avez utilisé ces panneaux de hachage dans la formule, vous pouvez être assuré, en sécurité, en sachant que vos listes se développent et se contracteront dynamiquement en fonction des données de votre tableau d'origine.
Enfin, vous avez également besoin d'un décompte de base du nombre total de types d'animaux dans votre sanctuaire.
Pour ce faire, après avoir tapé un en-tête pertinent dans la cellule H1, dans H2, tapez
=COUNTA(D2#)
où Comte est la formule Excel qui compte le nombre de cellules dans une gamme qui ne sont pas vides, et D2 # dit à Excel que la plage est un tableau renversé. En d'autres termes, nous disons à Excel de compter le nombre d'animaux que la fonction unique a été renvoyée dans le tableau renversé commençant dans la cellule D2, et que ce nombre peut changer.
En tant que dernier test, supposons que Londres la tortue est publiée. Après avoir retiré Londres des données originales, car il était la seule tortue dans le sanctuaire, Cell H2 vous dit correctement qu'il n'y a plus que 18 animaux uniques sous votre garde, et “Turtle” n'apparaît plus dans les autres listes que vous avez créées.
Opérateurs de gamme renversés (#): pointeurs finaux
Avant de commencer, il y a quelques points supplémentaires à noter.
Tout d'abord, en plus de référencer une plage renversée dans la même feuille de calcul, vous pouvez également référencer une plage renversée dans une autre feuille. Par exemple, pour référencer un tableau renversé commençant dans la cellule A2 sur la feuille 2, vous pouvez taper:
=Sheet2!A1#
Deuxièmement, vous pouvez référencer une gamme renversée dans un autre classeur. Bien que Microsoft dit que le classeur référencé doit être ouvert pour que cela ne renvoie pas le #ref! Erreur, lorsque je l'ai testé avec un classeur fermé, la référence a continué à fonctionner. Assurez-vous simplement de sauvegarder les deux classeurs et de les faire connecter tous les deux au même compte OneDrive pour vous assurer de ne perdre aucune donnée.
Enfin, pour éviter que vos formules renvoient le #spill! Erreur, assurez-vous que toutes les cellules dans lesquelles le tableau est due au déversement sont dégagées d'autres données et non fusionnées.
Maintenant, que vous visiez simplement à élargir vos connaissances des formules et fonctions d'Excel, ou que vous ayez postulé pour un emploi qui nécessite une expertise Excel, vous pouvez dire en toute confiance que vous savez ce que fait le signe de hachage dans les formules Microsoft Excel!
