การแปลงเซลล์ PivotTable เป็นสูตรของแผ่นงาน

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

สมมติว่าคุณต้องการสร้างรายงานที่แสดงยอดขายของผลิตภัณฑ์หนึ่งๆ โดยพนักงานขายที่ระบุสามคนสําหรับเดือนกรกฎาคม ถ้าคุณมีความรู้เกี่ยวกับนิพจน์ MDX และคิวรี 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

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

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

   หมายเหตุ

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

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

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

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

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

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