E-Book
81,99 €

Chemometrics in Excel E-Book

Alexey L. Pomerantsev

4,9

81,99 €

oder

Leseprobe lesen

Sammeln Sie Punkte in unserem Gutscheinprogramm und kaufen Sie E-Books und Hörbücher mit bis zu 100% Rabatt.
Mehr erfahren.

Herausgeber: John Wiley & Sons
Kategorie: Wissenschaft und neue Technologien
Sprache: Englisch

Beschreibung

Providing an easy explanation of the fundamentals, methods, and applications of chemometrics * Acts as a practical guide to multivariate data analysis techniques * Explains the methods used in Chemometrics and teaches the reader to perform all relevant calculations * Presents the basic chemometric methods as worksheet functions in Excel * Includes Chemometrics Add In for download which uses Microsoft Excel® for chemometrics training * Online downloads includes workbooks with examples

Details

Sie lesen das E-Book in den Legimi-Apps auf:

Android

iOS

von Legimi
zertifizierten E-Readern

Seitenzahl: 354

Veröffentlichungsjahr: 2014

Bewertungen

4,9 (16 Bewertungen)

Rezensionen(0 Rezensionen)

Leseprobe

Cover

Title Page

Dedication

Preface

Part I : Introduction

Chapter 1: What is Chemometrics?

1.1 Subject of Chemometrics

1.2 Historical Digression

Chapter 2: What the Book Is About?

2.1 Useful Hints

2.2 Book Syllabus

2.3 Notations

Chapter 3: Installation of Chemometrics Add-In*

3.1 Installation

3.2 General Information

Chapter 4: Further Reading on Chemometrics*

4.1 Books

4.2 The Internet

4.3 Journals

4.4 Software

Part II : The Basics

Chapter 5: Matrices and Vectors

5.1 The Basics

5.2 Advanced Information

Chapter 6: Statistics

6.1 The Basics

6.2 Main Distributions

6.3 Parameter Estimation

6.4 Properties of the Estimators

6.5 Confidence Estimation

6.6 Hypothesis Testing

6.7 Regression

Conclusion

Chapter 7: Matrix Calculations in Excel

7.1 Basic Information

7.2 Matrix Operations

7.3 Extension of Excel Possibilities

Conclusion

Chapter 8: Projection Methods in Excel*

8.1 Projection Methods

8.2 Application of Chemometrics Add-In

8.3 PCA

8.4 PLS

8.5 PLS2

8.6 Additional Functions

Conclusion

Part III: Chemometrics

Chapter 9: Principal Component Analysis (PCA)

9.1 The Basics

9.2 Principal Component Analysis

9.3 People and Countries

Conclusion

Chapter 10: Calibration

10.1 The Basics

10.2 Simulated Data

10.3 Classic Calibration

10.4 Inverse Calibration

10.5 Latent Variables Calibration

10.6 Methods Comparison

Conclusion

Chapter 11: Classification

11.1 The Basics

11.2 Data

11.3 Supervised Classification

11.4 Unsupervised Classification

Conclusion

Chapter 12: Multivariate Curve Resolution

12.1 The Basics

12.2 Simulated Data

12.3 Factor Analysis

12.4 Iterative Methods

Conclusion

Part IV: Supplements

Chapter 13: Extension of Chemometrics Add-In

13.1 Using Virtual Arrays

13.2 Using VBA Programming

Conclusion

Chapter 14: Kinetic Modeling of Spectral Data

14.1 The “Grey” Modeling Method

Conclusions

Chapter 15: Matlab®: Beginner'S Guide*

15.1 The Basics

15.2 Matrices

15.3 Integrating Excel and Matlab

15.4 Programming

15.5 Sample Programs

Conclusion

Afterword. The Fourth Paradigm

Index

End User License Agreement

Pages

xvii

xviii

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

Guide

Cover

Table of Contents

Preface

Part I : Introduction

Chapter 1: What is Chemometrics?

List of Illustrations

Figure 2.1

Figure 3.1

Figure 3.2

Figure 3.3

Figure 3.4

Figure 3.5

Figure 3.6

Figure 5.1

Figure 5.2

Figure 5.3

Figure 5.4

Figure 5.5

Figure 5.6

Figure 5.7

Figure 5.8

Figure 5.9

Figure 5.10

Figure 5.11

Figure 5.12

Figure 5.13

Figure 5.14

Figure 5.15

Figure 5.16

Figure 5.17

Figure 5.18

Figure 5.19

Figure 5.20

Figure 5.21

Figure 5.22

Figure 5.23

Figure 5.24

Figure 5.25

Figure 5.26

Figure 5.27

Figure 6.1

Figure 6.2

Figure 6.3

Figure 6.4

Figure 6.5

Figure 6.6

Figure 6.7

Figure 6.8

Figure 6.9

Figure 6.10

Figure 6.11

Figure 6.12

Figure 6.13

Figure 6.14

Figure 6.15

Figure 6.16

Figure 6.17

Figure 6.18

Figure 6.19

Figure 6.20

Figure 6.21

Figure 7.1

Figure 7.2

Figure 7.3

Figure 7.4

Figure 7.5

Figure 7.6

Figure 7.7

Figure 7.8

Figure 7.9

Figure 7.10

Figure 7.11

Figure 7.12

Figure 7.13

Figure 7.14

Figure 7.15

Figure 7.16

Figure 7.17

Figure 7.18

Figure 7.19

Figure 7.20

Figure 7.21

Figure 7.22

Figure 7.23

Figure 7.24

Figure 7.25

Figure 7.26

Figure 7.27

Figure 7.28

Figure 7.29

Figure 7.30

Figure 7.31

Figure 7.32

Figure 7.33

Figure 7.34

Figure 7.35

Figure 7.36

Figure 7.37

Figure 7.38

Figure 7.39

Figure 7.40

Figure 7.41

Figure 7.42

Figure 7.43

Figure 7.44

Figure 7.45

Figure 7.46

Figure 8.1

Figure 8.2

Figure 8.3

Figure 8.4

Figure 8.5

Figure 8.6

Figure 8.7

Figure 8.8

Figure 8.9

Figure 8.10

Figure 8.11

Figure 8.12

Figure 8.13

Figure 8.14

Figure 8.15

Figure 8.16

Figure 8.17

Figure 8.18

Figure 8.19

Figure 9.1

Figure 9.2

Figure 9.3

Figure 9.4

Figure 9.5

Figure 9.6

Figure 9.7

Figure 9.8

Figure 9.9

Figure 9.10

Figure 9.11

Figure 9.12

Figure 9.13

Figure 9.14

Figure 9.15

Figure 9.16

Figure 9.17

Figure 9.18

Figure 9.19

Figure 9.20

Figure 9.21

Figure 9.22

Figure 9.23

Figure 10.1

Figure 10.2

Figure 10.3

Figure 10.4

Figure 10.5

Figure 10.6

Figure 10.7

Figure 10.8

Figure 10.9

Figure 10.10

Figure 10.11

Figure 10.12

Figure 10.13

Figure 10.14

Figure 10.15

Figure 10.16

Figure 10.17

Figure 10.18

Figure 10.19

Figure 10.20

Figure 10.21

Figure 10.22

Figure 10.23

Figure 10.24

Figure 10.25

Figure 10.26

Figure 10.27

Figure 10.28

Figure 10.29

Figure 10.30

Figure 10.31

Figure 10.32

Figure 10.33

Figure 10.34

Figure 10.35

Figure 10.36

Figure 10.37

Figure 10.38

Figure 10.39

Figure 10.40

Figure 10.41

Figure 10.42

Figure 10.43

Figure 10.44

Figure 10.45

Figure 10.46

Figure 10.47

Figure 11.1

Figure 11.2

Figure 11.3

Figure 11.4

Figure 11.5

Figure 11.6

Figure 11.7

Figure 11.8

Figure 11.9

Figure 11.10

Figure 11.11

Figure 11.12

Figure 11.13

Figure 11.14

Figure 11.15

Figure 11.16

Figure 11.17

Figure 11.18

Figure 11.19

Figure 11.20

Figure 11.21

Figure 11.22

Figure 11.23

Figure 11.24

Figure 11.25

Figure 11.26

Figure 11.27

Figure 11.28

Figure 11.29

Figure 11.30

Figure 11.31

Figure 11.32

Figure 11.33

Figure 11.34

Figure 11.35

Figure 11.36

Figure 11.37

Figure 11.38

Figure 11.39

Figure 11.40

Figure 11.41

Figure 12.1

Figure 12.2

Figure 12.3

Figure 12.4

Figure 12.5

Figure 12.6

Figure 12.7

Figure 12.8

Figure 12.9

Figure 12.10

Figure 12.11

Figure 12.12

Figure 12.13

Figure 12.14

Figure 12.15

Figure 12.16

Figure 12.17

Figure 12.18

Figure 12.19

Figure 12.20

Figure 12.21

Figure 12.22

Figure 12.23

Figure 12.24

Figure 12.25

Figure 12.26

Figure 13.1

Figure 13.2

Figure 13.3

Figure 13.4

Figure 13.5

Figure 13.6

Figure 13.9

Figure 13.7

Figure 13.8

Figure 13.10

Figure 13.11

Figure 13.12

Figure 13.13

Figure 14.1

Figure 14.2

Figure 14.3

Figure 14.4

Figure 14.5

Figure 14.6

Figure 14.7

Figure 14.8

Figure 14.9

Figure 14.10

Figure 15.1

Figure 15.2

Figure 15.3

Figure 15.4

Figure 15.5

Figure 15.6

Figure 15.7

Figure 15.8

Figure 15.9

Figure 15.10

Figure 15.11

Figure 15.12

Figure 15.13

Figure 15.14

Figure 15.15

Figure 15.16

Figure 15.17

Figure 15.18

Figure 15.19

Figure 15.20

Figure 15.21

Figure 15.22

Figure 15.23

Figure 15.24

Figure 15.25

Figure 15.26

Figure 15.27

Figure 15.28

Figure 15.29

Figure 15.30

Figure 15.31

Figure 15.32

Figure 15.33

Figure 15.34

Figure 15.35

Figure 15.36

Figure 15.37

List of Tables

Table 9.1

Table 10.1

Table 10.2

Table 10.3

Table 10.4

Table 10.5

Table 10.6

Table 10.7

Table 10.8

Chemometrics in Excel

Alexey L. Pomerantsev

Published by John Wiley & Sons, Inc., Hoboken, New Jersey

Published simultaneously in Canada

No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 750-4470, or on the web at www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permission.

Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives or written sales materials. The advice and strategies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Neither the publisher nor author shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages.

For general information on our other products and services or for technical support, please contact our Customer Care Department within the United States at (800) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic formats. For more information about Wiley products, visit our web site at www.wiley.com.

MATLAB® is a trademark of The MathWorks, Inc. and is used with permission. The MathWorks does not warrant the accuracy of the text or exercises in this book. This book's use or discussion of MATLAB® software or related products does not constitute endorsement or sponsorship by The MathWorks of a particular pedagogical approach or particular use of the MATLAB® software.

Library of Congress Cataloging-in-Publication Data:

Pomerantsev, A. L. (Aleksei Leonidovich), 1954–

[Khemometrika v Excel. English]

Chemometrics in Excel / Alexey L. Pomerantsev.

pages cm

Includes index.

ISBN 978-1-118-60535-6 (cloth)

1.Chemometrics—Data processing.2.Microsoft Excel (Computer file)I. Title.

QD75.4.C45P72 2014

543.0285'554—dc23

2013046049

To Vicky and Yanek

Preface

Chemometrics is a very practical discipline. To learn it, one should not only understand the numerous chemometric methods but also adopt their practical application. This book can assist you in this difficult task. It is aimed primarily at those who are interested in the analysis of experimental data: chemists, physicists, biologists, and others. It can also serve as an introduction for beginners who have started to learn about multivariate data analysis.

The conventional way of chemometrics training utilizes either specialized programs (the Unscrambler, SIMCA, etc.) or the MATLAB®. We suggest our own method that employs the abilities of the world's most popular program, Microsoft Excel®. However, the main chemometric algorithms, for example, projection methods (principal component analysis (PCA), partial least squares (PLS) are difficult to implement using basic Excel facilities. Therefore, we have developed a special supplement to the standard Excel version called Chemometrics Add-In, which can be used to perform such calculations.

This book is not only a manual but also a practical guide to multivariate data analysis techniques. Almost every chapter is accompanied by a special Excel workbook, which contains calculations related to the techniques discussed. For better understanding of the books's material, the reader should constantly refer to these workbooks and try to repeat the calculations. The workbooks with examples as well as Chemometrics Add-In are collected at the Wiley Book Support site (http://booksupport.wiley.com). The installation guide is presented in Chapter 3. We strongly recommend installing and using the software while studying this book.

My colleague Dr. Oxana Rodionova played a very active role in the development of the book. She has designed and developed the Chemometrics Add-In software. She has also assisted in writing many chapters of this book. Therefore, it can be stated without exaggeration that this book would have never been completed without her help. Chapter 15 was written together with my student, Yevgeny V. Mikhailov.

I am deeply grateful to the language editors Svetlana and Pieter Rol who contributed a lot to arrange this book.

We plan to revise and supplement the book continuously. You are welcome to send us your comments and suggestions at [email protected].

Part I

Introduction

Chapter 1What is Chemometrics?

Preceding chapters

Dependent chapters

Matrix skills

Low

Statistical skills

Low

Excel skills

Low

Chemometric skills

Low

Chemometrics Add-In

Not used

Accompanying workbook

None

Chemometrics is what chemometricians do

—a popular wisdom

Chemometricians are the people who drink beer and steal ideas from statisticians

—Svante Wold

1.1 Subject of Chemometrics

What is chemometrics and what it does can be explained in different ways using more or less sophisticated words. At present, we have no generally accepted definition and, apparently, there never will be. The most popular is designation by D. Massart that chemometrics is the chemical discipline that uses mathematical, statistical and other methods employing formal logic to design or select optimal measurement procedures and experiments, and to provide maximum relevant chemical information by analyzing chemical data. This definition gathered a lot of criticism; therefore, new delineations have been suggested. For instance, S. Wold suggested the following formula: Chemometrics solves the following tasks in the field of chemistry: how to get chemically relevant information out of measured chemical data, how to represent and display this information, and how to get such information into data.

This also was not adopted and everybody agreed that the best way to explain the chemometric essence is an old method, proven in other equally obscure areas. It was declared that chemometrics is what chemometricians do. Actually, it was a clear rip-off because a similar idea has already been published,1 but nothing better has been thought up since.

Well, really, what are these chemometricians doing? Here is a small collection of subjects found in the papers published over the past 5 years. Substantially, chemometricians perform the following activities:

manage the production of semiconductors, aspirin, beer, and vodka;

investigate the causes of destruction of documents written by ancient Gallic ink;

conduct doping control in sport;

determine the composition of ancient Egyptian makeup;

localize gold deposit in Sweden;

control the state of forests in Canada;

diagnose arthritis and cancer in the early stages;

investigate the organics in comets;

select pigs' diet;

check how a diet affects the mental capacity;

find traces of cocaine on banknotes collected in the British Parliament;

detect counterfeit drugs;

decide on the origin of wines, oils, and pigments.

As long as everybody has clearly apprehended what chemometricians do, it remains to explain how they do this. However, initially, we have to be acquainted with the basic principles of their activity. Those are not numerous, just three rules, and they are as simple as ABC. The first principle states that no data are redundant, that is, a lot is better than nothing. In practice, this means that if you record a spectrum, it would be stupid to throw out all readings except a few characteristic wavelengths. Scientifically speaking, this is the multivariate methodology in the experimental design and data analysis.

Any data contains undesirable component called noise. The nature of noise can be different and, in many cases, a noise is just a part of data that does not contain relevant information. Which data component should be considered as noise and which component as information is always decided considering the goals and the methods used to achieve them. This is the second chemometric principle: noise is what we do not need.

However, the noise and redundancy necessarily provoke nonsystematic (i.e., noncausal) but random (i.e., correlation) relations between the variables. The difference between causality and correlation has been illustrated humorously in a book by Box and Hunters.2 There is an example of a high positive correlation between the number of inhabitants and the number of storks in Oldenburg (Germany) for the period from 1930 to 1936. This is good news for those who believe that the stork is a key factor in baby boom! However, the reason that these two variables are correlated is very simple. There was a somewhat hidden third variable, which had pair-wise causal relationship with both variables.3 Therefore, the third principle of chemometrics states as follows: seek for hidden variables.

1.2 Historical Digression

Strangely enough, when can somebody claim beyond a doubt that a science was born on a certain date, in a certain place, and under specific circumstances? Who dares, for example, to specify where and when chemistry appeared first? It is only clear that it was long ago and very likely it did not happen under very nice circumstances, and it was related to an urgent necessity to quietly send a sixth priest of the Anubis temple to Kingdom Come or something. But we are quite certain that chemometrics was born on the evening, June 10, 1974, in a small Tex-Mex restaurant in Seattle, as the result of a pretty noisy revelry, arranged on the occasion of Swante's return home in Umea, after a long training under the renowned statistician George Box.

The American side was represented by the chemist Bruce Kowalski with his disciples, who had been intensively developing a software package for chemical data analysis. In fact, the word “chemometrics” has been used since the early 1970s by Svante Wold and his team from the University of Umea, in order to identify, briefly, what they were doing. Actually, they were engaged in an intensive implementation of the interesting ideas produced by Svante's father, Herman Wold, who was a famous statistician, developing many techniques (including the now famous method of the projection to latent structures – PLS) for data analysis in economic and social sciences.

Apparently, with an analogy to psychometrics, biometrics, and other related disciplines, the term chemometrics was coined. The circumstances and the place of birth have left an indelible life mark for the science itself and for the people who are involved in chemometrics. Much later, Svante Wold, being already a very venerable scholar, joked at a conference “chemometricians are the people who drink beer and steal ideas from statisticians.” On the other hand, perhaps, the reason is that the first chemometrician, William Gosset, better known under the pseudonym Student, was employed as an analyst at the Guinness brewery. Anyway, beer and chemometric have gone together for many years. I remember a story told by another famous chemometrician, Agnar Höskuldsson at a Winter Symposium on Chemometrics held near Moscow, in February 2005. The atmosphere at the ceremony of awarding a young Russian chemometrician was semi-official, and Agnar, who presented the award, delivered a fitting address. This remarkable history, almost a parable, is worth presenting word by word. Thus spoke Agnar.

Chemometricians are the cheerful people who like to drink beer and sing merry songs. In the mid-1970s, when I (i.e., Agnar) first met Svante, he had only two students. And so, we, four men, were sitting in an Umea pub in the evening, drank beer, and sang merry songs. After 2 hours, I asked Svante:

“It may already be enough, and is it time to go home?”

“No,” Svante said, “still not enough, we will drink beer and sing merry songs.”

Another hour passed, and I asked Svante again

“It may already be enough, and is it time to go home?”

“No,” he answered, “we still have to drink beer and sing more songs.”

Meanwhile, a few students went down the street, near the pub where we were. They heard we had fun, and stopped and began to ask of the passersby:

“Who are these cheerful people who are singing these merry songs?”

No one could answer them, so the students came in the pub and asked Svante:

“Who are you guys, and why are you singing these merry songs?”

And Svante replied:

“We are chemometricians, the cheerful people, who like to drink beer and sing merry songs. Sit down, drink beer, and sing along with us.”

That was the beginning of the famous Scandinavian school of chemometrics, which gave us many great scientists and cheerful people.

The appearance of chemometrics in Russia was also marked by a funny story. Soon after the establishment of the Russian Chemometrics Society in 1997, my colleagues decided that it was a proper time to announce our existence urbi et orbi. Just that time, we received the first call to the All-Russian conference “Mathematical Methods in Chemistry,” which was to be held in the summer of 1998, in Vladimir city. This was a very popular scientific forum established in 1972 by the Karpov Institute of Physical Chemistry. The conference was a place where many scientists reported on their results; the hottest discussions of the latest mathematical methods applied in chemistry were conducted. We had a hope that using this high rostrum, we could tell fellows about this wonderful “chemometrics” developed by our friends around the world and how interesting and promising this science is. The abstract was written in the name of the Russian Chemometrics Society and timely delivered to the organizing committee.

Time had passed, but there was no response. The deadline was approaching, the program and the lists of speakers were already published, but our contribution was not included. Our bewildered requests went unanswered so we decided that there should be some misunderstanding. As Vladimir is rather close to Moscow, it was decided that we would take a chance and visit the conference without invitation in order to sort things out on the spot. So we did, appearing on the first conference day for the participant registration. This raised a big stink accompanied with an explicit fright, screaming, loud statements against provocation that finally resulted in the appearance of very responsible tovarishchi.

Things were heading toward us getting out of the university, facing the tough administrative sanctions, arrest, and finally imprisonment. Fortunately, in an hour, through the efforts of our old friends from previous conferences, the situation was resolved and we got the casus clarified. The reason was that the unsophisticated Vladimir scientists believed that chemometrics is something like Scientology and our society is a kind of a harmful sect that tries to use the science conference as a chance to embarrass the unstable minds of Vladimir's people and turn their comprehension away from true scientific values. At the end, we were saved by the fact that in the University library, a book titled “Chemometrics,”4 published in 1987 was found. Final reconciliation occurred during the closing banquet, but our lecture, de bene esse, was not permitted.

It is possible that the committee members were guided by the sound judgment of Auguste Comte, who in 1825 warned5 “Every attempt to employ mathematical methods in the study of chemical questions must be considered profoundly irrational and contrary to the spirit of chemistry. If mathematical analysis should ever hold a prominent place in chemistry – an aberration which is happily almost impossible – it would occasion a rapid and widespread degeneration of that science.”

1 P.W. Bridgman. On scientific method, in Reflections of a Physicist, Philosophical Library, NY, 1955.

2 G.E.P. Box, W.G. Hunter, J.S. Hunter. Statistics for Experimenters, John Wiley & Sons Inc., NY, 1978.

3 The subsequent investigations have revealed the nature of a hidden variable, which was proved to be the area of the fields raiseing the cabbage in the Oldenburg neighborhood.

4 M.A. Sharaf, D.L. Illman, B.R. Kowalski. Chemometrics, Wiley, New York, 1986 (Russian translation: Mir, 1987).

5 A. Comte. Cours de Philosophie Positive, Bachelier, Paris, 1830.