คำนวณค่าใน PivotTable

เมื่อต้องการคำนวณค่าใน PivotTable คุณสามารถใช้ชนิดวิธีการคำนวณใดชนิดหนึ่งหรือทั้งหมดต่อไปนี้:

• ฟังก์ชันสรุปในเขตข้อมูลค่า    ข้อมูลในพื้นที่ค่าจะสรุปข้อมูลต้นฉบับพื้นฐานใน PivotTable ตัวอย่างเช่น ข้อมูลต้นฉบับต่อไปนี้:

• สร้าง PivotTable และ Pivotchart ต่อไปนี้ ถ้าคุณสร้าง PivotChart จากข้อมูลใน PivotTable ค่าใน PivotChart นั้นจะแสดงการคำนวณใน รายงาน PivotTable ที่เชื่อมโยง

• ใน PivotTable เขตข้อมูลคอลัมน์ เดือน จะมีรายการ มีนาคม และ เมษายน เขตข้อมูลแถว ภูมิภาค จะมีรายการ ภาคเหนือภาคใต้ภาคตะวันออก และ ภาคตะวันตก ค่าที่จุดตัดของคอลัมน์ เมษายน และแถว ภาคเหนือ ในรายได้จากยอดขายรวมจากระเบียนในข้อมูลต้นฉบับที่มีค่า เดือน ของ เมษายน และค่า ภูมิภาค ของ ภาคเหนือ

• ใน PivotChart เขตข้อมูล ภูมิภาค อาจเป็นเขตข้อมูลประเภทที่แสดง ภาคเหนือภาคใต้ภาคตะวันออก และ ภาคตะวันตก เป็นประเภท เขตข้อมูล เดือน อาจเป็นเขตข้อมูลของชุดข้อมูลที่แสดงรายการ มีนาคมเมษายน และ พฤษภาคม เป็นชุดข้อมูลที่แสดงในคำอธิบายแผนภูมิ เขตข้อมูล ค่า ที่ชื่อว่า ผลรวมของยอดขาย อาจมีตัวทำเครื่องหมายข้อมูลที่แสดงรายได้รวมในแต่ละภูมิภาคของแต่ละเดือน ตัวอย่างเช่น ตัวทำเครื่องหมายข้อมูลอาจแสดง ตามตำแหน่งบนแกนแนวตั้ง (ค่า) ยอดขายรวมสำหรับ เมษายน ในภูมิภาค ภาคเหนือ

• เมื่อต้องการคำนวณเขตข้อมูลค่า ฟังก์ชันสรุปต่อไปนี้จะพร้อมใช้งานสำหรับข้อมูลต้นฉบับทุกประเภท ยกเว้น ข้อมูลต้นฉบับ Online Analytical Processing (OLAP)

  ฟังก์ชัน	สรุป
  Sum	ผลรวมของค่า นี่เป็นฟังก์ชันเริ่มต้นสำหรับข้อมูลตัวเลข
  จำนวน	จำนวนของค่าของข้อมูล ฟังก์ชันสรุปนับจำนวนทำงานเช่นเดียวกับ ฟังก์ชัน 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 ในการคำนวณเขตข้อมูลค่าที่มีข้อมูลตัวเลข และฟังก์ชันสรุป Count ในการคำนวณเขตข้อมูลที่มีข้อความ คุณสามารถเลือกฟังก์ชันสรุปอื่นๆ เช่น Average, Max หรือ Min เพื่อวิเคราะห์เพิ่มเติมและกำหนดค่าข้อมูลของคุณ คุณยังสามารถสร้างสูตรของคุณเองที่ใช้องค์ประกอบของรายงานหรือข้อมูลเวิร์กชีตอื่นๆ โดยการสร้างเขตข้อมูลจากการคำนวณหรือรายการจากการคำนวณภายในเขตข้อมูล

คุณสามารถสร้างสูตรได้เฉพาะในรายการที่ยึดตามข้อมูลต้นฉบับที่ใช่ 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 ให้คุณสร้างสูตรใน PivotTable ในเชื่อมโยง ซึ่งคุณสามารถดูแต่ละค่าที่สร้างข้อมูลของคุณ แล้วคุณจะสามารถดูผลลัพธ์เป็นภาพใน PivotChart

ตัวอย่างเช่น PivotChart ต่อไปนี้แสดงยอดขายสำหรับพนักงานขายแต่ละคนในภูมิภาค:

เมื่อต้องการดูลักษณะของยอดขายเมื่อเพิ่มขึ้น 10 เปอร์เซ็นต์ คุณสามารถสร้างเขตข้อมูลจากการคำนวณใน PivotTable ที่เชื่อมโยงที่ใช้สูตร เช่น =Sales * 110%

ผลลัพธ์จะปรากฎทันทีใน PivotChart ตามที่แสดงในแผนภูมิต่อไปนี้:

เมื่อต้องการดูตัวทำเครื่องหมายข้อมูลที่แยกต่างหากสำหรับยอดขายในภูมิภาคเหนือลบด้วยค่าขนส่ง 8 เปอร์เซ็นต์ คุณสามารถสร้างรายการจากการคำนวณในเขตข้อมูลภูมิภาคที่มีสูตร เช่น =North – (North * 8%)

แผนภูมิผลลัพธ์อาจมีลักษณะดังนี้:

อย่างไรก็ตาม รายการจากการคำนวณที่สร้างในเขตข้อมูล พนักงานขาย จะปรากฎเป็นชุดข้อมูลที่แสดงในคำอธิบายแผนภูมิและปรากฎในแผนภูมิเป็นจุดข้อมูลในแต่ละประเภท