ใน Excel รูปแบบการอ้างอิงเซลล์ (Cell Reference) เป็นสิ่งที่สำคัญมาก เช่น A1, $A$1, $A1 หรือ A$! ซึ่งรูปแบบการอ้างอิงเซลล์ที่ต่างๆ เหล่านี้มีผลต่อการเขียนสูตรคำนวณหรืออ้างอิงข้อมูลว่าต้องการให้เซลล์อ้าง อิงในสูตรนั้นอยู่คงที่ หรือเปลี่ยนตำแหน่งไป ถ้าเราเลือกรูปแบบการอ้างอิงได้เหมาะสมแล้ว จะทำให้การเขียนหรือแก้ไขสูตรต่างๆ เป็นไปได้อย่างรวดเร็ว เพราะสามารถเขียนสูตรแค่ครั้งเดียวแล้วคัดลอกไปใช้ในเซลล์อื่นๆ ได้ทั้งตาราง

การเรียกตำแหน่งเซลล์ (Cell Address)

ใน Excel สามารถแบ่งการเรียกตำแหน่งเซลล์ (Cell Address) ซึ่งเป็นการบอกตำแหน่งของเซลล์ว่าอยู่ที่แถวใด คอลัมน์ที่เท่าไหร่ ดังนี้

  • A1 Reference Style เป็นลักษณะที่เห็นบนหน้าจอ Excel เป็นการเรียกชื่อเซลล์โดยนำชื่อของคอลัมน์และแถวมารวมกัน โดยเรียกชื่อคอลัมน์เป็นอักษร เรียงลำดับจาก A ถึง IV – 256 คอลัมน์ และเรียกชื่อแถวเป็นตัวเลข จาก 1 ถึง 65,536 (ใน Excel 2007 เพิ่มจำนวนคอลัมน์เป็น A ถึง XFD – 16,384 คอลัมน์ และจำนวนแถวเพิ่มเป็น 1,048,576 แถว)

ข้อดีของการอ้างอิงแบบนี้ คือ สั้น และง่ายต่อการจดจำ เพราะเป็นการอ้างอิงที่ทุกคนคุ้นเคยอยู่แล้ว ส่วนข้อเสียคือ เนื่องจากตัวอักษรภาษาอังกฤษมี 26 ตัว ทำให้รหัสของคอลัมน์ที่เกิน 26 ต้องมีตัวอักษร 2-3 ตัว ซึ่งอาจทำให้เกิดความสับสนเวลาอ้างอิงสูตรต่างๆ แต่ไม่น่าเป็นปัญหาสำหรับการใช้งานโดยทั่วไป

  • R1C1 Reference Style เป็นรูปแบบการอ้างอิงลำดับที่ของแถวและคอลัมน์ด้วยตัวเลข โดย R แทน Row (แถว) ส่วน C แทน Column (คอลัมน์) เช่น R2C4 แทนแถวที่ 2 คอลัมน์ที่ 4 (หรือ D2)

ในการใช้งานทั่วไปจะใช้การเรียกตำแหน่งเซลล์แบบ A1 ส่วน R1C1 จะมีประโยชน์อย่างมากเมื่อมีการใช้งาน Visual Basic for Applications (VBA เป็นการเขียนโปรแกรมเพื่อช่วยในการทำงาน เช่น การสร้างสูตรพิเศษขึ้นเอง หรือ การให้โปรแกรมทำงานอัตโนมัติ เป็นต้น)

สามารถเลือกการเรียกตำแหน่งเซลล์แบบ R1C1 โดยกำหนดที่ Office Button > Excel options > Formulas > Working with formulas > R1C1 reference style

รูปแบบการอ้างอิงเซลล์

    รูปแบบการอ้างอิงเซลล์มีความสำคัญในการเขียนสูตรเพื่ออ้างอิงข้อมูล ใน Excel แบ่งเป็น 3 รูปแบบ คือ 

  • การอ้างอิงเซลล์แบบคงที่ (Absolute References) มีเครื่องหมาย $ ทั้งตำแหน่งอ้างอิงคอลัมน์และแถว เช่น $A$1 เหมาะสำหรับสูตรที่มีการอ้างอิงค่าคงที่ เช่น เซลล์ที่แทนอัตราภาษีมูลค่าเพิ่ม 7% เวลาคัดลอกเซลล์นี้ไป ตำแหน่งเซลล์อ้างอิงที่อยู่ในสูตรจะไม่เปลี่ยนแปลง
  • การอ้างอิงเซลล์แบบผันแปร (Relative References) ไม่มีเครื่องหมาย $ เลย เช่น A1 เวลาคัดลอกเซลล์นี้ไป ตำแหน่งเซลล์อ้างอิงในสูตร ไม่ว่าจะเป็นแถวหรือคอลัมน์จะเปลี่ยนตาม

    สูตรที่มีการอ้างอิงแบบคงที่และผันแปร

    จากรูปเป็นการคิดค่าคอมมิชชั่นให้กับพนักงาน โดยมีอัตราคงที่ คือ 2% ของยอดขาย สูตรที่ใช้ในเซลล์ C6 คือ =B6*$C$3 โดยให้

    B6 เป็นการอ้างอิงแบบ Relative เวลาคัดลอกสูตรลงมาที่เซลล์ C7 เซลล์อ้างอิงจะเปลี่ยนตาม

    $C$2 เป็นการอ้างอิงแบบ Absolute เนื่องจากเป็นอัตราที่คงที่ คัดลอกสูตรจะอ้างอิงไปยังเซลล์ $C$2 เท่านั้น

  • การอ้างอิงเซลล์แบบผสม (Mixed References) มีเครื่องหมาย $ ที่ตำแหน่งอ้างอิงคอลัมน์หรือแถว เช่น $A1 หรือ A$1 คือ จะมีแถวหรือคอลัมน์ด้านใดด้านหนึ่งที่ต้องการตรึงไว้ให้อยู่กับที่

    การอ้างอิงเซลล์แบบผสม (Mixed References)

    จากรูปเป็นตัวอย่างตารางสูตรคูณ โดยจะเอาเลขในคอลัมน์และแถวมาคูณกัน (คอลัมน์ A และแถวที่ 1) สูตรในเซลล์ B2 คือ =$A2*B$1

    $A2 ใช้ $ เพื่อตรึงคอลัมน์ A ไว้ เพราะจะเอาค่าจากคอลัมน์ A มาคอลัมน์เดียว

    B$1 ใช้ $ เพื่อตรึงแถวที่ 1 ไว้ เพราะเอาค่าจากแถวที่ 1 มาแถวเดียว

    เมื่อคัดลอกสูตรจากเซลล์ B2 ลงมาด้านล่าง ในเซลล์ B3 จะมีสูตรเป็น =$A3*B$1 จะเห็นว่า $A3 มีการตรึงค่าคอลัมน์ A ไว้ ส่วนแถวจะเปลี่ยนไปตามตำแหน่งที่คัดลอกไป ส่วน B$1 มีการตรึงค่าแถวที่ 1 ไว้ เช่นเดียวกับการคัดลอกเซลล์มาด้านขวา ในเซลล์ C2 จะมีสูตรเป็น =$A2*C$1

เวลาที่พิมพ์สูตรสามารถกดปุ่ม F4 ให้เปลี่ยนลักษณะการอ้างอิง เมื่อกด F4 ไปเรื่อยๆ จะเปลี่ยนจาก A1 เป็น $A$1, A$1 และ $A1 ตามลำดับ

โครงสร้างการอ้างอิงเซลล์

  • อ้างอิงภายในชีทเดียวกัน มีลักษณะของโครงสร้าง ดังนี้
    =CellAddress

    โดย พิมพ์เครื่องหมายเท่ากับ แล้วคลิกไปยังเซลล์ที่ต้องการอ้างอิง เช่น ถ้าต้องการอ้างอิงไปยังเซลล์ B10 ก็พิมพ์เครื่องหมายเท่ากับ แล้วคลิกเลือกเซลล์ B10

  • อ้างอิงจากชีทอื่นๆ ภายในไฟล์เดียวกัน มีลักษณะของโครงสร้าง ดังนี้
    =SheetName!CellAddress

    จะเริ่มต้นด้วยชื่อชีท ตามด้วยเครื่องหมาย ! และตำแหน่งเซลล์ที่ต้องการ เช่น Sales!B16 คือ การอ้างอิงไปยังเซลล์ B16 ในชีท Sales

    สามารถนำไปใช้ในสูตรคำนวณได้ตามปกติ เช่น =A10*Sales!B16

    ในกรณีที่ชื่อชีทที่เราอ้างอิง มีช่องว่าง เช่น Sales 2007 ในการอ้างอิงต้องใส่เครื่องหมายคำพูดขีดเดียวลงไปด้วย

    เช่น =A10*‘Sales 2007’!B16

  • อ้างอิงจากไฟล์ Excel ไฟล์อื่น มีลักษณะของโครงสร้าง ดังนี้
    =[WorkbookName]SheetName!CellAddress

    การอ้างอิงไปยังไฟล์อื่น ชื่อไฟล์จะอยู่ในเครื่องหมายวงเล็บที่เป็นเหลี่ยม เช่น [Cost.xls]Sheet1!A10

    ถ้า ชื่อไฟล์ หรือชื่อชีท มีช่องว่าง ต้องมีเครื่องหมายคำพูดขีดด้วยกำกับ โดยคลุมรวมกันทั้งหมดทั้งชื่อไฟล์และชื่อชีท ‘[Cost 2007]Sheet1’!A10 ถ้าไฟล์ที่อ้างอิงปิดอยู่ ต้องใส่ Path ที่อยู่แบบเต็มลงไป (ปกติจะไม่มีการเขียนชื่อ Path แบบเต็มๆ เอง จะเป็นการอ้างอิงข้อมูลจากไฟล์อื่นเมื่อเปิดอยู่ เมื่อไฟล์ที่อ้างอิงปิดลง Excel จะเปลี่ยนการอ้างอิงไปเป็นแบบ Full Path ให้เอง)

    เช่น =‘C:\My Documents\Actual Cost\[Cost 2007.xls]Sheet1’!A10

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

วิธีการอ้างอิงค่าจากเซลล์หรือไฟล์อื่น

  • ใช้เมาส์คลิกไปยังเซลล์ที่ต้องการอ้างอิง หรือใช้ปุ่มลูกศรบนแป้นพิมพ์เลื่อนไปยังที่เซลล์ที่ต้องการอ้างอิงในชีทหรือไฟล์ที่ต้องการ Excel จะอ้างอิงเซลล์ต่างๆ ให้เอง ถ้าใช้เมาส์คลิกไปยังเซลล์อ้างอิงในไฟล์อื่น Excel จะใส่การอ้างอิงแบบ Absolute ($A$1) ให้ ถ้าต้องการคัดลอกสูตรนั้นไปเซลล์อื่นๆ ต้องเปลี่ยนลักษณะการอ้างอิงให้เหมาะสมก่อน
  • ใช้ Paste Special โดยต้องไปคลิก Copy เซลล์ที่ต้องการอ้างอิง จากนั้นจึงใช้ Paste Special โดยเลือกเป็น Paste Link (วิธีนี้คงไม่ค่อยได้ใช้กัน เนื่องจากไม่ค่อยสะดวก)
ถ้าต้องอ้างอิงเซลล์ต่างๆ ในการคำนวณ นอกจากจะอ้างอิงค่าจากเซลล์นั้นๆ แล้ว แนะนำให้ตั้งชื่อให้กับเซลล์ เพื่อความสะดวกในการอ้างอิงค่าต่างๆ

การจัดการไฟล์ที่มีการอ้างอิงข้อมูลจากไฟล์อื่น

เวลาที่เปิดไฟล์ที่มีการอ้างอิงข้อมูลจากไฟล์อื่น Excel จะมีหน้าต่างขึ้นมาถามว่าจะให้อัพเดทข้อมูลจากไฟล์ที่ถูกอ้างอิงหรือไม่

หน้าต่างให้เลือกว่าจะมีการอัพเดทข้อมูลจากไฟล์ที่ถูกอ้างอิงหรือไม่

  • Update เป็นการนำค่าล่าสุดจากไฟล์ที่ถูกอ้างอิงมา ถ้า Excel หาไฟล์นั้นไม่เจอ จะมีหน้าต่างให้ระบุตำแหน่งที่ไฟล์นั้นอยู่
  • Don’t Update จะแสดงค่าเดิมตอนที่บันทึกไฟล์ครั้งล่าสุด

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