Microsoft Word - 1

Size: px

Start display at page:

Download "Microsoft Word - 1"

ありかつあざみ
4 years ago
Views:

1 J Comput Chem Jpn, Vol 7, No 2, pp (2008) Microsoft Excel (1) *,, * (Received: January 28, 2008; Accepted for publication: May 1, 2008; Advance publication: May 30, 2008) Microsoft Excel Excel Excel Excel Excel : Microsoft Excel,,,, 1 PC [1 4] MATLAB Microsoft Excel Excel Excel Excel Visual Basic for Applications VBA Excel (array formula) DOI: /jccjH

Microsoft Windows Excel Mac OS Excel 2 Excel 21 Excel Excel [5, 6] Excel Excel Excel Figure 1 A1 D4 4 4 Excel Excel ( ) Excel : Figure 1 A1:D4 SUM Figure 1 =SUM(A1,A2,A3,,D4) =SUM(A1:D4) 22 Excel

2 Microsoft Windows Excel Mac OS Excel 2 Excel 21 Excel Excel [5, 6] Excel Excel Excel Figure 1 A1 D4 4 4 Excel Excel ( ) Excel : Figure 1 A1:D4 SUM Figure 1 =SUM(A1,A2,A3,,D4) =SUM(A1:D4) 22 Excel Figure 1 A1 D4 A6 D9 A6 =A1 A1 A6 D9 4 4 D9 =D4 Figure 2 Figure 2(a) A6 D9 ( A6 D9 ) Figure 2(b) ( A6 ) =A1:D4 ( [Enter] ) ( ) [Enter] [Ctrl] [Shift] [Enter] (a) (b) (c) Figure 1 A simple example of the array expressed on an Excel worksheet Figure 2 An example of the array formula for refering the cell range A1:D4 to A6:D9 (a)select the cell range A6:D9 (b)type =A1:D4 (c)press [Ctrl] + [Shift] + [Enter] key 72 J Comput Chem Jpn, Vol 7, No 2 (2008)

[Ctrl] [Shift] [Enter] [Enter] Figure 2(c) A1:D4 A6 D9 f=a1:d4g fg 1 [F2] A6 D9 Figure 3(a) A6 2 A8 D11 4 4 A8 =A1:D4*A6 Figure 3(a) Figure 3(b) A1:D4 2 A6 A8 =A1*$A$6 D11 Figure 4 1 B6 D6 A6 D6 1 4

3 [Ctrl] [Shift] [Enter] [Enter] Figure 2(c) A1:D4 A6 D9 f=a1:d4g fg 1 [F2] A6 D9 Figure 3(a) A6 2 A8 D A8 =A1:D4*A6 Figure 3(a) Figure 3(b) A1:D4 2 A6 A8 =A1*$A$6 D11 Figure 4 1 B6 D6 A6 D6 1 4 Figure 4(a) A8 D11 A8 =A1:D4*A6:D6 Figure 4(b) A8:D11 A6:D6 A8 =A1*A$6 2 1 A 2 1 = A6:D6*A1:D4 1 (a) (a) (b) Figure 3 Example of a calculation using the array formula The output array (A8:D11) has elements whose values are twice those of the original array (A1:D4) (a)select the cell range A8:D11 Type =A1:D4*A6 (b)press [Ctrl] + [Shift] + [Enter] key (b) Figure 4 Product of 1 4 array by 4 4 array (a)select the cell range A8:D11 Type =A1:D4*A6:D6 (b)press [Ctrl] + [Shift] + [Enter] key DOI: /jccjH

Figure 5 A6 D9 Figure 5(a) 4 4 A11 D14 A11 =A1:D4*A6:D9 2 Figure 5(b) A11 =A1*A6 1 Figure 6 A1 D4 A [ ] A A A1:D4 A A1:D4 C, R, A1 [Ctrl]+[F3] 23

4 Name box Figure 6 Naming the cell range (A1:D4) A using name box (1)Select the cell range A1:D4 (2)Type A in [name box] and press [Enter] key Figure 5 Calculation of product between two arrays of 4 4 size (a)select the cell range A8:D11 Type =A1:D4*A6:D9 (b)press [Ctrl] + [Shift] + [Enter] key 4 4 Figure 5 A6 D9 Figure 5(a) 4 4 A11 D14 A11 =A1:D4*A6:D9 2 Figure 5(b) A11 =A1*A6 1 Figure 6 A1 D4 A [ ] A A A1:D4 A A1:D4 C, R, A1 [Ctrl]+[F3] 23 Excel TRANS- POSE MMULT MDETERM MINVERSE Figure 7 A1 D4 A 2 A6 D9 =TRANS- POSEA(A) A =TRANSPOSE(A1:D4) A6 D9 AT 74 J Comput Chem Jpn, Vol 7, No 2 (2008)

Original matrix, A Transposed matrix of A, AT Formula: =TRANSPOSE(A) Product between A and AT, AAT Formula: =MMULT(A,AT) Determination of AAT Formula: =MDETERM(AAT) Inverse matrix of AAT, INV

5 Original matrix, A Transposed matrix of A, AT Formula: =TRANSPOSE(A) Product between A and AT, AAT Formula: =MMULT(A,AT) Determination of AAT Formula: =MDETERM(AAT) Inverse matrix of AAT, INV Formula: =MINVERSE(AAT) Product between AAT and INV Formula: =MMULT(AAT, INV) Figure 7 Examples of matrix calculations on an Excel worksheet A11 D14 =MMULT(A,AT) MMULT(AT,A) 2 AAT MMULT 5461 Excel 2007 Excel 2003 =MDE- TERM(AAT) A A18 D21 INV =MINVERSE(AAT) =MMULT(AAT,INV) A23 D ( ) AA T DOI: /jccjH

=MMULT(A,TRANSPOSE(A)) ;1 AA AA T T =MMULT(MMULT(A,TRANSPOSE(A)),MINVER SE(MMULT(A,TRANSPOSE(A)))) MMULT(A,TRAN SPOSE(A)) 2 MMULT(A,TRANSPOSE(A)) ( ) AAT =MMULT(AAT,MINVERSE(AAT)) Figure 8 2 Va, Vb

6 =MMULT(A,TRANSPOSE(A)) ;1 AA AA T T =MMULT(MMULT(A,TRANSPOSE(A)),MINVER SE(MMULT(A,TRANSPOSE(A)))) MMULT(A,TRAN SPOSE(A)) 2 MMULT(A,TRANSPOSE(A)) ( ) AAT =MMULT(AAT,MINVERSE(AAT)) Figure 8 2 Va, Vb Excel =MMULT(TRANSPOSE(Va),Vb) A7 2 =SUMPODUCT(Va,Vb) A8 SUMPRODUCT TRANSPOSE MMULT 3 31 f g j j (= 1, 2,) a j f = a 1 g 1 + a 2 g 2 + = 1X j=1 ajgj (1) r Va Vb formula: =MMULT(Va,TRANSPOSE(Vb)) and press [Ctrl] + [Shift] + [Enter] key formula: =SUMPRODUCT(Va,Vb) and press [Enter] key Figure 8 Example of product between two vectors SUMPRODUCT function is simpler expression than MMULT function for this calculation f = nx j=1 ajgj + r (2) i (= 1, 2,, m) (2) fi = nx j=1 ajgij + ri (2) (2) 0 f1 f2 fi fm 1 0 = C A g11 g12 g1j g1n g21 g22 g2j g2n gi1 gi2 gij gin gm1gm2gmjgmn 10 C A a1 a2 aj an C A r1 r2 ri rm 1 C A f i, g ij, a j, r i f G a r (3) f = Ga + r (4) r i 0 a n = m (2) r i 0 a n < m r i 0 r i a r i E E a j 0 76 J Comput Chem Jpn, Vol 7, No 2 (2008)

7 G T f = G T Ga LS (5) a LS LS n (5) a LS a LS =(G T G) ;1 G T f (6) a LS f pre = Ga LS (7) f f pre Excel f f = ; x ; 9000x 2 + x 3 (8) x = Figure 9 B E 2 4 x f 8 [ ]x [ ] =B$4*$A5ˆB$2 B5 E25 =B4:E4*A5:A25ˆB2:E2 B5 E25 Figure 9 x x ( =SUM(B5:E5) ) F f B5 E25 F5 =SUM($B$4:$E$4*A5ˆ$B$2:$E$2) ( 1 ) F6 F25 SUMPRODUCT =SUMPRODUCT($B$4:$E$4,A5ˆ$B$2:$E$2) F5 F25 Figure 9 G ' F 3 [7] NORMINV G5 =F5+$G$4*NORMINV(RAND(),0,1) F25 G4 0( ) G4 0 Figure 9 G [F9] 322 Figure 10 Figure 9 x Figure 9 A5 A25 Figure 10 B5 B25 f Figure 9 A5 A25 Figure 10 C5 [Alt] [E] [S] [V] [Enter] C5 C25 Excel Figure DOI: /jccjH

Figure 9 Generaion of cubic curve data without and with error *SD:standard deviation 10 A i (2) i x i x i x i x Figure 10 B C f f f x 0 3 (2) g ij x 0 3000 x 0 y i = x i 3000 20 20 Figure 10 E E5 E25

8 Figure 9 Generaion of cubic curve data without and with error *SD:standard deviation 10 A i (2) i x i x i x i x Figure 10 B C f f f x 0 3 (2) g ij x x 0 y i = x i Figure 10 E E5 E25 =B5:B25-B15 =B5:B E5 E25 y G4 J4 0 3 deg y deg (4) G Figure 10 G5 J25 =IF(deg=0,1,yˆdeg) IF deg 0 1 y yˆdeg G5 J25 G a LS 4 1 N5 N8 als (6) =MMULT(MINVERSE(MMULT(TRANSPOSE(G), G)),MMULT(TRANSPOSE(G),f)) (a) 78 J Comput Chem Jpn, Vol 7, No 2 (2008)

9 Figure 10 The least squares fitting calculation using matrix on an Excel worksheet Coefficient of each degree, a LS, was calculated by *1: formula (a) and *2: formula (b) (6) Excel (6) 1 N5 N8 0, -100, 0, 1 (6) o (a) (6) n(g T G) ;1 G T f (G T G) ;1 (G T f ) Excel (b) =MMULT(MMULT(MINVERSE(MMULT(TRANS POSE(G),G)),TRANSPOSE(G)),f) (b) (b) a LS Figure 10 N11 N14 0 N11 114E-13 0 (a) (b) (7) G a LS f f pre =MMULT(G,aLS) Figure 10 P Excel LINEST f pre f R (9) R = vu u t X n i=0, ; 2 nx ; f prei ; f pre fi ; f 2: i=0 (9) DOI: /jccjH

10 f 6000 no error f f pre with error x Figure 11 Comparison of fittings for data with and without error The original data without error, f (), and fitting data, f pre (+) The original data with error, '(), and fitting data, ' pre () f f pre fi f prei Figure 10 N17 =SQRT(DEVSQ(P5:P25)/DEVSQ(f)) f f pre R 1 Figure 11 f f pre 3 3 Figure 9 ' G5 G25 Figure 10 f C5 C25 3 Figure 11 ' ' pre ' f f f ' pre f pre Figure 10 R pre 3 (1) (6) g j g j 323 f a LS a LS y y = x 3000 f pre f pre = ;100y + y 3 = ;100(x ; 3000) + (x ; 3000) 3 (10) y (8) x f pre (10) x (10) y =(x + x 0 ) y x Excel y x MX k=0 bky k = MX l=0 alx l, y = x + x 0 (11) a l b k al = MX k=l bk k Cl x k;l 0 (12) 80 J Comput Chem Jpn, Vol 7, No 2 (2008)

Figure 12 Coefficients conversion from y polynomial to x polynomial Figure 12 y x Excel 3 0 3 B3 B6 y = x 3000 x 0 = 3000 B9 (12) x k C l COMBIN =COMBIN(k,l) E3

11 Figure 12 Coefficients conversion from y polynomial to x polynomial Figure 12 y x Excel B3 B6 y = x 3000 x 0 = 3000 B9 (12) x k C l COMBIN =COMBIN(k,l) E3 =SUM(IF($A$3:$A$6<D3,0,$B$3:$B$6*COMBIN ($A$3:$A$6,D3)*$B$9ˆ($A$3:$A$6-D3))) (c) (c) E3 E4 E6 E3 (12) k l Excel IF k l 0 x , , -9000, 1 (8) a LS B3 B6 4 Excel 1 Excel 2 SLOPE INTER- CEPT LINEST 1 Excel (PCA) (PCR) partial least squares (PLS) Excel [1],, (2005) [2],, (2003) [3],,,, (2002) [4],,, (1995) [5], Excel, (2005) [6],, Special No78 Excel,CQ (2002) [7], J Comput Chem Jpn, 5, 29 (2006) DOI: /jccjH

12 Chemometrics Calculations with Microsoft Excel (1) Matrix Calculations and Multi Regression Norio YOSHIMURA* and Masao TAKAYANAGI Graduate School of Agriculture, Tokyo University of Agriculture and Technology Saiwaicho Fuchu, Tokyo Japan * Although chemometrics has become widely used recently for analyzing experimental chemical data, there exist in Japan only a few instructions for the proper usage of chemometrics except for some introductory books We have found that Microsoft Excel (Excel) is convenient, inexpensive, and popular software for chemometrics calculations especially for educational purposes In this paper, the first of a series on chemometrics calculations with Excel, instructions for matrix calculations and thier applications to multivariate analysis with Excel are described in detail Array formulas and array calculations are explained in relation to basic handlings of matrix (eg calculations of determination and inverse matrices) We also demonstrate applications of array formulas to calculations of polynomial curves, and to curve fittings Keywords: Microsoft Excel, Chemometrics, Array formula, Matrix, Multi regression 82 J Comput Chem Jpn, Vol 7, No 2 (2008)

JAMSTEC Rep. Res. Dev., Volume 12, March 2011, 27 _ 35 1,2* Pb 210 Pb 214 Pb MCA 210 Pb MCA MCA 210 Pb 214 Pb * 2

JAMSTEC Rep. Res. Dev., Volume 12, March 2011, 27 _ 35 1,2* 1 1 1 1 210 Pb 210 Pb 214 Pb MCA 210 Pb MCA MCA 210 Pb 214 Pb 2010 10 4 2010 12 10 1 2 * 237-0061 2-15 046-867-9794 ogurik@jamstec.go.jp 27 210