Cách dùng hàm INDIRECT trong Excel và ví dụ chi tiết

Trong Excel, việc tham chiếu linh hoạt đến nhiều ô hoặc vùng dữ liệu là nhu cầu thường gặp, đặc biệt khi bạn làm việc với bảng tính phức tạp. Hàm INDIRECT được tạo ra để giải quyết nhiệm vụ này, cho phép lấy dữ liệu dựa trên giá trị văn bản thay vì tham chiếu cố định. Nội dung dưới đây sẽ giúp bạn hiểu rõ cách dùng hàm INDIRECT trong Excel.

Hướng dẫn chi tiết cách sử dụng hàm INDIRECT đơn giản trong Excel

Hàm INDIRECT cho phép tạo ra các tham chiếu gián tiếp đến ô, vùng dữ liệu, trang tính hoặc bảng tính khác. Nhờ đó, bạn có thể linh hoạt lấy dữ liệu mà không cần chỉnh sửa lại công thức mỗi khi địa chỉ tham chiếu thay đổi.

Cú pháp hàm INDIRECT:

=INDIRECT(ref_text,[a1])

Trong đó:

• ref_text (Bắt buộc): Chuỗi văn bản biểu diễn tham chiếu ô hoặc vùng dữ liệu.
• a1(Tuỳ chọn): Đây là một giá trị logic dùng để xác định dạng tham chiếu mà đối số ref_text sẽ sử dụng.

Ví dụ: Sử dụng hàm INDIRECT để tham chiếu các ô trong cột B và trả về giá trị trong cột C.

Bước 1: Trong bảng dữ liệu Excel, tại ô C2, nhập công thức: =INDIRECT(B2).

Giải thích: B2 không chứa số mà chứa chuỗi văn bản “A2”. Hàm INDIRECT chuyển chuỗi đó thành một tham chiếu thật trong bảng tính. Do đó, Excel sẽ lấy giá trị tại ô A2 và hiển thị ở cột C tương ứng.

Hàm INDIRECT tham chiếu ô từ nội dung văn bản

Bước 2: Nhấn Enter để xem kết quả. Hàm INDIRECT sẽ đọc nội dung trong B2 như một tham chiếu ô, nghĩa là nó truy cập trực tiếp đến ô A2 và lấy giá trị tại đó. Vì ô A2 chứa số 10, nên kết quả trả về tại ô C2 cũng là 10.

Bước 3: Kéo công thức xuống các ô bên dưới. Excel sẽ tự lấy địa chỉ trong từng ô ở cột B và trả về giá trị tương ứng ở cột A. 

Kéo công thức để lấy dữ liệu tự động

Ví dụ: Sử dụng hàm INDIRECT để lấy giá trị trong cột A dựa trên số hàng được ghi trong cột B, và hiển thị kết quả tại cột C.

Bước 1: Trong bảng Excel, nhập công thức: =INDIRECT("A" & B2)

Giải thích:

• "A": đại diện cho cột A.
• B2: chứa số hàng cần lấy.
• Khi ghép lại thành "A3", hàm INDIRECT hiểu đây là địa chỉ ô A3 và trả về giá trị trong ô đó (ở ví dụ này là 3).

Ghép cột A với số hàng để lấy giá trị

Bước 2: Nhấn Enter để xem kết quả. Excel sẽ trả về giá trị của ô A3.

Sao chép công thức để lấy dữ liệu theo từng hàng

Ví dụ: Bạn có một bảng dữ liệu bán hàng (Bảng 1) gồm doanh số của 3 sản phẩm: A, B và C. Nhiệm vụ là tính tổng và giá trị trung bình doanh số của từng sản phẩm.

Bước 1: Tính tổng và trung bình doanh số của sản phẩm A

• Tại ô G2, nhập công thức: =SUM(INDIRECT(G1))
• Tại ô G3, nhập: =AVERAGE(INDIRECT(G1))

Giải thích:

• Ô G1 chứa tên vùng dữ liệu (“SPA”).
• Hàm INDIRECT chuyển giá trị văn bản trong G1 thành một vùng tham chiếu thực.
• Từ vùng tham chiếu này, các hàm SUM và AVERAGE sẽ tính tổng và trung bình doanh số.

Bước 2: Áp dụng cho sản phẩm B và C

Chỉ cần thay tên vùng tương ứng trong ô tham chiếu (ví dụ: “SPB”, “SPC”), bạn có thể tính được tổng và trung bình của từng sản phẩm theo cách tương tự.

INDIRECT truy cập vùng tên SPA để tính tổng và trung bình

Cấu trúc hàm: =INDIRECT("" & Sheet’s name & "!" & Cell to pull data from)

Trong đó:

• Các ký tự "", & và ! dùng để ghép chuỗi tham chiếu.
• Sheet’s name: Tên của trang tính chứa dữ liệu cần lấy.
• Cell to pull data from: Địa chỉ ô muốn truy xuất trong Sheet đó.

Ví dụ: Giả sử bạn đang ở Bảng 2 và muốn lấy dữ liệu từ Sheet2.

Bước 1: Nhập vào một ô bất kỳ công thức: =INDIRECT("" & $A$2 & "!" & B2)

Giải thích:

• A2 chứa tên Sheet cần truy cập (ví dụ: “Sheet2”).
• B2 chứa địa chỉ ô cần lấy (ví dụ: “A2”).

INDIRECT truy xuất dữ liệu từ Sheet2 theo địa chỉ ô

Bước 2: Nhấn Enter để hiển thị kết quả được lấy từ Sheet2.

Hiển thị giá trị từ Sheet2 dựa trên tham chiếu gián tiếp

Hàm INDIRECT thường được dùng chung với SUM, AVERAGE, COUNTIF… để linh hoạt thay đổi vùng dữ liệu.

Ví dụ 1: Kết hợp hàm INDIRECT với hàm COUNTIF

Khi đã có địa chỉ tọa độ tham chiếu tương ứng theo tên nhân viên, chúng ta có thể sử dụng hàm INDIRECT để lấy nội dung trong các tham chiếu đó thay vì phải sử dụng trực tiếp địa chỉ tham chiếu.

Công thức COUNTIFS phía trên có thể sửa lại là:

C3=COUNTIFS(INDIRECT($B3),”>0″,$I$3:$I$19,C$2)

(So với công thức ở trên thì các tham chiếu đã bị tăng 1 cột do chúng ta mới chèn thêm 1 cột là Tọa độ tham chiếu)

Lúc này  có thể sao chép công thức từ C3 tới các nhân viên khác bên dưới, sang cột Chưa hoàn thành bên phải.

Kết quả thu được là:

Kết hợp hàm INDIRECT với hàm COUNTIF

Ví dụ 2: Kết hợp hàm INDIRECT với hàm VLOOKUP

Sử dụng hàm VLOOKUP để dò tìm giảm giá của các sản phẩm.

Công thức: =VLOOKUP(B2;INDIRECT("GIAMGIA");2;0)

Giải thích:

• B2: Giá trị được dùng để dò tìm là tên sản phẩm.
• INDIRECT("GIAMGIA"): Là bảng chứa giá trị muốn dò tìm.
• 2: Vị trí của cột có chứa giá trị bạn muốn dò tìm.
• 0: Dò tìm tương đối.

Kết hợp hàm INDIRECT với hàm VLOOKUP

3. Một số lỗi thường gặp khi dùng hàm INDIRECT

3.1 Lỗi #REF!

Hàm INDIRECT có thể trả về lỗi #REF! trong các trường hợp sau:

• Chuỗi ref_text không tạo thành một địa chỉ ô hợp lệ.
• Tham chiếu vượt ngoài phạm vi cho phép, như số hàng lớn hơn 1.048.576 hoặc số cột vượt quá 16.384.
• Sheet hoặc workbook mà công thức hướng tới đang đóng, khiến Excel không thể truy xuất dữ liệu.

Cách khắc phục:

• Rà soát lại chuỗi tham chiếu và chỉnh cho đúng định dạng.
• Đảm bảo trang tính chứa dữ liệu đang được mở.
• Kết hợp IFERROR để xử lý lỗi an toàn.

Ví dụ khi sử dụng hàm INDIRECT bị lỗi #REF!

3.2 Lỗi #NAME?

Nguyên nhân: Lỗi xuất hiện khi nhập sai cú pháp hàm hoặc gõ nhầm ký tự trong công thức.

Cách khắc phục: Xem lại công thức và chỉnh sửa đúng tên hàm cũng như các ký tự liên quan.

Lỗi #NAME? khi nhập hàm INDIRECT không đúng

Hàm INDIRECT mang đến sự linh hoạt khi tham chiếu dữ liệu, đặc biệt hữu ích trong báo cáo và bảng tính nhiều sheet. Áp dụng đúng cách sẽ giúp bạn tiết kiệm thời gian và làm việc hiệu quả hơn. Đừng quên theo dõi chuyên mục Kinh nghiệm mua sắm của Siêu Thị Điện Máy - Nội Thất Chợ Lớn để cập nhật thêm nhiều thông tin hữu ích.

Cần trang bị thiết bị hỗ trợ công việc Excel hiệu quả hơn? Hãy đến ngay Siêu Thị Điện Máy - Nội Thất Chợ Lớn để chọn mua điện thoại, máy tính bảng và phụ kiện phù hợp!