Nội dung bài viết
Việc phải dùng chuột kéo sao chép công thức thủ công cho hàng nghìn dòng dữ liệu trên bảng tính không chỉ khiến bạn mất nhiều thời gian, công sức mà còn dễ làm file bị nặng, lag và tăng nguy cơ sai sót dữ liệu. Để tự động hóa quy trình tính toán hàng loạt chỉ với một lần nhập công thức duy nhất, sử dụng hàm mảng động thông minh chính là giải pháp tối ưu hàng đầu. Bài viết dưới đây MSO sẽ giải đáp chi tiết hàm ARRAYFORMULA là gì, phân tích lợi ích và cú pháp kích hoạt nhanh, đồng thời hướng dẫn cách sử dụng hàm ARRAYFORMULA kết hợp linh hoạt với các hàm IF, VLOOKUP, QUERY để bứt phá hiệu suất xử lý dữ liệu trên Google Sheets.
Hàm ARRAYFORMULA là gì?
ARRAYFORMULA là hàm trong Google Sheets cho phép áp dụng một công thức cho nhiều ô, nhiều hàng hoặc toàn bộ cột dữ liệu chỉ với một lần nhập. Thay vì phải sao chép công thức xuống từng dòng như các công thức thông thường, ARRAYFORMULA sẽ tự động thực hiện phép tính trên toàn bộ phạm vi dữ liệu và mở rộng kết quả tương ứng.
Đây là một trong những hàm được sử dụng phổ biến nhất khi làm việc với dữ liệu lớn trên Google Sheets bởi khả năng tự động hóa tính toán và cập nhật kết quả khi có dữ liệu mới phát sinh. Nhờ đó, người dùng có thể giảm đáng kể thời gian thao tác thủ công, hạn chế sai sót và tối ưu hiệu suất xử lý dữ liệu.
Ví dụ, thay vì nhập công thức nhân số lượng với đơn giá cho từng dòng sản phẩm, bạn chỉ cần sử dụng một công thức ARRAYFORMULA để tính toán cho toàn bộ danh sách sản phẩm trong bảng tính.
Lý do nên dùng hàm ARRAYFORMULA trong Google Sheets?
Hàm ARRAYFORMULA giúp người dùng tự động áp dụng một công thức cho nhiều ô, nhiều hàng hoặc toàn bộ cột dữ liệu chỉ với một lần nhập, từ đó tiết kiệm thời gian, giảm thao tác sao chép công thức thủ công và hạn chế sai sót khi xử lý dữ liệu. Đây là giải pháp đặc biệt hiệu quả khi làm việc với bảng tính có số lượng lớn dữ liệu, cần tự động cập nhật kết quả hoặc xây dựng các báo cáo động trên Google Sheets.
Tự động hóa tính toán
Hàm ARRAYFORMULA giúp thực hiện phép tính cho cả cột hoặc nhiều hàng dữ liệu cùng lúc mà không cần kéo công thức thủ công. Khi có dữ liệu mới được thêm vào, công thức vẫn tiếp tục hoạt động và tự động cập nhật kết quả. Điều này đặc biệt hữu ích với các bảng dữ liệu được cập nhật thường xuyên như báo cáo bán hàng, dữ liệu từ Google Forms hoặc danh sách khách hàng.
Xử lý hiệu quả dữ liệu lớn
Đối với các bảng tính có hàng nghìn dòng dữ liệu, việc sao chép công thức cho từng hàng có thể khiến file trở nên nặng và hoạt động chậm hơn. ARRAYFORMULA giúp giảm số lượng công thức cần sử dụng bằng cách xử lý toàn bộ dữ liệu trong một công thức duy nhất. Nhờ đó, bảng tính vận hành mượt mà hơn và tiết kiệm tài nguyên xử lý.
Giảm sai sót và dễ quản lý
Khi sử dụng công thức thông thường, một ô bất kỳ có thể bị chỉnh sửa hoặc xóa nhầm, dẫn đến kết quả không đồng nhất. Với ARRAYFORMULA, toàn bộ logic tính toán được quản lý trong một công thức duy nhất, giúp việc kiểm soát và chỉnh sửa trở nên đơn giản hơn. Nếu cần thay đổi công thức, bạn chỉ cần chỉnh sửa một lần thay vì cập nhật từng dòng dữ liệu.
Hỗ trợ xây dựng hệ thống báo cáo tự động
ARRAYFORMULA có thể kết hợp với các hàm như FILTER, VLOOKUP, QUERY hoặc IF để tạo các báo cáo tự động và luôn được cập nhật theo dữ liệu mới nhất. Nhờ khả năng tự động hóa này, người dùng có thể tiết kiệm đáng kể thời gian xử lý dữ liệu và xây dựng các bảng báo cáo chuyên nghiệp hơn trên Google Sheets.
Cú pháp sử dụng hàm ARRAYFORMULA trong Google Sheets
Cú pháp cơ bản của hàm ARRAYFORMULA rất đơn giản:
=ARRAYFORMULA(array_formula)
Trong đó:
- array_formula: Là một biểu thức toán học, một hàm hoặc một vùng dữ liệu (mảng) mà bạn muốn áp dụng công thức lên đó.
***Mẹo kích hoạt nhanh: Bạn không cần gõ từng ký tự ARRAYFORMULA. Sau khi nhập công thức thông thường (ví dụ: =A2:A*B2:B), bạn chỉ cần nhấn tổ hợp phím Ctrl + Shift + Enter (trên Windows) hoặc Cmd + Shift + Enter (trên Mac). Google Sheets sẽ tự động bao bọc công thức của bạn trong hàm ARRAYFORMULA.
Hướng dẫn sử dụng hàm ARRAYFORMULA trong Google Sheets
Sau khi hiểu được hàm ARRAYFORMULA là gì và cú pháp hoạt động của hàm, bạn có thể bắt đầu áp dụng vào các tình huống xử lý dữ liệu thực tế. Điểm mạnh lớn nhất của ARRAYFORMULA là khả năng thực hiện phép tính trên nhiều dòng dữ liệu cùng lúc mà không cần sao chép công thức xuống từng ô. Dưới đây là những cách sử dụng phổ biến nhất mà người dùng Google Sheets thường áp dụng.
Phép tính toán cơ bản trên toàn bộ cột (Cộng, Trừ, Nhân, Chia)
Đây là cách sử dụng phổ biến nhất của ARRAYFORMULA. Hàm giúp thực hiện phép tính cho toàn bộ danh sách dữ liệu chỉ với một công thức duy nhất mà không cần kéo công thức xuống từng dòng.
Ví dụ: Bạn có bảng dữ liệu bán hàng gồm cột Số lượng và Đơn giá của từng sản phẩm. Yêu cầu tính Thành tiền cho toàn bộ danh sách sản phẩm mà không cần kéo công thức xuống từng dòng.
Thông thường bạn sẽ nhập: =B2*C2 sau đó kéo xuống các dòng bên dưới. Tuy nhiên với ARRAYFORMULA, chỉ cần nhập: =ARRAYFORMULA(B2:B*C2:C)
Google Sheets sẽ tự động nhân giá trị ở cột Số lượng với cột Đơn giá cho toàn bộ các dòng dữ liệu và trả về kết quả tương ứng trong cột Thành tiền.
**Lưu ý:
- Không nên tham chiếu toàn bộ cột nếu bảng dữ liệu quá lớn vì có thể làm chậm file.
- Nên kết hợp IF để tránh hiển thị kết quả ở các dòng trống.
Kết hợp với hàm IF để xử lý logic hàng loạt
ARRAYFORMULA thường được sử dụng cùng hàm IF để tự động phân loại dữ liệu, đánh giá kết quả hoặc tạo trạng thái cho nhiều dòng cùng lúc.
Ví dụ: Bạn có bảng điểm của học sinh và muốn tự động xác định học sinh Đạt hoặc Không đạt dựa trên điểm số.
Dựa theo dữ liệu bên dưới, để phân loại kết quả học tập cho toàn bộ danh sách học sinh, bạn cần sử dụng ARRAYFORMULA kết hợp với IF tại ô D2.
Công thức áp dụng tại ô D2 là: =ARRAYFORMULA(IF(B2:B>=5,”Đạt”,”Không đạt”))
Google Sheets sẽ tự động kiểm tra toàn bộ điểm số trong cột B và trả về kết quả tương ứng.
***Lưu ý: Nếu bảng dữ liệu còn nhiều dòng trống bên dưới, nên dùng: =ARRAYFORMULA(IF(B2:B=””,””,IF(B2:B>=5,”Đạt”,”Không đạt”))) để tránh xuất hiện hàng loạt dòng “Không đạt” ở cuối bảng.
Kết hợp với hàm VLOOKUP để dò tìm dữ liệu cho cả cột
Một trong những ứng dụng mạnh nhất của ARRAYFORMULA là kết hợp với VLOOKUP để tra cứu hàng loạt dữ liệu tự động.
Ví dụ: Bạn có một bảng đơn hàng chỉ chứa Mã sản phẩm và cần tự động hiển thị Tên sản phẩm tương ứng từ bảng dữ liệu nguồn.
Dựa theo dữ liệu bên dưới, để tra cứu tên sản phẩm cho toàn bộ danh sách đơn hàng, bạn cần sử dụng ARRAYFORMULA kết hợp với VLOOKUP tại ô B2.
Công thức áp dụng tại ô B2 là: =ARRAYFORMULA(VLOOKUP(A2:A,F:G,2,FALSE))
Trong đó:
- A2 là danh sách mã sản phẩm cần dò tìm.
- F là bảng dữ liệu nguồn.
- 2 là cột chứa tên sản phẩm.
- FALSE là tìm kiếm chính xác.
Google Sheets sẽ tự động trả về tên sản phẩm tương ứng cho toàn bộ danh sách mã sản phẩm.
***Lưu ý: Nếu mã sản phẩm không tồn tại trong bảng dữ liệu nguồn, công thức sẽ trả về lỗi #N/A. Để xử lý trường hợp này, bạn hoàn toàn có thể sử dụng:
=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A,F:G,2,FALSE),”Không tìm thấy”))
Những lỗi thường gặp khi sử dụng hàm ARRAYFORMULA
Mặc dù hàm ARRAYFORMULA giúp tự động hóa tính toán và xử lý dữ liệu rất hiệu quả trong Google Sheets, người dùng vẫn có thể gặp một số lỗi trong quá trình sử dụng. Các lỗi phổ biến nhất bao gồm lỗi #REF! do không đủ không gian hiển thị kết quả, lỗi #VALUE! do dữ liệu đầu vào không đúng định dạng và tình trạng công thức tạo ra quá nhiều dòng trống khi tham chiếu toàn bộ cột. Việc hiểu rõ nguyên nhân và cách khắc phục từng lỗi sẽ giúp bạn có thể sử dụng ARRAYFORMULA hiệu quả hơn, đồng thời hạn chế các vấn đề ảnh hưởng đến báo cáo hoặc bảng tính.
Lỗi #REF!
Đây là lỗi xuất hiện phổ biến nhất khi sử dụng ARRAYFORMULA. Lỗi này xảy ra khi Google Sheets cần mở rộng kết quả xuống nhiều ô phía dưới nhưng các ô đó đã chứa dữ liệu khác, khiến công thức không thể hiển thị đầy đủ kết quả.
Ví dụ, bạn sử dụng công thức: =ARRAYFORMULA(A2:A*B2:B) để tính thành tiền cho toàn bộ danh sách sản phẩm. Tuy nhiên, nếu trong cột kết quả đã có một số ô chứa dữ liệu được nhập thủ công, Google Sheets sẽ hiển thị lỗi #REF! cùng thông báo “Array result was not expanded because it would overwrite data”.
Để khắc phục lỗi này, bạn chỉ cần xóa dữ liệu đang cản trở bên dưới hoặc chuyển công thức ARRAYFORMULA sang một cột hoàn toàn trống để Google Sheets có đủ không gian hiển thị kết quả.
Lỗi #VALUE!
Lỗi #VALUE! thường xuất hiện khi dữ liệu đầu vào không cùng kiểu dữ liệu hoặc chứa giá trị không thể thực hiện phép tính.
Ví dụ, công thức: =ARRAYFORMULA(A2:A*B2:B) sẽ hoạt động bình thường khi cả hai cột đều chứa số. Tuy nhiên, nếu một số ô trong cột A hoặc B chứa văn bản như “N/A”, “Chưa cập nhật” hoặc ký tự đặc biệt, Google Sheets sẽ không thể thực hiện phép nhân và trả về lỗi #VALUE!.
Để khắc phục, bạn nên kiểm tra và chuẩn hóa dữ liệu nguồn trước khi tính toán. Ngoài ra, có thể sử dụng hàm IFERROR để bỏ qua các giá trị lỗi: =ARRAYFORMULA(IFERROR(A2:A*B2:B,””)) . Khi đó, các ô gặp lỗi sẽ được thay thế bằng giá trị trống thay vì hiển thị thông báo lỗi trên bảng tính.
Công thức tạo quá nhiều dòng trống
Đây không phải là lỗi hệ thống nhưng là tình trạng khá phổ biến khi người dùng tham chiếu toàn bộ cột trong ARRAYFORMULA. Khi đó, Google Sheets sẽ cố gắng xử lý cả những dòng chưa có dữ liệu, khiến bảng tính xuất hiện nhiều ô hoặc dòng kết quả trống không cần thiết.
Ví dụ:
=ARRAYFORMULA(A2:A*B2:B). Công thức này sẽ áp dụng cho toàn bộ cột A và B, kể cả các dòng chưa nhập dữ liệu. Điều này khiến cột kết quả kéo dài xuống hàng trăm hoặc hàng nghìn dòng trống, làm bảng tính trở nên khó theo dõi hơn.
Để khắc phục, bạn nên kết hợp ARRAYFORMULA với hàm IF để chỉ thực hiện tính toán ở những dòng có dữ liệu: =ARRAYFORMULA(IF(A2:A<>””,A2:A*B2:B,””))
Kết quả sẽ chỉ hiển thị tại các dòng thực sự có dữ liệu, giúp bảng tính gọn gàng và chuyên nghiệp hơn.
Mẹo sử dụng hàm ARRAYFORMULA giúp xử lý dữ liệu nhanh hơn
ARRAYFORMULA vốn đã là một công cụ mạnh mẽ giúp tự động hóa việc tính toán trên Google Sheets. Tuy nhiên, để công thức hoạt động nhanh hơn, bảng tính gọn gàng hơn và hạn chế các lỗi phát sinh khi làm việc với dữ liệu lớn, bạn nên áp dụng một số mẹo như chỉ tham chiếu phạm vi dữ liệu cần thiết, kết hợp với hàm IF để loại bỏ dòng trống, sử dụng cùng QUERY để tạo báo cáo tự động và kết hợp với FILTER hoặc VLOOKUP để xử lý dữ liệu theo thời gian thực. Những cách làm này không chỉ giúp tối ưu hiệu suất bảng tính mà còn giúp việc quản lý dữ liệu trở nên chuyên nghiệp hơn.
Chỉ tham chiếu phạm vi dữ liệu cần thiết
Một trong những sai lầm phổ biến của người mới là tham chiếu toàn bộ cột khi sử dụng ARRAYFORMULA. Mặc dù công thức vẫn hoạt động bình thường nhưng Google Sheets sẽ phải xử lý cả những dòng chưa có dữ liệu, làm tăng thời gian tính toán và ảnh hưởng đến hiệu suất của bảng tính khi dữ liệu ngày càng lớn.
Thay vì sử dụng: A:A bạn nên giới hạn phạm vi dữ liệu thực tế: A2:A1000
Ví dụ, nếu danh sách sản phẩm của bạn chỉ có khoảng 1.000 dòng, việc sử dụng A2 sẽ giúp Google Sheets xử lý nhanh hơn đáng kể so với tham chiếu toàn bộ cột A.
Luôn kết hợp IF để loại bỏ dòng trống
Khi sử dụng ARRAYFORMULA trên toàn bộ cột, công thức thường tạo ra rất nhiều dòng kết quả trống bên dưới dữ liệu thực tế. Điều này khiến bảng tính trở nên dài dòng, khó theo dõi và thiếu tính chuyên nghiệp.
Một giải pháp đơn giản là kết hợp ARRAYFORMULA với hàm IF:
=ARRAYFORMULA(IF(A2:A=””,””,A2:A*B2:B))
Ví dụ, nếu cột A chứa tên sản phẩm và cột B chứa đơn giá, công thức trên chỉ thực hiện phép tính ở những dòng có dữ liệu. Những dòng trống sẽ không hiển thị kết quả.
Kết hợp với QUERY để tạo báo cáo tự động
Nếu thường xuyên làm báo cáo trên Google Sheets, bạn nên kết hợp ARRAYFORMULA với QUERY để tự động lọc và hiển thị dữ liệu theo điều kiện mong muốn.
Ví dụ, bạn có bảng dữ liệu bán hàng và chỉ muốn hiển thị những đơn hàng có doanh thu lớn hơn 1.000.000 đồng:
=QUERY(ARRAYFORMULA(A2:D),”SELECT Col1, Col2 WHERE Col4>1000000″,0)
Khi dữ liệu nguồn thay đổi hoặc phát sinh thêm đơn hàng mới, báo cáo sẽ tự động cập nhật mà không cần chỉnh sửa lại công thức.
Kết hợp với FILTER và VLOOKUP để xử lý dữ liệu theo thời gian thực
ARRAYFORMULA phát huy hiệu quả rất tốt khi kết hợp với FILTER hoặc VLOOKUP trong các hệ thống quản lý dữ liệu.
Ví dụ, bạn có thể sử dụng ARRAYFORMULA kết hợp VLOOKUP để tự động tra cứu tên sản phẩm cho toàn bộ danh sách mã sản phẩm: =ARRAYFORMULA(VLOOKUP(A2:A,F:G,2,FALSE))
Hoặc kết hợp với FILTER để chỉ hiển thị những khách hàng có doanh thu vượt một ngưỡng nhất định:
=FILTER(A2:C,C2:C>=10000000)
Nhờ khả năng tự động mở rộng công thức của ARRAYFORMULA, mọi thay đổi trong dữ liệu nguồn sẽ được cập nhật ngay lập tức mà không cần sao chép công thức thủ công.
Câu hỏi thường gặp về hàm ARRAYFORMULA
Trong quá trình sử dụng ARRAYFORMULA, người dùng thường thắc mắc về khả năng tương thích với Excel, cơ chế tự động cập nhật dữ liệu cũng như việc kết hợp với các hàm khác như SUMIF hoặc QUERY. Dưới đây là những câu hỏi phổ biến nhất giúp bạn hiểu rõ hơn cách hoạt động của hàm ARRAYFORMULA trong Google Sheets.
- ARRAYFORMULA có dùng được trong Excel không?
Không. ARRAYFORMULA là hàm được phát triển riêng cho Google Sheets và không tồn tại trong Microsoft Excel. Tuy nhiên, Excel hiện đại (Excel 365 và Excel 2021) đã hỗ trợ công nghệ Dynamic Array với khả năng tương tự thông qua các hàm như FILTER, UNIQUE, SORT, SEQUENCE và XLOOKUP. Ngoài ra, các phiên bản Excel cũ còn hỗ trợ công thức mảng (Array Formula) bằng tổ hợp phím Ctrl + Shift + Enter.
- ARRAYFORMULA có tự cập nhật khi thêm dữ liệu mới không?
Có. Đây là ưu điểm nổi bật nhất của ARRAYFORMULA. Khi có dữ liệu mới được thêm vào phạm vi tham chiếu, công thức sẽ tự động tính toán và mở rộng kết quả mà không cần người dùng kéo lại công thức.
- Có thể kết hợp ARRAYFORMULA với SUMIF không?
Có thể. Ví dụ: =ARRAYFORMULA(SUMIF(A2:A,D2:D,B2:B)). Tuy nhiên, trong nhiều trường hợp SUMIF đã tự hỗ trợ xử lý phạm vi dữ liệu nên không nhất thiết phải kết hợp với ARRAYFORMULA.
- Có thể kết hợp ARRAYFORMULA với QUERY không?
Hoàn toàn có thể. Đây là một trong những cách kết hợp mạnh nhất trong Google Sheets, giúp xây dựng báo cáo động, dashboard quản trị và hệ thống phân tích dữ liệu tự động mà không cần cập nhật thủ công mỗi khi dữ liệu thay đổi.
Kết luận
Hiểu rõ hàm ARRAYFORMULA là gì? là một kỹ năng rất hữu ích giúp bạn tự động hóa quá trình tính toán, xử lý dữ liệu hàng loạt và xây dựng bảng tính chuyên nghiệp hơn. Chỉ cần nắm vững cú pháp cơ bản, biết cách kết hợp ARRAYFORMULA với các hàm như IF, VLOOKUP, FILTER, QUERY và xử lý đúng các lỗi thường gặp như #REF! hay #VALUE!, bạn có thể tiết kiệm đáng kể thời gian thao tác thủ công, hạn chế sai sót và tối ưu hiệu suất làm việc với dữ liệu.
Nếu bạn đang tìm kiếm giải pháp nâng cao kỹ năng tin học văn phòng và tối ưu hóa việc sử dụng các công cụ trong Microsoft 365 một cách chuyên nghiệp, hãy liên hệ ngay với MSO – đối tác ủy quyền chính thức của Microsoft tại Việt Nam để nhận được sự tư vấn, hỗ trợ chuyên sâu.
——————————————————
Fanpage: MSO.vn – Microsoft 365 Việt Nam
Hotline: 024.9999.7777
