Buzz
Hàm INDIRECT là một công cụ mạnh mẽ giúp tham chiếu dữ liệu gián tiếp trong Excel mà không làm thay đổi công thức gốc. Dù vậy, nhiều người vẫn chưa nắm rõ cách áp dụng hàm này. Bài viết dưới đây sẽ giải đáp chi tiết mọi thắc mắc về hàm INDIRECT trong Excel!
Hàm INDIRECT là gì?
Hàm INDIRECT được dùng để tham chiếu gián tiếp đến các ô, dải ô, trang tính hoặc bảng tính khác nhau trong Excel. Các đối tượng này có thể nằm trong cùng một trang tính hoặc ở nhiều trang tính khác nhau.
Lưu ý: Nếu vùng tham chiếu nằm trong một trang tính hoặc bảng tính khác, bạn cần đảm bảo bảng tính đó được mở để tránh gặp lỗi khi sử dụng hàm.
Nhờ hàm INDIRECT, người dùng có thể kết nối dữ liệu một cách hiệu quả mà không cần thay đổi công thức gốc trong ô.
Cú pháp của hàm INDIRECT là gì?
Hàm INDIRECT được dùng để tham chiếu trong Excel với cú pháp như sau:
Các thành phần trong công thức bao gồm:
- ref_text: Đây là tham số bắt buộc trong hàm, có thể là một ô tham chiếu, một tham chiếu đến ô, một chuỗi văn bản hoặc một mảng đã được đặt tên.
- a1: Là giá trị logic quyết định kiểu tham chiếu của ref_text. Thường được bỏ qua để đơn giản hóa việc sử dụng hàm. Tuy nhiên:
Nếu a1 là TRUE (hoặc để trống), ref_text sẽ được hiểu theo kiểu A1 (cột-hàng).
Ví dụ: A1 tham chiếu đến ô nằm ở cột A và hàng 1.
Nếu a1 là FALSE, ref_text sẽ được hiểu theo kiểu R1C1 (hàng-cột).
Ví dụ: R1C1 tham chiếu đến ô ở hàng 1 và cột 1.
Cách sử dụng hàm INDIRECT trong Excel chi tiết
Hàm INDIRECT trong Excel rất hữu ích trong việc tham chiếu và liên kết các đối tượng trong bảng tính. Tuy nhiên, cách sử dụng hàm này khá đa dạng. Dưới đây là một số ví dụ minh họa cụ thể để bạn dễ hình dung.
Lưu ý: Các ví dụ được thực hiện trên Excel 2016, nhưng bạn có thể áp dụng tương tự cho các phiên bản Excel khác.
Sử dụng hàm INDIRECT để tham chiếu trực tiếp từ ô chứa giá trị
Hàm INDIRECT giúp tạo tham chiếu trực tiếp từ các ô chứa giá trị trong Excel. Dưới đây là ví dụ minh họa cụ thể:
Ví dụ minh họa: Giả sử bạn có một bảng dữ liệu như hình. Yêu cầu là trích xuất giá trị trực tiếp từ ô A2.
Bạn có thể áp dụng các công thức sau:
Công thức 1: = INDIRECT (C1)
Trong trường hợp này, a1 được bỏ trống, do đó ref_text sẽ được hiểu theo kiểu cột-hàng. Công thức này tham chiếu đến ô C1, và giá trị của ô này chính là chuỗi A2.
Vì ô A2 có giá trị là 222, nên kết quả trả về từ hàm là 222.
Công thức 2: = INDIRECT (C3,False)
Trong trường hợp này, a1 được đặt là False, nên ref_text sẽ được hiểu theo kiểu hàng-cột. Công thức tham chiếu đến ô C3, và giá trị của ô này là chuỗi R2C1.
R2C1 tương ứng với hàng 2 - cột 1, chính là ô A2, nên kết quả trả về từ hàm là 222.
Công thức 3: = INDIRECT (C5,False)
Trong trường hợp này, a1 được đặt là False, nên ref_text sẽ được hiểu theo kiểu hàng-cột. Công thức tham chiếu đến ô C5, và giá trị của ô này là chuỗi R3C.
R3C tương ứng với hàng 3 - cột 3, chính là ô R2C1, nên kết quả trả về từ hàm là 222.
Bạn có thể sử dụng 3 công thức trên với hàm INDIRECT để trích xuất dữ liệu trực tiếp từ các ô chứa giá trị.
Sử dụng hàm INDIRECT để tạo tham chiếu gián tiếp từ giá trị ô và văn bản
Bên cạnh cách sử dụng trên, bạn cũng có thể dùng hàm INDIRECT để tạo tham chiếu gián tiếp từ văn bản và giá trị ô.
Ví dụ minh họa: Để tham chiếu gián tiếp dữ liệu từ ô B4 bằng hàm INDIRECT, bạn có thể làm theo các bước sau:
Bước 1: Tại ô đầu tiên trong cột Kết quả, nhập công thức: =INDIRECT('B'&C4)
Trong đó:
- 'B': Đây là phần văn bản trong công thức.
- C4: Ô tham chiếu có giá trị là 4.
Vì vậy, “B”&C4 sẽ tham chiếu đến ô B4.
Bước 2: Nhấn phím Enter để hiển thị giá trị của ô B4 là 10.
Bước 3: Lặp lại các bước tương tự với các ô còn lại trong cột. Kết quả cuối cùng sẽ như hình dưới đây:
Lưu ý: Ký tự cần được đặt trong dấu ngoặc kép để đảm bảo tính hợp lệ.
Cách sử dụng hàm INDIRECT với các vùng dữ liệu đã đặt tên
Bạn có thể tạo tham chiếu đến vùng dữ liệu bằng cách nhập tên của vùng vào công thức INDIRECT. Ví dụ, với vùng dữ liệu B4:B8 được đặt tên là SL và gán tên này vào ô D4.
Lưu ý: Tên gán vào ô phải khớp chính xác với tên vùng dữ liệu đã đặt trước đó.
Để tham chiếu đến vùng dữ liệu SL bằng hàm INDIRECT, bạn có thể sử dụng công thức sau:
INDIRECT(D4)
Bạn có thể kết hợp công thức này với các hàm Excel khác để thực hiện các phép tính trên vùng dữ liệu. Ví dụ, để tính tổng, trung bình, giá trị lớn nhất và nhỏ nhất, bạn có thể áp dụng các công thức sau:
- Tính tổng: = SUM (INDIRECT(D4))
- Tính trung bình: = AVERAGE (INDIRECT(D4))
- Tìm giá trị lớn nhất: = MAX (INDIRECT(D4))
- Tìm giá trị nhỏ nhất: = MIN (INDIRECT(D4))
Kết quả sau khi nhấn Enter sẽ hiển thị như sau:
Bạn có thể áp dụng tương tự với các phép tính khác trong Excel bằng cách sử dụng hàm.
Những ứng dụng của hàm INDIRECT trong Excel
Với khả năng xử lý dữ liệu linh hoạt, hàm INDIRECT là một công cụ được sử dụng rộng rãi trong Excel. Để hiểu rõ hơn về cách ứng dụng và sử dụng hàm INDIRECT, hãy cùng khám phá phần nội dung tiếp theo.
Sử dụng hàm INDIRECT để tham chiếu đến trang tính khác
Để tham chiếu đến một trang tính khác bằng hàm INDIRECT, bạn có thể áp dụng công thức sau:
Trong đó:
- Sheet's name: Tên của trang tính trong bảng tính.
- Cell to pull data from: Địa chỉ ô trong trang tính mà bạn muốn tham chiếu.
Ví dụ minh họa: Giả sử có hai bảng dữ liệu nằm trên hai trang tính khác nhau.
Nếu bạn muốn dùng hàm INDIRECT để di chuyển dữ liệu từ Sheet2 sang Sheet1, bạn có thể làm theo các bước sau:
Bước 1: Tại một ô bất kỳ trong Sheet1, nhập công thức =INDIRECT(''&$A$2&'!'&B2).
Trong đó:
- $A$2 (trong Sheet1) là Sheet’s name.
- B2 (trong Sheet1) là Cell to pull data from. Giá trị của ô B2 (Sheet1) là A2, và giá trị của ô A2 (Sheet2) là 10.
Bước 2: Khi nhấn Enter, kết quả trả về sẽ là 10.
Bước 3: Bạn có thể áp dụng công thức tương tự cho các ô còn lại trong cột.
Bạn đã thành công chuyển dữ liệu từ Sheet2 sang Sheet1 bằng cách sử dụng hàm INDIRECT.
Tạo tham chiếu động đến một bảng tính khác
Để tham chiếu đến một bảng tính khác bằng hàm INDIRECT, bạn có thể áp dụng công thức sau:
Trong đó:
- Book name: Tên của bảng tính bạn muốn tham chiếu.
- Sheet name: Tên của trang tính trong bảng tính.
- Cell address: Địa chỉ ô bạn muốn lấy dữ liệu.
Sử dụng hàm INDIRECT để cố định tham chiếu ô
Trong Excel, tham chiếu ô thường bị thay đổi khi bạn thêm hoặc xóa hàng, cột. Điều này có thể gây lỗi dữ liệu và gây khó khăn cho người dùng. Để khắc phục, bạn có thể sử dụng hàm INDIRECT để cố định tham chiếu ô. Dưới đây là ví dụ minh họa chi tiết.
Ví dụ minh họa: Giả sử có một giá trị nằm trong ô A1. Ta có hai cách tham chiếu:
- Tham chiếu thông thường: =A1
- Tham chiếu bằng INDIRECT: =INDIRECT (“A1”)
Vì giá trị của ô A1 là 20, nên cả hai ô tham chiếu đều trả về kết quả là 20.
Nếu bạn thêm hoặc xóa một dòng mới, vị trí ô tham chiếu sẽ thay đổi, dẫn đến hai trường hợp sau:
- Tham chiếu thông thường: Vẫn trả về giá trị 20, vì công thức tự động thay đổi thành A2.
- Tham chiếu bằng INDIRECT: Sẽ trả về giá trị 0, vì hàm INDIRECT cố định tham chiếu đến ô A1. Ô A1 trống nên Excel mặc định giá trị là 0.
Với tính năng này, bạn có thể áp dụng hàm INDIRECT trong nhiều tình huống cần cố định tham chiếu khi thực hiện tính toán.
Kết hợp hàm INDIRECT với các hàm khác trong Excel
Ngoài những ứng dụng đã nêu, hàm INDIRECT còn thường được kết hợp với các hàm khác như ROW, ADDRESS, VLOOKUP, SUMIF, … Dưới đây là một số ví dụ cụ thể.
Kết hợp với hàm ROW
Hàm ROW trong Excel thường trả về một mảng giá trị. Tuy nhiên, khi chèn thêm hàng mới, công thức ROW sẽ bị thay đổi. Khi kết hợp với hàm INDIRECT, công thức sẽ luôn được cố định, dù có thêm hoặc xóa bao nhiêu dòng đi chăng nữa.
Ví dụ minh họa: Giả sử bạn cần tính trung bình của 3 số nhỏ nhất trong dải A1:A10.
- Công thức thông thường sẽ là: =AVERAGE(SMALL($A$1:$A$10,ROW(1:3)))
Hàm sẽ xác định ba số nhỏ nhất trong dải A1:A10 lần lượt là: 1; 2; 3.
- Tuy nhiên, khi chèn thêm một hàng mới từ hàng 1 đến 3, công thức sẽ tự động thay đổi thành: =AVERAGE(SMALL($A$1:$A$10,ROW(1:4)))
Lúc này, công thức sẽ tính trung bình của 4 số nhỏ nhất trong dải A1:A10 thay vì 3 như ban đầu.
- Để giải quyết vấn đề này, hàm INDIRECT sẽ được kết hợp với hàm ROW, với công thức: =AVERAGE(SMALL($A$1:$A$10,ROW(INDIRECT('1:3'))))
Khi đó, dù bạn có thêm bao nhiêu hàng, yêu cầu tính trung bình 3 số nhỏ nhất ban đầu vẫn được đảm bảo.
Lưu ý: Nếu hàm báo lỗi, hãy kiểm tra lại cú pháp.
Kết hợp với hàm ADDRESS
Hàm ADDRESS trong Excel trả về địa chỉ ô dựa trên số hàng và cột. Khi kết hợp với hàm INDIRECT, bạn có thể tham chiếu giá trị của ô bất kỳ.
Ví dụ minh họa:
Để xác định vị trí của một ô bất kỳ, sử dụng công thức = ADDRESS (1,3)
Đây là vị trí giao điểm của hàng 1 và cột thứ 3.
Kết quả trả về sẽ là chuỗi $C$1.
Khi kết hợp với hàm INDIRECT, công thức sẽ là: = INDIRECT (ADDRESS (1,3))
Bạn sẽ tham chiếu được địa chỉ chuỗi $C$1.
Sử dụng hàm INDIRECT với danh sách tùy chọn trong Excel
Trong Excel, bạn có thể dùng hàm INDIRECT để tạo danh sách phụ thuộc. Tùy thuộc vào giá trị được chọn trong danh sách đầu tiên, dữ liệu sẽ hiển thị nội dung khác nhau.
Ví dụ minh họa: Cho bảng dữ liệu sau, để tạo danh sách thả xuống phụ thuộc bằng hàm INDIRECT, thực hiện như sau:
Bước 1: Đặt tên cho vùng dữ liệu muốn tạo danh sách. Ví dụ, có 4 vùng được đặt tên như sau:
Bước 2: Tại ô Chọn nhóm, nhấn vào Data và mở bảng Data Validation. Sau đó, chọn list và khoanh vùng dữ liệu như hình dưới.
Bạn sẽ thấy ô Chọn nhóm hiển thị kết quả như sau.
Bước 3: Đối với phần Chọn sản phẩm, bạn cũng chọn và mở bảng Data Validation. Tương tự, chọn list nhưng ở phần Source, bạn nhập hàm tham chiếu đến ô Chọn nhóm.
Với công thức: =INDIRECT($A$1)
Lúc này, bạn đã có thể sử dụng danh sách tùy chọn phụ thuộc như hình:
Bạn có thể áp dụng tương tự với các mục khác trong bảng.
Lỗi thường gặp khi sử dụng hàm INDIRECT
Lỗi #REF!
Khi sử dụng hàm INDIRECT, nếu Excel báo lỗi #REF!, có thể do ba nguyên nhân sau:
- ref_text trong công thức không phải là một tham chiếu ô hợp lệ.
- ref_text vượt quá giới hạn vùng tham chiếu (cụ thể là 1,048,576 hàng và 16,384 cột).
Bạn có thể kiểm tra các đối số của hàm INDIRECT để khắc phục lỗi.
- Khi tham chiếu đến trang tính hoặc bảng tính khác nhưng không mở đồng thời.
Hãy đảm bảo mở đồng thời các tệp chứa dữ liệu cần tham chiếu.
Lỗi #NAME?
Khi sử dụng hàm INDIRECT, nếu Excel báo lỗi #NAME?, nguyên nhân có thể do nhập sai tên hàm hoặc lỗi ký tự. Bạn có thể khắc phục bằng cách kiểm tra và điều chỉnh lại công thức.
Kết luận:
Trên đây là hướng dẫn chi tiết về cách sử dụng hàm INDIRECT trong Excel. Bạn có thể tham chiếu dữ liệu một cách dễ dàng và hiệu quả nếu thực hiện đúng các bước trên. Nếu thấy bài viết hữu ích, đừng quên chia sẻ với bạn bè để cùng nâng cao kỹ năng xử lý dữ liệu trong Excel nhé!
