310-IT-2109 IT For Construction - Spreadsheet 2015 Final

Introduction to
spreadsheet
Information Technology for
Construction
:: Spreadsheet ::
1. Overview
2. Applications of spreadsheet
3. Advance spreadsheet programming
Department of Civil Engineering

Chulalongkorn University
Bangkok, Thailand
Copyright 2005 Vachara Peansupap. All Rights Reserved.
workbook
1. Overview
column
Spreadsheet is the electronic document that

supports the calculation
It contains with row and column, in which it
is called cell
Each cell has its name that represent a cell
location such as A1 (column A row 1)
To support the calculation, the cell can
contains function and number. However, we
can put the text in to cell as well.
cell
row
Worksheet
tab
Component of worksheet
Horizontal scroll
bar
1. History of spreadsheet
programs
Lotus 123 (Dos version)
In the past, several spreadsheet

programs were available such as
Lotus 123
Borlands Quattro Pro
Microsofts Excel
Open office (cal)
Lotus introduced programmable macro

commands to spreadsheets
Lotus 1-2-3 (windows)
Quattro Pro
Open Office (cal)
Spreadsheet functions
Store and process data

Display data graphically
Analyze data statistically
Fit algebraic equations
Solve optimization problems
Microsoft Excel
2. Applications in
construction
Spreadsheet program can be applied

in construction works such as
Bidding
Estimating
Structural design
Graphical report
Two Types of Paste

Object
Example of graph
considering updated data.
Spreadsheet program offers several

standard graph to present the data
Paste is used to copy object without
Paste Link is used to copy object with

automating updated data.
Paste spreadsheet ->

word
Paste Link spreadsheet

-> word
2
1
3. Protection and security
Spreadsheet program offers the ability

to protect or lock on specific cell.
This ability assists to protect the user
to modify the cell that may contains
value or formula.
Some spreadsheet program can
protect to open the worksheet.
3. Protection and security

There are three levels of spreadsheet
security and protection.
Protection Worksheet
Protection Workbook
File Security
3.1 Protection Worksheet
3.2 Protection workbook
2
Protect
Worksheet
1
Protect
Workbook
2
Uncheck
locked
Protect Workbook
Protect Workbook
(Window)
(Structure)
3.3 Security of File
4. Database functions
Sort (Ascending or Descending)

Search (Specific criteria or terms)
Select the data in column.
As spreadsheet program contains

information and data in column and
row, users can
Users can use the spreadsheet to

perform some database functions
Quiz
What is the benefits of using

spreadsheet for engineers?
What the difference feature between
spreadsheet with word processing?
5. Multiple Sheet
As the spreadsheet provides multiple

sheet, it allows users to gain the multidimensional function such as
Worksheet 1 contains the labor cost
Worksheet 2 contains the material cost
Worksheet 3 contains the equipment cot
Worksheet 4 is the link of labor, material,
and equipment cost.
Functions, Macro and VB

script
Worksheet Functions
Function (argument1, argument2, )
Macro Function - Macro is the

automatic command that keep the
records of users
VB script is the advance programming
on spreadsheet
Menu Formula
Functions in Excel
Characteristic of Formula
Formula (Argument1, Argument2, )

Argument is the input data that will use in the
functions. There are six characteristics of
argument:
Number
Text Text or 5
Logical values i.e. True or False
Error value i.e. #N/A
Reference i.e. B5, $B5 or R[6]C[1]
Arrays i.e. {a,b,c,d}
Financial
Lookup & Reference
Text
Math & Trig
Database
Logical
Engineering
Date & Time
Statistical
Information
Lookup & Reference
ADDRESS (Row, Col, Ref, Type, File)

=
=
=
=
=
=
Address (10,5) = $E$10

Address (10,5,1) = $E$10
Address (10,5,4) = E10
Address (10,5,4,True) = E10
Address (10,5,4,False) = R[10]C[5]
Address (10,5,4,False, Sheet1.xls) =
Sheet1!R[10]C[5]
Lookup & Reference
CHOOSE (Index_num, Value, Value,..)
Lookup & Reference
= Choose (2, A, B,C) = B

= Choose (3, 5, 6, 7, 8) = 7
HLOOKUP, VLOOKUP
HLOOKUP -> search data from horizontal
in Table
VLOOKUP -> search data from vertical in
Table
COLUMN (Reference)
= Column (B3) = 2
Lookup & Reference
Lookup & Reference
LOOKUP (value, set of group data)

= LOOKUP (b, {a,b,c;10, 20, 30}) =
20
= LOOKUP (a, {a,10;b,20;c,30})=
10
VLOOKUP(B1,E2:G4,3)
TEXT functions
FIND (text_search, text)

= Find (u, Chulalongkorn University) = 3
= Find (U, Chulalongkorn University) =
15
= Find (Un, Chulalongkorn University) =
15
VBA Random
TEXT functions
=LEFT("Chulalongkorn University",5) =
Chula
=RIGHT("Chulalongkorn University",8) =
iversity
=MID("Chulalongkorn University", 6, 8)
=longkorn
Case study
Sub rndNo()
Dim str As String
For i = 1 To 10
str = str & CStr(Rnd) & vbCrLf
Next i
MsgBox str
End Sub
Based on your experience, please develop

the spreadsheet applications to help
construction work.
Example:
Maintenance of master checklists

Quantity takeoff
Productivity and cost analysis
Analysis of construction and subcontractor bids
Distribution of overhead and indirect costs
Macro
Macro
Example of Macro
Define Macro Name

Create
Recording of macro
Example of macro
Establish of macro name
Example of macro
Example of macro code
User interface of Visual

basic in spreadsheet
Visual Basic
On the Tools menu in Microsoft Excel, point

to Macro, and then click Visual Basic Editor.
On the Insert menu, click Module.
Type your code into the code window of the
module.
If you want to run the macro from the
module window, press F5.
When you're finished writing your macro,
click Close and Return to Microsoft Excel on
the File menu.
Example of Future
Spreadsheet
Multiple Sheet
Cell
Multiplespreadsheet
Multiple Sheets
Collaboration
Cell
Collaboration
Multiplespreadsheet
Function
Application
Function
for
calculation
Exercise 2 Develop SCurve (Visual Basic)
Exercise 01
In this exercise, the engineering have to

classify the level of labor performance. The
score is gathered and calculated. Then we
need to translate score into grade. How we
applied the spreadsheet to help this.
Solutions:
IF(C2>=80,A", IF(C2>=70,B",
IF(C2>=60,C", IF(C2<60,D"))))
This exercise would like to build the graphic

presentation of project planning. This example
show the tips for drawing line
ActiveSheet.Shapes.AddLine (X, Y, X1, Y1).Select
ActiveSheet.Shapes.AddShape(msoShapeRectangle,
x, y, distance, width). _
Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor =
54
For i = 1 To 18
Example of bar-chart
Start_P = ""
Dur = Range("D" & 2 + i).Value
width_Bar = Dur * Width / (Total / 12)
ActiveSheet.Shapes.AddShape(msoShapeRectangle, left_cum, top_1 + (i - 1) *
Cell_height, width_Bar, Bar_H).Select
Selection.Name = "Bar" & i
left_cum = left_cum + width_Bar
'Draw line
X2 = X1 + XL / 18
Y2 = Y1 - Dur * YL / Total
ActiveSheet.Shapes.AddLine(X1, Y1, X2, Y2).Select
Selection.Name = "Line" & i
X1 = X2
Y1 = Y2
Next i

310-IT-2109 IT For Construction - Spreadsheet 2015 Final

Uploaded by

Copyright:

Available Formats

310-IT-2109 IT For Construction - Spreadsheet 2015 Final

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

310-IT-2109 IT For Construction - Spreadsheet 2015 Final

Uploaded by

Copyright:

Available Formats

Introduction to

Department of Civil Engineering

Copyright 2005 Vachara Peansupap. All Rights Reserved.

Spreadsheet is the electronic document that

Copyright 2005 Vachara Peansupap. All Rights Reserved.

Lotus 123 (Dos version)

In the past, several spreadsheet

Lotus introduced programmable macro

Copyright 2005 Vachara Peansupap. All Rights Reserved.

Lotus 1-2-3 (windows)

Copyright 2005 Vachara Peansupap. All Rights Reserved.

Copyright 2005 Vachara Peansupap. All Rights Reserved.

Copyright 2005 Vachara Peansupap. All Rights Reserved.

Open Office (cal)

Copyright 2005 Vachara Peansupap. All Rights Reserved.

Store and process data

Copyright 2005 Vachara Peansupap. All Rights Reserved.

Copyright 2005 Vachara Peansupap. All Rights Reserved.

Spreadsheet program can be applied

Copyright 2005 Vachara Peansupap. All Rights Reserved.

Two Types of Paste

considering updated data.

Spreadsheet program offers several

Copyright 2005 Vachara Peansupap. All Rights Reserved.

Paste is used to copy object without

Paste Link is used to copy object with

Copyright 2005 Vachara Peansupap. All Rights Reserved.

Paste spreadsheet ->

Paste Link spreadsheet

Copyright 2005 Vachara Peansupap. All Rights Reserved.

Copyright 2005 Vachara Peansupap. All Rights Reserved.

3. Protection and security

Spreadsheet program offers the ability

Copyright 2005 Vachara Peansupap. All Rights Reserved.

3. Protection and security

Copyright 2005 Vachara Peansupap. All Rights Reserved.

3.1 Protection Worksheet

3.2 Protection workbook

Copyright 2005 Vachara Peansupap. All Rights Reserved.

Copyright 2005 Vachara Peansupap. All Rights Reserved.

3.2 Protection workbook

3.2 Protection workbook

Copyright 2005 Vachara Peansupap. All Rights Reserved.

Copyright 2005 Vachara Peansupap. All Rights Reserved.

3.3 Security of File

Sort (Ascending or Descending)

Copyright 2005 Vachara Peansupap. All Rights Reserved.

As spreadsheet program contains

Users can use the spreadsheet to

Copyright 2005 Vachara Peansupap. All Rights Reserved.

What is the benefits of using

Copyright 2005 Vachara Peansupap. All Rights Reserved.

As the spreadsheet provides multiple

Copyright 2005 Vachara Peansupap. All Rights Reserved.

Functions, Macro and VB

Copyright 2005 Vachara Peansupap. All Rights Reserved.

Macro Function - Macro is the

Copyright 2005 Vachara Peansupap. All Rights Reserved.

Copyright 2005 Vachara Peansupap. All Rights Reserved.