Cách sử dụng hàm QUERY lọc ngày tháng trong Google Sheets

Nếu bạn đã từng cố gắng lọc ngày tháng bằng cách sử dụng hàm truy vấn trong Google Sheets, bạn có lẽ đã biết thao tác tưởng chừng như đơn giản này lại hóa ra vô cùng phức tạp.

Tóm lại, sự cố xảy ra vì ngày tháng trong Google sheets thực sự được lưu trữ dưới dạng số sê-ri, nhưng hàm QUERY yêu cầu ngày tháng phải được định dạng dưới dạng chuỗi ký tự yyyy-mm-dd, nếu không hàm này không thể thực hiện bộ lọc so sánh.

Trong bài viết này, chúng ta sẽ khám phá chi tiết hơn về cách lọc ngày một cách chính xác bằng công thức QUERY trong Google Sheets.

Lọc ngày tháng với hàm QUERY

Ví dụ, bạn có thể thử cú pháp sau:

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

Thật không may, đầu ra của một truy vấn như vậy là một ô trống.

Tuy nhiên, nếu chúng ta xóa các dấu ngoặc kép trong khoảng thời gian và thử lại, chúng tôi nhận được lỗi #VALUE! vì công thức truy vấn không thể thực hiện việc so sánh:

Cả hai định dạng “chuẩn” này đều không hoạt động, bởi vì ngày tháng không ở định dạng chính xác cho cho hàm QUERY

Cú pháp đúng cho ngày tháng trong hàm QUERY

Theo tài liệu Ngôn ngữ truy vấn, chúng tôi cần bao gồm từ khóa date và đảm bảo rằng ngày tháng ở định dạng yyyy-mm-dd để 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 để hàm Query sang một bên, chúng ta hãy xem xét lại chuỗi "select..." này.

Cú pháp mới mà chúng ta muốn sẽ giống như sau:

date_column > date ‘2000-01-01’

Thách thức của chúng ta ở đây là tạo một công thức text để tạo cú pháp đúng, bên trong hàm QUERY.

Đầu tiên, chúng ta sẽ xử lý hàm văn bản, bắt đầu từ ngày yêu cầu của là 1/1/2000.

1. Chuyển đổi ngày tháng sang định dạng số sê-ri với hàm DATEVALUE()
   • =DATEVALUE("1/1/2000")
   • Đầu ra của công thức này là một số: 36526
2. Sử dụng hàm TEXT() để chuyên con số đó thành định dạng cần thiết cho công thức QUYERY bằng cách chỉ định định dạng "yyyy-mm-dd":
   • =TEXT(DATEVALUE("1/1/2000"),"yyyy-mm-dd").
   • Đầu ra của công thức này là một ngày ở định dạng mong muốn: 2000-01-01
3. Thêm các dấu ngoặc kép xung quanh định dạng ngày mới, với cú pháp "'".
   • Chèn từ date vào chuỗi truy vấn
   • ="select C, B where B > date '"&TEXT(DATEVALUE("1/1/2000"),"yyyy-mm-dd")&"'"
   • Đầu ra của công thức là: select C, B where B > date '2000-01-01'

Như vậy, chúng ta đã tạo được một cú pháp hoàn hảo.

Bây giờ chúng ta có thể đưa chuỗi đó vào đối số giữa của hàm Query như bình thường và nó sẽ thực hiện thủ thuật cho chúng ta.

Trong ví dụ này, chúng ta sử dụng bảng dữ liệu sứ mệnh Tàu con thoi từ Wikipedia, bảng này chứa một cột ngày phóng. Nếu bạn chưa biết cách trích xuât dữ liệu từ một bảng có sẵn trên internet về trang tính của mình, vui lòng xem lại bài viết của chúng tôi về cách sử dụng hàm importHTML để chuyển một bảng từ trang web HTML sang Google Sheets.

Chúng ta sẽ sử dụng IMPORTHTML() để nhập bảng đó vào Google sheets của mình, vào một tab có tên là Data trong phạm vi A1:H136. Bạn có thể tải file dữ liệu mà chúng tôi đã đính kèm ở liên kết đính kèm ngay bên dưới dài viết này để tiện theo dõi.

Công thức Query này trả về tất cả các nhiệm vụ 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ề dữ liệu chính xác, đã được lọc:

Cách tham chiếu ngày tháng trong một ô

Công thức để tham chiếu ngày tháng trong một ô thật sự đơn giản, vì chúng ta không cần sử dụng hàm DATEVALUE. Giả sử chúng ta có một ngày trong ô A1 mà chúng ta muốn sử dụng trong bộ lọc của mình, thì công thức sẽ như sau

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

Cách hiển thị bộ lọc giữa hai ngày

Chúng ta có thể dễ dàng mở rộng công thức bằng cách thêm mệnh đề 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)
 

Cách sử dụng ngày hôm nay làm bộ lọc

Bạn chỉ cần thêm hàm TODAY() vào công thức của chúng ta:

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

Hy vọng, qua bài viết này bạn đã biết cách sử dụng hàm QUERY để lọc ngày tháng trong Google Sheets. Ngoài ra, để không bỏ lỡ các mẹo và thủ thuật tin học văn phòng hữu ích khác. Hãy theo dõi chúng tôi ngay hôm nay.