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ó với những người tiêu dùng thông thường. Hàm Indirect là trong những hàm kha khá khó, nhưng mà hiểu được cách sử dụng hàm để biệt này, bạn sẽ tiết kiệm được rất nhiều thời gian khi thao tác với các bảng tính phức hợp của mình. 

Vậy, hàm Indirect thực hiện 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 đang xem: Hàm indirect trong excel 2007

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

Hàm Indirect trong Excel dùng để làm tham chiếu trực tiếp đến các ô, những vùng, các sheet cùng workbook khác. Chúng ta sẽ sử dụng những giá trị trả về của hàm Indirect vào trong cách làm của mình. Những ô, vùng được tham chiếu trong hàm Indirect hoàn toàn linh động. Chính vì trong công thức có sử dụng showroom được tham chiếu bởi vì hàm Indirect, add ô tham chiếu vào 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 hay được dùng trong Excel.Bạn đã xem: Hàm indirect vào excel 2007

Cú pháp hàm INDIRECT trong Excel

Hàm Indirect gồm 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: có giá trị chuỗi của địa chỉ ô mà lại hàm Indirect tham chiếu đến. Đối số mà lại ref_text nhận được hoàn toàn có thể là 1 ô cơ mà giá trị của ô sẽ 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 rất có thể nhận đối số là 1 chuỗi cơ mà giá trị của chuỗi sẽ là một add một ô, vùng trong chính work sheet đó, worksheet khác hoặc trong workbook kháca1: sở hữu giá trị logic 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 gồm dạng A1. Hoặc False lúc ref_text là tham chiếu bao gồm dạng A1C1

Kiểu tham chiếu R1C1 thật sự bổ ích trong một trong những trường hợp. Tuy nhiên, hầu như 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 dưới đây sử dụng loại tham chiếu A1. Do thế, trong số ví dụ bên dưới, họ sẽ bỏ lỡ đối số sản phẩm 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 có một vài quý giá như sau: 

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

 

*

Hàm INDIRECT tại ô D1 nhận quý hiếm chuỗi và tất cả dạng add ô buộc phải tham chiếu là A1 trên ô C1. Hàm INDIRECT sẽ trỏ mang lại ô A1, lấy quý hiếm tại ô A1 là 3 cùng trả công dụng về trên ô D1. D1 nhận hiệu quả trả về là 3. 

Như vậy, hàm INDIRECT đã đưa một quý giá chuỗi “A1” thành một tham chiếu mang đến ô có showroom là A1. 

Lúc này, các bạn chưa thiệt sự nhìn thấy nhiều điệu độc đáo và lôi cuốn của hàm INDIRECT. Điều đầu tiên bạn thấy mê thích ở hàm INDIRECT trong lấy ví dụ là đó là sự đổi khác từ một trong những sang địa chỉ cửa hàng ô buộc phải tham chiếu. 

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

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

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

Như gần như gì tôi sẽ nói, nội dung bài viết này chỉ thật sự hữu ích khi bạn đọc không còn phần ví dụ. Sau thời điểm đọc không còn phần ví dụ, các bạn sẽ hiểu sức mạnh của hàm INDIRECT. Có thể sau đó, các bạn sẽ có thêm nhiều phát minh hơn để áp dụng hàm INDIRECT trong quá trình hàng ngày của mình.

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

Như đã trình bày ở trên, hàm INDIRECT có thể chấp nhận được tham chiếu đến địa chỉ một ô, một vùng bằng 2 thứ hạng tham chiếu A1 với R1C1. 

Có một điều bạn phải ghi hãy nhớ là hàm INDIRECT chỉ được cho phép bạn dùng một trong những 2 đẳng cấp tham chiếu là A1 hoặc R1C1 tại cùng 1 thời điểm, trong cùng một Sheet. Bạn chuyển đổi kiểu tham chiếu bằng phương pháp vào tệp tin > Options > Formulas > R1C1 kiểm tra box

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

Trong cùng một cách làm có sử dụng hàm INDIRECT, chúng ta vẫn hoàn toàn có thể dùng tham chiếu vẻ bên ngoài A1 hoặc R1C1 trong và một sheet nếu như khách hàng muốn. Excel không còn báo lỗi khi chúng ta lthực hiện thao tác làm việc này. 

Trước lúc bạn tìm hiểu sâu hơn về hàm Indirect, bạn nên biết một chút về sự không giống nhau giữa kiểu dáng tham chiếu A1 cùng kiểu tham chiếu R1C1

Tham chiếu mẫu mã A1 là giao diện tham chiếu được sử dụng thông dụng vào Excel. Dạng hình tham chiếu này đang trỏ cho 1 cột cùng theo sau là chỉ số của một dòng nó cho phép tham chiếu đến một ô là giao của cột với dòng. Ví dụ như B2 là ô sẽ tiến hành tham chiếu, là giao của cột B và loại thứ 2. Nghĩa là tham chiếu thứ hạng A1 vẫn trỏ đến cột B trước rồi tiếp đến mới kiểm soát dòng theo sau nó được coi là dòng số 2. Tham chiếu loại R1C1 là kiểu dáng tham chiếu ngược lại với thứ hạng tham chiếu A1. Chỉ số dòng sẽ được trỏ mang đến và theo sau là chỉ số cột. Dạng hình tham chiếu này chỉ đôi lúc mới được sử dụng. Ví dụ như R4C1 sẽ tiến hành tham chiếu bằng phương pháp trỏ đến dòng số 4 với theo sau là cột số một trong các bảng tính. Bởi vậy, ô A4 sẽ tiến hành tham chiếu đến theo kiểu tham chiếu R1C1. Ví như chỉ số cái theo sau bị bỏ qua thì mặc nhiên ô được tham chiếu mang đến sẽ là ô bao gồm cùng số dòng, số cột và đó sẽ là ô D4. 

Để làm rõ hơn, vui vẻ quan liền kề ví dụ sau


*

Như chúng ta thấy trong ảnh chụp màn hình ở trên, bố công thức gián tiếp khác biệt trả về cùng một kết quả. Chúng ta đã search ra lý do tại sao chưa?

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

Đây là giao diện tham chiếu A1, kiểu đơn giản dễ dàng nhất. Cách làm tại ô D1 đã trỏ mang đến ô C1 với nhận quý giá chuỗi “A2” tại ô C1. Cực hiếm chuỗi này vẫn được đổi khác thành ô phải 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 tại ô D3: = INDIRECT(C3, FALSE)

Đây là loại tham chiếu R1C1. Đối số thiết bị hai trong cách làm mang giá trị FALSE. Điều này cho biết rằng cực hiếm của ô C3 sẽ là một trong ô cần được tham chiếu, có dạng R1C1. Trong lấy 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 thứ nhất trong bảng tính. Đó là ô A2. Tác dụng trả về 222 cho ô D3 là giá trị tại ô A2. 

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

Cũng giống như ví dụ tại ô D3, phương pháp tại ô D5 tiến hành theo kiểu dáng tham chiếu R1C1. Bởi vì vậy mà bạn thấy đối số máy hai có mức giá trị FALSE và cực hiếm tại ô C5 tất cả dạng R1C1 của ô phải tham khảo. Như phía dẫn bên trên, khi số cột được bỏ lỡ trong đối số dạng R1C1 thì số cột bình thản sẽ thừa nhận chỉ số trùng với số dòng. Bởi vậy, quý hiếm tại ô C5 là “R3C” thì ô được tham chiếu đến sẽ sở hữu dạng R3C3, là giao của cái thứ 3 với cột đồ vật 3. Đó là ô C3. Giá trị tại ô C3 là “R2C1”. Tại điểm này, 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 mang đến là ô C3. Quý hiếm tại ô C3 là “R2C1” sẽ liên tiếp được gửi thành một ô được tham chiếu cho ở dòng 2, cột một là ô A2. Tác dụng trả về 222 mang đến ô D5 là giá trị tại ô A2

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

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

Cũng hệt như cách chế tác hàm Indirect tham chiếu mang đến giá trị của một ô thì thời gian này, chúng ta có thể kết đúng theo một chuỗi cùng 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 hòa hợp 2 đối tượng người dùng này. 


*

Dễ dàng nhận biết hàm Indirect trong 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ị trên ô C2 là “2” được chuỗi “B2”. Chuỗi B2 này sẽ được chuyển đổi thành ô đề xuất tham chiếu như ta đã làm cho ở những ví dụ trên. Ô B2 được tham chiếu mang lại và trả về tác dụng là 10 cho phương pháp tại ô D2. 

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

Khi thực hiện hàm Indirect, đối số ref_text được trao giá trị từ một ô, một chuỗi thì ref_text còn có thể nhận name ranges (tên vùng). 

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

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

Và bây giờ, bạn có thể thực hiện một bước xa hơn cùng ghép phương pháp 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ị vào một phạm vi được lấy tên nhất định hoặc tìm giá bán trị 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 nhận quý giá “Lemons” tại ô G1 với trỏ cho vùng sẽ đặt thương hiệu Lemons là D2:D6. Trên đây, hàm Sum được tiến hành và trả về kết quả cho ô G2 là tổng những số trong vùng Lemons. Cách làm được thực hiện tương tự trên ô G3, G4, G5. 

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

Hàm Indirect còn có thể tham chiếu đến các ô trên sheet khác. Để triển khai ví dụ, các bạn sẽ đặt các dữ liệu buộc phải lấy trên Sheet 2, khắc tên Sheet 2 là “My Sheet”. Phương pháp chứa hàm Indirect được đặt tại Sheet 1. 


*

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

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

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

Kết trái trả về tại ô C1 của Sheet một là giá trị của ô A1 trên Sheet “My Sheet”. Ngoài ra, hãy xem xét rằng nếu như bạn đang coppy công thức vào nhiều ô, 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 ô tuyệt đối như $A$1.

Xem thêm: Hướng Dẫn Cách Đổi Mật Khẩu Wifi Đơn Giản Như Ăn Kẹo, Cách Đổi Mật Khẩu Wifi Viettel, Tp

Lưu ý:

Nếu một trong số ô cất tên và add ô của trang tính thứ 2 (A1 và B1 trong cách làm trên) bị trống, bí quyết Gián tiếp của bạn sẽ trả về lỗi. Để kiêng điều này, chúng ta 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 tham chiếu cho 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 đang trả về lỗi #REF. Để tránh lỗi, chúng ta có thể sử dụng hàm IFERROR , hàm này vẫn 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 đến một Workbook khác cũng giống như sử dụng hàm Indirect để tham chiếu đến một Worksheet khác. Điều duy nhất bắt buộc làm là thêm thương hiệu Workbook vào trước thương hiệu Worksheet. Để triển khai việc tham chiếu này dễ dàng hơn, bạn nên đặ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 mang đến một Workbook khác có dạng: 

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

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

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

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

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

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

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

Điều gì xảy ra? cách làm tại ô C1 sẽ tiến hành đẩy xuống ô C2:=A2 và quý giá tại ô C2 vẫn luôn là 20. Cách làm 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 đã không chuyển đổi khi bạn chèn thêm 1 dòng.

Có lẽ hàm Indirect vẫn thể hiện công dụng của mình ví dụ hơn khi dùng để tham chiếu đến vùng. Ví dụ các bạn sẽ viết SUM ($A$2:$A$5) sẽ biến đổi thành SUM($A$2:$A$6) khi bạn chèn thêm 1 dòng mới 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 thực hiện cùng với những hàm ROW, ADDRESS, VLOOKUP, SUMIF. Sau đấy 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 làm 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á bán trị bé dại nhất vào vùng A1:A10