[[20200515213649]] 『配列を用いた参照と代入』(アイス) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『配列を用いた参照と代入』(アイス)

よろしくお願いいたします。
下記のようなid1〜3の被験者の反復測定データがあります。
測定回数順に"time"が1,2,3…と並びますが、
被験者毎に測定した回数はばらばら(id1は4回測定、id2は3回測定、id3は5回測定)です。
全被験者にどこかのタイミングで必ずC列"event"が1回のみありますが、このタイミングは被験者毎にばらばらです。
ある被験者は2回目かもしれないし、また別の被験者は初回かもしれません。
D列は各被験者の"event"が生じた行と同じ行で"event"と"id"を&(アンド)接続しています。
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、…と計算します。

 ____A____B_______C_______D_______E______F_________________
  1|id	 time	event	eventid  x	delta_x_from_event
  2| 1	 1			 2	-1
  3| 1	 2	event	event1	 3	 0
  4| 1	 3			 5	 2
  5| 1	 4			 9	 6
  6| 2	 1			 1	-1
  7| 2	 2	event	event2	 2	 0
  8| 2	 3			 4	 2
  9| 3	 1			 6	-1
 10| 3	 2			 6	-1
 11| 3	 3	event	event3	 7	 0
 12| 3	 4			 8	 1
 13| 3	 5			 8	 1

最初は空白になっているこのF列に変化量を計算して埋めることが目的です。
まず下記のコードを書いたところ、私の意図の通りに動いてくれました。
Sub test1()
Dim i As Long
Dim Rng As Range
Dim eventid As String

    For i = 2 To 13
        eventid = "event" & Cells(i, 1).Value
        Set Rng = Range(Cells(2, 4), Cells(13, 4)).Find(eventid)
        Cells(i, 6).Value = Cells(i, 5).Value - Cells(Rng.Row, 5).Value
    Next i
End Sub

しかし、実データではidの数が多いため、実行スピードが遅いです。
そこでネットで色々探すと、セルに代入する値をすべて配列に入れておいて、最後にその配列を1回だけ代入するとよい、
と書いてありました。
http://officetanaka.net/excel/vba/speed/s11.htm

そして、以下のように配列を使って代入してみました。
今回はid3まで13行目までのデータですが、実データでもデータ数は固定で動的ではありません。
配列の要素数は固定とします。
Sub test2()
Dim i As Long
Dim Rng As Range
Dim delta_x_from_event As Variant
ReDim delta_x_from_event(11)
Dim eventid As String

    For i = 2 To 13
        eventid = "event" & Cells(i, 1).Value
        Set Rng = Range(Cells(2, 4), Cells(13, 4)).Find(eventid)
        delta_x_from_event(i - 2) = Cells(i, 5).Value - Cells(Rng.Row, 5).Value
    Next i
    Range("F2:F13") = delta_x_from_event
End Sub

結果はF2:F13が全て-1の値となります。
配列"delta_x_from_event"には-1しか格納されていない状態と思いましたが
何が間違っているのでしょうか?

< 使用 Excel:Excel2013、使用 OS:Windows8 >


リンク先と同じ様に、2次元配列にしてはどうですか。

(マナ) 2020/05/15(金) 22:45


マナ様

ご回答下さりありがとうございます。
ご教授いただいたように以下のように、二次元配列とすれば、期待通りに動作しました。

Sub test2()
Dim i As Long
Dim Rng As Range
Dim delta_x_from_event As Variant
ReDim delta_x_from_event(11, 0)
Dim eventid As String

    For i = 2 To 13
        eventid = "event" & Cells(i, 1).Value
        Set Rng = Range(Cells(2, 4), Cells(13, 4)).Find(eventid)
        delta_x_from_event(i - 2, 0) = Cells(i, 5).Value - Cells(Rng.Row, 5).Value
    Next i
    Range("F2:F13") = delta_x_from_event
End Sub

しかし、ここで何故二次元配列にするのかが難しく思っています。
私の表のF列では1列ですが、あえて(11,0)と書いても配列の構造としては1次元になるのでしょうか?

(アイス) 2020/05/15(金) 23:00


https://www.moug.net/tech/exvba/0100049.html

すぐに慣れます。

(マナ) 2020/05/15(金) 23:12


マナ様

ご指導ありがとうございます!
「縦方向のセル範囲に一括代入するには、元の配列が二次元配列でなければなりません。」
とのこと承知しました。
一次元配列のデータ構造は行ではなく列が増えてゆく横長の構造であって、
それに合わせた横長のセル範囲でないと代入できないのですね。

二次元配列では、
配列名(一次元の要素最大値、二次元の要素最大値)という構造で、
一次元目は行が増える方向に、二次元目は列が増える方向に拡張すると理解しました。

ご紹介いただいたリンクの最後のようにTRASNPOSEで横長→縦長に変換しても、しっかり動作することを確認しました。

Sub test2()
Dim i As Long
Dim Rng As Range
Dim delta_x_from_event As Variant
ReDim delta_x_from_event(11)
Dim eventid As String

    For i = 2 To 13
        eventid = "event" & Cells(i, 1).Value
        Set Rng = Range(Cells(2, 4), Cells(13, 4)).Find(eventid)
        delta_x_from_event(i - 2) = Cells(i, 5).Value - Cells(Rng.Row, 5).Value
    Next i
    Range("F2:F13") = WorksheetFunction.Transpose(delta_x_from_event)
End Sub

また実データで動作確認しあまりの速さにぶったまげておりました。
大変勉強になりました!
ありがとうございます!

(アイス) 2020/05/15(金) 23:40


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.