Khi đưa nhiều địa chỉ vào hàm INDIRECT, bạn dễ nhận về toàn lỗi #VALUE! và nghĩ rằng hàm này không làm được. Thực ra, INDIRECT với mảng trong Excel vẫn tạo được nhiều tham chiếu, và bạn chỉ cần một hàm khác đọc giúp kết quả.
Bài viết này dành cho người đã biết hàm INDIRECT cơ bản. Nếu mới bắt đầu, bạn nên đọc bài hàm INDIRECT trong Excel trước. Bài không kèm file vì bạn chỉ cần tự gõ bảng. Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh, và màu tiêu đề trong ảnh chỉ để dễ nhìn.
Bạn cần gõ bảng này trước khi làm theo
Bạn gõ bảng dưới đây vào một sheet trống, bắt đầu từ ô A1:
Khu vực | Doanh số |
|---|---|
Bắc | 120 |
Trung | 80 |
Nam | 200 |

Khi sang mục mới, bạn xóa kết quả cũ ở cột D rồi gõ công thức mới vào D2. Ô D1 trong ảnh chỉ là tiêu đề cho dễ đọc.
Nếu Excel báo lỗi khi bạn gõ dấu phẩy trong công thức, máy của bạn đang dùng dấu chấm phẩy. Khi đó, bạn đổi dấu phẩy ngăn cách các tham số thành dấu chấm phẩy. Trong dấu ngoặc nhọn, ký hiệu ngăn dòng và ngăn cột cũng có thể khác theo cài đặt vùng của Windows.
Vì sao INDIRECT với mảng lại hiện lỗi #VALUE!?
Với một địa chỉ, hàm INDIRECT hoạt động bình thường. Bạn gõ công thức sau vào D2:
=INDIRECT("B2")
Ô D2 trả về 120.

Khi đưa vào 2 địa chỉ, bạn gõ công thức sau vào D2:
=INDIRECT({"B2";"B3"})
Hai ô kết quả đều hiện #VALUE!.

Theo cách tôi hiểu, hàm INDIRECT trả về tham chiếu chứ không trả về giá trị. Khi có nhiều tham chiếu rời nhau, Excel không tự đổi chúng thành giá trị để hiện ra ô. Microsoft không công bố chi tiết bên trong, nên đây chỉ là cách giải thích khớp với các kết quả thử nghiệm. Các cách dưới đây cho thấy các tham chiếu đó vẫn tồn tại.
Cách 1: Hàm CELL đọc địa chỉ của từng tham chiếu
Hàm CELL đọc được thông tin của tham chiếu. Bạn gõ công thức sau vào D2:
=CELL("address",INDIRECT({"B2";"B3"}))
Hai ô trả về $B$2 và $B$3.

Các hàm ROW và COLUMN cũng đọc được. Ví dụ, =ROW(INDIRECT({"B2";"B3"})) trả về 2 và 3.
Cách 2: Hàm N đọc giá trị số của từng tham chiếu
Hàm N lấy giá trị số từ tham chiếu. Bạn gõ công thức sau vào D2:
=N(INDIRECT({"B2";"B3"}))
Hai ô trả về 120 và 80.

Nếu ô chứa chữ, bạn dùng hàm T thay cho hàm N. Ví dụ, =T(INDIRECT({"A2";"A3"})) trả về Bắc và Trung.
Bạn cần nhớ rằng hàm N chỉ đọc ô đầu của mỗi vùng. Công thức =N(INDIRECT({"B2:B3";"B3:B4"})) trả về 120 và 80, không phải tổng của từng vùng.
Cách 3: Hàm SUBTOTAL cộng từng vùng riêng
Hàm SUBTOTAL nhận được nhiều tham chiếu và tính riêng cho mỗi tham chiếu. Tham số 9 nghĩa là tính tổng. Bạn gõ công thức sau vào D2:
=SUBTOTAL(9,INDIRECT({"B2:B3";"B3:B4"}))
Hai ô trả về 200 và 280. Đây là tổng của vùng B2:B3 và vùng B3:B4. Hàm SUBTOTAL bỏ qua các dòng đang bị lọc, nên kết quả sẽ giảm khi sheet nguồn đang bật bộ lọc.

Bạn có thể cộng dữ liệu qua nhiều sheet bằng cách này
Cách 3 hữu ích nhất khi cộng cùng một vùng ở nhiều sheet. Bạn tạo thêm 3 sheet tên Thang1, Thang2, Thang3, rồi gõ số vào ô A2 và A3 của mỗi sheet như sau:
Sheet | Ô A2 | Ô A3 |
|---|---|---|
Thang1 | 100 | 110 |
Thang2 | 150 | 160 |
Thang3 | 90 | 100 |
Sau đó, bạn quay lại sheet chứa bảng khu vực và gõ công thức sau vào D2:
=SUM(SUBTOTAL(9,INDIRECT({"Thang1";"Thang2";"Thang3"}&"!A2:A3")))
Ô D2 trả về 710, đúng bằng tổng của cả 3 sheet.

Nếu các sheet nằm liền nhau, công thức =SUM(Thang1:Thang3!A2:A3) ngắn hơn và cũng trả về 710. Công thức này không bỏ qua dòng bị lọc, nên 2 cách có thể khác nhau khi có bộ lọc. Cách dùng INDIRECT phù hợp hơn khi tên sheet nằm trong một danh sách có thể đổi, vì bạn có thể thay mảng trong ngoặc nhọn bằng một vùng chứa tên sheet.
Bạn thường gặp những lỗi nào khi dùng INDIRECT với mảng?
Toàn bộ kết quả hiện #VALUE!: Bạn đang trả tham chiếu thẳng ra ô. Bạn cần bọc thêm N, T hoặc SUBTOTAL.
Công thức báo lỗi #REF!: Tên sheet hoặc địa chỉ có thể bị sai, hoặc tên sheet có dấu cách nhưng thiếu dấu nháy đơn. Ví dụ,
=INDIRECT("Khong co!A1")báo #REF!.Tên sheet có dấu cách: Bạn cần đặt tên sheet trong dấu nháy đơn. Khi ghép với mảng, bạn viết
"'"&{"Tháng 4";"Tháng 5"}&"'!A2:A3".Tổng bị thiếu mà không báo lỗi: Hàm SUM hoặc N chỉ đọc ô đầu của mỗi vùng khi nhận mảng tham chiếu. Bạn cần dùng SUBTOTAL với vùng nhiều ô.
Người đọc thường hỏi gì về INDIRECT với mảng trong Excel?
Công thức SUM(INDIRECT(...)) có chạy trực tiếp được không?
Công thức =SUM(INDIRECT({"B2";"B3"})) trả về 200 trên Excel 365 khi nhấn Enter, nhưng chỉ đúng khi mỗi địa chỉ là một ô. Với vùng nhiều ô, công thức =SUM(INDIRECT({"B2:B3";"B3:B4"})) trả về 200 thay vì 480 mà không báo lỗi. Vì vậy, bạn nên dùng SUBTOTAL.
Tôi nên dùng INDIRECT hay VSTACK để gộp nhiều sheet?
Trên Excel 365, công thức =VSTACK(Thang1!A2:A3,Thang2!A2:A3,Thang3!A2:A3) dễ đọc hơn khi bạn cần xem dữ liệu đã gộp. Bạn nên nhớ rằng hàm INDIRECT tính lại mỗi khi bảng tính thay đổi, nên có thể làm file lớn chậm đi.
Bạn muốn dùng Excel thành thạo cho công việc văn phòng?
Những kỹ thuật này giúp bạn hiểu sâu hơn cách Excel xử lý tham chiếu, và bạn sẽ dễ tự tìm ra nguyên nhân khi công thức báo lỗi.
Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.
Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.