Hàm indirect trong data validation

Excel tất cả vô số hàm thú vị với cũng bao gồm tương đối nhiều hàm tương đối cực nhọc cùng với đầy đủ người dùng thông thường. Hàm Indirect là 1 trong trong số những hàm tương đối cực nhọc, tuy nhiên phát âm được biện pháp áp dụng hàm đặt biệt này, bạn sẽ tiết kiệm được không ít thời hạn Khi làm việc cùng với những bảng tính phức tạp của mình. 

Vậy, hàm Indirect sử dụng vào ngôi trường hợp làm sao và cú pháp của chính nó là gì? Bài viết này sẽ giúp chúng ta trả lời thắc mắc bên trên.

You watching: Hàm indirect trong data validation

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

Hàm Indirect trong Excel dùng làm tmê say chiếu trực sau đó những ô, các vùng, những sheet cùng workbook không giống. Chúng ta sẽ áp dụng những quý giá trả về của hàm Indirect vào vào công thức của chính mình. Các ô, vùng được tmê mệt chiếu vào hàm Indirect trọn vẹn biến hóa năng động. Bởi vày trong bí quyết tất cả thực hiện can dự được tyêu thích chiếu vày hàm Indirect, xúc tiến ô tsi chiếu trong hàm Indirect vẫn không bị thay đổi khi chúng ta chèn thêm mẫu, cột vào worksheet nlỗi phương pháp thường được sử dụng vào Excel.

Cú pháp hàm INDIRECT trong Excel

Hàm Indirect có nhì đối số, đối số trước tiên đề nghị, đối số thứ nhị tùy chọn

= INDIRECT (ref_text, )

 ref_text: Mang cực hiếm chuỗi của liên can ô mà hàm Indirect tsi mê chiếu đến. Đối số mà ref_text nhận ra có thể là một ô mà cực hiếm của ô kia là một cửa hàng một ô, vùng vào thiết yếu work sheet kia, 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 mà lại giá trị của chuỗi đó là 1 liên quan một ô, vùng trong chính work sheet đó, worksheet khác hoặc trong workbook kháca1: Mang quý hiếm logic với 2 tùy lựa chọn. True hoặc làm lơ vào hàm Indirect thì ref_text được xem là tmê mẩn chiếu bao gồm dạng A1. Hoặc False khi ref_text là tđắm say chiếu bao gồm dạng A1C1

Kiểu tmê mẩn chiếu R1C1 thật sự có lợi vào một vài ngôi trường hợp. Tuy nhiên, số đông trường đúng theo họ thực hiện là loại tham chiếu A1. Các ví dụ hướng dẫn tiếp sau đây sử dụng hình trạng tsay đắm chiếu A1. Vì nỗ lực, trong những ví dụ bên dưới, họ vẫn bỏ qua đối số lắp thêm nhì. 

Sử dụng hàm INDIRECT như vậy nào

Trước Khi ckém hàm Indirect vào bảng tính, chúng ta cần phải có một vài ba giá trị nlỗi sau: 

Đặt vào ô A1 quý hiếm số là 3Ở ô C1, chúng ta ckém vào quý hiếm chuỗi “A1”Tại ô D1 hoặc một ô bất kỳ, chúng ta cnhát bí quyết = INDIRECT (C1). quý khách hàng sẽ cảm nhận kết quả trả về trên ô D1 là 3. 

 

*

Hàm INDIRECT tại ô D1 thừa nhận cực hiếm chuỗi và gồm dạng liên hệ ô bắt buộc ttê mê chiếu là A1 trên ô C1. Hàm INDIRECT sẽ trỏ mang lại ô A1, lấy giá trị trên ô A1 là 3 với trả kết quả về tại ô D1. D1 nhấn kết quả trả về là 3. 

bởi thế, hàm INDIRECT đã gửi một cực hiếm chuỗi “A1” thành một tmê mệt chiếu đến ô gồm ảnh hưởng là A1. 

Lúc này, các bạn chưa thật sự nhìn thấy những điều độc đáo với lôi kéo của hàm INDIRECT. Điều trước tiên chúng ta thấy đam mê ở hàm INDIRECT trong ví dụ là chính là sự thay đổi từ bỏ một số lịch sự shop ô đề nghị tđê mê chiếu. 

Vậy ứng dụng hàm INDIRECT như thế nào? Ở phần tiếp sau của hướng dẫn này, họ sẽ mày mò một số ví dụ thực tiễn về hàm INDIRECT trong excel, nhằm bạn cũng có thể phát âm vì sao bây giờ, hàm INDIRECT lại được sử dụng những trong so với dữ liệu.

Cách sử dụng hàm INDIRECT qua ví dụ gắng thể

Nhỏng hầu hết gì tôi sẽ nói, nội dung bài viết này chỉ thật sự có lợi khi chúng ta đọc không còn phần ví dụ. Sau lúc hiểu hết phần ví dụ, các bạn sẽ đọc sức mạnh của hàm INDIRECT. Có thể tiếp đến, các bạn sẽ tất cả thêm nhiều ý tưởng rộng để thực hiện hàm INDIRECT vào các bước hằng ngày của mình.

Tạo tsi mê chiếu gián tiếp từ bỏ những quý giá ô

Nlỗi vẫn trình diễn ở bên trên, hàm INDIRECT chất nhận được ttê mê chiếu cho thúc đẩy một ô, một vùng bằng 2 mẫu mã ttê mê chiếu A1 với R1C1. 

Có một điều bạn cần phải ghi hãy nhờ rằng hàm INDIRECT chỉ được cho phép các bạn sử dụng 1 trong những 2 hình dáng tham chiếu là A1 hoặc R1C1 trên thuộc một thời điểm, trong cùng một Sheet. Bạn biến đổi hình dáng tyêu thích chiếu bằng cách vào File > Options > Formulas > R1C1 kiểm tra box

Đây là lý do vì sao người tiêu dùng không nhiều tiếp cận hàm INDIRECT theo phong cách tsay mê chiếu R1C1. lúc bạn phải sử dụng mang đến đẳng cấp tmê man chiếu này, chúng ta bắt buộc vào tùy chọn của “Working with formulas” nhằm biến hóa. 

Trong và một cách làm có thực hiện hàm INDIRECT, bạn vẫn có thể dùng tđắm đuối chiếu dạng hình A1 hoặc R1Cmột trong và một sheet nếu như bạn muốn. Excel không thể báo lỗi khi chúng ta lthực hiện thao tác này. 

Trước khi bạn tò mò sâu rộng về hàm Indirect, bạn nên biết một chút ít về sự khác biệt giữa kiểu tsay mê chiếu A1 cùng hình dạng tsay mê chiếu R1C1

Tsay đắm chiếu giao diện Amột là mẫu mã tsi chiếu được dùng thông dụng trong Excel. Kiểu tham mê chiếu này đã trỏ mang lại 1 cột và theo sau là chỉ số của một loại nó được cho phép tđam mê chiếu đến một ô là giao của cột cùng dòng. lấy một ví dụ B2 là ô sẽ được tham mê chiếu, là giao của cột B và chiếc thứ 2. Nghĩa là tmê say chiếu hình trạng A1 sẽ trỏ mang đến cột B trước rồi kế tiếp new đánh giá cái theo sau nó là dòng số 2. Tsi mê chiếu thứ hạng R1Cmột là mẫu mã tmê man chiếu ngược lại cùng với đẳng cấp ttê mê chiếu A1. Chỉ số chiếc sẽ được trỏ đến cùng theo sau là chỉ số cột. Kiểu tđắm đuối chiếu này chỉ nhiều khi mới được áp dụng. Ví dụ R4C1 sẽ được tham chiếu bằng phương pháp trỏ mang đến cái số 4 cùng theo sau là cột số một trong các bảng tính. Vì vậy, ô A4 sẽ tiến hành tđắm say chiếu cho theo phong cách tsi mê chiếu R1C1. Nếu chỉ số chiếc theo sau bị làm lơ thì điềm nhiên ô được tsi chiếu mang lại vẫn là ô bao gồm cùng số chiếc, số cột và đó sẽ là ô D4. 

Để nắm rõ hơn, vui lòng quan lại sát ví dụ sau

*

Nhỏng chúng ta thấy vào hình họa chụp màn hình ngơi nghỉ bên trên, bố phương pháp Gián tiếp không giống nhau trả về cùng một tác dụng. Quý khách hàng đang đưa ra nguyên do tại sao chưa?

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

Đây là phong cách tmê say chiếu A1, giao diện dễ dàng và đơn giản tuyệt nhất. Công thức tại ô D1 vẫn trỏ mang đến ô C1 cùng dấn quý giá chuỗi “A2” trên ô C1. Giá trị chuỗi này sẽ được biến hóa thành ô đề nghị tsi chiếu là ô A2 và lấy giá trị tại ô A2 trả về mang lại phương pháp tại ô D1. bởi vậy, ô D1 sẽ có được giá trị trả về là 222

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

Đây là mẫu mã tđắm đuối chiếu R1C1. Đối số thứ nhị vào công thức với quý hiếm FALSE. Vấn đề này cho thấy rằng quý giá của ô C3 sẽ là 1 ô cần phải tmê mẩn chiếu, bao gồm dạng R1C1. Trong ví dụ này, ô C3 có giá trị là “R2C1”. bởi vậy, ô rất cần phải tđắm say chiếu là giao của cái thứ hai và cột trước tiên vào bảng tính. Đó là ô A2. Kết trái trả về 222 cho ô D3 là cực hiếm tại ô A2. 

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

Cũng y hệt như ví dụ trên ô D3, bí quyết tại ô D5 tiến hành theo phong cách tham mê chiếu R1C1. Vì vậy cơ mà các bạn thấy đối số lắp thêm nhì có mức giá trị FALSE cùng quý giá trên ô C5 gồm dạng R1C1 của ô đề nghị xem thêm. Nlỗi lí giải trên, lúc số cột được làm lơ vào đối số dạng R1C1 thì số cột thản nhiên vẫn thừa nhận chỉ số trùng với số cái. Vì vậy, quý hiếm tại ô C5 là “R3C” thì ô được tsay mê chiếu mang lại sẽ có được dạng R3C3, là giao của dòng đồ vật 3 với cột sản phẩm 3. Đó là ô C3. Giá trị trên ô C3 là “R2C1”. Tại điểm đó, các bạn sẽ bao gồm một điều thú vui đề xuất ghi nhớ

Nếu giá trị trên ô C5 là “R3C”, ô được tmê say chiếu đến là ô C3. Giá trị tại ô C3 là “R2C1” đã liên tục được chuyển thành một ô được tsay mê chiếu mang đến làm việc chiếc 2, cột một là ô A2. Kết trái trả về 222 cho ô D5 là quý hiếm tại ô A2

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

Tạo tmê mệt chiếu loại gián tiếp trường đoản cú quý hiếm ô với vnạp năng lượng bản

Cũng giống hệt như giải pháp tạo thành hàm Indirect tmê mẩn chiếu cho cực hiếm của một ô thì từ bây giờ, chúng ta có thể kết hợp một chuỗi với một ô được tsay đắm phản vào phương pháp của hàm Indirect. Dấu “&” được dùng để làm kết hợp 2 đối tượng người tiêu dùng này. 

*

Dễ dàng nhận ra hàm Indirect vào cách làm này được tham chiếu theo phong cách A1. Đối số ref_text nhận được là chuỗi “B” kết phù hợp với giá trị tại ô C2 là “2” được chuỗi “B2”. Chuỗi B2 này sẽ tiến hành biến đổi thành ô cần tsay đắm chiếu như ta sẽ làm ở các ví dụ trên. Ô B2 được tham mê chiếu mang lại với trả về hiệu quả là 10 cho bí quyết trên ô D2. 

Tạo hàm INDIRECT tmê mệt chiếu đến vùng được đặt tên

Khi áp dụng hàm Indirect, đối số ref_text được trao quý giá xuất phát từ 1 ô, một chuỗi thì ref_text còn hoàn toàn có thể nhấn name ranges (thương hiệu vùng). 

Để triển khai ví dụ sau, các bạn hãy đánh tên vùng như sau: 

Apples – B2:B6Bananas – C2:C6Lemons – D2:D6

Tên các vùng này sẽ được đặt theo thứ tự vào ô G1 nhằm theo dõi với thực hiện các bí quyết tất cả sử dụng hàm Indirect trên những ô G2, G3, G4, G5. Để tạo ra tđắm đuối chiếu động Excel mang đến bất kỳ phạm vi được lấy tên làm sao làm việc trên, chỉ việc nhập tên của chính nó vào một số trong những ô, chẳng hạn G1 và tham chiếu cho ô đó xuất phát từ 1 công thức Gián tiếp =INDIRECT(G1).

See more: Attention Required! - Thay Pin Samsung S8 Plus Giá Rẻ

Và hiện thời, bạn cũng có thể thực hiện một bước xa rộng và ghép bí quyết INDIRECT này vào những hàm Excel không giống nhằm tính tổng và vừa phải của các quý giá trong một phạm vi được lấy tên nhất mực hoặc tra cứu cực hiếm lớn nhất / nhỏ dại nhất

G2:= SUM (INDIRECT (G1))

G3:= AVERAGE (INDIRECT (G1))

G4:= MAX (INDIRECT (G1))

G5:= MIN (INDIRECT (G1))

*

Tại ô G2, hàm Indirect dìm quý hiếm “Lemons” trên ô G1 cùng trỏ mang đến vùng sẽ viết tên Lemons là D2:D6. Tại trên đây, hàm Sum được tiến hành cùng trả về công dụng đến ô G2 là tổng các số trong vùng Lemons. Công thức được triển khai giống như trên ô G3, G4, G5. 

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

Hàm Indirect còn có thể ttê mê chiếu cho những ô trên sheet không giống. Để tiến hành ví dụ, bạn sẽ đặt các dữ liệu buộc phải rước trên Sheet 2, đặt tên Sheet 2 là “My Sheet”. Công thức đựng hàm Indirect được đặt ở Sheet 1. 

*

lúc tham khảo mang đến dữ liệu tại 1 Sheet khác trong Excel, cú pháp được viết gồm Tên_Sheet!Ô (giỏi vùng) được tmê mệt chiếu. thường thì, tên Sheet tất cả lốt giải pháp khoảng chừng nlỗi “My Sheet”. Tuy nhiên, các bạn nên được đặt tên Sheet không tồn tại khoảng chừng phương pháp để tách các lỗi xẩy ra không cần thiết. lấy ví dụ như My_Sheet!A1 để chỉ mang lại ô A1 của Sheet mang tên My_Sheet.

Trong ngôi trường hòa hợp bạn thực hiện tên Sheet gồm dùng khoảng cách, chúng ta nên áp dụng thêm vết nháy đơn nhằm tránh lỗi xảy ra. Đây là bí quyết các bạn đưa chuỗi và mang đến đối số ref_text của hàm Indirect: 

Tại Sheet hiện nay hành, Sheet 1, bạn đặt chuỗi “My Sheet” vào ô A1 với gõ vào ô C1 cách làm thực hiện hàm Indirect: 

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

Kết trái trả về trên ô C1 của Sheet 1 là cực hiếm của ô A1 trên Sheet “My Sheet”. Ngoài ra, hãy lưu ý rằng nếu bạn đang xào luộc công thức vào những ô, chúng ta phải khóa tsi chiếu mang lại tên trang tính bằng phương pháp sử dụng tmê say chiếu ô hoàn hảo nlỗi $A$1.

Lưu ý:

Nếu một trong các ô cất thương hiệu cùng can hệ ô của trang tính thứ 2 (A1 với B1 trong các cách làm trên) bị trống, bí quyết Gián tiếp của các bạn sẽ trả về lỗi. Để tránh điều đó, 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à ""!" & B1))Để phương pháp INDIRECT ttê mê chiếu cho trang tính khác vận động đúng mực, trang tính được reviews đề xuất được msinh sống, nếu không phương pháp đã trả về lỗi #REF. Để tránh lỗi, chúng ta có thể sử dụng hàm IFERROR , hàm này đã hiển thị một chuỗi trống, ngẫu nhiên lỗi nào xảy ra: IFERROR(INDIRECT(""" & $A$1 và ""!" &B1), "")

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

Sử dụng hàm Indirect để tham chiếu đến một Workbook khác cũng như thực hiện hàm Indirect để tđê mê chiếu cho một Worksheet không giống. Điều tốt nhất yêu cầu làm là thêm tên Workbook vào trước tên Worksheet. Để tiến hành việc tmê mệt chiếu này thuận tiện hơn, bạn nên được sắp xếp thương hiệu Workbook và Worksheet không có vệt cách.

See more: Uống Nước Mía Có Béo Không, 1 Ly Nước Mía Bao Nhiêu Calo

 "Sheet_name"!Range

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

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

Vì chúng ta không thích những ô đựng tên sách cùng trang tính biến hóa lúc sao chép phương pháp sang trọng những ô không giống, các bạn khóa bọn chúng bằng cách áp dụng tyêu thích chiếu ô hoàn hảo , tương xứng là $A$2 và $B$2.

*

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

Chụ ý: Workbook được tsi mê chiếu mang lại đề xuất trong trình trạng sẽ msống nếu không thì hàm Indirect đã báo lỗi

Địa chỉ ô được tsay mê chiếu trong hàm INDIRECT là cố kỉnh định

Đôi khi, khi chúng ta thực hiện cách làm trong Excel để tyêu thích chiếu mang lại 1 ô và khi chúng ta cnhát thêm một cái hay 1 cột thì can dự ô được tmê say chiếu trong bí quyết của Excel cũng auto được biến đổi. Để điều này ko xảy ra khi chúng ta thực hiện hàm Indirect nhằm tmê say chiếu mang đến một ô, phần lớn vấn đề bạn cần phải làm nlỗi sau: 

quý khách hàng đang nhập quý hiếm bất kỳ vào một trong những ô. lấy ví dụ như các bạn gõ đôi mươi vào ô A1Quý khách hàng tmê say chiếu cho ô A1 bằng phương pháp thông thường trên ô C1:= A1 với bằng hàm Indirect trên ô C2:=INDIRECT(“A1”). Quý Khách thử cnhát thêm 1 mẫu vào bên trên của dòng số 1

Điều gì xảy ra? Công thức tại ô C1 sẽ tiến hành đẩy xuống ô C2:=A2 với giá trị trên ô C2 vẫn luôn là 20. Công thức tại ô C2 được đẩy xuống ô C3:=INDIRECT(“A1”) và giá trị trên ô C3 là 0. Điều này tức là cách làm sử dụng hàm Indirect sẽ không còn biến hóa khi chúng ta chèn thêm một dòng.

*

Có lẽ hàm Indirect đang trình bày tác dụng của chính bản thân mình cụ thể hơn khi dùng để làm tđắm đuối chiếu cho vùng. lấy một ví dụ bạn sẽ viết SUM ($A$2:$A$5) sẽ biến đổi thành SUM($A$2:$A$6) khi bạn cnhát thêm 1 chiếc bắt đầu vào trong dòng 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 sử dụng với các hàm ROW, ADDRESS, VLOOKUP, SUMIF. Sau đó là một vài ba ví dụ

Hàm INDIRECT với hàm ROW

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