Hàm INDIRECT là gì? 7 ứng dụng của hàm INDIRECT trong excel

Hàm INDIRECT trong Excel là công cụ hữu ích giúp tạo tham chiếu động đến ô, vùng dữ liệu hoặc sheet mà không cần nhập trực tiếp địa chỉ trong công thức. Nhờ đó, người dùng có thể linh hoạt truy xuất và xử lý dữ liệu, đặc biệt khi làm việc với các bảng tính có cấu trúc thường xuyên thay đổi. Hãy cùng tìm hiểu về hàm INDIRECT trong bài dưới đây.

Hàm INDIRECT là gì?

Hàm INDIRECT trong Excel là hàm tham chiếu gián tiếp, cho phép chuyển một chuỗi văn bản thành địa chỉ ô hoặc vùng dữ liệu để lấy giá trị tương ứng. Thay vì nhập trực tiếp địa chỉ ô vào công thức, bạn có thể sử dụng INDIRECT để tạo tham chiếu linh hoạt dựa trên nội dung của một ô khác.

Hàm này hỗ trợ tham chiếu đến ô, dãy ô hoặc vùng dữ liệu được đặt tên trong cùng một trang tính và giữa các sheet khác nhau. Nhờ đó, INDIRECT giúp công thức dễ dàng thay đổi đối tượng tham chiếu mà không cần chỉnh sửa thủ công từng địa chỉ ô.

Hàm INDIRECT là gì?

Cú pháp của hàm INDIRECT

Hàm INDIRECT trong Excel có cú pháp như sau: =INDIRECT(ref_text, [a1])

Trong đó:

  • ref_text: Đối số bắt buộc, chứa chuỗi văn bản biểu thị địa chỉ ô, vùng dữ liệu hoặc tên vùng đã được thiết lập trong Excel. Đây là thông tin để hàm xác định vị trí cần tham chiếu.
  • a1: Đối số tùy chọn, dùng để xác định kiểu tham chiếu của chuỗi văn bản trong ref_text:
    • TRUE hoặc bỏ trống: Sử dụng kiểu tham chiếu A1 (cột được xác định bằng chữ cái, hàng được xác định bằng số). Đây là thiết lập mặc định của hàm.
    • FALSE: Sử dụng kiểu tham chiếu R1C1 (hàng và cột đều được xác định bằng số).

Cú pháp của hàm INDIRECT

Ứng dụng của hàm INDIRECT trong excel

Tạo tham chiếu INDIRECT từ giá trị ô

Một trong những ứng dụng cơ bản của hàm INDIRECT là chuyển nội dung của một ô thành địa chỉ tham chiếu thực tế. Cách này giúp bạn lấy dữ liệu từ nhiều vị trí khác nhau mà không cần nhập lại địa chỉ ô trong công thức.

Ví dụ: Giả sử ô B2 chứa nội dung A2, trong khi ô A2 có giá trị 100. Tại ô C2, bạn nhập công thức: =INDIRECT(B2)

Kết quả trả về là 100. Excel sẽ đọc nội dung A2 trong ô B2, sau đó truy xuất giá trị tại địa chỉ A2. Nếu thay đổi nội dung B2 thành A3, kết quả cũng tự động cập nhật theo giá trị của ô A3.

Kết hợp INDIRECT với giá trị ô và văn bản

Bạn có thể kết hợp hàm INDIRECT với toán tử nối chuỗi & để tạo địa chỉ ô từ nhiều thành phần khác nhau, chẳng hạn như tên cột và số hàng. Cách này đặc biệt hữu ích khi cần xây dựng tham chiếu dựa trên dữ liệu đầu vào.

Ví dụ: Giả sử ô B2 chứa số 3, bạn muốn lấy giá trị tại ô A3. Công thức sử dụng như sau: =INDIRECT(“A”&B2)

Trong đó:

  • “A” là ký tự xác định cột cần tham chiếu.
  • B2 chứa số hàng cần lấy dữ liệu.
  • Toán tử & kết hợp hai thành phần thành địa chỉ A3.

Kết quả trả về chính là giá trị nằm trong ô A3. Khi thay đổi số trong B2, địa chỉ tham chiếu cũng thay đổi tương ứng.

Kết hợp INDIRECT với giá trị ô và văn bản

Sử dụng INDIRECT với vùng dữ liệu đặt tên

Hàm INDIRECT còn hỗ trợ tham chiếu đến các vùng dữ liệu đã được đặt tên (Named Range) trong Excel. Nhờ đó, bạn có thể sử dụng tên vùng thay cho địa chỉ ô cụ thể, giúp công thức dễ đọc và thuận tiện hơn khi xử lý bảng dữ liệu lớn.

Ví dụ: Giả sử bạn có bảng doanh thu của ba sản phẩm A, B và C. Các vùng dữ liệu tương ứng được đặt tên là SPA, SPB và SPC. Tại ô G1, bạn nhập tên vùng cần tính là SPA.

Để tính tổng doanh thu của sản phẩm A, sử dụng công thức: =SUM(INDIRECT(G1))

Để tính doanh thu trung bình, sử dụng: =AVERAGE(INDIRECT(G1))

Excel sẽ xác định vùng dữ liệu có tên SPA thông qua nội dung ô G1, sau đó thực hiện phép tính tương ứng. Khi thay đổi G1 thành SPB hoặc SPC, kết quả cũng tự động cập nhật theo vùng dữ liệu mới.

Tham chiếu giữa các sheet bằng INDIRECT

Trong trường hợp cần lấy dữ liệu từ nhiều sheet trong cùng một file Excel, bạn có thể kết hợp hàm INDIRECT với toán tử nối chuỗi & để tạo địa chỉ tham chiếu động. Cách này giúp chuyển đổi sheet cần lấy dữ liệu chỉ bằng cách thay đổi tên sheet trong một ô.

Ví dụ: Giả sử bạn có hai sheet tên Thang1 và Thang2, mỗi sheet đều chứa bảng doanh thu tại ô A2. Tại sheet tổng hợp, ô E2 chứa tên sheet cần tham chiếu và ô B2 chứa địa chỉ ô cần lấy dữ liệu.

Bạn nhập công thức: =INDIRECT(“‘”&E2&”‘!”&B2)

Trong đó:

  • E2: Chứa tên sheet cần tham chiếu, chẳng hạn Thang1.
  • B2: Chứa địa chỉ ô cần lấy dữ liệu, chẳng hạn A2.
  • Dấu nháy đơn ‘ giúp công thức xử lý được cả những tên sheet có khoảng trắng.

Kết quả trả về là giá trị tại ô A2 của sheet được chỉ định. Khi thay đổi E2 thành Thang2, Excel sẽ tự động lấy dữ liệu từ sheet mới mà không cần sửa công thức.

Tham chiếu giữa các sheet bằng INDIRECT

Tạo tham chiếu động giữa các file Excel

Ngoài việc tham chiếu dữ liệu trong cùng một file, hàm INDIRECT còn có thể sử dụng để lấy dữ liệu từ một workbook Excel khác. Ứng dụng này phù hợp khi bạn cần tổng hợp số liệu từ nhiều file báo cáo hoặc bảng tính riêng biệt.

Ví dụ: Bạn có file DoanhThu.xlsx, trong đó sheet Thang1 chứa dữ liệu doanh thu tại ô B2. Tại một file Excel khác, bạn có thể sử dụng công thức: =INDIRECT(“‘[DoanhThu.xlsx]Thang1’!B2”)

Công thức sẽ tham chiếu đến ô B2 trong sheet Thang1 của file DoanhThu.xlsx và trả về giá trị tương ứng.

Lưu ý: Hàm INDIRECT chỉ có thể tham chiếu đến workbook bên ngoài khi file nguồn đang mở. Nếu đóng file nguồn, công thức có thể trả về lỗi #REF!. Vì vậy, khi cần liên kết dữ liệu giữa nhiều file Excel, bạn nên cân nhắc điều kiện sử dụng này.

Cố định tham chiếu ô bằng INDIRECT

Hàm INDIRECT có thể kết hợp với địa chỉ tuyệt đối để cố định vị trí ô tham chiếu khi sao chép công thức sang các ô khác. Cách này giúp hạn chế tình trạng địa chỉ tham chiếu tự động thay đổi theo vị trí công thức.

Ví dụ: Giả sử ô A1 chứa tỷ giá quy đổi và bạn muốn sử dụng giá trị này để tính toán cho nhiều dòng dữ liệu. Tại ô C2, nhập công thức: =INDIRECT(“$A$1”)*B2

Trong đó:

  • $A$1 là địa chỉ tuyệt đối, luôn cố định tại ô A1.
  • B2 là ô chứa giá trị cần nhân với tỷ giá.

Khi sao chép công thức xuống các dòng bên dưới, tham chiếu A1 vẫn được giữ nguyên, trong khi B2 tự động thay đổi thành B3, B4,… Điều này giúp bạn sử dụng một giá trị cố định cho nhiều phép tính mà không cần chỉnh sửa từng công thức.

Cố định tham chiếu ô bằng INDIRECT

Kết hợp INDIRECT với các hàm Excel khác

Để mở rộng khả năng xử lý dữ liệu, bạn có thể kết hợp INDIRECT với nhiều hàm Excel khác như SUM, AVERAGE, ADDRESS hoặc COUNTIF.

Một số cách kết hợp phổ biến:

  • INDIRECT và SUM: Tính tổng các giá trị trong vùng dữ liệu được chỉ định. Ví dụ, =SUM(INDIRECT(“B2:B10”)) trả về tổng các giá trị từ ô B2 đến B10.
  • INDIRECT và AVERAGE: Tính giá trị trung bình của một vùng dữ liệu. Ví dụ, =AVERAGE(INDIRECT(“C2:C10”)) tính trung bình các giá trị từ C2 đến C10.
  • INDIRECT và COUNTIF: Đếm số ô thỏa mãn điều kiện trong vùng tham chiếu. Ví dụ, =COUNTIF(INDIRECT(“D2:D10″),”>=100″) đếm số ô có giá trị lớn hơn hoặc bằng 100.
  • INDIRECT và ADDRESS: Tạo địa chỉ ô từ số hàng và số cột, sau đó sử dụng INDIRECT để lấy giá trị tại địa chỉ đó. Ví dụ, =INDIRECT(ADDRESS(4,2)) trả về giá trị tại ô B4.

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

Trong quá trình sử dụng hàm INDIRECT trong Excel, bạn có thể gặp một số lỗi khiến công thức không trả về kết quả như mong muốn. Nguyên nhân thường xuất phát từ địa chỉ tham chiếu không hợp lệ, sai cú pháp hoặc dữ liệu nguồn không tồn tại.

  • Lỗi #REF!: Xảy ra khi địa chỉ ô hoặc vùng dữ liệu trong ref_text không hợp lệ, sheet được tham chiếu đã bị xóa hoặc file Excel nguồn đang đóng. Để khắc phục, hãy kiểm tra lại tên sheet, địa chỉ ô và đảm bảo workbook bên ngoài đang được mở nếu sử dụng tham chiếu liên file.
  • Lỗi #VALUE!: Có thể xuất hiện khi đối số ref_text chứa chuỗi văn bản không phù hợp hoặc vượt quá giới hạn 255 ký tự. Bạn nên kiểm tra nội dung chuỗi tham chiếu, loại bỏ ký tự thừa và đảm bảo địa chỉ được tạo đúng định dạng.
  • Lỗi do sai cú pháp tham chiếu: Khi sử dụng INDIRECT để tham chiếu giữa các sheet, nếu tên sheet có khoảng trắng hoặc ký tự đặc biệt nhưng thiếu dấu nháy đơn, công thức có thể không hoạt động như mong muốn. Hãy đặt tên sheet trong cặp dấu nháy đơn, ví dụ: =INDIRECT(“‘Báo cáo tháng’!A1”).
  • Không cập nhật dữ liệu từ file Excel bên ngoài: Hàm INDIRECT không thể tham chiếu đến workbook bên ngoài khi file nguồn đã đóng. Nếu cần lấy dữ liệu từ các file thường xuyên không mở, bạn có thể cân nhắc sử dụng các công cụ khác như liên kết workbook trực tiếp hoặc Power Query.
  • Tham chiếu sai kiểu A1 và R1C1: Nếu thiết lập đối số thứ hai không đúng với định dạng địa chỉ trong ref_text, Excel có thể không xác định được vị trí cần lấy dữ liệu. Hãy kiểm tra và sử dụng TRUE cho kiểu A1 hoặc FALSE cho kiểu R1C1.

Kết luận

Hàm INDIRECT trong Excel giúp tạo các tham chiếu linh hoạt, hỗ trợ truy xuất dữ liệu và kết hợp với nhiều hàm tính toán khác để tối ưu quá trình xử lý bảng tính. Hy vọng với 7 ứng dụng và những ví dụ minh họa trong bài viết, bạn đã hiểu rõ cách sử dụng hàm INDIRECT.

Nắm được INDIRECT là bước đầu để làm chủ các tham chiếu động — và để vận dụng thành thạo cả bộ công cụ Excel vào phân tích dữ liệu thực tế, khóa học Excel for Business Analytics sẽ hướng dẫn bạn từ công thức nâng cao đến xây dựng báo cáo và dashboard hoàn chỉnh.

Nội dung bài viết được Starttrain biên soạn từ các tình huống thực tế khi làm việc với bảng tính Excel.

Form - đọc sách
Form - Single Report Template

Vui lòng liên hệ qua những thông tin bên dưới. Starttrain sẽ phản hồi bạn trong thời gian sớm nhất.

Form Dashboard service