统计单科成绩并判断总成绩是否合格

准提部林

2003公式//
G2:
=RIGHT("不合格",MAX(2,SUMPRODUCT((B2:F2={"C";"D";""})*{1;2;9})))

renkangjizhen

Sub 统计及格()
Dim i%, d, m%, s$
For i = 2 To [a65536].End(3).Row
For m = 2 To 6
s = Cells(i, m)
Set d = CreateObject("scripting.dictionary")
If Not d.exists(s) Then
d(s) = 1
Else
d(s) = d(s) + 1
End If
Next
If d("C") > 2 Or d("D") > 1 Or d("C") + d("D") >= 2 Then
Cells(i, "g") = "不及格"
Else
Cells(i, "g") = "及格"
End If
Set d = Nothing
Next
End Sub

renkangjizhen

用字典法解决！

roserice

cjc209 发表于 2024-11-17 18:18
利用溢出功能和按行遍历函数还可以
=BYROW(B2:F11,LAMBDA(x,IF(REGEXP(TEXTJOIN("",,SORT(x,,1,TRUE)),"CC ...

不用sort不行吗

cjc209

roserice 发表于 2024-11-18 22:01
不用sort不行吗

用了sort 才好用正则去匹配有没有合格的三种情况

caiyu93

1. Sub Main()
   
2.     Dim R As Range, arr(), x As Long, y As Long, s As String
   
3.     Dim regex As Object, Matches As Object
   
4.     Set regex = CreateObject("VBScript.RegExp")
   
5.     Set R = [b2:f11]
   
6.     x = R.Rows.Count
   
7.     y = R.Columns.Count
   
8.     arr = R
   
9.     Set R = [h2]
   
10.     With regex
    
11.         .Global = 1
    
12.         .Pattern = "[CD]"
    
13.         For i = 1 To x
    
14.             s = ""
    
15.             For j = 1 To y
    
16.                 s = s & arr(i, j)
    
17.             Next
    
18.             Set Matches = .Execute(s)
    
19.             If Matches.Count > 2 Then
    
20.                 R(i) = "不合格"
    
21.             ElseIf Matches.Count <> 2 Then
    
22.                 R(i) = "合格"
    
23.             ElseIf InStr(s, "C") And InStr(s, "D") Then
    
24.                 R(i) = "不合格"
    
25.             Else
    
26.                 R(i) = "合格"
    
27.             End If
    
28.         Next
    
29.     End With
    
30. End Sub
    

复制代码

caiyu93

haohu0406

if((countif(b2:f2,"D")*2+countif(b2:f2,"C"))>=3,"不合格","合格")

nihao123456789

G2单元格公式
=IF(OR(COUNTIF(B2:F2,"D")>=2,COUNTIF(B2:F2,"C")>=3,AND(COUNTIF(B2:F2,"C")>=1,COUNTIF(B2:F2,"D")>=1)),"不合格","合格")

今铭昔