Bạn có từng nghĩ rằng việc tìm kiếm ngày trong khoảng hai ngày và trả về giá trị từ cột khác không dễ dàng. Tôi nghĩ là bạn có thể sử dụng hàm Vlookup, hàm tìm kiếm phổ biến từ lâu trên Google Sheets, với mục đích này. Nhưng không phải cách thông thường! Vậy làm cách nào để tìm kiếm ngày trong khoảng hai ngày trên Google Sheets và trả về giá trị từ cột khác?
Bạn có thể tìm hiểu mẹo này tại đây trong hướng dẫn mới của Google Sheets. Tôi không chỉ nói về điều này (xem Ảnh chụp màn hình số 1 bên dưới).
Tìm kiếm một ngày duy nhất trong khoảng ngày bắt đầu và kết thúc (Ảnh chụp màn hình số 1)
Ngày tìm kiếm nằm trong ô F2.
Tôi nói về điều này nữa (xem Ảnh chụp màn hình số 2 bên dưới).
Công thức mảng để tìm kiếm / Vlookup ngày trong khoảng ngày (Ảnh chụp màn hình số 2)
Các ngày tìm kiếm nằm trong khoảng F2:F.
Trong ảnh chụp màn hình đầu tiên, tôi muốn chỉ tìm kiếm ngày trong ô F2 trong các ngày bắt đầu trong cột A và các ngày kết thúc trong cột B và trả về giá trị tương ứng từ cột C. Nếu bạn biết cách sử dụng cơ bản của Vlookup, bạn có thể nghĩ rằng điều này không thể.
Giá trị tìm kiếm trong ô F2 là 04/01/2018. Nó nằm giữa các ngày 01/01/2018 và 15/01/2018 ở hàng 2. Vì vậy, tôi muốn một công thức có thể trả về giá trị tương ứng trong cột C2, tức là “Apple”.
Điều này khá dễ dàng trên Google Sheets vì chúng ta có nhiều lựa chọn khác nhau như Filter, Query hoặc các hàm IF. Nhưng không thể áp dụng trong trường hợp sau đây (bạn có thể tham khảo Ảnh chụp màn hình số 2 ở trên).
Các tập dữ liệu giống nhau trong cả hai ví dụ đầu tiên (Ảnh chụp màn hình số 1) và thứ hai (Ảnh chụp màn hình số 2). Nhưng trong ví dụ thứ hai, có nhiều giá trị tìm kiếm trong khoảng F2:F.
Bởi vậy, không thể sử dụng Truy vấn hoặc Lọc một mảng trong trường hợp này. Nếu bạn sử dụng, bạn sẽ phải sử dụng nhiều công thức cho mỗi giá trị tìm kiếm. Nhưng tôi có một công thức mảng Vlookup cho bạn để tìm kiếm ngày trong khoảng hai ngày trên Google Sheets.
Công thức mảng Vlookup để tìm kiếm ngày trong khoảng hai ngày trên Google Sheets
Tôi biết rằng công thức tôi sẽ cung cấp cho bạn có một chút phức tạp. Vì vậy, trước tiên, tôi sẽ trực tiếp cung cấp cho bạn công thức và hướng dẫn sử dụng.
Đối với người dùng Google Sheets nâng cao và những người quan tâm đến việc học cách phát triển công thức này, tôi đã cung cấp giải thích. Đầu tiên, hãy xem công thức và cách sử dụng công thức.
Công thức mảng để tìm kiếm ngày trong khoảng hai ngày
Công thức chính:
=ArrayFormula(IF(LEN(F2:F),(VLOOKUP(F2:F,{row(indirect("A"&A2):indirect("A"&B5)),transpose(split(join("",(rept(C2:C5&"|",(B2:B5-A2:A5)+1))),"|"))},2,FALSE)),))
Nếu bạn tạo một bảng mẫu theo Ảnh chụp màn hình số 2 trên đây, bạn nên dán công thức này vào ô G2. Nó sẽ tự động điền thông tin cần thiết. Đây là một công thức mảng Vlookup có thể tìm kiếm trong một khoảng ngày.
Ghi chú quan trọng:
-
Không được có bất kỳ ô trống nào trong phạm vi. Điều đó có nghĩa là có các ngày trong phạm vi cột A2:A5 (phạm vi ngày) và B2:B5 (phạm vi ngày).
-
Nhưng trong C2:C5, nếu có ô trống, bạn có thể đặt 0.
-
Các ngày phải được sắp xếp theo thứ tự thời gian (hãy tham khảo dữ liệu mẫu của tôi ở trên).
Bây giờ hãy xem hướng dẫn sử dụng phía dưới.
Cách sử dụng công thức mảng Vlookup để tìm kiếm ngày trong khoảng ngày
Đây là hướng dẫn sử dụng của công thức mảng Vlookup trên. Mặc dù Vlookup hỗ trợ các phạm vi vô hạn (ví dụ A2: A thay vì A2: A100), do sử dụng hàm INDIRECT, tôi không có lựa chọn khác ngoài việc giảm tính linh hoạt của công thức.
Vì vậy, tôi nghĩ hướng dẫn sử dụng dưới đây quan trọng đối với bạn. Hãy hiểu nó đúng để nhận được đầu ra không có lỗi từ công thức mảng Vlookup trên để tìm kiếm ngày trong khoảng ngày.
Điểm số 1: Trong phạm vi cột F2:F, bạn có thể nhập bất kỳ số lượng ngày tìm kiếm nào. Nếu các ngày được tìm thấy bất kỳ phạm vi ngày nào trong tập dữ liệu bên trái, công thức sẽ trả về giá trị từ cột C, nếu không, nó sẽ trả về lỗi N/A. Vui lòng xem Ảnh chụp màn hình số 3 bên dưới.
Điểm số 2: Như đã nói ở trên, bạn có thể sử dụng bất kỳ số hàng không giới hạn nào trong phạm vi tìm kiếm F2: F. Nhưng công thức chỉ bao gồm phạm vi dữ liệu A2:C5.
Đây là hạn chế mà tôi đã đề cập ở trên. Hạn chế này do việc sử dụng hàm Indirect.
Vì vậy, khi bạn thêm nhiều hàng hơn vào cột A:C, hãy thay đổi phạm vi công thức tương ứng. Ví dụ, nếu bạn đã thêm 5 hàng khác vào dữ liệu hiện tại của tôi, bạn nên thực hiện các thay đổi sau trong công thức.
Tham chiếu ô A5 nên được thay đổi thành A10, B5 thành B10 và C5 thành C10.
Dưới đây là phần giải thích công thức.
Cách tìm kiếm ngày trong khoảng hai ngày trên Google Sheets – Phần giải thích công thức
Nếu bạn là người mới bắt đầu sử dụng ứng dụng Bảng tính, bạn có thể bỏ qua phần này hoặc chỉ đọc để hiểu logic.
Tôi nghĩ rằng ‘so sánh’ công thức mảng Vlookup của tôi tìm kiếm ngày trong khoảng ngày với cú pháp Vlookup là cần thiết. Xem cú pháp trước.
VLOOKUP(search_key, range, index, [is_sorted])Bây giờ hãy xem lại công thức chính dưới đây để so sánh. Vui lòng chú ý đặc biệt đến việc tôi tô đậm màu.
Công thức số 1
Lưu ý: Các công thức bên dưới có thể không hoạt động trong bảng tính của bạn do định dạng. Đây là chỉ để giải thích mục đích.
=ArrayFormula(IF(LEN(F2:F),(VLOOKUP(F2:F,{ArrayFormula(row(indirect("A"&A2):indirect("A"&B5))),ArrayFormula(transpose(split(ArrayFormula(join("",(rept(C2:C5&"|",(B2:B5-A2:A5)+1)))),"|")))},2,FALSE)),))Từ việc làm nổi bật màu sắc này, bạn có thể hiểu một điều. “phạm vi” trong cú pháp là công thức dài bên trong Dấu ngoặc nhọn.
Cũng có một điều nữa. Ở đây chúng ta không thể sử dụng tập dữ liệu gốc (A2:C5) như tập dữ liệu trong Sheets Vlookup làm phạm vi. Vì vậy, đây là một cách làm kỳ quặc. Điều mà tôi đã làm ở đây là:
- Cập nhật: Vlookup có thể tìm kiếm ngày giữa hai ngày bằng cách sử dụng phạm vi A2:C5 như nó. Chúng ta có thể điều chỉnh được Vlookup theo cách đó. Tôi có một hướng dẫn mới (tìm liên kết ở cuối bài viết này) giải thích cách đó. Nó đơn giản hơn phương pháp được trình bày trong hướng dẫn này.
Trong dữ liệu mẫu hiện tại của tôi, phạm vi dữ liệu là A2:C5. Ngày bắt đầu (01/01/2018) nằm trong A2 và ngày kết thúc (28/02/2018) nằm trong B5. Vì vậy, với sự trợ giúp của một số công thức Google Sheets, tôi đã điền các ngày từ 01/01/2018 đến 28/02/2018 vào một cột duy nhất.
Dưới đây là công thức đó mà tôi mới trích xuất từ Công thức số 1 bên trên. Để bạn hiểu, tôi đã giữ đậm màu.
Công thức số 1A
=ArrayFormula(row(indirect("A"&A2):indirect("A"&B5)))Nếu bạn thử công thức này trong một bảng tính trong một cột trống, nó sẽ trả về một số giá trị ngày, không phải ngày. Vì vậy, đừng lầm lẫn. Điều này là đúng.
Nếu bạn muốn xem ngày, hãy áp dụng Format> Number> Date trên kết quả này. Tôi đã sử dụng hàm ROW của Google Sheets để điền các ngày từ 01/01/2018 đến 28/02/2018.
Theo thông tin bổ sung, bạn có thể sử dụng hàm ROW dưới dạng mảng để có được các số liên tục. Nó giống như:
=ArrayFormula(row(A1:A10))Công thức trên chỉ là hình thức sử dụng thông thường. Thay vì A1 và A10, tôi đã sử dụng các giá trị ngày trong A2 và B5 với sự trợ giúp của hàm Indirect để tự động điền các ngày từ hai giá trị ngày trong Google Sheets.
Xem phần trên cùng của ảnh chụp màn hình.
Vui lòng tham khảo dữ liệu mẫu của tôi (Ảnh chụp màn hình số 2) nơi bạn có thể thấy phạm vi ngày trong hàng số 2 là 01/01/2018 đến 15/01/2018 và sản phẩm là “Apple”. Bây giờ hãy kiểm tra Ảnh chụp màn hình số 4 trên đây.
Tôi đã mở rộng các ngày theo thứ tự liên tục. Vì vậy, tôi sẽ nhân chuỗi “Apple” 15 lần. Tương ứng, tôi cũng sẽ lặp lại các giá trị cột C khác.
Đây là phần thứ hai của công thức nhân bản tên sản phẩm.
Công thức số 1B
=ArrayFormula(transpose(split(ArrayFormula(join("",(rept(C2:C5&"|",(B2:B5-A2:A5)+1)))),"|")))Trong công thức này, tôi đã sử dụng hàm REPT để lặp lại từng giá trị cột C N lần. Ở đây, N lần có nghĩa là số mà bạn nhận được bằng cách trừ một ngày kết thúc với một ngày bắt đầu ở mỗi hàng.
Để tạo đầu ra này trong một cột duy nhất, tôi đã sử dụng các hàm JOIN, SPLIT và TRANSPOSE kết hợp với REPT.
Tôi đã kết hợp Công thức số 1A và 1B để tạo ra bộ dữ liệu hai cột bằng Các dấu ngoặc nhọn. Đây là phạm vi trong công thức Vlookup.
Bây giờ Vlookup có thể dễ dàng tìm và trả về ngày tìm kiếm trong phạm vi mới này.
Đó là tất cả về công thức mảng để tìm kiếm ngày trong khoảng hai ngày trên Google Sheets. Thưởng thức nào!
Cập nhật quan trọng:
Tôi đã có một giải pháp đơn giản hơn cho vấn đề trên ở đây – [Cách Vlookup một khoảng ngày trên Google Sheets [dữ liệu được sắp xếp / không được sắp xếp]]. Vui lòng đừng bỏ lỡ nó!
Vậy là mình đã tham khảo bài viết về Cách dùng công thức mảng để Tìm kiếm ngày trong khoảng hai ngày trên Google Sheets. Nếu bạn quan tâm đến các vấn đề liên quan đến Google Sheets, hãy ghé thăm trang web Crawlan.com để tìm hiểu thêm.
