Note: The other languages of the website are Google-translated. Back to English

จะแสดงรายการตาราง Pivot ทั้งหมดจากสมุดงานได้อย่างไร?

สมมติว่าคุณมีสมุดงานขนาดใหญ่ซึ่งมีตาราง Pivot หลายตารางตอนนี้คุณต้องการแสดงรายการตาราง Pivot ทั้งหมดในสมุดงานนี้เป็นไปได้หรือไม่? แน่นอนรหัส VBA ต่อไปนี้ในบทความนี้จะช่วยคุณได้ หากต้องการทราบรายละเอียดเพิ่มเติมโปรดอ่านบทความด้านล่าง

แสดงรายการตาราง Pivot ทั้งหมดจากสมุดงานด้วยรหัส VBA

แท็บ Office เปิดใช้งานการแก้ไขและเรียกดูแบบแท็บใน Office และทำให้งานของคุณง่ายขึ้นมาก ...
Kutools สำหรับ Excel ช่วยแก้ปัญหาส่วนใหญ่ของคุณและเพิ่มผลผลิตของคุณได้ถึง 80%
  • ใช้ซ้ำอะไรก็ได้: เพิ่มสูตรที่ใช้มากที่สุดหรือซับซ้อนแผนภูมิและสิ่งอื่นใดในรายการโปรดของคุณและนำกลับมาใช้ใหม่ได้อย่างรวดเร็วในอนาคต
  • คุณสมบัติข้อความมากกว่า 20 รายการ: แยกหมายเลขจากสตริงข้อความ แยกหรือลบบางส่วนของข้อความ แปลงตัวเลขและสกุลเงินเป็นคำภาษาอังกฤษ
  • ผสานเครื่องมือ: สมุดงานและแผ่นงานหลายเล่มเป็นหนึ่งเดียว ผสานหลายเซลล์ / แถว / คอลัมน์โดยไม่สูญเสียข้อมูล รวมแถวและผลรวมที่ซ้ำกัน
  • แยกเครื่องมือ: แยกข้อมูลออกเป็นหลายแผ่นตามมูลค่า; สมุดงานหนึ่งเล่มเป็นไฟล์ Excel, PDF หรือ CSV หลายไฟล์ หนึ่งคอลัมน์ถึงหลายคอลัมน์
  • วางการข้าม แถวที่ซ่อน / กรอง; นับและผลรวม โดย Background Color; ส่งอีเมลส่วนบุคคลไปยังผู้รับหลายคนในกลุ่ม
  • ซุปเปอร์กรอง: สร้างโครงร่างตัวกรองขั้นสูงและใช้กับแผ่นงานใด ๆ ประเภท ตามสัปดาห์วันความถี่และอื่น ๆ กรอง by ตัวหนาสูตรคอมเม้น ...
  • คุณสมบัติที่ทรงพลังมากกว่า 300 รายการ ทำงานร่วมกับ Office 2007-2019 และ 365; รองรับทุกภาษา ใช้งานง่ายในองค์กรหรือองค์กรของคุณ

ลูกศรสีฟ้าฟองขวา แสดงรายการตาราง Pivot ทั้งหมดจากสมุดงานด้วยรหัส VBA


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

1. เปิดสมุดงานของคุณที่คุณต้องการแสดงรายการตาราง Pivot ทั้งหมด

2. กด ALT + F11 และจะเปิดไฟล์ หน้าต่าง Microsoft Visual Basic for Applications.

3. คลิก สิ่งที่ใส่เข้าไป > โมดูลและวางรหัสต่อไปนี้ในไฟล์ หน้าต่างโมดูล.

รหัส VBA: แสดงรายการตาราง Pivot ทั้งหมดจากสมุดงาน

Sub ListPivotsInfor()
'Update 20141112
    Dim St As Worksheet
    Dim NewSt As Worksheet
    Dim pt As PivotTable
    Dim I, K As Long
    Application.ScreenUpdating = False
    Set NewSt = Worksheets.Add
    I = 1: K = 2
    With NewSt
        .Cells(I, 1) = "Name"
        .Cells(I, 2) = "Source"
        .Cells(I, 3) = "Refreshed by"
        .Cells(I, 4) = "Refreshed"
        .Cells(I, 5) = "Sheet"
        .Cells(I, 6) = "Location"
        For Each St In ActiveWorkbook.Worksheets
            For Each pt In St.PivotTables
                I = I + 1
                .Cells(I, 1).Value = pt.Name
                .Cells(I, 2).Value = pt.SourceData
                .Cells(I, 3).Value = pt.RefreshName
                .Cells(I, 4).Value = pt.RefreshDate
                .Cells(I, 5).Value = St.Name
                .Cells(I, 6).Value = pt.TableRange1.Address
            Next
        Next
        .Activate
    End With
    Application.ScreenUpdating = True
End Sub

4. จากนั้นกด F5 กุญแจสำคัญในการเรียกใช้รหัสนี้ชื่อตาราง Pivot ช่วงข้อมูลต้นทางชื่อแผ่นงานและแอตทริบิวต์อื่น ๆ ทั้งหมดจะแสดงรายการในแผ่นงานใหม่ซึ่งวางไว้ด้านหน้าแผ่นงานที่ใช้งานอยู่ดังภาพหน้าจอต่อไปนี้:

doc-list-all-pivottable-1


บทความที่เกี่ยวข้อง:

วิธีตรวจสอบว่ามีตาราง Pivot อยู่ในสมุดงานหรือไม่?

จะเพิ่มหลายช่องลงในตาราง Pivot ได้อย่างไร?


เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด

Kutools สำหรับ Excel ช่วยแก้ปัญหาส่วนใหญ่ของคุณและเพิ่มผลผลิตของคุณได้ถึง 80%

  • นำมาใช้ใหม่: ใส่อย่างรวดเร็ว สูตรที่ซับซ้อนแผนภูมิ และสิ่งที่คุณเคยใช้มาก่อน เข้ารหัสเซลล์ ด้วยรหัสผ่าน; สร้างรายชื่อผู้รับจดหมาย และส่งอีเมล ...
  • ซุปเปอร์ฟอร์มูล่าบาร์ (แก้ไขข้อความและสูตรหลายบรรทัดได้อย่างง่ายดาย); การอ่านเค้าโครง (อ่านและแก้ไขเซลล์จำนวนมากได้อย่างง่ายดาย); วางลงในช่วงที่กรองแล้ว...
  • ผสานเซลล์ / แถว / คอลัมน์ โดยไม่สูญเสียข้อมูล แยกเนื้อหาของเซลล์ รวมแถว / คอลัมน์ที่ซ้ำกัน... ป้องกันเซลล์ซ้ำ; เปรียบเทียบช่วง...
  • เลือกซ้ำหรือไม่ซ้ำ แถว; เลือกแถวว่าง (เซลล์ทั้งหมดว่างเปล่า); Super Find และ Fuzzy Find ในสมุดงานจำนวนมาก สุ่มเลือก ...
  • สำเนาถูกต้อง หลายเซลล์โดยไม่เปลี่ยนการอ้างอิงสูตร สร้างการอ้างอิงอัตโนมัติ ถึงหลายแผ่น ใส่สัญลักษณ์แสดงหัวข้อย่อย, กล่องกาเครื่องหมายและอื่น ๆ ...
  • แยกข้อความ, เพิ่มข้อความ, ลบตามตำแหน่ง, ลบ Space; สร้างและพิมพ์ผลรวมย่อยของเพจ แปลงระหว่างเนื้อหาของเซลล์และความคิดเห็น...
  • ซุปเปอร์ฟิลเตอร์ (บันทึกและใช้โครงร่างตัวกรองกับแผ่นงานอื่น ๆ ); การเรียงลำดับขั้นสูง ตามเดือน / สัปดาห์ / วันความถี่และอื่น ๆ ตัวกรองพิเศษ โดยตัวหนาตัวเอียง ...
  • รวมสมุดงานและแผ่นงาน; ผสานตารางตามคอลัมน์สำคัญ แยกข้อมูลออกเป็นหลายแผ่น; Batch แปลง xls, xlsx และ PDF...
  • คุณสมบัติที่ทรงพลังมากกว่า 300 รายการ. รองรับ Office / Excel 2007-2019 และ 365 รองรับทุกภาษา ใช้งานง่ายในองค์กรหรือองค์กรของคุณ ทดลองใช้ฟรี 30 วันเต็ม รับประกันคืนเงิน 60 วัน
kte แท็บ 201905

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

  • เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
  • เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
  • เพิ่มผลผลิตของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!
ด้านล่าง officetab
จัดเรียงความคิดเห็นโดย
ความคิดเห็น (19)
ยังไม่มีการให้คะแนน เป็นคนแรกที่ให้คะแนน!
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
สิ่งที่ดี. อาจรวมข้อมูลโค้ดนี้เพื่อทำให้ชื่อตารางสาระสำคัญเป็นไฮเปอร์ลิงก์ .Cells(I, 1).Parent.Hyperlinks.Add Anchor:=.Cells(I, 1) _ , Address:="" _ , SubAddress:="'" + St.Name + "'!" + Split(pt.TableRange1.Address, ":")(0) _ , TextToDisplay:=pt.Name With .Cells(I, 7).Font .ColorIndex = xlAutomatic .Underline = xlUnderlineStyleNone End With .Cells(I, 7) อักขระ (เริ่มต้น: = 1, ความยาว: = Len (pt.Name)).Font .Underline = xlUnderlineStyleSingle .Color = -4165632 ลงท้ายด้วย
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
ยอดเยี่ยม. ขอบคุณ.
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
นี่เป็นการค้นพบครั้งใหญ่สำหรับฉัน! หวังว่าฉันจะมองหาวิธีแก้ปัญหานี้เมื่อสามวันก่อน มันจะช่วยฉันได้หลายชั่วโมง!! ขอบคุณ!
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
ฉันต้องแก้ไขไฟล์ที่มีตารางสาระสำคัญมากกว่า 60 รายการ เริ่มแรกคลิกทีละอันเพื่อแก้ไข (เช่นเดียวกับกรณีที่มีคน 'ยกเลิกการเชื่อมโยง' พวกเขา แต่ฉันไม่สามารถแก้ไขอันที่ผิดทั้งหมดได้ มันพบอันสุดท้ายในคอลัมน์สองสามคอลัมน์ที่ถูกซ่อนอยู่ที่ต้นฉบับ) โค้ดเด็ด !
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
ฉันได้ลองเรียกใช้ใน excel 2013 แล้วและได้รับข้อผิดพลาดรันไทม์ '1004': ข้อผิดพลาดที่กำหนดโดยแอปพลิเคชันหรือกำหนดวัตถุ หากแสดงความคิดเห็นในบรรทัด .Cells(I, 2).Value = pt.SourceData มันจะทำงานได้ดี คุณช่วยแนะนำได้ไหมว่าฉันต้องทำอย่างไรเพื่อให้ส่วน SourceData ทำงาน ขอบคุณ Ross
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
[quote]ฉันได้ลองเรียกใช้สิ่งนี้ใน excel 2013 และฉันได้รับข้อผิดพลาดรันไทม์ '1004': ข้อผิดพลาดที่กำหนดโดยแอปพลิเคชันหรือกำหนดวัตถุ หากแสดงความคิดเห็นในบรรทัด .Cells(I, 2).Value = pt.SourceData มันจะทำงานได้ดี คุณช่วยแนะนำได้ไหมว่าฉันต้องทำอย่างไรเพื่อให้ส่วน SourceData ทำงาน ขอบคุณ RossBy รอสส์[/quote] ฉันได้รับข้อผิดพลาดเดียวกัน ฉันคิดว่ามันระเบิดเมื่อต้นทางสำหรับตารางสาระสำคัญคือตัวแบบข้อมูล Excel และใช้โดย PowerPivot
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
ขอบคุณสำหรับสิ่งนี้. เป็นโค้ดที่ดีมาก มีประโยชน์มาก ตามที่คนอื่น ๆ ได้โพสต์ ฉันคิดว่ามันไม่ทำงานสำหรับ pivot table ที่มีแหล่งข้อมูลพื้นฐานอิงตามคิวรีข้อมูลสำหรับการเชื่อมต่อเท่านั้น ฉันมีสมุดงานที่มีตารางข้อมูลหนึ่งตาราง ต่อมาฉันสร้างแบบสอบถามข้อมูลสมุดงานที่ได้รับการขัดเกลามากขึ้นตามข้อมูลของตารางนี้ แบบสอบถามเป็นการเชื่อมต่อเท่านั้น (หลีกเลี่ยงการเพิ่มขนาดของเวิร์กบุ๊กโดยไม่จำเป็น) จากนั้นฉันก็สร้างตารางสาระสำคัญที่ใช้ข้อมูลในการสืบค้นสมุดงาน เมื่อฉันรันโค้ด มันใช้งานได้ดีสำหรับตารางสาระสำคัญที่มาจากแหล่งดั้งเดิม แต่พบข้อผิดพลาดรันไทม์เมื่อไปถึงตารางสาระสำคัญตามข้อมูลใน Workbook Queries โดยเฉพาะ; มันให้ข้อผิดพลาดรันไทม์ "1004": ข้อผิดพลาดที่กำหนดโดยแอปพลิเคชันหรือวัตถุที่กำหนด บรรทัดของรหัสที่เกิดข้อผิดพลาดคือ: .cells(I, 2).value = pt.SourceData ขอบคุณสำหรับการให้รหัส และฉันหวังว่าข้อมูลข้างต้นจะช่วยคุณในการปรับแต่ง
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
ทรงพลังมาก. ขอบคุณมาก.
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
สำหรับข้อมูลการเชื่อมต่อเท่านั้นข้อมูลการเชื่อมต่อข้อมูลคือคุณสมบัติของ PivotCache
ฉันมีเวิร์กบุ๊กที่มีตารางเดือยมากกว่า 40 อันที่ผสมผสานระหว่างตาราง Excel และการเชื่อมต่อข้อมูลของเซิร์ฟเวอร์ SQL ฉันใช้รหัสต่อไปนี้เพื่อติดตามพวกเขา


ย่อย GetPivotTableInfo()

Dim wb เป็นสมุดงาน
Dim pvt เป็น PivotTable
Dim wsheet เป็นแผ่นงาน
หรี่พีซีเป็น PivotCache

ตั้งค่า wb = ActiveWorkbook
สำหรับแต่ละแผ่นงานใน wb.Worksheets
สำหรับแต่ละ pvt ใน wsheet.PivotTables
Debug.Print wsheet.Name & ": " & pvt.Name
ตั้งค่าพีซี = wb.PivotCaches(pvt.CacheIndex)
ถ้า pc.SourceType = xlDatabase แล้ว
Debug.Print pc.SourceData
อื่น
ถ้า pc.QueryType = xlOLEDBQuery แล้ว
Debug.Print pc.Connection
Debug.Print pc.SourceConnectionFile
Debug.Print pc.WorkbookConnection.Name
Debug.Print pc.CommandText
End If
End If


pvt . ถัดไป
แผ่นต่อไป
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
ช่วยฉันจากอาการปวดหัวครั้งใหญ่!
ไม่พบตารางสาระสำคัญที่ทำให้เกิด "รีเฟรชทั้งหมดผิดพลาด"
Boom รวมสถานที่แล้ว ขอบคุณมากค่ะ
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
อัศจรรย์!!! ขอบคุณค้าบบบ!!!!
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
นี่มันเจ๋งมาก! ฉันกำลังมองหารหัสแบบนี้ แต่ด้วยการเพิ่มการแสดงฟิลด์ที่ใช้งานอยู่ทั้งหมดต่อตาราง ฉันต้องล้างตารางต้นทางจากฟิลด์ที่ไม่จำเป็น (หนักเกินไป) และพวกมันป้อนหนังสือมากกว่า 300 pivot ฉันไม่อยากจะไปทีละตารางเดือยเพื่อค้นหาว่าฟิลด์ใดที่ฉันสามารถกำจัดได้... หากคุณสามารถแสดงให้ฉันเห็นได้ว่ามันน่าทึ่งมาก... ขอบคุณ!
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
ขอบคุณสำหรับโพสต์ ฉันทำความสะอาดโค้ดเล็กน้อยโดยกำหนด I และ Long และลบการอ้างอิงถึง K เนื่องจากไม่ได้ใช้ สุดท้ายนี้ ฉันเพิ่มบรรทัดด้านล่าง .Activate ของคุณเพื่อแสดง .Columns.AutoFit
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
สะดวกมากที่จะหยิบจับ! ขอขอบคุณ.
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
ที่น่ากลัว.
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
ความช่วยเหลืออย่างมาก ช่วยฉันประหยัดเวลาได้มากในการติดตาม Pivot Table ที่มีชื่อเหมือนกันจำนวนหนึ่งในสมุดงาน!
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
ข้อผิดพลาดรันไทม์ 1004
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
ฟิตกระชับ!! ขอบคุณมาก
ความคิดเห็นนี้ถูกย่อให้เล็กสุดโดยผู้ดำเนินรายการบนเว็บไซต์
โชคไม่ดีที่ฉันได้รับข้อผิดพลาดหลายรายการ ซึ่งจะดีมากสำหรับเวิร์กบุ๊กแบบ multi-tab และ multi-pivot ของฉัน
มีความคิดเห็นยังไม่มีการโพสต์ที่นี่
แสดงความคิดเห็นของคุณ
โพสต์ในฐานะแขก
×
ให้คะแนนโพสต์นี้:
0   ตัวอักษร
สถานที่แนะนำ