情報基礎 A Lecture 13

1. 情報基礎ALecture 13 Takeshi TokuyamaTohoku University Graduate School of Information SciencesSystem Information SciencesDesign and Analysis of Information Systems

2. Programming VBAScore Data Processing

3. data2.xls Average Score for a Student Sheet: Score Column: j Operation for Columns A procedure to output average score of student 1001 into N3

4. Average Score for 100 Students Nested loop

5. data2.xls Average Score for 100 Students Sheet: Score Column: j Nested loop Row: i A procedure to output average score of student 1001 to 1100 into N3 to N102

6. data2.xls Grading Sheet: Score Column: j Nested loop Row: i • Add grading to Sub student_ave() • A procedure to output grade “Pass” or “Fail” into O3 to O102 • Pass if average >= 80 • Fail otherwise

7. Exercise • Add a function to paint cells below 60 red on Sub grade_6sub Ex. Function to paint cell B3 red Use variable for row number and column number

8. data2.xls Sheet: Score Column: j Row: i Insert “Cells( i , j ).Interior.ColorIndex = 3” into Sub grade_6sub()

9. Statistics-Counters

10. Statistics - Pass and Fail • Procedure to output number of grade “Pass” and “Fail” of 100 student into B12 and B13 on sheet “Statistics” • Data input • Sheet “Score” O3 to O102 • Data output • Sheet “Statistics” B12, B13 • Prepare two variables for counters • Counter for “Pass”: pass • Counter for “Fail”: fail

11. Statistics - Grade • Procedure to output grades(A, B, C, D or F) of 100 student for each subject on sheet “Score” into cells B4 to G8 on sheet “Statistics” • Data input • Sheet “Score” H3 to M102 • Data output • Sheet “Statistics” B4 to G8 • Prepare five variables for counters • For A: a • For B: b • For C: c • For D: d • For F: f