ใน PivotTable คุณสามารถใช้ฟังก์ชันสรุปในเขตข้อมูลค่าเพื่อรวมค่าจากแหล่งข้อมูลต้นแบบได้ ถ้าฟังก์ชันสรุปและการคำนวณแบบกำหนดเองไม่ให้ผลลัพธ์ตามที่คุณต้องการ คุณสามารถสร้างสูตรของคุณเองในเขตข้อมูลจากการคำนวณและรายการจากการคำนวณ ตัวอย่างเช่น คุณสามารถเพิ่มรายการจากการคำนวณที่มีสูตรสำหรับค่าคอมมิชชั่นของยอดขาย ซึ่งอาจแตกต่างกันในแต่ละภูมิภาค จากนั้น PivotTable จะมีค่าคอมมิชชั่นในผลรวมย่อยและผลรวมทั้งหมดโดยอัตโนมัติ
อีกวิธีหนึ่งในการคํานวณคือการใช้หน่วยวัดใน Power Pivot ที่คุณสร้างโดยใช้สูตร Data Analysis Expressions (DAX) สําหรับข้อมูลเพิ่มเติม ให้ดู สร้างหน่วยวัดใน Power Pivot
PivotTable มีวิธีในการคำนวณข้อมูล เรียนรู้เกี่ยวกับวิธีการคำนวณที่พร้อมใช้งาน วิธีการคำนวณจะได้รับผลกระทบจากชนิดของข้อมูลต้นฉบับ และวิธีการใช้สูตรใน PivotTable และ PivotChart
วิธีการคำนวณที่พร้อมใช้งาน
เมื่อต้องการคำนวณค่าใน PivotTable คุณสามารถใช้ชนิดวิธีการคำนวณใดชนิดหนึ่งหรือทั้งหมดต่อไปนี้:
ฟังก์ชันสรุปในเขตข้อมูลค่า ข้อมูลในพื้นที่ค่าจะสรุปข้อมูลต้นฉบับต้นแบบใน PivotTable ตัวอย่างเช่น ข้อมูลต้นฉบับต่อไปนี้:
สร้าง PivotTable และ Pivotchart ต่อไปนี้ ถ้าคุณสร้าง PivotChart จากข้อมูลใน PivotTable ค่าใน PivotChart นั้นจะแสดงการคํานวณในรายงาน PivotTable ที่สัมพันธ์กัน
ใน PivotTable เขตข้อมูลคอลัมน์ เดือน จะมีรายการ มีนาคม และ เมษายน เขตข้อมูลแถว ภูมิภาค จะมีรายการ ภาคเหนือภาคใต้ภาคตะวันออก และ ภาคตะวันตก ค่าที่จุดตัดของคอลัมน์ เมษายน และแถว ภาคเหนือ ในรายได้จากยอดขายรวมจากระเบียนในข้อมูลต้นฉบับที่มีค่า เดือน ของ เมษายน และค่า ภูมิภาค ของ ภาคเหนือ
ใน PivotChart เขตข้อมูล ภูมิภาค อาจเป็นเขตข้อมูลประเภทที่แสดง ภาคเหนือภาคใต้ภาคตะวันออก และ ภาคตะวันตก เป็นประเภท เขตข้อมูล เดือน อาจเป็นเขตข้อมูลของชุดข้อมูลที่แสดงรายการ มีนาคมเมษายน และ พฤษภาคม เป็นชุดข้อมูลที่แสดงในคำอธิบายแผนภูมิ เขตข้อมูล ค่า ที่ชื่อว่า ผลรวมของยอดขาย อาจมีตัวทำเครื่องหมายข้อมูลที่แสดงรายได้รวมในแต่ละภูมิภาคของแต่ละเดือน ตัวอย่างเช่น ตัวทำเครื่องหมายข้อมูลอาจแสดง ตามตำแหน่งบนแกนแนวตั้ง (ค่า) ยอดขายรวมสำหรับ เมษายน ในภูมิภาค ภาคเหนือ
เมื่อต้องการคำนวณเขตข้อมูลค่า ฟังก์ชันสรุปต่อไปนี้จะพร้อมใช้งานสำหรับข้อมูลต้นฉบับทุกประเภท ยกเว้น ข้อมูลต้นฉบับ Online Analytical Processing (OLAP)
ฟังก์ชัน สรุป ผลรวม ผลรวมของค่า นี่เป็นฟังก์ชันเริ่มต้นสำหรับข้อมูลตัวเลข จำนวน จำนวนของค่าของข้อมูล ฟังก์ชันสรุปนับจำนวนทำงานเช่นเดียวกับ ฟังก์ชัน COUNTA นับจำนวนเป็นฟังก์ชันเริ่มต้นสำหรับข้อมูลอื่นๆ ที่ไม่ใช่ตัวเลข ค่าเฉลี่ย ค่าเฉลี่ยของค่า Max ค่ามากที่สุด Min ค่าน้อยที่สุด ผลิตภัณฑ์ ผลคูณของค่า นับตัวเลข จำนวนค่าของข้อมูลที่เป็นตัวเลข ฟังก์ชันสรุปนับจำนวนค่าที่เป็นตัวเลขทำงานเช่นเดียวกับ ฟังก์ชัน COUNT StDev ค่าประมาณของค่าเบี่ยงเบนมาตรฐานของประชากร โดยที่ตัวอย่างคือชุดย่อยของทั้งประชากร StDevp ค่าเบี่ยงเบนมาตรฐานของประชากร โดยที่ประชากรคือข้อมูลทั้งหมดที่นำมาสรุป Var ค่าประมาณของค่าความแปรปรวนของประชากร โดยที่ตัวอย่างคือชุดย่อยของทั้งประชากร Varp ค่าความแปรปรวนของประชากร โดยที่ประชากรคือข้อมูลทั้งหมดที่นำมาสรุป
การคํานวณแบบกําหนดเอง การคํานวณแบบกําหนดเองจะแสดงค่าโดยยึดตามรายการหรือเซลล์อื่นๆ ในพื้นที่ข้อมูล ตัวอย่างเช่น คุณอาจแสดงค่าในเขตข้อมูล ผลรวมของยอดขาย เป็นเปอร์เซ็นต์ของยอดขาย ทีนาคม หรือผลรวมสะสมของรายการในเขตข้อมูล เดือน
ฟังก์ชันต่อไปนี้จะพร้อมใช้งานสำหรับการคำนวณแบบกำหนดเองในเขตข้อมูลค่าฟังก์ชัน ผลลัพธ์ ไม่มีการคำนวณ แสดงค่าที่ใส่ในเขตข้อมูล % ของผลรวมทั้งหมด แสดงค่าที่เป็นเปอร์เซ็นต์ของผลรวมทั้งหมดของค่าหรือจุดข้อมูลทั้งหมดในรายงาน % ของผลรวมคอลัมน์ แสดงค่าทั้งหมดในคอลัมน์แต่ละคอลัมน์หรือชุดแต่ละชุดที่เป็นเปอร์เซ็นต์ของผลรวมสำหรับคอลัมน์หรือชุดนั้น % ของผลรวมแถว แสดงค่าในแถวแต่ละแถวหรือประเภทแต่ละประเภทที่เป็นเปอร์เซ็นต์ของผลรวมสำหรับแถวหรือประเภทนั้น % ของ แสดงค่าที่เป็นเปอร์เซ็นต์ของค่าของ รายการพื้นฐาน ใน เขตข้อมูลพื้นฐาน นั้น % ของผลรวมแถวพาเรนต์ คำนวณค่าดังต่อไปนี้:
(ค่าสำหรับรายการ) / (ค่าสำหรับรายการแม่ในแถว)% ของผลรวมคอลัมน์พาเรนต์ คำนวณค่าดังต่อไปนี้:
(ค่าสำหรับรายการ) / (ค่าสำหรับรายการแม่ในคอลัมน์)% ของผลรวมพาเรนต์ คำนวณค่าดังต่อไปนี้:
(ค่าสำหรับรายการ) / (ค่าสำหรับรายการแม่ของเขตข้อมูลพื้นฐานที่เลือก)ความแตกต่างจาก แสดงค่าที่เป็นความแตกต่างจากค่าของ รายการพื้นฐาน ใน เขตข้อมูลพื้นฐาน นั้น % ความแตกต่างจาก แสดงค่าที่เป็นความแตกต่างเปอร์เซ็นต์จากค่าของ รายการพื้นฐาน ใน เขตข้อมูลพื้นฐาน นั้น ผลรวมสะสมสำหรับ แสดงค่าสำหรับรายการที่ต่อเนื่องกันใน เขตข้อมูลพื้นฐาน เป็นผลรวมสะสม % ผลรวมสะสมสำหรับ คำนวณค่าสำหรับรายการที่ต่อเนื่องกันใน เขตข้อมูลพื้นฐาน ที่แสดงผลรวมสะสมเป็นเปอร์เซ็นต์ จัดอันดับจากน้อยที่สุดไปหามากที่สุด แสดงลำดับของค่าที่เลือกในเขตข้อมูลที่เฉพาะเจาะจง ซึ่งแสดงรายการที่น้อยที่สุดในเขตข้อมูลเป็น 1 และค่าที่มากกว่าแต่ละค่าจะมีค่าลำดับที่สูงกว่า จัดอันดับจากมากที่สุดไปหาน้อยที่สุด แสดงลำดับของค่าที่เลือกในเขตข้อมูลที่เฉพาะเจาะจง ซึ่งแสดงรายการที่มากที่สุดในเขตข้อมูลเป็น 1 และค่าที่น้อยกว่าแต่ละค่าจะมีค่าลำดับที่สูงกว่า ดัชนี คำนวณค่าดังต่อไปนี้:
((ค่าในเซลล์) x (ผลรวมทั้งหมดของผลรวมทั้งหมด)) / ((ผลรวมแถวทั้งหมด) x (ผลรวมคอลัมน์ทั้งหมด))
- สูตร ถ้าฟังก์ชันสรุปและการคํานวณแบบกําหนดเองไม่ให้ผลลัพธ์ที่คุณต้องการ คุณสามารถสร้างสูตรของคุณเองในเขตข้อมูลจากการคํานวณและรายการจากการคํานวณได้ ตัวอย่างเช่น คุณสามารถเพิ่มรายการจากการคำนวณที่มีสูตรสำหรับค่าคอมมิชชั่นของยอดขาย ซึ่งอาจแตกต่างกันในแต่ละภูมิภาค จากนั้น รายงานจะมีค่าคอมมิชชั่นในผลรวมย่อยและผลรวมทั้งหมดโดยอัตโนมัติ
ชนิดของข้อมูลต้นฉบับมีผลกับการคำนวณอย่างไร
การคำนวณและตัวเลือกที่พร้อมใช้งานในรายการจะขึ้นอยู่กับว่าข้อมูลต้นฉบับมาจากฐานข้อมูล OLAP หรือฐานข้อมูลที่ไม่ใช่ OLAP
-
การคํานวณที่ยึดตามข้อมูลต้นฉบับ OLAP สําหรับ PivotTable ที่สร้างขึ้นจากคิวบ์ OLAP ค่าที่สรุปจะถูกคํานวณไว้ล่วงหน้าบนเซิร์ฟเวอร์ OLAP ก่อนที่ Excel จะแสดงผลลัพธ์ คุณไม่สามารถเปลี่ยนวิธีการคำนวณค่าที่กำหนดไว้ล่วงหน้าเหล่านี้ใน PivotTable ตัวอย่างเช่น คุณไม่สามารถเปลี่ยนฟังก์ชันสรุปที่ใช้ในการคำนวณเขตข้อมูลหรือผลรวมย่อย หรือเพิ่มเขตข้อมูลจากการคำนวณหรือรายการจากการคำนวณ
นอกจากนี้ ถ้าเซิร์ฟเวอร์ OLAP มีเขตข้อมูลจากการคำนวณ หรือที่เรียกว่า สมาชิกจากการคำนวณ คุณจะเห็นเขตข้อมูลเหล่านี้ในรายการเขตข้อมูล PivotTable คุณจะเห็นเขตข้อมูลจากการคำนวณและรายการจากการคำนวณใดก็ตามที่สร้างขึ้นโดยแมโครที่เขียนใน Visual Basic for Applications (VBA) และจัดเก็บในเวิร์กบุ๊กของคุณ แต่คุณจะไม่สามารถเปลี่ยนแปลงเขตข้อมูลหรือรายการเหล่านี้ ถ้าคุณต้องการชนิดของการคำนวณเพิ่มเติม ให้ติดต่อผู้ดูแลระบบฐานข้อมูล OLAP ของคุณ
สำหรับข้อมูลต้นฉบับ OLAP คุณสามารถใส่หรือยกเว้นค่าสำหรับรายการที่ซ่อนอยู่เมื่อคำนวณผลรวมย่อยและผลรวมทั้งหมด - การคํานวณที่ยึดตามข้อมูลต้นฉบับที่ไม่ใช่ OLAP ใน PivotTable ที่ยึดตามข้อมูลภายนอกหรือข้อมูลเวิร์กชีตชนิดอื่น Excel จะใช้ฟังก์ชันสรุป Sum ในการคํานวณเขตข้อมูลค่าที่มีข้อมูลตัวเลข และฟังก์ชันสรุปนับจํานวนเพื่อคํานวณเขตข้อมูลที่มีข้อความ คุณสามารถเลือกฟังก์ชันสรุปอื่นๆ เช่น Average, Max หรือ Min เพื่อวิเคราะห์เพิ่มเติมและกำหนดค่าข้อมูลของคุณ คุณยังสามารถสร้างสูตรของคุณเองที่ใช้องค์ประกอบของรายงานหรือข้อมูลเวิร์กชีตอื่นๆ โดยการสร้างเขตข้อมูลจากการคำนวณหรือรายการจากการคำนวณภายในเขตข้อมูล
การใช้สูตรใน PivotTable
คุณสามารถสร้างสูตรได้เฉพาะในรายการที่ยึดตามข้อมูลต้นฉบับที่ใช่ OLAP คุณไม่สามารถใช้สูตรในรายงานที่ยึดตามฐานข้อมูล OLAP เมื่อคุณใช้สูตรใน PivotTable คุณควรทราบเกี่ยวกับกฎไวยากรณ์ของสูตรต่อไปนี้และลักษณะการทำงานของสูตร:
องค์ประกอบสูตร PivotTable ในสูตรที่คุณสร้างสําหรับเขตข้อมูลจากการคํานวณและรายการจากการคํานวณ คุณสามารถใช้ตัวดําเนินการและนิพจน์เหมือนกับที่คุณทําในสูตรเวิร์กชีตอื่น คุณสามารถใช้ค่าคงที่และอ้างอิงถึงข้อมูลจากรายงาน แต่คุณไม่สามารถใช้การอ้างอิงเซลล์หรือชื่อที่กำหนด คุณไม่สามารถใช้ฟังก์ชันเวิร์กชีตที่ต้องใช้การอ้างอิงเซลล์หรือชื่อที่กำหนดเป็นอาร์กิวเมนต์ และคุณจะไม่สามารถใช้ฟังก์ชันอาร์เรย์
เขตข้อมูลและชื่อรายการ Excel จะใช้ชื่อเขตข้อมูลและรายการเพื่อระบุองค์ประกอบเหล่านั้นของรายงานในสูตรของคุณ ในตัวอย่างต่อไปนี้ ข้อมูลในช่วง C3:C9 ตะใช้ชื่อเขตข้อมูล ผลิตภัณฑ์นม รายการจากการคำนวณในเขตข้อมูล ชนิด ที่ประมาณยอดขายสำหรับผลิตภัณฑ์ใหม่ที่ยึดตามยอดขายผลิตภัณฑ์นมอาจใช้สูตร เช่น =Dairy * 115%
หมายเหตุ
ใน PivotChart ชื่อเขตข้อมูลจะแสดงในรายการเขตข้อมูล PivotTable และชื่อรายการอาจอยู่ในรายการดรอปดาวน์ของแต่ละเขตข้อมูล อย่าสับสนชื่อเหล่านี้กับชื่อที่คุณเห็นในเคล็ดลับแผนภูมิ ที่แสดงชุดข้อมูลและชื่อจุดข้อมูล
สูตรทํางานบนผลรวมทั้งหมด ไม่ใช่แต่ละระเบียน สูตรสําหรับเขตข้อมูลจากการคํานวณจะดําเนินการกับผลรวมของข้อมูลต้นแบบสําหรับเขตข้อมูลใดๆ ในสูตร ตัวอย่างเช่น สูตรเขตข้อมูลจากการคำนวณ =Sales * 1.2 จะคูณผลรวมของยอดขายสำหรับแต่ละชนิดและภูมิภาคด้วย 1.2 ซึ่งจะไม่คูณแต่ละยอดขายด้วย 1.2 แล้วรวมจำนวณที่คูณ
สูตรสำหรับรายการจากการคำนวณจะทำงานในแต่ละระเบียน ตัวอย่างเช่น สูตรรายการจากการคำนวณ =Dairy *115% จะคูณแต่ละยอดขายของผลิตภัณฑ์นมด้วย 115% หลักจากนั้น จำนวณที่คูณจะสรุปรวมกันในพื้นที่ ค่าช่องว่าง ตัวเลข และสัญลักษณ์ในชื่อ ในชื่อที่มีมากกว่าหนึ่งเขตข้อมูล เขตข้อมูลสามารถอยู่ในลําดับใดก็ได้ ในตัวอย่างข้างต้น เซลล์ C6:D6 อาจเป็น 'เมษายน ภาคเหนือ' หรือ 'ภาคเหนือ เมษายน' ก็ได้ ใช้เครื่องหมายอัญประกาศเดี่ยวล้อมรอบชื่อที่มีมากกว่าหนึ่งคำหรือชื่อที่มีตัวเลขหรือสัญลักษณ์
ผลรวม สูตรไม่สามารถอ้างอิงถึงผลรวม (เช่น ผลรวมของเดือนมีนาคมผลรวมของเดือนเมษายน และ ผลรวมทั้งหมด ในตัวอย่าง
ชื่อเขตข้อมูลในการอ้างอิงรายการ คุณสามารถใส่ชื่อเขตข้อมูลในการอ้างอิงไปยังรายการ ชื่อรายการต้องอยู่ในวงเล็บปีกกา ตัวอย่างเช่น ภูมิภาค[ภาคเหนือ] ใช้รูปแบบนี้เพื่อหลีกเลี่ยง #NAME หรือไม่ ข้อผิดพลาดเมื่อรายการสองรายการในสองเขตข้อมูลที่แตกต่างกันในรายงานมีชื่อเดียวกัน ตัวอย่างเช่น ถ้ารายงานมีรายการที่ชื่อว่า เนื้อสัตว์ ในเขตข้อมูล ชนิด และรายการอื่นที่ชื่อว่า เนื้อสัตว์ ในเขตข้อมูล ประเภท คุณสามารถป้องกัน #NAME ได้หรือไม่ ข้อผิดพลาดโดยอ้างถึงรายการเป็น ชนิด[เนื้อสัตว์] และ ประเภท[เนื้อสัตว์]
การอ้างถึงรายการตามตําแหน่ง คุณสามารถอ้างอิงถึงรายการตามตําแหน่งที่เรียงลําดับและแสดงในรายงาน ชนิด[1] คือ ผลิตภัณฑ์นม และ ชนิด[2] คือ อาหารทะเล รายการที่อ้างอิงด้วยวิธีนี้สามารถเปลี่ยนแปลงได้เมื่อใดก็ตามที่ตำแหน่งของรายการเปลี่ยนแปลงหรือรายการอื่นถูกแสดงหรือถูกซ่อน รายการที่ซ่อนอยู่จะไม่ถูกนับในดัชนีนี้
คุณสามารถใช้ตำแหน่งที่เกี่ยวข้องเพื่ออ้างอิงรายการ ตำแหน่งจะถูกกำหนดให้สอดคล้องกับรายการจากการคำนวณที่มีสูตร ถ้า ภาคใต้ คือภูมิภาคปัจจุบัน ภูมิภาค[-1] คือ ภาคเหนือ ถ้า ภาคเหนือ คือภูมิภาคปัจจุบัน ภูมิภาค[+1] คือ ภาคเหนือ ตัวอย่างเช่น รายการจากการคำนวณอาจใช้สูตร =Region[-1] * 3% ถ้าตำแหน่งที่คุณระบุอยู่ก่อนรายการแรกหรือหลังจากรายสุดท้ายในเขตข้อมูล สูตรจะให้ผลลัพธ์ใน #REF! ข้อผิดพลาด
การใช้สูตรใน PivotChart
เมื่อต้องการใช้สูตรใน PivotChart ให้คุณสร้างสูตรใน PivotTable ในเชื่อมโยง ซึ่งคุณสามารถดูแต่ละค่าที่สร้างข้อมูลของคุณ แล้วคุณจะสามารถดูผลลัพธ์เป็นภาพใน PivotChart
ตัวอย่างเช่น PivotChart ต่อไปนี้แสดงยอดขายสำหรับพนักงานขายแต่ละคนในภูมิภาค:
เมื่อต้องการดูลักษณะของยอดขายเมื่อเพิ่มขึ้น 10 เปอร์เซ็นต์ คุณสามารถสร้างเขตข้อมูลจากการคำนวณใน PivotTable ที่เชื่อมโยงที่ใช้สูตร เช่น =Sales * 110%
ผลลัพธ์จะปรากฎทันทีใน PivotChart ตามที่แสดงในแผนภูมิต่อไปนี้:
เมื่อต้องการดูตัวทำเครื่องหมายข้อมูลที่แยกต่างหากสำหรับยอดขายในภูมิภาคเหนือลบด้วยค่าขนส่ง 8 เปอร์เซ็นต์ คุณสามารถสร้างรายการจากการคำนวณในเขตข้อมูลภูมิภาคที่มีสูตร เช่น =North – (North * 8%)
แผนภูมิผลลัพธ์อาจมีลักษณะดังนี้:
อย่างไรก็ตาม รายการจากการคำนวณที่สร้างในเขตข้อมูล พนักงานขาย จะปรากฎเป็นชุดข้อมูลที่แสดงในคำอธิบายแผนภูมิและปรากฎในแผนภูมิเป็นจุดข้อมูลในแต่ละประเภท
ต้องการความช่วยเหลือหรือไม่
คุณสามารถสอบถามผู้เชี่ยวชาญใน ชุมชนด้านเทคนิคของ Excel หรือรับการสนับสนุนใน ชุมชนได้เสมอ