Gần đây, tôi đã tìm kiếm một công thức mảng để chuyển đổi nhiều địa chỉ ô thành giá trị trong Google Sheets. Hàm Indirect không thể làm được điều đó vì bạn có thể cần sử dụng nhiều hàm Indirect cho mục đích đó.
Sau một số lần thử, tôi đã viết một công thức để làm điều tương tự! Một công thức thay thế cho Indirect mà không hỗ trợ các ô trên bảng tính.
Chúng ta không thể sử dụng công thức của tôi là một sự thay thế cho Indirect trong tất cả các trường hợp. Nhưng đối với mục đích của chúng ta, đó là một lựa chọn phù hợp.
Một ví dụ có thể giúp bạn hiểu tình huống (mục đích) tốt hơn.
Liệu tôi có thể thay thế các công thức Indirect trong khoảng G2:G10 bằng một công thức mảng trong Google Sheets?
Lưu ý: Trong H2:H10, tôi đã sử dụng hàm FORMULATEXT để hiển thị công thức trong khoảng G2:G10.
Công thức Indirect sau đây sẽ không hoạt động vì hiện tại, Indirect không hỗ trợ hàm ArrayFormula.
=ArrayFormula(indirect(F2:F10))Giới hạn của Công thức Thay thế Indirect
Chúng ta có thể sử dụng một công thức mảng trong ô G2 để chuyển đổi các địa chỉ ô trong khoảng F2:F10 thành giá trị trong Google Sheets.
Tôi có một công thức mảng để thay thế Indirect như trên trong Google Sheets. Tuy nhiên, nó chỉ có các hạn chế “ngay lập tức” sau đây.
- Không hỗ trợ các địa chỉ ô từ các bảng tính khác.
- Công thức không hỗ trợ các bảng tính khác nhau.
- Yêu cầu nhập các địa chỉ ô (tiêu chí) theo một thứ tự cụ thể.
- Nghĩa là nhập các địa chỉ ô từ hàng đầu tiên của khoảng, sau đó từ hàng thứ hai của khoảng và tiếp tục như vậy. Bạn có thể thấy tôi đã duy trì điều này trong khoảng F2:F10 của mình.
- Các địa chỉ ô (tiêu chí) và công thức phải được nhập bên ngoài khoảng.
- Xem chi tiết bên dưới (sau điểm số 4).
- Hiệu suất chậm/ít trong một tập dữ liệu rất lớn.
Tôi biết tôi phải giải thích điểm thứ ba một cách chi tiết. Hãy xem ví dụ của chúng tôi ở trên và các địa chỉ ô trong cột F.
Bạn có thể thấy rằng tất cả các địa chỉ ô được cung cấp đều từ trái qua cột F. Điều đó có nghĩa là nếu danh sách trong cột F chứa địa chỉ ô H14, thì danh sách phải được chuyển sang cột I.
Cách Chuyển Đổi Nhiều Địa chỉ Ô thành Giá trị trong Google Sheets
Công thức có thể trông phức tạp với bạn, mặc dù không phải là như vậy. Vì vậy, để tôi giúp bạn hiểu công thức bằng cách mã hóa nó từng bước.
Cách thức Thay thế Indirect Logic
Để chuyển đổi các địa chỉ ô thành giá trị mà không sử dụng hàm Indirect trong Google Sheets, chúng ta sẽ tuân theo logic sau đây.
Chúng ta sẽ so khớp các địa chỉ ô (tiêu chí) với ‘dải’ tương ứng. Theo ví dụ, chúng ta sẽ so khớp F2:F10 với các địa chỉ ô trong khoảng A1:E.
Kết quả so khớp sẽ trả về một mảng tương ứng với A1:E với TRUE hoặc FALSE. Chúng ta sẽ trích xuất các giá trị từ ô tương ứng với các giá trị TRUE.
Hãy xem cách tôi sẽ thực hiện logic này từng bước.
Bước 1 – Tạo ra Địa chỉ ô của một Khoảng
Trong ví dụ của tôi, tôi chỉ sử dụng khoảng A1:E20. Điều này giúp chúng ta kiểm tra các công thức riêng lẻ mà không ảnh hưởng đến hiệu suất các trang tính. Tuy nhiên, trong công thức cuối cùng, chúng ta có thể thay đổi A1:E20 thành A1:E.
Để tạo ra các địa chỉ ô của một khoảng, chúng ta có thể sử dụng công thức ADDRESS như dưới đây.
ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])Chúng ta chỉ cần sử dụng 3 đối số đầu tiên.
Đối số ‘row’ phải là cột đầu tiên trong khoảng và đối số ‘column’ phải là hàng đầu tiên trong khoảng. Sau đó, sử dụng ‘absolute_relative_mode’ là 4.
=ArrayFormula(address(row(A1:A20),column(A1:E1),4))Bước 2 – So khớp Địa chỉ ô Bằng cách Sử dụng Regexmatch
Trong các địa chỉ ô trên trả về bởi công thức Address trên, chúng ta có thể so khớp các tiêu chí của chúng. Đây là một bước quan trọng trong việc chuyển đổi nhiều địa chỉ ô thành giá trị trong Google Sheets.
Chúng ta không thể sử dụng hàm Match ở đây vì nó chỉ dùng để so khớp một tiêu chí hoặc nhiều tiêu chí trong một hàng hoặc cột duy nhất. Của chúng ta là một khoảng nhiều cột.
Như một lựa chọn thay thế cho Match, chúng ta có thể sử dụng công thức Regexmatch mạnh mẽ hơn.
REGEXMATCH(text, regular_expression)Đối số ‘text’ (dải để so khớp) là công thức Address ở trên.
Chúng ta cần tạo ‘regular_expression’ để so khớp bằng cách kết hợp các giá trị trong khoảng F2:F10. Trong công thức cuối cùng, chúng ta có thể sử dụng F2:F thay thế.
="^"&textjoin("$|^",true,F2:F10)&"$"Tôi sẽ hiển thị kết quả cho bạn.
^A1$|^B1$|^A2$|^B2$|^C7$|^C11$|^E11$|^A13$|^D13$Tôi đã sử dụng hàm Textjoin để kết hợp các tiêu chí. Nó chèn ba ký tự là dấu phân cách – dấu mũ, dấu đô la và dấu chấm thẳng đứng.
Ngoài ra, sử dụng dấu & để bổ sung dấu mũ ở đầu và dấu đô la ở cuối các tiêu chí được kết hợp.
Liên quan: Sử dụng Biểu thức chính quy phù hợp chính xác trong Google Sheets (sử dụng dấu mũ và dấu đô la).
Chúng ta hiện đã có ‘text’ và ‘regular_expression’ để sử dụng trong công thức Regexmatch và đây là công thức.
=ArrayFormula(regexmatch(address(row(A1:A20),column(A1:E1),4),"^"&textjoin("$|^",true,F2:F10)&"$"))Bước 3 – Công thức để Chuyển đổi Nhiều Địa chỉ Ô thành Giá trị trong Google Sheets
Đây là bước cuối cùng.
Bây giờ, chúng ta có thể dễ dàng chuyển đổi nhiều địa chỉ ô thành giá trị bằng một câu lệnh IF logic.
=ArrayFormula(if(step_2_formula_output=true,A1:E20,))Nó sẽ trông giống như sau;
=ArrayFormula(if(regexmatch(address(row(A1:A20),column(A1:E1),4),"^"&textjoin("$|^",true,F2:F10)&"$")=TRUE,A1:E20,))Nhưng các giá trị sẽ được phân tán. Chúng ta có thể định dạng nó bằng cách sửa đổi công thức trên bằng cách sử dụng Textjoin, Split và Transpose.
Sử dụng =ArrayFormula(transpose(split(TEXTJOIN(“|”,1, trước IF và ,”|”))) ở cuối công thức.
Công thức cuối cùng trong ô G2:
=ArrayFormula(transpose(split(TEXTJOIN("|",1,if(regexmatch(address(row(A1:A20),column(A1:E1),4),"^"&textjoin("$|^",true,F2:F10)&"$")=true,A1:E20,)),"|")))Lưu ý: Như đã đề cập ở trên, nếu bạn muốn, bạn có thể thay thế A1:A20, F2:F10 và A1:E20 bằng A1:A, F2:F và A1:E tương ứng.
Ví dụ Sử dụng Thực Tế
Vì Indirect không hữu ích cho việc sử dụng mảng, chúng ta có thể tuân theo phương pháp trên trong một số tình huống thực tế. Dưới đây là một ví dụ như vậy.
- Làm thế nào để Tìm giá trị cuối cùng trong Mỗi hàng trong Google Sheets.
Đó là tất cả. Thưởng thức!
