Muutke küpsiste eelistusi

Excel for Scientists and Engineers: Numerical Methods [Pehme köide]

3.67/5 (18 hinnangut Goodreads-ist)
(Boston College, Chestnut Hill, Massachusetts)
  • Formaat: Paperback / softback, 480 pages, kõrgus x laius x paksus: 255x178x24 mm, kaal: 866 g
  • Ilmumisaeg: 04-May-2007
  • Kirjastus: Wiley-Interscience
  • ISBN-10: 0471387347
  • ISBN-13: 9780471387343
Teised raamatud teemal:
Excel for Scientists and Engineers: Numerical MethodsSuurem pilt
  • Formaat: Paperback / softback, 480 pages, kõrgus x laius x paksus: 255x178x24 mm, kaal: 866 g
  • Ilmumisaeg: 04-May-2007
  • Kirjastus: Wiley-Interscience
  • ISBN-10: 0471387347
  • ISBN-13: 9780471387343
Teised raamatud teemal:
Püsilink: https://www.kriso.ee/db/9780471387343.html
Märksõnad:
Billo, a former professor of chemistry at Boston College, describes how to use Microsoft Excel for calculations in chemical, biochemical, physical, engineering, biological, and medicinal problems. He covers using worksheets and formulas, using the tools in Excel, and writing programs using Visual Basic for Applications (VBA) programming language (introduced in the first two chapters). No background in programming is needed--so the text can be used by undergraduates and graduate students--and mathematical theory is kept to a minimum. The CD-ROM contains spreadsheets, charts, VBA code for examples, solutions to problems, and a workbook. Annotation ©2007 Book News, Inc., Portland, OR (booknews.com)

Learn to fully harness the power of Microsoft Excel® to perform scientific and engineering calculations

With this text as your guide, you can significantly enhance Microsoft Excel's® capabilities to execute the calculations needed to solve a variety of chemical, biochemical, physical, engineering, biological, and medicinal problems. The text begins with two chapters that introduce you to Excel's Visual Basic for Applications (VBA) programming language, which allows you to expand Excel's® capabilities, although you can still use the text without learning VBA. Following the author's step-by-step instructions, here are just a few of the calculations you learn to perform:

  • Use worksheet functions to work with matrices
  • Find roots of equations and solve systems of simultaneous equations
  • Solve ordinary differential equations and partial differential equations
  • Perform linear and non-linear regression
  • Use random numbers and the Monte Carlo method
This text is loaded with examples ranging from very basic to highly sophisticated solutions. More than 100 end-of-chapter problems help you test and put your knowledge to practice solving real-world problems. Answers and explanatory notes for most of the problems are provided in an appendix.

The CD-ROM that accompanies this text provides several useful features:

  • All the spreadsheets, charts, and VBA code needed to perform the examples from the text
  • Solutions to most of the end-of-chapter problems
  • An add-in workbook with more than twenty custom functions
This text does not require any background in programming, so it is suitable for both undergraduate and graduate courses. Moreover, practitioners in science and engineering will find that this guide saves hours of time by enabling them to perform most of their calculations with one familiar spreadsheet package

Arvustused

"I thoroughly enjoyed this book and recommend it to anyone who finds themselves using Excel to do numerical computations beyond the basic spreadsheet operations. The CD provides a wealth of custom functions that extend the usefulness of Excel plus virtually all of the spreadsheets, charts, and VBA code needed to perform the example problems given in the text. This paperback edition and CD are a real bargain." (Technometrics, May 2008) "This is a wonderful book for power users of Excel or those wanting to learn VBA and could also be useful as a course book in numerical methods"" (IEEE Electrical Insulation Magazine, July/August 2008)

"I am wiser now, and you should be, too! This book is strongly recommended for all engineers, scientists, undergraduate and graduate students that have ever used EXCEL." (Materials and Manufacturing Processes, Volume 22, Issue 7 2007)

"An excellent volume for practicing scientists or engineers encountering numerical methods." (CHOICE, October 2007)

Preface xv
Acknowledgments xix
About the Author xix
Introducing Visual Basic for Applications
1(14)
The Visual Basic Editor
1(3)
Visual Basic Procedures
4(5)
There Are Two Kinds of Macros
4(1)
The Structure of a Sub Procedure
4(1)
The Structure of a Function Procedure
5(1)
Using the Recorder to Create a Sub Procedure
5(2)
The Personal Macro Workbook
7(1)
Running a Sub Procedure
8(1)
Assigning a Shortcut Key to a Sub Procedure
8(1)
Entering VBA Code
9(4)
Creating a Simple Custom Function
10(1)
Using a Function Macro
10(2)
A Shortcut to Enter a Function
12(1)
Some FAQs
13(2)
Fundamentals of Programming with VBA
15(42)
Components of Visual Basic Statements
15(8)
Operators
16(1)
Variables
16(1)
Objects, Properties, and Methods
17(1)
Objects
17(1)
Properties
17(2)
Using Properties
19(1)
Functions
20(2)
Using Worksheet Functions with VBA
22(1)
Some Useful Methods
22(1)
Other Keywords
23(1)
Program Control
23(4)
Branching
23(1)
Logical Operators
24(1)
Select Case
24(1)
Looping
24(1)
For...Next Loop
25(1)
Do While... Loop
25(1)
For Each... Next Loop
25(1)
Nested Loops
26(1)
Exiting from a Loop or from a Procedure
26(1)
VBA Data Types
27(1)
The Variant Data Type
28(1)
Subroutines
28(1)
Scoping a Subroutine
29(1)
VBA Code for Command Macros
29(4)
Objects and Collections of Objects
29(1)
``Objects'' That Are Really Properties
30(1)
You Can Define Your Own Objects
30(1)
Methods
31(1)
Some Useful Methods
31(1)
Two Ways to Specify Arguments of Methods
32(1)
Arguments with or without Parentheses
33(1)
Making a Reference to a Cell or a Range
33(4)
A Reference to the Active Cell or a Selected Range
33(1)
A Reference to a Cell Other than the Active Cell
34(1)
References Using the Union or Intersect Method
35(1)
Examples of Expressions to Refer to a Cell or Range
35(1)
Getting Values from a Worksheet
36(1)
Sending Values to a Worksheet
37(1)
Interacting with the User
37(4)
MsgBox
37(2)
MsgBox Return Values
39(1)
InputBox
39(2)
Visual Basic Arrays
41(6)
Dimensioning an Array
41(1)
Use the Name of the Array Variable to Specify the Whole Array
42(1)
Multidimensional Arrays
42(1)
Declaring the Variable Type of an Array
42(1)
Returning the Size of an Array
42(1)
Dynamic Arrays
43(1)
Preserving Values in Dynamic Arrays
43(1)
Working with Arrays in Sub Procedures: Passing Values from Worksheet to VBA Module
44(1)
A Range Specified in a Sub Procedure Can Be Used as an Array
44(1)
Some Worksheet Functions Used Within VBA Create an Array Automatically
45(1)
Some Worksheet Functions Used Within VBA Create an Array Automatically
45(1)
An Array of Object Variables
45(1)
Working with Arrays in Sub Procedures: Passing Values from a VBA Module to a Worksheet
45(1)
A One-Dimensional Array Assigned to a Worksheet Range Can Cause Problems
46(1)
Custom Functions
47(1)
Specifying the Data Type of an Argument
47(1)
Specifying the Data Type Returned by a Function Procedure
47(1)
Returning an Error Value from a Function Procedure
48(1)
A Custom Function that Takes an Optional Argument
48(1)
Arrays in Function Procedures
48(2)
A Range Passed to a Function Procedure Can Be Used as an Array
48(1)
Passing an Indefinite Number of Arguments: Using the Paramarray Keyword
49(1)
Returning an Array of Values as a Result
49(1)
Creating Add-In Function Macros
50(1)
How to Create an Add-In Macro
51(1)
Testing and Debugging
51(6)
Tracing Execution
52(1)
Stepping Through Code
52(1)
Adding a Breakpoint
52(1)
Examining the Values of Variables While in Break Mode
53(1)
Examining the Values of Variables During Execution
54(3)
Worksheet Functions for Working with Matrices
57(12)
Arrays, Matrices and Determinants
57(1)
Some Types of Matrices
57(1)
An Introduction to Matrix Mathematics
58(2)
Excel's Built-in Matrix Functions
60(3)
Some Additional Matrix Functions
63(3)
Problems
66(3)
Number Series
69(8)
Evaluating Series Formulas
70(2)
Using Array Constants to Create Series Formulas
70(1)
Using the ROW Worksheet Function to Create Series Formulas
71(1)
The Indirect Worksheet Function
71(1)
Using the Indirect Worksheet Function with the Row Worksheet Function to Create Series Formulas
72(1)
The Taylor Series
72(3)
The Taylor Series: An Example
73(2)
Problems
75(2)
Interpolation
77(22)
Obtaining Values from a Table
77(6)
Using Excel's Lookup Functions to Obtain Values from a Table
77(1)
Using Vlookup to Obtain Values from a Table
78(1)
Using the Lookup Function to Obtain Values from a Table
79(1)
Creating a Custom Lookup Formula to Obtain Values from a Table
80(1)
Using Excel's Lookup Functions to Obtain Values from a Two-Way Table
81(2)
Interpolation
83(13)
Linear Interpolation in a Table by Means of Worksheet Formulas
83(2)
Linear Interpolation in a Table by Using the Trend Worksheet Function
85(1)
Linear Interpolation in a Table by Means of a Custom Function
86(1)
Cubic Interpolation
87(2)
Cubic Interpolation in a Table by Using the Trend Worksheet Function
89(1)
Linear Interpolation in a Two-Way Table by Means of Worksheet Formulas
90(1)
Cubic Interpolation in a Two-Way Table by Means of Worksheet Formulas
91(2)
Cubic Interpolation in a Two-Way Table by Means of a Custom Function
93(3)
Problems
96(3)
Differentiation
99(28)
First and Second Derivatives of Data in a Table
99(1)
Calculating First and Second Derivatives
100(9)
Using Linest as a Fitting Function
105(4)
Derivatives of a Worksheet Formula
109(15)
Derivatives of a Worksheet Formula Calculated by Using a VBA Function Procedure
109(1)
First Derivative of a Worksheet Formula Calculated by Using the Finite-Difference Method
110(1)
The Newton Quotient
110(1)
Derivative of a Worksheet Formula Calculated by Using the Finite-Difference Method
111(1)
First Derivative of a Worksheet Formula Calculated by Using a VBA Sub Procedure Using the Finite-Difference Method
112(3)
First Derivative of a Worksheet Formula Calculated by Using a VBA Function Procedure Using the Finite-Difference Method
115(3)
Improving the VBA Function Procedure
118(2)
Second Derivative of a Worksheet Formula
120(3)
Concerning the Choice of Δx for the Finite-Difference Method
123(1)
Problems
124(3)
Integration
127(20)
Area under a Curve
127(6)
Calculating the Area under a Curve Defined by a Table of Data Points
129(1)
Calculating the Area under a Curve Defined by a Table of Data Points by Means of a VBA Function Procedure
130(1)
Calculating the Area under a Curve Defined by a Formula
131(1)
Area between Two Curves
132(1)
Integrating a Function
133(8)
Integrating a Function Defined by a Worksheet Formula by Means of a VBA Function Procedure
133(4)
Gaussian Quadrature
137(3)
Integration with an Upper or Lower Limit of Infinity
140(1)
Distance Traveled Along a Curved Path
141(2)
Problems
143(4)
Roots of Equations
147(42)
A Graphical Method
147(2)
The Interval-Halving or Bisection Method
149(2)
The Interval Method with Linear Interpolation (the Regula Falsi Method)
151(2)
The Regula Falsi Method with Correction for Slow Convergence
153(1)
The Newton-Raphson Method
154(2)
Using Goal Seek
156(4)
The Secant Method
160(1)
The Newton-Raphson Method Using Circular Reference and Iteration
161(2)
A Newton-Raphson Custom Function
163(3)
Bairstow's Method to Find All Roots of a Regular Polynomial
166(8)
Finding Values Other than Zeroes of a Function
174(11)
Using Goal Seek... to Find the Point of Intersection of Two Curves
174(2)
Using the Newton-Raphson Method to Find the Point of Intersection of Two Lines
176(2)
Using the Newton-Raphson Method to Find Multiple Intersections of a Straight Line and a Curve
178(2)
A Goal Seek Custom Function
180(5)
Problems
185(4)
Systems of Simultaneous Equations
189(28)
Cramer's Rule
190(1)
Solving Simultaneous Equations by Matrix Inversion
191(1)
Solving Simultaneous Equations by Gaussian Elimination
191(5)
The Gauss-Jordan Method
196(4)
Solving Linear Systems by Iteration
200(7)
The Jacobi Method Implemented on a Worksheet
200(3)
The Gauss-Seidel Method Implemented on a Worksheet
203(1)
The Gauss-Seidel Method Implemented on a Worksheet Using Circular References
204(1)
A Custom Function Procedure for the Gauss-Seidel Method
205(2)
Solving Nonlinear Systems by Iteration
207(6)
Newton's Iteration Method
207(6)
Problems
213(4)
Numerical Integration of Ordinary Differential Equations Part I: Initial Conditions
217(28)
Solving a Single First-Order Differential Equation
218(10)
Euler's Method
218(2)
The Fourth-Order Runge-Kutta Method
220(1)
Fourth-Order Runge-Kutta Method Implemented on a Worksheet
220(3)
Runge-Kutta Method Applied to a Differential Equation Involving Both x and y
223(1)
Fourth-Order Runge-Kutta Custom Function for a Single Differential Equation with the Derivative Expression Coded in the Procedure
224(1)
Fourth-Order Runge-Kutta Custom Function for a Single Differential Equation with the Derivative Expression Passed as an Argument
225(3)
Systems of First-Order Differential Equations
228(7)
Fourth-Order Runge-Kutta Custom Function for Systems of Differential Equations
229(6)
Predictor-Corrector Methods
235(3)
A Simple Predictor-Corrector Method
235(1)
A Simple Predictor-Corrector Method Utilizing an Intentional Circular Reference
236(2)
Higher-Order Differential Equations
238(3)
Problems
241(4)
Numerical Integration of Ordinary Differential Equations Part II: Boundary Conditions
245(18)
The Shooting Method
245(9)
An Example: Deflection of a Simply Supported Beam
246(3)
Solving a Second-Order Ordinary Differential Equation by the Shooting Method and Euler's Method
249(2)
Solving a Second-Order Ordinary Differential Equation by the Shooting Method and the RK Method
251(3)
Finite-Difference Methods
254(8)
Solving a Second-Order Ordinary Differential Equation by the Finite-Difference Method
254(4)
Another Example
258(3)
A Limitation on the Finite-Difference Method
261(1)
Problems
262(1)
Partial Differential Equations
263(24)
Elliptic, Parabolic and Hyperbolic Partial Differential Equations
263(1)
Elliptic Partial Differential Equations
264(5)
Solving Elliptic Partial Differential Equations: Replacing Derivatives with Finite Differences
265(2)
An Example: Temperature Distribution in a Heated Metal Plate
267(2)
Parabolic Partial Differential Equations
269(13)
Solving Parabolic Partial Differential Equations: The Explicit Method
270(2)
An Example: Heat Conduction in a Brass Rod
272(2)
Solving Parabolic Partial Differential Equations: The Crank-Nicholson or Implicit Method
274(1)
An Example: Vapor Diffusion in a Tube
275(2)
Vapor Diffusion in a Tube Revisited
277(2)
Vapor Diffusion in a Tube (Again)
279(1)
A Crank-Nicholson Custom Function
280(2)
Vapor Diffusion in a Tube Solved by Using a Custom Function
282(1)
Hyperbolic Partial Differential Equations
282(4)
Solving Hyperbolic Partial Differential Equations: Replacing Derivatives with Finite Differences
282(1)
An Example: Vibration of a String
283(3)
Problems
286(1)
Linear Regression and Curve Fitting
287(26)
Linear Regression
287(4)
Least-Squares Fit to a Straight Line
288(1)
Least-Squares Fit to a Straight Line Using the Worksheet Functions Slope, Intercept and RSQ
289(2)
Multiple Linear Regression
291(18)
Least-Squares Fit to a Straight Line Using Linest
292(1)
Multiple Linear Regression Using Linest
293(4)
Handling Noncontinguous Ranges of known_x's in Linest
297(1)
A Linest Shortcut
297(1)
Linest's Regression Statistics
297(1)
Linear Regression Using Trendline
298(3)
Limitations of Trendline
301(1)
Importing Trendline Coefficients into a Spreadsheet by Using Worksheet Formulas
302(1)
Using the Regression Tool in Analysis Tools
303(2)
Limitations of the Regression Tool
305(1)
Importing the Trendline Equation from a Chart into a Worksheet
305(4)
Problems
309(4)
Nonlinear Regression Using the Solver
313(28)
Nonlinear Least-Squares Curve Fitting
314(13)
Introducing the Solver
316(1)
How the Solver Works
316(1)
Loading the Solver Add-In
317(1)
Why Use the Solver for Nonlinear Regression?
317(1)
Nonlinear Regression Using the Solver: An Example
318(5)
Some Notes on Using the Solver
323(1)
Some Notes on the Solver Parameters Dialog Box
323(1)
Some Notes on the Solver Options Dialog Box
324(2)
When to Use Manual Scaling
326(1)
Statistics of Nonlinear Regression
327(5)
The Solver Statistics Macro
328(1)
Be Cautious When Using Linearized Forms of Nonlinear Equations
329(3)
Problems
332(9)
Random Numbers and the Monte Carlo Method
341(22)
Random Numbers in Excel
341(9)
How Excel Generates Random Numbers
341(1)
Using Random Numbers in Excel
342(2)
Adding ``Noise'' to a Signal Generated by a Formula
344(1)
Selecting Items Randomly from a List
345(2)
Random Sampling by Using Analysis Tools
347(2)
Simulating a Normal Random Distribution of a Variable
349(1)
Monte Carlo Simulation
350(4)
Monte Carlo Integration
354(8)
The Area of an Irregular Polygon
354(8)
Problems
362(1)
APPENDICES
363(80)
Appendix 1 Selected VBA Keywords
365(22)
Appendix 2 Shortcut Keys for VBA
387(2)
Appendix 3 Custom Functions Help File
389(20)
Appendix 4 Some Equations for Curve Fitting
409(14)
Appendix 5 Engineering and Other Functions
423(4)
Appendix 6 ASCII Codes
427(2)
Appendix 7 Bibliography
429(2)
Appendix 8 Answers and Comments for End-of-Chapter Problems
431(12)
Index 443


E. JOSEPH BILLO, a former associate professor of chemistry at Boston College, is the author of Excel for Chemists: A Comprehensive Guide, Second Edition (Wiley). He has developed two short courses, "Advanced Excel for Scientists and Engineers" and "Excel Visual Basic Macros for Scientists and Engineers," which he has presented to thousands of scientists throughout the United States, Canada, and Europe for organizations such as The American Chemical Society, the National Cancer Institute, Procter & Gamble, Shell, and Texaco.