Hầu hết mọi người quen với việc sử dụng tham chiếu tương đối trong Excel. Điều này là do các tham chiếu ô trong bảng tính Excel được mặc định là phương pháp tham chiếu tương đối.
Tuy nhiên, có những lúc các tham chiếu tương đối gây khó chịu. Nó sẽ thay đổi tham chiếu ô bất kỳ lúc nào bạn sao chép ô hoặc điền vào columns và các hàng. Nếu bạn không muốn các tham chiếu thay đổi, bạn sẽ cần phải sử dụng 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à 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.
Nội dung bài viết
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 bạn 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 columns. 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ì cho phép 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.
Để thực hiện việc này, bạn phải nhập một công thức giống như sau:
= B $ 2 * A3
Thao tác này sẽ khóa tham chiếu “2” nên 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 nó.
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.
Chúng ta hãy 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 đúng cách.
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 trô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 khó khăn, 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. columns hoặc các hàng theo một trong hai hướng.
Đạp xe qua các kiểu tham chiếu trong Excel
Bạn có thể tăng tốc quá trình khi bạn gõ công thức với tham chiếu tuyệt đối bằng cách nhấn F4 khóa này sẽ làm cho tham chiếu ô là tuyệt đối.
Con trỏ có thể nằm ở 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 duy nhất đó 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 trông theo cách 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.
