情报基础ALecture12.pptVIP

情報基礎A　　Lecture 12 Takeshi TokuyamaTohoku University Graduate School of Information SciencesSystem Information SciencesDesign and Analysis of Information Systems Programming VBAScore Data Processing Preparation Data Processing –Score Data- Download “hogehoge” Code in Regular Module Insert - Regular Module 1 2 Check if you are in data2.xls-[module1] Backup your sheet Right Click Sheet “Score” “Score(2)” Backup of Sheet ”Score” 4 5 Backup your sheet in case of programming errors. Those errors might delete your data. You cannot undo executed operations. 1 2 3 Sum and average For - Next For i = 1 to 10 　　cells( i , 1 ) = i Next i For j = 1 to 10 　　cells( 1 , j ) = j Next j Ex.1 Procedure to enter row number （Row numbers in A1-A10） Ex.2 Procedure to enter column number （Column numbers in A1-J1） Row Operation Column Operation Cells(Row num, Col num) Cells( i , Col num) Put i into row num Column: j Row: i Cells(Row num, Col num) Cells(Row num, j ) Put j into col num Sum and Average -Japanese- Sum of 100 students for Japanese Data range for loop: B3 to B102 100 cells: Row 3 to Row 102 on column B Operation for Rows Sum in B103 Average in B104 Use i for cell number A subject row: i 1 Sub sum_ave_japanese() 2 Dim i As Integer 3 Dim sum1 As Integer 4 5 Worksheets(“Score”).Activate 6 sum1 = 0 7 8 For i = 3 To 102 9 sum1 = sum1 + Cells( i , 2 ) 10 Next I 11 12 ‘Sum B103 13 Cells( i , 2 ) = sum1 14 ‘Average B104 15 Cells( i + 1, 2 ) = sum1 / 100 16 17 End Sub Sheet: Score data2.xls For – Next(Nesting) Ex.3 Procedure to output “rownum * colnum” on each cell in A1 to J10 Nested loop Cells(Row num, Col num) Cells( 　 i 　 , j ) Put i as row number, j as column number For i = 1 to 10 　　　For j = 1 to 10 　　　　　　cells(i, j) = i * j 　　　Next j Next i Column: j Row: i Nest two For ~ Next For j = 1 to 10 　　　For i = 1 to 10 　　　　　　cells(i, j) = i * j 　　　Next i Next j Same results, Different o