ทำสูตร commission ทุกคนโดยคูณกับ rate ใน F1 — copy สูตรลงมา F1 กลายเป็น F2 F3 F4 ผลลัพธ์ผิดหมด แต่ไม่รู้ว่าเพราะอะไร
ใส่ $ ล็อค: =$F$1 ไม่ว่าจะ copy ไปไหน F1 ไม่เลื่อน — สูตรถูกต้องทุกแถวโดยไม่ต้องพิมพ์ใหม่ทีละแถว
$ ใช้เหมือนกัน 100% ใน Excel และ Google Sheets
กฎและวิธีใช้ $ เหมือนกันทุกอย่าง กด F4 เพื่อสลับระหว่าง 4 mode ได้ทั้งใน Excel และ Google Sheets
เข้าใจ 4 แบบ Reference ใน 5 นาที
| รูปแบบ | ชื่อ | ตอน Copy ลง | ตอน Copy ขวา | ใช้เมื่อ |
|---|---|---|---|---|
| A1 | Relative | เลื่อนลง A2, A3 | เลื่อนขวา B1, C1 | สูตรทั่วไป |
| $A$1 | Absolute | ไม่เลื่อน A1 | ไม่เลื่อน A1 | ค่าคงที่: rate, VAT |
| $A1 | Lock Column | เลื่อนลง $A2 | ไม่เลื่อน $A1 | Table lookup row |
| A$1 | Lock Row | ไม่เลื่อน A$1 | เลื่อนขวา B$1 | Table lookup column |
$A$1 — Absolute ล็อคทั้ง Row และ Column
B2=ยอดขาย, $G$1=commission rate 10% — copy สูตรลง 100 แถว G1 ไม่เลื่อน B2 เลื่อนเป็น B3, B4... เหมาะสำหรับค่าคงที่เช่น rate, VAT, เป้าหมาย
F4 — shortcut สลับระหว่าง 4 mode
คลิกที่ reference ใน formula bar (ไม่ใช่คลิกใน cell) แล้วกด F4 วนซ้ำ:
วนไปเรื่อยๆ 4 ครั้งกลับมาที่เดิม ไม่ต้องพิมพ์ $ เอง
Mixed Reference — ทำตารางคูณใน 1 สูตร
A column = แถวนอก (เลื่อนลงได้ แต่ lock column A), Row 1 = column header (เลื่อนขวาได้ แต่ lock row 1) — พิมพ์สูตรนี้เซลล์เดียว copy ขวาและลง สูตรถูกต้องทั้งตาราง
Named Range — ทางเลือกที่อ่านง่ายกว่า $
Data → Named ranges → ตั้งชื่อเซลล์ที่เก็บ rate ว่า "COMMISSION_RATE"
อ่านง่ายกว่า =$B2*$G$1 มาก — Named Range เป็น Absolute โดยอัตโนมัติ ไม่ต้องใส่ $ เพิ่ม
ซ้าย: ไม่มี $ → สูตรผิด, ขวา: ใส่ $G$1 → สูตรถูก ทุกแถว
เทคนิค: รู้ว่าจะใช้แบบไหน
- ถามว่า "cell นี้ควรเลื่อนไหมตอน copy?" — ถ้าไม่ควรเลื่อน ใส่ $ ถ้าควรเลื่อน อย่าใส่
- ค่าคงที่ทุกอย่างใส่ $ — rate ภาษี VAT เป้าหมาย threshold ทุกอย่างที่เป็น "ตัวเดิมทุกแถว" ต้อง Absolute
- VLOOKUP lookup_value ไม่ต้อง $ แต่ table_array ต้องมี $ — =VLOOKUP(A2, $D$2:$F$100, 2, FALSE) — A2 เลื่อนได้ แต่ table ต้องล็อค
- Conditional Formatting ใช้ Mixed Reference — เช่น =$B2>100 ล็อค column B แต่ row เลื่อนได้ เพื่อ apply กฎทีละแถว
ข้อผิดพลาดที่พบบ่อย
ใส่ $ แล้วสูตรยังเลื่อนอยู่
ตรวจว่าใส่ $ ครบหรือเปล่า: $G1 ล็อคแค่ column G แต่ row ยังเลื่อน ต้องการล็อคทั้งคู่ต้องใส่ $G$1
F4 ไม่ทำงานใน Google Sheets
ต้องคลิกที่ reference ใน formula bar ก่อน ถ้า cursor อยู่นอก formula bar F4 จะเป็น shortcut อื่น บน Mac ถ้า F4 ไม่ work ลอง Fn+F4
Conditional Formatting ใช้ $ ผิด
สูตรใน CF: =$B2>100 lock column B ไว้ (เพราะ B คือคอลัมน์ที่ต้องเช็ค) แต่ row ไม่ lock (เพื่อให้ rule apply ทีละแถว) ถ้าใส่ $B$2 ทุกแถวจะเช็คแค่ B2 ไม่ใช่ B3, B4...
คำถามที่ถามบ่อย
ต้องใส่ $ ทุกสูตรไหม?
ไม่จำเป็น ใส่เฉพาะตอนที่จะ copy สูตรและมีเซลล์ที่ไม่ควรเลื่อน ถ้าสูตรอยู่ในเซลล์เดียวไม่ copy ไปไหน ใส่หรือไม่ใส่ก็ได้ผลเหมือนกัน
Named Range กับ $ ต่างกันยังไง ควรใช้อะไร?
Named Range อ่านง่ายกว่า เหมาะสำหรับค่าสำคัญที่อยู่ในไฟล์นาน เช่น VAT_RATE, COMMISSION_PERCENT — $ เหมาะสำหรับ reference ที่ใช้ในสูตรเดียว ไม่ได้ใช้หลายที่
INDIRECT() ใช้แทน $ ได้ไหม?
INDIRECT สร้าง reference จาก text ยืดหยุ่นกว่ามาก แต่ทำให้สูตรช้าลงเพราะ Google ต้อง evaluate ทุกครั้ง สำหรับกรณีทั่วไปใช้ $ หรือ Named Range ดีกว่า ใช้ INDIRECT เฉพาะตอนที่ range ต้องเปลี่ยนแบบ dynamic
ตอน paste สูตรจากที่อื่น $ มีผลไหม?
Paste ปกติ (Ctrl+V) จะ adjust relative reference ตามตำแหน่งใหม่ แต่ $ จะไม่เลื่อน ถ้าต้องการ paste สูตรเหมือนเดิมทุกอย่างให้ copy แล้ว Paste Special → Paste formula only