ไปยังเนื้อหาหลัก

จะสร้างสารบัญสำหรับชีตทั้งหมดและอัพเดตโดยอัตโนมัติได้อย่างไร?

สมมติว่า คุณมีสมุดงานที่มีแผ่นงานหลายร้อยแผ่น เพื่อนำทางไปยังแผ่นงานเฉพาะจากแผ่นงานหลายแผ่น อาจทำให้พวกเราส่วนใหญ่ปวดหัวได้ ในกรณีนี้ การสร้างสารบัญสำหรับเวิร์กชีตเหล่านี้อาจช่วยให้คุณไปยังแผ่นงานบางแผ่นได้อย่างรวดเร็วและง่ายดาย บทช่วยสอนนี้จะพูดถึงวิธีสร้างสารบัญสำหรับชีตทั้งหมดและอัปเดตตารางโดยอัตโนมัติเมื่อแทรก ลบ หรือเปลี่ยนชื่อชีตด้วย

สร้างสารบัญสำหรับชีตทั้งหมดและอัปเดตอัตโนมัติด้วยสูตร

สร้างสารบัญสำหรับแผ่นงานทั้งหมดและอัปเดตโดยอัตโนมัติด้วยรหัส VBA

สร้างสารบัญสำหรับแผ่นงานทั้งหมดและอัปเดตโดยอัตโนมัติด้วยคุณสมบัติอันทรงพลัง

สร้างสารบัญสำหรับชีตทั้งหมดและอัปเดตอัตโนมัติด้วยสูตร

ใน Excel คุณสามารถสร้างสารบัญโดยกำหนดชื่อช่วงและอ้างอิงชื่อในสูตร โปรดทำตามขั้นตอนต่อไปนี้:

1. แทรกแผ่นงานใหม่ก่อนแผ่นงานทั้งหมดที่คุณต้องการสร้างสารบัญและเปลี่ยนชื่อตามที่คุณต้องการ

2. จากนั้นคลิก สูตร > กำหนดชื่อดูภาพหน้าจอ:

3. ใน ชื่อใหม่ กล่องโต้ตอบ ระบุชื่อ รายการแผ่น เข้าไปใน Name คุณสามารถเปลี่ยนเป็นของคุณเอง แล้วป้อนสูตรนี้: = GET.WORKBOOK (1) & T (ตอนนี้ ()) เข้าไปใน อ้างถึง กล่องข้อความดูภาพหน้าจอ:

4. จากนั้นคลิก OK เพื่อปิดกล่องโต้ตอบ

5. ตอนนี้ โปรดไปที่แผ่นงานใหม่ที่คุณต้องการสร้างสารบัญ จากนั้นป้อนสูตรต่อไปนี้ลงในเซลล์ว่าง:

=IFERROR(INDEX(MID(Sheetlist,FIND("]",Sheetlist)+1,255),ROWS($A$2:A2)),"")
หมายเหตุ: ในสูตรข้างต้น รายการแผ่น คือชื่อช่วงที่คุณสร้างในขั้นตอนที่ 2

6. จากนั้นลากที่จับเติมลงไปที่เซลล์จนกว่าเซลล์ว่างจะแสดงขึ้นและชื่อแผ่นงานทั้งหมด (รวมถึงแผ่นงานที่ซ่อนอยู่) ของสมุดงานปัจจุบันจะแสดงรายการตามภาพหน้าจอด้านล่าง:

7. จากนั้น คุณควรสร้างไฮเปอร์ลิงก์สำหรับเนื้อหาตาราง โปรดใช้สูตรด้านล่าง:

=HYPERLINK("#'"&A2&"'!A1","ไปที่แผ่นงาน")
หมายเหตุ: ในสูตรข้างต้น A2 คือเซลล์ที่มีชื่อแผ่นงานและ A1 คือเซลล์ที่คุณต้องการข้ามไปยังแผ่นงานนั้น ตัวอย่างเช่น หากคุณคลิกข้อความไฮเปอร์ลิงก์ ข้อความนั้นจะระบุตำแหน่งเซลล์ A1 ของแผ่นงานนั้น

8. ตอนนี้ เมื่อคลิกข้อความไฮเปอร์ลิงก์ ข้อความนั้นจะไปที่แผ่นงานนั้นทันที และถ้าคุณแทรกแผ่นงานใหม่ ลบแผ่นงาน หรือเปลี่ยนชื่อแผ่นงาน เนื้อหาของตารางจะได้รับการอัปเดตโดยอัตโนมัติ

หมายเหตุ / รายละเอียดเพิ่มเติม:
  • 1. ด้วยวิธีนี้ แผ่นงานที่ซ่อนอยู่ทั้งหมดจะแสดงในเนื้อหาของตารางด้วย
  • 2. คุณควรบันทึกไฟล์เป็น สมุดงานที่เปิดใช้งานแมโคร Excel ด้วยวิธีนี้ เมื่อคุณเปิดไฟล์นี้ในครั้งถัดไป สูตรจะทำงานได้ดี

สร้างสารบัญสำหรับแผ่นงานทั้งหมดและอัปเดตโดยอัตโนมัติด้วยรหัส VBA

บางครั้ง คุณไม่จำเป็นต้องแสดงแผ่นงานที่ซ่อนอยู่ในเนื้อหาของตาราง เพื่อแก้ปัญหานี้ รหัส VBA ต่อไปนี้สามารถช่วยคุณได้

1. แทรกแผ่นงานใหม่ก่อนแผ่นงานทั้งหมดที่คุณต้องการสร้างสารบัญและเปลี่ยนชื่อตามที่คุณต้องการ จากนั้นให้คลิกขวาที่แท็บแผ่นงานแล้วเลือก ดูรหัส จากเมนูบริบทดูภาพหน้าจอ:

2. ในการเปิด Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง คัดลอกและวางรหัสด้านล่างลงในหน้าต่างรหัสแผ่นงาน:

รหัส 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. ฟอร์มตอนนี้ เมื่อลบ แทรก หรือเปลี่ยนชื่อแผ่นงาน เนื้อหาของตารางจะได้รับการอัปเดตแบบไดนามิก

หมายเหตุ: คุณควรบันทึกไฟล์ Excel เป็น สมุดงานที่เปิดใช้งานแมโคร Excel รูปแบบเพื่อให้รหัสทำงานสำเร็จเมื่อคุณเปิดสมุดงานในครั้งต่อไป

สร้างสารบัญสำหรับแผ่นงานทั้งหมดและอัปเดตโดยอัตโนมัติด้วยคุณสมบัติอันทรงพลัง

ถ้าคุณมี Kutools สำหรับ Excelมัน บานหน้าต่างนำทาง สามารถช่วยให้คุณระบุชื่อแผ่นงานทั้งหมดลงในบานหน้าต่างด้านซ้ายในแนวตั้ง และนำทางไปยังแผ่นงานที่ต้องการได้อย่างรวดเร็วและง่ายดาย

หลังจากการติดตั้ง Kutools สำหรับ Excelโปรดทำตามนี้:

1. คลิก Kutools > การเดินเรือดูภาพหน้าจอ:

2. ในการขยายตัว การเดินเรือ บานหน้าต่างคลิก สมุดงานและแผ่นงาน ไอคอน สมุดงานที่เปิดอยู่ทั้งหมดจะแสดงอยู่ในกล่องรายการด้านบน และแผ่นงานที่มองเห็นได้ทั้งหมดภายในสมุดงานจะแสดงในกล่องรายการด้านล่าง ดูภาพหน้าจอ:

3. และตอนนี้ คุณสามารถไปที่แผ่นงานได้โดยคลิกที่ชื่อแผ่นงานจากบานหน้าต่างด้านซ้าย และเมื่อคุณลบ แทรก หรือเปลี่ยนชื่อแผ่นงาน รายการแผ่นงานในบานหน้าต่างจะได้รับการอัปเดตแบบไดนามิก

เคล็ดลับ: โดยค่าเริ่มต้น แผ่นงานที่ซ่อนอยู่จะไม่แสดงในบานหน้าต่างนำทาง หากคุณต้องการแสดงแผ่นงานที่ซ่อนอยู่ คุณเพียงแค่คลิก สลับเพื่อยกเลิกการซ่อน / ซ่อนแผ่นงานที่ซ่อนอยู่ทั้งหมด ไอคอน ให้คลิกไอคอนนี้อีกครั้งเพื่อซ่อนแผ่นงานที่ซ่อนอยู่ทันที

สุดยอดเครื่องมือเพิ่มผลผลิตในสำนักงาน

🤖 Kutools AI ผู้ช่วย: ปฏิวัติการวิเคราะห์ข้อมูลโดยยึดตาม: การดำเนินการที่ชาญฉลาด   |  สร้างรหัส  |  สร้างสูตรที่กำหนดเอง  |  วิเคราะห์ข้อมูลและสร้างแผนภูมิ  |  เรียกใช้ฟังก์ชัน Kutools...
คุณสมบัติยอดนิยม: ค้นหา เน้น หรือระบุรายการที่ซ้ำกัน   |  ลบแถวว่าง   |  รวมคอลัมน์หรือเซลล์โดยไม่สูญเสียข้อมูล   |   รอบโดยไม่มีสูตร ...
การค้นหาขั้นสูง: VLookup หลายเกณฑ์  |   VLookup หลายค่า  |   VLookup ข้ามหลายแผ่น   |   การค้นหาที่ไม่ชัดเจน ....
รายการแบบเลื่อนลงขั้นสูง: สร้างรายการแบบหล่นลงอย่างรวดเร็ว   |  รายการแบบหล่นลงขึ้นอยู่กับ   |  เลือกหลายรายการแบบหล่นลง ....
ผู้จัดการคอลัมน์: เพิ่มจำนวนคอลัมน์เฉพาะ  |  ย้ายคอลัมน์  |  สลับสถานะการมองเห็นของคอลัมน์ที่ซ่อนอยู่  |  เปรียบเทียบช่วงและคอลัมน์ ...
คุณสมบัติเด่น: กริดโฟกัส   |  มุมมองการออกแบบ   |   บาร์สูตรใหญ่   |  สมุดงานและตัวจัดการชีต   |  ห้องสมุดทรัพยากร (ข้อความอัตโนมัติ)   |  เลือกวันที่   |  รวมแผ่นงาน   |  เข้ารหัส/ถอดรหัสเซลล์   |  ส่งอีเมลตามรายการ   |  ซุปเปอร์ฟิลเตอร์   |   ตัวกรองพิเศษ (กรองตัวหนา/ตัวเอียง/ขีดทับ...) ...
ชุดเครื่องมือ 15 อันดับแรก:  12 ข้อความ เครื่องมือ (เพิ่มข้อความ, ลบอักขระ, ... )   |   50 + แผนภูมิ ประเภท (แผนภูมิ Gantt, ... )   |   40+ ใช้งานได้จริง สูตร (คำนวณอายุตามวันเกิด, ... )   |   19 การแทรก เครื่องมือ (ใส่ QR Code, แทรกรูปภาพจากเส้นทาง, ... )   |   12 การแปลง เครื่องมือ (ตัวเลขเป็นคำ, การแปลงสกุลเงิน, ... )   |   7 ผสานและแยก เครื่องมือ (แถวรวมขั้นสูง, แยกเซลล์, ... )   |   ... และอื่น ๆ

เพิ่มพูนทักษะ Excel ของคุณด้วย Kutools สำหรับ Excel และสัมผัสประสิทธิภาพอย่างที่ไม่เคยมีมาก่อน Kutools สำหรับ Excel เสนอคุณสมบัติขั้นสูงมากกว่า 300 รายการเพื่อเพิ่มประสิทธิภาพและประหยัดเวลา  คลิกที่นี่เพื่อรับคุณสมบัติที่คุณต้องการมากที่สุด...

รายละเอียด

แท็บ Office นำอินเทอร์เฟซแบบแท็บมาที่ Office และทำให้งานของคุณง่ายขึ้นมาก

  • เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
  • เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
  • เพิ่มประสิทธิภาพการทำงานของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations