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

Biên tập bởi Bùi Thị Kim Lượng

Cập nhật 4 tháng trước

10.537

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.

Bài hướng dẫn được triển khai trên máy tính HP với phiên bản Excel 2010, ngoài những bạn cũng hoàn toàn có thể thực thi trên những phiên bản Excel 2003, 2007, 2013, năm nay với thao tác tựa như .

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.

Cú pháp hàm INDIRECT :

=INDIRECT (ref_text, a1)

Trong đó :

• ref_text: Là một ô có chưa kiểu tham chiếu hay một tham chiếu đến ô ở dạng một chuỗi văn bản hoặc một mảng mà đã được đặt tên.
• a1: Là một giá trị logic chỉ rõ kiểu tham chiếu của đối số ref_text.

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 quy 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ượng của bản thân .
Vậy năng lượng của bản thân là đối tượng người dùng được tham chiếu và người tham chiếu sẽ là người gián tiếp bộc lộ, chứng tỏ năng lượng 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 link 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 những hàng hay cột hiện có trong trang tính sẽ làm đổi khác những 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ị tác động ả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.

Xem thêm: Công thức : Công thức hàm SUM

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

Cấu trúc hàm :

=INDIRECT(“” & Sheet’s name & “!” & Cell to pull data from)

Trong đó :

• “”, & và !: Là những ký tự liên kết các giá trị trong hàm.
• Sheet’s name: Là tên Sheet tính trong bảng tính.
• Cell to pull data from: Là tên cột bạn muốn gọi dữ liệu.

Giả sử bạn có một số ít tài liệu quan trọng trong Bảng 1 và bạn muốn lấy tài 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 những tính 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.

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 vẻ để lại phản hồi bên dưới để Điện máy XANH tương hỗ bạn nhé !