“ช่วยเขียนสูตรคิดค่าคอมมิชชั่นแบบอัตโนมัติให้หน่อย”

คือคำร้องขอ (จริงๆคือคำสั่ง) จากภรรยาผม

39890918_s

“ใช้ IF ช่วยก็ได้นี่” ผมตอบด้วยเสียงง่วงๆ

“เงื่อนไขมันยิบย่อยตั้ง 9 ชั้นอ่ะ เขียนยังไงดี?”

“ห๊ะ!” ผมอุทานเสียงหลง

หลายคนคงทราบว่า ข้อจำกัดหนึ่งของฟังก์ชั่น IF ในเอ็กเซลคือ

ไม่สามารถเขียนซ้อนกันเกิน 7 ชั้นได้

แต่เคสของภรรยาผม ต้องเขียนสูตรเพื่อคำนวณค่าคอมมิชชั่นที่มีเงื่อนไขซับซ้อน

ประมาณว่า ยิ่งขายได้มากชิ้น อัตราค่าคอมมิชชั่นยิ่งเพิ่ม แต่อัตรานี้จะใช้ได้เฉพาะยอดขายช่วงนี้เท่านั้น ช่วงที่มากกว่านี้ต้องใช้อีกอัตราหนึ่ง

หรือก็คือคิดค่าคอมมิชชั่นแบบขั้นบันไดนั่นเอง

เงื่อนไขที่ว่าสามารถแสดงได้ดังตารางนี้ครับ

Commission Rate 9 Steps
คอมมิชชั่น 9 ชั้น

ขออธิบายตารางนี้เล็กน้อยนะครับ

สมมติว่า สาขา 1 ขายสินค้าได้ 137 ชิ้น

  • สินค้าชิ้นที่ 1-50 คิดค่าคอมมิชชั่นชิ้นละ 1 บาท
  • ชิ้นที่ 51-100 คิดค่าคอมมิชชั่นชิ้นละ 2 บาท
  • ชิ้นที่ 101-137 คิดค่าคอมมิชชั่นชิ้นละ 3 บาท

แปลว่าได้ค่าคอมมิชชั่นทั้งหมด

(50*1)+(50*2)+37*3 = 261 บาท

เหตุผลที่ต้องเขียนสูตรแบบอัตโนมัติ เพราะถ้าต้องคิดแบบนี้กับสาขาทั้งหมด 300 สาขา แค่นึกก็เซ็งชีวิตแล้ว..

สำหรับโจทย์ข้อนี้ ต่อให้เราสร้างคอลัมน์ทดอีกสองคอลัมน์ เพื่อรองรับการใช้ IF แบบ 9 เงื่อนไขได้ ผมก็ไม่แนะนำให้ใช้

ทำไมล่ะ?

ลองคิดถึงกรณีที่เงื่อนไขเปลี่ยนแปลงดูสิครับ การมานั่งแก้สูตร IF ซับซ้อนแบบนี้ไม่ใช่เรื่องสนุกเลย สูตรคงยาว 4-5 บรรทัด แถมมีโอกาสผิดพลาดสูงอีกต่างหาก

แล้วจะทำยังไงล่ะ?

เวลาเจอเคสทำนองนี้

ลองเขียนวิธีคิดในกระดาษดูก่อน

ประมาณว่า ถ้าเราต้องคิดด้วยมือ เราจะคิดยังไง?

จากโจทย์ยอดขาย 137 ชิ้น ถ้าผมเปลี่ยนวิธีการคำนวณเล็กน้อยเป็น

=3*(137101+1)+(50*2+50*1)

คำตอบก็ได้เท่าเดิมคือ 261 บาทใช่ไหมครับ

ลองแทนที่ตัวเลขสมมติที่เราเขียนไว้เมื่อกี๊ด้วยสูตรเอ็กเซล

สำหรับผมแล้ว เทคนิคนึงของการเขียนสูตรก็คือ

การแทนที่ตัวเลขสมมติด้วยสูตร!

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

จากโจทย์ข้อนี้ เจ้าตารางค่าคอมมิชชั่นถูกเขียนอยู่ในเซลล์ $A$5:$A$13

ข้อมูลยอดขายของแต่ละสาขาถูกเก็บไว้ในคอลัมน์ F และ G

ยอดขายแต่ละสาขา
ยอดขายแต่ละสาขา

ตัวเลขสมมติ 3 ในสูตรเมื่อสักครู่ หรือก็คือ อัตราค่าคอมมิชชั่นของยอดขาย 137 ชิ้น สามารถแทนที่ด้วยสูตร VLOOKUP(G5,$A$5:$C$13,3,1) ได้

วิธีนี้ก็คือการใช้ฟังก์ชั่น VLOOKUP แบบประมาณการ (Approximate) นั่นเอง เพื่อหาว่าค่าคอมมิชชั่นของยอดขายนั้นอยู่ในช่วงใด ถ้าสนใจรายละเอียดของวิธีนี้ คลิกอ่าน ที่นี่ ครับ

แทนที่ตัวเลขสมมติ 137 หรือก็คือ ยอดขายของสาขานั้น ด้วยสูตร G5 หรือก็ค่าในเซลล์ G5 ของข้อมูลยอดขายแตละสาขานั่นเอง

แทนที่เจ้า 101 หรือก็คือขอบล่างของช่วงขั้นบันได้ที่เจ้า 137 อยู่ ด้วย VLOOKUP(G5,$A$5:$A$13,1,1)

ถึงตรงนี้ หลายคนคงพอนึกออก แต่ปัญหาคือเจ้า (50*2+50*1) จะแทนที่ด้วยสูตรแบบอัตโนมัติยังไงดี?

ง่ายมากครับ!

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

หรือก็คือแบบนี้

Commission Accumulated Amount
คำนวณค่าคอมมิชชั่นสะสมของแต่ละขั้น

จากภาพ ถ้าอัตราค่าคอมมิชชั่นคือ 3 บาท แสดงว่ามียอดคอมมิชชั่นสะสมมาจาก 2 ชั้นก่อนหน้านี้เท่ากับ 150 บาท (50*2+50*1) หรือ แทนที่ 150 บาทได้ด้วยสูตร (B6-A6+1)*C6+D6

ถ้าอัตราค่าคอมมิชชั่นคือ 5 บาท แสดงว่ามียอดคอมมิชชั่นสะสมมาจาก 3 ชั้นก่อนหน้านี้ 300 บาท (50*3+50*2+50*1) หรือ แทนที่ได้ด้วยสูตร (B7-A7+1)*C7+D7

จริงๆแล้ว เราใส่สูตรแค่ครั้งเดียว แล้วลากสูตรในคอลัมน์ D จากบนลงล่างได้เลยนะครับ ^_^

พอเรามีค่าคอมมิชชั่นสะสมในแต่ละขั้นบันได้แล้ว สามารถแทนที่เจ้า (50*2+50*1) หรือ (50*3+50*2+50*1) ได้ด้วย VLOOKUP(G5,$A$5:$D$13,4,1)

ณ จุดๆนี้ (สำนวนพริตตี้เลยนะนี่) เราแทนที่ค่าทั้งหมดด้วยสูตรได้แล้วใช่ไหมครับ

รอช้าอยู่ไย จับมันมาแทนที่ในสมการที่เราเขียนไว้ตอนแรก จาก

=3*(137101+1)+(50*2+50*1)

เป็น

=VLOOKUP(G5,$A$5:$C$13,3,1)*(G5VLOOKUP(G5,$A$5:$A$13,1,1)+1)+VLOOKUP(G5,$A$5:$D$13,4,1)

จากนั้นก็ดับเบิ้ลคลิกเจ้ากล่องดำเล็กที่มุมขวาล่างเพื่อก็อปปี้สูตรในคอลัมน์ H

Fill Formula
Fill Formula

ปรี๊ด… คำตอบออกมาแว๊ววว!

ต่อให้มี 3,000 สาขาก็ไม่หวั่น

สำหรับบางคนที่อยากเห็นตัวอย่างในลักษณะของไฟล์เอ็กเซล สามารถดาวน์โหลดได้เลยครับ

Commisssion9StepsCalculation_151023

โดยส่วนตัวแล้ว ประเด็นเรื่องการใช้ IF ได้ไม่เกิน 7 ชั้น คือ “ข้อเตือนใจ” ไม่ใช่ “ข้อจำกัด”

ที่ว่าเป็น “ข้อเตือนใจ” เพราะ ถ้าเรากำลังใช้ IF 8 ชั้น

ลองคิดด้วยวิธีอื่นดีไหม?

อาจมีวิธีที่ง่ายกว่าการใช้ IF ก็เป็นได้

ถ้าโจทย์ข้อนี้เกิดมีเงื่อนไขทั้งหมด 20 ชั้น ต่อให้เราสร้างคอลัมน์ทดอีก 3 คอลัมน์เพื่อช่วยคำนวณ

สุดท้ายเราจะงงเอง!

ยิ่งถ้าเงื่อนไขเปลี่ยนแปลงขึ้นมานี่ อาจมีเคืองกันได้

จริงๆแล้ว โจทย์ข้อนี้สามารถคิดได้หลายวิธีนะครับ วิธีของผมเป็นหนึ่งในหลายวิธีเท่านั้น ซึ่งอาจไม่ใช่วิธีที่ดีที่สุด

อย่างไรก็ตาม หากเราเปลี่ยนวิธีการคำนวณเป็นวิธีนี้ ต่อให้มีคอมมิชชั่น 20 ชั้น ก็ไม่มีปัญหา

แต่อย่าตั้งให้มี 20 ชั้นเลยครับ แทนที่จะเพิ่มกำลังใจให้พนักงานขาย เดี๋ยวจะกลายเป็นบั่นทอนซะก่อน…

หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่

อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^_^