Bạn có muốn biết làm thế nào để lấy số lần xuất hiện các giá trị trong mỗi dòng trong Google Sheets? Bài viết này sẽ giới thiệu một công thức mảng tương đương với công thức Drag and Drop Countif. Đây là một công thức hữu ích trong Google Sheets nếu bạn muốn đếm số lần xuất hiện của các mục trong mỗi dòng trong tập dữ liệu trên Google Sheets.
Trước hết, để dễ hiểu hơn, hãy xem một tập dữ liệu mẫu và công thức không sử dụng mảng dưới đây.
Công thức không sử dụng mảng để lấy số lần xuất hiện trong mỗi dòng trong Google Sheets
Công thức Countif trong ô C2 đếm số lần xuất hiện của giá trị (tên) trong ô B2 trong phạm vi B2:B11.
=countif(B2:B11,B2)
Khi kéo công thức xuống, phạm vi đếm và tiêu chí sẽ thay đổi. Ví dụ, trong ô B3, tiêu chí sẽ được lấy từ ô B3 và phạm vi sẽ là B3:B12.
Nghĩa là công thức Countif đếm số lần xuất hiện của các giá trị trong phạm vi bắt đầu từ hàng được nhập.
Mặc dù công thức Countif trên rất đơn giản, nhưng nó có nhược điểm khi sử dụng công thức kéo và thả như sau:
- Bạn phải sao chép-dán công thức cho toàn bộ cột (C2:C) hoặc sao chép-dán khi có hàng mới được thêm vào tập dữ liệu.
- Nếu bạn chèn một hàng vào phạm vi hiện có, bạn có thể cần sao chép-dán công thức từ ô phía trên vào ô kế bên trong hàng mới chèn.
Tôi có một công thức mảng để đếm số lần xuất hiện trong mỗi dòng như một phương án thay thế cho Countif trên trong Google Sheets.
Công thức có thể có vẻ phức tạp, nhưng bạn có thể quên công thức và logic và sử dụng nó ngay lập tức.
Đối với những ai quan tâm, tôi sẽ giải thích công thức dưới đây.
Công thức mảng để lấy số lần xuất hiện của các giá trị trong mỗi dòng
Công thức trong ô C2:
=ArrayFormula(array_constrain(sort({COUNTIFS(index(sort({B2:B,row(B2:B)},2,0),0,1),index(sort({B2:B,row(B2:B)},2,0),0,1),ROW(A2:A),"<="&ROW(A2:A)),sort({B2:B,row(B2:B)},2,0)},3,1),match(2,1/(B1:B<>""))-row(B1),1))
Logic của công thức: Đảo ngược Running Count
Cập nhật: Tôi có một công thức đơn giản mới để thay thế công thức trên. Bạn có thể tìm thấy nó ở đây – Reverse Running Count Simplified trong Google Sheets.
Để giúp bạn hiểu logic của công thức kết hợp trên, trả về số lần xuất hiện của các giá trị trong mỗi dòng trên Google Sheets, bạn phải xem đầu ra của running count này.
Để giải thích, tôi đã đánh dấu running count của tên “Florence”.
Nếu chúng ta có thể đảo ngược running count, chúng tôi sẽ nhận được số lần xuất hiện của tên ‘Florance’ giống như Countif làm (vui lòng tham khảo Screenshot # 1) như 3, 2, 1 thay vì 1, 2, 3.
Đảo ngược running count là logic mà tôi đã sử dụng trong công thức mảng của mình để trả về số lần xuất hiện trong mỗi dòng.
Hãy xem cách đảo ngược công thức running count trong Google Sheets trong phần giải thích công thức dưới đây.
Giải thích – Công thức này đếm số lần xuất hiện trong mỗi dòng như thế nào?
Tôi sẽ chỉ sử dụng một phạm vi giới hạn trong phần giải thích này. Ý tôi là phạm vi sẽ là một phạm vi đóng, tức là B2:B9 thay vì B2:B.
Sau khi hoàn thành, chúng ta có thể mở tham chiếu. Có ba bước được thực hiện / tuân thủ trong phần giải thích này. Chúng là:
- Đảo ngược Dữ liệu từ Dưới lên Trên.
- Running Count của Cột Đảo ngược.
- Đảo ngược Dữ liệu.
Đảo ngược Dữ liệu từ Dưới lên Trên
Trước tiên, tạo một mảng hai cột bằng cách sử dụng Dấu ngoặc nhọn. Cột đầu tiên chứa tên hiện có trong B2:B9 và cột thứ hai chứa số hàng từ 2 đến 9.
=ArrayFormula({B2:B9,row(B2:B9)})
Sau đó, sắp xếp cột thứ hai theo thứ tự giảm dần. Đó là điều mà công thức dưới đây thực hiện.
Đây là công thức mà tôi đã sử dụng để đảo ngược dữ liệu từ dưới lên trên (xóa ArrayFormula. Không cần thiết với SORT).
==sort({B2:B9,row(B2:B9)},2,0)
Điều này đã được giới thiệu từ trước trong hướng dẫn trước của tôi – Cách Đảo Ngược Một Cột trong Google Sheets – Cột Hữu hạn và Vô hạn.
Tìm Running Count của Cột Đảo ngược
Gõ công thức này trong ô F2 để trả về running count của các tên đảo ngược trong cột G.
=ARRAYFORMULA(COUNTIFS(G2:G9,G2:G9,ROW(A2:A9),"<="&ROW(A2:A9)))
Lưu ý: Running count đã được giải thích ở đây – Ví dụ Công thức Running Count trong Google Sheets.
Bạn có thể thay thế G2:G9 (hai lần) trong công thức bằng công thức 1. Công thức 1 trả về hai cột trong phạm vi G2:H9, phải không? Nhưng chúng ta chỉ muốn giá trị trong G2:G9.
Vì vậy, bằng cách sử dụng Index, chúng ta có thể trích xuất chỉ cột đầu tiên từ kết quả của công thức 1.
Công thức chung: =index(formula 1,0,1)
Đây là công thức.
=index(sort({B2:B9,row(B2:B9)},2,0),0,1)
Chúng ta có thể sửa đổi công thức trong ô F2 như sau.
=ARRAYFORMULA(COUNTIFS(index(sort({B2:B9,row(B2:B9)},2,0),0,1),index(sort({B2:B9,row(B2:B9)},2,0),0,1),ROW(A2:A9),"<="&ROW(A2:A9)))
Bây giờ hãy kết hợp hai công thức 1 và 2 ở trên. Nó sẽ như sau:
={formula 1,formula 2}
Đây là công thức kết hợp trong ô F2.
={ARRAYFORMULA(COUNTIFS(index(sort({B2:B9,row(B2:B9)},2,0),0,1),index(sort({B2:B9,row(B2:B9)},2,0),0,1),ROW(A2:A9),"<="&ROW(A2:A9))),sort({B2:B9,row(B2:B9)},2,0)}
Đừng quên loại bỏ công thức trong ô G2 để mở rộng đầu ra của công thức này thành cột G2:H.
Sau khi áp dụng công thức trên, chúng ta sẽ có dữ liệu như trong bức ảnh 4 ở trên.
Bây giờ chúng ta chỉ cần đảo ngược dữ liệu và loại bỏ cột thứ hai và thứ ba (cột G và H). Như vậy chúng ta sẽ có số lần xuất hiện của các tên trong mỗi dòng trên Google Sheets.
Đảo ngược Dữ liệu
Để đảo ngược, hãy sắp xếp cột thứ ba chứa số hàng (trong đầu ra của công thức F2) theo thứ tự tăng dần.
Công thức đảo ngược và kết quả của nó như sau:
=sort(Formula 3,3,1)
Đây là công thức đảo ngược và đầu ra của nó.
=sort({ARRAYFORMULA(COUNTIFS(index(sort({B2:B9,row(B2:B9)},2,0),0,1),index(sort({B2:B9,row(B2:B9)},2,0),0,1),ROW(A2:A9),"<="&ROW(A2:A9))),sort({B2:B9,row(B2:B9)},2,0)},3,1)
Chúng ta chỉ muốn cột đầu tiên F2:F9, trả về số lần xuất hiện trong mỗi dòng, phải không?
Bước cuối cùng:
Đầu tiên, hãy mở các phạm vi trong công thức. Để làm điều đó, thay đổi B2:B9 thành B2:B và A2:A9 thành A2:A.
={ARRAYFORMULA(COUNTIFS(index(sort({B2:B,row(B2:B)},2,0),0,1),index(sort({B2:B,row(B2:B)},2,0),0,1),ROW(A2:A),"<="&ROW(A2:A))),sort({B2:B,row(B2:B)},2,0)}
Đây là công thức cuối cùng trong ô C2:
=Array_Constrain(sort({ARRAYFORMULA(COUNTIFS(index(sort({B2:B,row(B2:B)},2,0),0,1),index(sort({B2:B,row(B2:B)},2,0),0,1),ROW(A2:A),"<="&ROW(A2:A))),sort({B2:B,row(B2:B)},2,0)},3,1),ArrayFormula(match(2,1/(B1:B<>""))-row(B1)),1)
Bạn có thể loại bỏ hai hàm ArrayFormula bên trong và chỉ sử dụng một hàm ở đầu.
Đó là tất cả.
Hy vọng, tôi đã giải thích rõ cách lấy số lần xuất hiện trong mỗi dòng bằng công thức mảng trong Google Sheets.
Cảm ơn đã đọc!
