Hàm ARRAYFORMULA trong Google Sheets | Hướng dẫn sử dụng từ A-Z

Khi làm việc với Google Sheets, chắc hẳn bạn đã từng gặp tình huống phải copy công thức cho hàng loạt ô, rất mất thời gian và dễ sai sót. Giải pháp hiệu quả nhất cho vấn đề này là sử dụng hàm ARRAYFORMULA trong Google Sheets. Đây là một hàm giúp bạn tự động áp dụng công thức cho cả cột, hàng hoặc mảng dữ liệu chỉ với một lần nhấp.  

Hàm ARRAYFORMULA trong Google Sheets là gì?

Hàm ARRAYFORMULA trong Google Sheets là một công cụ mạnh mẽ giúp người dùng tự động hóa quá trình tính toán và xử lý dữ liệu trên bảng tính. Hàm ARRAYFORMULA này cho phép áp dụng một công thức duy nhất cho cả một cột hoặc một phạm vi dữ liệu rộng, thay vì phải nhập công thức vào từng ô riêng lẻ. 

Điểm đặc biệt của hàm ARRAYFORMULA là khả năng mở rộng công thức trên quy mô lớn. Ví dụ, nếu bạn có hàng trăm dòng dữ liệu và muốn thực hiện phép tính giống nhau cho toàn bộ các dòng, sẽ phải sao chép công thức xuống từng dòng.

Nhưng khi sử dụng hàm ARRAYFORMULA, bạn chỉ cần viết một công thức duy nhất, Google Sheets tự động áp dụng cho tất cả các dòng trong phạm vi được chọn. 

Ngoài ra, hàm ARRAYFORMULA còn có thể kết hợp với nhiều hàm khác trong Google Sheets như IF, SUM, VLOOKUP, LEN,… để tạo ra những công thức động và linh hoạt. Khi kết hợp các hàm với nhau, bạn còn có thể dễ dàng xử lý các công thức khó khăn trong bảng tính, tự động hóa dữ liệu cho các bản báo cáo, thống kê,…

Tại sao nên dùng hàm ARRAYFORMULA trong Google Sheets?

Khi sử dụng hàm ARRAYFORMULA trong Google Sheets, ngoài việc dễ dàng kéo công thức xuống cải thiện hiệu suất công việc, bạn còn nhận được nhiều lợi ích khác như là:

• Tự động hóa các phép tính toán

Thay vì phải sao chép công thức cho hàng trăm, hàng nghìn hàng, bạn chỉ cần gõ một công thức duy nhất vào ô đầu tiên. Mọi phép tính sẽ được thực hiện tự động và mở rộng xuống các hàng bên dưới một cách liền mạch. 

Khi bạn làm việc với dữ liệu động, chẳng hạn như dữ liệu từ Google Forms hoặc các báo cáo được cập nhật liên tục thì điều này rất hữu ích. Bởi bạn không cần phải lo lắng về việc dữ liệu mới không được tính toán, hàm ARRAYFORMULA sẽ tự động xử lý tất cả. Bạn sẽ phần nào tiết kiệm được thời gian xử lý dữ liệu thủ công hàng giờ đồng hồ.

• Hiệu quả với dữ liệu lớn

Khi làm việc với các bảng tính có hàng chục nghìn hàng, mỗi công thức sao chép đều làm tăng thêm gánh nặng cho file của bạn. Thiết bị của bạn cũng sẽ dễ dàng gặp phải tình trạng lag, đơ máy hoặc thời gian tải trang kéo dài. 

Tuy nhiên, hàm ARRAYFORMULA sẽ giúp bạn xử lý được vấn đề này. Bằng cách, bạn thực hiện phép tính trên toàn bộ mảng dữ liệu chỉ trong một lần, thay vì 10.000 công thức. Sử dụng hàm ARRAYFORMULA trong Google Sheets vừa giúp file trở nên nhẹ hơn, tải nhanh hơn, hoạt động mượt hơn, vừa giảm đáng kể gánh nặng xử lý cho ứng dụng. 

• Tăng tính linh hoạt, chính xác 

Một công thức lặp lại có thể vô tình bị xóa hoặc sửa đổi ở một hàng nào đó, dẫn đến sai sót trong báo cáo. Tuy nhiên, khi sử dụng hàm ARRAYFORMULA, toàn bộ công thức nằm gọn trong một ô duy nhất.

Bạn sẽ dễ dàng quản lý, kiểm soát và chỉnh sửa. Nếu cần thay đổi, bạn chỉ cần sửa một lần ở ô công thức đầu tiên, mọi thay đổi sẽ được áp dụng cho toàn bộ cột. Điều này không chỉ tăng tính linh hoạt mà còn giảm thiểu rủi ro sai sót cho thao tác thủ công. Dữ liệu đầu ra sẽ luôn đồng nhất và chính xác trên toàn bộ mảng. 

• Tạo các bản báo cáo một cách chuyên nghiệp

Hàm ARRAYFORMULA khi kết hợp với các hàm như VLOOKUP, FILTER, QUERY có thể tạo ra những báo cáo tự động và chuyên nghiệp. Báo cáo của bạn sẽ luôn cập nhật và sẵn sàng để trình bày bất cứ lúc nào. Ngoài ra, bạn còn có thể tự động điền thông tin, lọc dữ liệu theo điều kiện, hoặc tổng hợp báo cáo chỉ bằng một công thức duy nhất. 

Cú pháp sử dụng hàm ARRAYFORMULA trong Google Sheets

Để sử dụng hàm ARRAYFORMULA trong Google Sheets, bạn chỉ cần bọc công thức thông thường của mình vào bên trong hàm. Cụ thể như sau:

=ARRAYFORMULA(công_thức_cần_mở_rộng)

Bạn có thể nhập trực tiếp hàm ARRAYFORMULA(…) vào ô tính, hoặc sử dụng phím tắt Ctrl + Shift + Enter (trên Windows) hoặc Cmd + Shift + Enter (trên Mac) sau khi gõ xong công thức. Khi đó, Google Sheets sẽ tự động thêm hàm ARRAYFORMULA(…) vào công thức. 

Công thức cần mở rộng trong hàm ARRAYFORMULA có thể là:

• Phép toán đơn giản trên dải ô. Ví dụ như =ARRAYFORMULA(A2:A10 + B2:B10) 
• Kết hợp với các hàm khác. Ví dụ như =ARRAYFORMULA(IF(C2:C10 > 10, “Đạt”, “Chưa đạt”))

*Lưu ý: Không phải hàm nào cũng có thể kết hợp với hàm ARRAYFORMULA. 

• Những hàm được thiết kế để làm việc với mảng như FILTER, QUERY hoặc UNIQUE thường không cần hàm ARRAYFORMULA. 
• Những hàm chỉ xử lý giá trị đơn lẻ như SUM, VLOOKUP (thường chỉ trả về một giá trị) khi cần xử lý nhiều giá trị thì phải kết hợp với hàm ARRAYFORMULA. 

Hướng dẫn sử dụng hàm ARRAYFORMULA trong Google Sheets

Để dễ hiểu hơn về cách sử dụng hàm ARRAYFORMULA trong Google Sheets, bạn có thể tham khảo ba tình huống thường gặp khi làm việc với dữ liệu trong Google Sheets dưới đây. 

Sử dụng hàm ARRAYFORMULA để sao chép lại toàn bộ hàng, cột và mảng

Hàm ARRAYFORMULA thường ứng dụng phổ biến nhất trong việc sao chép dữ liệu từ một cột sang cột khác mà không cần kéo công thức thủ công. 

*Ví dụ: Bạn có bảng dữ liệu về danh sách học sinh sau:

Yêu cầu: Sao chép toàn bộ dữ liệu ở cột A sang cột C bằng một công thức duy nhất.

Công thức:

=ARRAYFORMULA(A2:A)

Trong đó:

ARRAYFORMULA sẽ lấy toàn bộ dữ liệu từ cột A (bắt đầu từ A2) và sao chép sang cột C mà không cần copy từng ô.

Kết quả:

Sử dụng hàm ARRAYFORMULA để tiến hành áp dụng các hàm cho toàn bộ mảng

ARRAYFORMULA còn đặc biệt hữu ích khi kết hợp với các hàm tính toán như LEN, UPPER, LOWER… để xử lý dữ liệu hàng loạt.

*Ví dụ: Bạn muốn đếm số ký tự trong tên học sinh ở cột A.

Công thức:

=ARRAYFORMULA(LEN(A2:A5))

Trong đó:

• Hàm LEN dùng để đếm số ký tự trong một ô.
• Khi kết hợp với ARRAYFORMULA, công thức sẽ tự động áp dụng cho toàn bộ cột A thay vì chỉ một ô duy nhất.

Kết quả: 

Sử dụng hàm ARRAYFORMULA để áp dụng công thức cho toàn bộ mảng

Khi sử dụng hàm ARRAYFORMULA trong Google Sheets, bạn còn có thể dễ dàng thực hiện các phép toán giữa nhiều cột cùng lúc, chẳng hạn cộng, trừ, nhân, chia dữ liệu. 

*Ví dụ: Bạn có bảng điểm của học sinh như sau. Bạn cần tính tổng điểm của các học sinh bnao gồm cả điểm Toán và Văn:

Công thức:

=ARRAYFORMULA(B2:B5 + C2:C5)

Trong đó:

• Công thức sẽ tự động cộng giá trị từ cột B và cột C cho toàn bộ hàng.
• Không cần viết công thức cho từng dòng riêng biệt.

Kết quả:

Một số tips hay khi sử dụng hàm ARRAYFORMULA trong Google Sheets

Ngoài việc nắm được cú pháp và cách áp dụng cơ bản, bạn còn có thể nâng cao hiệu quả làm việc của hàm bằng cách kết hợp ARRAYFORMULA với các hàm cơ bản như là:

Sử dụng tính năng hỗ trợ tự động điền thông tin

Một trong những cách sử dụng phổ biến là tự động điền dữ liệu dựa trên điều kiện có sẵn. Như là: 

• Tạo mã đơn hàng tự động từ danh sách đơn mới.
• Sinh ra kết quả tính toán như chiết khấu, tổng tiền cho toàn bộ bảng.
• Điền trạng thái mặc định (ví dụ: “Chưa xử lý”) cho toàn bộ các dòng mới thêm.

*Ví dụ: Bạn có bảng danh sách điểm và muốn tự động phân loại học lực dựa trên tổng điểm.

Công thức:

=ARRAYFORMULA(IF(B2:B5>=15;”Giỏi”;”Khá”))

Trong đó:

• B2:B5>=15: kiểm tra điểm ở cột B có ≥ 15 không.
• IF(…,”Giỏi”,”Khá”): nếu đúng thì trả về “Giỏi”, sai thì “Khá”.
• ARRAYFORMULA(…): áp dụng công thức này cho cả cột thay vì từng ô.

Kết quả: 

Sử dụng kết hợp hàm FILTER để lọc dữ liệu

Hàm FILTER trong Google Sheets khi kết hợp với hàm ARRAYFORMULA sẽ giúp bạn xử lý nhiều bài toán phức tạp hơn. Bởi hàm FILTER cho phép bạn trích xuất dữ liệu thỏa mãn một điều kiện nào đó. Còn hàm ARRAYFORMULA giúp áp dụng logic đó trên toàn bộ mảng.

Một số ứng dụng phổ biến khi kết hợp ARRAYFORMULA + FILTER:

• Lọc ra danh sách khách hàng tiềm năng từ toàn bộ dữ liệu CRM.
• Lọc sản phẩm có doanh số vượt ngưỡng định sẵn.
• Lọc dữ liệu theo ngày tháng (ví dụ: chỉ hiển thị đơn hàng trong tháng hiện tại).

*Ví dụ: Lọc ra danh sách học sinh có điểm Toán từ 8 trở lên.

Công thức:

=ARRAYFORMULA(FILTER(A2:A5; B2:B5>=8))

Trong đó:

• A2:A5 : phạm vi chứa tên học sinh.
• B2:B5>=8  : điều kiện lọc, chỉ lấy học sinh có điểm từ 8 trở lên.
• FILTER(A2:A5; B2:B5>=8)  : lọc ra những học sinh thỏa điều kiện.
• ARRAYFORMULA(…)   : giúp công thức xử lý và trả về kết quả dưới dạng một danh sách nhiều ô (mảng) thay vì một ô duy nhất.

Kết quả: 

Sử dụng kết hợp hàm QUERY để phân tích dữ liệu phức tạp

Nếu hàm FILTER giúp bạn lọc nhanh, thì hàm QUERY là công cụ phân tích nâng cao trong Google Sheets. Khi kết hợp với hàm ARRAYFORMULA với hàm QUERY, bạn có thể xử lý các tập dữ liệu phức tạp gần giống như khi sử dụng SQL trong cơ sở dữ liệu.

Đồng thời, bạn còn dễ dàng xử lý được các vấn đề khác như là:

• Tạo bảng báo cáo doanh số theo khu vực hoặc theo nhân viên.
• Phân tích chiến dịch marketing bằng cách nhóm dữ liệu theo kênh (Facebook, Google Ads…).
• Tổng hợp dữ liệu tài chính, ngân sách một cách tự động.

*Ví dụ: Trong bảng điểm học sinh, ta muốn phân loại kết quả Giỏi, Khá, Trung Bình, Yếu dựa trên cột Điểm TB (E)

Công thức:

Sử dụng kết hợp ARRAYFORMULA + QUERY + IF để phân loại học sinh thành Giỏi, Khá, Trung Bình, Yếu.

Công thức thứ nhất sử dụng kết hợp ARRAYFORMULA để phân loại

=ARRAYFORMULA(

  IF(ROW(D2:D5)=1;”Xếp loại”;

    IF(LEN(D2:D5)=0;””;

      IF(D2:D5>=8;”Giỏi”;

        IF(D2:D5>=6,5;”Khá”;

          IF(D2:D5>=5;”Trung Bình”;”Yếu”)

        )

      )

    )

  )

)

Công thức thứ hai kết hợp với QUERY để lọc học sinh Giỏi, Khá trở lên

=QUERY(A1:E5; “select B, C, D, E where E = ‘Giỏi’ or E = ‘Khá’ order by D desc”; 1)

Lưu ý quan trọng khi sử dụng hàm ARRAYFORMULA trong Google Sheets

Khi sử dụng hàm ARRAYFORMULA trong Google Sheets không đúng cách, có thể bạn sẽ gặp phải các lỗi không mong muốn. Dưới đây là những lưu ý quan trọng giúp bạn sử dụng hàm này hiệu quả.

• Khi sử dụng hàm ARRAYFORMULA, bạn cần nhớ rằng nó sẽ ghi đè lên bất kỳ dữ liệu nào nằm trong dải ô mà nó tạo ra. Nếu bạn có một công thức ARRAYFORMULA ở ô A1, và có dữ liệu ở ô A2, A3, Google Sheets sẽ báo lỗi #REF!. Để tránh điều này, bạn hãy đảm bảo rằng các ô bên dưới công thức hàm ARRAYFORMULA hoàn toàn trống. Tốt nhất là bạn nên đặt nó ở đầu một cột trống.
• Những hàm như SUM, COUNT, AVERAGE… vốn dĩ đã được thiết kế để làm việc với mảng, nên việc kết hợp chúng trong hàm ARRAYFORMULA là không cần thiết. Thường thì hàm ARRAYFORMULA được dùng để biến một công thức đơn lẻ thành công thức mảng. Khi bạn kết hợp các hàm, hãy thử nghiệm để xem hàm nào cần và hàm nào không cần ARRAYFORMULA để tránh công thức bị dư thừa hoặc không hoạt động.
• Với tập dữ liệu rất lớn, hàm ARRAYFORMULA có thể làm Google Sheets xử lý chậm hơn. Vì vậy, bạn cần giới hạn phạm vi dữ liệu hợp lý.
• Nếu dữ liệu mới thêm vào nằm ngoài phạm vi mảng đã chọn, công thức chứa hàm có thể không áp dụng chính xác.
• Hàm ARRAYFORMULA sẽ xử lý toàn bộ phạm vi, nên các ô trống cũng có thể trả về kết quả không mong muốn.

Các lỗi thường gặp khi sử dụng hàm ARRAYFORMULA

Sử dụng hàm ARRAYFORMULA trong Google Sheets đôi khi bạn cũng có thể gặp một số lỗi phổ biến. Dưới đây là những lỗi thường gặp nhất, cùng phân tích nguyên nhân và cách khắc phục:

Sử dụng với hàm đã trả về mảng

Một số hàm trong Google Sheets vốn đã trả về một mảng kết quả (ví dụ như FILTER, QUERY, SEQUENCE). Do đó, khi chúng được bao với hàm ARRAYFORMULA có thể gây ra lỗi và kết quả không như mong muốn.

Vì vậy, bạn hãy bỏ hàm ARRAYFORMULA nếu hàm nội tại đã xử lý mảng, giúp tránh dư thừa và giảm khả năng lỗi.

Sử dụng với hàm không chấp nhận mảng

Một số hàm như SUM, SUMIFS, COUNT, CONCATENATE… không trả về mảng và cũng không thể mở rộng qua mảng khi kết hợp với hàm ARRAYFORMULA.

Do đó, nếu bạn cần tính toán theo mảng, hãy tìm cách thay thế như SUMIF thay vì SUMIFS, hoặc kết hợp với QUERY để nhóm và tổng hợp dữ liệu linh hoạt hơn.

Lỗi “Ghi đè” ô

Khi sử dụng hàm ARRAYFORMULA cùng với hàm VLOOKUP hoặc hàm khác, nếu vùng chứa công thức không hoàn toàn trống, Google Sheets sẽ báo lỗi overwrite (hoặc #REF). 

Do đó, bạn cần phải đảm bảo vùng ô mà hàm ARRAYFORMULA sẽ lan tới là trống hoàn toàn trước khi áp dụng công thức. Hoặc bạn có thể thêm logic IF để chỉ chạy hàm ARRAYFORMULA nếu ô đích trống.

Lỗi liên quan đến phân tích cú pháp (Parse Error)

Lỗi phân tích cú pháp thường xảy ra khi công thức có sai cú pháp. Ví dụ như công thức thiếu dấu ngoặc, dấu phân cách sai (theo ngôn ngữ), hoặc ký tự không hợp lệ.

Để khắc phục lỗi này, bạn chỉ cần thực hiện như sau:

• Kiểm tra xem bạn đã đóng, mở đủ số “()” hay chưa.
• Chú ý dấu phân tách (“,” hay “;”) tuỳ cấu hình ngôn ngữ.
• Loại bỏ ký tự lạ không mong muốn.

>>> Xem thêm: Chuyển text thành số trong google sheet

Lời kết

Bài viết đã giúp bạn hiểu rõ về hàm ARRAYFORMULA trong Google Sheets. Từ khái niệm, cách sử dụng cho đến các lỗi thường gặp và cách khắc phục hiệu quả. Người dùng nên đăng ký ngay ứng dụng Google Sheets để gia tăng hiệu quả công việc, bằng cách mua ngay gói Google Workspace bản quyền. 

Nếu bạn còn bất cứ thắc mắc nào liên quan việc sử dụng Google Sheets, hãy liên hệ ngay với GCS Việt Nam qua các kênh hỗ trợ chính thức để được tư vấn và giải đáp chi tiết.

• Fanpage: GCS – Google Cloud Solutions 

• Hotline: 024.9999.7777