Applies ToExcel for Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

PivotTable มีหลายเค้าโครงที่มีโครงสร้างที่กําหนดไว้ล่วงหน้าให้กับรายงาน แต่คุณไม่สามารถกําหนดเค้าโครงเหล่านี้เองได้ ถ้าคุณต้องการความยืดหยุ่นเพิ่มเติมในการออกแบบเค้าโครงของรายงาน PivotTable คุณสามารถแปลงเซลล์เป็นสูตรเวิร์กชีต แล้วเปลี่ยนเค้าโครงของเซลล์เหล่านี้โดยใช้ประโยชน์จากฟีเจอร์ทั้งหมดที่พร้อมใช้งานในเวิร์กชีต คุณสามารถแปลงเซลล์เป็นสูตรที่ใช้ฟังก์ชันคิวบ์หรือใช้ฟังก์ชัน GETPIVOTDATA ก็ได้ การแปลงเซลล์เป็นสูตรทําให้กระบวนการสร้าง อัปเดต และรักษา PivotTable แบบกําหนดเองเหล่านี้ง่ายขึ้นอย่างมาก

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

หมายเหตุ:  เมื่อคุณแปลง PivotTable การประมวลผลการวิเคราะห์แบบออนไลน์ (OLAP) คุณสามารถรีเฟรชข้อมูลเพื่อรับค่าการวัดล่าสุดได้ แต่คุณไม่สามารถอัปเดตสมาชิกจริงที่แสดงในรายงานได้

เรียนรู้เกี่ยวกับสถานการณ์สมมติทั่วไปในการแปลง PivotTable เป็นสูตรของเวิร์กชีต

ต่อไปนี้เป็นตัวอย่างทั่วไปของสิ่งที่คุณสามารถทําได้หลังจากที่คุณแปลงเซลล์ PivotTable เป็นสูตรเวิร์กชีตเพื่อกําหนดเค้าโครงของเซลล์ที่แปลง

จัดเรียงใหม่และลบเซลล์    

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

แทรกแถวและคอลัมน์    

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

ใช้แหล่งข้อมูลหลายแหล่ง    

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

ใช้การอ้างอิงเซลล์เพื่อเปลี่ยนการป้อนข้อมูลของผู้ใช้    

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

Create เค้าโครงแถวหรือคอลัมน์ที่ไม่ใช่รูปแบบข้อมูล (หรือเรียกว่าการรายงานแบบไม่สมมาตร)    

สมมติว่าคุณจําเป็นต้องสร้างรายงานที่มีคอลัมน์ 2008 ที่เรียกว่า ยอดขายจริง คอลัมน์ 2009 ที่ชื่อว่า ยอดขายที่คาดการณ์ แต่คุณไม่ต้องการให้มีคอลัมน์อื่น คุณสามารถสร้างรายงานที่มีเฉพาะคอลัมน์เหล่านั้น ซึ่งต่างจาก PivotTable ซึ่งต้องใช้การรายงานที่สมมาตร

Create สูตรคิวบ์และนิพจน์ MDX ของคุณเอง    

สมมติว่าคุณต้องการสร้างรายงานที่แสดงยอดขายสําหรับผลิตภัณฑ์หนึ่งๆ โดยพนักงานขายเฉพาะสามคนสําหรับเดือนกรกฎาคม ถ้าคุณมีความรู้เกี่ยวกับนิพจน์ MDX และคิวรี OLAP คุณสามารถป้อนสูตรคิวบ์ได้ด้วยตนเอง แม้ว่าสูตรเหล่านี้จะค่อนข้างซับซ้อน แต่คุณสามารถทําให้การสร้างง่ายขึ้นและปรับปรุงความถูกต้องของสูตรเหล่านี้ได้โดยใช้การทําให้สูตรสมบูรณ์อัตโนมัติ สําหรับข้อมูลเพิ่มเติม ให้ดู ใช้การทําให้สูตรสมบูรณ์อัตโนมัติ

หมายเหตุ: คุณสามารถแปลง PivotTable Online Analytical Processing (OLAP) ได้โดยใช้กระบวนงานนี้เท่านั้น

  1. เมื่อต้องการบันทึก PivotTable สําหรับใช้ในอนาคต เราขอแนะนําให้คุณทําสําเนาของเวิร์กบุ๊กก่อนที่คุณจะแปลง PivotTable โดยการคลิก ไฟล์ > บันทึกเป็น สําหรับข้อมูลเพิ่มเติม ให้ดู บันทึกไฟล์

  2. เตรียม PivotTable เพื่อให้คุณสามารถลดการจัดเรียงเซลล์ใหม่ให้เล็กสุดหลังจากการแปลงโดยทําดังต่อไปนี้:

    • เปลี่ยนเป็นเค้าโครงที่คล้ายกับเค้าโครงที่คุณต้องการมากที่สุด

    • โต้ตอบกับรายงาน เช่น การกรอง การเรียงลําดับ และการออกแบบรายงานใหม่ เพื่อให้ได้ผลลัพธ์ที่คุณต้องการ

  3. คลิก PivotTable

  4. บนแท็บ ตัวเลือก ในกลุ่ม เครื่องมือ ให้คลิก เครื่องมือ OLAP แล้วคลิก แปลงเป็นสูตร

    ถ้าไม่มีตัวกรองรายงาน การดําเนินการแปลงจะเสร็จสมบูรณ์ ถ้ามีตัวกรองรายงานอย่างน้อยหนึ่งตัวกรอง กล่องโต้ตอบ แปลงเป็นสูตร จะแสดงขึ้น

  5. เลือกวิธีที่คุณต้องการแปลง PivotTable ดังนี้

    แปลง PivotTable ทั้งหมด    

    • เลือกกล่องกาเครื่องหมาย แปลงตัวกรองรายงาน

      ซึ่งจะแปลงเซลล์ทั้งหมดเป็นสูตรเวิร์กชีตและลบ PivotTable ทั้งหมด

      แปลงเฉพาะป้ายชื่อแถว PivotTable ป้ายชื่อคอลัมน์ และพื้นที่ค่า แต่เก็บตัวกรองรายงานไว้    

    • ตรวจสอบให้แน่ใจว่าไม่ได้เลือกกล่องกาเครื่องหมาย แปลงตัวกรองรายงาน (ซึ่งเป็นค่าเริ่มต้น)

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

      หมายเหตุ:  ถ้ารูปแบบ PivotTable เป็นเวอร์ชัน 2000-2003 หรือเวอร์ชันก่อนหน้า คุณสามารถแปลงได้ทั้ง PivotTable เท่านั้น

  6. คลิก แปลง

    การดําเนินการแปลงจะรีเฟรช PivotTable ก่อนเพื่อให้แน่ใจว่ามีการใช้ข้อมูลล่าสุด

    ข้อความจะแสดงขึ้นในแถบสถานะในขณะที่การดําเนินการแปลงเกิดขึ้น ถ้าการดําเนินการใช้เวลานานและคุณต้องการแปลงในเวลาอื่น ให้กด ESC เพื่อยกเลิกการดําเนินการ

    หมายเหตุ: 

    • คุณไม่สามารถแปลงเซลล์ที่มีตัวกรองที่นําไปใช้กับระดับที่ถูกซ่อนได้

    • คุณไม่สามารถแปลงเซลล์ที่มีการคํานวณแบบกําหนดเองที่สร้างขึ้นผ่านแท็บ แสดงค่าเป็น ของกล่องโต้ตอบ การตั้งค่าเขตข้อมูลค่า ได้ (บนแท็บ ตัวเลือก ในกลุ่ม เขตข้อมูลที่ใช้งานอยู่ ให้คลิก เขตข้อมูลที่ใช้งานอยู่ แล้วคลิก การตั้งค่าเขตข้อมูลค่า)

    • สําหรับเซลล์ที่ถูกแปลง การจัดรูปแบบเซลล์จะถูกรักษาไว้ แต่สไตล์ PivotTable จะถูกเอาออก เนื่องจากสไตล์เหล่านี้สามารถนําไปใช้กับ PivotTable เท่านั้น

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

  1. ตรวจสอบให้แน่ใจว่าคําสั่ง สร้าง GETPIVOTDATA ในกลุ่ม PivotTable บนแท็บ ตัวเลือก เปิดอยู่

    หมายเหตุ: สร้างชุดคําสั่ง GETPIVOTDATA หรือล้างตัวเลือก ใช้ฟังก์ชัน GETPIVOTTABLE สําหรับการอ้างอิง PivotTable ในประเภท สูตร ของส่วน การทํางานกับสูตร ในกล่องโต้ตอบ ตัวเลือกของ Excel

  2. ใน PivotTable ตรวจสอบให้แน่ใจว่าเซลล์ที่คุณต้องการใช้ในแต่ละสูตรสามารถมองเห็นได้

  3. ในเซลล์เวิร์กชีตภายนอก PivotTable ให้พิมพ์สูตรที่คุณต้องการรวมข้อมูลจากรายงานจนถึงจุดที่คุณต้องการ

  4. คลิกเซลล์ใน PivotTable ที่คุณต้องการใช้ในสูตรของคุณใน PivotTable ฟังก์ชันเวิร์กชีต GETPIVOTDATA จะถูกเพิ่มลงในสูตรของคุณที่เรียกใช้ข้อมูลจาก PivotTable ฟังก์ชันนี้ยังคงเรียกใช้ข้อมูลที่ถูกต้องถ้าเค้าโครงรายงานเปลี่ยนแปลง หรือถ้าคุณรีเฟรชข้อมูล

  5. พิมพ์สูตรของคุณให้เสร็จ แล้วกด ENTER

หมายเหตุ: ถ้าคุณเอาเซลล์ใดๆ ที่อ้างอิงในสูตร GETPIVOTDATA ออกจากรายงาน สูตรจะส่งกลับ #REF!

ปัญหา: ไม่สามารถแปลงเซลล์ PivotTable เป็นสูตรของแผ่นงาน

ต้องการความช่วยเหลือเพิ่มเติมหรือไม่

ต้องการตัวเลือกเพิ่มเติมหรือไม่

สํารวจสิทธิประโยชน์ของการสมัครใช้งาน เรียกดูหลักสูตรการฝึกอบรม เรียนรู้วิธีการรักษาความปลอดภัยอุปกรณ์ของคุณ และอื่นๆ

ชุมชนช่วยให้คุณถามและตอบคําถาม ให้คําติชม และรับฟังจากผู้เชี่ยวชาญที่มีความรู้มากมาย