Bạn phải biết cách lọc theo tháng và năm trong bất kỳ ứng dụng Bảng tính nào. Nhiệm vụ này là một trong những nhiệm vụ phổ biến nhất. Trong Google Sheets, bạn có thể sử dụng chức năng Lọc hoặc Truy vấn cho việc này. Trong bài hướng dẫn Google Sheets này, bạn có thể tìm hiểu cách lọc theo tháng và năm trong Truy vấn.
Tại đây, tôi sẽ đi sâu vào một phần rất chi tiết của việc sử dụng Truy vấn với tiêu chí ngày tháng. Bởi vì tiêu chí ngày tháng là một trong những phần phức tạp trong Truy vấn hoặc ít nhất là tưởng như vậy đối với người dùng Google Sheets.
Hơn nữa, tôi ưu tiên Truy vấn hơn Lọc vì Truy vấn giữ lại tên cột trong kết quả đầu ra.
Giới thiệu về Lọc theo Tháng và Năm trong Truy vấn trong Google Sheets
Tiêu chí ngày tháng trong Truy vấn là một phần rất khó hiểu. Vấn đề là khi công thức TRUY VẤN tham chiếu đến một ngày trong một ô, ngày đó phải được chuyển đổi thành chuỗi (string) hoặc làm cho ngày đó trở thành một chuỗi trong một ô mới và tham chiếu đến ô đó trong công thức.
Ví dụ, nếu ô A1 chứa tiêu chí ngày, hãy chuyển đổi thành chuỗi trong ô B1 và tham chiếu đến B1 trong công thức. Bạn cũng có thể sử dụng chính ô A1 làm tiêu chí bằng cách chuyển đổi nó thành chuỗi trong công thức Truy vấn.
Bạn có thể tuân theo phương pháp Dài dòng (Long-winded Approach) hoặc phương pháp ngắn gọn để chuyển đổi một ngày thành chuỗi trong Truy vấn của Google Sheets. Ở đây, tôi sẽ tuân thủ phương pháp ngắn gọn.
Tôi sẽ giải thích phương pháp ngắn gọn của việc chuyển đổi ngày dưới đây. Nếu bạn muốn tìm hiểu về phương pháp Dài dòng, hãy làm theo liên kết dưới đây.
Một số mẹo để sử dụng Tiêu chí Ngày trong Truy vấn
Giả sử tôi muốn lọc cột A chứa các ngày. Công thức sẽ như sau.
Công thức Truy vấn 1:
=query(A1:A,"Select * where A=date'2018-1-26'",1)Công thức này để lọc cột A cho các hàng chứa ngày 26/01/2018. Bây giờ tôi muốn sử dụng một ô tham chiếu như tiêu chí ngày trong công thức Truy vấn này. Tôi có tiêu chí trong ô D1 là 26/01/2018. Xem công thức.
Công thức Truy vấn 2:
=query(A1:A,"Select * where A=date'"&text(D1,"YYYY-MM-DD")&"'",1)Từ hai ví dụ công thức Truy vấn trên để tiêu chí ngày, bạn có thể hiểu một điều. Ngày phải được chuyển đổi thành chuỗi văn bản trong Truy vấn để sử dụng như tiêu chí. Trong công thức thứ hai, tôi đã chuyển đổi ngày thành văn bản sử dụng phương pháp ngắn gọn.
Trong công thức đầu tiên, ngày được sử dụng chính nó như một chuỗi trong Truy vấn. Bạn có thể thấy ngày trong dấu ngoặc đơn.
Cách Lọc theo Tháng và Năm trong Truy vấn trong Google Sheets
Trước tiên, hãy cho tôi giải thích 4 mẹo dưới đây. Sau đó, tôi sẽ giải thích cho bạn cách lọc theo tháng và năm trong Truy vấn trong Google Sheets.
- Làm thế nào để lọc theo khoảng thời gian ngày trong Truy vấn?
- Làm thế nào để lọc theo tháng trong Truy vấn?
- Làm thế nào để lọc theo năm trong Truy vấn?
Trong bất kỳ ví dụ công thức Bảng tính nào, phần quan trọng nhất là có một số dữ liệu mẫu, ở đây không phải là ngoại lệ.
Tôi có hai trang với dữ liệu mẫu. Cho một số ví dụ đầu tiên, chỉ cần sử dụng Sheet1. Nhưng ở phần cuối cùng, tôi sẽ áp dụng bộ lọc Truy vấn theo tháng và năm trong danh sách thả xuống. Ở đó, tôi lấy dữ liệu từ các bảng khác nhau. Khi đó, tôi sẽ sử dụng Sheet2.
Sheet1:
Sheet2:
Dưới đây là một số ví dụ công thức có thể giúp bạn hiểu cách sử dụng ngày trong Truy vấn như là tiêu chí.
1. Làm thế nào để lọc theo khoảng thời gian ngày trong Truy vấn?
Hai công thức sau đây sẽ lọc dữ liệu mẫu trên Sheet1 dựa trên các ngày trong Cột 1.
Ở đây, tiêu chí ngày được dựa trên Công thức Truy vấn 1.
=query(A1:C,"Select * where A>=date'2018-1-1' and A<=date'2018-1-31'",1)Tiêu chí ngày dưới đây dựa trên Công thức Truy vấn 2.
=query(A1:C,"Select * where A>=date'"&text(D1,"YYYY-MM-DD")&"' and A<=date'"&text(E1,"YYYY-MM-DD")&"'",1)Trong công thức Truy vấn Google Sheets trên, tôi có tiêu chí ngày trong ô D1 và E1. Trong ô D1, tôi có một ngày bắt đầu và trong D2 là một ngày kết thúc. Công thức sẽ lọc dữ liệu mẫu trên đoạn này, cả hai ngày đều được bao gồm.
2. Làm thế nào để lọc theo tháng trong Truy vấn?
Theo Công thức Truy vấn 1.
=query(A1:C,"Select * where month(A)=month(date'2018-1-31')",1)Đây là phiên bản đơn giản. Ở đây, 0 đại diện cho tháng 1. Bạn có thể đặt 1 cho tháng 2, 2 cho tháng 3 và cứ tiếp tục như vậy.
=query(A1:C,"Select * where month(A)=0",1)Theo Công thức Truy vấn 2.
=query(A1:C,"Select * where month(A)=month(date'"&text(D1,"YYYY-MM-DD")&"')",1)3. Làm thế nào để lọc theo năm trong Truy vấn?
Ở đây, tôi bỏ qua các công thức. Chỉ cần thay đổi tháng trong các công thức trên thành năm. Đó là tất cả những gì bạn muốn làm để lọc theo tháng trong Truy vấn trong Google Sheets.
4. Công thức để lọc theo Tháng và Năm trong Truy vấn trong Google Sheets
Ngay cả khi bạn muốn lọc theo tháng, tôi đề nghị bạn luôn chọn lọc theo tháng và năm. Vì lọc theo tháng có thể đem lại kết quả không chính xác. Ví dụ: nếu bạn lọc cột A cho tháng tháng 1, nó có thể trả về các bản ghi chứa tháng tháng 1 bất kể năm.
Công thức Truy vấn để lọc theo Tháng và Năm trong Google Sheets
Công thức dựa trên Công thức Truy vấn 1.
=query(A1:C,"Select * where month(A)=month(date'2018-1-31') and year(A)=year(date'2018-1-31')",1)Đây là phiên bản đơn giản và phổ biến nhất.
=query(A1:C,"Select * where month(A)=0 and year(A)=2018",1)Công thức dựa trên Công thức Truy vấn 2. Tôi muốn gọi đó là công thức chính (master formula).
Công thức Truy vấn chính (Master Query Formula):
=query(A1:C,"Select * where month(A)=month(date'"&text(D1,"YYYY-MM-DD")&"') and Year(A)=Year(date'"&text(D1,"YYYY-MM-DD")&"')",1)Hy vọng bạn đã tìm hiểu được cách lọc dữ liệu theo tháng và năm trong Google Sheets. Bây giờ hãy xem một mẹo nâng cao và rất hữu ích sử dụng công thức trên.
Tôi đã chia sẻ các bản chụp màn hình của hai bảng dữ liệu mẫu ở trên. Cả hai bảng đều chứa dữ liệu tương tự.
Bây giờ trong bảng thứ ba, tôi muốn lọc dữ liệu theo tháng và năm. Nhưng tiêu chí được đưa ra dưới dạng các mục trong danh sách thả xuống. Tôi có một danh sách thả xuống như sau.
Các Mục dựa trên lựa chọn từ nhiều Bảng
Ví dụ dưới đây cũng cho thấy cách lọc theo tháng và năm trong Truy vấn trong Google Sheets nhưng theo cách khác.
Hãy xem ảnh này. Trong A2, B2 và C2, tôi có các danh sách thả xuống khác nhau để chọn Tên Bảng, Tháng và Năm.
Hy vọng bạn đã biết cách tạo một danh sách thả xuống trong Google Sheets. Đó là sử dụng xác nhận dữ liệu. Nếu chưa, hãy xem phần dưới đây.
Hãy vào ô A2. Tiếp theo, từ menu “Dữ liệu” chọn “Xác nhận dữ liệu”.
Các thiết lập trên sẽ tạo ra một danh sách thả xuống trong ô A2 chứa tên Bảng. Vì vậy, bạn có thể chọn Tên Bảng từ danh sách này.
Tương tự, tạo danh sách thả xuống trong B2 cho Tháng và C2 cho Năm.
Sau đó, nhập công thức sau vào ô A4.
=query(ArrayFormula({indirect(A2&"!A1:C1");indirect(A2&"!A2:C")}),"Select * where month(Col1)=month(date '"&text(date(C2,month(B2&1),1),"yyyy-mm-dd")&"') and Year(Col1)=year(date '"&text(date(C2,month(B2&1),1),"yyyy-mm-dd")&"')",1)Sau đó, hãy chọn Tên Bảng, Tháng và Năm. Công thức sẽ lọc dữ liệu của bạn theo Tháng và Năm từ bảng được chọn. Ngoài ra, công thức này rất linh hoạt vì nó giữ lại tiêu đề cột từ Bảng đã chọn. Nếu Sheet1 và Sheet2 có các tên cột khác nhau, công thức này có thể đúng trả về các tiêu đề đó.
Xin vui lòng tham khảo dữ liệu mẫu của tôi trong Sheet 1 và Sheet 2 (bạn có thể xem các bản chụp màn hình ở trên).
Đây là Sheet mẫu của tôi. Nó ở chế độ sao chép. Hãy thử nghiệm trên đó.
Sao chép Bảng mẫu Ví dụ Truy vấn.
Hy vọng bạn đã hiểu được công thức này. Tôi không đi vào chi tiết công thức này vì nó có thể gây nhầm lẫn cho bạn. Dù sao, đây là một số thông tin cơ bản.
Công thức này được thiết kế cho một bảng thứ ba. Phạm vi Truy vấn của chúng tôi dựa trên tên Bảng mà chúng tôi chọn từ danh sách thả xuống trong A2. Vì vậy, thay vì phạm vi A1:C, tôi đã sử dụng hàm Indirect để tham chiếu đến Bảng và phạm vi liên quan. Tôi đang nói về Indirect thứ hai trong công thức.
Ở đầu công thức, có một Indirect nữa. Đó là nhằm mục đích lấy hàng tiêu đề một cách linh hoạt. Vì hàng tiêu đề khác nhau trên Sheet1 và Sheet2.
Cuối cùng, Tháng nằm trong Định dạng văn bản trong danh sách thả xuống ở B2. Tôi đã chuyển đổi nó thành số trong Truy vấn.
Đó là tất cả. Hy vọng bạn đã thích bài hướng dẫn này về cách lọc theo tháng và năm trong Truy vấn trong Google Sheets. Cảm ơn vì đã đọc. Chúc bạn vui vẻ!
