อยากคิดค่าคอมมิชชั่นแบบอัตโนมัติ แต่เงื่อนไขซอยย่อยตั้ง 9 ชั้น ทำไงดี? [Advanced VLOOKUP]

“ช่วยเขียนสูตรคิดค่าคอมมิชชั่นแบบอัตโนมัติให้หน่อย” คือคำร้องขอ (จริงๆคือคำสั่ง) จากภรรยาผม

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

“เงื่อนไขมันยิบย่อยตั้ง 9 ชั้นอ่ะ เขียนยังไงดี?” ภรรยาโอดครวญ

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

เงื่อนไข 9 ชั้น ถ้าคิดแบบง่ายๆ แปลว่า ต้องใช้ IF 8 ตัว!

การเขียน IF ซ้อนกัน 8 ชั้นไม่ใช่เรื่องสนุกเลย มีโอกาสสูงมากที่จะเขียนผิด วงเล็บไม่ครบมั่ง ลืมคอมม่ามั่ง ลืมใส่เงื่อนไขมั่ง

แค่คิดก็เศร้าแล้ว T_T

เคสนี้ต้องคำนวณค่าคอมมิชชั่นที่เงื่อนไขซับซ้อน

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

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

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

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 เลย

ทำไมล่ะ?

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

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

เวลาเจอเคสทำนองนี้ ลองเขียนวิธีคิดในกระดาษดูก่อน ประมาณว่า ถ้าเราต้องคิดด้วยมือ เราจะคิดยังไง?

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

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

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

เทคนิคนึงของการเขียนสูตรก็คือ

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

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

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

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

CommissionRate_9Steps_SalesByBranch_171208

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

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

(ถ้าสนใจรายละเอียดของ VLOOKUP แบบ Approximate Match คลิกอ่าน ที่นี่ ครับ)

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

แทนที่ 101 หรือขอบล่างของช่วงขั้นบันไดด้วย 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)

CommissionRate_9Steps_FillFormula_171208

ได้คำตอบแว๊ววว!

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

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

Commisssion9StepsCalculation_151023

Excel เวอร์ชันเก่า (ตั้งแต่ 2003 ลงไป) มีข้อจำกัดหนึ่งที่ว่า ใช้ IF ซ้อนกันได้ไม่เกิน 7 ชั้น

(เวอร์ชันใหม่ๆเขียน IF ซ้อนกันได้ถึง 64 ชั้น)

โดยส่วนตัวแล้ว ผมกลับมองว่า ข้อจำกัดที่ไม่ให้เขียน IF ซ้อนกันเกิน 7 ชั้นอาจเป็นสิ่งที่ถูกต้องแล้ว

เพราะถ้าเรากำลังใช้ IF ซ้อนกัน 8 ชั้น อาจเป็นสัญญาณเตือนว่า

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

อาจมีวิธีที่ดีกว่า IF ก็เป็นได้

ยิ่งถ้าเจอเคสเงื่อนไข 20 ชั้น ต่อให้เขียนสูตรโดยใช้ IF ซ้อนกันไปเรื่อยๆเพื่อแก้ปัญหาได้จริง

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

ยิ่งถ้าเงื่อนไขเปลี่ยนแปลงขึ้นมา มีการตบตีกันระหว่างคนคิดเงื่อนไขกับคนเขียนสูตรแน่นอน

แต่ถ้าเปลี่ยนเป็นวิธีของผม ต่อให้มีคอมมิชชั่น 20 ชั้น ก็ไม่ใช่ปัญหา ^__^

(โจทย์ข้อนี้คิดได้หลายวิธี วิธีของผมเป็นหนึ่งในหลายวิธีเท่านั้น)

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

.

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

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

วิศวกรรีพอร์ต

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.