Đánh giá lequocthai.com:
INDIRECT còn mở rộng khả năng của các hàm khác bằng cách cung cấp tham chiếu ô động, giúp thực hiện các phép tính dựa trên các tham chiếu thay đổi.
Cú pháp
“`
=INDIRECT(ref_text, [a1])“`
Đối số
- `ref_text` (bắt buộc): chuỗi văn bản chứa địa chỉ ô hoặc tên đã được đặt cho ô.
- `a1` (tùy chọn): xác định kiểu tham chiếu trong `ref_text`. Mặc định là TRUE (kiểu A1). Đặt FALSE để sử dụng kiểu R1C1.
Các đặc điểm quan trọng
- Nếu `ref_text` không phải là địa chỉ ô hợp lệ, hoặc workbook/sheet chưa mở, hoặc phạm vi vượt quá giới hạn hàng‑cột, hàm sẽ trả về lỗi #REF!.
- Khi dùng kiểu tham chiếu R1C1 mà `a1` được đặt TRUE, cũng sẽ gây lỗi #REF!.
- Tránh tham chiếu vòng (circular reference) vì sẽ sinh lỗi vòng lặp.
- INDIRECT là hàm volatile (tính năng tính lại mỗi khi có thay đổi trong bảng tính), do đó với dữ liệu lớn có thể làm giảm hiệu suất.
Ví dụ về hàm INDIRECT
Hàm INDIRECT cho phép tạo công thức động, danh sách thả xuống phân cấp, và nhiều ứng dụng khác. Dưới đây là một số ví dụ thực tế.
Ví dụ 1 – Sử dụng cơ bản hàm INDIRECT
- Trường hợp 1: `ref_text` nhập B2. Ô B2 chứa địa chỉ A2, vì vậy `=INDIRECT(B2)` trả về giá trị của ô A2 (10).
- Trường hợp 2: dùng chuỗi trực tiếp `=INDIRECT(“A2”)` cũng trả về 10.
- Trường hợp 3: dùng kiểu R1C1, nhập `=INDIRECT(“R2C1”,FALSE)` để lấy giá trị ở A2.
Nếu chèn một cột hoặc một hàng trước A2, công thức `=A2` sẽ tự động thay đổi thành `=B2` hoặc `=A3`, trong khi `=INDIRECT(“A2”)` luôn giữ tham chiếu cố định.
Ví dụ 2 – Tạo các tham chiếu ô có tên động
Giả sử bạn muốn tính tổng điểm cho từng học sinh. Thay vì nhập thủ công phạm vi, hãy ghi địa chỉ phạm vi vào ô B8 và dùng:
“`
=SUM(INDIRECT(B8))“`
Để tiện hơn, tạo phạm vi có tên cho mỗi học sinh (tab Formulas → Create from Selection). Sau khi đặt tên, bạn có thể dùng công thức:
“`
=SUM(INDIRECT("TênHọcSinh"))“`
Thêm Xác Thực Dữ Liệu (Data Validation) vào ô B8 để người dùng chọn tên học sinh từ danh sách.
Ví dụ 3 – Tạo tham chiếu sheet động bằng hàm INDIRECT
Mỗi học sinh có một worksheet riêng, tên sheet trùng với tên học sinh. Để lấy tổng điểm và điểm cao nhất của học sinh được chọn trong ô C4, dùng:
“`
=SUM(INDIRECT("'"&$C$4&"'!C2:C6"))=MAX(INDIRECT("'"&$C$4&"'!C2:C6"))“`
Excel sẽ tự động thêm dấu nháy đơn nếu tên sheet chứa dấu cách.
Ví dụ 4 – Tạo phạm vi cố định bằng hàm INDIRECT
Khi chèn một dòng mới ở đầu bảng, công thức `=SUM(C2:C10)` sẽ tự động thay đổi thành `=SUM(C3:C11)` và bỏ qua dữ liệu đầu tiên. Để giữ ô đầu luôn là C2, dùng:
“`
=SUM(INDIRECT("C2"):C10)“`
Vì “C2” được xem như một chuỗi, địa chỉ này sẽ không bị thay đổi khi chèn dòng mới.
Ví dụ 5 – Sử dụng hàm INDIRECT với tham chiếu R1C1
Nếu dữ liệu bán hàng được cập nhật bằng cách thêm cột mới mỗi ngày, công thức sau sẽ luôn lấy giá trị ở hàng 7, cột cuối cùng:
“`
=INDIRECT("R7C"&COUNTA(7:7),FALSE)“`
`COUNTA(7:7)` đếm số ô không rỗng trong hàng 7, phần còn lại là tham chiếu R1C1.
Ví dụ 6 – INDIRECT kết hợp Xác Thực Dữ Liệu (Data Validation)
Tạo danh sách thả xuống phụ thuộc cấp đa tầng:
- Tạo phạm vi có tên cho các danh sách (ví dụ `Team_Name`, `TeamA_Members`, …).
- Đặt danh sách thả xuống cho ô A10 (đội) bằng `=Team_Name`.
- Đối với ô B10 (thành viên), trong Source nhập công thức:
“`
=INDIRECT($A$10)“`
Công thức sẽ lấy tên phạm vi được ghi trong ô A10 và hiển thị danh sách thành viên tương ứng.
INDIRECT kết hợp VLOOKUP
Khi có các bảng doanh thu theo tháng (ví dụ `Jan_Sales`, `Feb_Sales` …), công thức sau cho phép tra cứu doanh thu dựa trên tháng được chọn:
“`
=VLOOKUP(B10,INDIRECT(B11&"_Sales"),2,FALSE)“`
Ở đây `B11` chứa tên tháng, `INDIRECT` tạo phạm vi dữ liệu động, sau đó VLOOKUP trả về doanh thu.
Sau khi nắm vững hàm INDIRECT, bạn có thể xây dựng các bảng tính động và tương tác hơn. Hãy thực hành và khám phá thêm các ứng dụng, trong khi chúng tôi sẽ sớm giới thiệu những hàm Excel hữu ích khác.
