Để chèn tự động một hàng trống dưới mỗi nhóm trong Google Sheets, chúng ta có thể sử dụng một công thức. Không có lệnh tích hợp nào để làm điều này.
Trước đây, tôi đã viết và chia sẻ một công thức với độc giả của tôi để chèn các hàng trống phía dưới mỗi hàng. Ở đây, thay vì phía dưới mỗi hàng, tôi đang nói về phía dưới mỗi nhóm dữ liệu hoặc chúng ta có thể nói phía dưới mỗi danh mục.
Đúng! Trong bài viết này, tôi sẽ viết công thức cần thiết để chèn tự động một hàng trống dưới mỗi danh mục / nhóm trong Google Sheets.
Lưu ý, nếu mục đích của bạn khi phân tách các nhóm bằng các hàng trống chỉ để lấy một bản in dữ liệu đã được định dạng, thì hãy chọn đầu ra của công thức để in.
Ngược lại, nếu bạn muốn thêm giá trị vào hàng đã được chèn, bạn có thể cần sao chép kết quả của công thức và dán nó dưới dạng các giá trị trong cùng một phạm vi hoặc trong một phạm vi mới. Điều này sẽ tránh việc hỏng công thức và lỗi “#REF!”.
Hãy bắt đầu bằng cách viết công thức để giúp bạn chèn tự động một hàng trống dưới mỗi nhóm trong Google Sheets.
Bài viết này bao gồm công thức để sử dụng trong nhóm cột duy nhất cũng như trong nhóm nhiều cột.
Cách Chèn Hàng Trống Dưới Mỗi Nhóm Trong Google Sheets (Phương pháp Cũ)
Theo ví dụ trên (xem Hình 1), cột nhóm là cột A (Quý).
Tôi muốn tách mỗi “Quý” bằng cách chèn một hàng trống sau mỗi nhóm. Ví dụ: một hàng trống sau mỗi nhóm Q1, Q2, Q3 và Q4.
Với dữ liệu mẫu trong phạm vi A1: A, các hàng trống sẽ được chèn sau hàng # 4, hàng # 8 và hàng # 11, có nghĩa là sau khi kết thúc mỗi danh mục.
Để viết công thức tự động chèn một hàng trống dưới mỗi nhóm trong Google Sheets, chúng ta nên viết công thức đầu tiên có thể xác định hàng kết thúc của mỗi nhóm.
1. Đánh dấu hoặc xác định hàng kết thúc của mỗi nhóm
Trả lại số đếm chạy của các giá trị trong A2: A thành D2: D bằng cách sử dụng công thức COUNTIFS sau đây:
=ARRAYFORMULA(COUNTIFS($A$2:$A,$A$2:$A,ROW($A$2:$A),"<="&ROW($A$2:$A)))Chúng ta có thể sử dụng kết quả trên để tìm hàng kết thúc của mỗi nhóm trong Google Sheets. Xem các giá trị trong phạm vi ô D2: D. Hàng trên số 1 là hàng kết thúc của nhóm đó.
Đối với mục đích của công thức của chúng ta, hãy thay thế số 1 bằng ký tự gạch ngang và các số còn lại bằng các dấu cách.
Chúng ta có thể làm điều đó bằng cách gói công thức đếm chạy trong một ArrayFormula và IF như công thức thông thường dưới đây.
Công thức thông thường : =ARRAYFORMULA(IF(running_count=1,"-",))
Vì vậy, công thức trong ô D2 sẽ trở thành:
=ARRAYFORMULA(IF(COUNTIFS($A$2:$A,$A$2:$A,ROW($A$2:$A),"<="&ROW($A$2:$A))=1,"-",))2. Giãn cách cột A (Quý) và cột D (Ký tự dấu cách)
Hàm FLATTEN rất hữu ích để gỡ bỏ sự tải xuống tập dữ liệu trong Google Sheets. Tuy nhiên, nó có thể gây ra vấn đề nếu được sử dụng trong một phạm vi mở, như A2: A.
Điều này bởi vì khi bạn làm phẳng các cột, số hàng sẽ tăng gấp đôi nếu có hai cột, tăng ba lần nếu có ba cột, và cứ thế.
Để tránh điều này, tốt hơn hết là sử dụng hàm FLATTEN trong một phạm vi giới hạn, như A2: A13. Khi bạn đã xác minh rằng công thức hoạt động chính xác, sau đó bạn có thể thay đổi nó thành một phạm vi mở.
Đây là công thức F2 để giãn cột D và cột A:
=FLATTEN({$D$2:$D13,A2:A13})Cú pháp $D$2:D13 cho biết Google Sheets làm cho phạm vi D2: D13 tuyệt đối. Điều này có nghĩa là phạm vi sẽ không thay đổi nếu công thức được sao chép hoặc di chuyển. Cú pháp A2:A13 cho biết Google Sheets làm cho phạm vi A2:A13 tương đối. Điều này có nghĩa là phạm vi sẽ thay đổi nếu công thức được sao chép hoặc di chuyển.
Bằng cách chỉ giãn các cột D2: D13 với A2: A13, chúng ta không thể tự động chèn một hàng trống dưới mỗi nhóm (ở đây là Quý) trong Google Sheets.
Theo kết quả trong cột F, chúng ta cũng phải hoàn thành các bước sau bằng cách chỉnh sửa công thức F2.
- Loại bỏ các hàng trống trong kết quả phía trên.
- Làm cho phạm vi D2:D13 và A2:A13 mở.
- Xóa ký tự gạch ngang đầu tiên.
- Thay thế D2:D13 bằng công thức D2 chính nó.
- Thay thế các dấu gạch ngang còn lại bằng các ký tự dấu cách.
3. Bước Cuối Cùng Để Tách Tự Động Mỗi Nhóm Bằng Một Hàng Trống Trong Google Sheets
Bằng cách sử dụng hàm FILTER, chúng ta có thể loại bỏ các hàng trống. Lần này, chúng ta cũng có thể biến phạm vi đã gập lại từ kín thành mở.
Công thức là như sau:
=FILTER(FLATTEN({$D$2:$D,A2:A}), FLATTEN({$D$2:$D,A2:A})<>"")Chúng ta đã hoàn thành các điểm 1 và 2 ở trên.
Bây giờ, để loại bỏ ký tự gạch ngang đầu tiên (gạch ngang trong ô F2), chúng ta có thể sử dụng hàm QUERY và mệnh đề OFFSET trong nó (điểm số 3). Hãy nhớ, chúng ta đang chỉnh sửa công thức F2 trong mỗi bước.
=QUERY(FILTER(FLATTEN({$D$2:$D,A2:A}), FLATTEN({$D2:$D,A2:A})<>""), "offset 1", 0)Hãy thay thế D2:D (hai lần) trong công thức trên bằng công thức D2 chính nó và thay thế dấu gạch ngang trong công thức D2 bằng ký tự dấu cách.
=QUERY(FILTER(FLATTEN({ARRAYFORMULA(IF(COUNTIFS($A$2:$A,$A$2:$A,ROW($A$2:$A),"<="&ROW($A$2:$A))=1," ",)),A2:A}), FLATTEN({ARRAYFORMULA(IF(COUNTIFS($A$2:$A,$A$2:$A,ROW($A$2:$A),"<="&ROW($A$2:$A))=1," ",)),A2:A})<>"") ,"offset 1", 0)Xóa công thức trong ô D2 và voila! Bạn đã biết cách sử dụng công thức để tự động chèn một hàng trống dưới mỗi nhóm trong Google Sheets.
Hãy rút ngắn nó bằng cách tránh lặp lại các biểu thức sử dụng hàm LET. Đây là công thức:
=ARRAYFORMULA(LET(cat,$A$2:$A, fc,A2:A, rc,COUNTIFS(cat,cat,ROW(cat),"<="&ROW(cat)), QUERY(FILTER(FLATTEN({IF(rc=1," ",)),fc}),FLATTEN({IF(rc=1," ",)),fc})<>"") ,"offset 1", 0))Để xem đầu ra, vui lòng tham khảo ảnh chụp màn hình dưới đây.
Chỉ cần kéo công thức F2 qua để chèn một hàng trống dưới mỗi danh mục trong nhiều cột.
Công thức có thể ảnh hưởng đến định dạng, có nghĩa là nó có thể chuyển đổi các ngày thành giá trị ngày.
Bạn có thể định dạng lại các giá trị ngày trở lại thành ngày. Để làm điều này, chọn G2: G và nhấp chuột vào Định dạng> Số> Ngày.
Cách này, chúng ta có thể tự động chèn một hàng trống dưới mỗi nhóm trong nhiều cột trong Google Sheets.
Cách Chèn Hàng Trống Dưới Mỗi Nhóm Trong Google Sheets (Phương pháp Hiện Đại)
Google Sheets đã phát triển rất nhiều kể từ năm 2022 với sự giới thiệu của các hàm NHÌN và REDUCE mới, Các hàm được gọi và LAMBDA. Tất nhiên, còn có các tính năng khác.
Chúng tôi đang sử dụng một trong các hàm LAMBDA để chèn các hàng trống dưới mỗi nhóm.
Trong ví dụ trên, bạn có thể sử dụng công thức REDUCE sau đây để chèn một hàng trống dưới mỗi nhóm trong Google Sheets:
=REDUCE(A1:C1, TOCOL(UNIQUE(A2:A), 1), LAMBDA(a,v, IFNA(VSTACK(a, FILTER(A2:C, A2:A=v),))))Trong đó:
- A2:A là cột danh mục.
- A1:C1 là phạm vi tiêu đề.
- A2:C là phạm vi bảng.
Bạn không cần kéo công thức này qua hoặc xuống, và nó bao gồm tiêu đề.
Để chèn hai hàng trống, bạn có thể thêm dấu phẩy sau dấu phẩy cuối cùng trong công thức. Ví dụ:
=REDUCE(A1:C1, TOCOL(UNIQUE(A2:A), 1), LAMBDA(a,v, IFNA(VSTACK(a, FILTER(A2:C, A2:A=v),,))))Để chèn ba hàng trống, bạn có thể thêm hai dấu phẩy.
Sample_Sheet_12820
Tài nguyên:
- Cách Ghép Hai Cột Thành Một Cột Trong Google Sheets.
- Công thức để Chèn Tổng Nhóm vào trong Google Sheets.
- Chèn Các Hàng Trống để Phân tách Bắt Đầu/Kết Thúc Tuần trong Google Sheets.
