Absolute กับ Relative Reference ใน Excel ต่างกันยังไง ใส่ $ ($A$1) ตอนไหน

เขียนสูตรในเซลล์แรกได้ถูกต้องเป๊ะ พอลากคัดลอกลงมาทั้งคอลัมน์ ผลลัพธ์กลับเพี้ยนหมด บางช่องขึ้น 0 บางช่องขึ้น #DIV/0! ปัญหานี้เกือบทั้งหมดมาจากเรื่องเดียว คือยังไม่เข้าใจความต่างระหว่างการอ้างอิงเซลล์แบบ Relative (สัมพัทธ์) กับ Absolute (สัมบูรณ์) และไม่รู้ว่าจะใส่เครื่องหมาย $ ตอนไหน บทความนี้จะอธิบายให้เห็นภาพชัดๆ พร้อมตัวอย่างจำลองที่ลองตามได้ทันที

Relative Reference คืออะไร (ค่าเริ่มต้นของ Excel)

ทุกครั้งที่พิมพ์ A1 หรือ B2 ลงไปในสูตรเฉยๆ Excel จะถือว่าเป็นการอ้างอิงแบบ Relative ความหมายคือ Excel ไม่ได้จำว่า “เซลล์ A1” แต่จำเป็น “เซลล์ที่อยู่ทางซ้าย 2 ช่อง แถวเดียวกัน” พอเราคัดลอกสูตรไปวางที่อื่น ตำแหน่งอ้างอิงก็จะขยับตามไปด้วย

ส่วนใหญ่นี่คือสิ่งที่เราต้องการ เช่น ตารางคูณราคากับจำนวนทีละแถว

สูตรที่ D2 คือ =B2*C2 พอคัดลอกลงไป D3 มันจะกลายเป็น =B3*C3 เอง และ D4 กลายเป็น =B4*C4 อัตโนมัติ เพราะการอ้างอิงแบบ Relative ขยับตามแถวที่ย้ายไป นี่คือพฤติกรรมที่ถูกต้องและเป็นประโยชน์

ปัญหาเกิดเมื่อมีเซลล์ที่ต้อง “อยู่กับที่”

ลองเปลี่ยนโจทย์เป็น “คิดภาษี 7% ของทุกรายการ” โดยเก็บค่า 7% ไว้ที่เซลล์เดียวคือ G1 เพื่อให้แก้ทีเดียวเปลี่ยนทั้งตาราง

C2 คำนวณถูก (1,000 × 0.07 = 70) แต่พอคัดลอกลงมา C3 กลายเป็น =B3*G2 ซึ่ง G2 เป็นเซลล์ว่าง เลยได้ 0 และ C4 กลายเป็น =B4*G3 ก็ว่างอีก การอ้างอิง G1 ขยับตามลงมาเป็น G2, G3 ทั้งที่เราอยากให้มันชี้ที่ G1 ตลอด นี่คือจุดที่ต้องใช้ Absolute Reference

Absolute Reference: ล็อกด้วยเครื่องหมาย $

ใส่เครื่องหมาย $ นำหน้าตัวอักษรคอลัมน์และหน้าเลขแถว จะกลายเป็น $G$1 แปลว่า “ล็อกไว้ที่เซลล์ G1 เป๊ะๆ ไม่ว่าจะคัดลอกสูตรไปไว้ที่ไหน”

คราวนี้คัดลอกลงมา C3 เป็น =B3*$G$1 และ C4 เป็น =B4*$G$1 ส่วน B2 ที่ไม่มี $ ยังขยับตามแถวตามปกติ ผลคือทุกแถวคูณกับ 0.07 ที่ G1 ตัวเดียวกันหมด แก้ตัวเลขที่ G1 ทีเดียวทั้งคอลัมน์เปลี่ยนตาม

กด F4 สลับรูปแบบให้เร็ว ไม่ต้องพิมพ์ $ เอง

ขณะแก้ไขสูตร ให้เอาเคอร์เซอร์ไปวางที่ชื่อเซลล์ที่ต้องการ แล้วกดปุ่ม F4 Excel จะวนรูปแบบให้ 4 แบบ กดซ้ำไปเรื่อยๆ จนได้แบบที่ต้องการ

บนโน้ตบุ๊กบางรุ่นต้องกด Fn + F4 ถ้ากด F4 เฉยๆ แล้วไม่ทำงาน หรือถ้าใช้ Excel บนเว็บให้พิมพ์ $ เองได้เลย

Mixed Reference: ล็อกแค่ครึ่งเดียว ใช้ตอนไหน

Mixed Reference คือการล็อกแค่คอลัมน์ ($A1) หรือแค่แถว (A$1) เหมาะกับสูตรที่ต้องคัดลอกทั้งลงและออกด้านข้างพร้อมกัน ตัวอย่างคลาสสิกคือตารางสูตรคูณ ที่คอลัมน์ A เก็บตัวตั้ง 1–5 และแถว 1 เก็บตัวคูณ 1–5

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

สรุปและจุดที่คนพลาดบ่อย

• ลืมล็อกช่วงตารางใน VLOOKUP — เวลาเขียน VLOOKUP แล้วคัดลอกลง ต้องล็อกช่วงตารางที่ค้นหาเป็น $A$1:$C$100 ไม่งั้นช่วงจะเลื่อนหลุด
• กด F4 ผิดจังหวะ — ต้องคลิกวางเคอร์เซอร์ที่ชื่อเซลล์ในสูตรก่อน ถ้าเคอร์เซอร์อยู่ตรงอื่นจะไม่มีผล
• อยากเลี่ยง $ ทั้งหมด — ใช้ Named Range ตั้งชื่อเซลล์ค่าคงที่ เช่นตั้งชื่อ G1 ว่า VAT แล้วเขียน =B2*VAT ชื่อที่ตั้งไว้จะล็อกให้อัตโนมัติเหมือนใส่ $ ทุกตัว

ถ้ายังไม่แน่ใจว่าสูตรในชีตอ้างอิงถูกไหม ลองกด Ctrl + ` เพื่อ แสดงสูตรทั้งชีต จะเห็นภาพรวมว่าตรงไหนล็อก ตรงไหนขยับ อ่านเพิ่มได้ในบทความ 10 สูตร Excel พื้นฐานที่ต้องรู้