Hướng dẫn giới thiệu ngắn gọn về cú pháp hàm ADDRESS và chỉ ra cách sử dụng nó để trả về địa chỉ[giá trị] ô Excel và hơn thế nữa.
Để tạo tham chiếu ô trong Excel, bạn có thể nhập tọa độ cột và hàng theo cách thủ công. Ngoài ra, bạn có thể nhận địa chỉ ô Excel từ số hàng và số cột được cung cấp cho hàm ADDRESS. Gần như vô nghĩa, kết hợp với các chức năng khác, kỹ thuật này có thể là giải pháp duy nhất trong các tình huống khi không thể tham chiếu trực tiếp đến một ô.
Hàm ADDRESS của Excel – cú pháp và cách sử dụng cơ bản
Hàm ADDRESS được thiết kế để lấy giá trị ô trong Excel dựa trên số hàng và số cột được chỉ định. Một địa chỉ ô được trả về dưới dạng một chuỗi văn bản, không phải là một tham chiếu thực tế.
Cú pháp của hàm ADDRESS như sau:
ADDRESS [row_num, cột_num, [abs_num], [a1], [sheet_text]]
Hai đối số đầu tiên được yêu cầu:
row_num – số hàng để sử dụng trong tham chiếu ô.
cột_num – số cột để xây dựng tham chiếu ô.
Ba đối số cuối cùng, xác định định dạng tham chiếu ô, là tùy chọn:
abs_num – loại tham chiếu, tuyệt đối hoặc tương đối. Nó có thể lấy bất kỳ số nào dưới đây; mặc định là tuyệt đối
- 1 hoặc bỏ qua – tuyệt đối tham chiếu ô như $ A $ 1
- 2 – tài liệu tham khảo hỗn hợp: cột tương đối và hàng tuyệt đối như A $ 1
- 3 – tham chiếu hỗn hợp: cột tuyệt đối và hàng tương đối như $ A1
- 4 – quan hệ tham chiếu ô như A1
a1 – kiểu tham chiếu, A1 hoặc R1C1. Nếu bỏ qua, kiểu A1 mặc định được sử dụng.
- 1 hoặc TRUE hoặc bị bỏ qua – trả về giá trị ô trong Kiểu tham chiếu A1 trong đó cột là chữ cái và hàng là số.
- 0 hoặc FALSE – trả về giá trị ô trong Kiểu tham chiếu của R1C1 trong đó các hàng và cột được biểu thị bằng số.
sheet lòng – tên của bảng tính để bao gồm trong tài liệu tham khảo bên ngoài. Tên trang tính phải được cung cấp dưới dạng chuỗi văn bản và được đặt trong dấu ngoặc kép, ví dụ: “Trang tính 2”. Nếu bị bỏ qua, không có tên bảng tính nào được sử dụng và địa chỉ mặc định cho trang tính hiện tại.
Ví dụ:
=ADDRESS[1,1] – trả về giá trị của ô đầu tiên [tức là ô ở giao điểm của hàng đầu tiên và cột đầu tiên] dưới dạng tham chiếu ô tuyệt đối $ A $ 1.
=ADDRESS[1,1,4] – trả về giá trị của ô đầu tiên dưới dạng tham chiếu ô tương đối A1.
Trong bảng sau, bạn sẽ tìm thấy một vài loại tham chiếu khác có thể được trả về bởi các công thức ADDRESS.
| Công thức | Kết quả | Sự miêu tả |
| = ADDRESS [1,2] | $ B $ 1 | Tham chiếu tế bào tuyệt đối |
| = ADDRESS [1,2,4] | B1 | Tham chiếu tế bào tương đối |
| = ADDRESS [1,2,2] | B $ 1 | Cột tương đối và hàng tuyệt đối |
| = ADDRESS [1,2,3] | $ B1 | Cột tuyệt đối và hàng tương đối |
| = ADDRESS [1,2,1, SAI] | R1C2 | Tham chiếu tuyệt đối theo kiểu R1C1 |
| = ADDRESS [1,2,4, SAI] | R[1]C[2] | Tham chiếu tương đối theo kiểu R1C1 |
| = ADDRESS [1,2,1 ,, “Tờ 2”] | Tờ2! $ B $ 1 | Tham chiếu tuyệt đối đến một tờ khác |
| = ADDRESS [1,2,4 ,, “Trang2”] | Tờ2! B1 | Tham chiếu tương đối đến một tờ khác |
Cách sử dụng hàm ADDRESS trong Excel – ví dụ về công thức
Các ví dụ dưới đây cho thấy cách sử dụng hàm ADDRESS bên trong các công thức lớn hơn để thực hiện các nhiệm vụ khó khăn hơn.
Trả về một giá trị ô trong một hàng và cột đã cho
Nếu mục tiêu của bạn là lấy một giá trị từ một ô cụ thể dựa trên số hàng và số cột của nó, hãy sử dụng hàm ADDRESS cùng với GIÁN TIẾP:
INDIRECT [ADDRESS [row_num, cột_num]]
Hàm ADDRESS xuất giá trị ô dưới dạng văn bản. Hàm INDIRECT biến văn bản đó thành một tham chiếu bình thường và trả về giá trị từ ô tương ứng.
Ví dụ: để lấy giá trị ô dựa trên số hàng trong E1 và số cột trong E2, hãy sử dụng công thức này:
=INDIRECT[ADDRESS[E1,E2]]
Lấy giá trị của một ô có giá trị cao nhất hoặc thấp nhất
Trong ví dụ này, trước tiên chúng ta sẽ tìm thấy các giá trị cao nhất và thấp nhất trong phạm vi B2: B7 bằng cách sử dụng các hàm MAX và MIN và xuất các giá trị đó vào các ô đặc biệt:
Ô E2: =MAX[B2:B7]
Ô F2: =MIN[B2:B7]
Và sau đó, chúng tôi sẽ sử dụng ADDRESS kết hợp với Hàm MATCH để lấy địa chỉ ô.
Ô có giá trị tối đa:
=ADDRESS[MATCH[E2,B:B,0], COLUMN[B2]]
Ô có giá trị tối thiểu:
=ADDRESS[MATCH[F2,B:B,0], COLUMN[B2]]
Trong trường hợp bạn không muốn các giá trị cao nhất và thấp nhất trong các ô riêng biệt, bạn có thể lồng hàm MAX / MIN trong đối số đầu tiên của MATCH. Ví dụ:
Ô có giá trị cao nhất:
=ADDRESS[MATCH[MAX[B2:B7],B:B,0], COLUMN[B2]]
Ô có giá trị thấp nhất:
=ADDRESS[MATCH[MIN[B2:B7],B:B,0], COLUMN[B2]]
Những công thức này hoạt động như thế nào
Để tìm số hàng, bạn sử dụng MATCH [lookup_value, lookup_array, [match_type]] trả về vị trí tương đối của lookup_value trong lookup_array. Trong công thức của chúng tôi, giá trị tra cứu là số được trả về bởi hàm MAX hoặc MIN và mảng tra cứu là toàn bộ cột. Do đó, một vị trí tương đối của giá trị tra cứu trong mảng khớp chính xác với số hàng trên trang tính.
Để tìm số cột, bạn sử dụng hàm COLUM. Tất nhiên, không có gì ngăn bạn nhập số trực tiếp vào công thức, nhưng COLUMN lưu lại sự cố khi đếm thủ công trong trường hợp cột mục tiêu ở giữa trang tính.
Nhận một chữ cái cột từ một số cột
Để biến bất kỳ số đã cho nào thành một chữ cái cột, hãy sử dụng hàm ADDRESS bên trong SUBSTITUTE:
SUBSTITUTE [ADDRESS [1,số cột, 4], “1”, “”]
Ví dụ: chúng ta hãy tìm chữ cái cột tương ứng với số trong A2:
=SUBSTITUTE[ADDRESS[1,A2,4],"1",""]
Nhìn vào kết quả dưới đây, chúng ta có thể nói rằng cột đầu tiên trên trang tính là A, điều này là hiển nhiên; cột thứ 10 là J, 50thứ tự cột là AX và 100thứ tự cột là CV:
Công thức này hoạt động như thế nào
Để bắt đầu, hãy thiết lập hàm ADDRESS để trả về tham chiếu tương đối cho ô đầu tiên trong cột đích:
- Đối với số hàng, sử dụng 1.
- Đối với số cột, cung cấp tham chiếu đến ô chứa số, A2 trong ví dụ của chúng tôi.
- Đối với đối số abs_num, nhập 4.
Kết quả là, ADDRESS [1, A2,4] sẽ trả về A1.
Để thoát khỏi tọa độ hàng, hãy bọc công thức trên trong Hàm SUBSTITUTE và thay thế “1” bằng một chuỗi rỗng [“”]. Làm xong!
Lấy địa chỉ của một phạm vi được đặt tên
Để tìm địa chỉ của một phạm vi được đặt tên trong Excel, trước tiên bạn sẽ cần có được các tham chiếu ô đầu tiên và cuối cùng, sau đó nối chúng lại với nhau.
Cách lấy địa chỉ của ô đầu tiên trong một phạm vi
Để trả về một tham chiếu đến ô đầu tiên trong một phạm vi được đặt tên, hãy sử dụng công thức chung này:
ADDRESS [ROW [phạm vi],CỘT[phạm vi]]
Giả sử phạm vi được đặt tên là “Bán hàng”, công thức thực tế diễn ra như sau:
=ADDRESS[ROW[Sales], COLUMN[Sales]]
Và trả về địa chỉ của ô trên bên trái trong phạm vi:
Trong công thức này, các hàm ROW và COLUMN trả về một mảng của tất cả các số hàng và số cột trong phạm vi, tương ứng. Dựa trên những con số đó, hàm ADDRESS xây dựng một mảng các địa chỉ ô. Nhưng vì công thức được nhập vào một ô duy nhất, nên chỉ có mục đầu tiên của mảng được hiển thị, tương ứng với ô đầu tiên trong phạm vi.
Cách lấy giá trị của ô cuối cùng trong một phạm vi
Để tìm địa chỉ của ô cuối cùng trong phạm vi đã đặt tên, hãy sử dụng công thức chung này:
ADDRESS [ROW [phạm vi] + ROWS [phạm vi] -1, CỘT [phạm vi] + MÀU SẮC [phạm vi] -1]
Áp dụng cho phạm vi của chúng tôi có tên là “Bán hàng”, công thức có dạng như sau:
=ADDRESS[ROW[Sales] + ROWS[Sales]-1, COLUMN[Sales] + COLUMNS[Sales]-1]
Và trả về tham chiếu đến ô dưới cùng bên phải của phạm vi:
Lần này, chúng ta cần một phép tính phức tạp hơn một chút để tính ra số hàng. Giống như trong ví dụ trước, hàm ROW cung cấp cho chúng ta một mảng gồm tất cả các số hàng trong phạm vi, {4; 5; 6; 7} trong trường hợp của chúng ta. Chúng ta cần “dịch chuyển” các số này bằng tổng số hàng trừ đi 1, để mục đầu tiên trong mảng trở thành số hàng cuối cùng. Để tìm tổng số hàng, chúng tôi sử dụng hàm ROWS và trừ 1 từ kết quả của nó: [4-1 = 3]. Sau đó, chúng tôi thêm 3 vào mỗi phần tử của mảng ban đầu để thực hiện dịch chuyển cần thiết: {4; 5; 6; 7} + 3 = {7; 8; 9; 10}.
Số cột được tính theo cách tương tự: {2,3,4} + 3-1 = {4,5,6}
Từ các mảng số hàng và số cột ở trên, hàm ADDRESS tập hợp một mảng các địa chỉ ô, nhưng chỉ trả về mảng đầu tiên tương ứng với ô cuối cùng trong phạm vi.
Kết quả tương tự cũng có thể đạt được bằng cách chọn các giá trị tối đa từ các mảng của số hàng và số cột. Tuy nhiên, điều này chỉ hoạt động trong một công thức mảng, đòi hỏi phải nhấn Ctrl + Shift + Enter để được hoàn thành chính xác:
=ADDRESS[MAX[ROW[Sales]], MAX[COLUMN[Sales]]]
Làm thế nào để có được địa chỉ đầy đủ của một phạm vi được đặt tên
Để trả về địa chỉ đầy đủ của một phạm vi được đặt tên, bạn chỉ cần nối hai công thức từ các ví dụ trước và chèn toán tử phạm vi [:] ở giữa.
ADDRESS [ROW [phạm vi], CỘT[phạm vi]] & “:” & ADDRESS [ROW [phạm vi] + ROWS [phạm vi] -1, CỘT [phạm vi] + MÀU SẮC [phạm vi] -1]
Để làm cho nó hoạt động cho tập dữ liệu mẫu của chúng tôi, chúng tôi thay thế “phạm vi” chung bằng tên phạm vi thực “Bán hàng”:
=ADDRESS[ROW[Sales], COLUMN[Sales]] & ":" & ADDRESS[ROW[Sales] + ROWS[Sales]-1, COLUMN[Sales] + COLUMNS[Sales]-1]
Và lấy địa chỉ phạm vi đầy đủ dưới dạng một tuyệt đối tham khảo $ B $ 4: $ D $ 7:
Để trả về địa chỉ phạm vi dưới dạng quan hệ tham chiếu [không có dấu $, như B4: D7], đặt đối số abs_num trong cả hai hàm ADDRESS thành 4:
=ADDRESS[ROW[Sales], COLUMN[Sales], 4] & ":" & ADDRESS[ROW[Sales] + ROWS[Sales]-1, COLUMN[Sales] + COLUMNS[Sales]-1, 4]
Đương nhiên, những thay đổi tương tự có thể được thực hiện trong các công thức riêng lẻ cho ô đầu tiên và ô cuối cùng, và kết quả sẽ trông giống như sau:
Đó là cách bạn trả về một giá trị ô trong Excel. Để xem kỹ hơn tất cả các công thức được thảo luận trong hướng dẫn này, rất mong bạn tải xuống Sổ địa chỉ chức năng Excel. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!