Hàm chia quintile 5 (ngũ phân vị)

[thachneumc]

Em chào anh chị,
Anh chị cho em hỏi hàm để tính ra quintile như cột thứ 3 ạ. (sẽ chia đều nhân viên ra làm 5 nhóm, dựa vào số lượng hợp đồng mà xếp vào các nhóm ạ)
Em xin cảm ơn.

 

[HieuCD]

Em chào anh chị,
Anh chị cho em hỏi hàm để tính ra quintile như cột thứ 3 ạ. (sẽ chia đều nhân viên ra làm 5 nhóm, dựa vào số lượng hợp đồng mà xếp vào các nhóm ạ)
Em xin cảm ơn.
View attachment 297090

Ngũ vị là gì vậy, Bạn có phải là người Hoa?

 

[thachneumc]

Ngũ vị là gì vậy, Bạn có phải là người Hoa?

Không bác ạ, từ ngữ chuyên môn của toán học, em nhớ nhầm một chút, em cũng mới tiếp xúc từ này được mấy tháng .
Đại loại là sẽ chia 1 cột thành 5 phần bằng nhau về số lượng sau đó đánh số theo thứ tự rank.
ví dụ: có dữ liệu rank từ 1-100, rank 1-20 sẽ chia vào nhóm 1, rank 21-40 chia vào nhóm 2,..., rank 81-100 chia vào nhóm 5 ạ.

 

[bebo021999]

Một cách chia theo giá trị, phân bổ vào khoảng 20%,40%,...,100%
B2:B17 sắp xếp bất kỳ:
C2

=MATCH(PERCENTRANK($B$2:$B$17,B2),{1,0.8,0.6,0.4,0.2},-1)

 

[thachneumc]

=MATCH(PERCENTRANK($B$2:$B$17,B2),{1,0.8,0.6,0.4,0.2},-1)
[/QUOTE]
không được bác ạ, em cũng thử công thức này rồi, em sẽ ra kết quả như bên dưới, không thỏa mãn điều kiện số lượng người các nhóm bằng nhau. nhóm 5 và 4 có 4 người, nhóm 3 chỉ có 2 người. các nhóm chỉ lệch nhau 1 người là tối đa thôi ạ.
 [ATTACH type="full"]297094[/ATTACH]

 

[VetMini]

Quintile chỉ có 5 trị.

 

[LmoovoenX]

Em chào anh chị,
Anh chị cho em hỏi hàm để tính ra quintile như cột thứ 3 ạ. (sẽ chia đều nhân viên ra làm 5 nhóm, dựa vào số lượng hợp đồng mà xếp vào các nhóm ạ)
Em xin cảm ơn.

Mình chưa kiểm tra hết các trường hợp, bạn thử và kiểm tra thêm nhé

=INT(SUMPRODUCT(--((B2+ROW(B2)/100)<(B$2:B$22+ROW(B$2:B$22)/100)))/ROWS(B2:B22)*5)+1

 

[VetMini]

Muốn biết trị thuộc về quintile nào (1~5) thì dùng công thức PERCENTRANK như bài #4 là đúng rồi.
Vấn đề cần tính thêm là xác định Exclude và Include thôi.

 

[LmoovoenX]

Muốn biết trị thuộc về quintile nào (1~5) thì dùng công thức PERCENTRANK như bài #4 là đúng rồi.
Vấn đề cần tính thêm là xác định Exclude và Include thôi.

Cho em hỏi tí. Nếu dữ liệu là phân phối chuẩn thì khi chia như trên, các nhóm sẽ có số lượng tương đương nhau. Nhưng nếu dữ liệu không theo phân phối chuẩn thì các nhóm có còn tương đương không, hay chỉ cần số liệu đủ lớn là được.

Bôi đậm: cái này em đoán thôi, không chắc chắn lắm.

 

[thachneumc]

Mình chưa kiểm tra hết các trường hợp, bạn thử và kiểm tra thêm nhé

=INT(SUMPRODUCT(--((B2+ROW(B2)/100)<(B$2:B$22+ROW(B$2:B$22)/100)))/ROWS(B2:B22)*5)+1

cái này lại chia thành 4 rồi bác ạ.
Em có nhặt được công thức này trên mạng và khá hợp với nhu cầu của em, mọi người có thể tham khảo ạ:
=ROUNDUP(RANK.AVG(B2,$B$2:$B$17,0)/((COUNT($B$2:$B$17)+1)/5),0)

Bài đã được tự động gộp: 27/11/23

Cho em hỏi tí. Nếu dữ liệu là phân phối chuẩn thì khi chia như trên, các nhóm sẽ có số lượng tương đương nhau. Nhưng nếu dữ liệu không theo phân phối chuẩn thì các nhóm có còn tương đương không, hay chỉ cần số liệu đủ lớn là được.

Bôi đậm: cái này em đoán thôi, không chắc chắn lắm.

Em không thực sự hiểu ý của bác lắm.
Số lượng trong các nhóm sẽ tương đương nhau, trường hợp chia không hết cho sẽ phân bổ phần thừa đấy vào 1 trong 5 nhóm, số liệu càng lớn thì phần thừa đấy giá trị dùng để phân tích càng thấp, ta có thể tạm thời bỏ qua ạ.

 

[Phuocam]

Sau khi test nhiều lần, thấy công thức bài 7, chênh lệch giữa các nhóm chỉ 1 đơn vị.

 

[VetMini]

Cho em hỏi tí. Nếu dữ liệu là phân phối chuẩn thì khi chia như trên, các nhóm sẽ có số lượng tương đương nhau. Nhưng nếu dữ liệu không theo phân phối chuẩn thì các nhóm có còn tương đương không, hay chỉ cần số liệu đủ lớn là được.
...

Định nghĩa: quintile có nghĩa là 5 bậc - tiếng Latin quin là 5.
Để xác định n-tile, người ta sort mảng dữ liệu từ lớn đến nhỏ và ngắt ra làm 5 phần. Có thể coi như mỗi phần chứa 20% số phần tử trong mảng.
Một số nằm rank quintile 1 có nghĩa là nó lớn hơn các trị trong 4 phần còn lại. Tức là nó lớn hơn 80% số trị trong mảng. Nói theo Excel thì percentrank của nó sẽ là một côn số ở khoảng giữa 80% và 100%.
Tương tự, một số nằm rank quintile 4 có nghĩa là nó lớn hơn các trị trong phần cuối. Tức là lớn hơn 20% số trị trong mảng, và nhỏ hơn 60% còn lại. Nói theo Excel thì percentrank của nó là giữa 20% và 40%.

Chuẩn: tôi không rõ bạn muốn
Rank chỉ có ý nghĩa khi các trị trong mảng không cách nhau xa quá.
Thường được dùng để tính hạng/trình độ học sinh.
Nếu các trị trong mảng chênh lệch rất nhiều (như giá nhà) thì gần như thống kê này chả đúc kết được gì. Và người ta phải dùng loại thống kê khác, hoặc dùng box graph để loại các trị quá cao và quá thấp (outliers)

 

[cantl]

Môn này trí tuệ quá. Mỗi công thức là một kết quả khác nhau.

 

[VetMini]

Môn này trí tuệ quá. Mỗi công thức là một kết quả khác nhau.

Xác suất thống kê là một môn toán nghiêm chỉnh. Ngày xưa, thời của tôi, ít nhất tới lớp 12 mới thấy môn này. Đương nhiên nó phải có dính líu "trí tuệ".

Tôi có nhắc nhở 2 điều:
1. Quintile có 5 trị
2. Exclude và Include.

Điều 1: dùng hàm Percentile để tính ra 4 trị (4 trị này chia khoảng 0~100 ra thành 5 phần)
=Percenttile(array, { .8, .6, .4, .2 })
sẽ cho ra 4 trị giữa.
Các phần tử của array lớn hơn trị thứ nhất là các phần tử được liệt vào quintile 1.
Các phần tử của array nhỏ hơn trị thứ nhất và lớn hơn trị thứ hai sẽ được liệt vào quintile 2
(điều kiện >= trị Min và <= trị Max là tự hiểu ngầm)
Lô gic rõ rệt. Bạn cứ dựa vào đó mà test. Công thức nào cho ra kết quả khác là công thức sai.

Điều 2: Bình thường, ta chỉ dùng Include. Nếu dùng Exclude thì điều kiện là > Min và
< Max (không có dấu bằng).

 

[LmoovoenX]

cái này lại chia thành 4 rồi bác ạ.
Em có nhặt được công thức này trên mạng và khá hợp với nhu cầu của em, mọi người có thể tham khảo ạ:

Sao lại là 4 nhỉ, mình lấy rows()/5, tức là 5 phần mà.
Yêu cầu của bạn là mỗi nhóm chênh không quá 1 người. Cái này không đúng lắm với cách chia theo quintile. Vì khi đó sẽ chia các giá trị giống nhau (chính xác hơn là gần bằng nhau) trong cùng 1 khoảng. Tùy dữ liệu mà có thể chia đều, hoặc không.

 

[thachneumc]

Sao lại là 4 nhỉ, mình lấy rows()/5, tức là 5 phần mà.
Yêu cầu của bạn là mỗi nhóm chênh không quá 1 người. Cái này không đúng lắm với cách chia theo quintile. Vì khi đó sẽ chia các giá trị giống nhau (chính xác hơn là gần bằng nhau) trong cùng 1 khoảng. Tùy dữ liệu mà có thể chia đều, hoặc không.

Em cũng không hiểu sao em chèn công thức vào bảng của em lại nhảy ra 4 quintile mà không phải là 5 nữa.
Việc xác định quintile này em dùng trong công việc để đánh giá năng suất làm việc của nhân sự, em thì cũng gà mờ toán vs excel lắm, vào được người cũ chỉ dạy nên làm theo thôi ạ.
Ngoài ra, em thấy hàm nào cũng có nhược điểm khi gặp quá nhiều giá trị = nhau, dẫn đến việc phân phối vào các nhóm không đồng đều.
Khi mẫu lớn, có ít giá trị trùng lặp hơn thì trường hợp này ít xảy ra hơn ạ.

Bài đã được tự động gộp: 27/11/23

Sau khi test nhiều lần, thấy công thức bài 7, chênh lệch giữa các nhóm chỉ 1 đơn vị.

cảm ơn thím đã tạo bảng tính của cả 3 công thức. tại em là người đưa ra đầu bài nên em biết mình muốn kết quả như nào nhất,
kết quả em muốn nhất là số lượng người trong các nhóm bằng nhau nhất có thể, nó sẽ không đúng về mặt thống kê phân tích khi có thể có trường hợp 2 ông có giá trị 20 thuộc quintile 5, 2 ông cũng cùng có giá trị 20 thuộc quintile 4.
Nhưng trước giờ công ty em cứ tính theo thế để xác định năng suất của nhân viên ạ.

 

[LmoovoenX]

@thachneumc dễ hiểu thôi, dữ liệu bạn đến có ô 17, trong công thức lại để đến ô 22.

 

[VetMini]

Cái mà thớt muốn không thuộc về toán thống kê.
Nó chỉ là một quy trình xếp hạng của công ty ấy.

Theo quy trình này thì:
- Sắp xếp số
- Phân ra làm 5 ngăn với số phần tử bằng nhau
- hết
Quy trình này không cho biết cách giải quyết các trị bằng nhau nhưng phải xếp khác ngăn vì hết chỗ.

Tôi vốn không ưa việc chuyện gì cũng VBA, nhưng nhận xét cá nhân thì cái này dùng hàm tự tạo hoặc sub cho xong, khỏi lộn xộn chuyện công thức tùy thuộc vào phương sai dữ liệu.

Nếu dùng phiên bản 365 thì các hàm chuyên mảng có thể giúp dễ dàng.

 

[HieuCD]

Tứ phân vị, ngũ phân vị, thập phân vị . . . là việc chia các phần tử được xếp thứ tự thành các nhóm có số phần tử bằng nhau
Việc phân chia nầy không lệ thuộc vào phương sai và 2 phần tử có cùng giá trị ( số hoặc chuỗi ) có thể nằm ở 2 nhóm
Tùy theo quy ước cách tính về chặn trên và chặng dưới số lượng từng nhóm, các phần mềm phân tích thống kê khác nhau có thể trả về kết quả khác nhau. Đặc biệt các thầy cô giảng Thống kê có thể có quy ước cách tính khác nhau tí xíu.

 

[VetMini]

Tứ phân vị, ngũ phân vị, thập phân vị . . . là việc chia các phần tử được xếp thứ tự thành các nhóm có số phần tử bằng nhau
Việc phân chia nầy không lệ thuộc vào phương sai và 2 phần tử có cùng giá trị ( số hoặc chuỗi ) có thể nằm ở 2 nhóm
Tùy theo quy ước cách tính về chặn trên và chặng dưới số lượng từng nhóm, các phần mềm phân tích thống kê khác nhau có thể trả về kết quả khác nhau. Đặc biệt các thầy cô giảng Thống kê có thể có quy ước cách tính khác nhau tí xíu.

Thớt có nói rằng cái quy trình của mình do người trước để lại.
Tôi gần như chắc chắn rằng "người trước" ấy dùng toán thống kê để tính trước, sau đó chỉnh lại bằng tay.