คุณสามารถใช้สูตรและฟังก์ชันในรายการหรือไลบรารีต่างๆ เพื่อคํานวณข้อมูลได้หลายวิธี การเพิ่มคอลัมน์จากการคํานวณลงในรายการหรือไลบรารี คุณสามารถสร้างสูตรที่รวมข้อมูลจากคอลัมน์อื่นและใช้ฟังก์ชันคํานวณวันที่และเวลา ทําสมการทางคณิตศาสตร์ หรือจัดการข้อความได้ ตัวอย่างเช่น ในรายการงาน คุณสามารถใช้คอลัมน์เพื่อคํานวณจํานวนวันที่ใช้เพื่อทํางานแต่ละงานให้เสร็จสมบูรณ์โดยอิงจากคอลัมน์ วันที่เริ่มต้น และ วันที่เสร็จสมบูรณ์
หมายเหตุ
บทความนี้จะอธิบายแนวคิดพื้นฐานที่เกี่ยวกับการใช้สูตรและฟังก์ชัน สําหรับข้อมูลเฉพาะเกี่ยวกับฟังก์ชันใดฟังก์ชันหนึ่ง ให้ดูบทความเกี่ยวกับฟังก์ชันนั้น
ในบทความนี้
- ภาพรวมของสูตร
- ภาพรวมฟังก์ชัน
- การใช้การอ้างอิงคอลัมน์ในสูตร
- การใช้ค่าคงที่ในสูตร
- การใช้ตัวดําเนินการการคํานวณในสูตร
ภาพรวมของสูตร
สูตรคือสมการที่ใช้ในการคํานวณค่าในรายการหรือไลบรารี สูตรจะขึ้นต้นด้วยเครื่องหมายเท่ากับ (=) ตัวอย่างเช่น สูตรต่อไปนี้จะคูณ 2 ด้วย 3 แล้วจึงนําผลลัพธ์มาบวกกับ 5
=5+2*3
คุณสามารถใช้สูตรในคอลัมน์จากการคํานวณและเพื่อคํานวณค่าเริ่มต้นสําหรับคอลัมน์ได้ สูตรสามารถมีฟังก์ชัน การอ้างอิงคอลัมน์ ตัวดําเนินการ และค่าคงที่ ดังตัวอย่างต่อไปนี้
=PI()*[Result]^2
| องค์ประกอบ | คำอธิบาย |
|---|---|
| ฟังก์ชัน | ฟังก์ชัน PI() ส่งกลับค่าของ Pi: 3.141592654 |
| การอ้างอิง (หรือชื่อคอลัมน์) | [ผลลัพธ์] แสดงค่าในคอลัมน์ผลลัพธ์สําหรับแถวปัจจุบัน |
| ค่าคงที่ | ตัวเลขหรือค่าของข้อความที่ใส่เข้าไปยังสูตรโดยตรง เช่น 2 |
| ตัวดำเนินการ | ตัวดําเนินการ * (เครื่องหมายดอกจัน) ทําหน้าที่ในการคูณ และตัวดําเนินการ ^ (แคเรท) ทําหน้าที่ในการยกกําลังตัวเลข |
สูตรอาจใช้องค์ประกอบอย่างน้อยหนึ่งอย่างจากตารางก่อนหน้า ต่อไปนี้คือตัวอย่างของสูตร (ตามลําดับความซับซ้อน)
สูตรอย่างง่าย (เช่น =128+345)
สูตรต่อไปนี้มีค่าคงที่และตัวดําเนินการ
| ตัวอย่าง | คำอธิบาย |
|---|---|
| =128+345 | บวก 128 กับ 345 |
| =5^2 | สี่เหลี่ยม 5 |
สูตรที่มีการอ้างอิงคอลัมน์ (เช่น =[Revenue], >[Cost])
สูตรต่อไปนี้จะอ้างอิงไปยังคอลัมน์อื่นในรายการหรือไลบรารีเดียวกัน
| ตัวอย่าง | คำอธิบาย |
|---|---|
| =[รายได้] | ใช้ค่าในคอลัมน์ รายได้ |
| =[รายได้]*10/100 | 10% ของค่าในคอลัมน์รายได้ |
| =[รายได้] > [ต้นทุน] | ส่งกลับ ใช่ ถ้าค่าในคอลัมน์ รายได้ มากกว่าค่าในคอลัมน์ ต้นทุน |
สูตรที่เรียกใช้ฟังก์ชัน (เช่น =AVERAGE(1, 2, 3, 4, 5))
สูตรต่อไปนี้เรียกใช้ฟังก์ชันที่มีอยู่แล้วภายใน
| ตัวอย่าง | คำอธิบาย |
|---|---|
| =AVERAGE(1, 2, 3, 4, 5) | ส่งกลับค่าเฉลี่ยของชุดของค่า |
| =MAX([Q1], [Q2], [Q3], [Q4]) | ส่งกลับค่าที่มากที่สุดในชุดของค่า |
| =IF([Cost]>[Revenue], "Not OK", "OK") | ส่งกลับ ไม่โอเค ถ้าต้นทุนมากกว่ารายได้ มิฉะนั้นจะส่งกลับค่าตกลง |
| =DAY("15 เม.ย. 2008") | ส่งกลับส่วนของวันของวันที่ สูตรนี้ส่งกลับตัวเลข 15 |
สูตรที่มีฟังก์ชันซ้อนกัน (เช่น =SUM(IF([A][>B], [A]-[B], 10), [C]))
สูตรต่อไปนี้ระบุฟังก์ชันอย่างน้อยหนึ่งฟังก์ชันเป็นอาร์กิวเมนต์ของฟังก์ชัน
| ตัวอย่าง | คำอธิบาย |
|---|---|
| =SUM(IF([A][>B], [A]-[B], 10), [C]) | ฟังก์ชัน IF ส่งกลับผลต่างระหว่างค่าในคอลัมน์ A และ B หรือ 10 ฟังก์ชัน SUM จะบวกค่าที่ส่งกลับของฟังก์ชัน IF และค่าในคอลัมน์ C |
| =DEGREES(PI()) | ฟังก์ชัน PI ส่งกลับตัวเลข 3.141592654 ฟังก์ชัน DEGREES จะแปลงค่าที่ระบุในเรเดียนให้เป็นองศา สูตรนี้จะส่งกลับค่า 180 |
| =ISNUMBER(FIND("BD",[Column1])) | ฟังก์ชัน FIND จะค้นหาสตริง BD ใน Column1 และส่งกลับตําแหน่งเริ่มต้นของสตริง จะส่งกลับค่าความผิดพลาด ถ้าไม่พบสตริง ฟังก์ชัน ISNUMBER ส่งกลับ Yes ถ้าฟังก์ชัน FIND ส่งกลับค่าตัวเลข มิฉะนั้น จะส่งกลับ No |
ภาพรวมฟังก์ชัน
ฟังก์ชันคือสูตรที่กําหนดไว้ล่วงหน้าซึ่งจะทําการคํานวณโดยใช้ค่าที่ระบุ (เรียกว่า อาร์กิวเมนต์) ในลําดับหรือโครงสร้างที่แน่นอน ฟังก์ชันสามารถใช้ในการคํานวณอย่างง่ายหรือซับซ้อน ตัวอย่างเช่น อินสแตนซ์ต่อไปนี้ของฟังก์ชัน ROUND จะปัดเศษตัวเลขในคอลัมน์ ต้นทุน ให้เป็นทศนิยมสองตําแหน่ง
=ROUND([Cost], 2)
คําศัพท์ต่อไปนี้จะมีประโยชน์เมื่อคุณกําลังเรียนรู้ฟังก์ชันและสูตร
โครงสร้าง โครงสร้างของฟังก์ชันจะเริ่มต้นด้วยเครื่องหมายเท่ากับ (=) ตามด้วยชื่อฟังก์ชัน วงเล็บเปิด อาร์กิวเมนต์ต่างๆ สําหรับฟังก์ชันที่คั่นจากกันด้วยเครื่องหมายจุลภาค และวงเล็บปิด
ชื่อฟังก์ชัน คุณสมบัตินี้เป็นชื่อของฟังก์ชันที่ได้รับการสนับสนุนโดยรายการหรือไลบรารี แต่ละฟังก์ชันจะใช้จํานวนอาร์กิวเมนต์ที่ระบุ ประมวลผล และส่งกลับค่า
อาร์กิวเมนต์ อาร์กิวเมนต์อาจเป็นตัวเลข ข้อความ ค่าตรรกะ (เช่น จริงหรือเท็จ) หรือการอ้างอิงคอลัมน์ อาร์กิวเมนต์ที่คุณระบุต้องให้ค่าที่ถูกต้องสําหรับอาร์กิวเมนต์นั้น อาร์กิวเมนต์ยังอาจเป็นค่าคงที่ สูตร หรือฟังก์ชันอื่นๆ ได้อีกด้วย
ในบางกรณี คุณอาจต้องใช้ฟังก์ชันเป็นหนึ่งในอาร์กิวเมนต์ของฟังก์ชันอื่น ตัวอย่างเช่น สูตรต่อไปนี้ใช้ฟังก์ชัน AVERAGE ที่ซ้อนกัน และเปรียบเทียบผลลัพธ์ที่ได้กับผลรวมของค่าคอลัมน์สองค่า
=AVERAGE([Cost1], SUM([Cost2]+[Discount]))
ผลตอบแทนที่ถูกต้อง เมื่อฟังก์ชันถูกใช้เป็นอาร์กิวเมนต์ ฟังก์ชันนั้นจะต้องส่งกลับค่าที่เป็นชนิดเดียวกับที่อาร์กิวเมนต์นั้นใช้ ตัวอย่างเช่น ถ้าอาร์กิวเมนต์ใช้ Yes หรือ No ฟังก์ชันซ้อนก็จะต้องส่งกลับ Yes หรือ No ถ้าไม่ปรากฏ รายการหรือไลบรารีจะแสดง #VALUE! เป็นค่าความผิดพลาด
ขีดจํากัดระดับการซ้อน สูตรสามารถมีฟังก์ชันซ้อนกันได้สูงสุดแปดระดับ เมื่อฟังก์ชัน B ถูกใช้เป็นอาร์กิวเมนต์ในฟังก์ชัน A ฟังก์ชัน B จะเป็นฟังก์ชันระดับที่สอง ตัวอย่างเช่น ในตัวอย่างด้านบน ฟังก์ชัน SUM เป็นฟังก์ชันระดับที่สอง เนื่องจากเป็นอาร์กิวเมนต์ของฟังก์ชัน AVERAGE ฟังก์ชันที่ซ้อนอยู่ภายในฟังก์ชัน SUM จะเป็นฟังก์ชันระดับที่สาม และอื่นๆ
หมายเหตุ
- รายการและไลบรารีไม่สนับสนุนฟังก์ชัน RAND และ NOW
- ฟังก์ชัน TODAY และ ME ไม่ได้รับการสนับสนุนในคอลัมน์จากการคํานวณ แต่ได้รับการสนับสนุนในการตั้งค่าเริ่มต้นของคอลัมน์
การใช้การอ้างอิงคอลัมน์ในสูตร
การอ้างอิงจะระบุเซลล์ในแถวปัจจุบัน และระบุไปยังรายการหรือไลบรารีที่จะค้นหาค่าหรือข้อมูลที่คุณต้องการใช้ในสูตร ตัวอย่างเช่น [Cost] อ้างอิงค่าในคอลัมน์ต้นทุนในแถวปัจจุบัน ถ้าคอลัมน์ต้นทุนมีค่า 100 สําหรับแถวปัจจุบัน =[ต้นทุน]*3 จะส่งกลับ 300
ด้วยการอ้างอิง คุณสามารถใช้ข้อมูลที่อยู่ในคอลัมน์ต่างๆ ของรายการหรือไลบรารีในสูตรอย่างน้อยหนึ่งสูตรได้ คอลัมน์ของชนิดข้อมูลต่อไปนี้สามารถอ้างอิงได้ในสูตร: ข้อความบรรทัดเดียว ตัวเลข สกุลเงิน วันที่และเวลา ตัวเลือก ใช่/ไม่ใช่ และการคํานวณ
คุณใช้ชื่อที่ใช้แสดงของคอลัมน์เพื่ออ้างอิงคอลัมน์ในสูตร ถ้าชื่อมีเว้นวรรคหรืออักขระพิเศษ คุณต้องใส่ชื่อไว้ในวงเล็บเหลี่ยม ([ ]) การอ้างอิงจะไม่ตรงตามตัวพิมพ์ใหญ่-เล็ก ตัวอย่างเช่น คุณสามารถอ้างอิงคอลัมน์ราคาต่อหน่วยในสูตรเป็น [ราคาต่อหน่วย] หรือ [ราคาต่อหน่วย]
หมายเหตุ
- คุณไม่สามารถอ้างอิงค่าในแถวอื่นนอกเหนือจากแถวปัจจุบัน
- คุณไม่สามารถอ้างอิงค่าในรายการหรือไลบรารีอื่น
- คุณไม่สามารถอ้างอิง ID ของแถวสําหรับแถวที่แทรกใหม่ได้ ID ยังไม่มีอยู่เมื่อดําเนินการคํานวณ
- คุณไม่สามารถอ้างอิงคอลัมน์อื่นในสูตรที่สร้างค่าเริ่มต้นสําหรับคอลัมน์ได้
การใช้ค่าคงที่ในสูตร
ค่าคงที่เป็นค่าที่ไม่ได้รับการคํานวณ ตัวอย่างเช่น วันที่ 10/9/2008, เลข 210 และข้อความ "รายได้ประจําไตรมาส" ทั้งหมดนี้เป็นค่าคงที่ ค่าคงที่สามารถเป็นชนิดข้อมูลต่อไปนี้
- สตริง (ตัวอย่าง: =[นามสกุล] = "Smith")
ค่าคงที่ของสตริงจะอยู่ภายในเครื่องหมายอัญประกาศ และสามารถมีอักขระได้สูงสุด 255 อักขระ - ตัวเลข (ตัวอย่าง: =[ต้นทุน] >= 29.99)
ค่าคงที่ที่เป็นตัวเลขอาจมีตําแหน่งทศนิยม และสามารถเป็นค่าบวกหรือค่าลบได้ - วันที่ (ตัวอย่าง: =[วันที่] > DATE(2007,7,1))
ค่าคงที่ของวันที่จําเป็นต้องใช้ฟังก์ชัน DATE(ปี,เดือน,วัน) - บูลีน (ตัวอย่าง: =IF([Cost]>[Revenue], "Loss", "No Loss")
Yes และ No เป็นค่าคงที่บูลีน คุณสามารถใช้ได้ในนิพจน์เงื่อนไข ในตัวอย่างข้างต้น ถ้าต้นทุนมากกว่ารายได้ ฟังก์ชัน IF จะส่งกลับ Yes และสูตรจะส่งกลับสตริง "Loss" ถ้า Cost เท่ากับหรือน้อยกว่า Revenue ฟังก์ชันจะส่งกลับ No และสูตรจะส่งกลับสตริง "No Loss"
การใช้ตัวดําเนินการการคํานวณในสูตร
ตัวดําเนินการจะระบุถึงชนิดของการคํานวณที่คุณต้องการดําเนินการกับองค์ประกอบของสูตร รายการและไลบรารีสนับสนุนตัวดําเนินการคํานวณสามชนิด ได้แก่ คณิตศาสตร์ การเปรียบเทียบ และข้อความ
ตัวดำเนินการทางคณิตศาสตร์
ใช้ตัวดําเนินการทางคณิตศาสตร์ต่อไปนี้เพื่อดําเนินการทางคณิตศาสตร์ขั้นพื้นฐาน เช่น บวก ลบ หรือคูณ เพื่อรวมตัวเลข หรือเพื่อสร้างผลลัพธ์ที่เป็นตัวเลข
| ตัวดำเนินการทางคณิตศาสตร์ | ความหมาย (ตัวอย่าง) |
|---|---|
| + (เครื่องหมายบวก) | การบวก (3+3) |
| - (เครื่องหมายลบ) | การลบ (3–1) จํานวนติดลบ (–1) |
| * (เครื่องหมายดอกจัน) | การคูณ (3*3) |
| / (เครื่องหมายทับ) | การแบ่ง (3/3) |
| % (เครื่องหมายเปอร์เซ็นต์) | เปอร์เซ็นต์ (20%) |
| ^ (แคเรท) | เลขชี้กําลัง (3^2) |
ตัวดำเนินการเปรียบเทียบ
คุณสามารถเปรียบเทียบค่าสองค่าได้โดยใช้ตัวดําเนินการต่อไปนี้ เมื่อเปรียบเทียบค่าสองค่าโดยใช้ตัวดําเนินการเหล่านี้ ผลลัพธ์ที่ได้จะเป็นค่าตรรกะของ Yes หรือ No
| ตัวดําเนินการเปรียบเทียบ | ความหมาย (ตัวอย่าง) |
|---|---|
| = (เครื่องหมายเท่ากับ) | เท่ากับ (A=B) |
| > (เครื่องหมายมากกว่า) | มากกว่า (A,>B) |
| < (เครื่องหมายน้อยกว่า) | น้อยกว่า (A,<B) |
| >= (เครื่องหมายมากกว่าหรือเท่ากับ) | มากกว่าหรือเท่ากับ (A>=B) |
| <= (เครื่องหมายน้อยกว่าหรือเท่ากับ) | น้อยกว่าหรือเท่ากับ (A<=B) |
| <> (เครื่องหมายไม่เท่ากับ) | ไม่เท่ากับ (A,<>B) |
ตัวดำเนินการข้อความ
ใช้เครื่องหมาย และ (&) เพื่อรวม หรือต่อสตริงข้อความตั้งแต่หนึ่งสตริงขึ้นไป เพื่อสร้างเป็นข้อความชิ้นเดียว
| ตัวดำเนินการข้อความ | ความหมาย (ตัวอย่าง) |
|---|---|
| & (เครื่องหมาย 'และ') | เชื่อมต่อ หรือต่อค่าสองค่า เพื่อรวมเป็นค่าข้อความที่ต่อเนื่องกัน ("North"&"wind") |
ลําดับการดําเนินการของรายการหรือไลบรารีในสูตร
สูตรจะคํานวณค่าต่างๆ ตามลําดับที่เฉพาะเจาะจง สูตรอาจเริ่มต้นด้วยเครื่องหมายเท่ากับ (=) ที่อยู่ต่อจากเครื่องหมายเท่ากับคือองค์ประกอบที่จะนําไปคํานวณ (ตัวถูกดําเนินการ) ซึ่งจะถูกคั่นด้วยตัวดําเนินการคํานวณ รายการและไลบรารีจะคํานวณสูตรจากซ้ายไปขวาตามลําดับที่เฉพาะเจาะจงสําหรับตัวดําเนินการแต่ละตัวในสูตร
ความสำคัญของตัวดำเนินการ
ถ้าคุณรวมตัวดําเนินการหลายๆ ตัวไว้ในสูตรเดียวกัน รายการและไลบรารีจะคํานวณตามลําดับที่แสดงอยู่ในตารางต่อไปนี้ ถ้าสูตรมีตัวดําเนินการที่มีลําดับความสําคัญเท่ากัน ตัวอย่างเช่น ถ้าสูตรมีทั้งตัวดําเนินการคูณและตัวดําเนินการหาร รายการและไลบรารีจะประเมินตัวดําเนินการจากซ้ายไปขวา
| ตัวดำเนินการ | คำอธิบาย |
|---|---|
| – | จำนวนติดลบ (เช่น –1) |
| % | เปอร์เซ็นต์ |
| ^ | การยกกำลัง |
| * และ / | การคูณและการหาร |
| + และ – | การบวกและการลบ |
| & | การเชื่อมต่อ (เชื่อมต่อสตริงข้อความสองสตริง) |
| = <><= = ><> | การเปรียบเทียบ |
การใช้วงเล็บ
เมื่อต้องการเปลี่ยนแปลงลําดับของการประเมิน ให้ใส่วงเล็บล้อมรอบส่วนของสูตรที่จะให้คํานวณเป็นลําดับแรก ตัวอย่างเช่น สูตรต่อไปนี้ให้ผลลัพธ์เป็น 11 เนื่องจากรายการหรือไลบรารีคํานวณการคูณก่อนการบวก สูตรจะคูณ 2 ด้วย 3 แล้วจึงนําผลลัพธ์มาบวกกับ 5
=5+2*3
แต่อีกแบบหนึ่ง ถ้าคุณใช้วงเล็บเพื่อเปลี่ยนไวยากรณ์ รายการหรือไลบรารีจะบวก 5 กับ 2 ก่อน แล้วจึงคูณด้วย 3 ได้ผลลัพธ์เป็น 21
=(5+2)*3
ในตัวอย่างด้านล่าง วงเล็บล้อมรอบส่วนแรกของสูตรจะบังคับให้รายการหรือไลบรารีคํานวณ [ต้นทุน]+25 ก่อน แล้วจึงหารผลลัพธ์ที่ได้จากผลรวมของค่าในคอลัมน์ EC1 และ EC2
=([Cost]+25)/SUM([EC1]+[EC2])