Excel có vô số hàm thú vị và cũng bao hàm khá nhiều hàm tương đối khó cùng với những người tiêu dùng thông thường. Hàm Indirect là một trong những hàm kha khá khó, tuy nhiên hiểu được cách áp dụng hàm để biệt này, các bạn sẽ tiết kiệm được rất nhiều thời gian khi thao tác làm việc với các bảng tính tinh vi của mình. 

Vậy, hàm Indirect áp dụng trong trường thích hợp nào cùng cú pháp của nó là gì? nội dung bài viết này để giúp đỡ bạn trả lời thắc mắc trên.Bạn vẫn xem: Hàm indirect trong data validation

Cú pháp hàm INDIRECT vào Excel

Hàm Indirect trong Excel dùng để tham chiếu trực tiếp đến các ô, những vùng, các sheet và workbook khác. Chúng ta sẽ sử dụng các giá trị trả về của hàm Indirect vào trong phương pháp của mình. Các ô, vùng được tham chiếu trong hàm Indirect trọn vẹn linh động. Bởi vì trong công thức bao gồm sử dụng add được tham chiếu vì chưng hàm Indirect, địa chỉ cửa hàng ô tham chiếu trong hàm Indirect sẽ không bị chuyển đổi khi bạn chèn thêm dòng, cột vào worksheet như công thức thường được sử dụng trong Excel.

Bạn đang xem: Cách dùng hàm indirect trong data validation

Cú pháp hàm INDIRECT trong Excel

Hàm Indirect có hai đối số, đối số đầu tiên bắt buộc, đối số thiết bị hai tùy chọn

= INDIRECT (ref_text, )

 ref_text: có giá trị chuỗi của địa chỉ ô mà hàm Indirect tham chiếu đến. Đối số cơ mà ref_text nhận được rất có thể là 1 ô mà giá trị của ô đó là một địa chỉ một ô, vùng trong bao gồm work sheet đó, worksheet khác hoặc trong workbook khác. Hoặc ref_text có thể nhận đối số là một trong chuỗi cơ mà giá trị của chuỗi sẽ là một showroom một ô, vùng trong thiết yếu work sheet đó, worksheet không giống hoặc trong workbook kháca1: sở hữu giá trị ngắn gọn xúc tích với 2 tùy chọn. True hoặc bỏ qua trong hàm Indirect thì ref_text được coi là tham chiếu tất cả dạng A1. Hoặc False lúc ref_text là tham chiếu có dạng A1C1

Kiểu tham chiếu R1C1 thật sự hữu ích trong một trong những trường hợp. Tuy nhiên, hầu hết trường hợp chúng ta sử dụng là hình dạng tham chiếu A1. Các ví dụ hướng dẫn dưới đây sử dụng hình trạng tham chiếu A1. Vày thế, trong các ví dụ bên dưới, bọn họ sẽ làm lơ đối số trang bị hai. 

Sử dụng hàm INDIRECT như thế nào

Trước lúc chèn hàm Indirect vào bảng tính, bạn cần phải có một vài giá trị như sau: 

Đặt vào ô A1 giá trị số là 3Ở ô C1, chúng ta chèn vào quý hiếm chuỗi “A1”Tại ô D1 hoặc một ô bất kỳ, bạn chèn cách làm = INDIRECT (C1). Bạn sẽ nhận được kết quả trả về trên ô D1 là 3. 

 

*

Như bạn thấy trong ảnh chụp screen ở trên, cha công thức con gián tiếp khác biệt trả về cùng một kết quả. Chúng ta đã tìm kiếm ra nguyên nhân tại sao chưa?

Công thức tại ô D1: = INDIRECT (C1)

Đây là kiểu dáng tham chiếu A1, kiểu dễ dàng nhất. Công thức tại ô D1 sẽ trỏ mang đến ô C1 với nhận quý hiếm chuỗi “A2” trên ô C1. Quý giá chuỗi này đã được biến đổi thành ô đề nghị tham chiếu là ô A2 và lấy quý hiếm tại ô A2 trả về cho cách làm tại ô D1. Như vậy, ô D1 sẽ sở hữu giá trị trả về là 222

Công thức trên ô D3: = INDIRECT(C3, FALSE)

Đây là loại tham chiếu R1C1. Đối số lắp thêm hai trong cách làm mang cực hiếm FALSE. Điều này cho biết rằng quý hiếm của ô C3 sẽ là 1 trong những ô rất cần phải tham chiếu, bao gồm dạng R1C1. Trong lấy ví dụ này, ô C3 có giá trị là “R2C1”. Như vậy, ô rất cần phải tham chiếu là giao của dòng thứ 2 và cột trước tiên trong bảng tính. Đó là ô A2. Công dụng trả về 222 đến ô D3 là cực hiếm tại ô A2. 

Công thức tại ô D5: = INDIRECT(C5, FALSE)

Cũng hệt như ví dụ tại ô D3, bí quyết tại ô D5 triển khai theo thứ hạng tham chiếu R1C1. Vị vậy mà bạn thấy đối số thiết bị hai có mức giá trị FALSE và quý giá tại ô C5 có dạng R1C1 của ô yêu cầu tham khảo. Như phía dẫn bên trên, lúc số cột được bỏ qua mất trong đối số dạng R1C1 thì số cột mặc nhiên sẽ thừa nhận chỉ số trùng cùng với số dòng. Do vậy, cực hiếm tại ô C5 là “R3C” thì ô được tham chiếu đến sẽ sở hữu được dạng R3C3, là giao của chiếc thứ 3 cùng cột sản phẩm công nghệ 3. Đó là ô C3. Quý giá tại ô C3 là “R2C1”. Trên điểm này, các bạn sẽ có một điều thú vị buộc phải ghi nhớ

Nếu quý hiếm tại ô C5 là “R3C”, ô được tham chiếu đến là ô C3. Cực hiếm tại ô C3 là “R2C1” sẽ liên tiếp được đưa thành một ô được tham chiếu mang đến ở mẫu 2, cột một là ô A2. Hiệu quả trả về 222 cho ô D5 là quý hiếm tại ô A2

Nếu quý hiếm tại ô C5 là “R3C3”, ô được tham chiếu đến vẫn luôn là ô C3. Giá trị tại ô C3 là “R2C1” sẽ là hiệu quả trả về mang đến ô D5. 

Tạo tham chiếu gián tiếp từ quý hiếm ô và văn bản

Cũng hệt như cách tạo hàm Indirect tham chiếu cho giá trị của một ô thì thời gian này, bạn có thể kết phù hợp một chuỗi và một ô được tham chiếu vào bí quyết của hàm Indirect. Vết “&” được dùng làm kết phù hợp 2 đối tượng người tiêu dùng này. 


*

Tại ô G2, hàm Indirect nhận quý hiếm “Lemons” trên ô G1 và trỏ cho vùng vẫn đặt thương hiệu Lemons là D2:D6. Trên đây, hàm Sum được thực hiện và trả về công dụng cho ô G2 là tổng những số trong vùng Lemons. Phương pháp được tiến hành tương tự trên ô G3, G4, G5. 

Tạo hàm INDIRECT tham chiếu mang đến một trang tính khác

Hàm Indirect còn có thể tham chiếu đến những ô trên sheet khác. Để tiến hành ví dụ, các bạn sẽ đặt các dữ liệu đề nghị lấy trên Sheet 2, đánh tên Sheet 2 là “My Sheet”. Cách làm chứa hàm Indirect được đặt ở Sheet 1. 


*

Khi tìm hiểu thêm đến dữ liệu tại một Sheet không giống trong Excel, cú pháp được viết có Tên_Sheet!Ô (hay vùng) được tham chiếu. Thông thường, tên Sheet bao gồm dấu cách khoảng tầm như “My Sheet”. Mặc dù nhiên, bạn nên được đặt tên Sheet không tồn tại khoảng phương pháp để tránh những lỗi xảy ra không nên thiết. Lấy ví dụ My_Sheet!A1 nhằm chỉ mang đến ô A1 của Sheet mang tên My_Sheet.

Trong trường hợp bạn áp dụng tên Sheet tất cả dùng khoảng tầm cách, các bạn nên áp dụng thêm dấu nháy 1-1 để kị lỗi xảy ra. Đây là cách chúng ta đưa chuỗi và mang lại đối số ref_text của hàm Indirect: 

Ở Sheet hiện nay hành, Sheet 1, chúng ta đặt chuỗi “My Sheet” vào ô A1 với gõ vào ô C1 công thức sử dụng hàm Indirect: 

C1:=INDIRECT(“ ’ ”&$A$1&” ’!”&B1)

Kết trái trả về tại ô C1 của Sheet 1 là giá trị của ô A1 trên Sheet “My Sheet”. Ngoài ra, hãy xem xét rằng nếu như khách hàng đang coppy công thức vào các ô, chúng ta phải khóa tham chiếu mang lại tên trang tính bằng cách sử dụng tham chiếu ô hoàn hảo nhất như $A$1.

Xem thêm: Điểm Danh Những Tác Hại Của Việc Bỏ Bữa Sáng Khiến Bạn Phải Suy Ngẫm

Lưu ý:

Nếu một trong số ô cất tên và địa chỉ cửa hàng ô của trang tính thứ hai (A1 cùng B1 trong bí quyết trên) bị trống, công thức Gián tiếp của bạn sẽ trả về lỗi. Để tránh điều này, bạn cũng có thể bọc hàm INDIRECT vào hàm IF : IF(OR($A$1="",B1=""), "", INDIRECT(""" và $A$1 và ""!" và B1))Để bí quyết INDIRECT tham chiếu cho trang tính khác chuyển động chính xác, trang tính được reviews phải được mở, nếu không công thức đã trả về lỗi #REF. Để kiêng lỗi, chúng ta có thể sử dụng hàm IFERROR , hàm này đang hiển thị một chuỗi trống, ngẫu nhiên lỗi làm sao xảy ra: IFERROR(INDIRECT(""" & $A$1 & ""!" &B1), "")

Tạo hàm INDIRECT tham chiếu cho một Workbook khác

Sử dụng hàm Indirect để tham chiếu mang đến một Workbook khác cũng như sử dụng hàm Indirect để tham chiếu đến một Worksheet khác. Điều duy nhất đề nghị làm là thêm thương hiệu Workbook vào trước tên Worksheet. Để tiến hành việc tham chiếu này tiện lợi hơn, bạn nên được đặt tên Workbook với Worksheet không có dấu cách.

 "Sheet_name"!Range

Công thức sử dụng hàm Indirect tham chiếu đến một Workbook khác có dạng: 

=INDIRECT(""" và $B$2 và ""!" & C2)

Vì chúng ta không muốn các ô đựng tên sách với trang tính biến hóa khi xào nấu công thức sang các ô khác, các bạn khóa chúng bằng phương pháp sử dụng tham chiếu ô tuyệt đối hoàn hảo , tương xứng là $A$2 cùng $B$2.


Tên Workbook bạn đặt ở ô A2, thương hiệu Worksheet bạn đặt tại ô B2, ô bạn cần trỏ mang đến trong Workbook khác là ô A1, bạn đặt chuỗi A1 ngơi nghỉ ô C2. Công thức sử dụng hàm Indirect đặt ở ô D2:= INDIRECT(“’” và $B$2 &”’!” và C2). Công dụng trả về 111 tại ô D2 là quý hiếm tại ô A1 của Sheet có tên “My Sheet” thuộc Workbook mang tên “My Book”. 

Chú ý: Workbook được tham chiếu đến bắt buộc trong trình trạng đang mở nếu như không thì hàm Indirect đang báo lỗi

Địa chỉ ô được tham chiếu vào hàm INDIRECT là cố định

Thông thường, khi bạn sử dụng công thức trong Excel nhằm tham chiếu mang đến 1 ô và khi chúng ta chèn thêm một dòng hay là một cột thì địa chỉ ô được tham chiếu trong công thức của Excel cũng tự động được thế đổi. Để điều đó không xảy ra khi chúng ta sử dụng hàm Indirect để tham chiếu cho một ô, hầu như việc bạn phải làm như sau: 

Bạn đã nhập giá chỉ trị ngẫu nhiên vào một ô. Ví dụ các bạn gõ trăng tròn vào ô A1Bạn tham chiếu cho ô A1 bằng cách thông thường xuyên tại ô C1:= A1 và bởi hàm Indirect tại ô C2:=INDIRECT(“A1”). Các bạn thử chèn thêm một dòng vào phía trên của mẫu số 1

Điều gì xảy ra? phương pháp tại ô C1 sẽ được đẩy xuống ô C2:=A2 và cực hiếm tại ô C2 vẫn chính là 20. Cách làm tại ô C2 được đẩy xuống ô C3:=INDIRECT(“A1”) và quý giá tại ô C3 là 0. Điều này có nghĩa là công thức cần sử dụng hàm Indirect sẽ không biến hóa khi chúng ta chèn thêm 1 dòng.


Có lẽ hàm Indirect đang thể hiện tác dụng của mình rõ ràng hơn khi dùng để làm tham chiếu mang đến vùng. Ví dụ các bạn sẽ viết SUM ($A$2:$A$5) sẽ đổi khác thành SUM($A$2:$A$6) khi chúng ta chèn thêm một dòng mới vào dòng xoáy số 3. 

Sử dụng hàm INDIRECT với những hàm ROW, ADDRESS

Ngoài hàm SUM, hàm Indirect còn được áp dụng cùng với các hàm ROW, ADDRESS, VLOOKUP, SUMIF. Sau đó là một vài ba ví dụ

Hàm INDIRECT cùng hàm ROW

Thông thường, vào Excel hàm Row được dùng để trả về số dòng trong một mảng. Ví dụ bạn phải tính quý giá trung bình của 3 giá trị nhỏ dại nhất trong vùng A1:A10