[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『配列を用いた参照と代入；その２』（アイス）
昨日は、[[20200515213649]]のご回答をくださりありがとうございました。
追加でもうひとつ質問させてください。
昨日と同じような以下のような反復測定データです。
測定回数順に"time"が1,2,3…と並びますが、
被験者毎に測定した回数はばらばら（id1は4回測定、id2は3回測定、id3は5回測定）です。
id3の次はid10,id11と続きます。
全被験者にどこかのタイミングで必ずC列"event"が1回のみありますが、このタイミングは被験者毎にばらばらです。
ある被験者は2回目かもしれないし、また別の被験者は初回かもしれません。
また、D列で各被験者の初回の測定を"baseline"として示しています。
E列"x"は評価したいパラメータで、
F列"delta_x_from_event"はある測定時点での"event"時からの変化量です。
例えば、F2＝2-3、F3＝3-3、F4＝5-3、F5＝9-3、F6＝1-2、F7＝2-2、…と計算します。
G列"delta_x_from_baseline"はある測定時点での"baseline"時からの変化量です。
例えば、G2＝2-2、G3＝3-2、G4＝5-2、G5＝9-2、G6＝1-1、G7＝2-1、…と計算します。
____A___B________C________D____________E________________F_______________________G______________ 1| id time eventid baselineid x delta_x_from_event delta_x_from_baseline 2| 1 1 baseline1 2 -1 0 3| 1 2 event1 3 0 1 4| 1 3 5 2 3 5| 1 4 9 6 7 6| 2 1 baseline2 1 -1 0 7| 2 2 event2 2 0 1 8| 2 3 4 2 3 9| 3 1 baseline3 6 -1 0 10| 3 2 6 -1 0 11| 3 3 event3 7 0 1 12| 3 4 8 1 2 13| 3 5 8 1 2 14| 10 1 baseline10 7 -2 0 15| 10 2 event10 9 0 2 16| 10 3 4 -5 -3 17| 11 1 baseline11 7 -2 0 18| 11 2 6 -3 -1 19| 11 3 event11 9 0 2 20| 11 4 5 -4 -2
最初は空白になっているこのF2:G20に変化量を計算して埋めることが目的です。
下記のように19行1列サイズの静的二次元配列を２つ作りました。
Sub test3()
Dim i As Long, j As Long 'i,jは二次元配列のインデックス番号、iは行、jは列と定義
Dim Rng_event As Range, Rng_baseline As Range
Dim delta_x_from_event As Variant, delta_x_from_baseline As Variant
ReDim delta_x_from_event(18, 0), delta_x_from_baseline(18, 0) '二次元配列と定義 配列サイズは19行1列
Dim eventid As String, baselineid As String
For i = 0 To 18 '行要素数は19、0からスタート
eventid = "event" & Cells(i + 2, 1).Value
baselineid = "baseline" & Cells(i + 2, 1).Value
Set Rng_event = Range(Cells(2, 3), Cells(20, 3)).Find(eventid)
Set Rng_baseline = Range(Cells(2, 4), Cells(20, 4)).Find(baselineid)
delta_x_from_event(i, 0) = Cells(i + 2, 5).Value - Cells(Rng_event.Row, 5).Value
delta_x_from_baseline(i, 0) = Cells(i + 2, 5).Value - Cells(Rng_baseline.Row, 5).Value
Next i
Range(Cells(2, 6), Cells(20, 6)) = delta_x_from_event '19行1列の配列サイズと合わせたセル範囲に、配列を一括代入
Range(Cells(2, 7), Cells(20, 7)) = delta_x_from_baseline '19行1列の配列サイズと合わせたセル範囲に、配列を一括代入
End Sub
上記コードをトライすると、
____A___B________C________D____________E________________F_______________________G______________ 1| id time eventid baselineid x delta_x_from_event delta_x_from_baseline 2| 1 1 baseline1 2 -1 -5 3| 1 2 event1 3 0 -4 4| 1 3 5 2 -2 5| 1 4 9 6 2 6| 2 1 baseline2 1 -1 0 7| 2 2 event2 2 0 1 8| 2 3 4 2 3 9| 3 1 baseline3 6 -1 0 10| 3 2 6 -1 0 11| 3 3 event3 7 0 1 12| 3 4 8 1 2 13| 3 5 8 1 2 14| 10 1 baseline10 7 -2 0 15| 10 2 event10 9 0 2 16| 10 3 4 -5 -3 17| 11 1 baseline11 7 -2 0 18| 11 2 6 -3 -1 19| 11 3 event11 9 0 2 20| 11 4 5 -4 -2
上記のように、
(G2,G3,G4,G5)=(-5,-4,-2,2)となります。
本来は、
(G2,G3,G4,G5)=(0,1,3,7)となってほしいのですが、この4つのセルのみ間違った結果を出力します。
delta_x_from_baselineはid1だけ、誤ってid10のbaseline値（すなわちE14セルの値7）を参照しているようです・・・(*)
試しに、元のデータをE14＝100と書き換えて再度Sub test3()のコードをトライすると、
(G2,G3,G4,G5)=(-98,-97,-95,-91)と返されるので、
(*)は正しいと思います。
Sub test3()のコードのどこかで間違っているのでしょうか？
＜ 使用 Excel：Excel2013、使用 OS：Windows8 ＞
Sub test3()
Dim i As Long 'iは二次元配列のインデックス番号
Dim Rng_event As Range, Rng_baseline As Range
Dim delta_x_from_event As Variant, delta_x_from_baseline As Variant
ReDim delta_x_from_event(18, 0), delta_x_from_baseline(18, 0) '二次元配列と定義 配列サイズは19行1列
Dim eventid As String, baselineid As String
For i = 0 To 18 '行要素数は19、0からスタート
eventid = "event" & Cells(i + 2, 1).Value
baselineid = "baseline" & Cells(i + 2, 1).Value
Set Rng_event = Range(Cells(2, 3), Cells(20, 3)).Find(eventid)
Set Rng_baseline = Range(Cells(2, 4), Cells(20, 4)).Find(baselineid)
delta_x_from_event(i, 0) = Cells(i + 2, 5).Value - Cells(Rng_event.Row, 5).Value
delta_x_from_baseline(i, 0) = Cells(i + 2, 5).Value - Cells(Rng_baseline.Row, 5).Value
Next i
Range(Cells(2, 6), Cells(20, 6)) = delta_x_from_event '19行1列の配列サイズと合わせたセル範囲に、配列を一括代入
Range(Cells(2, 7), Cells(20, 7)) = delta_x_from_baseline '19行1列の配列サイズと合わせたセル範囲に、配列を一括代入
End Sub
上記コードですが、結局本コード内にカウンタ変数jは現れませんので、結果は同じです。
（アイス） 2020/05/16(土) 18:15
まったく、理解できていませんが
このFindは、何をしているのでしょうか。
（マナ） 2020/05/16(土) 18:26
＞Set Rng_baseline = Range(Cells(2, 4), Cells(20, 4)).Find(baselineid)
こっちのFindでは、何をしていますか。
（マナ） 2020/05/16(土) 18:29
（マナ） 2020/05/16(土) 18:40
（マナ） 2020/05/16(土) 18:45
（マナ） 2020/05/16(土) 18:49
ありがとうございます。
分かりづらく申し訳ございませんでした。
回答を書いておりましたが、私の返答が遅い間にマナ様にご解決いただきました。
＞ eventid = "event" & Cells(i + 2, 1).Value
＞ Set Rng_event = Range(Cells(2, 3), Cells(20, 3)).Find(eventid)
＞ delta_x_from_event(i, 0) = Cells(i + 2, 5).Value - Cells(Rng_event.Row, 5).Value
の趣旨は、
各被験者において反復測定データ数（行数）はばらばら、eventが生じたタイミングもばらばらですので、各被験者ごとにeventが生じた行はどこか検索して、その行番号を取得します。
変化量の計算の際に、"各被験者ごとにeventが生じた行番号"のx値を利用しています。
ステップイン（F8）確認ですが、
代入する際に配列で一気に代入するのでワークシート上では、
なぜE14セルを参照してしまうのかわかりませんでした。
しかし、マナ様にご教示いただいたFindの完全一致で、
Sub test3()
Dim i As Long 'iは二次元配列のインデックス番号
Dim Rng_event As Range, Rng_baseline As Range
Dim delta_x_from_event As Variant, delta_x_from_baseline As Variant
ReDim delta_x_from_event(18, 0) '二次元配列と定義 配列サイズは19行1列
ReDim delta_x_from_baseline(18, 0) '
Dim eventid As String, baselineid As String
For i = 0 To 18 '行要素数は19、0からスタート
eventid = "event" & Cells(i + 2, 1).Value
baselineid = "baseline" & Cells(i + 2, 1).Value
Set Rng_event = Range(Cells(2, 3), Cells(20, 3)).Find(eventid, LookAt:=xlWhole)
Set Rng_baseline = Range(Cells(2, 4), Cells(20, 4)).Find(baselineid, LookAt:=xlWhole)
delta_x_from_event(i, 0) = Cells(i + 2, 5).Value - Cells(Rng_event.Row, 5).Value
delta_x_from_baseline(i, 0) = Cells(i + 2, 5).Value - Cells(Rng_baseline.Row, 5).Value
Next i
Range(Cells(2, 6), Cells(20, 6)) = delta_x_from_event '19行1列の配列サイズと合わせたセル範囲に、配列を一括代入
Range(Cells(2, 7), Cells(20, 7)) = delta_x_from_baseline '19行1列の配列サイズと合わせたセル範囲に、配列を一括代入
End Sub
とすると、私が求めていた結果となりました。
1と10を誤認するため、Findメソッドでは注意が必要ということなのでしょうか。
本当にありがとうございます。
（アイス） 2020/05/16(土) 18:58
こんばんは！ お邪魔します。。。 解決されて良かったです。 方法は、色々あると思いますので、、一例です。 まぁ、、気が向いたら参考にしてみて下さい。 オリジナルを変更しようとしたんですけど、、わかりませんでした。。。 なので、、私のコードも良かったら。。。でいいので、、、 あっ、、検索は一回なめると結構楽になります。。。 では、、では、、、
Option Explicit
Sub てすと()
Dim MyA As Variant
Dim MyEve() As Variant
Dim MyBas() As Variant
Dim MyId As Variant
Dim i As Long
Dim n As Long
Dim k As Long
MyA = Range("A1").CurrentRegion.Resize(, 7).Value
ReDim MyEve(0)
ReDim MyBas(0)
For i = LBound(MyA, 1) + 1 To UBound(MyA, 1)
If MyA(i, 3) <> "" Then
MyEve(n) = MyA(i, 5)
n = n + 1
ReDim Preserve MyEve(n)
End If
If MyA(i, 4) <> "" Then
MyBas(k) = MyA(i, 5)
k = k + 1
ReDim Preserve MyBas(k)
End If
Next
k = 0
For i = LBound(MyA, 1) + 1 To UBound(MyA, 1)
If MyId <> MyA(i, 1) Then
MyId = MyA(i, 1)
k = k + 1
End If
MyA(i, 6) = MyA(i, 5) - MyEve(k - 1)
MyA(i, 7) = MyA(i, 5) - MyBas(k - 1)
Next
Range("M1").Resize(UBound(MyA, 1), UBound(MyA, 2)).Value = MyA
Erase MyA, MyEve, MyBas
End Sub
（SoulMan） 2020/05/16(土) 19:42
シート上の変化を観察するだけではありません。
VBE上で、コードの各変数にマウスカーソルを当てると
その時点で、変数に何が入ってるか確認できます。
あるいは、ローカルウインドウを使えるようになるとよいです。
（配列を使うなら特に）
http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub04_040_03.html
（マナ） 2020/05/16(土) 20:21
参考に
Sub Test()
Dim myCol As Collection
Dim c As Range, LastRow As Long, v As Variant, i As Long
Set myCol = New Collection
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
ReDim v(2 To LastRow, 1 To 2)
For Each c In Range("C2:D" & LastRow)
If c.Value <> "" Then myCol.Add Cells(c.Row, "E").Value, c.Value
Next
For i = 2 To LastRow
v(i, 1) = Cells(i, "E").Value - myCol("event" & Cells(i, "A").Value)
v(i, 2) = Cells(i, "E").Value - myCol("baseline" & Cells(i, "A").Value)
Next i
Range("F2:G" & LastRow).Value = v
End Sub
（ピンク） 2020/05/16(土) 22:44
ありがとうございます。
実務（エクセル使用でのデータ処理と医療統計解析）を進めながらVBA学習していますが、動的配列やコレクションオブジェクトなどご教授いただいたコードを咀嚼するのに時間がかかっています。しかし、教えていただいたこと一つ一つ学習してエッセンスを自分のものにしたいと思います。またその中でもマナ様のおっしゃるようにトラブルシューティングスキルとしてイミディエイト、ローカルウィンドウ、ウォッチリストなどデバッグ作業も出来るようになれればと思います。沢山の事教えていただき今日中に全て理解して返答することが難しいのでひとまずお礼を申し上げたく思います。
（アイス） 2020/05/16(土) 23:52
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.