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

Giới thiệu

Có hai loại tham chiếu ô: tương đối (relative) và tuyệt đối (absolute). 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 biến đổ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 còn hằng số bất kể chúng được sao chép ở đâu.

Tham chiếu tương đối (Relative References)

Theo mặc định, tất cả các tham chiếu ô đều là tham chiếu tương đối. Khi được sao chép qua nhiều ô, chúng sẽ thay đổi dựa trên vị trí tương đối của hàng và cột. Ví dụ: nếu bạn sao chép công thức =A1+B1 từ hàng 1 đến 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 sử dụng tham chiếu tương đối

Trong ví dụ sau, chúng tôi muốn tạo một công thức sẽ nhân giá bán mỗi mục với số lượng. 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 ô D4 và sau đó sao chép nó sang các hàng khác. Chúng tôi sẽ sử dụng các tham chiếu tương đối để công thức tính toán tổng số cho từng mục một cách chính xác.

1. Chọn ô sẽ chứa công thức. Trong ví dụ của chúng tôi, chúng tôi sẽ chọn ô D4.
   
2. Nhập công thức để tính giá trị mong muốn. Trong ví dụ của chúng tôi, chúng tôi sẽ gõ =B4*C4.
   
3. Nhấn Enter trên bàn phím của bạn. Công thức sẽ được tính toán và kết quả sẽ được hiển thị trong ô.
4. xác định vị trí tự động điền (fill handle) ở góc dưới cùng bên phải của ô mong muốn. Trong ví dụ của chúng tôi, chúng tôi sẽ xác định fill handle cho ô D4.
   
5. Nhấp và kéo dấu fill handle trên các ô bạn muốn điền. Trong ví dụ của chúng tôi, chúng tôi sẽ chọn các ô D5:D13.
   
6. Thả chuột. Công thức sẽ được sao chép đến các ô đã chọn với tham chiếu tương đối, hiển thị kết quả trong từng ô.
   

Bạn có thể nhấp đúp vào filled cells để kiểm tra công thức của nó cho chính xá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 các hàng của chúng.

Tham chiếu tuyệt đối (Absolute References)

Có thể đôi khi bạn không muốn tham chiếu ô thay đổi khi sao chép sang các ô khác. Không giống như các tham chiếu tương đối, tham chiếu tuyệt đối không thay đổi khi 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 một ký hiệu đô la ($). Nó có thể đứng trước tham chiếu cột, tham chiếu hàng hoặc cả hai.

• $A$2: cột và hàng không thay đổi khi sao chép
• A$2: hàng không thay đổi
• $A2: cột không thay đổi

Bạn thường sẽ sử dụng $A$2 định dạng khi tạo công thức có chứa tham chiếu tuyệt đối. Hai định dạng khác đượ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 của bạn để chuyển đổi giữa tham chiếu ô tương đối và ô tuyệt đối, đây là một cách dễ dàng để nhanh chóng chèn một tham chiếu tuyệt đối.

Để tạo và sao chép một công thức sử dụng tham chiếu tuyệt đối

Trong ví dụ bên dưới, chúng tôi sẽ sử dụng ô E2 (trong đó có 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 được sao chép và điền vào các ô khác—chúng ta sẽ cần tạo ô $E$2 một tham chiếu tuyệt đối.

1. Chọn ô sẽ chứa công thức. Trong ví dụ của chúng tôi, chúng tôi sẽ chọn ô D4.
   
2. Nhập công thức để tính giá trị mong muốn. Trong ví dụ của chúng tôi, chúng tôi sẽ gõ =(B4*C4)*$E$2, tạo $E$2 một tham chiếu tuyệt đối.
   
3. Nhấn Enter trên bàn phím của bạn. Công thức sẽ tính toán và kết quả sẽ hiển thị trong ô.
4. xác định vị trí tự động điền (fill handle) ở góc dưới cùng bên phải của ô mong muốn. Trong ví dụ của chúng tôi, chúng tôi sẽ xác định fill handle cho ô D4.
   
5. Nhấp và kéo các fill handle trên các ô bạn muốn điền (các ô D5:D13 trong ví dụ của chúng tôi).
   
6. Thả chuột. Công thức sẽ được sao chép đến các ô đã chọn với một tham chiếu tuyệt đối và các giá trị sẽ được tính trong mỗi ô.
   

Bạn có thể nhấp đúp vào filled cells để kiểm tra công thức của nó cho chính xá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 ô.

Hãy chắc chắn bao gồm các ký hiệu đô la ($) bất cứ khi nào bạn tạo một tham chiếu tuyệt đối trên nhiều ô. Các ký hiệu đô la đã bị 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 kết quả không chính xác khi được 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ỳ bảng 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 ô với Tên bảng tính theo sau là một dấu chấm than (exclamation point) (!). 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 một tên trang tính chứa một khoảng trống bạn sẽ cần bao gồm dấu nháy đơn (‘ ‘) quanh cái 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 7 tham chiếu ô của nó sẽ là ‘Ngân sách tháng 7’!A1.

Để tham chiếu các ô trên các trang tính

Trong ví dụ của chúng tôi bên dưới, chúng tôi sẽ đề cập đến một ô có giá trị được tính giữa hai trang tính. Điều này sẽ cho phép chúng tôi sử dụng chính xác cùng một giá trị 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à ghi lại trang tính của ô đó. Trong ví dụ của chúng tôi, chúng tôi muốn tham chiếu ô E14 trên bảng tính Thực đơn gọi món (Menu Order).
   
2. Điều hướng đến bảng tính mong muốn. Trong ví dụ của chúng tôi, chúng tôi sẽ chọn bảng tính Hóa đơn ăn uống (Catering Invoice).
   
3. Xác định vị trí và chọn ô nơi bạn muốn giá trị xuất hiện. Trong ví dụ của chúng tôi, chúng tôi sẽ chọn ô C4.
   
4. Gõ dấu bằng (=) , Tên bảng tính theo sau là một dấu chấm than (!) và địa chỉ ô. Trong ví dụ của chúng tôi, chúng tôi sẽ gõ =’Menu Order’!E14.
   
5. Nhấn Enter trên bàn phím của bạn. Các 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 bảng tính Đơn hàng, nó sẽ là được cập nhật tự động trên bảng tính Hóa đơn suất ăn.
   

Nếu bạn đổi tên trang tính của bạn sau này, 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, #REF! lỗi sẽ xuất hiện trong ô. Trong ví dụ dưới đây, chúng tôi đã nhập sai tên của trang tính. Đến chỉnh sửa, bỏ qua hoặc điều tra lỗi, bấm vào Lỗi bên cạnh ô và chọn một tùy chọn từ tùy chọn.