Trong bài viết này, tôi sẽ chia sẻ với bạn cách tổng hợp giá trị lớn nhất của ‘n’ trong từng nhóm trong Google Sheets. Công thức Query cùng với công thức mảng (array formula) tính tổng giá trị lớn nhất này sẽ giúp bạn rất nhiều trong thực tế.
Cách Tính Tổng Hợp Giá Trị Lớn Nhất Theo Nhóm trong Google Sheets
Đầu tiên, chúng ta cần có dữ liệu mẫu. Ví dụ, tôi sẽ lấy điểm số của hai học sinh trong năm môn học để minh họa cho cách tính toán.
Trước tiên, tôi sẽ cung cấp cho bạn một công thức hoạt động trong trường hợp dữ liệu đã được sắp xếp. Đối với dữ liệu chưa được sắp xếp, có công thức khác, nhưng cả hai công thức này gần như giống nhau.
Tổng Hợp Giá Trị Cao Nhất Theo Nhóm trong Khoảng Đã Sắp Xếp
Dữ liệu mẫu ở trên đã được sắp xếp. Bạn có thể thấy tên được sắp xếp theo cột A. Nhưng chúng ta muốn cột B cũng được sắp xếp theo thứ tự giảm dần.
Vì vậy, trước tiên hãy sắp xếp dữ liệu một cách đúng đắn. Để làm điều đó, hãy chọn mảng A2:C11. Sau đó, vào menu Data > Sắp xếp khoảng. Tiến hành sắp xếp dữ liệu như hình dưới đây.
Ở đây, tôi lấy số 3 làm ‘n’.
Query là hàm quan trọng giúp tôi tính tổng giá trị lớn nhất trong từng nhóm. Tuy nhiên, Query không thể làm điều đó một mình.
Chúng ta cần tạo một cột ảo dùng để tính số lần xuất hiện của từng nhóm.
=ARRAYFORMULA(if(len(A2:A),COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A))))Đây là công thức tạo ra cột ảo này. Chúng ta có thể sử dụng nó để tạo một mảng gồm bốn cột như dưới đây.
={A2:C,ARRAYFORMULA(if(len(A2:A),COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A))))}Đây là một bảng dữ liệu gồm bốn cột, trong đó “Tên” nằm ở cột đầu tiên, “Điểm trên 100” nằm ở cột thứ hai, “Môn học” nằm ở cột thứ ba và cột “Cột trợ giúp” nằm ở cột thứ tư.
Sử dụng toán tử so sánh “<=” để giới hạn số hàng là ba trong mỗi nhóm như dưới đây. Điều này có thể thực hiện được nhờ cột chạy bên cạnh.
=Query({A2:C,ARRAYFORMULA(if(len(A2:A),COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A))))},"Select * where Col4 <=3")Sử dụng Query khác, chúng ta có thể tổng hợp giá trị lớn nhất trong mỗi nhóm.
Công thức cuối cùng:
=Query(Query({A2:C,ARRAYFORMULA(if(len(A2:A),COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A))))},"Select * where Col4 <=3"),"Select Col1, Sum(Col2) where Col1 is not null group by Col1")
Tổng Hợp Giá Trị Lớn Nhất Theo Nhóm trong Khoảng Chưa Được Sắp Xếp
Giả sử dữ liệu của tôi không được sắp xếp hoặc được nhập bằng cách sử dụng Importrange và thường xuyên cập nhật. Vì vậy, việc sắp xếp thủ công dữ liệu mỗi lần không phải là lựa chọn thông minh.
Tôi muốn một công thức mà tôi chỉ cần cài đặt một lần và quên đi, để tổng hợp giá trị lớn nhất theo nhóm trong Google Sheets dựa trên thứ tự dữ liệu.
Bạn có thể sử dụng công thức cuối cùng trên với những thay đổi sau trong một mảng chưa được sắp xếp.
Sử dụng công thức SORT sau để thay thế mảng A2:C trong công thức trên.
sort(A2:C,1,true,2,false)Sau đó, sử dụng công thức SORT sau để thay thế mảng A2:A bên trong COUNTIFS (chỉ thay thế hai tham chiếu đầu tiên).
sort(A2:A,1,true)Vì vậy, công thức cuối cùng để tổng hợp giá trị lớn nhất theo nhóm trong một mảng chưa được sắp xếp sẽ như sau.
=Query(Query({sort(A2:C,1,true,2,false),ARRAYFORMULA(if(len(A2:A),COUNTIFS(sort(A2:A,1,true),sort(A2:A,1,true),ROW(A2:A),"<="&ROW(A2:A))))},"Select * where Col4 <=3"),"Select Col1, Sum(Col2) where Col1 is not null group by Col1")
