Les liens de travail qui ne travaillent pas les ravages à vos feuilles de travail? Ne t'inquiète pas! Ce tutoriel vous apprendra 3 fa?ons faciles de trouver et de réparer les liens Excel cassés, ainsi que notre propre solution en un clic en bonus supplémentaire :)

Les cellules Excel peuvent souvent être liées à d'autres classeurs pour tirer des informations pertinentes à partir de là. Lorsqu'un classeur source est supprimé, déplacé ou endommagé, des références externes à ce fichier se décomposent et que vos formules commencent à renvoyer les erreurs. De toute évidence, pour réparer les formules, vous devez trouver des liens cassés. La question est de savoir comment? Les réponses suivent ci-dessous :)

Trouver et réparer les liens cassés dans Excel

Pour détecter les liens non travaillant vers d'autres classeurs, effectuez les étapes suivantes:

1. Dans l'onglet Données , dans le groupe Queries & Connections , cliquez sur le bouton Modifier les liens .

   Si ce bouton est grisé, cela signifie qu'il n'y a pas de références externes dans votre classeur.

   

2. Dans la bo?te de dialogue Modifier les liens , vous pouvez voir une liste de toutes les sources externes auxquelles votre classeur actuel fait référence. Lorsque cette fenêtre est ouverte pour la première fois, tous les liens ont un statut inconnu . Pour clarifier l'état, cliquez sur le bouton d' état de vérification du c?té droit:

   

3. Lorsque les informations sont mises à jour, passez en revue l'état de chaque lien. Pour plus de commodité, vous pouvez cliquer sur l'en-tête de la colonne d' état pour trier les enregistrements en conséquence.

   De toute évidence, les liens diagnostiqués comme erreur: la source introuvable est rompue. Dans mon classeur, il y a deux de ces liens:

   

4. Allons-y et réparons les liens cassés. Pour cela, sélectionnez la source qui signale une erreur et cliquez sur le bouton de modification de la source à droite:

   

5. Dans la fenêtre Changer la source qui s'ouvre, parcourez les dossiers de votre ordinateur jusqu'à ce que vous trouviez le classeur correct, sélectionnez-le et cliquez sur OK (ou double-cliquez sur le classeur):

   

6. Répétez l'étape ci-dessus pour chaque source problématique individuellement. Une fois terminé, cliquez sur le bouton Fermer .

Après avoir corrigé toutes les sources erronées, vous remarquerez peut-être que votre liste de liens est en fait devenue plus courte. La raison en est que vous avez peut-être eu plusieurs occurrences du même classeur, et après avoir changé la source, les incorrectes ont disparu de la liste.

Par exemple, nous avions les paires suivantes qui faisaient référence au même fichier: Colrado Report.xlsx (malpellé) et Colorado Report.xlsx (correct); Florida_report.xlsx (inexistant) et Florida Report.xlsx (correct). Après avoir corrigé les liens, les sources incorrectes ont disparu et nous avons maintenant cette liste:

Identifier et corriger les liens cassés avec la recherche et le remplacement

La fonction de liens d'édition discutée ci-dessus peut vous aider à obtenir rapidement une liste de toutes les sources externes dans un classeur, mais elle ne montre pas quelles cellules contiennent ces références externes. Pour identifier ces cellules, vous pouvez utiliser la recherche et le remplacement d'Excel.

Trouvez des liens brisés vers tous ou un classeur spécifique

Les liens externes pointent toujours vers un autre fichier Excel qui a ".xl" dans le cadre de l'extension de nom de fichier tel que .xls, .xlsx, .xlsm, etc. Vous pouvez utiliser ce fait lors de la recherche de références à des classeurs externes. Ou vous pouvez rechercher un texte spécifique (substrice) dans un nom de classeur particulier. Les étapes détaillées suivent ci-dessous.

1. Appuyez sur Ctrl F pour ouvrir la bo?te de dialogue Rechercher et remplacer . Ou cliquez sur trouver et sélectionner > Rechercher… sur l'onglet Home dans le groupe d'édition .
2. Dans la bo?te de dialogue Rechercher et remplacer , cliquez sur le bouton Options .
3. Selon que vous souhaitiez trouver tous les liens externes dans un classeur ou uniquement des références à un fichier spécifique, tapez l'une des éléments suivants dans la bo?te de recherche: quelle bo?te:
   • Pour rechercher tous les liens, tapez .xl .
   • Pour rechercher des liens vers un classeur particulier, saisissez ce nom de classeur ou sa partie unique.
4. Dans la case intérieure , sélectionnez le classeur pour rechercher sur tous les onglets ou feuilles pour consulter la feuille de calcul actuelle.
5. Dans le look dans la bo?te, choisissez des formules .
6. Cliquez sur le bouton Find All .

Et maintenant, c'est la partie clé - analyser les résultats.

Si vous avez recherché toutes les références externes , cliquez sur l'en-tête de la colonne de valeur pour trier les résultats par valeur et faire défiler la liste. Les liens cassés vers d'autres classeurs auront un #Ref! erreur dans la colonne de valeur .

Si vous avez recherché des références à un classeur spécifique , passez simplement en revue les résultats.

Par exemple, nous savons que certaines cellules du rapport sommaire indiquent le fichier inexistant ( Colrado Report.xlsx ) et nous souhaitons savoir exactement quelles cellules. Donc, nous tapons le mot mal orthographié Colrado dans la bo?te de recherche et appuyez sur trouver tout . En conséquence, Excel affiche 3 de ces références dans 2 feuilles différentes. Le #ref! Les erreurs dans la colonne de valeur confirment que les trois liens sont rompus:

Note. Cette méthode de diagnostic des liaisons cassées n'est pas fiable à 100%. Par exemple, un #ref! L'erreur peut être renvoyée par une formule dans la cellule liée dans le classeur source, et il sera également affiché dans tous les résultats. Cela ne signifie cependant pas que la référence à cet autre classeur est rompue. Ainsi, lors de l'utilisation de cette approche, il va de soi pour raisonner de cliquer sur chaque erreur et de revérifier les références manuellement.

Fixez des liens brisés vers un classeur spécifique

Dans la liste de Rechercher tous les résultats, vous pouvez cliquer sur n'importe quel élément pour naviguer vers la cellule contenant le lien et modifier chacune individuellement. Ou vous pouvez utiliser la fonction Remplacer toute la fonction pour corriger toutes les occurrences d'un lien non valide à la fois. Voici comment:

1. Dans la bo?te de dialogue Rechercher et remplacer , passez à l'onglet Remplacer .
2. Dans la case Fin , tapez le nom ou le chemin du fichier incorrect.
3. Dans la zone Remplacez par la bo?te, saisissez le nom ou le chemin du fichier correct.
4. Cliquez sur Remplacer tout .

Note. Après avoir cliqué sur le bouton Remplacer tout , la fenêtre de mise à jour des valeurs pourrait vous ouvrir pour vous provoquer le choix du classeur source. Ne faites pas cela et cliquez simplement sur Annuler sans rien sélectionner.

à titre d'exemple, rempla?ons un mauvais nom de classeur Colrado Report.xlsx par le bon Colorado Report.xlsx . Dans ce cas particulier, le remplacement d'un seul mot ( Colrado) fonctionnera également. Cependant, veuillez garder à l'esprit qu'un texte spécifié sera remplacé n'importe où dans la cha?ne de chemin (le chemin complet vers un fichier s'affiche si le classeur source est fermé pour le moment). Ainsi, plus le petit texte dans lequel vous entrez, plus la chance d'une erreur est grande.

Une fois le remplacement terminé, le #Ref! Les erreurs disparaissent des résultats et les valeurs normales sont présentées à la place:

De la même manière, vous pouvez remplacer le chemin d'accès à un fichier source . Par exemple, si le classeur source était à l'origine dans le dossier Documents , puis que vous l'avez déplacé vers le sous-dossier de rapports dans le même dossier, vous pouvez remplacer \ Documents \ par \ Documents \ Reports \ .

Quelqu'un peut dire qu'il est ridicule d'utiliser la recherche et le remplacement pour résoudre le problème des liens cassés, mais pour autant que je sache, c'est la seule fonctionnalité intégrée qui peut vous aider à trouver des cellules contenant des liens cassés .

Vérifiez les liens cassés avec VBA

Le code ci-dessous boucle via chaque référence externe dans un classeur et essaie de déterminer si elle est cassée ou non. Pour trouver des fichiers externes, nous utilisons la méthode Linksources . Pour identifier les liens cassés, la méthode LinkInfo est utilisée.

Une liste de liens non valides est sortie dans une nouvelle feuille de calcul nommée Rapport Broken Links . La colonne B a un lien hypertexte à la cellule contenant le lien.

Vous pouvez insérer le code dans votre propre classeur ou télécharger notre exemple de fichier avec la macro ainsi que les instructions étape par étape sur la fa?on de l'utiliser.

Note. Ce code ne trouve que des liens vers des classeurs non valides (inexistants, déplacés ou supprimés), mais ne pas manquer de feuilles. La raison en est que la méthode LinkInfo vérifie uniquement le nom du fichier. Une tentative de vérifier un nom de feuille entra?ne l'erreur 2015.

Trouver des liens cassés dans Excel en un clic

En lisant la première partie de ce tutoriel, vous pourriez vous sentir un peu découragé qu'il n'y a pas de moyen simple de trouver tous les liens cassés dans un fichier, par exemple en cliquant sur un seul bouton. Bien qu'une telle solution n'existe pas dans Excel, rien ne nous empêche de le développer nous-mêmes :)

Pour les utilisateurs de notre suite ultime, nous fournissons un outil en un clic pour trouver toutes les références externes dans un classeur ou uniquement des liens cassés. Cliquez simplement sur les liens de recherche? Bouton dans l'onglet Oneaux AbleBits , et vous verrez immédiatement une liste de tous les liens dans le classeur actuel, où ceux non valides sont mis en évidence en rouge clair. Pour limiter la liste aux liens de ne pas fonctionner, sélectionnez la case Broken Links uniquement .

Cliquez sur une adresse de cellule dans le volet du complément vous amènera à une cellule qui contient un lien particulier . C'est tout ce qu'il y a!

Contrairement au code VBA ci-dessus, le complément trouve toutes sortes de liens cassés, y compris ceux où une feuille est manquante ou malppée.

C'est comment vérifier les liens cassés dans Excel. Je vous remercie d'avoir lu et j'espère vous voir sur notre blog la semaine prochaine!

Téléchargements disponibles:

Version entièrement fonctionnelle de Suite ultime (fichier .exe) macro pour trouver des liens cassés (.xlsm)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!