เขียนสูตรในเซลล์แรกได้ถูกต้องเป๊ะ พอลากคัดลอกลงมาทั้งคอลัมน์ ผลลัพธ์กลับเพี้ยนหมด บางช่องขึ้น 0 บางช่องขึ้น #DIV/0! ปัญหานี้เกือบทั้งหมดมาจากเรื่องเดียว คือยังไม่เข้าใจความต่างระหว่างการอ้างอิงเซลล์แบบ Relative (สัมพัทธ์) กับ Absolute (สัมบูรณ์) และไม่รู้ว่าจะใส่เครื่องหมาย $ ตอนไหน บทความนี้จะอธิบายให้เห็นภาพชัดๆ พร้อมตัวอย่างจริงจาก Excel ที่ลองตามได้ทันที
🎬 ดูวิดีโอสอน (1 นาที 38 วินาที) บน Excel จริง หรืออ่านขั้นตอนละเอียดด้านล่าง
Relative Reference คืออะไร (ค่าเริ่มต้นของ Excel)
ทุกครั้งที่พิมพ์ A1 หรือ B2 ลงไปในสูตรเฉยๆ Excel จะถือว่าเป็นการอ้างอิงแบบ Relative ความหมายคือ Excel ไม่ได้จำว่า “เซลล์ A1” แต่จำเป็น “เซลล์ที่อยู่ทางซ้าย 2 ช่อง แถวเดียวกัน” พอเราคัดลอกสูตรไปวางที่อื่น ตำแหน่งอ้างอิงก็จะขยับตามไปด้วย
ส่วนใหญ่นี่คือสิ่งที่เราต้องการ เช่น ตารางคูณราคากับจำนวนทีละแถว
สูตรที่ D2 คือ =B2*C2 พอคัดลอกลงไป D3 มันจะกลายเป็น =B3*C3 เอง และ D4 กลายเป็น =B4*C4 อัตโนมัติ เพราะการอ้างอิงแบบ Relative ขยับตามแถวที่ย้ายไป นี่คือพฤติกรรมที่ถูกต้องและเป็นประโยชน์
ปัญหาเกิดเมื่อมีเซลล์ที่ต้อง “อยู่กับที่”
ลองเปลี่ยนโจทย์เป็น “คิดภาษี 7% ของทุกรายการ” โดยเก็บค่า 7% ไว้ที่เซลล์เดียวคือ E2 เพื่อให้แก้ทีเดียวเปลี่ยนทั้งตาราง
ใส่สูตร =B2*E2 ที่ C2 แล้วลากลงมา
C2 คำนวณถูก (1,000 × 0.07 = 70) แต่พอคัดลอกลงมา C3 กลายเป็น =B3*E3 ซึ่ง E3 เป็นเซลล์ว่าง เลยได้ 0 และ C4 กลายเป็น =B4*E4 ก็ว่างอีก การอ้างอิง E2 ขยับตามลงมาเป็น E3, E4 ทั้งที่เราอยากให้มันชี้ที่ E2 ตลอด นี่คือจุดที่ต้องใช้ Absolute Reference
Absolute Reference: ล็อกด้วยเครื่องหมาย $
ใส่เครื่องหมาย $ นำหน้าตัวอักษรคอลัมน์และหน้าเลขแถว จะกลายเป็น $E$2 แปลว่า “ล็อกไว้ที่เซลล์ E2 เป๊ะๆ ไม่ว่าจะคัดลอกสูตรไปไว้ที่ไหน”
แก้สูตรที่ C2 เป็น =B2*$E$2 (พิมพ์ =B2*E2 แล้วกด F4 ขณะเคอร์เซอร์อยู่ที่ E2 ก็ได้) แล้วลากลงมาอีกครั้ง
คราวนี้คัดลอกลงมา C3 เป็น =B3*$E$2 และ C4 เป็น =B4*$E$2 ส่วน B2 ที่ไม่มี $ ยังขยับตามแถวตามปกติ ผลคือทุกแถวคูณกับ 0.07 ที่ E2 ตัวเดียวกันหมด แก้ตัวเลขที่ E2 ทีเดียวทั้งคอลัมน์เปลี่ยนตาม
กด F4 สลับรูปแบบให้เร็ว ไม่ต้องพิมพ์ $ เอง
ขณะแก้ไขสูตร ให้เอาเคอร์เซอร์ไปวางที่ชื่อเซลล์ที่ต้องการ แล้วกดปุ่ม F4 Excel จะวนรูปแบบให้ 4 แบบ กดซ้ำไปเรื่อยๆ จนได้แบบที่ต้องการ
| กด F4 ครั้งที่ | รูปแบบ | ความหมาย |
| 1 | $A$1 |
ล็อกทั้งคอลัมน์และแถว (Absolute เต็ม) |
| 2 | A$1 |
ล็อกเฉพาะแถว คอลัมน์ยังขยับ (Mixed) |
| 3 | $A1 |
ล็อกเฉพาะคอลัมน์ แถวยังขยับ (Mixed) |
| 4 | A1 |
กลับมาเป็น Relative ปกติ |
บนโน้ตบุ๊กบางรุ่นต้องกด Fn + F4 ถ้ากด F4 เฉยๆ แล้วไม่ทำงาน หรือถ้าใช้ Excel บนเว็บให้พิมพ์ $ เองได้เลย
Mixed Reference: ล็อกแค่ครึ่งเดียว ใช้ตอนไหน
Mixed Reference คือการล็อกแค่คอลัมน์ ($A1) หรือแค่แถว (A$1) เหมาะกับสูตรที่ต้องคัดลอกทั้งลงและออกด้านข้างพร้อมกัน ตัวอย่างคลาสสิกคือตารางสูตรคูณ ที่คอลัมน์ A เก็บตัวตั้ง 1–3 และแถวที่ 6 เก็บตัวคูณ 1–4
พิมพ์สูตรเดียวที่ B7 แล้วลากไปทางขวาและลงมาให้เต็มตาราง
สูตรเดียวที่ B7 คือ =$A7*B$6 คัดลอกไปเต็มตารางได้เลย เพราะ $A7 ล็อกให้ดึงตัวตั้งจากคอลัมน์ A เสมอ (แต่แถวขยับได้) ส่วน B$6 ล็อกให้ดึงตัวคูณจากแถวที่ 6 เสมอ (แต่คอลัมน์ขยับได้) ถ้าใช้ $A$1 ล็อกหมดทั้งคู่ ทั้งตารางจะได้ค่าเดียวกันหมด
สรุปและจุดที่คนพลาดบ่อย
| รูปแบบ | เมื่อคัดลอกสูตร | ใช้กับ |
A1 |
ขยับทั้งคอลัมน์และแถว | คำนวณทีละแถว/คอลัมน์ตามปกติ |
$A$1 |
ไม่ขยับเลย | ค่าคงที่ เช่น อัตราภาษี อัตราแลกเปลี่ยน ยอดรวม |
$A1 |
ล็อกคอลัมน์ แถวขยับ | ตารางที่คัดลอกออกด้านข้าง |
A$1 |
ล็อกแถว คอลัมน์ขยับ | ตารางที่คัดลอกลงล่าง |
- ลืมล็อกช่วงตารางใน VLOOKUP — เวลาเขียน VLOOKUP แล้วคัดลอกลง ต้องล็อกช่วงตารางที่ค้นหาเป็น
$A$1:$C$100ไม่งั้นช่วงจะเลื่อนหลุด - กด F4 ผิดจังหวะ — ต้องคลิกวางเคอร์เซอร์ที่ชื่อเซลล์ในสูตรก่อน ถ้าเคอร์เซอร์อยู่ตรงอื่นจะไม่มีผล
- อยากเลี่ยง $ ทั้งหมด — ใช้ Named Range ตั้งชื่อเซลล์ค่าคงที่ เช่นตั้งชื่อ E2 ว่า
VATแล้วเขียน=B2*VATชื่อที่ตั้งไว้จะล็อกให้อัตโนมัติเหมือนใส่ $ ทุกตัว
ถ้ายังไม่แน่ใจว่าสูตรในชีตอ้างอิงถูกไหม ลองกด Ctrl + ` เพื่อ แสดงสูตรทั้งชีต จะเห็นภาพรวมว่าตรงไหนล็อก ตรงไหนขยับ อ่านเพิ่มได้ในบทความ 10 สูตร Excel พื้นฐานที่ต้องรู้
