Để tự động điền số thứ tự trong các ô đã gộp trong một cột, chúng ta có thể sử dụng công thức mảng trong Google Sheets.
Ví dụ, có các tên trong cột B2:B và trong đó, tôi đã gộp một số hàng.
Tôi đã gộp các ô trong cột A2:A tương ứng với định dạng trong cột B2:B.
Làm thế nào để tự động điền số seri vào các ô đã gộp trong cột A tương ứng với tên trong cột B?
Giải pháp tốt nhất là sử dụng công thức mảng trong ô A2 để nhận số thứ tự ra các ô đã gộp A2:A trong Google Sheets. Tôi sẽ giới thiệu công thức đó.
Một số người dùng tuân theo một phương pháp không dùng công thức mảng. Tôi không thấy điểm nào để sử dụng chúng trừ khi tôi muốn đánh số một số lượng lớn các hàng đã gộp.
Phương pháp đó như sau.
- Chọn A2:A và hủy gộp các hàng (Định dạng > Gộp ô > Hủy gộp).
- Trong ô A2, nhập
=if(len(B2),counta($B$2:B2),)và kéo xuống cho đến hàng mà bạn muốn. Ví dụ từ hàng số 19. - Sau đó, chọn B2:B19.
- Nhấp chuột phải và chọn “Sao chép”.
- Nhấp chuột phải vào ô A2 và chọn “Dán đặc biệt” > “Chỉ dán định dạng”.
Đây là quy trình mất thời gian.
Tôi có một công thức mảng để tự động điền số thứ tự vào các ô đã gộp trong Google Sheets.
Không cần hủy gộp A2:A. Chỉ cần chèn công thức của tôi vào ô A2 để điền số seri vào cột đã gộp tương ứng với các giá trị trong cột B.
Tôi có hai lựa chọn công thức.
1. Vlookup để tự động điền số thứ tự vào các ô đã gộp
Công thức mảng #1:
=ArrayFormula( ifna( vlookup( row(B2:B), { filter( ROW(B2:B),len(B2:B) ), sequence(counta(B2:B),1) }, 2,0) ) )
Chèn công thức mảng trên vào ô A2. Nó sẽ tự động điền các số seri vào các ô đã gộp trong cột tương ứng với các giá trị trong B2:B.
Cách giải thích công thức
Để hiểu công thức Vlookup trên, bạn phải bắt đầu từ phần giữa của nó. Đó là phần Filter sau đây.
filter( ROW(B2:B),len(B2:B) )
Nó trả về số hàng chứa giá trị trong B2:B. Hàm Len kiểm tra xem có giá trị trong B2:B hay không.
Kết quả sẽ là các số hàng 2, 5, 6, 9, 12 và 15.
Phần tiếp theo là phần Sequence sau đây.
=SEQUENCE(counta(B2:B),1)
Counta trong nó đếm các giá trị (tên trong B2:B). Có sáu tên. Do đó, Sequence sẽ trả về các số từ 1 đến 6.
Sử dụng Dấu ngoặc nhọn, tôi đã tạo một mảng từ hai kết quả trên.
{ filter( ROW(B2:B),len(B2:B) ), sequence(counta(B2:B),1) }
Kết quả sẽ là một bảng hai cột như dưới đây.
Phần Vlookup trong phần đầu của công thức sẽ tìm kiếm xuống cột đầu tiên của bảng này để tìm các số hàng, tức là row(B2:B), từ hàng số 2 trở xuống.
Nếu tìm thấy một kết quả, nó sẽ trả về các số seri từ cột thứ hai của bảng.
Ở những nơi công thức không tìm thấy kết quả, nó sẽ trả về #N/A. Ifna rỗng các hàng đó.
Dưới đây là một giải pháp thay thế.
2. Đếm chạy để tự động điền số thứ tự vào các ô đã gộp
Công thức mảng #2:
=ARRAYFORMULA( IF( len(B2:B), COUNTIFS( ROW(B2:B), "<="&ROW(B2:B), len(B2:B),">0" ), ) )
Tương tự với giải pháp đầu tiên của chúng tôi, bạn chỉ cần chèn công thức Google Sheets này vào ô A2.
Cách giải thích công thức
Công thức này sử dụng Countifs và làm theo cách tính chạy.
Có hai điều kiện trong Countifs này.
- Trong mỗi hàng, Countifs sẽ kiểm tra xem số hàng có nhỏ hơn hoặc bằng số hàng hiện tại hay không.
- Trong mỗi hàng, nó cũng sẽ kiểm tra xem có giá trị trong cột B hay không.
Nếu cả hai điều kiện đều đúng, công thức sẽ trả về số đếm đến dòng đó trong mỗi hàng.
Để trả về số thứ tự trong các ô đã gộp trong một cột trong Google Sheets, bạn có thể thử một trong hai công thức trên.
Khi bạn thử nghiệm nó trong một cột chứa nhiều hàng đã gộp, bạn có thể hiểu công thức nào hoạt động nhanh hơn. Bạn có thể sử dụng công thức đó.
Đó là tất cả. Cảm ơn bạn đã đọc bài viết. Chúc bạn vui vẻ!
Liên kết: Crawlan.com
