Cách sắp xếp thứ tự (rank) theo nhóm (2 người xem)

[khaothibaclieu]

Nhờ các bạn chỉ mình cách sắp xếp thứ tự theo nhóm.
Ví dụ: Nhóm môn Toán: Có 5 thí sinh, sắp xếp thứ hạng từ 1 - 5.
Nhóm môn Lý: Có 4 thí sinh, sắp xếp thứ hạng từ 1 - 4.
Nhóm môn Hóa có 6 thí sinh, sắp xếp thứ hạng từ 1 - 6.
Dùng hàm rank thì càng tốt (mình không muốn dùng sort vì sẽ đảo lộn danh sách của mình).
Mình đã dùng hàm rank cho từng nhóm nhưng như vậy thì thủ công quá. Có công thức chung nào không để mình kéo cái rẹt từ trên xuống cho khỏe, không cần phải thay đổi vùng chọn cho từng nhóm.
Cám ơn. Xin xem file gửi kèm.

 

[ndu96081631]

Nhờ các bạn chỉ mình cách sắp xếp thứ tự theo nhóm.
Ví dụ: Nhóm môn Toán: Có 5 thí sinh, sắp xếp thứ hạng từ 1 - 5.
Nhóm môn Lý: Có 4 thí sinh, sắp xếp thứ hạng từ 1 - 4.
Nhóm môn Hóa có 6 thí sinh, sắp xếp thứ hạng từ 1 - 6.
Dùng hàm rank thì càng tốt (mình không muốn dùng sort vì sẽ đảo lộn danh sách của mình).
Mình đã dùng hàm rank cho từng nhóm nhưng như vậy thì thủ công quá. Có công thức chung nào không để mình kéo cái rẹt từ trên xuống cho khỏe, không cần phải thay đổi vùng chọn cho từng nhóm.
Cám ơn. Xin xem file gửi kèm.

Nói thật: Bài toán này dùng Sort là hay nhất
Nếu bạn sợ đảo lộn DS thì làm 1 đoạn code VBA để SORT, lúc cần thì lại chạy 1 đoạn code khác để quay lại ban đầu
Dùng công thức với bài này không dể à nha

 

[DOSNET]

Nhờ các bạn chỉ mình cách sắp xếp thứ tự theo nhóm.
Ví dụ: Nhóm môn Toán: Có 5 thí sinh, sắp xếp thứ hạng từ 1 - 5.
Nhóm môn Lý: Có 4 thí sinh, sắp xếp thứ hạng từ 1 - 4.
Nhóm môn Hóa có 6 thí sinh, sắp xếp thứ hạng từ 1 - 6.
Dùng hàm rank thì càng tốt (mình không muốn dùng sort vì sẽ đảo lộn danh sách của mình).
Mình đã dùng hàm rank cho từng nhóm nhưng như vậy thì thủ công quá. Có công thức chung nào không để mình kéo cái rẹt từ trên xuống cho khỏe, không cần phải thay đổi vùng chọn cho từng nhóm.
Cám ơn. Xin xem file gửi kèm.

Công thức vẫn được bác Ndu ơi. Dùng name động là OK

VUNG=OFFSET(Sheet1!$B$1,MATCH(Sheet1!$B15,Sheet1!$B$2:$B$1000,0),,COUNTIF(Sheet1!$B$2:$B$1000,Sheet1!$B15),)

 

[ndu96081631]

Công thức vẫn được bác Ndu ơi. Dùng name động là OK

VUNG=OFFSET(Sheet1!$B$1,MATCH(Sheet1!$B15,Sheet1!$B$2:$B$1000,0),,COUNTIF(Sheet1!$B$2:$B$1000,Sheet1!$B15),)

Hình như đây không phải là yêu cầu
Theo tôi hiểu thì yêu cầu là:
- Lọc ra 1 DS khác có KQ giống như sau khi Sort vậy!
- Tôi nghĩ giống như KQ trong file đính kèm

 

[khaothibaclieu]

Cám ơn 2 bạn. Yêu cầu của mình giống như bạn Dosnet thực hiện. Mình không muốn sort cái danh sách.
Còn nếu dùng VBA để có yêu cầu như bạn ndu.. nói thì mình nghĩ nên dùng Screen Updating trong VBA thì hay nhất. Nhưng chương trình của mình đã nặng quá rồi. Mình không muốn dùng nhiều VBA. Dù gì cũng cám ơn các bạn đã quan tâm.

Công thức vẫn được bác Ndu ơi. Dùng name động là OK

VUNG=OFFSET(Sheet1!$B$1,MATCH(Sheet1!$B15,Sheet1!$B$2:$B$1000,0),,COUNTIF(Sheet1!$B$2:$B$1000,Sheet1!$B15),)

Bạn Dosnet ơi, bạn chỉ mình cách dùng name động đi. Mình thấy hay quá vì code VUNG=OFFSET(Sheet1!$B$1,MATCH(Sheet1!$B15,Sheet1!$B$2:$B$1000,0),,COUNTIF(Sheet1!$B$2:$B$1000,Sheet1!$B15),) của bạn thì dài nhưng khi mình mở file excel ra thì chỉ thấy =RANK(D2,OFFSET(VUNG,,2)) là sao hả bạn

 

[ndu96081631]

Bạn Dosnet ơi, bạn chỉ mình cách dùng name động đi. Mình thấy hay quá vì code VUNG=OFFSET(Sheet1!$B$1,MATCH(Sheet1!$B15,Sheet1!$B$2:$B$1000,0),,COUNTIF(Sheet1!$B$2:$B$1000,Sheet1!$B15),) của bạn thì dài nhưng khi mình mở file excel ra thì chỉ thấy =RANK(D2,OFFSET(VUNG,,2)) là sao hả bạn

Bạn bấm Ctrl + F3 sẽ thấy cái.. dài dài ấy nằm tróng đó
Đại khái là thế này: Có đôi khi thực hiện 1 công thức sẽ rất dài nên người ta mới nghĩ ra cách đặt TÊN ĐẠI DIỆN cho nó (trong menu Insert\Name\Define).... Khi nào dùng đến đoạn công thức ấy, thay vì gõ hết, chỉ cần gõ TÊN ĐẠI DIỆN là xong
Vậy nên:
VUNG sẽ là TÊN ĐẠI DIỆN của:

=Offset(... dài dài gì đó.....)

Nếu gõ hết luôn vào cell chắc nhìn mù mắt luôn

 

[huuthang_bd]

Bổ sung cách dùng cột phụ.

Nhờ các bạn chỉ mình cách sắp xếp thứ tự theo nhóm.
Ví dụ: Nhóm môn Toán: Có 5 thí sinh, sắp xếp thứ hạng từ 1 - 5.
Nhóm môn Lý: Có 4 thí sinh, sắp xếp thứ hạng từ 1 - 4.
Nhóm môn Hóa có 6 thí sinh, sắp xếp thứ hạng từ 1 - 6.
Dùng hàm rank thì càng tốt (mình không muốn dùng sort vì sẽ đảo lộn danh sách của mình).
Mình đã dùng hàm rank cho từng nhóm nhưng như vậy thì thủ công quá. Có công thức chung nào không để mình kéo cái rẹt từ trên xuống cho khỏe, không cần phải thay đổi vùng chọn cho từng nhóm.
Cám ơn. Xin xem file gửi kèm.

Mình bổ xung thêm cách dùng cột phụ. Mọi người tham khảo nha. Ưu điểm của cách này là đơn giản dễ hiểu và đặt biệt là có thể dùng trong trường hợp môn học của các học sinh không được phân nhóm theo trật tự mà sắp xếp ngẫu nhiên. Trong file có ví dụ cho trường hợp này.

 

[khaothibaclieu]

Bạn bấm Ctrl + F3 sẽ thấy cái.. dài dài ấy nằm tróng đó
Đại khái là thế này: Có đôi khi thực hiện 1 công thức sẽ rất dài nên người ta mới nghĩ ra cách đặt TÊN ĐẠI DIỆN cho nó (trong menu Insert\Name\Define).... Khi nào dùng đến đoạn công thức ấy, thay vì gõ hết, chỉ cần gõ TÊN ĐẠI DIỆN là xong
Vậy nên:
VUNG sẽ là TÊN ĐẠI DIỆN của:

Nếu gõ hết luôn vào cell chắc nhìn mù mắt luôn

Mình hiểu rồi. Nhưng sau mình vẫn không làm được nhỉ. Tập tin trên chỉ là ví dụ. Có phải mình sẽ thay tên sheet1 thành tên sheet của mình, chẳng hạn như là "NhapDS"?

 

[OverAC]

Các bác xem hộ em dùng công thức này có được không

=SUMPRODUCT(($B$2:$B$16=B2)*($D$2:$D$16>D2))+1

 

[huuthang_bd]

Mình hiểu rồi. Nhưng sau mình vẫn không làm được nhỉ. Tập tin trên chỉ là ví dụ. Có phải mình sẽ thay tên sheet1 thành tên sheet của mình, chẳng hạn như là "NhapDS"?

Đương nhiên rồi bạn àh! Các vùng tham chiếu cũng chưa chắc là đúng bạn cũng nên xem lại và tham chiếu cho hợp lý thì mới ra kết quả đúng được.

 

[khaothibaclieu]

Mình bổ xung thêm cách dùng cột phụ. Mọi người tham khảo nha. Ưu điểm của cách này là đơn giản dễ hiểu và đặt biệt là có thể dùng trong trường hợp môn học của các học sinh không được phân nhóm theo trật tự mà sắp xếp ngẫu nhiên. Trong file có ví dụ cho trường hợp này.

Cách của bạn cũng hay, nhưng nếu có nhiều môn thì dài quá. Ví dụ có thêm môn Sinh thì bổ sung ra sao vậy bạn?

Các bác xem hộ em dùng công thức này có được không

=SUMPRODUCT(($B$2:$B$16=B2)*($D$2:$D$16>D2))+1

Hoan hô. Mình mới test thử, thấy đúng tróc.

 

[huuthang_bd]

Các bác xem hộ em dùng công thức này có được không

=SUMPRODUCT(($B$2:$B$16=B2)*($D$2:$D$16>D2))+1

Một ý tưởng hay nhưng cách này chúng ta không sử dụng được các tùy chọn của hàm Rank.

Cách của bạn cũng hay, nhưng nếu có nhiều môn thì dài quá. Ví dụ có thêm môn Sinh thì bổ sung ra sao vậy bạn?

Mình sửa lại công thức một chút cho phù hợp. Gọn hơn và không bị phụ thuộc vào số môn:

G2=RANK(F2;$F$2:$F$16)-COUNTIF($F$2:$F$16;">"&(F2+10))

 

[khaothibaclieu]

Một ý tưởng hay nhưng cách này chúng ta không sử dụng được các tùy chọn của hàm Rank.



Mình sửa lại công thức một chút cho phù hợp. Gọn hơn và không bị phụ thuộc vào số môn:

G2=RANK(F2;$F$2:$F$16)-COUNTIF($F$2:$F$16;">"&(F2+10))

Bạn giải thích hộ chỗ này, mình chưa hiểu: F2+10

 

[rollover79]

Vấn đề này tôi đã có trình bày 1 lần ở đây, các bạn tham khảo thêm xem có vấn đề gì không nhé.
http://www.giaiphapexcel.com/forum/showthread.php?t=13568

 

[Mainho]

Các bạn giúp mình chút nhé, cũng về ham rank nên để trong bài viết này luôn. Sợ mở thêm subject sẽ bị... cảnh báo.
Mình muốn khi làm hàm rank vẫn tính được các vị trí đồng hạng rồi đến thứ hạng kế tiếp mà không nhảy hạng. Các bạn xem trong file dính kèm nhé. Cám ơn nhiều

 

[huuthang_bd]

Bạn giải thích hộ chỗ này, mình chưa hiểu: F2+10

G2=RANK(F2;$F$2:$F$16)-COUNTIF($F$2:$F$16;">"&(F2+10))

Ví dụ có 5 HS học toán, 4 HS học lý, 6 HS học hóa. Giá trị của cột phụ bằng:
Điểm (nếu là HS toán)
Điểm + 100 (nếu là HS lý)
Điểm + 200 (nếu là HS hóa)
Đoạn Code màu xanh dùng để sắp xếp thứ tự của cột phụ, kết quả trả về các giá trị từ 1 đến 15 theo thứ tự: Cao nhất môn hóa đến thấp nhất môn toán. Lúc này kết quả sếp thứ tự của các HS môn lý là 7, 8, 9, 10 (do đứng sau các HS môn hóa vì môn hóa được cộng thêm 200 mà môn lý chỉ cộng thêm 100). Tương tự như vậy kết quả sếp thứ tự của các HS môn toán là 11, 12, 13, 14, 15 (đứng sau các HS môn hóa và môn lý).

Đoạn Code màu đỏ dùng để trừ ra những HS học môn khác được xếp cao hơn.
Ví dụ:
Đối với HS học toán thì (F2+10) nằm trong vùng giá trị (10,20). Hàm Countif() sẽ trả về giá trị là tổng số HS học hóa và lý (tức là 10). Lúc này kết quả của cả công thức sẽ là {11, 12, 13, 14, 15} - 10 = {1, 2, 3, 4, 5} theo thứ tự sắp xếp điểm từ lớn đến nhỏ của các HS toán.
Đối với các HS học lý thì (F2+10) nằm trong vùng giá trị (110,120). Hàm Countif() sẽ trả về giá trị là tổng số HS hóa (là 6). Kết quả công thức sẽ là: {7, 8, 9, 10} - 6 = {1, 2, 3, 4} theo thứ tự sắp xếp điểm từ lớn đến nhỏ của các HS lý.
Tương tự với các HS học hóa thì (F2+10) nằm trong vùng giá trị (210,220). Hàm Countif() trả về 0. Kết quả công thức là {1, 2, 3, 4, 5, 6} - 0 = {1, 2, 3, 4, 5, 6}

 

[GGSN.PTR]

M dùng sumproduct cũng thấy đúng! Nhưng bạn có thể giải thích quy tắc được k? "+1" nghĩa là gì nhỉ?
Thanks nhiều!

 

[wuchengcai]

Nếu chèn thêm cột Hạnh kiểm, việc xếp hạng dựa theo cột B (Môn), cột D (Điểm) và cột E (Hạnh kiểm), thì làm sao cho ra kết quả như cột F?

 

[xuan hanh kt]

=+RANK(D14,IF(B14=$B$2,$D$2:$D$6,IF(B14=$B$7,$D$7:$D$10,IF(B14=$B$11,$D$11:$D$16))))
bạn dùng công thức này nhé

 

[wuchengcai]

=+RANK(D14,IF(B14=$B$2,$D$2:$D$6,IF(B14=$B$7,$D$7:$D$10,IF(B14=$B$11,$D$11:$D$16))))
bạn dùng công thức này nhé

Không ra kết quả như cột F bạn ơi.
Bạn xem lại giúp mình.