Cách sử dụng hàm ADDRESS trong Google Sheets

GiGiKing·Tháng ba 19, 2024·0 comments

Hàm ADDRESS trong Google Sheets có chức năng cơ bản là trả về tham chiếu ô, dựa trên đầu vào của hàng và cột. Nhưng điều khiến nó mạnh mẽ hơn là chúng ta có thể sử dụng các hàm như MATCH, XMATCH, ROW và COLUMN để thay thế các đối số hàng và cột.

Hàm ADDRESS trong Google Sheets: Cú pháp và Đối số

Cú pháp của hàm ADDRESS trong Google Sheets:

ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])

Một số ví dụ về tham chiếu ô (theo cách ghi A1):

  • $A$1, A$1, $A1, và A1

Hàm ADDRESS có thể trả về cả bốn loại tham chiếu ô, đóng vai trò quan trọng trong Định dạng có điều kiện.

Giải thích các đối số:

  • row: Số dòng trong tham chiếu ô. Ví dụ, số 100 trong Z100.
  • column: Số cột trong tham chiếu ô. A là cột số 1. Ví dụ, số 26 trong Z100.
  • absolute_relative_mode: (Tùy chọn) Chỉ số cho biết tham chiếu ô trả về là tuyệt đối hay tương đối:
    • 1 là tuyệt đối cả hàng và cột (ví dụ, $A$1).
    • 2 là tuyệt đối hàng và tương đối cột (ví dụ, A$1).
    • 3 là tương đối hàng và tuyệt đối cột (ví dụ, $A1).
    • 4 là tương đối cả hàng và cột (ví dụ, A1).
  • use_a1_notation: Một giá trị boolean cho biết có sử dụng cách ghi chú thích A1 (TRUE) hay cách ghi chú thích R1C1 (FALSE).
  • sheet: (Tùy chọn) Để bao gồm tên sheet (mặc định là không bao gồm).

Các ví dụ về công thức của hàm ADDRESS (kèm giải thích)

Công thức sau sẽ trả về $Z$10 vì đối số hàng là 10 và đối số cột là 26:

=ADDRESS(10,26)

Trong kết quả này, cả tham chiếu hàng và cột đều là tuyệt đối. Để biến chúng thành tương đối, chúng ta có thể sử dụng đối số thứ ba, tức là absolute_relative_mode:

=ADDRESS(10,26,1) // trả về $Z$10
=ADDRESS(10,26,2) // trả về Z$10
=ADDRESS(10,26,3) // trả về $Z10
=ADDRESS(10,26,4) // trả về Z10

Làm thế nào để chỉ định tên sheet trong công thức ADDRESS?

Hãy sử dụng đối số thứ năm, sheet. Dưới đây là một ví dụ:

=ADDRESS(10,26,4,,"form_response") // trả về 'form_response!'!Z10

Như bạn có thể thấy, chúng tôi chưa sử dụng đối số thứ tư, tức là use_a1_notation. Đó là lý do tại sao chúng tôi để lại một dấu phẩy ngay sau đối số thứ ba trong công thức.

Công thức ADDRESS sau đây trả về địa chỉ ô theo kiểu tham chiếu R1C1:

ADDRESS(10,1,1,FALSE) // trả về R10C1

Bạn có thể sử dụng hàm INDIRECT với hàm ADDRESS để trả về giá trị ô của tham chiếu ô được trả về bởi hàm ADDRESS.

Trong tất cả các công thức, trừ công thức cuối cùng trả về tham chiếu ô kiểu R1C1, bạn chỉ cần bọc công thức bằng INDIRECT.

Ví dụ:

=INDIRECT(ADDRESS(10,1))

Công thức này sẽ trả về giá trị ô A10.

Khi sử dụng ghi chú R1C1, sử dụng công thức như sau:

=INDIRECT(ADDRESS(10,1,1,FALSE),FALSE)

Cách sử dụng đối số hàng và cột động trong hàm ADDRESS

Để tìm địa chỉ ô của lần xuất hiện đầu tiên hoặc cuối cùng của một giá trị trong một phạm vi, bạn có thể sử dụng hàm XMATCH trong hàm ADDRESS. Hãy xem cách sử dụng nó như một đối số hàng hoặc cột động trong hàm ADDRESS trong Google Sheets.

Sử dụng hàm XMATCH để tạo đối số hàng động

Ví dụ, giả sử cột B chứa tên các loại trái cây. Công thức XMATCH sau sẽ trả về số dòng của lần xuất hiện đầu tiên của “Chuối” trong cột đó:

XMATCH("Chuối",B:B)

Chúng ta sau đó có thể sử dụng công thức XMATCH này để thay thế đối số hàng trong hàm ADDRESS:

=ADDRESS(XMATCH("Chuối",B:B),2)

Trong đó:

  • hàng là XMATCH(“Chuối”, B:B)
  • cột là 2

Công thức này sẽ trả về $B$5, theo dữ liệu mẫu trong ảnh chụp màn hình.

Để có được địa chỉ ô của giá trị cuối cùng khớp, hãy sử dụng công thức ADDRESS và XMATCH sau.

=ADDRESS(XMATCH("Chuối",B:B,0,-1),2) // trả về $B$7

Sử dụng hàm XMATCH để tạo đối số cột động

Nếu dữ liệu của bạn được sắp xếp theo hàng, hãy thay thế đối số cột trong hàm ADDRESS bằng hàm XMATCH.

Công thức sau sẽ trả về địa chỉ ô của lần xuất hiện đầu tiên của trái cây “Chuối” trong hàng #2:

=ADDRESS(2,XMATCH("Chuối",2:2)) // trả về $F$2

Để có được địa chỉ ô của lần xuất hiện cuối cùng của giá trị “Chuối” trong hàng đó, hãy sử dụng công thức sau:

=ADDRESS(2,XMATCH("Chuối",2:2,0,-1)) // trả về $H$2

Sử dụng hàm XMATCH để tạo đối số hàng và cột động trong hàm ADDRESS

Đôi khi, chúng ta có thể muốn sử dụng đối số hàng và cột động trong hàm ADDRESS để lấy địa chỉ của giao điểm của hai giá trị khớp. Để làm điều này, chúng ta có thể sử dụng hai công thức XMATCH: một để thay thế đối số hàng và một để thay thế đối số cột.

Công thức sau sẽ khớp giá trị “C” trong cột A và “Q3” trong hàng #1 và trả về địa chỉ ô giao điểm:

=ADDRESS(XMATCH("C",A:A),XMATCH("Q3",1:1))

Công thức này có thể trả về giá trị 11 bằng cách bọc nó bằng hàm INDIRECT.

=INDIRECT(ADDRESS(XMATCH("C",A:A),XMATCH("Q3",1:1))) // trả về 11

Lưu ý: Bạn có thể thay XMATCH bằng MATCH trong hàm ADDRESS, nhưng điều này sẽ không hỗ trợ khớp giá trị cuối cùng.

Cách sử dụng công thức mảng của hàm ADDRESS trong Google Sheets

Chúng ta đã thấy một số sử dụng nâng cao của hàm ADDRESS ở trên. Dưới đây là một số ví dụ nữa:

Chúng ta có thể sử dụng hàm ADDRESS để khớp một giá trị trong nhiều ô trong một hàng, cột hoặc phạm vi và trả về địa chỉ ô của tất cả các khớp.

Ví dụ, công thức mảng sau sẽ trả về địa chỉ ô của tất cả các ô trong cột A chứa giá trị “Táo”:

=ARRAYFORMULA( TOCOL( IF( A:A="Táo", ADDRESS(ROW(A1:A),COLUMN(A1:A)), ),1 ) )

Hàm ADDRESS trả về địa chỉ ô dựa trên số dòng và số cột của ô. Hàm IF kiểm tra từng ô trong phạm vi A:A để xem nó có chứa giá trị “Táo” hay không. Nếu có, hàm IF trả về địa chỉ ô của ô đó. Nếu không, hàm IF trả về một chuỗi rỗng (“”).

Hàm TOCOL chỉ dùng để loại bỏ các ô trống trong kết quả.

Khi bạn sử dụng công thức này trong một hàng, hãy thay hàm TOCOL bằng hàm TOROW. Công thức sau đây dành cho hàng #1:

=ARRAYFORMULA( TOROW( IF( 1:1="Táo", ADDRESS(ROW(1:1),COLUMN(1:1)), ),1 ) )

Để sử dụng hàm ADDRESS trong một phạm vi (mảng 2D) để trả về nhiều địa chỉ ô, bạn có thể sử dụng công thức mảng sau:

=ARRAYFORMULA(IF(B2:E5="",,ADDRESS(ROW(B2:E5),COLUMN(B2:E5))))

Công thức này sẽ trả về địa chỉ ô của tất cả các ô không rỗng trong phạm vi B2:E5.

参考网址是Crawlan.com

GiGiKing

Related posts