Hàm INDIRECT trong Excel giúp bạn tham chiếu dữ liệu dễ dàng khi cần. Dưới đây là chi tiết cách dùng hàm INDIRECT trong Excel.

Hàm INDIRECT trong Excel là gì?

Hàm INDIRECT trong Excel trả về một tham chiếu ô hợp lệ từ chuỗi văn bản được cung cấp. INDIRECT hữu ích khi bạn muốn tập hợp một giá trị văn bản có thể được sử dụng làm tham chiếu hợp lệ.

• Mục đích: Tạo một tham chiếu từ văn bản.
• Giá trị trả về: Một tham chiếu worksheet hợp lệ.
• Đối số:
  • ref_text - Tham chiếu được cung cấp dưới dạng text.
  • a1 - (tùy chọn) Một giá trị boolean để chỉ tham chiếu kiểu A1 hay R1C1.
  • Mặc định là TRUE = A1 style.
• Công thức: =INDIRECT(ref_text, [a1])

Lưu ý khi sử dụng hàm INDIRECT trong Excel

Hàm INDIRECT trả về một tham chiếu ô hợp lệ từ một chuỗi text được cung cấp. INDIRECT hữu ích khi bạn cần xây dựng một giá trị text bằng cách nối các chuỗi văn bản riêng biệt mà có thể được phiên dịch thành một tham chiếu ô hợp lệ.

INDIRECT tính hai đối số, ref_text và a1. Ref_text là chuỗi văn bản để đánh giá dưới dạng tham chiếu. A1 chỉ kiểu tham chiếu cho giá trị văn bản sắp tới. Khi a1 là TRUE (giá trị mặc định), kiểu là A1. Khi a1 là FALSE, kiểu là R1C1. Ví dụ:

=INDIRECT("A1") // Trả tham chiếu giống =A1 =INDIRECT("R1C1",FALSE) // Trả tham chiếu giống =R1C1

Mục đích của INDIRECT ban đầu có vẻ khó hiểu nhưng có nhiều tính huống bạn cần tạo một tham chiếu từ văn bản, bao gồm:

• Một công thức tên sheet có thể tùy biến.
• Một công thức có thể tập hợp một tham chiếu ô từ từng đoạn text nhỏ.
• Một tham chiếu cố định sẽ không thay đổi ngay cả khi các hàng hoặc cột bị xóa.
• Tạo các mảng số với hàm ROW trong công thức phức tạp.

Lưu ý: INDIRECT là một hàm không ổn định nên có thể làm chậm tiến độ của bạn khi xử lý các worksheet lớn hoặc phức tạp. Vì thế, hãy dùng nó thật cẩn thận.

Ví dụ 1 - Tên worksheet có thể tùy biến

Ở ví dụ trên, INDIRECT được thiết lập để dùng một tên sheet có thể tùy biến như sau:

=INDIRECT(B5&"!A1") // Tên trang tính trong B6 là biến

Công thức trong B5, được sao chép xuống, nối văn bản trong B5 với chuỗi "!A1" và trả về kết quả là INDIRECT. Hàm INDIRECT sau đó đánh giá văn bản và chuyển đổi nó thành một tham chiếu phù hợp. Kết quả trong C5:C9 là các giá trị từ ô A1 trong 5 trang tính được liệt kê trong cột B.

Ví dụ 2 - Bảng tra cứu có thể tùy biến

Trong worksheet bên dưới, VLOOKUP được dùng để tính chi phí cho hai nhà cung cấp, A và B. Sử dụng nhà cung cấp ở cột F, VLOOKUP tự động dùng bảng chính xác này:

Công thức ở G5 là:

=VLOOKUP(E5,INDIRECT("vendor_"&F5),2,0)

Ví dụ 3: Tham chiếu cố định

Tham chiếu được tạo bởi INDIRECT sẽ không thay đổi ngay cả khi ô, hàng hoặc cột được chèn hoặc xóa. Ví dụ, công thức bên dưới sẽ luôn tham chiếu tới 100 hàng đầu tiên của cột A, ngay cả khi các hàng trong phạm vi đó bị xóa hoặc được chèn:

Hàm Indirect trong excel là gì? Dùng hàm indirect excel bạn đã biết chưa? Yêu cầu bạn áp dụng hàm indirect excel vào xử lý công việc cụ thể nào đó chắc bạn chưa thể làm ngay được, bạn cần làm gì để có thể hiểu nhanh hiểu cơ bản về hàm về cách sử dụng ứng dụng của hàm trong excel. Bạn chỉ cần xem nhanh bài viết hướng dẫn dưới đây, chỉ sau vài phút bạn có thể áp dụng ngay để xử lý công việc của bạn một cách hiệu quả.

Hàm indirect trong excel là gì?

Hàm indirect là gì? Hàm INDIRECT trong Excel là một hàm thuộc nhóm hàm Tham chiếu và Tra cứu. Hàm INDIRECT cho phép bạn tạo một tham chiếu ô hoặc vùng dựa trên một giá trị văn bản. Hàm INDIRECT có thể được sử dụng để tham chiếu đến các ô, các vùng, các vùng đã đặt tên, hoặc các trang tính khác.

Hàm INDIRECT trong Excel là một hàm thuộc nhóm hàm Tham chiếu và Tra cứu

\>>> Xem thêm: Cách sử dụng hàm SUBSTITUTE trong Excel đơn giản, có ví dụ dễ hiểu

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

Hàm INDIRECT có nhiều ứng dụng trong Excel, chẳng hạn như:

- Tạo các tham chiếu động, linh hoạt và có thể điều chỉnh được.

- Tạo các công thức có thể tự động cập nhật khi thêm hoặc xóa các hàng hoặc cột.

- Kết hợp với các hàm khác như SUM, SUMIF, COUNTIF, VLOOKUP, INDEX, MATCH, ADDRESS… để tăng khả năng tính toán và tra cứu.

- Tạo các bảng tính liên kết với nhau một cách dễ dàng và hiệu quả.

Hàm INDIRECT có nhiều ứng dụng trong Excel

Cấu trúc hàm indirect trong excel

Công thức indirect trong excel như sau:

\=INDIRECT(ref_text,[a1])

Trong đó:

- ref_text: là một giá trị văn bản chứa thông tin về tham chiếu ô hoặc vùng. Ví dụ: “A1”, “B2:C5”, “Sheet1!A1”, “NamedRange”…

- a1: là một giá trị logic (TRUE hoặc FALSE) xác định kiểu tham chiếu của ref_text. Nếu a1 là TRUE (hoặc bỏ trống), ref_text sẽ được hiểu theo kiểu A1 (dạng hàng-cột). Nếu a1 là FALSE, ref_text sẽ được hiểu theo kiểu R1C1 (dạng số hàng-số cột).

Cấu trúc hàm INDIRECT

Cách sử dụng hàm indirect trong excel

Trong phần này, chúng ta sẽ xem xét cách dùng hàm INDIRECT trong Excel với các ví dụ cụ thể.

1. Tạo các tham chiếu INDIRECT từ các ô chứa giá trị

Ví dụ: Cho bảng dữ liệu:

Yêu cầu: Dùng hàm INDIRECT để tham chiếu gián tiếp dữ liệu trong bảng dữ liệu vừa cho.

Cách thực hiện:

- Bước 1: Ở ô D4, bạn nhập lệnh indirect trong excel =INDIRECT(C4).

Nhập công thức vào ô D4

- Bước 2: Nhấn enter, Excel sẽ trả về kết quả là 8. Nhấn đúp chuột để sao chép công thức xuống bên dưới sẽ thu được bảng kết quả như hình:

2. Tạo các tham chiếu gián tiếp từ các giá trị ô và văn bản

Ví dụ: Cho bảng dữ liệu:

Yêu cầu: Dùng hàm INDIRECT để tham chiếu gián tiếp dữ liệu trong bảng dữ liệu vừa cho.

Cách thực hiện:

- Bước 1: Đặt con trỏ chuột vào ô D4, nhập công thức tính là =INDIRECT("B"&C4).

Nhập công thức hàm INDIRECT vào ô D4

Trong đó:

- INDIRECT: Là lệnh hàm.

- "B": Là ký tự dùng để chỉ cột B trong bảng tính.

- C4: Là giá trị liên kết với chữ B để tạo ra ô tham chiếu truy xuất kết quả.

- Bước 2: Nhấn enter sẽ thu được kết quả là 10.

- Bước 3: Copy công thức xuống các ô bên dưới sẽ thu được bảng kết quả là:

3. Sử dụng hàm INDIRECT với các vùng đã đặt tên

Ví dụ: Cho bảng dữ liệu:

Yêu cầu: Dùng công thức hàm INDIRECT để tham chiếu gián tiếp với các vùng dữ liệu đã đặt tên trong bảng dữ liệu vừa cho.

Cách thực hiện hàm indirect trong excel:

- Bước 1: Nhập công thức tương đương như trong hình vào ô G5 đến G8.

Nhập công thức vào ô G5 đến G8

- Bước 2: Nhấn enter sẽ thu được kết quả như sau:

Các ứng dụng của hàm INDIRECT

Trong phần này, chúng ta sẽ xem xét một số ví dụ minh họa cho các ứng dụng của hàm INDIRECT trong Excel.

1. Công thức INDIRECT – tự động tham chiếu đến một trang tính khác

Nếu muốn dùng hàm INDIRECT để tự động tham chiếu đến một trang tính khác, bạn cần dùng cấu trúc hàm là:

\=INDIRECT("" & Sheet’s name & "!" & Cell to pull data from)

Giải thích công thức hàm indirect trong excel:

- INDIRECT: Là lệnh hàm.

- "", & và !: Là những ký tự liên kết các giá trị trong hàm.

- Sheet's name: Là tên Sheet tính trong bảng tính.

- Cell to pull data from: Là tên cột bạn muốn gọi dữ liệu.

Ví dụ: Cho bảng dữ liệu với tên gọi là My Sheet.

Yêu cầu: Dùng hàm INDIRECT để tham chiếu gián tiếp dữ liệu trong bảng dữ liệu đã cho.

Cách thực hiện:

- Bước 1: Nhập hàm =INDIRECT("'"&$A$1&"'!"&B1) vào ô C4.

Nhập công thức hàm INDIRECT

Trong đó:

- INDIRECT: Là lệnh hàm

- "", &, !: Là những ký tự liên kết các giá trị trong hàm.

- A1: Là ô tham chiếu chứa tên Sheet muốn gọi.

- B1: Là ô tham chiếu chứa cột dữ liệu muốn gọi.

- Bước 2: Nhấn enter, ô C4 sẽ trả về kết quả là 76. Kéo thả chuột xuống các ô bên dưới sẽ thu được kết quả như hình sau:

2. Tạo một tham chiếu động đến một bảng tính khác

Nếu muốn dùng hàm INDIRECT để tạo một tham chiếu động đến một bảng tính khác, bạn cần dùng cấu trúc hàm là:

\=INDIRECT("[" & Book name & "]" & Sheet name & "!" & Cell address)

Giải thích công thức:

- INDIRECT: Là lệnh hàm.

- "", & và !: Là những ký tự liên kết các giá trị trong hàm.

- Book name: Là tên của bảng tính bạn muốn gọi.

- Sheet name: Là tên Sheet tính trong bảng tính.

- Cell address: Là tên cột bạn muốn gọi dữ liệu.

Ví dụ: Cho bảng dữ liệu với tên gọi là My Sheet.

Yêu cầu: Dùng công thức lệnh INDIRECT để tham chiếu gián tiếp dữ liệu trong bảng dữ liệu đã cho.

Cách thực hiện:

- Bước 1: Ở ô D4, bạn nhập công thức:

\=INDIRECT("("&$A$4&")"&$B$4&"!"&C4).

Nhập công thức INDIRECT

- Bước 2: Nhấn enter, ô D4 sẽ trả về kết quả là 1002.

- Bước 3: Kéo thả chuột xuống bên dưới sẽ thu được bảng kết quả như sau:

3. Sử dụng hàm INDIRECT để cố định một tham chiếu ô

Ví dụ: Cho bảng dữ liệu như hình:

Yêu cầu: Dùng hàm INDIRECT để cố định dải tham chiếu dữ liệu trong bảng dữ liệu đã cho.

Cách thực hiện hàm indirect trong excel:

- Bước 1: Nhập công thức =SUM(INDIRECT("$B$5:$B$7")) vào ô D5.

Nhập công thức vào D5

- Bước 2: Nhấn enter, Excel sẽ trả về kết quả là 51 như hình dưới đây:

Kết hợp hàm indirect và một số hàm khác

Trong phần này, chúng ta sẽ xem xét cách kết hợp hàm INDIRECT và một số hàm khác trong Excel để tạo các công thức phức tạp và linh hoạt hơn.

1. Hàm indirect kết hợp address

Hàm ADDRESS trong Excel là một hàm thuộc nhóm hàm Tham chiếu và Tra cứu. Hàm ADDRESS cho phép bạn trả về một giá trị văn bản chứa thông tin về tham chiếu ô hoặc vùng dựa trên số hàng, số cột, kiểu tham chiếu, và tên trang tính. Hàm ADDRESS có cấu trúc như sau:

\=ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])

Trong đó:

- row_num: là số hàng của tham chiếu ô hoặc vùng.

- column_num: là số cột của tham chiếu ô hoặc vùng.

- abs_num: là một giá trị số xác định kiểu tham chiếu của hàng và cột (tùy chọn). Có thể là một trong các giá trị sau:

+ 1 hoặc bỏ trống: trả về kiểu tham chiếu tuyệt đối (dạng $A$1).

+ 2: trả về kiểu tham chiếu tương đối cho hàng và tuyệt đối cho cột (dạng A$1).

+ 3: trả về kiểu tham chiếu tuyệt đối cho hàng và tương đối cho cột (dạng $A1).

+ 4: trả về kiểu tham chiếu tương đối (dạng A1).

- a1: là một giá trị logic (TRUE hoặc FALSE) xác định kiểu tham chiếu của ref_text. Nếu a1 là TRUE (hoặc bỏ trống), ref_text sẽ được hiểu theo kiểu A1 (dạng hàng-cột). Nếu a1 là FALSE, ref_text sẽ được hiểu theo kiểu R1C1 (dạng số hàng-số cột) (tùy chọn).

- sheet_text: là một giá trị văn bản chứa tên của trang tính có chứa tham chiếu ô hoặc vùng (tùy chọn).

Bài tập hàm indirect trong excel: Cho bảng dữ liệu:

Yêu cầu: Kết hợp hàm ADDRESS và INDIRECT để tham chiếu giá trị của ô tính có địa chỉ bất kỳ.

Cách làm:

- Bước 1: Tại ô C1, bạn nhập hàm tính là =INDIRECT(ADDRESS(4;2)).

- Bước 2: Nhấn enter sẽ thu được kết quả là 12.

Kết quả sau khi nhập công thức

2. Sử dụng hàm indirect trong excel với các hàm AVERAGE, SMALL và ROW

Trong trường hợp bạn cần thêm/xóa dòng, cột trong bảng tính Excel mà không muốn làm thay đổi kết quả tính toán thì bạn nên kết hợp hàm INDIRECT với các hàm khác.

Ví dụ: Cho bảng dữ liệu sau:

Yêu cầu:

Cách thực hiện:

- Bước 1: Ở ô kết quả, bạn nhập công thức sau:

\=AVERAGE(SMALL(B5:B9,ROW(INDIRECT("1:3")))).

Nhập công thức hàm AVERAGE

Trong đó:

- AVERAGE, SMALL, ROW và INDIRECT: Là lệnh hàm.

- B5:B9: Là dải tham chiếu.

- ("1:3"): Là điều kiện lấy giá trị thỏa mãn.

- Bước 2: Nhấn tổ hợp phím Ctrl + Shift + Enter, Excel sẽ trả về kết quả là 2.

Kết quả sau nhập công thức

3. Sử dụng hàm INDIRECT với hàm SUM

Trong excel bạn không chỉ gặp những công việc có sử dụng nguyên chỉ hàm indirect mà thường sẽ áp dụng hàm indirect kết hợp với một số hàm khác như hàm tính tổng Sum.

Kết hợp 2 hàm bạn sẽ giải quyết được cho một công việc tính tổng nhanh chóng mà không cần phải chọn mảng dữ liệu cụ thể, bạn có thể thấy rõ qua ví dụ dưới đây:

Dùng hàm Indirect kết hợp hàm Sum (1)

Trong ví dụ trên đây, có dữ liệu của 3 cột với từng mục khác nhau, để có thể tính tổng số mũ mà sử dụng việc kết hợp 2 hàm trên thì đầu tiên việc bạn làm đó là chọn vùng dữ liệu từ ô C2 đến C6 và đặt tên ở phần name box là Mũ:

Dùng hàm Indirect kết hợp hàm Sum (2)

Và chỉ cần nhập công thức của kết hợp 2 hàm là: =SUM(INDIRECT(F1)) và ngay sau đó hàm sẽ tính tổng theo dạng tham chiếu. Và bạn thực hiện tương tự với 2 cột bên cạnh.

\>>> Xem thêm: Hướng dẫn cách cố dịnh hàm trong excel đơn giản, nhanh chóng

Một số lỗi khi dùng hàm INDIRECT

Khi dùng hàm INDIRECT trong Excel, bạn có thể gặp phải một số lỗi thường gặp như sau:

1. Lỗi

REF!

Lỗi

REF! xảy ra khi tham chiếu ô hoặc vùng không tồn tại hoặc không hợp lệ. Ví dụ:

- =INDIRECT(“A0”) sẽ trả về lỗi

REF! bởi vì không có ô A0 trong bảng tính.

- =INDIRECT(“Z1000”) sẽ trả về lỗi

REF! bởi vì ô Z1000 ngoài phạm vi của bảng tính (giới hạn là XFD1048576).

- =INDIRECT(“Sheet3!A1”) sẽ trả về lỗi

REF! bởi vì không có trang tính nào có tên là Sheet3 trong bảng tính.

Để khắc phục lỗi này, bạn cần kiểm tra lại giá trị văn bản của tham số ref_text và đảm bảo rằng nó là một tham chiếu ô hoặc vùng hợp lệ và tồn tại.

Lỗi thường gặp khi dùng hàm INDIRECT

2. Lỗi

NAME?

Lỗi

NAME? xảy ra khi giá trị văn bản của tham số ref_text không được nhận dạng là một tham chiếu ô hoặc vùng. Ví dụ:

- =INDIRECT(“Hello”) sẽ trả về lỗi

NAME? bởi vì Hello không phải là một tham chiếu ô hoặc vùng.

- =INDIRECT(A1) sẽ trả về lỗi

NAME? nếu ô A1 chứa một giá trị không phải là một tham chiếu ô hoặc vùng. Ví dụ, nếu ô A1 chứa giá trị 100, công thức sẽ trả về lỗi

NAME?.

Để khắc phục lỗi này, bạn cần kiểm tra lại giá trị văn bản của tham số ref_text và đảm bảo rằng nó là một tham chiếu ô hoặc vùng hợp lệ và được nhận dạng bởi Excel.

Lỗi thường gặp khi dùng hàm INDIRECT

Kết luận

Vậy qua bài hướng dẫn và giới thiệu cơ bản về học Excel online từ cơ bản đến nâng cao chi tiết hàm bạn đã biết cách sử dụng hàm cho công việc của mình chưa? Thực tế thì cách dùng hàm indirect trong excel để tạo các tham chiếu động, linh hoạt và có thể điều chỉnh được, cách kết hợp hàm INDIRECT với các hàm khác để tăng khả năng tính toán và tra cứu, và cách khắc phục một số lỗi thường gặp khi dùng hàm INDIRECT. Hy vọng bài viết này sẽ giúp bạn hiểu rõ hơn và áp dụng hàm INDIRECT một cách hiệu quả trong các bảng tính của mình.