Khóa tham chiếu

Vị trí trên ribbon: Tab DVD Addin → Nhóm Công thức → ▾ của Tự động tổng

ID lệnh: btnLockCellReferences

Chức năng

Lệnh chuyển đổi kiểu tham chiếu của mọi công thức trong vùng đã chọn giữa bốn dạng: A1, $A1, A$1 và $A$1. Thay vì bấm F4 từng ô, bạn xử lý cả một cột hoặc cả bảng chỉ với một lần Áp dụng.

Rất cần khi bạn vừa dựng xong một công thức mẫu và sắp kéo nó ra hàng trăm dòng: vùng tra cứu, ô hệ số, ô đơn giá gốc phải được khóa, còn ô dữ liệu theo dòng thì phải để trôi. Ngược lại, khi tách một bảng ra file mới, việc gỡ hết dấu $ giúp công thức dịch chuyển đúng theo vị trí mới.

Cách dùng

1. Chọn vùng ô chứa các công thức cần đổi kiểu tham chiếu.
2. Vào tab DVD Addin → nhóm Công thức → bấm ▾ của Tự động tổng → chọn Khóa tham chiếu.
3. Thẻ Khóa tham chiếu (tiêu đề Thêm/xóa khóa ô) của cửa sổ công cụ Công thức mở ra, với mục Loại khóa gồm bốn lựa chọn:
   • Không khóa — A1
   • Khóa hàng — $A1 (thực tế cho ra A$1 — xem Lưu ý)
   • Khóa cột — A$1 (thực tế cho ra $A1 — xem Lưu ý)
   • Khóa cả hàng và cột — $A$1 (mặc định)
4. Bên dưới có ô tick Bỏ qua ô ẩn (mặc định bật).
5. Chọn loại khóa mong muốn rồi bấm Áp dụng. Hộp Xác nhận thay đổi dữ liệu hiện ra — giữ tick sao lưu sheet rồi bấm Tiếp tục; công thức trong vùng chọn được viết lại tại chỗ.
6. Cửa sổ vẫn mở: chọn vùng khác rồi bấm Áp dụng tiếp, hoặc bấm Đóng khi xong.

Video hướng dẫn

Khóa tham chiếu · 6 phút 05 giây · 12 trường hợp · Tải video · File thực hành theo video

Các chương (17)

1. Giới thiệu
2. Mở lệnh
3. Cửa sổ Khóa tham chiếu
4. 1 · Khóa cả hàng và cột
5. 2 · Kéo công thức
6. 3 · Khóa cột – kéo ngang
7. 4 · Khóa hàng – kéo xuống
8. 5 · Bỏ hết dấu $
9. 6 · Chuỗi, sheet khác, tên vùng
10. 7 · Hàm trùng địa chỉ ô
11. 8 · Tham chiếu kiểu Bảng
12. 9 · Nhiều vùng, ô ẩn, gõ tay
13. 10 · Công thức dài
14. 11 · Ghi đè và hoàn tác
15. 12 · Khi lệnh không đổi gì
16. Mẹo
17. Kết thúc

Hướng dẫn bằng hình

Thực hành

Tải file mẫu của lệnh này — dữ liệu y như hình dưới (sheet Thép sàn), mở ra là làm theo được ngay từng bước.

Bước 1. Bôi đen vùng ô có công thức cần đổi kiểu tham chiếu (1) — ví dụ cột khối lượng thép trước khi chép cả khối công thức sang bảng tổng hợp mà không muốn tham chiếu bị trượt. Công thức hiện đang là tham chiếu tương đối (2).

Bước 2. Trên ribbon, mở tab DVD Addin → nhóm Công thức → mở Tự động tổng → chọn Khóa tham chiếu.

Bước 3. Thẻ Khóa tham chiếu của cửa sổ công cụ Công thức mở ra. Chọn Khóa cả hàng và cột — $A$1 (1), để nguyên tick Bỏ qua ô ẩn, rồi bấm Áp dụng (2).

Bước 4. Hộp xác nhận nhắc rằng một kiểu tham chiếu duy nhất sẽ áp cho mọi công thức trong vùng chọn. Giữ tick sao lưu sheet (1) rồi bấm Tiếp tục (2).

Bước 5. Mọi tham chiếu trong công thức của vùng chọn đều thành tuyệt đối (1) — kể cả C4, D4, B4 — nên khối công thức chép đi đâu vẫn trỏ đúng ô cũ. Thanh trạng thái báo số công thức đã đổi (2).

Ví dụ

Bảng tính thép sàn, ô G8 chứa công thức tra khối lượng riêng theo đường kính:

Trước	Sau (chọn Khóa cả hàng và cột)
=E8*F8*VLOOKUP(D8,BANGTHEP!A2:B12,2,0)	=E8*F8*VLOOKUP(D8,BANGTHEP!$A$2:$B$12,2,0)

Nếu bạn chỉ muốn khóa hàng (A$2:B$12) để còn kéo ngang sang các cột tầng khác, chọn mục Khóa hàng thay vì khóa cả hai chiều — dù nhãn mục này ghi $A1, kết quả là A$1.

Lưu ý

• Loại khóa được áp cho toàn bộ tham chiếu trong công thức của vùng chọn — kể cả những ô lẽ ra nên để trôi. Nên chọn vùng hẹp đúng phần cần xử lý.
• Chỉ ô có công thức bị ảnh hưởng; ô nhập số hoặc chữ giữ nguyên.
• Chế độ Không khóa — A1 chỉ gỡ dấu $ khỏi tham chiếu ô. Dấu $ nằm trong chuỗi chữ của công thức (ví dụ mã định dạng "$#,##0" trong hàm TEXT) hay trong tên sheet đặt giữa hai dấu nháy được giữ nguyên.
• Ký hiệu trong nhãn hai mục giữa bị đảo: mục Khóa hàng (nhãn ghi $A1) thực tế khóa hàng, cho ra A$1; mục Khóa cột (nhãn ghi A$1) thực tế khóa cột, cho ra $A1. Hãy chọn theo chữ "hàng"/"cột", không theo ký hiệu.
• Ba chế độ khóa hàng, khóa cột và khóa cả hai dùng bộ chuyển đổi có sẵn của Excel, vốn chỉ nhận công thức dài tối đa 255 ký tự. Công thức dài hơn sẽ được bỏ qua, giữ nguyên như cũ; thông báo khi chạy xong cho biết có bao nhiêu ô như vậy kèm tối đa 5 địa chỉ để bạn sửa tay.
• Tick Bỏ qua ô ẩn giữ nguyên các dòng/cột đang ẩn hoặc bị lọc; bỏ tick nếu muốn xử lý cả những ô đó.
• Cửa sổ ở dạng không khóa bảng tính, có thể đổi vùng chọn giữa các lần bấm Áp dụng.
• Thao tác ghi đè công thức. Ctrl + Z ngay sau lệnh đưa sheet về như trước (nhờ bản sao lưu); xem Khôi phục dữ liệu.

Liên quan

• Hàm làm tròn — cửa sổ cùng kiểu, thêm/bỏ ROUND quanh công thức trong vùng chọn.
• Tham chiếu — tạo công thức tham chiếu giữa hai bảng trước khi khóa vùng tra cứu.
• Kiểm tra lỗi — bật/tắt cảnh báo lỗi công thức khi rà lại vùng vừa sửa.