ARRAYFORMULA là hàm bọc của Google Sheets cho phép một công thức đặt ở một ô tính cho cả một dải, thường là cả cột, thay vì phải kéo công thức xuống từng dòng. Ví dụ =ARRAYFORMULA(IF(A2:A="", "", E2:E*F2:F)) đặt ở ô G2 sẽ tính thành tiền bằng số lượng nhân đơn giá cho mọi dòng có dữ liệu, và tự tính luôn cho dòng mới thêm vào sau này.
Hàm này đặc biệt hữu ích với bảng nhận dữ liệu từ biểu mẫu Google Forms, nơi dòng mới được thêm vào mà không ai kéo công thức hộ. Bài viết đi qua cách hàm hoạt động, các mẫu dùng hằng ngày ở văn phòng và cửa hàng, những hàm không chịu chạy trong ARRAYFORMULA, lỗi tràn ô hay gặp, và cách mô tả cho trợ lý AI để nó viết đúng ngay lần đầu.
Kéo công thức xuống và ARRAYFORMULA khác nhau ở đâu
Khi bạn viết E2*F2 ở ô G2 rồi kéo xuống đến G500, bảng có 499 công thức riêng lẻ, mỗi ô một công thức. Cách này dễ hiểu, nhưng có ba điểm yếu quen thuộc. Dòng 501 thêm vào sẽ không có công thức nếu không ai nhớ kéo tiếp. Một người lỡ gõ đè số vào G230 thì ô đó thành số chết, không ai nhận ra vì nhìn vẫn giống các ô khác. Và khi cần sửa cách tính, bạn phải sửa rồi kéo lại cả cột.
ARRAYFORMULA đổi cách nghĩ: công thức nhận vào cả dải E2:E và F2:F, tính từng cặp giá trị, rồi trả về cả một cột kết quả tràn xuống từ ô chứa nó. Chỉ có một công thức duy nhất ở G2; các ô bên dưới hiển thị kết quả nhưng không chứa công thức. Sửa cách tính là sửa một chỗ. Ai gõ đè vào một ô trong vùng kết quả thì công thức báo lỗi ngay, thay vì im lặng sai.
Phím tắt tiện dụng: gõ công thức như bình thường với dải thay cho ô đơn, rồi nhấn Ctrl, Shift và Enter cùng lúc, Google Sheets tự bọc ARRAYFORMULA bên ngoài. Trên máy Mac là Command thay cho Ctrl. Excel có khái niệm mảng động tương tự nhưng cơ chế và cú pháp không giống hẳn; bài này chỉ nói Google Sheets, phần Excel có danh mục riêng trên trang.
Năm mẫu ARRAYFORMULA dùng hằng ngày
Tính thành tiền, bỏ dòng trống. Mẫu =ARRAYFORMULA(IF(A2:A="", "", E2:E*F2:F)). Phần IF kiểm cột A để các dòng chưa có dữ liệu hiện trống thay vì số 0 kéo dài vô tận. Cột dùng để kiểm nên là cột luôn có dữ liệu ở mọi dòng thật, như ngày hoặc mã đơn.
Ghép họ tên hoặc mã. Mẫu =ARRAYFORMULA(IF(B2:B="", "", B2:B&" "&C2:C)) ghép họ với tên. Cùng cách đó ghép mã chi nhánh với số thứ tự thành mã đơn, ví dụ giả định CN1 nối với số 0001.
Tra cứu cả cột. Mẫu =ARRAYFORMULA(IF(D2:D="", "", IFERROR(VLOOKUP(D2:D, DanhMuc!A:C, 3, FALSE), "Không có mã"))) lấy tên hàng từ trang danh mục cho mọi mã trong cột D. IFERROR với lời nhắn rõ giúp nhận ra ngay mã gõ sai thay vì một ô #N/A khó hiểu.
Phân loại theo điều kiện. Mẫu =ARRAYFORMULA(IF(F2:F="", "", IF(F2:F>=1000000, "Đơn lớn", "Đơn thường"))). Nhiều nhóm thì dùng IFS, nhưng nhớ rằng ngưỡng ở đây là ví dụ giả định, ngưỡng thật do bạn đặt.
Đặt tiêu đề ngay trong công thức. Đặt ở ô G1 công thức ={"Thành tiền"; ARRAYFORMULA(IF(A2:A="", "", E2:E*F2:F))}. Dấu chấm phẩy trong ngoặc nhọn xếp tiêu đề nằm trên kết quả. Cách này giữ tiêu đề và công thức đi cùng nhau, rất hợp với trang nhận dữ liệu từ biểu mẫu vì không ai chèn dòng vào giữa tiêu đề và công thức được.
Vì sao ARRAYFORMULA hợp với bảng nhận dữ liệu từ biểu mẫu
Khi bạn liên kết Google Forms với một bảng tính, mỗi lần có người gửi biểu mẫu, một dòng mới được ghi vào trang câu trả lời. Công thức bạn kéo sẵn ở các cột bên cạnh không tự đi theo dòng mới một cách đáng tin cậy. Kết quả quen thuộc là vài tuần đầu mọi thứ đúng, sau đó cột tính toán bỏ trống ở những dòng mới và báo cáo thiếu.
Cách làm ổn định hơn có hai lựa chọn. Lựa chọn một là đặt ARRAYFORMULA ở ô tiêu đề của cột tính toán ngay trong trang câu trả lời, như mẫu thứ năm ở trên. Lựa chọn hai, gọn hơn cho bảng dùng lâu dài, là để nguyên trang câu trả lời không đụng vào, rồi tạo một trang tính khác đọc dữ liệu từ đó bằng ARRAYFORMULA hoặc QUERY và tính toán ở trang mới. Trang câu trả lời khi ấy đóng vai dữ liệu gốc, không ai sắp xếp, chèn cột hay sửa trong đó.
Lựa chọn hai còn có lợi khi bạn sửa câu hỏi trong biểu mẫu. Thêm câu hỏi mới thường tạo cột mới ở trang câu trả lời; công thức đặt ở trang khác ít bị xô lệch hơn. Chi tiết cách thiết kế biểu mẫu để dữ liệu đổ về gọn có bài riêng trong danh mục này.
Hàm nào chạy được trong ARRAYFORMULA, hàm nào không
Không phải hàm nào bỏ vào ARRAYFORMULA cũng tự tính theo từng dòng. Những hàm vốn gộp cả dải thành một con số sẽ trả về một giá trị duy nhất. Bảng dưới gom các trường hợp hay gặp và cách thay thế.
| Hàm hoặc phép toán | Hành vi trong ARRAYFORMULA | Cách thay thế khi cần tính từng dòng |
|---|---|---|
| Cộng, trừ, nhân, chia, nối chữ bằng dấu và | Tính từng dòng bình thường | Không cần thay |
| IF, IFERROR, VLOOKUP, LEFT, RIGHT, MID, LEN, TEXT | Tính từng dòng bình thường | Không cần thay |
| AND, OR | Gộp cả dải thành một giá trị đúng hoặc sai | Dùng phép nhân thay cho AND, phép cộng lớn hơn 0 thay cho OR |
| SUM, MAX, MIN, AVERAGE trên một dải | Trả về một con số cho cả dải | Dùng BYROW với LAMBDA, hoặc viết phép cộng từng cột |
| Hàm có điều kiện như COUNTIFS, SUMIFS | Tuỳ cách viết; có công thức chạy, có công thức không | Thử trên vài dòng; không ổn thì dùng MAP hoặc BYROW với LAMBDA |
| Hàm tự trả về mảng như FILTER, UNIQUE, SORT, SPLIT | Vốn đã trả về mảng, bọc thêm thường không cần | Dùng trực tiếp, chú ý vùng tràn |
Nhóm hàm LAMBDA như BYROW, MAP được Google Sheets bổ sung vài năm gần đây, cho phép viết một phép tính áp lên từng dòng một cách tường minh. Khi ARRAYFORMULA không chịu chạy với một hàm, hãy hỏi AI viết bằng BYROW và dặn rõ phiên bản Google Sheets bạn dùng là bản trên trình duyệt hiện hành. Dù dùng cách nào, kiểm trên vài dòng trước khi áp cho cả cột.
Lỗi tràn ô và những lỗi hay gặp khác
Thông báo kết quả mảng không mở rộng được vì sẽ ghi đè dữ liệu. Đây là lỗi phổ biến nhất. Có một ô nào đó trong vùng kết quả cần tràn xuống đã có nội dung, kể cả một dấu cách vô hình hay một số ai đó gõ tạm. Rê chuột vào ô báo lỗi, Google Sheets chỉ ra địa chỉ ô chặn đường. Xoá nội dung ô đó là công thức chạy lại.
Công thức chạy đến hàng nghìn dòng trống. Dải mở như A2:A kéo đến cuối trang tính. Nếu bạn quên phần IF kiểm ô trống, kết quả sẽ là một cột số 0 hoặc lỗi kéo dài, làm bảng nặng và khó nhìn. Luôn có điều kiện bỏ dòng trống, và với bảng rất lớn, cân nhắc giới hạn dải đến một số dòng hợp lý.
Kích thước các dải không khớp. Viết E2:E nhân với F2:F100 sẽ báo lỗi kích thước. Mọi dải trong một phép tính mảng phải cùng số dòng. AI hay mắc lỗi này khi bạn mô tả bảng không thống nhất, ví dụ nói dữ liệu đến dòng 100 ở chỗ này và để trống ở chỗ khác.
Dấu phân cách sai theo vùng. Ở tệp đặt vùng Việt Nam, dấu phân cách tham số thường là chấm phẩy; trong ngoặc nhọn, dấu xếp ngang thường là gạch chéo ngược thay cho dấu phẩy, còn dấu xếp dọc vẫn là chấm phẩy. Công thức AI viết theo kiểu tiếng Anh sẽ báo lỗi phân tích khi dán vào tệp vùng Việt Nam nếu không đổi dấu.
Sắp xếp làm lệch dữ liệu. Nếu cột kết quả ARRAYFORMULA nằm cạnh dữ liệu gốc và ai đó sắp xếp bảng, kết quả vẫn tính đúng theo dòng mới vì công thức đọc lại cả cột. Nhưng nếu có cột nhập tay bên cạnh, như ghi chú, cột đó sẽ đi theo dòng còn cột công thức thì không. Đây là lý do nên tách trang tính toán khỏi trang dữ liệu gốc khi bảng có nhiều người cùng sửa.
Nhờ AI viết ARRAYFORMULA và kiểm kết quả
Trợ lý AI viết ARRAYFORMULA khá tốt nếu được mô tả đúng. Quy trình gọn dưới đây áp dụng cho ChatGPT, Claude, Gemini hay trợ lý tích hợp trong Google Sheets:
- Nói rõ Google Sheets, nói rõ muốn một công thức duy nhất cho cả cột, đặt ở ô nào.
- Mô tả các cột tham gia bằng chữ cái, kiểu dữ liệu và cột nào luôn có dữ liệu ở dòng thật để làm điều kiện bỏ dòng trống.
- Dán vài dòng mẫu đã thay tên khách, số điện thoại, địa chỉ bằng dữ liệu giả định.
- Nói vùng của tệp để AI dùng đúng dấu phân cách.
- Dán công thức vào một trang tính thử, so ba đến năm dòng với phép tính tay. Chọn cả dòng bình thường lẫn dòng đặc biệt như số lượng bằng 0, mã hàng không có trong danh mục, ô trống giữa bảng.
- Thêm một dòng mới ở cuối bảng thử, xem công thức có tự tính cho dòng đó không.
- Ghi một ô chú thích bằng lời công thức tính gì, rồi mới chép sang bảng thật.
Một câu lệnh mẫu: Viết một công thức ARRAYFORMULA cho Google Sheets đặt ở ô H1 của trang TraLoi, dòng 1 là tiêu đề. Cột B là mã hàng, cột C là số lượng, trang DanhMuc có mã ở cột A và đơn giá ở cột C. Cần cột Thành tiền bằng số lượng nhân đơn giá tra từ danh mục, bỏ dòng trống, mã không có thì ghi Sai mã. Tệp đặt vùng Việt Nam.
Câu hỏi thường gặp
ARRAYFORMULA có làm bảng tính chậm không?
Thường nhanh hơn hàng nghìn công thức riêng lẻ làm cùng việc. Bảng chậm khi dải mở kéo đến cuối trang tính rất dài mà không có điều kiện bỏ dòng trống, hoặc khi bên trong lồng những hàm nặng như tra cứu trên bảng lớn. Giới hạn dải và bỏ dòng trống là đủ cho đa số bảng văn phòng.
Có sửa tay được một ô trong vùng kết quả của ARRAYFORMULA không?
Không. Gõ vào bất kỳ ô nào trong vùng kết quả sẽ làm công thức báo lỗi vì không tràn được. Nếu cần ghi đè cho trường hợp đặc biệt, hãy làm một cột nhập tay riêng rồi để công thức ưu tiên cột đó bằng IF khi cột nhập tay có giá trị.
Vì sao AND trong ARRAYFORMULA chỉ trả về một giá trị?
Vì AND gộp toàn bộ dải lại thành một kết quả đúng hoặc sai. Trong công thức mảng, thay AND bằng phép nhân hai điều kiện, ví dụ điều kiện một nhân điều kiện hai bằng 1, và thay OR bằng phép cộng các điều kiện lớn hơn 0.
Dùng ARRAYFORMULA hay kéo công thức cho người mới học?
Với bảng một người dùng, ít thay đổi, kéo công thức dễ hiểu hơn. Với bảng nhiều người cùng nhập, nhận dữ liệu từ biểu mẫu hay thêm dòng liên tục, ARRAYFORMULA an toàn hơn vì không phụ thuộc ai nhớ kéo công thức và không ai lỡ gõ đè mà không biết.
Excel có ARRAYFORMULA không?
Excel không có hàm cùng tên. Excel bản mới có mảng động, công thức tự tràn ra nhiều ô mà không cần bọc hàm nào; bản cũ dùng công thức mảng nhập bằng tổ hợp phím. Khi chuyển công thức giữa hai phần mềm, nhờ AI chuyển và nói rõ phiên bản Excel.