Hàm Indirect trong Excel là hàm tham chiếu gián tiếp các dữ liệu hỗ trợ người dùng trong quá trình thống kê và tính toán số liệu. Tuy nhiên hàm còn có rất nhiều những ứng dụng thực tế hiệu quả bất ngờ cho người sử dụng. Hãy cùng khám phá ngay trong bài viết dưới đây để tìm hiểu về hàm Indirect này nhé!
Xem thêm: Cách dùng hàm VLOOKUP trong Excel kèm ví dụ cụ thể
Xem thêm: Cách sử dụng hàm ABS trong Excel để giá trị tuyệt đối chính xác
Mục lục bài viết
Hàm INDIRECT là gì? Công thức
Hàm Indirect trong Excel là hàm được sử dụng để giúp người sử dụng tham chiếu gián tiếp các ô giá trị trong trang tính. Hoặc có thể là các dãy, bảng tính số liệu,… một cách nhanh chóng, tiện lợi và chuyên nghiệp. Hàm này sẽ không làm thay đổi các công thức
Hàm Indirect trong Excel có thể sử dụng ở nhiều phiên bản Excel khác nhau: Excel 2007, 2010, 2013, 2016, 2109 và trên các phiên bản có cùng hệ điều hành. Ngay cả hệ điều hành MACOS cũng thao tác tương tự như hướng dẫn được chia sẻ trong bài viết
Công thức
=INDIRECT (ref_text, [a1])
Trong đó
- Ref_text: Ô tham chiếu hay một tham chiếu đến ô bất kỳ ở dạng một chuỗi văn bản hoặc một mảng mà đã được đặt tên sẵn trước đó
- a1: Là một giá trị logic xác định rõ loại tham chiếu của đối số ref_text được khai báo
Ví dụ minh hoạ
Sử dụng hàm Indirect để lấy giá trị tham chiếu gián tiếp trong bảng số liệu dưới đây
Bước 1: Thực hiện nhập hàng vào ô trống liền kệ trong cột kết quả muốn hiển thị theo cú pháp
=INDIRECT(C4)
Bước 2: Nhấn Enter để hiển thị kết quả sau khi sử dụng hàm Indirect
Chức năng hàm INDIRECT trong Excel
Sử dụng hàm INDIRECT để cố định một tham chiếu ô
Đối với trường hợp bạn thực hiện chèn mới hoặc thao tác xoá các cột hay các hàng có trong trang tính thì cần chú ý tránh ảnh hưởng đến công thức. Sự thay đổi này sẽ diễn ra khi bạn thực hiện thao tác chèn thêm một dòng mới vào bảng tính. Kết quả tính sẽ bị ảnh hưởng bởi thao tác chèn thêm cột này
Tuy nhiên để khắc phục vấn đề trên bạn có thể sử dụng hàm Indirect để giải quyết. Hàm sẽ biến dải tham chiếu thành chuỗi văn bản. Hàm sẽ không thực hiện bất kỳ thay đổi nào trong thời gian đó. Nếu bạn thực hiện chèn thêm cột hay hàng vào trang tính
Ví dụ minh hoạ
Bước 1: Di chuyển đến bảng dữ liệu trang tính Excel sau đó nhập hàm Indirect vào ô trống bất kỳ theo công thức sau:
=SUM(INDIRECT(“$B$5:$B$7”))
Bước 2: Nhấn Enter để hiển thị kết quả sau khi sử dụng hàm Indirect
Tạo các tham chiếu INDIRECT từ các ô chứa giá trị
Ví dụ minh hoạ
Sử dụng hàm Indirect trong Excel để thực hiện tham chiếu gián tiếp các giá trị có trong bảng dưới đây
Bước 1: Thực hiện di chuyển đến trang tính muốn tham chiếu giá trị => Lựa chọn ô trống liền kề để nhập hàm Indirect theo công thức sau:
=INDIRECT(C4)
Bước 2: Nhấn Enter để hiển thị kết quả sau khi sử dụng công thức hàm Indirect. Để áp dụng hàm cho toàn bộ cột giá trị bạn thực hiện thao tác kéo thả chuột để thả toàn bộ công thức vào các ô trống bên dưới. Kết quả sẽ được hiển thị như sau:
Tạo các tham chiếu gián tiếp từ các giá trị ô và văn bản
Ví dụ minh hoạ
Sử dụng hàm Indirect để thực hiện tham chiếu gián tiếp từ các ô giá trị và văn bản dựa theo bảng số liệu sau đây:
Bước 1: Trong bảng dữ liệu Excel sau đây bạn lựa chọn ô trống liền kề và nhập công thức sau:
=INDIRECT(“B”&C4)
Trong đó
- INDIRECT: Là lệnh sử dụng hàm.
- “B”: Chỉ cột B trong bảng tính được áp dụng hàm
- C4: Liên kết giá trị với chữ B để tạo ra ô tham chiếu truy xuất kết quả.
Bước 2: Thực hiện nhấn Enter để hiển thị kết quả. Để áp dụng hàm cho toàn bộ cột giá trị bạn thực hiện thao tác kéo thả chuột để thả toàn bộ công thức vào các ô trống bên dưới. Kết quả sẽ được hiển thị như sau:
Công thức INDIRECT – Tự động tham chiếu đến một trang tính khác
Công thức
=INDIRECT(“” & Sheet’s name & “!” & Cell to pull data from)
Trong đó:
- INDIRECT: Là lệnh sử dụng hàm
- “”, & và !: Ký tự liên kết các giá trị trong hàm được sử dụng
- Sheet’s name: Tên trang tính trong bảng tính của bạn
- Cell to pull data from: Tên cột bạn muốn gọi dữ liệu cho hàm
Ví dụ minh hoạ
Sử dụng hàm Indirect trong Excel để thực hiện tham chiếu dữ liệu trong bảng sau:
Giả sử bạn có trang tính chứa giá trị cần tham chiếu mang tên My Sheet
Bước 1: Nhập hàm sau vào bảng tính
=INDIRECT(“‘”&$A$1&”‘!”&B1)
Trong đó
- INDIRECT: Là lệnh sử dụng hàm
- “”, & và !: Ký tự liên kết các giá trị trong hàm được sử dụng
- A1: Tên trang tính trong bảng tính của bạn
- B1: Tên cột bạn muốn gọi dữ liệu cho hàm
Bước 2: Nhấn Enter để hiển thị kết quả
Tạo một tham chiếu động đến một bảng tính khác
Công thức
=INDIRECT(“” & Sheet’s name & “!” & Cell to pull data from)
Trong đó
- INDIRECT: Là lệnh sử dụng 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 trang tính trong bảng tính được đặt tên
- Cell to pull data from: Là tên cột bạn muốn gọi dữ liệu trong hàm
Ví dụ minh hoạ:
Sử dụng hàm Indirect trong Excel để thực hiện tham chiếu dữ liệu trong bảng sau:
Giả sử bạn có trang tính chứa giá trị cần tham chiếu mang tên My Sheet
Bước 1: Thực hiện nhập công thức sau vào ô trống trỏng bảng dữ liệu để thực hiện tham chiếu, kiểm tra:
=INDIRECT(“‘”&$A$1&”‘!”&B1)
Trong đó
- INDIRECT: Là lệnh sử dụng hàm
- “”, &, !: Các ký tự liên kết các giá trị trong hàm.
- A4: Ô tham chiếu chứa tên bảng tính muốn gọi hàm
- B4: Ô tham chiếu chứa tên trang tinh muốn gọi
- C4: Ô tham chiếu chứa cột dữ liệu muốn gọi.
Bước 2: Nhấn Enter để hiển thị kết quả
Sử dụng hàm INDIRECT với các hàm Excel
Ví dụ minh hoạ
Sử dụng hàm Indirect với hàm ROW để thực hiện tính giá trị trung bình các số liệu trong bảng dữ liệu dưới đây
Bước 1: Sử dụng công thức sau:
=AVERAGE(SMALL(B5:B9,ROW(INDIRECT(“1:3”))))
Trong đó:
- AVERAGE, SMALL, ROW và INDIRECT: Là lệnh sử dụng hàm.
- B5:B9: Vùng giá trị được lựa chọn
- (“1:3”): Là điều kiện thỏa mãn lấy giá trị
Bước 2: Sử dụng tổ hợp phím Ctrl + Shift + Enter để xem kết quả
Sử dụng hàm INDIRECT với các vùng đã đặt tên
Ví dụ minh hoạ
Sử dụng hàm Indirect trong Excel để thực hiện tham chiếu gián tiếp các vùng dữ liệu được đặt tên trong bảng dữ liệu dưới đây
Giả sử bạn có các vùng dữ liệu tham chiếu được đặt tên theo thứ tự như sau:
- Vùng giá trị B4:B8 ở cột Số liệu 1 có tên là GT
- Vùng giá trị 4:C8 ở cột Số liệu 2 có tên là GTR
Bước 1: Thực hiện nhập công thức sau vào ô trống liền kề có trong bảng dữ liệu
=SUM(INDIRECT($G$4))
Trong đó:
- INDIRECT: Là lệnh sử dụng hàm.
- G4: Là ô tham chiếu chứa vùng dữ liệu đã đặt tên trước đó
Bước 2: Nhấn Enter để hiển thị kết quả
Thao tác này được áp dụng hàm để tính kết quả cho bảng dữ liệu trong cột số 1. Nếu bạn muốn làm tương tự với dữ liệu ở cột thức 2 thì có thể thay thế thêm số liệu thứ 2 vào cột G4
Một số lỗi thường gặp khi sử dụng hàm INDIRECT
Lỗi #REF!
Nguyên nhân
Lỗi #REF! xảy ra khi bạn áp dụng công thức hàm Indirect trong Excel mắc phải 1 trong 3 trường hợp sau đây:
- Tham chiếu ref_text không phải là một tham chiếu ô hợp lệ có thể sử dụng
- Vượt quá giới hạn dải tham chiếu được cài đặt mặc định (Giới hạn tham chiếu của hàng là 1,048,576 hoặc giới hạn tham chiếu cột là 16,384).
- Một trang tham chiếu hoặc một bảng tính đã được đóng lại
Cách khắc phục
Bạn thực hiện kiểm tra lại toàn bộ các giá trị có trong bảng dữ liệu. Điều chỉnh lại bằng phương pháp thủ công hoặc sử dụng chức năng hàm IFERROR
Lỗi #NAME?
Nguyên nhân
Trường hợp này là do hàm bị lỗi ký tự hoặc bạn sử dụng sai tên hàm
Cách khắc phục
Kiểm tra lại hàm tính để khắc phục lỗi. Với lỗi này bạn có thể hoàn toàn chỉnh sửa bằng các thao tác thủ công
Tổng kết
Hy vọng những chia sẻ vừa rồi về hàm Indirect trong Excel đã giúp bạn nắm bắt được toàn bộ các cách sử dụng hàm hiệu quả. Ngoài ra còn rất nhiều những bài viết chia sẻ toàn bộ các kiến thức về hàm và các tính năng tại khoá học Excel online trên Box sẽ giúp bạn trở thành chuyên gia Excel chỉ trong nháy mắt.
Khám phá ngay hôm nay!
