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

การใช้ SUMIF, SUMIFS รวมข้อมูลแบบมีเงื่อนไขใน excel

ถ้าเปรียบเทียบการทำงานกับถนนทูเวย์
ถนนด้านหนึ่งคือ Vlookup ถนนอีกด้านคือ Sumif ด้วยคุณสมบัติการทำงานที่สวนทางกัน

• Vlookup = one from many ดึงคำตอบหนึ่งข้อมูล จากแหล่งข้อมูลที่มากมายหลากหลาย
• Sumif = many to one เป็นการรวมผลลัพท์จากข้อมูลจากหลายๆ ข้อมูล

แล้วเมื่อไรควรใช้ Sumif??

เพราะ Sumif ก็คือการรวมผลลัพท์ โดยกำหนดจากค่าอ้างอิงในฟังก์ชัน เช่น ต้องการหายอดรวมค่ารถโดยสาร จากรายการที่เก็บรวมรวมไว้ทั้งเดือน ดังนั้น sumif จึงเป็นฟังก์ชัน ที่มีคุณสมบัติเหมาะสม เมื่อต้องการรวมผลลัพท์หรือสรุปยอดตัวเลขจากฐานข้อมูลที่มีการบันทึกค่าซ้ำๆ กันมากมาย และสิ่งสำคัญที่จะทำให้ฟังก์ชันอย่าง Sumif เวริ์คสุดๆ คือต้องมีฟิลด์ข้อมูลหลัก ที่มีค่าไม่ซ้ำ (primary key) เพื่อให้สามารถใช้เป็นตัวอ้างอิงให้ดึงค่าจากฐานข้อมูลมารวมให้ถูกต้อง

ไวยากรณ์

=SUMIF (range, criteria, sum_range)

• range: เซลล์หรือช่วงของเซลล์ที่เป็นโจทย์ เพื่อใช้หาผลรวมของ sum_range
• criteria: เป็นเงื่อนไขที่ระบุ
• sum_range: คือช่วงเซลล์ที่ต้องการหาผลรวม

ไวยากรณ์

SUMIFS(sum_range, criteria_range 1, criteria 1 criteria_range 2, criteria 2..)

• sum_range: เหมือนกับ Sumif คือเซลล์ที่ต้องการหาผลรวม
• criteria_range: range ที่เป็นเงื่อนไขสำหรับ SUMIFS
• Sumif สามารถรวมข้อมูลจากหลายแหล่ง ด้วยเงื่อนไขที่แตกต่างได้เป็นอย่างดี

---

ตัวอย่างการใช้ SUMIF และ SUMIFS ให้คืนค่าผลรวม iPod ในสต็อค

• Branch (Column A)คือสาขาสมมุติให้มีสาขา A, B และ C
• Product (Column B) กับ Mem. (Column C)คือ ประเภทของสินค้าในสต็อคของแต่ละสาขา เช่น สาขา A มีสต็อคอยู่ 3 รายการ, สาขา B และ C มีอยู่ 2 รายการ
• Qty. (Column D) เป็นจำนวนสต็อคของ iPod

เราจะใช้ตารางโจทย์เป็นฐานข้อมูลเพื่อหาผลลัพท์ในตารางแสดงฟังก์ชัน Sumif,Sumifs

ตาราง1 คือโจทย์ทดสอบฟังก์ชัน Sumif,Sumifs

Row	Column
	A	B	C	D
1	Branch	Product	Mem	Qty.
2	A	iPod touch	32 GB.	23
3	A	iPod touch	64 GB.	12
4	A	iPod Nano	16 GB.	10
5	B	iPod touch	32 GB.	15
6	B	iPod Nano	16 GB.	14
7	C	iPod Shuffle	2 GB.	32
8	C	iPod touch	16 GB.	11

ตาราง2 แสดงการใช้ฟังก์ชัน SUMIF กับ SUMIFS

Row	Column
	G	H	I
1	หาผลรวมของ	ผลลัพท์	การใช้ฟังก์ชัน ใน Column G
2	iPod Nano	24	=SUMIF(B2:B8,G2,D2:D8)
3	iPod touch	61	=SUMIF(B2:B8,G4,D2:D8)
4	iPod touch 32 GB.	38	=SUMIFS(D2:D8,B2:B8,LEFT(G3,10),C2:C8,RIGHT(G3,6))

อธิบายการใช้ฟังก์ชัน Sumif กับ Sumifs ในตารางการใช้ฟังก์ชัน

1. SUMIF ใช้หาผลรวมที่มีเงื่อนไขเดียว
   1. iPod Nano :
      1. range พื้นที่เซลล์เรนจ์ที่ใช้หาผลรวมของ iPod Nano คือ B2:B8 (จากตาราง1)
      2. criteria เงื่อนไขคือ G2 (จากตาราง2)
      3. sum_range ช่วงเซลล์เรนจ์ที่ต้องการให้คำนวณคือ D2:D8(จากตาราง1)
      4. คำตอบที่ได้คือ (10 +14)ผลลัพท์จึงได้ 24
   2. iPod touch: แทนค่าในฟังก์ชันเหมือนการหาผลรวมสต็อค iPod Nano ทุกอย่างยกเว้นเงื่อนไข(criteria)
      1. ผลลัพท์ได้(D2  +D3 + D5 + D8) หรือ (23 +12 + 15 + 11) = 61

---

2. SUMIFS ใช้หาผลรวมที่มีหลายเงื่อนไขมากกว่า Sumif เราจะกำหนดเงื่อนไข 2 ตัว เพื่อหาผลสต็อคของ iPod touch 32 GB.ในตารางที่2
   1. กำหนดให้ Criteria_range1 คือ คอลัมท์ Product (B2:B8) จากตารางที่1
   2. ต้องทำให้ Criteria1 เหมือนกับค่าใน คอลัมท์ Product (B2:B8) ของตารางที่ 1 เราจึงต้องตัดคำในเซลล์ G4(ตารางที่2) โดยใช้  Left(G4,10) จะได้คำว่า iPod touch
   3. Criteria_range2 คือ คอลัมท์ Mem (C2:C8)
   4. หา Criteria2 ด้วยการตัดคำใน  G4(ตารางที่2) โดยใช้ Right(G4,6) คือแทนค่าด้วย Text function  จะได้คำว่า 32 GB.
   5. เมื่อแทนค่าทั้งหมดผลลัพท์จะได้ 38 หน่วยจากสองรายการของตาราง1

จากตัวอย่างจะเห็นว่า criteria ในฟังก์ชัน sumif ไม่จำเป็นต้องเป็นค่าใดค่าหนึ่ง สามารถใช้กับฟังก์ชันแทนที่ลงไปเลยก็ได้แต่ถ้าหากเป็นข้อมูลจำนวนมากๆ การจัดเก็บข้อมูลควรแบ่งเป็น 3 ส่วน คือ Input sheet, Record sheet และ Output sheet จะทำให้งาน flow ได้สะดวกและรวดเร็วขึ้น

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