Ngày đối sánh trên Google Trang tính

Nếu bạn đã từng cố gắng lọc theo cột ngày trong chức năng Truy vấn trong Google Trang tính, thì bạn sẽ biết nó phức tạp như thế nào

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

Bài đăng này khám phá vấn đề này chi tiết hơn và chỉ cho bạn cách lọc theo ngày chính xác trong công thức Truy vấn của bạn

Vấn đề

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à trống

Thay vào đó, nếu chúng tôi xóa các trích dẫn đơn trong khoảng ngày đó và thử lại, thì chúng tôi sẽ nhận được lỗi vì Công thức truy vấn không thể thực hiện so sánh

Than ôi, chúng ta phải làm gì

Cả hai định dạng "chuẩn" này đều không hoạt động vì ngày không ở định dạng đúng cho hàm Truy vấn

Cú pháp đúng cho ngày tháng trong hàm Truy vấn

Theo , chúng tôi cần bao gồm từ khóa date và đảm bảo rằng ngày ở định dạng yyyy-mm-dd để sử dụng ngày làm bộ lọc trong mệnh đề WHERE của hàm Truy vấn của chúng tôi

Tạm gác chức năng Truy vấn sang một bên, hãy xem xét chuỗi "select..." đó

Cú pháp mới mà chúng tôi muốn sẽ giống như thế này

date_column > date '2000-01-01'

Thử thách của chúng tôi là tạo một công thức văn bản để tạo cú pháp này cho chúng tôi, bên trong hàm truy vấn của chúng tôi

Xử lý chức năng văn bản trước tiên, bắt đầu từ ngày bắt buộc của chúng tôi là 1/1/2000 và tiếp tục

Đầu tiên, chúng tôi chuyển đổi nó sang định dạng số sê-ri bằng trình bao bọc DATEVALUE()

=DATEVALUE("1/1/2000")

Đầu ra của công thức này là một số

36526

Sau đó, hàm TEXT() chuyển đổi nó thành định dạng được yêu cầu cho công thức Truy vấn bằng cách chỉ định định dạng của "yyyy-mm-dd"

date0

Đầu ra của công thức này là một ngày ở định dạng mong muốn

date1

Tiếp theo, chúng tôi thêm dấu nháy đơn xung quanh định dạng ngày mới, với cú pháp date2. Cuối cùng, chúng tôi chèn từ date vào chuỗi truy vấn, để đưa ra

date4

cung cấp hoặc đầu ra mong muốn

date5

Đó là thử thách cú pháp được thực hiện

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

Trong trường hợp này, tôi đang sử dụng một , chứa một cột ngày ra mắt

Tôi đã sử dụng hàm date6 để nhập bảng đó vào Google Trang tính của mình, vào một tab có tên là Dữ liệu trong phạm vi date7. Có một liên kết đến tập dữ liệu và bảng tính này ở cuối bài đăng

Công thức truy vấn 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

date8

Đầu ra của công thức của chúng tôi hiện đang trả về dữ liệu được lọc chính xác

Tham chiếu một ngày trong một ô

Công thức thực sự đơn giản hơn trong trường hợp này, vì chúng ta không cần 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ẽ trở thành

date9

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

Một lần nữa, việc mở rộng công thức của chúng ta tương đối đơn giản bằng cách thêm mệnh đề ngày thứ hai sau từ khóa yyyy-mm-dd0

Vlookup một phạm vi ngày trong Google Sheets. Điều đó có thể không? . Nó có thể và một nhiệm vụ khá đơn giản

Trên thực tế, tôi đã thảo luận về chủ đề này với tiêu đề Công thức mảng để tra cứu ngày giữa hai ngày trong Google Trang tính. Tôi trở lại với một chủ đề tương tự lần này vì tôi đã tìm thấy một giải pháp dễ dàng hơn

Thay vì viết lại bài đăng đó, tôi nghĩ sẽ viết hướng dẫn mới này vì cách tiếp cận hoàn toàn khác. Cách tiếp cận trước đó đối với Vlookup (chữ V là viết tắt của chiều dọc) phạm vi ngày được giới hạn trong phạm vi ngày được sắp xếp cũng theo thứ tự thời gian

Trong bài đăng này, tôi sẽ hướng dẫn bạn cách Vlookup phạm vi ngày được sắp xếp cũng như chưa sắp xếp trong Google Trang tính. Các công thức đơn giản hơn nhiều so với công thức trước đây của tôi

Trước khi đi đến các công thức mà Vlookup sắp xếp/chưa sắp xếp phạm vi ngày, đây là 1 ví dụ nhỏ. Để các bạn có thể hiểu Vlookup dãy ngày là gì

Có hai bảng trong ảnh chụp màn hình. Trong cả hai bảng, phạm vi ngày nằm trong Cột A và B

Ngày Vlookup (khóa tìm kiếm) là 24/02/2018 (ô F2) không có trong cả hai bảng. Nhưng nó có sẵn trong phạm vi ngày 16/02/18 đến 28/02/18. Ngày Vlookup rơi vào phạm vi ngày này. Vì vậy, tôi muốn một công thức tra cứu ngày trong ô F2 trong phạm vi ngày này và trả về giá trị từ Cột D

Bạn sẽ học cách Vlookup phạm vi ngày trong Google Sheets. Các chức năng mà tôi sẽ sử dụng ở đây là VLOOKUP và LOOKUP

Vlookup phạm vi ngày trong Google Sheets

Hàm VLOOKUP nổi tiếng mà bạn có thể sử dụng trong cả phạm vi dữ liệu được sắp xếp cũng như chưa sắp xếp. Ngược lại, hàm LOOKUP chỉ dùng cho dãy dữ liệu đã sắp xếp. Nhưng ở đây có một sự thay đổi

Để Vlookup (tra cứu theo chiều dọc) một ngày trong dải dữ liệu đã sắp xếp, tôi sẽ sử dụng hàm VLOOKUP. Đối với dữ liệu chưa được sắp xếp, chức năng tốt nhất ở đây là LOOKUP

Nhưng bạn đã nói với tôi trước khi chức năng LOOKUP chỉ dành cho dữ liệu được sắp xếp. Sau đó, làm thế nào nó có thể?

Hàm LOOKUP cũng có thể được sử dụng trong phạm vi dữ liệu chưa được sắp xếp với một giải pháp thay thế – Cách sử dụng hàm LOOKUP trong một mảng chưa được sắp xếp trong Google Trang tính. Xem các công thức dưới đây để hiểu thêm

Ngày Vlookup trong Phạm vi Ngày được Sắp xếp

Nếu bạn muốn Vlookup một phạm vi ngày trong Google Trang tính, ý tôi là tìm kiếm ngày trong phạm vi ngày bắt đầu và ngày kết thúc, chỉ cần sử dụng hàm VLOOKUP nếu bạn có thể tuân thủ những điều sau

1. Sắp xếp cột ngày

2. Đặt tham số cuối cùng (được_sắp xếp) trong VLOOKUP thành TRUE hoặc 1

Đây là công thức để Vlookup phạm vi ngày trong Google Sheets. Một lần nữa, tôi đang bảo bạn sử dụng công thức này nếu dữ liệu nguồn của bạn được sắp xếp

=VLOOKUP(F2,A2:D,4,1)

VLOOKUP sẽ chỉ tìm kiếm cột đầu tiên, tôi. e. cột A đây. Sau đó, làm thế nào nó trả về kết quả chính xác vì tôi có ngày bắt đầu ở cột A và ngày kết thúc ở cột B?

Khi bạn đặt tham số cuối cùng trong Vlookup thành TRUE, 1 hoặc bỏ qua, công thức sẽ tìm kiếm kết quả khớp gần nhất của khóa tìm kiếm, nghĩa là nhỏ hơn hoặc bằng khóa tìm kiếm, trong cột A

Trong ví dụ trên, key tìm kiếm của tôi là ngày 24/02/18. Xem ảnh chụp màn hình. Bạn có thể hiểu ngày nhỏ hơn hoặc bằng từ khóa tìm kiếm là 16/02/2018

Giả sử khóa tìm kiếm của bạn là 15/02/18. Công thức sẽ tìm kiếm ngày này trong cột A. Ngày không ở cột A mà ở ô B4 (cột B). Vì vậy, trận đấu gần nhất là 01/02/18 trong A4 và công thức sẽ trả về thành công giá trị chính xác 7860. 00

Hơn nữa, Vlookup thân thiện với tài nguyên hơn trong phạm vi dữ liệu được sắp xếp. Nếu bạn muốn sử dụng nhiều phím tìm kiếm, ý tôi là nhiều ngày hơn trong F2. F, sử dụng phiên bản mảng của công thức trên

=ArrayFormula(IFERROR(VLOOKUP(F2:F,A2:D,4,1)))

Bạn có thể thích. Cách sử dụng Vlookup để trả về kết quả mảng trong Google Sheets

Ngày Vlookup trong một phạm vi ngày chưa sắp xếp

Không phải lúc nào chúng ta cũng có thể tranh luận về một phạm vi dữ liệu được sắp xếp. Có thể có lý do mà chúng tôi không được phép sắp xếp tập dữ liệu. Trong tình huống đó, bạn cũng nên biết cách Vlookup Phạm vi ngày trong Google Sheets

Bạn phải luôn chuẩn bị cho các tình huống khác nhau mà bạn có thể gặp phải khi thao tác dữ liệu. Xem cách Vlookup phạm vi ngày chưa sắp xếp trong Google Sheets bằng LOOKUP

Công thức

=ArrayFormula(LOOKUP(2,1/($A$2:$A<=F2)/($B$2:$B>=F2),$D$2:$D))

Nếu bạn muốn tra cứu ngày bổ sung trong phạm vi ngày, chỉ cần kéo núm điều khiển điền trong ô G1 xuống dưới. Lần này không có ARRAYFORMULA

Cập nhật. Bây giờ tôi cũng có một giải pháp công thức mảng cho vấn đề này. Xem điều đó dưới tiêu đề Vlookup + Sort to Lookup Date Range bên dưới

Như bạn có thể thấy trong công thức, thay vì tìm kiếm ngày 24/02/18 trong ngày bắt đầu và ngày kết thúc (phạm vi ngày) trong cột A và B, công thức sẽ tìm kiếm số 2 trong một phạm vi ảo và trả về giá trị từ cột

Tôi biết bạn không chắc lắm về phạm vi ảo được sử dụng trong công thức trên

Phạm vi ảo trong LOOKUP

1/($A$2:$A<=F2)/($B$2:$B>=F2)

Thay vì nêu chi tiết chức năng này ở đây, tôi sẽ hướng bạn đến hướng dẫn trước đó của tôi ở đây –  Cách tìm giá trị khớp cuối cùng trong Google Trang tính. Trong đó, tôi đã hướng dẫn chi tiết cách sử dụng phạm vi ảo trong hàm LOOKUP

Vlookup + Sắp xếp để tra cứu phạm vi ngày trong Google Trang tính

Sau này, khi thử nghiệm với các sự cố Vlookup khác trong Google Trang tính, tôi đã tìm thấy một giải pháp dễ dàng hơn. Chúng ta có thể sử dụng chính Vlookup để tra cứu ngày trong phạm vi ngày chưa được sắp xếp trong Google Trang tính

Nếu bạn sử dụng phương pháp Vlookup này để Tra cứu phạm vi ngày chưa được sắp xếp trong Google Trang tính, thì có một lợi thế. Nó có thể trả về một kết quả mảng

Google Trang tính có thể nhận dạng ngày không?

Bạn có thể sử dụng Vlookup với ngày Google Trang tính không?

Bạn có thể sử dụng chức năng nào để so sánh ngày?