Tham chiếu ô tương đối và tuyệt đối

Chúng ta đã biết và làm quen với tham chiếu ô trong Excel rồi phải không nào. Thế nhưng trong thực tế Excel có tới 2 loại tham chiếu ô. Trong bài này chúng ta sẽ tìm hiểu rõ hơn về các loại tham chiếu ô của Excel bao gồm tham chiếu tuyệt đối và tham chiếu tương đối nhé

Giới thiệu 

Trong Excel có hai loại tham chiếu ô: tham chiếu tương đối và tham chiếu tuyệt đối.Tham chiếu tương đối và tuyệt đối hoạt động khác nhau khi được sao chép và điền vào các ô khác. 

• Tham chiếu tương đối thay đổi khi một công thức được sao chép sang một ô khác. 
• Mặt khác, các tham chiếu tuyệt đối vẫn không đổi cho dù chúng được sao chép ở đâu.

Tham chiếu tương đối

Theo mặc định của Excel, tất cả các tham chiếu ô là tham chiếu tương đối. Khi được sao chép trên nhiều ô, chúng thay đổi dựa trên vị trí tương đối của các hàng và cột. Ví dụ: nếu bạn sao chép công thức = A1 + B1 từ hàng 1 sang hàng 2, công thức sẽ trở thành = A2 + B2. Tham chiếu tương đối đặc biệt thuận tiện bất cứ khi nào bạn cần lặp lại cùng một phép tính trên nhiều hàng hoặc cột.

Tạo và sao chép công thức bằng cách sử dụng tham chiếu tương đối:

Trong ví dụ sau, chúng mình sẽ tạo ra một công thức tính hoa hồng cho các nhân viên. Thay vì tạo một công thức mới cho mỗi hàng, chúng ta có thể tạo một công thức duy nhất trong ô F2 và sau đó sao chép nó sang các hàng khác. Chúng mình sẽ sử dụng các tham chiếu tương đối để công thức tính tổng chính xác cho từng mục.

1. Chọn ô muốn nhập công thức. Trong ví dụ chúng mình sẽ chọn ô F2
2. Nhập công thức để tính giá trị mong muốn. Trong ví dụ của này chúng mình sẽ nhập =D2*E2
3. Nhấn Enter trên bàn phím. Công thức sẽ được tính toán và hiển thị kết quả trong ô
4. Di chuột đến góc cuối ô F2 để xuất hiện Fill Handle để copy công thức 
5. Nhấn và kéo Fill Handle xuống các ô tiếp theo để copy công thức, trong ví dụ này chúng mình kéo Fill Handle từ ô F3 đến F11
6. Thả chuột. Công thức sẽ được sao chép vào các ô đã chọn có tham chiếu tương đối, hiển thị kết quả trong mỗi ô.
7. Bạn có thể bấm đúp vào các ô đã điền để kiểm tra độ chính xác của công thức. Các tham chiếu ô tương đối phải khác nhau đối với mỗi ô, tùy thuộc vào hàng của chúng.

Tham chiếu tuyệt đối

Có thể có lúc bạn không muốn tham chiếu ô thay đổi khi được sao chép sang các ô khác. Không giống như tham chiếu tương đối, tham chiếu tuyệt đối không thay đổi khi được sao chép hoặc điền. Bạn có thể sử dụng một tham chiếu tuyệt đối để giữ một hàng và cột không thay đổi hoặc là chỉ hàng không đổi cột đổi, hàng đổi cột không đổi.

Một tham chiếu tuyệt đối được chỉ định trong công thức bằng cách thêm dấu đô la ($). Nó có thể đứng trước tham chiếu cột, tham chiếu hàng hoặc cả hai. Nếu nó đứng trước cột hoặc hàng (nhưng không phải cả hai), nó được gọi là một tham chiếu hỗn hợp.

Tham chiếu tuyệt đối chia ra 3 loại

• $A$1: Cột và hàng tuyệt đối (không đổi khi copy )
• $A1: Cột tuyệt đối hàng tương đối
• A$1: Cột tương đối và hàng tuyệt đối

Nói chung, bạn sẽ sử dụng định dạng $A$1 khi tạo công thức có chứa tham chiếu tuyệt đối. Hai định dạng còn lại được sử dụng ít thường xuyên hơn.

Khi viết công thức, bạn có thể nhấn phím F4 trên bàn phím để chuyển đổi giữa các tham chiếu ô tương đối và tuyệt đối.

Bạn sẽ sử dụng định dạng tương đối (A2) và tuyệt đối ($ A $ 2) trong hầu hết các công thức. Các tham chiếu hỗn hợp ít được sử dụng hơn.

Tạo và sao chép công thức bằng cách sử dụng tham chiếu tuyệt đối:

Trong ví dụ dưới đây chúng mình sẽ sử dụng ô C13  chứa 10% VAT để tính toán tiền thuế phải trả của mỗi nhân viên. Để đảo bảo cho việc VAT không đổi chúng ta cần sử dụng tham chiếu tuyệt đối ở ô C13 tức là sử dụng $C$13

1. Chọn ô cần tính toán ở đây chúng mình chọn ô F2
2. Nhập vào công thức để tính giá trị mong muốn. Trong ví dụ chúng mình sẽ nhập =D2*E2*$C$13 với $C$13 là ô tham chiếu tuyệt đối
3. Nhấn Enter trên bàn phím. Công thức sẽ tính toán và hiển thị kết quả trong ô
4. Sử dụng Fill Handle để kéo và copy công thức xuống các ô còn lại
5. Thả chuột. Công thức sẽ được sao chép vào các ô đã chọn với tham chiếu tuyệt đối và các giá trị sẽ được tính toán trong mỗi ô.
6. Bạn có thể bấm đúp vào các ô đã điền để kiểm tra độ chính xác của công thức. Tham chiếu tuyệt đối phải giống nhau cho mỗi ô, trong khi các tham chiếu khác có liên quan đến hàng của ô.
7. Đảm bảo bao gồm ký hiệu đô la ($) bất cứ khi nào bạn tạo tham chiếu tuyệt đối trên nhiều ô. Các ký hiệu đô la đã được bỏ qua trong ví dụ dưới đây. Điều này khiến Excel hiểu nó là một tham chiếu tương đối, tạo ra một kết quả không chính xác khi sao chép sang các ô khác.

Sử dụng tham chiếu ô với nhiều trang tính

Excel cho phép bạn tham chiếu đến bất kỳ ô nào trên bất kỳ trang tính nào, điều này có thể đặc biệt hữu ích nếu bạn muốn tham chiếu một giá trị cụ thể từ trang tính này sang trang tính khác. Để thực hiện việc này, bạn chỉ cần bắt đầu tham chiếu ô bằng tên trang tính, theo sau là dấu chấm than (!). Ví dụ: nếu bạn muốn tham chiếu ô A1 trên Sheet1, tham chiếu ô của nó sẽ là Sheet1! A1.

Lưu ý rằng nếu tên trang tính chứa khoảng trắng, bạn sẽ cần bao gồm dấu ngoặc kép ('') xung quanh tên. Ví dụ: nếu bạn muốn tham chiếu ô A1 trên trang tính có tên Ngân sách Tháng Bảy, thì tham chiếu ô của nó sẽ là 'Ngân sách Tháng Bảy'! A1.

Cách để tham chiếu các ô trên các trang tính khác nhau

Trong ví dụ dưới đây, chúng mình sẽ đề cập đến một ô có giá trị được tính toán giữa hai trang tính. Điều này sẽ cho phép chúng mình sử dụng cùng một giá trị chính xác trên hai trang tính khác nhau mà không cần viết lại công thức hoặc sao chép dữ liệu.

1. Định vị ô bạn muốn tham chiếu và lưu ý trang tính của nó. Trong ví dụ chúng mình muốn tham chiếu ô D1 trên trang tính VAT
2. Điều hướng đến trang tính mong muốn. Trong ví dụ của này chúng mình sẽ chọn bảng tính Học TV. 
3. Định vị và chọn ô mà bạn muốn giá trị xuất hiện. Trong ví dụ của chúng mình chọn ô C13 
4. Nhập dấu bằng (=), tên trang tính theo sau là dấu chấm than (!) Và địa chỉ ô. Trong ví dụ của chúng ta, chúng ta sẽ gõ =VAT!D1
5. Nhấn Enter trên bàn phím của bạn. Giá trị của ô được tham chiếu sẽ xuất hiện. Bây giờ, nếu giá trị của ô D1 thay đổi trên trang tính VAT  nó sẽ được cập nhật tự động trên trang tính Học TV

Nếu bạn đổi tên trang tính của mình sau đó, tham chiếu ô sẽ được cập nhật tự động để phản ánh tên trang tính mới.

Nếu bạn nhập tên trang tính không chính xác, lỗi #REF! lỗi sẽ xuất hiện trong ô. Trong ví dụ của chúng mình bên dưới, chúng mình đã gõ sai tên của trang tính. Để chỉnh sửa, bỏ qua hoặc điều tra lỗi, hãy nhấp vào nút Lỗi bên cạnh ô và chọn một tùy chọn từ menu.

Tổng kết:

Có thể thấy rằng nhận biết khi nào sử dụng Tham chiếu tương đối và khi nào sử dụng Tham chiếu tuyệt đối rất quan trọng trong Excel bởi vì chỉ cần sử dụng nhầm tham chiếu thì bảng tính của bạn sẽ sai hoàn toàn. Vì vậy cần nắm rõ bài học này nhé các bạn. Chúc các bạn học tốt