Vous devez savoir ce que fait le signe dièse dans les formules Excel

Sommaire

Toggle

Résumé

• L’opérateur de plage renversée Excel (#) permet aux formules de s’ajuster automatiquement aux changements dans la taille de la plage renversée.

• Vous devez utiliser Excel pour Microsoft 365 sur Windows ou Mac pour utiliser cet outil pratique.

• Des fonctions bien connues telles que 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 dièse (#), également appelé opérateur de plage dispersée, dans une formule Excel, est un moyen d'indiquer au programme de prendre en compte tous les résultats d'une plage dispersée. Par conséquent, même si la plage étendue augmente ou diminue, 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 gérez un sanctuaire pour animaux et que votre feuille de calcul contient un tableau Excel formaté appelé Animals_Admit, qui montre les animaux actuellement sous votre garde.

Afin que vous puissiez utiliser au mieux l'espace dont vous disposez au sanctuaire, vous devez savoir combien de chaque type d'animal vous possédez et combien de types d'animaux différents vous possédez au total.

Étant donné que les fonctions que vous êtes sur le point d'utiliser produisent des tableaux répartis et que les tableaux répartis ne fonctionnent pas dans les tableaux Excel formatés, vous devez saisir les formules dans les zones de votre feuille de calcul qui sont pas formaté sous forme de tableau Excel.

Pour voir combien de chaque animal se trouvent actuellement dans votre sanctuaire, dans les cellules D1 et E1 (les cellules au-dessus où iront vos premiers tableaux renversés), tapez les en-têtes de colonnes Animal et Compterrespectivement.

Maintenant, dans la cellule D2, tapez :

=UNIQUE(Animals_Admitted(Animal))

où UNIQUE est la fonction Excel qui répertorie les éléments uniques dans une plage, Animaux_Admis est le nom de la table où se trouvent les données d'origine, et (Animal) est le nom attribué à la troisième colonne de cette table.

Lorsque vous appuyez sur Entrée, vous obtenez un tableau réparti qui répertorie chaque élément unique dans la colonne Animal. Vous savez qu'il s'agit d'un tableau dispersé 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é selon 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 qu'Excel compte le nombre d'animaux de chaque animal dans votre tableau d'origine, et c'est là que vous utilisez le hacher le signe rend la vie beaucoup plus facile.

Pour ce faire, vous devrez utiliser la fonction COUNTIF. Cependant, parce que vous voulez qu'Excel compte tous pour les animaux renvoyés par la fonction UNIQUE dans la colonne D (même si certains animaux sont ajoutés ou supprimés de cette liste ultérieurement), vous devez ajouter un signe dièse après la référence du critère.

Alors, dans la cellule E2, tapez :

=COUNTIF(Animals_Admitted(Animal),D2#)

où COUNTIF est la fonction qui compte le nombre d'occurrences, Animaux_Admis(Animal) est la plage contenant chaque animal dans votre table d'origine, et D2# indique à Excel que les critères de la fonction COUNTIF sont un tableau réparti commençant dans la cellule D2 et peuvent donc changer de taille.

Au lieu de saisir la formule manuellement, si vous utilisez votre souris pour sélectionner les cellules de chaque argument, la formule adoptera automatiquement les noms de colonnes (également appelés références structurées) et, le cas échéant, ajoutera le signe dièse.

Imaginons maintenant qu'un hérisson soit amené dans votre sanctuaire, et ce hérisson sera le seul actuellement sous votre garde.

Pour ajouter une ligne supplémentaire à un tableau formaté, cliquez et faites glisser la poignée dans le coin inférieur droit vers le bas.

Étant donné que vous avez référencé un en-tête de tableau formaté dans la colonne D et utilisé l'opérateur de plage renversé dans votre formule COUNTIF dans la colonne E, le hérisson est automatiquement ajouté à la liste dans la colonne D et le nombre de hérissons dans la colonne E s'affiche correctement comme « 1 ».

Maintenant, vous souhaitez créer une liste qui trie les animaux par nombre.

Après avoir tapé les en-têtes ci-dessus dans les cellules F1 et G1, dans la cellule F2, tapez :

=SORTBY(D2#:E2#,E2#,-1)

où TRIER PAR est la fonction Excel qui trie une plage en fonction des valeurs d'une autre plage ou d'un autre tableau, D2#:E2# indique à Excel que le tableau, qui occupe les colonnes D et E, contient deux colonnes de tableaux répartis commençant aux cellules D2 et E2, E2# est le tableau répandu sur lequel trier, et -1 indique à Excel de trier les données par ordre décroissant.

Le résultat de la fonction SORTBY est d'abord trié selon 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 selon 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 signes dièse dans la formule, vous pouvez être assuré que vos listes s'agrandiront et se contracteront dynamiquement en fonction des données de votre table d'origine.

Enfin, vous avez également besoin d'un décompte de base du nombre total d'espèces 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 d'une plage qui ne sont pas vides, et D2# indique à Excel que la plage est un tableau réparti. En d’autres termes, nous disons à Excel de compter le nombre d’animaux renvoyé par la fonction UNIQUE dans le tableau renversé commençant dans la cellule D2, et que ce nombre peut changer.

Comme test final, supposons que la tortue soit relâchée à Londres. Après avoir supprimé London des données d'origine, puisqu'il était la seule tortue du sanctuaire, la cellule H2 vous indique correctement qu'il n'y a désormais que 18 animaux uniques sous votre garde, et « Tortue » n'apparaît plus dans les autres listes que vous avez créées.

Opérateurs de champ de tir renversé (#) : derniers indicateurs

Avant de commencer, il y a quelques points supplémentaires à noter.

Premièrement, en plus de référencer une plage répartie dans la même feuille de calcul, vous pouvez également référencer une plage répartie dans une autre feuille. Par exemple, pour référencer un tableau réparti commençant dans la cellule A2 de la feuille 2, vous pouvez taper :

=Sheet2!A1#

Deuxièmement, vous pouvez référencer une plage répandue dans un autre classeur. Bien que Microsoft dise 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 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 doit se répandre sont exemptes d'autres données et non fusionnées.

---

Désormais, que vous souhaitiez simplement élargir vos connaissances sur les formules et les fonctions d'Excel, ou que vous ayez postulé pour un emploi nécessitant une expertise d'Excel, vous pouvez affirmer en toute confiance que vous savez ce que fait le signe dièse dans les formules Microsoft Excel !