Hàm ADDRESS trong Excel lấy số hàng và số cột làm đối số và trả về tham chiếu ô tiêu chuẩn (địa chỉ ô). Ví dụ, nếu bạn tới hàng số 4 và cột số 3, hàm sẽ trả về C4. Hàm ADDRESS của Excel có thể trả về một tham chiếu tuyệt đối hoặc tương đối ở một trong hai định dạng tham chiếu của Excel.
Tìm hiểu cách sử dụng hàm ADDRESS trong Excel
Các loại tham chiếu
- Tham chiếu tương đối được thể hiện dưới dạng cột và số hàng (ví dụ: M290). Khi bạn sao chép một công thức có chứa tham chiếu ô tương đối, tham chiếu – số hàng và ký tự cột – được điều chỉnh để phản ánh vị trí mà bạn đã sao chép công thức.
- Tham chiếu tuyệt đối có ký hiệu $ ở phía trước chữ cái cột và số hàng (ví dụ: $M$290). Khi bạn sao chép một công thức có chứa tham chiếu ô tuyệt đối, tham chiếu đó không thay đổi.
- Tham chiếu hỗn hợp có ký hiệu $ ở phía trước chữ cái cột hoặc số hàng (ví dụ: $M290 hoặc M$290). Khi bạn sao chép một công thức có chứa tham chiếu ô hỗn hợp, phần tham chiếu có ký hiệu $ không thay đổi, nhưng phần khác thì có.
Hình ảnh sau đây cho thấy một bảng tính trong đó việc nhập công thức với tham chiếu ô tương đối gây ra sự cố. Phần tổng số (Total) là kết quả của việc cộng thêm thuế vào số tiền ban đầu. Mức thuế là 7,5% (0,075). Tỷ lệ này nằm trong ô C1 và được tham chiếu bởi các công thức. Công thức đầu tiên được nhập vào trong ô C7 và có nội dung như sau: =B7*(1 + C1).
Công thức trong ô C7 là chính xác. Nó tham chiếu ô C1 để tính tổng. Nhưng nếu bạn sử dụng fill handle (chấm màu đen ở góc dưới bên phải của ô được chọn) để sao chép công thức từ ô C7 sang ô C8 và C9, thì sẽ có vấn đề xảy ra. Tham chiếu đến ô C1 đã thay đổi thành ô C2 và C3. Vì các ô này trống nên kết quả trong các ô C8 và C9 không chính xác (kết quả giống như số tiền ở bên trái và thuế chưa được thêm vào).
Để hiểu rõ hơn, hãy nhìn vào cột D hiển thị các công thức nằm trong cột C. Khi công thức trong ô C7 được kéo xuống, tham chiếu C1 đã thay đổi thành C2 trong ô C8 và C3 trong ô C9. Thông thường, người dùng sẽ muốn để Excel tự động thay đổi tham chiếu ô khi công thức được sao chép. Nhưng đôi khi (ví dụ như trong tình huống này), bạn lại không muốn thực hiện việc này. Bạn cần một tham chiếu ô tuyệt đối.
Công thức trong ô C17 gần giống với công thức trong ô C7, ngoại trừ việc tham chiếu đến ô C1 đã được tạo thành hàng tuyệt đối, bằng cách đặt ký hiệu $ trước số hàng. Công thức trong ô C17 trông như sau: =B17*(1 + C$1). Khi công thức này được kéo xuống C18 và C19, tham chiếu không được điều chỉnh mà vẫn chỉ vào ô C1. Lưu ý rằng trong ví dụ này, chỉ phần hàng của tham chiếu là tuyệt đối. Bạn có thể đã tạo tham chiếu hoàn toàn tuyệt đối bằng cách nhập cú pháp sau: =B17*(1 + $C$1). Kết quả sẽ giống nhau, nhưng điều đó không bắt buộc trong ví dụ này.
2 kiểu tham chiếu ô
Lưu ý: Đặt ký hiệu $ trước chữ cái cột của tham chiếu ô để tạo tham chiếu cột tuyệt đối. Đặt ký hiệu $ trước số hàng để tạo tham chiếu hàng tuyệt đối.
Excel hỗ trợ hai kiểu tham chiếu ô: Kiểu A1 cũ và kiểu R1C1. Kiểu R1C1 sử dụng hệ thống số cho cả hàng và cột, chẳng hạn như: R4C10. Trong ví dụ này, R4C10 có nghĩa là hàng 4 cột 10.
Để thay đổi kiểu tham chiếu ô, chọn File > Options và kiểm tra kiểu tham chiếu R1C1 trong khu vực Working with Formulas trên tab Formulas. Sử dụng định dạng R1C1 cũng buộc các cột trên bảng tính hiển thị dưới dạng số thay vì hệ thống chữ. Điều này rất hữu ích khi bạn làm việc với một số lượng lớn các cột. Ví dụ, cột CV theo vị trí là cột thứ 100. Ghi nhớ con số 100 dễ hơn ghi nhớ chữ CV.
Để quay lại hàm ADDRESS của Excel, phải mất tới 5 đối số:
- Số hàng của tham chiếu
- Số cột của tham chiếu
- Một số cho hàm biết làm thế nào để trả về tham chiếu. Số này mặc định là 1, nhưng cũng có thể là:
- 1: Cả hàng và cột đều tuyệt đối
- 2: Hàng tuyệt đối và cột tương đối
- 3: Hàng tương đối và cột tuyệt đối
- 4: Cả hàng và cột đều tương đối
- Giá trị 0 hoặc 1 để cho hàm biết phải sử dụng kiểu tham chiếu nào:
- 0 sử dụng kiểu R1C1.
- 1 (giá trị mặc định và được bỏ qua nếu dùng) sử dụng kiểu A1.
- Một bảng tính hoặc workbook bên ngoài và tham chiếu bảng tính.
Chỉ có hai đối số đầu tiên là bắt buộc. Hàm trả về tham chiếu đã chỉ định dưới dạng văn bản.
| Cú pháp | Kết quả | Giải thích |
=ADDRESS(5,2) | $B$5 | Chỉ có cột và hàng được cung cấp làm đối số. Hàm trả về một địa chỉ tuyệt đối đầy đủ. |
=ADDRESS(5,2,1) | $B$5 | Khi 1 được sử dụng làm đối số thứ ba, một địa chỉ tuyệt đối đầy đủ được trả về. Kết quả cũng tương tự nếu bỏ qua đối số thứ ba. |
=ADDRESS(5,2,2) | B$5 | Khi 2 được sử dụng làm đối số thứ ba, một tham chiếu hỗn hợp được trả về, với giá trị cột tương đối và hàng tuyệt đối. |
=ADDRESS(5,2,3) | $B5 | Khi 3 được sử dụng làm đối số thứ ba, một tham chiếu hỗn hợp được trả về, với giá trị cột tuyệt đối và hàng tương đối. |
=ADDRESS(5,2,4) | B5 | Khi 4 được sử dụng làm đối số thứ ba, một tham chiếu tương đối đầy đủ được trả về. |
=ADDRESS(5,2,1,0) | R5C2 | Khi đối số thứ tư bị sai, tham chiếu kiểu R1C1 được trả về. |
=ADDRESS(5,2,3,0) | R[5]C2 | Ví dụ này cho biết hàm trả về một tham chiếu hỗn hợp theo kiểu R1C1. |
=ADDRESS(5,2,1,,"Sheet4") | Sheet4!$B$5 | Đối số thứ năm trả về tham chiếu đến một bảng tính hoặc workbook bên ngoài. Cú pháp này trả về tham chiếu kiểu A1 cho ô B5 trên Sheet 4. |
=ADDRESS(5,2,1,0,"Sheet4") | Sheet4!R5C2 | Cú pháp này trả về tham chiếu kiểu R1C1 cho ô B5 trên Sheet 4. |
Cách sử dụng hàm ADDRESS trong Excel
Sử dụng hàm ADDRESS trong Excel như sau:
1. Bấm vào một ô nơi bạn muốn kết quả xuất hiện.
2. Nhập =ADDRESS( để bắt đầu hàm.
3. Nhập số hàng, dấu phẩy (,) và số cột. Bạn cũng có thể nhập tham chiếu đến các ô nơi đặt các giá trị đó.
4. Nếu bạn muốn kết quả được trả về trong một tham chiếu hỗn hợp hoặc đầy đủ, hãy nhập dấu phẩy (,) và một số thích hợp: 2, 3 hoặc 4.
5. Nếu bạn muốn kết quả được trả về theo kiểu R1C1, hãy nhập dấu phẩy (,) và số 0.
6. Nếu bạn muốn kết quả tham chiếu đến một bảng tính khác, hãy nhập dấu phẩy và đặt tên của bảng tính trong dấu ngoặc kép.
Nếu bạn muốn kết quả tham chiếu đến workbook bên ngoài, hãy nhập dấu phẩy (,), tên workbook và trang tính cùng nhau. Tên workbook được đặt trong ngoặc và toàn bộ tham chiếu có dấu ngoặc kép, chẳng hạn như: “[Book1] Sheet2”.
7. Nhập ) và nhấn Enter.
Thay vì nhập số hàng và số cột trực tiếp trong ADDRESS, bạn có thể nhập tham chiếu ô. Tuy nhiên, giá trị bạn tìm thấy trong những ô đó phải ước tính cho các số có thể được sử dụng làm số hàng và số cột.
