Để tính giá trị trung bình của N phần trăm cao nhất trong Google Sheets, bạn có thể sử dụng hàm PERCENTILE như tiêu chí trong hàm AVERAGEIF.
Hơn nữa, khi bạn muốn tính giá trị trung bình của N phần trăm cao nhất dựa trên điều kiện, sử dụng hàm PERCENTILE kết hợp với hàm AVERAGEIFS.
Bạn có thể thay thế cả hai hàm AVERAGEIF và AVERAGEIFS bằng sự kết hợp của AVERAGE + FILTER. Hãy xem tất cả các kết hợp thú vị bên dưới.
Để giải thích rõ hơn, chúng ta cần một dữ liệu mẫu. Hãy bắt đầu với một ví dụ cực kỳ cơ bản.
Cách tính giá trị trung bình của N phần trăm cao nhất mà không có điều kiện
Sử dụng dữ liệu mẫu trên, tôi muốn tìm giá trị trung bình của 20% phần trăm cao nhất. Tôi đã đánh dấu những giá trị nằm trên mức phần trăm cao nhất 20%. Hãy xem giá trị trung bình trên ảnh chụp màn hình. Hãy xem cách tính toán bằng công thức như thế nào.
Các bước:
Bằng cách sử dụng hàm PERCENTILE, chúng ta có thể tìm giá trị tại một phần trăm nhất định của một loạt giá trị. Ví dụ:
=percentile(A2:A11,20%)
Kết quả: 280
Giá trị 280 này là giá trị ở phần trăm nhất định (20%) của phạm vi/lượng đã chọn. Chúng ta không thể sử dụng giá trị này như >280 hoặc <280 để tìm giá trị cao nhất 20% trong khoảng giá trị.
Lý do là trong tính toán PERCENTILE, các giá trị được lấy từ thấp đến cao (thứ tự tăng dần), không ngược lại.
Vì vậy, để lấy giá trị cao nhất 20% trong các giá trị, bạn phải sử dụng công thức PERCENTILE như sau.
=percentile(A2:A11,(100%-20%))
hoặc
=percentile(A2:A11,(80%))
Sau đó, sử dụng nó trong công thức FILTER như sau.
=filter(A2:A11,A2:A11>percentile(A2:A11,(100%-20%)))
Đơn giản chỉ cần bọc công thức này bằng hàm AVERAGE để có được giá trị trung bình của 20% phần trăm cao nhất trong một phạm vi.
=average(filter(A2:A11,A2:A11>percentile(A2:A11,(100%-20%))))
Thực ra, chúng ta không cần công thức FILTER ở giữa để tìm giá trị trung bình của N phần trăm cao nhất (ở đây là 20%) của các giá trị. Bằng cách sử dụng hàm FILTER, tôi chỉ muốn chỉ cho bạn cách lọc giá trị phần trăm cao nhất.
Dưới đây là công thức thay thế cho công thức trên để tìm giá trị trung bình của N phần trăm cao nhất, ở đây là 20%, trong Google Sheets.
=averageif(A2:A11,”>”&percentile(A2:A11,(100%-20%)))
Tính giá trị trung bình của N% giá trị có điều kiện trong Google Sheets
Dữ liệu mẫu:
Ở đây, tôi muốn tính giá trị trung bình của N% (20%) giá trị bán hàng cao nhất từ khu vực “Nam”.
Trong tình huống này, chúng ta cũng có thể sử dụng công thức dựa trên FILTER hoặc AVERAGEIFS (không phải AVERAGEIF)
Dưới đây là cả hai giải pháp. Bạn có thể so sánh hai công thức này với công thức trước để hiểu cách tôi đã tích hợp điều kiện vào chúng.
Công thức dựa trên AVERAGEIFS:
=averageifs(D2:D,C2:C,”Nam”,D2:D,”>”&percentile(D2:D,(100%-20%)))
Công thức dựa trên FILTER:
=average(filter(D2:D,C2:C=”Nam”,D2:D>percentile(D2:D,(100%-20%))))
Đó là tất cả cho bây giờ. Chúc bạn vui vẻ!
Xem thêm tại Crawlan.com để biết thêm nhiều thông tin hữu ích nhé!
