Dans Microsoft Excel, c'est une tâche courante de faire référence à des cellules dans d'autres feuilles de calcul ou même dans différents fichiers Excel. Au début, cela peut sembler un peu écrasant et déroutant, mais une fois qu'on a compris comment ça marche, ce n'est pas si difficile.
Dans ce billet, Nous verrons comment faire référence à une autre feuille dans le même fichier Excel et comment faire référence à un fichier Excel différent. De plus, nous couvrirons des choses comme comment référencer une plage de cellules dans une fonction, comment garder les choses simples avec des noms définis et comment utiliser RECHERCHEV pour les références dynamiques.
Comment référencer une autre feuille dans le même fichier Excel
Une référence de cellule de base est écrite comme la lettre de colonne suivie du numéro de ligne.
Ensuite, la référence de cellule B3 fait référence à la cellule à l'intersection de la colonne B et de la ligne 3.
Lorsque vous faites référence à des cellules sur d'autres feuilles, cette référence de cellule est précédée du nom de l'autre feuille. Par exemple, Ci-dessous se trouve une référence à la cellule B3 dans le nom d'une feuille “Janvier”.
= janvier!B3
Le point d'exclamation (!) Séparer le nom de la feuille de l'adresse de la cellule.
Si le nom de la feuille contient des espaces, vous devez mettre le nom entre guillemets simples dans la référence.
='Ventes de janvier'!B3
Pour créer ces références, vous pouvez les écrire directement dans la cellule. Malgré cela, il est plus facile et plus fiable de laisser Excel écrire la référence pour vous.
Écrire un signe égal (=) dans une cellule, cliquez sur l'onglet Feuille, puis cliquez sur la cellule que vous souhaitez croiser.
En faisant cela, Excel écrit la référence pour vous dans la barre de formule.
Appuyez sur Entrée pour compléter la formule.
Comment référencer un autre fichier Excel
Vous pouvez référencer des cellules dans un autre classeur en utilisant la même méthode. Assurez-vous simplement que l'autre fichier Excel est ouvert avant de commencer à taper la formule.
Écrire un signe égal (=), passez à l'autre fichier, puis cliquez sur la cellule de ce fichier que vous souhaitez référencer. Appuyez sur Entrée lorsque vous avez terminé.
La référence croisée complète contient l'autre nom du classeur entre parenthèses, suivi du nom de la feuille et du numéro de cellule.
=[Chicago.xlsx]janvier!B3
Si le nom du fichier ou de la feuille contient des espaces, vous devrez joindre la référence du fichier (y compris les crochets) entre guillemets simples.
='[New York.xlsx]Janvier'!B3
Dans cet exemple, peut voir les signes du dollar ($) entrer l'adresse de la cellule. Ceci est une référence de cellule absolue (En savoir plus sur les références de cellules absolues).
Lors du référencement de cellules et de plages dans différents fichiers Excel, les références sont rendues absolues par défaut. Vous pouvez changer cela en une référence relative si nécessaire.
Si vous regardez la formule lorsque le classeur référencé est fermé, contiendra le chemin complet de ce fichier.
Même s'il est facile de créer des références à d'autres classeurs, sont plus sensibles aux problèmes. Les utilisateurs qui créent ou renomment des dossiers et déplacent des fichiers peuvent casser ces références et provoquer des erreurs.
Conserver les données dans un classeur, si c'est faisable, est plus fiable.
Comment croiser une plage de cellules dans une fonction
Le référencement d'une seule cellule est très utile. Mais il est possible que vous vouliez écrire une fonction (comme SUMA) qui fait référence à une plage de cellules dans une autre feuille de calcul ou un autre classeur.
Démarrez la fonction comme d'habitude, puis cliquez sur la feuille et la plage de cellules, de la même manière que vous l'avez fait dans les exemples précédents.
Dans l'exemple suivant, une fonction SUM additionne les valeurs de la plage B2: B6 dans une feuille de calcul nommée Ventes.
=SOMME(Ventes!B2:B6)
Comment utiliser des noms définis pour des références croisées simples
dans excel, peut nommer une cellule ou une plage de cellules. Ceci est plus significatif qu'une cellule ou une plage d'adresses lorsque vous les regardez en arrière. Si vous utilisez beaucoup de références dans votre feuille de calcul, nommer ces références peut rendre beaucoup plus facile de voir ce que vous avez fait.
Encore mieux, ce nom est unique pour toutes les feuilles de calcul de ce fichier excel.
Par exemple, nous pourrions nommer une cellule 'ChicagoTotal’ et puis la référence croisée se lirait:
=ChicagoTotal
C'est une alternative plus significative à une référence standard comme celle-ci:
=Ventes!B2
Il est facile de créer un nom défini. Commencez par sélectionner la cellule ou la plage de cellules que vous souhaitez nommer.
Cliquez sur la case Nom dans le coin supérieur gauche, tapez le nom que vous souhaitez attribuer, puis appuyez sur Entrée.
Lors de la création de noms définis, ne peut pas utiliser d'espaces. Pour cela, dans cet exemple, les mots étaient réunis dans le nom et séparés par une majuscule. Vous pouvez également séparer les mots avec des caractères comme un trait d'union (-) ou un trait de soulignement (_).
Excel dispose également d'un gestionnaire de noms qui facilite la surveillance de ces noms à l'avenir.. Cliquez sur Formules> Gestionnaire de noms. Dans la fenêtre Gestionnaire de noms, vous pouvez voir une liste de tous les noms définis dans le classeur, où sont-ils et quelles valeurs stockent-ils en ce moment.
Ensuite, vous pouvez utiliser les boutons en haut pour modifier et supprimer ces noms définis.
Comment formater les données sous forme de tableau
Lorsque vous travaillez avec une longue liste de données connexes, L'utilisation de la fonction Formater en tant que tableau d'Excel peut simplifier la façon dont vous y référencez les données.
Prenez la prochaine table simple.
Cela peut prendre la forme d'un tableau.
Cliquez sur une cellule de la liste, passer à l'onglet “Début”, Cliquez sur le bouton “Formater sous forme de tableau” puis sélectionnez un style.
Confirmez que la plage de cellules est correcte et que votre tableau a des en-têtes.
Ensuite, Vous pouvez donner un nom significatif à votre table depuis l'onglet “Concevoir”.
Ensuite, si nous devions additionner les ventes de Chicago, on pourrait se référer à la table par son nom (de n'importe quelle feuille), suivi d'une parenthèse ([) pour voir la liste des colonnes du tableau.
Sélectionnez la colonne en double-cliquant dessus dans la liste et entrez un crochet fermant. La formule résultante ressemblerait à ceci:
=SOMME(Ventes[Chicago])
Vous pouvez voir comment les tables peuvent faciliter la référence des données pour les fonctions d'agrégation telles que SUM et AVERAGE que les références de feuille standard.
Cette table est petite à des fins de démonstration. Plus le tableau est grand et plus vous avez de feuilles dans un classeur, plus d'avantages vous verrez.
Comment utiliser la fonction RECHERCHEV pour les références dynamiques
Toutes les références utilisées dans les exemples jusqu'à présent ont été définies sur une cellule ou une plage de cellules spécifique. C'est cool et, souvent, est suffisant pour vos besoins.
Malgré cela, Que faire si la cellule à laquelle vous faites référence a le potentiel de changer lorsque de nouvelles lignes sont insérées ou si quelqu'un trie la liste?
Dans ces scénarios, vous ne pouvez pas garantir que la valeur souhaitée sera toujours dans la même cellule que vous avez initialement référencée.
Une alternative dans ces scénarios consiste à utiliser une fonction de recherche dans Excel pour trouver la valeur dans une liste. Cela le rend plus résistant aux changements de lame.
Dans l'exemple suivant, nous utilisons la fonction RECHERCHEV pour rechercher un employé sur une autre feuille par son identifiant d'employé, puis retourner sa date de début.
Voici la liste des exemples de travailleurs.
La fonction RECHERCHEV trouve la première colonne d'une table, puis renvoie les informations d'une colonne spécifiée à droite.
La fonction RECHERCHEV suivante recherche l'ID de l'employé saisi dans la cellule A2 de la liste ci-dessus et renvoie la date de jointure de la colonne 4 (quatrième colonne du tableau).
=RECHERCHEV(A2, employés!UNE:E,4,FAUX)
Vous trouverez ci-dessous une illustration de la façon dont cette formule recherche la liste et renvoie les informations correctes.
La meilleure chose à propos de cette RECHERCHEV par rapport aux exemples précédents est que l'employé sera trouvé même si la liste change dans l'ordre.
Noter: RECHERCHEV est une formule incroyablement utile, et nous n'avons fait qu'effleurer la surface de sa valeur dans ce post. Vous pouvez en savoir plus sur l'utilisation de RECHERCHEV dans notre article sur le sujet..
Dans ce billet, Nous avons analysé plusieurs alternatives pour les références croisées entre les feuilles de calcul Excel et les classeurs. Choisissez l'approche qui convient à votre tâche et avec laquelle vous êtes à l'aise de travailler.
