Hướng dẫn cách sử dụng tham chiếu hỗn hợp trong Excel

Tham chiếu hỗn hợp là gì?

Tham chiếu hỗn hợp trong Excel: Tham chiếu hỗn hợp được tạo thành từ cả tham chiếu tuyệt đối và tham chiếu tương đối. Điều này có nghĩa là một phần của tham chiếu là cố định, hàng hoặc cột và phần khác là tương đối. 

Tham chiếu tuyệt đối đề cập đến một điểm tham chiếu cố định trong đó giá trị trả về luôn giữ nguyên ngay cả khi ô hoặc công thức di chuyển. 
 

Tham chiếu tương đối đề cập đến một điểm tham chiếu đang thay đổi, nơi giá trị trả về thay đổi tùy thuộc vào vị trí ô hoặc công thức di chuyển đến. 

Vì một tham chiếu ô hỗn hợp bao gồm một tham chiếu tuyệt đối với một ký hiệu $ (đô la) có trong công thức. Chỉ có một ký hiệu đô la hiện diện để giữ hàng hoặc cột không đổi, thay vì hai ký hiệu đô la trong tham chiếu tuyệt đối thông thường được thiết lập. 

Những dạng tham chiếu hỗn hợp

Tham chiếu hỗn hợp có hai dạng phổ biến sau:

=$A1 ($ở phía trước cột, giữ cột không đổi)

= A$1 ($ ở phía trước hàng, giữ hàng không đổi)

Các tham chiếu hỗn hợp thường được sử dụng trong Excel vì chúng khá tiện dụng trong các trường hợp sử dụng các phép tính giống nhau trên các hàng và cột. 

Có thể tham chiếu hỗn hợp hơi khó bắt đầu so với các tham chiếu tuyệt đối và tham chiếu tương đối. Tuy nhiên nó lại giúp tiết kiệm thời gian và giảm thiểu sai sót.

Cách tạo tham chiếu hỗn hợp trong Excel

Dưới đây là các bước về cách tạo tham chiếu ô hỗn hợp cơ bản trong Excel.

Bước 1: Chọn một ô mà bạn muốn tạo tham chiếu hỗn hợp. Như trong ví dụ này chúng tôi chọn ô B2.

Bước 3: Giả sử ý tưởng là tổng hợp các số, trong công thức của ô B2, bạn nhập “= ” (dấu bằng) sau đó chọn điểm tham chiếu là ô A2 và B1.

Bước 4: Giữ cột và hàng không đổi:

- Để giữ hàng không đổi, hãy thêm $ (ký hiệu đô la) vào trước A, sau đó sao chép công thức sang các cột khác trong bảng, như hình dưới đây.

- Kết quả trả về sẽ như sau:

- Để giữ cột không đổi, hãy thêm $ (ký hiệu đô la) vào trước hàng thay thế (=A$2+B1), sau đó sao chép các công thức sang các vị trí khác.

- Kết quả trả về sẽ như sau:

Như đã thấy từ hai ví dụ trên, việc giữ nguyên hàng hoặc cột trong một bảng sẽ tạo ra các kết quả rất khác nhau. Trước khi gán ký hiệu đô la cho một cột hoặc hàng, bạn nên xem lại các phép tính và logic để tránh sai sót.  

Ví dụ về tham chiếu hỗn hợp

Tham chiếu ô hỗn hợp linh hoạt và tiện lợi thường được sử dụng trong bảng tính Excel. Chúng có thể được sử dụng với cả số và văn bản.

Tham chiếu hỗn hợp được ưu tiên đặc biệt khi thực hiện tính toán trong Excel , vì nó giúp người dùng đánh giá các số liệu trong các tình huống và điều kiện. Nó cũng tiết kiệm thời gian bằng cách sao chép các công thức trên bảng Excel. Dưới đây là một ví dụ về việc áp dụng tham chiếu hỗn hợp:

Bạn được yêu cầu tính thuế thu nhập hàng năm cho một số người trong danh sách. Trong đó bảng dữ liệu được cung cấp là thu nhập hàng năm từ năm 2017 đến năm 2019 và thuế suất tương ứng của họ.

Để biết số thuế thu nhập phải nộp hàng năm tương ứng của họ, phép tính được thực hiện là = thu nhập hàng năm*thuế suất (Annual income*Tax rate). 

- Trong trường hợp của Jonathan, nó sẽ là = 2598*17%

- Trong biểu thức Excel, nó sẽ là = B3*E3.

 - Giá trị trả về là 441,66 USD.

Để tính phần còn lại, vì thuế suất luôn nằm trong cột E nên cột E phải được giữ không đổi trong phép tính. Để làm như vậy, hãy thêm ký hiệu $ vào phép tính = B3*$E3 :

Sau đó, sao chép công thức này đến phần còn lại của các ô trong bảng.

Kết quả trả về là các giá trị tương ứng như bảng dưới đây.

Nếu một tham chiếu hỗn hợp không được áp dụng trong trường hợp trên và chỉ sử dụng một tham chiếu tương đối, thì các phép tính sẽ không chính xác.

Trong ví dụ này, các tập dữ liệu là tất cả các biến, nhưng đặc biệt thuế suất không đổi đối với từng đối tượng đóng thuế qua các năm. 

Vì thuế suất chỉ có trong cột E nên khi áp dụng công thức cho các ô khác, điều quan trọng là phải giữ cột E không đổi để tạo ra giá trị chính xác.

Kết luận

Tham chiếu hỗn hợp trong Excel có nghĩa là chỉ một phần của tham chiếu là cố định, hàng hoặc cột và phần khác là tương đối. Không giống như tham chiếu tuyệt đối, chỉ một ký hiệu $ (ký hiệu đô la) được áp dụng, ở phía trước số cột hoặc hàng.

Do tính chất phức tạp của nó, các tham chiếu hỗn hợp có thể dễ dàng tính toán các tình huống phức tạp và thường được sử dụng để tạo trang tổng quan và dự báo. Người dùng có thể sao chép và dán công thức trong bảng để giảm thiểu các phép tính thủ công và sai sót.

Điều cần thiết là phải xem lại các phép tính và logic trong tay trước khi áp dụng các tham chiếu hỗn hợp vì việc khóa cột hoặc hàng sẽ tạo ra các kết quả khác biệt đáng kể.