Cách dùng hàm INDIRECT trong Excel để tham chiếu giá trị
Hàm INDIRECT trong Excel là gì?
Hàm INDIRECT là hàm được sử dụng để tham chiếu “gián tiếp” trong Excel. Đối tượng tham chiếu có thể là một ô, dãy ô (bảng), hoặc thậm chí là một trang tính. Bên cạnh đó, bạn có thể dùng hàm INDIRECT để tham chiếu đến đối tượng trong cùng một trang tính hoặc trong một trang tính khác (sheet khác).
Sau đây là cú pháp chuẩn xác của hàm INDIRECT trong Excel:
=INDIRECT(ref_text, [a1])
Ý nghĩa của các thông số trong công thức:- ref_text: là đối tượng tham chiếu bắt buộc phải có trong hàm. Đối tượng này có thể là một ô hoặc một mảng đã được đặt tên.
- a1: là giá trị tự chọn, chỉ rõ kiểu tham chiếu của ref_text. Thông số này thường được bỏ qua để đơn giản cách dùng hàm.
Cách dùng hàm INDIRECT trong Excel và ví dụ minh họa
Hàm INDIRECT có phần nâng cao hơn so với các hàm trong Excel cơ bản như hàm SUM, hàm AVERAGE. Để hiểu rõ hơn về cách sử dụng công thức INDIRECT cho đúng. Bạn có thể tham khảo một số bài toán ví dụ sau đây.
Dùng hàm INDIRECT tạo tham chiếu trực tiếp
Cú pháp hàm INDIRECT tạo tham chiếu trực tiếp hết sức đơn giản. Trong ví dụ này, ta có một bảng dữ liệu (bảng 1) với hai cột chứa giá trị A và B. Yêu cầu của bài toán là sử dụng hàm để tham chiếu các ô trong cột B và trả về giá trị trong cột C.
Tại ô C2, nhập hàm như sau: =INDIRECT(B2). Sau đó, bạn có thể kéo thả công thức tại C2 cho các ô còn lại trong cột, và đây là kết quả:
Giải thích kết quả: hàm =INDIRECT(B2) sẽ tham chiếu đến ô B2 (có giá trị là “A2”), sau đó tiếp tục tham chiếu đến “A2” - vị trí ô tính A2 trên bảng tính. Tại ô A2, giá trị của ô tính là 10 nên trả về giá trị 10 tại ô C2. Cách hoạt động của hàm ở các ô khác trong cột C cũng tương tự như vậy.
Dùng hàm INDIRECT tạo tham chiếu gián tiếp
Bên cạnh cách tham chiếu trực tiếp như ví dụ trên, bạn có thể dùng hàm INDIRECT để tạo tham chiếu gián tiếp.
Trong ví dụ này, ta cũng có bảng dữ liệu (bảng 1) với hai cột chứa giá trị A và B. Tại ô C2, nhập hàm như sau: =INDIRECT('A'&B2), trong đó:
- A là ký tự chỉ cột A trong sheet Excel.
- B2 là ô tham chiếu đến, có giá trị là 2.
Như vậy, hàm này sẽ tham chiếu đến ô tính “A2” và trả về giá trị của ô tính này tại C2 (kết quả: 10). Bạn có thể kéo thả công thức cho các ô còn lại trong cột C, và đây là kết quả cuối cùng:
Cách dùng hàm INDIRECT với các vùng được đặt tên
Bên cạnh việc tham chiếu đến ô chỉ định, bạn có thể dùng hàm INDIRECT để tham chiếu đến vùng đã được đặt tên.
Bài toán: Cho bảng dữ liệu bán hàng (bảng 1) của 3 sản phẩm (A, B và C). Yêu cầu của bài toán là tính tổng và trung bình doanh số bán hàng của từng sản phẩm.
Đối với sản phẩm A trong bảng 1, ta áp dụng hàm INDIRECT như sau:
Để tính tổng doanh số bán hàng, tại ô G2, nhập hàm như sau:
=SUM(INDIRECT(G1))
Để tính trung bình doanh số bán hàng, tại ô G3, nhập hàm như sau:
=AVERAGE(INDIRECT(G1))
Trong đó: G1 có giá trị là “SPA”, ám chỉ vùng dữ liệu đã được đặt tên tương ứng. Cụ thể, vùng được đặt tên “Sản phẩm A” là B2:B6 trong bảng dữ liệu (Bảng 1).
Để tính tổng và trung bình doanh số bán hàng cho sản phẩm B và C. Bạn cũng áp dụng hàm INDIRECT làm theo cách tương tự, và đây là kết quả:
Dùng hàm INDIRECT để tham chiếu tới trang tính khác
Trong trường hợp bạn muốn tham chiếu tới một ô trong sheet khác, thì sử dụng hàm INDIRECT như sau:
=INDIRECT(' & Sheet’s name & '!' & Cell to pull data from)
Ý nghĩa của các thông số trong cú pháp hàm trên là:
- Sheet’s name: là tên trang tính mà bạn muốn tham chiếu đến.
- Cell to pull data from: là ô nằm trong Sheet’s name mà bạn muốn tham chiếu.
Ví dụ: Cho bảng dữ liệu (Bảng 1) nằm trong Sheet 2. Yêu cầu của bài toán là trả giá trị trong cột A (Bảng 1 - Sheet 2) về cột C (Bảng 1 - Sheet 1).
Tại ô C2 (Sheet 1), nhập hàm INDIRECT như sau: =INDIRECT('&$E$2&'!'&B2), và đây là kết quả:
Kết hợp hàm INDIRECT với các hàm khác trong Excel
Bạn có thể kết hợp hàm INDIRECT trong Excel với một số hàm khác, chẳng hạn:
Sử dụng hàm ADDRESS và INDIRECT để tham chiếu giá trị của ô tính có địa chỉ bất kỳ. Ví dụ, tại ô C1, nhập hàm như sau: =INDIRECT (ADDRESS(4,2)), và đây là kết quả:
Ý nghĩa: hàm ADDRESS ám chỉ địa chỉ ô A4B2 và hàm INDIRECT tiếp tục tham chiếu đến ô này và trả về giá trị là …
Một số lỗi thường gặp khi dùng hàm INDIRECT trong Excel
Lỗi #REF: Có nhiều nguyên nhân dẫn đến lỗi này, có thể là đối số ref_text trong hàm không hợp lệ, dải tham chiếu vượt quá giới hạn hoặc trang tính tham chiếu đã bị đóng. Bạn cần kiểm tra lại các đối số trong hàm, giảm bớt phạm vi tham chiếu hoặc mở trang tính tham chiếu lên. Lỗi #NAME: Lỗi này xảy ra khi bạn nhập sai tên hàm INDIRECT.
Bình luận (0)