=INDIRECT(E2) đọc ô có địa chỉ được ghi dưới dạng văn bản trong E2. Nếu E2 ghi C4, công thức trả về giá trị ở C4. Địa chỉ cũng có thể được ghép từ nhiều phần: =INDIRECT("C"&E3) đọc cột C ở hàng có số ghi trong E3.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Address | Value | |
| 2 | Apple | Fruit | $1.20 | C4 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | 6 | $1.10 | |
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
F2 đọc C4, giá của Carrot, 1,10. Đổi E3 thành 2 để lấy giá của Apple.
Cú pháp hàm INDIRECT
=INDIRECT(ref_text, [a1])
ref_text: văn bản ghi một tham chiếu:"C4","B2:B6","Prices!A2","'Price list'!A2:B9".a1:TRUEhoặc bỏ trống cho địa chỉ kiểu A1.FALSEđọc theo kiểu R1C1, trong đó"R4C3"nghĩa là hàng 4, cột 3, phù hợp khi cả hàng và cột đều là số.
Nếu văn bản không phải địa chỉ hợp lệ, kết quả là #REF!. INDIRECT trả về một tham chiếu thật, nên nó dùng được trong SUM, COUNTIF, VLOOKUP và mọi hàm nhận một vùng.
Tạo vùng từ các con số
Địa chỉ có thể là cả một vùng. Ghép một số vào địa chỉ sẽ cho một vùng có kích thước lấy từ một ô.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Rows | Total | ||
| 2 | Jan | 4,200 | 3 | 12,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 |
Với số 3 ở E2, văn bản thành B2:B4, và F2 cộng từ Jan đến Mar: 12.900. Đặt E2 thành 6 để lấy nửa năm, 27.900. 1+E2 có mặt vì dữ liệu bắt đầu từ hàng 2. Cùng tổng đó có thể viết không cần INDIRECT, =SUM(B2:INDEX(B2:B7,E2)), cách này không volatile; trang OFFSET so sánh các lựa chọn.
Tham chiếu tới trang tính có tên ghi trong một ô
Tên trang tính cũng có thể lấy từ một ô. Nhờ đó một công thức tổng hợp trở thành phép tra cứu qua nhiều trang tính: mỗi hàng đọc trang tính có tên ghi ở cột A. Dấu nháy đơn quanh tên giúp công thức vẫn chạy với tên có dấu cách.
| A | B | |
|---|---|---|
| 1 | Month | Total |
| 2 | Jan | 12,500 |
| 3 | Feb | 12,200 |
| 4 | Mar | 13,700 |
B2 ghép thành văn bản 'Jan'!B2:B4 và tính tổng: 12.500. B3 và B4 là cùng công thức kéo xuống, nên chúng đọc Feb (12.200) và Mar (13.700). Mở thẻ Feb và đổi một con số: bảng tổng hợp cập nhật theo. Gõ Feb đè lên Jan ở A2 và B2 sẽ tính tổng của Feb. Phần B2:B4 trong dấu nháy là văn bản, nên nó không đổi khi kéo công thức xuống; chỉ tham chiếu A2 thay đổi.
Danh sách thả xuống phụ thuộc
Một danh sách thả xuống thứ hai có các mục phụ thuộc vào danh sách đầu tiên là việc kinh điển của INDIRECT. Trong Excel, cách thiết lập thường dùng là:
- Đặt các mục của mỗi nhóm vào một cột và đặt tên mỗi vùng theo tên nhóm: chọn các cột cùng tiêu đề rồi dùng Formulas > Create from Selection > Top row. Thao tác này tạo ra các tên
Fruit,VegetablevàDairy. - Cho A2 một danh sách các nhóm: Data > Data Validation > Allow: List, Source
Fruit,Vegetable,Dairy. - Cho B2 một danh sách với Source là
=INDIRECT(A2). Khi A2 ghi Fruit, danh sách đọc vùng có tên Fruit.
Trang tính dưới đây làm điều tương tự nhưng dùng mỗi nhóm một trang tính thay cho vùng được đặt tên. D2 dùng INDIRECT để trả tràn các mục của trang tính có tên ở A2, và danh sách ở B2 đọc D2:D4.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Item | Items for the category | |
| 2 | Fruit | Apple | Apple | |
| 3 | Pear | |||
| 4 | Plum |
Chọn Dairy ở A2: D2:D4 chuyển thành Milk, Butter, Cheese, và các lựa chọn ở B2 cũng vậy. B2 giữ giá trị cũ cho đến khi bạn chọn giá trị mới; Excel cũng làm như vậy, nên các biểu mẫu thường thêm một ô kiểm tra như =COUNTIF(D2:D4,B2)>0 bên cạnh mục đó. Trong Excel 365, bạn có thể bỏ qua vùng được đặt tên và trỏ danh sách thứ hai tới một công thức trả tràn, ví dụ =INDIRECT("'"&A2&"'!A2:A4") ở một ô phụ và =D2# làm Source. Trang danh sách thả xuống trình bày phần còn lại của cách thiết lập.
INDIRECT là hàm volatile và bỏ qua hàng được chèn
Vì INDIRECT đọc văn bản chứ không đọc tham chiếu, nó có hai tác dụng phụ:
- Nó tính lại sau mỗi thay đổi. Excel không biết một đoạn văn bản sẽ trỏ tới ô nào, nên tính lại mọi INDIRECT sau bất kỳ chỉnh sửa nào trong workbook. Vài chục hàm thì không sao; hàng chục nghìn hàm khiến mỗi lần gõ phím đều chậm. INDEX với số thứ tự hàng (
=INDEX(C:C,E3)) cho cùng kết quả như=INDIRECT("C"&E3)và chỉ tính lại khi đầu vào của nó thay đổi. - Địa chỉ không dịch chuyển. Chèn một hàng phía trên hàng 4 thì
=C4thành=C5, nhưng=INDIRECT("C4")vẫn đọc C4, giờ đã là một hàng khác. Đôi khi đó chính là mục đích, một tham chiếu phải đứng yên ở một ô cố định dù trang tính thay đổi thế nào. Nhưng thường thì đó là một bug chờ ai đó chèn thêm hàng.
INDIRECT trỏ sang workbook khác chỉ chạy khi workbook đó đang mở; nếu nó đóng, kết quả là #REF!.
Luyện tập: lấy giá từ số thứ tự hàng
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Row | Price | |
| 2 | Apple | Fruit | $1.20 | 5 | ||
| 3 | Pear | Fruit | $1.50 | |||
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
Đến lượt bạn: Trong F2, dùng INDIRECT để trả về giá ở cột C, tại hàng có số ghi trong E2.
Câu hỏi thường gặp
Hàm INDIRECT trong Excel làm gì?
Hàm này biến văn bản thành tham chiếu. =INDIRECT("C4") trả về giá trị của C4, còn =INDIRECT(E2) trả về giá trị của ô có địa chỉ được ghi trong E2. Địa chỉ có thể ghép bằng &, nên =INDIRECT("C"&E2) đọc cột C ở hàng có số ghi trong E2.
Làm sao tham chiếu sang trang tính khác có tên ghi trong một ô?
Ghép địa chỉ với tên trang tính đặt trong dấu nháy đơn: =INDIRECT("'"&A2&"'!B2"). Dấu nháy giúp công thức vẫn chạy với tên có dấu cách. =SUM(INDIRECT("'"&A2&"'!B2:B4")) tính tổng một vùng trên trang tính đó.
Vì sao INDIRECT trả về #REF!?
Văn bản không phải là địa chỉ hợp lệ, hoặc nó ghi tên một trang tính không tồn tại, hoặc nó trỏ sang một workbook khác đang đóng. Hãy kiểm tra văn bản mà công thức ghép ra bằng cách đặt riêng biểu thức đó vào một ô, không có INDIRECT.
INDIRECT có phải hàm volatile không?
Có. Excel tính lại mọi INDIRECT mỗi khi có thay đổi ở bất kỳ đâu trong workbook, vì nó không biết trước văn bản sẽ trỏ tới ô nào. Vài hàm thì không sao; hàng nghìn hàm sẽ làm workbook chậm đi. INDEX thường làm được cùng việc mà không volatile.
