Hàm INDIRECT trong Excel là hàm tham chiếu gián tiếp dữ liệu và nếu bạn chưa biết cách sử dụng hàm này. Hãy cùng theo dõi bài viết sau để hiểu rõ hơn về hàm INDIRECT cũng như cách sử dụng hàm này nhé.
Cách sử dụng hàm INDIRECT tham chiếu gián tiếp dữ liệu trong Excel cực đơn giản có ví dụ minh họa dễ hiểu.
I. Hàm INDIRECT là gì? Ứng dụng của hàm INDIRECT trong Excel
Hàm INDIRECT là hàm đượ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 với cú pháp: =INDIRECT (ref_text, [a1])
Trong đó:
- Ref_text: Là một ô 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 xác định loại tham chiếu của đối số ref_text.
Ví dụ: Hãy sử dụng hàm INDIRECT để lấy giá trị tham chiếu gián tiếp trong bảng sau.
Bước 1: Trong bảng dữ liệu tính Excel, bạn nhập hàm =INDIRECT(C4) vào ô tham chiếu bất kỳ để kiểm tra.
Ví dụ minh họa hàm INDIRECT.
Bước 2: Nhấn phím Enter để hiển thị kết quả.
Nhấn Enter để hiển thị kết quả.
II. Các ứng dụng của hàm INDIRECT
1. Tạo các tham chiếu INDIRECT từ các ô chứa giá trị
Ví dụ: Sử dụng hàm INDIRECT để tham chiếu gián tiếp dữ liệu trong bảng sau.
Bước 1: Trong bảng dữ liệu tính Excel, bạn nhập hàm =INDIRECT(C4) vào ô tham chiếu bất kỳ để kiểm tra.
Ví dụ minh họa hàm INDIRECT.
Bước 2: Nhấn phím Enter để hiển thị kết quả và dùng trỏ chuột kéo xuống để hiển thị các kết quả còn lại như hình.
Nhấn Enter để hiển thị kết quả.
2. Tạo các tham chiếu gián tiếp từ các giá trị ô và văn bản
Ví dụ: Sử dụng hàm INDIRECT để tham chiếu gián tiếp dữ liệu trong bảng sau.
Bước 1: Trong bảng dữ liệu tính Excel, bạn nhập hàm =INDIRECT(“B”&C4) vào ô tham chiếu bất kỳ để kiểm tra.
Giải thích hàm:
- INDIRECT: Là lệnh hàm.
- “B”: Là ký tự dùng để chỉ cột B trong bảng tính.
- C4: Là giá trị liên kết với chữ B để tạo ra ô tham chiếu truy xuất kết quả.
Ví dụ minh họa hàm INDIRECT tạo các tham chiếu gián tiếp từ các giá trị ô và văn bản
Bước 2: Nhấn phím Enter để hiển thị kết quả và dùng trỏ chuột kéo xuống để hiển thị các kết quả còn lại như hình.
Nhấn Enter để hiển thị kết quả.
3. Sử dụng hàm INDIRECT với các vùng đã đặt tên
Ví dụ: Sử dụng hàm INDIRECT để tham chiếu gián tiếp với các vùng dữ liệu đã đặt tên trong bảng sau.
Giả sử mình có các vùng tham chiếu được đặt tên như sau:
- Vùng tham chiếu B4:B8 ở cột Số liệu 1 tương ứng với tên là GT
- Vùng tham chiếu C4:C8 ở cột Số liệu 2 tương ứng với tên là GTR
Bước 1: Trong bảng dữ liệu tính Excel, bạn nhập các lệnh hàm như hình minh họa vào ô tham chiếu bất kỳ để kiểm tra.
Giải thích hàm:
- INDIRECT: Là lệnh hàm.
- G4: Là ô tham chiếu chứa vùng dữ liệu đã đặt tên.
Ví dụ minh họa hàm INDIRECT với các vùng đã đặt tên.
Bước 2: Nhấn phím Enter để hiển thị kết quả như hình.
Nhấn Enter để hiển thị kết quả.
4. Công thức INDIRECT – tự động tham chiếu đến một trang tính khác
Để công thức hàm INDIRECT có thể tự động tham chiếu đến một trang tính khác bạn hãy áp dụng công thức tổng quát sau:
Cấu trúc hàm: =INDIRECT(“” & Sheet’s name & “!” & Cell to pull data from)
Trong đó:
- INDIRECT: Là lệnh hàm
- “”, & 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.
Ví dụ: Sử dụng hàm INDIRECT để tham chiếu gián tiếp dữ liệu trong bảng sau.
Giả sử minh có sheet tính chứa giá trị như hình với tên là My Sheet.
Bảng ví dụ minh họa.
Bước 1: Trong bảng dữ liệu tính Excel, bạn nhập hàm =INDIRECT(“‘”&$A$1&”‘!”&B1) vào ô tham chiếu bất kỳ để kiểm tra.
Giải thích hàm:
- INDIRECT: Là lệnh hàm
- “”, &, !: Là những ký tự liên kết các giá trị trong hàm.
- A1: Là ô tham chiếu chứa tên Sheet muốn gọi.
- B1: Là ô tham chiếu chứa cột dữ liệu muốn gọi.
Ví dụ minh họa hàm INDIRECT tham chiếu đến một trang tính khác.
Bước 2: Nhấn phím Enter để hiển thị kết quả và dùng trỏ chuột kéo xuống để hiển thị các kết quả còn lại như hình.
Nhấn Enter để hiển thị kết quả.
5. Tạo một tham chiếu động đến một bảng tính khác
Để công thức hàm INDIRECT có thể tự động tham chiếu đến một bảng tính (File) khác bạn hãy áp dụng công thức tổng quát sau:
Cấu trúc hàm: =INDIRECT(“[” & Book name & “]” & Sheet name & “!” & Cell address)
Trong đó:
- INDIRECT: Là lệnh hàm
- “”, & và !: Là những ký tự liên kết các giá trị trong hàm.
- Book name: Là tên của bảng tính bạn muốn gọi.
- Sheet name: Là tên Sheet tính trong bảng tính.
- Cell address: Là tên cột bạn muốn gọi dữ liệu.
Ví dụ: Sử dụng hàm INDIRECT để tham chiếu gián tiếp dữ liệu trong bảng sau.
Giả sử mình có tên bảng tính “ví dụ” chứa dữ liệu sau với tên Sheet là My Sheet.
Bảng ví dụ minh họa.
Bước 1: Trong bảng dữ liệu tính Excel, bạn nhập hàm =INDIRECT(“(“&$A$4&”)”&$B$4&”!”&C4) vào ô tham chiếu bất kỳ để kiểm tra.
Giải thích hàm:
- INDIRECT: Là lệnh hàm
- “”, &, !: Là những ký tự liên kết các giá trị trong hàm.
- A4: Là ô tham chiếu chứa tên bảng tính muốn gọi.
- B4: Là ô tham chiếu chứa tên Sheet muốn gọi.
- C4: Là ô tham chiếu chứa cột dữ liệu muốn gọi.
Ví dụ minh họa hàm INDIRECT tham chiếu động đến một bảng tính khác.
Bước 2: Nhấn phím Enter để hiển thị kết quả và dùng trỏ chuột kéo xuống để hiển thị các kết quả còn lại như hình.
Nhấn Enter để hiển thị kết quả.
6. Sử dụng hàm INDIRECT để cố định một tham chiếu ô
- Khi bạn chèn mới hoặc xóa các hàng hay cột hiện có trong một trang tính sẽ làm công thức đã tính bị ảnh hưởng và thay đổi khi có một dòng mới được chèn vào. Việc này sẽ ảnh hưởng trực tiếp đến kết quả tính.
- Để khắc phục vấn đề trên, bạn sử dụng hàm INDIRECT để biến dải tham chiếu thành chuỗi văn bản. Khi đó, hàm sẽ không thực hiện bất kỳ thay đổi nào khi bạn chèn hoặc xóa hàng hay cột.
Ví dụ: Sử dụng hàm INDIRECT để cố định dải tham chiếu dữ liệu trong bảng sau.
Bước 1: Trong bảng dữ liệu tính Excel, bạn nhập hàm =SUM(INDIRECT(“$B$5:$B$7”)) vào ô tham chiếu bất kỳ để kiểm tra.
Ví dụ minh họa hàm INDIRECT để cố định một tham chiếu ô.
Bước 2: Nhấn phím Enter để hiển thị kết quả.
Nhấn Enter để hiển thị kết quả.
7. Sử dụng hàm INDIRECT với các hàm Excel
Ví dụ: Sử dụng hàm INDIRECT kết hợp hàm ROW để tính giá trị trung bình trong bảng sau.
Bước 1: Trong bảng dữ liệu tính Excel, bạn nhập hàm =AVERAGE(SMALL(B5:B9,ROW(INDIRECT(“1:3”)))) vào ô tham chiếu bất kỳ để kiểm tra.
Giải thích hàm:
- AVERAGE, SMALL, ROW và INDIRECT: Là lệnh hàm.
- B5:B9: Là dải tham chiếu.
- (“1:3”): Là điều kiện lấy giá trị thỏa mãn.
Ví dụ minh họa hàm INDIRECT kết hợp hàm AVERAGE, ROW.
Bước 2: Nhấn tổ hợp phím Ctrl + Shift + Enter để hiển thị kết quả.
Nhấn tổ hợp phím Ctrl + Shift + Enter để hiển thị kết quả.
III. Các lỗi thường gặp khi sử 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 đã được đóng lại.
Lỗi #REF! trong hàm INDIRECT.
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 hàm bị lỗi ký tự hoặc sai tên hàm.
Lỗi #NAME? trong hàm INDIRECT.
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 sử dụng hàm INDIRECT tham chiếu gián tiếp dữ liệu trong Excel cực đơn giản, có ví dụ minh họa dễ hiểu. Hy vọng bài viết sẽ giúp bạn thực hiện được hàm INDIRECT trong công việc cũng như học tập và nếu bạn có góp ý hãy để lại bình luận bên dưới và đứng quên chia sẻ nếu thấy hữu ích bạn nhé.
