Nội dung

how to use excel ® in analytical chemistry and in general scientific data analysis how to use ® excel in analytical chemistry and in general scientific data analysis Robert de Levie Bowdoin College, Brunswick, ME           The Pitt Building, Trumpington Street, Cambridge, United Kingdom    The Edinburgh Building, Cambridge CB2 2RU, UK 40 West 20th Street, New York, NY 10011-4211, USA 477 Williamstown Road, Port Melbourne, VIC 3207, Australia Ruiz de Alarcón 13, 28014 Madrid, Spain Dock House, The Waterfront, Cape Town 8001, South Africa http://www.cambridge.org © R. de Levie 2004 First published in printed format 2001 ISBN 0-511-04037-7 eBook (netLibrary) ISBN 0-521-64282-5 hardback ISBN 0-521-64484-4 paperback contents Preface [xi] part i introduction to using the spreadsheet 1 How to use Excel [1] 1.1 Starting Windows [2] 1.2 A first look at the spreadsheet [3] 1.3 A simple spreadsheet and graph [7] 1.3a Making a graph in Excel 97 or a more recent version 1.3b Making a graph in Excel 5 or Excel 95 1.4 Addressing a spreadsheet cell 1.5 More on graphs [13] [15] 1.6 Mathematical operations 1.7 Error messages [21] [26] 1.8 Naming and annotating cells 1.9 Viewing the spreadsheet 1.10 Printing 1.11 Help! [9] [11] [27] [28] [29] [30] 1.12 The case of the changing options 1.13 Importing macros and data [31] [32] 1.14 Differences between the various versions of Excel 1.15 Some often-used spreadsheet commands 1.16 Changing the default settings 1.17 Summary [33] [35] [36] [37] part ii statistics and related methods 2 Introduction to statistics [39] 2.1 Gaussian statistics [39] 2.2 Replicate measurements [45] v vi Contents 2.3 The propagation of imprecision from a single parameter [51] 2.4 The propagation of imprecision from multiple parameters 2.5 The weighted average [58] 2.6 Least-squares fitting to a proportionality [60] 2.7 Least-squares fitting to a general straight line 2.8 Looking at the data 2.9 What is ‘normal’? [66] [71] [73] 2.10 Poissonian statistics [78] 2.11 How likely is the improbable? 2.12 Summary [54] [79] [83] 3 More on least squares [90] 3.1 Multi-parameter fitting [90] 3.2 Fitting data to a quadratic [93] 3.3 Least squares for equidistant data: smoothing and differentiation 3.4 Weighted least squares [99] 3.5 Another example of weighted least squares: enzyme kinetics 3.6 Non-linear data fitting 3.6a Some kinetic data [106] 3.6b A double exponential 3.6c False minima [109] [115] 3.6d Enzyme kinetics revisited 3.6e SolverAid 3.7 Summary [103] [105] [116] [117] [118] part iii chemical equilibrium 4 Acids, bases, and salts [121] 4.1 The mass action law and its graphical representations [121] 4.2 Conservation laws, proton balance, and pH calculations 4.3 Titrations of monoprotic acids and bases 4.4 Schwartz and Gran plots 4.5 The first derivative [133] [136] 4.6 A more general approach to data fitting 4.7 Buffer action [130] [142] [146] 4.8 Diprotic acids and bases, and their salts 4.9 Polyprotic acids and bases, and their salts 4.10 Activity corrections [155] 4.11 A practical example [161] 4.12 Summary [172] 5 Other ionic equilibria [175] 5.1 Complex formation [175] [148] [152] [127] [94] Contents vii 5.2 Chelation [180] 5.3 Extraction [182] 5.4 Solubility [185] 5.5 Precipitation and dissolution 5.6 Precipitation titrations [194] 5.7 The von Liebig titration [200] [190] 5.8 The graphical representation of electrochemical equilibria 5.9 Redox titrations [211] 5.10 Redox buffer action 5.11 Summary [204] [217] [220] part iv instrumental methods 6 Spectrometry, chromatography, and voltammetry [223] 6.1 Spectrometric pKa determination [223] 6.2 Multi-component spectrometric analysis 1 [225] 6.3 Multi-component spectrometric analysis 2 [227] 6.4 The absorbance–absorbance diagram 6.5 Chromatographic plate theory 1 [234] 6.6 Chromatographic plate theory 2 [239] 6.7 Peak area, position, and width [231] [243] 6.8 Determining the number of theoretical plates 6.9 Optimizing the mobile phase velocity 6.10 Polarography [248] [251] 6.11 Linear sweep and cyclic voltammetry 1 [257] 6.12 Linear sweep and cyclic voltammetry 2 [261] 6.13 Summary [263] part v mathematical methods 7 Fourier transformation [265] 7.1 Introduction to Fourier transformation 7.2 Interpolation and filtering 7.3 Differentiation [285] 7.4 Aliasing and leakage 7.5 Convolution [288] [295] 7.6 Deconvolution 7.7 Summary [265] [277] [304] [309] 8 Standard mathematical operations [311] 8.1 The Newton–Raphson method 8.2 Non-linear least squares 8.3 Signal averaging [314] [313] [311] [245] viii Contents 8.4 Lock-in amplification 8.5 Data smoothing 8.6 Peak fitting [324] 8.7 Integration [328] 8.8 Differentiation [316] [318] [331] 8.9 Semi-integration and semi-differentiation 8.10 Interpolation 8.11 Matrix manipulation 8.12 Overflow 8.13 Summary [335] [336] [337] [341] [343] 9 Numerical simulation of chemical kinetics [345] 9.1 Introduction [345] 9.2 The explicit method [346] 9.2a First-order kinetics [346] 9.2b Numerical accuracy [348] 9.2c Dimerization kinetics [350] 9.2d A user-defined function to make the spreadsheet more efficient 9.2e Trimerization kinetics [353] 9.2f Monomer–dimer kinetics 9.2g Polymerization kinetics [354] [355] 9.3 Implicit numerical simulation 9.3a First-order kinetics [359] [359] 9.3b Dimerization kinetics [360] 9.3c Trimerization kinetics [361] 9.3d Monomer–dimer kinetics 9.3e Polymerization kinetics 9.4 Some applications [365] 9.4a Autocatalysis [365] [362] [363] 9.4b Heterogeneous catalysis [367] 9.4c The steady-state approximation [369] 9.4d Oscillating reactions: the Lotka model 9.5 Summary [372] [374] part vi spreadsheet programming 10 Some useful macros [375] 10.1 What is a macro? [375] 10.1a The macro module [376] 10.1b Reading and modifying the contents of a single cell [378] 10.1c Reading and modifying the contents of a block of cells [382] 10.1d Two different approaches to modifying a block of cells [384] 10.1e Numerical precision [387] [351] Contents ix 10.1f Communication via boxes 10.1g Subroutines [389] [393] 10.2 A case study: interpolating in a set of equidistant data 10.2a Step-by-step 10.2b The finished product [401] 10.3 Propagation of imprecision [405] 10.4 Installing and customizing a macro 10.4a Installing external macros 10.4b Assigning a shortcut key 10.4c Embedding in a menu 10.4d Miscellany [410] [410] [411] [412] [414] 10.5 Fourier transformation [415] 10.5a Forward Fourier transformation 10.5b Descriptive notes [416] [420] 10.5c A bidirectional Fourier transformation macro 10.6 Convolution and deconvolution 10.7 Weighted least squares [432] 10.7b Implementation 10.8 More about Solver [426] [432] 10.7a The algorithm [433] [442] 10.8a Adding uncertainty estimates to Solver 10.8b Incorporating Solver into your macro [442] [448] 10.9 Smoothing and differentiating equidistant data 10.10 Semi-integration and semi-differentiation 10.11 Reducing data density 10.12 An overview of VBA 10.12a Objects [463] [466] [467] 10.12b Properties and methods 10.12c Data types 10.12d Expressions 10.12f [469] [470] Active regions [470] [471] 10.12h Ranges [472] 10.12i Subroutines 10.12j Macros [473] [474] 10.12k Functions 10.12l [467] [468] 10.12e Statements 10.12g Cells [474] Message boxes 10.12m Input boxes 10.12n Dialog boxes [475] [475] [476] 10.12o Collective statements 10.12p For…Next loops 10.12q Do loops [395] [396] [478] [477] [476] [449] [460] [421] x Contents 10.12r Conditional statements 10.12s Exit statements 10.13 Summary Index [481] [484] [480] [479]