Phương pháp dùng hàm INDIRECT trong Excel đơn giản

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 dùng hàm này. Hãy cùng mygear.vn tìm hiểu bài viết sau để hiểu rõ hơn về hàm INDIRECT cũng như phương pháp dùng hàm này nhé.

I. Hàm INDIRECT là gì? Vận dụng của hàm INDIRECT trong Excel

Hàm INDIRECT là hàm tham chiếu gián tiếp tới những ô, trang hoặc bảng tính khác 1 cách dễ dàng và nhanh chóng mà không làm thay đổi công thức. Hàm có cú pháp: =INDIRECT (ref_text, [a1])

Trong đó:

• Ref_text: Là một ô tham chiếu hoặc 1 tham chiếu tới ô ở dạng 1 chuỗi văn bản hoặc một vùng được đặt tên sẵn.
• a1 (không bắt buộc): Là giá trị logic xác định ở kiểu tham chiếu của đối số ref_text.

Ví dụ: Hãy dùng hàm INDIRECT lấy giá trị tham chiếu gián tiếp tại bảng dưới.

Bước 1: Trong bảng tính Excel, điền hàm =INDIRECT(C4) vào ô bất kỳ để kiểm tra.

Ví dụ

Bước 2: Ấn phím Enter để hiển thị kết quả.

Ấn Enter

II. Các vận dụng của hàm INDIRECT

1. Tạo những tham chiếu INDIRECT từ những ô chứa giá trị

Ví dụ: Dùng hàm INDIRECT để tham chiếu gián tiếp số liệu trong bảng sau.

Bước 1: Trong bảng tính Excel, điền hàm =INDIRECT(C4) vào ô bất kỳ để kiểm tra.

Ví dụ

Bước 2: Ấn phím Enter để hiển thị kết quả và dùng trỏ chuột kéo xuống để hiển thị những kết quả còn lại như ảnh.

Nhấn Enter

2. Thiết lập những tham chiếu từ các ô và văn bản

Ví dụ: Dùng hàm INDIRECT để tham chiếu dữ liệu tại bảng dưới.

Bước 1: Trong bảng tính Excel, điền hàm =INDIRECT("B"&C4) vào ô bất kỳ để rà soát.

Giảng giải hàm:

• INDIRECT: Là lệnh hàm.
• "B": Là chữ chỉ cột B trong bảng tính.
• C4: Là giá trị liên kết với ký tự B để hình thành ô tham chiếu xuất kết quả.

Ví dụ minh họa hàm INDIRECT

Bước 2: Ấn phím Enter để hiện kết quả và dùng chuột kéo xuống các ô dưới để hiện kết quả còn lại.

Nhấn Enter

3. Dùng hàm INDIRECT với các vùng đã đặt tên

Ví dụ: Dùng hàm INDIRECT để tham chiếu gián tiếp với những vùng dữ liệu đã đặt tên trong bảng sau.

Ta có tên của các vùng tham chiếu như sau:

• Vùng tham chiếu B4:B8 tại cột Số liệu 1 tương ứng có tên là GT.
• Vùng tham chiếu C4:C8 tại cột Số liệu 2 tương ứng có tên là GTR.

Bước 1: Trong bảng dữ liệu tính Excel, bạn nhập những lệnh hàm như ảnh bên dưới vào ô tham chiếu bất kỳ để rà soát.

Giải thích hàm:

• INDIRECT: Là lệnh hàm.
• G4: Là ô tham chiếu đã được đặt tên chứa vùng dữ liệu.

Ví dụ

Bước 2: Ấn phím Enter để hiển thị kết quả như hình.

Lưu ý: Đây là kết quả tính cho cột Số liệu 1. Bạn có thể làm cho cột Số liệu 2 bằng phương pháp thay tên GTR vào ô tham chiếu G4.

Nhấn Enter

4. Hàm INDIRECT – tự động tham chiếu tới 1 trang tính khác

Để hàm INDIRECT tự động tham chiếu tới một trang tính khác hãy vận dụng như 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à ký tự liên kết những giá trị trong hàm.
• Sheet's name: Là tên Sheet trong bảng tính.
• Cell to pull data from: Tên cột muốn gọi dữ liệu.

Ví dụ: Dùng hàm INDIRECT để tham chiếu dữ liệu tại bảng dưới.

Giả sử có Sheet tính chứa giá trị như ảnh mang tên là My Sheet.

Bảng ví dụ minh họa

Bước 1: Trong bảng tính Excel, điền hàm =INDIRECT("'"&$A$4&"'!"&B4) vào ô ngẫu nhiên để kiểm tra.

Giải thích hàm:

• INDIRECT: Là lệnh hàm
• "", &, !: Là các 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 rà soát.
• B4: Là ô tham chiếu chứa cột số liệu muốn gọi.

Ví dụ

Bước 2: Ấn phím Enter để hiển thị kết quả và dùng trỏ chuột kéo xuống để hiển thị những kết quả còn lại như hình.

Nhấn Enter

5. Hình thành một tham chiếu tự động đến một bảng tính khác

Để hàm INDIRECT có thể tự động tham chiếu tới 1 bảng tính (File) khác bạn hãy vận dụng công thức tổng quát sau:

Công thức HÀM: =INDIRECT("[" & Book name & "]" & Sheet name & "!" & Cell address)

Trong đó:

• INDIRECT: Là lệnh hàm
• "", & và !: Là các ký tự liên kết các trị giá trong hàm.
• Book name: Là tên bảng tính muốn gọi.
• Sheet name: Là tên Sheet trong bảng tính muốn gọi.
• Cell address: Là tên cột muốn gọi dữ liệu.

Ví dụ: Dùng hàm INDIRECT để tham chiếu dữ liệu tại bảng dưới.

Giả sử có bảng tính với tên Sheet là My Sheet.

Bảng ví dụ

Bước 1: Trong bảng tính Excel, nhập hàm =INDIRECT("("&$A$4&")"&$B$4&"!"&C4) vào ô ngẫu nhiên để kiểm tra.

Giải thích hàm:

• INDIRECT: Là lệnh hàm.
• "", &, !: Là các ký tự liên kết những giá trị trong hàm.
• A4: Là ô tham chiếu chứa tên bảng tính muốn rà soát.
• B4: Là ô tham chiếu có tên Sheet muốn gọi.
• C4: Là ô tham chiếu có cột dữ liệu muốn gọi.

Ví dụ

Bước 2: Ấn phím Enter để hiển thị kết quả và sử 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

6. Dùng hàm INDIRECT để cố định dải tham chiếu ô

• Khi bạn chèn mới hoặc xóa các hàng hay cột hiện với trong 1 trang tính sẽ khiến 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 kết quả tính.
• Để giải quyết vấn đề trên, bạn dùng hàm INDIRECT để biến dải tham chiếu thành chuỗi văn bản. Khi đấy, hàm sẽ không thực hành bất kỳ thay đổi nào lúc bạn chèn hoặc xóa hàng hay cột.

Ví dụ: Dùng hàm INDIRECT để cố định dải tham chiếu số liệu trong bảng sau.

Bước 1: Trong bảng Excel, điền hàm =SUM(INDIRECT("$B$5:$B$7")) vào ô tham chiếu bất kỳ để kiểm tra.

Ví dụ

Bước 2: Ấn phím Enter để hiển thị kết quả.

Nhấn Enter

7. Dùng hàm INDIRECT với những hàm Excel

Lưu ý: Việc dùng phối hợp với hàm INDIRECT là nhằm mục đích KHÔNG làm ảnh hưởng kết quả khi thêm hay xóa dòng, cột.

Ví dụ: Dùng hàm INDIRECT phối hợp hàm ROW để tính giá trị trung bình trong bảng sau.

Bước 1: Trong bảng tính Excel, nhập hàm =AVERAGE(SMALL(B5:B9,ROW(INDIRECT("1:3")))) vào ô tham chiếu bất kỳ để rà soát.

Giải thích hàm:

• AVERAGE, SMALL, ROW và INDIRECT: Là tên hàm.
• B5:B9: Là dải tham chiếu.
• ("1:3"): Là điều kiện lấy giá trị thỏa yêu cầu.

Ví dụ

Bước 2: Ấn tổ hợp phím Ctrl + Shift + Enter để hiển thị kết quả.

Ấn tổ hợp phím Ctrl + Shift + Enter

III. Những lỗi thường gặp lúc sử dụng hàm INDIRECT

1. Lỗi #REF!

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

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

Lỗi #REF!

Cách thức sửa lỗi: Rà soát điều chỉnh lại thông tin hoặc dùng hàm IFERROR.

2. Lỗi #NAME?

Nguyên nhân: Do điền hàm bị lỗi ký tự hoặc sai tên hàm.

Lỗi #NAME? trong hàm INDIRECT

Cách thức sửa lỗi: Rà soát điều chỉnh lại hàm tính.

Trên đây mình đã hướng dẫn dùng hàm INDIRECT tham chiếu gián tiếp số liệu trong Excel đơn giản, dễ hiểu. Hy vọng bài viết sẽ giúp bạn nâng cao được kỹ năng dùng hàm INDIRECT trong công việc. Nếu bạn có góp ý hãy để lại bình luận bạn nhé.