Smart Formula แปลว่า สูตรฉลาด ซึ่งสูตรฉลาดที่ว่านี้จะฉลาดได้ก็ต่อเมื่อใช้ความฉลาดของคุณในการสร้างสูตรขึ้นมา เพื่อให้มีลักษณะการใช้งานได้ตามนี้
| ในชีทหนึ่งๆหรือตารางหนึ่งๆ ต้องมีสูตรต้นฉบับเพียงเซลล์เดียว ซึ่งสามารถนำไปใช้ได้กับเซลล์อื่นๆได้ทั้งตาราง แล้วเมื่อใดที่จำเป็นต้องแก้ไขสูตรใหม่ ก็จัดการแก้ไขสูตรนี้เพียงเซลล์เดียวแล้ว Copy นำไป Paste เพื่อใช้ได้กับทุกเซลล์ในตาราง |
ในโครงสร้างของตัวสูตรฉลาดอาจใช้วิธีกำหนดตำแหน่งอ้างอิงแบบเซลล์เดียวหรือแบบกำหนดขอบเขตตารางก็ได้ โดยไม่ว่าจะใช้วิธีใดก็หลีกหนีการกำหนดตำแหน่งอ้างอิงที่มีเครื่องหมาย $ กำกับตำแหน่ง 3 แบบต่อไปนี้ไปไม่พ้น
- ตำแหน่งอ้างอิงแบบ Absolute Reference มีเครื่องหมาย $ วางไว้ด้านหน้าของทั้งตำแหน่ง Row และ Column เช่น =$A$1 โดยให้ใช้ตำแหน่งอ้างอิงแบบนี้กับกรณีที่ต้องการนำค่าคงที่ไปใช้
- ตำแหน่งอ้างอิงแบบ Relative Reference เป็นตำแหน่งอ้างอิงซึ่งไม่มีเครื่องหมาย $ เช่น =A1 ซึ่งสูตรที่อ้างอิงแบบนี้จะต้องใช้ที่เซลล์เดิมเซลล์เดียวไปตลอด ไม่สามารถ Copy นำไป Paste เพื่อทำให้ได้ค่าเดิมซ้ำที่เซลล์อื่นได้อีก เพราะตำแหน่งอ้างอิงจะเขยื้อนต่างจากตำแหน่งเดิมเสมอ ดังนั้นจึงเป็นการอ้างอิงแบบที่ใช้ในเซลล์สูตรกันน้อยที่สุด แต่กลับถูกนำมาใช้กันมากใน Conditional Formatting หรือ Data Validation
- ตำแหน่งอ้างอิงแบบ Mixed Reference เป็นตำแหน่งอ้างอิงที่นิยมใช้กันมากที่สุด โดยผู้สร้างสูตรต้องเลือกใส่เครื่องหมาย $ ด้านหน้าตำแหน่ง Row หรือ Column เพียงอย่างใดอย่างหนึ่ง เช่น =A$1 เพื่อคงตำแหน่ง Row 1 ไว้ตามเดิมตลอด หรือ =$A1 เพื่อคงตำแหน่ง Column A ไว้ตามเดิมตลอด ไม่ว่าจะ Copy นำสูตรไป Paste ที่ตำแหน่งอื่นก็ตาม
วิธีสร้างสูตรให้มีตำแหน่งอ้างอิงที่มีเครื่องหมาย $ นำหน้าตำแหน่งของ Row และหรือ Column นี้ ให้ใช้วิธี Pointing แล้วกดปุ่ม F4 ซ้ำไปเรื่อยๆจนกว่าจะได้ตำแหน่ง $ ตามต้องการ
- ให้เริ่มจากพิมพ์เครื่องหมายเท่ากับ = ลงไปในเซลล์ที่ต้องการสร้างสูตร (เพื่อแสดงการยึดครองเซลล์นั้นไว้ก่อนว่า เป็นเซลล์ที่จะรับสูตรลงไปเมื่อเรากดปุ่ม Enter)
- จากนั้นใช้วิธีชี้ลงไปในเซลล์ที่ต้องการ (Pointing แปลว่า ชี้) โดยใช้ Mouse คลิกเลือกเซลล์ที่ต้องการ ไม่ว่าจะอยู่ต่างชีทหรือต่างแฟ้มกันก็ตาม เช่น คลิกที่เซลล์ A1 จะได้สูตร =A1
- จากนั้นให้กดปุ่ม F4 ไปเรื่อยๆ จะพบว่ามีเครื่องหมาย $ แทรกลงในสูตรให้เองตามลำดับที่มีการกดปุ่ม F4 ดังนี้ A1 > $A$1 > A$1 > $A1 > A1 > แล้วจะมีเครื่องหมาย $ วนซ้ำไปอีก
- เมื่อได้ตำแหน่ง $ ควบคุมตำแหน่ง Row และหรือ Column ตามต้องการแล้วให้กดปุ่ม Enter เพื่อรับสูตรลงไปในเซลล์ แล้วจะพบว่า Excel จะพากลับมาที่เซลล์ซึ่งพิมพ์เครื่องหมายเท่ากับนำหน้าสูตรเอาไว้ตั้งแต่แรกนั่นเอง
การใส่เครื่องหมาย $ เพื่อเป็นการควบคุมตำแหน่งอ้างอิงไว้ไม่ให้เขยื้อนนี้ นอกเหนือจากการทำให้คุณสามารถ Copy สูตรไปใช้ในเซลล์อื่นได้ทันทีแล้ว ยังเป็นเรื่องน่าสังเกตว่าช่วยทำให้แฟ้มมีขนาดเล็กลงกว่าแฟ้มที่มีสูตรซึ่งไม่มี $ กำหนดไว้เลย (ขอให้ทดลองลบเครื่องหมาย $ ทิ้งจากสูตรในตารางขนาดใหญ่เพื่อเปรียบเทียบดูขนาดแฟ้ม โดยใช้คำสั่ง Replace เครื่องหมาย $ แต่ไม่ได้ Replace with ด้วยเครื่องหมายใด)
นอกจากตำแหน่งอ้างอิงที่ต้องมีเครื่องหมาย $ เป็นองค์ประกอบสำคัญแล้ว ในโครงสร้างของสูตร Smart Formula ยังอาจจำเป็นต้องพึ่งสูตร IF, And, Or หรือสูตรอื่นๆซึ่งทำหน้าที่ตัดสินใจ เพื่อช่วยทำให้สูตรมีเงื่อนไขในการคำนวณต่างไปจากเดิมได้เองทันที เมื่อมีการนำสูตรเดียวกันไปใช้ซ้ำที่ตำแหน่งเซลล์อื่น
ตัวอย่าง Smart Formula แบบใช้ตำแหน่งอ้างอิงเซลล์เดียว
ขอให้สร้างตารางสูตรคูณ ซึ่งนำตัวเลขบนหัวตารางมาคูณกับตัวเลขด้านข้างซ้ายของตาราง
ขั้นตอนการสร้าง
- เริ่มจากเลือกเซลล์ C3 แล้วพิมพ์เครื่องหมายเท่ากับ = ลงไป
- จากนั้นคลิกเซลล์ C2 จะได้สูตร =C2
- กดปุ่ม F4 เพื่อเปลี่ยนสูตรให้เป็น =C$2
- พิมพ์เครื่องหมายคูณ *
- จากนั้นคลิกเลือกเซลล์ B3 จะได้สูตร =C$2*B3
- กดปุ่ม F4 เพื่อเปลี่ยนสูตรเป็น =C$2*$B3
- กดปุ่ม Enter เพื่อรับสูตรลงไปในเซลล์ C3
- Copy ไปทับตาราง C3:F5
คำอธิบาย
- เซลล์ C3 เป็นเซลล์ที่มีสูตร Smart Formula ซึ่งโดยทั่วไปจะเลือกให้เซลล์หัวมุมด้านซ้ายบนของตารางเป็นเซลล์ที่มีสูตร Smart Formula
- คุณอาจเลือกสร้างสูตรคูณโดยใช้เลข 2 นำหน้าแล้วนำไปคูณกับเลข 10 เพื่อทำให้ได้สูตร =C$2*$B3 หรือจะเลือกใช้เลข 10 นำหน้าแล้วนำไปคูณกับเลข 2 ซึ่งจะทำให้เกิดสูตร =$B3*C$2 ก็ได้และทำให้เกิดผลลัพธ์ถูกต้องเท่ากันทั้งสองแบบ แต่ขอแนะนำให้สร้างพฤติกรรมในการสร้างสูตรของตนว่า “ทุกสูตรที่สร้างขึ้นจะนำตำแหน่งอ้างอิงตามแนวนอนขึ้นก่อนตำแหน่งอ้างอิงตามแนวตั้งเสมอ” ซึ่งหลักนี้จะช่วยให้คุณแกะสูตรของตัวเองได้ง่ายขึ้น และหากสังเกตสูตรอื่นๆของ Excel เช่น Index หรือ Offset จะใช้ตำแหน่งอ้างอิงจากแนวนอน Row ขึ้นก่อนตำแหน่งอ้างอิงจากแนวตั้ง Column เช่นกัน
- C$2 ต้องมีเครื่องหมาย $ วางไว้หน้าเลข 2 เนื่องจากตารางสูตรคูณนี้มี Row 2 เป็นตำแหน่งอ้างอิงที่ต้องควบคุมไว้ไม่ให้เปลี่ยน ส่วนตัว C ไม่ต้องมีเครื่องหมาย $ นำหน้า เพื่อปล่อยให้ C เปลี่ยนตำแหน่งเป็น Column D E F หรือ Column อื่นๆตามแต่ว่าจะใช้สูตรนี้หรือไม่
- $B3 ต้องมีเครื่องหมาย $ วางไว้หน้า Column B เนื่องจากตารางสูตรคูณนี้มี Column B เป็นตำแหน่งอ้างอิงที่ต้องควบคุมไว้ไม่ให้เปลี่ยน ส่วนเลข 3 ไม่ต้องมีเครื่องหมาย $ นำหน้า เพื่อปล่อยให้เลข 3 เปลี่ยนตำแหน่งเป็น Row 4 5 6 หรือ Row อื่นๆตามแต่ว่าจะใช้สูตรนี้หรือไม่
- หลักการใส่เครื่องหมาย $ นี้ ให้จำไว้ว่า “ถ้าตำแหน่งอ้างอิงในสูตรมาจากค่าตามแนวนอน ให้ใส่ $ ไว้หน้า Row (เพราะ แนวนอน = Row) แต่ถ้าตำแหน่งอ้างอิงในสูตรมาจากค่าตามแนวตั้ง ให้ใส่ $ ไว้หน้า Column (เพราะ แนวตั้ง = Column)”
ตัวอย่าง Smart Formula แบบใช้ตำแหน่งอ้างอิงตามขอบเขตพื้นที่หัวตาราง
ขอให้สร้างตารางสูตรคูณ ซึ่งนำตัวเลขบนหัวตารางมาคูณกับตัวเลขด้านข้างซ้ายของตาราง
วิธีการสร้างคล้ายกับวิธีแรก แทนที่จะคลิกเลือกเซลล์ C2 หรือเซลล์ B3 วิธีนี้ให้คลิกเลือกเซลล์ C2:F2 และ B3:B5 ซึ่งเป็นหัวตารางด้านบนและด้านซ้ายทั้งหมด แล้วกดปุ่ม F4 เพื่อทำให้เกิดสูตร =$C$2:$F$2*$B$3:$B$5
- เริ่มจากเลือกเซลล์ C3 แล้วพิมพ์เครื่องหมายเท่ากับ = ลงไป
- จากนั้นคลิกเลือกเซลล์บนหัวตารางด้านบนจากเซลล์เลข 2 – 5 จะได้สูตร =C2:F2
- กดปุ่ม F4 เพื่อเปลี่ยนสูตรให้เป็น =$C$2:$F$2
- พิมพ์เครื่องหมายคูณ *
- จากนั้นคลิกเลือกเซลล์บนหัวตารางด้านซ้ายจากเซลล์เลข 10 – 30 จะได้สูตร =$C$2:$F$2*B3:B5
- กดปุ่ม F4 เพื่อเปลี่ยนสูตรเป็น =$C$2:$F$2*$B$3:$B$5
- กดปุ่ม Enter เพื่อรับสูตรลงไปในเซลล์ C3
- Copy ไปทับตาราง C3:F5
คำอธิบาย
- สูตรแบบที่อ้างอิงกับตำแหน่งทั้งหมดบนหัวตารางหรือข้างตาราง แล้วใส่เครื่องหมาย $ ให้กับตำแหน่งทั้ง Row และ Column นี้ ผมตั้งชื่อเรียกว่า สูตรแบบล้อมรั้ว โดยที่สูตร =$C$2:$F$2*$B$3:$B$5 จะทำหน้าที่นำตำแหน่งอ้างอิงบนรั้วตามแนวตั้งฉากที่ตัดกันมาคูณกันทีละตัวให้เอง ทำให้ไม่ต้องคอยพะวงว่าจะต้องเลือกใส่เครื่องหมาย $ เพื่อควบคุมตำแหน่งอ้างอิงตามแนวใดเป็นหลัก
- ถ้าเปรียบเทียบกับสูตรคูณวิธีแรกซึ่งใช้ตำแหน่งอ้างอิงแบบเซลล์เดียว จะพบว่าการใช้สูตรอ้างอิงแบบเซลล์เดียวมีความยืดหยุ่นมากกว่า เช่นเมื่อหัวตารางมีขนาดใหญ่มากขึ้น ก็สามารถ Copy สูตรไปใช้ต่อได้ทันที แต่ถ้าสร้างสูตรแบบล้อมรั้ว จะต้องสร้างสูตรใหม่เพื่อกำหนดตำแหน่งอ้างอิงในสูตรให้มีขนาดขยายตาม (เว้นแต่ว่าคุณทราบวิธีขยายขอบเขตตำแหน่งอ้างอิง ซึ่งจะนำมาอธิบายต่อไปในภายหลัง)
- จุดอ่อนของสูตรแบบล้อมรั้วก็คือ สูตรแบบนี้ใส่เครื่องหมาย $ ควบคุมตำแหน่งแบบ Absolute จึงคำนวณหาคำตอบให้ได้เฉพาะตารางจาก C3:F5 ซึ่งอยู่ภายในขอบเขตของรั้วเป็นแนวของตำแหน่งคงที่ หาก Copy สูตรไปใช้นอกรั้วจะเกิด Error ขึ้น
- หากใช้ Excel 365 พอสร้างสูตรลงไปในเซลล์ C3 จะพบว่าสูตรกระจายลงไปเต็มในพื้นที่ตารางให้เองโดยไม่ต้องสั่ง Copy ไป Paste ต่อแต่อย่างใด (แถมไม่ต้องใส่ $ ก็ยังคำนวณให้ถูกต้อง)
เรื่องนี้เป็นบทเรียนหนึ่งในหลักสูตรเรื่องรีบรู้เพื่อพร้อมใช้ Excel แบบ Fast and Easy และหลักสูตรสุดยอดเคล็ดลับและลัดของ Excel ถือเป็นพื้นฐานสำคัญที่ผู้ทำงานทุกคนต้องใช้เครื่องหมาย $ ให้เป็น และหาทางสร้างสูตรเพียงหัวมุมตารางหรือแนวด้านบนหรือแนวด้านข้างเท่านั้นแล้ว Copy ไปวางในพื้นที่อื่น หากสูตรฉลาดจะพบว่า Excel ปรับตำแหน่งอ้างอิงให้เอง