Công thức chung
=ADDRESS(ROW(nr),COLUMN(nr))&":"&ADDRESS(ROW(nr)+ROWS(nr)-1,COLUMN(nr)+COLUMNS(nr)-1)
Tóm lược
Để lấy địa chỉ đầy đủ của một dải ô đã đặt tên bằng công thức Excel, bạn có thể sử dụng hàm ADDRESS cùng với các hàm ROW và COLUMN. Trong ví dụ được hiển thị, công thức trong G5 là:
=ADDRESS(ROW(data),COLUMN(data),4)&":"&ADDRESS(ROW(data)+ROWS(data)-1,COLUMN(data)+COLUMNS(data)-1,4)
trong đó "dữ liệu" là phạm vi được đặt tên B5: D10
Giải trình
Cốt lõi của công thức này là hàm ADDRESS, được sử dụng để trả về địa chỉ ô dựa trên một hàng và cột nhất định. Thật không may, công thức hơi phức tạp vì chúng ta cần sử dụng ADDRESS hai lần: một lần để lấy địa chỉ của ô đầu tiên trong phạm vi và một lần để lấy địa chỉ của ô cuối cùng trong phạm vi. Hai kết quả được nối bằng phép nối và toán tử phạm vi (:) và toàn bộ phạm vi được trả về dưới dạng văn bản.
Để lấy ô đầu tiên trong phạm vi, chúng tôi sử dụng biểu thức sau:
=ADDRESS(ROW(data),COLUMN(data))
ROW trả về số hàng đầu tiên được liên kết với phạm vi, 5 *.
COLUMN trả về số cột đầu tiên được liên kết với phạm vi, 2.
Với abs_num được đặt thành 4 (tương đối), ADDRESS trả về văn bản "B5".
=ADDRESS(5,2,4) // returns "B5"
Để lấy ô cuối cùng trong phạm vi, chúng tôi sử dụng biểu thức sau:
=ADDRESS(ROW(data)+ROWS(data)-1,COLUMN(data)+COLUMNS(data)-1,4)
Xem trang này để được giải thích chi tiết.
Về cơ bản, chúng tôi thực hiện theo cùng một ý tưởng như trên, thêm phép toán đơn giản để tính toán hàng cuối cùng và cột cuối cùng của phạm vi, được đưa như trước vào ADDRESS với abs_num được đặt thành 4. Điều này giảm xuống biểu thức sau, trả về văn bản " D10 ":
=ADDRESS(10,4,4) // returns "D10"
Cả hai kết quả được nối bằng dấu hai chấm để nhận địa chỉ phạm vi cuối cùng dưới dạng văn bản:
="B5"&":"&"D10" ="B5:D10
Dải ô được đặt tên từ một ô khác
Để lấy địa chỉ cho một dải ô đã đặt tên trong một ô khác, bạn cần sử dụng hàm INDIRECT. Ví dụ, để lấy địa chỉ của tên trong A1, bạn sẽ sử dụng:
=ADDRESS(ROW(INDIRECT(A1)),COLUMN(INDIRECT(A1)))&":"&ADDRESS(ROW(INDIRECT(A1))+ROWS(INDIRECT(A1))-1,COLUMN(INDIRECT(A1))+COLUMNS(INDIRECT(A1))-1)
Đặt abs_num thành 4 bên trong ADDRESS để lấy địa chỉ tương đối.
* Trên thực tế, trong tất cả các trường hợp chúng tôi sử dụng ROW và COLUMN với một dải ô có tên nhiều ô, chúng tôi sẽ nhận lại một mảng số thay vì một giá trị duy nhất. Tuy nhiên, vì chúng tôi không sử dụng công thức mảng nên việc xử lý bị giới hạn ở mục đầu tiên trong các mảng này.
