Microsoft Excel ®で学ぶ モンテカルロ計算による 不確かさ評価

1 Microsoft Excel 2020 8 13 23 r cm cm m g g cm 3 95 % 334334rmrm = =g cm 34 r ( )44cmg = rm m 3-3cm cm 45 r r cm 4 r cm g cm 3 m m 3 m g g cm 3 u( )u = + cm 3= cm 36 U U= 2 u( ) = g cm 3 95 % ( U, + U) = ( , + ) g cm 3= ( , ) g cm 3 u(xi) ci |ci|u(xi)(g cm 3) u(r) cm cm u(m) uc( ) 27 = (RAND(),0,1) 8 cm cm = (RAND(), , ) 9 A2 A100 10 g = (RAND(), , ) 11 B2 B100 12 A1 r B1 m r C1 =3/(4*PI())*B1/A1^3 13 C2 C100 14 = (C1:C100) 1516 95 % PERCENTILE =PERCENTILE(C1:C100, ) 17 95 % PERCENTILE =PERCENTILE(C1:C100, ) 18 g cm 3 95 % ( , ) g cm 3 95 % ( , ) g cm 319 % % u( ) = g cm 3( , ) g cm 3 / g cm 3 1.

2 2. 3. A 2021 ISO 17025 22 of measurement data Supplement 1 to the "Guide to the expression of uncertainty in measurement" Propagation of distributions using a Monte Carlo methodJCGM 24 25 26 27 End Sub Msgbox( ) 28 29 Sub Sengen()Dim i As LongDim x As Doublei = 1x = 1#MsgBox(i )MsgBox(x)

3 End Subi x 1# 1 MsgBox() i 1 30 Sub Hairetsu()Dim x(1 To 3) As Doublex(1) = 1#x(2) = 2#x(3) = 3#MsgBox(x(1))MsgBox(x(2))MsgBox(x(3))En d Subx(1) x(2) x(3) x(1) 31 Sub NyuShutsuRyoku1()Dim x As Doublex = Range("A1").ValueRange("B1").Value = xEnd Sub A1 x x B1 32 Sub NyuShutsuRyoku2()Dim x As Doublex = Cells(2,1).ValueCells(2,2).Value = xEnd Sub A2 x x B2 33 Sub Warizan()Dim x As DoubleDim y As DoubleDim z As Doublex = 1y = 2z = x/yMsgBox(z)End Sub + - * / x y 34 Sub KansuCos()Dim x As DoubleDim y As Doublex = 0 y = cos(x)MsgBox(y)End Sub cos() cos VBA 35x^2, x^3.

4 X^(1/2), x^(1/3), ..Abs(x) Sin(x), Cos(x), Tan(x)Exp(x)Log(x)x2, x3, .. x, 3 x, .. |x| sin(x), cos(x), tan(x)exp(x)ln(x) log10(x) Log(x)/Log(10#) 36 VBA Max VBA () VBA Max . _ 37 Sub ChooseMax()Dim x(1 To 3) As DoubleDim y As Doublex(1) = 1#x(2) = 2#x(3) = 3#y = (x)MsgBox(y)End Subx(1)~ x(3) y For 38 Sub Kurikaeshi()Dim i As LongDim n As Longn = 0 For i = 1 To 10n = n + iNextMsgBox(n)End Sub For = 1 To 10 1 10 Next 10 100 1000 100 1000 n = n + i n i n 1 i 2 n = n + i n 1 3 39100 1000 Goukei If 40 Sub JoukenBunki()Dim i As Longi = 1If (i = 1) ThenMsgBox("i 1 ")ElseMsgBox("i 1 ")End IfEnd Sub If( )

5 Then Else End If i= 1 i 1 Then Else Else End If Else If( ) Then End If If 41x = yx > yx >= yx < y x <= y x <> yx y x y x y x y x y x y x y Double Long y 42 Sub Ransu()Dim x As Doublex = RndMsgBox(x)End Sub Rnd 0 1 xba(a+ b)/243 44 Sub ItiyouBunpu()Dima AsDoubleDim b As DoubleDim x As Doublea = -100b = 100x = (b-a)*Rnd+aMsgBox(x)End Sub a b (b-a)*Rnd 0 (b-a) a a b Frequency 45 A A1 A6 B B1 B3 C B C1 C4 4 =Frequency(A1:A6,B1.)

6 B2) Frequency Cntrl Shift Enter 465 C1 B1 C2 B1 B2 C4 B 6 C 477 VBA 1000 0 1 48x 0 49 Sub Histgram()Dimi As LongDim x(1 To 1000) As DoubleDim y(1 To 10) As DoubleDim nCountAs VariantFor i = 1 To 1000x(i) = RndNext( )Variant Frequency Variant 50 For i = 1 To 10y( i) = i * (x, y)For i = 1 To 10 Cells(i, 1) = y(i) - (i, 2) = nCount(i , 1)NextEnd Sub nCount(1,1) nCount(11,1) nCount(11,1) nCount(1,1) y(1) x nCount(2,1) y(1) y(2) nCount(11,1) y(10) 511 B 2 523 4 A1 A10 53 5 OK OK 6 54 2 55 01x Rnd 0 1 0 1 x 0 1 x 56 Sub SeikiBunpu()Dim mu As DoubleDim sigma As DoubleDim x As Doublemu = 100#sigma = 10#x = (Rnd, mu, sigma)MsgBox(x)End Sub mu WorksheetFunction.

7 _ () 0 ~ 1 sigma Rnd 0 1 (Rnd, mu, sigma) Rnd 0 1 Rnd (1-2* )*Rnd+1E-16 1E-16 57 58 1000 Seiki_Hist t 59 = 1 = 3 = 5 t t 60 Sub TBunpu()Dim nu As LongDim x As Doublenu = 4x = (Rnd, nu)MsgBox(x)End Sub () 0 ~ 1 nu 61 62 0 ~ 1 x y z= x y 1 ~ 1 01x01 z= x y1 1 63 Sub SankakuBunpu()Dima AsDoubleDim b As DoubleDim x As Doublea = -100b = 100x = (b-a)/2*(Rnd-Rnd)+(b+a)/2 MsgBox(x)End Sub a b (b-a)/2*(Rnd-Rnd) 0 (b a) (b+a)/2 U sin 640 xbaxabU 65 Sub UjiBunpu()Dima AsDoubleDim b As DoubleDim x As Doublea = -100b = 100p = ()x = (b-a)/2*sin(2*p*Rnd)+(b+a)/2 MsgBox(x)End Sub a b U p = p (b-a)/2*sin(2*p*Rnd) 0 (b a) U (b+a)/2 1000 66 = 0?

8 = % 67 Sub SeikiBunpuPercentile()Dim i As LongDim x(1 To 1000) As DoubleDim y As Doublemu = 0#sigma = 1#For i = 1 To 1000x(i) = (Rnd, mu, sigma)Nexty = (x, )MsgBox(y)End SubPercentile 100a % (100 100a) % 68 9 t 1000 % T_Percent 3. 69 1. 2. 3. A 7071 JCGM 101 JCGM 100 t A 9 12 11 10 =_____ = _____??72 t JCGM101 74 75 Sub TypeAUncertainty()Dimi AsLongDim n1 As LongDim rx1(1 To 4) As DoubleDim mrx1 As DoubleDim srx1 As DoubleDim x1(1 To 1000) As Doublen1 = 4rx1(1) = 9rx1(2) = 12rx1(3) = 11rx1(4) = 10( )rx x 76mrx1 = (rx1) srx1 = (rx1) / n1 ^ i = 1 To 1000x1(i) = mrx1 + srx1 * (Rnd, n1 - 1)NextMsgBox( (x1) )End Sub 3 = n1 - 1 t (s1) (m) 77 r cm cm m g 95 % g cm 3 334334rmrm = =g cm 3 Sub Density1 Histgram()

9 Dim i As LongDim mu1 As DoubleDim sigma1 As DoubleDim x1(1 To 1000000) As DoubleDim mu2 As DoubleDim sigma2 As DoubleDim x2(1 To 1000000) As DoubleDim p As DoubleDim rho(1 To 1000000) As DoubleDim mrhoAs DoubleDim srhoAs DoubleDim nCountAs VariantDim y(1 To 29) As Double 78x1 mu1 sigma1 x2 mu2 sigma2 rho mrho srho y nCount mu1 = 1#sigma1 = i = 1 To 1000000x1(i) = (Rnd, mu1, sigma1)Nextmu2 = 10#sigma2 = i = 1 To 1000000x2(i) = (Rnd, mu2, sigma2)Nextp = ()For i = 1 To 1000000rho(i) = (3 / (4 * p)) * x2(i) / x1(i) ^ 3 Next 79rho x1 mu1 sigma1 x2 p = mrho= (rho)srho= (rho)For i = 1 To 29y(i) = (srho/ 3) * (i- 15) + mrhoNextnCount= (rho, y)For i = 2 To 29 Cells(i, 1).Value = y(i) - (srho/ 6)Cells(i, 2).

10 Value = nCount(i, 1)NextEnd Sub80 ( /3) = (srho/3) y (srho/6) 5 30 ( /3) = (srho/3) g cm 3 g cm 3 95 % 0 ~ 95 % ~ % 5 ~ 100 % 95 % 81()3g/cm JCGM 101 82 % % 95 % JCGM 101 0 % 95 % 5 % 100% 83 Sub Density1 Coverage()Dim i As LongDim mu1 As DoubleDim sigma1 As DoubleDim x1(1 To 1000000) As DoubleDim mu2 As DoubleDim sigma2 As DoubleDim x2(1 To 1000000) As DoubleDim p As DoubleDim rho(1 To 1000000) As DoubleDim rhoiAs DoubleDim rhoeAs DoubleDim d As DoubleDim dminAs DoubleDim rhominAs DoubleDim rhomaxAs Double 84mu1 = 1#sigma1 = i = 1 To 1000000x1(i) = (Rnd, mu1, sigma1)Nextmu2 = 10#sigma2 = i = 1 To 1000000x2(i) = (Rnd, mu2, sigma2)Nextp = ()For i = 1 To 1000000rho(i) = (3 / (4 * p)) * x2(i) / x1(i) ^ 3 Next( )85 rhomin= (rho, 0#)rhomax= (rho, )dmin= rhomax- rhominFor i = 1 To 50di = * irhoi= (rho, 0# + di)rhoe= (rho, + di)d = rhoe rhoiIf (d < dmin) Thendmin= drhomin= rhoirhomax= rhoeEnd IfNextCells(1, 3).