วันพฤหัสบดีที่ 7 พฤศจิกายน พ.ศ. 2556

การใช้ excel : เทคนิคการคำนวณค่าเฉลี่ย KPI

การใช้ excel ในบทความนี้จะนำเสนอเทคนิคการหาค่าเฉลี่ยข้อมูล ในปัจจุบันการประเมินผลงานด้วย KPI ถูกนำมาใช้อย่างแพร่หลายในองค์กรต่างๆไม่ว่าจะของรัฐ ของเอกชน หากท่านได้รับมอบหมายให้ทำการหาค่าเฉลี่ยประสิทธิภาพของ KPI ใน 5 อันดับสูงสุด จาก KPI ของคนเป็นร้อยๆคน จะทำอย่างไร วิธีที่ง่ายที่สุดสำหรับผู้ใช้ excel คือใช้คำสั่งเรียงลำดับข้อมูลตามผลของ KPI จากนั้นก็ใช้สูตร excel : average และเลือกข้อมูล KPI 5 อันดับแรก ก็จะได้ค่าเฉลี่ยค่า KPI  5 อันดับสูงสุดแล้ว แต่ลองพิจารณาดูให้ดี หากมีหัวข้อ KPI ที่ต้องประเมินมากๆ และท่านผู้บริหารต้องการค่าเฉลี่ย KPI ใน 5 อันดับสูงสุดของ KPI แต่ละตัว การทำวิธีการนี้คงยุ่งยากพอควร เรามาดูวิธีการประยุกต์สูตร Excel เพื่อแก้ปัญหานี้กันครับ
เราจะใช้สูตร 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 อย่างไร สวัสดีครับ






Yahoo bot last visit powered by  Ybotvisit.com