โดยปกติ เมื่อคุณสร้างตารางสาระสำคัญแล้ว คุณต้องขยายการวิเคราะห์และรวมข้อมูล/การคำนวณเพิ่มเติมเข้าไป
หากคุณต้องการจุดข้อมูลใหม่ที่สามารถรับได้โดยใช้จุดข้อมูลที่มีอยู่ในตารางสาระสำคัญ ไม่จำเป็นต้องย้อนกลับและเพิ่มลงในข้อมูลต้นทางคุณสามารถใช้ .แทนได้เขตข้อมูลจากการคำนวณ PivotTableเพื่อทำสิ่งนี้.
เนื้อหา
เขตข้อมูลจากการคำนวณ PivotTable คืออะไร
เริ่มต้นด้วยตัวอย่างพื้นฐานของตารางสาระสำคัญ
สมมติว่าคุณมีชุดข้อมูลของผู้ค้าปลีกและคุณสร้างตารางสาระสำคัญดังนี้:
ตารางสาระสำคัญด้านบนสรุปมูลค่าการขายและกำไรของผู้ค้าปลีก
ทีนี้ ถ้าคุณอยากรู้ว่าอัตรากำไรของผู้ค้าปลีกเหล่านั้นเป็นอย่างไร (อัตรากำไรคือ "กำไร" หารด้วย "ยอดขาย")
มีหลายวิธีในการทำเช่นนี้:
- กลับไปที่ชุดข้อมูลเดิมและเพิ่มจุดข้อมูลใหม่นี้ดังนั้น คุณจึงสามารถแทรกคอลัมน์ใหม่ในข้อมูลต้นทางและคำนวณส่วนต่างกำไรในนั้นได้เมื่อเสร็จแล้ว คุณต้องอัปเดตข้อมูลต้นทางของตารางสรุปข้อมูลเพื่อรับคอลัมน์ใหม่นี้เป็นส่วนหนึ่งของคอลัมน์
- ในขณะที่วิธีนี้ใช้ได้ผล คุณจะต้องกลับไปที่ชุดข้อมูลและทำการคำนวณด้วยตนเองตัวอย่างเช่น คุณอาจต้องการเพิ่มคอลัมน์อื่นเพื่อคำนวณยอดขายเฉลี่ยต่อหน่วย (ยอดขาย/ปริมาณ)อีกครั้ง คุณต้องเพิ่มคอลัมน์นี้ลงในข้อมูลต้นทางแล้วอัปเดตตารางสาระสำคัญ
- วิธีนี้ยังขยายตารางสาระสำคัญของคุณเมื่อคุณเพิ่มข้อมูลใหม่เข้าไป
- เพิ่มการคำนวณนอกตารางสาระสำคัญเลือกตัวเลือกนี้หากโครงสร้างตารางสาระสำคัญของคุณไม่น่าจะเปลี่ยนแปลงอย่างไรก็ตาม หากคุณเปลี่ยนตารางสาระสำคัญ การคำนวณอาจไม่อัปเดตตามนั้น และอาจให้ผลลัพธ์หรือข้อผิดพลาดที่ไม่ถูกต้องดังที่แสดงด้านล่าง ฉันคำนวณส่วนต่างกำไรเมื่อมีผู้ค้าปลีกอยู่ในแถวแต่เมื่อเปลี่ยนจากลูกค้าเป็นพื้นที่ สูตรผิดพลาด
- คำนวณฟิลด์โดยใช้ตารางเดือยนี่คือใช้ข้อมูล PivotTable ที่มีอยู่แล้วคำนวณวิธีที่มีประสิทธิภาพมากที่สุดคิดว่าเขตข้อมูลจากการคำนวณเป็นคอลัมน์เสมือนที่คุณเพิ่มโดยใช้คอลัมน์ที่มีอยู่ในตารางสรุปข้อมูลของคุณการใช้เขตข้อมูลจากการคำนวณของตารางเดือยมีประโยชน์มากมาย (ดังที่เราจะเห็นในภายหลัง):
- คุณไม่จำเป็นต้องทำงานกับสูตรหรืออัปเดตข้อมูลต้นฉบับ
- ขยายได้เนื่องจากจะพิจารณาข้อมูลใหม่ใดๆ ที่คุณอาจเพิ่มลงในตารางสรุปข้อมูลโดยอัตโนมัติหลังจากเพิ่มช่องจากการคำนวณแล้ว คุณจะใช้ได้เหมือนกับช่องอื่นๆ ในตาราง Pivot
- ง่ายต่อการอัปเดตและจัดการตัวอย่างเช่น หากตัววัดเปลี่ยนแปลงหรือคุณจำเป็นต้องเปลี่ยนการคำนวณ คุณสามารถทำได้จากตาราง Pivot เอง
เพิ่มเขตข้อมูลจากการคำนวณลงในตารางสาระสำคัญ
มาดูวิธีการเพิ่มเขตข้อมูลจากการคำนวณตารางสาระสำคัญไปยังตารางสาระสำคัญที่มีอยู่
สมมติว่าคุณมีตารางสาระสำคัญที่มีลักษณะดังนี้ และคุณต้องการคำนวณส่วนต่างกำไรสำหรับผู้ค้าปลีกแต่ละราย:
ต่อไปนี้เป็นขั้นตอนในการเพิ่มเขตข้อมูลจากการคำนวณของ PivotTable:
หลังจากเพิ่มฟิลด์จากการคำนวณ ฟิลด์นั้นจะปรากฏเป็นหนึ่งในฟิลด์ในรายการ ฟิลด์ PivotTable
ขณะนี้ คุณสามารถใช้เขตข้อมูลจากการคำนวณนี้เป็นเขตข้อมูลตารางสาระสำคัญอื่นๆ ได้แล้ว (โปรดทราบว่าคุณไม่สามารถใช้เขตข้อมูลจากการคำนวณของตารางสาระสำคัญเป็นตัวกรองรายงานหรือตัวแบ่งส่วนข้อมูล)
อย่างที่ได้กล่าวไปแล้ว ประโยชน์ของการใช้ฟิลด์คำนวณของตารางเดือยคือคุณสามารถเปลี่ยนโครงสร้างของตารางสาระสำคัญได้และมันจะปรับโดยอัตโนมัติ
ตัวอย่างเช่น ถ้าฉันลากและวางขอบเขตลงในพื้นที่แถว คุณจะได้ผลลัพธ์ดังต่อไปนี้ โดยจะรายงานค่าส่วนต่างกำไรสำหรับผู้ค้าปลีกและภูมิภาค
ในตัวอย่างข้างต้น ฉันใช้สูตรง่ายๆ (=กำไร/ยอดขาย) เพื่อแทรกฟิลด์ที่คำนวณได้อย่างไรก็ตาม คุณสามารถใช้สูตรขั้นสูงบางอย่างได้เช่นกัน
ก่อนที่ฉันจะแสดงตัวอย่างการใช้สูตรขั้นสูงเพื่อสร้างเขตข้อมูลจากการคำนวณของ PivotTable ต่อไปนี้คือสิ่งที่คุณต้องรู้:
- คุณไม่สามารถใช้การอ้างอิงหรือช่วงที่มีชื่อเมื่อสร้างฟิลด์จากการคำนวณของ PivotTableซึ่งจะไม่รวมหลายสูตร เช่น VLOOKUP, INDEX, OFFSET เป็นต้นอย่างไรก็ตาม คุณสามารถใช้สูตรที่ทำงานโดยไม่มีการอ้างอิงได้ (เช่น SUM, IF, COUNT ฯลฯ...)
- คุณสามารถใช้ค่าคงที่ในสูตรได้ตัวอย่างเช่น หากคุณต้องการทราบยอดขายที่คาดการณ์ไว้ซึ่งคาดว่าจะเติบโต 10% คุณจะต้องใช้สูตร =Sales*1.1 (โดยที่ 1.1 เป็นค่าคงที่)
- ทำตามลำดับความสำคัญในสูตรที่สร้างฟิลด์จากการคำนวณตามแนวทางปฏิบัติที่ดีที่สุด ให้ใช้วงเล็บเพื่อให้แน่ใจว่าคุณไม่จำเป็นต้องจำลำดับความสำคัญ
ตอนนี้ มาดูตัวอย่างการสร้างเขตข้อมูลจากการคำนวณโดยใช้สูตรขั้นสูงกัน
สมมติว่าคุณมีชุดข้อมูลที่แสดงด้านล่าง และคุณจำเป็นต้องแสดงยอดขายที่คาดการณ์ไว้ในตารางสรุปข้อมูล
สำหรับมูลค่าคาดการณ์ คุณจะใช้ยอดขายที่เพิ่มขึ้น 300% สำหรับผู้ค้าปลีกรายใหญ่ (ยอดขายมากกว่า 5 ล้านรายการ) และยอดขายเพิ่มขึ้น 300% สำหรับผู้ค้าปลีกรายย่อยและขนาดกลาง (ยอดขายน้อยกว่า 10 ล้านรายการ)
หมายเหตุ: ตัวเลขยอดขายที่นี่เป็นของปลอมและถูกนำมาใช้เพื่อแสดงตัวอย่างในบทช่วยสอนนี้
นี่คือวิธีการ:
สิ่งนี้จะเพิ่มคอลัมน์ใหม่ให้กับ PivotTable พร้อมค่าพยากรณ์การขาย
ปัญหาเขตข้อมูลจากการคำนวณตารางเดือย
เขตข้อมูลจากการคำนวณเป็นคุณลักษณะที่น่าทึ่งที่เพิ่มมูลค่าของตารางสาระสำคัญด้วยการคำนวณเขตข้อมูล ในขณะที่ยังคงรักษาทุกอย่างที่สามารถปรับขนาดและจัดการได้
อย่างไรก็ตาม มีปัญหากับเขตข้อมูลจากการคำนวณ PivotTable ที่คุณต้องเข้าใจก่อนใช้งาน
สมมติว่า ฉันมีตารางสาระสำคัญดังนี้ และฉันใช้เขตข้อมูลจากการคำนวณเพื่อรับตัวเลขยอดขายที่คาดการณ์ไว้
โปรดทราบว่าผลรวมย่อยและยอดรวมไม่ถูกต้อง
แม้ว่าค่าเหล่านี้ควรเพิ่มมูลค่าการคาดการณ์การขายแยกต่างหากสำหรับผู้ค้าปลีกแต่ละราย แต่ในความเป็นจริง ค่าเหล่านี้เป็นไปตามสูตรฟิลด์ที่คำนวณได้แบบเดียวกับที่เราสร้างขึ้น
ดังนั้นสำหรับ South Total ในขณะที่ค่าควรเป็น 22,824,000 South Total รายงานว่าไม่ถูกต้องเป็น 22,287,000สิ่งนี้เกิดขึ้นเพราะใช้สูตร 21,225,800*1.05 เพื่อให้ได้ค่า
ขออภัย คุณไม่สามารถแก้ไขปัญหานี้ได้
วิธีที่ดีที่สุดในการจัดการนี้คือการลบผลรวมย่อยและผลรวมทั้งหมดออกจากตารางสาระสำคัญ
คุณยังสามารถผ่าน นำเสนอโดยเดบร้าวิธีแก้ปัญหาที่เป็นนวัตกรรมใหม่เพื่อจัดการกับปัญหานี้
จะแก้ไขหรือลบฟิลด์คำนวณของตารางสาระสำคัญได้อย่างไร
หลังจากที่คุณสร้างฟิลด์จากการคำนวณของ PivotTable คุณสามารถแก้ไขหรือลบสูตรโดยใช้ขั้นตอนต่อไปนี้:
จะรับรายการสูตรเขตข้อมูลจากการคำนวณทั้งหมดได้อย่างไร
ถ้าคุณสร้างเขตข้อมูลจากการคำนวณ PivotTable จำนวนมาก คุณไม่ต้องกังวลกับการติดตามสูตรที่ใช้ในแต่ละเขตข้อมูล
Excel ช่วยให้คุณสร้างรายการสูตรทั้งหมดที่ใช้สร้างฟิลด์จากการคำนวณได้อย่างรวดเร็ว
ต่อไปนี้คือขั้นตอนในการรับรายการสูตรของเขตข้อมูลจากการคำนวณทั้งหมดอย่างรวดเร็ว:
- เลือกเซลล์ใดก็ได้ในตารางสาระสำคัญ
- ไปที่เครื่องมือ PivotTable -> วิเคราะห์ -> เขตข้อมูล รายการและชุด -> รายการสูตร
เมื่อคุณคลิกที่สูตรรายการ Excel จะแทรกแผ่นงานใหม่โดยอัตโนมัติพร้อมรายละเอียดของเขตข้อมูล/รายการที่คำนวณทั้งหมดที่คุณใช้ในตารางสรุปข้อมูล
นี่อาจเป็นเครื่องมือที่มีประโยชน์มากหากคุณต้องส่งงานให้กับลูกค้าหรือแบ่งปันกับทีมของคุณ
