Hàm ĐỊA CHỈ Excel để lấy địa chỉ ô và hơn thế nữa

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ỉ ô 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 địa chỉ ô 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:

ĐỊA CHỈ (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ề địa chỉ ô 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ề địa chỉ ô 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ề địa chỉ 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ề đị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 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ả
= ĐỊA CHỈ (1,2)	$ B $ 1	Tham chiếu tế bào tuyệt đối
= ĐỊA CHỈ (1,2,4)	B1	Tham chiếu tế bào 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
= ĐỊA CHỈ (1,2,1 ,, “Tờ 2”)	Tờ2! $ B $ 1	Tham chiếu tuyệt đối đến một tờ khác
= ĐỊA CHỈ (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 (ĐỊA CHỈ (row_num, cột_num))

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 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 địa chỉ 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 ĐỊA CHỈ 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 (ĐỊA CHỈ (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, 50^{thứ tự} cột là AX và 100^{thứ 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à, ĐỊA CHỈ (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:

ĐỊA CHỈ (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 đị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 phạm vi đã đặt tên, hãy sử dụng công thức chung này:

ĐỊA CHỈ (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.

ĐỊA CHỈ (ROW (phạm vi), CỘT(phạm vi)) & “:” & ĐỊA CHỈ (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 địa chỉ ô 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!

Bạn cũng có thể quan tâm:

Source link

The post Hàm ĐỊA CHỈ Excel để lấy địa chỉ ô và hơn thế nữa appeared first on Kế Toán MVB | Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng.

source https://ketoanmvb.com/ham-dia-chi-excel-de-lay-dia-chi-o-va-hon-the-nua.html