Hàm ADDRESS Trong Excel: Hướng Dẫn Chi Tiết Có Ví Dụ Minh Họa

Hàm ADDRESS nằm trong nhóm tra cứu và tham chiếu, nhưng ít người sử dụng so với VLOOKUP hay INDEX. Tôi bỏ qua hàm này trong 2 năm đầu dùng Excel, nghĩ nó chỉ phục vụ các công thức quá phức tạp. Hóa ra ADDRESS giải quyết nhiều vấn đề hàng ngày mà tôi vẫn làm thủ công.

Mục Lục

Cú pháp cơ bản của hàm ADDRESS

Hàm ADDRESS trả về địa chỉ của một ô dưới dạng văn bản, dựa trên số hàng và số cột bạn chỉ định. Cú pháp đầy đủ gồm 5 tham số, trong đó chỉ 2 tham số đầu là bắt buộc.

Công thức:

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

Các tham số:

row_num (bắt buộc): Số thứ tự hàng, ví dụ 5 nghĩa là hàng thứ 5
column_num (bắt buộc): Số thứ tự cột, ví dụ 3 nghĩa là cột C
abs_num (tùy chọn): Kiểu tham chiếu trả về (1, 2, 3, hoặc 4)
a1 (tùy chọn): TRUE cho kiểu A1, FALSE cho kiểu R1C1
sheet_text (tùy chọn): Tên trang tính cần tham chiếu

Ví dụ đơn giản: =ADDRESS(2,3) sẽ trả về $C$2. Hàng thứ 2, cột thứ 3 trong Excel là ô C2, và mặc định hàm trả về dạng tuyệt đối với ký hiệu đô la.

Bốn kiểu tham chiếu với tham số abs_num

Tham số abs_num kiểm soát cách ADDRESS trả về địa chỉ ô. Đây là điểm mạnh của hàm vì cho phép bạn tạo tham chiếu linh hoạt phù hợp với từng tình huống.

Giá trị 1 – Tuyệt đối hoàn toàn: =ADDRESS(2,3,1) trả về $C$2. Cả hàng và cột đều cố định. Khi sao chép công thức sang ô khác, tham chiếu không thay đổi.

Giá trị 2 – Hàng tuyệt đối, cột tương đối: =ADDRESS(2,3,2) trả về C$2. Hàng 2 cố định, cột C có thể thay đổi khi kéo ngang công thức.

Giá trị 3 – Hàng tương đối, cột tuyệt đối: =ADDRESS(2,3,3) trả về $C2. Cột C cố định, hàng 2 thay đổi khi kéo dọc công thức.

XEM THÊM: Cách Sử Dụng Hàm EXPAND Để Điền Dữ Liệu Thiếu Trong Excel

Giá trị 4 – Tương đối hoàn toàn: =ADDRESS(2,3,4) trả về C2. Cả hàng và cột đều thay đổi theo vị trí mới khi sao chép công thức.

Nếu bạn bỏ qua tham số này, Excel mặc định dùng giá trị 1 (tuyệt đối hoàn toàn). Tôi thường dùng giá trị 4 khi cần công thức động điều chỉnh theo dữ liệu mới.

Chuyển đổi giữa kiểu A1 và R1C1

Excel hỗ trợ hai hệ thống đặt tên ô: A1 (phổ biến) và R1C1 (ít dùng hơn). Kiểu A1 dùng chữ cái cho cột và số cho hàng. Kiểu R1C1 dùng số cho cả hai.

Tham số thứ tư trong ADDRESS quyết định kiểu trả về:

=ADDRESS(2,3,,TRUE) hoặc bỏ trống → $C$2 (kiểu A1)
=ADDRESS(2,3,,FALSE) → R2C3 (kiểu R1C1)

Kiểu R1C1 hữu ích khi làm việc với bảng lớn có hàng trăm cột. Nhớ cột thứ 300 dễ hơn nhớ cột KN. Để bật kiểu R1C1 cho toàn bộ Excel, vào File > Options > Formulas > chọn “R1C1 reference style”.

Tôi chỉ dùng R1C1 khi viết macro VBA hoặc xử lý dữ liệu có cấu trúc cột thay đổi liên tục. Với công việc thông thường, kiểu A1 trực quan hơn.

Tham chiếu sang trang tính khác

Tham số cuối cùng là sheet_text, cho phép ADDRESS trỏ đến ô ở trang tính khác. Giá trị này phải là văn bản nằm trong dấu ngoặc kép.

Ví dụ:

=ADDRESS(5,2,1,TRUE,"Dữ liệu")

Kết quả: Dữ liệu!$B$5

Nếu tên trang tính có khoảng trắng hoặc ký tự đặc biệt, Excel tự động thêm dấu ngoặc đơn: 'Báo cáo tháng 10'!$B$5. Tính năng này tiết kiệm thời gian khi cần tạo tham chiếu động tới nhiều sheet khác nhau trong cùng workbook.

Kết hợp ADDRESS với INDIRECT để lấy giá trị ô

Một mình hàm ADDRESS chỉ trả về địa chỉ dưới dạng văn bản, không phải giá trị trong ô đó. Đây là lý do nhiều người thấy ADDRESS vô dụng. Bí quyết là kết hợp với hàm INDIRECT.

INDIRECT chuyển địa chỉ văn bản thành tham chiếu thực, rồi lấy giá trị từ ô đó.

Công thức:

=INDIRECT(ADDRESS(row_num, column_num))

Ví dụ thực tế: Giả sử bạn có bảng dữ liệu với tên trái cây từ B2 đến B10. Bạn muốn lấy giá trị tại hàng 4, cột 2:

=INDIRECT(ADDRESS(4,2))

Kết quả sẽ là giá trị trong ô B4, ví dụ “Cam”. Thay vì nhập cứng B4, công thức này cho phép bạn thay đổi số hàng và cột linh hoạt.

XEM THÊM: Tôi Tăng Tốc Tra Cứu Excel Gấp 30 Lần Sau Khi Bỏ VLOOKUP Chuyển Sang INDEX

Tôi dùng kỹ thuật này để tạo dashboard động. Khi người dùng chọn tháng trong dropdown (tháng 3 = hàng 5), công thức tự động lấy dữ liệu từ hàng tương ứng mà không cần viết lại VLOOKUP cho từng tháng.

Tìm địa chỉ ô có giá trị lớn nhất hoặc nhỏ nhất

ADDRESS kết hợp với MATCH, MIN và MAX tạo thành công cụ mạnh để định vị giá trị đặc biệt trong dữ liệu lớn.

Bước 1: Tìm giá trị lớn nhất bằng hàm MAX:

=MAX(B2:B50)

Bước 2: Tìm vị trí của giá trị đó trong dãy:

=MATCH(MAX(B2:B50), B2:B50, 0)

Bước 3: Chuyển vị trí thành địa chỉ ô:

=ADDRESS(MATCH(MAX(B2:B50), B2:B50, 0)+1, 2)

Lưu ý cộng thêm 1 vào MATCH vì dãy bắt đầu từ B2 (hàng 2), không phải B1. Số 2 trong ADDRESS là cột B.

Kết quả có thể là $B$23 nếu giá trị lớn nhất nằm ở hàng 23. Với INDIRECT bọc ngoài, bạn lấy được chính giá trị đó thay vì chỉ biết địa chỉ.

Công thức này giúp tôi highlight ngay ô chứa doanh số cao nhất trong báo cáo tuần, thay vì phải lướt qua hàng trăm dòng để tìm thủ công.

Chuyển số cột thành chữ cái với SUBSTITUTE

Một ứng dụng khác của ADDRESS là chuyển đổi số thứ tự cột thành chữ cái tương ứng. Kỹ thuật này cần thêm hàm SUBSTITUTE để loại bỏ số hàng và ký hiệu đô la.

Công thức:

=SUBSTITUTE(SUBSTITUTE(ADDRESS(1,A1,4),"1",""),"$","")

Giải thích:

ADDRESS(1,A1,4) tạo địa chỉ ô tại hàng 1, cột được chỉ định bởi giá trị trong A1, kiểu tương đối
SUBSTITUTE đầu tiên xóa ký tự “1” (số hàng)
SUBSTITUTE thứ hai xóa “$” nếu có

Ví dụ: Nếu A1 chứa số 26, kết quả là “Z”. Nếu A1 là 100, kết quả là “CV”.

Tính năng này hữu ích khi làm việc với dữ liệu import từ database, nơi cột thường được đánh số thay vì chữ cái. Thay vì đếm thủ công cột thứ 50 là cột gì, công thức trả về “AX” ngay lập tức.

Hạn chế và lưu ý khi dùng hàm ADDRESS

ADDRESS có một số điểm cần chú ý để tránh lỗi trong công thức phức tạp.

Kết quả là văn bản, không phải tham chiếu: Hàm trả về chuỗi ký tự như “$C$2”, không phải tham chiếu thực đến ô C2. Bạn phải dùng INDIRECT để chuyển đổi nếu muốn lấy giá trị.

XEM THÊM: Tôi Xử Lý Bảng 500 Cột Trong 3 Giây Nhờ Hàm CHOOSECOLS Excel 2024

Không kiểm tra tính hợp lệ: ADDRESS(10000, 500) sẽ trả về $SN$10000 ngay cả khi bảng tính không có dữ liệu ở vị trí đó. Excel không cảnh báo lỗi cho đến khi bạn thử truy cập ô bằng INDIRECT.

Tính toán chậm với dữ liệu lớn: Khi kết hợp ADDRESS với INDIRECT trong công thức array, thời gian tính toán tăng đáng kể. Với bảng trên 10.000 hàng, nên dùng INDEX/MATCH thay vì ADDRESS/INDIRECT để tốc độ nhanh hơn.

Yêu cầu Excel 2007 trở lên: Các phiên bản Excel cũ hơn (2003 trở về trước) có giới hạn 256 cột, tối đa trả về cột IV. ADDRESS vẫn hoạt động nhưng column_num không được vượt quá 256.

Tôi từng gặp lỗi #REF! khi dùng ADDRESS kết hợp INDIRECT trên file Excel có protected sheet. Hàm cần quyền truy cập đầy đủ để tham chiếu động, vì vậy hãy kiểm tra protection settings nếu công thức không hoạt động.

Khi nào nên dùng ADDRESS thay vì các hàm khác

ADDRESS không phải lựa chọn đầu tiên cho mọi tình huống. Hiểu khi nào nên dùng sẽ giúp bạn tối ưu công thức.

Dùng ADDRESS khi:

Cần tạo tham chiếu động dựa trên input của người dùng
Xây dựng công thức tự điều chỉnh theo cấu trúc dữ liệu thay đổi
Viết macro VBA cần chuyển đổi giữa số và địa chỉ ô
Tạo dashboard với nhiều trang tính, cần linh hoạt chọn nguồn dữ liệu

Không nên dùng ADDRESS khi:

VLOOKUP hoặc XLOOKUP đơn giản đủ cho lookup thông thường
INDEX/MATCH nhanh hơn và dễ debug hơn
Chỉ cần tham chiếu tĩnh đến ô cố định
Làm việc với người khác không quen cú pháp phức tạp

Với kinh nghiệm 5 năm dùng Excel, tôi ước mình biết ADDRESS sớm hơn. Nó không thay thế VLOOKUP hay INDEX/MATCH, nhưng giải quyết những bài toán mà các hàm đó làm không được hoặc làm rất cồng kềnh.

Tương thích và phiên bản Excel

Hàm ADDRESS có mặt trong tất cả phiên bản Excel từ 2007 trở đi, bao gồm Excel 365, Excel 2021, 2019, 2016, 2013, và 2010. Cú pháp hoàn toàn giống nhau trên các phiên bản này.

Đối với Excel for Mac, hàm hoạt động tương tự Excel for Windows không có khác biệt. Phím tắt F4 để chuyển đổi kiểu tham chiếu (tuyệt đối/tương đối) có trên cả hai nền tảng.

Excel Online (web version) cũng hỗ trợ ADDRESS đầy đủ. Bạn có thể mở file chứa công thức này trên bất kỳ thiết bị nào có trình duyệt mà không lo lỗi tính toán.

Nếu bạn làm việc với Google Sheets, ADDRESS cũng tồn tại với cú pháp giống hệt Excel, điều này giúp chuyển đổi file giữa hai nền tảng dễ dàng hơn. Chỉ lưu ý rằng Google Sheets không có hàm INDIRECT trong array formula như Excel 365.