چگونه یک فهرست محتوای پویا در اکسل ایجاد کنیم؟

به گزارش اپ خونه، فهرست محتوای پویا در اکسل (Dynamic Table of Contents) یک صفحه خاص در یک فایل اکسل است که لیستی از تمام شیت‌های (برگه‌های) موجود در آن فایل را نمایش می‌دهد. این فهرست به‌طور خودکار به‌روز شده و دارای لینک‌های قابل‌کلیک است که به شما اجازه می‌دهد به‌سرعت بین صفحات مختلف جابه‌جا شوید.

فهرست محتوا هنگام کار با فایل‌های بزرگ در اکسل، یک تغییر اساسی ایجاد می‌کند – این ویژگی به شما کمک می‌کند که همه‌چیز را مرتب و قابل‌دسترسی نگه دارید. اما برخلاف Word، در اکسل خبری از یک دکمه ساده برای ایجاد و به‌روزرسانی خودکار فهرست محتوا نیست.

بنابراین، باید خودتان دست‌به‌کار شوید و یک فهرست پویا بسازید. این فهرست به‌طور خودکار به‌روزرسانی می‌شود و دارای لینک‌های قابل‌کلیک است، بنابراین می‌توانید به‌راحتی بین صفحات مختلف جابه‌جا شوید. این راهنما، تمام اطلاعات لازم برای ایجاد یک فهرست محتوای پویا در اکسل را در اختیار شما قرار می‌دهد.

چگونه یک فهرست محتوای پویا در اکسل ایجاد کنیم؟

به‌طور کلی، سه روش برای ایجاد یک فهرست محتوای پویا در اکسل وجود دارد. اما فقط یک روش به شما یک فهرست کاملاً خودکار ارائه می‌دهد، و آن هم استفاده از VBA (Visual Basic for Applications) است که زبان برنامه‌نویسی اختصاصی مایکروسافت محسوب می‌شود.

دو روش دیگر – یعنی استفاده از فرمول‌های معمولی و Power Query – به شما یک فهرست محتوای نیمه‌پویا ارائه می‌دهند. این بدان معناست که یا لینک‌های قابل‌کلیک نخواهید داشت، یا فهرست به‌طور خودکار به‌روزرسانی نمی‌شود. از آنجایی که هدف ما ایجاد یک فهرست کاملاً پویا است، از VBA استفاده خواهیم کرد.

اگر در کار با VBA مهارت زیادی ندارید، نگران نباشید – کافی است مراحل زیر را دنبال کنید. اما قبل از هر چیز، ابتدا باید یک صفحه مخصوص فهرست محتوا ایجاد کنیم.

مرحله۱. روی دکمه “Insert Worksheet” در کنار صفحات موجود در پایین اکسل کلیک کنید.

مرحله۲. نام این صفحه را “Table of Contents” بگذارید.

مرحله۳. این صفحه را به اولین موقعیت در بین صفحات منتقل کنید تا دسترسی به آن راحت‌تر باشد.

مرحله۴. نام تمامی صفحات موجود را در ستون A این صفحه وارد کنید.

اکنون فهرست محتوای شما ایجاد شد. حالا می‌توانید ظاهر آن را تغییر دهید، اما مهم‌تر از آن، باید آن را پویا کنیم. برای این کار، نیاز به کمک ویرایشگر VBA داریم – یک ابزار داخلی در اکسل که به شما امکان نوشتن و اجرای کدهای سفارشی را می‌دهد.

برای افزودن کد VBA برای پویا کردن فهرست مراحل زیر را دنبال کنید:

مرحله۱. کلیدهای Alt + F11 را فشار دهید تا ویرایشگر VBA باز شود.

مرحله۲. از منوی بالا، به تب Insert بروید.

مرحله۳. گزینه Module را انتخاب کنید.

مرحله۴. کد VBA زیر را کپی کرده و در ماژول ایجاد شده جای‌گذاری کنید:

Sub CreateTOC()
Dim ws As Worksheet
Dim toc As Worksheet
Dim i As Integer

 بررسی وجود صفحه TOC و حذف آن در صورت موجود بودن
On Error Resume Next
Set toc = ThisWorkbook.Sheets(“Table of Contents”)
On Error GoTo 0

If Not toc Is Nothing Then Application.DisplayAlerts = False: toc.Delete: Application.DisplayAlerts = True

ایجاد صفحه جدید TOC
Set toc = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(1))
toc.Name = “Table of Contents”

تنظیم عنوان فهرست
toc.Cells(1, 1).Value = “Table of Contents”
toc.Cells(1, 1).Font.Bold = True
toc.Cells(1, 1).Font.Size = 14

ایجاد لینک‌های قابل‌کلیک به صفحات مختلف
i = 2
For Each ws In ThisWorkbook.Sheets
If ws.Name <> “Table of Contents” Then
toc.Hyperlinks.Add Anchor:=toc.Cells(i, 1), _
Address:=””, _
SubAddress:=”‘” & ws.Name & “‘!A1”, _
TextToDisplay:=ws.Name
i = i + 1
End If
Next ws

تنظیم عرض ستون‌ها برای نمایش بهتر
toc.Columns(“A”).AutoFit
End Sub

مرحله۵. کلید F5 را فشار دهید تا کد اجرا شود.
مرحله۶. از ویرایشگر VBA خارج شوید.

حالا متوجه می‌شوید که فهرست محتوای شما قابل‌کلیک شده است.

برای اینکه فهرست محتوا پس از ایجاد صفحات جدید یا حذف صفحات قدیمی به‌طور خودکار به‌روزرسانی شود، کافی است مراحل ۱ تا ۶ بالا را دوباره اجرا کنید. با این کار، صفحات جدید به فهرست اضافه شده و صفحات حذف‌شده، از لیست حذف می‌شوند.

بنابراین اکسل به‌طور پیش‌فرض دارای گزینه‌ای برای ایجاد فهرست محتوا نیست، اما با استفاده از VBA، می‌توان یک فهرست پویا و قابل‌کلیک ایجاد کرد که همیشه به‌روز بماند. این ویژگی به شما کمک می‌کند که فایل‌های بزرگ را سریع‌تر و راحت‌تر مدیریت کنید.

اگر قصد دارید مرتباً تغییرات جدید را در فهرست محتوا اعمال کنید، کافی است کد را دوباره اجرا کنید. همچنین می‌توانید ظاهر فهرست را شخصی‌سازی کنید، مانند تغییر رنگ، فونت و اندازه متن‌ها.

حالا که می‌دانید چطور یک فهرست محتوای پویا در اکسل ایجاد کنید، از آن برای سازماندهی بهتر و افزایش بهره‌وری در کارهای خود استفاده کنید.