Đánh giá lequocthai.com:
Hàm INDIRECT còn mở rộng khả năng của các hàm khác bằng cách tạo các tham chiếu ô động, cho phép thực hiện các phép tính khi tham chiếu thay đổi.
Cú pháp
Cú pháp của hàm INDIRECT như sau.
=INDIRECT(ref_text, [a1])Đối số
Hàm INDIRECT chấp nhận hai đối số, trong đó một đối số là bắt buộc. Chi tiết của từng đối số được mô tả dưới đây.
`ref_text` – Đối số bắt buộc, nhận giá trị của tham chiếu ô dưới dạng chuỗi văn bản. Giá trị đưa vào cũng có thể là một tham chiếu đã được đặt tên.
`a1` – Đối số tùy chọn, xác định kiểu tham chiếu được dùng trong `ref_text`. Mặc định là đúng (kiểu A1). Nếu đặt là sai, hàm sẽ sử dụng kiểu R1C1.
Các đặc điểm quan trọng của hàm INDIRECT
Một số tính năng nổi bật của hàm INDIRECT được liệt kê dưới đây.
- Nếu giá trị của đối số `ref_text` không phải là một tham chiếu ô hợp lệ, INDIRECT sẽ trả về lỗi #REF!.
- INDIRECT là cách dễ nhất để tham chiếu tới một sổ làm việc khác. Khi sổ làm việc được tham chiếu chưa mở, hàm sẽ trả về lỗi #REF!. Khi tên sheet hoặc sổ làm việc chứa dấu cách, cần bao quanh tên bằng dấu nháy đơn.
- Nếu phạm vi ô được chỉ định trong `ref_text` vượt quá giới hạn hàng hoặc cột của Excel, hàm sẽ trả về lỗi #REF!.
- Khi sử dụng kiểu tham chiếu R1C1 trong `ref_text`, nếu đối số thứ hai có giá trị đúng, INDIRECT sẽ trả về lỗi #REF!.
- Cần tránh các tham chiếu vòng (circular references) vì chúng có thể gây lỗi sự phụ thuộc vòng. Ngoài ra, vì hàm này là volatile (tái tính mỗi khi có thay đổi trên bảng tính), nên với bộ dữ liệu lớn có thể làm giảm hiệu năng.
Ví dụ về hàm INDIRECT
Hàm INDIRECT cho phép người dùng tạo các công thức động, danh sách thả xuống phân cấp, và nhiều hơn nữa. Dưới đây là một loạt ví dụ minh hoạ các cách áp dụng hàm này để nâng cao khả năng phân tích và trích xuất dữ liệu.
Ví dụ 1 – Sử dụng cơ bản hàm INDIRECT
Ban đầu, hàm INDIRECT có thể gây nhầm lẫn. Để giải thích, trong ví dụ này chúng ta sẽ đưa vào các giá trị đơn giản cho cả hai đối số của hàm để hiểu rõ hơn về cách hoạt động.
Trong trường hợp đầu tiên, giá trị đưa vào của `ref_text` là B2. Hàm INDIRECT sẽ truy cập ô B2, nơi chứa tham chiếu ô A2. Sau đó hàm trả về giá trị trong ô A2, tức là 10. Một cách khác để lấy giá trị ô A2 là đặt tham chiếu ô trong dấu ngoặc kép, biến nó thành một chuỗi văn bản, như minh hoạ trong ví dụ thứ hai.
Ví dụ cuối cùng cho thấy cách sử dụng kiểu tham chiếu R1C1, trong đó INDIRECT di chuyển tới ô B4 chứa tham chiếu tới ô A4 theo kiểu R1C1. Lưu ý rằng đối số thứ hai được đặt là sai, chỉ ra kiểu tham chiếu R1C1.
Bạn có thể nghĩ rằng chỉ cần gõ `=A2` là đã lấy được giá trị 10. Đúng, nhưng nếu chúng ta muốn luôn lấy giá trị hiện tại của ô A2 khi có cột hoặc hàng mới được chèn vào trước A2, công thức `=A2` sẽ tự động thay đổi thành `=B2` (khi chèn cột) hoặc `=A3` (khi chèn hàng). Sử dụng INDIRECT sẽ “khóa” tham chiếu vào ô A2 bất kể có thay đổi nào. Điều này sẽ được minh hoạ chi tiết hơn trong các ví dụ sau.
Hi vọng bạn đã nắm bắt được cách sử dụng hàm này và hiểu rõ hơn về khả năng của nó. Các ví dụ tiếp theo sẽ giới thiệu những ứng dụng thực tiễn và thú vị hơn, giúp bạn tận dụng INDIRECT hàng ngày.
Ví dụ 2 – Tạo các tham chiếu ô có tên động
Giả sử bạn là một giáo viên và đã tổng hợp điểm số của học sinh cho các môn học khác nhau trong Excel. Điểm của mỗi học sinh được lưu trong các cột riêng cho từng môn. Để tính tổng điểm của mỗi học sinh, cách thông thường là nhập thủ công phạm vi ô cho từng học sinh, ví dụ:
=SUM(B2:B6)
Để dễ dàng thay đổi tên học sinh, chúng ta sẽ ghi phạm vi ô vào ô B8 cho từng học sinh và dùng hàm INDIRECT:
=SUM(INDIRECT(B8))
Bây giờ chỉ cần thay đổi nội dung ô B8 là đã có tổng điểm cho học sinh tương ứng. Tuy nhiên cách này vẫn chưa tối ưu. Chúng ta có thể tạo định danh vùng (named ranges) cho mỗi học sinh và để INDIRECT tham chiếu tới các định danh vùng này.
Để tạo định danh vùng, vào Công thức > Tạo từ lựa chọn, chọn phạm vi B1:G6 và đặt tiêu chí là hàng đầu. Khi đó, mỗi học sinh sẽ có một định danh vùng riêng.
Sau khi có định danh vùng, thay thế các phạm vi ô bằng các tên đã đặt:
Để tiện lợi hơn, chúng ta có thể thêm danh sách xác thực dữ liệu vào ô B8.
Trên đây là trường hợp dữ liệu cùng một sheet. Khi mỗi học sinh có một worksheet riêng, chúng ta sẽ dùng INDIRECT để tạo tham chiếu động tới sheet.
Ví dụ 3 – Tạo tham chiếu worksheet động bằng hàm INDIRECT
Bây giờ dữ liệu của từng học sinh được lưu trên các worksheet riêng, mỗi worksheet được đặt tên theo tên học sinh.
Trong sheet Scorecard, chúng ta muốn lấy tổng điểm và điểm cao nhất của mỗi học sinh. Công thức truyền thống sẽ là:
=SUM(Emily!C2:C6)
=MAX(Emily!C2:C6)</code></pre>
Thay vì thay đổi thủ công tên sheet, chúng ta sẽ xây dựng một chuỗi văn bản đại diện cho tham chiếu đầy đủ và dùng INDIRECT để chuyển chuỗi đó thành tham chiếu thực tế.
Cú pháp sẽ là:
=INDIRECT(sheetname!cell_range)
Vì Excel tự động bao quanh tên sheet có dấu cách bằng dấu nháy đơn, chúng ta cần ghép dấu nháy đơn ở đầu và `'!` ở cuối. Đồng thời, đặt phần tham chiếu ô trong dấu ngoặc kép.
=INDIRECT("'"&sheetname&"'!"&"cell_range")
Giả sử ô C4 chứa tên học sinh, công thức sẽ là:
=SUM(INDIRECT("'"&$C$4&"'!"&"C2:C6"))
=MAX(INDIRECT("'"&$C$4&"'!"&"C2:C6"))</code></pre>
Như vậy, chỉ cần nhập tên học sinh (tên worksheet) vào ô C4 là đã nhận được dữ liệu mong muốn.
Ví dụ 4 – Tạo phạm vi cố định với INDIRECT
Giả sử bạn quản lý dữ liệu bán hàng hàng ngày. Để tính tổng doanh thu, công thức đơn giản là:
=SUM(C2:C10)
Nếu chèn một dòng mới ở đầu, Excel sẽ tự động điều chỉnh công thức thành `=SUM(C3:C11)`, làm bỏ qua giá trị ở ô C2. Để giữ phạm vi bắt đầu cố định ở C2, chúng ta dùng:
=SUM(INDIRECT("C2"):C10)
Ở đây, "C2" được coi là chuỗi, vì vậy không bị thay đổi khi chèn dòng mới. Ô cuối C10 vẫn tự động cập nhật.
Sau khi chèn một dòng mới ở đầu, công thức vẫn giữ nguyên phần bắt đầu:
=SUM(INDIRECT("C2"):C10)
Sự so sánh này cho thấy cách INDIRECT giúp công thức duy trì điểm bắt đầu cố định trong khi vẫn linh hoạt mở rộng phạm vi cuối.
Ví dụ 5 – Sử dụng INDIRECT với tham chiếu R1C1
Trong bảng dữ liệu bán hàng, mỗi ngày sẽ có một cột mới ghi tổng doanh thu. Để tự động lấy doanh thu của ngày mới nhất, chúng ta dùng kiểu R1C1.
Hàng là cố định (R7), nhưng cột thay đổi. Để tính số cột cuối cùng, dùng:
=COUNTA(7:7)
Sau đó, công thức INDIRECT sẽ là:
=INDIRECT("R7C"&COUNTA(7:7),FALSE)
Đối số thứ hai sai chỉ ra kiểu tham chiếu R1C1. Khi thêm cột mới, công thức tự cập nhật đúng giá trị.
Bạn có thể áp dụng tương tự cho dữ liệu nằm trên worksheet khác.
Ví dụ 6 – INDIRECT kết hợp Data Validation
Một cách hay khác của INDIRECT là tạo danh sách thả xuống phụ thuộc (dependent drop‑down) qua Xác thực dữ liệu.
Đầu tiên, tạo định danh vùng cho các cột dữ liệu (qua Công thức > Quản lý tên > Mới hoặc nhập trực tiếp trong Hộp tên).
Sau đó, vào Dữ liệu > Xác thực dữ liệu, chọn Danh sách và nhập tên vùng vào ô Nguồn:
=Team_Name
Để tạo danh sách phụ thuộc, tạo một Xác thực dữ liệu nữa, nhưng trong Nguồn nhập công thức:
=INDIRECT($A$10)
Trong đó $A$10 là ô chứa lựa chọn đầu tiên (tên đội). Khi người dùng chọn một đội, danh sách thứ hai sẽ tự động hiển thị các thành viên tương ứng.
Bạn có thể tiếp tục tạo các danh sách phụ thuộc tiếp theo dựa trên lựa chọn thứ hai.
INDIRECT kết hợp VLOOKUP
Giả sử bạn có bảng doanh thu cho sáu cửa hàng trong các tháng. Để tra cứu doanh thu tổng của một sản phẩm trong tháng được chọn, cách truyền thống là dùng VLOOKUP kết hợp IF, nhưng không hiệu quả.
Cách nhanh hơn là dùng INDIRECT + VLOOKUP. Đầu tiên, tạo định danh vùng cho từng tháng (ví dụ `Jan_Sales`, `Feb_Sales`, …). Sau đó, tạo bảng tóm tắt cho người dùng nhập Store Location (ô B10) và Month (ô B11).
Công thức lấy bộ dữ liệu tương ứng:
=INDIRECT(B11&"_Sales")
Sau đó, dùng VLOOKUP:
=VLOOKUP(B10,INDIRECT(B11&"_Sales"),2,FALSE)
Công thức này tìm vị trí cửa hàng trong bộ dữ liệu của tháng đã chọn và trả về doanh thu tổng.
Bạn cũng có thể dùng Xác thực dữ liệu để tạo danh sách lựa chọn cho Store Location.
Kết hợp INDIRECT và VLOOKUP cho phép tra cứu dữ liệu một cách linh hoạt dựa trên đầu vào của người dùng. Bạn có thể áp dụng cùng logic cho các worksheet riêng biệt nếu tên sheet trùng với tên bộ dữ liệu.
Bạn đã có trong tay một công cụ mạnh mẽ – hàm INDIRECT – để tạo ra các bảng tính Excel động và tương tác. Hãy thực hành, khám phá các ứng dụng khác nhau và chuẩn bị cho những hàm Excel hữu ích tiếp theo mà chúng tôi sẽ giới thiệu.
