วันอังคารที่ 21 กันยายน พ.ศ. 2553

การใช้งาน Excel:ประยุกต์สูตร Excel:VLOOKUP คำนวณค่าคอมมิชชั่นในด้านการตลาด

สวัสดีครับท่านผู้อ่าน How to excel ครับ บทความนี้ขอนำเสนอการใช้สูตร Excel : VLOOKUP มาประยุกต์ใช้กับการคำนวณค่าคอมมิชชั่น ตามปกติ พนักขายทุกท่านจะได้รับค่าคอมมิชชั่นในการขายผลิตภัณฑ์ตามเป้าหมายที่บริษัทตั้งไว้ ตัวอย่างเช่น

หากขายได้ไม่เกิน 100,000 บาทจะได้ค่าคอมมิชชั่น 5% ของยอดขาย

หากขายได้มากกว่า 100,000 แต่ไม่เกิน 200,000 บาท จะได้ค่าคอมมิชชั่น 10% ของยอดขาย

หากขายได้มากกว่า 200,000 แต่ไม่เกิน 500,000 บาท จะได้ค่าคอมมิชชั่น 15% ของยอดขาย

หากขายได้มากกว่า 500,000 บาท แต่ไม่เกิน 750,000 บาท จะได้ค่าคอมมิชชั่น 20% ของยอดขาย

หากขายได้มากกว่า 750,000 บาท แต่ไม่เกิน 1,000,000 บาท จะได้ค่าคอมมิชชั่น 25% ของยอดขาย

และหากขายได้มากกว่า 1,000,000 บาท จะได้ค่าคอมมิชชั่น 30% ของยอดขาย

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

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

TableLookup ภาพที่ 1 ตารางเงื่อนไขค่าคอมมิชชั่น

จะเห็นว่าผมเรียงลำดับขอบเขตล่างของยอดขายในแต่ละระดับจากน้อยไปหามากแล้ว เพื่อให้เราสามารถใช้เงื่อนไข TRUE ของสูตร Excel : VLOOKUP ได้อย่างถูกต้อง เอาหล่ะครับต่อไปเราก็เขียนสูตร Excel เพื่อคำนวณค่าคอมมิชชั่นและตัดเกรดได้ดังนี้ครับ สมมุติผมให้ รหัสพนักงาน ชื่อพนักงาน และยอดขายของพนักงาน อยู่ในหลัก D, E, F ตามลำดับ หากผมให้ค่าคอมมิชชั่นที่ถูกคำนวณเก็บไว้ในหลัก G และเกรดพนักงานดีเด่นอยู่ในหลัก H สมมุติกำหนดให้ข้อมูลเริ่มจากแถวที่ 3 เป็นต้นไป เราจะเขียนสูตร Excel : VLOOKUP ได้ดังนี้

G2 = VLOOKUP(F2,$A$4:$C$8,2,1)*F2

H2 = VLOOKUP(F2,$A$4:$C$8,3,1)

ทำการ Autofill ให้ครบจะได้ผลลัพธ์ดังแสดงในภาพที่ 2

Commission

ภาพที่ 2 ผลการคำนวณค่าคอมมิชชั่นและการจัดระดับพนักงานขาย

หากต้องการหายอดขายสูงสุดหรือต่ำสุดก็สามารถทำได้

ยังมีวิธีการคำนวณค่าคอมมิชชั่นอีกรูปแบบหนึ่งที่น่าสนใจ ดูได้ที่นี่ครับ

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

การทำซ้ำด้านบนเพื่อพิมพ์หัวตารางแบบซ้ำๆกันในทุกหน้า (Repeat a Row)

VLOOKUP การตัดเกรด

การค้นหาค่า Max$Min(Custom Lookup)

การสร้างแผนภูมิในรายงานด้วยคำสั่ง REPT

1 ความคิดเห็น:

nickycama กล่าวว่า...

ได้ความรู้ดีค่ะ ไม่ค่อยเก่งตัวเลขเท่าไหร่

แสดงความคิดเห็น

Yahoo bot last visit powered by  Ybotvisit.com