Chemometrics in Excel

Alexey L Pomerantsev is a Leading Researcher at The Russian Academy of Science. He is a founding member and Chair of the Russian Chemometrics Society, being instrumental in organizing the annual Winter Symposium on Chemometrics. He is a peer reviewer and member of Editorial Board of the Journal ‘Chemometrics and Intelligent Laboratory Systems.’ Dr. Pomerantsev has over 100 publications, many of them dealing with Chemometric Investigations.

• Preface xviiPART I INTRODUCTION 11 What is Chemometrics? 31.1 Subject of Chemometrics, 31.2 Historical Digression, 52 What the Book Is About? 82.1 Useful Hints, 82.2 Book Syllabus, 92.3 Notations, 103 Installation of Chemometrics Add-In 113.1 Installation, 113.2 General Information, 144 Further Reading on Chemometrics 154.1 Books, 154.1.1 The Basics, 154.1.2 Chemometrics, 164.1.3 Supplements, 164.2 The Internet, 174.2.1 Tutorials, 174.3 Journals, 174.3.1 Chemometrics, 174.3.2 Analytical, 184.3.3 Mathematical, 184.4 Software, 184.4.1 Specialized Packages, 184.4.2 General Statistic Packages, 194.4.3 Free Ware, 19PART II THE BASICS 215 Matrices and Vectors 235.1 The Basics, 235.1.1 Matrix, 235.1.2 Simple Matrix Operations, 245.1.3 Matrices Multiplication, 255.1.4 Square Matrix, 265.1.5 Trace and Determinant, 275.1.6 Vectors, 285.1.7 Simple Vector Operations, 295.1.8 Vector Products, 295.1.9 Vector Norm, 305.1.10 Angle Between Vectors, 305.1.11 Vector Representation of a Matrix, 305.1.12 Linearly Dependent Vectors, 315.1.13 Matrix Rank, 315.1.14 Inverse Matrix, 315.1.15 Pseudoinverse, 325.1.16 Matrix–Vector Product, 335.2 Advanced Information, 335.2.1 Systems of Linear Equations, 335.2.2 Bilinear and Quadratic Forms, 345.2.3 Positive Definite Matrix, 345.2.4 Cholesky Decomposition, 345.2.5 Polar Decomposition, 345.2.6 Eigenvalues and Eigenvectors, 355.2.7 Eigenvalues, 355.2.8 Eigenvectors, 355.2.9 Equivalence and Similarity, 365.2.10 Diagonalization, 375.2.11 Singular Value Decomposition (SVD), 375.2.12 Vector Space, 385.2.13 Space Basis, 395.2.14 Geometric Interpretation, 395.2.15 Nonuniqueness of Basis, 395.2.16 Subspace, 405.2.17 Projection, 406 Statistics 426.1 The Basics, 426.1.1 Probability, 426.1.2 Random Value, 436.1.3 Distribution Function, 436.1.4 Mathematical Expectation, 446.1.5 Variance and Standard Deviation, 446.1.6 Moments, 446.1.7 Quantiles, 456.1.8 Multivariate Distributions, 456.1.9 Covariance and Correlation, 456.1.10 Function, 466.1.11 Standardization, 466.2 Main Distributions, 466.2.1 Binomial Distribution, 466.2.2 Uniform Distribution, 476.2.3 Normal Distribution, 486.2.4 Chi-Squared Distribution, 506.2.5 Student’s Distribution, 526.2.6 F-Distribution, 536.2.7 Multivariate Normal Distribution, 546.2.8 Pseudorandom Numbers, 556.3 Parameter Estimation, 566.3.1 Sample, 566.3.2 Outliers and Extremes, 566.3.3 Statistical Population, 566.3.4 Statistics, 576.3.5 Sample Mean and Variance, 576.3.6 Sample Covariance and Correlation, 586.3.7 Order Statistics, 596.3.8 Empirical Distribution and Histogram, 606.3.9 Method of Moments, 616.3.10 The Maximum Likelihood Method, 626.4 Properties of the Estimators, 626.4.1 Consistency, 626.4.2 Bias, 636.4.3 Effectiveness, 636.4.4 Robustness, 636.4.5 Normal Sample, 646.5 Confidence Estimation, 646.5.1 Confidence Region, 646.5.2 Confidence Interval, 656.5.3 Example of a Confidence Interval, 656.5.4 Confidence Intervals for the Normal Distribution, 656.6 Hypothesis Testing, 666.6.1 Hypothesis, 666.6.2 Hypothesis Testing, 666.6.3 Type I and Type II Errors, 676.6.4 Example, 676.6.5 Pearson’s Chi-Squared Test, 676.6.6 F-Test, 696.7 Regression, 706.7.1 Simple Regression, 706.7.2 The Least Squares Method, 716.7.3 Multiple Regression, 72Conclusion, 737 Matrix Calculations in Excel 747.1 Basic Information, 747.1.1 Region and Language, 747.1.2 Workbook, Worksheet, and Cell, 767.1.3 Addressing, 777.1.4 Range, 787.1.5 Simple Calculations, 787.1.6 Functions, 787.1.7 Important Functions, 817.1.8 Errors in Formulas, 857.1.9 Formula Dragging, 867.1.10 Create a Chart, 877.2 Matrix Operations, 887.2.1 Array Formulas, 887.2.2 Creating and Editing an Array Formula, 907.2.3 Simplest Matrix Operations, 917.2.4 Access to the Part of a Matrix, 917.2.5 Unary Operations, 937.2.6 Binary Operations, 957.2.7 Regression, 957.2.8 Critical Bug in Excel 2003, 997.2.9 Virtual Array, 997.3 Extension of Excel Possibilities, 1007.3.1 VBA Programming, 1007.3.2 Example, 1017.3.3 Macro Example, 1037.3.4 User-Defined Function Example, 1047.3.5 Add-Ins, 1057.3.6 Add-In Installation, 106Conclusion, 1078 Projection Methods in Excel 1088.1 Projection Methods, 1088.1.1 Concept and Notation, 1088.1.2 PCA, 1098.1.3 PLS, 1108.1.4 Data Preprocessing, 1118.1.5 Didactic Example, 1128.2 Application of Chemometrics Add-In, 1138.2.1 Installation, 1138.2.2 General, 1138.3 PCA, 1148.3.1 ScoresPCA, 1148.3.2 LoadingsPCA, 1148.4 PLS, 1168.4.1 ScoresPLS, 1168.4.2 UScoresPLS, 1178.4.3 LoadingsPLS, 1188.4.4 WLoadingsPLS, 1198.4.5 QLoadingsPLS, 1208.5 PLS2, 1218.5.1 ScoresPLS2, 1218.5.2 UScoresPLS2, 1228.5.3 LoadingsPLS2, 1248.5.4 WLoadingsPLS2, 1258.5.5 QLoadingsPLS2, 1268.6 Additional Functions, 1278.6.1 MIdent, 1278.6.2 MIdentD2, 1278.6.3 MCutRows, 1298.6.4 MTrace, 129Conclusion, 130PART IIICHEMOMETRICS 1319 Principal Component Analysis (PCA) 1339.1 The Basics, 1339.1.1 Data, 1339.1.2 Intuitive Approach, 1349.1.3 Dimensionality Reduction, 1369.2 Principal Component Analysis, 1369.2.1 Formal Specifications, 1369.2.2 Algorithm, 1379.2.3 PCA and SVD, 1379.2.4 Scores, 1389.2.5 Loadings, 1399.2.6 Data of Special Kind, 1409.2.7 Errors, 1409.2.8 Validation, 1439.2.9 Decomposition “Quality”, 1439.2.10 Number of Principal Components, 1449.2.11 The Ambiguity of PCA, 1459.2.12 Data Preprocessing, 1469.2.13 Leverage and Deviation, 1469.3 People and Countries, 1469.3.1 Example, 1469.3.2 Data, 1479.3.3 Data Exploration, 1479.3.4 Data Pretreatment, 1489.3.5 Scores and Loadings Calculation, 1499.3.6 Scores Plots, 1519.3.7 Loadings Plot, 1529.3.8 Analysis of Residuals, 153Conclusion, 15310 Calibration 15610.1 The Basics, 15610.1.1 Problem Statement, 15610.1.2 Linear and Nonlinear Calibration, 15710.1.3 Calibration and Validation, 15810.1.4 Calibration “Quality”, 16010.1.5 Uncertainty, Precision, and Accuracy, 16210.1.6 Underfitting and Overfitting, 16310.1.7 Multicollinearity, 16410.1.8 Data Preprocessing, 16610.2 Simulated Data, 16610.2.1 The Principle of Linearity, 16610.2.2 “Pure” Spectra, 16610.2.3 “Standard” Samples, 16610.2.4 X Data Creation, 16710.2.5 Data Centering, 16810.2.6 Data Overview, 16810.3 Classic Calibration, 16910.3.1 Univariate (Single Channel) Calibration, 16910.3.2 The Vierordt Method, 17210.3.3 Indirect Calibration, 17410.4 Inverse Calibration, 17610.4.1 Multiple Linear Calibration, 17710.4.2 Stepwise Calibration, 17810.5 Latent Variables Calibration, 18010.5.1 Projection Methods, 18010.5.2 Latent Variables Regression, 18410.5.3 Implementation of Latent Variable Calibration, 18510.5.4 Principal Component Regression (PCR), 18610.5.5 Projection on the Latent Structures-1 (PLS1), 18810.5.6 Projection on the Latent Structures-2 (PLS2), 19110.6 Methods Comparison, 193Conclusion, 19711 Classification 19811.1 The Basics, 19811.1.1 Problem Statement, 19811.1.2 Types of Classes, 19911.1.3 Hypothesis Testing, 19911.1.4 Errors in Classification, 20011.1.5 One-Class Classification, 20011.1.6 Training and Validation, 20111.1.7 Supervised and Unsupervised Training, 20111.1.8 The Curse of Dimensionality, 20111.1.9 Data Preprocessing, 20111.2 Data, 20211.2.1 Example, 20211.2.2 Data Subsets, 20311.2.3 Workbook Iris.xls, 20411.2.4 Principal Component Analysis, 20511.3 Supervised Classification, 20511.3.1 Linear Discriminant Analysis (LDA), 20511.3.2 Quadratic Discriminant Analysis (QDA), 21011.3.3 PLS Discriminant Analysis (PLSDA), 21411.3.4 SIMCA, 21711.3.5 k-Nearest Neighbors (kNN), 22311.4 Unsupervised Classification, 22511.4.1 PCA Again (Revisited), 22511.4.2 Clustering by K-Means, 225Conclusion, 22912 Multivariate Curve Resolution 23012.1 The Basics, 23012.1.1 Problem Statement, 23012.1.2 Solution Ambiguity, 23212.1.3 Solvability Conditions, 23412.1.4 Two Types of Data, 23512.1.5 Known Spectrum or Profile, 23612.1.6 Principal Component Analysis (PCA), 23612.1.7 PCA and MCR, 23712.2 Simulated Data, 23712.2.1 Example, 23712.2.2 Data, 23812.2.3 PCA, 23812.2.4 The HELP Plot, 24012.3 Factor Analysis, 24112.3.1 Procrustes Analysis, 24112.3.2 Evolving Factor Analysis (EFA), 24412.3.3 Windows Factor Analysis (WFA), 24612.4 Iterative Methods, 24912.4.1 Iterative Target Transform Factor Analysis (ITTFA), 24912.4.2 Alternating Least Squares (ALS), 250Conclusion, 252PART IV SUPPLEMENTS 25513 Extension Of Chemometrics Add-In 25713.1 Using Virtual Arrays, 25713.1.1 Simulated Data, 25713.1.2 Virtual Array, 25913.1.3 Data Preprocessing, 25913.1.4 Decomposition, 26013.1.5 Residuals Calculation, 26013.1.6 Eigenvalues Calculation, 26213.1.7 Orthogonal Distances Calculation, 26313.1.8 Leverages Calculation, 26413.2 Using VBA Programming, 26513.2.1 VBA Advantages, 26513.2.2 Virtualization of Real Arrays, 26513.2.3 Data Preprocessing, 26613.2.4 Residuals Calculation, 26713.2.5 Eigenvalues Calculation, 26813.2.6 Orthogonal Distances Calculation, 26913.2.7 Leverages Calculation, 270Conclusion, 27114 Kinetic Modeling of Spectral Data 27214.1 The “Grey” Modeling Method, 27214.1.1 Problem Statement, 27214.1.2 Example, 27414.1.3 Data, 27414.1.4 Soft Method of Alternating Least Squares (Soft-ALS), 27514.1.5 Hard Method of Alternating Least Squares (Hard-ALS), 27714.1.6 Using Solver Add-In, 279Conclusions, 28215 MATLAB®: Beginner’s Guide 28315.1 The Basics, 28315.1.1 Workspace, 28315.1.2 Basic Calculations, 28515.1.3 Echo, 28515.1.4 Workspace Saving: MAT-Files, 28615.1.5 Diary, 28615.1.6 Help, 28715.2 Matrices, 28715.2.1 Scalars, Vectors, and Matrices, 28715.2.2 Accessing Matrix Elements, 28915.2.3 Basic Matrix Operations, 28915.2.4 Special Matrices, 29015.2.5 Matrix Calculations, 29215.3 Integrating Excel and MATLAB®, 29415.3.1 Configuring Excel, 29415.3.2 Data Exchange, 29415.4 Programming, 29515.4.1 M-Files, 29515.4.2 Script File, 29615.4.3 Function File, 29715.4.4 Plotting, 29815.4.5 Plot Printing, 30015.5 Sample Programs, 30115.5.1 Centering and Scaling, 30115.5.2 SVD/PCA, 30115.5.3 PCA/NIPALS, 30215.5.4 PLS1, 30315.5.5 PLS2, 304Conclusion, 306Afterword. The Fourth Paradigm 307Index 311

“The book is for sure very interesting and very well written, and it covers all the major topics of chemometrics.”  (Journal of Chemometrics, 14 July 2015)