“ช่วยเขียนสูตรคิดค่าคอมมิชชั่นแบบอัตโนมัติให้หน่อย” คือคำร้องขอ (จริงๆคือคำสั่ง) จากภรรยาผม
“ใช้ IF ช่วยก็ได้นี่” ผมตอบด้วยเสียงง่วงๆ
“เงื่อนไขมันยิบย่อยตั้ง 9 ชั้นอ่ะ เขียนยังไงดี?” ภรรยาโอดครวญ
“ห๊ะ!” ผมอุทานเสียงหลง
เงื่อนไข 9 ชั้น ถ้าคิดแบบง่ายๆ แปลว่า ต้องใช้ IF 8 ตัว!
การเขียน IF ซ้อนกัน 8 ชั้นไม่ใช่เรื่องสนุกเลย มีโอกาสสูงมากที่จะเขียนผิด วงเล็บไม่ครบมั่ง ลืมคอมม่ามั่ง ลืมใส่เงื่อนไขมั่ง
แค่คิดก็เศร้าแล้ว T_T
เคสนี้ต้องคำนวณค่าคอมมิชชั่นที่เงื่อนไขซับซ้อน
ประมาณว่า ยิ่งขายได้มากชิ้น อัตราค่าคอมมิชชั่นยิ่งเพิ่ม แต่อัตรานี้จะใช้ได้เฉพาะยอดขายช่วงนี้เท่านั้น ช่วงที่มากกว่านี้ต้องใช้อีกอัตราหนึ่ง
หรือก็คือคิดค่าคอมมิชชั่นแบบขั้นบันไดนั่นเอง
เงื่อนไขที่ว่าสามารถแสดงได้ดังตารางนี้ครับ
ขออธิบายตารางนี้เล็กน้อยนะครับ
สมมติว่า สาขา 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*(137–101+1)+(50*2+50*1)
คำตอบก็ได้เท่าเดิมคือ 261 บาทใช่ไหมครับ
เทคนิคนึงของการเขียนสูตรก็คือ
การแทนที่ตัวเลขสมมติด้วยสูตร!
เพราะเวลาเราคำนวณจริง เราไม่สามารถใช้เลขนั้นได้ตลอดเวลา ต้องเปลี่ยนเป็นสูตรที่สามารถแปรเปลี่ยนตามค่าอ้างอิงได้
จากโจทย์ข้อนี้ เจ้าตารางค่าคอมมิชชั่นถูกเขียนอยู่ในเซลล์ $A$5:$A$13
ข้อมูลยอดขายของแต่ละสาขาถูกเก็บไว้ในคอลัมน์ F และ G
ตัวเลขสมมติ 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) จะแทนที่ด้วยสูตรแบบอัตโนมัติยังไงดี?
ง่ายมากครับ!
เพียงใช้ประโยชน์จากเจ้าตารางค่าคอมมิชชั่นเพิ่มอีกนิดหน่อย โดยสร้างสูตรคำนวณคอมมิชชั่นสะสมของแต่ละชั้นขึ้นมา
หรือก็คือแบบนี้
จากภาพ ถ้าอัตราค่าคอมมิชชั่นคือ 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*(137–101+1)+(50*2+50*1)
เป็น
=VLOOKUP(G5,$A$5:$C$13,3,1)*(G5–VLOOKUP(G5,$A$5:$A$13,1,1)+1)+VLOOKUP(G5,$A$5:$D$13,4,1)
จากนั้นก็ก็อปปี้สูตร (คอลัมน์ H)
ได้คำตอบแว๊ววว!
ต่อให้มี 3,000 สาขาก็ไม่หวั่น ^__^
สำหรับบางคนที่อยากเห็นตัวอย่างในลักษณะของไฟล์เอ็กเซล สามารถดาวน์โหลดได้เลยครับ
Commisssion9StepsCalculation_151023
Excel เวอร์ชันเก่า (ตั้งแต่ 2003 ลงไป) มีข้อจำกัดหนึ่งที่ว่า ใช้ IF ซ้อนกันได้ไม่เกิน 7 ชั้น
(เวอร์ชันใหม่ๆเขียน IF ซ้อนกันได้ถึง 64 ชั้น)
โดยส่วนตัวแล้ว ผมกลับมองว่า ข้อจำกัดที่ไม่ให้เขียน IF ซ้อนกันเกิน 7 ชั้นอาจเป็นสิ่งที่ถูกต้องแล้ว
เพราะถ้าเรากำลังใช้ IF ซ้อนกัน 8 ชั้น อาจเป็นสัญญาณเตือนว่า
ลองคิดด้วยวิธีอื่นดีไหม?
อาจมีวิธีที่ดีกว่า IF ก็เป็นได้
ยิ่งถ้าเจอเคสเงื่อนไข 20 ชั้น ต่อให้เขียนสูตรโดยใช้ IF ซ้อนกันไปเรื่อยๆเพื่อแก้ปัญหาได้จริง
สุดท้ายเราจะงงเอง!
ยิ่งถ้าเงื่อนไขเปลี่ยนแปลงขึ้นมา มีการตบตีกันระหว่างคนคิดเงื่อนไขกับคนเขียนสูตรแน่นอน
แต่ถ้าเปลี่ยนเป็นวิธีของผม ต่อให้มีคอมมิชชั่น 20 ชั้น ก็ไม่ใช่ปัญหา ^__^
(โจทย์ข้อนี้คิดได้หลายวิธี วิธีของผมเป็นหนึ่งในหลายวิธีเท่านั้น)
แต่อย่าให้มีตั้ง 20 ชั้นเลยครับ แทนที่จะเพิ่มกำลังใจให้พนักงานขาย เดี๋ยวจะบั่นทอนซะก่อน ^^
.
หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่
อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^__^
