Excel Example A Microsoft Excel Cookbook for Electronics Engineers

Aubrey Kagan0750677562, 9780750677561, 9781417537143

The spreadsheet has become a ubiquitous engineering tool, and Microsoft Excel is the standard spreadsheet software package. Over the years, Excel has become such a complex program that most engineers understand and use only a tiny part of its power and features. This book is aimed at electronics engineers and technicians in particular, showing them how to best use Excel’s features for computations, circuit modeling, graphing, and data analysis as applied to electronics design. Separate chapters cover lookup tables and file I/O, using macros, graphing, controls, using Analysis Toolpak for statistical analysis, databases, and linking into Excel from other sources, such as data from a serial port. The book is basically an engineering cookbook, with each chapter providing tutorial information along with several Excel “recipes” of interest to electronics engineers. The accompanying CD-ROM features ready-to-run, customizable Excel worksheets derived from the book examples, which will be useful tools to add to any electronics engineer’s spreadsheet toolbox. Engineers are looking for any and all means to increase their efficiency and add to their “bag of design tricks.” Just about every electronics engineer uses Excel but most feel that the program has many more features to offer, if they only knew what they were! The Excel documentation is voluminous and electronics engineers don’t have the time to read it all and sift through looking for those features that are directly applicable to their jobs and figure out how to use them. This book does that task for them-pulls out those features that they need to know about and shows them how to make use of them in specific design examples that they can then tailor to their own design needs. *This is the ONLY book to deal with Excel specifically in the electronics field *Distills voluminous and time-consuming Excel documentation down to nitty-gritty explanations of those features that are directly applicable to the electronics engineer’s daily job duties *The accompanying CD-ROM provides ready-to-use, fully-customizable worksheets from the book’s examples

---

Table of contents :
Cover……Page 1
Half Title Page……Page 2
Title Page……Page 4
Copyright……Page 5
Dedication Page……Page 6
Contents……Page 8
Acknowlegments……Page 14
Introduction……Page 15
Rules of Engagement……Page 16
What’s on the CD-ROM?……Page 19
Model Description……Page 20
Starting Excel……Page 21
Data Entry into a Worksheet……Page 22
Autofill……Page 24
Bulk Formatting……Page 26
Formulas……Page 27
Copying Formulas……Page 28
Relative and Absolute References……Page 29
Naming Cells……Page 31
Borders……Page 34
Chart……Page 36
Error Bars……Page 39
Adding a Trendline……Page 40
Macro: Timer……Page 41
Model Description……Page 45
Setup Workbook……Page 46
Hexadecimal……Page 48
Lookup Tables……Page 49
Conditional Formatting……Page 52
Macro……Page 54
Model Description……Page 60
Factors……Page 61
Bill of Material……Page 63
Calculating the Quality Factor……Page 68
Calculate Electrical Stress Factor……Page 69
Calculation of λG……Page 71
Scenario……Page 73
Importing the File……Page 77
Extracting Op-code……Page 81
Opening a Second Workbook……Page 82
Easing the Pain of Nested IFs……Page 86
Model Description……Page 88
Creating the Basic Workbook……Page 89
Forms Controls……Page 90
Comments……Page 94
Double-Click Macro……Page 95
Macro Activation by the Command Button……Page 97
Save to Data File……Page 100
Usage……Page 103
Spreadsheet Concept……Page 105
Counter/Timer 0 Sheet……Page 112
Counting Types……Page 116
Timer……Page 119
Macros to Hide and Unhide……Page 121
Adding Forms……Page 125
Add Image Control……Page 127
Timer/Counter 1 Sheet……Page 130
Timer/Counter 2 Sheet……Page 131
Interrupt Control Sheet……Page 132
Summary Sheet……Page 134
Conclusion……Page 135
Custom Autofill……Page 137
Data Tables……Page 139
MATCH Function……Page 142
INDEX Function……Page 143
Block Conditional Formatting……Page 144
Model Description……Page 146
Viewing and Hiding the Language Bar……Page 148
Macros……Page 149
Custom Toolbar……Page 153
Evaluate the Color Code……Page 156
Text to Speech……Page 160
Conclusion……Page 161
Model Description……Page 162
Acquiring RTD Tables……Page 163
Lookup RTD Value……Page 167
Creating a Function……Page 168
Accessing a Function……Page 169
Creating the Model in Excel……Page 171
Standard Resistor Values……Page 174
Installing the NearestValues Add-In……Page 177
Back to the Project At Hand……Page 178
Prompting for User Input……Page 180
Printout……Page 181
Running Macros when the Workbook is Started……Page 182
Running from the Desktop……Page 185
Model Description……Page 186
Initial Model……Page 187
Goal Seek……Page 189
Worst Case Analysis……Page 191
Thermal Analysis……Page 192
True RMS and Integration……Page 198
More Preparation……Page 200
Standard Capacitance Value……Page 203
Chart……Page 205
Conclusion……Page 208
Installing the NearestValues Add-In……Page 209
Solver……Page 210
Adding a Macro……Page 218
Report Generation……Page 221
Limitations……Page 223
Monostable Operation……Page 225
Add User Form……Page 226
Add Image Control……Page 227
Second Image……Page 229
Modifying Form Location……Page 231
Monostable Pulse Width Entry……Page 233
Command Button……Page 235
Solver……Page 238
SolverSolve……Page 239
Using Standard Capacitor Values……Page 240
Astable Operation……Page 242
Worksheet Setup……Page 243
Create a Purchase Order……Page 248
Print Macro……Page 250
User Form……Page 252
Initial Procedure……Page 254
Event Actions……Page 255
Auto Startup……Page 257
Running PurchaseOrder……Page 258
Model Description……Page 259
DMM Interface Protocol……Page 260
MSComm32……Page 262
Initializing the Serial Port……Page 267
Conversion of DMM Display to Data……Page 277
Analog Meter Chart……Page 279
Zone Identification……Page 286
Data Plot—Chart Recorder……Page 290
Food For Thought……Page 298
Model Description……Page 300
Hardware Interface……Page 301
Timing Diagram……Page 302
PC Parallel Port……Page 303
First Steps……Page 305
Acquiring Data……Page 306
Adding Sound……Page 311
Thoughts on Improvement……Page 312
Statistics……Page 313
Model Description……Page 320
Workbook Open and Close……Page 321
Adding VBA Controls: Granularity……Page 324
Adding VBA Controls: Frequency……Page 328
Waveform Sampling Frequency……Page 330
Bump Frequency……Page 332
Generating Frequency Tables……Page 334
Add a Chart……Page 338
Download Waveform……Page 341
Setting the Amplitude……Page 345
Skew……Page 346
Average Voltage, RMS Voltage……Page 349
Where Do Macros, Procedures and Functions Get Stored?……Page 352
Using a Macro……Page 353
Accessing a Function Across Workbook Boundaries……Page 354
Template……Page 355
Add-Ins……Page 356
Running a Macro……Page 357
Naming Constants……Page 358
Moving Cell Selection Relative to Current Cell……Page 359
Cell Access……Page 360
Extended Range Selection……Page 361
Accessing a Procedure in a Different Workbook……Page 362
Workbook Open, Activation……Page 363
Array Formula……Page 364
Error Values……Page 366
Web Resources……Page 367
Appendix B: Parallel and Serial I/O……Page 368
Serial Port……Page 370
Possible Hardware Sources……Page 372
References……Page 373
About the Author……Page 376
Index……Page 378
CD-ROM License Agrement……Page 384