Bạn đã từng gặp phải tình huống trong Google Sheets khi có những ngày thiếu trong bảng dữ liệu của mình? Đừng lo lắng, trong bài viết này, chúng ta sẽ khám phá hai cách để tự động điền những ngày thiếu đó. Bạn sẽ không cần phải thực hiện bất kỳ công thức phức tạp nào, chỉ cần chỉ định đúng phạm vi dữ liệu và công thức sẽ hoàn thành công việc của mình.
Điền ngày thiếu trong Google Sheets: Phương pháp tổng quát
Trong phương pháp này, chúng ta sẽ sử dụng hai cột: một cột chứa ngày và một cột khác chứa số lượng hoặc giá trị bất kỳ. Bạn chỉ cần nhập công thức sau vào ô E2 để điền vào những ngày thiếu trong phạm vi dữ liệu B2:C100, trong đó B2:B100 chứa ngày và C2:C100 chứa số lượng (hoặc giá trị bất kỳ). Trong công thức này, bạn chỉ cần chỉ định tham chiếu cột ngày.
Công thức 1:
=LET( dt, B2:B100, insert, REDUCE(, dt, LAMBDA(a, v, IFERROR(VSTACK(a, VSTACK( HSTACK(v, OFFSET(v, 0, 1)), SEQUENCE(MAX(0, OFFSET(v, 1, 0)-v-1), 1, v+1) ) )) )), FILTER(insert, CHOOSECOLS(insert, 1)<>"") )Lưu ý quan trọng:
- Phạm vi B2:C100 phải được sắp xếp theo cột ngày theo thứ tự tăng dần.
- Sau khi áp dụng công thức, hãy đảm bảo định dạng phù hợp cho cột ngày trong kết quả. Chọn các giá trị ngày trong kết quả và áp dụng Format > Number > Date.
Công thức 1 chi tiết:
Công thức này sử dụng hàm LET để gán tên cho các phạm vi/ biểu thức.
Cú pháp:
LET(tên1, biểu_thức_giá_trị1, [tên2, …], [biểu_thức_giá_trị2, …], biểu_thức_công_thức)Các tên và biểu thức giá trị đã xác định:
- tên1: dt
- biểu_thức_giá_trị1: B2:B100 (phạm vi ngày)
Công thức biểu thức:
=LET( dt, B2:B100, insert, REDUCE(, dt, LAMBDA(a, v, IFERROR(VSTACK(a, VSTACK( HSTACK(v, OFFSET(v, 0, 1)), SEQUENCE(MAX(0, OFFSET(v, 1, 0)-v-1), 1, v+1) ) )) )), FILTER(insert, CHOOSECOLS(insert, 1)<>"") )Biểu thức này sẽ điền vào những ngày thiếu trong bảng hai cột.
Công thức này sử dụng hàm REDUCE để lặp lại từng giá trị trong cột ngày và thực hiện một hàm lambda. Phần quan trọng của hàm lambda là:
VSTACK( HSTACK(v, OFFSET(v, 0, 1)), SEQUENCE(MAX(0, OFFSET(v, 1, 0) - v - 1), 1, v + 1) )Nó sẽ chồng dọc phần tử hiện tại (‘v’) và giá trị trong cột tiếp theo với các giá trị ngày chuỗi.
Phần SEQUENCE trả về n giá trị chuỗi nếu giá trị tiếp theo hiện tại – giá trị hiện tại – 1 lớn hơn 0, với chuỗi bắt đầu từ giá trị hiện tại + 1. Quá trình này lặp lại trên mỗi hàng của phạm vi ngày.
Để có được kết quả tích lũy, VSTACK(a, …) được sử dụng trước phần quan trọng đó. Hàm IFERROR loại bỏ bất kỳ lỗi nào do việc chồng chéo mảng không khớp và lỗi chuỗi.
Công thức biểu thức:
FILTER(insert, CHOOSECOLS(insert, 1)<>"")Biểu thức này lọc bỏ các hàng trống từ cột đầu tiên của “insert”.
Hãy áp dụng phương pháp này khi bạn không muốn điền vào ngày thiếu theo phân loại.
Điền ngày thiếu trong Google Sheets: Phương pháp theo phân loại
Trong phương pháp này, chúng ta xử lý một bảng gồm ba cột: Phân loại, Ngày và Số lượng.
Việc có một cột phân loại trong bảng làm tăng độ phức tạp của thách thức. Hãy giả sử rằng chúng ta có hai phân loại.
Ngày tối thiểu và tối đa cho phân loại đầu tiên lần lượt là 01/01/2024 và 06/01/2024 và đối với phân loại thứ hai, chúng là 03/01/2024 và 07/01/2024.
Sau khi áp dụng công thức, ta có thể mong đợi mỗi phân loại sẽ có cùng một số lượng bản ghi. Do đó, các ngày bắt đầu và kết thúc cho cả hai phân loại sẽ được điều chỉnh thành 01/01/2024 và 07/01/2024.
Việc xử lý các ngày thiếu trở nên khó khăn trong kịch bản này. Hãy xem cách giải quyết vấn đề này trong Google Sheets.
Công thức 2:
=ArrayFormula( LET( table, A2:C100, ur, IFNA( HSTACK( IFNA( HSTACK( TOCOL(UNIQUE(CHOOSECOLS(table, 1)), 3), MIN(CHOOSECOLS(table, 2)) ), MIN(CHOOSECOLS(table, 2)) ), MAX(CHOOSECOLS(table, 2)) ), MAX(CHOOSECOLS(table, 2))), seq, MAP( CHOOSECOLS(ur, 1), CHOOSECOLS(ur, 2), CHOOSECOLS(ur, 3), LAMBDA(x, y, z, LET( test, SEQUENCE(1, z-y+1, y), FILTER( test, IFNA(XMATCH(x&test, CHOOSECOLS(table, 1)&CHOOSECOLS(table, 2)))="" ) ) ) ), missing, HSTACK(TOCOL(IF(seq, CHOOSECOLS(ur, 1),)), TOCOL(seq)), IFNA(SORT(VSTACK(table, missing), 1, 1, 2, 1)) ) )Lưu ý quan trọng:
- Phạm vi A2:C100 phải được sắp xếp theo cột A (phân loại) theo thứ tự tăng dần, sau đó theo cột B (ngày) theo thứ tự tăng dần.
- Sau khi áp dụng công thức, hãy đảm bảo định dạng chính xác cho các giá trị ngày trong kết quả. Chọn giá trị ngày và áp dụng Format > Number > Date.
Công thức 2 chi tiết:
Hãy xem cách công thức điền vào những ngày thiếu theo phân loại trong Google Sheets.
Ở đây, chúng ta đã sử dụng hàm LET để đơn giản hóa công thức. Dưới đây là các tên, biểu thức giá trị và biểu thức công thức được giải thích chi tiết.
Các tên và biểu thức giá trị đã xác định:
-
Tên 1: table
-
Biểu thức giá trị 1: A2:C100 (phạm vi bảng)
-
Tên 2: ur
-
Biểu thức giá trị 2:
Giá trị biểu thức 2:
=ArrayFormula( LET( table, A2:C100, ur, IFNA( HSTACK( IFNA( HSTACK( TOCOL(UNIQUE(CHOOSECOLS(table, 1)), 3), MIN(CHOOSECOLS(table, 2)) ), MIN(CHOOSECOLS(table, 2)) ), MAX(CHOOSECOLS(table, 2)) ), MAX(CHOOSECOLS(table, 2))))Phần ‘ur’ trích xuất các phân loại duy nhất và chồng dọc chúng với một ngày bắt đầu tối thiểu chung trong một cột và một ngày kết thúc tối đa chung trong một cột khác.
Nó trả về một bảng ba cột với các phân loại duy nhất, ngày bắt đầu tối thiểu chung và ngày kết thúc tối đa chung. Chúng ta sử dụng các hàm UNIQUE, IFNA, VSTACK và HSTACK cho mục đích này.
Các tên và biểu thức giá trị đã xác định:
- Tên 3: seq
- Biểu thức giá trị 3:
Giá trị biểu thức 3:
=ArrayFormula( MAP( CHOOSECOLS(ur, 1), CHOOSECOLS(ur, 2), CHOOSECOLS(ur, 3), LAMBDA(x, y, z, LET( test, SEQUENCE(1, z-y+1, y), FILTER( test, IFNA(XMATCH(x&test, CHOOSECOLS(table, 1)&CHOOSECOLS(table, 2)))="" ) ) ) ))Chúng ta sử dụng hàm MAP để lặp lại từng giá trị trong bảng ‘ur’ ba cột, mở rộng các ngày bắt đầu và kết thúc trong mỗi hàng và lọc ra các ngày theo phân loại đã tồn tại trong bảng ‘table’.
Các tên và biểu thức giá trị đã xác định:
- Tên 4: missing
- Biểu thức giá trị 4:
Giá trị biểu thức 4:
=ArrayFormula( HSTACK(TOCOL(IF(seq, CHOOSECOLS(ur, 1),)), TOCOL(seq)) )Kiểm tra logic IF trả về phân loại trong mỗi hàng tương ứng với các ngày mở rộng. Hàm TOCOL biến nó thành một cột duy nhất. Với điều này, chúng ta chồng dọc các ngày mở rộng sau khi biến nó thành một cột.
Công thức biểu thức:
=ArrayFormula( IFNA(SORT(VSTACK(table, missing), 1, 1, 2, 1)) )Kết hợp các ngày thiếu với bảng ban đầu, sắp xếp theo cột 1 (phân loại) theo thứ tự tăng dần, sau đó theo cột 2 (ngày) theo thứ tự tăng dần.
Lợi ích của việc điền ngày thiếu trong một bảng trong Google Sheets
Việc điền ngày thiếu trong một bảng có một số lợi ích trong việc xử lý và hiển thị dữ liệu. Phương pháp này đặc biệt hữu ích trong biểu đồ và tính toán tổng diễn tiến.
Việc điền ngày thiếu đảm bảo rằng dữ liệu của bạn liên tục, tạo ra biểu đồ đường mượt mà hiển thị chính xác các xu hướng theo thời gian. Bên cạnh đó, có một tập hợp đầy đủ các ngày liên tục đảm bảo trục X nhất quán, cung cấp một cách hiển thị rõ ràng hơn về dữ liệu dựa trên thời gian.
Đối với các tình huống bạn cần đếm chạy hoặc tổng cộng tích lũy theo thời gian, việc có một tập hợp đầy đủ các ngày cho phép tính toán chính xác mà không có khoảng trống.
Chúng tôi đã triển khai các công thức tích lũy chạy phân loại theo từng phân loại. Bạn có thể sử dụng chúng sau khi chèn các ngày thiếu vào bảng của bạn.
Tài nguyên
Hướng dẫn này mô tả hai phương pháp để điền vào những ngày thiếu trong một bảng. Các hướng dẫn sau đây đề cập đến các kỹ thuật xử lý dữ liệu nâng cao liên quan đến ngày và phạm vi ngày.
- Tìm ngày liên tiếp bị thiếu trong một danh sách trong Google Sheets
- Chuyển đổi các ngày liên tiếp thành phạm vi ngày trong Google Sheets: Phương pháp REDUCE
- Chuyển đổi ngày thành phạm vi tuần trong Google Sheets (Công thức mảng)
Hy vọng rằng các phương pháp điền ngày thiếu trong Google Sheets sẽ giúp bạn tối ưu hóa công việc với dữ liệu và tạo ra các biểu đồ và tính toán diễn tiến chính xác. Hãy thử áp dụng phương pháp này vào dự án của bạn và xem sự khác biệt nó mang lại.
