การเข้าใจวิธีสร้างสูตรเป็นขั้นตอนพื้นฐานในการเรียนรู้การใช้ Excel นี่คือบทนำเกี่ยวกับไวยากรณ์ของฟังก์ชันและสูตรใน MS Excel
แม้คุณจะไปไกลได้ด้วยการคัดลอกสูตรจากสเปรดชีตอื่น หรือแทรกฟังก์ชัน Excel จากเมนูเฉพาะทาง แต่การมีความรู้พื้นฐานอย่างน้อยเกี่ยวกับวิธีการทำงานของไวยากรณ์จริงๆ เป็นแนวคิดที่ดี เพื่อที่คุณจะสามารถแก้ไขข้อผิดพลาดหรือปรับแต่งสูตรเพื่อให้ได้ประโยชน์สูงสุดจากสเปรดชีตของคุณ
ความหมายของ Syntax (ไวยากรณ์) คืออะไร?
ภาษาโปรแกรมมิ่งถูกออกแบบมาเพื่อให้คุณสั่งงานคอมพิวเตอร์ด้วยประโยคที่มีโครงสร้าง Syntax หมายถึงกฎของภาษาที่มีโครงสร้างดังกล่าว เนื่องจากคอมพิวเตอร์ไม่พอที่จะแยกวิเคราะห์ข้อความที่เบี่ยงเบนจากมาตรฐานที่คาดหวัง
Microsoft Excel แม้จะไม่ใช่ภาษาโปรแกรมมิ่ง แต่ก็มีความสามารถในการสั่งงานโปรแกรมโดยใช้สูตร และเช่นเดียวกับรูปแบบการสั่งงานเครื่องจักรอื่นๆ สิ่งนี้มีไวยากรณ์เฉพาะตัว การเข้าใจไวยากรณ์นี้เป็นกุญแจสำคัญในการใช้ประโยชน์จากความสามารถของแอปพลิเคชันสเปรดชีต
สูตรใน Excel คืออะไร?
สูตร Excel เป็นนิพจน์ที่สามารถป้อนลงในเซลล์แทนข้อมูลจริง โดยใช้การดำเนินการทางคณิตศาสตร์หรือฟังก์ชันอื่นๆ เพื่อสร้างผลลัพธ์ ตัวอย่างเช่น สูตรอาจเขียนขึ้นเพื่อบวกตัวเลขทั้งหมดในคอลัมน์และแสดงผลลัพธ์
ทำไมต้องใช้สูตรสำหรับสิ่งนี้? 毕竟 คุณสามารถคำนวณผลลัพธ์ด้วยตนเองและป้อนลงในฟิลด์ที่ต้องได้
ข้อได้เปรียบของการใช้สูตรแทนค่าคงที่สำหรับการคำนวณคือ ทำให้การอัปเดตสเปรดชีตง่ายขึ้น ทุกครั้งที่คุณทำการเปลี่ยนแปลงข้อมูลพื้นฐานที่นำไปคำนวณ Excel จะคำนวณผลลัพธ์ใหม่ให้คุณโดยอัตโนมัติ
นี่หมายความว่าคุณสามารถขยายสูตรไปยังร้อยหรือพันเซลล์ได้อย่างง่ายดายโดยไม่สูญเสียความแม่นยำ คุณแม้แต่สามารถคัดลอกและวางสูตรเหล่านี้ข้ามสมุดงาน และให้ Excel ปรับพารามิเตอร์โดยอัตโนมัติเพื่อให้เหมาะกับตำแหน่งใหม่
การสร้างสูตรพื้นฐาน
คุณป้อนสูตรในเซลล์โดยเริ่มต้นด้วยเครื่องหมายเท่ากับ แล้วพิมพ์นิพจน์ออกไป ตัวอย่างเช่น การพิมพ์นี้จะให้คุณผลรวมของตัวเลขสองตัวนี้:
= 30 + 40
當然 การคำนวณกับตัวเลขคงที่แบบนี้ไม่ได้มีประโยชน์มากนัก เพื่อบวกตัวเลขที่อยู่ในเซลล์ คุณต้องใช้การอ้างอิงเซลล์ (cell references)
เหมือนช่องบนกระดานหมากรุก เซลล์ของสเปรดชีตสามารถอ้างอิงได้โดยการผสมผสานระหว่างตำแหน่งตัวอักษรและตัวเลข หมายเลขแถว (row number) เพิ่มขึ้นเมื่อคุณลงไปด้านล่างของสเปรดชีต ในขณะที่หมายเลขคอลัมน์ (column letters) ก้าวหน้าไปทางขวา
คุณอาจสังเกตปัญหาที่ชัดเจนแล้ว – ตัวอักษรหมดไวมากพอสมควร ดังนั้นคอลัมน์หลัง Z จะเป็นอย่างไร? แล้วเราแค่เริ่มรวมตัวอักษร ซึ่งให้เรา AA, AB, AC, และต่อไป
โดยใช้การอ้างอิงนี้ คุณสามารถใช้ค่าของเซลล์ใดๆ ของสเปรดชีตในสูตรได้ เช่นนี้:
= A1 + A2
โปรดสังเกตว่าเมื่อคุณกด Enter สูตรจะปรากฏเฉพาะในแถบสูตร (formula bar) – เซลล์เองจะแสดงค่าผลลัพธ์ ดังนั้นคุณต้องเลือกเซลล์เพื่อตรวจสอบว่าค่าของมันมาจากสูตรหรือป้อนด้วยตนเอง
นั่นคือทั้งหมดที่คุณต้องการเพื่อสร้างสูตรพื้นฐานที่ดำเนินการคำนวณด้วยตัวเอง ส่วนที่ดีที่สุดคือ หากคุณแก้ไขเซลล์ที่ถูกอ้างอิงใดๆ ผลลัพธ์จะอัปเดตด้วยตัวเอง
บทนำฟังก์ชัน (Functions)
การอ้างอิงเซลล์และคณิตศาสตร์พื้นฐานพาคุณไปไกลได้ แต่ยังคงเหลือสิ่งที่ต้องการมากอยู่ การเขียนสูตรสำหรับบวกเซลล์ร้อยๆ ช่อง ตัวอย่างเช่น อาจทำให้เบื่อหน่าย แม้คุณจะรู้การอ้างอิง
นี่คือที่มาของฟังก์ชัน (functions) เหมือนภาษาโปรแกรมมิ่งใดๆ Excel รวมฟังก์ชันในตัว (built-in functions) มากมายที่สามารถทำงานซับซ้อนได้ด้วยไวยากรณ์ที่ง่ายกว่า
ฟังก์ชันถูกเรียกใช้โดยชื่อของมัน ตามด้วยอาร์กิวเมนต์ (arguments) ที่ล้อมรอบด้วยวงเล็บ (หรือ parentheses ตามชื่อทางการ) ตัวอย่างเช่น คุณสามารถใช้ฟังก์ชัน SUM เพื่อบวกจำนวนเซลล์มากได้อย่างง่ายดาย:
เพื่อลดสูตรให้สั้นลงอีก เราสามารถระบุช่วง (range) แทนการป้อนการอ้างอิงของทุกเซลล์ ตัวอย่างเช่น คุณสามารถพิมพ์ A1:A4 เพื่อรวมช่วงเซลล์ทั้งหมดจาก A1 ถึง A4 สามารถขยายข้ามแถวได้เช่นกัน และรวมเซลล์ได้ไม่จำกัดจำนวน
SUM ไม่ใช่ฟังก์ชันเดียวที่คุณมีอยู่ รายการเต็มสามารถรับได้โดยการคลิกที่สัญลักษณ์ ƒ𝑥 ถัดจากแถบสูตร แต่นี่คือบางฟังก์ชันที่พบบ่อย:
- AVERAGE – ฟังก์ชันพื้นฐานอีกตัวหนึ่ง ฟังก์ชัน AVERAGE ส่งคืนค่าเฉลี่ยของช่วงค่าที่ป้อนเข้าไป เหมาะสำหรับการคำนวณทางการเงิน
- CONCATENATE – ฟังก์ชันนี้ทำงานกับข้อความแทนตัวเลข ด้วย CONCATENATE คุณสามารถรวมสตริงสองตัวเข้าด้วยกันและส่งออกค่าสุดท้าย เหมาะสำหรับสร้างข้อความกับตัวดำเนินการเงื่อนไขและรวมเข้าด้วยกันสำหรับผลลัพธ์สุดท้าย
- LEN – รูปแบบย่อของ Length (ความยาว) LEN เพียงแค่นับจำนวนตัวอักษรในสตริงที่กำหนด รวมถึงช่องว่าง เฉพาะเจาะจงในการใช้งาน (เช่น การหาว่าเซลล์ว่างจริงๆ มีช่องว่างหรือไม่) แต่ดีกว่านับตัวอักษรด้วยตนเอง
- TRIM – การป้อนช่องว่างเพิ่มเติมในบางเซลล์โดยไม่ได้ตั้งใจเป็นเรื่องปกติ ทำให้ผลลัพธ์ของบางการคำนวณหรือการดำเนินการ CONCATENATE ผิดพลาด ฟังก์ชัน TRIM สามารถใช้ลบช่องว่างเพิ่มเติมทั้งหมด ทิ้งเพียงช่องว่างเดียวระหว่างคำทุกคำ
ฟังก์ชันตรรกะ (Logical Functions)
จนถึงตอนนี้ เราได้เห็นฟังก์ชันต่างๆ หลายตัวที่ทำงานที่มีประโยชน์มาก เช่น SUM, AVERAGE, หรือ TRIM แต่การเปรียบเทียบตรรกะเป็นอย่างไร?
เหมือนภาษาโปรแกรมมิ่งอื่นๆ ไวยากรณ์ของ Excel รวมตัวดำเนินการตรรกะ (logical operators) เช่น IF, AND, และ OR ฟังก์ชันที่มีประโยชน์ที่สุดคือ IF โดยอีกสองตัวมักใช้ภายในนิพจน์ตรรกะ
ไวยากรณ์ของ IF ง่ายมาก: นิพจน์ตรรกะที่ประเมินค่าเป็น TRUE หรือ FALSE ตามด้วยค่าที่จะปฏิบัติในแต่ละเงื่อนไข ตั้งเป็นลำดับ ทั้งหมดนี้อยู่ภายในวงเล็บและคั่นด้วยคอมมา บางอย่างแบบนี้:
โปรดสังเกตว่าอาร์กิวเมนต์สุดท้ายสามารถละเลยได้ ในกรณีนั้น ฟังก์ชันจะส่งคืนค่าเฉพาะเมื่อเงื่อนไขเป็นจริง สามารถใช้สร้างสูตรซับซ้อนโดยการรวมค่าเงื่อนไขในการคำนวณ
อาร์กิวเมนต์แรก ส่วนอื่น สามารถรับรูปแบบมากมาย นิพจน์ตรรกะอาจเป็นเพียงการเปรียบเทียบค่าสองค่าโดยใช้สัญลักษณ์เช่น "<" หรือ "=" แต่ก็อาจเป็นการรวมหลายๆ Statement ด้วยการใช้ AND หรือ OR
นี่เป็นเพราะ AND และ OR ส่งคืนเพียง TRUE หรือ FALSE แทนที่จะเป็นสตริงข้อความหรือค่าตัวเลข คุณใช้ฟังก์ชันเหล่านี้โดยการรวมนิพจน์ตรรกะสองนิพจน์เป็นอาร์กิวเมนต์ AND ส่งคืน TRUE หากทั้งสองอาร์กิวเมนต์ประเมินค่าเป็น TRUE ในขณะที่ OR ส่งคืน TRUE แม้เพียงตัวเดียวเป็น TRUE มิฉะนั้น จะส่งคืน FALSE
โดยใช้ IF, AND, และ OR สามารถสร้างเงื่อนไขซ้อน (nested conditions) ที่ซับซ้อนมากได้ ซึ่งประเมินค่าต่างๆ หลายค่าก่อนจะปล่อยผลลัพธ์สุดท้าย แม้สูตรแบบนี้มักพลาดได้ง่ายเนื่องจากวงเล็บและคอมมาที่มากมาย แนะนำให้แยกการคำนวณและทำให้เรียบง่าย
เพื่อทำให้ง่ายขึ้น Excel รวมฟังก์ชันบางตัวที่ผสม IF กับการดำเนินการที่มีประโยชน์อื่นๆ เช่น SUM หรือ AVERAGE
การคัดลอกสูตร
บ่อยครั้งที่คุณต้องทำการคำนวณประเภทเดียวกันบนช่วงเซลล์ที่ต่างกันในเวิร์กชีต แสดงผลลัพธ์ในเซลล์แยกกัน คุณต้องป้อนสูตรเดิมซ้ำๆ และเปลี่ยนการอ้างอิงเซลล์ทุกครั้งไหม?
ไม่ คุณแค่คัดลอกมัน
เมื่อคุณพยายามวางเซลล์ที่คัดลอกใน Excel (โดยคลิกขวาที่เซลล์เป้าหมาย) คุณจะได้ตัวเลือก Paste หลายอย่าง คุณสามารถวางเซลล์ทั้งหมด คัดลอกแค่ค่า หรือแม้แต่วางแค่สูตร
ยิ่งไปกว่านั้น สูตรที่วางจะถูกปรับเปลี่ยนเพื่อสะท้อนการอ้างอิงสัมพัทธ์ (relative reference) ตามตำแหน่งใหม่ หากคุณคัดลอกสูตรบวกค่าของคอลัมน์ A และวางในเซลล์อื่น มันจะอัปเดตการอ้างอิงด้วยหมายเลขคอลัมน์ใหม่
สิ่งที่คุณต้องรู้เกี่ยวกับไวยากรณ์ฟังก์ชัน Excel
ในฐานะมือใหม่ อย่าพยายามจดจำฟังก์ชัน Excel ทุกตัว ส่วนใหญ่นادراًมีประโยชน์ และคุณสามารถหาไวยากรณ์ของพวกมันได้โดยการคลิกปุ่ม ƒ𝑥 ถัดจากแถบสูตร
แค่จำไว้ไวยากรณ์พื้นฐานของสูตร Excel – เครื่องหมายเท่ากับตามด้วยนิพจน์ที่อาจรวมฟังก์ชันหลายตัว ฟังก์ชันแต่ละตัวรับอาร์กิวเมนต์บางอย่าง (แม้บางฟังก์ชันไม่รับ) และส่งคืนค่าที่สามารถใช้ในฟังก์ชันหรือการคำนวณอื่นได้
แม้การซ้อนฟังก์ชันแบบนี้อาจซับซ้อนได้เร็วมาก ทำให้คุณหลงทางในสายพานของวงเล็บที่หายไป เราขอแนะนำให้ทำให้สูตรทุกตัวเรียบง่ายและตรงไปตรงมา ขยายการคำนวณซับซ้อนไปหลายเซลล์เพื่อให้จัดการได้ง่าย
