Thêm hàng trống để phân tách đầu/tuần cuối trong Google Sheets

GiGiKing·Tháng ba 31, 2024·0 comments

Đôi khi, chúng ta có thể muốn thêm hàng trống để phân tách đầu hoặc cuối tuần trong Google Sheets. Làm thế nào chúng ta có thể thực hiện điều này?

Tất nhiên, việc thêm hàng trống giữa mỗi thay đổi tuần trong cột ngày có thể hữu ích để cải thiện tính đọc và làm cho bản sao được in ra của báo cáo trông sạch sẽ hơn.

Nếu bạn có một tập dữ liệu lớn, việc làm thủ công này có thể mất công. Đó là lý do tại sao chúng tôi đã tạo ra các công thức để tự động hóa quy trình cho bạn.

Hãy tiếp cận vấn đề này từ hai góc nhìn khác nhau:

  1. Thêm hàng trống tại mỗi hàng thay đổi tuần trong dữ liệu hiện có.
  2. Tạo ra một chuỗi ngày được phân tách bởi mỗi thay đổi tuần.

Trong bài viết này, chúng tôi sẽ chia sẻ hai công thức phục vụ cho mục đích trên, bắt đầu với dữ liệu hiện có.

Công thức có các đặc điểm chính sau:

  • Chúng là công thức mảng.
  • Chúng không sử dụng các ô trợ giúp.
  • Bạn có thể tăng hoặc giảm số hàng trống để chèn vào mỗi hàng thay đổi tuần.
  • Bạn có thể sử dụng bất kỳ ngày nào trong tuần làm phân tách.

Cách thêm hàng trống để phân tách đầu hoặc cuối tuần trong Google Sheets

Bấm vào nút bên dưới để sao chép bảng mẫu của tôi.

Sản phẩm chứng chỉ OKR

Bây giờ, hãy xem các ví dụ.

Trong ví dụ sau, tôi có một báo cáo trách nhiệm tài chính nổi bật trong Google Sheets được sắp xếp theo ngày xuất hóa đơn. Tôi muốn chèn hàng trống để phân tách đầu tuần, tức là thứ Hai.

Báo cáo bao gồm bốn cột: ID, Ngày xuất hóa đơn, Số hóa đơn và Số tiền.

Trong bức ảnh chụp màn hình sau, bạn có thể thấy dữ liệu mẫu trong A1: D và kết quả công thức trong F1: I. Công thức trong F1 lấp đầy kết quả toàn bộ.

Bức ảnh

Tiền điều kiện

Để sử dụng công thức, bạn cần biết các thông tin sau:

  • Phạm vi dữ liệu: Phạm vi của các ô chứa dữ liệu của bạn, không bao gồm hàng tiêu đề.
  • Phạm vi hàng tiêu đề: Phạm vi của các ô chứa hàng tiêu đề.
  • Phạm vi cột ngày: Phạm vi của các ô chứa cột ngày.
  • Ngày bắt đầu của tuần: Ngày trong tuần mà bạn muốn sử dụng làm phân tách. Sử dụng một trong các số từ 11 đến 17, trong đó 11 là thứ Hai và 17 là Chủ nhật.

Công thức

Công thức sau đây trong Google Sheets chèn hàng trống để phân tách các tuần bắt đầu vào thứ Hai. Nó chèn một hàng trống sau mỗi tuần kết thúc vào Chủ nhật.

=ARRAYFORMULA(LET(
range, A2:D, 
header, A1:D1, 
dt, B2:B, 
at, 11, 
helper, WEEKNUM(DATEVALUE(dt),at), 
REDUCE(header, COL(UNIQUE(helper)), LAMBDA(a,v,IFERROR(VSTACK(a,FILTER(range,helper=v),)))) 
))

Để chèn một hàng trống để phân tách các tuần bắt đầu vào Chủ nhật và kết thúc vào thứ Bảy, thay thế số 11 trong công thức bằng 17.

Cách chèn hai hàng trống dưới mỗi thay đổi tuần

Để chèn hai hàng trống dưới mỗi thay đổi tuần trong một cột ngày, thêm một dấu phẩy nữa vào phần cuối của công thức. Ví dụ:

=ARRAYFORMULA(LET(
range, A2:D, 
header, A1:D1, 
dt, B2:B, 
at, 11, 
helper, WEEKNUM(DATEVALUE(dt),at), 
REDUCE(header, COL(UNIQUE(helper)), LAMBDA(a,v,IFERROR(VSTACK(a,FILTER(range,helper=v),,)))) 
))

Các công thức trên có thể được sử dụng để chèn hàng trống để phân tách đầu hoặc cuối tuần trong Google Sheets. Các công thức này linh hoạt và có thể được tùy chỉnh để đáp ứng nhu cầu cụ thể của bạn.

Giải thích công thức

Tóm lại, công thức trên là sự kết hợp của các hàm WEEKNUM, UNIQUE và REDUCE.

  • WEEKNUM: Trả về số tuần của một ngày, dựa trên ngày trong tuần mà tuần bắt đầu. Trong trường hợp này, tuần bắt đầu vào thứ Hai.
  • UNIQUE: Trả về danh sách duy nhất giá trị từ một mảng.
  • REDUCE: Giảm một mảng các giá trị thành một giá trị duy nhất bằng cách áp dụng một hàm cho mỗi phần tử của mảng và tích lũy kết quả.

Hãy xem làm thế nào công thức chèn hàng trống để phân tách đầu hoặc cuối tuần.

Số tuần

Để kiểm tra công thức, bạn chỉ cần giữ lại dữ liệu mẫu trong A1: D. Sau đó, chèn công thức sau vào ô F2 để trả về số tuần của các ngày trong A2: A, với tuần bắt đầu vào thứ Hai:

=ARRAYFORMULA(WEEKNUM(DATEVALUE(B2:B),11))

Hàm DATEVALUE đảm bảo rằng hàm WEEKNUM chỉ trả về số tuần của các ô không rỗng. Thông thường, hàm WEEKNUM trả về một số ngay cả khi ô trống, điều này có thể gây ra lỗi công thức.

Số tuần duy nhất

Tiếp theo, chèn công thức UNIQUE sau vào ô G2:

=COL(UNIQUE(F2:F))

Hàm TOCOL đảm bảo rằng các số tuần duy nhất không chứa giá trị lỗi.

Bức ảnh

Hàm Lambda

Cuối cùng, hãy chèn công thức REDUCE phức tạp sau đây vào ô I2, đây là phần khó hiểu nhất để hiểu.

=REDUCE(A1:D1, G2:G11, LAMBDA(a, v, IFERROR(VSTACK(a,FILTER(A2:D, F2:F = v),))))

Hàm REDUCE hoạt động bằng cách áp dụng một hàm lambda cho mỗi phần tử của mảng và tích lũy kết quả.

Hàm lambda trong công thức này có hai đối số: a và v.

  • a là bộ ghi nhớ, đó là kết quả của việc áp dụng hàm lambda cho tất cả các phần tử trước đó của mảng.
  • v là phần tử hiện tại của mảng.

Chú ý: Mảng ở đây là G2: G11, chứa các số tuần duy nhất và phần tử hiện tại của mảng là G2. Giá trị hiện tại trong bộ ghi nhớ là A1: D1 (giá trị ban đầu trong bộ ghi nhớ).

Hàm lambda trả về tất cả các hàng trong phạm vi dữ liệu (A2: D) có cùng số tuần (v) bằng cách trùng khớp v trong F2: F.

FILTER(A2:D,F2:F=v)

Hàm REDUCE sau đó áp dụng hàm VSTACK cho bộ ghi nhớ và kết quả của hàm lambda, sau đó là một ô trống.

VSTACK(a,FILTER(A2:D,F2:F=v),)

Điều này xếp chồng ba mảng theo chiều dọc, tạo ra một mảng mới chứa giá trị ban đầu (hàng tiêu đề) và tất cả các hàng trong phạm vi dữ liệu có cùng số tuần như G2, sau đó là một hàng chứa giá trị lỗi do dấu phẩy mà chúng ta đặt trong VSTACK.

Hàm REDUCE sau đó tiếp tục lặp qua mảng các số tuần duy nhất (G2: G11), áp dụng hàm lambda và hàm VSTACK cho mỗi phần tử của mảng.

Hàm IFERROR loại bỏ các lỗi và trả về các ô trống. Kết quả là một mảng duy nhất chứa tất cả các hàng trong phạm vi dữ liệu, với hàng trống được chèn để phân tách đầu tuần.

Tạo ra một chuỗi ngày và phân tách đầu hoặc cuối tuần bằng hàng trống

Đôi khi, chúng ta muốn tạo ra một chuỗi ngày và phân tách đầu hoặc cuối tuần bằng một hoặc nhiều hàng trống. Trong trường hợp này, chúng ta có thể sử dụng công thức trên bằng cách thay thế phạm vi dữ liệu bằng công thức SEQUENCE. Chúng ta không cần chỉ định phạm vi ngày vì chúng ta chỉ có một cột.

Ví dụ, bạn muốn tạo ra một chuỗi 100 ngày theo thứ tự tuần tục bắt đầu từ ngày được chỉ định trong ô A1. Công thức sau đây sẽ giúp bạn làm điều đó:

=ARRAYFORMULA(LET(
range, SEQUENCE(100,1,A1), 
header, "text", 
at, 11, 
helper, WEEKNUM(range,at), 
REDUCE(header, COL(UNIQUE(helper)), LAMBDA(a,v,VSTACK(a,FILTER(range,helper=v),))) 
))

trong đó:

  • range là SEQUENCE(100,1,A1)
  • header là tiêu đề bạn muốn ở ô đầu tiên. Ở đây, nó được chỉ định là “text”.
  • at là 11, phân tách các ngày dựa trên thứ Hai là ngày bắt đầu tuần.

Để sử dụng công thức này, nhập nó vào một ô trong Google Sheets và nhấn Enter. Công thức sẽ tạo ra một chuỗi 100 ngày, phân tách bởi các hàng trống ở đầu tuần.

Bolamarketing.com

GiGiKing

Related posts