สูตร Excel: สูตร Excel คืออะไรและใช้งานอย่างไร

คำว่า “สูตร Excel” สามารถหมายถึงการรวมกันของ โอเปอเรเตอร์ di เอ็กเซล และ/หรือ ฟังก์ชัน Excel.

สูตร Excel จะถูกป้อนลงในเซลล์สเปรดชีตโดยพิมพ์เครื่องหมาย = ตามด้วยตัวดำเนินการและ/หรือฟังก์ชันที่จำเป็น ซึ่งสามารถทำได้ง่ายๆ เหมือนกับการเพิ่มพื้นฐาน (เช่น “=A1+B1”) หรืออาจเป็นการผสมผสานที่ซับซ้อนของตัวดำเนินการ Excel และฟังก์ชัน Excel ที่ซ้อนกันหลายรายการ

ตัวดำเนินการ Excel

ตัวดำเนินการ Excel ดำเนินการกับค่าตัวเลข ข้อความ หรือการอ้างอิงเซลล์ ตัวดำเนินการ Excel มีสี่ประเภทที่แตกต่างกัน

เควสติโซโน:

• ดำเนินการทางคณิตศาสตร์
• ตัวดำเนินการข้อความ
• ตัวดำเนินการเปรียบเทียบ
• ตัวดำเนินการอ้างอิง

เรามาอธิบายตัวดำเนินการสี่ประเภทกัน:

ดำเนินการทางคณิตศาสตร์

ตัวดำเนินการทางคณิตศาสตร์ของ Excel และลำดับที่ได้รับการประเมินจะแสดงอยู่ในตารางต่อไปนี้:

ลำดับความสำคัญของตัวดำเนินการทางคณิตศาสตร์

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

ลำดับการประเมินตัวดำเนินการทางคณิตศาสตร์สร้างความแตกต่างอย่างมากให้กับผลลัพธ์ของสูตร Excel อย่างไรก็ตาม สามารถใช้วงเล็บเพื่อบังคับให้ส่วนของสูตรได้รับการประเมินก่อนได้ หากส่วนหนึ่งของสูตรอยู่ในวงเล็บ ส่วนที่อยู่ในวงเล็บของสูตรจะมีความสำคัญเหนือกว่าตัวดำเนินการทั้งหมดที่ระบุไว้ข้างต้น ซึ่งแสดงไว้ในตัวอย่างต่อไปนี้:

ตัวอย่างของตัวดำเนินการทางคณิตศาสตร์

ตัวดำเนินการข้อความ Excel

ตัวดำเนินการต่อข้อมูลของ Excel (แสดงด้วยสัญลักษณ์ &) จะรวมสตริงข้อความเพื่อสร้างสตริงข้อความเดี่ยวเพิ่มเติม

ตัวอย่างของตัวดำเนินการต่อข้อมูล

สูตรต่อไปนี้ใช้ตัวดำเนินการต่อข้อมูลเพื่อรวมสตริงข้อความ “SMITH" " จ "John"

ตัวดำเนินการเปรียบเทียบ Excel

ตัวดำเนินการเปรียบเทียบ Excel ใช้สำหรับ defiกำหนดเงื่อนไขต่างๆ เช่น เมื่อใช้ฟังก์ชัน IF ของเอ็กเซล ตัวดำเนินการเหล่านี้แสดงอยู่ในตารางต่อไปนี้:

ตัวอย่างของตัวดำเนินการเปรียบเทียบ

สเปรดชีตด้านล่างแสดงตัวอย่างตัวดำเนินการเปรียบเทียบที่ใช้กับฟังก์ชัน IF ของเอ็กเซล

ตัวดำเนินการอ้างอิง

ตัวดำเนินการอ้างอิงของ Excel จะใช้เมื่ออ้างอิงช่วงภายในสเปรดชีต ตัวดำเนินการอ้างอิงคือ:

ตัวอย่างของตัวดำเนินการอ้างอิง

ตัวอย่างที่ 1 – ตัวดำเนินการช่วง Excel

เซลล์ C1 ในสเปรดชีตต่อไปนี้แสดงตัวดำเนินการช่วงที่ใช้ defiสิ้นสุดช่วงเวลา A1-B3. จากนั้นช่วงจะถูกส่งไปยังฟังก์ชัน SUM ของ Excel ซึ่งจะเพิ่มค่าลงในเซลล์ A1-B3 และส่งคืนค่า 21.

ตัวอย่างที่ 2 – ตัวดำเนินการสหภาพ Excel

เซลล์ C1 ของสเปรดชีตต่อไปนี้จะแสดงตัวดำเนินการสหภาพที่ใช้ define ช่วงที่ประกอบด้วยเซลล์ในสองช่วง A1-A3 e A1-B1. จากนั้นช่วงผลลัพธ์จะถูกส่งไปยังฟังก์ชัน SUM ใน Excel ซึ่งจะรวมค่าในช่วงรวมและส่งกลับค่า 12.

โปรดทราบว่าตัวดำเนินการสหภาพของ Excel จะไม่ส่งคืนการรวมทางคณิตศาสตร์ที่แท้จริง เช่น เซลล์ A1ซึ่งรวมอยู่ในทั้งสองช่วง A1-A3 e A1-B1 จะถูกนับสองครั้งในการคิดผลรวม)

ตัวอย่างที่ 3 – ตัวดำเนินการทางแยกของ Excel

เซลล์ C1 ในสเปรดชีตต่อไปนี้แสดงตัวดำเนินการทางแยกที่ใช้ defiสิ้นสุดช่วงที่สร้างขึ้นบนเซลล์ที่จุดตัดของช่วง A1-A3 e A1-B2. ช่วงผลลัพธ์ (range A1-A2) จากนั้นจะจ่ายให้กับฟังก์ชันของ SUM ของ Excel ซึ่งรวมค่าในช่วงที่ตัดกันและส่งกลับค่า 4.

ข้อมูลเพิ่มเติมเกี่ยวกับตัวดำเนินการ Excel มีอยู่ที่ เว็บไซต์ไมโครซอฟต์ออฟฟิศ.

ฟังก์ชัน Excel

Excel มีฟังก์ชันในตัวจำนวนมากที่สามารถใช้เพื่อคำนวณเฉพาะหรือส่งคืนข้อมูลเกี่ยวกับข้อมูลสเปรดชีต ฟังก์ชันเหล่านี้จัดเป็นหมวดหมู่ (ข้อความ ตรรกะ คณิตศาสตร์ สถิติฯลฯ) เพื่อช่วยคุณค้นหาฟังก์ชันที่คุณต้องการจากเมนู Excel

ด้านล่างนี้เราจะแสดงรายการฟังก์ชัน Excel ทั้งหมด โดยจัดกลุ่มตามหมวดหมู่ ลิงก์ฟังก์ชันแต่ละลิงก์จะนำคุณไปยังหน้าเฉพาะ ซึ่งคุณจะพบคำอธิบายของฟังก์ชัน พร้อมตัวอย่างการใช้งานและรายละเอียดเกี่ยวกับข้อผิดพลาดทั่วไป

ฟังก์ชันทางสถิติของ Excel:

การนับและความถี่

• COUNT: ส่งกลับจำนวนค่าตัวเลขในชุดเซลล์หรือค่าที่ระบุ
• COUNTA: ส่งกลับจำนวนที่ไม่ใช่ช่องว่างในชุดเซลล์หรือค่าที่ระบุ
• COUNTBLANK: ส่งคืนจำนวนเซลล์ว่างในช่วงที่ระบุ
• COUNTIF: ส่งคืนจำนวนเซลล์ (ของช่วงที่กำหนด) ซึ่งเป็นไปตามเกณฑ์ที่กำหนด
• COUNTIFS: ส่งคืนจำนวนเซลล์ (ของช่วงที่ระบุ) ที่ตรงตามชุดเกณฑ์ที่ระบุ (ใหม่ใน Excel 2007)
• FREQUENCY: ส่งคืนอาร์เรย์ที่แสดงจำนวนค่าจากอาร์เรย์ที่ระบุซึ่งอยู่ในช่วงที่ระบุ

ค้นหาค่าสูงสุดและต่ำสุด

• MAX: ส่งกลับค่าที่มากที่สุดจากรายการตัวเลขที่ให้มา
• MAXA: ส่งกลับค่าที่มากที่สุดจากรายการค่าที่ให้มา การนับข้อความ และค่าตรรกะ FALSE เป็นค่า 0 และนับค่าตรรกะ TRUE เป็นค่า 1
• MAXIFS: ส่งกลับค่าที่มากที่สุดจากชุดย่อยของค่าในรายการที่ระบุตามเกณฑ์ตั้งแต่หนึ่งเกณฑ์ขึ้นไป (ใหม่จาก Excel 2019)
• MIN: ส่งกลับค่าที่น้อยที่สุดจากรายการตัวเลขที่ให้มา
• MINA: ส่งกลับค่าที่น้อยที่สุดจากรายการค่าที่ให้มา โดยนับข้อความและค่าตรรกะ FALSE เป็นค่า 0 และนับค่าตรรกะ TRUE เป็นค่า 1
• MINIFS: ส่งกลับค่าที่น้อยที่สุดจากชุดย่อยของค่าในรายการที่ระบุตามเกณฑ์ตั้งแต่หนึ่งเกณฑ์ขึ้นไป (มีอะไรใหม่ใน Excel 2019)
• LARGE: ส่งกลับค่า Kth LARGEST จากรายการตัวเลขที่ให้มาสำหรับค่า K ที่กำหนด
• SMALL: ส่งคืนค่า Kth SMALLEST จากรายการตัวเลขที่ให้มาสำหรับค่า K ที่กำหนด

มีเดีย

• AVERAGE: ส่งกลับค่าเฉลี่ยของรายการตัวเลขที่ให้มา
• AVERAGEA: ส่งกลับค่าเฉลี่ยของรายการตัวเลขที่ให้มา โดยนับข้อความและค่าตรรกะ FALSE เป็นค่า 0 และนับค่าตรรกะ TRUE เป็นค่า 1
• AVERAGEIF: คำนวณค่าเฉลี่ยของเซลล์ในช่วงที่ระบุซึ่งตรงตามเกณฑ์ที่กำหนด (ใหม่ใน Excel 2007)
• AVERAGEIFS: คำนวณค่าเฉลี่ยของเซลล์ในช่วงที่ระบุซึ่งตรงตามเกณฑ์หลายข้อ (ใหม่ใน Excel 2007)
• MEDIAN: ส่งกลับค่ามัธยฐาน (ค่ากลาง) ของรายการตัวเลขที่ให้มา
• MODE: คำนวณโหมด (ค่าที่พบบ่อยที่สุด) ของรายการตัวเลขที่กำหนด (แทนที่ด้วยฟังก์ชัน Mode.Sngl ใน Excel 2010)
• MODE.SNGL: คำนวณโหมด (ค่าที่พบบ่อยที่สุด) ของรายการตัวเลขที่ให้มา (ใหม่ใน Excel 2010: แทนที่ฟังก์ชัน Mode)
• MODE.MULT: ส่งกลับอาร์เรย์แนวตั้งของค่าที่พบบ่อยที่สุดในอาร์เรย์หรือช่วงข้อมูล (ใหม่ใน Excel 2010)
• GEOMEAN: ส่งกลับค่าเฉลี่ยเรขาคณิตของชุดตัวเลขที่กำหนด
• HARMEAN: ส่งกลับค่าเฉลี่ยฮาร์มอนิกของชุดตัวเลขที่ให้มา
• TRIMMEAN: ส่งกลับค่าเฉลี่ยภายในของชุดค่าที่ให้มา

การเรียงสับเปลี่ยน

• PERMUT: ส่งกลับจำนวนการเรียงสับเปลี่ยนสำหรับจำนวนวัตถุที่ระบุ
• PERMUTATIONA: ส่งกลับจำนวนการเรียงสับเปลี่ยนสำหรับจำนวนวัตถุที่กำหนด (ด้วยการซ้ำ) ที่สามารถเลือกได้จากวัตถุทั้งหมด (ใหม่ใน Excel 2013)

ช่วงความเชื่อมั่น

• CONFIDENCE: ส่งกลับช่วงความเชื่อมั่นสำหรับค่าเฉลี่ยประชากร โดยใช้การแจกแจงแบบปกติ (แทนที่ด้วยฟังก์ชัน Confidence.Norm ใน Excel 2010)
• CONFIDENCE.NORM: ส่งกลับช่วงความเชื่อมั่นสำหรับค่าเฉลี่ยประชากร โดยใช้การแจกแจงแบบปกติ (ใหม่ใน Excel 2010: แทนที่ฟังก์ชันความเชื่อมั่น)
• CONFIDENCE.T: ส่งกลับช่วงความเชื่อมั่นสำหรับค่าเฉลี่ยประชากร โดยใช้การแจกแจงแบบ t ของนักเรียน (ใหม่ใน Excel 2010)

เปอร์เซ็นต์และควอไทล์

• PERCENTILE: ส่งกลับค่าเปอร์เซ็นต์ไทล์ Kth ในช่วงที่กำหนด โดยที่ K อยู่ในช่วง 0 – 1 (รวม) (แทนที่ด้วยฟังก์ชัน Percentile.Inc ใน Excel 2010)
• PERCENTILE.INC: ส่งกลับค่าเปอร์เซ็นไทล์ Kth ในช่วงที่กำหนด โดยที่ K อยู่ในช่วง 0 – 1 (รวม) (ใหม่ใน Excel 2010: แทนที่ฟังก์ชันเปอร์เซ็นไทล์)
• PERCENTILE.EXC: ส่งกลับเปอร์เซ็นต์ไทล์ K ของค่าในช่วงที่กำหนด โดยที่ K อยู่ในช่วง 0 – 1 (ไม่รวม) (ใหม่ใน Excel 2010)
• QUARTILE: ส่งกลับควอไทล์ที่ระบุของชุดตัวเลขที่กำหนด โดยยึดตามค่าเปอร์เซ็นไทล์ 0 – 1 (รวม) (แทนที่ด้วยฟังก์ชัน Quartile.Inc ใน Excel 2010)
• QUARTILE.INC: ส่งกลับควอร์ไทล์ที่ระบุของชุดตัวเลขที่ระบุ โดยยึดตามค่าเปอร์เซ็นไทล์ 0 – 1 (รวม) (ใหม่ใน Excel 2010: แทนที่ฟังก์ชันควอไทล์)
• QUARTILE.EXC: ส่งกลับควอร์ไทล์ที่ระบุของชุดตัวเลขที่กำหนด โดยยึดตามค่าเปอร์เซ็นไทล์ 0 – 1 (ไม่รวม) (ใหม่ใน Excel 2010)
• RANK: ส่งกลับอันดับทางสถิติของค่าที่กำหนดภายในอาร์เรย์ของค่าที่ระบุ (แทนที่ด้วยฟังก์ชัน Rank.Eq ใน Excel 2010)
• RANK.EQ: ส่งกลับโหมด (ค่าที่พบบ่อยที่สุด) ของรายการตัวเลขที่ให้มา (หากมีมากกว่าหนึ่งค่าที่มีอันดับเดียวกัน ระบบจะส่งกลับอันดับสูงสุดของชุดนั้น) (ใหม่ใน Excel 2010: แทนที่ฟังก์ชันอันดับ)
• RANK.AVG: ส่งกลับอันดับทางสถิติของค่าที่กำหนดภายในอาร์เรย์ของค่าที่ระบุ (หากหลายค่ามีอันดับเดียวกัน ก็จะส่งคืนอันดับเฉลี่ย) (ใหม่ใน Excel 2010)
• PERCENTRANK: ส่งกลับอันดับของค่าในชุดข้อมูลเป็นเปอร์เซ็นต์ (รวม 0 – 1) (แทนที่ด้วยฟังก์ชัน Percentrank.Inc ใน Excel 2010)
• PERCENTRANK.INC: ส่งกลับอันดับของค่าในชุดข้อมูลเป็นเปอร์เซ็นต์ (รวม 0 – 1) (ใหม่ใน Excel 2010: แทนที่ฟังก์ชันเปอร์เซ็นต์อันดับ)
• PERCENTRANK.EXC: ส่งกลับอันดับของค่าในชุดข้อมูลเป็นเปอร์เซ็นต์ (ไม่รวม 0 – 1) (ใหม่ใน Excel 2010)

การเบี่ยงเบนและความแปรปรวน

• AVEDEV: ส่งกลับค่าเฉลี่ยของการเบี่ยงเบนสัมบูรณ์ของจุดข้อมูลจากค่าเฉลี่ย
• DEVSQ: ส่งกลับผลรวมของกำลังสองของการเบี่ยงเบนของชุดจุดข้อมูลจากค่าเฉลี่ยตัวอย่าง
• STDEV: ส่งกลับค่าเบี่ยงเบนมาตรฐานของชุดค่าที่ให้มา (แสดงตัวอย่างประชากร) (แทนที่ด้วยฟังก์ชัน St.Dev ใน Excel 2010)
• STDEV.S: ส่งกลับค่าเบี่ยงเบนมาตรฐานของชุดค่าที่กำหนด (แสดงถึงตัวอย่างประชากร) (ใหม่ใน Excel 2010: แทนที่ฟังก์ชัน STDEV)
• STDEVA: ส่งคืนค่าส่วนเบี่ยงเบนมาตรฐานของชุดค่าที่กำหนด (แทนกลุ่มตัวอย่างประชากร) โดยนับข้อความและค่าตรรกะ FALSE เป็นค่า 0 และนับค่าตรรกะ TRUE เป็นค่า 1
• STDEVP: ส่งกลับค่าเบี่ยงเบนมาตรฐานของชุดค่าที่กำหนด (แทนประชากรทั้งหมด) (แทนที่ด้วยฟังก์ชัน StdPDev ใน Excel 2010)
• STDEV.P: ส่งกลับค่าเบี่ยงเบนมาตรฐานของชุดค่าที่กำหนด (แสดงถึงประชากรทั้งหมด) (ใหม่ใน Excel 2010: แทนที่ฟังก์ชัน STDEV)
• STDEVPA: ส่งคืนค่าส่วนเบี่ยงเบนมาตรฐานของชุดค่าที่กำหนด (แทนประชากรทั้งหมด) โดยนับข้อความและค่าตรรกะ FALSE เป็นค่า 0 และนับค่าตรรกะ TRUE เป็นค่า 1
• VAR: ส่งคืนค่าความแปรปรวนของชุดค่าที่กำหนด (แทนกลุ่มตัวอย่างประชากร) (แทนที่ด้วยฟังก์ชัน SVar ใน Excel 2010)
• VAR.S: ส่งกลับค่าความแปรปรวนของชุดค่าที่กำหนด (แสดงตัวอย่างประชากร) (ใหม่ใน Excel 2010 - แทนที่ฟังก์ชัน Var)
• VARA: ส่งคืนค่าความแปรปรวนของชุดค่าที่กำหนด (เป็นตัวแทนของกลุ่มตัวอย่าง) โดยนับข้อความและค่าตรรกะ FALSE เป็นค่า 0 และนับค่าตรรกะ TRUE เป็นค่า 1
• VARP: ส่งคืนค่าความแปรปรวนของชุดค่าที่กำหนด (แสดงถึงประชากรทั้งหมด) (แทนที่ด้วยฟังก์ชัน Var.P ใน Excel 2010)
• VAR.P: ส่งคืนค่าความแปรปรวนของชุดค่าที่กำหนด (แสดงถึงประชากรทั้งหมด) (ใหม่ใน Excel 2010 - แทนที่ฟังก์ชัน Varp)
• VARPA: ส่งคืนค่าความแปรปรวนของชุดค่าที่กำหนด (เป็นตัวแทนของประชากรทั้งหมด) โดยนับข้อความและค่าตรรกะ FALSE เป็นค่า 0 และนับค่าตรรกะ TRUE เป็นค่า 1
• COVAR: ส่งกลับค่าความแปรปรวนร่วมของประชากร (นั่นคือ ค่าเฉลี่ยผลคูณของส่วนเบี่ยงเบนสำหรับแต่ละคู่ภายในชุดข้อมูลที่กำหนดสองชุด) (แทนที่ด้วยฟังก์ชัน Covariance.P ใน Excel 2010)
• COVARIANZA.P: ส่งกลับค่าความแปรปรวนร่วมของประชากร (นั่นคือ ค่าเฉลี่ยผลคูณของส่วนเบี่ยงเบนสำหรับแต่ละคู่ภายในชุดข้อมูลที่กำหนดสองชุด) (ใหม่ใน Excel 2010: แทนที่ฟังก์ชัน Covar)
• COVARIANZA.S: ส่งกลับค่าความแปรปรวนร่วมตัวอย่าง (เช่น ค่าเฉลี่ยผลคูณของส่วนเบี่ยงเบนสำหรับแต่ละคู่ภายในชุดข้อมูลที่กำหนดสองชุด) (ใหม่ใน Excel 2010)

ฟังก์ชันการทำนาย

• FORECAST: ทำนายจุดในอนาคตบนเส้นแนวโน้มเชิงเส้นที่เหมาะกับชุดค่า x และ y ที่กำหนด (แทนที่ด้วยฟังก์ชัน FORECAST.LINEAR ใน Excel 2016)
• FORECAST.ETS: ใช้อัลกอริธึมการปรับให้เรียบแบบเอ็กซ์โพเนนเชียลเพื่อทำนายมูลค่าในอนาคตในช่วงเวลาหนึ่ง โดยอิงตามชุดค่าที่มีอยู่ (ใหม่ใน Excel 2016 – ไม่มีใน Excel 2016 สำหรับ Mac)
• FORECAST.ETS.CONFINT: ส่งกลับช่วงความเชื่อมั่นสำหรับค่าการคาดการณ์ ณ วันที่เป้าหมายที่ระบุ (ใหม่ใน Excel 2016 – ไม่มีใน Excel 2016 สำหรับ Mac)
• FORECAST.ETS.SEASONALITY: ส่งกลับความยาวของรูปแบบการทำซ้ำที่ Excel ตรวจพบสำหรับอนุกรมเวลาที่ระบุ (ใหม่ใน Excel 2016 – ไม่มีใน Excel 2016 สำหรับ Mac)
• FORECAST.ETS.STAT: ส่งกลับค่าทางสถิติเกี่ยวกับการคาดการณ์อนุกรมเวลา (ใหม่ใน Excel 2016 – ไม่มีใน Excel 2016 สำหรับ Mac)
• FORECAST.LINEAR: คาดการณ์จุดในอนาคตบนเส้นแนวโน้มเชิงเส้นที่พอดีกับชุดค่า x และ y ที่กำหนด (ใหม่ใน Excel 2016 (ไม่ใช่ Excel 2016 สำหรับ Mac) - แทนที่ฟังก์ชันพยากรณ์)
• INTERCEPT: คำนวณเส้นการถดถอยที่เหมาะสมที่สุดโดยใช้ชุดค่า x และ y จะส่งกลับค่าที่เส้นนี้ตัดแกน y
• LINEST: ส่งกลับข้อมูลทางสถิติที่อธิบายแนวโน้มของเส้นที่เหมาะสมที่สุด ผ่านชุดค่า x และ y
• SLOPE: ส่งคืนค่าความชันของเส้นการถดถอยเชิงเส้นผ่านชุดค่า x และ y ที่กำหนด
• TREND: คำนวณเส้นแนวโน้มผ่านชุดค่า y ที่กำหนดและส่งกลับค่า y เพิ่มเติมสำหรับชุดค่า x ใหม่ที่กำหนด
• GROWTH: ส่งกลับตัวเลขในแนวโน้มการเติบโตแบบเอ็กซ์โปเนนเชียล โดยอิงตามชุดของค่า x และ y ที่ให้ไว้
• LOGEST: ส่งกลับพารามิเตอร์ของแนวโน้มเอ็กซ์โปเนนเชียลสำหรับชุดค่า x และ y ที่กำหนด
• STEYX: ส่งกลับข้อผิดพลาดมาตรฐานของค่า y ที่คาดการณ์ไว้สำหรับแต่ละ x ในเส้นการถดถอยสำหรับชุดค่า x และ y ที่กำหนด

Ercole Palmeri