Tôi biết dân chuyên Excel ghét hàm này, nhưng nó vẫn là “vũ khí bí mật” yêu thích của tôi

Chỉ cần nhắc đến hàm INDIRECT trong các hội nhóm về Excel, bạn sẽ lập tức thấy nhiều ý kiến trái chiều. Nó là một hàm “volatile”, nghĩa là nó luôn trong trạng thái sẵn sàng và tự động tính toán lại mỗi khi có thay đổi nhỏ, điều này có thể biến một file Excel nhanh thành một file ì ạch đến khó chịu. Tuy nhiên, nếu biết cách sử dụng đúng, nó lại là vũ khí bí mật để xây dựng các bảng điều khiển (dashboard) động, linh hoạt.

Mặc dù tiếng tăm làm chậm bảng tính của INDIRECT là có thật, nhưng mối nguy thực sự không nằm ở bản thân hàm số, mà là ở việc dùng nó cho những công việc không phù hợp trong khi có những lựa chọn thay thế tốt hơn. Để thành thạo INDIRECT, bạn cần biết khi nào nên tận dụng sự linh hoạt của nó và khi nào nên chuyển sang một giải pháp ổn định hơn.

Cách hoạt động của hàm INDIRECT

Hầu hết các hàm Excel đều tham chiếu trực tiếp, nhưng INDIRECT lại hoạt động một cách gián tiếp. Hãy tưởng tượng nó như một mảnh giấy ghi chú dán trên cửa: thay vì bước thẳng vào một căn phòng (một công thức thông thường), mảnh giấy đó chỉ cho bạn địa chỉ của một căn phòng khác để tìm dữ liệu.

Cú pháp của hàm INDIRECT trong Excel khá đơn giản:

=INDIRECT(ref_text,[a1])

Trong đó:

• ref_text có thể là: một tham chiếu ô thông thường (như A1), một tham chiếu theo kiểu R1C1, một ô đã được đặt tên (named range), hoặc một chuỗi văn bản chứa tham chiếu đến một ô.
• a1 (tùy chọn) là một giá trị luận lý (TRUE/FALSE). Nếu là TRUE hoặc bỏ trống, Excel sẽ hiểu ref_text là tham chiếu kiểu A1 (cột chữ, hàng số). Nếu là FALSE, Excel sẽ hiểu ref_text là tham chiếu kiểu R1C1 (cả cột và hàng đều là số, ví dụ R2C1 là hàng 2, cột 1).

Theo mặc định, Excel dùng kiểu tham chiếu A1. Vì vậy, trong hầu hết các trường hợp, bạn có thể bỏ qua đối số a1.

INDIRECT lý tưởng để tạo danh sách thả xuống phụ thuộc (Dependent Drop-down Lists)

Giả sử bạn có hai bảng đã được đặt tên (Named Tables) trong Excel: T_France và T_USA. Bạn muốn các tùy chọn trong ô Thành phố (F2) thay đổi tự động dựa trên lựa chọn Quốc gia (E2).

Bước 1: Tạo danh sách thả xuống cho Quốc gia (ô E2). Chọn ô E2, vào tab Data, nhấp Data Validation.

Bước 2: Trong hộp thoại, chọn List ở mục Allow, và gõ France,USA vào ô Source, sau đó nhấn OK.

Bước 3: Tạo danh sách thả xuống phụ thuộc cho Thành phố (ô F2). Chọn ô F2, mở lại Data Validation, chọn List. Lần này, trong ô Source, bạn nhập công thức sau:

=INDIRECT("T_"&E2)

Bây giờ, khi bạn chọn “USA” ở ô E2, danh sách ở ô F2 sẽ hiển thị các thành phố từ bảng T_USA. Khi chọn “France”, danh sách sẽ chuyển sang các thành phố từ bảng T_France.

Công thức hoạt động bằng cách dùng ký tự “&” để nối chuỗi “T_” với giá trị trong ô E2 (ví dụ: “T_USA”). Sau đó, INDIRECT biến chuỗi văn bản này thành một tham chiếu thực sự đến bảng có tên tương ứng.

INDIRECT là lựa chọn tuyệt vời để liên kết một Dashboard đa bảng (Multi-Table Dashboard)

Giả sử bạn có các bảng doanh số riêng biệt cho từng năm: T_2024 và T_2025. Bạn muốn một ô tổng hợp (J2) tự động tính tổng doanh số dựa trên năm được chọn từ menu thả xuống (I2).

Bước 1: Tạo một bảng thứ ba tên là T_Years, trong đó liệt kê các năm (2024, 2025) để làm nguồn cho danh sách thả xuống ở ô I2.

Bước 2: Đảm bảo các bảng dữ liệu được đặt tên nhất quán (T_[Năm]) và cột cần lấy dữ liệu có cùng tên (ví dụ: “Sales”).

Bước 3: Tạo danh sách thả xuống cho Năm (ô I2) bằng Data Validation > List, và chọn nguồn là cột năm trong bảng T_Years.

Bước 4: Trong ô J2, nhập công thức tính tổng động sau:

=SUM(INDIRECT("T_"&I2&"[Sales]"))

Công thức này ghép chuỗi “T_”, giá trị năm ở ô I2, và “[Sales]” để tạo thành một tham chiếu bảng động (ví dụ: “T_2024[Sales]”). INDIRECT sẽ chuyển chuỗi này thành tham chiếu thực tế, sau đó hàm SUM sẽ tính tổng cột Sales của bảng đó.

INDIRECT không phải lúc nào cũng tốt nhất: Tính toán trên một cửa sổ trượt (Rolling Window)

INDIRECT rất tốt để chuyển đổi giữa các bảng riêng biệt. Tuy nhiên, khi dùng nó để tính toán liên tục trên một tập dữ liệu đang mở rộng, cái giá về hiệu suất (do tính volatile) là quá lớn.

Ví dụ: Bạn có một bảng T_Stocks cập nhật giá mỗi ngày. Bạn muốn tính giá trung bình của X bản ghi gần nhất, với X là số bạn nhập vào ô D3.

Bạn có thể dùng INDIRECT để xây dựng phạm vi động, nhưng với loại tính toán này, tôi khuyên dùng hàm INDEX (không volatile) – nó chỉ tính toán lại khi dữ liệu trong phạm vi tham chiếu thay đổi.

Công thức trong ô E3 sẽ là:

=AVERAGE(INDEX(T_Stocks[Price],ROWS(T_Stocks)-(D3-1)):INDEX(T_Stocks[Price],ROWS(T_Stocks)))

Dấu hai chấm (:) ở giữa hai hàm INDEX tạo ra một phạm vi động. Hàm INDEX đầu tiên xác định điểm bắt đầu (lấy từ dưới lên), hàm INDEX thứ hai xác định điểm kết thúc (hàng cuối cùng của bảng).

Nếu bạn đổi số 5 trong ô D3 thành 10, công thức sẽ ngay lập tức tính trung bình cho 10 bản ghi gần nhất.

INDIRECT vẫn là một trong những hàm gây tranh cãi nhất Excel vì bản chất volatile có thể khiến file chạy chậm. Nhưng khi bạn cần biến một chuỗi văn bản thành tham chiếu động cho danh sách thả xuống phụ thuộc hay để chuyển đổi giữa các bảng, nó vẫn là công cụ phù hợp nhất. Hiểu rõ và thận trọng với đặc tính volatile của nó là chìa khóa để duy trì một bảng tính Excel mạnh mẽ và hiệu quả.

Tags:
Excel tips, INDIRECT function, Dashboard automation
Tags: Excel tips, INDIRECT function, Dashboard automation