Hướng dẫn lọc thời gian trong Google Sheets bằng hàm QUERY

Nếu bạn đã từng cố gắng lọc thời gian (ngày, tháng, năm) bằng hàm QUERY trong Google Sheets thì bạn tất nhiên biết nó phức tạp như thế nào.

Nói chung, vấn đề này là do thời gian trong Google Sheets được lưu trữ dưới dạng gọi là số sê-ri, nhưng hàm QUERY lại yêu cầu ngày dưới dạng [yyyy-mm-dd], nếu không thì không thể dùng bộ lọc để so sánh được.

Vấn đề chung

Ví dụ, ta có cú pháp như sau:

=QUERY(Data!$A$1:$H$136,"select C, B where B > '1/1/2000'",1)

Và đầu ra của truy vấn thì lại TRỐNG:

Thay vào đó, nếu mình xóa các dấu nháy đơn ('1/1/2000') xung quanh ngày được nhập và thử lại thì sẽ báo lỗi #VALUE! do công thức QUERY không thể thực hiện so sánh:('1/1/2000')#VALUE!QUERY

Cả hai định dạng “chuẩn” trên đều không hoạt động, vì ngày - tháng - năm KHÔNG ở định dạng [yyyy-mm-dd] của hàm QUERY.

Cú pháp lọc thời gian của hàm QUERY

Theo Ngôn ngữ truy vấn, ta cần có từ khóa "date" và đảm bảo rằng ngày luôn ở định dạng [yyyy-mm-dd] mới có thể sử dụng ngày làm bộ lọc trong mệnh đề WHERE của hàm QUERY.

Tạm thời, ta sẽ bỏ hàm QUERY sang một bên và hãy xét chuỗi "select ..." đây: date_column > date '2000-01-01'

Thách thức của ta chính là tạo công thức hàm TEXT để cho ra cú pháp đúng của hàm QUERY.

Đầu tiên, ta sẽ xử lý hàm TEXT với thời gian "1/1/2000" trước tiên:

Mình sẽ chuyển đổi nó sang định dạng số sê-ri với hàm DATEVALUE (): =DATEVALUE("1/1/2000")

Kết quả của công thức này là 1 số: 36526

Hàm TEXT() sẽ chuyển kết quả trên thành định dạng cần thiết cho công thức QUERY bằng cách chỉ định định dạng "yyyy-mm-dd":

=TEXT(DATEVALUE("1/1/2000"),"yyyy-mm-dd")

Kết quả sẽ cho ra: 2000-01-01

Tiếp theo, mình thêm các dấu nháy đơn và ngoặc kép xung quanh định dạng ngày mới, với cú pháp " ' ". Cuối cùng, ta chèn từ khóa "date" vào chuỗi truy vấn trên:

="select C, B where B > date '"&TEXT(DATEVALUE("1/1/2000"),"yyyy-mm-dd")&"'"

Với đầu ra ta cần là:

select C, B where B > date '2000-01-01'

Bây giờ, ta có thể đưa chuỗi đó vào đối số giữa của hàm QUERY.

Trong trường hợp này, mình đang sử dụng một bảng dữ liệu về Tàu con thoi từ Wikipedia, trong đó có một cột là ngày tàu được phóng.

Mình đã sử dụng hàm IMPORTHTML () để nhập bảng đó vào Google Sheets của mình, vào một tab có tên là Dữ liệu trong vùng A1: H136.

Công thức QUERY này sẽ trả về tất cả các ngày phóng của Tàu con thoi sau ngày 1 tháng 1 năm 2000:

=QUERY(Data!$A$1:$H$136,"select C, B where B > date '"&TEXT(DATEVALUE("1/1/2000"),"yyyy-mm-dd")&"'",1)

Đầu ra của công thức hiện đang trả về các dữ liệu đã lọc:

Tham chiếu đến 1 ngày trong ô

Công thức trên thực sự đơn giản hơn trong trường hợp này, vì ta sẽ không dùng hàm DATEVALUE. Giả sử ta có một ngày trong ô A1 mà ta muốn sử dụng trong bộ lọc của mình, thì công thức sẽ trở thành:

=QUERY(Data!$A$1:$H$136,"select C, B where B > date '"&TEXT(A1,"yyyy-mm-dd")&"'",1)

Ví dụ hiển thị bộ lọc giữa hai ngày

Tương tự, cũng rất đơn giản để mở rộng công thức của ta bằng cách thêm một mệnh đề của ngày thứ hai sau từ khóa AND:

=QUERY(Data!$A$1:$H$136,"select C, B where B > date '"&TEXT(A1,"yyyy-mm-dd")&"' and B <= date '"&TEXT(B1,"yyyy-mm-dd")&"'",1)

Sử dụng ngày hiện tại làm bộ lọc

Thay hàm TODAY () vào công thức trên:

=QUERY(Data!$A$1:$H$136,"select C, B where B > date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'",1)

Trên đây là những hướng dẫn về cách lọc thời gian (ngày - tháng - năm) bằng hàm QUERY trong Google Sheets. Ngoài ra, các bạn có thể tham khảo khóa học Google Sheets từ cơ bản tới nâng cao của Gitiho để nâng cao hiệu quả làm việc với Google Sheets nhé. Gitiho chúc bạn thành công!

CÓ THỂ BẠN CŨNG QUAN TÂM:

Cách tính giá trị tuyệt đối trong Google Sheets

Một số khái niệm và hướng dẫn cơ bản về Macro trong Google Sheets

Hướng dẫn cách tách văn bản thành cột trong Google Sheets