Excel 2019 (Phần 14): Tham chiếu ô tương đối và tuyệt đối

Trong Excel, có hai loại tham chiếu ô: Tương đối và 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, 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 sẽ 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.

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

Ví dụ sau muốn tạo một công thức sẽ nhân giá của mỗi mặt hàng với số lượng. Thay vì tạo một công thức mới cho mỗi hàng, bạn có thể tạo một công thức duy nhất trong ô D4 và sau đó sao chép nó sang các hàng khác. Ví dụ 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 ô sẽ chứa công thức. Ví dụ sẽ chọn ô D4.

2. Nhập công thức để tính giá trị mong muốn. Ví dụ sẽ nhập =B4*C4.

3. Nhấn Enter trên bàn phím. Công thức sẽ được tính toán và kết quả được hiển thị trong ô.

4. Tìm fill handle ở góc dưới cùng bên phải của ô mong muốn. Ví dụ sẽ xác định vị trí fill handle cho ô D4.

5. Nhấp và kéo fill handle vào các ô bạn muốn điền. Ví dụ sẽ chọn các ô D5:D13.

6. Nhả 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 ô.

Bạn có thể bấm đúp vào các ô đã điền để kiểm tra độ chính xác của các công thức của chúng. 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à/hoặc cột không đổi.

Một tham chiếu tuyệt đối được chỉ định trong một công thức bằng cách thêm vào ký hiệu đô la ($). Ký hiệu này có thể đứng trước tham chiếu cột, tham chiếu hàng hoặc cả hai.

Nói chung, bạn sẽ sử dụng định dạng $A$2 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 nhiều.

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, như thể hiện trong video bên dưới. Đây là một cách dễ dàng để nhanh chóng chèn một tham chiếu tuyệt đối.

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

Ví dụ bên dưới sẽ sử dụng ô E2 (chứa thuế suất 7,5%) để tính thuế bán hàng cho từng mặt hàng trong cột D. Để đảm bảo tham chiếu đến thuế suất không đổi - ngay cả khi công thức là đã sao chép và điền vào các ô khác - ô $E$2 cần được đặt làm tham chiếu tuyệt đối.

1. Chọn ô sẽ chứa công thức. Ví dụ sẽ chọn ô D4.

2. Nhập công thức để tính giá trị mong muốn. Ví dụ sẽ nhập =(B4*C4)*$E$2, làm cho $E$2 là một 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à kết quả sẽ hiển thị trong ô.

4. Tìm fill handle ở góc dưới cùng bên phải của ô mong muốn. Ví dụ sẽ xác định vị trí fill handle cho ô D4.

5. Nhấp và kéo fill handle vào các ô bạn muốn điền (ô D5:D13 trong ví dụ).

6. Nhả chuột. Công thức sẽ được sao chép vào những ô đã chọn với tham chiếu tuyệt đối và các giá trị sẽ được tính toán trong mỗi ô.

Bạn có thể nhấp đúp vào các ô đã điền để kiểm tra độ chính xác của các 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 ô.

Đảm bảo bao gồm ký hiệu đô la ($) bất cứ khi nào bạn đang thực hiện một 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 mọi trang tính, đ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. Để làm điều 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 một trang tính có tên July Budget, thì tham chiếu ô của nó sẽ là 'July Budget'!A1.

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

Ví dụ bên dưới sẽ đề cập đến một ô có giá trị được tính toán giữa hai trang tính. Điều này cho phép 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 ô đó. Ví dụ muốn tham chiếu ô E14 trên trang tính Menu Order.

2. Điều hướng đến trang tính mong muốn. Ví dụ sẽ chọn bảng tính Catering Invoice.

3. Định vị và chọn ô mà bạn muốn giá trị xuất hiện. Ví dụ sẽ chọn ô C4.

4. Nhập dấu bằng (=), tên trang tính theo sau dấu chấm than (!) và địa chỉ ô. Ví dụ sẽ nhập ='Menu Order'!E14.

5. Nhấn Enter trên bàn phím. Giá trị của ô được tham chiếu sẽ xuất hiện. Bây giờ, nếu giá trị của ô E14 thay đổi trên trang tính Menu Order, nó sẽ được cập nhật tự động trên trang tính Catering Invoice.

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 sai tên trang tính, lỗi #REF! lỗi sẽ xuất hiện trong ô. Trong ví dụ bên dưới, tên của trang tính đã bị gõ sai. Để chỉnh sửa, bỏ qua hoặc kiểm tra lỗi, hãy nhấp vào nút Error bên cạnh ô và chọn một tùy chọn từ menu.