[第5期] 英语四级考试统计——多表计算问题 [已总结]

dj-1621

交卷.

还是枚举法啊，不能得3分哦。喜欢星爷？

[此贴子已经被apolloh于2005-11-21 9:17:14编辑过]

ladhome

难啊!只做了两个.

哼！第一题还没做完整呢！

[此贴子已经被apolloh于2005-11-21 9:20:16编辑过]

yuwd3

用了辅助列，多个最高分还不会。

rongjun

我的答案：

sailorgg

做了第一、第二题，第三小题用VBA做了一下（函数的等版主公布答案）

第三小题vba代码：

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Me.[D13] = "姓名" Then Lxinming End Sub Option Explicit Sub Lxinming() Dim mysh As Worksheet Dim addR As Integer, r1 As Integer, r As Integer, c As Integer addR = 0 r = 14 c = 4 For Each mysh In ThisWorkbook.Worksheets If mysh.Name = "一班" Then For r1 = 7 To 57 If mysh.Cells(r1, 3) = Sheet1.[d10] Then Sheet1.Cells(r + addR, c) = mysh.Cells(r1, 2) Sheet1.Cells(r + addR, c - 1) = mysh.Name addR = addR + 1 End If Next End If Next For Each mysh In ThisWorkbook.Sheets If mysh.Name = "二班" Then For r1 = 7 To 57 If mysh.Cells(r1, 3) = Sheet1.[d10] Then Sheet1.Cells(r + addR, c) = mysh.Cells(r1, 2) Sheet1.Cells(r + addR, c - 1) = mysh.Name addR = addR + 1 End If Next End If Next For Each mysh In ThisWorkbook.Sheets If mysh.Name = "三班" Then For r1 = 7 To 57 If mysh.Cells(r1, 3) = Sheet1.[d10] Then Sheet1.Cells(r + addR, c) = mysh.Cells(r1, 2) Sheet1.Cells(r + addR, c - 1) = mysh.Name addR = addR + 1 End If Next End If Next End Sub

misty

新手第一次试一试！难啊

也是枚举法，灵活度不够啊！

[此贴子已经被apolloh于2005-11-21 9:37:04编辑过]

sailorgg

Option Explicit Function Lbanji(mynum As Integer) Dim mysh As Worksheet Dim addR As Integer, r1 As Integer, r As Integer, c As Integer addR = 1 r = ActiveCell.Row c = ActiveCell.Column For Each mysh In ThisWorkbook.Worksheets If mysh.Name = "一班" Then For r1 = 7 To 57 If mysh.Cells(r1, 3) = Sheet1.[d10] Then If mynum = addR Then Lbanji = mysh.Name addR = addR + 1 End If Next End If Next For Each mysh In ThisWorkbook.Sheets If mysh.Name = "二班" Then For r1 = 7 To 57 If mysh.Cells(r1, 3) = Sheet1.[d10] Then If mynum = addR Then Lbanji = mysh.Name addR = addR + 1 End If Next End If Next For Each mysh In ThisWorkbook.Sheets If mysh.Name = "三班" Then For r1 = 7 To 57 If mysh.Cells(r1, 3) = Sheet1.[d10] Then If mynum = addR Then Lbanji = mysh.Name addR = addR + 1 End If Next End If Next If mynum >= addR Then Lbanji = "已全部列出" End Function

Function Lxinming(mynum As Integer) Dim mysh As Worksheet Dim addR As Integer, r1 As Integer, r As Integer, c As Integer addR = 1 r = ActiveCell.Row c = ActiveCell.Column For Each mysh In ThisWorkbook.Worksheets If mysh.Name = "一班" Then For r1 = 7 To 57 If mysh.Cells(r1, 3) = Sheet1.[d10] Then If mynum = addR Then Lxinming = mysh.Cells(r1, 2) addR = addR + 1 End If Next End If Next For Each mysh In ThisWorkbook.Sheets If mysh.Name = "二班" Then For r1 = 7 To 57 If mysh.Cells(r1, 3) = Sheet1.[d10] Then If mynum = addR Then Lxinming = mysh.Cells(r1, 2) addR = addR + 1 End If Next End If Next For Each mysh In ThisWorkbook.Sheets If mysh.Name = "三班" Then For r1 = 7 To 57 If mysh.Cells(r1, 3) = Sheet1.[d10] Then If mynum = addR Then Lxinming = mysh.Cells(r1, 2) addR = addR + 1 End If Next End If Next If mynum >= addR Then Lxinming = "已全部列出" End Function

附件：

辛苦了，应该鼓励一下：）

[此贴子已经被apolloh于2005-11-21 9:35:17编辑过]

misty

发现自己真笨，再改进一下。

神哪酒酒我

先解决1.5个题目，其余慢慢想

wangtx218