Excel INDIRECT(): Cách tạo tham chiếu động trong công thức của bạn

Hàm INDIRECT() là một trong những công cụ mạnh mẽ nhưng thường bị hiểu sai của Excel. Khác với các hàm tính toán trực tiếp, INDIRECT() chuyển chuỗi văn bản thành tham chiếu ô trực tiếp. Điều này cho phép bạn tạo các công thức linh hoạt, động, thích ứng với đầu vào thay đổi. Dù bạn đang xây dựng bảng điều khiển tóm tắt, tham chiếu các trang tính khác dựa trên lựa chọn của người dùng, hay thay đổi tham chiếu ô một cách động, INDIRECT() đều có thể tạo nên khác biệt.

Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách dùng hàm INDIRECT() để tham chiếu ô và cách xử lý các vấn đề thường gặp, bao gồm cả thời điểm nên tránh dùng hàm này. Nếu bạn chưa quen với một số kiến thức cơ bản, tôi khuyến nghị bạn học khóa Introduction to Excel để xây nền tảng vững chắc. 

Hàm INDIRECT() làm gì

Hàm INDIRECT() trong Excel truy xuất giá trị của một ô hoặc phạm vi dựa trên tham chiếu được cung cấp dưới dạng chuỗi văn bản. Thay vì mã hóa cứng địa chỉ ô như "A1" trong công thức, INDIRECT() cho phép bạn xác định động địa chỉ đó bằng văn bản, và Excel sẽ hiểu nó như một tham chiếu trực tiếp.

Cú pháp của hàm INDIRECT() như sau:

INDIRECT(ref_text, [a1])Được hỗ trợ bởi

Trong đó:

• ref_text (bắt buộc): Chuỗi văn bản biểu diễn một tham chiếu ô hoặc phạm vi hợp lệ. Có thể tham chiếu tới một ô như "A1", một phạm vi được đặt tên, hoặc thậm chí một tham chiếu ở trang tính khác như "Sheet2!B5".

• [a1]: Đây là đối số tùy chọn xác định kiểu tham chiếu trong ref_text. Nếu a1 là TRUE hoặc bị bỏ qua, ref_text sẽ được hiểu là tham chiếu kiểu A1. Nếu a1 là FALSE, ref_text sẽ được hiểu là tham chiếu kiểu R1C1.

Cách sử dụng hàm INDIRECT()

Ví dụ dưới đây cho thấy cách hàm INDIRECT() chuyển đổi động văn bản thành tham chiếu ô.

Giả sử ô C2 chứa văn bản "A2" và ô A2 chứa giá trị 100. Hàm dưới đây cũng sẽ trả về 100 vì Excel đọc nội dung của C2 và INDIRECT() xử lý nó như một tham chiếu.

=INDIRECT(C2)Được hỗ trợ bởi

Sử dụng INDIRECT() để tham chiếu dựa trên một ô khác trong Excel. Ảnh: Tác giả.

Nếu ô A5 chứa số 5, bạn có thể tạo tham chiếu động bằng công thức dưới đây:

=INDIRECT("B"&A5)Được hỗ trợ bởi

Công thức này nối "B" với giá trị trong A5 (6), tạo thành "B6". Sau đó INDIRECT() trả về giá trị tại ô B6, là "Product A".

Sử dụng INDIRECT() với & để tham chiếu động trong Excel. Ảnh: Tác giả.

Các cách dùng động của INDIRECT() trong Excel

Sức mạnh thực sự của INDIRECT() nằm ở khả năng tạo tham chiếu động. Điều này khiến nó lý tưởng cho mô hình nâng cao, bảng điều khiển và báo cáo do người dùng điều khiển. Dưới đây là ba trường hợp tiêu biểu thể hiện tính linh hoạt của nó.

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

Bạn có thể dùng INDIRECT() để lấy dữ liệu động từ các trang khác dựa trên đầu vào của một ô. Ví dụ, nếu ô A1 chứa tên một trang, như "Sales". Bạn muốn lấy giá trị từ ô B2 trên trang đó.

Công thức dưới đây tạo tham chiếu như 'Sales'!B2 và trả về giá trị của ô đó.

=INDIRECT("'" & A1 & "'!B2")Được hỗ trợ bởi

Sử dụng INDIRECT() để tham chiếu gián tiếp tới trang tính khác trong Excel. Ảnh: Tác giả.

Luôn đặt tên trang trong dấu nháy đơn bằng ' nếu có khả năng chứa khoảng trắng hoặc ký tự đặc biệt, ví dụ 'Sales 2023'!A1.

Tham chiếu phạm vi được đặt tên

Bạn cũng có thể dùng hàm INDIRECT() để tham chiếu động tới các phạm vi được đặt tên, đặc biệt hữu ích khi kết hợp với danh sách thả xuống hoặc đầu vào người dùng.

Giả sử bạn có các phạm vi được đặt tên, Sales_2023 và Sales_2023, trong đó ô “A1” chứa "Sales_2023"

Đặt tên cho phạm vi ô trong Excel. Ảnh: Tác giả.

Công thức dưới đây sẽ tính tổng các giá trị trong phạm vi được đặt tên Sales_2023, dù không tham chiếu trực tiếp. Cách này rất phù hợp cho bảng điều khiển do người dùng điều khiển hoặc các mô hình có nhiều kịch bản.

=SUM(INDIRECT(A1))Được hỗ trợ bởi

Sử dụng INDIRECT() để tham chiếu phạm vi được đặt tên trong Excel. Ảnh: Tác giả.

Tham chiếu cố định không thay đổi

Thông thường, khi bạn chèn hoặc xóa hàng/cột, tham chiếu ô trong công thức sẽ tự điều chỉnh. Nhưng hàm INDIRECT() tạo ra tham chiếu tuyệt đối, không bị dịch chuyển.

Ví dụ, nếu ô “A1” chứa 100, cả công thức =A1 và =INDIRECT("A1") đều trả về 100. Tuy nhiên, nếu bạn dịch chuyển giá trị

Tham chiếu ô trực tiếp trong Excel. Ảnh: Tác giả.

Sử dụng INDIRECT() để tham chiếu ô trong Excel. Ảnh: Tác giả.

Tuy nhiên, nếu bạn chèn một dòng mới phía trên hàng 1, D3 (trước là B2) giờ hiển thị =A3 vì giá trị dịch theo hàng. Ô E3 với hàm INDIRECT() vẫn hiển thị A2 ban đầu, có thể giờ trống hoặc có giá trị khác (UID).

Sử dụng INDIRECT() cho các tham chiếu cố định trong Excel. Ảnh: Tác giả.

Ví dụ và trường hợp nâng cao

Khi bạn đã nắm vững cơ bản, hàm INDIRECT() tỏa sáng trong các tình huống Excel nâng cao, đặc biệt khi kết hợp với các hàm như VLOOKUP(), ROW() và mảng động. Những kết hợp này mở khóa các mô hình còn linh hoạt và năng động hơn.

Dùng INDIRECT() với VLOOKUP()

Bạn có thể kết hợp INDIRECT() với VLOOKUP() để thay đổi động bảng tra cứu dựa trên đầu vào người dùng.

Giả sử bạn có hai bảng, Prices_2022 và Prices_2023. Ở các ô riêng, G1: Prices_2022 và G2: Apple`. Công thức dưới đây sẽ tìm động "Apple" trong bảng của năm được chọn dựa trên giá trị ở A1 và trả về giá của nó.

=VLOOKUP(G2, INDIRECT(G1), 2, FALSE)Được hỗ trợ bởi

Sử dụng INDIRECT() với VLOOKUP() trong Excel. Ảnh: Tác giả.

Xem thêm hướng dẫn VLOOKUP() từ trang tính khác: Cách thực hiện trong Excel để tìm hiểu thêm về cách tìm kiếm và truy xuất dữ liệu từ các trang tính Excel khác nhau.

INDIRECT() với tham chiếu R1C1

INDIRECT() cũng có thể dùng ký pháp R1C1, đặc biệt hữu ích khi xây dựng công thức động trong macro hoặc tham chiếu vị trí theo lập trình.

Ví dụ, công thức bên dưới trả về giá trị ở hàng 2, cột 3 (ô C2). Đối số thứ hai (FALSE) cho Excel biết bạn dùng kiểu R1C1, không phải A1.

=INDIRECT("R2C3", FALSE)Được hỗ trợ bởi

Sử dụng INDIRECT() với R1C1 trong Excel. Ảnh: Tác giả.

Công thức sau kết hợp cách dùng động của ROW() và COLUMN(). Công thức sẽ luôn trả về giá trị ở cột 3 trên cùng hàng nơi công thức được viết.

=INDIRECT("R" & ROW() & "C3", FALSE)Được hỗ trợ bởi

Sử dụng INDIRECT() với ROW() và COLUMN() trong Excel. Ảnh: Tác giả.

INDIRECT() và mảng động

Trước khi Excel giới thiệu mảng động, INDIRECT() là cách phổ biến để tạo hành vi giống mảng.

Ví dụ, công thức dưới đây trả về một mảng: {1; 2; 3; 4; 5}. Nó mô phỏng mảng từ 1 đến 5 bằng cách tham chiếu gián tiếp các hàng.

=ROW(INDIRECT("1:5"))Được hỗ trợ bởi

Sử dụng INDIRECT() với ROW() cho mảng động trong Excel. Ảnh: Tác giả.

Phương án hiện đại thay thế là dùng hàm SEQUENCE().

=SEQUENCE(5)Được hỗ trợ bởi

Sử dụng SEQUENCE() cho mảng động trong Excel. Ảnh: Tác giả.

Dùng INDIRECT() cho mảng động vẫn hoạt động, nhưng bị xem là kém hiệu quả và khó đọc hơn so với SEQUENCE(), FILTER() và các hàm mảng động khác trong Excel hiện đại (Office 365 và Excel 2021+).

Lưu ý và vấn đề hiệu năng

Hàm INDIRECT() có một số hạn chế quan trọng, đặc biệt khi dùng trong sổ làm việc Excel lớn hoặc phức tạp. Các vấn đề này bao gồm:

• Hàm biến động (tính toán lại quá mức): INDIRECT() là hàm biến động, nghĩa là nó sẽ tính toán lại mỗi khi có bất kỳ thay đổi nào ở bất cứ đâu trong sổ làm việc, ngay cả khi thay đổi đó không liên quan đến công thức. Điều này có thể làm chậm hiệu năng.

• Khó gỡ lỗi: Vì INDIRECT() tạo tham chiếu từ văn bản, Excel không dễ truy vết như với tham chiếu ô chuẩn. Điều đó khiến khó kiểm toán công thức bằng Trace Precedents/Dependents, hoặc phát hiện tham chiếu hỏng khi có thay đổi.

• Cân nhắc phương án hiện đại: Khi có thể, tốt hơn nên dùng các hàm không biến động cho hiệu năng và sự rõ ràng tốt hơn. Bao gồm INDEX() cho tra cứu ô động, CHOOSE() để chọn động từ các phạm vi hoặc tùy chọn định sẵn, và SEQUENCE() để tạo mảng số động.

Khi nào nên tránh dùng INDIRECT()

Dù INDIRECT() mang lại tính linh hoạt mạnh mẽ, không phải lúc nào nó cũng là lựa chọn tốt nhất. Nó thường khiến công thức phức tạp không cần thiết, giảm hiệu năng, hoặc khó bảo trì. Dưới đây là một số tình huống nên tránh và phương án thay thế tốt hơn.

• Tham chiếu ô động trong cùng trang: Dùng INDEX() thay cho INDIRECT() để có hiệu năng và khả năng truy vết tốt hơn.

• Mảng động hoặc dãy số: Tránh =ROW(INDIRECT("1:10")). Thay vào đó dùng =SEQUENCE(10), nhanh hơn, gọn hơn và không biến động.

• Khi tính truy vết quan trọng: Công thức dùng INDIRECT() khó truy vết hiệu quả bằng “Trace Precedents” hoặc “Evaluate Formula”.

• Tham chiếu tới ô hoặc phạm vi tĩnh: Đừng dùng INDIRECT("A1") nếu bạn chỉ cần viết =A1.

• Dùng nhiều trong bảng tính lớn: Vì INDIRECT() là hàm biến động, nó liên tục tính toán lại và có thể làm chậm đáng kể.

Các phương án thay thế INDIRECT()

Bảng sau tóm tắt các lựa chọn thay thế tốt nhất cho INDIRECT().

Điểm cần ghi nhớ

INDIRECT() biến chuỗi văn bản thành tham chiếu trực tiếp, khiến nó trở thành công cụ mạnh để xây dựng bảng tính linh hoạt và động. Nó hữu ích để tham chiếu các trang khác, phạm vi được đặt tên, hoặc các ô cố định không nên dịch chuyển khi có thay đổi. Tuy nhiên, đây là hàm biến động thường xuyên tính toán lại và có thể làm chậm các sổ làm việc lớn. Hàm này cũng khó truy vết và gỡ lỗi hơn so với các hàm truyền thống. Hãy dùng INDIRECT() có chọn lọc và cân nhắc các phương án an toàn, hiệu quả hơn.

Tôi khuyến nghị học khóa Data Analysis in Excel để tìm hiểu về insight trong dữ liệu B2B và lộ trình kỹ năng Excel Fundamentals để tìm hiểu thêm về các hàm có trong các phiên bản Excel mới nhất. Khóa Advanced Excel Functions là lựa chọn hoàn hảo nếu bạn đang khám phá tham chiếu và tra cứu nâng cao.