Excel formulas หรือ การเขียนสูตรใน Excel เป็นหัวใจและจิตวิญญาณของ Microsoft Excel ช่วยให้ท่านคำนวณ วิเคราะห์ข้อมูล และทำงานอัตโนมัติได้ สูตรใน Excel จะเริ่มต้นด้วยเครื่องหมายเท่ากับ (=) เสมอ และภายในสูตรจะประกอบด้วย ตัวเลข ตัวดำเนินการ และฟังก์ชัน
Excel มีฟังก์ชันในตัวมากมายสำหรับช่วยคำนวณและดำเนินการต่าง ๆ กับข้อมูล ตัวอย่างเช่น ฟังก์ชัน SUM เพื่อบวก หรือ รวมผลลัพธ์ในช่วงของเซลล์ ฟังก์ชัน AVERAGE เพื่อคำนวณค่าเฉลี่ย หรือฟังก์ชัน COUNT เพื่อนับจำนวน
นอกจากฟังก์ชันในตัวแล้ว หากท่านใช้ Visual Basic for Applications (VBA) เป็นก็สามารถเขียนฟังก์ชันของท่านเองได้
สูตร Excel ยังสามารถอ้างอิงเซลล์อื่นหรือช่วงของเซลล์เพื่อคำนวณตามค่าในเซลล์เหล่านั้น ตัวอย่างเช่น ใช้สูตรเพื่อคำนวณยอดขายรวมสำหรับเดือนหนึ่งๆ โดยอ้างอิงจากตัวเลขยอดขายในแต่ละวันในเดือนนั้น
คุณลักษณะที่มีประสิทธิภาพอีกประการหนึ่งของสูตร Excel คือความสามารถในการใช้ตัวดำเนินการเชิงตรรกะในการคำนวณตามเงื่อนไข ตัวอย่างเช่น ใช้ฟังก์ชัน IF เพื่อทำการคำนวณโดยขึ้นอยู่กับว่าเงื่อนไขเฉพาะเป็นจริงหรือเท็จ
Excel Formular ทำงานอย่างไร
สิ่งสำคัญประการแรก Excel Formular จะเริ่มต้นด้วยเครื่องหมายเท่ากับ (=) ก่อนเสมอ เป็นการบอกให้ Excel รับรู้ว่า สิ่งที่พิมพ์ต่อด้านหลังคือสูตรที่ต้องดำเนินการ ซึ่งจะตามด้วยตัวเลข ตัวดำเนินการทางคณิตศาสตร์ (เช่น เครื่องหมายบวกหรือลบ) และ/หรือ ฟังก์ชัน ก็ได้
องค์ประกอบของสูตร
ในสูตร จะมีองค์ประกอบ 4 ส่วน ซึ่ง ไม่จำเป็นต้องมีครบ ก็คือ
- Functions – ฟังก์ชัน
- References – การอ้างอิงตำแหน่งเซลล์
- Constants – ค่าคงที่ (จะเป็นตัวเลขหรืออักขระ) ค่านี้จะเป็นค่าที่ใส่เข้าไป ไม่ได้เกิดจากคำนวณ เช่น วันที่ ตัวเลข และ ข้อความ
- Operators – ตัวดำเนินการต่าง ๆ
ที่ว่าไม่ต้องมีทุกอย่าง ลองมาดูตัวอย่างแรก
= A1+100
A1 เป็น References อ้างอิงค่าในเซลล์ A1 ไม่ว่าค่าใน A1 เป็นอะไรก็ตามนั้น และ + (เครื่องหมายบวก) เป็นตัวดำเนินการ หรือ Operators และ 100 เป็นค่าคงที่ หรือ Constants
หรือ อีกตัวอย่างหนึ่ง
= SUM(A1:A10)*1.5ในตัวอย่างนี้
SUM คือ ฟังก์ชัน สำหรับฟังก์ชัน จะเริ่มต้นด้วยชื่อฟังก์ชันเสมอ (ในตัวอย่างนี้ ชื่อฟังก์ชันคือ SUM) ตามด้วย วงเล็บ ซึ่งจะมี อาร์กิวเมนต์ตามที่ฟังก์ชันนั้นกำหนด โดยแต่ละ อาร์กิวเมนต์ จะคั่นด้วยจุลภาค (,)
ในตัวอย่างนี้ อาร์กิวเมนต์คือ A1:A10 เป็นอาร์กิวเมนต์เดี่ยว ซึ่งทำหน้าที่ อ้างอิงตำแหน่งเซลล์ – references
เครื่องหมาย * คือตัวดำเนินการ – Operators คือ คูณ
และ 1.5 คือ Constants หรือ ค่าคงที่
ตัวอย่าง
เราลองเริ่มต้นด้วยการ คำนวณง่าย ๆ
ตัวอย่างเช่น เราจะ เอา 2 บวก 3 แล้วคูณ 5
2+3x5คำถามคลาสสิก ท่านคิดว่าจะได้คำตอบเป็น 25 หรือว่า 17 ?
ถ้าท่านตอบ 25 แปลว่า ท่านมองการคำนวณจากซ้ายไปขวา คือ 2+3 ได้ 5 คูณ 5 ได้ 25
แต่ถ้าท่านตอบ 17 ก็หมายความ ท่านเอา 3 คูณ 5 ได้ 15 แล้วบวก 2
คำตอบไหนคือคำตอบที่ถูกต้อง?
บางท่านอาจจะตอบว่า ถูกทั้งสอง ซึ่งก็ไม่ผิด (อ้าว) ดูเหมือนว่าคำตอบจะขึ้นอยู่กับวิธีแก้ปัญหาแต่ในการทำงานกับคนหมู่มาก เราไม่อาจยอมรับความยืดหยุ่นแบบนี้ได้ ลองนึกว่านี่คือการทำงานทางบัญชี แต่ละคนคำนวณแล้วได้คำตอบไม่เท่ากัน ทั้งที่ตัวเลขเหมือนกัน ผลมันจะเป็นเช่นใด
เพื่อขจัดความสับสน เรามีกฎของลำดับความสำคัญของการดำเนินการ หรือ Order of Operations ซึ่งใช้กันอย่างน้อยย้อนไปถึงช่วงปี ค.ศ. 1500 “การดำเนินการ” คือการบวก การลบ การคูณ การหาร การยกกำลัง และการจัดกลุ่มการดำเนินการเหล่านี้ ว่าจะต้องดำเนินการอะไรก่อน หรือ ตัวดำเนินการใดความสำคัญกว่า (นั่นคือ การดำเนินการใดได้รับการดูแลมาก่อน) ซึ่งการดำเนินการอื่น ๆ
ถ้าท่านทำงานกับ Excel ท่านจะได้คำตอบ 17 นั่นคือ Excel ใช้วิธีจัดลำดับความสำคัญของเครื่องหมายตัวดำเนินการ หรือ Order of Operations ซึ่งท่านจะต้องทำความเข้าใจกับกระบวนการนี้ เพื่อจะได้ออกแบบสูตรได้ผลอย่างที่ท่านต้องการ
Order of Operations
Operations หรือ การดำเนินการ ก็คือขั้นตอนกับตัวเลขต่าง ๆ ตามตัวดำเนินการจะกำหนด เช่น บวก ลบ คูณ หาร ยกกำลัง ความจริงเราเรียนเรื่องนี้มาตั้งแต่สมัยประถมแล้ว นั่นคือ PEMDAS
- P – Parentheses first วงเล็บก่อน
- E – Exponents (เลขยกกำลัง เช่น เลขยกกำลังและรากที่สอง เป็นต้น)
- MD – Multiplication and Division การคูณและการหาร (จากซ้ายไปขวา)
- AS – Addition and Subtraction การบวกและการลบ (จากซ้ายไปขวา)
ก็คือ
- ถ้าเห็นวงเล็บเมื่อใด ให้ทำสิ่งที่เกิดขึ้นในวงเล็บนั้นก่อน
- ถัดจากนั้น ถ้ามีเลขยกกำลัง หรือ สแควร์รูต
- แล้วจึงเป็น การคูณและหาร
- และสุดท้าย บวกกับลบ
โดยที่ ถ้าหากว่าอยู่ในลำดับเดียวกัน (อย่างเช่น บวกกับลบ) ให้ทำตัวที่อยู่ด้านซ้ายก่อน ไล่ไปทางด้านขวา
จากตัวอย่างเดิม 2+3×5 ถ้าเราอยากให้ 2 + 3 x 5 = 25 เราจะต้องใส่วงเล็บกำกับที่ 2+3 นั่นคือ (2 + 3) x 5 ซึ่ง จะกลายเป็นว่า ต้องเอา 2 บวก 3 ก่อน แล้วจึงนำผลลัพธ์ คือ 5 ไปคูณกับ 5 อีกที
ในตัวอย่างต่อไปนี้ วงเล็บที่อยู่ในส่วนแรกของสูตรจะบังคับให้ Excel สำหรับเว็บคำนวณ B4+25 ก่อน จากนั้นจึงหารผลลัพธ์ด้วยผลรวมของค่าในเซลล์ D5, E5 และ F5
เราลองมาดูอีกตัวอย่างหนึ่ง
=(C1-100)/SUM(A1:A5)
ในการทำงานจริง บางที (หรือหลายที) เราอาจจะต้องใช้ฟังก์ชันซ้อนฟังก์ชัน ถ้าใช้คำเป็นกิจจะลักษณะก็ต้องบอกว่า บางครั้ง เราต้องใช้ฟังก์ชันเป็นอาร์กิวเมนต์ในฟังก์ชันอื่น ซึ่งตรงนี้อาจจะต้องระวัง ใน Excel 2003 หรือ ก่อนหน้า รวมทั้ง Excel ในเว็บ จะซ้อนได้ 7 ระดับ ตั้งแต่ Excel 2007 เป็นต้นมา จนถึง Microsoft 365 จะซ้อนได้ 64 ระดับ
