كيفية إنشاء فهرس لجميع الأوراق مع تحديث تلقائي؟

لنفترض أن لديك مصنفًا يحتوي على مئات أوراق العمل، التنقل إلى ورقة معينة بين العديد من الأوراق قد يسبب صداعًا لمعظم الناس. في هذه الحالة، إنشاء فهرس لهذه الأوراق قد يساعدك في الانتقال بسرعة وسهولة إلى الورقة المطلوبة. سيرشدك هذا البرنامج التعليمي حول كيفية إنشاء فهرس لجميع الأوراق وتحديث الجدول تلقائيًا عند إدراج أو حذف أو تغيير أسماء الأوراق.

استخدم الصيغة لإنشاء فهرس لجميع الأوراق مع تحديث تلقائي

استخدم Kutools لـ Excel لإنشاء فهرس لجميع الأوراق مع تحديث تلقائي

استخدم كود VBA لإنشاء فهرس لجميع الأوراق مع تحديث تلقائي

استخدم الصيغة لإنشاء فهرس لجميع الأوراق مع تحديث تلقائي

في Excel، يمكنك إنشاء فهرس عن طريق تحديد اسم نطاق واستخدامه في صيغة، يرجى اتباع الخطوات التالية:

1. أدخل ورقة جديدة قبل جميع الأوراق حيث تريد إنشاء الفهرس وأعد تسميتها كما تحتاج.

2. بعد ذلك، انقر فوق "الصيغة" > "تحديد اسم"، انظر إلى لقطة الشاشة:

3. في مربع الحوار "اسم جديد"، حدد اسمًا مثل "Sheetlist" في مربع "الاسم"، يمكنك تغييره إلى ما يناسبك، ثم أدخل الصيغة التالية في مربع النص "يشير إلى":

=GET.WORKBOOK(1)&T(NOW())

4. بعد ذلك، انقر فوق زر "موافق" لإغلاق مربع الحوار.

5. الآن، انتقل إلى الورقة الجديدة حيث تريد إنشاء فهرس، ثم أدخل الصيغة التالية في خلية فارغة:

=IFERROR(INDEX(MID(Sheetlist,FIND("]",Sheetlist)+1,255),ROWS($A$2:A2)),"")

6. وبعد ذلك، اسحب مقبض التعبئة إلى الأسفل حتى تظهر الخلايا الفارغة، وسيتم سرد جميع أسماء الأوراق (بما في ذلك الأوراق المخفية) للمصنف الحالي كما هو موضح في لقطة الشاشة أدناه:

7. بعد ذلك، يجب عليك إنشاء روابط تشعبية لمحتوى الجدول، يرجى استخدام الصيغة التالية:

=HYPERLINK("#'"&A2&"'!A1","Go To Sheet")

8. الآن، عند النقر على نص الرابط التشعبي، سيتم الانتقال إلى تلك الورقة على الفور. وإذا قمت بإدراج ورقة جديدة، أو حذف ورقة، أو تغيير اسم الورقة، سيتم تحديث محتوى الجدول تلقائيًا.

استخدم Kutools لـ Excel لإنشاء فهرس لجميع الأوراق مع تحديث تلقائي

إذا كان لديك "Kutools لـ Excel"، فإن لوحة "التنقل" الخاصة به يمكنها مساعدتك في سرد جميع أسماء الأوراق في اللوحة اليسرى عموديًا والتنقل بسرعة وسهولة إلى الورقة المحددة.

بعد تثبيت Kutools لـ Excel، يرجى القيام بما يلي:

1. انقر فوق "Kutools" > "التنقل"، انظر إلى لقطة الشاشة:

2. في لوحة "التنقل" الموسعة، انقر فوق أيقونة "المصنف والأوراق"، يتم سرد جميع المصنفات المفتوحة في مربع القائمة العلوية، وتُعرض جميع الأوراق المرئية داخل المصنف في مربع القائمة السفلي، انظر إلى لقطة الشاشة:

3. والآن، يمكنك الانتقال إلى الورقة فقط بالنقر فوق اسم الورقة من اللوحة اليسرى. وعندما تقوم بحذف، إدراج، أو إعادة تسمية ورقة، سيتم تحديث قائمة الأوراق في اللوحة ديناميكيًا.

استخدم كود VBA لإنشاء فهرس لجميع الأوراق مع تحديث تلقائي

أحيانًا، ليس من الضروري عرض الأوراق المخفية في محتوى الجدول. لحل هذه المشكلة، يمكن أن يساعدك الكود التالي بلغة VBA.

1. أدخل ورقة جديدة قبل جميع الأوراق حيث تريد إنشاء الفهرس وأعد تسميتها كما تحتاج. بعد ذلك، انقر بزر الماوس الأيمن على علامة تبويب الورقة، واختر "عرض الكود" من قائمة السياق، انظر إلى لقطة الشاشة:

2. في نافذة "Microsoft Visual Basic for Applications" المفتوحة، انسخ والصق الكود أدناه في نافذة كود الورقة:

كود VBA: إنشاء فهرس لجميع الأوراق مع تحديث تلقائي

Private Sub Worksheet_Activate()
'Updateby ExtendOffice
Dim xWsh As Worksheet
Dim xWshs As Worksheets
Dim xShowHinddenWorkSheet As Boolean
Dim xI As Long
Dim xRg As Range
Dim xStrTitle, xStrTCHeader, xStrWShName As String
xShowHinddenWorkSheet = False 'Change this to True to display the hidden sheets as you need
xStrTitle = "A1"
xStrTCHeader = "A3"
On Error Resume Next
Application.ScreenUpdating = False
Me.Cells.Clear
Me.Range(xStrTitle).Font.Bold = True
Me.Range(xStrTitle).Font.Size = Me.Range(xStrTitle).Font.Size + 2
Me.Range(xStrTitle).Value = "Table of Contents"
Me.Range(xStrTCHeader).Value = "No."
Me.Range(xStrTCHeader).Offset(0, 1).Value = "Sheet Name"
Me.Range(xStrTCHeader).Resize(1, 2).Font.Bold = True
xStrWShName = Me.Name
xI = 1
For Each xWsh In Application.ActiveWorkbook.Worksheets
    If xWsh.Name <> xStrWShName Then
        If (xWsh.Visible = xlSheetVisible) Or xShowHinddenWorkSheet Then
            Me.Hyperlinks.Add Anchor:=Me.Range(xStrTCHeader).Offset(xI, 1), Address:="", SubAddress:="'" & xWsh.Name & "'!A1", TextToDisplay:=xWsh.Name
            Me.Range(xStrTCHeader).Offset(xI).Value = xI
            xI = xI + 1
        End If
    End If
Next
Application.ScreenUpdating = True
End Sub

3. بعد ذلك، اضغط على مفتاح "F5" لتشغيل هذا الكود، وسيتم إنشاء محتوى الجدول على الفور في الورقة الجديدة، لن يتم عرض جميع الأوراق المخفية في محتوى الجدول، انظر إلى لقطة الشاشة:

4. من الآن فصاعدًا، عند حذف، إدراج، أو إعادة تسمية الورقة، سيتم تحديث محتوى الجدول ديناميكيًا.