Làm chủ hàm INDIRECT Excel: 8 Cách đơn giản tham chiếu động dải ô

Hàm INDIRECT (một hàm Tra cứu & Tham chiếu) dùng để trả về tham chiếu được chỉ định bởi chuỗi văn bản. Hàm này dùng để lấy giá trị từ bất kỳ tham chiếu ô hoặc dải ô nào. Trong bài viết này, tôi sẽ giải thích việc sử dụng dải INDIRECT Excel.

Để giải thích rõ hơn, tôi sẽ dùng bộ dữ liệu đại diện cho thông tin bán hàng 4 quý. Bộ dữ liệu có 5 cột là Nhân viên bán hàng, Quý-1, Quý-2, Quý-3, và Quý-4.

Tải xuống để thực hành

8 Cách sử dụng Dải INDIRECT Excel

1. Sử dụng Dải INDIRECT để Lấy giá trị qua Tham chiếu ô

Bạn có thể dùng hàm INDIRECT để lấy giá trị qua tham chiếu ô của một ô hoặc dải ô.

Ở đây, tôi muốn lấy giá trị của dải ô C4:C8 từ Quý-1.

Để thực hiện:

Ô H4, nhập công thức sau:

=INDIRECT("C4:C8")

Ở đây, trong hàm INDIRECT, tôi dùng dải ô C4:C8 làm chuỗi/văn bản (“C4:C8”) trong ref_text.

Bây giờ, nhấn ENTER, bạn sẽ nhận được tất cả giá trị của các tham chiếu ô đã dùng.

2. Lấy giá trị qua Tham chiếu ô trong Ô

Nếu bạn muốn lấy giá trị đồng thời giữ ô hoặc dải ô trong một ô, bạn cũng có thể làm bằng hàm INDIRECT.

Ở đây, tôi muốn lấy giá trị dải ô D4:D8 từ Quý-2. Thay vì dùng tham chiếu ô trực tiếp, tôi muốn giữ nó trong một ô. Trong bộ dữ liệu dưới, tôi đặt dải ô từ nơi muốn lấy giá trị.

Ô I4, nhập công thức sau:

=INDIRECT(H4)

Ở đây, trong hàm INDIRECT, tôi chọn ô H4 làm ref_text. Nơi tôi lưu dải ô D4:D8 trong ô H4. Vì vậy, nó sẽ lấy giá trị của những ô đó.

Nhấn ENTER, bạn sẽ nhận được tất cả giá trị từ ô đã chọn.

3. Sử dụng Giá trị trong Ô tạo Tham chiếu

Hàm INDIRECT cho phép tạo tham chiếu ô dùng giá trị trong một ô.

Hãy xem quy trình tạo tham chiếu.

Ô I4, nhập công thức sau:

=INDIRECT("E"&H4)

Ở đây, trong hàm INDIRECT, tôi dùng “E”&H4 làm ref_text. Nơi E là cột và trong H4 tôi lưu giá trị 5. Trong hàm, tôi nối cột E và giá trị ô H4 nên thành E5. Cuối cùng, hàm INDIRECT sẽ lấy giá trị ô E5.

Nhấn ENTER, bạn sẽ nhận được giá trị từ ô dùng làm tham chiếu.

4. Tìm TỔNG dải ô dùng Dải đặt tên (Named Range)

Bạn có thể dùng dải đặt tên trong hàm INDIRECT. Sau đó dùng các công thức khác để tính giá trị của dải đặt tên.

Quy trình đặt tên dải ô:

Chọn dải ô. Tôi chọn C4:C8.
Đi đến Thanh địa chỉ (Address Bar) và nhập tên bất kỳ để đặt tên dải. Tôi đặt tên Quarter_1.

Bây giờ, để dùng dải đặt tên tính tổng bán hàng Quarter_1, tôi dùng hàm INDIRECT và SUM cùng nhau.

Ô I4, nhập công thức sau:

=SUM(INDIRECT(I3))

Ở đây, trong hàm SUM, tôi dùng INDIRECT(I3) làm number1.

Trong hàm INDIRECT, tôi chọn ô I3 làm ref_text. Nơi trong I3 tôi giữ tên dải C4:C8.

Hàm INDIRECT sẽ lấy giá trị các ô đó và hàm SUM trả về tổng các ô đó.

Nhấn ENTER, bạn nhận được tổng bán hàng Quarter_1.

5. Dùng INDIRECT Dải đặt tên tìm TRUNG BÌNH

Bạn cũng có thể tính TRUNG BÌNH (AVERAGE) của dải đặt tên ô dùng hàm AVERAGE và INDIRECT.

Ô I4, nhập công thức sau:

=AVERAGE(INDIRECT(I3))

Hàm INDIRECT lấy giá trị các ô và hàm AVERAGE trả về trung bình của các ô đó.

Nhấn ENTER, bạn nhận được trung bình bán hàng Quarter_1.

6. Tìm GIÁ TRỊ LỚN NHẤT dải ô

Nếu muốn tìm lớn nhất của dải đặt tên, dùng hàm MAX và INDIRECT.

Ô I4, nhập công thức sau:

=MAX(INDIRECT(I3))

Hàm INDIRECT lấy giá trị các ô và hàm MAX trả về số lớn nhất từ các ô đó.

Nhấn ENTER, bạn nhận được lớn nhất bán hàng Quarter_1.

7. Tìm GIÁ TRỊ NHỎ NHẤT dải ô

Nếu muốn, bạn cũng có thể tìm nhỏ nhất của dải đặt tên bằng hàm MIN và INDIRECT.

Ô I4, nhập công thức sau:

=MIN(INDIRECT(I3))

Hàm INDIRECT lấy giá trị các ô và hàm MIN trả về số nhỏ nhất từ các ô đó.

Nhấn ENTER và bạn nhận được nhỏ nhất bán hàng Quarter_1.

8. Dùng Dải INDIRECT Excel tìm TRUNG BÌNH giá trị NHỎ NHẤT

Bạn cũng có thể tìm trung bình của 3 số nhỏ nhất từ dải ô bằng hàm AVERAGE, SMALL, ROW và INDIRECT.

Ô I4, nhập công thức sau:

=AVERAGE(SMALL(F4:F8,ROW(INDIRECT("1:3"))))

Ở đây, trong hàm AVERAGE, tôi dùng SMALL(F4:F8,ROW(INDIRECT(“1:3”))) làm number1.

Trong hàm SMALL, tôi dùng F4:F8 làm array và ROW(INDIRECT(“1:3”)) làm k (vị trí).

Trong hàm ROW, tôi dùng INDIRECT(“1:3”) làm tham chiếu (reference).

Trong hàm INDIRECT, tôi dùng 1:3 làm ref_text. 1:3 nghĩa là 3 giá trị. Hàm INDIRECT dùng trong hàm ROW để công thức đúng dù bạn chèn/xóa hàng.

Hàm SMALL trả về 3 số nhỏ nhất và hàm AVERAGE trả về trung bình của các số nhỏ nhất đó.

Nhấn ENTER và bạn nhận được trung bình của 3 nhỏ nhất bán hàng Quý_4.

Lưu ý quan trọng

🔺 Hàm INDIRECT hiển thị lỗi #REF nếu ref_text không phải tham chiếu ô hợp lệ và vượt quá giới hạn dải.
🔺 Hàm INDIRECT hiển thị lỗi #NAME nếu bạn đánh sai tên hàm.

Phần thực hành

Kết luận

Trong bài viết này, tôi đã giải thích 8 cách dùng dải INDIRECT Excel. Tôi cũng đề cập khi và tại sao hàm INDIRECT thường báo lỗi. Cuối cùng, nếu bạn có góp ý, ý tưởng hoặc phản hồi, hãy thoải mái bình luận bên dưới.