วิธีการคำนวณถัวเฉลี่ยถ่วงน้ำหนักใน Excel ด้วย SUMPRODUCT

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

ตัวอย่างเช่นเครื่องหมายบางอย่างในโรงเรียนเช่นการสอบภาคปฏิบัติและการสอบปลายภาคมักมีค่ามากกว่าการทดสอบหรือการกำหนดปกติ ถ้าค่าเฉลี่ยใช้ในการคำนวณคะแนนสุดท้ายของนักเรียนช่วงกลางคันและการสอบปลายภาคสุดท้ายจะได้รับน้ำหนักมากขึ้น ใน Excel คุณสามารถคำนวณถัวเฉลี่ยถ่วงน้ำหนักโดยใช้SUMPRODUCT ฟังก์ชัน

01 จาก 04

วิธีการทำงานของฟังก์ชันการทำงานแบบ SUMPRODUCT

SUMPRODUCT จะคูณองค์ประกอบของอาร์เรย์ตั้งแต่สองตัวขึ้นไปแล้วเพิ่มผลิตภัณฑ์ ตัวอย่างเช่นในสถานการณ์ที่มีอาร์เรย์อาร์เรย์สองชุดสำหรับอาร์เรย์สองอาร์เรย์ที่มีสี่องค์ประกอบ SUMPRODUCT ฟังก์ชั่น:

1. องค์ประกอบแรกของอาร์เรย์ 1 คูณด้วยอิลิเมนต์แรกในอาร์เรย์ 2
2. องค์ประกอบที่สองของอาร์เรย์ 1 คูณด้วยองค์ประกอบที่สองของอาร์เรย์ 2
3. องค์ประกอบที่สามของอาร์เรย์ 1 คูณด้วยองค์ประกอบที่สามของอาร์เรย์ 2
4. องค์ประกอบที่สี่ของอาร์เรย์ 1 คูณด้วยองค์ประกอบที่สี่ของอาร์เรย์ 2

ถัดไปผลิตภัณฑ์ของการดำเนินการคูณสี่จะสรุปและส่งกลับโดยฟังก์ชันเป็นผล

02 จาก 04

SUMPRODUCT ไวยากรณ์และอาร์กิวเมนต์

ไวยากรณ์ของฟังก์ชันหมายถึงเค้าโครงของฟังก์ชันและรวมถึงชื่อฟังก์ชันวงเล็บและอาร์กิวเมนต์ ไวยากรณ์สำหรับ SUMPRODUCT ฟังก์ชันคือ:

= SUMPRODUCT (array1, array2, array3, … array255)

อาร์กิวเมนต์สำหรับฟังก์ชัน SUMPRODUCT คือ:

• array1: อาร์เรย์อาร์เรย์แรก (จำเป็น)
• Array2, array3, … array255: อาร์เรย์เพิ่มเติม (ไม่จำเป็น) สูงสุด 255 มีอาร์เรย์สองตัวหรือมากกว่าฟังก์ชันนี้จะคูณองค์ประกอบของแต่ละอาร์เรย์เข้าด้วยกันและเพิ่มผลลัพธ์

อิลิเมนต์อาร์เรย์สามารถอ้างอิงเซลล์ไปยังตำแหน่งของข้อมูลในแผ่นงานหรือตัวเลขที่คั่นด้วยตัวดำเนินการทางคณิตศาสตร์เช่นบวก (+) หรือลบ (-) สัญญาณ ถ้าคุณป้อนตัวเลขที่ไม่ได้ถูกคั่นด้วยโอเปอเรเตอร์ Excel จะถือว่าเป็นข้อมูลข้อความ

• อาร์กิวเมนต์ทั้งหมดของอาร์เรย์ต้องมีขนาดเท่ากัน กล่าวอีกนัยหนึ่งจำนวนองค์ประกอบเดียวกันต้องอยู่ในแต่ละอาร์เรย์ ถ้าไม่, SUMPRODUCT ส่งกลับ #ราคา! ค่าความผิดพลาด
• ถ้าองค์ประกอบอาร์เรย์ไม่ใช่ตัวเลขเช่นข้อมูลข้อความ SUMPRODUCT ถือว่าเป็นศูนย์

03 จาก 04

การป้อนสูตรลดน้ำหนัก

ตัวอย่างของเราที่แสดงในบทความนี้จะคำนวณค่าเฉลี่ยถ่วงน้ำหนักสำหรับเครื่องหมายสุดท้ายของนักเรียนโดยใช้ SUMPRODUCT ฟังก์ชั่น; ฟังก์ชันสำเร็จโดย:

• การคูณเครื่องหมายต่างๆตามปัจจัยน้ำหนักของแต่ละบุคคล
• การเพิ่มผลิตภัณฑ์ของการดำเนินการคูณด้วยกัน
• หารผลรวมข้างต้นโดยรวมของปัจจัยการถ่วงน้ำหนัก 7 (1 + 1 + 2 + 3) สำหรับการประเมินทั้งสี่ครั้ง

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

= SUMPRODUCT (B3: B6, C3: C6) / (1 + 1 + 2 + 3)

1. คลิกที่เซลล์ C7 เพื่อให้เป็นเซลล์ที่ใช้งาน (ตำแหน่งที่เครื่องหมายสุดท้ายของนักเรียนจะปรากฏขึ้น)
2. พิมพ์สูตรข้างต้นลงในเซลล์
3. กดเข้าสู่ บนแป้นพิมพ์
4. คำตอบ78.6 ควรปรากฏในเซลล์ C7 (คำตอบของคุณอาจมีจุดทศนิยมเพิ่มเติม)

ค่าเฉลี่ยที่ไม่ได้รวมสำหรับสี่เครื่องหมายเดียวกันจะเท่ากับ 76.5 เนื่องจากนักเรียนมีผลดีกว่าสำหรับการสอบมิดไนท์และการสอบปลายภาคสุดท้ายการชั่งน้ำหนักโดยเฉลี่ยช่วยเพิ่มคะแนนโดยรวมของเขา

04 จาก 04

SUMPRODUCT รูปแบบสูตร

เพื่อเป็นการเน้นย้ำว่าผลของ SUMPRODUCT จะถูกหารด้วยผลรวมของน้ำหนักสำหรับแต่ละกลุ่มการประเมินตัวหาร - ส่วนที่ทำหาร - ถูกป้อนเป็น

(1+1+2+3สูตรการถ่วงน้ำหนักโดยรวมอาจจะง่ายขึ้นโดยการป้อนเลข 7 (ผลรวมของน้ำหนัก) เป็นตัวหาร สูตรก็จะเป็น:

= SUMPRODUCT (B3: B6, C3: C6) / 7

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

อีกทางเลือกหนึ่งและน่าจะเป็นตัวเลือกที่ดีที่สุดเนื่องจากใช้การอ้างอิงเซลล์แทนที่จะเป็นตัวเลขในตัวหารทั้งหมดจะเป็นการใช้ฟังก์ชัน SUM เพื่อรวมตัวหาร สูตรคือแล้ว:

= SUMPRODUCT (B3: B6, C3: C6) / SUM (B3: B6)

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

ตัวอย่างเช่นถ้าปัจจัยการถ่วงน้ำหนักสำหรับ การมอบหมาย ถูกเปลี่ยนเป็น 0.5 ในตัวอย่างและสำหรับการทดสอบ เป็น 1.5 คุณต้องแก้ไขสูตรสองสูตรแรกด้วยตนเองเพื่อแก้ไขตัวหาร

ในรูปแบบที่สามเฉพาะข้อมูลในเซลล์เท่านั้น B3 และ B4 ต้องได้รับการปรับปรุงและสูตรจะคำนวณผลลัพธ์ใหม่