Hướng dẫn how do you reference an address in excel? - làm thế nào để bạn tham khảo một địa chỉ trong excel?

Hướng dẫn đưa ra một giới thiệu ngắn gọn về cú pháp chức năng địa chỉ 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 được một địa chỉ ô Excel từ các số và số cột được cung cấp cho hàm địa chỉ. Hầu 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ể đề cập trực tiếp đến một tế bào.

Chức năng địa chỉ Excel - Syntax và sử dụng cơ bản

Hàm địa chỉ được thiết kế để có được địa chỉ ô trong Excel dựa trên các số hàng và cột được chỉ định. Một địa chỉ ô được trả về dưới dạng chuỗi văn bản, không phải là một tham chiếu thực tế.

Hàm có sẵn trong tất cả các phiên bản của Excel cho Microsoft 365 - Excel 2007.

Cú pháp của hàm địa chỉ 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 ô. - the row number to use in the cell reference.

Cột_num - Số cột để xây dựng tham chiếu ô. - the column number to build the cell reference.

Ba đối số cuối cùng, chỉ đị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ố dưới đây; Mặc định là tuyệt đối. - the reference type, absolute or relative. It can take any of the below numbers; the default is absolute.

• 1 hoặc bỏ qua - tham chiếu ô tuyệt đối như $ a $ 1
• 2 - Tham khảo hỗn hợp: Cột tương đối và hàng tuyệt đối như $ 1
• 3 - Tham khảo 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ị bỏ qua, kiểu A1 mặc định được sử dụng. - the reference style, A1 or R1C1. If omitted, the default A1 style is used.

• 1 hoặc đúng hoặc bị bỏ qua - Trả về địa chỉ ô trong kiểu tham chiếu A1 trong đó các cột là các chữ cái và hàng là số.
• 0 hoặc sai - Trả về địa chỉ ô theo kiểu tham chiếu R1C1 trong đó các hàng và cột được biểu diễn bằng các số.

Sheet_Text - Tên của bảng 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 các dấu ngoặc kép, ví dụ: "Sheet2". 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 bảng hiện tại. - the name of the worksheet to include in the external reference. The sheet name should be supplied as a text string and enclosed in quotation marks, e.g. "Sheet2". If omitted, no worksheet name is used, and the address defaults to the current sheet.

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) như một tham chiếu ô tuyệt đối $ 1 $ 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 vài loại tham chiếu có thể được trả về bằng các công thức địa chỉ.

Cách sử dụng chức năng địa chỉ trong các ví dụ về công thức Excel - Công thức

Các ví dụ dưới đây cho thấy cách sử dụng chức năng địa chỉ bên trong các công thức lớn hơn để hoàn thành các nhiệm vụ khó khăn hơn.

Trả về giá trị ô trong một hàng và cột đã cho

Nếu mục tiêu của bạn là nhận được giá trị từ một ô cụ thể dựa trên các số hàng và cột của nó, hãy sử dụng hàm địa chỉ cùng với gián tiếp:

Gián tiếp (địa chỉ (hàng_num, cột_num)))

Hàm địa chỉ xuất địa chỉ ô dưới dạng văn bản. Hàm gián tiếp 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ụ: để nhận 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))

Nhận đị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 tối đa và tối thiểu và xuất các giá trị đó vào các ô đặc biệt:

Cell E2: =MAX(B2:B7)

Tế bào F2: =MIN(B2:B7)

Và sau đó, chúng tôi sẽ sử dụng địa chỉ kết hợp với chức năng khớp để 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ể tổ chức năng tối đa/min trong đối số đầu tiên của trận đấu. 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))

Cách các công thức này hoạt động

Để tìm số hàng, bạn sử dụng khớp (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 tối đa hoặc tối thiểu 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 cản bạn nhập số trực tiếp vào công thức, nhưng cột tiết kiệm rắc rối của việc đếm thủ công trong trường hợp cột đích nằm ở giữa bảng.

Nhận một chữ cái cột từ số cột

Để biến bất kỳ số đã cho thành một chữ cái cột, hãy sử dụng hàm địa chỉ bên trong thay thế:

SUBSTITUTE(ADDRESS(1,column_number,4),"1","")

Ví dụ, 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, cột thứ 50 là AX và cột thứ 100 là CV:

Cách thức hoạt động của công thức này

Để bắt đầu, hãy thiết lập chức năng địa chỉ để trả về một 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ả lại A1.

Để loại bỏ tọa độ hàng, hãy bọc công thức trên trong hàm thay thế và thay thế "1" bằng một chuỗi trống (""). Xong!

Nhận đị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 di động đầu tiên và cuối cùng, sau đó tham gia cùng nhau. Điều này hoạt động hơi khác nhau trong Excel tiền động (2019 trở lên) và Array Array Excel (Office 365 và Excel 2021). Các ví dụ dưới đây dành cho Excel 2019 - Excel 2007. Các hướng dẫn cho Excel 365 và Excel 2021 ở đây.

Cách lấy địa chỉ của ô đầu tiên trong 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(range),COLUMN(range))

Giả sử phạm vi được đặt tên là "Bán hàng", công thức thực sự như sau:

=ADDRESS(1,1,4)0

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 hàng và cột trả về một mảng của tất cả các số hàng và cột trong phạm vi, tương ứng. Dựa trên những con số đó, hàm địa chỉ xây dựng một mảng địa chỉ ô. Nhưng vì công thức được nhập trong một ô duy nhất, 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.

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 này:

ADDRESS(ROW(range)+ROWS(range)-1,COLUMN(range)+COLUMNS(range)-1)

Áp dụng cho phạm vi của chúng tôi có tên "Bán hàng", công thức có hình dạng sau:

=ADDRESS(1,1,4)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 tính toán 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 hàng cung cấp cho chúng ta một mảng 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 tôi. Chúng ta cần "thay đổi" các số này theo tổng số lượng hàng trừ 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 hàng 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 ca yêu cầu: {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 trên của các số hàng và cột, hàm địa chỉ lắp ráp một mảng các địa chỉ ô, nhưng chỉ trả về cái đầ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 các số hàng và 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 để hoàn thành chính xác:Ctrl + Shift + Enter to be completed correctly:

=ADDRESS(1,1,4)2

Cách lấy địa chỉ đầy đủ của một phạm vi được đặt tên

Để trả về địa chỉ hoàn chỉnh của một phạm vi được đặt tên, bạn chỉ cần kết hợp 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ỉ (hàng (phạm vi), cột (phạm vi)) & ":" & địa chỉ (hàng (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 bộ 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 thực tế "bán hàng":

=ADDRESS(1,1,4)3

Và nhận được địa chỉ phạm vi đầy đủ dưới dạng tham chiếu tuyệt đối $ B $ 4: $ D $ 7:absolute reference $B$4:$D$7:

Để trả về địa chỉ phạm vi dưới dạng tham chiếu tương đối (không có dấu $, như B4: D7), đặt đối số ABS_NUM trong cả hai hàm địa chỉ thành 4:relative reference (without the $ sign, like B4:D7), set the abs_num argument in both ADDRESS functions to 4:

=ADDRESS(1,1,4)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 tương tự như thế này:

Cách lấy địa chỉ của một phạm vi được đặ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 New Excel, bất kỳ công thức nào có khả năng trả về nhiều giá trị, tự động thực hiện điều này. Hành vi như vậy được gọi là tràn.

Ví dụ: thay vì trả về địa chỉ của ô đầu tiên, công thức bên dưới xuất ra địa chỉ của mỗi và mọi ô trong một phạm vi được đặt tên:

=ADDRESS(1,1,4)0

Để chỉ nhận địa chỉ của ô đầu tiên, bạn cần bật giao điểm ngầm, đượ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 ngầm) trước các tên phạm vi:first cell only, you need to enable implicit intersection, which is triggered by default in Excel 2019 and older. For this, put the @ symbol (implicit intersection operator) before the range names:

=ADDRESS(1,1,4)6

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:last cell in the range:

=ADDRESS(1,1,4)7

Để lấy địa chỉ của một phạm vi được đặt tên:address of a named range:

=ADDRESS(1,1,4)8

Ảnh chụp màn hình dưới đây cho thấy kết quả:

Mẹo. Khi mở một bảng tính với các công thức được tạo trong một phiên bản cũ hơn trong Array Array Excel, một toán tử giao nhau ngầm được chèn tự động bởi Excel. When opening a worksheet with formulas created in an older version in dynamic array Excel, an implicit intersection operator is inserted by Excel automatically.

Đó là cách bạn trả lại một địa chỉ ô trong Excel. Để xem xét kỹ hơn tất cả các công thức được thảo luận trong hướng dẫn này, bạn có thể tải xuống sổ làm việc mẫu của chúng tôi dưới đây. 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!

Thực hành sổ làm việc để tải xuống

Chức năng địa chỉ Excel - Ví dụ công thức (tệp .xlsx)

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