Hướng dẫn sử dụng hàm INDIRECT trong Excel kèm ví dụ chi tiết

Giới thiệu và hướng dẫn chi tiết cách sử dụng hàm INDIRECT trong Excel kèm ví dụ cụ thể và chi tiết. Tham khảo ngay để nâng cao kiến thức Excel của bạn.

Hàm INDIRECT trong Excel là gì?

Hàm INDIRECT là gì trong Excel: được sử dụng tham chiếu gián tiếp cho các ô, dãy, bảng tính hoặc trang tính. Khi sử dụng hàm INDIRECT, các bạn có thể tạo được một ô chứa tham chiếu động hoặc một mảng chứa tham chiếu trong Excel. Điều này khác với việc bạn “mã hóa cố định” các tham chiếu vào công thức Excel bởi vì bạn có thể thay đổi một tham chiếu ô trong công thức mà không làm thay đổi nó. Các tham chiếu INDIRECT sẽ không thay đổi khi bạn chèn thêm hoặc xóa các cột, hàng trong Excel.

Cú pháo của hàm INDIRECT gồm 2 đối số như sau:

INDIRECT (ref_text, [a1])

Trong đó:

• Ref_text: 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 mà đã được đặt tên.
• a1: Là một giá trị logic xác định loại tham chiếu của đối số ref_text:
• Nếu TRUE hoặc bỏ qua, thì ref_text được hiểu như một tham chiếu ô VD: A1 – tham chiếu ô.
• Nếu FALSE, ref_text được coi như tham chiếu R1C1.

Mặc dù kiểu tham chiếu R1C1 có thể hữu ích trong những tình huống nhất định nhưng sử dụng các tham chiếu A1 quen thuộc hơn.

Chức năng cơ bản của hàm INDIRECT trong Excel

Để giúp các bạn hình dung rõ hơn về hàm indirect là hàm gì và các sử dụng hàm indirect trong Excel thì chúng ta sẽ đi vào một ví dụ cụ thể.

Giả sử bây giờ có số 3 trong ô A1 và văn bản "A1" trong ô C1. Lúc này bạn thử nhập công thức =INDIRECT (C1) trong ô tính bất kỳ thì sẽ thấy 2 điều sau:

• Hàm INDIRECT tham chiếu đến giá trị trong ô C1, là "A1".
• Công thức này đồng thời cũng lấy được giá trị trả về là "3" ở trong ô A1.

Điều này có nghĩa là hàm INDIRECT đã chuyển đổi một chuỗi văn bản thành một ô tham chiếu.

Để thành thạo 150+ hàm và các chức năng Excel từ cơ bản đến nâng cao giúp bạn tự tin tính toán và xử lý dữ liệu phức tạp, hãy tham khảo khóa học sau của Gitiho:

 

Làm thế nào để sử dụng INDIRECT trong Excel?

Trong ví dụ trên, các bạn đã thấy rằng hàm INDIRECT trong Excel có thể đặt địa chỉ của một ô vào một ô khác như một chuỗi văn bản, lấy giá trị của ô thứ nhất bằng cách tham chiếu qua ô thứ hai. Đây chỉ là một ứng dụng nhỏ của hàm này thôi nhé. Hàm INDIRECT trong Excel vẫn còn nhiều ứng dụng khác rất thú vị.

Khi bạn làm việc với dữ liệu thực, hàm này có thể biến mọi chuỗi văn bản thành tham chiếu. Dù chuỗi văn bản của bạn có phức tạp đến đâu thì vẫn có thể sử dụng các giá trị của ô khác và kết quả trả về bởi công thức Excel khác. Chúng ta sẽ đi vào tìm hiểu từng ứng dụng cụ thể nhé:

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

Ở phần trên, chúng mình đã nói rằng hàm INDIRECT có thể tham chiếu theo các kiểu A1 và R1C1. Thông thường, chúng ta sẽ không sử dụng được cả 2 kiểu trong 1 trang cùng lúc được. Thay vào đó, chúng ta sẽ chuyển đổi giữ 2 kiểu tham chiếu. Cách chuyển đổi là: 

Vào File => chọn Options => chọn mục Formula => chọn R1C1

Vì phải trải qua thêm một bước nên người dùng Excel ít khi nghĩ đến việc dùng kiểu tham chiếu R1C1. Vậy 2 kiểu tham chiếu A1 và R1C1 có gì khác nhau?

Điểm khác biệt giữa chúng là:

• A1: Là kiểu thông dụng, quen thuộc với người dùng Excel, liên quan đến một cột theo sau bởi một số hàng. Ví dụ: B2 tham chiếu đến ô tại giao điểm trên cột B và dòng 2.
• R1C1: Là kiểu tham chiếu ngược lại với A1, liên quan đến các hàng theo sau bởi các cột. Ví dụ: R4C1 dùng để chỉ ô A4 trong hàng 4, cột 1 trong một bảng tính. Nếu phía sau chữ cái không có con số nào thì có nghĩa là các bạn đang tham chiếu đến cùng hàng hoặc cột.

Hình ảnh dưới đây mà cách làm hàm INDIRECT xử lý các tham chiếu A1 và R1C1:

Trong hình ảnh này, các bạn thấy cả 3 công thức với hàm INDIRECT đều trả về kết quả tương tự nhau. Chúng ta sẽ cùng phân tishc một chút nhé:

• Công thức trong ô D1: =INDIRECT(C1)

Công thức này đang tham chiếu đến ô C1, lấy chuỗi giá trị "A2" của nó, chuyển nó thành một tham chiếu ô. Sau đó khi tham chiếu qua ô A2 thì trả về giá trị là 222.

• Công thức trong ô D3: =INDIRECT(C3, FALSE)

FALSE trong đối số thứ 2 chỉ ra rằng giá trị được tham chiếu (C3) là tham chiếu ô kiểu R1C1, tức là các hàng được theo sau bởi cột. Do đó, công thức INDIRECT của chúng tôi diễn giải giá trị trong ô C3 (R2C1) như một tham chiếu đến ô tại kết hợp của hàng 2 và cột 1, đó là ô A2.

• Công thức trong ô D5: =INDIRECT(C5, FALSE)

FALSE trong đối số thứ hai ở công thức này, có nghĩa là nó diễn giải giá trị trong đối 
số đầu tiên như một tham chiếu kiểu R1C1. Vì vậy, công thức sẽ lấy giá trị trong ô C5 (R3C) và biến nó thành địa chỉ ô D3 (hàng 3 trong cột đó) và tiếp tục như trên.

Tuy nhiên, nếu giá trị trong ô C5 là R3C3 chứ không phải là R3C, cùng một công thức sẽ trả lại giá trị trong ô C3 (R2C1) mà không đi thêm nữa.

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

Chúng ta có thể kết hợp được một chuỗi văn bản với một tham chiếu ô trong công thức INDIRECT nhờ ký tự đặc biệt (&).

Ví dụ: Công thức  =INDIRECT (“B” & C2) trả về giá trị từ ô B2 dựa trên cơ sở logic như sau:

Hàm INDIRECT trong Excel kết nối các phần tử trong đối số ref_text là văn bản “B” và giá trị trong ô C2 là số 2, tạo ra tham chiếu ô là B2 => công thức đi đến ô B2 và trả về giá trị của nó là "10".

Xem thêm: Cách sử dụng hàm AGGREGATE - một trong những hàm Excel mạnh nhất

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

Chúng ta có thể dùng hàm INDIRECT nâng cao để tham chiếu đến các vùng dữ liệu đã được đặt tên. Giả sử trong bảng tính của bạn có 3 vùng được đặt tên như sau:

• Apples – B2:B6
• Bananas – C2:C6
• Lemons – D2:D6

Chúng ta có thể tạo được tham chiếu động cho bất kỳ vùng dữ liệu nào ở trên chỉ bằng cách nhập tên của nó vào một số ô. Giả sử là G1 thì tham chiếu ô đó từ công thức INDIRECT: = INDIRECT (G1).

Sau đó bạn có thể áp dụng công thức hàm INDIRECT trong excel này vào các hàm Excel khác để tính toán cho dải dữ liệu:

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

Xem thêm: Hướng dẫn cách dùng hàm SUMIF trong Excel có ví dụ cụ thể

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

Giả sử bạn có Bảng 1 với nhiều dữ liệu quan trọng và bạn muốn kéo chúng sang bảng 2 thì hàm INDIRECT hoàn toàn có thể làm được:

Bây giờ hãy cùng phân tích công thức trong hình ảnh trên theo từng thành phần nhỏ nhé:

Thông thường, khi chúng ta tham chiếu một trang tính khác trong Excel thì sẽ ghi sau tên của bảng một dấu chấm tham hoặc một tham chiếu ô/dải như: SheetName! Range. Vì một tên trang thường chứa một dấu cách nên chúng ta sẽ phải thêm nó vào trong dấu nháy đơn để tránh lỗi. Ví dụ: ‘My Sheet!‘ $A$1.

Bây giờ bạn chỉ cầm nhập tên bảng trong ô, địa chỉ ô thì nhập vào ô khác rồi nối chúng với nhau trong một chuỗi văn bản. Bạn chỉ cần lưu ý rằng trong một chuỗi văn bản, đi kèm mỗi phân tử luôn phải có địa chỉ hoặc số của ô trong ngoặc kép và liên kết tất cả các yếu tố với nhau bằng cách sử dụng "&". Sau đó các bạn đưa nó vào hàm INDIRECT là chúng ta sẽ có cấu trúc như sau:

INDIRECT(“‘” & Sheet’s name & “‘!” & Cell to pull data from)

Quay lại ví dụ trên, chúng ta đặt tên cho bảng trong ô A1 sau đó gõ các địa chỉ vào cột B thì công thức sẽ có dạng:

INDIRECT (“‘” & $A$1 & “‘!” & B1)

Lưu ý:

• Nếu phải sao chép công thức vào nhiều ô, chúng ta phải cố định tham chiếu đến trang tính bằng các sử dụng các tham chiếu ô tuyệt đối như $A$1.
• Nếu một trong hai ô chứa tên và địa chỉ của trang thứ 2 (A1 và B1 trong công thức trên) là rỗng, thì công thức INDIRECT của bạn sẽ trả về lỗi. Để xử lý lỗi này, chúng ta sẽ lồng hàm INDIRECT trong hàm IF theo công thức sau:

IF (OR ($A$1 = “”, B1 = “”), “”, INDIRECT (“‘” & $A$1 & “‘!” & B1))

• Đối với công thức INDIRECT được tham chiếu đến trang khác thì cần mở tham chiếu lên để nó hoạt động chính xác thay vì hiện lỗi #REF. Để hạn chế và sửa lỗi #REF trong Excel, bạn có thể sử dụng chức năng IFERROR, nó sẽ hiển thị một chuỗi rỗng dù có lỗi nào hay không;

IFERROR (INDIRECT (“‘” & $A$1 & “‘!” & B1), “”)

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

Hàm INDIRECT trong Excel có thể được dùng để tham chiếu đến bảng tính khác bằng cách làm tương tự như với trang tính. Bạn chỉ cần xác định tên của bảng tính và địa chỉ ô là được nhé.

Để làm việc này dễ dàng hơn, chúng ta sẽ tạo tham chiếu cho một bảng tính khác theo cách thông thường dấu nháy được thêm vào trong trường hợp bảng tính và / hoặc tên bảng của bạn có chứa dấu cách):

‘[Book_name.xlsx] Sheet_name’!Range

Giả sử rằng tên sách nằm trong ô A2, tên bảng là B2 và địa chỉ ô trong C2, chúng ta lấy công thức sau:

=INDIRECT (“‘[” & $A$2 & “]” & $B$2 & “‘!” & C2)

Nếu không muốn các ô chứa tên của bảng tính và trang tính thay đổi khi sao chép công thức vào các ô khác thì chúng ta sẽ cố định chúng bằng cách sử dụng tham chiếu ô tuyệt đối, tương ứng là $A$2 và $B$2.

Bây giờ việc viết tham chiếu động của riêng bạn vào một bảng tính Excel khác trở nên dễ dàng hơn với công thức sau:

=INDIRECT(“‘[” & Book name & “]” & Sheet name & “‘!” & Cell address)

Lưu ý: Hãy đảm bảo rằng bảng tính mà công thức bạn tham chiếu đến luôn luôn được mở, nếu không thì sẽ bị hiện lỗi #REF. Để tránh lỗi này, chúng ta cũng có thể dùng chức năng IFERROR với công thức sau:

=IFERROR (INDIRECT (“” [“& A2 &”] “& $A$1 &” ‘! “& B1),” “)

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

Thông thường, khi bạn chèn hoặc xóa các hàng, cột thì Excel sẽ tự động thay đổi tham chiếu ô. 

Ví dụ: Đầu tiên các bạn hãy thử làm theo các bước sau:

1. Nhập bất kỳ giá trị nào vào ô bất kỳ, ví dụ, số 20 trong ô A1.
2. Tham chiếu A1 từ hai ô khác bằng nhiều cách khác nhau: =A1 và =INDIRECT (“A1”)
3. Chèn một hàng mới phía trên hàng 1.

Indirect là hàm gì ? Hãy cùng Gitiho tìm hiểu về cách chèn thêm hàng giá trị của ô được gán giá trị ô khác, vẫn trả về 20, vì công thức của nó đã được tự động thay đổi thành =A2. Ô chứa công thức INDIRECT lại trả về kết quả bằng 0, tức là công thức không thay đổi khi chèn thêm hàng và vẫn tham chiếu đến ô A1 đang để trống:

Đừng vì những điều bạn thấy trong ví dụ trên mà vội đánh giá hàm INDIRECT Excel không hữu ích. Chúng ta sẽ chuyển qua một ví dụ khác ngay để đánh giá một cách chính xác hơn.

Ví dụ: Bạn muốn tính tổng trong dải A2:A5 thì cách thông thường mà tất cả mọi người đều sử dụng là dùng hàm SUM với công thức =SUM (A2: A5).

Tuy nhiên, công thức này không thể cố định được tham chiếu khi bạn thêm hoặc xóa hàng, cột. Nhiều bạn sẽ nghĩ đến việc thêm dấu $ đế cố định thì sẽ thấy công thức chuyển thành dạng = SUM ($A$2:$A$6).

Nếu không muốn công thức tự động thay đổi thì chúng ta lồng INDIRECT vào hàm SUM là được nhé. Công thức cụ thể của ví dụ này là: =SUM (INDIRECT (“A2: A5”)). Lúc này Excel sẽ nhận diện rằng "A1:A5" là một chuỗi văn bản nên sẽ không có bất kỳ thay đổi nào khi chèn thêm hoặc xóa bớt các cột, hàng.

Xem thêm: Cách dùng hàm IF nhiều điều kiện: AND, OR, hàm IF lồng nhau và hơn thế

Sử dụng hàm INDIRECT với các hàm Excel khác

Ngoài việc lồng hàm INDIRECT vào hàm SUM như ở ví dụ trên thì chúng ta có thể sử dụng cùng hàm khác như ROW, COLUMN, ADDRESS, VLOOKUP, SUMIF. Hãy cùng nhìn qua một vài ví dụ nhé.

Ví dụ 1: Hàm INDIRECT và hàm ROW

Chúng ta thường sử dụng hàm ROW để trả về một mảng các giá trị. Ví dụ: Công thức sau đây được sử dụng để  trả lại trung bình của 3 số nhỏ nhất trong dải A1: A10 (cần phải nhấn Ctrl + Shift + Enter):

=AVERAGE(SMALL (A1: A10, ROW (1: 3)))

Lúc này nếu chèn thêm một hàng mới vào khu vực giữa hàng 1 và hàng 3 trong bảng tính của bạn thì dải công thức sẽ thay đổi thành ROW (1:4) và thu về kết quả trung bình của 4 con số nhỏ nhất thay vì 3 như ban đầu.

Thay vào đó chúng ta lồng thêm công thức INDIRECT vào hàm ROW thì dải công thức sẽ được cố định, không thay đổi khi chèn thêm hàng, cột:

=AVERAGE(SMALL(A1: A10, ROW (INDIRECT (“1: 3”)))))

Ví dụ 2: Hàm INDIRECT và hàm ADDRESS

Hàm ADDRESS trong Excel thường được sử dụng để lấy địa chỉ một ô theo số hàng và cột. Ví dụ, công thức = ADDRESS (1,3) trả về chuỗi $C$1 vì C1 là ô ở giao điểm của hàng 1 và cột thứ 3.

Chúng ta có thể lồng thêm hàm INDIRECT vào với hàm này thì sẽ có công thức như sau:

=INDIRECT (ADDRESS (1,3))

Ví dụ 3: Dùng hàm INDIRECT với danh sách tùy chọn trong Excel

Chúng có thể kết hợp hàm INDIRECT và danh sách tùy chọn để tạo ra các danh sách theo mục dạng xếp chồng lên nhau để hiển thị các lựa chọn khác 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.

Trong danh sách tùy chọn của bạn sẽ cần có một vài dải ô được đặt tên để lưu trữ thành các mục bằng công thức đơn giản là: =INDIRECT (A2) với điều kiện A2 là ô chứa danh sách tùy chọn của bạn.

Xem thêm: Hướng dẫn cách tạo danh sách thả xuống trong Excel

Lỗi thường gặp và cách xử lý khi dùng hàm INDIRECT trong Excel

3 lỗi khiến INDIRECT hiển thị kết quả là #REF

Cũng như các hàm Excel khác thì khi bạn sử dụng INDIRECT cũng sẽ gặp một vài rắc rối nhỏ. Hàm INDIRECT khó đánh giá vì ô mà nó tham chiếu đến không phải là vị trí cuối cùng của giá trị được sử dụng trong công thức. Điều này sẽ gây khó khăn khi bảng tính của bạn có những công thức dài và phức tạp. Ngoài ra, nếu bạn lạm dụng các đối số của hàm thì có thể sẽ gặp các lỗi sau:

1. ref_text không phải là một tham chiếu ô hợp lệ: Vì nếu tham số ref_text trong công thức INDIRECT của bạn không phải là tham chiếu ô hợp lệ, thì công thức sẽ cho kết quả là #REF! Giá trị lỗi. Để tránh các vấn đề có thể xảy ra, hãy kiểm tra các đối số của hàm INDIRECT.
2. Lỗi vượt quá giới hạn dải: Nếu đối số ref_text của công thức INDIRECT của bạn tham chiếu đến một loạt các ô vượt quá giới hạn của hàng là 1,048,576 hoặc giới hạn cột là 16,384, bạn cũng sẽ gặp lỗi #REF trong Excel 2007, 2010 và Excel 2013. Các phiên bản Excel cũ thì đã bỏ qua quá mức giới hạn và trả lại một số giá trị, mặc dù kết quả này thường không phải là cái mà bạn mong đợi.
3. Một trang tham chiếu hoặc một bảng tính đã được đóng lại: Nếu công thức INDIRECT của bạn tham chiếu đến một bảng tính hoặc một tính Excel khác, thì bảng tính / trang tính khác phải được mở, nếu không thì INDIRECT trả về #REF! lỗi.

INDIRECT #NAME? lỗi

Đây là trường hợp rõ ràng nhất, ngụ ý rằng có một số lỗi trong tên của hàm công thức.

Lỗi khi dùng hàm INDIRECT với Excel không có tiếng Anh

Có thể có trường hợp bạn cần dùng hàm INDIRECT nhưng phần mềm lại không dùng tiếng Anh thì làm thế nào? Vậy thì các bạn chỉ cần đối chiếu với bảng quy đổi hàm INDIRECT theo ngôn ngữ này thôi nhé:

• Đan Mạch – INDIREKTE
• Tiếng Phần Lan – EPÄSUORA
• Tiếng Đức – INDIREKT
• Hungarian – INDIREKT
• Ý – INDIRETTO
• Na Uy – INDIREKTE
• Ba Lan – ADR.POŚR
• Tiếng Tây Ban Nha – INDIRECTO
• Thụy Điển – INDIREKT
• Thổ Nhĩ Kỳ – DOLAYLI

Khi ngôn ngữ trên phần mềm Excel không phải tiếng Anh thì hàm này không còn là INDIRECT nữa mà sẽ phụ thuộc vào Regional Settings (thiết lập khu vực) khác nhau cho List Separator. Thồng thường, với cấu hình Windows chuẩn ở khu vực Bắc Mỹ và một số quốc gia khác thì List Separator mặc định là một dấu phẩy; trong khi đó ở  châu Âu thì lại là dấu chấm phẩy bởi vì dấu phẩy được giữ như biểu thức thập phân (Decimal Symbol).

Lúc này, nếu các bạn sao chép một công thức giữa hai vị trí Excel khác nhau, bạn có thể nhận được thông báo lỗi “We found a problem with this data…” bởi vì List Separator được sử dụng trong công thức sẽ khác với những gì được đặt trên máy của bạn. Nên nếu bạn gặp lỗi này khi sao chép một số công thức INDIRECT trong bài viết này thì chỉ cần thay dấu phẩy (,) bằng dấu chấm phẩy (;) là được nhé.

Để kiểm tra Dấu tách Danh sách và Ký hiệu thập phân được đặt trên máy tính của bạn, hãy mở Bảng điều khiển và đi Region and Language > Additional Settings nhé.

Kết luận

Hy vọng bài hướng dẫn của chúng mình có thể giúp các bạn sử dụng thành thạo được hàm INDIRECT trong Excel thành thạo hơn. Nếu các bạn muốn được học nhiều hơn về các hàm trong Excel thì hãy đăng ký ngay khóa học:

Khóa học sẽ cung cấp cho bạn hệ thống kiến thức từ cơ bản đến nâng cao về các công cụ, hàm và phương pháp làm việc chủ động, hiệu quả trong Excel. Các bạn sẽ được giảng viên hỗ trợ, giải đáp mọi thắc mắc trong vòng 24h xuyên suốt khóa học. Khóa học cũng tặng kèm nhiều Ebook và tài liệu độc quyền của Gitiho cho học viên. Các bạn hãy đăng ký ngay để làm chủ phần mềm Excel, nâng cao hiệu quả công việc nhé.