Phân tích hàm INDIRECT bằng các ví dụ

Thoạt nhìn (đặc biệt là khi đọc phần trợ giúp), hàm GIÁN TIẾP (GIÁN TIẾP) trông đơn giản và thậm chí không cần thiết. Bản chất của nó là biến văn bản trông giống như một liên kết thành một liên kết chính thức. Những thứ kia. nếu chúng ta cần tham chiếu đến ô A1 thì chúng ta có thể tạo liên kết trực tiếp theo thói quen (nhập dấu bằng vào D1, nhấp vào A1 và nhấn Enter) hoặc chúng ta có thể sử dụng GIÁN TIẾP cho cùng một mục đích:

Xin lưu ý rằng đối số của hàm – tham chiếu đến A1 – được nhập trong dấu ngoặc kép, do đó, trên thực tế, đây là văn bản.

“Ồ, được thôi,” bạn nói. “Và lợi ích là gì?” 

Nhưng đừng đánh giá qua ấn tượng đầu tiên – điều đó là lừa đảo. Tính năng này có thể giúp bạn trong rất nhiều tình huống.

Ví dụ 1. Chuyển cung

Một thể loại kinh điển: bạn cần xoay đường dọc

rãnh sang ngang (chuyển vị). Tất nhiên, bạn có thể sử dụng một chức năng hoặc chức năng chèn đặc biệt GIAO THÔNG (VẬN CHUYỂN) trong một công thức mảng, nhưng bạn có thể làm được với GIÁN TIẾP:

Logic rất đơn giản: để lấy địa chỉ của ô tiếp theo, chúng ta dán chữ “A” với ký tự đặc biệt “&” và số cột của ô hiện tại mà hàm cung cấp cho chúng ta CỘT (CỘT).

Thủ tục ngược lại được thực hiện tốt hơn một chút khác nhau. Vì lúc này chúng ta cần tạo liên kết đến các ô B2, C2, D2, v.v. nên sử dụng chế độ liên kết R1C1 thay vì “trận chiến biển” cổ điển sẽ thuận tiện hơn. Ở chế độ này, các ô của chúng ta sẽ chỉ khác nhau ở số cột: B2=R1C2, C2=R1C3, D2=R1C4 và vv

Đây là nơi xuất hiện đối số hàm tùy chọn thứ hai. GIÁN TIẾP. Nếu bằng nói dối (SAI), thì bạn có thể đặt địa chỉ liên kết ở chế độ R1C1. Vì vậy, chúng ta có thể dễ dàng chuyển phạm vi ngang trở lại dọc:

Ví dụ 2. Tính tổng theo khoảng

Chúng tôi đã phân tích một cách tính tổng trên một cửa sổ (phạm vi) có kích thước nhất định trên một trang tính bằng hàm TRANH CHẤP (BÙ LẠI). Một vấn đề tương tự cũng có thể được giải quyết bằng cách sử dụng GIÁN TIẾP. Nếu chúng ta chỉ cần tóm tắt dữ liệu từ một khoảng thời gian nhất định thì chúng ta có thể ghép dữ liệu đó thành từng phần và sau đó biến nó thành một liên kết chính thức mà chúng ta có thể chèn vào bên trong hàm TÓM TẮT (TỔNG):

Ví dụ 3. Danh sách thả xuống bảng thông minh

Đôi khi Microsoft Excel không coi tên và cột bảng thông minh là liên kết đầy đủ. Vì vậy, ví dụ: khi cố gắng tạo danh sách thả xuống (tab Dữ liệu - Xác thực dữ liệu) dựa trên cột Nhân viên từ bàn thông minh người chúng ta sẽ gặp lỗi:

Nếu chúng ta “bọc” liên kết bằng chức năng của mình GIÁN TIẾP, thì Excel sẽ dễ dàng chấp nhận và danh sách thả xuống của chúng ta sẽ được cập nhật động khi thêm nhân viên mới vào cuối bảng thông minh:

Ví dụ 4. Liên kết không thể phá vỡ

Như bạn đã biết, Excel tự động sửa địa chỉ tham chiếu trong công thức khi chèn hoặc xóa cột hàng trên trang tính. Trong hầu hết các trường hợp, điều này đúng và thuận tiện, nhưng không phải lúc nào cũng vậy. Giả sử chúng ta cần chuyển tên từ thư mục nhân viên sang báo cáo:

Nếu bạn đặt các liên kết thông thường (nhập =B2 vào ô màu xanh lá cây đầu tiên và sao chép nó xuống), thì khi bạn xóa, ví dụ Dasha, chúng ta sẽ nhận được #LINK! lỗi ở ô màu xanh lá cây tương ứng với cô ấy. (#REF!). Trong trường hợp sử dụng chức năng tạo liên kết GIÁN TIẾP sẽ không có vấn đề như vậy.

Ví dụ 5: Thu thập dữ liệu từ nhiều sheet

Giả sử chúng ta có 5 tờ báo cáo cùng loại từ các nhân viên khác nhau (Mikhail, Elena, Ivan, Sergey, Dmitry):

Giả sử rằng hình dạng, kích thước, vị trí và trình tự của hàng hóa và tháng trong tất cả các bảng đều giống nhau – chỉ có các con số là khác nhau.

Bạn có thể thu thập dữ liệu từ tất cả các sheet (không gộp lại mà xếp chồng lên nhau thành một “đống”) chỉ bằng một công thức:

Như bạn có thể thấy, ý tưởng là như nhau: chúng tôi dán liên kết đến ô mong muốn của trang nhất định và GIÁN TIẾP biến nó thành một "cuộc sống". Để thuận tiện, phía trên bảng, tôi đã thêm các chữ cái của các cột (B,C,D) và ở bên phải – số dòng cần lấy từ mỗi trang.

Cạm bẫy

Nếu bạn đang sử dụng GIÁN TIẾP (GIÁN TIẾP) bạn cần nhớ về điểm yếu của nó:

• Nếu bạn liên kết đến một tệp khác (bằng cách dán tên tệp vào dấu ngoặc vuông, tên trang tính và địa chỉ ô), thì nó chỉ hoạt động khi tệp gốc đang mở. Nếu đóng nó lại thì chúng ta sẽ gặp lỗi #LINK!
• GIÁN TIẾP không thể tham chiếu đến dải ô được đặt tên động. Ở chế độ tĩnh – không vấn đề gì.
• INDIRECT là một hàm dễ bay hơi hoặc “không ổn định”, tức là nó được tính toán lại cho bất kỳ thay đổi nào trong bất kỳ ô nào của trang tính chứ không chỉ ảnh hưởng đến các ô như trong các hàm thông thường. Điều này có ảnh hưởng xấu đến hiệu suất và tốt hơn hết là bạn không nên quá bận tâm với các bảng GIÁN TIẾP lớn.

• Cách tạo phạm vi động bằng tính năng tự động định cỡ
• Tính tổng một cửa sổ phạm vi trên một trang tính bằng chức năng OFFSET