Cách sử dụng hàm INDIRECT trong Excel để tham chiếu dễ hiểu nhất

Hàm INDIRECT được sử dụng để tham chiếu gián tiếp dữ liệu mà không làm thay đổi công thức trong ô. Bài viết sau đây sẽ hướng dẫn bạn cách dùng hàm INDIRECT trong Excel để tham chiếu một cách đơn giản và nhanh nhất.

1Công thức hàm INDIRECT

Hàm INDIRECT được sử dụng để tham chiếu gián tiếp các ô, dãy, các trang tính hoặc các bảng tính khác một cách nhanh chóng và tiện lợi mà không làm thay đổi công thức.

Tham chiếu là một giá trị cho phép chương trình truy xuất gián tiếp tới một số liệu cụ thể (một biến hay một bản ghi) trong bộ nhớ máy tính hay trong các thiết bị lưu trữ khác.

Ví dụ: Một ví dụ giúp bạn dễ hiểu hơn là: Trong quá trình phỏng vấn, bạn sẽ có một mục người tham chiếu để xác nhận năng lực của bản thân.

Vậy năng lực của bản thân là đối tượng được tham chiếu và người tham chiếu sẽ là người gián tiếp thể hiện, chứng minh năng lực của bạn.

Xem thêm: Hàm SUMIF

2Cách tạo tham chiếu từ các ô chứa giá trị

1. Tạo trực tiếp

Giả sử bạn có số 10 trong ô A2 và văn bản “A2″ trong ô B2.

Bước 1: Trong bảng dữ liệu Excel, bạn nhập hàm =INDIRECT(B2) vào ô tham chiếu bất kỳ để kiểm tra.

Bước 2: Nhấn Enter và kiểm tra kết quả. Lúc này hàm INDIRECT tham chiếu đến giá trị trong ô B2 là “A2” và nó được chuyển đến ô A2 – nơi nó lấy giá trị để trả về là 10.

Giải thích: Tham chiếu tại B2 là giá trị A2 tương ứng với vị trí A2 trong bảng Excel nên kết quả trả về sẽ là 10.

2. Tạo gián tiếp

Bước 1: Trong bảng dữ liệu Excel, bạn nhập hàm =INDIRECT(“A”&B2) vào ô tham chiếu bất kỳ để kiểm tra.

Giải thích hàm:

• “A”: Là ký tự dùng để chỉ cột A trong bảng tính.
• B2: Là giá trị tại ô B2 liên kết với chữ “A” để tạo ra ô tham chiếu truy xuất kết quả.

Ví dụ ở bài này, giá trị tại ô B2 là 3, bạn liên kết với chữ A thành A3, giá trị tại ô A3 là 3, đây là ô lấy giá trị để trả về và là 3.

Bước 2: Nhấn phím Enter để xem kết quả.

3Một số ứng dụng hàm INDIRECT

1. Cố định một tham chiếu là ô

Khi bạn chèn mới hoặc xóa các hàng hay cột hiện có trong trang tính sẽ làm thay đổi các tham chiếu ô. Vì vậy, bạn cần sử dụng hàm INDIRECT để biến dải tham chiếu thành chuỗi văn bản và giá trị sẽ không bị ảnh hưởng khi bạn chèn hoặc xóa hàng hay cột.

Bước 1: Trong bảng dữ liệu Excel, bạn nhập hàm =SUM(INDIRECT(“$A$2:$A$8”)) vào ô tham chiếu bất kỳ để kiểm tra.

Bước 2: Nhấn Enter và xem kết quả. Nếu bạn chèn một hàng vào thì dải A2:A8 vẫn không thay đổi vì Excel nhận ra rằng “A2:A8” chỉ là một chuỗi văn bản chứ không phải là một dải tham chiếu, nó sẽ không thực hiện bất kỳ thay đổi nào khi bạn chèn hoặc xóa một hàng.

2. Tạo tham chiếu tới trang tính khác

Giả sử bạn có một số dữ liệu quan trọng trong Bảng 1 và bạn muốn lấy dữ liệu đó qua từ Bảng 2.

Bước 1: Trong bảng dữ liệu Excel, bạn nhập hàm =INDIRECT(“”&$A$2&”!”&B2) vào ô tham chiếu bất kỳ để kiểm tra. Trong đó

• A2 là ô tham chiếu chứa tên Sheet muốn gọi, tên Sheet ở đây là Sheet2
• B2 là ô tham chiếu chứa cột dữ liệu muốn gọi, ở đây, dữ liệu ô B2 là A2 và giá trị tại ô A2 trong Sheet2 là 10.

Bước 2: Nhấn Enter và xem kết quả.

3. Sử dụng với các hàm excel khác

Ngoài hàm SUM, INDIRECT thường được sử dụng với các chức năng Excel khác như ROW, COLUMN, ADDRESS, VLOOKUP, SUMIF,…

Ví dụ: Sử dụng hàm INDIRECT kết hợp hàm ROW để tính giá trị trung bình.

Bước 1: Thường thì hàm ROW được sử dụng trong Excel để trả về một mảng các giá trị. Bạn có thể sử dụng công thức sau để trả lại trung bình của 3 số nhỏ nhất trong dải A2: A8. Lưu ý: Hãy nhấn Ctrl + Shift + Enter thay vì Enter nhé.

=AVERAGE(SMALL(A2: A8, ROW (INDIRECT (“1:3”)))))

Trong đó:

• A2: A8: Là dải tham chiếu.
• “1:3”: Là điều kiện lấy giá trị thỏa mãn.

Bước 2: Nhấn Enter và xem kết quả. Nếu bạn chèn một hàng hay xóa một hàng thì dải trong công thức hàm ROW 1:3 vẫn không thay đổi.

4Một số lỗi khi dùng hàm INDIRECT

1. Lỗi #REF!

Nguyên nhân: Thông thường công thức hàm INDIRECT sẽ trả về lỗi #REF! trong ba trường hợp:

• ref_text không phải là một tham chiếu ô hợp lệ.
• Vượt quá giới hạn dải tham chiếu (giới hạn của hàng là 1,048,576 hoặc giới hạn cột là 16,384).
• Một trang tham chiếu hoặc một bảng tính đã đóng lại.

Cách sửa lỗi: Kiểm tra điều chỉnh lại dữ liệu hoặc sử dụng chức năng hàm IFERROR.

2. Lỗi #NAME?

Nguyên nhân: Do nhập sai tên hàm hoặc lỗi ký tự.

Cách sửa lỗi: Kiểm tra điều chỉnh lại hàm tính.

Mời bạn tham khảo một số mẫu laptop đang được kinh doanh tại Điện máy XANH:

Trên đây là cách dùng hàm indirect trong Excel để tham chiếu. Nếu bạn còn bất kỳ thắc nào cần được giải đáp, bạn vui lòng để lại bình luận bên dưới để Điện máy XANH hỗ trợ bạn nhé!