不確かさ等の計算に使うExcelの式についてのメモ

ISO/IEC 17025の証明書の発行の際、計算にはなんだかんだでExcelを使用することになる。
証明書に記載する数値の有効数字とか表示桁数についてルールがあり、数値型として値を持ってると誤って必要以上の桁数を標示してしまったりと面倒なことが多い。
セルの表示形式で処理したり、計算後の値を手打したり、面倒なことをしているケースが多いので計算式で処理するようにしてみたのでメモとして残す

■不確かさを有効数字2桁で丸める。
測定器からの出力はVやAが多いので、単位をmV、μV等への変換も行う。
数値として値を持つと有効数字以下にゼロになるだけで、証明書の表示として利用しにくいのでTEXTにしてしまう。
切捨てる値が元の値の5%以下であれば切捨て可だが、面倒なのですべて切上げとしている。

=LET(
unitConversion,IF(A2="m",1000,IF(A2="u",1000000,IF(A2="n",1000000000,1))),
find2digit,INT(LOG10(ABS(A1*unitConversion))),
formattedValue,TEXT(ROUNDUP(A1*unitConversion,2-1-find2digit), "0."&REPT("0",MAX(0,1-find2digit))),
IF(RIGHT(formattedValue, 1) = ".", LEFT(formattedValue, LEN(formattedValue) - 1), formattedValue)
)

■同様に校正値を不確かさの表示桁数と同じ桁数で丸める。
=LET(
unitConversion,IF(A2="m",1000,IF(A2="u",1000000,IF(A2="n",1000000000,1))),
find2digit,INT(LOG10(ABS(A1*unitConversion))),
formattedValue,TEXT(ROUND(B1*unitConversion,2-1-find2digit), "0."&REPT("0",MAX(0,1-find2digit))),
IF(RIGHT(formattedValue, 1) = ".", LEFT(formattedValue, LEN(formattedValue) - 1), formattedValue)
)
■有効自由度から包含係数を求める。

包含係数は正しくは有効自由度νeffのT分布のt95から求めるが、有効自由度10以上であれば2を採用していいので、10以下の時のみT分布から算出する。
これもセルの表示形式に左右されないようTEXTに変換している。

=IF (ISBLANK(A1), "", TEXT(IF(A1 >= 10, 2, TINV(0.05, A1)), "0.00"))

基本的には有効自由度10以上になるように繰り返し数を増やせばいいので、k=2以外を採用することはないと思うが、決め打ちで値を入れておくとミスが起きがちなので、計算式を入れておく