INDIRECT Và Tham Chiếu Động Trong Excel: Tạo Công Thức Linh Hoạt Vô Hạn

1. INDIRECT là gì? #

Hàm INDIRECT chuyển đổi TEXT thành THAM CHIẾU ô thật. Nghĩa là bạn có thể dùng text để "chỉ" đến ô, vùng, hoặc sheet — giúp tạo công thức linh hoạt thay đổi tham chiếu dựa trên giá trị ô khác.

=INDIRECT(ref_text, [a1])

2. Ví dụ 1: Tham chiếu từ giá trị ô #

Ô B1 chứa text "A5":

=INDIRECT(B1)

Kết quả: giá trị của ô A5. Khi đổi B1 thành "A10" → công thức tự trỏ đến A10.

3. Ví dụ 2: Ghép tham chiếu động #

Cột dữ liệu thay đổi — cột C, D, E tùy vào lựa chọn:

Ô G1 chứa chữ cái cột: "D"

=SUM(INDIRECT(G1&"2:"&G1&"100"))

Kết quả: =SUM(D2:D100) — nếu đổi G1 thành "E" → tính cột E.

4. Ví dụ 3: Tham chiếu đến sheet khác #

Tên sheet nằm trong ô A1: "Tháng 1"

=INDIRECT("'"&A1&"'!B5")

Kết quả: lấy giá trị ô B5 của sheet "Tháng 1". Đổi A1 thành "Tháng 2" → tự động lấy từ sheet khác.

Lưu ý: Sheet có dấu cách phải bọc trong dấu nháy đơn 'Tháng 1'!B5.

5. Ví dụ 4: Dropdown phụ thuộc (Dependent Dropdown) #

5.1. Bước 1: Tạo Named Ranges #

Tạo 3 Named Range cho phòng ban:

5.2. Bước 2: Dropdown cấp 1 (Phòng ban) #

Cell B1 → Data Validation → List → Source: Kinh_doanh, Marketing, IT

5.3. Bước 3: Dropdown cấp 2 (Nhân viên) #

Cell B2 → Data Validation → List → Source:

=INDIRECT(SUBSTITUTE(B1," ","_"))

SUBSTITUTE thay dấu cách bằng _ (vì Named Range không chứa dấu cách).

Kết quả: Chọn "Kinh_doanh" ở B1 → B2 chỉ hiện An, Bình, Cường.

6. Ví dụ 5: SUM nhiều sheet #

Sheet: Tháng_1, Tháng_2, ..., Tháng_12

Ô A1 chứa "Tháng_1", A2 chứa "Tháng_12":

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Tháng_1","Tháng_2","Tháng_3"}&"'!A:A"),"Kinh doanh",INDIRECT("'"&{"Tháng_1","Tháng_2","Tháng_3"}&"'!B:B")))

7. Ví dụ 6: Tạo bảng xoay động #

Header cột thay đổi dựa trên lựa chọn:

=INDIRECT("Dữ liệu["&F1&"]")

F1 chứa tên cột trong Table → INDIRECT trả toàn bộ cột đó.

8. ADDRESS — Tạo tham chiếu từ số #

=ADDRESS(row, column, [abs_type], [a1], [sheet])

8.1. Kết hợp INDIRECT + ADDRESS #

=INDIRECT(ADDRESS(ROW(), MATCH("Doanh số", 1:1, 0)))

Tìm cột "Doanh số" bất kể vị trí → lấy giá trị tại hàng hiện tại.

9. ROW và COLUMN — Tham chiếu vị trí #

=ROW()       → số hàng hiện tại
=COLUMN()    → số cột hiện tại
=ROW(A5)     → 5
=COLUMN(C3)  → 3

9.1. Ứng dụng: Đánh số tự động #

=ROW()-1

Ở hàng 2 → trả về 1, hàng 3 → 2... Không bị ảnh hưởng khi xóa/thêm hàng (khác Number fill).

9.2. Ứng dụng: Xen kẽ màu hàng #

=MOD(ROW(),2)=0

Dùng trong Conditional Formatting → tô màu hàng chẵn.

10. OFFSET — Tham chiếu dịch chuyển #

=OFFSET(reference, rows, cols, [height], [width])

10.1. Ví dụ: Lấy N giá trị cuối #

=OFFSET(A1, COUNTA(A:A)-5, 0, 5, 1)

Tạo vùng 5 ô cuối cùng có dữ liệu.

10.2. Dynamic Chart Range #

Dùng OFFSET tạo Named Range động → biểu đồ tự mở rộng khi thêm dữ liệu.

=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)

11. Lưu ý khi dùng INDIRECT #

1. Volatile function: INDIRECT tính lại mỗi khi Excel recalculate → chậm trên file lớn

2. Không update khi rename sheet: Text "Sheet1" không tự đổi khi bạn đổi tên sheet

3. Khó audit: Công thức chứa INDIRECT khó trace dependency

4. OFFSET cũng volatile: Dùng INDEX thay nếu có thể

12. Mẹo INDIRECT #

1. Kết hợp Data Validation: Tạo dashboard thay đổi dữ liệu dựa trên dropdown

2. SUBSTITUTE khoảng trắng: Named Range không chứa dấu cách → dùng SUBSTITUTE

3. Dùng INDEX thay OFFSET: INDEX không volatile → nhanh hơn

4. Debug: Tách ref_text ra ô riêng → dễ kiểm tra text tham chiếu

13. Tổng kết #

INDIRECT và các hàm tham chiếu động là chìa khóa để tạo bảng tính "thông minh" — tự thay đổi dữ liệu dựa trên lựa chọn của người dùng. Kết hợp với Data Validation và Named Range, bạn có thể xây dựng dashboard tương tác mà không cần VBA.