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 .