Nếu bạn đang tìm hiểu cách sử dụng giá trị ngày trong Google Sheets Query, bài viết này sẽ giúp bạn. Giá trị ngày đề cập đến ngày trong định dạng số, một ngày có thể được nhận dạng theo cách này.
Ví dụ, giá trị ngày của ngày 03/11/2020 (Ngày 3 tháng 11 năm 2020) trong Google Sheets sẽ là 44138. Bạn có thể kiểm tra như sau:
- Nhập ngày trên vào một ô bất kỳ và định dạng ô đó thành số từ menu Format > Number > Number.
- Sử dụng công thức =DATEVALUE(“03/11/2020”) (ngày trong dấu nháy kép sẽ theo định dạng dd/mm/yyyy hoặc mm/dd/yyyy tùy thuộc vào cài đặt Sheets của bạn).
Đôi khi chúng ta có thể có một cột giá trị ngày trong dữ liệu do hai lý do sau đây:
- Dữ liệu được nhập.
- Kết quả của công thức.
Trong bài viết này, tôi sẽ chuyển đổi ngày thành số để giải thích cách sử dụng giá trị ngày trong Google Sheets Query.
Giới thiệu
Để thao tác với một cột ngày, chúng ta có thể sử dụng công thức Query như sau:
=query({C1:C},"Select Col1 where Col1=date '"&TEXT(E1,"yyyy-mm-dd")&"'",1)
Công thức trên sẽ lọc tất cả các ngày trong C1:C, khớp với ngày trong E1, tức là 01/11/2020.
Bây giờ, hãy định dạng các ngày trong C1:C thành số thứ tự ngày theo cách thủ công.
Chọn C2:C, sau đó đi đến menu Format > Number > Number.
Bây giờ bạn có thể thấy rằng công thức Query trong G1 chỉ trả về phần tiêu đề, không có giá trị khác.
Hãy xem cách sử dụng giá trị ngày trong Google Sheets Query sau một ví dụ khác về vấn đề này.
Lần này, tôi sử dụng hàm INT trong một cột DateTime.
Hãy hiểu rõ điều gì xảy ra khi chúng ta sử dụng hàm INT trong cột timestamp/DateTime.
Tôi đã sử dụng hàm INT để trích xuất ngày từ timestamp. Kết quả sẽ là các số thứ tự ngày.
Bây giờ hãy tìm hiểu cách sử dụng giá trị ngày trong Google Sheets Query.
1. Dữ liệu nhập có chứa giá trị ngày và cách sử dụng công thức Query
Giả sử dữ liệu được nhập có một cột số thứ tự ngày. Làm thế nào chúng ta sử dụng công thức Query trong cột giá trị ngày này?
Chúng ta có hai lựa chọn để lựa chọn và đây là những gì chúng ta có.
-
Sử dụng Số Thể hiện thay vì Thể hiện Ngày trong phần Tiêu chí.
-
Sử dụng menu Format để định dạng giá trị ngày.
Hãy bắt đầu với lựa chọn đơn giản nhất.
Lựa chọn 1: Số thể hiện thay vì Thể hiện Ngày trong phần Tiêu chí
Giả sử dữ liệu trong C2:C được nhập hoặc chuyển đổi thủ công thành số thứ tự ngày.
Công thức Query sau trong ô G1 sẽ lọc các giá trị ngày đó bằng cách sử dụng điều kiện ngày trong ô E1.
=ArrayFormula(query({C1:C},"Select Col1 where Col1="&E1&"",1))
Trong công thức Query trên, tôi đã sử dụng số thể hiện (Col1=”&E1&) thay vì thể hiện ngày (Col1=date ‘”&TEXT(E1,”yyyy-mm-dd”)&”‘”) trong phần tiêu chí.
Kết quả của công thức cũng sẽ ở định dạng số. Chỉ cần bao gồm hàm TO_DATE để định dạng nó thành dạng ngày.
=ArrayFormula(TO_DATE(query({C1:C},"Select Col1 where Col1="&E1&"",1)))
Lựa chọn 2: Sử dụng menu Format
Nếu bạn định dạng lại dữ liệu nhập trong C2:C thành dạng ngày từ menu Format, sau đó bạn có thể sử dụng công thức Query bình thường với thể hiện ngày trong phần điều kiện.
=query({C1:C},"Select Col1 where Col1=date '"&TEXT(E1,"yyyy-mm-dd")&"'",1)
2. Dữ liệu chứa giá trị ngày do công thức (Biểu thức là Dữ liệu Query)
Đây là một ví dụ khác về cách sử dụng giá trị ngày (số thứ tự ngày) trong Google Sheets Query.
Ở đây, hàm INT chuyển đổi ngày thành số thứ tự ngày. Vì vậy, chúng ta không thể sử dụng menu Format ở đây vì không có cột vật lý nào chứa số thứ tự ngày để định dạng.
Nếu chúng ta sử dụng các số nguyên trong Query, công thức sẽ như sau.
=ArrayFormula(query(int(C1:C),"Select Col1 where Col1="&E1&"",1))
Chúng ta có thể tuỳ ý bao gồm hàm TO_DATE như trước.
=ArrayFormula(TO_DATE(query(int(C1:C),"Select Col1 where Col1="&E1&"",1)))
Giá trị lỗi ở hàng đầu tiên của kết quả là do hàm INT cố gắng chuyển đổi hàng tiêu đề trong dữ liệu, một chuỗi, thành ngày.
Bằng cách sử dụng mệnh đề Label, chúng ta có thể dễ dàng loại bỏ lỗi đó (thay thế giá trị lỗi bằng tiêu đề yêu cầu).
=ArrayFormula(TO_DATE(query(int(C1:C),"Select Col1 where Col1="&E1&" label Col1'date'",1)))
Nếu chúng ta sử dụng thể hiện ngày, chúng ta nên di chuyển TO_DATE vào trong Query (bao bọc INT thay vì Query).
=ArrayFormula(query(to_date(int(C1:C)),"Select Col1 where Col1=date '"&text(E1,"yyyy-mm-dd")&"' label Col1'date'",1))
Đó là tất cả về cách sử dụng giá trị ngày (số thứ tự ngày) trong Google Sheets Query.
Cảm ơn đã đọc bài viết. Chúc bạn có những trải nghiệm thú vị!
