Hàm INDIRECT trả về một tham chiếu ô hợp lệ từ một chuỗi văn bản đã cho. INDIRECT hữu ích khi bạn cần tạo một giá trị văn bản bằng cách nối các chuỗi văn bản riêng biệt mà sau đó có thể được hiểu là một tham chiếu ô hợp lệ
INDIRECT nhận hai đối số, ref_text và a1. Ref_text là chuỗi văn bản để đánh giá dưới dạng tham chiếu. A1 cho biết kiểu tham chiếu cho giá trị văn bản đến. Khi a1 là TRUE [giá trị mặc định], kiểu là "A1". Khi a1 là FALSE, kiểu là "R1C1". Ví dụ
=INDIRECT["A1"] // returns reference like =A1
=INDIRECT["R1C1",FALSE] // returns reference like =R1C1
Mục đích của INDIRECT thoạt nghe có vẻ khó hiểu [tôi. e. Tại sao lại sử dụng văn bản khi bạn chỉ có thể cung cấp một tài liệu tham khảo thích hợp?] nhưng có nhiều tình huống mà khả năng tạo một tài liệu tham khảo từ văn bản là hữu ích, bao gồm
- Một công thức cần một tên bảng biến
- Một công thức có thể tập hợp một tham chiếu ô từ các bit văn bản
- Một tham chiếu cố định sẽ không thay đổi ngay cả khi hàng hoặc cột bị xóa
- Tạo mảng số bằng hàm ROW trong công thức phức tạp
Ghi chú. INDIRECT là một hàm không ổn định và có thể gây ra các vấn đề về hiệu suất trong các trang tính lớn hoặc phức tạp. Sử dụng cẩn thận
Ví dụ #1 - Tên trang tính biến
Trong ví dụ hiển thị ở trên, INDIRECT được thiết lập để sử dụng tên trang tính biến như thế này
=INDIRECT[B5&"!A1"] // sheet name in B6 is variable
Công thức trong B5, được sao chép xuống, nối văn bản trong B5 với chuỗi ". A1" và trả kết quả về INDIRECT. Hàm INDIRECT sau đó sẽ đánh giá văn bản và chuyển đổi nó thành một tham chiếu phù hợp. Kết quả ở C5. C9 là các giá trị từ ô A1 trong 5 trang tính được liệt kê trong cột B.
Công thức động ở chỗ đáp ứng các giá trị trong cột B. Nói cách khác, nếu một tên trang tính khác được nhập vào cột B5, thì giá trị từ ô A1 trong trang tính mới sẽ được trả về. Với cách tiếp cận tương tự, bạn có thể cho phép người dùng chọn tên trang tính bằng danh sách thả xuống, sau đó tạo tham chiếu đến trang tính đã chọn bằng INDIRECT
Ghi chú. tên trang tính chứa dấu chấm câu hoặc dấu cách phải được đặt trong dấu nháy đơn ['], như được giải thích trong ví dụ này. Điều này không dành riêng cho hàm INDIRECT;
Ví dụ #2 - bảng tra cứu biến
Trong bảng tính bên dưới, VLOOKUP được sử dụng để lấy chi phí cho hai nhà cung cấp A và B. Sử dụng nhà cung cấp được chỉ định trong cột F, VLOOKUP sẽ tự động sử dụng đúng bảng
Công thức trong G5 là
=VLOOKUP[E5,INDIRECT["vendor_"&F5],2,0]
Đọc giải thích đầy đủ ở đây
Ví dụ #3 - Tham chiếu cố định
Tham chiếu được tạo bởi INDIRECT sẽ không thay đổi ngay cả khi các ô, hàng hoặc cột được chèn hoặc xóa. Ví dụ: công thức bên dưới sẽ luôn tham chiếu đến 100 hàng đầu tiên của cột A, ngay cả khi các hàng trong phạm vi đó bị xóa hoặc chèn
=INDIRECT["A1:A100"] // will not change
Ví dụ #4 - phạm vi được đặt tên
Có thể dễ dàng sử dụng hàm INDIRECT với các phạm vi được đặt tên. Trong trang tính bên dưới, có hai dải ô được đặt tên. Nhóm1 [B5. B12] và Nhóm2 [C5. C12]. Khi "Nhóm 1" hoặc "Nhóm 2" được nhập vào ô F5, công thức trong ô F6 tính tổng phạm vi thích hợp bằng cách sử dụng GIÁN TIẾP như thế này
=SUM[INDIRECT[F5]]
Giá trị trong F5 là văn bản, nhưng INDIRECT chuyển đổi văn bản thành một phạm vi hợp lệ
Một ví dụ cụ thể của phương pháp này là sử dụng các phạm vi được đặt tên để tạo danh sách thả xuống phụ thuộc
Ví dụ #5 - Tạo mảng số
Cách sử dụng INDIRECT nâng cao hơn là tạo một mảng số với hàm ROW như thế này
ROW[INDIRECT["1:10"]] // create {1;2;3;4;5;6;7;8;9;10}
Một trường hợp sử dụng được giải thích trong công thức này, tính tổng n giá trị dưới cùng trong một dải ô. Bạn cũng có thể gặp ý tưởng ROW + INDIRECT trong các công thức phức tạp hơn cần lắp ráp một mảng số "ngay lập tức". Một ví dụ là công thức này, được thiết kế để loại bỏ các ký tự số khỏi một chuỗi
Hàm Microsoft Excel INDIRECT trả về một tham chiếu đến một phạm vi và tôi sử dụng hàm này để tạo các danh sách thả xuống phụ thuộc trong sổ làm việc Excel của mình, để hiển thị các mục dựa trên giá trị của một ô khác. Bạn cũng có thể sử dụng INDIRECT để tạo một tham chiếu không thay đổi, nếu các hàng hoặc cột được chèn vào trang tính, nơi bạn đang sử dụng hàm SUM
Tác giả. Debra Dalgleish
Băng hình. Ví dụ hàm INDIRECT
Xem video hướng dẫn Excel ngắn này để biết cách sử dụng riêng hàm INDIRECT hoặc kết hợp với các hàm Excel khác
Hướng dẫn bằng văn bản ở bên dưới video. Tải xuống sổ làm việc GIÁN TIẾP mẫu, để xem các ví dụ và làm theo video
Dòng thời gian video
- 00. 00 Giới thiệu
- 00. 14 Khóa tham chiếu ô
- 01. 27 Tham khảo Phạm vi đã đặt tên
- 02. 50 Tham khảo Sheet cụ thể
- 04. 22 Khóa Mảng Số
Cảm ơn Dave Peterson vì những đóng góp của anh ấy cho trang này
Cách chức năng INDIRECT hoạt động
Hàm INDIRECT hữu ích khi bạn muốn trả về một giá trị, dựa trên một chuỗi văn bản. Ví dụ: chọn tên phạm vi từ danh sách thả xuống và nhận tổng số tiền cho phạm vi đã chọn
Trong ảnh chụp màn hình này, có một danh sách thả xuống trong ô B2, nơi bạn có thể chọn Thực tế hoặc Ngân sách. Sau khi bạn thực hiện lựa chọn, tổng số cho loại đó sẽ xuất hiện trong ô B3
Hàm GIÁN TIẾP Cú pháp Đối số
Cú pháp của hàm INDIRECT có hai đối số. GIÁN TIẾP[ref_text,a1]
- ref_text. Tham chiếu ô hoặc chuỗi văn bản [hoặc cả hai], tạo tham chiếu phạm vi cho một ô, phạm vi ô hoặc phạm vi được đặt tên
- a1. [tùy chọn] Giá trị logic - tham chiếu có sử dụng kiểu tham chiếu A1 không?
- TRUE hoặc bị bỏ qua -- ref_text phải là tham chiếu kiểu A1
- FALSE -- ref_text phải là kiểu R1C1 của kiểu tham chiếu
Danh sách thả xuống phụ thuộc
Cách yêu thích của tôi để sử dụng hàm INDIRECT là trong xác thực dữ liệu, để tạo danh sách thả xuống phụ thuộc. Kỹ thuật này giúp mọi người dễ dàng tìm thấy những gì họ cần hơn khi nhập dữ liệu trên trang tính Excel
Đoạn video ngắn bên dưới, hiển thị các bước và có các bước được viết chi tiết cũng như các tệp mẫu trên trang Danh sách thả xuống phụ thuộc
Ví dụ,
- Chọn danh mục Rau trong cột Loại sản phẩm
- Danh sách thả xuống phụ thuộc trong cột Mục hiển thị danh sách các tùy chọn Rau
Quy tắc xác thực dữ liệu
Hàm INDIRECT được sử dụng trong hộp thoại cài đặt xác thực dữ liệu, giống như ví dụ về công thức được hiển thị bên dưới
Video dưới đây cho thấy cách thiết lập điều này
Băng hình. Danh sách thả xuống phụ thuộc
Trong video ngắn này, bạn sẽ thấy cách thiết lập danh sách thả xuống chính, với danh sách thả xuống phụ thuộc ở cột tiếp theo, sử dụng hàm INDIRECT
Có hướng dẫn bằng văn bản và tệp mẫu để tải xuống trên trang Video Danh sách thả xuống phụ thuộc
Giới thiệu các ví dụ về video GIÁN TIẾP
Dưới đây là các bước viết cho các ví dụ GIÁN TIẾP có trong
1]
2] Tham khảo Trang tính khác nhau
3]
4]
Khóa tham chiếu ô
Nếu bạn tạo một công thức SUM và các ô được chèn sau đó, ở đầu danh sách, thì công thức SUM có thể không bao gồm số tiền mới. Đây là cách bạn có thể sử dụng hàm INDIRECT để tránh vấn đề đó
Hàm INDIRECT có thể "khóa" một ô cụ thể trong công thức. Sau đó, nếu các hàng hoặc cột được chèn hoặc xóa phía trên hoặc bên trái của ô đó, thì tham chiếu không thay đổi. Không có GIÁN TIẾP, tham chiếu sẽ tự động điều chỉnh
Để xem cách thức hoạt động của tính năng này, hãy làm theo các bước sau để tạo hai công thức -- một có tham chiếu ô bình thường và một có hàm INDIRECT kết hợp với hàm SUM
- Tại các ô C2. C7, nhập danh sách các số
- Sao chép danh sách vào E2. E7
- Trong ô C8, nhập công thức SUM. =SUM[C2. C7]
- Trong ô E8, nhập công thức này. =SUM[INDIRECT[“E2”]. E7]
Chèn một hàng
Để thấy sự khác biệt giữa các công thức, hãy chèn một hàng trống phía trên hàng 2, rồi nhập 100 cho tháng 1, trong các ô C2 và E2
- Tổng số tiền thay đổi trong cột E, vì ô bắt đầu bị khóa tại E2
- Tổng số tiền KHÔNG thay đổi trong cột C. Ô bắt đầu chuyển xuống ô C3
Tham khảo trang tính khác nhau
Một công thức GIÁN TIẾP cũng có thể tham chiếu đến các ô trên các trang tính khác. Trong ví dụ này, bạn sẽ tạo một công thức với hàm INDIRECT, sử dụng các tham chiếu đến tên trang tính và tên ô
- Trên trang tính có tên là Bảng dữ liệu, nhập số vào ô A1. A10
- Trên một trang tính khác, trong ô A2, hãy nhập tên trang tính. Bảng dữliệu
- Trong ô B2, nhập tên ô từ dãy số, e. g. A2
- Trong ô C2, nhập công thức sau
=INDIRECT["'" & A2 & "'. " & B2] - Phần đầu tiên của chuỗi là một trích dẫn đơn trong một tập hợp các trích dẫn kép
" ' " [đã thêm dấu cách cho rõ ràng] - Sau tham chiếu đến A2, chứa tên trang tính, là một dấu nháy đơn và dấu chấm than, trong một tập hợp các dấu nháy kép
"'. " [dấu cách đã được thêm vào để làm rõ] - Chuỗi kết thúc bằng tham chiếu đến ô B2 chứa địa chỉ ô
- Dấu nháy đơn được bao gồm trong chuỗi để tránh lỗi nếu tên trang tính chứa ký tự khoảng trắng
- Nhấn phím Enter và công thức trả về số trong ô A2 trên trang tính Bảng dữ liệu
- Thay đổi số trong ô C2 và kết quả trong D2 sẽ thay đổi
Ghi chú. Nếu ô A2 hoặc B2 trống, công thức sẽ trả về lỗi. Để ngăn chặn điều này, bạn có thể thêm hàm IF
=IF[OR[A2="",B2=""],"",INDIRECT["'" & A2 & "'. " & B2]]
Tham khảo Sổ làm việc khác nhau
Một công thức GIÁN TIẾP có thể tham chiếu đến các ô trong các sổ làm việc khác, nhưng sẽ trả về lỗi #REF. lỗi nếu sổ làm việc đó bị đóng. Trong ví dụ này, bạn sẽ tạo một công thức với hàm INDIRECT, sử dụng các tham chiếu đến tên tệp, tên trang tính và tên ô
Thiết lập sổ làm việc dữ liệu
- Tạo một tệp Excel mới có tên là TestFile. xlsx
- Thay đổi tên sheet đầu tiên thành Test Data
- Trên bảng Dữ liệu Kiểm tra, nhập số vào ô A1. A10
- Lưu sổ làm việc
Thiết lập Sổ làm việc được Liên kết
Tiếp theo, hãy làm theo các bước sau để tạo sổ làm việc sẽ liên kết với sổ làm việc dữ liệu. Hoặc, và đi đến trang tính WkbkRef
- Tạo một tệp Excel mới khác, có tên là LinkedFile. xlsx
- Trên trang tính đầu tiên, trong ô A2, nhập tên tệp dữ liệu, có hoặc không có phần mở rộng tệp. Tệp kiểm tra
- Trong ô A3, nhập tên trang tính. Dữ liệu thử nghiệm
- Trong ô A4, nhập A7 -- chúng tôi sẽ lấy một số từ ô đó trong sổ làm việc dữ liệu
Nhận một công thức mẫu
Để xem cú pháp mà bạn sẽ cần sử dụng trong công thức GIÁN TIẾP, hãy làm theo các bước sau
- Trong tệp được liên kết, hãy chọn ô A7 và nhập dấu bằng. =
- Chuyển sang sổ làm việc Tệp kiểm tra, nhấp vào ô A7 và nhấn Enter
- Trong thanh công thức, bạn sẽ thấy tham chiếu đã được tạo
='[Tệp kiểm tra. xlxs]Dữ liệu thử nghiệm'. $A$7 - Để giữ cho công thức đó hiển thị, hãy nhập dấu nháy đơn trước dấu bằng
Tạo một công thức GIÁN TIẾP
Tiếp theo, hãy làm theo các bước sau để tạo một công thức GIÁN TIẾP sử dụng cùng một cú pháp. Công thức sẽ bao gồm dấu nháy đơn, dấu ngoặc vuông và dấu chấm than
- Trong ô A6, nhập công thức sau
=INDIRECT["'[" & A2 & "]" & A3 & "'. " & A4]
Công thức hoạt động như thế nào
- Phần đầu tiên của chuỗi là một dấu ngoặc đơn và dấu ngoặc vuông, trong một tập hợp các dấu ngoặc kép
" ' [ " [Tôi đã thêm dấu cách vào đây để dễ đọc hơn]. Các dấu nháy đơn được bao gồm trong chuỗi để tránh lỗi nếu tên trang tính hoặc tên tệp chứa các ký tự khoảng trắng, giống như trong ví dụ này - Các ký tự & [dấu và] nối tất cả các phần của chuỗi văn bản lại với nhau
- A2 là ô có tên tệp dữ liệu
- Tiếp theo, có các ký tự để kết thúc tên sổ làm việc dữ liệu -- một dấu ngoặc vuông, trong một tập hợp các dấu ngoặc kép. " ] " [thêm dấu cách cho rõ ràng]
- A3 là ô có tên sheet
- Tiếp theo, có các ký tự để kết thúc tên bảng dữ liệu -- một dấu nháy đơn và dấu chấm than, trong một tập hợp các dấu nháy kép. "'. " [đã thêm khoảng trắng cho rõ ràng]
- A4 là ô có địa chỉ ô dữ liệu
Kiểm tra công thức
- Thay đổi địa chỉ ô trong ô A4 và kết quả trong A6 sẽ thay đổi
- Sau khi công thức INDIRECT hoạt động, bạn có thể xóa liên kết mẫu trong ô A5
ghi chú công thức
Lưu ý1. Nếu A2, A3 hoặc A4 trống, công thức sẽ trả về lỗi. Để ngăn chặn điều này, bạn có thể thêm hàm IF
=IF[OR[A2="",A3="",A4=""],"",INDIRECT["'[" & A2 & "]" & A3 & "'. " & A4]]
Lưu ý 2. Nếu tệp thử nghiệm. sổ làm việc xlsx bị đóng, công thức INDIRECT sẽ trả về lỗi #REF. Tôi chưa sử dụng các tệp sau, nhưng chúng có thể giúp ích cho bạn nếu bạn cần lấy dữ liệu từ một sổ làm việc đã đóng
- Harlan Grove đã viết một UDF có tên là PULL sẽ lấy giá trị từ một sổ làm việc đã đóng. Bạn có thể tìm thấy chức năng tại trang tải xuống của Harlan
- Laurent Longre có một phần bổ trợ [morefunc. xll] tại
- Nó bao gồm = gián tiếp. ext[] có thể giúp bạn.
Tham khảo một phạm vi được đặt tên
Ngoài các tham chiếu ô, bạn có thể tham chiếu các phạm vi được đặt tên trong công thức GIÁN TIẾP. Trong ví dụ này, hàm INDIRECT được sử dụng để tính tổng phạm vi đã đặt tên đã chọn
- Tại các ô A1. B5, nhập tiêu đề và số, như hình bên phải
- Đặt tên ô A2. A5 là Đông và các ô B2. B5 như Tây. Có hướng dẫn đặt tên ở đây
Tên -- Phạm vi đặt tên
- Trong ô D2, nhập tên của một trong các phạm vi, e. g. Phía đông
- Trong ô E2, gõ công thức. =SUM[GIÁC TIẾP[D2]]
- Nhấn phím Enter, công thức trả về tổng các số trong dãy Đông
- Thay đổi ô D2 thành West và công thức trả về tổng các số trong phạm vi West
Ví dụ đối số Ref_Text
Phần kỹ thuật này không bắt buộc phải đọc - nó hiển thị các ví dụ cho đối số ref_text trong hàm INDIRECT. Hãy tiếp tục đọc nếu bạn muốn tìm hiểu thêm về các tùy chọn nàyTrong hầu hết các trường hợp, tham chiếu ô hoặc tổ hợp chuỗi văn bản và tham chiếu ô hoạt động tốt nhất với hàm INDIRECT