An Introduction to EXCEL for Civil Engineers From engineering theory to Excel practice

An Introduction to EXCEL for Civil Engineers 

From engineering theory to Excel practice 

Gunthar Pangaribuan 

CONTENTS

PREFACE......................................................................................................... ii

Chapter 1: BASICS OF  EXCEL.........................................................................1

1.1 Worksheet and Workbook ............................................................................... 1

1.2 Data Type ........................................................................................................ 2

1.3 Formula..................................................................................................... 5

1.4 Built‐In Function .............................................................................................. 7

1.5 Array Formula .................................................................................................. 9

1.6 Data Formatting .................................................................................................... 11

1.7 Error Message ................................................................................................. 11

1.8 Printing ................................................................................................................. 12

1.9 Making Charts .................................................................................................... 13

1.10 Engineering Drawing ............................................................................................ 15

1.11 Visual Basic for Application ...................................................................................... 24

1.11.1 Creating Macro ....................................................................................... 25

1.11.2 Recording Macro ................................................................................................. 27

1.11.3 Procedure ................................................................................................................ 29

1.11.4 Running Macro ...................................................................................................... 31

1.11.5 VBA Dictionary ................................................................................................. 32

Chapter 2: EXCEL FUNCTIONS......................................................................................33

2.1 Math and Trigonometry Functions ................................................................................. 33

2.2 Logical Functions ......................................................................................................... 36

2.3 Lookup Functions .................................................................................................... 38

2.4 Text Functions ............................................................................................................. 40

2.5 Data Analysis Functions ............................................................................................... 43

2.5.1 Linear Regression ...................................................................................................... 43

2.5.2 Polynomial Regression ............................................................................................ 52

2.5.3 Interpolation ................................................................................................................ 53

2.5.4 Statistical Data ............................................................................................................. 59

2.5.5 Circular Reference ..................................................................................................... 65

Chapter 3: CREATING MACRO............................................................................................69

3.1 Function Procedure ........................................................................................................... 69

3.2 Sub Procedure ................................................................................................................ 74

3.3 Control Structures .......................................................................................................... 76

3.3.1 Looping ......................................................................................................................... 76

3.3.2 Branching .................................................................................................................... 79

3.4 User Defined Function Problems .................................................................................... 83

3.5 Structure of Program ...................................................................................................... 97

3.5.1 Input Output Form ..................................................................................................... 97

3.5.2 Work With Modules .................................................................................................. 98

3.5.3 Tips .......................................................................................................................... 100

3.6 Chart Macro........................................................................................... 102

3.7 Manipulation on Program Steps ......................................................................... 108

Chapter 4: MATRIX PROGRAM.................................................................................. 112

4.1 Matrix Definition ................................................................................................ 112

4.1.1 Types of Matrix.................................................................................................. 112

4.1.2 Matrix Operation ............................................................................................ 115

4.2 Program for Matrix Operations ................................................................................. 124

4.3 Matrix Method for Structural Analysis ............................................................... 132

4.3.1  Upper Structure .................................................................................................... 132

4.3.2  Sub Structure ......................................................................................................... 134

Chapter 5: NUMERICAL METHOD ................................................... 135

5.1 Numerical Integration ......................................................................... 135

5.2 Numerical Differentiation .................................................................................... 138

Chapter 6: PROGRAM FOR 2D FRAME STRUCTURE ANALYSIS........................... 144

6.1 Case Example................................................................... 144

6.2 Sign Convention for Diagram ............................................................... 160

6.3 Application ........................................................................... 162

Chapter 7: PROGRAM FOR 2D TRUSS STRUCTURE ANALYSIS..... 163

7.1 Case Example................................................. .................................. 164

7.2 Application ....................................................................................... 176

Chapter 8: BEAM ON ELASTIC FOUNDATION............................................ 180

8.1 Case Example.................................................................................. 182

8.1 Application ............................................................................................... 187

Chapter 9: LATERALLY LOADED STRUCTURE.............................................. 189

9.1 Case Example...................................................................................................... 189

9.2 Application ........................................................................................................... 196

Chapter 10: ONE DIMENSIONAL CONSOLIDATION .................................................... 199

10.1 Application 1 ...................................................................................................... 205

10.2 Application 2 ...................................................................................................... 207

Chapter 11: AUTOCAD SCRIPT FILE...................................................................... 210

11.1 Creating Scripts in Worksheet ........................................................................ 210

11.2 Creating Scripts in VBA ............................................................................. 231

REFERENCES ............................................................................. 240

ATTACHMENT: PROGRAM CODE.............................................. 241

Full Text   Click Here