Bài toán xếp hạng (1 người xem)

[letuongqt]

Xin nhờ các anh em trên diễn đàn giúp đỡ vấn đề như sau : Tôi có 1 danh sách học sinh gồm nhiều lớp . Yêu cầu là xếp hạng học sinh của từng lớp . Xếp theo nguyên tắc sau :
- Xếp theo từng đơn vị lớp .
- Từng lớp xếp lần lượt hết loại "Giỏi" đến "Khá",đến "TB" ,đến "Yếu",...
- Nếu xãy ra đồng hạng , thì hạng kế tiếp phải công thêm số lượng hạng trùng (Ví dụ : 2 người đồng hạng 3 , thì hạng kế tiếp là hạng 5 ) .
Rất mong được anh em giúp đỡ . Cảm ơn rất nhiều .

 

[ndu96081631]

Xin nhờ các anh em trên diễn đàn giúp đỡ vấn đề như sau : Tôi có 1 danh sách học sinh gồm nhiều lớp . Yêu cầu là xếp hạng học sinh của từng lớp . Xếp theo nguyên tắc sau :
- Xếp theo từng đơn vị lớp .
- Từng lớp xếp lần lượt hết loại "Giỏi" đến "Khá",đến "TB" ,đến "Yếu",...
- Nếu xãy ra đồng hạng , thì hạng kế tiếp phải công thêm số lượng hạng trùng (Ví dụ : 2 người đồng hạng 3 , thì hạng kế tiếp là hạng 5 ) .
Rất mong được anh em giúp đỡ . Cảm ơn rất nhiều .

Tại cell F2 bạn gõ công thức:

=RANK($D2,$D$2:$D$20)

Cuối cùng là Sort dử liệu theo Cột Lớp ---> Rồi đến cột Kết quả xếp hạng

 

[letuongqt]

Cảm ơn sự góp ý của ndu96081631 ,nhưng có cách nào để nguyên bảng dữ liệu như vậy , khi fill xuống sẽ được kết quả không bạn ?

Tức là bảng dữ liệu vẫn như vậy không sort gì cả - Chỉ coppy công thức xuống thì được kết quả . Mong được giúp đỡ thêm .

 

[MinhNguyet_cntt]

Xếp hạng cho dữ liệu

chào bạn!Mình thử làm như thế này bạn xem có được không nhé!
- Vào Data/filter/Auto filter để lọc danh sách theo lớp
- Dùng hàm rank để xếp hàng cho học sinh lớp đó theo điểm xếp hạng
- Dùng hàm sort để sắp xếp danh sách tên hs theo thứ tự giảm dần của kết quả xếp hạng(hs xếp hạng từ 1,2...)và xếp loại từ giỏi,khá,tb,yếu
- Lặp lại quá trình trên với danh sách của từng lớp
- Sau đó kích chọn All ở cột Lớp để hiện toàn bộ danh sách đã xếp hạng

 

[letuongqt]

Bảng dữ liệu này có rất nhiều lớp , nếu làm thủ công như vậy thì rất lâu . Mặt khác bảng này là nguồn liên kết với rất nhiều sheet . Vì vậy, cái cần là khi công thức tự tính điểm xếp hạng và xếp loại (Cái này thì mình đã làm được) thì cột " kết quả xếp hạng" phải cho ra kết quả luôn (Cái này thì mình đang hỏi ở đây) . Rất cảm ơn .

 

[boyxin]

Bảng dữ liệu này có rất nhiều lớp , nếu làm thủ công như vậy thì rất lâu . Mặt khác bảng này là nguồn liên kết với rất nhiều sheet . Vì vậy, cái cần là khi công thức tự tính điểm xếp hạng và xếp loại (Cái này thì mình đã làm được) thì cột " kết quả xếp hạng" phải cho ra kết quả luôn (Cái này thì mình đang hỏi ở đây) . Rất cảm ơn .

bạn thử xài file này xem được không?
​

 

[letuongqt]

Cảm ơn anh boyxin rất nhiều , thật là tuyệt . Có cách nào sử dụng hàm của excel cho đơn giản hơn cái VBA này được không anh ?

Cảm ơn anh boyxin rất nhiều , thật là tuyệt . Có cách nào sử dụng hàm của excel cho đơn giản hơn cái VBA này được không anh ?

Dùng hàm của excel kết hợp cột phụ có thể giải quyết được không các anh ?

 

[boyxin]

Dùng hàm của excel kết hợp cột phụ có thể giải quyết được không các anh ?

Nếu chấp nhận mỗi lớp 1 cột phụ (dùng hẳn 1 sheet phụ) thì có thể giải quyết được vấn đề này
(như vậy file sẽ chạy chậm)
​

Làm cột phụ và sheet phụ như anh Boyxin hướng dẫn nhưng mò mẫm mãi vẫn không xong. Nhờ các anh giúp đỡ .

Tạm thời mình làm công thức xếp hạng với tối đa 10 lớp, mỗi lớp tối đa 50 HS
(càng nhiều lớp, mỗi lớp càng nhiều HS thì file chạy càng chậm)

bạn kiểm tra lại kết quả nhé​

 

[letuongqt]

Rất cảm ơn sự nhiệt tình và bái phục trình độ excel của anh Boyxin ! Tuy nhiên đối với lớp a1 thì đúng rồi , nhưng còn lớp a2, a3 ,..thì chưa đúng yêu cầu anh Boyxin ạ . Yêu cầu kết quả xếp hạng là :trong phạm vị của mỗi lớp ( Tức là mỗi lớp phải có hạng 1 , 2 , 3 ,...) . Ví dụ : lớp a1 : có 25 học viên thì kết quả từ 1 đến tối đa là 25 ; lớp a2 có 30 học viên thì hạng từ 1 đến tối đa là 30 ,... rất mong được anh giúp tiếp cho hoàn chỉnh . Xin cảm ơn .

 

[tigertiger]

Tạm thời mình làm công thức xếp hạng với tối đa 10 lớp, mỗi lớp tối đa 50 HS
(càng nhiều lớp, mỗi lớp càng nhiều HS thì file chạy càng chậm)

bạn kiểm tra lại kết quả nhé​

Sao không chỉ dùng Name không cần vùng phụ làm gì

Boyxin?

 

[boyxin]

Rất cảm ơn sự nhiệt tình và bái phục trình độ excel của anh Boyxin ! Tuy nhiên đối với lớp a1 thì đúng rồi , nhưng còn lớp a2, a3 ,..thì chưa đúng yêu cầu anh Boyxin ạ . Yêu cầu kết quả xếp hạng là :trong phạm vị của mỗi lớp ( Tức là mỗi lớp phải có hạng 1 , 2 , 3 ,...) . Ví dụ : lớp a1 : có 25 học viên thì kết quả từ 1 đến tối đa là 25 ; lớp a2 có 30 học viên thì hạng từ 1 đến tối đa là 30 ,... rất mong được anh giúp tiếp cho hoàn chỉnh . Xin cảm ơn .

Ý bạn là muốn như thế này à? Formula xep_hang01.rar
lại càng dễ hơn

​

Sao không chỉ dùng Name không cần vùng phụ làm gì

Boyxin?

Làm vội chưa nghĩ ra, tigertiger bỏ vùng phụ giúp luôn nhé

 

[tigertiger]

Làm vội chưa nghĩ ra, tigertiger bỏ vùng phụ giúp luôn nhé

Boyxin làm tốt chứ, sao không làm luôn

Giống bài xếp hạng bóng đá thôi - bài này boyxin đưa lên mà, nếu tôi k nhớ nhầm

 

[boyxin]

Boyxin làm tốt chứ, sao không làm luôn

Giống bài xếp hạng bóng đá thôi - bài này boyxin đưa lên mà, nếu tôi k nhớ nhầm

Hic, bài này khác bài xếp hạng bóng đá nhiều

Chiều ý tigertiger: Dùng 2 name, không cần dùng đến vùng phụ để xếp hạng

Theo quy tắc sau:

Yêu cầu kết quả xếp hạng là :trong phạm vị của mỗi lớp ( Tức là mỗi lớp phải có hạng 1 , 2 , 3 ,...) . Ví dụ : lớp a1 : có 25 học viên thì kết quả từ 1 đến tối đa là 25 ; lớp a2 có 30 học viên thì hạng từ 1 đến tối đa là 30 ,...

 

[letuongqt]

Như thế thì quá tốt rồi bác Boyxin ạ! cảm ơn bác nhiều nhé I sẵn đây bác giải thích rõ hơn co dòng công thức này - mấu chốt vấn đề nằm ở đây hả bác ?
=SUM(IF(Lop=Sheet1!$C9,IF(OFFSET(Lop,,1)>Sheet1!$D9,1,0),0))+1
tại sao phải $C9 ,$D9 ,... nhờ bác giải thích hộ để được học hỏi thêm .

 

[boyxin]

Yêu cầu kết quả xếp hạng là :trong phạm vị của mỗi lớp ( Tức là mỗi lớp phải có hạng 1 , 2 , 3 ,...) . Ví dụ : lớp a1 : có 25 học viên thì kết quả từ 1 đến tối đa là 25 ; lớp a2 có 30 học viên thì hạng từ 1 đến tối đa là 30

Chỉ dùng 1 công thức

F2 =SUM(IF(C$2:C$20=Sheet1!C2,IF(D$2:D$20>Sheet1!D2,1,0),0))+1

Kết thúc bằng Ctrl+Shift+Enter rồi copy (kéo fill) xuống

Thì khỏi cần name vẫn xếp loại đúng yêu cầu

Như thế thì quá tốt rồi bác Boyxin ạ! cảm ơn bác nhiều nhé I sẵn đây bác giải thích rõ hơn co dòng công thức này - mấu chốt vấn đề nằm ở đây hả bác ?
=SUM(IF(Lop=Sheet1!$C9,IF(OFFSET(Lop,,1)>Sheet1!$D9,1,0),0))+1
tại sao phải $C9 ,$D9 ,... nhờ bác giải thích hộ để được học hỏi thêm .

$C9 ,$D9 là để test kết quả ở cột khác, không phải ở cột F

 

[tigertiger]

Hic, bài này khác bài xếp hạng bóng đá nhiều

Chiều ý tigertiger: Dùng 2 name, không cần dùng đến vùng phụ để xếp hạng

Theo quy tắc sau:

Đó sáng sủa hơn nhiều,

Áp dụng bài bóng đá cũng được boyxin ah, áp dụng bài bóng đá còn được nhiều đ/k hơn nữa cơ?

Không rõ tác giả thế nào --> nhưng nếu điểm bằng nhau mà căn theo khá- giỏi -TB ... thì file boyxin chưa làm được, có phải không nhỉ?

thường thì giỏi - khá -TB ... phải theo điểm chứ nhỉ? hay còn cái khác?

 

[letuongqt]

Tại sao trong công thức trên không liên quan gì đến cột " Xếp loại" (cột E) vậy anh Boyxin? trong khi đó cột này vẫn phải bị ràng buộc khi xếp hạng theo yêu cầu của đề bài ( Tức là lần lượt Giỏi xếp trước , đến khá , đến TB,...) . Hiện tại bài của anh giúp thử lại thấy đúng tất cả , không biết vấn đề cột E không liên quan có ảnh hưởng gì đối với các trường hợp khác hay không ?

Sẵn đây trả lời ý của anh TigerTiger như sau : cột "điểm xếp hạng" là điểm quy đổi ra từ học lực và hạnh kiểm ( được dùng để xếp hạng , nghĩa là một học viên có thể điểm học lực cao nhưng điểm hạnh kiểm thấp thì "điểm xếp hạng" sau khi quy đổi vẫn có điểm thấp như thường ) - Cách tính này chỉ mang tính địa phương mà thôi .

 

[tigertiger]

Chỉ dùng 1 công thức

F2 =SUM(IF(C$2:C$20=Sheet1!C2,IF(D$2:D$20>Sheet1!D2,1,0),0))+1

Kết thúc bằng Ctrl+Shift+Enter rồi copy (kéo fill) xuống

Thì khỏi cần name vẫn xếp loại đúng yêu cầu

Nếu chỉ thế thì dùng SUMPRODUCT hơn boyxin ah khi đó k cần mảng ctrl+shift+enter nữa

cụ thể công thức như sau

=SUMPRODUCT(($C$2:$C$21=Sheet1!$C2)*($D$2:$D$21>=Sheet1!$D2))

tuy nhiên công thức này vẫn:

nhưng nếu điểm bằng nhau mà căn theo khá- giỏi -TB ... thì file boyxin chưa làm được, có phải không nhỉ?

Tại sao trong công thức trên không liên quan gì đến cột " Xếp loại" (cột E) vậy anh Boyxin? trong khi đó cột này vẫn phải bị ràng buộc khi xếp hạng theo yêu cầu của đề bài ( Tức là lần lượt Giỏi xếp trước , đến khá , đến TB,...) . Hiện tại bài của anh giúp thử lại thấy đúng tất cả , không biết vấn đề cột E không liên quan có ảnh hưởng gì đối với các trường hợp khác hay không ?

Có ảnh hưởng đó bạn cứ thử 1 trường hợp thấy ngay cho 2 điểm bằng nhau - 1 đồng chí vượt bậc sếp loại (chẳng hạn Khá và Giỏi) sẽ thấy ngay --> vị trí vẫn bằng sau --> CHƯA ĐÚNG
.

 

[tigertiger]

giải pháp mới - đầy đủ hơn

Tại sao trong công thức trên không liên quan gì đến cột " Xếp loại" (cột E) vậy anh Boyxin? trong khi đó cột này vẫn phải bị ràng buộc khi xếp hạng theo yêu cầu của đề bài ( Tức là lần lượt Giỏi xếp trước , đến khá , đến TB,...) . Hiện tại bài của anh giúp thử lại thấy đúng tất cả , không biết vấn đề cột E không liên quan có ảnh hưởng gì đối với các trường hợp khác hay không ?

Sẵn đây trả lời ý của anh TigerTiger như sau : cột "điểm xếp hạng" là điểm quy đổi ra từ học lực và hạnh kiểm ( được dùng để xếp hạng , nghĩa là một học viên có thể điểm học lực cao nhưng điểm hạnh kiểm thấp thì "điểm xếp hạng" sau khi quy đổi vẫn có điểm thấp như thường ) - Cách tính này chỉ mang tính địa phương mà thôi .

OK, thế thì bạn dùng file này,

Nhiều name hơn, nhưng đảm bảo xếp đúng theo bạn yêu cầu

lưu ý trong name (Insert \ name define ) AXLVNI các thành phần là đánh theo code VNI (vì thấy file sẵn là font VNI) nếu bạn thay đổi code font khác thì phải gõ theo code font tương ứng

tròng file thêm trường hợp dòng 21 để kiểm tra đó

chúc thành công


to Boyxin: file này làm theo kiểu "bóng đá" đó
.

 

[letuongqt]

À quên , còn một vấn đề nhờ các anh giúp cho : Nếu có tình trạng học viên bỏ học thì dữ liệu của học viên này vẫn còn nằm ở danh sách (Vì đã nhập trước đó ) nhưng điểm xếp hạng sẽ không có (cột E có cell rỗng) lúc này đương nhiên học viên này sẽ không được xếp hạng (kết quả xếp hạng = rỗng). Vậy trường hợp này phải làm như thế nào (vì hàm OFFSET vùng tham chiếu phải là dãy ô liên tục) ? (Nếu xãy ra trường hợp này thì kết quả xếp hạng của các học viên khác sẽ không còn đúng nữa) . Nhờ các anh cảm phiền giúp tiếp nhé - Xem đây là cơ hội tốt để học hỏi được nhiều hơn .

 

[tigertiger]

À quên , còn một vấn đề nhờ các anh giúp cho : Nếu có tình trạng học viên bỏ học thì dữ liệu của học viên này vẫn còn nằm ở danh sách (Vì đã nhập trước đó ) nhưng điểm xếp hạng sẽ không có (cột E có cell rỗng) lúc này đương nhiên học viên này sẽ không được xếp hạng (kết quả xếp hạng = rỗng). Vậy trường hợp này phải làm như thế nào (vì hàm OFFSET vùng tham chiếu phải là dãy ô liên tục) ? (Nếu xãy ra trường hợp này thì kết quả xếp hạng của các học viên khác sẽ không còn đúng nữa) . Nhờ các anh cảm phiền giúp tiếp nhé - Xem đây là cơ hội tốt để học hỏi được nhiều hơn .

Dĩ nhiên có cách,

Nhưng theo tôi những sinh viên như thế bạn mạnh dạn xóa đi bạn ah, xóa hẳn luôn không nên vương vấn - để lại sau xử lý mệt mỏi mà không có ích gì cả - đó là giải pháp hay
.

 

[cadafi]

Nhưng theo tôi những sinh viên như thế bạn mạnh dạn xóa đi bạn ah, xóa hẳn luôn không nên vương vấn - để lại sau xử lý mệt mỏi mà không có ích gì cả - đó là giải pháp hay
.

Nếu xóa hết thì sau này nếu có nhu cầu thống kê số sinh viên bỏ học và điều tra xem tại sao sinh viên bỏ học thì không có cơ sở để làm, không biết bắt đầu từ đâu. Thôi thì tuỳ tác giả quyết định vậy!

 

[letuongqt]

Xin cảm ơn sự quan tâm của anh . những học viên bỏ học vẫn được lưu giữ , thống kê , báo cáo ,... về sau (việc này cũng rất thường xuyên cần đến ) vì vậy không xóa được trong danh sách anh ạ , chỉ khi bỏ học thì không xếp loại mà thôi chứ các thông tin khác vẫn cần và dùng đến . Mong anh thông cảm (có lẽ hơi làm phiền vì hỏi quá nhiều) . xin cảm ơn .

 

[tigertiger]

Xin cảm ơn sự quan tâm của anh . những học viên bỏ học vẫn được lưu giữ , thống kê , báo cáo ,... về sau (việc này cũng rất thường xuyên cần đến ) vì vậy không xóa được trong danh sách anh ạ , chỉ khi bỏ học thì không xếp loại mà thôi chứ các thông tin khác vẫn cần và dùng đến . Mong anh thông cảm (có lẽ hơi làm phiền vì hỏi quá nhiều) . xin cảm ơn .

Không đừng hiểu nhầm thế,

Tôi đã chứng kiến nhiều trường hợp giữ lại trong Danh sách chính thức các HVbỏ học --> tô màu, hide, vv... đến lúc xử lý dữ liệu, tra cứu --> in trích lục, vất vả

Còn việc xóa là xóa ở DL điểm - DS HV chính thức

Quản lý những trường hợp này --> chúng ta lập hẳn danh sách DL riêng cho dễ quản lý,

Nếu bạn vẫn thích để thế, mai tôi sẽ xem lại giúp bạn(?)
?

 

[letuongqt]

Đã đáp ứng y/c của bạn có xét đến HV bỏ học (với tiêu chí "Xếp loại" là trống) xem ví dụ trường hợp 10, Thậm chí cả điểm hay lớp không có (trống - đ/v trưởng hợp nghỉ học) - Tuy nhiên phần cột họ nhất thiết phải có

Bạn download file mới về.

Rất OK , tuy nhiên vẫn có những trường hợp xếp chưa đúng : Ví dụ : Nếu cho điểm xếp hạng của loại "Khá" > điểm xếp hạng của loại "Giỏi" ( Thực tế vẫn có những trường hợp này xãy ra - Vì điẻm xếp hạng ở đây là điểm quy đổi 2 mặt học lực và hạnh kiểm - Một hv học rất giỏi thì có điểm TB rất cao ==> điểm xếp hạng cao , tuy nhiên hv này mặt hạnh kiểm bị xếp thấp thì sẽ không được xếp loại "Giỏi" mà xuống "Khá" hoặc "TB",...) . Nhờ anh TigerTigerr xem lại các trường hợp này dùm (Cột xếp loại phai ưu tiên trước , cột điểm xếp hạng ) . Xin đính kèm file về trường hợp sai này . Cảm ơn anh .

 

[tigertiger]

Hy vọng hoàn hảo

Rất OK , tuy nhiên vẫn có những trường hợp xếp chưa đúng : Ví dụ : Nếu cho điểm xếp hạng của loại "Khá" > điểm xếp hạng của loại "Giỏi" ( Thực tế vẫn có những trường hợp này xãy ra - Vì điẻm xếp hạng ở đây là điểm quy đổi 2 mặt học lực và hạnh kiểm - Một hv học rất giỏi thì có điểm TB rất cao ==> điểm xếp hạng cao , tuy nhiên hv này mặt hạnh kiểm bị xếp thấp thì sẽ không được xếp loại "Giỏi" mà xuống "Khá" hoặc "TB",...) . Nhờ anh TigerTigerr xem lại các trường hợp này dùm (Cột xếp loại phai ưu tiên trước , cột điểm xếp hạng ) . Xin đính kèm file về trường hợp sai này . Cảm ơn anh .

Tôi không để ý, cứ nghĩ điểm là đ/k trước.

Tuy nhiên, theo tôi rút kinh nghiệm lần sau, bạn hỏi / tự làm nên giả lập tất cả các trường hợp có thể; và KQ kỳ vọng để ktra -> giúp tiết kiệm thời gian công sức chúng ta, bạn có đồng ý không?

Giờ thì đúng như bạn muốn, K.tra lại và phản hồi nhé

Download file mới View attachment 19010
.

 

[letuongqt]

Tôi không để ý, cứ nghĩ điểm là đ/k trước.

Tuy nhiên, theo tôi rút kinh nghiệm lần sau, bạn hỏi / tự làm nên giả lập tất cả các trường hợp có thể; và KQ kỳ vọng để ktra -> giúp tiết kiệm thời gian công sức chúng ta, bạn có đồng ý không?

Giờ thì đúng như bạn muốn, K.tra lại và phản hồi nhé

Download file mới
.

Rất hoàn chỉnh rồi anh TIgerTiger ạ . Thực ra có những trường hợp rất khó tiên liệu trước , vấn đề chỉ nãy sinh trong quá trình thực hiện . Xin cảm ơn sự nhiệt tình của anh , bản thân cũng rút kinh nghiệm lường hết các khả năng co thể xãy ra để không phiền người khác .

 

[tigertiger]

Rất hoàn chỉnh rồi anh TIgerTiger ạ . Thực ra có những trường hợp rất khó tiên liệu trước , vấn đề chỉ nãy sinh trong quá trình thực hiện . Xin cảm ơn sự nhiệt tình của anh , bản thân cũng rút kinh nghiệm lường hết các khả năng co thể xãy ra để không phiền người khác .

Thê thì tốt rồi, bạn chỉ cần chú ý :

Insert \ Name \ define:

Name: AXLVNI = ={"Y","TB","KHAÙ","GIOÛI"} là theo kiểu code ký tự kiểu VNI thay đổi font khác phải gõ lại cái này

Names: CoXL, CoDx là các hằng số thể hiện trọng số để xếp hạng

* một điều nữa nếu nếu hơn 700 HV thì tốc độ tính sẽ chậm đang biệt là >1000HV -- nếu nhiều hơn nữa chỉ có cách dùng VBA là nhanh -- hoặc copy giá trị lại thôi (value) nhưng sẽ không động nữa
.

Do sơ suất nên bản tải về đã bị hư , vào lại link anh gởi sao bị báo như thế (không remove đâu ) . Nhờ anh xem lại giúp.

Vậy thì file mới nhé
.

 

[vumian]

anh Boyxin, anh Tigertiger ơi,

Giả sử ta chỉ xếp hạng chỉ dựa vào điểm và xếp loại (G,K,TB,Y). Không dựa vào lớp nữa thì giải pháp nào ạ ???

Hồi nhỏ em được thầy xếp 1 lần, chỉ cần dùng 2 hay 3 ô phụ thôi, mà giờ file đó không còn nữa

Cám ơn nhiều,