Cách sử dụng tham chiếu tuyệt đối trong Excel

Hầu hết mọi người đều quen thuộc với việc sử dụng tham chiếu tương đối trong Excel. Điều này là do tham chiếu ô trong bảng tính Excel mặc định là phương pháp tham chiếu tương đối.

Tuy nhiên, đôi khi các tài liệu tham khảo tương đối gây bất tiện. Nó sẽ thay đổi các tham chiếu ô bất kỳ khi nào bạn sao chép ô hoặc điền vào các cột và hàng. Nếu bạn không muốn các tham chiếu thay đổi, bạn cần phải đi với tham chiếu tuyệt đối hoặc tham chiếu hỗn hợp (bao gồm cả tham chiếu tương đối và tham chiếu tuyệt đối).

Trong một tham chiếu tuyệt đối, cả tham chiếu cột và hàng đều “bị khóa”, vì vậy cả hai tham chiếu đó đều không thay đổi khi bạn sao chép hoặc điền từ ô đó.

Bài viết này sẽ xem xét cách bạn có thể sử dụng tham chiếu tuyệt đối để làm cho Excel hoạt động theo cách bạn muốn với dữ liệu.

Cách tham chiếu tương đối hoạt động trong Excel

Khi bạn nhập giá trị vào bảng tính Excel, mỗi ô có một chữ cái và số cụ thể được gán cho nó. Điều này đại diện cho cột và hàng của ô đó.

Ví dụ: giá trị của “1” trong bảng tính bên dưới nằm trong cột A và hàng 2. Vì vậy, “tham chiếu” cho ô này là A2.

Nếu bạn muốn thực hiện một phép tính trong ô tiếp theo dựa trên ô này, bằng cách thêm 1 vào ô đó, bạn sẽ viết công thức sau:

=A2+1

Công thức này sẽ chèn giá trị từ A2 vào công thức, tính toán nó và sau đó xuất kết quả vào ô có công thức này.

Khi nhấn Enter, bạn sẽ thấy kết quả.

Với tham chiếu tương đối, bạn không phải nhập công thức này vào mọi ô khác. Tất cả những gì bạn phải làm là kéo góc của ô có công thức ban đầu xuống tùy thích.

Trong ô tiếp theo, tham chiếu đến A2 sẽ trở thành A3. Trong ô bên dưới, A3 sẽ trở thành A4. Nói cách khác, Excel biết rằng bạn muốn thêm 1 vào ô trước đó, vì vậy Excel sẽ cập nhật số (tham chiếu hàng) tương ứng khi bạn kéo xuống.

Điều này hoạt động theo cách tương tự nếu bạn kéo công thức qua các cột. Thay vì cập nhật số, Excel sẽ cập nhật phần sau của tham chiếu (cột) để luôn tham chiếu đến ô phía trên nó.

Cột bên phải chứa B2, bên phải chứa C2, v.v.

Đây là một ví dụ đơn giản về cách địa chỉ tương đối hoạt động cho cả tham chiếu cột và ô.

Cách tham chiếu tuyệt đối hoạt động trong Excel

Tham chiếu tuyệt đối trong Excel cho phép bạn tham chiếu cùng một ô, thay vì để Excel tự động cập nhật tham chiếu hàng hoặc cột cho bạn. Tham chiếu “hỗn hợp” là nếu bạn chỉ khóa hàng hoặc cột và “tham chiếu tuyệt đối” là khi bạn khóa cả hai.

Hãy xem một số ví dụ.

Giả sử bảng tính của bạn có “10” ở hàng trên cùng và bạn muốn mọi hàng bên dưới nó nhân số đó với số trong ô bên trái.

Để làm điều này, bạn phải nhập một công thức giống như sau:

=B$2*A3

Điều này sẽ khóa tham chiếu “2”, vì vậy nó sẽ không thay đổi tham chiếu hàng nếu bạn kéo ô có công thức này xuống các ô bên dưới nó. Vì A3 vẫn được “mở khóa” nên cả tham chiếu hàng và cột sẽ vẫn tự động thay đổi và luôn tham chiếu ô ở bên trái.

Bạn sẽ nhận thấy rằng điều này chỉ hoạt động vì bạn đang kéo xuống các ô trong cùng một cột. Vì vậy, bạn không cần phải khóa cột (B) bằng cách đặt một ký hiệu đô la ($) trước cột đó.

Vấn đề với điều này là nếu bạn muốn sử dụng cùng một công thức ở bên phải của công thức ban đầu, thì tham chiếu “B” sẽ thay đổi và công thức sẽ không còn tham chiếu B2 như dự định.

Hãy cùng xem cách sử dụng tham chiếu tuyệt đối thay vì tham chiếu hỗn hợp để làm cho việc điền vào cả hai hướng hoạt động bình thường.

Sử dụng đúng tham chiếu tuyệt đối trong Excel

Để sử dụng tham chiếu chính xác trong công thức này, bạn phải xem xét chính xác những gì bạn đang cố gắng thực hiện.

Trong trường hợp này, chúng tôi muốn các hành vi sau đây khi điền vào bên phải.

– Luôn tham chiếu giá trị trong ô B2
– Luôn tham chiếu giá trị trong cột A
– Chuyển tham chiếu của hàng cho cột A thành hàng hiện tại của công thức
Nhìn vào những hành vi này, bây giờ bạn biết những gì bạn cần “khóa” và những gì bạn không. Cả “B” và “2” cần được khóa (không thay đổi). Ngoài ra, cột A cần được khóa.

Vì vậy, công thức của bạn trong B3 cần phải giống như sau: =$B$2*$A3

Bây giờ khi bạn kéo cùng một ô này xuống hoặc lên, công thức sẽ hoạt động như dự định.

Việc sử dụng tham chiếu tuyệt đối một cách chính xác có thể trở nên phức tạp, vì vậy điều cần thiết là dành thời gian để xem xét cẩn thận cách bạn muốn Excel cập nhật công thức khi bạn điền các cột hoặc hàng theo một trong hai hướng.

Chu kỳ qua các kiểu tham chiếu trong Excel

Bạn có thể tăng tốc quá trình khi nhập công thức có tham chiếu tuyệt đối bằng cách nhấn phím F4, thao tác này sẽ làm cho tham chiếu ô là tuyệt đối.

Con trỏ có thể ở hai bên của tham chiếu ô (hoặc thậm chí ở giữa ô) khi bạn nhấn F4 và nó sẽ vẫn chuyển đổi tham chiếu đơn lẻ đó thành tuyệt đối.

Nếu bạn không muốn tuyệt đối (ví dụ: thay vào đó là hỗn hợp), hãy tiếp tục nhấn F4 cho đến khi tham chiếu có giao diện như bạn muốn.

Nếu bạn muốn thêm bất kỳ loại tham chiếu nào đến các ô khác trong công thức, chỉ cần đặt con trỏ của bạn ở đó và bắt đầu chuyển qua F4 một lần nữa.

Khi bạn đã định cấu hình công thức của mình, chỉ cần nhấn Enter và bắt đầu điền vào bảng tính của bạn theo bất kỳ hướng nào bạn muốn. Nếu bạn thiết lập đúng tham chiếu của mình, mọi thứ sẽ hoạt động như mong đợi.