Cách dùng hàm INDIRECT trong Excel để tham chiếu dễ hiểu nhất

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ả.

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

1. 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

2. Ứ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

3. 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

>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"

ĐĂNG KÝ MUA NGAY

4. 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ể.

4.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:

4.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à:

ket-qua.jpg?

4.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:

5. 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.

5.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:

5.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:

5.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:

6. 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.

6.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).

- 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

6.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 hàm small và row hoặc average.

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

6.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.

7. 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:

7.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

7.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

8. 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.

Chúc các bạn mọi ngày làm việc hiệu quả!