Tham chiếu ô Excel là gì?
Nói một cách đơn giản, một tham chiếu ô trong Excel là một địa chỉ ô. Nó cho Microsoft Excel biết nơi tìm giá trị bạn muốn sử dụng trong công thức.
Ví dụ: nếu bạn nhập công thức đơn giản = A1 vào ô C1, Excel sẽ kéo một giá trị từ ô A1 vào C1:
Như đã đề cập, miễn là bạn viết công thức cho một ô duy nhất , bạn có thể tự do sử dụng bất kỳ loại tham chiếu nào, có hoặc không có ký hiệu đô la ($), kết quả sẽ giống nhau:
Nhưng nếu bạn muốn di chuyển hoặc sao chép công thức trên bảng tính, điều quan trọng là bạn phải chọn loại tham chiếu phù hợp cho công thức để được sao chép chính xác sang các ô khác. Các phần sau đây cung cấp giải thích chi tiết và ví dụ về công thức cho từng loại tham chiếu ô.
Tham chiếu ô tương đối Excel (không có dấu $)
Khi một công thức có tham chiếu ô tương đối được sao chép sang ô khác, tham chiếu thay đổi dựa trên vị trí tương đối của các hàng và cột. Theo mặc định, tất cả các tham chiếu trong Excel là tương đối. Ví dụ sau đây cho thấy cách các tài liệu tham khảo tương đối làm việc.
Giả sử bạn có công thức sau trong ô B1:
=A1*10
Nếu bạn sao chép công thức này sang một hàng khác trong cùng một cột, nối với ô B2, công thức sẽ điều chỉnh cho hàng 2 (A2 * 10) vì Excel cho rằng bạn muốn nhân một giá trị trong mỗi hàng của cột A với 10.
Nếu bạn sao chép công thức có tham chiếu ô tương đối sang cột khác trong cùng hàng, Excel sẽ thay đổi tham chiếu cột tương ứng:
Và nếu bạn sao chép hoặc di chuyển một công thức Excel có tham chiếu ô tương đối sang một hàng khác và một cột khác, cả tham chiếu cột và hàng sẽ thay đổi:
Như bạn thấy, sử dụng các tham chiếu ô tương đối trong các công thức Excel là một cách rất thuận tiện để thực hiện các phép tính tương tự trên toàn bộ trang tính. Để minh họa rõ hơn điều này, hãy thảo luận về một ví dụ thực tế.
Sử dụng tham chiếu tương đối là Excel – ví dụ về công thức
Giả sử bạn có một cột giá USD (cột B) trong bảng tính của mình và bạn muốn chuyển đổi chúng thành VND. Biết tỷ lệ chuyển đổi USD – VND (23.000 tại thời điểm viết), công thức cho hàng 2, đơn giản như =B2*23000. Lưu ý rằng chúng tôi đang sử dụng một tham chiếu ô tương đối Excel, không có ký hiệu đô la.
Nhấn phím Enter sẽ nhận được công thức tính toán và kết quả sẽ xuất hiện ngay trong ô.
Để sao chép công thức xuống cột , hãy di chuột qua tay cầm điền (một hình vuông nhỏ ở góc dưới bên phải của ô đã chọn). Khi bạn thực hiện việc này, con trỏ sẽ thay đổi thành một chữ thập màu đen mỏng và bạn giữ và kéo nó qua các ô bạn muốn tự động điền.
Đó là nó! Công thức được sao chép sang các ô khác với các tham chiếu tương đối được điều chỉnh đúng cho từng ô riêng lẻ. Để đảm bảo rằng một giá trị trong mỗi ô được tính toán chính xác, hãy chọn bất kỳ ô nào và xem công thức trong thanh công thức. Trong ví dụ này, tôi đã chọn ô C4 và thấy rằng tham chiếu ô trong công thức có liên quan đến hàng 4, chính xác như nó phải là:
Tham chiếu ô tuyệt đối của Excel (có ký hiệu $)
Ký hiệu đô la ($) sửa chữa tham chiếu đến một ô đã cho, sao cho nó không thay đổi cho dù công thức di chuyển ở đâu. Nói cách khác, sử dụng $ trong tham chiếu ô cho phép bạn sao chép công thức trong Excel mà không thay đổi tham chiếu.
Ví dụ: nếu bạn có số 10 trong ô A1 và bạn sử dụng tham chiếu ô tuyệt đối ($A$1), công thức =$A$1+5 sẽ luôn trả về 15, bất kể các ô khác mà công thức đó được sao chép vào. Mặt khác, nếu bạn viết cùng một công thức với tham chiếu ô tương đối (A1), sau đó sao chép nó xuống các ô khác trong cột, một giá trị khác sẽ được tính cho mỗi hàng. Hình ảnh sau đây cho thấy sự khác biệt:
Sử dụng tham chiếu ô tương đối và tuyệt đối trong một công thức
Thông thường, bạn có thể cần một công thức trong đó một số tham chiếu ô được điều chỉnh cho các cột và hàng nơi công thức được sao chép, trong khi các công thức khác vẫn cố định trên các ô cụ thể. Nói cách khác, bạn phải sử dụng các tham chiếu ô tương đối và tuyệt đối trong một công thức duy nhất.
Ví dụ 1. Tham chiếu ô tương đối và tuyệt đối để tính số
Trong ví dụ trước của chúng tôi với giá USD và VND, bạn có thể không muốn mã hóa tỷ giá hối đoái trong công thức. Thay vào đó, bạn có thể nhập số đó vào một số ô, giả sử C1 và sửa tham chiếu ô đó trong công thức bằng cách sử dụng ký hiệu đô la ($) như hiển thị trong ảnh chụp màn hình sau:
Trong công thức này (B4 * $C$1), có hai loại tham chiếu ô:
- B4 – tham chiếu ô tương đối được điều chỉnh cho mỗi hàng
- $C$1 – tham chiếu ô tuyệt đối không bao giờ thay đổi cho dù công thức được sao chép ở đâu.
Một lợi thế của phương pháp này là người dùng của bạn có thể tính giá VND dựa trên tỷ giá hối đoái thay đổi mà không thay đổi công thức. Khi tỷ lệ chuyển đổi thay đổi, tất cả những gì bạn phải làm là cập nhật giá trị trong ô C1.
Ví dụ 2. Tham chiếu ô tương đối và tuyệt đối để tính ngày
Một cách sử dụng phổ biến khác của các tham chiếu ô tuyệt đối và tương đối trong một công thức duy nhất là Tính ngày trong Excel dựa trên ngày hôm nay.
Giả sử bạn có một danh sách các ngày giao hàng trong cột B và bạn nhập ngày hiện tại vào C1 bằng cách sử dụng hàm TODAY () . Những gì bạn muốn biết là trong bao nhiêu ngày mỗi mặt hàng được vận chuyển và bạn có thể tính toán điều này bằng cách sử dụng công thức sau: =B4-$C$1
Và một lần nữa, chúng tôi sử dụng hai loại tham chiếu trong công thức:
- Tương đối cho ô có ngày giao hàng đầu tiên (B4), vì bạn muốn tham chiếu ô này thay đổi tùy thuộc vào hàng nơi công thức cư trú.
- Tuyệt đối cho ô có ngày hôm nay ($C$1), vì bạn muốn tham chiếu ô này không đổi.
Cuối cùng, bất cứ khi nào bạn muốn tạo tham chiếu ô tĩnh Excel luôn tham chiếu đến cùng một ô, hãy đảm bảo bao gồm ký hiệu đô la ($) trong công thức của bạn để tạo tham chiếu tuyệt đối trong Excel.
Tham chiếu ô hỗn hợp Excel
Tham chiếu ô hỗn hợp trong Excel là một tham chiếu trong đó chữ cái cột hoặc số hàng được cố định. Ví dụ: $ A1 và A $ 1 là các tham chiếu hỗn hợp. Nhưng mỗi cái có nghĩa là gì? Nó rất đơn giản.
Như bạn nhớ, một tham chiếu tuyệt đối Excel chứa 2 ký hiệu đô la ($) khóa cả cột và hàng. Trong một tham chiếu ô hỗn hợp, chỉ một tọa độ được cố định (tuyệt đối) và khác (tương đối) sẽ thay đổi dựa trên vị trí tương đối của hàng hoặc cột:
- Cột tuyệt đối và hàng tương đối, như $A1. Khi một công thức với loại tham chiếu này được sao chép sang các ô khác, dấu $ phía trước chữ cái cột sẽ khóa tham chiếu vào cột được chỉ định để nó không bao giờ thay đổi. Tham chiếu hàng tương đối, không có ký hiệu đô la, thay đổi tùy theo hàng mà công thức được sao chép.
- Cột tương đối và hàng tuyệt đối, như A$1. Trong loại tham chiếu này, đó là tham chiếu của hàng sẽ không thay đổi và tham chiếu của cột sẽ
Dưới đây bạn sẽ tìm thấy một ví dụ về việc sử dụng cả hai loại tham chiếu ô hỗn hợp, hy vọng sẽ làm cho mọi thứ dễ hiểu hơn.
Sử dụng tham chiếu hỗn hợp trong Excel – ví dụ về công thức
Trong ví dụ này, chúng tôi sẽ sử dụng lại bảng chuyển đổi tiền tệ của mình. Nhưng lần này, chúng tôi sẽ không giới hạn mình chỉ với chuyển đổi USD – VND. Những gì chúng ta sẽ làm là chuyển đổi giá đô la sang một số loại tiền tệ khác, tất cả chỉ với một công thức!
Để bắt đầu, hãy nhập tỷ lệ chuyển đổi trong một số hàng, giả sử hàng 2, như được hiển thị trong ảnh bên dưới. Và sau đó, bạn chỉ viết một công thức cho ô trên cùng bên trái (trong ví dụ này) để tính giá VND:
=$B5*C$2
Trong đó $B5 là giá đô la trong cùng một hàng và C$2 là tỷ giá chuyển đổi USD – VND.
Và bây giờ, sao chép công thức xuống các ô khác trong cột C và sau đó tự động điền các cột khác với cùng một công thức bằng cách kéo tay cầm điền. Kết quả là, bạn sẽ có 3 cột giá khác nhau được tính toán chính xác dựa trên tỷ giá hối đoái tương ứng trong hàng 2 trong cùng một cột. Để xác minh điều này, chọn bất kỳ ô nào trong bảng và xem công thức trong thanh công thức.
Ví dụ: hãy chọn ô D7 (trong cột EURO). Những gì chúng ta thấy ở đây là công thức =$B7*D$2 lấy giá USD trong B7 và nhân nó với giá trị trong D2, đó là tỷ lệ chuyển đổi USD-EURO.
Và bây giờ, hãy hiểu làm thế nào mà Excel biết chính xác mức giá nào và tỷ giá hối đoái nào để nhân nó lên. Như bạn có thể đoán, đó là các tham chiếu ô hỗn hợp thực hiện thủ thuật ($B5*C$2).
- $B5 – cột tuyệt đối và hàng tương đối . Tại đây, bạn chỉ thêm ký hiệu đô la ($) trước chữ cái cột để neo tham chiếu vào cột A, vì vậy Excel luôn sử dụng giá USD gốc cho tất cả các chuyển đổi. Tham chiếu hàng (không có dấu $) không bị khóa vì bạn muốn tính giá cho từng hàng riêng lẻ.
- C$2 – cột tương đối và hàng tuyệt đối . Bởi vì tất cả các tỷ giá hối đoái nằm trong hàng 2, bạn khóa tham chiếu hàng bằng cách đặt ký hiệu đô la ($) trước số hàng. Và bây giờ, cho dù bạn sao chép công thức vào hàng nào, Excel sẽ luôn tìm tỷ giá hối đoái ở hàng 2. Và vì tham chiếu cột là tương đối (không có dấu $), nên nó sẽ được điều chỉnh cho cột có công thức sao chép.
Cách chuyển đổi giữa các tham chiếu tuyệt đối, tương đối và hỗn hợp (phím F4)
Khi bạn viết một công thức Excel, ký hiệu $ tất nhiên có thể được nhập thủ công để thay đổi một tham chiếu ô tương đối thành tuyệt đối hoặc hỗn hợp. Hoặc dùng phím tắt F4 hoặc Fn + F4 ở một số dòng máy để tối ưu thời gian làm việc.
Lưu ý: Hoạt động phím tắt F4, phải ở chế độ chỉnh sửa công thức.
- Chọn ô có công thức.
- Vào chế độ Chỉnh sửa bằng cách nhấn phím F2 hoặc bấm đúp vào ô.
- Chọn tham chiếu ô bạn muốn thay đổi.
- Nhấn F4 hoặc Fn+F4 chuyển đổi giữa bốn loại tham chiếu ô.
Tôi hy vọng bây giờ bạn hoàn toàn hiểu các tham chiếu ô tương đối và tuyệt đối là gì và một công thức Excel có dấu $ không còn là một bí ẩn nữa.
