情報基礎B (Computer Literacy)
Lecture 1: Course Overview
Matias Korman
Tohoku University Graduate School of Information Sciences
System Information Sciences
Design and Analysis of Information Systems

About me• Matias Korman– Assistant Professor, Graduate School of Information
Sciences– http://www.dais.is.tohoku.ac.jp/~mati/– Bonus! Find these slides there!
– mati@dais.is.tohoku.ac.jp– Course taught in English– Also speak inCatalan, Spanish, French, Italian, Japanese• Profile– First time to Japan in 2003
– Studied in Tohoku University (06-09)– In 2015 returned to Sendai– Research in theoretical computer scienceTeaching Assistants– Quentin Labernia• English• French• Some German• quentin@dais.is.tohoku.ac.jp– Aji Kasmaji• English• Indonesian• ajiajikasmaji@gmail.com

Slides by Jinhee Chun
– Translated into English by Takeshi Tokuyama– Further edited by meCourse Overview• Contents– Basic knowledge on information technology– Usage of computer– Ofimatic software– The Internet– Programming– Information society and information ethics– Basic usage of a computer (today’s goal)– Learn the potential of a computer• Evaluation– Assignments (and Attendance)

MultiMedia Building• ICL rooms are open except duringlectures– ICL1– ICL2– ICL3– ICL4

8:50-17:50 Linux/Windows*152
8:50-20:45 Linux/Windows*152
8:50-17:50 Linux/Windows*55, MacOS*2
8:50-20:45 MacOS*20

IT Help Desk

MultiMedia Building• Technical Assistants are available for yourquestion– 09:00-17:50– 18:00-20:45

HERE!

Assistant room(red circle below)
ICL2,3

Computer system in Campus• Operating System– Windows○ Graphical user interface (Icon, Mouse)○ Various useful software○ User friendly, available in most home computers– Linux

You know all of this

○ Character user interface○ Secure• Attention!

– Computers are powerful tools– Can also be harmful– Handle with ethics and responsibility

Basic Usage of Windows• Desktop environment– Mouse operation– Application startup• Application example– Command prompt: programming– Text editor: Word
– Spreadsheet: Excel– Presentation: PowerPoint– Internet Browser: Fire Fox, Chrome, Tor

Don’t use Microsoft explorer

Linux• Common Desktop Environment(CDE)– Visual environment (windows-like)– More powerful via command line

• Application examples– Terminal: command line operation,
programming– Text editor: K write– Mailer– Internet browser

TDLR: More complicated and powerfulLoginYou know all of this• Turn on machine and display– Select an OS(Linux or Windows)• Enter your user ID and password

User ID and Initial Password• User ID– Allocated by system administrator– Two different IDs○ Tohoku University ID○ Student ID• Password– Decided by user
– NEVER share• Initial Password– Given by system administrator– Generated from your personal information– Change immediately

（例例）

学籍番号：

A9JB1234

⽣生年年⽉月⽇日：

1990年年1⽉月1⽇日

名前： 徳⼭山（トクヤマ） 出身⾼高校所在地：

東京

利利⽤用者番号は [a9jb1234]

12

初期パスワードの算出

13初期パスワードの算出

tokuya 01
0

t

1
14

初期パスワードの算出

表２．出身⾼高校所在都道府県記号⼀一覧

tokuya 01

1 9 900

01 0 1

1 1
1 990

01 01

m

t

1

m

00 19 9 0

0 1 9 9 0
東京：⼩小⽂文字m

᮷᭲଱唏

懿‫ݩ‬

᮷᭲଱唏

懿‫ݩ‬

۹ၹ᭲
ᶆ༏唏
ટಋ唏
䋠उ唏
ᐾኦ唏
ઊ୵唏
ᐰ䎦唏
ᝮउ唏
䪭๙唏
ᗭ泷唏
䁈ሳ唏
‫܉‬巃唏
䩚Ղ᮷
ᐟॱ૝唏
ෛ侤唏
੄ઊ唏
Ꭺ૝唏
ᐰԳ唏
ઊ໾唏
槱ᰀ唏
ઓᴘ唏
ᶉ䍫唏
䙄Ꭳ唏
ӣ᯿唏
ჴ揷唏
Ղ᮷଱

a
b
c
d
e
f
g
h
i
j
k
l
m
n
o
p
q
r
s
t
u
v
w
x
y
z

य़ᴝ଱
ً䓚唏
ॱᜉ唏
޾ྈઊ唏
澆‫ݐ‬唏
䎦໑唏
䍫ઊ唏
䓈䎦唏
ઊ‫ݗ‬唏
䕿䎦唏
ḕ૝唏
䙄৞唏
ṛᎣ唏
ᐰ䍫唏
֙揷唏
槱઼唏
ᆢ๜唏
य़‫ړ‬唏
䋠઼唏
ỿ㱽䎦唏
䷳娮唏
ͳ΄՜
独ኸ਍ኞ
独य़䭥‫ݳ‬໒ᘏ
独य़਍ᴺ਍ኞ

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V

15

Initial Password
Anyone can generate from your basic information.
Leaving initial password can cause

ACOUNT HACKING

Change it NOW at “Integrated Electronic
Authentication System Login” in CITE website

ComplianceBe specially careful when using
university computers• No online purchases• No movie downloading• Don’t send missiles to North Korea• Don’t print counterfeit money• No Pokemon go server hackYou know all of this

Course Registration• Open your Internet Browser– Go to “Teaching Support System” in CITE
website– Login and click course registration• Only registered user can use PC in acourse (otherwise forced to logout)
This registration is only valid in Multi Media
Building and not related to grading system.

Homework• Prepare a 5 minute presentation• Topic: Information Society and Ethics• i.e., internetbullying
Important!
Countsfor final grade!
• Introduce the problem• Give your opinion• Possible solutions• Make slides in (preferably PDF)
• Deadline: 16 October

情報基礎B (Computer Literacy)
Lecture 2: Ethics and Security
Matias Korman
Tohoku University Graduate School of Information Sciences
System Information Sciences
Design and Analysis of Information Systems

Course Overview• Acquisition of basic knowledge on
Information Technology and Information
Science•Netiquette•Problem Solving•Mathematical Solution

Processing•Visualization•Presentation

Utilization•Intellectual Property Right &Laws•(Security)

EthicsBasic knowledge of information•Computer andNetwork•Logical understanding

Course Information• Mostly Slides• Exercises done in class• Classic textbook• Internet• Handouts
Print out limitation is 120 Pages/
Semester

Information Ethics• Ethics– Right attitude as a human in society– Standard for people to live together

Do not harm others• Aristotle : Mesotes• Confucius(孔⼦子) : 五常の徳： 仁(Humaneness)・義(Justice)・礼(Politeness)・智(Wisdom)・信(Honesty)• Information Ethics– Ethics issues related to computers

Do not harm others using computers

Not even by accident!

Information in Society• Humanity has evolved along with information• Major evolutions– Language– Paper+Pen– Printing– (physical) mail– Telegram– Telephone–…
– Internet• Email, Web, Blog, Twitter, Facebook• Human culture depends on information
infrastructure and information exchange

Information in Society• Humanity has evolved along with information• Major evolutions– Language– Paper+Pen– Printing– (physical) mail– Telegram– Telephone–…
– Internet• Email, Web, Blog, Twitter, Facebook• Human culture depends on information
infrastructure and information exchange

Benefits and Risks of the Internet• Benefit– World wide scale– Lots of information available– Easy to publish and share– Make distances shorter– Each person takes part in a huge events• Facing unspecified large number of people• Risk– Individual act leads to a large effect– Many malicious uses
– Small mistakes have big impact• With great power comes great responsibility• Use internet wisely

Is this new?
• First documented case is in December 1941
- Pearl Harbor- Japanese destroyed eight battleships- High presence in media- “Forced” US to join WWII
• Now happens in a larger scale– Russia interference in US election– Police tweeted “about to raid a terrorist cell”– Clickbait news
– Need to be “first”• Everyone can access a large audience easily– Everyone should study information ethics!

What can we do?
• LEARN– Do not believe any news you see– Numbers can be easily tweaked– Small mistakes have big impact– Try to contrast the news
– Be mindful of “schauenfreude”– Do not share if you are not certain– Most people only read caption• With great power comes great responsibility• Use internet wisely

Crimes one may commit• Violation of Privacy– Stronger than freedom of speech– Disclosing a private life of others(tweet the location of a famous person)• Defamation– Blaming, Discrimination(you could go to jail for trolling!)• Piracy– Using information from others without permission– Putting your photo of a celebrity– Your computer used as spambot• Gambling, trickery, pyramid selling– Your loss fuels loss for others

Illegal acts we ALL do daily• Registering in some website with a fake name
• 5 to 20 years!
• Using an open wi-fi without consent• Open does not mean “please use”
• Posting someone else’s image• Giving credit is not enough• Sharing password with family• My wife cannot benefit from my Netflix!
• Registering with a fake name
• 5 to 20 years!
• Selling on eBay
• Did you declare taxes?

Law point of view
• Prohibited by law– Human rights violation (right to privacy, to beforgotten, etc)
– Easier than regular crimes– You do not see anyone suffer– Easy to anonymize• Unconscious misconduct– Lack of awareness is your fault• Unethical acts should be avoided– Possession of child pornography

Don’t hack your professor’s PC!

How to make a good password?
WINDOWS: Please enter your new password.
USER: cabbage
WINDOWS: Sorry, the password must be more than 8 characters.
USER: boiled cabbage
WINDOWS: Sorry, the password must contain 1 numerical character.
USER: 1 boiled cabbage
WINDOWS: Sorry, the password cannot have blank spaces.
USER: 50boiledcabbages
WINDOWS: Sorry, the password must contain at least one upper case character.
USER: 50FUCKINGboiledcabbages

Internet Security– Having a strong password is essential– Letters and numbers– Special characters– Blood type– Hair color– Long password do not make your account safer!
– Do not use the same password in different pages– Use an algorithm– Even better: password manager

Is this enough?

Internet Security

Internet Security• Security does not depend only on you– Even with Firewall and Anti-virus– Day 0-vulnerabilities can affect any computer– Sell for 200.000$ on black market– Update all software!
– NSA spying on all of us– How much data do you have online?
– One disgruntled employee is all it takes– Most nuclear power plants are infected– Not even aware of themSecurity Example 1• While browsing through internet I saw an add
Your computer is infected,
try our antivirus for free!
• Install the program and computer slows down
• Pay for the pro version and no more
problems!

Malware (Malicious Software)• Malware– Old ones would destroy your data
– Possibly ask for ransom money– New ones give your information• How to get infected– USB– Plug & play devices– Internet○ Downloaded file
○ E-mail attachment○ Logging to “Free_wifi” network○ Man in the middle○ Hard Drive swap
– Software vulnerabilities
always update software!

What can they do?
• Steal your money• They probably don’t care
• Your account is payment for other transactions• Steal your information• friends are potential targets• Destroy data, ask for ransom money• WannaCry, Pirates of the Caribbean 5• Blackmail• Do you want your drunk images on Facebook?
• Have you ever cheated on your boyfriend/girlfriend/dog?
• Become figurehead for larger crimes• Identity theft• Someone could do this course instead of you!

Security Example 2• Google workers in China use new laptops• Use computer as little as possible• Never type passwords with keyboard• Use USB authentication• Shower with USB• Throw computer when returning home
• Aren’t virus only programs?
How much security do you need?

Security Example 3• Why was Bin Laden hard to find?
• He never accessed internet• carry a USB key to internet cafe
• 30 Drive each way
Not practical for us.
Make it not worth the effort!

Self Defense Summary• Very hard/impossible to be secure• Make it not worth for hackers• Never stop learning• National Police Agency Japan Countermeasureagainst Cybercrime– http://www.npa.go.jp/cyber/english/
index.html• National Consumer Affairs Center of Japan– http://www.kokusen.go.jp/
ncac_index_e.html

Spam Mail
• 97% of all mail is unwanted• Most of it are adds
• 99.99999% of them end in spam folder• 46% of people open them
• 11% of them click• ??% end up in a purchase• Millions of dollars in benefits!!
• Imagine how fast would internet can be?
• Never support them!

Data Privacy
Or why is Facebook free?

Privacy in Social Networks

Why are Social Networks Free?
• Sell Ads• Targeted to specific audience• They learn a lot from you• What did you click?
• Whose page do you stalk?
• Several known abuses• Selling information on you being sick• Sharing that you are homosexual• How much would a paid SN cost?

Other problems of SN• Most people get news from social networks• Friends share news, more likely to believe• Friends think alike -> reinforce your opinion• Fake news (more than 50%?)• Obama’s war chest came from social media• Russia influenced the US election through ads on
Facebook• People share explicit images during terrorist attacks• Sharing pictures of your ex-boyfriend/girlfriend

How can we be protected?
• Understand that anything you upload will
become public• Even if sent privately• Think before you share• Tweak settings for extra privacy• Remove metadata of images• Close old accounts

Further Study• Movie/TV• Fifth Estate• Snowden• Black Mirror• Mr Robot• Bruce Schneier blog on security (https://www.schneier.com/)• Essays on Social Sciences (nerdwriter1)• …

Remember the homework!
• Prepare a 5 minute presentation• Topic: anything related to what I spoke• i.e., internet bullying• Introduce the problem• Give your opinion• Possible solutions• Make slides (PDF)• Deadline: 16 October

情報基礎B (Computer Literacy)
Lecture 3: History of Computers
Matias Korman
Tohoku University Graduate School of Information Sciences
System Information Sciences
Design and Analysis of Information Systems

History of Computers• Computer– A machine that carries out arithmetic andlogical operation– Anything from calculator to supercomputers

Early Computers• Pascalina by Blaise Pascal (1640?)• Charles Babbage(1822) programmable• ABC by Atanasoff-Berry (1942) electronic

http://www.infonet.co.jp/ueyama/ip/history/eniac.html (Japanese)More ancienthistory• George Boole (1815-1864): Boolean Algebra• Alan Turing(1912-1954): Turing machine:
Basic idea of logical process by a computer– Church-Turing thesis: Computable functions• First tangible benefit of computers• John Von Neumann (1903-1957)– Added “computer program” (stored program)• Claude Shannon(1916-2001)– Information theory

Boolean algebra and computing• Boolean algebra: Algebra on 0 and 1– 0⊕0 = 0, 0⊕1=1⊕0=1, 1⊕1= 1 (AND operation)– 0⊗0=0, 0⊗1=1⊗0=0, 1⊗1= 1 (OR operation)– NOT(0) = 1, NOT(1)=0(NOT operation)• Every computation can be represented• Easy to implement electronically– Relay, transistor, etc• Information coding (Shannon) : Transform
information into sequences of 0s and 1s

The first “proper” computer• ENIAC (Electronic Numerical Integrator and Computer)– First universal computer– Invented by John William Mauchly and John
Presper Eckert in 1942

http://www.infonet.co.jp/ueyama/ip/history/eniac.html (Japanese)Fun facts about ENIAC– Floor area: 100㎡, Length: 30m, Weight: 30t,
Power Consumption 150kW– Developed secretly on military purpose such as
trajectory calculation and Cryptanalysis– How fast?

http://www.infonet.co.jp/ueyama/ip/history/eniac.html (Japanese)

Hardware Architecture
Control Unit

Instruction
Load

Processing
Instruction

Processing
Unit

Data
Read & Write

ProgramContainer

I/O Controller

Main Memory

I/O Device– Control Architecture operates thosetransaction– Operating System shares those devices

Operating System• Main program– Manages and launches other programs– Also helps with managing files, etc• History of OS– MVS/CMS: OS for IBM Mainframe• Multiple Virtual Storage• Conversational Monitor System• System/360(1964) has established Mainframe features– UNIX: Typical workstation OS– MS-DOS: De facto standard of OS on early PCs– Windows, MacOS: Main OS on current ones
– LINUX: UNIX for PC– Android/iOS for smartphones

Files and Folders• File– A block of information• Document• Program• Image (a picture from a digital camera)• Audio (ex: a song in CD)• Movie• etc– tThe size (amount of storage needed) ismeasured in Bytes• KiloByte (1000), MegaByte, TeraByte … etc

Folders• Folder (“Directory” in Linux)– A virtual container to group files and otherfolders– ex)• Music folder which contains audio files• “My Documents” or “Home Directory” in youraccount at Tohoku University– Common file system in almost all operating
systems

File Structure
/

usr

www

home

b1v

b1c
b1cb123?

system

local

b0

bin

Why tree structures?
• Tree structure is needed to handle large data
– I have more than 1000,000 files in my own PC– We have more than 1000,000,000 web pages in
the world– How to organize them?? Tree is the solution.
• www.dais.is.tohoku.ac.jp/~tokuyama/profile.htm– By using a tree with 6 layers with 20 branches at
each node, how many information can be
represented?

What is file name?
• File name consists of name and extension– Doc1.txt• Extension is a type of file• Some applications use their own extensions– mytext.txt (text file)– mydocument.doc (MS Word 97-2003 document file)– mydocument.docx (MS Word 2007- document file)– myweb.html (html file: for web design)– mypicture.jpg (picture file)Basic File Operations• Open “MyDocument” from icon
• Create folders below– practice– assignment– handout• Move files from folder to another folder• Create file and copy them

COMPUTER BASICS II
INTERNETInternet• Big network connecting many computers• Networks in home, office and School
reach the internet through a provider.

Internet

Internet Provider

LAN: Office, University

Internet• History of the Internet– 1970s: Development on packet communication and TCP/
IP(Transmission Control Protocol / Internet Protocol)– 1980s: ARPANET(Pentagon and UC Berkley)• Academic, Military, Aviation, Space Development• IBM VNET: Corporate network– 1990s: Practical Internet based on e-mail• Alternative communication method to mail and phone• For hearing-impaired person (MCI VIntonCerf)– 1990s latter half: www(world wide web)• one-to-many multimedia information service on web
page• Development exceeding TV– 2000s: smartphones

Internet• WWW(World Wide Web)– Multicasting information service• Open to everybody: one-to-many• User select information he/she needs– We use search engines to find information• i.e. google– Links between texts with Hypertext– Visualization on Internet Browser– Other applications (Mail, RSS, svn, etc)

Internet• IP address(Internet Protocol Address)– Used identify a machine on network– Each computer has a different address– Four numbers separated with dots
• Ex) 192.168.0.1– Provided by an agency in each country• JPNIC(Japan Network Information Center)

Internet• Domain names– cs.he.tohoku.ac.jp– http://www.ise.he.tohoku.ac.jp• Domain name Servers (DNS) convert IPs tonames to make it easier to remember• More robust to webpage failure, split
load, …

E-mail• System to send messages between people
SMTP

Internet

MTA

SMTP
MTA

MUA

MUA

– MUA(Mail User Agent)• Mailer– MTA(Mail Transport Agent)• Program on mail server– SMTP(Simple Mail Transport Protocol)

E-mail• Mail Address– Consists of user name and domain nameb1xxxxx@cs.he.tohoku.ac.jp– Domain• Indicates address of mail server• Name of university or company• Easy to find (using “DNS”)

情報基礎B (Computer Literacy)
Lecture 4: Databases and Spreadsheets

Matias Korman
Tohoku University Graduate School of Information Sciences
System Information Sciences
Design and Analysis of Information Systems

Databases• Database– System to store and organize large amount of data
– Need good methods to use (search and extract)– Address book, Music database, University Library,
DNA database, Criminal Records, …
• Operation– Data collection– Database construction– Data management○ Storing data, search, modify– Data analysis and understanding• Major database structures• Relational Database, Functional Database, XML
DatabaseDatabase model
Relational database model
Link tables

Hierarchical database

NetworkRelational Databases• Based on the theory of relational data model
proposed by Edgar F. Codd in IBM(1970)• Each Table contains information of one type(say students, courses)• Combine information with IDs (i.e., student
24 obtained a C score in course 12)• SQL (Structured Query Language) used toobtain and modify information

SELECT Math ≧ 80 AND English ≧ 90 FROM Exam;Example of Relational Database:
Sales Database
Sales: 2010Jan

Sales: 2010Dec

Item1

Item2

Item3

Item4

Item5

Item6

Item7

Item8

Item9

Item
10

Hokkaido

136

84

192

102

174

73

51

76

84

105

Aomori

127

122

63

70

35

224

75

246

230

253

Iwate

24

225

120

214

170

142

252

80

253

124

Miyagi

147

62

172

58

218

75

208

224

161

111

Akita

99

221

148

188

140

69

184

78

172

204

Yamagata

143

157

184

105

166

129

78

206

164

Fukushima

148

99

105

49

253

63

102

113

Ibaraki

49

62

240

51

107

223

147

199

Tochigi

64

159

191

24

120

215

210

Gunma

59

87

131

211

83

249

Saitama

92

131

99

193

240

Chiba

118

58

60

46

245

Item1

Item2

Item3

Item4

Item5

Item6

Item7

Item8

Item9

Item
10

Hokkaido

110

218

85

133

25

33

117

111

233

65

Aomori

145

89

31

141

104

217

88

99

36

263

Iwate

126

29

79

70

155

113

144

211

128

236

Miyagi

108

92

263

100

249

134

52

72

208

163

Akita

152

113

33

41

153

48

147

130

79

201

189

Yamagata

150

93

115

166

120

46

260

77

113

54

163

185

Fukushima

206

256

109

60

230

61

157

238

117

82

107

140

Ibaraki

220

263

140

250

225

30

246

171

150

25

249

123

54

Tochigi

153

118

57

42

186

197

182

111

85

225

36

221

263

138

Gunma

237

87

137

129

199

151

128

115

163

214

105

184

52

74

144

Saitama

176

58

82

86

268

158

191

234

70

216

206

93

240

55

153

Chiba

116

58

175

237

103

72

34

165

37

101

Prices
Item1

Item2

Item3

Item4

Item5

Item6

Item7

Item8

Item9

Item10

Cost price

874

574

785

250

184

385

456

784

890

458

Selling price

980

870

900

500

354

450

980

800

980

650

Development of Database Technology
Data Collection, Database Construction
1960s

Database Management System
1970s-Early 1980s

Developed Database
System
Mid 1980s-Today

Data Warehousing
Data Mining

Web Database
SystemLate 1980s-Today

1990s-Today

New Generation Connected Information System
2000s-Today

Using EXCEL• Application software of Microsoft• Data is stored in spreadsheets (idea from the 1960th)– VisCalc on Apple II (1979) changed the use of PC• Specialized for data analysis– Calculation○Simple calculation○Math Functions– Data to graph– Data collection to Database(small DB)– Numeric data, character data
• Programming with VBA– Software programming○Accounting software, game and etc

Open Excel
Start↓
All Programs↓
Microsoft Office↓
Microsoft Office Excel 2010

Entering Data
Selected cell

• Select a celland type• Active cell
• Cell number– A1, C2

○ Column– A, B, C, D, …

○ Row

– 1, 2, 3, 4, …

• Sheets separate
info– Sheet index○ Sheet1

Entering Data
Math bar• Editing isshown in themath bar

Simple Calculation• Data can be simple or derived
– A1
– A2
– A3
– A4
– A6
– A7
– A8
– A9
– A10

50
75
5
8
=A1+A2=A2-A3=A3*A4=A1/A3=(A1+A3)*A4 – (A2+A4)/A3

Other Math Operators
• Sum

– E1
– E2

• Average
– E3
– E4
– E5

=A1+A2+A3+A4=sum(A1:A4)=(A1+A2+A3+A4)/4=E1/4=average(A1:A4)• Max, Min
– E6
– E7

=max(A1:A4)=min(A1:A4)

Simple Exercise Grade students• Create Exam Data
– Items• ID and 3 courses (Japanese, English, Math)– 10 students (1, 2 ,…, 10)

Or download from my webpage
Let’s compute the Average, Total Score and bestscore of each student!

Sum• Enter a formula below=B2+C2+D2
Sheet: simplecalc

Average

=E2/3

or

=(B2+C2+D2)/3
Sheet: simplecalc

Beware of cell format!
Set decimal places

Sheet: simplecalc

Copying Cells• Copy/Paste• Your new bestfriend• Formulas arecopied and“Translated”• Use & To prevent
translation

Sum=sum(Cell range)=sum(B2:D2)
Cell range：B2 to D2

Sheet: mathfunction

Average=average(Cell range)=average(B2:D2)
Cell range：B2 to D2

Sheet: mathfunction

Other Math Functions

Other Math Functions“Search is your friend”

情報基礎B
Lecture 5: Complex formulas
Matias Korman
Tohoku University Graduate School of Information Sciences
System Information Sciences
Design and Analysis of Information Systems

PLAYING WITH SPREADSHEETSComparing data
• Comparison
– A15
– B15
– C15
– D15
– E16

=10>3
=10<3
=(10*2)<3=C1>C2=C7>E2• Result is “TRUE” or “FALSE”

Interesting programs?
• Branching• Action depends on something else
• Implemented with IF function– IF(criterion, action1, action2)• Proceed action1 when the criterion is true,
otherwise proceed action2– =IF(logical_test, value_if_true,
value_if_false)
Logical test or cell number

Characters with “” or numbers

Simple Program• Operators used in logical tests
– A=B
– A>B
– A<B
– A<=B– A>=B– A<>B

A is equal to B
A is larger than B
A is smaller than B
A=B or A<B
A=B or A>B
A is not equal to B

Simple Program=IF(logical_test, value_if_true, value_if_false)
logical_test

TRUEvalue_if_true

FALSEvalue_if_false=IF(logical_test，1, 0)•A16=IF(A15, 1, 0）=IF(logical_test，“string”, “string”)•B16=IF(A15, “True”, “False”）•C16=IF(C1>C2, “Correct”, “Wrong”）

Grading a student• D16=IF(A1>=80, “Pass”, “Fail”)

>=80
FALSE

Fail

TRUE

Pass

Exercise 1: Coarse grading• Use table from last week
download from https://goo.gl/Kks4Bh• Program a grading system on excel which
outputs “Pass” or “Fail”– Pass: if Score of Japanese, English, and Mathis more than 80– Fail: otherwise– Add “Result” on G1– Same entry for positions G2 to G21

Branching
>=90
FALSE

>=80
FALSE
FALSE
FALSE

>=70
>=60

F

TRUE

A

TRUE

B

TRUE

C

TRUE

D

Branching• Three scores by nesting “IF” operations=IF(B2>=90, “A”, IF(B2>=80, “B”,”OTHER”) )

>=90
FALSE

>=90

FALSE

[OTHER]

TRUE

A

TRUE

B

Do we need to stop at 3?
>=90
FALSE
FALSE
FALSE
FALSE

>=80
>=70
>=60

F

TRUE

A

TRUE

B

TRUE

C

TRUE

D

Writing in Excel• Grade– A 100 > Score >= 90– B 90 > Score >= 80– C 80 > Score >= 70– D 70 > Score >= 60– F 60 > Score=IF(B2>=90, ”A”,
IF(B2>=80, ”B”,
IF(B2>=70, ”C”,
IF(B2>=60, ”D”, “F”))))

Exercise 2: fine grading• Make a grading system which outputs “A”,
“B”, “C”, “D” or “F” for each Subject○A 100 > Score >= 90○B90 > Score >= 80○C80 > Score >= 70○D70 > Score >= 60○F60 > Score– Apply same method to all students– Add “Japanese”, “English” and “Math”
header each on G1, H1 and L1

Counting• How many students got A on Japanese?

=COUNTIF(range, criteria)=COUNTIF(G2:G21, “A”)

Exercise 3: counting• Count numbers of students that got each
grade in each subject
Japanese

English

Math

A

6

4

5

B

4

7

6

C

3

4

2

D

3

1

2

F

4

4

5

Exercise 3: counting• Count numbers of students that got each
grade in each subject
Japanese

English

Math

A

6

4

5

B

4

7

6

C

3

4

2

D

3

1

2

F

4

4

5

SUM

20

20

20

• Sanity check: totals in each course

CHARTSLet’s make pretty drawings!
• Bar chart②

1. Select data range
2. Click “Chart Wizard”
3. Select a chart type③①

Chart• Bar chart⑥⑤⑦

5. Data range is shown. Or
enter a range directly

6. Select “Series”
7. Set Category (X) axis labels

Chart• Bar chart⑧

8. Enter chart title and label for category and valueMany different charts• Bar ChartOther Charts• Pie chart

Even more
• Column chart

How many drawings can we have?
• XY (scatter)情報基礎B (Computer Literacy)
Lecture 6: computer language
Matias Korman
Tohoku University Graduate School of Information Sciences
System Information Sciences
Design and Analysis of Information Systems

Computation• The world of {0, 1}– Boolean algebra– Logical operation• AND operation– 0 × 0 = 0 × 1 = 1 × 0 = 0, 1 × 1 = 1– Series circuit• OR operation– 0 + 0 = 0, 0 + 1 = 1 + 0 = 1, 1 + 1 = 1– Parallel circuit• NOT operation– NOT(0) = 1, NOT(1) = 0

NOTComputation• AND operation– 0 × 0 = 0 × 1 = 1 × 0 = 0, 1 × 1 = 1– Series circuit– Switching : relay, transistor, diodeComputation• OR operation– 0 + 0 = 0, 0 + 1 = 1 + 0 = 1, 1 + 1 = 1– Parallel circuit

Computation• NOT operation– NOT(0) = 1, NOT(1) = 0aNOT(a)

electromanget

Adding two bits
• Circuit to calculate a + b (a, b = 0 or 1)ab

0 + 0 = 00

NOT

1 + 0 = 01
0 + 1 = 01
1 + 1 = 10

Upper Bit

Lower Bit

Computation Model• AND, OR, NOT– Logic operations– Addition, subtraction, …
– IF (via comparison)• Programming model (decision tree model)– Basic math operations– Load data from storage– Move data to storage– IF operations to branch

Program and Decision Tree
IF(B2>=90, “A”,
IF(B2>=80, “B”,
IF(B2>=70, “C”,
IF(B2>=60, “D”, “F”))))

B2
True

A

False

True

B

False

True

C

False

True

False

D
F
Computation proceeds through the tree

New tool: loop
• SUM(A2:A100)– Decision tree becomes deep
– Loop structure– Loop management
Read a cell in• When to stop?
• In Excel…

– By setting range A2:A100

column A
Adder
TemporalResultLoop• Example: SUM(A2:A100)
i=2
i=i+1

Read A(i)

TRUE
Adder

i<100
TemporalResult

FALSE

OutputTemporal Result

Programing
1. Think about an algorithm– e.g. Grading procedure

2. Show a flowchart with decision tree andloop structure
3. Write the algorithm in programming
language– Simple easy vocabulary– No ambiguities

Programing• Make operations for computers– Algorithm design• Logical thinking needed– Coding• Transform idea into “computer language”– Learning a programming language• Read programs• Change/make your own• Debug (error correction) is crucial

Programing hints• Learning programming language– Practice makes perfect• Follow examples– This is a pen > This is a dog– Follow good examples• Learn from the mistakes– This is a apple ??
– I is a man ??
– 99.9999% of computer errors are our fault• System guides in the type of error– Syntax error (i.e., #VALUE)• Guess what is wrong/mistakes– Logical thinking helps

情報基礎B (Computer Literacy)
Lecture 7: If condition with VBA
Matias Korman
Tohoku University Graduate School of Information Sciences
System Information Sciences
Design and Analysis of Information Systems

Variables• A box used to store information• Must declare the type first– e.g. prepare a box “x” to store an integer– Dim x As Integer• Declare variable “x” to store integers– Dim name As String• Stores text instead

name

x

Numeric Data types
Data type
name

Data

Value range

Size

Byte

Small integer

0 to 255

1 byteInteger

Integer-32,768 to 32,767

2 byteLong

Long integer-2,147,483,648 to
2,147,483,647

4 byteSingleShort “real” number±3.4×1038 to ±1.4×10-45

4 byteLong “real” number±1.8×10308 to±4.9×10-324

8 byteDoubleOther Data Types
Data type

Value

BooleanTrue, False

StringText

Date

100/Jan/1 to 9999/Dec/31

Currency

Larger than Long,
922,337,203,477.5808 to 922,337,203,685,477.5807

Other

????

“IF” in Excel Function• Branch with “TRUE” or “FALSE”• IF(logical_test, value_if_true, value_if_false)

Logical formula or Cell number String with “” or just
numbersIｆ - Then - Else in VBA
logical_test

TRUE

FALSE

Action2
If logical_test
Action1
Else
Action2
End IfThen

Action1

Grading in Excel
>=60

TRUE

Pass

FALSE

Fail
• Grading program in previous lecture– Pass if score is more than 60, fail otherwise• D16 = IF(A1>=60, “Pass”, “Fail”)

Grading in VBA
1

Sub seiseki1()

2‘Grading Program3
4

Dim score1 As Integer

5

Dim name1 As String

6
7

name1 = InputBox(“Enter your name.”)8score1 = InputBox(“Enter your score.”)

9
10
11
12
13
14

If score1 => 60 Then
MsgBox “Congratulations!” & name1 & ”, You passed the exam.”
Else
MsgBox name1 & ”, You failed the exam.”
End If

15
16

End Sub

Nesting “IF” conditions
>=90
FALSE
FALSE
FALSE
FALSE

>=80
>=70
>=60

F

TRUE

A

TRUE

B

TRUE

C

TRUE

D

Nesting “IF”s in Excel• Grade– A 100 > Score >= 90– B 90 > Score >= 80– C 80 > Score >= 70– D 70 > Score >= 60– F 60 > Score=IF(B2>=90, ”A”,
IF(B2>=80, ”B”,
IF(B2>=70, ”C”,
IF(B2>=60, ”D”, “F”))))

Nesting “If”s in VBA
logical_test1

FALSE
logical_test2

TRUE
TRUE

Action1
Action2

FALSE

Action3

If logical_test1

Then

Action1
ElseIf logical_test2
Action2
Else
Action3
End IfThen

Exercise 1• Transform this excel formula into a VBA
program=IF(B2>=90, ”A”,
IF(B2>=80, ”B”,
IF(B2>=70, ”C”,
IF(B2>=60, ”D”, “F”))))

Grading Program If-Then-Else
1

Sub seiseki2()

2‘Grading Program If-Then-Else3
4

Dim score As Integer

5

Dim name As String

6
7

name = InputBox(“Enter your name.”)8score = InputBox(“Enter your score.”)

9
10

If

11
12
13

score >=90

Then

MsgBox name & ”, Your grade is A.”
ElseIfscore >=80

Then

MsgBox name & ”, Your grade is B.”

14

ElseIf

15
16

MsgBox name & ”, Your grade is C.”
ElseIfscore >=60
Then

17

MsgBox name & ”, Your grade is D.”

18
19score >=70

Then

Else
MsgBox name & ”, Your grade is F.”

20

End If

21
22

End Sub情報基礎B (Computer Literacy)
Lecture 8: Arrays and Loops
Matias Korman
Tohoku University Graduate School of Information Sciences
System Information Sciences
Design and Analysis of Information Systems

Array• Collection of the same data type

– Useful for lots of data of same type– i.e., grades per studenteIntegersacbArray of Integer

x(0)

x(1)

x(2)

x(3)

f

d

x(4)

x(5)

Index
Element

Name of ArrayDeclaring Arrays
x(1)

x(2)

x(3)

x(4)

Index starts
at “0”

x(0)

x(5)

x(6)

Six boxes to store Integer type variables
Box name: x, Index: 0 to 5

x(1)

x(2)

x(3)

x(4)

x(5)

Dim x(5) As Integer
Array nameMaximum index Data typex(5): create six boxes
named x each of whichcontains an Integer

Declaring Arrays
StudentID: 1
Score: 100

score(0)StudentID: 3
Score: 76

StudentID: 5
Score: 61

StudentID: 2
Score: 65

StudentID: 4
Score: 87

StudentID: 6
Score: 99

score(1)score(3)score(5)score(2)score(4)What if I don’t have Id 0?

Array Declaration with Index
x(1)

x(2)

x(3)

x(4)

Element
Box x(1), x(2), … , x(6)

x(5)

x(6)

Array nameIndex

Dim x(1 to 6) As Integer
Array nameIndex Range

Data typeUsing arrays in VBA
1

Sub array1()

2

Dim score(5) As Integer

3

score(0) = 100

4score(1) = 65

5score(2) = 76

6score(3) = 877score(4) = 618score(5) = 99

910

MsgBox score(0)11

MsgBox score(1)12

MsgBox score(2)13

MsgBox score(3)14

MsgBox score(4)15

MsgBox score(5)16

End Sub

Looping• Repeat same operation several times– i.e., compute average grade of each student– Use a counter to know when to stop
• Counter variable: iCounter initial value
i: Counter namei=0

overwrite iwith i+1

i=i+1
i<=5
FALSE

Counter maximum value

TRUE

Action1

For i = 0 To 5 Step 1

Action1
Next iCounter increment stepWhat is I?
0

Dim i As IntegeriCounter name(arbitrary name): i• What does i = i + 1 mean?
– Store i + 1 onto i– i.e., increment i by 1

i

+ 1

i

1

Sub array3()2

Display StudentID and score of all students using a loop

3

‘score: array name, i: counter name4

Dim score(5) As Integer

5

Dim i As Integer

6
7

score(0) = 100

8score(1) = 65

9score(2) = 76

10score(3) = 8711score(4) = 61

12score(5) = 99

13
14
15

For i = 0 To 5 Step 1
MsgBox “StudentID: ” & i & “,16

Next i17

End Sub

Score: ” & score(i)14

name(0) = “Koji Tanaka”

15

name(1) = “Hiroshi Abe”

16

name(2) = “Akiko Ito”

17

name(3) = “Ichiro Suzuki”

18

name(4) = “Takako Kato”

19

name(5) = “Junpei Kimura”

20
21
22

For i = 0 To 5 Step 1
MsgBox “StudentID: ” & i & “, Name: ” & name(i) & “, Score: ” &score(i)23

Next I24

End Sub

Exercise 2• Make similar program that displays for each student if
they “Pass” or “Fail” (instead of numerical score)• “Pass” only when score is equal or greater than79
• “Fail” otherwise• Display in increasing order of StudentID– Ex. StudentID: 1, Name: Koji Tanaka, Score: 100,->PassSum of Scores• Calculate the sum of score of a student
score(0)score(1)score(2)score(3)i=0, sum=0

sum
i=i+1

i<=5
FALSE

score(4)TRUE

sum=sum+score(i)score(5)iExercise 3• Compute the sum of scores of all students• Report the sum and the average

1

Sub sum()2‘Calculate the sum of score for all student using For - Next

3

‘score: array name, i: counter4‘sum: variable for sum, ave: variable for average

5

Dim score(5) As Integer

6

Dim i As Integer

7

Dim sum As Integer8

Dim ave As Single

9

sum = 010

ave = 0.0

11

score(0) = 100

12score(1) = 65

13score(2) = 76

14score(3) = 8715score(4) = 61

16score(5) = 99

17

For i = 0 To 5 Step 1

18

sum = sum + score(i)19Next i

20

ave =

21

MsgBox “Sum of score for ” & i+1 & “students is ” & sum

22

MsgBox “Average is ” & ave

23

End Sub

sum/6

情報基礎B (Computer Literacy)
Lecture 9: more loops
Matias Korman
Tohoku University Graduate School of Information Sciences
System Information Sciences
Design and Analysis of Information Systems

Let’s get started!
• Download the sample data
• Available in my webpage• http://www.dais.is.tohoku.ac.jp/~mati/• Google my nameActivating Macros in VBA
First we need to activate macros
BONUS: Why aren’t they activated bydefault?

Backup your sheet!
Programming errors can delete your data.
You cannot undo operations!

Activating Macros
Check if you are indata2.xls-[module1]

Insert -> Regular Module1
2

Accessing values
Cells(Row num, Col num)

If Cells(2,3)>60 Then
Cells(4,7) = 30
End Ifdata2.xls

Sum and Average Example
Operation for Rows

1

Sub sum_ave_japanese()

A subject2

Dim i As Integer3

Dim sum As Integer

Sum the grades of the 100
students in Japanese
Loop 100 cells;
100 cells: Row 3 to Row 102
on column B

4
5

Worksheets(“Score”).Activate

6

sum = 078For i = 3 To 102

9sum = sum + Cells( i , 2 )10Next I11

Sum in B103
Average in B104
Use i for cell number

Sheet: Score

12‘Write the Sum in B103

13

Cells( i , 2 ) = sum

14‘Write the average in B104

15

Cells( i + 1, 2 ) = sum / 100

16
17

End Sub

row: iEx 1: Sum and Average for each Subject
Japanese Sub sum_ave_japanese()

EnglishMathChemistry

Science

Dim i As Integer
Dim sum As Integer
Worksheets(“Score”).Activate
sum = 0For i = 3 To 102
sum = sum + Cells( i , 2 )Next iCells( i , 2 ) = sum
Cells( i + 1 , 2) = sum / 100
End Sub

History

Write a procedure to calculate sum and average foreach subject using nested loop.

Ex 1: Sum and Average for each Subject
You can copy and edit for the other subject, but…
Japanese Sub sum_ave_japanese()

Dim i As Integer
Dim sum As Integer
Worksheets(“Score”).Activate
sum = 0For i = 3 To 102
sum = sum + Cells( i , 2 )Next iCells( i , 2 ) = sum
Cells( i + 1 , 2) = sum / 100
End Sub

History

Sub sum_ave_history()
Dim i As Integer
Dim sum As Integer
Worksheets(“Score”).Activate
sum = 0For i = 3 To 102
sum = sum + Cells( i , 5 )Next iCells( i , 5 ) = sum
Cells( i + 1 , 5) = sum / 100
End Sub

EnglishSub sum_ave_english()Dim i As Integer
Dim sum As Integer
Worksheets(“Score”).Activate
sum = 0For i = 3 To 102
sum = sum + Cells( i , 3 )Next iCells( i , 3 ) = sum
Cells( i + 1 , 3) = sum / 100
End Sub

Chemistry Sub sum_ave_chemistry()

Dim i As Integer
Dim sum As Integer
Worksheets(“Score”).Activate
sum = 0For i = 3 To 102
sum = sum + Cells( i , 6 )Next iCells( i , 6 ) = sum
Cells( i + 1 , 6) = sum / 100
End Sub

Math

Science

Sub sum_ave_math()
Dim i As Integer
Dim sum As Integer
Worksheets(“Score”).Activate
sum = 0For i = 3 To 102
sum = sum + Cells( i , 4 )Next iCells( i , 4 ) = sum
Cells( i + 1 , 4) = sum / 100
End Sub

Sub sum_ave_science()
Dim i As Integer
Dim sum As Integer
Worksheets(“Score”).Activate
sum = 0For i = 3 To 102
sum = sum + Cells( i , 7 )Next iCells( i , 7 ) = sum
Cells( i + 1 , 7) = sum / 100
End Sub

Write a procedure to calculate sum and average foreach subject using nested loop.

Nesting For operations
Procedure to output “rownum *colnum” on each cell in
A1 to J10

For j = 1 to 10
For i = 1 to 10
cells(i, j) = i * j
Next iNext jFor i = 1 to 10
For j = 1 to 10
cells(i, j) = i * j
Next jNext iSame results,
Different orderdata2.xls

Using that for many grades

Sheet: Score
Row: iColumn: j

Nested loop
Nested loop
Calculate sum and averagefor each subject.
Nest structure is below
For j ■ to ■column(subject)

For i ■ to ■Next iNext jrow(student)

data2.xls

Using that for many grades

Sheet: Score
Row: i

Nested loop
Nested loop

1

Sub sum_ave_6sub()2

Dim i As Integer3

Dim j As Integer

4

Dim sum2 As Integer

5

Worksheets(“Score”).Activate

6

Column: j

Be careful when
initialize “sum2”

7

For j = 2 To 78

sum2 = 0

9

For i= 3 to 102

10sum2 = sum2 + Cells(i, j)

11

Next i

12‘Sum B103

13

Cells( i , 2 ) = sum1

14

‘Average B104

15

Cells(i+1 , 2) = sum1 / 100

16

Next i17

End Sub

Grading for each subject

Grading for Japanese
Recall the code for one student:

Make a program to reportJapanese grades into H3-103

If Cells(3, 2) >= 90 Then
Cells(3, 8) = “A"
ElseIf Cells(3, 2) >= 80 Then
Cells(3, 8) = “B"ElseIf Cells(3, 2) >= 70 Then
Cells(3, 8) = “C"
ElseIf Cells(3, 2) >= 60 Then
Cells(3, 8) = “D"ElseCells(3, 8) = “F"End If170

Grading Japanese・Grading criterion
A: if score >= 90
B: if 90 > score >= 80
C: if 80 > score >= 70
D: if 70 > score >= 60
F: if 60 > score

1

Sub grade_jp()

2

Dim i As Integer

3

Worksheets(“Score”).Activate

45

For i = 3 To 102

6

If Cells(i, 2) >= 90 Then

7

Cells(i, 8) = “A”

8

ElseIf Cells(i, 2) >= 80 Then

9

Cells(i, 8) = “B”

10

ElseIf Cells(i, 2) >= 70 Then

11

Cells(i, 8) = “C”

12

ElseIf Cells(i, 2) >= 60 Then

13

Cells(i, 8) = “D”

14

Else

15

Cells(i, 8) = “F”

16

End If17

Next i18

End Sub

data2.xls
Sheet: Score

Row: iGrading each subject Nested loop

Write a procedure to grade all students in all courses
using nested loop.
Operation Order:
Japanese
student 1001 to 1100
Englishstudent 1001 to 1100…
Sciencestudent 1001 to 1100
172

Grading each Subject
Nested loop
・Grading criterion
A: if score >= 90
B: if 90 > score >= 80
C: if 80 > score >= 70
D: if 70 > score >= 60
F: if 60 > score

1

Sub grade_6sub()2

Dim i As Integer3

Dim j As Integer

4

Worksheets(“Score”).Activate

5

For j = 2 To 76

For i = 3 To 102

7

If Cells(i, 2) >= 90 Then

8

Cells(i, 8) = “A”

9

ElseIf Cells(i, 2) >= 80 Then

10

Cells(i, 8) = “B”

11

ElseIf Cells(i, 2) >= 70 Then

12

Cells(i, 8) = “C”

13

ElseIf Cells(i, 2) >= 60 Then

14

Cells(i, 8) = “D”

15

Else

16

Cells(i, 8) = “F”

17

End If18

Next i19

Next j20

End Sub

data2.xls
Sheet: Score
Row: iCol: j情報基礎B (Computer Literacy)
Lecture 10: VBA in Excel
Matias Korman
Tohoku University Graduate School of Information Sciences
System Information Sciences
Design and Analysis of Information Systems

Two dimensional Arrays
Array
x(9)

Array
y(5)

Array
z(5)

2-dimensional ArrayDeclaring Two dimensional arrays• ArrayName(Row index range, Column index range)

Dim a(1 to 10,1 to 3) As Integer
Array nameIndex Range

Data Type

2-dimensional array (score per student and course)
Array name: a
Number of variables: 10*3=30

Equivalent in Excel
Cells(row, column)

Row number starts from 1 in Excel!

Beware of Switch!
Cell Index：C2
Range(“C2”)orCells(2,3)
in VBA
Range(“Column Row”)

Cells(Column index, Row index)

Methods• Operation for object– Delete, Open and more
1

ThisWorkbook.Worksheets("Sheet1”).Range("A:A”).Delete

Affects
wholecolumn!
Method (operation we want to do)
Separator between object and method

Method examples• Many ways of interacting with Excel– Color, Value, … anything!
– Google for more!
1

ThisWorkbook . Worksheets("Sheet1") . Range("A:A") . Value = 1

1

ThisWorkbook . Worksheets("Sheet1") . Range("A:A") . Interior.ColorIndex = 4

1

ThisWorkbook . Worksheets("Sheet1") . Range("A:A") . Delete

Separating Procedures• Code begins from “Sub” to end with “End Sub”
1
2

Sub exercise1()
ThisWorkbook.Worksheets("Sheet1").Range("A:A").Value = 1

3

End Sub

1

Sub exercise2()

2

ThisWorkbook.Worksheets("Sheet1").Range("A:A").Interior.ColorIndex=4

3

End Sub

1

Sub exercise3()

2
3

ThisWorkbook.Worksheets("Sheet1").Range("A:A").Delete
End SubAffecting multiple cells• Range operation• i.e., set the cell value of several cells– Different code, same result
1
2

Sub Example_Range1()
ActiveSheet.Range(“A1”).Value = 10

3

End Sub

1

Sub Example_Cells1()

2
3

ActiveSheet.Range(1, 1).Value = 10
End Sub

Simple programs• Output the sum of A1 and A2 to A3 with Range
12
3

Sub Example_Range3()
Range(“A3”) = Range(“A1”) + Range(“A2”)End Sub• Output the sum of A1 and A2 to A4 with Cells
1
2
3

Sub Example_Cells3()
Cells(4,1) = Cells(1,1) + Cells(2,1)
End Sub

Sum of Cells B1 to B10• Output the sum of B1 to B10 to B11 with Range
12

3

Sub Example_Range4()
Range(“B11”) = Range(“B1”) + Range(“B2”)+ Range(“B3”) + Range(“B4”) + Range(“B5”)+ Range(“B6”) + Range(“B7”) + Range(“B8”)+ Range(“B9”) + Range(“B10”)
End Sub

Can you program this in a better way?

Sum of Cells B1 to B10• Output the sum of B1 to B10 to B11 with Cells
1

Sub Example_Cells5()

2

Dim i As Integer3

Dim sum1 As Integer4

sum1=05678

For i = 1 to 100
sum1 = sum1 + Cells((i, 2)
Next i9
10
11

Cells(i, 2) = sum1
End Sub情報基礎A
Lecture 11: Batch processing
Matias Korman
Tohoku University Graduate School of Information Sciences
System Information Sciences
Design and Analysis of Information Systems

Average Score for one Student
Column Operation
Make a procedure to compute theaverage score of student 1001 into N3

1

Sub student_ave()2

Dim j As Integer3

Dim sum3 As Integer

4

Worksheets(“Score”).Activate

5
6

sum3 = 0

7
8

For j = 2 To 79

sum3 = sum3 + Cells( 3, j )Sheet: Score

10

Next jColumn: j

12

data2.xls

11
Cells( 3, j + 6) = sum3 / 6

13
14

End Sub

Same operation for 100 Students

Must use a second nested loopAverage Score for 100 Students
Procedure that computes average score of each student

1

Sub student_ave()2

Dim i As Integer3

Dim j As Integer

Sheet: Score

4

Dim sum4 As Integer

5

Worksheets(“Score”).Activate

Column: j

6

data2.xls

Row: iOne loop is nested
inside the other7

For i = 3 To 102

8

sum4 = 0

9

For j = 2 To 710

sum3 = sum3 + Cells( i, j )11

Next j12

Cells( i, j + 6) = sum4 / 6

13

Next i14

End Sub

data2.xls

Grading
Nested loop
• Lets add grading
student_ave()• Modify so that it writes“Pass” or “Fail” intocolumn O• Pass if average >= 80• Fail otherwise

Sheet: Score
Column: j
Row: i1

Sub student_ave()2

Dim i As Integer3

Dim j As Integer

4

Dim sum4 As Integer

5

Worksheets(“Score”).Activate

67

For i = 3 To 102

8

sum4 = 0

9

For j = 2 To 710

sum3 = sum3 + Cells(i, j)

11

Next j12

Cells(i, j + 6) = sum4 / 613
14

If Cells(i, j+6) >= 80 Then

15

Cells(i, j+7) = “Pass”

16

Else

17

Cells(i, j+7) = “Fail”

18

End If19

Next i20

End Sub

Exercise• Make a function that highlights cells with scorebelow 60
• Name it Sub grade_6sub

Ex. Function to paint cell B3 red
1Cells( 3 , 2 ). Interior.ColorIndex = 3

Add it into grade_6 sub
1

Sub grade_6sub()2

Dim i As Integer3

Dim j As Integer

4

Worksheets(“Score”).Activate

5

For j = 2 To 76

For i = 3 To 102

7

If Cells(i, 2) >= 90 Then

8

Cells(i, 8) = “A”

9

ElseIf Cells(i, 2) >= 80 Then

10

Cells(i, 8) = “B”

11

ElseIf Cells(i, 2) >= 70 Then

12

Cells(i, 8) = “C”

13

ElseIf Cells(i, 2) >= 60 Then

14

Cells(i, 8) = “D”

15

Else

16

Cells(i, 8) = “F”

17

End If18

Next i19

Next j20

End Sub

data2.xls
Sheet: Score
Column: j
Row: iAdd “Cells( i , j ).Interior.ColorIndex = 3”
into Sub grade_6sub()Statistics-Counting occurrences

Statistics - Pass and Fail• Modify previous program tocompute of “Pass” and “Fail”
grades• Report B12 and B13 on sheet“Statistics”• Use after Sub student_ave()– Data input• Sheet “Score” O3 to O102

– Data output• Sheet “Statistics” B12, B13– Prepare two variables for counting• Counter for “Pass”: pass
• Counter for “Fail”: fail1

Sub stat_pass_fail()2

Dim i As Integer3

Dim pass As Integer

4

Dim fail As Integer

5

Worksheets(“Score”).Activate

6
7

pass = 08

fail = 0910For i = 3 To 102

11

If Cells(i, 15) = “Pass” Then

12

pass = pass + 1

13

Elseif Cells(i, 15) = “Fail” Then

14

fail = fail + 1

15

End If16

Next i

17
18

Worksheets(“Statistics”).Cells(12, 2) = pass

19

Worksheets(“Statistics”).Cells(13, 2) = fail20

End Sub

Easy Exercise• Make a new program to count grades• Use Sub stat_pass_fail()to report grades• Count subjects on sheet “Score” separately• Report into cells B4:G8 on “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

1

Sub stat_grade()18

For i = 3 To 102

2

Dim i As Integer

19

If Cells(i, 2) >= 90 Then3

Dim j As Integer

20

Cells(i, 8) = “A”

4

Dim a As Integer

21

ElseIf Cells(i, 2) >= 80 Then5

Dim b As Integer

22

Cells(i, 8) = “B”

6

Dim c As Integer

23

ElseIf Cells(i, 2) >= 70 Then7

Dim d As Integer

24

Cells(i, 8) = “C”

8

Dim f As Integer

25

ElseIf Cells(i, 2) >= 60 Then

26

Cells(i, 8) = “D”

27

Else

28

Cells(i, 8) = “F”

9
10

Worksheets(“Score”).Activate

11
12

For i = 8 To 1329

End If

13

a=0

30

Next i

14

b=0

31

15

c=0

32

Worksheets(“Statistics”).Cells(4, j-6) = a

16

d=0

33

Worksheets(“Statistics”).Cells(5, j-6) = b

17

f=0

34

Worksheets(“Statistics”).Cells(6, j-6) = c

35

Worksheets(“Statistics”).Cells(7, j-6) = d

36

Worksheets(“Statistics”).Cells(8, j-6) = f

37

Next j38

End Sub

1

Sub stat_grade()18

For i = 3 To 102

2

Dim i As Integer

19

If Cells(i, 2) >= 90 Then3

Dim j As Integer

20

Cells(i, 8) = “A”

4

Dim a As Integer

21

ElseIf Cells(i, 2) >= 80 Then5

Dim b As Integer

22

Cells(i, 8) = “B”

6

Dim c As Integer

23

ElseIf Cells(i, 2) >= 70 Then7

Dim d As Integer

24

Cells(i, 8) = “C”

8

Dim f As Integer

25

ElseIf Cells(i, 2) >= 60 Then

26

Cells(i, 8) = “D”

27

Else

28

Cells(i, 8) = “F”

9
10

Worksheets(“Score”).Activate

11
12

For i = 8 To 1329

End If

13

a=0

30

Next i

14

b=0

31

15

c=0

32

Worksheets(“Statistics”).Cells(4, j-6) = a

16

d=0

33

Worksheets(“Statistics”).Cells(5, j-6) = b

17

f=0

34

Worksheets(“Statistics”).Cells(6, j-6) = c

35

Worksheets(“Statistics”).Cells(7, j-6) = d

36

Worksheets(“Statistics”).Cells(8, j-6) = f

37

Next j38

End Sub

Can you do better?

Bonus Exercise• Let’s look for best student in each course• Highlight in green each highest score
Beware! More than one student can win!
HINT: use a counter• Call it sub bestStudent()
BONUS: can you show the ids in a MsgBox?

情報基礎A
Lecture 12: Combining all together
Matias Korman
Tohoku University Graduate School of Information Sciences
System Information Sciences
Design and Analysis of Information Systems

Inserting a table1

Sub chart()

2
3

Worksheets(“Statistics”).Activate

45Charts.Add

6

ActiveChart.ChartType = xlColumnStacked100

7

ActiveChart.SetSourceData Source := Sheets(“Statistics”).Range(“A2:G8”), PlotBy := xlRows

8

ActiveChart.Location Where := xlLocationAsObject, Name := “Statistics"

9

End Sub

Combining it all• Make a procedure that computes:– Average score of each course– Total score of each course– Average score of each student– Give a A-E score per student per course– Give a global pass/fail per student– Computes the number of pass/fails per course– Makes a chart showing all of this information

Combining it all• Make a procedure that computes:– Average score of each course– Total score of each course– Average score of each student– Give a A-E score per student per course– Give a global pass/fail per student– Computes the number of pass/fails per course– Makes a chart showing all of this information

Sounds Familiar?

Let’s nest programs• We have 6 separate programs that do so• Sub sum_ave_6sub()• Sub grade_6sub()• Sub student_ave()• Sub stat_pass_fail()• Sub stat_grade()• Sub chart()– Instead of copy/paste and fixing errors, let’s
make a program that calls all of them
– Use Call “Procedure name”

Invoking other programs• Procedures are executed in order1

Sub score()2
3Call sum_ave_6sub4

Call grade_6sub5

Call student_ave

6Call stat_pass_fail7

Call stat_grade

8

Call chart

9
10

End Sub

Processing several files at once
• Download collection of grades• Available on my webpage• http://www.dais.is.tohoku.ac.jp/~mati/

Remember to make BACKUPS!!

Scores of 20 courses and 1 summary

Exercise• We want to score all 20 courses• We want a program that computes:• For each class• Average and sum per course• Score per student and global pass/fail• Statistics and chart on each course• charts, etc• Write statistics of 20 classes into score.xls
Unfeasible by hand!!

Operating files with VBA• Opening one file1

Workbooks.Open Filename := “class1.xls”• Opening 20 files one by one– Must use variables to create filenames
1

For i= a To 20

2

Workbooks.Open Filename := “class” & i & “.xls”

3

Next i• Saving and closing active file1

ActiveWorkbook.Save

2

ActiveWindow.CloseAccessing data from other files• How can we copy cell the number who got “A” inJapanese on class3.xls onto seiseki.xls?
– From cell B4 on sheet “Statistics” to B3 on score.xls
1Workbooks("seiseki.xls").Worksheets("平成21年年").Cells(3, 2) = Worksheets("統計").Cells(4, 2)

Be careful with file location!

Copy the path\\netsrv22\c90a1rlu\MyDocuments\foo\bar\baz.xls

1 Sub score()
Dim i As Integer2
Dim m As Integer3
Dim n As Integer4
Dim x As Integer
5
Workbooks.Open
6
Filename:=“\\netsrv22\c90a1rlu\MyDocuments\foo\bar\baz.xls"
7
For i = 1 To 20
8
Workbooks.Open Filename:="\\netsrv22\c90a1rlu\MyDocuments\foo\bar\class" & i & ".xls"
9Call goukei_6kamoku
10
Call hyouka_6kamoku
11
Call kojin_heikin
12Call toukei_gouhi
13
Call toukei_hyoka
14Call graph
15
Workbooks(“baz.xls").Worksheets(“2011”).Cells(52, i + 1) = Worksheets(“Statistics”).Cells(12, 2)
16
Workbooks(“baz.xls").Worksheets(“2011”).Cells(53, i + 1) = Worksheets(“Statistics”).Cells(13, 2)
17
x=0
18
For m = 1 To 619
For n = 2 To 6
20
Workbooks("seiseki.xls").Worksheets(“2011").Cells(n+1+x,i+1)=Worksheets(“Statistics").Cells(n+2,m+1)
21
Next n22
x=x+823
Next m24
ActiveWorkbook.Save
25
ActiveWindow.Close
26
27 Next i
28 Workbooks("seiseki.xls").Save
29 End Sub

Submit your file• mati@dais.is.tohoku.ac.jp• Do not forget [KISO2017] in the subject• Filename with your student ID• Deadline 22nd January

Do not just copy!