PivotTable มีหลายเค้าโครงที่มีโครงสร้างที่กําหนดไว้ล่วงหน้าให้กับรายงาน แต่คุณไม่สามารถกําหนดเค้าโครงเหล่านี้เองได้ ถ้าคุณต้องการความยืดหยุ่นเพิ่มเติมในการออกแบบเค้าโครงของรายงาน PivotTable คุณสามารถแปลงเซลล์เป็นสูตรเวิร์กชีต แล้วเปลี่ยนเค้าโครงของเซลล์เหล่านี้โดยใช้ประโยชน์จากฟีเจอร์ทั้งหมดที่พร้อมใช้งานในเวิร์กชีต คุณสามารถแปลงเซลล์เป็นสูตรที่ใช้ฟังก์ชันคิวบ์หรือใช้ฟังก์ชัน GETPIVOTDATA ก็ได้ การแปลงเซลล์เป็นสูตรทําให้กระบวนการสร้าง อัปเดต และรักษา PivotTable แบบกําหนดเองเหล่านี้ง่ายขึ้นอย่างมาก
เมื่อคุณแปลงเซลล์เป็นสูตร สูตรเหล่านี้จะเข้าถึงข้อมูลเดียวกันกับ PivotTable และสามารถรีเฟรชเพื่อดูผลลัพธ์ที่อัปเดตแล้วได้ อย่างไรก็ตาม ด้วยข้อยกเว้นที่เป็นไปได้ของตัวกรองรายงาน คุณจะไม่สามารถเข้าถึงฟีเจอร์แบบโต้ตอบของ PivotTable เช่น การกรอง การเรียงลําดับ หรือการขยายและการยุบระดับได้อีกต่อไป
หมายเหตุ: เมื่อคุณแปลง PivotTable การประมวลผลการวิเคราะห์แบบออนไลน์ (OLAP) คุณสามารถรีเฟรชข้อมูลเพื่อรับค่าการวัดล่าสุดได้ แต่คุณไม่สามารถอัปเดตสมาชิกจริงที่แสดงในรายงานได้
เรียนรู้เกี่ยวกับสถานการณ์สมมติทั่วไปในการแปลง PivotTable เป็นสูตรของเวิร์กชีต
ต่อไปนี้เป็นตัวอย่างทั่วไปของสิ่งที่คุณสามารถทําได้หลังจากที่คุณแปลงเซลล์ PivotTable เป็นสูตรเวิร์กชีตเพื่อกําหนดเค้าโครงของเซลล์ที่แปลง
จัดเรียงใหม่และลบเซลล์
สมมติว่าคุณมีรายงานประจํางวดที่คุณต้องสร้างแต่ละเดือนสําหรับพนักงานของคุณ คุณต้องการเพียงชุดย่อยของข้อมูลรายงาน และคุณต้องการจัดเค้าโครงข้อมูลด้วยวิธีที่กําหนดเอง คุณสามารถย้ายและจัดเรียงเซลล์ในเค้าโครงการออกแบบที่คุณต้องการ ลบเซลล์ที่ไม่จําเป็นสําหรับรายงานเจ้าหน้าที่รายเดือน แล้วจัดรูปแบบเซลล์และเวิร์กชีตให้เหมาะกับความต้องการของคุณ
แทรกแถวและคอลัมน์
สมมติว่าคุณต้องการแสดงข้อมูลการขายสําหรับสองปีก่อนหน้าที่แบ่งตามภูมิภาคและกลุ่มผลิตภัณฑ์ และคุณต้องการแทรกข้อคิดเห็นแบบขยายในแถวเพิ่มเติม เพียงแทรกแถวแล้วใส่ข้อความ นอกจากนี้ คุณต้องการเพิ่มคอลัมน์ที่แสดงยอดขายตามภูมิภาคและกลุ่มผลิตภัณฑ์ที่ไม่ได้อยู่ใน PivotTable ต้นฉบับ เพียงแทรกคอลัมน์ เพิ่มสูตรเพื่อให้ได้ผลลัพธ์ที่คุณต้องการ แล้วเติมคอลัมน์ลงมาเพื่อให้ได้ผลลัพธ์สําหรับแต่ละแถว
ใช้แหล่งข้อมูลหลายแหล่ง
สมมติว่าคุณต้องการเปรียบเทียบผลลัพธ์ระหว่างฐานข้อมูลการผลิตและฐานข้อมูลทดสอบเพื่อให้แน่ใจว่าฐานข้อมูลทดสอบสร้างผลลัพธ์ที่คาดไว้ คุณสามารถคัดลอกสูตรเซลล์ได้อย่างง่ายดาย แล้วเปลี่ยนอาร์กิวเมนต์การเชื่อมต่อให้ชี้ไปที่ฐานข้อมูลทดสอบเพื่อเปรียบเทียบผลลัพธ์ทั้งสองนี้
ใช้การอ้างอิงเซลล์เพื่อเปลี่ยนการป้อนข้อมูลของผู้ใช้
สมมติว่าคุณต้องการให้ทั้งรายงานเปลี่ยนแปลงตามการป้อนข้อมูลของผู้ใช้ คุณสามารถเปลี่ยนอาร์กิวเมนต์เป็นสูตรคิวบ์เป็นการอ้างอิงเซลล์บนเวิร์กชีต แล้วใส่ค่าที่แตกต่างกันในเซลล์เหล่านั้นเพื่อให้ได้ผลลัพธ์ที่แตกต่างกัน
Create เค้าโครงแถวหรือคอลัมน์ที่ไม่ใช่รูปแบบข้อมูล (หรือเรียกว่าการรายงานแบบไม่สมมาตร)
สมมติว่าคุณจําเป็นต้องสร้างรายงานที่มีคอลัมน์ 2008 ที่เรียกว่า ยอดขายจริง คอลัมน์ 2009 ที่ชื่อว่า ยอดขายที่คาดการณ์ แต่คุณไม่ต้องการให้มีคอลัมน์อื่น คุณสามารถสร้างรายงานที่มีเฉพาะคอลัมน์เหล่านั้น ซึ่งต่างจาก PivotTable ซึ่งต้องใช้การรายงานที่สมมาตร
Create สูตรคิวบ์และนิพจน์ MDX ของคุณเอง
สมมติว่าคุณต้องการสร้างรายงานที่แสดงยอดขายสําหรับผลิตภัณฑ์หนึ่งๆ โดยพนักงานขายเฉพาะสามคนสําหรับเดือนกรกฎาคม ถ้าคุณมีความรู้เกี่ยวกับนิพจน์ MDX และคิวรี OLAP คุณสามารถป้อนสูตรคิวบ์ได้ด้วยตนเอง แม้ว่าสูตรเหล่านี้จะค่อนข้างซับซ้อน แต่คุณสามารถทําให้การสร้างง่ายขึ้นและปรับปรุงความถูกต้องของสูตรเหล่านี้ได้โดยใช้การทําให้สูตรสมบูรณ์อัตโนมัติ สําหรับข้อมูลเพิ่มเติม ให้ดู ใช้การทําให้สูตรสมบูรณ์อัตโนมัติ
หมายเหตุ: คุณสามารถแปลง PivotTable Online Analytical Processing (OLAP) ได้โดยใช้กระบวนงานนี้เท่านั้น
-
เมื่อต้องการบันทึก PivotTable สําหรับใช้ในอนาคต เราขอแนะนําให้คุณทําสําเนาของเวิร์กบุ๊กก่อนที่คุณจะแปลง PivotTable โดยการคลิก ไฟล์ > บันทึกเป็น สําหรับข้อมูลเพิ่มเติม ให้ดู บันทึกไฟล์
-
เตรียม PivotTable เพื่อให้คุณสามารถลดการจัดเรียงเซลล์ใหม่ให้เล็กสุดหลังจากการแปลงโดยทําดังต่อไปนี้:
-
เปลี่ยนเป็นเค้าโครงที่คล้ายกับเค้าโครงที่คุณต้องการมากที่สุด
-
โต้ตอบกับรายงาน เช่น การกรอง การเรียงลําดับ และการออกแบบรายงานใหม่ เพื่อให้ได้ผลลัพธ์ที่คุณต้องการ
-
-
คลิก PivotTable
-
บนแท็บ ตัวเลือก ในกลุ่ม เครื่องมือ ให้คลิก เครื่องมือ OLAP แล้วคลิก แปลงเป็นสูตร
ถ้าไม่มีตัวกรองรายงาน การดําเนินการแปลงจะเสร็จสมบูรณ์ ถ้ามีตัวกรองรายงานอย่างน้อยหนึ่งตัวกรอง กล่องโต้ตอบ แปลงเป็นสูตร จะแสดงขึ้น
-
เลือกวิธีที่คุณต้องการแปลง PivotTable ดังนี้
แปลง PivotTable ทั้งหมด
-
เลือกกล่องกาเครื่องหมาย แปลงตัวกรองรายงาน
ซึ่งจะแปลงเซลล์ทั้งหมดเป็นสูตรเวิร์กชีตและลบ PivotTable ทั้งหมด
แปลงเฉพาะป้ายชื่อแถว PivotTable ป้ายชื่อคอลัมน์ และพื้นที่ค่า แต่เก็บตัวกรองรายงานไว้
-
ตรวจสอบให้แน่ใจว่าไม่ได้เลือกกล่องกาเครื่องหมาย แปลงตัวกรองรายงาน (ซึ่งเป็นค่าเริ่มต้น)
ซึ่งจะแปลงป้ายชื่อแถว ป้ายชื่อคอลัมน์ และเซลล์พื้นที่ค่าทั้งหมดเป็นสูตรของเวิร์กชีต และเก็บ PivotTable ต้นฉบับไว้ แต่มีเฉพาะตัวกรองรายงานเท่านั้นที่คุณจะสามารถกรองต่อไปได้โดยใช้ตัวกรองรายงาน
หมายเหตุ: ถ้ารูปแบบ PivotTable เป็นเวอร์ชัน 2000-2003 หรือเวอร์ชันก่อนหน้า คุณสามารถแปลงได้ทั้ง PivotTable เท่านั้น
-
-
คลิก แปลง
การดําเนินการแปลงจะรีเฟรช PivotTable ก่อนเพื่อให้แน่ใจว่ามีการใช้ข้อมูลล่าสุด
ข้อความจะแสดงขึ้นในแถบสถานะในขณะที่การดําเนินการแปลงเกิดขึ้น ถ้าการดําเนินการใช้เวลานานและคุณต้องการแปลงในเวลาอื่น ให้กด ESC เพื่อยกเลิกการดําเนินการ
หมายเหตุ:
-
คุณไม่สามารถแปลงเซลล์ที่มีตัวกรองที่นําไปใช้กับระดับที่ถูกซ่อนได้
-
คุณไม่สามารถแปลงเซลล์ที่มีการคํานวณแบบกําหนดเองที่สร้างขึ้นผ่านแท็บ แสดงค่าเป็น ของกล่องโต้ตอบ การตั้งค่าเขตข้อมูลค่า ได้ (บนแท็บ ตัวเลือก ในกลุ่ม เขตข้อมูลที่ใช้งานอยู่ ให้คลิก เขตข้อมูลที่ใช้งานอยู่ แล้วคลิก การตั้งค่าเขตข้อมูลค่า)
-
สําหรับเซลล์ที่ถูกแปลง การจัดรูปแบบเซลล์จะถูกรักษาไว้ แต่สไตล์ PivotTable จะถูกเอาออก เนื่องจากสไตล์เหล่านี้สามารถนําไปใช้กับ PivotTable เท่านั้น
-
คุณสามารถใช้ฟังก์ชัน GETPIVOTDATA ในสูตรเพื่อแปลงเซลล์ PivotTable เป็นสูตรเวิร์กชีตเมื่อคุณต้องการทํางานกับแหล่งข้อมูลที่ไม่ใช่ OLAP เมื่อคุณไม่ต้องการอัปเกรดเป็นรูปแบบ PivotTable เวอร์ชัน 2007 ใหม่ทันที หรือเมื่อคุณต้องการหลีกเลี่ยงความซับซ้อนของการใช้ฟังก์ชันคิวบ์
-
ตรวจสอบให้แน่ใจว่าคําสั่ง สร้าง GETPIVOTDATA ในกลุ่ม PivotTable บนแท็บ ตัวเลือก เปิดอยู่
หมายเหตุ: สร้างชุดคําสั่ง GETPIVOTDATA หรือล้างตัวเลือก ใช้ฟังก์ชัน GETPIVOTTABLE สําหรับการอ้างอิง PivotTable ในประเภท สูตร ของส่วน การทํางานกับสูตร ในกล่องโต้ตอบ ตัวเลือกของ Excel
-
ใน PivotTable ตรวจสอบให้แน่ใจว่าเซลล์ที่คุณต้องการใช้ในแต่ละสูตรสามารถมองเห็นได้
-
ในเซลล์เวิร์กชีตภายนอก PivotTable ให้พิมพ์สูตรที่คุณต้องการรวมข้อมูลจากรายงานจนถึงจุดที่คุณต้องการ
-
คลิกเซลล์ใน PivotTable ที่คุณต้องการใช้ในสูตรของคุณใน PivotTable ฟังก์ชันเวิร์กชีต GETPIVOTDATA จะถูกเพิ่มลงในสูตรของคุณที่เรียกใช้ข้อมูลจาก PivotTable ฟังก์ชันนี้ยังคงเรียกใช้ข้อมูลที่ถูกต้องถ้าเค้าโครงรายงานเปลี่ยนแปลง หรือถ้าคุณรีเฟรชข้อมูล
-
พิมพ์สูตรของคุณให้เสร็จ แล้วกด ENTER
หมายเหตุ: ถ้าคุณเอาเซลล์ใดๆ ที่อ้างอิงในสูตร GETPIVOTDATA ออกจากรายงาน สูตรจะส่งกลับ #REF!