آموزش ایجاد شیت اتوماتیک در اکسل برای ساخت فهرست پویا

معمولاً در فایل‌های ورد از فهرست مطالب یا Table of Contents استفاده می‌کنیم اما گاهی اوقات همه‌ی اطلاعات حالت آماری دارد و می‌خواهیم فقط از نرم‌افزار Excel برای ارایه کردن اطلاعات استفاده کنیم. در نرم‌افزار Excel قابلیت و گزینه‌ای برای ساخت فهرست مطالب وجود ندارد و چاره‌ای جز ساخت فهرست به روش دستی وجود ندارد ولیکن می‌توانید برای ساخت فهرستی با قابلیت آپدیت شدن خودکار، از قابلیت برنامه‌نویسی اکسل به زبان VBA استفاده کنید.

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

ساخت فهرستی که با تغییر کردن فایل Word و اضافه شدن هدینگ‌های جدید، به صورت خودکار آپدیت شود، ساده است اما در اکسل چنین قابلیتی وجود ندارد. حتی امکان ساخت فهرست بدون قابلیت آپدیت خودکار نیز در Excel پیش‌بینی نشده است. ۲ راهکار برای ساخت فهرست با امکانات موجود در اکسل وجود دارد:‌ روش اول استفاده از فرمول نویسی و قابلیت Power Query است و روش بعدی استفاده از VBA.

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

آموزش ساخت فهرست در اکسل به صورت دستی

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

نام شیت دلخواه است، بهتر است از نامی مناسب نظیر فهرست مطالب یا Table of Contents استفاده کنید.

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

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

فرآیند سفارشی‌سازی ظاهر فهرست شیت‌ها سلیقه‌ای است و به خودتان بستگی دارد. برای لینک دادن به سلول‌ها به طوری که با کلیک روی هر سلول، صفحه مربوطه باز شود، روی هر یک از سلول‌ها راست‌کلیک کنید و گزینه Link را انتخاب کنید.

در پنجره‌ی Insert Hyperlink در ستون کناری Place in This Document را انتخاب کنید و سپس در سمت راست صفحه مربوط به سلول فعلی را انتخاب کنید.

نتیجه چیزی شبیه به تصویر زیر خواهد بود:

آموزش ساخت فهرست پویا در اکسل

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

برای شروع ساخت فهرست پویا در اکسل نیازی به شیت فهرست موضوعات ندارید، چرا که برنامه‌ای که در ادامه معرفی می‌کنیم، فرآیند ایجاد شیت اتوماتیک در اکسل را به صورت خودکار انجام می‌دهد و پس از ایجاد صفحه‌ای به اسم TOC‌، فهرست سایر صفحات فایل اکسل را به صورت خودکار در این شیت ایجاد می‌کند. لذا مستقیماً پنل برنامه‌نویسی اکسل را باز کنید.

در بالای صفحه روی منوی Insert کلیک کنید.

روی Module کلیک کنید تا یک ماژول جدید در ستون کناری اضافه شود.

اکنون می‌توانید برنامه‌ را به زبان ویژوآل بیسیک بنویسید. لذا کدهای زیر را کپی کنید و در ماژولی که اضافه کرده‌اید، پیست کنید:

Option Explicit 
Sub Create_TOC() 
Dim wbBook As Workbook 
Dim wsActive As Worksheet 
Dim wsSheet As Worksheet 
Dim lnRow As Long 
Dim lnPages As Long 
Dim lnCount As Long 
Set wbBook = ActiveWorkbook 
With Application 
    .DisplayAlerts = False 
    .ScreenUpdating = False 
End With 
'If the TOC sheet already exist delete it and add a new 
'worksheet. 
On Error Resume Next 
With wbBook 
    .Worksheets("TOC").Delete 
    .Worksheets.Add Before:=.Worksheets(1) 
End With 
On Error GoTo 0 
Set wsActive = wbBook.ActiveSheet 
With wsActive 
    .Name = "TOC" 
    With .Range("A1:B1") 
        .Value = VBA.Array("Table of Contents", "Sheet # - # of Pages") 
        .Font.Bold = True 
    End With 
End With 
lnRow = 2 
lnCount = 1 
'Iterate through the worksheets in the workbook and create 
'sheetnames, add hyperlink and count & write the running number 
'of pages to be printed for each sheet on the TOC sheet. 
For Each wsSheet In wbBook.Worksheets 
    If wsSheet.Name <> wsActive.Name Then 
        wsSheet.Activate 
        With wsActive 
            .Hyperlinks.Add .Cells(lnRow, 1), "", _ 
            SubAddress:="'" & wsSheet.Name & "'!A1", _ 
            TextToDisplay:=wsSheet.Name 
            lnPages = wsSheet.PageSetup.Pages().Count 
            .Cells(lnRow, 2).Value = "'" & lnCount & "-" & lnPages 
        End With 
        lnRow = lnRow + 1 
        lnCount = lnCount + 1 
    End If 
Next wsSheet 
wsActive.Activate 
wsActive.Columns("A:B").EntireColumn.AutoFit 
With Application 
    .DisplayAlerts = True 
    .ScreenUpdating = True 
End With 
End Sub

دقت کنید که نام شیت فهرست مطالب که TOC است و همین‌طور برخی از توضیحات این شیت در کد فوق قابل تغییر است.

برای اجرا کردن برنامه کلید F5 را فشار دهید. سپس پنجره‌ی ویژوآل بیسیک را ببندید. به این ترتیب فهرست شیت‌ها در صفحه‌ای با عنوان TOC ایجاد می‌شود و حالت لینک شده دارد.

برای بازسازی فهرست پس از تغییر دادن فایل Excel می‌توانید پنل VBA را مجدداً باز کنید و کلید F5 را فشار دهید. به همین سادگی!

guidingtechسیاره‌ی آی‌تی