Hàm indirect trong excel 2007

      100

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

Vậy, hàm Indirect sử dụng trong trường phù hợp nào với cú pháp của chính nó là gì? bài viết này sẽ giúp bạn trả lời thắc mắc trên.Bạn sẽ xem: Hàm indirect vào excel 2007

Cú pháp hàm INDIRECT trong Excel

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

Bạn đang xem: Hàm indirect trong excel 2007

Cú pháp hàm INDIRECT trong Excel

Hàm Indirect có hai đối số, đối số trước tiên bắt buộc, đối số vật dụng hai tùy chọn

= INDIRECT (ref_text, )

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

Kiểu tham chiếu R1C1 thiệt sự hữu ích trong một trong những trường hợp. Mặc dù nhiên, đa số trường hợp họ sử dụng là kiểu dáng tham chiếu A1. Những ví dụ phía dẫn sau đây sử dụng loại tham chiếu A1. Vì thế, trong những ví dụ mặt dưới, chúng ta sẽ bỏ qua mất đối số sản phẩm công nghệ hai. 

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

Trước khi chèn hàm Indirect vào bảng tính, bạn cần có một vài quý hiếm như sau: 

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

 

*

Hàm INDIRECT trên ô D1 nhận quý giá chuỗi và có dạng showroom ô nên tham chiếu là A1 trên ô C1. Hàm INDIRECT đã trỏ cho ô A1, lấy giá trị tại ô A1 là 3 với trả tác dụng về tại ô D1. D1 nhận kết quả trả về là 3. 

Như vậy, hàm INDIRECT đã chuyển một quý hiếm chuỗi “A1” thành một tham chiếu cho ô có add là A1. 

Lúc này, bạn chưa thật sự nhìn thấy nhiều điều thích thú và hấp dẫn của hàm INDIRECT. Điều trước tiên bạn thấy phù hợp ở hàm INDIRECT trong lấy một ví dụ là đó là sự đổi khác từ một số sang add ô nên tham chiếu. 

Vậy áp dụng hàm INDIRECT như vậy nào? Ở phần tiếp sau của gợi ý này, chúng ta sẽ khám phá một số ví dụ thực tế về hàm INDIRECT trong excel, để chúng ta có thể hiểu bởi vì sao hiện nay nay, hàm INDIRECT lại được thực hiện nhiều trong đối chiếu dữ liệu.

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

Như đa số gì tôi vẫn nói, nội dung bài viết này chỉ thật sự hữu ích khi chúng ta đọc không còn phần ví dụ. Sau khi đọc hết phần ví dụ, các bạn sẽ hiểu sức mạnh của hàm INDIRECT. Hoàn toàn có thể sau đó, các bạn sẽ có thêm nhiều ý tưởng hơn để thực hiện hàm INDIRECT trong các bước hàng ngày của mình.

Tạo tham chiếu gián tiếp từ các giá trị ô

Như đã trình diễn ở trên, hàm INDIRECT được cho phép tham chiếu đến địa chỉ một ô, một vùng bởi 2 hình trạng tham chiếu A1 cùng R1C1. 

Có một điều bạn phải ghi hãy nhờ rằng hàm INDIRECT chỉ có thể chấp nhận được bạn dùng 1 trong các 2 thứ hạng tham chiếu là A1 hoặc R1C1 trên cùng 1 thời điểm, trong và một Sheet. Bạn biến đổi kiểu tham chiếu bằng phương pháp vào file > Options > Formulas > R1C1 check box

Đây là tại sao tại sao người dùng ít tiếp cận hàm INDIRECT theo kiểu tham chiếu R1C1. Khi chúng ta cần sử dụng đến dạng hình tham chiếu này, chúng ta phải vào tùy chọn của “Working with formulas” để gửi đổi. 

Trong cùng một cách làm có áp dụng hàm INDIRECT, các bạn vẫn hoàn toàn có thể dùng tham chiếu đẳng cấp A1 hoặc R1C1 trong và một sheet nếu khách hàng muốn. Excel không thể báo lỗi khi bạn lthực hiện thao tác này. 

Trước khi bạn mày mò sâu hơn về hàm Indirect, bạn cần biết một chút về sự không giống nhau giữa hình trạng tham chiếu A1 cùng kiểu tham chiếu R1C1

Tham chiếu mẫu mã A1 là thứ hạng tham chiếu được sử dụng thông dụng vào Excel. Mẫu mã tham chiếu này đã trỏ đến 1 cột cùng theo sau là chỉ số của 1 dòng nó có thể chấp nhận được tham chiếu mang lại một ô là giao của cột với dòng. Lấy một ví dụ B2 là ô sẽ tiến hành tham chiếu, là giao của cột B và mẫu thứ 2. Tức thị tham chiếu vẻ bên ngoài A1 đang trỏ mang lại cột B trước rồi sau đó mới bình chọn dòng theo sau nó là dòng số 2. Tham chiếu kiểu R1C1 là loại tham chiếu trái lại với mẫu mã tham chiếu A1. Chỉ số dòng sẽ tiến hành trỏ cho và theo sau là chỉ số cột. Thứ hạng tham chiếu này chỉ thỉnh thoảng mới được sử dụng. Lấy ví dụ R4C1 sẽ tiến hành tham chiếu bằng cách trỏ đến cái số 4 với theo sau là cột số 1 trong những bảng tính. Vì vậy, ô A4 sẽ tiến hành tham chiếu đến theo kiểu tham chiếu R1C1. Nếu chỉ số cái theo sau bị bỏ qua thì điềm nhiên ô được tham chiếu đến sẽ là ô tất cả cùng số dòng, số cột và đó sẽ là ô D4. 

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


*

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

Công thức trên ô D1: = INDIRECT (C1)

Đây là hình dáng tham chiếu A1, kiểu dễ dàng nhất. Bí quyết tại ô D1 đã trỏ cho ô C1 cùng nhận giá trị chuỗi “A2” tại ô C1. Quý hiếm chuỗi này sẽ được thay đổi thành ô đề xuất tham chiếu là ô A2 cùng lấy cực hiếm tại ô A2 trả về cho công thức tại ô D1. Như vậy, ô D1 sẽ có được giá trị trả về là 222

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

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

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

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

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

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

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

Cũng y hệt như cách tạo ra hàm Indirect tham chiếu cho giá trị của một ô thì thời điểm này, chúng ta có thể kết đúng theo một chuỗi với một ô được tham chiếu vào công thức của hàm Indirect. Vết “&” được dùng làm kết vừa lòng 2 đối tượng người tiêu dùng này. 


*

Dễ dàng nhận biết hàm Indirect trong công thức này được tham chiếu theo phong cách A1. Đối số ref_text nhận ra 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 đã được chuyển đổi thành ô đề nghị tham chiếu như ta đã có tác dụng ở các ví dụ trên. Ô B2 được tham chiếu mang đến và trả về tác dụng là 10 cho cách làm tại ô D2. 

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

Khi áp dụng hàm Indirect, đối số ref_text được nhận giá trị xuất phát từ một ô, một chuỗi thì ref_text còn hoàn toàn có thể nhận name ranges (tên vùng). 

Để triển khai ví dụ sau, bạn hãy đặt thương hiệu vùng như sau: 

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

Và bây giờ, bạn cũng có thể thực hiện một bước xa hơn với ghép bí quyết INDIRECT này vào các hàm Excel khác nhằm tính tổng cùng trung bình của những giá trị trong một phạm vi chọn cái tên nhất định hoặc tìm giá chỉ trị lớn số 1 / nhỏ nhất

G2:= SUM (INDIRECT (G1))

G3:= AVERAGE (INDIRECT (G1))

G4:= MAX (INDIRECT (G1))

G5:= MIN (INDIRECT (G1))


*

Tại ô G2, hàm Indirect nhận giá trị “Lemons” tại ô G1 và trỏ mang lại vùng đang đặt tên Lemons là D2:D6. Tại đây, hàm Sum được tiến hành và trả về hiệu quả cho ô G2 là tổng các số trong vùng Lemons. Công thức được thực hiện tương tự tại ô 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 hoàn toàn có thể tham chiếu đến các ô bên trên sheet khác. Để thực hiện ví dụ, bạn sẽ đặt những dữ liệu nên lấy trên Sheet 2, viết tên Sheet 2 là “My Sheet”. Cách làm chứa hàm Indirect được đặt ở Sheet 1. 


*

Khi xem thêm đến dữ liệu ở 1 Sheet không giống trong Excel, cú pháp được viết bao gồm Tên_Sheet!Ô (hay vùng) được tham chiếu. Thông thường, thương hiệu Sheet tất cả 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 đề nghị thiết. Lấy ví dụ như My_Sheet!A1 để chỉ cho ô A1 của Sheet có tên My_Sheet.

Xem thêm: Top Phần Mềm Ghép Hình Trên Máy Tính Dễ Sử Dụng Nhất, Top Phần Mềm Ghép Ảnh Tốt Nhất Trên Windows

Trong trường phù hợp bạn áp dụng tên Sheet gồm dùng khoảng chừng cách, bạn nên thực hiện thêm vết nháy đơn để kiêng lỗi xảy ra. Đây là cách chúng ta đưa chuỗi và cho đố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 cùng gõ vào ô C1 công thức thực hiện hàm Indirect: 

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

Kết quả trả về trên ô C1 của Sheet 1 là giá trị của ô A1 trên Sheet “My Sheet”. Ngoài ra, hãy để ý rằng nếu như bạn đang coppy công thức vào nhiều ô, bạn phải khóa tham chiếu đến tên trang tính bằng phương pháp sử dụng tham chiếu ô tuyệt đối như $A$1.

Lưu ý:

Nếu một trong các ô đựng tên và showroom ô của trang tính thứ hai (A1 cùng B1 trong cách làm trên) bị trống, công thức Gián tiếp của các bạn sẽ trả về lỗi. Để kiêng điều này, bạn cũng có thể bọc hàm INDIRECT trong hàm IF : IF(OR($A$1="",B1=""), "", INDIRECT(""" và $A$1 & ""!" và B1))Để công thức INDIRECT tham chiếu mang lại trang tính khác chuyển động chính xác, trang tính được ra mắt phải được mở, còn nếu như không công thức vẫn trả về lỗi #REF. Để né lỗi, bạn 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 tham chiếu mang lại một Workbook khác

Sử dụng hàm Indirect để tham chiếu đến một Workbook khác cũng như sử dụng hàm Indirect nhằm tham chiếu mang đến một Worksheet khác. Điều duy nhất nên làm là thêm tên Workbook vào trước thương hiệu Worksheet. Để thực hiện việc tham chiếu này dễ dãi hơn, bạn nên được sắp xếp tên Workbook và Worksheet không tồn tại 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 bao gồm dạng: 

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

Vì bạn không muốn những ô cất tên sách cùng trang tính chuyển đổi khi sao chép công thức sang những ô khác, bạn khóa chúng bằng cách sử dụng tham chiếu ô tuyệt vời , khớp ứng là $A$2 cùng $B$2.


Tên Workbook bạn đặt tại ô A2, tên Worksheet bạn đặt ở ô B2, ô bạn phải trỏ mang lại trong Workbook khác là ô A1, bạn đặt chuỗi A1 sinh hoạt ô C2. Công thức áp dụng hàm Indirect để ở ô D2:= INDIRECT(“’” và $B$2 &”’!” và C2). Hiệu quả trả về 111 tại ô D2 là quý giá tại ô A1 của Sheet mang tên “My Sheet” nằm trong Workbook mang tên “My Book”. 

Chú ý: Workbook được tham chiếu đến yêu cầu trong trình trạng sẽ mở còn nếu không thì hàm Indirect sẽ báo lỗi

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

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

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

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


Có lẽ hàm Indirect sẽ thể hiện hiệu quả của mình cụ thể hơn khi dùng làm tham chiếu mang lại vùng. Ví dụ các bạn sẽ viết SUM ($A$2:$A$5) sẽ thay đổi 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 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 và hàm ROW

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