Tôi thích sử dụng công thức mảng (array formulas) trong Google Sheets để hoàn thành các công việc. Tôi có một công thức mảng để tìm các ngày liên tiếp bị thiếu trong một danh sách trên Google Sheets.
Ví dụ, giả sử bạn có một danh sách các ngày trong cột A trên Google Sheets. Bạn muốn tìm các ngày bị thiếu trong danh sách này, dù các ngày đó có được sắp xếp theo trật tự liên tiếp hay không.
Ở ví dụ dưới đây, cột A chứa danh sách và cột C chứa các ngày bị thiếu, được trả về bằng công thức mảng trong ô C2.
Ví dụ về cách tìm các ngày liên tiếp bị thiếu trong danh sách trên Google Sheets:
Cách tìm các ngày liên tiếp bị thiếu trên Google Sheets
Cú pháp:
LET(tên1, giá_trị_biểu_thức1, [tên2, ...], [giá_trị_biểu_thức2, ...], biểu_thức_công_thức)Đây là công thức mảng mà tôi đã sử dụng trong ô C2 để liệt kê các ngày liên tiếp bị thiếu.
Công thức chính:
=LET(dải, A2:A, chuỗi, SEQUENCE(DAYS(MAX(dải),MIN(dải))+1,1,MIN(dải),1), FILTER(chuỗi,IFNA(XMATCH(chuỗi,dải))=""))Trong đó:
dải: là dải ô chứa danh sách các ngày.chuỗi: là danh sách tất cả các ngày có thể giữa ngày đầu tiên và ngày cuối cùng trongdải.biểu_thức_công_thức: là công thức lọc (trích xuất) các ngày bị thiếu từ danh sách các ngày có thể.
Bạn chỉ cần chỉ định dải chứa danh sách (ở đây là A2:A). Công thức sẽ xử lý phần còn lại.
Đây là công thức mảng, nên nó sẽ bao phủ tất cả các ngày trong danh sách trong cột A, sau đó đưa ra các ngày liên tiếp bị thiếu trong cột C.
Bạn chỉ cần nhập công thức mảng vào ô C2. Bạn không cần sao chép và dán công thức vào các ô bên dưới. Tuy nhiên, hãy đảm bảo rằng phạm vi ô C2:C trống trước khi nhập công thức.
Giải thích công thức
Dải là A2:A, nên không cần giải thích thêm. Hãy xem chi tiết về phần chuỗi và biểu_thức_công_thức ở dưới đây.
Tôi đã sử dụng tám hàm cơ bản của Google Sheets trong công thức trên. Đó là:
- DAYS, MIN và MAX
- SEQUENCE
- LET
- XMATCH, IFNA và FILTER
Tôi sẽ cố gắng giải thích vai trò của từng hàm một cách rõ ràng trong việc tạo các ngày liên tiếp bị thiếu trên Google Sheets.
Tạo ra các ngày liên tiếp dựa trên ngày bắt đầu và ngày kết thúc trong danh sách: phần chuỗi
Chúng ta có thể sử dụng công thức SEQUENCE sau (chuỗi) để tạo ra một danh sách các ngày liên tiếp từ 01/09/2019 (ngày nhỏ nhất trong dải) đến 15/09/2019 (ngày lớn nhất trong dải).
SEQUENCE(DAYS(MAX(dải),MIN(dải))+1,1,MIN(dải),1)Dạng mở rộng:
=SEQUENCE(DAYS(MAX(A2:A),MIN(A2:A))+1,1,MIN(A2:A),1)Cú pháp:
SEQUENCE(số_hàng, [số_cột], [giá_trị_bắt_đầu], [số_bước])Giải thích:
- Đối số số_hàng chỉ định số giá trị cần trả về. Số này bằng số ngày giữa ngày nhỏ nhất và ngày lớn nhất trong A2:A. Công thức
DAYS(MAX(A2:A),MIN(A2:A))+1sẽ trả về số đó. - Đối số số_cột là tùy chọn. Chúng ta đặt giá trị này là 1 vì chúng ta muốn các ngày liên tiếp nằm trong một cột.
- Đối số giá_trị_bắt_đầu chỉ định giá trị khởi đầu. Trong trường hợp này, chúng ta muốn bắt đầu từ ngày nhỏ nhất trong dải A2:A.
- Đối số số_bước là tùy chọn. Chúng ta đặt giá trị này là 1 vì chúng ta muốn các ngày được tăng lên 1 đơn vị.
Chúng ta nên so sánh chuỗi này với dải A2:A. Sau đó, chúng ta có thể trích xuất các giá trị không khớp. Những giá trị đó sẽ là các ngày liên tiếp bị thiếu trong danh sách (dải).
Lọc các ngày liên tiếp bị thiếu bằng cách so sánh hai danh sách: phần biểu_thức_công_thức
Công thức FILTER sau (biểu_thức_công_thức) sẽ lọc ra các ngày liên tiếp bị thiếu bằng cách so sánh hai danh sách.
FILTER(chuỗi,IFNA(XMATCH(chuỗi,dải))="")Dạng Mở Rộng của “biểu_thức_công_thức” (thay thế chuỗi bằng công thức tương ứng và dải bằng A2:A):
=FILTER(SEQUENCE(DAYS(MAX(A2:A),MIN(A2:A))+1,1,MIN(A2:A),1),IFNA(XMATCH(SEQUENCE(DAYS(MAX(A2:A),MIN(A2:A))+1,1,MIN(A2:A),1),A2:A))="")Hãy giải thích công thức này chi tiết hơn.
Chúng ta đã so sánh hai danh sách trong công thức này. Danh sách 1 (chuỗi) là chuỗi số được tạo ra bằng hàm SEQUENCE trong bước trước. Danh sách 2 (dải) là các ngày trong dải A2:A.
Để so sánh, chúng ta sử dụng hàm XMATCH.
Công thức:
XMATCH(chuỗi,dải)Cú pháp:
XMATCH(khóa_tìm_kiếm, dải_tra_cứu, [chế_độ_khớp], [chế_độ_tìm_kiếm])Trong công thức này, khóa_tìm_kiếm là chuỗi ngày (chuỗi) và dải_tra_cứu là các ngày trong A2:A.
Điều này sẽ trả về giá trị #N/A nếu ngày trong chuỗi không được tìm thấy trong dải A2:A.
Chúng ta sử dụng hàm IFNA để thay thế #N/A bằng giá trị rỗng.
IFNA(XMATCH(chuỗi,dải))Công thức FILTER lọc chuỗi ngày nếu kết hợp IFNA + XMATCH bằng giá trị rỗng.
Như vậy, chúng ta có thể tìm các ngày liên tiếp bị thiếu trong một danh sách trên Google Sheets.
Công thức cũng hoạt động với một danh sách các số. Hãy thử nó thôi.
