Auto Running Number ด้วย ฟังก์ชัน IF กับ MAX

Auto Running Numbers ด้วย ฟังก์ชัน MAX

Running Number หรือ การเรียงอันดับ
ที่ไม่ประสงค์จะเรียงทุกแถว (ROW) สามารถใช้ Function Max ร่วมกับ IF จะสะดวกมากขึ้น
โดยที่สูตร MAX จำเป็นต้องใส่ $ เพื่อล็อคคอลัมภ์

---

ตัวอย่าง 1

=IF( $C3=$I$2,MAX($B$2:$B2)+1,0)

การใช้ฟังก์ชัน MAX กับ IF เรียงอันดับ ประเภทสินค้า

1. ต้องการเรียงอันดับ 123... .ในคอลัมภ์ B เฉพาะสินค้าประเภท Frozen Fruit Type
2. ใช้ เซลล์ I2 (Frozen Fruit) เป็นเซลล์กำหนดเงื่อนไข เปรียบเทียบกับข้อมูลของคอลัมภ์ C ในตาราง โดยใช้ Function IF
3. ถ้าเซลส์ C3 = เซลล์ I2 ให้สูตร Max หาค่าสูงสุดของเซลล์ก่อนหน้า แล้วบวก 1
4. ผลลัพท์คือสูตรจะไม่ Running Number สำหรับสินค้าประเภท Frozen Pork- Grill Pork Fore Leg, Marinated Pork

---

ตัวอย่าง 2

=IF($E3=$I$2,MAX($B$2:$B2)+1,0)

การใช้ฟังก์ชัน MAX กับ IF เรียงอันดับเฉพาะ ราคาที่กำหนด

1. ต้องการเรียงอันดับ 123... .ในคอลัมภ์ B เฉพาะสินค้าราคาต่ำกว่า 100
2. ใช้ เซลล์ I2 (100) เป็นจุดกำหนดเงื่อนไข เปรียบเทียบกับข้อมูลของคอลัมภ์ E ในตาราง โดยใช้ Function IF
3. ถ้าเซลส์ E3 <(น้อยกว่า) เซลล์ I2 ให้สูตร Max หาค่าสูงสุดของเซลล์ก่อนหน้า แล้วบวก 1
4. ผลลัพท์คือสูตรจะข้าม Running Number ถ้าราคาสูงกว่า 100 เช่น Cranberry, Lychee Meat

---

ตัวอย่าง 3

=IF( $F5-$G5<$I$2,MAX($B$2:$B2)+1,0)

การใช้ฟังก์ชัน MAX กับ IF เรียงอันดับเฉพาะสินค้าที่ยอดขาย (Sale) ต่ำกว่า ประมาณการ (Forecast) น้อยกว่า 9

1. ต้องการเรียงอันดับ 123... .ในคอลัมภ์ B เฉพาะสินค้าที่ขายได้ต่ำกว่าประมาณการน้อยกว่า 9
2. ใช้ เซลล์ I2 (9) เป็นจุดกำหนดเงื่อนไข เปรียบเทียบกับข้อมูลของคอลัมภ์ F และ G ในตาราง โดยใช้ Function IF
3. ถ้าผลลัพท์มากกว่า เซลล์ I2 ให้สูตร Max หาค่าสูงสุดของเซลล์ก่อนหน้า แล้วบวก 1
4. ผลลัพท์คือสูตรจะข้าม Running Number ถ้าผลต่างมากกว่า 9

การใช้ฟังก์ชัน MAX, MIN กับ IF ใน EXCEL

Function MAX และ Min ใน Excel

MAX จะคืนค่าสูงสุดของชุดข้อมูล ส่วน MIN จะคืนค่าต่ำสุดของชุดข้อมูล

• ซึ่งเป็นค่าตัวเลข, วันที่
• หรืออยู่ในช่วงเซลล์ที่กำหนด
• ถ้าเงื่อนไขไม่มีค่า จะคืนค่า 0 (ศูนย์)
• สามารถใช้ประยุกต์กับฟังก์ชั่นอื่นๆ

---

Row	Column
	A	B	C	D
1	No	Inventory	Price	Start
2	1	Cook's knife	710	1/8/2015
3	2	Filleting knife	500	1/7/2015
4	3	Bread knife	800	15/7/2015
5	4	Paring knife	420	15/4/2015
6	5	Chinese Chopper	450	15/5/2015
7	6	Cleaver	610	1/6/2015
8	7	Filleting knife	449	1/9/2015
9	8	Paring knife	430	15/8/2015

จากตารางข้างต้น เป็นรายการสินค้าคงเหลือ โดย B เป็นชนิดของมีดแบบต่างๆ, C คือราคา และ D เป็นวันที่ล่าสุดที่ใช้ราคาใน Column C เราจะใช้ข้อมูลในตารางทดสอบการใช้ฟังก์ชัน MAX และ MIN ในแบบต่างๆ รวมถึงการใช้ร่วมกับ ฟังก์ชั่น IF

Row	Column
10	E	F	G
11	เงื่อนไข	การใช้ฟังก์ชัน	ผลลัพท์
12	ราคาสูงสุด	=MAX(C2:C9)	800
13	ราคาต่ำสุด	=MIN(C2:C9)	420
14	วันที่ล่าสุด	=MAX(D2:D9)	1/9/2015
15	และ การหาคำตอบตามเงื่อนไข โดยใช้คู่กับ IF
16	ชื่อสินค้า	การใช้ฟังก์ชัน	ผลลัพท์
18	Price: Cleaver	{=MAX(IF($B$2:$B$9=E18,$C$2:$C$9,0))}	610
20	Price: Filleting knife	{=MIN(IF($B$2:$B$9=E18,$C$2:$C$9,0))}	449
19	Start: Paring knife	{=MAX(IF($B$2:$B$9=E19,$D$2:$D$9))}	15/8/2015

จากตารางตัวอย่าง การใชัฟังก์ชัน MAX, MIN และ MAX+IF, MIN+IF

1. การหาค่าสูงสุด หรือ ต่ำสุด เพียงใช้ฟังก์ชัน MAX หรือ MIN แล้วกำหนดช่วงเซลล์ที่ต้องการเท่านั้น (ตัวอย่าง Row12,13,14)


2. กรณี Row18 ใช้ IF เพิ่มเงื่อนไข ถ้าช่วงเซลล์สินค้า $B$2:$B$9 เท่ากับ Cleaver (E18) ให้สูตรหาราคา ของ E18 ซึ่งมองดู การทำงานคล้าย Vlookup หรือ Sumif แต่ การใช้ MAX มีข้อแตกต่างตรงที่เราเลือกค่าที่มากที่สุดหรือ น้อยที่สุดได้ ถ้าข้อมูลมีมากกว่าหนึ่งรายการ


3. กรณี Row19,20 เหมือนกันตรงที่ สินค้าทั้ง Filleting Knife (No.2,No7) กับ Paring knife (No4, No.8) มีสองรายการ เมื่อใช้ฟังก์ชัน MIN ผลลัพท์ของ Filleting Knife คือ 449 และ Paring Knife ใช้ฟังก์ชัน MAX สูตรจึงคืนค่า วันล่าสุด คือ 15/8/2015


4. การใช้ร่วมกับฟังก์ชัน IF ในลักษณะนี้ ต้องปิดสูตรด้วยการกด Ctrl + Shift + Enter

การใช้ฟังก์ชัน 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)))

การใช้ 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 รายการถัดไป ใช้เงื่อนไขแบบ "<" (เครื่องหมายน้อยกว่า) ซึ่งถึงแม้กำหนดเงื่อนไขต่างกัน ก็ให้ผลที่เท่ากัน