Lấy giá trị của một ô có mức giá trị cao nhất hoặc thấp nhất
Nhận một vần âm cột từ một số cột
Lấy showroom của một phạm vi được để tên
Hướng dẫn ra mắt ngắn gọn về cú pháp hàm ADDRESS và chỉ ra rằng cách sử dụng nó để trả về địa chỉ(giá trị) ô Excel và hơn thế nữa nữa.
Bạn đang xem: Hàm lấy vị trí ô trong excel
Để tạo nên tham chiếu ô trong Excel, bạn cũng có thể nhập tọa độ cột cùng hàng theo cách thủ công. Không tính ra, bạn có thể nhận địa chỉ ô Excel tự số hàng với 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ác dụng khác, kỹ thuật này có thể là giải pháp duy tốt nhất trong các tình huống khi không thể tham chiếu trực tiếp nối một ô.
Hàm ADDRESS của Excel – cú pháp cùng cách sử dụng cơ bản
Hàm ADDRESS có thiết kế để lấy quý hiếm ô trong Excel dựa vào số hàng cùng số cột được chỉ định. Một showroom ô được trả về bên dưới dạng một chuỗi văn bản, ko phải là 1 trong 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ố thứ nhất được yêu cầu:
row_num – số sản phẩm để thực hiện trong tham chiếu ô.
cột_num – số cột để kiến thiế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 – các loại tham chiếu, tuyệt vời hoặc tương đối. Nó rất có thể lấy ngẫu nhiên số nào bên dưới đây; khoác định là xuất xắc đối
3 – tham chiếu láo lếu hợp: cột tuyệt vời và hoàn hảo nhất và hàng kha khá như $ A1a1 – phong cách tham chiếu, A1 hoặc R1C1. Nếu bỏ qua, kiểu dáng A1 mang định được sử dụng.
sheet lòng – thương hiệu của bảng tính để bao gồm trong tài liệu tìm hiểu thêm bên ngoài. Tên trang tính buộc phải được cung cấp dưới dạng chuỗi văn bạn dạng và được đặt trong lốt ngoặc kép, ví dụ: “Trang tính 2”. Giả dụ bị quăng quật qua, không mang tên bảng tính như thế nào được sử dụng và địa chỉ mặc định đến trang tính hiện tại.
Ví dụ:
=ADDRESS(1,1) – trả về cực hiếm của ô thứ nhất (tức là ô ở giao điểm của hàng đầu tiên và cột đầu tiên) bê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, các bạn sẽ tìm thấy một vài các loại tham chiếu khác có thể được trả về bởi những 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 tố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 giỏi đối |
| = ADDRESS (1,2,3) | $ B1 | Cột hoàn hảo và sản phẩm tương đối |
| = ADDRESS (1,2,1, SAI) | R1C2 | Tham chiếu hoàn hảo theo hình trạng R1C1 |
| = ADDRESS (1,2,4, SAI) | R<1>C<2> | Tham chiếu tương đối theo loại R1C1 |
| = ADDRESS (1,2,1 ,, “Tờ 2”) | Tờ2! $ B $ 1 | Tham chiếu hoàn hảo đến một tờ khác |
| = ADDRESS (1,2,4 ,, “Trang2”) | Tờ2! B1 | Tham chiếu kha khá đến một tờ khác |
Cách sử dụng hàm ADDRESS vào Excel – ví dụ về công thức
Các ví dụ dưới đây cho biết cách áp dụng hàm ADDRESS bên phía 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 quý hiếm ô trong một hàng với cột đang cho
Nếu mục tiêu của khách hàng là lấy một giá trị xuất phát từ 1 ô cụ thể dựa trên số hàng và số cột của nó, hãy sử dụng hàm ADDRESS với GIÁN TIẾP:
INDIRECT (ADDRESS (row_num, cột_num))
Hàm ADDRESS xuất giá trị ô bên 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ề cực hiếm từ ô tương ứng.
Ví dụ: để lấy giá trị ô dựa trên số sản phẩm trong E1 cùng số cột vào E2, hãy thực hiện công thức này:
=INDIRECT(ADDRESS(E1,E2))
Lấy quý hiếm của một ô có mức giá trị cao nhất hoặc rẻ nhất
Trong ví dụ như này, trước tiên bọn họ sẽ search thấy những giá trị tối đa và thấp độc nhất trong phạm vi B2: B7 bằng cách sử dụng những hàm MAX và MIN cùng xuất các giá trị đó vào các ô đặc biệt:
Ô E2: =MAX(B2:B7)
Ô F2: =MIN(B2:B7)
Và sau đó, shop chúng tôi sẽ áp dụng ADDRESS kết hợp với Hàm MATCH để lấy showroom ô.
Ô có mức giá trị về tối đa:
=ADDRESS(MATCH(E2,B:B,0), COLUMN(B2))
Ô có mức giá trị buổi tối thiểu:
=ADDRESS(MATCH(F2,B:B,0), COLUMN(B2))
Trong trường hợp chúng ta không muốn các giá trị tối đa và rẻ nhất trong các ô riêng biệt biệt, chúng ta cũng có thể lồng hàm MAX / MIN vào đối số đầu 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ị rẻ nhất:
=ADDRESS(MATCH(MIN(B2:B7),B:B,0), COLUMN(B2))
Những phương pháp này chuyển động như nạm nàoĐể tìm kiếm số hàng, bạn thực hiện MATCH (lookup_value, lookup_array,
Để tìm kiếm số cột, bạn áp dụng hàm COLUM. Tất nhiên, không tồn tại gì ngăn bạn nhập số thẳng vào công thức, dẫu vậy COLUMN giữ giàng sự cầm cố khi đếm bằng tay thủ công trong trường hòa hợp cột mục tiêu ở 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 ngẫu nhiên số đã mang đến nào thành một vần âm cột, hãy áp dụng hàm ADDRESS bên trong SUBSTITUTE:
SUBSTITUTE (ADDRESS (1,số cột, 4), “1”, “”)
Ví dụ: họ hãy tìm chữ cái cột khớp ứng với số vào A2:
=SUBSTITUTE(ADDRESS(1,A2,4),"1","")
Nhìn vào công dụng dưới đây, bạn cũng có thể nói rằng cột trước tiên trên trang tính là A, điều đó là hiển nhiên; cột sản phẩm 10 là J, 50thứ từ cột là AX cùng 100thứ từ bỏ cột là CV:
Để bắt đầu, hãy cấu hình thiết lập hàm ADDRESS để trả về tham chiếu kha khá cho ô đầu tiên trong cột đích:
Đối với số hàng, áp dụng 1.Đối cùng với số cột, hỗ trợ tham chiếu đến ô cất số, A2 trong lấy ví dụ của chúng tôi.Đối với đối số abs_num, nhập 4.Kết trái là, ADDRESS (1, A2,4) đang trả về A1.
Để thoát ra khỏi tọa độ hàng, hãy bọc bí quyết trên trong Hàm SUBSTITUTE và thay thế “1” bằng một chuỗi trống rỗng (“”). Làm xong!
Cách thực hiện Tìm kiếm mục tiêu 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 Excel
Thanh lỗi trong Excel: tiêu chuẩn và tùy chỉnh
#SPILL! lỗi vào Excel – nó tức là gì và bí quyết khắc phục
Lấy showroom của một phạm vi được đặt tên
Để tìm địa chỉ của một phạm vi được lấy tên trong Excel, trước tiên bạn sẽ cần có được các tham chiếu ô thứ nhất và cuối cùng, kế tiếp nối chúng lại cùng với nhau.
Cách lấy showroom của ô trước tiên trong một phạm viĐể trả về một tham chiếu đến ô trước tiên trong một phạm vi được để tên, hãy áp dụng công thức phổ biến này:
ADDRESS (ROW (phạm vi),CỘT(phạm vi))
Giả sử phạm vi được lấy tên là “Bán hàng”, phương pháp thực tế ra mắt như sau:
=ADDRESS(ROW(Sales), COLUMN(Sales))
Và trả về địa chỉ cửa hàng của ô trên phía trái trong phạm vi:
Trong phương pháp này, các hàm ROW cùng COLUMN trả về một mảng của tất cả các số hàng và số cột vào phạm vi, tương ứng. Dựa vào những con số đó, hàm ADDRESS thiết kế một mảng các add ô. Cơ mà vì cách làm được nhập vào một ô duy nhất, nên có thể có mục đầu tiên của mảng được hiển thị, tương ứng với ô trước tiên trong phạm vi.
Cách lấy quý hiếm của ô cuối cùng trong một phạm viĐể tìm địa chỉ cửa hàng của ô sau cuối trong phạm vi sẽ đặt tên, hãy áp 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 cho 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 mang lại ô dưới cùng bên buộc phải của phạm vi:
Lần này, bọn họ cần một phép tính phức hợp hơn một chút để tính ra số hàng. Y hệt như trong lấy ví dụ trước, hàm ROW hỗ trợ cho chúng ta một mảng gồm tất cả các số sản phẩm trong phạm vi, 4; 5; 6; 7 vào trường hợp của chúng ta. Họ cần “dịch chuyển” những số này bằng tổng số hàng trừ đi 1, để mục thứ nhất trong mảng đổi thay số hàng cuối cùng. Để tìm tổng số hàng, cửa hàng chúng tôi sử dụng hàm ROWS và trừ 1 từ tác dụng của nó: (4-1 = 3). Sau đó, công ty chúng tôi thêm 3 vào mỗi thành phần của mảng lúc đầu để thực hiện di chuyển cần thiết: 4; 5; 6; 7 + 3 = 7; 8; 9; 10.
Số cột được tính theo biện pháp tương tự: 2,3,4 + 3-1 = 4,5,6
Từ các mảng số hàng và số cột nghỉ ngơi trên, hàm ADDRESS tập đúng theo một mảng các showroom ô, cơ mà chỉ trả về mảng đầu tiên tương ứng với ô sau cuối trong phạm vi.
Kết quả tựa như cũng rất có thể đạt được bằng phương pháp chọn các giá trị buổi tối đa từ các mảng của số hàng với số cột. Mặc dù nhiên, điều đó chỉ chuyển động trong một cách làm mảng, yên cầu phải thừa nhận Ctrl + Shift + Enter để được ngừng chính xác:
=ADDRESS(MAX(ROW(Sales)), MAX(COLUMN(Sales)))
Làm nỗ lực nào để có được địa chỉ đầ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ên, bạn chỉ việc nối hai phương pháp từ các ví dụ trước cùng chèn toán tử phạm vi (:) sinh sống giữa.
ADDRESS (ROW (phạm vi), CỘT(phạm vi)) và “:” & ADDRESS (ROW (phạm vi) + ROWS (phạm vi) -1, CỘT (phạm vi) + MÀU SẮC (phạm vi) -1)
Để khiến cho nó hoạt động cho tập tài liệu mẫu của bọn chúng tôi, shop chúng tôi thay nuốm “phạm vi” chung bởi tên phạm vi thực “Bán hàng”:
=ADDRESS(ROW(Sales), COLUMN(Sales)) và ":" & ADDRESS(ROW(Sales) + ROWS(Sales)-1, COLUMN(Sales) + COLUMNS(Sales)-1)
Và lấy showroom phạm vi không thiếu thốn dưới dạng một tuyệt đối xem thêm $ B $ 4: $ D $ 7:
Để trả về showroom phạm vi bên dưới dạng quan hệ tham chiếu (không tất 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à ":" và ADDRESS(ROW(Sales) + ROWS(Sales)-1, COLUMN(Sales) + COLUMNS(Sales)-1, 4)
Đương nhiên, những biến hóa tương tự có thể được tiến hành trong các công thức chưa có người yêu cho ô đầu tiên và ô cuối cùng, và kết quả sẽ trông y như sau:
Đó là cách chúng ta trả về một quý giá ô trong Excel. Để xem kỹ hơn tất cả các công thức được đàm luận trong gợi ý này, rất mong muốn bạn cài đặt xuống Sổ địa chỉ cửa hàng chức năng Excel. Tôi cảm ơn bạn đã hiểu và hi vọng sẽ gặp bạn bên trên emcanbaove.edu.vn của shop chúng tôi vào tuần tới!
Trong nội dung bài viết này, Học Excel Online đã hướng dẫn chúng ta lấy địa chỉ cửa hàng ô tính bởi công thức và bởi mã VBA.
Lấy showroom của ô tính bằng công thức
Sử dụng hàm Address1. Lựa chọn ô tính bạn phải lấy địa chỉ.
2. Xào luộc và dán bí quyết =ADDRESS(ROW(),COLUMN()) vào ô tính cùng nhấn Enter.
Địa chỉ ô tính hiển thị trong ô ngay lập tức.
Lấy địa chỉ ô tính bằng công thức Cell
Chọn ô tính bạn cần lấy địa chỉNhập công thức =CELL(“address”) rồi dìm Enter
Lấy showroom ô tính bởi VBA Code
1. Nhấn giữ phím Alt + F11, cửa sổ Microsoft Visual Basic for Applications mở ra.
2. Trong cửa sổ Microsoft Visual Basic for Applications, nhấn loài chuột hai lần vào thương hiệu của trang tính đang sử dụng trong bảng phía bên trái để mở phần sửa đổi mã, sau đó xào luộc và dán đoạn mã sau:
3. Dìm nút Run nhằm chạy mã.
4. Một vỏ hộp thoại chỉ ra hiển thị địa chỉ ô tính.
Hiển thị showroom ô tính vào một ô nhất định bằng VBA Code
Trong trường phù hợp khác, chúng ta cần địa chỉ ô tính hiển thị vào một ô tính chũm thể. Mỗi khi bạn di chuyển từ ô này lịch sự ô khác, add ô tính sẽ được tự động thay đổi và hiển thị vào một ô tốt nhất định.
1. Thừa nhận giữ phím Alt + F11, cửa sổ Microsoft Visual Basic for Applications mở ra.
2. Trong cửa ngõ sổ Microsoft Visual Basic for Applications, nhấn loài chuột hai lần vào thương hiệu của trang tính đang sử dụng trong bảng phía trái để mở phần chỉnh sửa mã, sau đó xào nấu và dán đoạn mã sau:
Private
Sub
Worksheet_Selection
Change(By
Val
Target As
Range)
Range("A1").Value = Active
Cell.Address
End
Sub
Chú ý. A1 là ô tính bạn muốn hiển thị add ô, chúng ta cũng có thể thay biến đổi ô không giống tùy ý.
3. Nhấp File > Close và Return lớn Microsoft Excel để tạm dừng hoạt động sổ Microsoft Visual Basic for Applications.
Xem thêm: Lỗi không xuống dòng trong excel, cách sửa lỗi không xuống được dòng trong excel
Địa chỉ ô tính bạn cần hiển thị vào ô A1, cùng nó sẽ auto thay thay đổi khi bạn dịch rời sang ô tính khác.
Tác giả: dtnguyen (Nguyễn Đức Thanh)
học tập Excel Online | DTNguyen.business
·· ·
Bài viết không giống của thuộc tác giả
khóa học mới xuất phiên bản
Lập trình tác động Python Excel chìa khóa tối ưu công việc
3.000.000VND 1.800.000VND
PYXL101
Xem chi tiết
khóa huấn luyện và đào tạo Trọn bộ SQL cơ bản qua 500 câu truy vấn dành cho tất cả những người mới ban đầu
3.000.000VND 1.600.000VND
SQL100
Xem đưa ra tiết
Python cơ phiên bản cho fan mới ban đầu
499.000VND 199.000VND
PY100
Xem bỏ ra tiết
Hiểu về kiếm tìm kiếm xê dịch trong Excel – một phương pháp để tối ưu hóa tốc độ
7 ứng dụng bóc tách chuỗi cùng với hàm FILTERXML vào Excel
Chia khoảng tầm thời gian để gia công bài toán phân tích dữ liệu lượt truy tìm cập
Hướng dẫn kích hoạt khoá học tập tại học Excel Online
Tạo dãy số bỗng nhiên không giống nhau trong Excel
Cách gộp nhiều mảng, vùng dữ liệu với hàm VSTACK cùng HSTACK vào Excel
khóa huấn luyện và đào tạo liên quan
Khóa học Excel từ bỏ cơ phiên bản đến chuyên viên dành cho những người đi làm
Chi máu khóa học
Khóa học Trọn bộ Power Query nền tảng cho công việc report dữ liệu
Chi tiết khóa học
Click đk học tại:
Tất cả khoá học