Nội dung
Xin chúc mừng! Bạn đã đến được ngày cuối cùng của cuộc thi marathon 30 hàm Excel trong 30 ngày. Đó là một hành trình dài và thú vị mà bạn đã học được nhiều điều bổ ích về các hàm trong Excel.
Vào ngày thứ 30 của cuộc thi marathon, chúng tôi sẽ dành phần nghiên cứu về hàm GIÁN TIẾP (INDIRECT), trả về liên kết được chỉ định bởi chuỗi văn bản. Với chức năng này, bạn có thể tạo danh sách thả xuống phụ thuộc. Ví dụ: khi chọn một quốc gia từ danh sách thả xuống sẽ xác định những tùy chọn nào sẽ xuất hiện trong danh sách thành phố thả xuống.
Vì vậy, chúng ta hãy xem xét kỹ hơn phần lý thuyết của hàm GIÁN TIẾP (CHỈNH SỬA) và khám phá các ví dụ thực tế về ứng dụng của nó. Nếu bạn có thêm thông tin hoặc ví dụ, vui lòng chia sẻ chúng trong phần bình luận.
Chức năng 30: INDIRECT
Chức năng GIÁN TIẾP (INDIRECT) trả về liên kết được chỉ định bởi chuỗi văn bản.
Bạn có thể sử dụng hàm INDIRECT như thế nào?
Kể từ khi chức năng GIÁN TIẾP (INDIRECT) trả về một liên kết được cung cấp bởi một chuỗi văn bản, bạn có thể sử dụng nó để:
- Tạo một liên kết ban đầu không thay đổi.
- Tạo một tham chiếu đến một phạm vi được đặt tên tĩnh.
- Tạo một liên kết bằng cách sử dụng thông tin trang tính, hàng và cột.
- Tạo một mảng số không thay đổi.
Cú pháp INDIRECT (INDIRECT)
Chức năng GIÁN TIẾP (INDIRECT) có cú pháp sau:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- văn bản giới thiệu (link_to_cell) là văn bản của liên kết.
- a1 - nếu bằng TRUE (TRUE) hoặc không được chỉ định, thì kiểu của liên kết sẽ được sử dụng A1; và nếu FALSE (FALSE), thì kiểu R1C1.
Bẫy INDIRECT (INDIRECT)
- Chức năng GIÁN TIẾP (INDIRECT) được tính toán lại bất cứ khi nào các giá trị trong trang tính Excel thay đổi. Điều này có thể làm chậm sổ làm việc của bạn rất nhiều nếu hàm được sử dụng trong nhiều công thức.
- Nếu chức năng GIÁN TIẾP (INDIRECT) tạo một liên kết đến một sổ làm việc Excel khác, sổ làm việc đó phải được mở nếu không công thức sẽ báo lỗi #REF! (#LIÊN KẾT!).
- Nếu chức năng GIÁN TIẾP (INDIRECT) tham chiếu đến một phạm vi vượt quá giới hạn hàng và cột, công thức sẽ báo lỗi #REF! (#LIÊN KẾT!).
- Chức năng GIÁN TIẾP (INDIRECT) không thể tham chiếu đến một phạm vi được đặt tên động.
Ví dụ 1: Tạo một liên kết ban đầu không thay đổi
Trong ví dụ đầu tiên, cột C và E chứa các số giống nhau, tổng của chúng được tính bằng cách sử dụng hàm TÓM TẮT (SUM) cũng vậy. Tuy nhiên, các công thức hơi khác một chút. Trong ô C8, công thức là:
=SUM(C2:C7)
=СУММ(C2:C7)
Trong ô E8, hàm GIÁN TIẾP (INDIRECT) tạo liên kết đến ô bắt đầu E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Nếu bạn chèn một hàng ở đầu trang tính và thêm giá trị cho tháng Giêng (Jan), thì số tiền trong cột C sẽ không thay đổi. Công thức sẽ thay đổi, phản ứng với việc thêm một dòng:
=SUM(C3:C8)
=СУММ(C3:C8)
Tuy nhiên, hàm GIÁN TIẾP (INDIRECT) cố định E2 làm ô bắt đầu, do đó, tháng XNUMX tự động được đưa vào phép tính tổng cột E. Ô kết thúc đã thay đổi, nhưng ô bắt đầu không bị ảnh hưởng.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Ví dụ 2: Liên kết đến một dải ô được đặt tên tĩnh
Chức năng GIÁN TIẾP (INDIRECT) có thể tạo một tham chiếu đến một dải ô đã đặt tên. Trong ví dụ này, các ô màu xanh lam tạo nên phạm vi Danh sách số. Ngoài ra, một dải động cũng được tạo từ các giá trị trong cột B Danh sách sốDyn, tùy thuộc vào số lượng các số trong cột này.
Tổng cho cả hai phạm vi có thể được tính bằng cách chỉ cần đặt tên của nó làm đối số cho hàm TÓM TẮT (SUM), như bạn có thể thấy trong ô E3 và E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Thay vì nhập tên phạm vi vào một hàm TÓM TẮT (SUM), Bạn có thể tham chiếu đến tên được viết trong một trong các ô của trang tính. Ví dụ, nếu tên Danh sách số được viết trong ô D7, thì công thức trong ô E7 sẽ như thế này:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Thật không may, chức năng GIÁN TIẾP (INDIRECT) không thể tạo tham chiếu phạm vi động, vì vậy khi bạn sao chép công thức này vào ô E8, bạn sẽ gặp lỗi #REF! (#LIÊN KẾT!).
Ví dụ 3: Tạo liên kết bằng cách sử dụng thông tin trang tính, hàng và cột
Bạn có thể dễ dàng tạo liên kết dựa trên số hàng và cột, cũng như sử dụng giá trị FALSE (FALSE) cho đối số hàm thứ hai GIÁN TIẾP (GIÁN TIẾP). Đây là cách tạo liên kết kiểu R1C1. Trong ví dụ này, chúng tôi đã thêm tên trang tính vào liên kết - 'MyLinks'! R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Ví dụ 4: Tạo một mảng số không thay đổi
Đôi khi bạn cần sử dụng một mảng số trong công thức Excel. Trong ví dụ sau, chúng ta muốn tính trung bình của 3 số lớn nhất trong cột B. Các số có thể được nhập vào một công thức, như được thực hiện trong ô D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Nếu bạn cần một mảng lớn hơn, thì bạn không muốn nhập tất cả các số vào công thức. Tùy chọn thứ hai là sử dụng hàm HÀNG (ROW), như được thực hiện trong công thức mảng được nhập vào ô D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Tùy chọn thứ ba là sử dụng hàm HÀNG (STRING) cùng với GIÁN TIẾP (INDIRECT), như được thực hiện với công thức mảng trong ô D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Kết quả cho cả 3 công thức sẽ giống nhau:
Tuy nhiên, nếu các hàng được chèn ở đầu trang tính, công thức thứ hai sẽ trả về kết quả không chính xác do thực tế là các tham chiếu trong công thức sẽ thay đổi cùng với sự dịch chuyển hàng. Bây giờ, thay vì giá trị trung bình của ba số lớn nhất, công thức trả về giá trị trung bình của các số lớn thứ 3, 4 và 5.
Sử dụng các chức năng GIÁN TIẾP (INDIRECT), công thức thứ ba giữ các tham chiếu hàng chính xác và tiếp tục hiển thị kết quả chính xác.