Mẹo Google Sheets · 17 phút đọc
Hàm FILTER trả về những dòng thoả điều kiện bạn đặt ra, tự tràn thành bảng mới và tự cập nhật khi dữ liệu gốc đổi: =FILTER(B2:F9; E2:E9="Đã chốt") lấy ra mọi khách đã chốt đơn. Nhiều điều kiện cùng đúng thì nhân (*), chỉ cần một điều kiện đúng thì cộng (+). Bài đi qua cú pháp, lọc theo ngày, theo chữ chứa bằng SEARCH và REGEXMATCH, ghép với SORT, UNIQUE, CHOOSECOLS, cách sửa lỗi không tìm thấy kết quả, và FILTER khác bộ lọc thường, khác Excel 365 ở đâu.
FILTER nhận một vùng dữ liệu và một hay nhiều điều kiện, rồi trả về đúng những dòng thoả điều kiện, giữ nguyên các cột. Kết quả nằm ở chỗ khác với bảng gốc, nên bảng gốc không bị ẩn dòng hay xáo trộn, và ai sửa dữ liệu gốc thì bảng lọc tự đổi theo.
Nên dùng FILTER khi bạn cần danh sách dòng: khách đã chốt, việc quá hạn của một người, đơn trong tuần này. Cần một con số (tổng, số lượng) thì SUMIFS, COUNTIFS ngắn hơn. Cần gộp nhóm, cộng theo từng người, xoay cột thì dùng hàm QUERY. Cần tìm một giá trị theo mã thì dùng XLOOKUP.
Mọi ví dụ trong bài dùng chung bảng Khách dưới đây (A1:F9, dòng 1 là tiêu đề). 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.
| A: Ngày | B: Khách | C: Nguồn | D: Nhân viên | E: Trạng thái | F: Giá trị | |
|---|---|---|---|---|---|---|
| 2 | 01/10/2026 | Nguyễn Hải Yến | Lan | Đã chốt | 3.500.000 | |
| 3 | 03/10/2026 | Trần Quốc Bảo | Zalo | Tuấn | Đang tư vấn | 2.000.000 |
| 4 | 06/10/2026 | Lê Thu Hà | Website | Lan | Đã chốt | 5.200.000 |
| 5 | 09/10/2026 | Phạm Minh Khoa | Hương | Mất | 1.800.000 | |
| 6 | 14/10/2026 | Võ Ngọc Anh | Zalo | Lan | Đang tư vấn | 4.000.000 |
| 7 | 17/10/2026 | Đặng Văn Phúc | Website | Tuấn | Đã chốt | 2.700.000 |
| 8 | 21/10/2026 | Bùi Thanh Tâm | Hương | Đã chốt | 6.000.000 | |
| 9 | 27/09/2026 | Hoàng Gia Huy | Zalo | Tuấn | Mất | 1.500.000 |
=FILTER(dải_ô; điều_kiện1; [điều_kiện2; …])| Đối số | Nghĩa | Ví dụ |
|---|---|---|
| dải_ô | Vùng cần lọc, lấy bao nhiêu cột tuỳ bạn | B2:F9 |
| điều_kiện1 | Một cột TRUE/FALSE cao bằng đúng dải_ô, thường là một phép so sánh | E2:E9="Đã chốt" |
| điều_kiện2… | Tuỳ chọn. Thêm bao nhiêu cũng được, dòng phải thoả tất cả | D2:D9="Lan" |
Lấy Khách, Nguồn, Nhân viên, Trạng thái, Giá trị của các khách đã chốt:
=FILTER(B2:F9; E2:E9="Đã chốt")Thử lại: cột E bằng "Đã chốt" ở dòng 2, 4, 7, 8. Kết quả 4 dòng: Nguyễn Hải Yến · Facebook · Lan · 3.500.000; Lê Thu Hà · Website · Lan · 5.200.000; Đặng Văn Phúc · Website · Tuấn · 2.700.000; Bùi Thanh Tâm · Facebook · Hương · 6.000.000. FILTER không trả dòng tiêu đề; muốn có tiêu đề thì gõ tay ở dòng trên ô công thức.
Điều kiện có thể là bất kỳ phép so sánh nào: F2:F9>=3000000 (số, không gõ dấu chấm ngăn nghìn), D2:D9<>"Lan" (khác), C2:C9=H3 (bằng giá trị ở ô H3). Cột hộp đánh dấu thì viết thẳng G2:G9 vì ô đã tích có giá trị TRUE.
Lọc từ trang khác thì ghi tên trang trước cả dải_ô và điều kiện: =FILTER('Khách'!B2:F; 'Khách'!E2:E="Đã chốt"). Dải mở như B2:F tự lấy dòng mới thêm vào.
Không gõ gì vào vùng kết quả
Kết quả FILTER chiếm chỗ bên dưới ô công thức. Có ô nào trong vùng đó đã có chữ thì công thức báo #REF! (kết quả mảng không mở rộng được vì sẽ ghi đè dữ liệu). Xoá ô cản đường hoặc dời công thức sang chỗ trống. Muốn sửa kết quả thì sửa ở bảng gốc, không sửa trên bảng lọc.
Muốn dòng phải thoả tất cả điều kiện, Google Sheets cho hai cách cho cùng một kết quả. Cách 1: viết mỗi điều kiện thành một đối số riêng.
=FILTER(B2:F9; D2:D9="Lan"; E2:E9="Đã chốt")Cách 2: nhân các điều kiện trong một đối số. TRUE được tính là 1, FALSE là 0, nên tích chỉ bằng 1 khi mọi điều kiện đều đúng.
=FILTER(B2:F9; (D2:D9="Lan")*(E2:E9="Đã chốt"))Thử lại: Lan có ở dòng 2, 4, 6; trong đó dòng 2 và 4 đã chốt, dòng 6 (Võ Ngọc Anh) đang tư vấn nên bị loại. Kết quả: Nguyễn Hải Yến 3.500.000 và Lê Thu Hà 5.200.000. Cộng hai giá trị được 8.700.000 — đây là doanh số đã chốt của Lan.
Cách nhân dài hơn một chút nhưng có hai lợi thế: dùng chung được với Excel 365 (xem phần cuối), và ghép được với điều kiện OR như phần dưới.
Lọc theo khoảng số là hai điều kiện AND trên cùng một cột. Khách có giá trị từ 2 đến dưới 4 triệu:
=FILTER(B2:F9; F2:F9>=2000000; F2:F9<4000000)Thử lại: giá trị trong khoảng [2.000.000; 4.000.000) là 3.500.000 (Hải Yến), 2.000.000 (Quốc Bảo), 2.700.000 (Văn Phúc). Ngọc Anh đúng 4.000.000 không lấy vì điều kiện là nhỏ hơn hẳn.
FILTER không có đối số "hoặc". Muốn giữ dòng thoả ít nhất một điều kiện, bạn cộng các điều kiện lại: tổng lớn hơn 0 là giữ.
=FILTER(B2:F9; (C2:C9="Zalo")+(C2:C9="Website"))Thử lại: Zalo ở dòng 3, 6, 9; Website ở dòng 4, 7. Kết quả 5 dòng theo thứ tự trong bảng: Trần Quốc Bảo, Lê Thu Hà, Võ Ngọc Anh, Đặng Văn Phúc, Hoàng Gia Huy. Ba khách Facebook bị loại.
Ghép AND với OR: khách của Lan hoặc Tuấn, và giá trị từ 3 triệu trở lên. Phần OR bọc trong ngoặc trước, rồi mới nhân với phần AND:
=FILTER(B2:F9; ((D2:D9="Lan")+(D2:D9="Tuấn"))*(F2:F9>=3000000))Thử lại: Lan có 3.500.000, 5.200.000, 4.000.000 — cả ba đều ≥ 3 triệu. Tuấn có 2.000.000, 2.700.000, 1.500.000 — không dòng nào đạt. Kết quả: Nguyễn Hải Yến, Lê Thu Hà, Võ Ngọc Anh. Quên ngoặc ngoài thành (D2:D9="Lan")+(D2:D9="Tuấn")*(F2:F9>=3000000) thì phép nhân làm trước, mọi khách của Lan đều lọt bất kể giá trị.
Một dòng thoả cả hai điều kiện OR thì tổng bằng 2 — vẫn được giữ, không bị lặp. Lọc theo danh sách dài (5–6 nguồn) thì dùng REGEXMATCH ở phần dưới cho gọn.
Ô chọn trống thì lấy hết: H3 là ô thả xuống chọn nhân viên (cách tạo danh sách thả xuống). Cộng thêm điều kiện H3="" để khi ô trống, mọi dòng đều đúng:
=FILTER(B2:F9; (D2:D9=H3)+(H3=""))Thử lại: H3 = Tuấn: ra dòng 3, 7, 9 — Trần Quốc Bảo, Đặng Văn Phúc, Hoàng Gia Huy. Xoá H3: H3="" đúng với mọi dòng nên ra đủ 8 khách.
Ngày trong Google Sheets là số, nên so sánh như số. Gõ ngày vào công thức bằng hàm DATE(năm; tháng; ngày) để không phụ thuộc kiểu ngày tháng của file.
=FILTER(A2:F9; A2:A9>=DATE(2026;10;1); A2:A9<DATE(2026;11;1))Thử lại: chỉ dòng 9 (27/09/2026, Hoàng Gia Huy) bị loại, còn 7 khách tháng 10.
Để người xem tự chọn khoảng ngày, gõ Từ ngày vào H1 và Đến ngày vào H2:
=FILTER(A2:F9; A2:A9>=H1; A2:A9<=H2)Thử lại: H1 = 01/10/2026, H2 = 15/10/2026: lấy các ngày 01, 03, 06, 09, 14/10 — Hải Yến, Quốc Bảo, Thu Hà, Minh Khoa, Ngọc Anh. Tổng giá trị 3,5 + 2 + 5,2 + 1,8 + 4 = 16,5 triệu. Nếu cột A có cả giờ, đổi điều kiện cuối thành A2:A9<H2+1.
Khách 7 ngày gần nhất, tính cả hôm nay:
=FILTER(B2:F9; A2:A9>=TODAY()-6)Thử lại: hôm nay 24/10/2026, TODAY()-6 = 18/10. Chỉ dòng 8 (21/10, Bùi Thanh Tâm) thoả; dòng 7 ngày 17/10 vừa ra ngoài khoảng.
MONTH() trên ô trống ra 12
Lọc bằng MONTH(A2:A)=12 với dải mở sẽ kéo theo hàng trăm dòng trống, vì ô trống được coi là số 0, tức ngày 30/12/1899, tháng 12. Thêm điều kiện A2:A<>"" hoặc dùng khoảng DATE như trên. Ngày dán từ phần mềm khác mà nằm dạng chữ thì FILTER không so được — cách nhận ra và sửa có ở bài sửa lỗi ngày tháng trong Google Sheets.
Điều kiện B2:B9="Anh" chỉ khớp ô có đúng chữ "Anh". Muốn lọc ô chứa một đoạn chữ, dùng SEARCH: hàm trả về vị trí tìm thấy (một số) hoặc lỗi nếu không thấy, nên bọc ISNUMBER để thành TRUE/FALSE.
=FILTER(B2:F9; ISNUMBER(SEARCH("an"; B2:B9)))Thử lại: "an" có trong Võ Ngọc Anh và Bùi Thanh Tâm. Đặng Văn Phúc và Hoàng Gia Huy không khớp vì "ăn", "oàn" là chữ có dấu khác với "an". Tìm theo tên tiếng Việt thì phải gõ đúng dấu.
Muốn đổi từ khoá mà không sửa công thức, thay "an" bằng ô H4. Lưu ý: H4 trống thì SEARCH("") khớp mọi dòng, nên bảng ra đủ 8 khách — thường đó lại đúng là điều bạn muốn.
REGEXMATCH (chỉ có trên Google Sheets) dùng biểu thức chính quy, tiện khi có nhiều từ khoá hoặc cần "bắt đầu bằng", "kết thúc bằng":
| Điều kiện trong FILTER | Nghĩa | Kết quả trên bảng mẫu |
|---|---|---|
| REGEXMATCH(C2:C9; "Zalo|Website") | Chứa Zalo hoặc Website — thay cho phép cộng OR | 5 khách như ví dụ OR ở trên |
| REGEXMATCH(B2:B9; "^(Nguyễn|Trần)") | Bắt đầu bằng Nguyễn hoặc Trần | Nguyễn Hải Yến, Trần Quốc Bảo |
| REGEXMATCH(C2:C9; "(?i)zalo") | (?i) để không phân biệt hoa thường | Quốc Bảo, Ngọc Anh, Gia Huy |
| NOT(REGEXMATCH(E2:E9; "Mất")) | Không chứa "Mất" | 6 khách còn lại |
REGEXMATCH phân biệt hoa thường và chỉ làm việc với chữ: cột toàn số (mã đơn 1001, 1002…) phải đổi sang chữ trước, ví dụ REGEXMATCH(TO_TEXT(G2:G9); "^10"), nếu không công thức báo lỗi.
FILTER trả về một mảng, nên bạn đưa thẳng nó vào hàm khác như một dải ô.
SORT — sắp xếp kết quả. Đối số thứ hai là số thứ tự cột trong kết quả, không phải chữ cột của trang tính. Dải B:F có Giá trị là cột thứ 5. FALSE là giảm dần.
=SORT(FILTER(B2:F9; E2:E9="Đã chốt"); 5; FALSE)Thử lại: Bùi Thanh Tâm 6.000.000; Lê Thu Hà 5.200.000; Nguyễn Hải Yến 3.500.000; Đặng Văn Phúc 2.700.000.
UNIQUE — bỏ trùng. Những nhân viên đã có ít nhất một khách chốt:
=SORT(UNIQUE(FILTER(D2:D9; E2:E9="Đã chốt")))Thử lại: cột D của 4 dòng đã chốt là Lan, Lan, Tuấn, Hương. UNIQUE còn Lan, Tuấn, Hương; SORT xếp thành Hương, Lan, Tuấn. Bọc thêm COUNTA bên ngoài thì ra số 3. Các cách khác để tìm và xoá dòng trùng có ở bài lọc dữ liệu trùng lặp trong Google Sheets.
CHOOSECOLS — chỉ lấy vài cột, đổi thứ tự cột. Khi các cột cần lấy nằm xa nhau, lọc cả bảng rồi chọn cột:
=CHOOSECOLS(FILTER(A2:F9; E2:E9="Đã chốt"); 2; 6)Thử lại: ra 2 cột, 4 dòng: Nguyễn Hải Yến 3.500.000; Lê Thu Hà 5.200.000; Đặng Văn Phúc 2.700.000; Bùi Thanh Tâm 6.000.000. Viết 6; 2 thì Giá trị đứng trước Khách. Không muốn dùng CHOOSECOLS thì ghép mảng: =FILTER({B2:B9\F2:F9}; E2:E9="Đã chốt") — trong vùng Việt Nam, dấu \ ghép cột cạnh nhau.
Top 3 khách đã chốt có giá trị cao nhất: sắp xếp rồi cắt 3 dòng đầu bằng ARRAY_CONSTRAIN.
=ARRAY_CONSTRAIN(SORT(CHOOSECOLS(FILTER(A2:F9; E2:E9="Đã chốt"); 2; 6); 2; FALSE); 3; 2)Thử lại: Bùi Thanh Tâm 6.000.000; Lê Thu Hà 5.200.000; Nguyễn Hải Yến 3.500.000.
Bọc SUM, AVERAGE, COUNTA quanh FILTER để ra một con số: =SUM(FILTER(F2:F9; E2:E9="Đã chốt")) = 17.400.000. Con số này SUMIFS cũng tính được, nhưng FILTER cho phép điều kiện mà SUMIFS không làm được, như ISNUMBER(SEARCH(…)) hay MONTH(…).
1. #N/A khi không có dòng nào thoả. Ví dụ =FILTER(B2:F9; D2:D9="Minh") báo #N/A, di chuột vào ô thấy thông báo không tìm thấy kết quả phù hợp trong FILTER. Đây không phải công thức sai mà là danh sách rỗng. Bọc IFNA để hiện chữ dễ hiểu:
=IFNA(FILTER(B2:F9; D2:D9="Minh"); "Chưa có khách nào")Nhiều người dùng IFERROR cho việc này, cũng chạy. Khác biệt: IFERROR giấu mọi lỗi, kể cả lỗi do bạn viết sai dải ô ở dưới, nên bảng cứ hiện "Chưa có khách nào" trong khi thật ra công thức hỏng. IFNA chỉ bắt đúng trường hợp không tìm thấy. Khi cần một con số, bọc trong SUM: =SUM(IFNA(FILTER(F2:F9; D2:D9="Minh"); 0)) ra 0 thay vì lỗi.
2. Kích thước dải ô không khớp. =FILTER(B2:F9; E2:E10="Đã chốt") báo #VALUE! vì dải_ô cao 8 dòng mà điều kiện cao 9 dòng. Mọi điều kiện phải bắt đầu và kết thúc cùng dòng với dải_ô. Lỗi này hay xuất hiện khi bạn kéo dài dải_ô nhưng quên sửa điều kiện.
3. Lọc "khác" ra hàng trăm dòng trống. =FILTER(B2:F; E2:E<>"Mất") với dải mở: mọi dòng trống bên dưới cũng "khác Mất" nên bị lấy vào. Thêm điều kiện bỏ dòng trống:
=FILTER(B2:F; E2:E<>"Mất"; B2:B<>"")Thử lại: ra đúng 6 khách không có trạng thái Mất, không kèm dòng trống.
4. Không khớp vì khoảng trắng hoặc kiểu dữ liệu. "Đã chốt " có dấu cách thừa ở cuối không bằng "Đã chốt". Số gõ dạng chữ (sát trái ô) không thoả điều kiện F2:F9>=3000000. Dùng danh sách thả xuống cho cột trạng thái và xem bài sửa lỗi SUM bằng 0 để đổi số dạng chữ.
| Dấu hiệu | Nguyên nhân | Cách sửa |
|---|---|---|
| #N/A, không tìm thấy kết quả | Không dòng nào thoả | IFNA(…; "Không có") |
| #VALUE!, kích thước không khớp | Điều kiện và dải_ô khác số dòng | Cho cùng dòng đầu, dòng cuối |
| #REF!, không mở rộng được | Vùng kết quả có sẵn dữ liệu | Xoá ô cản hoặc dời công thức |
| Ra thêm nhiều dòng trống | Điều kiện <> trên dải mở | Thêm điều kiện cột<>"" |
| Thiếu dòng tưởng phải có | Khoảng trắng thừa, số/ngày dạng chữ, sai dấu tiếng Việt | TRIM, đổi kiểu dữ liệu, dùng ô thả xuống |
Bộ lọc thường (nút phễu trên thanh công cụ) ẩn dòng ngay trong bảng gốc. Nó tiện để xem nhanh, nhưng khi nhiều người cùng mở file, bộ lọc của bạn đổi cả màn hình người khác. Chế độ xem bộ lọc chỉ ảnh hưởng tới bạn. Hàm FILTER không đụng vào bảng gốc mà tạo bản lọc ở chỗ khác, dùng được làm nguồn cho biểu đồ, báo cáo, trang tính khác.
| Bộ lọc thường | Chế độ xem bộ lọc | Hàm FILTER | |
|---|---|---|---|
| Bảng gốc | Bị ẩn dòng | Bị ẩn dòng, chỉ với bạn | Giữ nguyên |
| Người khác thấy | Thấy cùng bộ lọc | Không bị ảnh hưởng | Thấy bảng kết quả |
| Tự cập nhật | Phải lọc lại khi có dòng mới thoả | Phải lọc lại | Có |
| Điều kiện phức tạp (OR nhiều cột, chữ chứa, theo ô chọn) | Hạn chế | Hạn chế | Viết được |
| Sửa trên kết quả | Được, sửa thẳng bảng gốc | Được | Không, sửa ở bảng gốc |
Excel 365 cũng có hàm FILTER nhưng cú pháp khác: =FILTER(array; include; [if_empty]). Excel chỉ có một đối số điều kiện; đối số thứ ba là giá trị hiện khi không có kết quả. Khi không có dòng nào và bỏ trống if_empty, Excel báo #CALC! chứ không phải #N/A. FILTER có trên Excel cho Microsoft 365, Excel 2021 và 2024; Excel 2019 trở về trước không có.
| Google Sheets | Excel 365 / 2021 / 2024 | |
|---|---|---|
| Nhiều điều kiện AND | Viết thành nhiều đối số, hoặc nhân | Bắt buộc nhân: (đk1)*(đk2) |
| Điều kiện OR | Cộng | Cộng |
| Không có kết quả | #N/A — bọc IFNA | #CALC! — dùng đối số if_empty |
| Lọc chữ chứa | SEARCH, REGEXMATCH | SEARCH |
| Ghép SORT, UNIQUE, CHOOSECOLS | Có | Có |
File dùng cả hai bên thì viết kiểu nhân
Công thức Google =FILTER(B2:F9; D2:D9="Lan"; E2:E9="Đã chốt") khi mở bằng Excel sẽ bị hiểu đối số thứ ba là if_empty, cho kết quả sai. Viết =FILTER(B2:F9; (D2:D9="Lan")*(E2:E9="Đã chốt")) thì chạy giống nhau ở cả hai. REGEXMATCH và ARRAY_CONSTRAIN chỉ có trên Google Sheets. Những khác biệt khác có ở bài Google Sheets hay Excel.
FILTER tự tính trên cả cột nên không cần bọc ARRAYFORMULA. Lọc dữ liệu nằm ở file khác thì dùng IMPORTRANGE làm dải_ô, hoặc kéo dữ liệu về một trang rồi FILTER trên trang đó.
Một ứng dụng hay gặp ở văn phòng là lọc việc đã xong trong tuần để viết báo cáo, ghép với TEXTJOIN thành đoạn chữ dán thẳng vào Zalo — cách làm có ở bài cách viết báo cáo công việc hằng tuần.
Cùng đúng (AND): viết thêm điều kiện thành đối số mới, hoặc nhân (đk1)*(đk2). Một trong các điều kiện đúng (OR): cộng (đk1)+(đk2). Ghép cả hai thì bọc phần OR trong ngoặc rồi nhân với phần AND.
Vì không có dòng nào thoả điều kiện. Kiểm tra lại khoảng trắng thừa, dấu tiếng Việt, số hoặc ngày dạng chữ. Nếu danh sách rỗng là bình thường, bọc IFNA(FILTER(…); "Không có") để hiện chữ thay cho lỗi.
Không, FILTER chỉ trả về dòng dữ liệu. Gõ tiêu đề ở dòng ngay trên ô công thức, hoặc ghép bằng mảng: ={B1:F1; FILTER(B2:F9; E2:E9="Đã chốt")}.
So sánh trực tiếp vì ngày là số: A2:A9>=DATE(2026;10;1) và A2:A9<DATE(2026;11;1), hoặc A2:A9>=H1 và A2:A9<=H2 với H1, H2 là ô ngày. Dùng MONTH() trên dải mở thì thêm điều kiện bỏ ô trống.
Cần danh sách dòng, điều kiện linh hoạt như chữ chứa hay ô chọn tuỳ ý: FILTER dễ viết hơn. Cần gộp nhóm, cộng theo người, đổi tiêu đề, xoay cột: QUERY. File phải mở bằng Excel 365 thì FILTER viết kiểu nhân chạy được, QUERY thì không.
Không. Gõ vào vùng kết quả sẽ làm công thức báo #REF!. Muốn sửa thì sửa ở bảng gốc, bảng lọc tự đổi theo. Muốn có bản tĩnh để chỉnh thì chép rồi dán chỉ giá trị sang chỗ khác.
Mẫu khác liên quan: Bảng theo dõi công việc PRO.
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 tạo danh sách thả xuống (dropdown) trong Google Sheets: nhập tay, lấy từ dải ô, chip màu, thả xuống phụ thuộc 2 cấp, chặn nhập sai, sửa lỗi không hợp lệ.
Cách dùng hàm ARRAYFORMULA trong Google Sheets: một công thức cho cả cột, bỏ dòng trống bằng IF, kết hợp VLOOKUP, XLOOKUP, SUMIF, thay AND/OR, sửa lỗi #REF!.
Cách dùng hàm SUMIFS và COUNTIFS trong Google Sheets: cú pháp, nhiều điều kiện, lọc theo tháng bằng DATE, ký tự đại diện, sửa lỗi ra 0, so với SUMIF.
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)
