Hàm INDIRECT trong Excel đượ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 thực hiện trên laptop HP với phiên bản Excel 2010, ngoài ra bạn cũng có thể thực hiện trên các phiên bản Excel 2003, 2007, 2013, 2016 với thao tác tương tự.
Công thức hàm INDIRECT trong Excel
Hàm INDIRECT trong Excel đượ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 trong Excel:
=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 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
Cách tạo tham chiếu từ các ô chứa giá trị
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 trong excel 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.
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ả.
Một số ứng dụng hàm INDIRECT trong Excel
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 trong excel để 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.
Xem thêm: Công thức hàm SUM
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ố 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ả.
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 trong excel 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.
Một số lỗi khi dùng hàm INDIRECT trong Excel
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.
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.
Xem thêm:
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 để Tin học văn phòng PRO hỗ trợ bạn nhé!
