วิธีคำนวณค่าเฉลี่ยถ่วงน้ำหนักใน Excel ด้วยเปอร์เซ็นต์

การคำนวณค่าเฉลี่ยถ่วงน้ำหนักใน Excel ด้วยเปอร์เซ็นต์ช่วยให้คุณจัดการกับข้อมูลที่มีระดับความสำคัญต่างกันได้

ค่าเฉลี่ยถ่วงน้ำหนักจะคล้ายกับค่าเฉลี่ยมาตรฐาน แต่เน้นค่าเฉพาะตามน้ำหนักที่กำหนด เมื่อน้ำหนักที่แตกต่างกันแสดงเป็นเปอร์เซ็นต์ ผลลัพธ์มักจะมีความหมายมากกว่า

ในบทความนี้ คุณจะได้เรียนรู้ขั้นตอนในการคำนวณค่าเฉลี่ยถ่วงน้ำหนักโดยใช้เปอร์เซ็นต์ใน Excel เราครอบคลุมสูตร ฟังก์ชัน และเคล็ดลับ Excel ที่จำเป็นที่คุณจ���เป็นต้องรู้

เข้าเรื่องกันเลย

สารบัญ

• ค่าเฉลี่ยถ่วงน้ำหนักคืออะไร?
  • การใช้เปอร์เซ็นต์หรือหน่วย
• การตั้งค่าแผ่นงาน Excel ของคุณ
  • การจัดรูปแบบเซลล์เป็นเปอร์เซ็นต์
• วิธีคำนวณค่าเฉลี่ยถ่วงน้ำหนักใน Excel
  • การใช้ฟังก์ชัน SUMPRODUCT ของ Excel
  • หารด้วยน้ำหนักรวม
  • คำแนะนำสำหรับ Excel
  • ทำลายลงสูตร
• สถานการณ์ในโลกแห่งความเป็นจริง
  • ระบบการให้เกรด
  • การวิเคราะห์ทางการเงิน
• 4 ข้อผิดพลาดทั่วไปและการแก้ไขปัญหา
  • 1. ข้อมูลและน้ำหนักที่ไม่ตรงแนว
  • 2. ไม่คิดเป็นร้อยละ
  • 3. การอ้างอิงเซลล์ไม่ถูกต้อง
  • 4. ใช้ฟังก์ชั่นที่ไม่ถูกต้อง
• คำพูดสุดท้ายของเรา

ค่าเฉลี่ยถ่วงน้ำหนักคืออะไร?

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

ค่าเฉลี่ยถ่วงน้ำหนักคือประเภทของค่าเฉลี่ยที่พิจารณาความสำคัญสัมพัทธ์ของชุดค่าต่างๆ

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

ด้วยค่าเฉลี่ยถ่วงน้ำหนัก ค่าทั้งหมดจะมีน้ำหนักไม่เท่ากัน น้ำหนักถูกกำหนดให้เป็นค่าตามความสำคัญหรือความเกี่ยวข้อง

ในแง่คณิตศาสตร์ ค่าเฉลี่ยถ่วงน้ำหนักคำนวณโดย:

1. คูณแต่ละค่าด้วยน้ำหนักที่สอดคล้องกัน
2. การรวมผลิตภัณฑ์เพื่อให้ได้ผลรวม
3. หารผลรวมด้วยผลรวมของน้ำหนัก

สูตรทางคณิตศาสตร์สามารถแสดงเป็น:

ค่าเฉลี่ยถ่วงน้ำหนัก = (?(ค่า × น้ำหนัก)) / ?(น้ำหนัก)

คุณสามารถเขียนสูตรทางคณิตศาสตร์เมื่อทำงานกับแมโครและ VBA

อย่างไรก็ตาม บทช่วยสอนนี้ใช้ฟังก์ชันที่มีประโยชน์บางอย่างในMicrosoft Excelเพื่อทำให้การคำนวณง่ายขึ้น คุณจะพบว่ามันเป็นทางลัดที่ใช้งานง่าย

การใช้เปอร์เซ็นต์หรือหน่วย

การคำนวณแบบถ่วงน้ำหนักบางรายการใช้เปอร์เซ็นต์ และบางรายการใช้หน่วย

ตัวอย่างเช่น หากคุณกำลังคิดราคาเฉลี่ยในร้านค้า คุณจะใช้หน่วยเงินที่แสดงราคาของสินค้า

หากดินสอมีราคา 50 เซ็นต์และปากกามีราคา 1 ดอลลาร์ น้ำหนักจะเท่ากับ 0.5 และ 1.0 ตามลำดับ

เกรดของวิทยาลัยมีแนวโน้มที่จะขึ้นอยู่กับเปอร์เซ็นต์ เช่น อาจกำหนดให้สอบกลางภาค 40% ของคะแนนรวม ส่วน 60% กำหนดให้สอบปลายภาค

บทความนี้มุ่งเน้นไปที่การคำนวณและการแสดงค่าเฉลี่ยโดยใช้เปอร์เซ็นต์

การตั้งค่าแผ่นงาน Excel ของคุณ

ก่อนที่คุณจะคำนวณค่าเฉลี่ยถ่วงน้ำหนักใน Excel คุณต้องตั้งค่าเวิร์กชีตของคุณด้วยข้อมูลที่จำเป็น ทำตามขั้นตอนเหล่านี้:

1. ป้อนค่าข้อมูลของคุณในหนึ่งคอลัมน์ (เช่น คอลัมน์ B)
2. กำหนดน้ำหนักเป็นเปอร์เซ็นต์ในคอลัมน์แยกต่างหาก (เช่น คอลัมน์ C)
3. ตรวจสอบว่าน้ำหนักเพิ่มขึ้นถึง 100%

บทช่วยสอนนี้เริ่มต้นด้วยตัวอย่างจากโลกแห่งกีฬา โค้ชประเมินผู้เล่นตามคุณลักษณะที่แตกต่างกัน และคุณลักษณะบางอย่างมีค่ามากกว่าคุณลักษณะอื่นๆ

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

คัดลอกตารางนี้ลงในเทมเพลต Excel ของคุณ:

ค่าน้ำหนักด้านบนแสดงเป็นเปอร์เซ็นต์ และผลรวมคือ 100%

การจัดรูปแบบเซลล์เป็นเปอร์เซ็นต์

เพื่อความชัดเจน จำเป็นต้องจัดรูปแบบเซลล์ที่มีค่าน้ำหนักเป็นเปอร์เซ็นต์ นี่คืองานทีละขั้นตอน:

1. เลือกเซลล์น้ำหนักที่ต้องการจัดรูปแบบ (เช่น คอลัมน์ B)
2. คลิกที่แท็บ "หน้าแรก" ในแถบเครื่องมือของ Excel
3. ค้นหากลุ่ม "ตัวเลข" และคลิกปุ่ม "รูปแบบเปอร์เซ็นต์" (%) ซึ่งจะจัดรูปแบบเซลล์ที่เลือกเป็นเปอร์เซ็นต์และแสดงด้วยเครื่องหมาย %

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

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

การคำนวณค่าเฉลี่ยถ่วงน้ำหนักเกี่ยวข้องกับชุดของขั้นตอนง่ายๆ โดยใช้ฟังก์ชัน SUMPRODUCT และ SUM

ฟังก์ชันเหล่านี้สามารถรวมกันเพื่อหาค่าเฉลี่ยขั้นสุดท้ายที่คำนึงถึงความสำคัญหรือน้ำหนักที่แตกต่างกันซึ่งกำหนดให้กับค่าต่างๆ

การใช้ฟังก์ชัน SUMPRODUCT ของ Excel

สูตร SUMPRODUCT ของ Excel ใช้เพื่อคูณอาร์เรย์ของค่าตั้งแต่ 2 อาร์เรย์ขึ้นไปและส่งกลับผลรวมของผลิตภัณฑ์

อาร์เรย์หมายถึงรายการของค่าต่างๆ ช่วงของเซลล์ที่มีจำนวนเป็นหนึ่งอาร์เรย์ อาร์เรย์ที่มีราคาเป็นอาร์เรย์ที่สอง

หารด้วยน้ำหนักรวม

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

สมมติว่าน้ำหนักรวมเป็นผลรวมของปริมาณในคอลัมน์ B

เมื่อใช้ฟังก์ชัน SUM เช่นนี้ SUM(B2:B4) คุณจะได้น้ำหนักรวม

ตัวเลขนี้ใช้เพื่อแบ่งเป็นผลลัพธ์ของผลลัพธ์ SUMPRODUCT

คำแนะนำสำหรับ Excel

ทำตามขั้นตอนเหล่านี้ใน แผ่นงาน Excel ของคุณ :

1. คลิกที่เซลล์ว่างซึ่งจะแสดงค่าเฉลี่ยถ่วงน้ำหนัก
2. ป้อนสูตรต่อไปนี้: =SUMPRODUCT(B2:B4, C2:C4)/SUM(B2:B4)
3. กดปุ่มตกลง'.

เมื่อคุณทำตามขั้นตอนเหล่านี้เรียบร้อยแล้ว Excel จะแสดงราคาถัวเฉลี่ยถ่วงน้ำหนักในเซลล์ที่คุณเลือก

หากคุณทำตามตัวอย่างนี้ คุณอาจเห็นตัวเลขยาวๆ ในเซลล์ผลลัพธ์ที่มีทศนิยมหลายตำแหน่ง

ณ จุดนี้ คุณควรจัดระเบียบการแสดงผลโดยจัดรูปแบบเซลล์ผลลัพธ์ให้แสดงทศนิยมสองตำแหน่ง

ทำลายลงสูตร

นี่คือรายละเอียดของสูตรจากส่วนก่อนหน้า:

• SUMPRODUCT(B2:B4, C2:C4) รับผลรวมของผลิตภัณฑ์ของจำนวนสินค้าคูณด้วยราคาที่เกี่ยวข้อง
• SUM(B2:B4) คำนวณน้ำหนักรวม (ผลรวมของจำนวนสินค้า)
• SUMPRODUCT(B2:B4, C2:C4)/SUM(B2:B4) หารผลรวมของผลิตภัณฑ์ด้วยน้ำหนักรวม โดยระบุค่าเฉลี่ยถ่วงน้ำหนัก

สถานการณ์ในโลกแห่งความเป็นจริง

การใช้งานทั่วไปสองแบบของสูตรนี้ ได้แก่ ระบบการให้คะแนนและการวิเคราะห์ทางการเงิน

ตัวอย่างเหล่านี้สาธิตวิธีการใช้ค่าเฉลี่ยถ่วงน้ำหนักในExcelโดยใช้เปอร์เซ็นต์เป็นน้ำหนัก

ระบบการให้เกรด

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

ตัวอย่างเช่น สมมติว่าการแบ่งเกรดต่อไปนี้:

• การมอบหมายงาน: 40% (รวมเป็น 4 ชิ้น แต่ละชิ้นมีมูลค่า 10%)
• แบบทดสอบ: 20% (รวม 2 อย่าง แต่ละอันมีค่า 10%)
• สอบกลางภาค: 20%
• การสอบปลายภาค: 20%

หากต้องการทำตามตัวอย่าง คุณสามารถคัดลอกข้อมูลในตารางนี้ลงในสเปรดชีตของคุณได้ ใส่คอลัมน์แรกในตารางลงในคอลัมน์ A

ในการคำนวณค่าเฉลี่ยถ่วงน้ำหนักใน Excel คุณสามารถใช้ฟังก์ชัน SUMPRODUCT

สมมติว่าเกรดอยู่ในคอลัมน์ B (ตั้งแต่ B2 ถึง B9) และน้ำหนักที่เกี่ยวข้องซึ่งแสดงเป็นเปอร์เซ็นต์อยู่ในคอลัมน์ C (ตั้งแต่ C2 ถึง C9) สูตรจะมีลักษณะดังนี้:

=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)

สูตร Excel นี้จะคำนวณค่าเฉลี่ยถ่วงน้ำหนักโดยใช้เกรดและเปอร์เซ็นต์ที่ให้มา

การวิเคราะห์ทางการเงิน

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

สมมติว่าคุณเป็นเจ้าของหุ้นต่อไปนี้ในพอร์ตของคุณ:

สมมติว่าราคาหุ้นอยู่ในคอลัมน์ B (ตั้งแต่ B2 ถึง B4) หุ้นในคอลัมน์ C (ตั้งแต่ C2 ถึง C4) และเปอร์เซ็นต์การลงทุนในคอลัมน์ D (ตั้งแต่ D2 ถึง D4) สูตรถัวเฉลี่ยถ่วงน้ำหนักจะเป็นดังนี้:

=SUMPRODUCT(B2:B4, D2:D4)/SUM(D2:D4)

สูตรข้างต้นคำนวณราคาหุ้นถัวเฉลี่ยถ่วงน้ำหนักตามเปอร์เซ็นต์การลงทุนสำหรับหุ้นแต่ละตัว ค่านี้ช่วยในการประเมินประสิทธิภาพโดยรวมของพอร์ตโฟลิโอ

ภาพหน้าจอนี้แสดงการคำนวณและผลลัพธ์:

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

ในการดำเนินการดังกล่าว ให้ใช้การจัดรูปแบบตามเงื่อนไขเพื่อเพิ่มกฎ "เน้นเซลล์" ที่มีเงื่อนไข "มากกว่า" ซึ่งช่วยให้คุณระบุเกณฑ์และเลือกสีไฮไลท์ได้

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

ซึ่งช่วยให้สามารถคำนวณน้ำหนักราคาปิดเฉลี่ยรายวันตามความถี่ได้ วิดีโอที่เป็นประโยชน์นี้แสดงวิธีสร้างตารางความถี่ใน Excel:

4 ข้อผิดพลาดทั่วไปและการแก้ไขปัญหา

ต่อไปนี้เป็นข้อผิดพลาดทั่วไปสี่ประการที่คุณจะพบเมื่อคำนวณค่าเฉลี่ยถ่วงน้ำหนักใน Excel

1. ข้อมูลและน้ำหนักที่ไม่ตรงแนว

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

ข้อมูลควรแสดงอยู่ในคอลัมน์ที่อยู่ติดกัน โดยแต่ละค่าจะสอดคล้องกับน้ำหนักที่เกี่ยวข้อง:

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

2. ไม่คิดเป็นร้อยละ

เมื่อจัดการกับเปอร์เซ็นต์เป็นน้ำหนัก ตรวจสอบให้แน่ใจว่าน้ำหนักแสดงเป็นค่าทศนิยม ตัวอย่างเช่น ถ้าน้ำหนักแสดงเป็น 20% ค่าเทียบเท่าทศนิยมควรเป็น 0.20

หากต้องการแปลงเปอร์เซ็นต์เป็นทศนิยม ให้หารเปอร์เซ็นต์ด้วย 100:

=C2 / 100

การคิดเป็นเปอร์เซ็นต์อย่างถูกต้องจะทำให้การคำนวณถัวเฉลี่ยถ่วงน้ำหนักได้ผลลัพธ์ที่ถูกต้อง

3. การอ้างอิงเซลล์ไม่ถูกต้อง

สิ่งสำคัญคือต้องแน่ใจว่าการอ้างอิงเซลล์นั้นถูกต้อง การอ้างอิงเซลล์ที่ไม่ถูกต้องอาจนำไปสู่ข้อผิดพลาดหรือผลลัพธ์ที่ไม่ถูกต้องในการคำนวณ

ตรวจสอบว่าสูตรมีช่วงเซลล์ที่เหมาะสมสำหรับทั้งค่าและน้ำหนัก

4. ใช้ฟังก์ชั่นที่ไม่ถูกต้อง

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

ส่วนเบี่ยงเบนมาตรฐานและค่าเฉลี่ยเคลื่อนที่เป็นวิธีการจับการเปลี่ยนแปลงของค่าเฉลี่ยข้อมูล การคำนวณเหล่านั้นใช้สูตรที่แตกต่างกัน

ฟังก์ชัน AVERAGEIF ช่วยให้คุณกรองค่าที่ไม่ตรงตามเกณฑ์ที่กำหนดออกได้ อย่างไรก็ตาม จะเป็นการดีกว่าหากกำหนดค่าเป็นเปอร์เซ็นต์ต่ำเมื่อคำนวณค่าเฉลี่ยถ่วงน้ำหนัก

คำพูดสุดท้ายของเรา

ในบทความนี้ เราได้สาธิตกระบวนการคำนวณค่าเฉลี่ยถ่วงน้ำหนักใน Excel พร้อมเปอร์เซ็นต์ ค่าเฉลี่ยถ่วงน้ำหนักให้ข้อมูลเชิงลึกที่มีค่าในชุดข้อมูล โดยเน้นความสำคัญสัมพัทธ์ของค่าจำนวนหนึ่ง

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

=SUMPRODUCT(ช่วงข้อมูล, ช่วงน้ำหนัก) / SUM(ช่วงน้ำหนัก)

วิธีนี้มีประโยชน์อย่างยิ่งเมื่อต้องจัดการกับเกรด ผลการสำรวจ หรือข้อมูลทางการเงิน ซึ่งค่าบางค่ามีความสำคัญมากกว่าค่าอื่นๆ

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

พร้อมที่จะเริ่มสร้างทักษะ Excel ที่แข็งแกร่งแล้วหรือยัง ตรวจสอบ