คนทั่วไปมักมองข้ามเรื่องพื้นฐานที่นึกว่าไม่สำคัญหรือไม่เคยคิดว่า Excel ทำได้ไปใช้สูตร Offset กำหนดขอบเขตตารางกันเลย โดยหารู้ไม่ว่าเมื่อนำตำแหน่งเซลล์ของเซลล์เพียงเซลล์เดียวหรือเซลล์ในแนวเดียวกันมาใช้ให้เป็น จะทำให้เกิดขอบเขตตารางแบบยืดหยุ่นได้เช่นกัน แถมเป็นวิธีที่ง่ายกว่าเสียอีกเพราะไม่จำเป็นต้องใช้สูตร Offset ให้ยุ่งยาก
ตัวอย่าง
ตัวอย่างนี้เป็นตารางสูตรคูณซึ่งนำตัวเลข 2, 3, 4, 5 บนหัวตารางมาคูณกับตัวเลข 10, 20, 30 ด้านข้างซ้ายของตาราง โดยใช้วิธีตั้งชื่อเซลล์ C2:F2 บนหัวตารางว่า Top และตั้งชื่อเซลล์ B3:B5 ด้านซ้ายของตารางว่า Left
ในเซลล์ C3 สร้างสูตร =Top*Left แล้วเมื่อ copy เซลล์ C3 ไป paste ลงไปในเซลล์ตั้งแต่ C3:G7 ย่อมพบว่าสูตร =Top*Left สามารถคำนวณหาผลคูณได้ถูกต้องเฉพาะพื้นที่ C3:F5 ซึ่งเป็นตารางที่อยู่ในขอบเขตแนวคู่ขนานตัดกันของ Top และ Left เท่านั้น ส่วนสูตรในเซลล์นอกแนวคู่ขนานของ Top กับ Left ให้คำตอบเป็น error ว่า #VALUE!
แทนที่จะดัดแปลง Top กับ Left ให้เป็น Dynamic Range ด้วยสูตร Offset เพื่อทำให้ Top กับ Left ขยายขอบเขตให้มีขนาดตามค่าตัวเลขบนหัวตารางและตัวเลขข้างซ้ายของตารางที่อาจจะมีเพิ่มขึ้น ให้ดัดแปลงตำแหน่งอ้างอิงของ Top กับ Left ให้เป็น Dynamic Cell โดยมีขั้นตอนดังนี้
- คลิกเลือกเซลล์ C3
- เลือกคำสั่ง Formulas > Name Manger
- แก้ตำแหน่งอ้างอิงของ Top =C$2 และ Left =$B3
เมื่อ Top =C$2 จะทำให้ Excel ถือว่าทุกเซลล์ที่อยู่ใน row 2 มีชื่อว่า Top และเมื่อ Left =$B3 ย่อมหมายถึงทุกเซลล์ใน column B มีชื่อว่า Left ทำให้สามารถใช้สูตร Top*Left ได้ทุกที่ในชีทโดยไม่จำเป็นต้องกำหนดขอบเขตตารางของ Top กับ Left ตามขนาดข้อมูลอีกต่อไป
หากต้องการย้ายตารางสูตรคูณนี้ไปยัง row อื่น ไม่สามารถใช้วิธี Cut เฉพาะพื้นที่ตารางสูตรคูณ B2:G7 แต่ต้องเลือก Cut row 2:7 ไปวางที่ row อื่น หรือใช้วิธี Insert row เพื่อขยับตำแหน่งอ้างอิงของ Top ทั้งแนว row ที่ 2 ไปพร้อมกัน เพราะ Top มิได้หมายถึงแค่พื้นที่บางส่วนของ หากครอบคลุมพื้นที่ทั้ง row ที่ 2 ทั้งหมด
ตัวอย่าง
ในกรณีที่ต้องการเปลี่ยนสี column ในตารางสูตรคูณให้มีสีเฉพาะแนวของ column ที่อยู่ใต้ตัวเลขบนหัวตารางที่เป็นเลขคี่
ให้เลือกพื้นที่ตัวเลขผลคูณตั้งแต่ C3:G7 แล้วใช้คำสั่ง Home > Conditional Formatting แบบ Use a formula to determine which cells to format แล้วใช้สูตรต่อไปนี้
=MOD(C$2,2)=1
ขอให้สังเกตตำแหน่งเซลล์ C$2 ในสูตรนี้ว่าเป็นเซลล์แรกบนหัวตารางด้านบนซึ่งอยู่ในแนวคู่ขนานกับพื้นที่ C3:G7 ที่เลือกไว้ และต้องกำหนดให้ใส่เครื่องหมาย $ ไว้หน้าเลข 2 แต่ไม่ได้ใส่ $ หน้าตัว C เพื่อควบคุมให้ Excel นำตำแหน่งเซลล์ถัดไปใน row ที่ 2 มาใช้ในการคำนวณ ซึ่งได้แก่เซลล์ C2, D2, E2, F2, และ G2 นั่นคือตัวเลข 2, 3, 4, 5, 6 ตามลำดับ ซึ่งเมื่อนำมาหารด้วยเลข 2 แล้ว หากเหลือเศษเท่ากับ 1 ย่อมแสดงว่าเลขในเซลล์บนหัวตารางด้านบนเซลล์นั้นเป็นเลขคี่ ทำให้ Conditional Formatting เปลี่ยนสีเซลล์ผลคูณที่อยู่ใต้เลข 3 และ 5 ซึ่งเป็นเลขคี่
ประโยชน์ของวิธีทำให้เซลล์ C$2 เป็น Dynamic Cell จะช่วยทำให้ใช้คำสั่ง Conditional Formatting กำหนดเงื่อนไขให้กับทั้งตารางได้พร้อมกันในทีเดียว โดยไม่จำเป็นต้องเสียเวลาใช้คำสั่งนี้กับทีละเซลล์หรือทีละ column และยังสามารถนำหลักการนี้ไปใช้กับคำสั่ง Data Validation ที่กำหนด Allow แบบ Custom โดยใช้สูตรตรวจสอบการบันทึกค่าได้เช่นกัน
