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
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"
date
0
Đầu ra của công thức này là một ngày ở định dạng mong muốn
date
1
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 date
2. Cuối cùng, chúng tôi chèn từ date
vào chuỗi truy vấn, để đưa ra
date
4
cung cấp hoặc đầu ra mong muốn
date
5
Đó 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 date
6 để 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 date
7. 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
date
8
Đầ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
date
9
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-dd
0
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],$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]
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