HÀM TRẢ VỀ ĐỊA CHỈ Ô TRONG EXCEL

     
Cách áp dụng hàm ADDRESS vào Excel – ví dụ như về công thứcLấy quý hiếm của một ô có mức giá trị tối đa hoặc tốt nhấtNhận một vần âm cột từ một vài cộtLấy add của một phạm vi được để tên

Hướng dẫn reviews ngắn gọn về cú pháp hàm ADDRESS và chỉ ra cách sử dụng nó nhằm trả về địa chỉ(giá trị) ô Excel và không những thế nữa.

Bạn đang xem: Hàm trả về địa chỉ ô trong excel


Để tạo ra tham chiếu ô trong Excel, chúng ta có thể nhập tọa độ cột và hàng theo cách thủ công. Ko kể ra, bạn cũng có thể nhận địa chỉ cửa hàng ô Excel từ số hàng cùng số cột được hỗ trợ cho hàm ADDRESS. Gần như là vô nghĩa, kết hợp với các tính năng khác, chuyên môn này hoàn toàn có thể là phương án duy độc nhất trong các trường hợp khi tất yêu tham chiếu trực tiếp đến một ô.

Hàm ADDRESS của Excel – cú pháp với cách áp dụng cơ bản

Hàm ADDRESS được thiết kế theo phong cách để lấy quý giá ô vào Excel dựa trên số hàng cùng số cột được chỉ định. Một add ô được trả về bên 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, , , )

Hai đối số đầu tiên được yêu thương cầu:

row_num – số hàng để sử dụng trong tham chiếu ô.

cột_num – số cột để sản xuất 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 vời nhất hoặc tương đối. Nó hoàn toàn có thể lấy ngẫu nhiên số nào dưới đây; mang định là hay đối

3 – tham chiếu láo hợp: cột tuyệt đối hoàn hảo và hàng kha khá như $ A1

a1 – loại tham chiếu, A1 hoặc R1C1. Nếu quăng quật qua, phong cách A1 mặc định được sử dụng.

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 cần được cung ứng dưới dạng chuỗi văn phiên bản và được đặt trong lốt ngoặc kép, ví dụ: “Trang tính 2”. Nếu như bị quăng quật qua, không mang tên bảng tính nào được áp dụng và showroom mặc định mang đến trang tính hiện nay tại.

Ví dụ:

=ADDRESS(1,1) – trả về giá trị của ô trước tiên (tức là ô sinh hoạt giao điểm của bậc nhất tiên cùng cột đầu tiên) dưới dạng tham chiếu ô tuyệt vời nhất $ A $ 1.

=ADDRESS(1,1,4) – trả về quý giá của ô trước tiên dưới dạng tham chiếu ô kha khá 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 những công thức ADDRESS.

Công thứcKết quảSự miêu tả
= ADDRESS (1,2)$ B $ 1Tham chiếu tế bào hay đối
= ADDRESS (1,2,4)B1Tham chiếu tế bào tương đối
= ADDRESS (1,2,2)B $ 1Cột tương đối và hàng tốt đối
= ADDRESS (1,2,3)$ B1Cột tuyệt đối và hàng tương đối
= ADDRESS (1,2,1, SAI)R1C2Tham chiếu hoàn hảo theo giao diện R1C1
= ADDRESS (1,2,4, SAI)R<1>C<2>Tham chiếu kha khá theo phong cách R1C1
= ADDRESS (1,2,1 ,, “Tờ 2”)Tờ2! $ B $ 1Tham chiếu tuyệt đối đến một tờ khác
= ADDRESS (1,2,4 ,, “Trang2”)Tờ2! B1Tham chiếu kha khá đến một tờ khác

Cách áp dụng hàm ADDRESS trong Excel – lấy một ví dụ về công thức

Các ví dụ dưới đây cho biết thêm 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.

Xem thêm: 100+ Hình Nền Màu Sắc Cho Điện Thoại, Hình Nền Màu Thuần Khiết

Trả về một cực hiếm ô trong một hàng và cột sẽ cho

Nếu mục tiêu của người sử dụng là đem một giá chỉ trị xuất phát từ 1 ô ví dụ dựa bên trên số hàng với số cột của nó, hãy thực hiện hàm ADDRESS cùng rất GIÁN TIẾP:

INDIRECT (ADDRESS (row_num, cột_num))

Hàm ADDRESS xuất quý hiếm ô dưới dạng văn bản. Hàm INDIRECT biến hóa văn phiên bản đó thành một tham chiếu bình thường và trả về cực hiếm từ ô tương ứng.

Ví dụ: để lấy giá trị ô dựa trên số hàng trong E1 cùng số cột trong E2, hãy sử dụng công thức này:

=INDIRECT(ADDRESS(E1,E2))

*

Lấy cực hiếm của một ô có giá trị tối đa hoặc tốt nhất

Trong lấy một ví dụ này, trước tiên bọn họ sẽ kiếm tìm thấy các giá trị tối đa và thấp tốt nhất trong phạm vi B2: B7 bằng cách sử dụng các hàm MAX và MIN cùng xuất các giá trị kia vào các ô đặc biệt:

Ô E2: =MAX(B2:B7)

Ô F2: =MIN(B2:B7)

*

Và sau đó, cửa hàng chúng tôi sẽ thực hiện ADDRESS kết hợp với Hàm MATCH nhằm lấy add ô.

Ô có giá trị tối đa:

=ADDRESS(MATCH(E2,B:B,0), COLUMN(B2))

Ô có mức giá trị về tối thiểu:

=ADDRESS(MATCH(F2,B:B,0), COLUMN(B2))

*

Trong ngôi trường hợp chúng ta không muốn các giá trị tối đa và tốt nhất trong những ô riêng biệt biệt, bạn có thể lồng hàm MAX / MIN vào đối số trước tiên của MATCH. Ví dụ:

Ô có mứ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 phương pháp này vận động như cầm nào

Để search số hàng, bạn thực hiện MATCH (lookup_value, lookup_array, ) trả về vị trí kha khá của lookup_value vào lookup_array. Trong phương pháp của chúng tôi, quý hiếm tra cứu vớt là số được trả về vì chưng hàm MAX hoặc MIN cùng mảng tra cứu giúp là cục bộ cột. Vì chưng đó, một vị trí tương đối của quý giá tra cứu vãn trong mảng khớp chính xác với số sản phẩm trên trang tính.

Để tìm số cột, bạn thực hiện hàm COLUM. Vớ nhiên, không tồn tại gì ngăn bạn nhập số thẳng vào công thức, nhưng lại COLUMN lưu lại sự cầm cố khi đếm bằng tay trong trường thích hợp cột phương châm ở giữa trang tính.

Nhận một vần âm cột từ một số trong những cột

Để biến bất kỳ số đã mang lại nào thành một chữ cái cột, hãy áp dụng hàm ADDRESS bên phía trong SUBSTITUTE:

SUBSTITUTE (ADDRESS (1,số cột, 4), “1”, “”)

Ví dụ: họ hãy tìm vần âm cột khớp ứng với số vào A2:

=SUBSTITUTE(ADDRESS(1,A2,4),"1","")

Nhìn vào tác dụng dưới đây, bạn có thể nói rằng cột thứ nhất trên trang tính là A, điều đó là hiển nhiên; cột trang bị 10 là J, 50thứ từ bỏ cột là AX với 100thứ tự cột là CV:

*

Công thức này vận động như vậy nào

Để bắt đầu, hãy thiết lập cấu hình hàm ADDRESS nhằm trả về tham chiếu tương đối cho ô thứ nhất trong cột đích:

Đối cùng với số hàng, thực hiện 1.Đối cùng với số cột, cung cấp tham chiếu cho ô cất số, A2 trong lấy một ví dụ của chúng tôi.Đối cùng với đối số abs_num, nhập 4.

Kết trái là, ADDRESS (1, A2,4) vẫn trả về A1.

Để thoát khỏi tọa độ hàng, hãy bọc bí quyết trên vào Hàm SUBSTITUTE và sửa chữa thay thế “1” bởi một chuỗi rỗng (“”). Làm cho xong!

Cách áp dụng Tìm kiếm kim chỉ nam trong Excel để tiến hành phân tích What-If

Cách thêm đường xu hướng trong biểu vật dụng Excel

Thanh lỗi vào Excel: tiêu chuẩn chỉnh và tùy chỉnh

#SPILL! lỗi vào Excel – nó có nghĩa là gì và phương pháp khắc phục

Lấy showroom của một phạm vi được đặt tên

Để tìm showroom của một phạm vi được lấy tên trong Excel, trước tiên bạn sẽ cần gồm được những tham chiếu ô trước tiên và cuối cùng, sau đó nối bọn chúng lại cùng với nhau.

Cách lấy add của ô trước tiên trong một phạm vi

Để trả về một tham chiếu đến ô thứ nhất 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 chọn cái tên là “Bán hàng”, bí quyết thực tế ra mắt như sau:

=ADDRESS(ROW(Sales), COLUMN(Sales))

Và trả về add của ô trên phía trái trong phạm vi:

*

Trong phương pháp này, những hàm ROW cùng COLUMN trả về một mảng của tất cả các số hàng với số cột vào phạm vi, tương ứng. Dựa trên những con số đó, hàm ADDRESS xây cất một mảng các địa chỉ ô. Tuy vậy vì bí quyết được nhập vào trong 1 ô duy nhất, nên có thể có mục trước tiên của mảng được hiển thị, khớp ứng với ô thứ nhất trong phạm vi.

Cách lấy quý giá của ô cuối cùng trong một phạm vi

Để tìm showroom của ô sau cùng trong phạm vi đã đặt tên, hãy sử dụng công thức phổ biến 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 đến phạm vi của chúng tôi có tên là “Bán hàng”, công thức gồm dạng như sau:

=ADDRESS(ROW(Sales) + ROWS(Sales)-1, COLUMN(Sales) + COLUMNS(Sales)-1)

Và trả về tham chiếu cho ô dưới thuộc bên phải của phạm vi:

*

Lần này, họ cần một phép tính tinh vi hơn một chút ít để tính ra số hàng. Giống như trong lấy một ví dụ trước, hàm ROW cung cấp cho bọn họ một mảng gồm tất cả các số hàng trong phạm vi, 4; 5; 6; 7 vào trường hợp của chúng ta. Bọn họ cần “dịch chuyển” những số này bằng tổng số mặt hàng trừ đi 1, nhằm mục trước tiên trong mảng biến đổi số sản phẩm 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ừ hiệu quả của nó: (4-1 = 3). Sau đó, shop chúng tôi thêm 3 vào mỗi thành phần của mảng ban đầu để thực hiện dịch rời cần thiết: 4; 5; 6; 7 + 3 = 7; 8; 9; 10.

Số cột được xem theo giải pháp tương tự: 2,3,4 + 3-1 = 4,5,6

Từ những mảng số hàng với số cột làm việc trên, hàm ADDRESS tập hợp một mảng các địa chỉ ô, dẫu vậy chỉ trả về mảng đầu tiên tương ứng cùng với ô sau cùng trong phạm vi.

Xem thêm: I (Iốt) Khối Lượng Mol Của Iot, I (Iốt) Khối Lượng Mol

Kết quả tương tự như cũng có thể đạt được bằng phương pháp chọn các giá trị về tối đa từ những mảng của số hàng và số cột. Mặc dù nhiên, vấn đề này chỉ vận động trong một cách làm mảng, đòi hỏi phải dấn Ctrl + Shift + Enter để được xong xuôi chính xác:

=ADDRESS(MAX(ROW(Sales)), MAX(COLUMN(Sales)))

Làm núm nào để có được địa chỉ cửa hàng đầy đầy đủ của một phạm vi được đặt tên

Để trả về add đầy đầy đủ của một phạm vi được đặt tên, bạn chỉ việc nối hai cách làm từ những ví dụ trước cùng chèn toán tử phạm vi (:) ở giữa.

ADDRESS (ROW (phạm vi), CỘT(phạm vi)) và “:” và ADDRESS (ROW (phạm vi) + ROWS (phạm vi) -1, CỘT (phạm vi) + MÀU SẮC (phạm vi) -1)

Để tạo cho nó chuyển động cho tập dữ liệu mẫu của chúng tôi, shop chúng tôi thay cụ “phạm vi” chung bởi tên phạm vi thực “Bán hàng”:

=ADDRESS(ROW(Sales), COLUMN(Sales)) và ":" và ADDRESS(ROW(Sales) + ROWS(Sales)-1, COLUMN(Sales) + COLUMNS(Sales)-1)

Và lấy showroom phạm vi đầy đủ dưới dạng một tuyệt đối tìm hiểu thêm $ B $ 4: $ D $ 7:

*

Để trả về địa chỉ cửa hàng phạm vi dưới dạng quan hệ tham chiếu (không có dấu $, như B4: D7), để đối số abs_num trong cả nhị hàm ADDRESS thành 4:

=ADDRESS(ROW(Sales), COLUMN(Sales), 4) & ":" và ADDRESS(ROW(Sales) + ROWS(Sales)-1, COLUMN(Sales) + COLUMNS(Sales)-1, 4)

Đương nhiên, những thay đổi tương tự rất có thể được thực hiện trong các công thức đơn côi cho ô thứ nhất và ô cuối cùng, và hiệu quả sẽ trông y như sau:

*

Đó là cách bạn trả về một giá trị ô trong Excel. Để coi kỹ hơn tất cả các cách làm được bàn bạc trong lý giải này, rất mong muốn bạn download xuống Sổ add chức năng Excel. Tôi cảm ơn chúng ta đã hiểu và hi vọng sẽ chạm mặt bạn bên trên blog của cửa hàng chúng tôi vào tuần tới!