假设由好几个助教, 我们希望合成一个表格, 需要将其它助教的纸质档成绩登记到现有excel表格. 可以使用以下宏来完成:
1. 自动录入A+(Ctrl+Shift+A), A(Ctrl+a), A-(Ctrl+q), B+(Ctrl+Shift+B), B(Ctrl+b), B-(Ctrl+g)
2. 上面括号里面是快捷键, 需要自己定义(方法: 宏->查看宏->选项->快捷键)
3. 我们可以使用Alt+F11来编辑宏, 然后将下面的代码粘贴进去即可.
4. 下面的代码中, 会自动添加颜色为暗红色, 这在一定程度上实现了自动改变输入的颜色
4. 助教成绩的统计很容易通过COUNTIF(range, "A+")来实现(这里是统计A+的个数)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
Sub redA() ‘ ‘ redA 宏 ‘ 红色A ‘ ‘ 快捷键: Ctrl+a ‘ ActiveCell.FormulaR1C1 = “A” With Selection.Font .Color = –16777024 .TintAndShade = 0 End With End Sub Sub red_A() ‘ ‘ red_A 宏 ‘ 红色A+ ‘ ‘ 快捷键: Ctrl+Shift+A ‘ ActiveCell.FormulaR1C1 = “A+” With Selection.Font .Color = –16777024 .TintAndShade = 0 End With End Sub Sub redA_() ‘ ‘ redA_ 宏 ‘ 红色A- ‘ ‘ 快捷键: Ctrl+q ‘ ActiveCell.FormulaR1C1 = “A-“ With Selection.Font .Color = –16777024 .TintAndShade = 0 End With End Sub Sub redB() ‘ ‘ redB 宏 ‘ 红色B ‘ ‘ 快捷键: Ctrl+b ‘ ActiveCell.FormulaR1C1 = “B” With Selection.Font .Color = –16777024 .TintAndShade = 0 End With End Sub Sub red_B() ‘ ‘ red_B 宏 ‘ 红色B+ ‘ ‘ 快捷键: Ctrl+Shift+B ‘ ActiveCell.FormulaR1C1 = “B+” With Selection.Font .Color = –16777024 .TintAndShade = 0 End With End Sub Sub redB_() ‘ ‘ redB_ 宏 ‘ 红色B- ‘ ‘ 快捷键: Ctrl+g ‘ ActiveCell.FormulaR1C1 = “B-“ With Selection.Font .Color = –16777024 .TintAndShade = 0 End With End Sub |
发表回复