Hàm INDIRECT trong Excel

Đôi khi, bạn cần lấy dữ liệu từ nhiều trang tính và phân tích trong một trang tính tóm tắt hoặc dashboard. Bạn có thể thử sao chép và dán những gì bạn cần vào một trang tính và chỉnh sửa thủ công, nhưng đây là một quá trình tốn thời gian. Thay vì làm thủ công, bạn có thể sử dụng hàm INDIRECT để tiết kiệm thời gian và công sức.

Với hàm INDIRECT, bạn có thể tham chiếu và hợp nhất các điểm dữ liệu quan trọng trên nhiều trang tính. Đây là một quy trình đơn giản, chỉ cần một công thức duy nhất có thể dễ dàng áp dụng cho nhiều ô. Hơn nữa, nếu trang tính nguồn được đổi tên, mọi thứ sẽ không bị gián đoạn và được cập nhật tương ứng.

Tóm tắt về hàm INDIRECT

Chìa khóa để xây dựng tham chiếu ô động

Nếu bạn chưa biết hàm INDIRECT là gì, chức năng chính của nó là chuyển đổi một chuỗi văn bản thành tham chiếu ô. Bài viết sẽ nhanh chóng giải thích những gì bạn cần biết để hiểu ví dụ này.

Giả sử bạn có giá trị 5 trong ô A1 và giá trị A1 trong ô B1; công thức sau sẽ trả về giá trị 5.

=INDIRECT(B1)

Điều xảy ra ở đây là hàm INDIRECT đã chuyển đổi giá trị trong ô B1 thành tham chiếu ô (A1). Sau đó, nó trả về giá trị trong ô được tham chiếu, là 5.

Một ví dụ tương tự là tra cứu một địa chỉ, nhập tọa độ vào GPS, rồi lái xe đến đó để lấy hàng. Nghe có vẻ khá dài dòng, nhưng nó sẽ hữu ích khi xây dựng tham chiếu ô động.

Tiếp tục với ví dụ, giả sử chúng ta thay thế giá trị trong ô B1 bằng 1 (lưu ý rằng đây sẽ là giá trị văn bản cho đến khi bạn chuyển đổi nó sang định dạng khác). Chúng ta có thể sử dụng phép nối chuỗi để xây dựng tham chiếu ô.

=INDIRECT("A" & B1)

Ở đây, hàm INDIRECT sẽ lấy chuỗi "A" và kết hợp nó với giá trị trong ô B1 bằng ký hiệu & để tạo thành tham chiếu ô A1. Kết quả cuối cùng vẫn là giá trị 5. Điều tuyệt vời hơn nữa là nếu bạn thay đổi giá trị trong ô B1 thành 2, hàm sẽ chuyển sang ô A2, v.v...

Tóm tắt một bảng tính Excel bằng hàm INDIRECT

Nơi hàm thực sự tỏa sáng

Giờ hãy quay lại ví dụ về dữ liệu bán hàng. Trước tiên, chúng ta cần tạo một cột phụ với tên của tất cả các bảng tính. Giả sử phạm vi là A2:A4 cho North, South và East. Vì ô A2 chứa North, chúng ta có thể tính tổng doanh số bằng hàm SUM và INDIRECT bằng cách nhập công thức sau vào ô B2:

=SUM(INDIRECT("'" & A2 & "'!D:D"))

Các giá trị trong công thức INDIRECT ("'" & A2 & "'!D:D") tạo nên chuỗi 'North'!D:D. Chuỗi này đóng vai trò là tham chiếu thực sự đến cột B trên trang tính North. Lưu ý rằng dấu ngoặc đơn là biện pháp bảo vệ cho các trang tính có khoảng trắng hoặc ký tự đặc biệt. Nếu không, chúng không thực sự cần thiết.

Lưu ý: INDIRECT không thể tham chiếu đến các workbook đã đóng vì nó cần workbook đang mở để có thể tìm thấy những gì đã được tham chiếu. Nếu không, nó sẽ trả về lỗi #REF!. Nó cũng không thể tạo tham chiếu 3D.

Ưu điểm của phương pháp này là bạn có thể sao chép công thức này xuống B3 và B4, và nó sẽ tự động tham chiếu đến các trang tính South và East tương ứng. A2 trong công thức sẽ trở thành A3 và A4 khi bạn thực hiện thao tác này. Vì vậy, thay vì nhập công thức thủ công vào từng ô, cách này giúp bạn tiết kiệm rất nhiều thời gian và có độ chính xác cao.

Hơn nữa, nếu đổi tên một trang tính, bạn chỉ cần cập nhật tên trong cột A. Thao tác này sẽ cập nhật tất cả các tham chiếu ô và công thức vẫn hoạt động hoàn hảo. Bạn không cần phải tìm kiếm từng ô nơi bạn đã nhập công thức.

Ngoài ra, bạn có thể sử dụng hàm IFERROR để xử lý lỗi trong trường hợp hàm INDIRECT trả về lỗi #REF!.

=IFERROR(SUM(INDIRECT("'" & A2 & "'!D:D")), "")

Một số lưu ý cần biết

Đây là một hàm tự tính toán lại

INDIRECT là một hàm tự tính toán lại, nghĩa là nó tính toán lại mỗi khi bất kỳ ô nào trong bảng tính của bạn thay đổi. Các hàm tự tính toán lại có thể khiến bảng tính của bạn chậm đáng kể, đặc biệt là trong những workbook có dung lượng vừa phải đến lớn.

Trong nhiều trường hợp, bạn nên giới hạn việc sử dụng hàm INDIRECT ở mức cần thiết. Tuy nhiên, đối với các bảng tính tóm tắt nhẹ với phạm vi nhập liệu thấp, hiệu suất có thể không đáng kể.

Tối ưu hóa cách bạn hợp nhất các trang tính bằng công thức

Khi tạo các bảng tính tóm tắt và bảng thông tin, hàm INDIRECT là một công cụ đột phá. Nó thậm chí có thể trở nên linh hoạt hơn với một số tính năng khác của Microsoft Excel. Ví dụ, bạn có thể sử dụng danh sách drop-down Excel với hàm này để chuyển đổi các trang tính một cách nhanh chóng. Chào mừng bạn đến với một cách được cho là tốt hơn để hợp nhất các trang tính trong workbook của bạn.