การใช้สูตร

สูตรที่ 1
='C:\Dummy\[CashFlow.xls]Year2005'!C$2-'C:\Dummy\[CashFlow.xls]Year2005'!C$3
สูตรที่ 2
=Income-Cost
คนเราน่าจะชอบสูตรที่ 2 เพราะง่ายๆสั้นๆใช่ไหม เมื่อเห็นสูตร =Income-Cost สมองเราจะอ่านออกเสียงขึ้นมาในใจ แล้วตีความหมายได้โดยตรงว่า ... "อ้อ สูตรนี้คำนวณหากำไรขั้นต้น โดยนำยอดขาย จากเซลล์ที่ตั้งชื่อว่า Income มาลบด้วยต้นทุน จากเซลล์ที่ตั้งชื่อว่า Cost" ช่วยทำให้ไม่ต้องเสียเวลาตามเข้าไปหาในตาราง ตรงตำแหน่งอ้างอิง C2 กับ C3 ว่าเป็นเรื่องอะไร
โดยทั่วไปพอเราสร้างสูตรบวกกันไปบวกกันมาหลายๆเซลล์ แถมใช้ Link ข้ามชีทข้ามไฟล์ จะทำให้สูตรยาวขึ้นเรื่อยๆ สร้างปัญหาปวดเศียรเวียนเกล้า จนทำให้เมื่อขึ้นชื่อว่าสูตรของ Excel แล้ว มักบอกต่อกันว่าเป็นของยาก จะสร้างก็ยาก แกะก็ยาก แก้ไขก็ยาก ยิ่งถ้าคนแกะไม่ใช่คนสร้างด้วยแล้ว มักใช้เวลานานมากกว่าที่จะสร้างใหม่ขึ้นมาเองเสียอีก

ควรตั้งชื่อเมื่อใดและอย่างไร

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

ชื่อที่ดีควรเป็นภาษาอังกฤษ
ใช้อักษรตัวใหญ่ผสมตัวเล็ก
ไม่เว้นช่องว่างระหว่างตัวอักษร

แม้เราจะตั้งชื่อเป็นภาษาไทยได้ แต่ถ้านำไฟล์ไปเปิดใน Excel ที่เป็น version ภาษาอื่น จะทำให้ช่อง Name Box บนเมนูด้านซ้ายติดกับ Formula Bar แสดงชื่อภาษาไทยเป็นภาษาขอม
เมื่อชื่อที่ตั้งขึ้นใช้ตัวอักษรตัวใหญ่ผสมตัวเล็ก ย่อมเห็นชัดว่า แตกต่างจากชื่อสูตรหรือชื่อมาตรฐานอื่นของ Excel ซึ่งใช้อักษรตัวใหญ่ทั้งหมด
นอกจากนี้เพื่อให้ชื่อที่ตั้งขึ้นมีความแตกต่างจากตำแหน่งอ้างอิงของ Column ซึ่งมีถึง Column IV จึงควรตั้งชื่อโดยใช้ตัวอักษรตั้งแต่ 3 ตัวขึ้นไป (Excel ยอมรับชื่อที่เป็นตัวอักษรตัวเ้ดียว เว้นตัว C และ R ซึ่งซ้ำกับตำแหน่งอ้างอิงของ Row และ Column)
พยายามหลีกเลี่ยงชื่อซึ่งซ้ำกับชื่อที่ Excel สำรองไว้ใช้เอง เช่น Excel จะไม่ยอมให้ตั้งชื่อชีทว่า History เพราะเป็นชื่อที่สงวนไว้ใช้ในระบบโปรแกรม ชื่อตารางไม่ควรใช้ชื่อว่า Database เพราะ Excel เตรียมไว้ใช้กับคำสั่ง Data > Form หรือชื่อตารางว่า Criteria เพราะจะถูกทำลายทิ้งทันทีเมื่อใช้คำสั่ง Data > Filter > Advanced Filter แบบกำหนด Criteria
ไม่ควรตั้งชื่อใดๆเป็นตัวเลขล้วนๆติดกัน ไม่ว่าจะเป็นชื่อชีท ชื่อไฟล์ ชื่อโฟลเดอร์ หรือชื่ออื่นใด เพราะจะทำให้ยากต่อการแยกแยะเมื่อนำไปใช้อ้างอิงร่วมกับตัวเลขในสูตร
ไม่จำเป็นต้องเสียเวลาคิดตั้งชื่อชีท ชื่อไฟล์ หรือชื่อโฟลเดอร์ให้เสมือนจริงตั้งแต่ต้น ขอให้เลือกชื่อง่ายๆสั้นๆ เหมือนชื่อเล่น ง่ายต่อการสร้างและตรวจสอบความถูกต้อง ให้ใช้ชื่อเล่นไปพลางๆจนกว่าจะสร้างงานเสร็จ หลังจากนั้นสามารถเปลี่ยนชื่อเล่นเป็นชื่อจริง ซึ่งสูตรจะเปลี่ยนตามให้เอง

Range Name

Range Name เป็นชื่อที่ตั้งให้กับเซลล์เดียว หรือพื้นที่ตารางหลายเซลล์

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

เช่น ถ้าตั้งชื่อเซลล์ A1:A5 ว่า Source จะสร้างสูตร =Source เพื่อนำค่าในเซลล์ A1:A5 ไปใช้ได้ ต่อเมื่อสูตร =Source อยู่ใน Row 1:5 ของทุกชีท (หากต้องการใช้ชื่อ Source ในเซลล์อื่นนอกแนวคู่ขนาน ต้องใช้กับสูตรประเภทฐานข้อมูลหรือใช้ในสูตร Array)
วิธีตั้งชื่อ Range Name มีอยู่ 3 วิธี คือ

1. คลิกเลือกเซลล์ที่ต้องการตั้งชื่อ แล้วพิมพ์ชื่อลงไปในช่อง Name Box ซึ่งวิธีนี้ง่ายและสะดวก เพียงแต่จะใช้ Name Box แก้ไขชื่อที่ตั้งไปแล้วไม่ได้อีก
2. ใช้คำสั่ง Insert > Name > Define จากนั้นตั้งชื่อลงไปในช่อง Names in workbook และกำหนดตำแหน่งเซลล์ตามต้องการลงไปในช่อง Refers to
3. ในกรณีที่ต้องการตั้งชื่อให้กับตารางตามคำที่กำกับอยู่บนหัวตารางหรือ ข้างตาราง ให้เลือกพื้นที่ตารางทั้งหมดรวมคำกำกับด้านบนและด้านข้าง แล้วใช้คำสั่ง Insert > Name > Create

ผลจากการตั้งชื่อทั้ง 3 วิธี ชื่อทั้งหมดจะมีตำแหน่งอ้างอิงแบบ Absolute เสมอ

เคล็ดของ Range Name

1. หากต้องการปรับขนาดขอบเขตตำแหน่งอ้างอิงให้ใหญ่ขึ้นหรือเล็กลง ให้ใช้วิธี Insert หรือ Delete Row ก่อน Row ล่างสุดหรือ Column ก่อน Column ขวาสุด (ไม่ควรใช้วิธี Cut เซลล์ล่างสุดหรือขวาสุด เพราะจะขยายได้แต่ใช้กับการลดขนาดอ้างอิงให้เล็กลงไม่ได้)
2. พื้นที่เซลล์เดียวกัน สามารถตั้งชื่อได้หลายชื่อที่ไม่่ซ้ำกัน แต่ Name Box จะแสดงชื่อเรียงตามตัวอักษรที่มาก่อน เมื่อเราคลิกเลือกเซลล์นั้น
3. ในไฟล์เดียวกัน แต่ตารางอยู่ต่างชีทกัน สามารถตั้งชื่อซ้ำกันได้ โดยการ Copy ทั้งชีทหรือโดยใช้คำสั่ง Insert > Name > Create ทั้งนี้ชื่อที่เกิดขึ้นแรกสุดถือเป็น File Level Name แต่ชื่อซ้ำที่เกิดภายหลังถือเป็น Sheet Level Name ซึ่งเมื่อต้องการสร้างสูตรอ้างอิงในชีทอื่น จะต้องพิมพ์ชื่อชีทกำกับไว้ด้วยเสมอ เช่น Sheet2!Source
4. ชื่อ Range Name สามารถตั้งชื่อให้อ้างอิงถึงเซลล์ที่อยู่ในไฟล์อื่น โดยใช้คำสั่ง Insert > Name > Define หรือเมื่อ Cut เซลล์ที่ตั้งชื่อไว้ไปยังไฟล์อื่น ตำแหน่งอ้างอิงใน Insert > Name > Define จะปรับตามให้เอง
5. เมื่อปรับตำแหน่งอ้างอิงเปลี่ยนจาก Absolute เป็นแบบ Mixed หรือ Relative Reference จะช่วยให้ Range Name กลายเป็น Formula Name ไปกลายๆและสามารถย้ายตำแหน่ง หรือขยายขนาดได้เองอย่างไม่น่าเชื่อ เช่น ชื่อ Source เป็นชื่อที่ Refers to : $A$1 แต่พอปรับตำแหน่งอ้างอิงใหม่เป็น A$1 จะทำให้ทุกเซลล์ใน Row 1 ไม่ว่าอยู่ใน Column ใดตั้งแต่ Column A ถึง IV ได้ชื่อว่าเป็น Range Name ชื่อว่า Source ได้ทุก Column

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

Formula Name

Formula Name เป็นชื่อที่ต้องใช้คำสั่ง Insert > Name > Define ตั้งชื่อขึ้นมาใช้งาน โดยกำหนดให้ใส่สูตรที่ต้องการลงไปช่อง Refers to โดยตรง หรือจะสร้างสูตรแล้วตรวจสอบความถูกต้องให้เสร็จในเซลล์ก่อน จากนั้นจึง Copy สูตรแล้วมา Paste ลงในช่อง Refers to ก็ได้
สูตรที่ใช้ใน Formula Name อาจอ้างอิงไปยังเซลล์ที่ต้องการใช้คำนวณในสูตรโดยตรง เช่น ตั้งชื่อ Formula Name ว่า Profit มีสูตร =$C$2-$C$3 หรือใช้ชื่อ Range Name แทน เช่น =Income-Cost

เคล็ดของ Formula Name

1. เมื่อใดที่ตำแหน่งอ้างอิงที่ใช้ในสูตร ไม่ได้ใช้การอ้างอิงแบบ Absolute โดยเลือกใส่เครื่องหมาย $ เพียงตัวเดียวหรือไม่ใส่เครื่องหมาย $ เลย จะพบว่าตำแหน่งอ้างอิงนั้นมีความสัมพันธ์ กับตำแหน่งเซลล์ในขณะที่คลิกเมนู Insert เช่น ในตัวอย่างหนึ่งของหลักสูตรสุดยอดเคล็ดลับและลัดของ Excel มีสูตรชื่อ MultiplyFML ซึ่งจะแสดงสูตร =E$5*$D6 ต่อเมื่อคลิกในเซลล์ E6 เท่านั้น หากคลิกที่เซลล์อื่น จะพบว่าตำแหน่งอ้างอิงต่างไปจาก =E$5*$D6 ทันที
2. เมื่อไม่ได้ใช้ตำแหน่งอ้างอิงแบบ Absolute จะทำให้สูตรทำงานได้ยืดหยุ่นมากขึ้น ไม่จำกัดตำแหน่งเซลล์ตามแนวที่ไม่ได้ใส่ $ ล็อกไว้ เช่น สูตรชื่อ MultiplyFML แม้เริ่มต้นสร้างให้ =E$5*$D6 แต่สามารถนำสูตรชื่อนี้ไปใช้ได้ทุกเซลล์ โดยจะทำหน้าที่นำค่าจากเซลล์ใน Row ที่ 5 มาคูณกับค่าในเซลล์ใน Column D ได้ตลอด หากใช้สูตรในเซลล์ Z100 ก็จะได้สูตร =Z$5*$D100
3. Excel จะปรับขนาด Range Name ให้ทันที เมื่อเปลี่ยนตำแหน่งอ้างอิงในช่อง Refers to ที่เป็นตำแหน่งตารางคงที่ของ Range Name เป็น Dynamic Range โดยนำสูตร Offset, Indirect, Address, Row, Column มาประยุกต์ใช้แทน
4. ในช่อง Refers to ของ Formula Name ไม่จำเป็นต้องอ้างถึงสูตรเสมอไป อาจอ้างถึงชื่อ Formula Name หรือ Range Name ใด ๆซ้อนกันก็ได้
5. Formula Name จะกลายเป็น Constant Name เมื่อกำหนดให้เท่ากับตัวเลขหรือตัวอักษร เช่น =9 หรือ ="Accept"

วิธีใช้ชื่อ Range Name, Formula Name, Constant Name

1. พิมพ์ = แล้วพิมพ์ชื่อด้วยตัวเล็กตลอด เช่น พิมพ์ =multiplyfml พอกด Enter จะพบว่าชื่อเปลี่ยนเป็นตัวใหญ่ผสมตัวเ็ล็กตามแบบที่ตั้งไว้ กลายเป็น =MultiplyFML
2. ถ้าไม่ต้องการพิมพ์เอง ให้เริ่มจากพิมพ์ = แล้วคลิกเลือกเซลล์หรือพื้นที่ตารางส่วนที่ตั้งชื่อไว้ให้ครบ Excel จะนำชื่อมาใส่ต่อให้เอง
3. ถ้าจำตำแหน่งเซลล์ไม่ได้ หรือเป็นเซลล์ที่อยู่ต่างชีท ให้กดปุ่ม F3 แล้วดับเบิลคลิกชื่อที่ต้องการ แต่ถ้าเป็นชื่อที่อยู่ในไฟล์อื่น ให้เริ่มจากพิมพ์ = แล้วใช้เมนู Window ไปที่ไฟล์ที่ต้องการก่อน จากนั้นจึงกดปุ่ม F3 แล้วเลือกชื่อที่ต้องการ
4. หากต้องการไปที่เซลล์ที่ตั้งชื่อไว้ ให้คลิกเลือกชื่อที่ช่อง Name Box หรือกดปุ่ม F5 Goto แล้วเลือกชื่อที่ต้องการไป
5. Range Name ที่ทำงานแบบ Formula Name เป็น Dynamic Range จะไม่แสดงให้เห็นในช่อง Name Box หรือรายชื่อใน Goto ของ F5 เมื่อต้องการไปที่ Dynamic Range ให้กดปุ่ม F5 จากนั้นพิมพ์ชื่อ Formula Name ที่ต้องการลงไปด้วยตนเอง แล้วกด Enter

ข้อควรระวังในการใช้ชื่อ

1. สูตรซึ่งสร้างขึ้นก่อนโดยใช้ตำแหน่งเซลล์โดยตรง ต่อมาจึงกำหนด Range Name ขึ้นภายหลัง จะยังคงเป็นสูตรที่ใช้ตำแหน่งเซลล์เช่นเดิม จนกว่าจะใช้คำสั่ง Insert > Name > Apply จึงจะเปลี่ยนตำแหน่งอ้างอิงในสูตรให้กลายเป็นชื่อ
2. ก่อนการ Delete Row หรือ Column หรือลบชีททิ้ง ควรใช้คำสั่ง Insert > Name > Paste แล้วกดปุ่ม Paste List เพื่อสรุปชื่อทั้งหมดที่มีอยู่ในไฟล์นั้นก่อน เพื่อทราบตำแหน่งเซลล์ที่ถูกใช้งานซึ่งห้ามลบทิ้ง
3. การสร้างสูตรอ้างอิงถึงเซลล์ที่ตั้งชื่อไว้โดยการพิมพ์ื = แล้วคลิกเซลล์ จะทำให้ Excel นำชื่อมาใส่ต่อให้เองในสูตร แต่ถ้าจำเป็นต้องสร้างสูตรในเซลล์หลายเซลล์พร้อมกัน จะพบว่า เมื่ิอพิมพ์ = แล้วคลิกเลือกเซลล์ที่ตั้งชื่อไว้ จะไม่ได้ชื่อ แต่จะได้ตำแหน่งเซลล์ตามปกติ
4. ไม่ควร Merge เซลล์ที่เกี่ยวข้องกับการคำนวณ ซึ่งแน่ล่ะว่าย่อมไม่ควรตั้งชื่อให้กับเซลล์ที่ Merge ไว้ เพราะโดยทั่วไปเรานำชื่อไปใช้ต่อในการคำนวณ
5. หากต้องการ Copy ตารางที่มีเซลล์ที่ตั้งชื่อไว้นำไปใช้ในไฟล์อื่น โดยต้องการชื่อ Range Name ติดไปด้วย ขอให้ Copy ไปทั้งชีทหรืออย่างน้อยต้องเลือกพื้นที่ตารางที่จะ Copy ให้รวมถึงเซลล์สูตรและเซลล์ที่ตั้งชื่อไว้ไปใช้พร้อมกัน และเมื่อนำไป Paste ต้อง Paste ลงในเซลล์ตามตำแหน่งเดิมก่อน จากนั้นจึงค่อย Cut ย้ายตำแหน่งได้ตามใจ