Partage de cas Excel : génération par lots de répertoires hyperliés et mises à jour automatiques

Cet article vous présentera la fonction GET.WORKBOOK et partagera un cas pour voir comment utiliser cette fonction pour générer par lots des répertoires avec hyperliens dans Excel et les mettre à jour automatiquement. Venez apprendre à créer des répertoires de feuilles de calcul dans Excel !

Au travail, vous pouvez rencontrer un classeur Excel avec de nombreuses feuilles de calcul, tout comme un livre avec de nombreuses pages. À ce stade, si vous pouvez créer un répertoire de feuilles de calcul, non seulement toutes les feuilles de calcul peuvent afficher le nom du tableau, mais aussi. en cliquant sur le nom de la feuille de calcul, vous pouvez accéder rapidement à la page de feuille de calcul spécifiée, ce qui améliorera considérablement notre efficacité de travail.

Alors, certains cousins ​​​​ont commencé à le faire. Ils ont utilisé manuellement Excel pour créer des liens de répertoire pointant vers chaque feuille de calcul. Finalement, des dizaines de minutes plus tard, ils ont terminé la création...

À ce moment-là, si la feuille de calcul change. ou le travail Si le tableau est ajouté, tout le travail précédent sera vain, et il devra être recréé et modifié, ce qui prend du temps et demande beaucoup de travail.

Aujourd'hui, je vais partager avec vous une méthode très intelligente sur la façon de créer par lots des répertoires avec des hyperliens dans Excel. Quelle que soit la façon dont la feuille de calcul change ou est ajoutée, elle peut être automatiquement extraite et créée, ce qui permet d'économiser du temps et des efforts.

Comme indiqué ci-dessous, il y a 8 feuilles de calcul dans le classeur Afin d'accéder rapidement à la feuille de calcul spécifiée, nous créons un répertoire de feuilles de calcul pour celle-ci.

Créez d'abord une nouvelle feuille de calcul nommée "Table des matières"

Sélectionnez l'onglet "Formules" et cliquez sur "Définir le nom".

Une nouvelle boîte de dialogue de nom apparaît, saisissez « feuille de calcul » pour le nom et saisissez la formule pour la position de référence :

=GET.WORKBOOK(1)=GET.WORKBOOK(1)

<strong>GET.WORKBOOK</strong>函数是宏表函数，可以提取当前工作簿中的所有工作表名称，宏表函数在单元格中无法直接使用，需要定义名称才可以使用。

在“公式”选项卡-名称管理器中就有了一个定义好的名为“工作表”的名称。

此时在A2单元格输入公式：=INDEX(工作表,ROW(A2))往下拖拉填充公式，就能提取出工作表名称。

公式说明： 使用INDEX函数引用定义名称“工作表”中所有的工作表名称，第二参数用ROW(A2)表示从第二个工作表名称开始提取，因为第一个工作表名称是“目录”，这个工作表名称是我们不需要的。

可以看到用INDEX函数提取出来的工作表名称是带工作簿名称的，所以我们还需要改进一下公式，将工作簿名称换掉，只保留工作表名称。

将A2单元格公式改进为:

=REPLACE(INDEX(工作表,ROW(A2)),1,FIND("]",INDEX(工作表,ROW(A2))),"")

公式说明：用REPLACE函数将工作簿名称替换为空，替换的字符位置为第一个，替换个数用FIND函数查找“]”所在的字符位置，然后替换为空。

最后在B2单元格输入公式：

=HYPERLINK("#"&A2&"!A1",A2)

<strong>GET.WORKBOOK </strong>La fonction est une fonction de table de macro qui peut extraire les noms de toutes les feuilles de calcul du classeur actuel. La fonction de table de macro ne peut pas être utilisée directement dans les cellules et doit définir un. nom avant de pouvoir être utilisé.

Il existe un nom défini appelé "Feuille de travail" dans l'onglet "Formule" - Gestionnaire de noms.

🎜À ce moment, entrez la formule dans la cellule A2 : =INDEX(worksheet, ROW(A2)) Faites glisser vers le bas pour remplir la formule et vous pourrez extraire le nom de la feuille de calcul . 🎜🎜Description de la formule : utilisez la fonction INDEX pour référencer tous les noms de feuille de calcul dans le nom défini « feuille de calcul ». Le deuxième paramètre utilise ROW (A2) pour indiquer que l'extraction commence à partir du nom de la deuxième feuille de calcul, car le nom de la première feuille de calcul est « Répertoire ». , ce nom de feuille de calcul est quelque chose dont nous n'avons pas besoin. 🎜🎜🎜🎜Vous pouvez voir que le nom de la feuille de calcul extrait avec la fonction INDEX a le nom du classeur, nous devons donc améliorer la formule et remplacer le nom du classeur, ne laissant que le nom de la feuille de calcul. 🎜🎜Améliorez la formule dans la cellule A2 pour :🎜🎜=REPLACE(INDEX(worksheet,ROW(A2)),1,FIND("]",INDEX(worksheet,ROW(A2)))," ") 🎜🎜Description de la formule : utilisez la fonction REPLACE pour remplacer le nom du classeur par un espace vide. La position du caractère remplacé est la première. Utilisez la fonction FIND pour rechercher la position du caractère où se trouve "]", puis remplacez-la. avec un espace vide. 🎜🎜 🎜🎜Enfin, saisissez la formule dans la cellule B2 : 🎜🎜=HYPERLINK("#"&A2&"!A1",A2) Faites glisser vers le bas pour remplir la formule. 🎜🎜Explication de la formule : HYPERLINK est une fonction qui peut créer des raccourcis ou des hyperliens. "#" signifie que le nom de la feuille de calcul référencée se trouve dans le classeur actuel, "!A1" signifie un lien vers la cellule A1 de la feuille de calcul correspondante, HYPERLINK No. Le deux paramètres A2 représentent le lien hypertexte nommé d'après le nom de la feuille de calcul. 🎜🎜🎜🎜🎜Le répertoire des feuilles de travail est maintenant complet ! Si une feuille de calcul est ajoutée ou modifiée ultérieurement dans le classeur, il suffit de faire glisser vers le bas pour remplir la formule afin d'extraire automatiquement le nom de la feuille de calcul et de créer automatiquement un lien hypertexte. 🎜

Étant donné que nous utilisons la fonction de tableau de macros, il ne peut pas être enregistré dans un tableau ordinaire. Vous devez sélectionner « Classeur Excel prenant en charge les macros » dans Enregistrer sous, avec le nom de suffixe xlsm ou l'enregistrer sous « Classeur Excel 97-2003 ».

C'est tout pour le tutoriel d'aujourd'hui. Après l'avoir terminé, avez-vous l'impression d'avoir fait de nombreux détours dans la réalisation des formulaires ? Les innombrables nuits où nous avons fait des heures supplémentaires étaient en fait inutiles ~

Recommandations d'apprentissage associées : Tutoriel Excel

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!