Nếu bạn cần chèn một số hàng trống cố định vào dữ liệu của bạn, bạn có thể làm điều này bằng cách sử dụng một công thức trong Google Sheets.
Có một số lợi ích thực tế khi bạn muốn làm điều này. Ví dụ, bạn có thể chèn hàng trống vào mỗi hàng khác nhau để dễ đọc dữ liệu của bạn. Điều này cũng hữu ích khi bạn muốn chèn một công thức mảng vào một phạm vi ô hợp nhất duy nhất, vì nó sẽ ngăn công thức ẩn giá trị.
Trong bài hướng dẫn này, tôi sẽ chỉ cho bạn cách chèn 2 hàng trống sau mỗi hàng. Tôi cũng sẽ giải thích cách thay đổi số hàng trống.
Có một số cách khác nhau để làm điều này. Phương pháp ưu tiên của tôi là sử dụng công thức REDUCE. Chúng tôi sẽ tìm hiểu công thức này chi tiết trong phần cuối của bài hướng dẫn. Nhưng trước tiên, hãy xem xét một phương pháp khác sử dụng VLOOKUP.
Công thức VLOOKUP để chèn hàng trống trong Google Sheets
Hãy giới thiệu công thức VLOOKUP mà tôi đã sử dụng trong ô F2. Sau đó, tôi sẽ giải thích những thay đổi bạn có thể cần thực hiện trong công thức này để sử dụng nó trong tập dữ liệu của bạn. Cuối cùng, tôi sẽ giải thích công thức một cách ngắn gọn.
=ARRAYFORMULA( LET( **range, "A2:B", first_col, A1:A, n, 2,**
test, XMATCH("?*",TO_TEXT(first_col),2,-1), IFNA( VLOOKUP( SEQUENCE(test*(n+1),1,0)/(n+1)+1, HSTACK(SEQUENCE(test),INDIRECT(range&test)), {2,3},0 ) ) ) )Công thức VLOOKUP trên chèn hai hàng trống sau mỗi hàng khác.
Làm thế nào để điều chỉnh công thức này cho tập dữ liệu của bạn?
Tôi biết rằng phạm vi dữ liệu của bạn có thể khác hoàn toàn. Để cho phép bạn sử dụng công thức này trong một mảng/phạm vi dữ liệu khác, tôi cần giải thích những thay đổi cần thiết trong công thức trên.
1. Thay đổi phạm vi dữ liệu trong công thức
Giả sử bạn muốn chèn hai hàng trống sau mỗi hàng khác nhau trong phạm vi B5:C. Bạn cần thực hiện những thay đổi sau trong công thức của tôi:
- Thay thế phạm vi trong công thức từ “A2:B” thành “B5:C”.
- Thay first_col trong công thức từ A1:A thành B1:B. Điều này quan trọng. Bạn nên giữ phạm vi cột đầu tiên (first_col) bắt đầu từ hàng đầu tiên, không phải từ hàng thứ 5.
Công thức trên chèn 2 hàng sau mỗi hàng. Để thay đổi số hàng trống, bạn cần thay đổi giá trị của đối số n.
2. Thay đổi số hàng để chèn
Xem cách đơn giản để tăng số hàng trống. Bạn thay thế giá trị của n trong công thức từ 2 thành số mà bạn muốn. Trong ví dụ dưới đây, tôi sẽ đặt n = 3.
Công thức dưới đây chèn 3 hàng trống sau mỗi hàng khác nhau trong phạm vi B5:C.
=ARRAYFORMULA( LET( **range, "B5:C", first_col, B1:B, n, 3,**
test, XMATCH("?*",TO_TEXT(first_col),2,-1), IFNA( VLOOKUP( SEQUENCE(test*(n+1),1,0)/(n+1)+1, HSTACK(SEQUENCE(test),INDIRECT(range&test)), {2,3},0 ) ) ) )
3. Thay đổi số cột
Trong các ví dụ trên của tôi, tôi đã sử dụng một tập dữ liệu hai cột làm ví dụ.
Nếu bạn có một số cột khác nhau, chẳng hạn như A2:C, thì phần công thức {2,3} sẽ được thay đổi thành {2,3,4}. Tương tự, nếu số cột là 4, thì nó sẽ là {2, 3, 4, 5}.
Nếu bạn muốn tìm hiểu thêm về tính năng mảng VLOOKUP này, hãy xem hướng dẫn này: Multiple Values Using Vlookup in Google Sheets is Possible [How to]
Công thức dưới đây (được hiển thị trong ảnh chụp màn hình) chèn một hàng trống sau mỗi hàng khác nhau trong phạm vi A2:D.
Giải thích cấu trúc của công thức VLOOKUP để chèn hàng trống trong Google Sheets
Đây là bản chất của công thức:
Tôi đã sử dụng hai công thức SEQUENCE. Một công thức được thêm vào phạm vi và công thức khác được sử dụng như một khóa tìm kiếm trong VLOOKUP.
Cú pháp của hàm VLOOKUP trong Google Sheets:
VLOOKUP(search_key, range, index, [is_sorted])Giả sử phạm vi dữ liệu là A1:B6. Điều đó có nghĩa là có 6 bản ghi trong bảng.
Công thức =SEQUENCE(6) sẽ trả về các số từ 1 đến 6, sau đó được xếp chồng vào phạm vi dữ liệu như sau (vui lòng tham khảo F1:H6 trong hình ảnh dưới đây):
=HSTACK(SEQUENCE(6),A1:B6)Đây là phạm vi trong VLOOKUP.
Bây giờ, khóa tìm kiếm là các số chuỗi * n / (n + 1). Hãy cùng xem xét n là 3, thì nó sẽ là:
=ArrayFormula(SEQUENCE(6 * 3, 1, 0) / 3 + 1)Vui lòng tham khảo D1:D18 trong hình ảnh dưới đây.
Với VLOOKUP, bây giờ chúng ta có thể dễ dàng chèn các hàng trống n vào bên dưới mỗi hàng khác nhau. Vui lòng xem ảnh minh họa dưới đây.
Công thức REDUCE để chèn hàng trống trong Google Sheets
Công thức VLOOKUP trên là một công thức linh hoạt hoạt động với cả phạm vi mở và đóng. Chúng ta có thể sử dụng hàm REDUCE để viết một công thức linh hoạt tương tự như chèn hàng trống dưới mỗi hàng khác.
Tuy nhiên, để làm mọi thứ đơn giản, tôi sẽ sử dụng nó trên các phạm vi giới hạn.
Chúng tôi có thể sử dụng công thức REDUCE sau đây để chèn một hàng trống bên dưới từng hàng khác nhau trong phạm vi đóng B3:E8:
=REDUCE(,B3:B8,LAMBDA(a,v,IFNA(VSTACK(a,FILTER(B3:E8,ROW(B3:B8)=ROW(v)),))))Ở đây,
- B3:B8 là phạm vi cột đầu tiên trong dữ liệu nguồn.
- B3:E8 là phạm vi dữ liệu nguồn.
Để chèn hai hàng trống, bạn thêm một dấu phẩy nữa ngay sau dấu phẩy cuối cùng trong công thức.
Công thức này có một vấn đề. Nó sẽ chèn một hàng trống ở đầu, nhưng đây là công thức tốt nhất để chèn hàng trống trong Google Sheets, bỏ qua vấn đề phạm vi đóng.
Logic và giải thích công thức
Hàm FILTER lọc hàng đầu tiên trong phạm vi.
FILTER(B3:E8,ROW(B3:B8)=ROW(v))trong đó v bằng với B3.
VSTACK xếp chồng giá trị tích lũy (a), trong trường hợp này là rỗng, với hàng được lọc ở trên và sau đó là một ô trống duy nhất.
VSTACK(a,FILTER(B3:E8,ROW(B3:B8)=ROW(v)),)
Trong bước tiếp theo, trong VSTACK, giá trị tích lũy sẽ là kết quả trên. v trong FILTER(B3:E8, ROW(B3:B8) = ROW(v)) biến thành B4.
Quá trình này lặp lại cho đến hàng cuối cùng trong phạm vi.
Đó là logic đằng sau công thức REDUCE chèn hàng trống dưới mỗi hàng khác nhau trong Google Sheets.
Hãy làm hiện các giá trị bị ẩn trong các ô hợp nhất trong Google Sheets
Bạn có thử áp dụng một công thức mảng cho ô trên cùng của một phạm vi ô hợp nhất chưa? Nếu có, liệu nó có đúng xuất tất cả các giá trị hay không?
Khi bạn sử dụng một công thức mảng trong một phạm vi ô hợp nhất, nó ẩn một số giá trị, phụ thuộc vào số hàng đã hợp nhất.
Vui lòng xem hình ảnh dưới đây là 1 ví dụ.
=SEQUENCE(10)Bạn có thể thấy rằng công thức SEQUENCE trong ô A2 không trả về tất cả các số từ 1 đến 10. Một số giá trị bị ẩn do sự hợp nhất các ô.
Bạn đã học cách chèn hàng trống bằng cách sử dụng một công thức trong Google Sheets. Điều này là thời gian để thử nó trong một tình huống thực tế.
Bạn có thể chọn công thức dựa trên REDUCE. Tôi đang tránh công thức dựa trên VLOOKUP vì nó sử dụng phạm vi dưới dạng chuỗi văn bản (trong dấu ngoặc kép).
=REDUCE(,A1:A10,LAMBDA(a,v,IFNA(VSTACK(a,FILTER(**SEQUENCE(10)**,ROW(A1:A10)=ROW(v)),))))Công thức này trong ô E1 sẽ trả về tất cả các giá trị chính xác. Tuy nhiên, chúng ta nên nhập công thức trong một ô không được hợp nhất vì nó trả về một ô trống ở đầu.
Công thức SEQUENCE trả về 10 số. Vì vậy, chúng ta nên chỉ định A1:A10 trong công thức. Nếu công thức trả về 100 số, thì bạn nên chỉ định A1:A100.
Nếu bạn sử dụng bất kỳ công thức nào khác thay vì SEQUENCE, ví dụ, một QUERY, bạn phải kiểm tra trước số hàng mà QUERY trả về và chỉ định một phạm vi tương ứng, thay vì A1:A100.
Lưu ý: Công thức trên yêu cầu các ô được hợp nhất duy nhất để hoạt động chính xác.
Liên quan:
- How to Automatically Insert a Blank Row Below Each Group in Google Sheets.
- Insert Blank Rows to Separate Week Starts/Ends in Google Sheets.
