Không có lối tắt để lọc hoặc lấy điểm hàng đầu n% từng nhóm trên Google Sheets.
Một người đọc của tôi, Lorraine, đã hỏi về khả năng lọc 10% điểm hàng đầu từng nhóm.
Theo như tôi biết, trong Google Sheets, không có lệnh hay chức năng tích hợp cho việc này. Tôi có một vài phương án khác. Vì vậy, tôi nghĩ đến việc viết bài hướng dẫn này.
Chúng ta có thể sử dụng kết hợp của bốn chức năng – Percentile, Filter, Vlookup và IF – cho mục đích đã nêu. Có bốn bước liên quan.
Trong bài viết trước đây của tôi, tôi đã đề cập đến bước đầu tiên. Tôi sẽ liên kết đến bài viết đó khi chúng ta bắt đầu viết công thức bên dưới.
Mà không cần chờ đợi, tôi sẽ đi đến các bước dưới đây.
Lấy điểm hàng đầu n% từng nhóm – Hướng dẫn từng bước
Dữ liệu mẫu (trong khoảng ô B1:C17) và Kết quả mong đợi (trong khoảng ô G7:H10):
Có hai nhóm trong mẫu của tôi, và chúng là “a” và “b”.
Trong kết quả của tôi, có ba điểm. Hai điểm từ nhóm đầu tiên và một điểm từ nhóm cuối cùng. Bởi vì trong ví dụ này, chúng tôi đang lọc các giá trị hàng đầu 10% từng nhóm.
Để lấy điểm hàng đầu n% từng nhóm trên Google Sheets, chúng ta nên tìm giá trị vị trí phần trăm k-th.
Điều đó có nghĩa là để lấy điểm hàng đầu 10%, trước tiên, chúng ta nên tìm giá trị vị trí phần trăm thứ 90. Chúng ta có thể sử dụng chức năng Percentile cho việc này. Đó là bước 1.
Dưới đây là bốn bước liên quan.
Giá trị vị trí phần trăm 90 của mỗi nhóm (Bước 1)
Chúng tôi đang viết công thức để lấy điểm hàng đầu n% từng nhóm trên Google Sheets. Hãy xem xét n = 10%.
Vì vậy điều chúng ta cần làm là tìm giá trị vị trí phần trăm thứ 90 từng nhóm.
Điều đó có nghĩa là các điểm trên giá trị vị trí phần trăm thứ 90 là điểm hàng đầu 10% trong mỗi nhóm.
Trong ô G2, lọc các mục bằng cách sử dụng công thức Unique sau đây.
=unique(B2:B)Trong ô H2, nhập công thức sau và kéo xuống H3 để lấy giá trị vị trí phần trăm 90 của mỗi mục.
=round(percentile(filter($C$2:$C,$B$2:$B=G2),90%))Để biết thêm chi tiết (giải thích công thức), bạn có thể đọc bài viết này – Tính phân vị cho mỗi nhóm trên Google Sheets.
Lưu ý:
Làm tròn phân vị là bắt buộc, và tôi đã lựa chọn điều đó trong công thức. Nhưng đôi khi nó có thể gây ra vấn đề nếu bạn có điểm thập phân.
Ví dụ, nếu vị trí phần trăm thứ 90 là 6,5 khi được làm tròn, nó sẽ trở thành 7. Vì vậy, nếu bạn không có điểm nào lớn hơn 7, công thức có thể không trả lại bất kỳ hàng nào từ nhóm đó.
Ví dụ:
Vị trí phần trăm thứ 90 của các giá trị 5, 5, 5, 5, 6,5, 6,5 và 6,5 sẽ là 6,5. Nếu bạn làm tròn giá trị vị trí phần trăm này, nó sẽ trở thành 7. Vì vậy, bạn sẽ không thể lọc các giá trị hàng đầu 10% từ nhóm này.
Điều đó có nghĩa rằng, nếu bạn có điểm thập phân, hãy làm tròn xuống hoặc không làm tròn giá trị vị trí phần trăm.
Tra cứu và gán giá trị vị trí phần trăm cho các nhóm (Bước 2)
Bước 2 là yếu tố chính trong việc lọc các giá trị hàng đầu n% từng nhóm.
Công thức trên trả về hai điểm trong khoảng ô H2:H3. Bằng công thức Mảng tra cứu Vlookup, chúng ta có thể gán các giá trị đó cho các mục.
Chèn công thức Vlookup sau đây vào ô D2.
=ArrayFormula(IFNA(vlookup(B2:B,G2:H3,2,0)))Vlookup tìm các mục (B2:B) trong cột đầu tiên của khoảng ô (G2:H3) và trả về các giá trị vị trí phần trăm thứ 90 từ cột thứ hai (H2:H3).
Bây giờ bạn có thể xem cột C và D trong ảnh chụp ở trên.
Như bạn có thể thấy, chúng tôi chỉ muốn lọc các hàng mà điểm lớn hơn hoặc bằng giá trị vị trí phần trăm tương ứng.
Chúng ta có thể áp dụng điều kiện này trong cột tiếp theo bằng một câu lệnh logic. Đó là bước tiếp theo.
Kiểm tra logic điểm và giá trị vị trí phần trăm (Bước 3)
Chúng ta có thể chèn câu lệnh IF sau đây cho mục đích đã nêu.
=ArrayFormula(if(C2:C="",,if(C2:C>=D2:D,true,false)))Công thức bước 3 này nên được đặt trong ô E2.
Công thức này làm gì?
Nó kiểm tra xem các điểm (C2:C) có lớn hơn hoặc bằng các giá trị vị trí phần trăm (D2:D) hay không. Nếu thỏa mãn, công thức trả về TRUE, ngược lại, trả về FALSE.
Bây giờ đến phần cuối cùng.
Lọc các giá trị TRUE để lấy điểm hàng đầu n% từng nhóm (Bước 4)
Vì chúng tôi đã hoàn thành công việc chuẩn bị sẵn, việc lấy các giá trị hàng đầu n% (ở đây là 10) từng nhóm giờ đơn giản.
Chỉ cần chèn công thức Lọc sau đây vào ô G8, và voila!
=filter(B2:C,E2:E=true)Công thức này lọc bảng (B2:C) nếu cột E2:E chứa TRUE.
Kết luận
Chướng ngại duy nhất khi kết hợp cả bốn bước trên thành một công thức duy nhất là công thức bước 1.
Chúng ta không thể biến nó thành công thức mảng.
Điều đó có nghĩa là chúng ta có thể lấy kết quả trong 2 bước.
Bước đầu tiên là, tất nhiên, công thức bước 1. Bước thứ hai là kết hợp công thức từ bước 2 đến 4.
Bạn có thể bỏ qua công thức từ bước 2 đến 4 và thay vào đó sử dụng công thức sau đây trong ô G8.
=filter(B2:C,if(C2:C="",,if(C2:C>=IFNA(vlookup(B2:B,G2:H3,2,0)),true,false))=true)Ghi chú bổ sung:
Trong dữ liệu mẫu trên, chỉ có hai nhóm. Vì vậy, công thức bước 1 chỉ trả về hai hàng trong khoảng ô G2:H3.
Nếu bạn có dữ liệu phát triển, bạn không thể dự đoán số lượng nhóm.
Vì vậy, tốt hơn hãy chèn công thức trên vào ô J2 và thay thế G2:H3 trong công thức bằng G2:H. Sau đó, kéo công thức H2 xuống dưới tùy thuộc vào số lượng nhóm.
Đó là tất cả về cách lấy điểm hàng đầu n% từng nhóm trên Google Sheets.
Cảm ơn vì đã đọc. Thật tuyệt!
