ĐỊA CHỈ GIÁN TIẾP Excel

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)

1. 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
2. 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

Cảnh báo. Nếu công thức INDIRECT tham chiếu đến một sổ làm việc khác, thì sổ làm việc đó phải đang mở, nếu không công thức sẽ trả về lỗi #REF. lỗi. Xem các ví dụ trên trang này để tham khảo một hoặc một

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ụ,

1. Chọn danh mục Rau trong cột Loại sản phẩm
2. 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

1. Tại các ô C2. C7, nhập danh sách các số
2. Sao chép danh sách vào E2. E7
3. Trong ô C8, nhập công thức SUM. =SUM(C2. C7)
4. 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 ô

1. Trên trang tính có tên là Bảng dữ liệu, nhập số vào ô A1. A10
2. 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
3. Trong ô B2, nhập tên ô từ dãy số, e. g. A2
4. Trong ô C2, nhập công thức sau
   =INDIRECT("'" & A2 & "'. " & B2)
5. 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)
   
6. 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õ)
7. Chuỗi kết thúc bằng tham chiếu đến ô B2 chứa địa chỉ ô
8. 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
   
9. 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
10. 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

1. Tạo một tệp Excel mới có tên là TestFile. xlsx
2. Thay đổi tên sheet đầu tiên thành Test Data
3. Trên bảng Dữ liệu Kiểm tra, nhập số vào ô A1. A10
4. 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

1. Tạo một tệp Excel mới khác, có tên là LinkedFile. xlsx
2. 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
3. Trong ô A3, nhập tên trang tính. Dữ liệu thử nghiệm
4. 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

1. Trong tệp được liên kết, hãy chọn ô A7 và nhập dấu bằng. =
2. Chuyển sang sổ làm việc Tệp kiểm tra, nhấp vào ô A7 và nhấn Enter
3. 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
4. Để 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

1. Trong ô A6, nhập công thức sau
   =INDIRECT("'[" & A2 & "]" & A3 & "'. " & A4)

Công thức hoạt động như thế nào

1. 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
2. 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
3. A2 là ô có tên tệp dữ liệu
4. 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)
5. A3 là ô có tên sheet
6. 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)
7. A4 là ô có địa chỉ ô dữ liệu

Kiểm tra công thức

1. Thay đổi địa chỉ ô trong ô A4 và kết quả trong A6 sẽ thay đổi
2. 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

1. Tại các ô A1. B5, nhập tiêu đề và số, như hình bên phải
2. Đặ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

3. Trong ô D2, nhập tên của một trong các phạm vi, e. g. Phía đông
4. Trong ô E2, gõ công thức. =SUM(GIÁC TIẾP(D2))
5. Nhấn phím Enter, công thức trả về tổng các số trong dãy Đông
6. 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ày

Trong 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

Hàm địa chỉ gián tiếp trong Excel là gì?

Công thức gián tiếp trong Excel là gì?

Chức năng gián tiếp Excel để tham chiếu một trang tính là gì?

3 loại tham chiếu ô trong Excel là gì?