Điều cần biết
- Để tính toán nhiều mục dựa trên các ô ở nơi khác và giữ hàng hoặc cột không đổi, hãy sử dụng tham chiếu ô tuyệt đối.
- Trong phương trình này, tham chiếu ô tuyệt đối là A $ 12:=+ B2 + B2 + A $ 12.
- Ký hiệu $"giữ" hàng hoặc cột không đổi ngay cả khi sao chép hoặc điền vào một cột hoặc hàng với cùng một công thức.
Bài viết này giải thích cách sử dụng tham chiếu ô tuyệt đối trong Excel 2010 trở lên để ngăn các công thức tự động điều chỉnh liên quan đến số hàng hoặc cột bạn đã điền.
Tham chiếu ô tuyệt đối trong Excel là gì?
Có hai loại cách để tham chiếu một ô bên trong công thức trong Excel. Bạn có thể sử dụng tham chiếu ô tương đối hoặc tham chiếu ô tuyệt đối.
- Tham chiếu ô tương đối: Địa chỉ ô không chứa ký hiệu $ phía trước tọa độ hàng hoặc cột. Tham chiếu này sẽ tự động cập nhật cột hoặc ô liên quan đến ô ban đầu khi bạn điền xuống hoặc trên.
- Tham chiếu ô tuyệt đối: Địa chỉ ô chứa ký hiệu $ ở phía trước tọa độ hàng hoặc cột. Điều này "giữ" hàng hoặc cột tham chiếu không đổi ngay cả khi điền vào một cột hoặc hàng với cùng một công thức.
Tham chiếu ô có vẻ như là một khái niệm trừu tượng đối với những người mới làm quen với nó.
Cách Sử dụng Tham chiếu Ô Tuyệt đối trong Excel
Có một số cách khác nhau để sử dụng tham chiếu tuyệt đối trong Excel. Phương pháp bạn sử dụng tùy thuộc vào phần nào của tham chiếu mà bạn muốn giữ không đổi: cột hay hàng.
-
Ví dụ: lấy một bảng tính mà bạn muốn tính thuế bán hàng thực tế cho nhiều mặt hàng, dựa trên các ô tham chiếu ở nơi khác trong trang tính.
Image -
Trong ô đầu tiên, bạn nhập công thức bằng cách sử dụng tham chiếu ô tương đối chuẩn cho giá mặt hàng. Nhưng để tính toán thuế bán hàng chính xác, bạn sẽ sử dụng tham chiếu tuyệt đối cho hàng, nhưng không sử dụng cho ô.
Image -
Công thức này tham chiếu đến hai ô theo những cách khác nhau. B2là ô bên trái của ô tính thuế bán hàng đầu tiên, liên quan đến vị trí của ô đó. Tham chiếu A $ 12trỏ đến số thuế bán hàng thực tế được liệt kê trong A12. Biểu tượng $ sẽ giữ tham chiếu đến hàng 12 không đổi, bất kể bạn điền vào các ô liền kề theo hướng nào. Điền vào tất cả các ô bên dưới ô đầu tiên này để xem điều này đang hoạt động.
Image -
Bạn có thể thấy rằng khi bạn điền vào cột, ô trong cột thứ hai sử dụng tham chiếu ô tương đối cho các giá trị chi phí trong cột B, tăng cột để khớp với cột thuế bán hàng. Tuy nhiên, tham chiếu đến A12 không đổi do ký hiệu $, giữ nguyên tham chiếu hàng. Bây giờ điền vào cột D bắt đầu từ ô C2.
Image -
Bây giờ, bạn có thể thấy rằng việc điền vào hàng bên phải sử dụng thuế bán hàng chính xác của tiểu bang (Indiana) vì tham chiếu cột là tương đối (nó dịch chuyển một sang bên phải giống như ô bạn đang điền). Tuy nhiên, tham chiếu tương đối cho chi phí hiện cũng đã dịch chuyển một bên sang phải, điều này không chính xác. Bạn có thể khắc phục điều này bằng cách đặt công thức gốc trong C2 tham chiếu đến cột B với tham chiếu tuyệt đối.
Image -
Bằng cách đặt biểu tượng $ trước "B", bạn đã tạo một tham chiếu tuyệt đối cho cột. Bây giờ khi bạn điền vào bên phải, bạn sẽ thấy rằng tham chiếu đến cột B cho chi phí vẫn giữ nguyên.
Image -
Bây giờ, khi bạn điền vào cột D, bạn sẽ thấy rằng tất cả các tham chiếu ô tương đối và tuyệt đối hoạt động chính xác theo cách bạn dự định.
Image -
Tham chiếu ô tuyệt đối trong Excel rất quan trọng để kiểm soát ô nào được tham chiếu khi bạn điền vào cột hoặc hàng. Sử dụng biểu tượng $ theo cách bạn cần trong tham chiếu ô để giữ hàng hoặc tham chiếu cột không đổi, để tham chiếu dữ liệu chính xác trong ô đúng. Khi bạn kết hợp các tham chiếu ô tương đối và tuyệt đối như thế này, nó được gọi là tham chiếu ô hỗn hợp.
Nếu bạn sử dụng tham chiếu hỗn hợp, điều quan trọng là phải căn chỉnh cột hoặc hàng của dữ liệu nguồn với cột hoặc hàng mà bạn đang nhập công thức. Nếu bạn đã tạo tham chiếu hàng tương đối, thì hãy nhớ rằng khi bạn điền sang một bên, số cột của dữ liệu nguồn sẽ tăng lên cùng với cột của ô công thức. Đây là một trong những điều khiến việc giải quyết tương đối và tuyệt đối trở nên phức tạp đối với người dùng mới, nhưng khi bạn đã học nó, bạn sẽ có nhiều quyền kiểm soát hơn đối với việc kéo các ô dữ liệu nguồn vào tham chiếu ô công thức của mình.
Sử dụng Tham chiếu Ô Tuyệt đối để Ghim Tham chiếu Một Ô
Một cách tiếp cận khác để sử dụng tham chiếu ô tuyệt đối là áp dụng nó cho cả cột và hàng để "ghim" công thức để chỉ sử dụng một ô duy nhất cho dù nó ở đâu.
Sử dụng phương pháp này, bạn có thể tô sang một bên hoặc xuống dưới và tham chiếu ô sẽ luôn giữ nguyên.
Sử dụng tham chiếu ô tuyệt đối trên cả cột và hàng chỉ hiệu quả nếu bạn chỉ tham chiếu một ô duy nhất trên tất cả các ô bạn đang điền.
-
Sử dụng cùng một bảng tính mẫu ở trên, bạn chỉ có thể tham khảo thuế suất của một tiểu bang bằng cách thêm ký hiệu $ vào cả tham chiếu cột và hàng.
Image -
Điều này làm cho cả cột "A" và hàng "12" không đổi, bất kể bạn điền các ô theo hướng nào. Để xem điều này trong thực tế, hãy điền vào toàn bộ cột cho thuế bán hàng ME sau khi bạn đã cập nhật công thức với tham chiếu cột và hàng tuyệt đối. Bạn có thể thấy rằng mỗi ô được điền luôn sử dụng tham chiếu tuyệt đối $ A $ 12. Cả cột hoặc hàng đều không thay đổi.
Image -
Sử dụng cùng một công thức cho cột thuế bán hàng ở Indiana, nhưng lần này sử dụng tham chiếu tuyệt đối cho cả cột và hàng. Trong trường hợp này, đó là $ B $ 12.
Image -
Điền vào cột này và một lần nữa bạn sẽ thấy rằng tham chiếu đến B12 không thay đổi trong ô bao giờ, nhờ tham chiếu tuyệt đối cho cả cột và hàng.
Image -
Như bạn có thể thấy, có nhiều cách bạn có thể sử dụng tham chiếu ô tuyệt đối trong Excel để thực hiện các tác vụ tương tự. Những gì tham chiếu tuyệt đối cung cấp cho bạn là sự linh hoạt để duy trì các tham chiếu ô không đổi ngay cả khi bạn đang điền vào một số lượng lớn các cột hoặc hàng.
Bạn có thể chuyển qua các tham chiếu ô tương đối hoặc tuyệt đối bằng cách đánh dấu tham chiếu và sau đó nhấn F4Mỗi khi bạn nhấn F4, tham chiếu tuyệt đối sẽ được áp dụng cho một trong hai cột, hàng, cả cột và ô, hoặc cả hai. Đây là một cách đơn giản để sửa đổi công thức của bạn mà không cần phải nhập ký hiệu $.
Khi nào bạn nên sử dụng tham chiếu tế bào tuyệt đối
Trong hầu hết mọi ngành và lĩnh vực, có rất nhiều lý do khiến bạn có thể muốn sử dụng tham chiếu ô tuyệt đối trong Excel.
- Sử dụng hệ số cố định (như giá trên mỗi đơn vị) trong một danh sách lớn các mặt hàng.
- Áp dụng một tỷ lệ phần trăm duy nhất cho mỗi năm khi bạn dự kiến mục tiêu lợi nhuận hàng năm.
- Khi tạo hóa đơn, hãy sử dụng tham chiếu tuyệt đối để đề cập đến cùng một mức thuế suất trên tất cả các mặt hàng.
- Sử dụng tham chiếu ô tuyệt đối trong quản lý dự án để tham khảo tỷ lệ sẵn có cố định cho các tài nguyên riêng lẻ.
- Sử dụng tham chiếu cột tương đối và tham chiếu hàng tuyệt đối để khớp các phép tính cột trong ô được tham chiếu của bạn với giá trị cột trong bảng khác.
Nếu bạn sử dụng kết hợp các tham chiếu tương đối và tuyệt đối cho các cột hoặc hàng, bạn chỉ cần đảm bảo rằng vị trí của các cột hoặc hàng dữ liệu nguồn khớp với cột hoặc hàng của ô đích (nơi bạn ' đang gõ lại công thức).
