“เจอตัวแล้ว” โจ้ส่งเสียงเมื่อเจอหน้าผม
“มาอธิบายต่อเลย คราวนี้เจอแต่เช้า ห้ามหนีไปไหนนะ” โจ้ตัดทางถอยทันที
“มีอะไรให้พี่ช่วยหรือโจ้”
“ครั้งก่อนพี่บอกว่าถ้าไม่อยากใช้ IF ซ้อนกันเยอะๆให้ใช้ VLOOKUP แทน”
“ใช่ ทำไมหรือ?” ผมถามกลับ
“พี่พูดต่อว่า VLOOKUP อาจไม่ใช่วิธีที่ดีที่สุด”
“ใช่ พี่พูดยังงั้น”
“แล้ววิธีที่ดีที่สุดคืออะไรครับ?” โจ้เผยสิ่งที่ต้องการ
“วิธีที่ดีที่สุดคือ ตรรกะ”
“ตรรกะ… ยังไงอ่ะพี่?”
“พี่มาที่โต๊ะผมทีได้ไหม ผมมีเคส IF ซ้อน IF จะถามพอดีเลย” โจ้ใช้ประโยคคำสั่งและขอร้อง

สำหรับใครที่เพิ่งอ่านบทความนี้เป็นครั้งแรก บทความนี้เป็น episode 3 (ep3) ของซีรีส์ IF ซ้อน IF
อ่าน ep1 ได้ ที่นี่
อ่าน ep2 ได้ ที่นี่

“คือผมอยากจะเปลี่ยนเดือนเป็นไตรมาสน่ะพี่”
“เช่น เดือน 1 เป็นไตรมาส 1, เดือน 4 เป็นไตรมาส 2, เดือน 7 เป็นไตรมาส 3, เดือน 10 เป็นไตรมาส 4″
แบบนี้

“ตอนนี้ใช้ IF แต่ต้องเขียน IF ซ้อนกัน 11 ชั้นแบบนี้”

=”Q”&IF(A2=1,1,IF(A2=2,1,IF(A2=3,1,IF(A2=4,2,IF(A2=5,2,IF(A2=6,2,IF(A2=7,3,IF(A2=8,3,IF(A2=9,3,IF(A2=10,4,IF(A2=11,4,4)))))))))))

“มีวิธีที่ดีกว่านี้ไหมครับ?”

ผมเห็นสูตรแล้วแอบสูดลมหายใจด้วยความหนาวเหน็บ
“มีสิ มีอย่างน้อย 6 วิธีเลยนะ”
มีอะไรบ้าง มาดูกันเลย ^__^

1. IFS

โดยปกติแล้วฟังก์ชันที่สามารถใช้แทน IF ซ้อน IF มี 4 ฟังก์ชันคือ
– IFS
– SWITCH
– CHOOSE
– VLOOKUP
( อ่านรายละเอียดของทั้ง 4 ฟังก์ชันได้จาก ep1 และ ep2 )

เคสนี้ถ้าใช้ IFS แทนแบบตรงๆจะเขียนสูตรได้เป็น

=”Q”&IFS(A2=1,1,A2=2,1,A2=3,1,A2=4,2,A2=5,2,A2=6,2,A2=7,3,A2=8,3,A2=9,3,A2=10,4,A2=11,4,A2=12,4)

ซึ่งแน่นอนว่าไม่ใช่วิธีที่ดี
แต่ถ้านำ ‘ตรรกะ’ มาช่วย จะพบว่า
ถ้า A2<4 คือ 1
ถ้า A2<7 คือ 2
ถ้า A2<10 คือ 3
ถ้า A2<13 คือ 4
เราก็ปรับสูตรได้เป็น

=”Q”&IFS(A2<4,1,A2<7,2,A2<10,3,A2<13,4)

“สูตรสั้นลงเยอะเลยพี่” โจ้โห่ร้อง
“พี่ถึงบอกว่า สิ่งสำคัญที่สุดคือตรรกะไง” ^__^
“แล้ววิธีที่ 2 ล่ะครับ?”

2. SWITCH

คราวนี้มาลองใช้ SWITCH บ้าง
ถ้าใช้ SWITCH จะเขียนสูตรเป็น

=”Q”&SWITCH(TRUE,A2<4,1,A2<7,2,A2<10,3,4)

“ดูคล้ายๆ IFS แบบที่สองเลยครับพี่”
“ใช่ คล้ายกันมาก แค่เปลี่ยนฟังก์ชัน แต่ตรรกะยังคงเดิม”

3. CHOOSE

สังเกตได้ว่า ถ้าใช้ IFS หรือ SWITCH ต้องเขียน A2<4, A2<7, A2<10 คล้ายเขียนตรรกะซ้ำซ้อน
แต่ถ้าใช้ CHOOSE จะเขียนง่ายกว่า ไม่ต้องเขียนตรรกะซ้ำ
หรือเขียนสูตรได้เป็น

=”Q”&CHOOSE(A2,1,1,1,2,2,2,3,3,3,4,4,4)

“เฮ้ย! เจ๋งอ่ะ สูตรสั้นลงเยอะเลยพี่” โจ้โห่ร้องเป็นครั้งที่สอง
“ใช่ เคสนี้ใช้ CHOOSE ดีกว่า IF, IFS, SWITCH เพราะคล้ายแทนค่าลงบนตำแหน่งที่ต้องการได้เลย เป็นการเขียนสูตรที่สั้นและเข้าใจง่าย”
“แต่เคสนี้ CHOOSE ก็มีข้อเสียนะ”
“ข้อเสียคืออะไรครับ?”
“ถ้าเขียนสูตรแล้วไม่แก้ไข CHOOSE น่าจะเป็นวิธีที่ดีที่สุด แต่ถ้าแก้ไข มันจะแก้ยาก ดูลายตานิดนึง เพราะผลลัพธ์มีตั้ง 12 แบบ”
“ถ้าคิดเผื่อการแก้ไข VLOOKUP อาจเป็นวิธีที่ดีกว่า”

4. VLOOKUP

ถ้าใช้ VLOOKUP อาจสร้างตารางอ้างอิงขึ้นมาก่อน หน้าตาประมาณนี้

แล้วเขียนสูตรเป็น

=VLOOKUP(A2,$E$2:$F$5,2,1)

“ถ้าไม่อยากสร้างตารางอ้างอิง ต้องเขียนสูตรยังไงครับ?” โจ้ถาม
ถ้าไม่อยากสร้างตารางอ้างอิง ให้สร้าง Array Constant ขึ้นมาแทน
หรือเขียน Array Constant เป็น

{1,”Q1″;4,”Q2″;7,”Q3″;10,”Q4″}

(สำหรับผู้สนใจรายละเอียดของ Array Constant อ่านรายละเอียดได้จาก ep1 )

หรือเขียนสูตรใหม่เป็น

=VLOOKUP(A2,{1,”Q1″;4,”Q2″;7,”Q3″;10,”Q4″},2,1)

ถ้ามองว่า Array Constant มี Q มากไป ดูซ้ำซ้อน อาจเขียน Array Constant ใหม่เป็น

{1,1;4,2;7,3;10,4}

แล้วค่อยเชื่อมกับ Q หรือเขียนสูตรเป็น

=”Q”&VLOOKUP(A2,{1,1;4,2;7,3;10,4},2,1)

“เฮ้ย! เจ๋งอ่ะพี่” โจ้โห่ร้องเป็นครั้งที่ 3
“ว่าแต่.. นี่ก็ครบทั้ง 4 ฟังก์ชันแล้ว ทำไมพี่ถึงบอกว่ามีอย่างน้อย 6 วิธีล่ะครับ?”
“เพราะมีอีกอย่างน้อย 2 วิธีไง” ^__^

5. LOOKUP

เคสนี้เป็นการใช้ VLOOKUP แบบ approximate match
ถ้าเป็น VLOOKUP แบบ approximate match ก็แปลว่าสามารถใช้ฟังก์ชัน LOOKUP ได้เช่นกัน
หรือเขียนสูตรเป็น

=”Q”&LOOKUP(A2,{1,1;4,2;7,3;10,4})

จะพบว่าสูตรคล้าย VLOOKUP แต่เขียนสั้นกว่า (ไม่มี ,2,1 ต่อท้าย)

จริงๆแล้วถ้าเป็นการใช้แบบ approximate match LOOKUP เป็นฟังก์ชันที่ยืดหยุ่นกว่า VLOOKUP
อาจเขียนสูตรเป็นแบบนี้ก็ได้

=”Q”&LOOKUP(A2,{1,4,7,10},{1,2,3,4})

“ผมไม่เคยใช้ฟังก์ชัน LOOKUP มาก่อนเลย ไม่นึกว่ามันเจ๋งขนาดนี้”
“ถ้าโจ้ได้รู้จักฟังก์ชัน XLOOKUP จะพบว่ามันเจ๋งกว่า LOOKUP, VLOOKUP อีกนะ เรียกว่าลืม VLOOKUP ไปได้เลย”
“XLOOKUP หรือครับพี่?” โจ้ทวนคำ
“ใช่ XLOOKUP เป็นฟังก์ชันใหม่ ตอนนี้ใช้ได้เฉพาะใน Office Insider แต่ปีหน้าน่าจะใช้ใน Office 365 ได้แล้ว”
“โห Excel มีอะไรให้ค้นหาเยอะมากจริงๆ”
“แล้ววิธีที่ 6 ล่ะครับพี่?”
“วิธีที่ 6 คือตรรกะทางคณิตศาสตร์”

6. Logic

“ตรรกะทางคณิตศาสตร์ หมายความว่าไงครับพี่?” โจ้งง

พี่อธิบายยังงี้ละกัน
ลองมองเป็นโจทย์ทางคณิตศาสตร์นะ
ถ้าพี่ถามว่า
มี 1,2,3 ทำยังไงให้ได้ 1
มี 4,5,6 ทำยังไงให้ได้ 2
มี 7,8,9 ทำยังไงให้ได้ 3
มี 10,11,12 ทำยังไงให้ได้ 4
โจ้จะตอบว่าไง?

.
…
…..
(2 นาทีผ่านไป)

“คิดออกแล้ว” โจ้โห่ร้องเป็นครั้งที่ 4
“ยังไง?” ผมถามกลับ
“บวก 2 ก่อน แล้วหารด้วย 3”
“ถ้ามีเศษจากการหาร ให้ตัดเศษทิ้งไปเลย”

เช่น 5+2 เป็น 7
เอา 7 ไปหาร 3 ได้ 2 เศษ 1
ตัดเศษ 1 ทิ้งไป เหลือ 2
คำตอบคือ 2

“ถูกต้องนะคร๊าบบบ….” ผมใช้นิ้วโป้งตั้งฉากกับนิ้วชี้แล้วชี้ไปที่โจ้
“ถ้าสมมติ 5 คือ A2 จะเขียนสูตรว่าไง?” ผมถามต่อ
“(A2+2)/3”
“แต่ปัญหาคือมีทศนิยม ถ้าอยากตัดทศนิยมทิ้ง ต้องทำไงครับ?” โจ้ถามกลับ
“ครอบด้วยฟังก์ชัน TRUNC ได้เลย”
( TRUNC คือฟังก์ชันที่ใช้ตัดเศษหรือทศนิยมทิ้ง (truncate) เวลาใช้ให้ครอบตัวเลขที่ต้องการ )

หรือเขียนสูตรเป็น
TRUNC((A2+2)/3)

ถ้าต้องการ Q นำหน้า ก็เอา “Q” ไปเชื่อม
หรือเขียนสูตรเป็น

=”Q”&TRUNC((A2+2)/3)

“เฮ้ย! สั้นสุดๆเลยอ่ะพี่” โจ้โห่ร้องเป็นครั้งที่ 5
“นี่แหละ พี่เลยพูดว่า VLOOKUP อาจไม่ใช่วิธีที่ดีที่สุดเสมอไป” ^__^

ถ้าเน้นสูตรสั้น
=”Q”&TRUNC((A2+2)/3)
น่าจะสั้นที่สุด

ถ้าเน้นสูตรที่เข้าใจง่าย
=”Q”&CHOOSE(A2,1,1,1,2,2,2,3,3,3,4,4,4)
น่าจะเข้าใจง่ายที่สุด

ถ้าเน้นสูตรที่ง่ายต่อการแก้ไข
=VLOOKUP(A2,$E$2:$F$5,2,1)
น่าจะแก้ไขง่ายที่สุด

ทุกสูตรมีทั้งข้อดีและข้อเสีย
ไม่มีวิธีที่ดีที่สุด ขึ้นอยู่กับบริบทของเคสนั้นๆ และขึ้นอยู่กับว่าเราถนัดวิธีใด
สิ่งสำคัญคือ ‘ตรรกะ’
เพราะถ้าใช้ตรรกะ จะพบว่ามีทางเลือกอย่างน้อย 6 วิธี

ผมใช้คำว่า “อย่างน้อย” เพราะถ้าใช้ตรรกะอื่น จะพบว่ามีวิธีอีกมากมายที่ได้คำตอบตรงกัน
อาจมีวิธีที่ 7, 8, 9, 10, …
และอาจมีวิธีอื่นที่ดีกว่า
นี่แหละคือ “เสน่ห์” ของ Excel
ยิ่งศึกษา ยิ่งพบว่าไม่รู้
แต่ก็ทำให้เราเก่งขึ้น และสนุกกับมัน
เห็นด้วยไหมครับ ^__^

สำหรับผู้ที่สนใจไฟล์ตัวอย่างของบทความนี้ สามารถดาวน์โหลดเพื่อศึกษาได้เลยครับ

หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟซบุ๊กแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่

อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^__^

.
==== อัพเดต ====
หลังจากโพสต์บทความนี้ พบว่าหลายคนร่วมสนุกแบ่งปันสูตรและไอเดีย ขอบคุณทุกความคิดเห็นครับ ^/\^

ผมชอบสูตรจากเจ้าของเพจ Excel Wizard มากๆ เพราะคิดได้อีกถึง 14 แบบ !!

=”Q”&INT((A2+2)/3)
=”Q”&ROUNDUP(A2/3,)
=TEXT((A2+1)/3,”Q0″)
=”Q”&CEILING(A2,3)/3
=”Q”&FLOOR(A2+2,3)/3
=”Q”&MROUND(A2+1,3)/3
=”Q”&QUOTIENT(A2+2,3)
=”Q”&ROUND((A2+1)/3,0)
=”Q”&MATCH(A2,{1,4,7,10})
=”Q”&5-MATCH(A2,{12,9,6,3},-1)
=”Q”&LOOKUP((A2+2)/3,{1,2,3,4})

และแบบพิศดาร 😄
=”Q”&DECIMAL(INT(BASE(A2+2,3)/10),3)
=”Q”&TEXT(A2,”[>9]4;[>6]3;2″)-(A2<4)
=”Q”&TEXT(A2,”[<4]1;[<7]2;3″)+(A2>9)

สุดยอดมากๆครับ ทั้งตรรกะ ไอเดีย และความคิดสร้างสรรค์ ให้คะแนน 100 เต็ม 10 เลย ^__^

อ่านที่มาของทั้ง 14 สูตรได้จากโพสต์นี้ครับ
https://www.facebook.com/XcWizard/posts/2674765362591889