Cách Sử Dụng Hàm Indirect Trong Excel 2007, Giới Thiệu Về Hàm Indirect Trong Excel

 ref_text: Mang giá trị chuỗi của địa chỉ ô mà hàm Indirect tham chiếu đến. Đối số mà ref_text nhận được có thể là 1 ô mà giá trị của ô đó là một địa chỉ một ô, vùng trong chính work sheet đó, worksheet khác hoặc trong workbook khác. Hoặc ref_text có thể nhận đối số là một chuỗi mà giá trị của chuỗi đó là một địa chỉ một ô, vùng trong chính work sheet đó, worksheet khác hoặc trong workbook kháca1: Mang giá trị logic 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 có dạng A1. Hoặc False khi 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 số trường hợp. Tuy nhiên, hầu hết trường hợp chúng ta sử dụng là kiểu tham chiếu A1. Các ví dụ hướng dẫn dưới đây sử dụng kiểu tham chiếu A1. Vì thế, trong các ví dụ bên dưới, chúng ta sẽ bỏ qua đối số thứ hai. 

Hàm INDIRECT tại ô D1 nhận giá trị chuỗi và có dạng địa chỉ ô cần tham chiếu là A1 tại ô C1. Hàm INDIRECT sẽ trỏ đến ô A1, lấy giá trị tại ô A1 là 3 và trả kết quả về tại ô D1. D1 nhận kết quả trả về là 3. 

Như vậy, hàm INDIRECT đã chuyển một giá trị chuỗi “A1” thành một tham chiếu đến ô có địa chỉ là A1. 

Lúc này, bạn chưa thật sự nhìn thấy nhiều điều thú vị và hấp dẫn của hàm INDIRECT. Điều đầu tiên bạn thấy thích ở hàm INDIRECT trong ví dụ là chính là sự chuyển đổi từ một số sang địa chỉ ô cần tham chiếu. 

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

Đang xem: Hàm indirect trong excel 2007

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

Như những gì tôi đã nói, bài viết này chỉ thật sự hữu ích khi bạn đọc hết phần ví dụ. Sau khi đọc hết phần ví dụ, bạn sẽ hiểu sức mạnh của hàm INDIRECT. Có thể sau đó, bạn sẽ có thêm nhiều ý tưởng hơn để sử dụng hàm INDIRECT trong công việ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 bày ở trên, hàm INDIRECT cho phép tham chiếu đến địa chỉ một ô, một vùng bằng 2 kiểu tham chiếu A1 và R1C1. 

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

Đây là lý do tại sao người dùng ít tiếp cận hàm INDIRECT theo kiểu tham chiếu R1C1. Khi bạn cần dùng đến kiểu 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ông thức có sử dụng hàm INDIRECT, bạn vẫn có thể dùng tham chiếu kiểu A1 hoặc R1C1 trong cùng một sheet nếu bạn muốn. Excel không hề báo lỗi khi bạn lthực hiện thao tác này. 

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

Tham chiếu kiểu A1 là kiểu tham chiếu được dùng thông dụng trong Excel. Kiểu tham chiếu này sẽ trỏ đến 1 cột và theo sau là chỉ số của 1 dòng nó cho phép tham chiếu đến một ô là giao của cột và dòng. Ví dụ B2 là ô sẽ được tham chiếu, là giao của cột B và dòng thứ 2. Nghĩa là tham chiếu kiểu A1 sẽ trỏ đến cột B trước rồi sau đó mới kiểm tra dòng theo sau nó là dòng số 2. Tham chiếu kiểu R1C1 là kiểu tham chiếu ngược lại với kiểu tham chiếu A1. Chỉ số dòng sẽ được trỏ đến và theo sau là chỉ số cột. Kiểu tham chiếu này chỉ đôi khi mới được sử dụng. Ví dụ R4C1 sẽ được tham chiếu bằng cách trỏ đến dòng số 4 và theo sau là cột số 1 trong bảng tính. Vì vậy, ô A4 sẽ được tham chiếu đến theo kiểu tham chiếu R1C1. Nếu chỉ số dòng theo sau bị bỏ qua thì mặc nhiên ô được tham chiếu đến sẽ là ô có cùng số dòng, số cột và đó sẽ là ô D4. 

Để hiểu rõ hơn, vui lòng quan sát ví dụ sau

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

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

Đây là kiểu tham chiếu A1, kiểu đơn giản nhất. Công thức tại ô D1 sẽ trỏ đến ô C1 và nhận giá trị chuỗi “A2” tại ô C1. Giá trị chuỗi này sẽ được chuyển đổi thành ô cần tham chiếu là ô A2 và lấy giá trị tại ô A2 trả về cho công thức tại ô D1. Như vậy, ô D1 sẽ có giá trị trả về là 222

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

Đây là kiểu tham chiếu R1C1. Đối số thứ hai trong công thức mang giá trị FALSE. Điều này cho biết rằng giá trị của ô C3 sẽ là 1 ô cần được tham chiếu, có dạng R1C1. Trong ví dụ này, ô C3 có giá trị là “R2C1”. Như vậy, ô cần được tham chiếu là giao của dòng thứ 2 và cột thứ nhất trong bảng tính. Đó là ô A2. Kết quả 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, công thức tại ô D5 thực hiện theo kiểu tham chiếu R1C1. Vì vậy mà bạn thấy đối số thứ hai có giá trị FALSE và giá trị tại ô C5 có dạng R1C1 của ô cần tham khảo. Như hướng dẫn bên trên, khi số cột được bỏ qua trong đối số dạng R1C1 thì số cột mặc nhiên sẽ nhận chỉ số trùng với số dòng. Vì vậy, giá trị tại ô C5 là “R3C” thì ô được tham chiếu đến sẽ có dạng R3C3, là giao của dòng thứ 3 và cột thứ 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ị cần ghi nhớ

Nếu giá trị tại ô C5 là “R3C”, ô được tham chiếu đến là ô C3. Giá trị tại ô C3 là “R2C1” sẽ tiếp tục được chuyển thành một ô được tham chiếu đến ở dòng 2, cột 1 là ô A2. Kết quả trả về 222 cho ô 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. Giá trị tại ô C3 là “R2C1” sẽ là kết quả trả về cho ô D5. 

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

Cũng giống như cách tạo hàm Indirect tham chiếu đến giá trị của một ô thì lúc này, bạn có thể kết hợp một chuỗi và một ô được tham chiếu vào công thức của hàm Indirect. Dấu “&” được dùng để kết hợp 2 đối tượng này. 

Dễ dàng nhận ra hàm Indirect trong công thức này được tham chiếu theo kiểu A1. Đối số ref_text nhận được là chuỗi “B” kết hợp với giá trị tại ô C2 là “2” được chuỗi “B2”. Chuỗi B2 này sẽ được chuyển đổi thành ô cần tham chiếu như ta đã làm ở các ví dụ trên. Ô B2 được tham chiếu đến và trả về kết quả là 10 cho công thức tại ô D2. 

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

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

Để thực hiện 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 và ghép công thức INDIRECT này vào các hàm Excel khác để tính tổng và trung bình của các giá trị trong một phạm vi được đặt tên nhất định hoặc tìm giá trị lớn nhất / 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ỏ đến vùng đã đặt tên Lemons là D2:D6. Tại đây, hàm Sum được thực hiện và trả về kết 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 đến 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. Để thực hiện ví dụ, bạn sẽ đặt các dữ liệu cần lấy trên Sheet 2, đặt tên Sheet 2 là “My Sheet”. Công thức chứa hàm Indirect được đặt ở Sheet 1. 

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

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

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

Kết quả 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 lưu ý rằng nếu bạn đang sao chép 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 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 các ô chứa tên và địa chỉ ô của trang tính thứ 2 (A1 và B1 trong công thức 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ó thể bọc hàm INDIRECT trong hàm IF : IF(OR($A$1=””,B1=””), “”, INDIRECT(“”” & $A$1 & “”!” & B1))Để công thức INDIRECT tham chiếu đến trang tính khác hoạt động chính xác, trang tính được giới thiệu phải được mở, nếu không công thức sẽ trả về lỗi #REF. Để tránh lỗi, bạn có thể sử dụng hàm IFERROR , hàm này sẽ hiển thị một chuỗi trống, bất kỳ lỗi nào xảy ra: IFERROR(INDIRECT(“”” & $A$1 & “”!” &B1), “”)

Tạo hàm INDIRECT tham chiếu đến 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 cần làm là thêm tên Workbook vào trước tên Worksheet. Để thực hiện việc tham chiếu này dễ dàng hơn, bạn nên đặt tên Workbook và Worksheet không có dấu cách.

Xem thêm:

 “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(“”” & $B$2 & “”!” & C2)

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

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

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

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

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

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

Điều gì xảy ra? Công thức tại ô C1 sẽ được đẩy xuống ô C2:=A2 và giá trị tại ô C2 vẫn là 20. Công thức tại ô C2 được đẩy xuống ô C3:=INDIRECT(“A1”) và giá trị tại ô C3 là 0. Điều này có nghĩa là công thức dùng hàm Indirect sẽ không thay đổi 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 rõ ràng hơn khi dùng để tham chiếu đến vùng. Ví dụ bạn sẽ viết SUM ($A$2:$A$5) sẽ thay đổi thành SUM($A$2:$A$6) khi bạn chèn thêm 1 dòng mới vào dòng số 3. 

Sử dụng hàm INDIRECT với các hàm ROW, ADDRESS

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