エクセルで2個から10個のデータの差の小さい2つ

質問

エクセルでG列に最初2つの試験結果が入力されその相対誤差（大値から小値を引いてそれを平均で割った値）が5%以上なら3つ目の結果を入力します。
この3つの中で一番近い値の相対誤差を求めて、相対誤差が５％以下になるまで（最大10個程度）試験結果を入れていくのですがこれを満たす式（方法論）が思いつきません。
つまりG列に入力された数値の中で一番近い2つの値の相対誤差を求める表を作りたいのです。

総当たりで各々の差を計算してその相対誤差の一番小さな値を求めればよいと思うのですがかなり大きな表になってしまうのでもっと簡単な式ができないかと思い質問させていただきます。

回答No.7

HohoPapa · ベストアンサー

>さらに途中に空白セルが入っても問題なく動作しました。
たまたま問題がないだけで、考慮していません。!(^^)!

＞得られた相対誤差の結果を同じシートの指定のセルに入力までさせたいのです

それぞれ対応してみました。

Sub Sample()

　Dim i As Long　　　　　'作業カウンター
　Dim j As Long　　　　　'作業カウンター
　Dim c As Long　　　　　'要素の配列位置
　Dim n As Long　　　　　'要素の数
　Dim y() As Double　　　'要素格納配列
　Dim RangeX As Range　　'対象セル範囲
　Dim Gosa As Double　　 '算出誤差
　Dim wkGosa As Double　 '作業用誤差
　Dim HitRow1 As Long　　'ヒットした値の位置１
　Dim HitRow2 As Long　　'ヒットした値の位置２
　Dim MsgText As String　'メッセージテキスト

　Set RangeX = Selection

　If RangeX.Columns.Count <> 1 Then Exit Sub
　n = RangeX.Rows.Count
　If n <= 1 Then Exit Sub

　ReDim y(n)
　For c = 1 To n　 '配列に要素を格納
　　If IsNumeric(RangeX.Cells(c, 1).Value) = False Then Exit Sub
　　y(c) = RangeX.Cells(c, 1).Value
　Next c

　RangeX.Interior.ColorIndex = 0　'背景色を無しに

　Gosa = 100　'適当な大ききな値
　HitRow1 = 0
　HitRow2 = 0

　For i = 1 To n - 1　'総当たりで相対誤差を算出
　　For j = i + 1 To n
　　　If ((y(i) <> 0) And (y(j) <> 0)) Then
　　　　wkGosa = Abs(y(i) - y(j)) / ((y(i) + y(j)) / 2)
　　　　If Gosa > wkGosa Then　'より小さい場合に位置を記憶
　　　　　Gosa = wkGosa
　　　　　HitRow1 = i
　　　　　HitRow2 = j
　　　　End If
　　　End If
　　Next j
　Next i

　If Gosa = 100 Then Exit Sub

　With ActiveSheet
　'With ThisWorkbook.Sheets("sh誤差")
　　.Cells(2, 3).Value = "相対誤差："
　　.Cells(2, 4).Value = Format(Gosa, "0.00%")
　　.Cells(3, 3).Value = "値１："
　　.Cells(3, 4).Value = y(HitRow1)
　　.Cells(4, 3).Value = "値２：相対誤差:"
　　.Cells(4, 4).Value = y(HitRow2)
　End With

　If Gosa < 0.05 Then　'5%未満なら色を染める
　　RangeX.Cells(HitRow1).Interior.Color = vbYellow
　　RangeX.Cells(HitRow2).Interior.Color = vbYellow
　End If

End Sub

お礼

完璧です!
これにて一件落着です。

本当にありがとうございました。

補足

解説付きでのコードも何となく分かる気がして安心感が持てます。
実際にほんの少し（セル等）変えることもありますが、決して自作できとは思わないです。。。。

回答No.6

bunjii

>上記で質問内容がご理解いただけましたでしょうか?
勝手解釈になるかも知れませんが添付画像のような表を作成してみました。
B2に下記の数式を設定して最終の入力値を求めます。
=INDEX(G:G,MAX(INDEX(ISNUMBER(G2:G11)*ROW(G2:G11),0)))
C2は最終入力値と他の入力値の比較をして最少差を求めます。
=SMALL(INDEX(ABS(G2:G11-B2)+ISBLANK(G2:G11)*9,0),2)
A2へB2セルとC2セルの値から逆算で最少差となった相手の値を算出します。
=MIN(B2+C2,ABS(B2-C2))
D2はA2とB2から2値の平均を求めます。
=AVERAGE(A2:B2)
E2はC2とE2から相対誤差を算出します。
=C2/D2　（表示形式を「%」にしました）
G列の塗りつぶしは条件付き書式でA2とB2に一致するG列のセルに着色します。
数式の解説が必要なときは数式を提示して理解できない点を述べてください。

お礼

bunjiiさん
何度もありがとうございます。

上記補足で不具合を記載しましたが、＃No7さんのご回答で達成できましたので、これ以上のお手数は結構です。
さんざんお手数をおかけして申し訳ありませんが、このお手間を次回の質問に取っておいてくださればありがたい。

ありがとうございました。

補足

ご回答ありがとうございます。

早々に試してみました。
が、最終入力値が減った場合、比較値がおかしな値を示すようです。
最終入力値から最小差（何との?）を引いた値を比較値にするようです。（おかしな表現ですみません）
1.0、0.9と入力すると比較値0.8となってしまいます。

数式自体はISNUMBER、ISBLANK以外は使ったことがあるので意味は分かりますが、組み合わせられると頭が????で全体が分からなくなるレベルです。
よってたとえ完全に式が理解ができても自作はできないレベルです。

回答No.5

HohoPapa

・VBAで総当たりし、
・最小の相対誤差、その元となる値を表示し、
更に、最小の相対誤差が5%未満の時に
・該当セルの背景色（2か所）を黄色に染める
という仕様でよければ、添付のコードでいかがでしょうか？

要素（測定値？）の埋まったセルたちを選択してから
実行することを想定しています。
なお、データの埋まったシートのレイアウトを説明すれば、
別解を提示することも可能と思います。
例えば、
最下位行の測定値を選択してから実行するマクロとか
最上位行の測定値を選択してから実行するマクロとかです。

Sub Sample()

　Dim i As Long　　　　　'作業カウンター
　Dim j As Long　　　　　'作業カウンター
　Dim c As Long　　　　　'要素の位置番号
　Dim n As Long　　　　　'要素の数
　Dim y() As Double　　　'要素格納配列
　Dim RangeX As Range　　'対象セル範囲
　Dim Gosa As Double　　 '算出誤差
　Dim wkGosa As Double　 '作業用誤差
　Dim HitRow1 As Long　　'ヒットした値の位置１
　Dim HitRow2 As Long　　'ヒットした値の位置１
　Dim MsgText As String　'メッセージテキスト

　Set RangeX = Selection

　If RangeX.Columns.Count <> 1 Then Exit Sub
　n = RangeX.Rows.Count
　If n <= 1 Then Exit Sub

　ReDim y(n)
　For c = 1 To n　 '配列に要素を格納
　　If IsNumeric(RangeX.Cells(c, 1).Value) = False Then Exit Sub
　　y(c) = RangeX.Cells(c, 1).Value
　Next c

　RangeX.Interior.ColorIndex = 0　'背景色を無しに

　Gosa = 100　'適当な大ききな値をあらかじめセット
　HitRow1 = 0
　HitRow2 = 0

　For i = 1 To n - 1　'総当たりで相対誤差を算出
　　For j = i + 1 To n
　　　wkGosa = Abs(y(i) - y(j)) / ((y(i) + y(j)) / 2)
　　　If Gosa > wkGosa Then　'より小さい場合に位置を記憶
　　　　Gosa = wkGosa
　　　　HitRow1 = i
　　　　HitRow2 = j
　　　End If
　　Next j
　Next i

　MsgText = ""
　MsgText = MsgText & "相対誤差:　" & Format(Gosa, "0.00%") & vbCrLf
　MsgText = MsgText & "値１:　" & y(HitRow1) & vbCrLf
　MsgText = MsgText & "値２:　" & y(HitRow2) & vbCrLf
　MsgBox MsgText

　If Gosa < 0.05 Then　'5%未満なら色を染める
　　RangeX.Cells(HitRow1).Interior.Color = vbYellow
　　RangeX.Cells(HitRow2).Interior.Color = vbYellow
　End If

End Sub

補足

Hoho Papaさん
毎度お世話になっております。

早々に試してみました。
なんと!
信じられないことですが、一発で期待通りの結果が得られました!!

しかも2回目の試行では想定通りに前回の着色が消えて新しい結果を表示してくれます。（忖度がすごい）
さらに途中に空白セルが入っても問題なく動作しました。
なんと素晴らしい!!

これ以上望むことは贅沢だとは思うのですが、得られた相対誤差の結果を同じシートの指定のセルに入力までさせたいのですが。
指定するセルは表中で固定ですが、今回のご回答に合わせて表が変わるので任意のセルに指定できればありがたいのですが。

Msgの値をメモして、所定のセルに手入力、では2回のオペミスの可能性があるので、お手数ですが何とかお願いします。
いつものように追加の要求となってしまい申し訳ありません。

回答No.4

bunjii

>3つ目の値を入れてこの値に一番近い値を探して比率を計算して５％以下になるまで繰り返します。
「この値」とは3つ目の値のことですか？
回答No.3で提示しましたG2の36と3つ目の入力値の差は60-36=24で、G3の60と3つ目の入力値の差は68-60=8なので比率の計算は8/ABERAGE(36,68,60)≒15%で良いのですか？
また、3つ目以降の入力には予め手元に用意された数表から人為的に選んで入力するのでしょうか？（最初の2つの値も同様に人為的に選んだ値ですか？）

>何番目と何番目のデータを使った結果なのかをもとの入力表に表示させるのは当方にはハードルが高いです。
入力値の選択がどのような判断なのか分からないので数式の組み立て方が考え難いです。
もう少し詳しい条件を提示してください。

補足

何度もすみません。
No3のご回答の解釈で合っています。

実際の作業はある特性を分析をしてその結果の2つの数値で計算します。
以下の相対誤差の定義はご回答の＜2つの値の差をその2つの値の平均で割った比＞のことです。

最初に2回の測定値を入力してこの2値の差が相対誤差５％以内ならそこで分析終了。
もしこの2つの値の相対誤差が５％以上の場合、条件を変えて3回目の測定を実施してその値を入力します。
この3回目の値とそれ以前の測定値（この場合2つ）と比較して近い方の値を用いて相対誤差を求めて５％以内かどうかを判定します。
これでも５％以内にならなければ4回目の測定をして、この4回目と過去の3回の測定値の中で一番近い値を用いて相対誤差を出します。
これを繰り返して５％以下になったら測定終了。

その時の（５％以内になった時）の2つの値の測定条件を見つけたいのです。
これまでは人が見ていたのですが、出てくる値が1.23456、1.34267、1.23987、1.34987等々非常に似た数字なので一度見落とすとドツボにはまるので何とかしたく。

よって、大きさ順に並べ変えるのは有効な方法だとは思うのですが、そのあとが分かりません。
6回目の測定で過去の5回の値のどれかとうまく結果が出たとして、その値が入力されたセル（例えば2番目の測定結果と6番目の測定結果を用いて計算したら５％以内になった）を自動で見つける（当該値が入ったセルの色を変える等）までを自動化したいのです。
2つの値のうちの1つは最新の測定結果になるのですが、その上か下のセルの値の場所を見つけてやりたい。
（実際には並べ替えるといっても色んなケースが想定されるのでその「手順の自動化」は当方には難しい）

上記で質問内容がご理解いただけましたでしょうか?
何卒よろしくお願いします。

回答No.3

bunjii

>エクセルでG列に最初2つの試験結果が入力されその相対誤差（大値から小値を引いてそれを平均で割った値）が5%以上なら3つ目の結果を入力します。
「G列に最初2つの試験結果」とはG2へ1つ目の値、G3へ2つ目の値と言うことですか？
「相対誤差」とはABS(G2-G3)と言うことでしょうか？
最初にG2へ36、G3へ68と入力すると相対誤差は32ですよね？、このとき平均値は52なので比率は61.5%になりますので3つ目の試験結果をG4へ60と入力します。

>この3つの中で一番近い値の相対誤差を求めて、相対誤差が５％以下になるまで（最大10個程度）試験結果を入れていくのですがこれを満たす式（方法論）が思いつきません。
「3つの中で一番近い」と言うのは何と比較するのですか？
ABS(G2-G3)、ABS(G3-G4)、ABS(G2-G4)の中で一番小さい値のことですか？
それともABS(AVERAGE(G2:G4)-G2)、ABS(AVERAGE(G2:G4)-G3)、ABS(AVERAGE(G2:G4)-G4)の中で一番小さい値ですか？
計算する論理を正確に提示して頂ければ数式を提示できます。

作業用の数表を作成すれば数式が分散されますので考え易いと思いますが1つのセルへ長い数式を設定すれば目的通りの結果が得られると思います。

補足

早々のご回答ありがとうございます。
ご指摘の通り抜けだらけの質問内容でした。

＞「G列に最初2つの試験結果」とはG2へ1つ目の値、G3へ2つ目の値と言うことですか？
その通りです。

＞「相対誤差」とはABS(G2-G3)と言うことでしょうか？
その通りです。ただしABS(G2-G3)／Average(G2：G3)の比です。

＞比率は61.5%になりますので3つ目の試験結果をG4へ60と入力します。
その通りです。3つ目の値を入れてこの値に一番近い値を探して比率を計算して５％以下になるまで繰り返します。

「3つの中で一番近い」と言うのは何と比較するのですか？
ABS(G2-G3)、ABS(G3-G4)、ABS(G2-G4)の中で一番小さい値のことです。
入力した値の中で一番近い2値を選択してその2つの値の誤差比率が５％以下になる組み合わせを入力表から見つけたいのです。

＃No1さんのご回答を参考に、並べ替え等の作業用の列を作ってやって見たのですが、目的の５％以下の数字が出せても、何番目と何番目のデータを使った結果なのかをもとの入力表に表示させるのは当方にはハードルが高いです。

＞作業用の数表を作成すれば数式が分散されますので考え易い・・・・
作業用の数表も同じシート内への行列の挿入であればその方が当方も助かります。

何卒よろしくお願いいたします。

回答No.2

imogasi

質問の意味が、小生頭が悪いのか、硬いのか、よくわからないです。
シートのデータ例を１０数行作って、処理する方法の説明文章と、結果を載せてもらえませんか？
ーーー
>最初2つの試験結果が入力され
>G列に
2つ（２回か？）のデータを１列（Ｇ列）に入力してあるのか？
受験者の氏名はどこ？
＞平均
１人の受験生の平均か。クラスやテスト回の平均？
＞相対誤差
よく使われる意味とは違うようですが。
＝＝
ソートが有効な方法のようだが、別シートにコピーし、そこでソートなど
ＶＢＡで処理しさえすれば（何なら結果以外は消してしまえば）、素人が云々
などの心配は消えるかも。
＝＝
こういう、もし組み合わせ的な課題ならば、アルゴリズムが大切で、理数系の
人が多く見てくれる、質問カテゴリーに質問したほうが良いように思う。
ＥｘｃｅｌやＶＢＡなんて知っている人は多いが、多くは文系で、そういう理数系の課題に強い回答者は、長年質問回答を見ていて、少なそうだから。

お礼

早々のご回答ありがとございます。

＞ソートが有効な方法のようだが、別シートにコピーし、そこでソートなど
ＶＢＡで処理しさえすれば（何なら結果以外は消してしまえば）、素人が云々
などの心配は消えるかも。

全くその通りで、VBAができれば苦労はないのですが残念ながら・・・ですので質問させてもらいました。

回答No.1

neKo_quatre

> G列に入力された数値の中で一番近い2つの値の相対誤差を

なら、個々のデータの差を総当たりして最小のものを求めるより、一度並べ替えして１つ後ろのデータとの差を求めるのが良いと思います。
SMALL関数で小さい順に値を抜き出して並べ替えとか。

よねさんのWordとExcelの小部屋 » エクセル関数の技 » 関数(数式)を使ってデータを並べ替える
http://www.eurus.dti.ne.jp/~yoneyama/Excel/waza/sort.html

補足

早々のご回答ありがとうございます。

なるほどですが、この表はエクセルが使えない人を含む複数の担当者が使うので、基本的に並べ替え等の操作をさせることは避けたいのです。

また同じシートに複数の計算表があるので部分的に並べ替える、等の操作は恐らく他のエラーの可能性が出ますので、もし上記の操作させるならマクロボタンにしてクリックするだけにする必要があると思っています。（マクロのコードは全く思いつかないのですが）

数値を入れたら答えが表示されるようにしたいのです。