Đếm số môn còn nợ của SV (2 người xem)

[ZaceGL]

Em đang dùng Excel để quản lý sinh viên (trong file đính kèm), vấn đề là mỗi sinh viên học nhiều năm, mỗi năm, mỗi học kỳ có nhiều môn, mỗi môn có 3 cột điểm: lần 1, lần 2 và điểm cao nhất (đặt cạnh nhau); hết một môn là đến 3 cột của môn khác; hết học kỳ thì đến cột tính điểm TBHK và một số cột khác.

Cuối khóa cần tính xem số môn học còn đang nợ của mỗi sinh viên, nghĩa là xem các cột điểm cao nhất của các môn học có bao nhiêu cột điểm <5.

Giải pháp hiện tại đang áp dụng là đưa dữ liệu sang sheet khác, sau đó xóa toàn bộ các cột dữ liệu thừa, chỉ giữ lại những cột điểm cao nhất, sau đó dùng COUNTIF để đếm.

Trong file đính kèm là cần tính dữ liệu cho cột Nợ cả khóa bôi màu đỏ chóe ở cuối cùng.

Mong các bác giúp em. Xin đa tạ ạ.

 

[yeudoi]

Bạn nhập công thức này vào ô AW3 sau đó kéo xuống

=SUMPRODUCT(--($A$1:$AR$1=$G$1);--($A3:$AR3<>"");--($A3:$AR3<5))

 

[SA_DQ]

Bạn dùng hàm tự tạo đếm theo màu như sau

Option Explicit
Function SoMonNo(LookUpRange As Range, Color_ As Range)
 Dim Clls As Range
 Dim MyColor As Byte
 
 MyColor = Color_.Interior.ColorIndex
 For Each Clls In LookUpRange
   With Clls.Interior
      If .ColorIndex = MyColor And Clls.Value < 5 Then SoMonNo = SoMonNo + 1
   End With
 Next Clls
End Function

Hướng dẫn cách dùng:

(*) Bạn tô màu các ô [Z1] & [AU1] màu nền giống với các cột [MAX. . .] (Chỉ số màu 35)

(*) Tại [Z3] nhập cú pháp =SoMonNo(F3:Y3,Z$1)
Sau đó chép xuống các ô bên dưới;
(Tại cột [Au] cũng tương tự , ví dụ tại [Au12] có cú pháp =SoMonNo(AA12:AS12,AU$1)

Chúc vui!

 

[ZaceGL]

Bạn nhập công thức này vào ô AW3 sau đó kéo xuống

=SUMPRODUCT(--($A$1:$AR$1=$G$1);--($A3:$AR3<>"");--($A3:$AR3<5))

Cho em hỏi thêm tý, tại vì em chưa hiểu công thức lắm:

- Em tra Help thì Sumproduct dùng để nhân 2 mảng với nhau theo thứ tự rồi tính tổng. Tại sao lại áp dụng hàm này ở đây ạ?
- Sao lại có 2 dấu trừ liền nhau, tác dụng để làm gì và dùng nó khi nào ạ?
- $A3:$AR3<>"" nghĩa là tham chiếu đến những ô trong mảng A3:AR3 thỏa mãn điều kiện khác rỗng phải không ạ?

Cám ơn (các) bác lần nữa ạ

 

[duongdv]

Cho em hỏi thêm tý, tại vì em chưa hiểu công thức lắm:

- Em tra Help thì Sumproduct dùng để nhân 2 mảng với nhau theo thứ tự rồi tính tổng. Tại sao lại áp dụng hàm này ở đây ạ?
- Sao lại có 2 dấu trừ liền nhau, tác dụng để làm gì và dùng nó khi nào ạ?
- $A3:$AR3<>"" nghĩa là tham chiếu đến những ô trong mảng A3:AR3 thỏa mãn điều kiện khác rỗng phải không ạ?

Cám ơn (các) bác lần nữa ạ

Thắc mắc của bạn được giải đáp rất chi tiết và rõ dàng tại đây.
http://www.giaiphapexcel.com/forum/showthread.php?t=7174

 

[ZaceGL]

Em cảm ơn các bác nhiều lắm ạ, em sẽ nghiên cứu, có gì không hiểu thì lại hỏi tiếp

 

[PhanTuHuong]

Em cảm ơn các bác nhiều lắm ạ, em sẽ nghiên cứu, có gì không hiểu thì lại hỏi tiếp

Với kiểu lằng nhằng này có lẽ dùng VBA như bác HYen là hợp lý nhất, hàm thì hạn chế nhiều.