Trong bài viết này, chúng ta sẽ tìm hiểu cách tìm giá trị lớn nhất trong mỗi nhóm sử dụng Google Spreadsheets. Điều này liên quan đến việc xếp hạng. Nhưng để tìm n giá trị lớn nhất trong mỗi nhóm, các hàm RANK, MAX, MAXIFS hoặc LARGE không đáp ứng được yêu cầu. Nhưng đừng lo, tôi đã tìm ra một công thức cho bạn!
Tôi kết hợp sử dụng các hàm QUERY, SORT, MATCH, LET, CHOOSECOLS và ROW. Đây có thể là lần đầu tiên bạn gặp phải một công thức như vậy.
Với công thức của tôi, bạn có thể tìm giá trị lớn nhất trong mỗi nhóm trên Google Sheets.
Hãy xem ví dụ dưới đây.
Tìm giá trị lớn nhất trong Mỗi nhóm trên Google Sheets
Dưới đây là công thức mà tôi đã sử dụng trong ô D2 trong ví dụ trên. Nó trả về hai giá trị lớn nhất của mỗi nhóm.
=LET( sorted, SORT(A2:B,1,true,2,false), ARRAYFORMULA( QUERY( HSTACK( sorted, IFERROR(ROW(A2:A)- MATCH(CHOOSECOLS(sorted,1),CHOOSECOLS(sorted,1),0)) ),"Select Col1,Col2 where Col3<3") ) )Lưu ý: Đây là phiên bản đã cập nhật. Tôi đã sử dụng LET để cải thiện hiệu suất bằng cách tránh các tính toán lặp lại và sử dụng HSTACK thay vì dấu ngoặc nhọn để xếp dữ liệu theo chiều ngang.
Lần này, tôi sẽ không giải thích logic của công thức này. Thay vào đó, tôi sẽ cung cấp cho bạn đầy đủ thông tin về cách sử dụng công thức này trên bảng tính của bạn.
Tôi đã phân loại các mẹo dưới các tiêu đề phụ khác nhau dưới đây.
Cách thay đổi số hàng chứa 2 giá trị lớn nhất trong mỗi nhóm thành số hàng chứa N giá trị
Xem khái niệm “n” trong công thức Google Sheets này. Nghĩa là làm thế nào để thay đổi 2 giá trị lớn nhất thành 3 giá trị; hoặc thành một giá trị do người dùng xác định?
Hãy xem phần cuối của công thức. Đó là câu lệnh “Where” trong truy vấn.
Where Col3<3Ở đây, <3 cho biết 2 giá trị/hàng lớn nhất trong mỗi nhóm.
Nếu nó là <4, đó chứng tỏ có 3 giá trị lớn nhất trong mỗi nhóm, <5 có nghĩa là có 4 giá trị lớn nhất trong mỗi nhóm, và cứ tiếp tục như vậy.
Bạn có thắc mắc câu hỏi dưới đây, đúng không?
Tôi có nhiều hơn hai cột. Làm thế nào để điều chỉnh công thức của bạn?
Đó là một câu hỏi hay đấy! Dưới đây là một số mẹo để thực hiện điều đó.
Làm thế nào để thích nghi với nhiều cột trong công thức của tôi để tìm số hàng lớn nhất trong nhóm
Bạn chỉ cần chú ý đến phần công thức SORT. Dưới đây là một tập dữ liệu gồm bốn cột.
Bạn có thể thay thế công thức SORT trong công thức của tôi dựa trên những gợi ý dưới đây.
Trước đó, chúng ta đã áp dụng công thức trong một tập dữ liệu hai cột. Đó là:
SORT(A2:B,1,true,2,false)Công thức SORT phải được sửa như sau trong tập dữ liệu bốn cột mới của chúng ta.
SORT(A2:D,1,true,4,false)Điều đó có nghĩa là trong công thức SORT, chúng ta sẽ thay đổi phạm vi dữ liệu và thay đổi cột sắp xếp. Bạn cần:
- Sắp xếp cột tên (nhóm) theo thứ tự tăng dần.
- Sắp xếp cột giá trị chứa n giá trị lớn nhất theo thứ tự giảm dần.
Sau đó, bạn chỉ cần điều chỉnh một chút từ phần câu lệnh Select của truy vấn (phần cuối của công thức).
Trong tập dữ liệu hai cột của tôi, câu lệnh Select được sử dụng như sau:
Select Col1,Col2 where Col3<3Trong tập dữ liệu bốn cột mới của chúng ta, nó nên có dạng như sau:
Select Col1,Col2,Col3,Col4 where Col5<3Có điểm khác biệt gì?
Ở đây, số cột là bốn. Vì vậy, nó sẽ được phản ánh trong truy vấn.
Vậy tại sao tôi lại sử dụng Col5<3?
Tôi đã mô tả vai trò của <3 ở đâu đó ở đầu bài viết này. Nó xác định các giá trị n.
Vậy tại sao tôi lại sử dụng Col5? Chúng ta chỉ có bốn cột, phải không?
Công thức của tôi, tìm giá trị lớn nhất trong mỗi nhóm, tạo ra một cột bổ sung.
Cho nên trong tập dữ liệu gồm bốn cột, số này nên là 5.
Nếu bạn nhìn vào công thức cho tập dữ liệu gồm bốn cột, bạn sẽ thấy tôi đã sửa công thức SORT và phần câu lệnh Select như trên.
=LET( sorted, SORT(A2:D,1,true,4,false), ARRAYFORMULA( QUERY( HSTACK( sorted, IFERROR(ROW(A2:A)- MATCH(CHOOSECOLS(sorted,1),CHOOSECOLS(sorted,1),0)) ),"Select Col1,Col2,Col3,Col4 where Col5<3") ) )Lưu ý: Vì đây là phiên bản mới, hãy bỏ qua công thức trên thanh công cụ ở hình ảnh dưới đây.
Tương tự, bạn có thể điều chỉnh công thức này cho bất kỳ số cột nào. Hãy làm theo hướng dẫn này để tìm giá trị lớn nhất trong mỗi nhóm trên Google Sheets.
Đó là tất cả những bí quyết tuyệt vời mà tôi muốn chia sẻ với bạn về cách tìm giá trị lớn nhất trong mỗi nhóm trên Google Sheets. Hy vọng bạn tìm thấy ứng dụng của nó trong công việc của mình!
Nếu bạn cần thêm thông tin, hãy truy cập Crawlan.com để biết thêm chi tiết!
