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

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

เรื่องใกล้ตัวที่บางครั้งก็ลืม อย่างการทอนเงินลูกค้า ซึ่งปัญหาที่แคชเชียร์มักเจอกันบ่อยๆ คือ ทอนเงินเกิน,ทอนผิด เพราะวันๆ ก็จับแต่เงินคนอื่น แต่พอหายขึ้นมากลับกลายเป็นเงินของเราเองซะงั้น

จากตัวอย่างรูป Figure A เป็นการ
หาเงินทอนแยกแบงค์แยกเหรียญ ด้วยโจทย์ดังนี้

• ยอดรับเงิน 2,000 (เซลล์ C2)
• หักค่าสินค้า 1,067.50 (เซลล์ D2)
• เหลือเงินที่ต้องทอนให้ลูกค้า 932.75 (เซลล์ E2)

Figure A

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

---

ฟังก์ชันที่ใช้กับตารางนี้มี 2 ฟังก์ชัน

1. ฟังก์ชัน IF กำหนดเงื่อนไข
2. ฟังก์ชัน INT ปัดเศษทศนิยมออก ไวยากรณ์ INT คือ INT(number)

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

Row	Column
	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 บาทเข้าไป

การใช้ฟังก์ชัน IFERROR กับ VLOOKUP

การพัฒนาฟีเจอร์ต่างๆ ของ MS Office ทำให้เกิดฟังก์ชันใหม่ๆ ที่ใช้งานง่ายขึ้นและสามารถใช้ตรวจสอบความถูกต้องได้ดีขึ้น
โดยที่เวอร์ชั่นเก่าจะต้องใช้หลายฟังก์ชันซ้อนกัน และฟังก์ชัน IFERROR ก็เป็นหนึ่งฟังก์ชันที่พูดถึง

IFERROR ทำหน้าที่คล้าย IF เพียงแต่เพิ่มศักยภาพการจัดการกับข้อมูลได้กว้างขึ้นและตรวจสอบความถูกต้องไปพร้อมๆ กัน

ไวยากรณ์และความหมาย

IFERROR(value,value_if_error)

• value คืออาร์กิวเมนต์ที่ใช้ตรวจสอบเพื่อหาข้อผิดพลาด
• value_if_error คือค่าที่จะส่งกลับถ้า value ได้ผลลัพท์เป็นค่าความผิดพลาด(Error_val) และชนิดของค่าความผิดพลาดที่จะทำให้ฟังก์ชันส่งคืนค่า value_if_error มีอยู่ 7 ชนิด

  #N/A #VALUE! #REF! #DIV/0! #NUM! #NAME? #NULL!

---

โจทย์ตัวอย่างทดสอบการใช้ IFERROR

Row	Column
2	A	B	C	D	E
3	Product	Price	Dis.	Dis.Price	Formula in Dis.Price
4	Ice-Cream Maker C30	5,400	3%	5,238	=IFERROR(B4-(B4*C4),B4)
5	Ice-Cream Maker C45	9,100	5%	8,645	=IFERROR(B5-(B5*C5),B5)
6	Ice-Cream Maker C50	17,900	7%	16,647	=IFERROR(B6-(B6*C6),B6)
7	Mini Prep Processor	3,600		3,600	=IFERROR(B7-(B7*C7),B7)
8	Soup Blender SB30	10,000	3%	9,700	=IFERROR(B8-(B8*C8),B8)
9	Stick hand Blender	3,000		3,000	=IFERROR(B9-(B9*C9),B9)
10	Waffle Maker WF20	5,800	2%	5,684	=IFERROR(B10-(B10*C10),B10)

จากตัวอย่างกำหนดให้ Dis.Price เป็นราคาสุทธิที่หักส่วนลด (Dis.) ตามเปอร์เซนต์ที่กำหนดไว้โดยให้ Price เป็นราคาฐานที่่ใช้คำนวณส่วนลด และถ้าสินค้าชนิดใดไม่มีส่วนลด (Dis.) ให้ใช้ราคาปกติ (Price) ซึ่งจะแสดงสูตรที่ใช้ในคอลัมท์ Formula in Dis.Price

นอกจากนี้ถ้าเราใช้ IFFEROR ร่วมกับ Vlookup เพื่อให้สูตรคืนค่าผลลัพท์จากสองฐานข้อมูลได้ในเวลาเดียวกัน ซึ่งจะเป็นข้อมูลจาก Worksheet เดียวกัน หรือ ต่าง Worksheet ก็ได้

---

ตัวอย่างถัดไปเป็นการทดสอบ IFERROR ร่วมกับ VLOOKUP
เราจะใช้ข้อมูลจากรูปเป็นฐานข้อมูล ซึ่งข้อมูลด้านซ้ายเป็นจำนวนสต็อคสินค้าของ LED TV และด้านขวาเป็นจำนวนสต็อคสินค้าของ Kitchenware

Row Column 2 A B C 3 LED TV QTY 4 1 LG 300 5 2 SAMSUNG 250 6 3 PANASONIC 120 7 4 BRAVIA 90 8 5 TOSHIBA 560 9 6 SUNYO 50 10 11 12

E F G KICHENWARE QTY 1 OTTO 1,110 2 IMARFLEX 500 3 HOUSE WORTH 459 4 MISAWA 50 5 VICTOR 89 6 SHARP 300 7 PHILIPS 70 8 DEBRANDT 79 9 CUISINART 250

เราจะใช้การค้นหาจำนวนสต็อคของสินค้า 2 หมวดนี้ โดยใช้ฟังก์ชัน IFERROR ที่ โดยใช้เซลล์ A13 เป็นตัวกำหนดเงื่อนไข ให้หาจำนวนสินค้าในสต็อคแบรนด์ Cuisinart ว่ามีจำนวนกี่หน่วย ซึ่งผลลัพท์เท่ากับ 250 ที่เซลล์ B13 และ C13 เป็นรูปแบบฟังก์ชัน IFERROR ที่ใช้ใน B13
และ ที่ เซลล์ A14 เป็นการทดสอบสูตรแบบเดียวกันเพียงแต่เปลี่ยนเป็น แบรนด์ LG ได้คำตอบ 300 หน่วย (B14), แสดงสูตรที่ (C14)

Row	Column
11	A	B	C
12	Brand	Qty.	Formula in Qty. (Column B)
13	CUISINART	250	=IFERROR(VLOOKUP(A13,B4:C9,2,FALSE),(VLOOKUP(A13,F4:G12,2,FALSE)))
14	LG	300	=IFERROR(VLOOKUP(A14,B4:C9,2,FALSE),(VLOOKUP(A13,F4:G12,2,FALSE)))

วิธีเพิ่ม check box ใน Excel 2007

การเพิ่ม Check boxes ใน Excel Data Sheet

การเพิ่ม checkbox เป็นฟีเจอร์ในกลุ่ม Form Control ของ Excel ซึ่งนอกจาก Checkbox แล้วยังมีรูปแบบอื่นๆ เช่น combobox, option box etc.)

ที่มีคุณสมบัติใช้สำหรับอ้างอิงค่าตรรกจริงหรือเท็จ True/False

ซึ่งสามารถนำไปใช้ร่วมกับฟังก์ชันอื่นๆ นอกจากนี้ยังสามารถใส่ Check boxes หลายๆๆๆ อัน ในหนึ่ง Data sheet

ก่อนอื่นสิ่งแรกที่ต้องทำคือเราต้องเปิดใช้งาน Developer Tab วิธีทำตามขั้นตอนนี้

1. เปิด MS Excel
2. แล้วคลิกที่ Office Button ซึ่งอยู่มุมบนซ้ายสุด
3. Excel จะแสดงรายการใต้ Office Button ทั้งหมด
4. คลิกที่ Excel Options ที่อยู่ด้านล่าง Recent documents ตามรูป

เข้าหัวข้อPopular

ใต้ตัวเลือก Top options for working with Excel

ให้เลือกติ๊กตัวเลือก

Show Developer tab in the Ribbon

จากนั้นคลิกปุ่ม OK

เมื่อกลับมาที่หน้า Excel Worksheet จะเห็นเมนูแทป Developer เพิ่มขึ้นมาหลังเมนู View

Developer นี้เป็นเมนูแทป ที่ช่วยให้เราสร้างแบบฟอร์มต่างๆ ใน Excel ได้ง่ายและรวดเร็วขึ้น

---

ต่อไปเป็นการเพิ่ม Check Box ใน Excel Data sheet

1. เลือกเมนู Developer คลิก Insert เลือก Check box จาก Form Controls
   
   


2. วางลงที่เซลส์ใดที่ต้องการ จากนั้น คลิกเม้าส์ขวา(secoundary menu)เลือก Format Control ซึ่งอยู่ล่างสุด
   
   


3. ใส่เซลส์ที่ต้องการ link กับ check box ในช่อง Cell Link ซึ่งเมื่อติ๊กจะแสดงเป็น TRUE และ เอาตัวเช็คออก จะแสดงเป็น FALSE หรือง่ายกว่านั้นคือ เอาเมาส์คลิกที่เซลส์ที่ต้องการให้ Link
   
   


4. ภาพตัวอย่างการแสดงผลของ check box
   
   

การใช้ IF ใน Excel - Excel Condition Function

IF ฟังก์ชันที่คืนค่าตามเงื่อนไข - Condition Function IF

IF เป็นตัวค้นหาที่มี 2 ทางเลือกคือถูกกับผิด

การทำงานของ IF เหมือนกับการตั้งใจซื้อของขวัญเป็นชุดกาแฟสักชุดแล้วมีเงื่อนไข(IF)คือ
1)ชุดกาแฟนี้ต้องไม่แพงเกิน 1000 บาท 2)จ้างเขาห่อเป็นของขวัญด้วยเลย ซึ่งเราจะใช้ IF ตรวจสอบเงื่อนไขที่ 1 ว่าเป็นจริงหรือไม่ (TRUE/FALSE) แล้วค่อยทำตามเงื่อนไขที่ 2

นั่นก็คือ ถ้าชุดกาแฟราคา 999 บาท คือเงื่อนไขเป็นจริง(True)ก็ให้ทำตามทางเลือกที่เป็นจริง, แต่ถ้าชุดกาแฟนี้แพงกว่า 1000 คือเงื่อนไขราคาเป็นเท็จ (False)ก็ให้ทำตามทางเลือกที่เป็นเท็จ

ไวยากรณ์

=IF(logical_test,[value_if_true],[value_if_false])

ซึ่งแต่ละตัว มีความหมายตามนี้

• logical_test: ค่าหรือเงื่อนไขใดๆ ที่กำหนด และสามารถใช้ประเมิน เพื่อ คืนค่า จริง หรือ เท็จ
• value_if_true: คืนค่า ผลลัพท์ ถ้าเงื่อนไขจาก logical_test เป็นจริง
• Value_if_false:คืนค่า ผลลัพท์ ถ้าเงื่อนไขจาก logical_test เป็นเท็จ

Figure A

Remarks:

1. จากรูป Figure A เงื่อนไขคือ ถ้าคะแนนสอบ (คอลัมท์ C) มากกว่าหรือเท่ากับ 50 ให้ฟังก์ชัน IF คืนค่าคำว่า "สอบผ่าน" แต่ถ้าคะแนน (คอลัมภ์ C) น้อยกว่า 50 ให้คืนค่าคำว่า "ต้องซ่อม"
2. เป็นการใช้ฟังก์ชัน IF แบบชั้นเดียว
3. Column D เป็นผลลัพท์ของฟังก์ชัน IF ส่วน Column E แสดงรูปแบบฟังก์ชั่นที่ใช้

---

ตัวอย่างประยุกต์การใช้งาน

จะใช้ข้อมูลตารางด้านล่างที่แสดงอัตราการจ่ายค่าคอมมิชชั่นให้กับพนักงานขายแบบอัตราก้าวหน้า มีเงื่อนไขการจ่ายแบบขั้นบันได กำหนดด้วยปริมาณหน่วยที่ขายได้ในแต่ละเดือน และ แสดงยอดขายของ Sales ไว้ 5 คน คือ Yuta, Tanaka, Adrain, Tom และ Benny การหา Rate คอมมิชชั่น ไม่ยาก แต่หากข้อมูลพนักงาน มีจำนวนมากๆ การใช้ IF จะช่วยให้ง่ายและรวดเร็วกว่า

Row	Column
	A	B	C	D	E	F
1	เป้าขายอัตราก้าวหน้า		Commission	พนักงานขาย	ยอดขายจริง	Rate ที่ได้
2	หน่วย		Rate.%	ชื่อ	หน่วย	เปอร์เซนต์
3	1	- 6	5%	Yuta	301	8%
4	7	- 30	6%	Tanaka	170	7.5%
5	31	- 100	7%	Adrian	78	7%
6	101	- 300	7.50%	Tom	6	5%
7	301	+	8%	Benny	30	6%

• ถ้าขายได้ช่วง 1-6 หน่วยค่าคอม= 5%, ถ้าขายได้ 32 หน่วยจะได้ค่าคอมช่วง 31-100 คือ 7%
• กำหนดให้  Column A และ Column B เป็นเงื่อนไข
• เงื่อนไขมากกว่า 1 เรื่องต้องปรับใช้ IF ซ้อน IF 

D	E	F
ชื่อ	หน่วย	แสดงการแทนค่าฟังก์ชันของคอลัมท์ F (Rate ที่ได้)
Yuta	301	=IF(E3>=A7,C7,IF(E3>=A6,C6,IF(E3>=A5,C5,IF(E3>=A4,C4,IF(E3>=A3,C3,0)))))
Tanaka	170	=IF(E4>=A7,C7,IF(E4>=A6,C6,IF(E4>=A5,C5,IF(E4>=A4,C4,IF(E4>=A3,C3,0)))))
Adrian	78	=IF(E5>=A7,C7,IF(E5>=A6,C6,IF(E5>=A5,C5,IF(E5>=A4,C4,IF(E5>=A3,C3,0)))))
Tom	6	=IF(E6<A4,C3,IF(E6<A5,C4,IF(E6<A6,C5,IF(E6<A7,C6,IF(E6>=A7,C7)))))
Benny	30	=IF(E7<A4,C3,IF(E7<A5,C4,IF(E7<A6,C5,IF(E7<A7,C6,IF(E7>=A7,C7)))))

Remarks:

1. Rate ที่ได้ (Column F) คือผลลัพท์ของ Commission Rate พนักงานแต่ละคน ซึ่งแสดงการแทนค่าสูตรฟังก์ชันไว้ที่ตารางที่สอง
2. ตารางที่สองใช้ฟังก์ชั่น IF(logical_test,[value_if_true],[value_if_false]) ซ้อนกัน 5 ชั้น ซึ่งต้องปรับเป็น IF(logical_test,[value_if_true], IF(logical_test,[value_if_true], IF(logical_test,[value_if_true],........ [value_if_false])))))
3. การซ้อนฟังก์ชั่น IF คือวางสูตรถัดไปไว้หลัง เงื่อนไข TRUE หรือจำง่ายๆ คือหลัง comma ตัวที่สอง และการปิดวงเล็บด้านขวาต้องครบ แต่บางครั้งเมื่อ Enter, Excel มักช่วยเติมให้เอง
4. ฟังก์ชั่น IF สามารถพลิกแพลงการกำหนดเงื่อนไข Logical_Test ได้อย่างหลายหลาย อาจใช้ค่า sum, vlookup หรือ อื่นๆ ได้อีกมาก
5. จาก 3 รายการแรก ใช้ เงื่อนไขแบบ " >= " (มากกว่าหรือเท่ากับ) และ 2 รายการถัดไป ใช้เงื่อนไขแบบ "<" (เครื่องหมายน้อยกว่า) ซึ่งถึงแม้กำหนดเงื่อนไขต่างกัน ก็ให้ผลที่เท่ากัน