Giới thiệu về hàm INDIRECT trong Excel - Công thức và ví dụ minh họa

1. Giới thiệu:

- Hàm Indirect trả về tham chiếu được chỉ rõ bởi một chuỗi văn bản. Hay nói cách khác, hàm Indirect được dùng để tham chiếu gián tiếp các ô, dãy, hoặc bảng tính khác.

- Dùng hàm Indirect khi bạn muốn thay đổi tham chiếu tới một ô trong một công thức mà không cần thay đổi chính công thức đó.

2. Cú pháp:

=INDIRECT(ref_text, [a1])

Trong đó:

• Ref text: Là tham chiếu tới một ô, là tên của một mảng, hoặc là một chuỗi văn bản.
• [a1] (không bắt buộc): Là giá trị logic chỉ rõ kiểu tham chiếu:
  • Nếu a1 là TRUE hoặc bỏ qua thì ref_text được hiểu là kiểu tham chiếu A1.
  • Nếu a1 là FALSE thì ref_text được hiểu là kiểu tham chiếu R1C1.

- Lưu ý:

• Nếu ref_text tham chiếu tới một sổ làm việc khác (tham chiếu ngoài), thì sổ làm việc đó phải đang mở. Nếu sổ làm việc nguồn không mở, thì hàm INDIRECT trả về giá trị lỗi #REF! .
• Nếu ref_text tham chiếu tới một phạm vi ô bên ngoài giới hạn hàng 1.048.576 hoặc giới hạn cột 16.384 (XFD), hàm INDIRECT trả về lỗi #REF! (Áp dụng với các phiên bản Excel từ 2007 trở đi).

3. Ví dụ:

a. Ví dụ 1: Trước hết, ta xét 1 ví dụ đơn giản để hiểu cách thức hoạt động của hàm Indirect:

- Giả sử trong ô A1 ta có số 10, ô C1 có văn bản “A1”.

Tại ô D1 ta lập công thức: =INDIRECT(C1)

Khi đó: Hàm Indirect sẽ tham chiếu tới giá trị trong ô C1 là A1, trả về kết quả là giá trị trong ô A1, đó là số 10.

- Nhập số 1 vào ô C2.

Tại ô D2 ta lập công thức: =INDIRECT("A"&C2)

"A"&C2 là sự kết hợp của văn bản “A” và số 1 trong ô C2, tức là công thức trên cũng sẽ đi đến ô A1 và trả về giá trị trong ô A1 là số 10.

b. Ví dụ 2: Sử dụng hàm Indirect cho các vùng đã đặt tên:

Chẳng hạn trong bảng dữ liệu sau, ta có các cột được đặt tên trong Name Box:

• C2:C8: Bút
• D2:D8: Vở
• E2:E8: Sách

Ta tính tổng của các cột Bút, Vở, Sách ra một bảng phụ riêng biệt, sử dụng hàm Indirect như sau:

Trong ô H1, ta điền tên các vùng mà ta muốn tính tổng (Bút, Sách, Vở).

Trong ô H2, ta lập công thức: =SUM(INDIRECT(H1))

• Nếu H1 là Bút, hàm trên sẽ tính tổng các ô từ C2:C8
• Nếu H1 là Vở, hàm trên sẽ tính tổng các ô từ D2:D8
• Nếu H1 là Sách, hàm trên sẽ tính tổng các ô từ E2:E8

Như vậy, ta chỉ cần thay đổi văn bản trong ô H1 mà không cần thay đổi công thức ở ô H2.

c. Ví dụ 3: Tạo công thức để lấy dữ liệu giữa các sheet:

Ta có bảng dữ liệu gồm các sheet như sau:

Tại sheet Tổng Hợp, ta cần tổng hợp doanh thu của các tháng ở các sheet phía sau:

Cấu trúc để tham chiếu đến một ô trong Sheet khác như sau: 'SheetName'!Cell

Khi đó, ta lập công thức trong ô B3 như sau: =INDIRECT("'"&A3&"'!"&"B8")

Trong đó:  "'"&A3&"'!"&"B8"  được hiểu là 'T1'!B8, tức là ô B8 của sheet T1. Do vậy, kết quả trả về sẽ là 528.074.200

Tương tự như vậy, tại sheet Tổng Hợp, bạn chỉ cần copy công thức trên xuống các dòng dưới để lấy được doanh thu của các tháng sau từ các sheet T2, T3, T4, T5, T6, với điều kiện ô B8 của các sheet đó cũng là ô tổng doanh thu.

Với các ví dụ trên, chắc hẳn các bạn đã nắm được cách thức hoạt động cũng như ứng dụng rất đa dạng của hàm Indirect. Hi vọng các bạn sẽ sử dụng tốt loại hàm thú vị này để tạo hiệu quả cao trong công việc!