Trong Google Sheets, hàm ARRAYFORMULA là một công cụ đặc biệt dùng để tạo ra những công thức mảng. Khác với Excel, Google Sheets không có cách tích hợp để tự động mở rộng kết quả của một công thức mảng xuống dòng hoặc ngang. Bạn có thể sử dụng hàm ARRAYFORMULA để đạt được hiệu ứng tương tự, nhưng bạn cần bao bọc công thức cần sử dụng trong hàm này.
Hàm ARRAYFORMULA: Cú pháp và đối số
Cú pháp của hàm ARRAYFORMULA trong Google Sheets:
ARRAYFORMULA(array_formula)Hàm này chỉ có một đối số duy nhất là array_formula.
Lưu ý:
- Đối số
array_formulacó thể là một phạm vi các ô. Ví dụ:=ARRAYFORMULA(A2:A10)sẽ trả về các giá trị từ ô A2 đến A10. - Đối số
array_formulacó thể là biểu thức toán học. Ví dụ:=ARRAYFORMULA(B2:B10*25)sẽ trả về giá trị từ ô B2 đến B10 nhân với 25. - Đối số
array_formulacó thể là một hàm trả về kết quả lớn hơn một ô duy nhất. Ví dụ:=ARRAYFORMULA(FILTER(Sheet1!B2:B10,Sheet1!B2:B10>5)*25)sẽ trả về các giá trị từ ô B2 đến B10 lớn hơn 5 nhân với 25.
Hình 1: Đối số của hàm ARRAYFORMULA được giải thích
Ví dụ về công thức mảng trong Google Sheets
Trong ví dụ sau, để tính tổng tiền của mỗi sản phẩm, tôi đã sử dụng công thức mảng sau đây trong ô E3.
=ARRAYFORMULA(C3:C7*D3:D7)
Hình 2: Ví dụ về công thức mảng cơ bản trong Google Sheets
Bạn có thể nhập công thức ARRAYFORMULA vào Google Sheets theo hai cách:
-
Thủ công: Gõ
=ARRAYFORMULA(, sau đó gõ công thức muốn mở rộng bên trong và nhấn phím Enter. -
Tự động: Bạn chỉ cần gõ công thức (bắt đầu với dấu
=) và nhấn Ctrl+Shift+Enter (Windows) hoặc ⌘ + Shift + Enter (Mac).
Trong ví dụ trên, nếu bạn không sử dụng hàm ARRAYFORMULA và áp dụng một công thức không phải là công thức mảng, bạn cần sử dụng =C3*D3 trong ô E3 và điền công thức xuống các dòng bên dưới.
Để điền xuống, bạn có thể sử dụng một trong các phương pháp sau:
-
Sao chép công thức E3 và dán vào ô E4:E7. Để làm điều này, nhấn Ctrl+C (Windows) hoặc ⌘+C (Mac) trong ô E3, sau đó chọn các ô E4:E7 và nhấn Ctrl+V (Windows) hoặc ⌘+V (Mac).
-
Chọn các ô E3:E7 và nhấn Ctrl+Enter (Windows) hoặc ⌘+Enter (Mac) để tự động điền công thức.
-
Điều chỉnh cỡ ô E3, sau đó kéo thanh điều chỉnh xuống (góc dưới bên phải của ô đã chọn).
Dưới đây là một số ví dụ về công thức mảng cơ bản trong Google Sheets.
-
=ARRAYFORMULA(LEFT(A2:A10,2)): Trả về hai ký tự đầu tiên từ các văn bản trong A2:A10. -
=ARRAYFORMULA(TO_DATE(IFERROR(EOMONTH(DATEVALUE(B1:B1000),0)))): Chuyển đổi các ngày trong B1:B1000 thành ngày cuối tháng. -
=ARRAYFORMULA(A1:A100&" "&B1:B100): Kết hợp tên đầu tiên và tên cuối cùng trong phạm vi ô A1:B100.
5 Điều cần nhớ khi sử dụng công thức mảng trong Google Sheets
Bạn nên biết khi nào sử dụng hàm ARRAYFORMULA trong Google Sheets để trả về một mảng kết quả. Dưới đây là 5 điểm quan trọng giúp bạn hiểu cách sử dụng nó và khắc phục các lỗi có thể xảy ra.
Liên quan đến hiệu suất
-
Đảm bảo rằng công thức mảng có đủ ô trống để mở rộng. Nếu công thức mảng không có đủ ô trống, nó sẽ trả về lỗi #REF!
-
Khi sử dụng các phạm vi mở, đặt công thức trong hàng đầu tiên (với dữ liệu dọc) hoặc cột đầu tiên (với dữ liệu ngang) của phạm vi. Điều này sẽ đảm bảo công thức mở rộng đúng số lượng ô.
Ví dụ, công thức mảng sau đây trong ô C3 trong Google Sheets sẽ trả về lỗi “Resut was not expanded automatically, please insert more rows(1)”.
=ARRAYFORMULA(ROW(A2:A))Đừng nhập công thức này vào hàng 3 hoặc bất kỳ hàng nào phía dưới. Nếu làm như vậy, công thức sẽ thêm một số hàng trống ở cuối trang và làm ảnh hưởng đến hiệu suất.
Liên quan đến tính năng
- Không phải tất cả các ứng dụng của hàm ARRAYFORMULA đều được thiết kế để trả về kết quả mảng. Trong một số trường hợp, hàm ARRAYFORMULA có thể được sử dụng để mở rộng kết quả của một hàm khác, chẳng hạn như hàm YEAR trong ví dụ sau:
=ARRAYFORMULA(COUNTIF(YEAR(B1:B),2023))Công thức COUNTIF này trả về số lượng ô trong phạm vi ngày tháng B1:B chứa năm 2023. Hàm ARRAYFORMULA được sử dụng để mở rộng kết quả của hàm YEAR.
Một ví dụ khác: trong trường hợp này, hàm ARRAYFORMULA được sử dụng để mở rộng C2:C5>5, không phải là hàm logic AND.
=ARRAYFORMULA(AND(C2:C5>5))- Hãy nhớ các hàm và biểu thức không được hỗ trợ bởi hàm ARRAYFORMULA trong Google Sheets.
Làm thế nào để tìm ra chúng?
Đây là một quy tắc chung mà tôi tuân thủ để tìm ra các hàm không hỗ trợ hàm ARRAYFORMULA trong Google Sheets:
Nếu một hàm không dựa trên tiêu chí chấp nhận đối số phạm vi và trả về một kết quả duy nhất, thì công thức mảng có thể không thể mở rộng nó. Ví dụ: SUM, MAX, ISDATE, AND, OR, vv.
Dưới đây là một ví dụ sử dụng hàm logic AND trong đó phạm vi ô C2:C6 chứa các giá trị Boolean TRUE hoặc FALSE.
=AND(C2:C6)Công thức này sẽ trả về TRUE nếu tất cả các giá trị Boolean trong phạm vi đều là TRUE. Ngược lại, nó sẽ trả về FALSE.
Nếu một hàm dựa trên tiêu chí chấp nhận đối số phạm vi và nhiều tiêu chí từ một phạm vi, thì nó có thể được mở rộng. Ví dụ: COUNTIF, SUMIF, VLOOKUP, XLOOKUP, IF, IFS, v.v.
Tuy nhiên, nếu bạn sử dụng nhiều tiêu chí từ nhiều phạm vi, một số hàm sẽ trả về kết quả mảng, trong khi các hàm khác sẽ không. Ví dụ, hàm SUMIFS sẽ không trả về kết quả mảng, nhưng hàm COUNTIFS sẽ trả về.
- Một số hàm có thể tự động mở rộng đến các ô lân cận mà không cần sử dụng hàm ARRAYFORMULA. Các hàm này bao gồm SEQUENCE, INDEX, QUERY, FILTER, SORT, IMPORTRANGE, VSTACK, HSTACK, v.v.
Hãy kiểm tra: Hướng dẫn các hàm Google Sheets.
Lợi ích khi sử dụng hàm ARRAYFORMULA trong Google Sheets
Dưới đây là những lợi ích chính khi sử dụng hàm ARRAYFORMULA trong Google Sheets:
-
Cải thiện hiệu suất bảng tính: Một công thức mảng có thể thay thế hàng ngàn công thức không phải là mảng, điều này có thể cải thiện hiệu suất bảng tính.
-
Dễ dàng chỉnh sửa công thức: Nếu bạn cần chỉnh sửa một công thức, bạn có thể làm điều đó nhanh chóng nếu bạn sử dụng một công thức mảng. Bạn chỉ cần chỉnh sửa công thức trong một ô duy nhất.
-
Loại bỏ lỗi: Các công thức không phải là mảng dễ bị lỗi khi bố cục bảng tính được sửa đổi. Thêm hàng hoặc cột có thể gây ra ô trống trong hàng hoặc cột đã áp dụng công thức. Công thức mảng có thể tự động điền chúng.
-
Dọn dẹp dữ liệu: Nếu bạn có các công thức mảng, sẽ dễ dàng loại bỏ các công thức cũ khỏi bảng tính của bạn. Bạn chỉ cần nhấn nút xóa trong một hoặc hai ô, tùy thuộc vào số lượng công thức mảng.
Cách sử dụng hàm ARRAYFORMULA với VLOOKUP, XLOOKUP, COUNTIF và SUMIF trong Google Sheets
Giả sử chúng ta có một danh sách 10 loại trái cây hàng đầu và nguồn gốc bản địa của chúng trong phạm vi A1:B11, trong đó A2:A11 chứa tên các loại trái cây và B2:B11 chứa nguồn gốc bản địa. Ô A1 và B1 là tiêu đề.
Tiêu chí là tên các loại trái cây “Dâu” và “Xoài” trong các ô D2 và D3. Làm thế nào để trả về nguồn gốc bản địa bằng VLOOKUP và XLOOKUP?
Đây là công thức mảng VLOOKUP để tìm kiếm tên các loại trái cây trong phạm vi A2:A11 và trả về nguồn gốc bản địa trong phạm vi B2:B11.
=ARRAYFORMULA(VLOOKUP(D2:D3,A2:B11,2,FALSE))Dưới đây là công thức mảng tương ứng cho XLOOKUP trong Google Sheets.
=ARRAYFORMULA(XLOOKUP(D2:D3,A2:A11,B2:B11))
Hình 4: Sử dụng hàm XLOOKUP cùng với hàm ARRAYFORMULA trong Google Sheets
Bạn có thể tham khảo: Sự khác biệt chính giữa VLOOKUP và XLOOKUP trong Google Sheets.
Trong cùng phạm vi dữ liệu, bạn muốn tìm số lần xuất hiện của “Bắc Mỹ” và “Đông Nam Á” trong phạm vi B2:B11.
Nếu các tiêu chí nằm trong các ô D2:D3, công thức mảng COUNTIF sau đây sẽ trả về kết quả là 1 và 3.
=ARRAYFORMULA(COUNTIF(B2:B11,D2:D3))Dưới đây là một ví dụ khác về cách sử dụng công thức mảng trong Google Sheets.
Kết hợp các công thức mảng ARRAYFORMULA và SUMIF sau đây trả về tổng các giá trị trong cột B cho các giá trị trong cột A khớp với các tiêu chí trong phạm vi ô D4:D6.
=ARRAYFORMULA(SUMIF(A1:A,D4:D6,B1:B))
Hình 5: Công thức mảng SUMIF (Cơ bản)
Những điều mà LAMBDA có thể làm nhưng ARRAYFORMULA không thể
Đây là một mẹo bổ sung. Tôi đã giải thích rằng hàm ARRAYFORMULA trong Google Sheets không thể mở rộng tất cả các công thức. Ví dụ, chúng ta không thể mở rộng MIN function sau bằng hàm ARRAYFORMULA trong Google Sheets.
=MIN(B2:E2)Vui lòng xem phạm vi ô G2:G5 trong ảnh chụp màn hình dưới đây để xem các ví dụ. Chúng ta có thể làm cho nó mở rộng bằng cách sử dụng BYROW, một trong các hàm trợ giúp của LAMBDA.
=BYROW(B2:E5,LAMBDA(v,MIN(v)))
Hình 6: Công thức Lambda: BYROW với MIN trong Google Sheets
Kết luận
Việc học hàm ARRAYFORMULA là bước đầu tiên để trở thành chuyên gia trong việc sử dụng Google Sheets. Các công thức mảng có thể thay đổi cách bạn sử dụng Google Sheets.
Luôn nhớ chỉ giữ lại các hàng và cột mà bạn muốn trong bảng tính của bạn để nâng cao hiệu suất. Hơn nữa, hãy tuân thủ các hướng dẫn của tôi khi sử dụng các phạm vi mở trong hàm này.
