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

Trong Excel, có hai loại tham chiếu ô: Tham chiếu ô tương đối và tuyệt đối. Các 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 và tuyệt đối

Theo mặc định,Tham chiếu ô tương đối và tuyệt đố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 hữu ích 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ó vào 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ể đôi khi 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.

Tham chiếu tuyệt đối được chỉ định trong công thức bằng cách thêm ký hiệu đô la ($). Biểu tượng này có thể được đặt trước bởi một tham chiếu cột, mộ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 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.

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ụ dưới đây 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 rằng tham chiếu đến thuế suất vẫn giữ nguyên – ngay cả khi công thức được sao chép. 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 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ó như 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, 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 Ngân sách Tháng Bảy, 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ụ dưới đây 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 này, tham chiếu ô sẽ được tự động cập nhật để 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ụ dưới đây, tên của trang tính đã bị nhập sai. Để chỉnh sửa, bỏ qua hoặc kiểm 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.