5 Lỗi Thường Gặp Khi Dùng Hàm INDIRECT Và Cách Khắc Phục Ngay

Hàm INDIRECT trong Excel báo lỗi thường xuyên hơn hầu hết các hàm khác. Trong ba năm làm việc với Excel, tôi gặp lỗi này gần như hàng tuần, đặc biệt khi làm việc với nhiều bảng tính hoặc tham chiếu phức tạp.

Mục Lục

Tại sao lỗi INDIRECT lại gây mất thời gian

Mỗi lần gặp lỗi, bạn mất trung bình mười lăm phút để tìm nguyên nhân. Nhân với mười lần một tháng, bạn lãng phí hai giờ rưỡi chỉ để sửa một loại lỗi. Hàm INDIRECT khác với các hàm thông thường vì nó tham chiếu gián tiếp qua chuỗi văn bản. Một ký tự sai, một trang tính đóng, hoặc một vùng ngoài giới hạn đều khiến toàn bộ công thức sụp đổ. Tệ hơn nữa, thông báo lỗi không cho biết nguyên nhân cụ thể, chỉ hiển thị dấu thăng kèm mã lỗi mơ hồ.

Lỗi một: Tham chiếu đến trang tính hoặc tệp đang đóng

Hàm INDIRECT không thể truy cập dữ liệu từ bảng tính đã đóng. Khi bạn sử dụng công thức như =INDIRECT("'[BaoCao.xlsx]Sheet1'!A1") mà tệp BaoCao.xlsx không mở, Excel trả về lỗi #REF! ngay lập tức. Đây là hạn chế cố hữu của INDIRECT vì hàm cần truy cập trực tiếp vào bộ nhớ để đọc giá trị.

Nhận biết lỗi:

Công thức hoạt động khi tệp nguồn mở
Báo lỗi #REF! ngay khi đóng tệp
Xuất hiện khi tham chiếu giữa các workbook

Cách khắc phục nhanh:

Mở tất cả các tệp được tham chiếu trước khi tính toán
Sử dụng liên kết trực tiếp thay vì INDIRECT nếu không cần tính động
Thêm hàm IFERROR để bắt lỗi: =IFERROR(INDIRECT("'[BaoCao.xlsx]Sheet1'!A1");"Vui lòng mở tệp BaoCao.xlsx")

Với các dự án lớn tham chiếu mười tệp khác nhau, tôi tạo một bảng kiểm tra liệt kê tất cả tệp cần mở. Điều này giúp tránh mở thiếu và giảm thời gian sửa lỗi từ hai mươi phút xuống còn hai phút.

XEM THÊM: Cách Chuyển Đổi Bảng Excel Thành Cột Duy Nhất Với Hàm TOCOL 2024

Lỗi hai: Chuỗi văn bản không hợp lệ trong đối số ref_text

INDIRECT yêu cầu đối số ref_text phải là địa chỉ ô hợp lệ theo cú pháp Excel. Các lỗi phổ biến bao gồm: thiếu dấu ngoặc kép, dấu chấm than sai vị trí, hoặc tên trang tính chứa khoảng trắng không được bao trong ngoặc đơn.

Các trường hợp lỗi điển hình:

=INDIRECT(My Sheet!A1) thiếu dấu ngoặc đơn bao quanh tên trang
=INDIRECT("Sheet1:A1") dùng dấu hai chấm thay vì dấu chấm than
=INDIRECT(A1) khi ô A1 rỗng hoặc chứa giá trị không phải địa chỉ ô

Giải pháp đúng:

Đối với trang tính có khoảng trắng:

=INDIRECT("'My Sheet'!A1")

Đối với tham chiếu động từ ô khác:

=INDIRECT("'"&B1&"'!"&C1)

Trong đó B1 chứa tên trang và C1 chứa địa chỉ ô.

Kiểm tra chuỗi văn bản:

Nhập công thức ="'"&B1&"'!"&C1 vào ô trống
Xem kết quả có đúng định dạng như 'Sheet1'!A1 không
Nếu đúng, thay thế vào INDIRECT: =INDIRECT("'"&B1&"'!"&C1)

Phương pháp này tiết kiệm mười phút mỗi lần gỡ lỗi vì bạn thấy ngay chuỗi sai ở đâu thay vì đoán mò trong công thức INDIRECT.

Lỗi ba: Vượt quá giới hạn hàng hoặc cột

Excel có giới hạn một triệu không trăm bốn mươi tám nghìn năm trăm bảy mươi sáu hàng và mười sáu nghìn ba trăm tám mươi bốn cột. Nếu INDIRECT tham chiếu đến địa chỉ như XFE1 hoặc A1048577, hàm trả về lỗi #REF! vì vượt quá phạm vi cho phép.

Nguyên nhân phổ biến:

Sao chép công thức từ Excel phiên bản cũ có giới hạn thấp hơn
Tính toán động tạo địa chỉ vượt giới hạn: =INDIRECT("A"&B1) khi B1 chứa giá trị 1048577
Tham chiếu cột vượt XFD do nhập sai công thức ghép chuỗi

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

Khắc phục:

Kiểm tra giá trị động trước khi đưa vào INDIRECT:

=IF(B1>1048576;"Vượt giới hạn hàng";INDIRECT("A"&B1))

Hoặc giới hạn giá trị nhập:

=INDIRECT("A"&MIN(B1;1048576))

Sử dụng công thức ADDRESS kết hợp để kiểm tra tính hợp lệ:

=IFERROR(INDIRECT(ADDRESS(B1;1));"Địa chỉ không hợp lệ")

Trong các bảng tính lớn với hàng nghìn công thức INDIRECT, tôi thêm cột kiểm tra giá trị trước khi tham chiếu. Điều này giúp phát hiện lỗi sớm và dễ dàng sửa hàng loạt bằng tìm kiếm thay thế.

Lỗi bốn: Cột hoặc hàng được tham chiếu bị xóa

Khác với tham chiếu trực tiếp tự động cập nhật khi chèn hoặc xóa hàng cột, INDIRECT giữ nguyên chuỗi văn bản gốc. Khi bạn xóa cột C mà công thức là =INDIRECT("C5"), hàm vẫn tìm cột C không còn tồn tại và báo lỗi #REF!.

Tình huống thực tế:

Giả sử có công thức:

=INDIRECT("D"&ROW())

Khi xóa cột D, công thức vẫn tìm cột D đã mất. Tham chiếu trực tiếp như =D5 sẽ tự động chuyển thành =C5 sau khi xóa, nhưng INDIRECT không có khả năng này.

Giải pháp phòng tránh:

Sử dụng tham chiếu vùng thay vì cột cố định:

=INDIRECT(ADDRESS(ROW();COLUMN(D:D)))

Hoặc lưu vị trí cột bằng số thay vì chữ:

=INDIRECT(ADDRESS(5;4))

Trong đó 4 là số thứ tự cột D.

Sửa lỗi sau khi xóa:

Nhấn Ctrl+Z ngay lập tức để hoàn tác
Hoặc thêm lại cột đã xóa với dữ liệu tương ứng
Hoặc sửa công thức INDIRECT sang cột mới: đổi “D” thành “C”

Tôi từng mất một giờ để sửa lỗi này trong bảng tính có hai trăm công thức INDIRECT sau khi đồng nghiệp xóa một cột giữa. Từ đó, tôi chuyển sang dùng số cột thay vì chữ cái cho tất cả tham chiếu động.

Lỗi năm: Viết sai tên hàm hoặc tham số

Lỗi #NAME? xuất hiện khi Excel không nhận ra tên hàm. Với INDIRECT, lỗi này xảy ra khi:

Viết thành INDERECT, INDIREKT, hoặc các biến thể sai chính tả
Phiên bản Excel không phải tiếng Anh dùng tên khác: INDIREKT trong tiếng Đức, INDIRETO trong tiếng Bồ Đào Nha
Thiếu dấu ngoặc kép bao quanh chuỗi văn bản

XEM THÊM: Tôi Giảm Thời Gian Làm Báo Cáo Excel Từ 2 Giờ Xuống 10 Phút Nhờ Hàm OFFSET

Các lỗi viết thường gặp:

Thiếu dấu ngoặc kép:

❌ =INDIRECT(A1)         (sai nếu A1 không chứa địa chỉ ô)
✅ =INDIRECT("A1")       (đúng cho tham chiếu cố định)
✅ =INDIRECT(A1)         (đúng nếu A1 chứa chuỗi như "B5")

Viết sai tên:

❌ =INDERECT("A1")
❌ =INDIRECT ("A1")      (có khoảng trắng trước ngoặc)
✅ =INDIRECT("A1")

Khắc phục:

Kiểm tra chính tả: gõ lại từ đầu thay vì copy từ nguồn khác
Dùng tính năng gợi ý: gõ =IND và nhấn Tab để Excel tự hoàn thành
Kiểm tra ngôn ngữ Excel: vào File > Options > Language > Office display language

Trong môi trường làm việc đa ngôn ngữ, tôi lưu danh sách tên hàm tương ứng giữa các phiên bản. Điều này giúp sửa nhanh khi mở file từ đồng nghiệp dùng Excel ngôn ngữ khác.

Cách phòng tránh lỗi INDIRECT hiệu quả

Thay vì sửa lỗi sau khi xảy ra, áp dụng ba quy tắc này từ đầu:

Quy tắc một: Luôn bọc INDIRECT trong IFERROR

=IFERROR(INDIRECT(A1);"Kiểm tra lại tham chiếu")

Quy tắc hai: Kiểm tra chuỗi tham chiếu trước khi dùng INDIRECT

Cột phụ: ="'"&B1&"'!"&C1
Cột chính: =IFERROR(INDIRECT(D1);"Lỗi")

Quy tắc ba: Ghi chú rõ các tệp và trang tính cần mở Tạo bảng liệt kê:

Tên tệp cần mở
Tên trang tính được tham chiếu
Số lượng công thức INDIRECT liên quan

Sau khi áp dụng ba quy tắc này, số lần gặp lỗi INDIRECT trong công việc của tôi giảm từ mười lần xuống còn một lần mỗi tháng. Thời gian sửa lỗi trung bình cũng giảm từ mười lăm phút xuống ba phút vì nguyên nhân được xác định ngay.

Khi nào nên dùng giải pháp thay thế

INDIRECT rất mạnh nhưng không phải lúc nào cũng tối ưu. Nếu bạn không cần tham chiếu động thay đổi theo điều kiện, tham chiếu trực tiếp hoặc hàm INDEX với MATCH hoạt động ổn định hơn. Đối với tham chiếu giữa các workbook, Power Query xử lý tốt hơn và không yêu cầu mở tất cả tệp nguồn. Các công thức INDIRECT chứa trong hàm SUM, AVERAGE, hoặc các hàm tổng hợp khác có thể được thay bằng SUMIFS hoặc AVERAGEIFS với tiêu chí động, giúp tránh lỗi và cải thiện hiệu suất tính toán.