วันพุธที่ 14 พฤศจิกายน พ.ศ. 2555

การใช้ IF กับ INT หาเงินทอนแบบแยกแบงค์แยกเหรียญ

การใช้ฟังก์ชัน IF กับ INT คำนวณเงินทอนหรือแลกเงินด้วยจำนวนธนบัตรและเหรียญเท่าที่จำเป็น





เรื่องใกล้ตัวที่บางครั้งก็ลืม อย่างการทอนเงินลูกค้า ซึ่งปัญหาที่แคชเชียร์มักเจอกันบ่อยๆ คือ ทอนเงินเกิน,ทอนผิด เพราะวันๆ ก็จับแต่เงินคนอื่น แต่พอหายขึ้นมากลับกลายเป็นเงินของเราเองซะงั้น
จากตัวอย่างรูป Figure A เป็นการ
หาเงินทอนแยกแบงค์แยกเหรียญ ด้วยโจทย์ดังนี้
  • ยอดรับเงิน 2,000 (เซลล์ C2)
  • หักค่าสินค้า 1,067.50 (เซลล์ D2)
  • เหลือเงินที่ต้องทอนให้ลูกค้า 932.75 (เซลล์ E2)

Figure A-calculate give change at least coins and bills needed
Figure A
จุดประสงค์ของการคำนวณเงินทอนก็เพื่อใช้คัดแยกเหรียญและธนบัตรให้เหลือจำนวนที่น้อยที่สุดในการทอนเงินแต่ละครั้ง ดังนั้นสูตรที่ใช้จึงเกี่ยวพันกันทุกประเภทเหรียญ/ธนบัตร และเนื่องจากหน่วยเงิน 1000 บาท เป็นรายการสุดท้ายของตาราง ดังนั้นสูตรชุดนี้จึงเหมือนกับรันย้อนขึ้นจากด้านล่าง

ฟังก์ชันที่ใช้กับตารางนี้มี 2 ฟังก์ชัน
  1. ฟังก์ชัน IF กำหนดเงื่อนไข
  2. ฟังก์ชัน INT ปัดเศษทศนิยมออก ไวยากรณ์ INT คือ INT(number)
Logic การใช้ IF ของสูตรชุดนี้ คือ ถ้าเงินที่ต้องทอนหักหน่วยเงินแล้วมากกว่า, เป็นจริง-ใช้ INT ปัดเศษผลลัพท์ จากเงินทอนรวม หักหน่วยเงินที่ใหญ่กว่า แล้วจึงคูณหน่วยเงิน ,ไม่จริง-เป็นศูนย์

RowColumn
B
C
D
E
F
1
รับเงิน
ค่าสินค้า
เงินทอน
2
2,000.00
1,067.25
932.75
    ส่วนต่างๆ ของตารางแสดงสูตร โดยตำแหน่งคอลัมท์และแถวที่ใช้ (กำหนดจาก FigureA)
  1. หน่วยเงิน (column C)แยกประเภทของเหรียญและธนบัตร เช่น เหรียญ 25 สตางค์, เหรียญ 5 บาท, ธนบัตรใบละ 50 บาท, ธนบัตรใบละ 100 บาท เป็นต้น
  2. สูตรในคอลัมท์เงินทอน แสดงการใช้ฟังก์ชัน ของ column E
  3. เงินทอน/บาท(column E) แสดงผลลัพท์ที่คำนวณได้
B C E F
3 หน่วยเงิน สูตรในคอลัมท์เงินทอน เงินทอน/
บาท
จำนวน
4 สต. 0.25 =IF($E$2-$E5>$C4,INT(($E$2-SUM($E5:$E$14))/C4)*$C4,0) 0.25 1
5 สต. 0.50 =IF($E$2-$E6>$C5,INT(($E$2-SUM($E6:$E$14))/C5)*$C5,0) 0.50 1
6 บาท 1 =IF($E$2-$E7>$C6,INT(($E$2-SUM($E7:$E$13))/C6)*$C6,0) 0
7 บาท 2 =IF($E$2-$E8>$C7,INT(($E$2-SUM($E7:$E$14))/C7*$C7,0) 2 1
8 บาท 5 =IF($E$2-$E9>$C8,INT(($E$2-SUM($E9:$E$14))/C8)*$C8,0) 0
9 บาท 10 =IF($E$2-$E10>$C9,INT(($E$2-SUM($E10:$E$14))/C9)*$C9,0) 10 1
10 บาท 20 =IF($E$2-$E11>$C10,INT(($E$2-SUM($E11:$E$14))/C10)*$C10,0) 20 1
11 บาท 50 =IF($E$2-$E12>$C11,INT(($E$2-SUM($E12:$E$14))/C11)*$C11,0) 0
12 บาท 100 =IF($E$2-$E13>$C12,INT(($E$2-SUM($E13:$E$14))/C12)*$C12,0) 400 4
13 บาท 500 =IF($E$2-$E14>$C13,INT(($E$2-SUM($E14:$E$14))/C13)*$C13,0) 500 1
14 บาท 1000 =IF($E$2>$C14,INT($E$2/$C14)*$C14,0) 0
15 รวมเงินทอน 932.75 10
จากภาพ Figure A และตารางตัวอย่าง
column F เป็นผลคำนวณหาจำนวนเหรียญ/ธนบัตร ที่ต้องทอนให้กับลูกค้า ในขณะที่ column E เป็นการคำนวณหาเงินทอนตามมูลค่าของเหรียญ/ธนบัตร ความแตกต่างของสองคอลัมท์นี้ มีเพียงแค่ตัวคูณของหน่วยเงินเท่านั้น คือ ตัวคูณมูลค่าของเหรียญหรือ ธนบัตร (ในที่นี้คือ คอลัมท์ C)
หมายเหตุ
  • ในเซลล์ E4 : =IF(E2-E5>C4,INT((E2-SUM(E5:E14))/C4)*C4,0)
  • ในเซลล์ F4 : =IF(E2-E5>C4,INT((E2-SUM(E5:E14))/C4),0)
  • ในคอลัมท์ C ที่เป็นค่าของเหรียญหรือธนบัตร หากใช้ custom format cells เราสามารถใส่หน่วย "สต." หรือ "บาท" ได้พร้อมทั้งแทนค่าในสูตรด้วยได้เลย โดยไม่ต้องแยกหน่วยที่คอลัมท์ B
  • ตารางที่แสดงด้านล่างได้เพิ่มหน่วยเงิน 2 บาทเข้าไป

Reactions:

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

  1. :) ทำแ้ล้วไม่ได้อ่ะครับ แล้วก็ ถ้ามีเหรียญ 2 บาทด้วยล่ะครับ เซลส์ 12 หายไปไหน เซลส์ 9 ซ้ำกันครับ

    ตอบลบ
    คำตอบ
    1. หลักของสูตรนี้คือยึดค่าเงินที่มากที่สุดก่อนคือ 1000 แล้วคำนวณย้อนขึ้นจำนวนน้อย

      ให้ลองก็อปปี้สูตรแล้ววางในเอกเซลชีทที่ต้องการทดสอบโดยวางให้ตรงกับคอลัมท์ที่ระบุไว้ คือ หน่วยเงิน ให้วางที่คอลัมท์ C และสูตรให้วางที่คอลัมท์ E โดยที่ให้ใส่มูลค่าเงินที่ต้องการทอนไว้ที่ E2 และที่เซลล์ E15กับ F15 ให้sum ผลรวมทดสอบด้วยค่ะ

      ตอนแรกที่คุณboukhum ทำไม่ได้คาดว่าเกิดจากหน่วยเงินในคอลัมท์ C ที่ใส่ไว้ ถ้าเอาหน่วยออกสูตรน่ารันได้เลย

      และขอบคุณมากที่แจ้งเรื่องข้อมูลซ้ำตอนนี้ได้แก้ไขแล้ว

      ลบ