å¨EXCELä¸âå®âæ¯æè½å¤å®ææä¸ä¸ªç¹å®æä½ç代ç ï¼æ4.0å®åVBAå®ï¼ä¸è¬ç°å¨æ说çâå®âæ¯æVBAç¼åç代ç ã
åå¦è
å¯ä»¥ç¨âå·¥å
·ï¼å®ï¼å½å¶å®âåè½ï¼å°å¸¸ç¨æä½å½å¶æå®ï¼ç¶åå¨éè¦ç¨çæ¶åï¼ä»âå·¥å
·ï¼å®ï¼å®âä¸éä¸è¿ä¸ªå®çååï¼ç¹âæ§è¡âå°±å¯ä»¥å°åé¢å½å¶çæä½èªå¨æ§è¡ä¸æ¬¡ã
çç»è¿ç¨âå®âå¯åè½»æä½å¼ºåº¦ï¼æé«æçã
é£ä¹ï¼"å®"å°åºæ¯ä»ä¹å¢ï¼
å¨Excel 97ä¸ï¼"å®"æ¯ä¸ä¸ªé¾ä»¥ç解çæ¦å¿µï¼ä½å¯¹äºä¸ä¸ªå
·ä½ç"å®"èè¨ï¼å´æ¯å®¹æç解çï¼å¦æ说"å°ä¸åæåå为'é»ä½'ï¼åå·ä¸º'ä¸å·'"å°±å¯ä»¥çä½ä¸ä¸ª"å®"çè¯ï¼é£ä¹"å®"å°±ä¸é¾ç解äºï¼å
¶å®Excel 97ä¸ç许å¤æä½é½å¯ä»¥æ¯ä¸ä¸ª"å®"ã
"è®°å½å®"å
¶å®å°±æ¯å°å·¥ä½çä¸ç³»åæä½ç»æå½å¶ä¸æ¥ï¼å¹¶å½ååå¨ï¼ç¸å½äºVBä¸ä¸ä¸ªåç¨åºï¼ãå¨Excel 97ä¸ï¼"è®°å½å®"ä»
è®°å½æä½ç»æï¼èä¸è®°å½æä½è¿ç¨ãä¾å¦ï¼æ¹åæååä½æ¶ï¼éè¦æå¼"åä½"æ ä¸çä¸æå表ï¼åéæ©ä¸ç§åä½ï¼è¿æ¶æåå³å为æéæ©çåä½ï¼è¿æ¯ä¸ä¸ªè¿ç¨ï¼ç»ææ¯å°æéæ©çæåæ¹å为æéæ©çåä½ãè"è®°å½å®"ååªè®°å½"å°æéæ©çæåæ¹å为æéæ©çåä½"è¿ä¸ç»æã
Excel 97ä¸å·¥ä½è¡¨æ¯ç±è¡ååç»æçäºç»´è¡¨æ ¼ï¼æ们å¯ä»¥éè¿ç³»ç»æä¾çè¯å¥activesheet.cells(I,j)ï¼å°å½åå·¥ä½è¡¨ä¸ç第Iè¡ç¬¬jåæå¨çåå
æ ¼ä¸çæ°æ®ååºï¼ä¹å¯å°å®æ°æ®å¡«å
¥å°æå®çåå
æ ¼ä¸ï¼ï¼ç¶ååæå®æ¾å
¥æå®ä¹çæ°ç»ä¸ï¼è¿æ¶å°±å¯ä»¥å¯¹å
¶è¿è¡åç§æä½ï¼å¦æ±å¹³ååãæ»åãåæ°æ®µäººæ°ççã
æ¬äººå°±å©ç¨Excel 97ä¸ææä¾çå®åè½æ¥åå¦çæ绩çåæå¤çç¨åºãæ¬ç¨åºæ¯Excelä¸çä¸ä¸ªæ件ï¼å
¶ä¸å
å«ä»¥ä¸å 个å®ï¼åçãæ»åãå¹³ååãåæ°æ®µãå é¤çã
æ¬ç¨åºæ¯ä»¥æ¬æ ¡é«ä¸çç§çå¦çæ绩è¿è¡åæã
æå
³ç¨åºä¸ç¨å°çå å
·å®çåè½è¯´æï¼
åçï¼é对äºé¾ä»¥ç¡®å®ç级çæ
åµä¸ï¼ä»¥ç为åä½è¿è¡åçï¼æ¬å®å¯ä»¥ä½ä¸ºé«ä¸æ°çå
¥å¦æ¶è¿è¡åççåè½ã
æ»åï¼å¯¹åå§çæ绩èªå¨æ±æ»åã
å¹³ååï¼å¯¹åå§çæ绩以ç为åä½è¿è¡åå¦ç§å¹³åæ绩ç计ç®åå
¨æ ¡åå¦ç§æ绩ç计ç®ã
åæ°æ®µï¼ç»å®ä¸ä¸ªæé«åæ°åæä½åæ°ï¼ç¶åç»è®¡åºåçå个åæ°æ®µç人æ°ï¼ååæ°æ®µäººæ°è¿è¡ç´¯è®¡ã
å é¤ï¼ç¨äºå é¤ä¸ç¨çå·¥ä½è¡¨ã
ç°å°å个å®ç代ç å举å¦ä¸ï¼
ä¸.åç
Sub åç()
Const studentno = 191 'å¦ç人æ°
Const zdno = 12 'å段æ°
Dim zd$(zdno) 'å®ä¹ä¸º12个å段çæ°ç»
Dim a(studentno, zdno), stu(60, zdno)
Dim nam$(studentno), bjname$(60) 'å®ä¹ä¸ä¸ªåæ¾å
¨æ ¡å¦çååååçå¦çååçæ°ç»
Dim bj(studentno) 'å®ä¹åæ¾ç级çä¸ä¸ªæ°ç»
'çç§çå·¥ä½è¡¨
Sheets("é«ä¸ç").Select
For i = 2 To studentno
bj(i) = ActiveSheet.Cells(i, 1)
nam$(i) = ActiveSheet.Cells(i, 2)
For j = 3 To zdno
a(i, j) = ActiveSheet.Cells(i, j)
Next j
Next i
'åæ¾å段å°æ°ç»ä¸ã
For i = 1 To zdno
zd$(i) = ActiveSheet.Cells(1, i)
Next i
'å
建ç«å个ç级çå·¥ä½è¡¨
Sheets("é«ä¸ç").Select
Sheets("é«ä¸ç").Copy After:=Sheets("åæ°æ®µ")
Sheets("é«ä¸ç (2)").Select
Sheets("é«ä¸ç (2)").Name = "33"
For i = 2 To studentno
For j = 1 To zdno
ActiveSheet.Cells(i, j) = Space$(1)
Next j
Next i
Range("a1").Select
For i = 1 To zdno
ActiveSheet.Cells(1, i) = zd$(i)
Next i
'34å°36çå·¥ä½è¡¨ç建ç«
For i = 34 To 36
x$ = Mid$(Str(33), 2)
Sheets(x$).Select
Sheets(x$).Copy After:=Sheets("åæ°æ®µ")
Sheets(x$ + " (2)").Select
Sheets(x$ + " (2)").Name = Mid$(Str(i), 2)
Next i
'å
·ä½åçã
For k = 33 To 36
bjrs = 0
x$ = Mid$(Str(k), 2)
no = k Mod 10
Sheets(x$).Select
For i = 2 To studentno
If bj(i) = no Then
bjrs = bjrs + 1
bjname$(bjrs) = nam$(i)
For j = 3 To zdno
stu(bjrs, j) = a(i, j)
Next j
End If
Next i
For i = 2 To bjrs
ActiveSheet.Cells(i, 1) = no
ActiveSheet.Cells(i, 2) = bjname$(i)
For j = 3 To zdno
ActiveSheet.Cells(i, j) = stu(i, j)
Next j
Next i
Next k
End Sub
äº.æ»å
Const studentno = 190
Const xknum = 6
Const zdnum = 12
Sheets("é«ä¸ç").Select
For i = 2 To studentno + 1
Sum = 0
For j = 1 To xknum
Sum = Sum + ActiveSheet.Cells(i, j + 3)
Next j
ActiveSheet.Cells(i, zdnum-1) = Sum
Next i
End Sub
ä¸.å¹³åå
Sub å¹³åå()
Const studentno = 190
Const xknum = 6
Dim fs(studentno, xknum), pjf3(4, 6), bjrs(4), qxpjf(6)
Dim bj(studentno)
Sheets("é«ä¸ç").Select
'以ä¸ç¨åºæ®µç¨äºæ±å
¨æ ¡å¹³åå
For i = 1 To studentno
bj(i) = ActiveSheet.Cells(i + 1, 1)
For j = 1 To xknum
fs(i, j) = ActiveSheet.Cells(i + 1, j + 3)
Next j
Next i
For i = 1 To xknum
Sum = 0
For j = 1 To studentno
um = Sum + fs(j, i)
Next j
qxpjf(i) = Sum / (j - 1)
Next i
'以ä¸ç¨åºæ®µç¨äºæ±åçå¹³åå
For j = 1 To 4
For i = 1 To studentno
Ifbj(i) = j + 2 Then
bjrs(j) = bjrs(j) + 1
For k = 1 To xknum
pjf3(j, k) = pjf3(j, k) + fs(i, k)
Next k
End If
Next i
Next j
For j = 1 To 4
For i = 1 To 6
pjf3(j, i) = pjf3(j, i) / bjrs(j)
Next i
Next j
'åå
¥åçåç§å¹³åå
Sheets("å¹³åå").Select
For i = 1 To 4
For j = 1 To 6
ActiveSheet.Cells(i + 2, j + 1) = pjf3(i, j)
Next j
Next i
'åå
¥å
¨æ ¡åç§å¹³åå
i = 7
For j = 1 To 6
ActiveSheet.Cells(i, j + 1) = qxpjf(j)
Next j
End Sub
温馨提示:答案为网友推荐,仅供参考