[[20200520005138]]　『残りの項目を上の結果を元に乱数で』（豆豚）　ページの最後に飛ぶ

『残りの項目を上の結果を元に乱数で』（豆豚）

     A       B       C       D       E       F       G       H
1
2            項目1    項目2   項目3   項目4   項目5   項目6   項目7
3  ｻﾝﾌﾟﾙ1    2.382    5.052  -10.343  12.011  3.554   -3.000  0.444
4  ｻﾝﾌﾟﾙ2    2.370    5.031  -10.422  12.139  3.522   -3.032  0.429
5  ｻﾝﾌﾟﾙ3    2.413    5.100  -10.357  11.987  3.501   -2.982  0.363
6  ｻﾝﾌﾟﾙ4    2.398    4.982  -10.421  11.898  3.498   -2.952  0.392
7  ｻﾝﾌﾟﾙ5    2.401    5.025  -10.341  12.101  3.524   -2.925  0.421
8  ｻﾝﾌﾟﾙ6    2.378    4.973  -10.346  12.074  3.552   -3.012  0.414
9  ｻﾝﾌﾟﾙ7    2.402    4.938  -10.432  11.984  3.521   -3.014  0.412
10 ｻﾝﾌﾟﾙ8    2.399    5.021  -10.369  11.964  3.532   -3.042  0.432
11 ｻﾝﾌﾟﾙ9    2.413    4.980  -10.424  11.935  3.523   -3.021  0.453
12 ｻﾝﾌﾟﾙ10   2.383    4.984  -19.413  11.940  3.521   -2.934  0.421
13 ｻﾝﾌﾟﾙ11
14 ｻﾝﾌﾟﾙ12
15 ｻﾝﾌﾟﾙ13
16 ｻﾝﾌﾟﾙ14
17 ｻﾝﾌﾟﾙ15

こんにちはです。

業務でｻﾝﾌﾟﾙの評価が終わるとデータが出てそれを入力しているのですが
日によっては上のように12個分だったり10個だったり11個分だったりしか出てこなくて
残りのｻﾝﾌﾟﾙ15までは上のデータの傾向を見ながらキーボードで手入力で数値をバラしたり、エクセルの乱数を使ったり人それぞれのやり方でデータを埋めている状態です。

・評価ｻﾝﾌﾟﾙがどこからか空白になってしまった時点で
それまでの項目のデータのMAXとMINを参考にその中で乱数を使ってｻﾝﾌﾟﾙ15まで自動で埋めてくれる

という夢の様なコードは可能でしょうか。

すみませんが出来たら教えて頂きたいです。
エクセルのRandbetweenとRoundで何とかやりくりしていたのですが
効率がどうにも悪くVBAで出来ないかとご相談させて頂きました。
よろしくお願いします。

＜使用 Excel：Excel2013、使用 OS：Windows10 ＞

データの作成的な感じがして、そこは気になりますが、それはさておき。
　
その統計値がどのような事象に関するものなのかによりますね。
データがどんな分布に従っているかに応じて、採用する乱数も変わってきます。
　
例えば、正規分布に従うという前提であれば、
1．既存データに基づき、期待値と標準偏差を求めます。
2．それをもとに、NORMINV関数に一様乱数と期待値と標準偏差を与えて、正規乱数を発生させる
というのが普通の考え方だと思います。
　
なお、上記例の項目3の最後のデータはいかにもoutlierという気がするので、
そうした場合にそれを除外するか否かは一考の余地があるかもしれません。
（まあ、それも質問にあたっての架空のデータでしょうから気にする必要はないとは思いますが。）

コードは、夜分でもあるので割愛して、昼間、あなたのほうで検討してください。
　
（γ） 2020/05/20(水) 01:55

ちなみに、正規性の検定が必要かもしれないし、項目間の相関を考えて相関を持つ
正規乱数(*)を作るといったことが本来必要かもしれない。
ただし、そこまで求められていないようだし、そもそも、本来測定すべき数値を造って
よいのかという気がする。
原因の解明をして必要なデータが出力されるように力を入れるのが筋ではないかと思う。
　
(*)必要なら、"相関を持つ正規乱数の作成"などでネット検索すれば出てくると思われる。
　　対称行列のコレスキー分解の話になる。これをExcelで計算するのは面倒に過ぎる。

（γ） 2020/05/20(水) 05:42

（γ）様　詳しいご説明ありがとうございます。勉強になります。

項目3の最後については誤記になります。すみません。
本来ならデータが出力されるべきなのですが会社の古い習慣になっており
まぁ品質に関わるというものでもないのでルーティン化されております。

現在　乱数のついてはエクセルの関数式で他のシートで発生させてコピーして残りの空白に張り付ける。
という作業をしています。

他シートで
B2　　項目1のMAX値
B3　　項目1のMAX値
C2　　=(B2+B3)/2
D2　　=C2*1000
E2 =B2-B3
F2 =E2/2
G2 =F2*1000
H2 =(RANDBETWEEN(-(G2),(G2))+D2)　←ここからH3～H15まで同じ数式を入れる
I2 =H2/1000　　　　　　　　　　　 ←ここは相対参照で左から反映
J2 =ROUND(I2,3) 　　　　　　　　←相対参照で左から反映

このように乱数を発生させており
このJの列の値をコピーしてデータの空白に貼り付ける

という作業を何とかVBAでできないかなと考えておりました。
自分たちではコンマ3桁バラし　と呼んでおります。

なるべく狭い範囲でバラけるように桁を調整して作った記憶はあります。
これの再現は可能でしょうか。　　　　　　
（豆豚） 2020/05/20(水) 19:51

 こんばんは！
あまり難しいことはわかりませんが、、、ちょっと書いてみました。。。
これ以上のことを求められましてもお答えできない可能性が大です。。。(^^;
では、、では、、

 	項目1	項目2	項目3	項目4	項目5	項目6	項目7
ｻﾝﾌﾟﾙ1	2.382	5.052	-10.343	12.011	3.554	-3	0.444
ｻﾝﾌﾟﾙ2	2.37	5.031	-10.422	12.139	3.522	-3.032	0.429
ｻﾝﾌﾟﾙ3	2.413	5.1	-10.357	11.987	3.501	-2.982	0.363
ｻﾝﾌﾟﾙ4	2.398	4.982	-10.421	11.898	3.498	-2.952	0.392
ｻﾝﾌﾟﾙ5	2.401	5.025	-10.341	12.101	3.524	-2.925	0.421
ｻﾝﾌﾟﾙ6	2.378	4.973	-10.346	12.074	3.552	-3.012	0.414
ｻﾝﾌﾟﾙ7	2.402	4.938	-10.432	11.984	3.521	-3.014	0.412
ｻﾝﾌﾟﾙ8	2.399	5.021	-10.369	11.964	3.532	-3.042	0.432
ｻﾝﾌﾟﾙ9	2.413	4.98	-10.424	11.935	3.523	-3.021	0.453
ｻﾝﾌﾟﾙ10	2.383	4.984	-19.413	11.94	3.521	-2.934	0.421
ｻﾝﾌﾟﾙ11	2.413	4.994	-15.233	12.044	3.523	-3.003	0.39
ｻﾝﾌﾟﾙ12	2.378	5.079	-12.192	12.097	3.546	-2.965	0.431
ｻﾝﾌﾟﾙ13	2.396	4.987	-14.651	12.068	3.517	-2.932	0.422
ｻﾝﾌﾟﾙ14	2.407	4.983	-13.936	11.912	3.508	-3.001	0.383
ｻﾝﾌﾟﾙ15	2.396	5.07	-18.491	11.898	3.504	-3.028	0.386

 Option Explicit
Sub てすと()
Dim MyA As Variant
Dim MyMax As Double
Dim MyMin As Double
Dim i As Long
Dim j As Long
'A2を起点としたデータをMyAに取得
'MyA = Range("A2").CurrentRegion.Resize(, 5).Value
'なんてすると5列のデータに制限することが出来ます。。。
MyA = Range("A2").CurrentRegion.Value
'MyAの二次元上限までループ　項目があったので下限に＋１してます。。
For j = LBound(MyA, 2) + 1 To UBound(MyA, 2)
    '各項目ごとに最大値を取得
    MyMax = Application.Max(Application.Index(MyA, 0, j))
    '各項目ごとに最小値を取得
    MyMin = Application.Min(Application.Index(MyA, 0, j))
    'MyAの一次元方向に上限までループ　見出しがあったので下限に＋１してます。
    For i = LBound(MyA, 1) + 1 To UBound(MyA, 1)
        '空白だったら
        If IsEmpty(MyA(i, j)) Then
            '乱数を初期化
            Randomize
            '乱数×(最大値-最小値)＋最小値　公式です。。。
            MyA(i, j) = Round(Rnd * (MyMax - MyMin) + MyMin, 3)
        End If
    Next
Next
'結果を出力します。
Range("K2").Resize(UBound(MyA, 1), UBound(MyA, 2)).Value = MyA
'変数を開放します。。。
Erase MyA
End Sub
（SoulMan） 2020/05/20(水) 19:54

（SoulMan）様　　早速のご解答ありがとうございます！！

今スマホで閲覧しており早く帰宅して試したくてたまらないです。

それと図々しすぎて申し訳ないのですがド無知でして
今後もし
・セルの列や行の位置
・サンプル数や、項目数
変わった時に自分でも弄れるように'で簡単な解説を頂けないでしょうか。

いやいやそれぐらい自分で試せよって話ですがこれだけでもありがたすぎます。

（豆豚） 2020/05/20(水) 20:16

 こんばんは！
久しぶりですねぇ。。。解説を求められたのは(笑)
本当は乱数に↓これを使って欲しいんですけど、、、たいそうなんで今回はRndにしました。。
http://www001.upp.so-net.ne.jp/isaku/mt.html
気が向いたらお勉強してください。。。
あっ、今回のコードはちょうぉ～～手抜きコードなんで、、、あしからず。。。m(__)m
では、、では、、また。。。
（SoulMan） 2020/05/20(水) 20:36

ありがとうございます。
今やってみましたがK2を起点に開放させるって事ですかね。
これを色々弄って頑張ってみます!
（豆豚） 2020/05/20(水) 21:28

 >今やってみましたがK2を起点に開放させるって事ですかね。 
うぅぅぅぅ、、ちょっと違う(^^;
K2を起点に出力。。。。
まぁ、、どっちでもいいんですけどね(^^;
がんばってください。。。
（SoulMan） 2020/05/20(水) 21:41

     |  [A]  | [B] | [C] |  [D]  | [E]  | [F] | [G]  | [H]  
 [1] |       |     |     |       |      |     |      |     
 [2] |       |項目1|項目2|項目3  |項目4 |項目5|項目6 |項目7
 [3] |ｻﾝﾌﾟﾙ1 |2.382|5.052|-10.343|12.011|3.554|    -3|0.444
 [4] |ｻﾝﾌﾟﾙ2 | 2.37|5.031|-10.422|12.139|3.522|-3.032|0.429
 [5] |ｻﾝﾌﾟﾙ3 |2.413|  5.1|-10.357|11.987|3.501|-2.982|0.363
 [6] |ｻﾝﾌﾟﾙ4 |2.398|4.982|-10.421|11.898|3.498|-2.952|0.392
 [7] |ｻﾝﾌﾟﾙ5 |2.401|5.025|-10.341|12.101|3.524|-2.925|0.421
 [8] |ｻﾝﾌﾟﾙ6 |2.378|4.973|-10.346|12.074|3.552|-3.012|0.414
 [9] |ｻﾝﾌﾟﾙ7 |2.402|4.938|-10.432|11.984|3.521|-3.014|0.412
 [10]|ｻﾝﾌﾟﾙ8 |2.399|5.021|-10.369|11.964|3.532|-3.042|0.432
 [11]|ｻﾝﾌﾟﾙ9 |2.413| 4.98|-10.424|11.935|3.523|-3.021|0.453
 [12]|ｻﾝﾌﾟﾙ10|2.383|4.984|-19.413| 11.94|3.521|-2.934|0.421
 [13]|ｻﾝﾌﾟﾙ11|     |     |       |      |     |      |     
 [14]|ｻﾝﾌﾟﾙ12|     |     |       |      |     |      |     
 [15]|ｻﾝﾌﾟﾙ13|     |     |       |      |     |      |     
 [16]|ｻﾝﾌﾟﾙ14|     |     |       |      |     |      |     
 [17]|ｻﾝﾌﾟﾙ15|     |     |       |      |     |      |

 >それまでの項目のデータのMAXとMINを参考にその中で乱数を使ってｻﾝﾌﾟﾙ15まで自動で埋めてくれる

 そのまんまですが

 Sub Twst()
    Dim maxVal As Long, minVal As Long
    Dim 列 As Long, 行 As Long
    Dim LastRow1 As Long, LastRow2 As Long

    With Application.WorksheetFunction
        LastRow1 = Range("A3").End(xlDown).Row
        For 列 = 2 To Cells(2, 2).End(xlToRight).Column
            LastRow2 = Cells(2, 列).End(xlDown).Row
            minVal = .Min(Range(Cells(3, 列), Cells(LastRow2, 列))) * 1000
            maxVal = .Max(Range(Cells(3, 列), Cells(LastRow2, 列))) * 1000
            For 行 = LastRow2 + 1 To LastRow1
                Cells(行, 列).Value = .RandBetween(minVal, maxVal) / 1000
            Next 行
        Next 列
    End With
 End Sub

（ピンク） 2020/05/20(水) 22:42

コメント返信：

[ 一覧(最新更新順) ]

[[20200520005138]] 『残りの項目を上の結果を元に乱数で』（豆豚） ページの最後に飛ぶ

[[20200520005138]]　『残りの項目を上の結果を元に乱数で』（豆豚）　ページの最後に飛ぶ