Bạn đã bao giờ gặp phải vấn đề khi sử dụng hàm INDIRECT kết hợp với ArrayFormula trong Google Sheets? Hàm INDIRECT mạnh mẽ, tuy nhiên, nó không hoạt động với ArrayFormula để trả về kết quả trên nhiều ô. Thay vào đó, bạn có thể sử dụng Lambda Helper Functions (LHFs) như MAP và REDUCE để đạt được kết quả tương tự.
LHFs và Ứng dụng của chúng trong việc sử dụng INDIRECT
LHFs giúp tối ưu công việc lặp lại và tăng cường tính năng của các hàm tính toán trên bảng tính. Và hàm INDIRECT cũng không phải là một ngoại lệ.
Cách sử dụng LHFs với INDIRECT phụ thuộc vào trường hợp sử dụng cụ thể của bạn. Dưới đây là một số LHFs bạn có thể sử dụng:
- MAP: Để tham chiếu đến một ô trên nhiều bảng tính và lấy dữ liệu từng cái một hoặc tổng hợp dữ liệu từng cái một từng bảng tính.
- REDUCE: Để tham chiếu đến một phạm vi ô trên nhiều bảng tính và xếp chúng.
- Các LHFs khác: Có các chức năng như SCAN, BYCOL và BYROW rất hữu ích khi sử dụng dữ liệu được trích xuất bằng REDUCE và INDIRECT.
Trong bài viết này, chúng ta sẽ khám phá các ví dụ khác nhau để nâng cao hiểu biết về các trường hợp sử dụng này.
Khi nào thì nên sử dụng các phương pháp thay thế INDIRECT ArrayFormula?
Việc phân biệt này rất quan trọng và có đặc biệt hữu ích trong hai tình huống sau:
- Khi bạn cần tham chiếu động đến nhiều tên bảng tính được lưu trong một hàng hoặc một cột các ô.
- Khi bạn cần tham chiếu đến đa dạng các phạm vi có tên một cách gián tiếp. Trong trường hợp này, tên phạm vi được liệt kê trong một hàng hoặc một cột.
Bây giờ, hãy cùng đi vào các ví dụ thực hành để minh họa cách sử dụng các phương pháp thay thế INDIRECT ArrayFormula trong Google Sheets.
MAP: Một phương pháp thay thế ArrayFormula trong INDIRECT cho Google Sheets
Trong một tệp Google Sheets có bốn bảng tính (Dashboard, ABC 12345, ABC 12346 và ABC 12347), có thể gặp khó khăn khi trích xuất và tổng hợp dữ liệu từ nhiều bảng tính một cách gián tiếp.
Tên các bảng tính được liệt kê trong các ô A3:A5 trong bảng tính Dashboard. Trong ô B3, chúng ta sẽ sử dụng các phương pháp thay thế của INDIRECT ArrayFormula để trích xuất giá trị hoặc trích xuất và tổng hợp giá trị từ các bảng tính khác.
Dưới đây là các giải pháp sử dụng hàm MAP:
Ví dụ 1: Trích xuất Tổng
=MAP(A3:A5, LAMBDA(val, INDIRECT(val&"!B7"))) // Trích xuất các giá trị từ ô B7 trên mỗi bảng tính
Trong công thức này và các công thức tiếp theo, A3:A5 là mảng chứa tên bảng tính và B7 là ô mục tiêu trên mỗi bảng tính để trích xuất.
Ví dụ 2: Trích xuất và Tổng hợp Tổng
=SUM(MAP(A3:A5, LAMBDA(val, INDIRECT(val&"!B7")))) // Trả về 587.50 (198 + 196 + 193.5)
Ví dụ 3: Trích xuất và Tổng hợp Phạm vi
=MAP(A3:A5, LAMBDA(val, SUM(INDIRECT(val&"!B2:B6")))) // Trả về tổng giá trị của phạm vi B2:B6 từng bảng tính một
Trong công thức này, A3:A5 đại diện cho mảng chứa tên bảng tính và B2:B6 là phạm vi mục tiêu trên mỗi bảng tính để trích xuất và tổng hợp. Nói cách khác, nó sẽ trả về kết quả được thể hiện trong ví dụ số 1 ở trên.
Những ví dụ sử dụng hàm MAP này giải quyết những vấn đề mà một công thức ArrayFormula kết hợp với INDIRECT không thể giải quyết. Hàm này lặp lại các tên bảng tính được chỉ định, cung cấp một giải pháp linh hoạt để tổng hợp dữ liệu từ nhiều bảng tính trong Google Sheets.
REDUCE + INDIRECT: Tăng cường trải nghiệm trên Sheets của bạn
Khi tìm kiếm một phương pháp thay thế cho INDIRECT với ArrayFormula trong Google Sheets, việc nhắm tới một dạng mảng của INDIRECT mà không cần ArrayFormula trở nên quan trọng do không được hỗ trợ.
Trong ví dụ trước, chúng ta sử dụng hàm INDIRECT với MAP như một thay thế cho INDIRECT với ArrayFormula.
Tuy nhiên, trong khi MAP xuất sắc trong việc lấy giá trị từng cái một từ mỗi bảng tính, chúng ta giới thiệu hàm REDUCE để giải quyết các tình huống yêu cầu trích xuất và xếp chồng các phạm vi.
Làm thế nào để trích xuất một phạm vi từ mỗi bảng tính và xếp chúng theo chiều dọc hoặc theo chiều ngang? Dưới đây là một ví dụ:
Để xếp chồng theo chiều ngang:
=REDUCE(TOROW(, 1), A3:A5, LAMBDA(a, val, HSTACK(a, INDIRECT(val&"!B2:B6")))) // Trích xuất và xếp chồng phạm vi B2:B6 từng bảng tính theo chiều ngang
Để xếp chồng theo chiều dọc:
=REDUCE(TOCOL(, 1), A3:A5, LAMBDA(a, val, VSTACK(a, INDIRECT(val&"!B2:B6")))) // Trích xuất và xếp chồng phạm vi B2:B6 từng bảng tính theo chiều dọc
Cả hai công thức đều lặp lại các tên bảng tính trong mảng A3:A5, trích xuất phạm vi B2:B6. val là tên bảng tính hiện tại trong mảng.
Hàm REDUCE lưu trữ kết quả trung gian trong a, bộ tích trữ. TOROW/TOCOL đại diện cho giá trị ban đầu trong bộ tích trữ (về cơ bản là null).
Khám phá phương pháp thay thế INDIRECT với ArrayFormula này hơn nữa trong các hướng dẫn sau:
- Ghép dữ liệu một cách động trong nhiều tab theo chiều dọc trong Google Sheets.
- Kết hợp động nhiều bảng tính theo chiều ngang trong Google Sheets.
INDIRECT với các Phương pháp thay thế ArrayFormula và Named Ranges
Trong các ví dụ trước, chúng ta sử dụng một mảng tên bảng tính (A3:A5) trong Lambda Helper Functions (LHFs) để lặp lại từng tên bảng tính. Trong hàm INDIRECT, chúng ta kết hợp các tham chiếu ô/phạm vi với tên bảng tính như đã được thể hiện bởi:
INDIRECT(val&"!B2:B6")cho tham chiếu phạm viINDIRECT(val&"!B7")cho tham chiếu ô
Tuy nhiên, khi làm việc với các phạm vi có tên, bạn nên chỉ định các tên phạm vi thay vì tên bảng tính trong A3:A5. Trong trường hợp này, trong hàm INDIRECT, bạn chỉ cần tham chiếu trực tiếp đến các phạm vi có tên mà không cần kết hợp tham chiếu ô/phạm vi.
Ví dụ, nếu các phạm vi mang tên vehicle_1, vehicle_2 và vehicle_3, tương ứng với các phạm vi ‘ABC 12345’!B2:B6, ‘ABC 12346’!B2:B6 và ‘ABC 12347’!B2:B6.
Trích xuất và Tổng hợp Phạm vi:
=MAP(A3:A5, LAMBDA(val, SUM(INDIRECT(val)))) // Trả về tổng giá trị từng phạm vi có tên một cách riêng biệt
Xếp chồng theo chiều ngang:
=REDUCE(TOROW(, 1), A3:A5, LAMBDA(a, val, HSTACK(a, INDIRECT(val)))) // Xếp chồng kết quả gián tiếp từng phạm vi có tên theo chiều ngang
Các công thức này thể hiện tính linh hoạt của các phương pháp thay thế INDIRECT với ArrayFormula, cho phép trích xuất và tổng hợp một cách liền mạch các phạm vi có tên trong Google Sheets.
Tài nguyên
Chúng ta đã khám phá ví dụ về các phương pháp thay thế INDIRECT với ArrayFormula để trích xuất dữ liệu từ nhiều bảng tính và phạm vi có tên, minh họa cho ứng dụng linh hoạt của chúng. Dưới đây là một số hướng dẫn thực hành mà bạn có thể tham khảo:
- SUMIF Across Multiple Sheets in Google Sheets
- Role of Indirect Function in Conditional Formatting in Google Sheets
- Reference a List of Tab Names in Query in Google Sheets
- Highlight Indirect Range in Google Sheets
Hãy áp dụng những phương pháp này vào công việc của bạn trên Google Sheets để tận hưởng hiệu quả của chúng!
