เราจะใช้สูตร excel : LARGE() เข้ามาช่วยสูตร excel : AVERAGE()
แนวคิดคือเราจะใช้ สูตร excel : LARGE() ในการค้นหาค่ามากสุด 5 ลำดับแรก และให้ส่งกลับทั้ง 5 ค่ามาที่สูตร excel : AVERAGE() เพื่อหาค่าเฉลี่ยต่อไป
สมุติมีข้อมูล KPI อยู่ 10 ตัว วางอยู่ในหลัก A ดังรูป เราสามารถเขียนสูตร excel ได้ดังนี้
= AVERAGE(LARGE(A1:A10,{1,2,3,4,5}))
ผลการคำนวณค่าเฉลี่ย KPI ที่มีค่าสูงสุด 5 ลำดับแรก
จากสูตรนี้เราก็สามารถนำไปใช้คำนวณค่าเฉลี่ย KPI ตัวอื่นๆได้อย่างรวดเร็วครับ
ก่อนจบบทความนี้มีคำถามเพิ่มเติมก่อนจะมาเฉลยต่อไปว่า หากเราจะทำให้สูตรหาค่าเฉลี่ย KPI ตัวนี้มีความยืดหยุ่นมากยิ่งขึ้น เช่น หากข้อมูล KPI มีเพิ่มขึ้นหรือลดลงจาก 10 ค่า, หากต้องการหาค่าเฉลี่ย KPI ที่มีค่าสูงสุด n ลำดับแรก หรือหากต้องการหาค่าเฉลี่ย KPI ที่มีค่าน้อยสุด n ลำดับแรก เราจะต้องปรับสูตร excel อย่างไร แนะนำให้ไปดู การสร้างรายการแบบไดนามิค
ท่านสามารถนำเทคนิคนี้ไปประยุกต์กับการหาค่าเฉลี่ยของข้อมูลอื่นๆได้อีกครับ สวัสดีครับ
มาดูเพิ่มเติมจากคำถามเรื่องการปรับสูตรให้ยืดหยุ่นกันต่อครับ วันนี้มาตอบคำถาม 2 เรื่อง
เรื่องแรก หากข้อมูล KPI มีเพิ่มขึ้นหรือลดลงจาก 10 ค่า เราจะใช้การสร้างรายการแบบไดนามิคในส่วนของช่วงข้อมูล จะได้สูตร excel ดังนี้
AVERAGE(LARGE(OFFSET(A:A,0,0,COUNT(A:A),1),{1,2,3,4,5}))
และหากต้องการหาค่าเฉลี่ย KPI ที่มีค่าน้อยสุด 5 ลำดับแรก เราจะใช้ สูตร excel: SMALL แทน LARGE
AVERAGE(SMALL(OFFSET(A:A,0,0,COUNT(A:A),1),{1,2,3,4,5}))
พิจารณาจากสูตร excel ท่านจะพบว่ามีความยาวพอสมควร ท่านผู้อ่านสามารถใช้วิธีการสร้าง ตัวแปรแบบไดนามิค และนำเข้ามาใช้ในสูตร excel ดังกล่าวได้ (แทนที่ OFFSET(A:A,0,0,COUNT(A:A),1) ด้วยตัวแปรไดนามิค)
ผลจากการปรับสูตร excel ให้ยืดหยุ่นขึ้น
คำถามเพิ่มเติม หากข้อมูลมีน้อยกว่า 5 เราจะปรับสูตร excel อย่างไร สวัสดีครับ
ไม่มีความคิดเห็น:
แสดงความคิดเห็น