การเขียนสูตรผูกความสัมพันธ์ (Formula)

สูตร (Formula) คืออะไร?

Formula หรือ สูตร คือการสั่งให้ Excel คำนวณค่า โดยระบุความสัมพันธ์ระหว่าง Input และ Output

• สิ่งที่เราใส่ลงไปในสูตร เรียกว่า Input ของสูตร โดยที่จะต้องใส่อยู่หลังเครื่องหมาย = เสมอ
• Excel จะแสดงผลลัพธ์ของการคำนวณออกมาให้เห็นใน Cell เลย เรียกว่า Output ของสูตร
• ความสัมพันธ์สูตรที่เป็นวิธีคิดคำนวณนั้น เราสามารถมองเห็นและแก้ไขได้จาก Formula Bar

ตัวอย่าง  ถ้าจะหาพื้นที่สามเหลี่ยม เมื่อรู้ความสูงและความยาวฐานของรูปสามเหลี่ยม เราสามารถใช้ความสัมพันธ์ คือ
พื้นที่สามเหลี่ยม = ½ x สูง x ฐาน (เพราะสามเหลี่ยมก็คือสี่เหลี่ยมแบ่งครึ่ง)

• Output ที่เราต้องการคือ พื้นที่สามเหลี่ยม
• ความสัมพันธ์ในช่อง Output คือ ½ x สูง x ฐาน
• Input คือ ให้ความสูงอยู่ในช่อง B2, ให้ความยาวฐานอยู่ในช่อง B3
• สูตรในช่อง Output คือ  =1/2 * B2 * B3 หรือ =0.5 * B2 * B3 ก็ได้

หมายเหตุ

• = เป็นการบ่งบอกว่าเรากำลังจะใส่สูตร (สูตรต้องนำหน้าด้วย = เสมอ)
• * คือ เครื่องหมายคูณ (Operator) ใน Excel
• B2 คือ ความสูง, B3 คือ ความยาวฐาน
  จะเห็นว่าสองค่านี้เราอาจลองเปลี่ยนเล่นได้ จึงใช้เป็น Cell Reference แทนการใส่เลขลงไปตรงๆ

องค์ประกอบของการเขียนสูตร

ตัวอย่างที่เพิ่งกล่าวไปเป็นเพียงตัวอย่างหนึ่งของการใส่สูตรเท่านั้น จริงๆ แล้ว Input ของสูตรใน Excel สามารถใส่อะไรลงไปได้อีกมากมาย แล้วมันมีอะไรบ้างล่ะ? เรามาดูกันครับ

สิ่งที่เราจะใส่ลงไปหลังเครื่องหมายเท่ากับ (=)  สามารถแบ่งออกเป็น 4 ประเภทหลักๆ คือ

• Dataคือ การใส่ค่าข้อมูลลงไปตรงๆ (ที่ไม่ได้อ้างอิงจากช่องอื่น)
• Cell Referenceคือ การอ้างอิงค่าจากช่องอื่น เช่น B2 ซึ่งอาจอ้างไป Workbook อื่นก็ยังได้
• Defined Name คือ การตั้งชื่อให้ Cell/Range/สูตร ซึ่ง ใช้แทนสิ่งเหล่านั้นได้เลย
• Operator ตัวดำเนินการ แบ่งได้หลายประเภท คือ
  • Arithmetic Operator (ตัวดำเนินการทางคณิตศาสตร์ เช่น + – * / )
  • Comparison Operator (ตัวดำเนินการเปรียบเทียบ เช่น > = <)
  • Text Operator (ตัวดำเนินการข้อความ เช่น &)
  • Reference Operator (ตัวดำเนินการอ้างอิง เช่น , : )
• Function เปรียบเสมือนสูตรสำเร็จรูป เช่น ฟังก์ชั่น SUM ที่ได้ยกตัวอย่างไปแล้ว ซึ่งผมจะพูดในรายละเอียดในบทถัดไป

ตัวอย่าง

ข้อจำกัดของการเขียนสูตร

แม้ว่าการเขียนสูตรจะสามารถดึงค่ามาจาก Cell อื่นๆ โดยใช้ Cell Reference ได้อย่างที่บอกไปตอนต้น แต่ว่าการเขียนสูตรไม่สามารถที่จะดึงรูปแบบการแสดงผล หรือ Format จาก Cell อ้างอิงได้ เช่น Cell อ้างอิงมีสีเขียว เราอยากให้ช่องที่กรอกสูตรดึงสีเขียวมาใช้บ้าง แบบนี้ไม่ได้นะครับ!! ดังนั้นหากต้องการที่จะให้ Format ของ Cell เปลี่ยนแปลงได้ อาจต้องใช้วิธีอื่น เช่น Conditional Formatting หรือเขียน VBA สั่งงานเท่านั้น

ประเภทของ Operator (ตัวดำเนินการ)

• Arithmetic Operator (ตัวดำเนินการทางคณิตศาสตร์) เช่น เครื่องหมายคำนวณ +, -, *, /, ^, % เอาไว้ใช้กับข้อมูลประเภทตัวเลข (ก็แน่ล่ะ ตัวหนังสือมันคงบวกลบกันไม่ได้น่ะสิ)
• Comparison Operator(ตัวดำเนินการเปรียบเทียบ) เช่น เครื่องหมายเปรียบเทียบ จะทำให้ผลลัพธ์ออกมาเป็นตรรกะTRUE/FALSE
  • = (เท่ากับ)                                 A1=B1
  • > (มากกว่า) A1>B1
  • < (น้อยกว่า) A1<B1
  • >= (มากกว่าหรือเท่ากับ) A1>=B1
  • <= (น้อยกว่าหรือเท่ากับ) A1<=B1
  • <> (ไม่เท่ากับ) A1<>B1
• Text Operator(ตัวดำเนินการข้อความ) มีเครื่องหมายเดียวคือ & เอาไว้เชื่อมข้อมูลหลายๆอันเข้าด้วยกัน ซึ่งผลลัพธ์ที่ได้จะกลายเป็นข้อมูลประเภทTextโดยอัตโนมัติ เช่น
  • = “แมว” & “เหมียว” & “ “ & 10 & “ตัว” จะออกมาเป็นคำว่า แมวเหมียว 10ตัว
  • =10&200 จะออกมาเป็น 10200 แต่จะกลายเป็นข้อมูลประเภทข้อความ ไม่ใช่ตัวเลข
  • =10&”” จะออกมาเป็น 10 แต่จะกลายเป็นข้อมูลประเภทข้อความ ไม่ใช่ตัวเลข
• Reference Operator (ตัวดำเนินการอ้างอิง) เอาไว้เชื่อม Cell Reference มี 3 เครื่องหมาย คือ ,(comma)   :(colon)   และ  (ช่องว่าง)  เหมือนกับที่เคยยกตัวอย่างไปแล้วในบทก่อนหน้านี้ที่พูดถึงเรื่อง Cell Reference

ลำดับความสำคัญของ Operator

สมมติเราใส่สูตรว่า =2+5*3  คุณคิดว่า Excel จะคิดได้เลข 21 (เอา 2+5 ก่อน แล้วค่อยคูณ 3 ) หรือ จะได้ 17 (เอา 5 คูณ 3 ก่อน แล้วบวก 2) ครับ?

ที่ถามเพราะจริงๆแล้ว Operator แต่ละตัวมีลำดับความสำคัญไม่เท่ากัน  หากเราเผลอใส่สูตรลงไปโดยที่ไม่ได้คิดถึงประเด็นเรื่องนี้ Excel อาจทำงานผิดพลาดไปจากที่คุณคิดได้เลยล่ะ!!

เอาล่ะ ถ้าอยากรู้ว่า Excel คำนวณอะไรก่อนหลัง ดูได้ตามนี้เลยครับ

สัญลักษณ์ (เรียงจากคำนวณก่อน ไปหลัง)	ความหมาย	ตัวอย่าง
( )	วงเล็บ	=(2+5)*3
:	colon	A1:A5
	ช่องว่าง	A1:A5 B1:B5
,	comma	A1,A2
–	เลขติดลบ	–1
%	เปอร์เซ็นต์	5%
^	เลขยกกำลัง	3^2
* , /	คูณ หรือ หาร	3*5 หรือ 10/4
+ , –	บวก หรือ ลบ (คนละอันกับเลขติดลบ)	2+3 หรือ 10–7
&	ตัวเชื่อม text	A3&” บาท”
=, < >, <=, >=, <>	เครื่องหมายเปรียบเทียบ	A1=3 หรือ 6>5

ดังนั้นในตัวอย่าง =2+5*3  จะเห็นว่า เครื่องหมาย * จะถูกคำนวณก่อน + จะได้ =2+15 = 17 ครับ

ไม่ต้องห่วงไป หากคุณจำลำดับพวกนี้ไม่ได้ก็ไม่เป็นไรครับ เพราะสาระสำคัญไม่ใช่อยู่ที่เราต้องจำลำดับความสำคัญ แต่อยู่ที่ว่า เราจะต้องรู้ว่า หากเราไม่แน่ใจเรื่องลำดับความสำคัญ อย่าลืมใส่เครื่องหมายวงเล็บครอบลงไปด้วย เพราะเครื่องหมายวงเล็บมีลำดับความสำคัญสูงที่สุด (Excel จะคำนวณในวงเล็บก่อน) นั่นเอง

แต่ถ้าอยากจะลองจำประดับสมองซักหน่อย ผมแนะนำให้จำ 3 ตัวที่ใส่สีแดงไว้ให้ครับ เพราะใช้ค่อนข้างบ่อยเลยทีเดียว

การทำงานกับประเภทของ Data ที่แตกต่างกัน

ประเภทของข้อมูลนั้นเป็นสิ่งที่สำคัญมาก เพราะเวลาเราทำงานกับฟังก์ชั่น หรือ สูตร มันจะทำงานได้ถูกต้องก็ต้องเมื่อเราใช้ประเภทของข้อมูลได้ถูกต้องเท่านั้น เช่น หากฟังก์ชั่นต้องการข้อมูลประเภทตัวเลข เราจะใส่ข้อมูลประเภท Text ลงไปไม่ได้ แม้ว่าหน้าตามันจะเหมือนตัวเลขทุกประการก็ตาม

ดังนั้นเราจะต้องมีความรู้ว่าเราจะจัดการข้อมูลประเภทต่างๆ รวมถึงอาจจะต้องแปลงข้อมูลจากประเภทหนึ่งไปยังอีกประเภทหนึ่งอย่างไร

Number vs Text

ข้อมูล Number และ Text เป็นอะไรที่หน้าตาคล้ายกันมากที่สุด จนแทบไม่มีทางดูออกด้วยตาเปล่าได้เลย เราจะต้องใช้ฟังก์ชั่น TYPE มาทดสอบดูจึงจะรู้ (Numberจะออกมาเป็น 1, ส่วน Text เป็น 2)

การแปลงข้อมูลระหว่าง 2  ประเภทนี้มีวิธีทำดังนี้

แปลง Text  –> Number : ให้นำไป *1 (คูณ 1)

ใน A1 มี text ว่า 00056 เราต้องการแปลงให้เป็น Number ในช่อง A2 จะเขียนว่า =A1*1

แปลง Number –> Text  : ให้นำไป &”” (เชื่อมด้วย & และเครื่องหมายคำพูด 2 อันติดกัน )

เช่น ใน A1 มี ตัวเลข ว่า 56 เราต้องการแปลงให้เป็น Text ในช่อง A2 จะเขียนว่า =A1&””

สังเกตเรื่องการจัดวางชิดซ้ายชิดขวาให้ดีนะครับ ว่า number จะชิดขวาโดยอัตโนมัติ ส่วน text จะชิดซ้ายโดยอัตโนมัติเช่นกัน

Text vs Defined Name

เวลาต้องการเขียนตัวหนังสือในสูตร จะต้องอยู่ในเครื่องหมายคำพูด (“ ”) มิฉะนั้น Excel จะตีความว่าเป็นชื่อที่ตั้งเอาไว้ (Defined Name) ซึ่งทำให้เกิดความผิดพลาดได้

ส่วนช่องที่ว่างเปล่า(ว่างจริงๆ) เราจะแทนด้วยการเขียน  “” (เขียนติดกันไม่มีเว้นวรรค) นะครับ

Logic vs Number

เมื่อนำค่า TRUE/FALSE ไปใช้กับเครื่องหมายการคำนวณทางคณิตศาสตร์ มันจะกลายเป็นเลข 1 และ 0 ตามลำดับ ซึ่งนิยมอยู่ 2 วิธี คือ

• นำไปคูณ 1
  • =TRUE*1 จะได้ 1, =FALSE*1 จะได้ 0
• ใส่ — นำหน้า (ติดลบ 2ตัวติดกัน)
  • =–TRUE จะได้ 1 เพราะติดลบสองทีกลายเป็นบวก =-(-1) =1
  • =–FALSE จะได้ 0 เพราะ =-(-0) =0

ตัวอย่างการเขียนสูตร

หลังจากที่คุณได้เรียนรู้องค์ประกอบของการเขียนสูตรไปทั้งหมดแล้ว คราวนี้มาดูตัวอย่างการเขียนสูตรกันครับ ว่าจะนำองค์ประกอบแต่ละส่วนมารวมกันเป็นสูตรได้ยังไง?

ตัวอย่าง 1 : การคำนวณค่านายหน้า

สมมติมีคนมาฝากคุณขายของ โดยบอกว่าถ้าขายได้เท่าไหร่ เค้าจะให้คุณ 10% แล้วคุณขายของได้ 3 ชิ้น คือ100, 300, 900 บาทตามลำดับ คุณจะได้ Commission เท่าไหร่?

สูตรที่ผิด : =100+300+900*10%

ผิด เพราะว่าถ้าไม่ใส่วงเล็บ Excel จะเอา 10% ไปคูณ 900 ตัวเดียว เนื่องจากลำดับความสำคัญของคูณนั้นมาก่อนการบวก

สูตรที่ถูก : =(100+300+900)*10% แบบนี้จึงจะบวกกันก่อน แล้วค่อยคูณ

ตัวอย่าง 2 : สมการฟิสิกส์ E=mc² ของไอน์สไตน์

ถ้าผมจะหาพลังงาน E  ที่จะได้จากการเปลี่ยนมวล m (กิโลกรัม) ให้กลายเป็นพลังงาน 100% ว่าจะออกมาได้เท่าไหร่? ผมสามารถคำนวณได้ดังนี้ ผมให้มวลอยู่ในช่อง A1, c ความเร็วแสงอยู่ในช่อง B1

สูตร: =A1*B1*B1 หรือจะใช้ =A1*(B1^2) หรือจะใช้ =A1*B1^2 ก็ได้
(ที่ไม่ต้องใส่วงเล็บเพราะการยกกำลังมีลำดับความสำคัญก่อนการคูณ)

แค่นี้คุณก็ใช้สูตรของไอน์สไตน์ได้แล้ว เจ๋งมะ!

ตัวอย่าง 3 : สมการซับซ้อนมากๆ เช่น คำนวณยอดผ่อนบ้านโดยใช้สมการคณิตศาสตร์

เฮ้ย นี่มันอะไรกัน! เขียนสมการซับซ้อนนี่ไม่สนุกเอาซะเลย… แต่ไม่ต้องห่วง ตัวช่วยอยู่ในบทถัดไปครับ