ในชีทหนึ่งๆมีเซลล์กว่าสิบหกล้านเซลล์ ผู้ใช้ Excel จะเลือกใช้เซลล์ใดที่ใดก็ได้ตามสะดวก แต่ถ้าเอาสะดวกเข้าว่าจนเกินไป เช่น สร้างสูตรไว้ที่เซลล์ A1, B16, F18, G32 ต่อมาเมื่อเวลาผ่านไปสัก 6 เดือน คงไม่มีใครผู้ใดจำได้ว่า เคยสร้างสูตรอะไรไว้ที่เซลล์ไหนหรอกใช่ไหม
แทนที่จะต้องเสียเวลาค้นหาเซลล์ที่ต้องการว่าอยู่ที่ใด ขอให้ใช้เวลาตั้งแต่ตอนต้นออกแบบตารางให้ดีไว้ก่อน จากนั้นสร้างสูตร Smart Formula ลงไปในเซลล์ซ้ายบนสุดเซลล์เดียว แล้ว Copy สูตรนี้กระจายไปใช้งานทั้งตาราง ถ้าสูตรที่สร้างไว้นั้น Smart จริง คือ เป็นสูตรที่ฉลาดจริง สูตรนั้นจะต้องสามารถนำไปใช้ที่ใดก็ได้ในตาราง โดยสูตรจะปรับเปลี่ยนตำแหน่งอ้างอิงให้เอง ตำแหน่งอ้างอิงที่ว่านี้ต้องใส่เครื่องหมาย $ ควบคุมตำแหน่ง Row หรือ Column ไว้อย่างฉลาด
เครื่องหมาย $ นี้จะช่วยให้เราสามารถใช้ Excel ได้อย่างมีประสิทธิภาพ ช่วยประหยัดเวลาทั้งตอนที่สร้าง และจะลดเวลาที่เสียไปในการแกะแฟ้มงาน เพียงแค่แก้ไขสูตรเดิมที่สร้างไว้ในเซลล์ซ้ายมุมบนสุดของตาราง จากนั้น Copy ไปใช้ทั้งตาราง
กดปุ่ม F4 เพื่อสร้าง $
สมมติว่า เราต้องการสร้างสูตรเพื่อนำค่าจากเซลล์ A1 มาใช้ ขอให้เริ่มจากพิมพ์เครื่องหมายเท่ากับลงไปในเซลล์ปลายทาง จากนั้นคลิกลงไปที่เซลล์ A1 จะได้สูตร =A1 โดยไม่ต้องพิมพ์ตำแหน่งเซลล์เอง (วิธีนี้เรียกว่า วิธีชี้ หรือ Pointing)
ในขณะที่ได้สูตร =A1 แสดงอยู่บน Formula Bar ขอให้กดปุ่ม F4 ซ้ำไปเรื่อยๆ จะพบว่ามีเครื่องหมาย $ ใส่แทรกระหว่างตำแหน่งอ้างอิงสลับไปสลับมา จากเดิม A1 >> $A$1 >> A$1 >> $A1 >> A1 .... วนกลับมาเริ่มต้นใหม่
ตำแหน่งอ้างอิงซึ่งมีเครื่องหมาย $ กำกับมี 3 แบบ คือ
- Relative Reference ไม่ได้ใช้ีเครื่องหมาย $ เช่น A1 ทำให้ใช้สูตรได้ที่เซลล์เดิมเซลล์เดียว เพราะเมื่อ Copy ไปใช้ที่เซลล์อื่น จะทำให้ตำแหน่งอ้างอิงเดิมเขยื้อนต่างไปจากเดิม
- Absolute Reference ใช้เครื่องหมาย $ 2 ตัว เช่น $A$1 ล็อกควบคุมตำแหน่งอ้างอิงทั้ง Row และ Column ทำให้คงตำแหน่งอ้างอิงเดิมไว้ตลอด ไม่เปลี่ยนแปลงไปแม้จะ Copy ไปใช้ที่เซลล์อื่น เหมาะสำหรับการอ้างอิงถึงค่าคงที่
- Mixed Reference ใช้เครื่องหมาย $ ล็อกควบคุมตำแหน่ง Row หรือ Column อย่างใดอย่างหนึ่ง เช่น A$1 หรือ $A1 ทำให้เมื่อ Copy สูตรไปใช้ที่เซลล์อื่น ตำแหน่งอ้างอิงจะคุมค่าของเซลล์ตามแนวนอนหรือแนวตั้งอย่างใดอย่างหนึ่ง สูตรแบบ Mixed Reference นี้ เป็นสูตรแบบที่ใช้มากที่สุดในการสร้างตาราง
โครงสร้างตารางสำหรับ Smart Formula
สูตรที่ใช้ตำแหน่งอ้างอิงแบบ Mixed Reference ถือเป็นรูปแบบสูตรซึ่งมีประโยชน์มากที่สุด เนื่องจากสามารถ Copy สูตรจากเซลล์เดียวที่หัวมุมซ้ายบน ไปใช้ได้ทั้งตารางทั้งแนวแนวและแนวตั้ง
แต่ก่อนที่จะใช้สูตรแบบ Mixed Reference ได้นั้น ต้องออกแบบตารางให้เหมาะสมก่อน โดยกระจายค่าตัวแปรไว้ในเซลล์ให้คู่ขนานกับทิศทางของการ Copy ไม่ว่าจะกระจายค่าตัวแปรไว้ด้านบน ด้านล่าง ด้านซ้าย หรือ ด้านขวาก็ตาม และไม่ว่าจะอยู่ในชีทหรือแฟ้มเดียวกันหรือไม่ เราต้องจัดแนวเซลล์ตัวแปรให้คู่ขนานกับแนวที่จะ Copy สูตรไป Paste เสมอ
ในระหว่างที่กดปุ่ม F4 เพื่อกำหนดเครื่องหมาย $ นั้น ผู้ที่สร้างสูตรควรคิดเผื่อไว้ในอนาคตตลอดเวลาว่า สูตรที่ตนกำลังสร้างอยู่ สามารถในไปใช้กับตารางซึ่งมีเงื่อนไขการใช้งานได้ทุกกรณีหรือไม่ สูตรที่ฉลาดจริง อาจเป็นสูตรซึ่งใช้เวลาคิดสร้างนานหน่อย แต่ไม่ต้องย้อนกลับมาเสียเวลาแก้ไขหรือสร้างสูตรใหม่อีกเลย
ข้อควรระวังในการ Copy สูตรที่มี $ ไปใช้ในชีทอื่น
หากต้องการ Copy สูตรซึ่งกำหนดตำแหน่งอ้างอิงไว้แบบ Mixed หรือ Absolute Reference เพื่อนำไปใช้ที่ชีทอื่น ต้องนำตารางสูตรไป Paste ในชีทอื่นตรงตำแหน่งเซลล์ซึ่งซ้ำกับตำแหน่งในชีทเดิม เพื่อป้องกันไม่ให้ตำแหน่งอ้างอิงเพี้ยนไปจากเดิม จากนั้นจึงค่อย Cut ไป Paste ที่ตำแหน่งอื่นได้ตามสบาย
ถ้าต้องการ Paste ลงในแฟ้มอื่น แนะนำให้ใช้วิธี Cut แทน Copy เนื่องจากวิธี Cut จะช่วยแก้ไขตำแหน่งอ้างอิงในสูตรให้เอง โดยไม่ต้องคอยระวังว่านำไป Paste ลงตำแหน่งเซลล์เดิมกับชีทต้นตอหรือไม่