Tham chiếu tuyệt đối trong Excel: Ngăn tham chiếu ô bị thay đổi

Theo mặc định, Excel sử dụng tham chiếu tương đối, nghĩa là khi bạn sao chép hoặc kéo một công thức sang ô khác, các tham chiếu sẽ tự động điều chỉnh dựa trên vị trí của công thức.

Tuy nhiên, đôi khi bạn muốn một tham chiếu ô luôn cố định, bất kể công thức được sao chép hoặc di chuyển đi đâu. Trong bài viết này, tôi sẽ chỉ cho bạn cách tham chiếu tuyệt đối trong Excel cho phép khóa tham chiếu tới một ô cụ thể, đảm bảo công thức luôn tham chiếu đến cùng một ô, dù đặt ở vị trí mới. Sau bài viết này, hãy đăng ký khóa học Introduction to Excel của chúng tôi để tiếp tục học.

Tham chiếu tuyệt đối trong Excel là gì?

Tham chiếu tuyệt đối trong Excel là kiểu tham chiếu ô luôn giữ nguyên, bất kể công thức được sao chép hoặc di chuyển ở đâu. Khác với tham chiếu tương đối, vốn điều chỉnh theo vị trí mới của công thức, tham chiếu tuyệt đối luôn trỏ đến cùng một ô. Tham chiếu tuyệt đối được tạo bằng cách đặt ký hiệu đô la ($) trước cả chữ cột và số hàng. Ví dụ, $A$1 tham chiếu tuyệt đối đến ô A1.

Ở mức khái quát, Excel hỗ trợ tham chiếu tương đối, tự động điều chỉnh khi sao chép, và tham chiếu tuyệt đối, luôn cố định. Ngoài ra, tham chiếu hỗn hợp khóa hoặc cột hoặc hàng, nhưng không phải cả hai. Chúng ta sẽ bàn chi tiết về các loại tham chiếu này ở phần sau của bài viết.

Cách tạo tham chiếu tuyệt đối

Tạo tham chiếu tuyệt đối trong Excel khá đơn giản và có thể thực hiện thủ công hoặc bằng phím tắt.

• Nhập một công thức vào ô. Ví dụ, =A1+B1.

• Thêm ký hiệu đô la ($) thủ công vào ô bạn muốn cố định. Thao tác này sẽ chuyển A1 thành $A$1.

• Nhấn Enter.

Giả sử bạn có dữ liệu dưới đây để tính giá sau chiết khấu của một mặt hàng. Bạn nhập công thức =$A$2*B2 vào ô C2. Khi bạn kéo công thức xuống, những điều sau sẽ xảy ra:

• Tham chiếu $A$2 (tham chiếu tuyệt đối) sẽ được giữ cố định, luôn tham chiếu đến ô A2.

• Tham chiếu B2 (tham chiếu tương đối) sẽ đổi thành B3, B4, v.v., khi bạn kéo xuống.

Ví dụ về tham chiếu tuyệt đối trong Excel. Hình ảnh do Tác giả cung cấp.

Khi chỉnh sửa công thức, đặt con trỏ lên một tham chiếu ô và nhấn F4 sẽ chuyển đổi luân phiên giữa các tùy chọn sau:

• $A$1: Tham chiếu tuyệt đối khóa cả cột và hàng.

• A$1: Tham chiếu hỗn hợp chỉ khóa hàng.

• $A1: Tham chiếu hỗn hợp chỉ khóa cột.

• A1: Tham chiếu tương đối, không khóa ô.

Trên Mac, dùng Command + T thay cho F4 để chuyển giữa các loại tham chiếu.

Tìm hiểu các loại tham chiếu ô

Excel cung cấp các loại tham chiếu ô khác nhau để kiểm soát cách công thức hoạt động khi được sao chép hoặc kéo. Dưới đây là phần phân tích rõ ràng cho từng loại:

Tham chiếu tuyệt đối

Tham chiếu tuyệt đối khóa cả cột và hàng, vì vậy tham chiếu không thay đổi khi công thức được sao chép hoặc di chuyển sang ô khác. Ví dụ, $A$2 sẽ luôn trỏ đến ô A2, bất kể công thức đặt ở đâu hoặc được kéo đến đâu.

Ví dụ về tham chiếu tuyệt đối trong Excel. Hình ảnh do Tác giả cung cấp.

Tham chiếu tương đối

Tham chiếu tương đối cho phép cả cột và hàng tự động điều chỉnh khi công thức được sao chép hoặc di chuyển. Ví dụ, sao chép =A2 * B2 xuống một hàng sẽ thành =A3 + B3.

Ví dụ về tham chiếu tương đối trong Excel. Hình ảnh do Tác giả cung cấp.

Tham chiếu hỗn hợp

Tham chiếu hỗn hợp khóa hoặc cột hoặc hàng, nhưng không cả hai:

• $A1: Cột (A) được khóa, nhưng số hàng thay đổi khi bạn kéo công thức theo chiều dọc. Ví dụ, kéo $A1 xuống sẽ thành $A2, $A3, v.v.

• A$1: Hàng (1) được khóa, nhưng cột thay đổi khi bạn kéo công thức theo chiều ngang. Ví dụ, kéo A$1 sang phải sẽ thành B$1, C$1, v.v.

Phạm vi được đặt tên cho phép bạn gán một nhãn tùy chỉnh cho một ô hoặc một dải ô, giúp công thức dễ đọc và dễ quản lý hơn.

Ví dụ về phạm vi được đặt tên trong Excel. Hình ảnh do Tác giả cung cấp.

Ô neo (anchor cell) là một điểm tham chiếu cố định, thường được duy trì bằng tham chiếu tuyệt đối, để đảm bảo tính nhất quán trong tính toán.

Vì sao nên dùng tham chiếu tuyệt đối?

Dưới đây là một số lý do bạn nên dùng tham chiếu tuyệt đối:

• Giữ ổn định các hằng số: Dùng tham chiếu tuyệt đối để cố định các giá trị như thuế suất, hệ số chiết khấu hoặc đơn giá để chúng không đổi trong nhiều công thức.

• Tham chiếu các giá trị cấu hình: Trong các mô hình tài chính, dùng tham chiếu tuyệt đối cho các giá trị cấu hình như tỷ giá hối đoái, giả định lạm phát hoặc tham số cố định.

• Tạo bảng tính gọn gàng, dễ bảo trì hơn: Khóa các ô quan trọng giúp công thức dễ đọc, dễ hiểu và dễ kiểm tra.

• Ngăn lỗi tính toán: Tham chiếu tuyệt đối đóng vai trò như mỏ neo, đảm bảo công thức luôn lấy dữ liệu từ đúng ô, kể cả trong bộ dữ liệu phức tạp.

Ví dụ giúp bạn quen với tham chiếu tuyệt đối trong Excel

Luyện tập với các kịch bản thực tế là cách tốt nhất để hiểu cách tham chiếu tuyệt đối hoạt động. Bạn có thể bắt gặp những ví dụ như thế này trong khóa học Financial Modeling in Excel của chúng tôi.

Áp dụng thuế suất

Giả sử bạn có một thuế suất cố định tại ô B2, và danh sách giá mặt hàng ở cột A (A2:A6). Để tính tiền thuế cho từng mặt hàng bằng tham chiếu tuyệt đối, nhập công thức sau vào ô C2. Sau đó kéo công thức xuống. Tham chiếu A2 sẽ điều chỉnh thành A3, A4, v.v., nhưng $B$1 vẫn cố định tại thuế suất.

Áp dụng chiết khấu

Giả sử bạn có tỷ lệ chiết khấu lưu ở ô B2 và danh sách giá gốc ở cột A (A2:A6). Để tính giá đã chiết khấu cho từng mặt hàng, dùng công thức =A2*(1-$B$2). Sao chép công thức này xuống sẽ giữ nguyên tỷ lệ chiết khấu trong khi áp dụng cho giá của từng mặt hàng.

Tính tổng một dải với hệ số nhân cố định

Giả sử bạn muốn tính tổng các giá trị trong A2:A8 và nhân tổng đó với một hệ số cố định lưu ở C2. Hãy dùng công thức sau:

=SUM(A2:A8)*$C$2Được hỗ trợ bởi

Điều này giúp hệ số nhân luôn cố định, dù bạn di chuyển hay sao chép công thức ở đâu.

Mẹo dùng tham chiếu tuyệt đối hiệu quả

Như bạn đã thấy, dùng tham chiếu tuyệt đối một cách hợp lý có thể giúp bảng tính chính xác hơn, dễ đọc hơn và dễ bảo trì hơn. Dưới đây là một số mẹo bổ sung mà tôi thấy hữu ích.

• Sắp xếp các hằng số ở một khu vực riêng: Đặt các giá trị cố định như thuế suất, phần trăm chiết khấu hoặc tỷ giá quy đổi tiền tệ vào một khu vực được gắn nhãn rõ ràng trên trang tính để dễ tìm, tham chiếu và cập nhật.

• Kết hợp tham chiếu tuyệt đối và tương đối: Các công thức hỗn hợp như $A1 hoặc B$2 cho phép bạn chỉ khóa một phần của tham chiếu, giúp linh hoạt hơn khi kéo công thức qua các hàng hoặc cột.

• Kiểm tra kỹ tham chiếu sau khi sao chép công thức: Khi sao chép công thức sang các phần khác của trang tính, hãy xác minh rằng các tham chiếu tuyệt đối vẫn trỏ đúng ô và không bị điều chỉnh sai.

• Cân nhắc dùng phạm vi đặt tên: Phạm vi đặt tên cho phép bạn gán tên mô tả cho một ô hoặc dải ô, như TaxRate, giúp tăng tính dễ đọc và giảm lỗi, đặc biệt trong các công thức lớn hoặc phức tạp.

Những lỗi thường gặp cần tránh

Đôi khi, ngay cả người dùng Excel dày dạn cũng có thể mắc những lỗi nhỏ về tham chiếu dẫn đến vấn đề tính toán lớn. Dưới đây là một số lỗi thường gặp cần lưu ý:

• Quên khóa các tham chiếu quan trọng: Sao chép công thức mà không khóa các giá trị quan trọng có thể dẫn đến kết quả sai khi tham chiếu bị dịch chuyển. Luôn đảm bảo bạn khóa tham chiếu khi cần.

• Lạm dụng tham chiếu tuyệt đối: Mặc dù khóa giá trị rất hữu ích, dùng quá nhiều tham chiếu tuyệt đối có thể khiến công thức cứng nhắc và khó điều chỉnh. Chỉ dùng khi thật sự cần tính nhất quán.

• Khóa sai phần trong tham chiếu hỗn hợp: Vô tình khóa nhầm phần trong tham chiếu hỗn hợp có thể làm hỏng công thức. Hãy dùng phím F4 trên Windows hoặc Command + T trên Mac để chuyển nhanh giữa các loại tham chiếu và tránh lỗi thao tác thủ công.

Kết luận

Thành thạo tham chiếu tuyệt đối rất quan trọng đối với bất kỳ người dùng Excel nào mong muốn xây dựng bảng tính linh hoạt, năng động và không lỗi. Bằng cách hiểu khi nào và cách khóa ô, bạn có thể tạo các công thức thích ứng một cách thông minh trong khi vẫn giữ các giá trị quan trọng nhất quán. Tôi khuyến khích bạn luyện tập sử dụng tham chiếu tuyệt đối trong các tình huống thực tế, như áp dụng thuế suất, chiết khấu hoặc các hằng số, để tăng sự tự tin và rèn luyện kỹ năng Excel.

Tôi đề xuất bạn tham gia khóa học Data Analysis in Excel để tiếp tục thực hành kỹ năng, và lộ trình kỹ năng Excel Fundamentals để học các kỹ thuật làm sạch dữ liệu nâng cao và các hàm có trong các phiên bản Excel mới nhất.