Mẹo Google Sheets · 14 phút đọc
ARRAYFORMULA cho một công thức chạy trên cả cột: gõ =ARRAYFORMULA(IF(D2:D=""; ""; D2:D*E2:E)) một lần ở F2 là mọi dòng có số lượng đều ra Thành tiền, kể cả dòng bạn thêm vào ngày mai. Không phải kéo công thức, không sợ ai sửa lệch một ô giữa cột. Bài này đi qua cú pháp, cách bỏ dòng trống, kết hợp với VLOOKUP, XLOOKUP, SUMIF, những hàm không chạy trong ARRAYFORMULA (SUM, AND, OR) và cách thay, lỗi #REF! do mảng bị chặn, so sánh với mảng động của Excel 365 và MAP, BYROW, LAMBDA.
Bình thường bạn viết =D2*E2 ở F2 rồi kéo xuống, mỗi ô một công thức riêng. Cách này có ba nhược điểm: dòng mới thêm vào thì phải nhớ kéo tiếp, ai đó gõ đè một ô thì cột lệch mà không ai hay, và file có hàng nghìn công thức giống nhau nặng hơn cần thiết.
ARRAYFORMULA bảo Google Sheets tính cả một dải ô thay vì một ô. Bạn viết D2:D*E2:E thay cho D2*E2, Google nhân từng cặp ô cùng dòng và trải kết quả xuống các ô bên dưới. Chỉ ô đầu tiên có công thức; các ô còn lại chỉ hiển thị kết quả.
Các ví dụ trong bài dùng trang Đơn hàng: A = Mã đơn, B = Ngày, C = Mã hàng, D = Số lượng, E = Đơn giá, F = Thành tiền, và trang Sản phẩm: A = Mã hàng, B = Tên hàng, C = Đơn giá. Công thức viết theo vùng Việt Nam nên đối số cách nhau bằng dấu chấm phẩy.
=ARRAYFORMULA(công_thức_mảng)| A: Mã đơn | C: Mã hàng | D: Số lượng | E: Đơn giá | |
|---|---|---|---|---|
| 2 | HD01 | SP01 | 3 | 120.000 |
| 3 | HD02 | SP02 | 2 | 85.000 |
| 4 | HD03 | SP01 | 5 | 120.000 |
| 5 | HD04 | SP03 | 1 | 250.000 |
=ARRAYFORMULA(D2:D5*E2:E5)Thử lại: F2 = 3 × 120.000 = 360.000, F3 = 2 × 85.000 = 170.000, F4 = 5 × 120.000 = 600.000, F5 = 1 × 250.000 = 250.000. Bấm vào F3, thanh công thức hiện công thức của F2 bằng chữ xám: F3 chỉ là phần kết quả trải xuống, không sửa riêng được.
Mẹo gõ nhanh: đang sửa công thức, bấm Ctrl+Shift+Enter (trên Mac là Cmd+Shift+Enter) thì Google tự thêm ARRAYFORMULA( vào đầu. Hai dải khác số dòng, ví dụ D2:D5*E2:E9, thì công thức báo lỗi vì không ghép được từng cặp.
Hàm nào đã trả về nhiều ô thì không cần ARRAYFORMULA
FILTER, QUERY, SORT, UNIQUE, IMPORTRANGE tự trải kết quả ra nhiều ô. Bọc ARRAYFORMULA bên ngoài chúng không sai nhưng thừa. Cách dùng QUERY có ở bài hàm QUERY trong Google Sheets.
Để dòng mới tự tính, dải ô phải để mở: D2:D nghĩa là từ D2 tới hết trang. Nhưng viết =ARRAYFORMULA(D2:D*E2:E) thì mọi dòng trống bên dưới đều hiện số 0 kéo dài tới cuối trang. Bọc IF để dòng chưa có số lượng thì để trống:
=ARRAYFORMULA(IF(D2:D=""; ""; D2:D*E2:E))Thử lại: bạn thêm dòng 6: HD05, SP02, số lượng 4, đơn giá 85.000. Không cần làm gì, F6 hiện ngay 340.000. Dòng 7 trở xuống chưa có số lượng nên trống.
Muốn công thức nằm luôn ở ô tiêu đề (để không ai vô tình xoá F2), ghép tiêu đề với kết quả bằng ngoặc nhọn. Dấu ; trong ngoặc nhọn nghĩa là xếp xuống dòng dưới:
={"Thành tiền"; ARRAYFORMULA(IF(D2:D=""; ""; D2:D*E2:E))}Thêm điều kiện vào IF như bình thường. Ví dụ đơn từ 5 cái trở lên được giảm 10%:
=ARRAYFORMULA(IF(D2:D=""; ""; IF(D2:D>=5; D2:D*E2:E*0,9; D2:D*E2:E)))Thử lại: HD03 mua 5 cái: 5 × 120.000 × 0,9 = 540.000. HD01 mua 3 cái không đủ mốc: vẫn 360.000. Hai kiểu cột tự tính hay dùng khác:
| Việc | Công thức (gõ ở dòng 2) | Kết quả ví dụ |
|---|---|---|
| Số thứ tự tự đánh | =ARRAYFORMULA(IF(A2:A=""; ""; ROW(A2:A)-1)) | Dòng 2 ra 1, dòng 6 ra 5 |
| Tháng của đơn | =ARRAYFORMULA(IF(B2:B=""; ""; TEXT(B2:B; "mm/yyyy"))) | Ngày 02/10/2026 ra 10/2026 |
| Nối mã và tên | =ARRAYFORMULA(IF(A2:A=""; ""; A2:A&" - "&C2:C)) | HD01 - SP01 |
Cột B phải là ngày thật thì TEXT mới ra đúng tháng; nếu ra nguyên văn chuỗi bạn gõ, xem cách sửa lỗi ngày tháng trong Google Sheets.
Một số hàm vốn dùng để gộp nhiều ô thành một số: SUM, AVERAGE, MAX, MIN, COUNT, AND, OR, TEXTJOIN. Đặt chúng trong ARRAYFORMULA, chúng vẫn gộp cả cột thành một giá trị duy nhất chứ không tính riêng từng dòng. Công thức không báo lỗi, chỉ ra kết quả sai.
Mẹo: trong Google Sheets, TRUE tính là 1, FALSE là 0. Nhân hai điều kiện thì chỉ ra 1 khi cả hai cùng đúng (giống AND); cộng hai điều kiện thì ra lớn hơn 0 khi ít nhất một cái đúng (giống OR).
=ARRAYFORMULA(IF(C2:C=""; ""; IF((C2:C="SP01")*(D2:D>=3); "Ưu tiên"; "")))Thử lại: HD01 (SP01, 3 cái): 1 × 1 = 1, ra Ưu tiên. HD02 (SP02, 2 cái): 0 × 0 = 0, để trống. HD03 (SP01, 5 cái): Ưu tiên. HD04 (SP03, 1 cái): trống. Viết bằng AND(C2:C="SP01"; D2:D>=3) thì AND xét cả cột cùng lúc, gặp một FALSE là ra FALSE, nên mọi dòng đều trống.
=ARRAYFORMULA(IF(C2:C=""; ""; IF((C2:C="SP01")+(C2:C="SP03")>0; "Có"; "")))Thử lại: HD01, HD03 (SP01) và HD04 (SP03) ra Có; HD02 (SP02) trống.
| Muốn tính theo từng dòng | Đừng viết | Viết thế này |
|---|---|---|
| Cả hai điều kiện đúng | AND(B2:B>0; C2:C>0) | (B2:B>0)*(C2:C>0) |
| Một trong hai đúng | OR(B2:B>0; C2:C>0) | (B2:B>0)+(C2:C>0)>0 |
| Cộng 3 cột trên cùng dòng | SUM(B2:D) | B2:B+C2:C+D2:D hoặc BYROW |
| Số lớn hơn trong 2 cột | MAX(B2:B; C2:C) | IF(B2:B>C2:C; B2:B; C2:C) |
Ví dụ cộng doanh số 3 tháng cho từng nhân viên: B, C, D là tháng 7, 8, 9; Lan có 10, 12, 8 (triệu). =ARRAYFORMULA(IF(A2:A=""; ""; B2:B+C2:C+D2:D)) ra 30 ở dòng của Lan. Ô trống được tính là 0 nên nhân viên mới chưa có số tháng 7 vẫn cộng được. Viết SUM(B2:D) trong ARRAYFORMULA thì mọi dòng đều ra tổng của cả bảng.
Tra giá cho cả cột đơn hàng thay vì từng dòng: đưa cả cột mã hàng vào làm giá trị cần tìm. Trang Sản phẩm có SP01 Áo thun 120.000, SP02 Mũ lưỡi trai 85.000, SP03 Giày vải 250.000.
=ARRAYFORMULA(IF(C2:C=""; ""; IFNA(VLOOKUP(C2:C; 'Sản phẩm'!$A$2:$C$50; 3; FALSE); "Không có mã")))Thử lại: C2 = SP01 ra 120.000, C3 = SP02 ra 85.000, C5 = SP03 ra 250.000. Gõ nhầm SP09 thì ra "Không có mã". Đổi giá trong trang Sản phẩm là cả cột E đổi theo.
Lấy hai cột cùng lúc (tên hàng và đơn giá) bằng cách đưa danh sách chỉ mục vào ngoặc nhọn. Vùng Việt Nam ghép cột trong ngoặc nhọn bằng dấu gạch chéo ngược:
=ARRAYFORMULA(IF(C2:C=""; ""; VLOOKUP(C2:C; 'Sản phẩm'!$A$2:$C$50; {2\3}; FALSE)))Thử lại: dòng HD02 (SP02) ra Mũ lưỡi trai | 85.000. File để vùng Hoa Kỳ thì viết {2,3}.
XLOOKUP cũng nhận cả cột mã khi nằm trong ARRAYFORMULA, miễn là dải kết quả chỉ có một cột:
=ARRAYFORMULA(IF(C2:C=""; ""; XLOOKUP(C2:C; 'Sản phẩm'!$A$2:$A$50; 'Sản phẩm'!$C$2:$C$50; "Không có mã")))Thử lại: ra cùng kết quả với VLOOKUP ở trên: 120.000, 85.000, 120.000, 250.000. Nếu cho XLOOKUP trả về nhiều cột với nhiều mã cùng lúc, nó chỉ trả về một cột; khi đó dùng VLOOKUP với {2\3} như trên. Các cách dùng khác của XLOOKUP có ở bài hàm XLOOKUP trong Google Sheets.
Khác với SUM, các hàm có điều kiện như SUMIF, COUNTIF, SUMIFS, COUNTIFS nhận cả một cột làm điều kiện và trả về một kết quả cho mỗi điều kiện. Ví dụ cột D trang Sản phẩm là "Đã bán", cộng số lượng từ trang Đơn hàng theo từng mã:
=ARRAYFORMULA(IF(A2:A=""; ""; SUMIF('Đơn hàng'!C2:C; A2:A; 'Đơn hàng'!D2:D)))Thử lại: đơn hàng gồm HD01 SP01 × 3, HD02 SP02 × 2, HD03 SP01 × 5, HD04 SP03 × 1, HD05 SP02 × 4. SP01 = 3 + 5 = 8, SP02 = 2 + 4 = 6, SP03 = 1.
Đánh dấu mã đơn bị gõ trùng bằng COUNTIF, đếm mỗi mã xuất hiện mấy lần trong cả cột:
=ARRAYFORMULA(IF(A2:A=""; ""; IF(COUNTIF(A2:A; A2:A)>1; "Trùng"; "")))Thử lại: cột A là HD01, HD02, HD03, HD02. HD02 đếm được 2 lần nên dòng 3 và dòng 5 hiện Trùng; HD01, HD03 trống. Cách lọc và xoá dòng trùng có ở bài lọc dữ liệu trùng lặp trong Google Sheets. Điều kiện nhiều cột (theo người và theo tháng) viết SUMIFS, COUNTIFS như bài hàm SUMIFS và COUNTIFS, rồi thay ô điều kiện bằng cả cột.
ARRAYFORMULA cần các ô bên dưới (và bên phải, nếu kết quả nhiều cột) hoàn toàn trống để trải kết quả ra. Chỉ cần một ô trong vùng đó có dữ liệu, kể cả một dấu cách, ô công thức sẽ báo #REF! và cả cột mất kết quả.
Di chuột vào ô báo lỗi, Google cho biết kết quả mảng không được mở rộng vì sẽ ghi đè dữ liệu ở ô nào. Bấm vào ô đó, nhấn Delete, cột kết quả hiện lại ngay.
| Dấu hiệu | Nguyên nhân | Cách sửa |
|---|---|---|
| #REF!, báo sẽ ghi đè dữ liệu ở F5 | Có người gõ tay vào vùng kết quả | Xoá ô đó, rồi khoá cả cột kết quả |
| #REF! sau khi dán dữ liệu vào | Dán cả cột (gồm cột công thức) từ chỗ khác | Chỉ dán vào các cột nhập liệu |
| Ô trông trống mà vẫn #REF! | Ô có dấu cách hoặc ký tự không nhìn thấy | Chọn vùng, nhấn Delete |
| Báo lỗi kích thước không khớp | Hai dải khác số dòng: D2:D5 với E2:E9 | Viết cùng dòng bắt đầu, cùng dòng kết thúc |
| Ra một giá trị cho mọi dòng | Dùng SUM, AND, OR bên trong | Thay bằng phép cộng, phép nhân |
| File chậm dần | Nhiều cột mở tới cuối trang hàng chục nghìn dòng trống | Xoá bớt dòng trống thừa cuối trang |
Cách chắc nhất để không bị gõ đè là khoá cả cột kết quả (không chỉ ô đầu), cho phép mọi người sửa các cột nhập liệu. Các bước có ở bài cách khoá ô công thức trong Google Sheets.
Excel không có hàm ARRAYFORMULA. Từ Excel 2021 và Microsoft 365, Excel có mảng động: gõ =D2:D6*E2:E6 là kết quả tự tràn xuống, không cần bọc gì. Ý tưởng giống nhau, khác ở vài chỗ:
| Google Sheets | Excel 2021 / 365 | Excel 2019 trở về trước | |
|---|---|---|---|
| Nhân cả cột | =ARRAYFORMULA(D2:D6*E2:E6) | =D2:D6*E2:E6 (tự tràn) | Chọn vùng, gõ, Ctrl+Shift+Enter |
| Lỗi khi vùng kết quả bị chặn | #REF! | #SPILL! | Không tràn, phải chọn đủ vùng |
| Dòng mới tự tính | Dải mở D2:D + IF bỏ dòng trống | Định dạng thành Bảng (Table): cột tính toán tự chép cho dòng mới | Bảng (Table) |
| MAP, BYROW, LAMBDA | Có | Có (Microsoft 365) | Không |
Trang trợ giúp của Google có ghi chú công thức mảng không xuất ra được, nên khi tải file về .xlsx hãy mở thử bằng Excel và kiểm lại các cột dùng ARRAYFORMULA trước khi gửi đi. Những khác biệt khác giữa hai bên có ở bài Google Sheets hay Excel.
Google Sheets có LAMBDA và các hàm đi kèm từ năm 2022. Chúng chạy một công thức cho từng dòng hoặc từng ô, nên bên trong dùng được cả SUM, AND, OR mà không bị gộp cả cột.
=BYROW(B2:D; LAMBDA(dong; IF(COUNT(dong)=0; ""; SUM(dong))))Thử lại: dòng của Lan 10, 12, 8 ra 30; dòng trống ra trống. Kết quả giống B2:B+C2:C+D2:D ở trên, nhưng khi có 12 cột tháng thì viết BYROW gọn hơn nhiều.
=MAP(C2:C; D2:D; LAMBDA(ma; sl; IF(ma=""; ""; IF(AND(ma="SP01"; sl>=3); "Ưu tiên"; ""))))Thử lại: ra đúng như cách nhân ở phần trên: HD01 và HD03 là Ưu tiên. MAP, BYROW chậm hơn phép tính thường trên bảng lớn, nên việc nào làm được bằng ARRAYFORMULA với phép cộng, nhân thì cứ dùng ARRAYFORMULA.
Vì dải ô mở như D2:D chạy tới hết trang, dòng trống cũng được tính. Bọc IF: =ARRAYFORMULA(IF(D2:D=""; ""; D2:D*E2:E)) để dòng chưa có dữ liệu thì để trống.
Thường là có ô trong vùng kết quả đã có dữ liệu. Di chuột vào ô lỗi để xem ô nào chặn, xoá ô đó bằng phím Delete. Sau đó khoá cả cột kết quả để không ai gõ đè.
Được nhưng sai ý: SUM, AND, OR gộp cả cột thành một giá trị. Muốn tính theo từng dòng, thay AND bằng phép nhân (A2:A>0)*(B2:B>0), OR bằng phép cộng, SUM bằng B2:B+C2:C hoặc BYROW.
Một ARRAYFORMULA thường nhẹ hơn hàng nghìn công thức kéo xuống. File chậm khi có nhiều cột mở tới cuối trang mà trang có hàng chục nghìn dòng trống; xoá bớt dòng thừa ở cuối trang là đỡ.
Ô đó nằm trong vùng kết quả của mảng. Gõ vào là chặn mảng, ô đầu báo #REF!. Muốn nhập tay giá trị khác cho một dòng, thêm điều kiện vào IF hoặc dùng một cột riêng.
Google Sheets hay Excel: so chi phí, làm chung, dùng trên điện thoại, khi mất mạng, giới hạn dữ liệu, Apps Script với VBA và các công thức khác nhau.
Cách dùng hàm FILTER trong Google Sheets: lọc 1 hay nhiều điều kiện AND/OR, theo ngày, chữ chứa, kết hợp SORT, UNIQUE và sửa lỗi không tìm thấy kết quả.
Cách dùng hàm XLOOKUP trong Google Sheets: gõ mã khách ra tên, SĐT, hạng; tra sang trái, giá trị khi không thấy, khớp gần đúng, sửa lỗi #N/A.
Cách tạo biểu đồ trong Google Sheets: chọn loại cột, đường, tròn, kết hợp, cột chồng; trục phụ, đổi màu, nhãn, biểu đồ tự cập nhật, SPARKLINE và lỗi hay gặp.
Xem thêm mẹo và video hướng dẫn trên kênh YouTube HiTaVa, hoặc đọc các bài khác trong chủ đề Mẹo Google Sheets.
Cần hỏi thêm?Zalo 0935077673 (Hải Vân)
