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.)

2 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. 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( )

3 28 29 Sub Sengen()Dim i As LongDim x As Doublei = 1x = 1#MsgBox(i )MsgBox(x)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).

4 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, ..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.

5 _ 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( ) Then Else End If i= 1 i 1 Then Else Else End If Else If( )

6 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.)

7 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)

8 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.

9 _ () 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)

10 /2*sin(2*p*Rnd) 0 (b a) U (b+a)/2 1000 66 = 0? = % 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 =_____ = _____?