Tạo tham chiếu với hàm INDIRECT từ A đến Z trong Excel

Hàm INDIRECT là gì? Có thể nói hàm INDIRECT công cụ khá hữu ích để tham chiếu gián tiếp dữ liệu trong Excel nhưng không làm thay đổi công thức trong ô. Tuy nhiên, rất nhiều người lại không biết công thức cũng như cách sử dụng hàm INDIRECT trong Excel thế nào? Hãy cùng tìm hiểu bài viết để giải đáp mọi thắc mắc về hàm INDIRECT trong Excel nhé!

 Hàm INDIRECT là gì?

INDIRECT là hàm được sử dụng để tham chiếu gián tiếp các ô, dãy, các trang tính hoặc các bảng tính trong Excel. Các đối tượng này có thể tồn tại trong cùng một trang tính hoặc trong nhiều trang tính khác nhau. 

Lưu ý: Khi vùng tham chiếu nằm trong một trang tính hoặc bảng tính khác, bạn cần mở bảng tính đó lên để tránh bị lỗi khi nhập hàm.

Với hàm INDIRECT người dùng có thể liên kết dữ liệu một cách nhanh chóng nhưng không làm thay đổi công thức với cú pháp ban đầu của ô.

Công thức hàm INDIRECT là gì? 

Hàm INDIRECT dùng để tham chiếu trong Excel được sử dụng với cú pháp như sau:

=INDIRECT(ref_text, [a1])

Trong đó: 

• ref_text: Là đối tượng tham chiếu bắt buộc phải có trong hàm. Có thể là một ô tham chiếu, hoặc một tham chiếu đến ô. Ở dạng một chuỗi văn bản, hoặc một mảng đã được đặt tên.
• a1: Là giá trị logic xác định kiểu tham chiếu của đối tượng ref_text. Thường được lược bỏ để quá trình sử dụng hàm được đơn giản hơn. Tuy nhiên:

Nếu a1 là TRUE (hoặc bỏ trống) thì ref_text sẽ được hiểu theo A1 (cột-hàng). 

Ví dụ: A1 tham chiếu đến ô tại giao điểm trên cột A và dòng 1.

Nếu a1 là FALSE thì ref_text sẽ được hiểu theo dạng R1C1 (số hàng-số cột).

Ví dụ: R1C1 dùng để chỉ ô trong hàng 1 và thuộc cột 1.

Hướng dẫn cách sử dụng hàm INDIRECT trong Excel

Trong Excel, hàm INDIRECT đóng vai trò quan trọng trong tham chiếu hay liên kết các đối tượng trong bảng. Tuy nhiên, hàm INDIRECT lại có rất nhiều cách sử dụng khác nhau. Để hiểu rõ, các bạn có thể tham khảo các ví dụ dưới đây.

Lưu ý: Các thao tác minh họa đều được thực hiện trên phiên bản Excel 2016. Bạn có thể áp dụng tương tự với các phiên bản Excel khác. 

Dùng hàm INDIRECT tạo tham chiếu trực tiếp từ các ô chứa giá trị

Hàm INDIRECT được dùng trong Excel để tạo tham chiếu trực tiếp từ các ô chứa giá trị. Bạn có thể tham khảo ví dụ dưới đây: 

Ví dụ minh họa: Cho một bảng số liệu như hình. Đề yêu cầu bạn cần trích xuất dữ liệu một cách trực tiếp ô A2. 

Bạn có thể thực hiện với các công thức dưới đây: 

Công thức 1: = INDIRECT (C1)

Ở đây, a1 được để trống, nên ref_text sẽ được hiểu theo kiểu cột-hàng. Cụ thể công thức đang tham chiếu đến ô C1, giá trị của ô tham chiếu chính là chuỗi giá trị A2. 

Mà ô A2 đang có giá trị là 222, do đó ô nhập hàm trả về kết quả cuối là 222. 

Công thức 2: = INDIRECT (C3,False)

Ở đây, a1 được để là False, nên ref_text sẽ được hiểu theo kiểu số hàng-số cột. Cụ thể công thức đang tham chiếu đến ô C3, giá trị của ô tham chiếu chính là chuỗi giá trị R2C1. 

Mà R2C1 tức là hàng 2 - cột 1, là ô A2, do đó ô nhập hàm trả về kết quả cuối là 222. 

Công thức 3: = INDIRECT (C5,False)

Ở đây, a1 được để là False, nên ref_text sẽ được hiểu theo kiểu số hàng-số cột. Cụ thể công thức đang tham chiếu đến ô C5, giá trị của ô tham chiếu chính là chuỗi giá trị R3C. 

Mà R3C tức là hàng 3 - cột 3, là ô R2C1, do đó ô nhập hàm trả về kết quả cuối là 222. 

Bạn có thể áp dụng 3 công thức trên với hàm INDIRECT để trích xuất dữ liệu một cách trực tiếp các ô chứa giá trị.

Dùng hàm INDIRECT tạo tham chiếu gián tiếp từ các giá trị ô và văn bản

Ngoài cách dùng trên, bạn cũng có thể áp dụng hàm INDIRECT để tạo tham chiếu gián tiếp từ văn bản và giá trị ô.

Ví dụ minh họa: Sử dụng hàm INDIRECT để tham chiếu gián tiếp dữ liệu của ô B4, bạn có thể thực hiện theo các bước sau:

Bước 1: Tại ô đầu tiên trong cột Kết Kết quả, bạn nhập công thức: =INDIRECT('B'&C4)

Với: 

• 'B': Là văn bản trong công thức. 
• C4: Là ô tham chiếu có giá trị là 4.

Do đó “B”&C4: tức là tham chiếu đang dẫn đến ô B4.

Bước 2: Khi nhấn phím Enter, kết quả sẽ ra giá trị của ô B4 là 10.

Bước 3: Bạn tiếp tục làm tương tự với các ô còn lại trong cột. Kết quả sẽ thu được như hình dưới đây: 

Lưu ý: Ký tự phải được để trong dấu ngoặc kép mới hợp lệ nhé.

Xem thêm cách tách chữ trong excel

Cách dùng hàm INDIRECT với các vùng được đặt tên

Bạn có thể tạo được tham chiếu cho vùng dữ liệu bằng cách nhập vị trí ô có tên của nó vào công thức INDIRECT. Giả sử với ví dụ trên, ta đặt tên của vùng dữ liệu B4:B8 là SL và gán tên vào ô D4. 

Lưu ý: Tên được gán vào ô cần phải chính xác với tên của vùng dữ liệu đã đặt từ trước.

Để dùng hàm INDIRECT tham chiếu với các vùng dữ liệu SL, bạn có công thức như sau:

INDIRECT(D4)

Sau đó bạn có thể áp dụng công thức hàm INDIRECT này vào các hàm Excel khác để tính toán cho vùng dữ liệu. Ở đây yêu cầu tính tổng, trung bình, GTLN, GTNN, ta lần lượt có:

• Tính tổng của vùng: = SUM (INDIRECT(D4))
• Tính giá trị trung bình của vùng: = AVERAGE (INDIRECT(D4))
• Tìm giá trị lớn nhất trong vùng: = MAX (INDIRECT(D4))
• TÌm giá trị nhỏ nhất trong vùng: = MIN (INDIRECT(D4))

Kết quả sau khi nhấn Enter sẽ là:

Bạn cũng có thể thực hiện tương tự với các phép tính khác bằng hàm trong Excel.

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

Vì tính ứng dụng trong xử lý dữ liệu, hàm INDIRECT là một công cụ rất phổ biến trong Excel. Vậy để hình dung rõ hơn về ứng dụng và cách sử dụng hàm INDIRECT trong Excel, hãy đến với phần nội dung sau đây nhé..

Dùng hàm INDIRECT để tham chiếu tới trang tính khác

Để áp dụng hàm INDIRECT trong tham chiếu tới một trang tính khác. Bạn có thể dùng với công thức sau:

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

Trong đó:

• Sheet's name: Là tên Sheet trong bảng tính.
• Cell to pull data from: Là địa chỉ ô thuộc Sheet's name mà bạn muốn tham chiếu.

Ví dụ minh họa: Cho 2 bảng dữ liệu nằm ở 2 Sheet khác nhau.

Nếu bạn muốn sử dụng hàm INDIRECT để chuyển dữ liệu ở Sheet2 qua Sheet1. Bạn có thể thực hiện như sau:

Bước 1: Tại ô tham chiếu bất kỳ tại sheet 1, bạn nhập =INDIRECT(''&$A$2&'!'&B2).

Với:

• $A$2 (thuộc Sheet1) là Sheet’s name.
• B2(thuộc Sheet1) là Cell to pull data from. Mà dữ liệu ô B2(Sheet1) là A2. Mà giá trị tại ô A2(Sheet2) là 10.

Bước 2: Do đó, khi nhấn Enter, kết quả của ô nhập hàm sẽ là 10.

Bước 3: Tương tự, bạn có thể áp dụng cú pháp với các ô còn lại trong cột.

Bạn đã chuyển thành công dữ liệu từ Sheet2 sang Sheet1 với hàm INDIRECT.

Cùng tìm hiểu về hàm or

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

Để áp dụng hàm INDIRECT trong tham chiếu tới một bảng tính khác. Bạn có thể dùng với công thức sau:

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

Trong đó:

• Book name: Là tên bảng tính bạn muốn tham chiếu.
• 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.

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

Trong Excel, các tham chiếu ô sẽ bị thay đổi khi bạn thêm mới hoặc xóa các hàng hoặc cột hiện có. Khi tình trạng này xảy ra, các dữ liệu thường sẽ bị lỗi và gây bất tiện cho người sử dụng. Do đó, để cố định một tham chiếu ô trong mọi trường hợp bạn cũng có thể sử dụng hàm INDIRECT. Để hình dung chi tiết hơn ta có ví dụ sau.

Ví dụ minh họa: Cho một giá trị bất kỳ nằm trong ô A1. Ta có 2 cách tham chiếu ô:

• Tham chiếu thông thường: =A1
• Tham chiếu với INDIRECT  =INDIRECT (“A1”)

Lúc này, giá trị của ô A1 là 20 do đó, kết quả sẽ bằng 20 ở cả 2 ô tham chiếu.

Nếu bạn thêm hoăc xóa bớt một dòng mới, tức là đang thay đổi vị trí của ô tham chiếu, sẽ có 2 TH xảy ra: 

• Tham chiếu thông thường: Vẫn trả về giá trị 20, vì công thức đã tự thay đổi thành A2.
• Tham chiếu với INDIRECT: Sẽ trả về giá trị 0, vì hàm INDIRECT sẽ cố định công thức, do đó tham chiếu vẫn giữ nguyên đến ô A1. Mà ô A1 trống, Excel sẽ mặc định là 0.

Với chức năng này, bạn có thể áp dụng hàm INDIRECT với nhiều trường hợp muốn cố định tham chiếu khi tính toán. 

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

Ngoài các ứng dụng trên, hàm INDIRECT vẫn thường được sử dụng kết hợp với các ham hàm khác trong Excel khác như ROW, ADDRESS, VLOOKUP, SUMIF, … Bạn có thể tham khảo một vài trường hợp dưới đây nhé.

Với hàm ROW

Thông thường,hàm ROW trong Excel sẽ giúp bạn trả về một mảng các giá trị. Tuy nhiên khi tiến hành chèn thêm một hàng mới trong trang tính, công thức của ROW sẽ bị biến đổi. Do đó, nếu kết hợp hàm ROW và INDIRECT công thức trên sẽ luôn luôn cố định, dù có bao nhiêu dòng được chèn vào hoặc bị xóa.

Ví dụ minh họa: Giả sử để yêu cầu tính trung bình của 3 số nhỏ nhất trong dải A1:A10. 

• Công thức bình thường sẽ là: =AVERAGE(SMALL($A$1:$A$10,ROW(1:3)))

Hàm sẽ xác định trong dải từ A1:A10, ba số nhỏ nhất lần lượt là: 1;2;3

• Tuy nhiên, khi chèn một hàng mới khoảng từ hàng 1 đến 3, công thức sẽ tự thay đổi thành: =AVERAGE(SMALL($A$1:$A$10,ROW(1:4)))

Lúc này, công thức sẽ thay đổi thành tính trung bình 4 số nhỏ nhất trong dải A1:A10, thay vì 3 như ban đầu.

• Để khắc phục tình trạng này xảy ra, hàm INDIRECT sẽ được lồng vào hàm ROW, với công thức: =AVERAGE(SMALL($A$1:$A$10,ROW(INDIRECT('1:3'))))

Khi đó, dù bạn có thêm bao nhiêu hàng vào cũng sẽ không thay đổi yêu cầu tìm trung bình 3 số nhỏ nhất ban đầu.

Lưu ý: Nếu hàm báo lỗi, hãy coi lại cú pháp nhé.

Với hàm ADDRESS

Hàm ADDRESS trong Excel sẽ giúp bạn trả về một địa chỉ ô theo số hàng và cột. Do đó, nếu kết hợp hàm ADDRESS và INDIRECT bạn sẽ có thể để tham chiếu giá trị của ô tính có địa chỉ bất kỳ. 

Ví dụ minh họa: 

Để xác định vị trí của một ô bất kỳ, ta sẽ sử dụng công thức = ADDRESS (1,3)

Tức là vị trí của giao điểm hàng 1 và cột thứ 3.

Do đó, kết quả sẽ trả về chuỗi $C$1. 

Khi kết hợp với hàm INDIRECT bằng công thức: = INDIRECT (ADDRESS (1,3))

Bạn sẽ tham chiếu được địa chỉ chuỗi $C$1. 

Thao tác thực hiện hàm mid

Dùng hàm INDIRECT với danh sách tùy chọn trong Excel

Trong Excel, bạn cũng có thể dùng hàm INDIRECT để tạo các danh sách theo mục dạng xếp chồng lên nhau.Tùy thuộc vào giá trị mà người dùng đã chọn trong danh sách theo mục đầu tiên, các dữ liệu sẽ hiển thị nội dung khác nhau.

Ví dụ minh họa: Cho bảng dữ liệu như sau, để tạo danh sách thả xuống phụ thuộc bằng hàm INDIRECT, ta làm như sau:

Bước 1: Bạn cần đặt tên cho vùng dữ liệu muốn tạo danh sách. Cụ thể ở đây, ví dụ sẽ có 4 vùng được đặt tên:

Bước 2: Tại ô Chọn nhóm, bạn nhấn vào Data và mở bảng Data Validation. Tiếp đó, chọn list và khoanh vùng dữ liệu như hình dưới.

Bạn sẽ thấy ô Chọn nhóm hiện kết quả như sau.

Bước 3: Đối với phần Chọn sản phẩm, bạn cũng chọn và mở bảng Data Validation. Tương tự chọn list nhưng ở phần Source, bạn sẽ tiến hành nhập hàm tham chiếu tới ô Chọn nhóm. 

Với công thức: =INDIRECT($A$1)

Lúc đó, bạn đã có thể sử dụng danh sách tùy chọn phụ thuộc như hình:

Bạn có thể làm tương tự với các mục khác trong bảng.

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

Lỗi #REF!

Khi sử dụng hàm INDIRECT nếu Excel báo lỗi #REF!, có thể do ba trường hợp:

• ref_text trong công thức không phải là một tham chiếu ô hợp lệ.
• ref_text vượt quá giới hạn vùng tham chiếu (cụ thể là 1,048,576 với hàng và 16,384 với cột).

Bạn có thể kiểm tra các đối số của hàm INDIRECT để khắc phục.

• Khi tham chiếu qua trang tham chiếu hoặc một bảng tính khác nhưng không mở song song.

Hãy mở song song các tệp chứa dữ liệu cần tham chiếu.

Lỗi #NAME?

Khi sử dụng hàm INDIRECT nếu Excel báo lỗi #NAME?, có thể do lỗi nhập sai tên của hàm công thức hoặc lỗi ký tự. Bạn có thể khắc phục bằng cách kiểm tra điều chỉnh lại hàm tính.

Kết luận: 

Trên đây là tổng hợp những hướng dẫn cơ bản cách dùng hàm INDIRECT trong Excel. Bạn hoàn toàn có thể tham chiếu dữ liệu một cách dễ dàng và thuận tiện nếu như thao tác đúng các bước trên. Nếu thấy bài viết hay và hữu ích thì đừng quên chia sẻ cho bạn bè để cùng nâng cao hiệu quả khi xử lý dữ liệu trong Excel nhé! 

Đừng quên theo dõi website Điện Thoại Vui để biết thêm nhiều thủ thuật Excel hay và hữu ích nhé!