Bạn đã biết cách tìm ra một địa chỉ ô trong Excel như thế nào? Để xem kỹ hơn hãy xem tất cả các công thức để xác định địa chỉ của một ô hãy tham khảo trong bài viết bên dưới đây.
1. Địa chỉ ô có nghĩa là gì?
Tham chiếu ô hoặc địa chỉ ô là một giá trị chữ và số được sử dụng để xác định một ô cụ thể trong bảng tính. Mỗi tham chiếu ô chứa một hoặc nhiều chữ cái theo sau là một số. Chữ cái hoặc các chữ cái xác định cột và số đại diện cho hàng.
Để 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ể lấy địa chỉ ô Excel từ số hàng và số cột được cung cấp cho hàm ADDRESS.
2. Hàm ADDRESS trong Excel – cú pháp và cách sử dụng cơ bản:
Hàm ADDRESS được thiết kế để lấy địa chỉ ô trong Excel dựa trên số hàng và số cột đã chỉ định. Một địa chỉ ô được trả về dưới dạng một chuỗi
Hàm này sẵn dùng trong tất cả các phiên bản Excel cho Microsoft 365/
Cú pháp của hàm ADDRESS như sau:
ĐỊA CHỈ (hàng_num, cột_num, [abs_num], [a1], [sheet_text])
Hai đối số đầu tiên là bắt buộc:
row_num – số hàng sẽ sử dụng trong tham chiếu ô.
column_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ị bỏ qua – tham chiếu ô tuyệt đối như $A$1
- 2 – tham chiếu 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 – tham chiếu ô tương đối như A1
a1 – kiểu tham chiếu, A1 hoặc R1C1. Nếu bỏ qua, kiểu A1 mặc định sẽ được sử dụng.
1 hoặc TRUE hoặc bị bỏ qua – trả về địa chỉ ô theo kiểu tham chiếu A1 trong đó cột là chữ cái và hàng là số.
0 hoặc FALSE – trả về địa chỉ ô theo kiểu tham chiếu R1C1 trong đó các hàng và cột được biểu thị bằng số.
sheet_text – tên của trang tính để đưa vào tham chiếu 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ỏ qua, không có tên trang tính nào được sử dụng và địa chỉ sẽ mặc định là trang tính hiện tại.
Ví dụ:
=ADDRESS(1,1)– trả về địa chỉ của ô đầu tiên (nghĩa 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ề địa chỉ 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 thêm một số loại tham chiếu có thể được trả về bằng công thức ADDRESS.
| Công thức | Kết quả | Sự mô tả |
| =ĐỊA CHỈ(1,2) | $B$1 | Tham chiếu ô tuyệt đối |
| =ĐỊA CHỈ(1,2,4) | B1 | Tham chiếu ô tương đối |
| =ĐỊA CHỈ(1,2,2) | B$1 | Cột tương đối và hàng tuyệt đối |
| =ĐỊA CHỈ(1,2,3) | $B1 | Cột tuyệt đối và hàng tương đối |
| =ĐỊA CHỈ(1,2,1,SAI) | R1C2 | Tham chiếu tuyệt đối theo kiểu R1C1 |
| =ĐỊA CHỈ(1,2,4,SAI) | R[1]C[2] | Tham chiếu tương đối theo kiểu R1C1 |
| =ADDRESS(1,2,1,,”Sheet2″) | Sheet2!$B$1 | Tham chiếu tuyệt đối đến một trang tính khác |
| =ADDRESS(1,2,4,,”Sheet2″) | Sheet2!B1 | Tham chiếu tương đối đến một trang tính khác |
3. 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 để hoàn thành các tác vụ khó hơn.
3.1. Trả về một giá trị ô trong một hàng và cột nhất định:
Nếu mục tiêu của bạn là lấy giá trị từ một ô cụ thể dựa trên số hàng và cột của ô đó, hãy sử dụng hàm ADDRESS cùng với INDIRECT (gián tiếp): GIÁN TIẾP(ĐỊA CHỈ(hàng_số, cột_số))
Hàm ADDRESS xuất địa chỉ ô dưới dạng văn bản. Hàm INDIRECT biến văn bản đó thành 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 sau:
=INDIRECT(ADDRESS(E1,E2))
3.2. Lấy địa chỉ của ô 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 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 ta sẽ sử dụng ADDRESS kết hợp với Hàm Match để lấy địa chỉ ô.
Ô có giá trị lớn nhất:
=ADDRESS(MATCH(E2,B:B,0), COLUMN(B2))
Ô có giá trị nhỏ nhất:
=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 vào đối số đầu tiên của MATCH. Ví dụ:
Ô có giá trị lớn nhất:
=ADDRESS(MATCH(MAX(B2:B7),B:B,0), COLUMN(B2))
Ô có giá trị nhỏ nhất:
=ADDRESS(MATCH(MIN(B2:B7),B:B,0), COLUMN(B2))
4. Cách các công thức này hoạt động:
Để bắt đầu, hãy thiết lập hàm ADDRESS để trả về một tham chiếu tương đối đến ô đầu tiên trong cột mục tiêu:
– Đố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, hãy nhập 4.
Kết quả là ADDRESS(1,A2,4) sẽ trả về A1.
Để loại bỏ 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 trống (“”). Xong!
4.1. 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 cần lấy tham chiếu ô đầu tiên và ô cuối cùng, sau đó nối chúng lại với nhau. Điều này hoạt động hơi khác một chút trong Excel tiền động (2019 trở lên) và Dynamic Array Excel (Office 365 và Excel 2021). Các ví dụ dưới đây dành cho Excel 2019 – Excel 2007.
4.2. 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 đã đặt tên, hãy sử dụng công thức chung sau:
ĐỊA CHỈ(ROW( phạm vi ),CỘT( phạm vi ))
Giả sử phạm vi được đặt tên là “Sales”, công thức thực sẽ như sau:
=ADDRESS(ROW(Sales), COLUMN(Sales))
Và trả về địa chỉ của ô phí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 lần lượt trả về một mảng gồm tất cả các số hàng và số cột trong phạm vi. 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 trong một ô duy nhất nên chỉ 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.
4.3. Cách lấy địa chỉ của ô cuối cùng trong một phạm vi:
Để tìm địa chỉ của ô cuối cùng trong một phạm vi được đặt tên, hãy sử dụng công thức chung sau:
ĐỊA CHỈ(ROW( phạm vi )+ROWS( phạm vi )-1,CỘT( phạm vi )+CỘT( 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 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 các phép tính phức tạp hơn một chút để tìm 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 ta thêm 3 vào mỗi phần tử của mảng ban đầu để thực hiện phép dịch 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ề địa chỉ đầu tiên tương ứng với ô cuối cùng trong phạm vi.
Cũng có thể đạt được kết quả tương tự bằng cách chọn các giá trị lớn nhất 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, yêu cầu nhấn Ctrl + Shift + Enter để được hoàn thành một cách chính xác:
=ADDRESS(MAX(ROW(Sales)), MAX(COLUMN(Sales)))
4.4. Cách lấy địa chỉ đầy đủ của một phạm vi được đặt tên:
Để trả về địa chỉ đầy đủ của một dải ô đã đặ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ử dải ô (:) vào giữa.
ĐỊA CHỈ(ROW( phạm vi ), CỘT( phạm vi )) & “:” & ĐỊA CHỈ(ROW( phạm vi ) + HÀNG( phạm vi )-1, CỘT( phạm vi ) + CỘT( phạm vi )-1)
Để làm cho nó hoạt động cho tập
=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 đủ làm tham chiếu tuyệt đối $B$4:$D$7:
Để trả về địa chỉ phạm vi dưới dạng tham chiếu tương đối (không có ký hiệu $, chẳng hạn như B4:D7), hãy đặ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ẽ giống như sau:
5. Cách lấy địa chỉ của dải ô đã đặt tên trong Excel 365 và Excel 2021:
Không giống như hành vi “một công thức – một ô” truyền thống trong các phiên bản cũ hơn, trong Excel mới, bất kỳ công thức nào có khả năng trả về nhiều giá trị đều thực hiện điều này một cách tự động. Hành vi như vậy được gọi là spilling.
Ví dụ: thay vì trả về địa chỉ của ô đầu tiên, công thức bên dưới sẽ xuất địa chỉ của từng và mọi ô trong một phạm vi được đặt tên:
=ADDRESS(ROW(Sales), COLUMN(Sales))
Để chỉ lấy địa chỉ của ô đầu tiên , bạn cần bật giao lộ ẩn, được kích hoạt theo mặc định trong Excel 2019 trở lên. Đối với điều này, hãy đặt ký hiệu @ (toán tử giao nhau ẩn) trước tên dải ô:
=ADDRESS(@ROW(Sales), @COLUMN(Sales))
Theo cách tương tự, bạn có thể sửa các công thức khác.
Để có được ô cuối cùng trong phạm vi:
=ADDRESS(@ROW(Sales) + ROWS(Sales)-1, @COLUMN(Sales) + COLUMNS(Sales)-1)
Để lấy địa chỉ của một phạm vi được đặt tên :
=ADDRESS(@ROW(Sales), @COLUMN(Sales)) & ":" & ADDRESS(@ROW(Sales) + ROWS(Sales)-1, @COLUMN(Sales) + COLUMNS(Sales)-1)
Ảnh chụp màn hình bên dưới hiển thị kết quả:
