Hàm ADDRESS trong Excel – Cách Sử Dụng

Hàm ADDRESS trả về một chuỗi văn bản xác định vị trí của ô trong bảng tính ở dạng tương đối, hỗn hợp hoặc tuyệt đối. Kết quả trả về có thể ở dạng A1 hoặc R1C1.

Cú pháp

Cú pháp của hàm ADDRESS như sau:

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

Hàm ADDRESS nhận năm đối số, trong đó hai đối số là bắt buộc và ba còn lại là tùy chọn. Chi tiết của mỗi đối số được mô tả dưới đây để bạn hiểu rõ hơn.

Đối số:

• row_num – Đối số bắt buộc, chỉ số hàng của ô mà chúng ta muốn tạo tham chiếu. Giá trị có thể là số trực tiếp hoặc là ô chứa số. Số hàng phải là số nguyên dương.
• column_num – Đối số bắt buộc, chỉ số cột của ô mà chúng ta muốn tạo tham chiếu. Giá trị cũng phải là số nguyên dương và có thể là số trực tiếp hoặc ô chứa số.
• abs_num – Đối số tùy chọn, xác định kiểu tham chiếu cần tạo. Giá trị từ 1‑4 cho phép trả về tham chiếu tuyệt đối, hỗn hợp hoặc tương đối. Mặc định là 1 (tham chiếu tuyệt đối `$A$1`). Nếu đặt 2, sẽ nhận được tham chiếu hỗn hợp với hàng tuyệt đối và cột tương đối (`A$1`). Nếu là 3, sẽ là hỗn hợp với hàng tương đối và cột tuyệt đối (`$A1`). Nếu là 4, sẽ nhận được tham chiếu hoàn toàn tương đối (`A1`).
• a1 – Đối số tùy chọn, xác định kiểu tham chiếu (A1 hay R1C1). Đây là đối số kiểu Boolean; mặc định là 1 hoặc TRUE tương ứng với kiểu A1 (ví dụ: A1, B2, C3). Nếu đặt 0 hoặc FALSE, kết quả sẽ ở dạng R1C1 (ví dụ: R1C1, R2C3, R3C4) trong đó R là hàng, C là cột.
• sheet_text – Đối số cuối cùng, tùy chọn, chứa tên trang tính sẽ được dùng làm tham chiếu. Mặc định là trang tính hiện tại. Nếu muốn tham chiếu tới một trang tính bên ngoài, giá trị của đối số này phải là tên trang tính được đặt trong dấu ngoặc kép.

Các đặc điểm quan trọng của hàm ADDRESS

• Kết quả của hàm ADDRESS luôn là một chuỗi văn bản.
• Nếu giá trị của row_num hoặc column_num bằng 0 hoặc âm, hàm ADDRESS sẽ trả về lỗi #VALUE!.
• Khi giá trị của abs_num không nằm trong khoảng 1‑4, hàm sẽ trả về lỗi #VALUE!.

Ví dụ về hàm ADDRESS

Hàm ADDRESS là công cụ mạnh mẽ khi làm việc với nhiều trang tính và bộ dữ liệu lớn. Dưới đây là một số ví dụ để bạn khám phá các ứng dụng của hàm này.

Ví dụ 1 – Sử dụng đơn giản hàm ADDRESS

Trong ví dụ này, chúng ta sẽ khám phá các đối số của hàm ADDRESS và cách sử dụng chúng bằng các giá trị đầu vào khác nhau. Điều này giúp bạn nắm vững cách khai thác từng đối số để đạt được kết quả mong muốn.

Mỗi cột trong ví dụ hiển thị các giá trị đầu vào riêng cho từng đối số, cho thấy rõ các tùy chọn tùy biến của hàm ADDRESS. Để minh họa vai trò của các đối số tùy chọn, chúng tôi đã đặt số hàng và số cột đều là 2 (tương ứng với ô B2) cho tất cả các ví dụ. Hãy xem chúng ta có thể nhận được những kiểu tham chiếu ô nào cho B2 khi bật hoặc tắt các đối số tùy chọn.

• Ví dụ đầu tiên: chỉ dùng hai đối số bắt buộc (row_num, column_num). Kết quả là một tham chiếu tuyệt đối kiểu A1: “$B$2”.
• Hai ví dụ tiếp theo: đặt abs_num lần lượt là 2 và 3, tạo ra các tham chiếu hỗn hợp (B$2 và $B2).
• Ví dụ thứ tư: đặt abs_num = 4, nhận được tham chiếu hoàn toàn tương đối “B2”.
• Hai ví dụ tiếp theo minh họa đối số a1. Khi a1 = 0 và abs_num = 1, hàm trả về tham chiếu tuyệt đối dạng R1C1 (“R2C2”). Khi abs_num = 4, kết quả là tham chiếu tương đối dạng R1C1 (“R[2]C[2]”).
• Ví dụ cuối cùng tạo một tham chiếu tuyệt đối kiểu A1 tới ô B2, đồng thời thêm chuỗi văn bản “ADDRESS Function” làm tên trang tính.

Sau khi nắm vững chức năng cơ bản của hàm ADDRESS, chúng ta sẽ tiếp tục khám phá các ví dụ kết hợp với các hàm khác như INDIRECT và MATCH để tăng tính linh hoạt và hiệu quả trong các tình huống thực tế.

Ví dụ 2 – Tìm địa chỉ ô đầu tiên trong một vùng dữ liệu

Khi làm việc với dữ liệu lớn, việc nhanh chóng xác định địa chỉ ô đầu và ô cuối của một phạm vi là một thủ thuật hữu ích. Hàm ADDRESS có thể được dùng để lấy địa chỉ ô đầu và ô cuối. Để minh họa, chúng tôi chuẩn bị một bảng dữ liệu mẫu chứa các quốc gia, thủ đô và tiền tệ của chúng.

Để lấy địa chỉ ô mong muốn, chúng ta cần cung cấp giá trị cho hai đối số đầu tiên của hàm ADDRESS là row_num và column_num. Có thể sử dụng kết hợp các hàm ROW, COLUMN và MIN để lấy các giá trị này.

• Lấy tất cả các số hàng của phạm vi:

=ROW(B4:D18)

• Dùng hàm MIN để tìm số hàng nhỏ nhất (hàng đầu):

=MIN(ROW(B4:D18))

• Tương tự, lấy số cột và tìm cột nhỏ nhất:

=COLUMN(B4:D18)

=MIN(COLUMN(B4:D18))

• Cuối cùng, ghép lại thành công thức hoàn chỉnh:

=ADDRESS(MIN(ROW(B4:D18)),MIN(COLUMN(B4:D18)))

Đó là cách bạn lấy địa chỉ của ô đầu tiên trong một phạm vi bằng hàm ADDRESS.

Ví dụ 2.1 – Tìm địa chỉ ô cuối cùng trong một vùng dữ liệu

Sử dụng cùng một logic, chúng ta có thể xác định địa chỉ ô cuối cùng bằng cách thay MIN bằng MAX:

=ADDRESS(MAX(ROW(B4:D18)),MAX(COLUMN(B4:D18)))

Nếu muốn kết quả ở dạng R1C1, chỉ cần đặt a1 = 0:

=ADDRESS(MAX(ROW(B4:D18)),MAX(COLUMN(B4:D18)),1,0)

Khi làm việc với dữ liệu lớn, định dạng tham chiếu R1C1 thường dễ hiểu hơn. Hãy thực hành tìm địa chỉ ô đầu và ô cuối trên một cơ sở dữ liệu lớn hơn.

Ví dụ 3 – Lấy giá trị ô dựa trên hàng và cột cho trước

Trong ví dụ này, chúng ta có một bộ dữ liệu gồm các tên màu ngẫu nhiên. Người dùng nhập số hàng và số cột, sau đó trích xuất giá trị tại ô tương ứng. Điều này hữu ích khi muốn lấy ngẫu nhiên một giá trị từ bộ dữ liệu lớn.

Người dùng nhập số hàng vào ô G1 và số cột vào ô G2. Khi kết hợp hàm ADDRESS với INDIRECT, chúng ta có thể lấy giá trị của ô mong muốn. Hàm INDIRECT sẽ chuyển chuỗi văn bản do ADDRESS trả về thành tham chiếu thực tế và trả về giá trị của ô đó.

=INDIRECT(ADDRESS(G1,G2))

Đây là một cách hiệu quả để tạo các tham chiếu động và trích xuất dữ liệu từ các bộ dữ liệu lớn. Kết quả tương tự cũng có thể đạt được bằng cách dùng INDEX và MATCH.

Ví dụ 4 – Trích xuất địa chỉ ô có giá trị cao nhất

Trong kịch bản này, chúng ta có dữ liệu doanh số bán hàng hàng tháng của từng nhân viên. Thông thường, dữ liệu lớn khiến việc xác định ô chứa giá trị mong muốn trở nên khó khăn. Chúng ta sẽ tìm doanh số cao nhất trong mỗi tháng và trả về địa chỉ ô chính xác.

• Tìm giá trị cao nhất trong cột C (tháng 1):

=MAX(C3:C12)

• Dùng MATCH để xác định vị trí (hàng) của giá trị cao nhất:

=MATCH(MAX(C3:C12),C:C,0)

Giả sử giá trị cao nhất nằm ở hàng 11, chúng ta có thể dùng ADDRESS để tạo địa chỉ ô:

=ADDRESS(MATCH(MAX(C3:C12),C:C,0),3)

Nếu muốn lấy tên nhân viên (cột B) tương ứng, chỉ cần thay column_num = 2 và đóng gói bằng INDIRECT:

=INDIRECT(ADDRESS(MATCH(MAX(C3:C12),C:C,0),2))

Cùng nguyên tắc, bạn cũng có thể tìm địa chỉ ô có giá trị thấp nhất trong một bộ dữ liệu.

Khám phá thêm nhiều cách hữu ích để sử dụng hàm ADDRESS trong Excel, khi chúng tôi tiếp tục giới thiệu những hàm Excel thú vị khác cho bạn.