Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


9636 / 76734 ←次へ | 前へ→

【72659】Re:数値範囲のデータから数値がその範囲にあるか、もしくは含むかを調べる方法
発言  kanabun  - 12/8/31(金) 16:25 -

引用なし
パスワード
   ▼T.K さん:

T.K さん、全文引用は紙面がもったいないから、やめましょう。

>▼kanabun さん:
>ありがとうございます。ここまでの説明でちょっとずつ仕組みが分かりました。
>細かい部分がまだ分からないですが、続きの解説楽しみにしています。

Dictionaryの概略が分かっていただけたら、
ではコード【72606】の説明のつづきです。


[Sheet1]
2 chr1  1048371    1049797    ER_1_Carrol
3 chr1  1054645    1055778    ER_2_Carrol
4 chr1  1285749    1286349    ER_3_Carrol
5 chr1  243097903  243098610    ER_396_Carrol
6 chr1  243121865  243122466    ER_397_Carrol
7 chr1  243604797  243605398    ER_398_Carrol
8 chr2  250722     251323    ER_399_Carrol
9 chr2  1650212    1650812    ER_400_Carrol
10 chr2  1660654    1661254    ER_401_Carrol
11 chr3  58431890   58432547    ER_756_Carrol
12 chr3  61588283   61588993    ER_757_Carrol
13 chr3  61595330   61595989    ER_758_Carrol
14 chr3  61605662   61606263    ER_759_Carrol
15 chr3  61649269   61649870    ER_760_Carrol
16 chr3  61768126   61769266    ER_761_Carrol
17 chr3  62137944   62138544    ER_762_Carrol

まず
>  v = Intersect(r, r.Offset(1)).Value
を実行することにより、
[Sheet1]の2行目から17行目までが配列として変数vに代入されます。

つぎに
'------------------------------------------------------- (code)
>   For i = 1 To UBound(v)
>     If Not dic.Exists(v(i, 1)) Then
>       Set dic(v(i, 1)) = _
>         CreateObject("Scripting.Dictionary")
>     End If
>     dic(v(i, 1))(v(i, 4)) = Array(v(i, 2), v(i, 3))
>   Next
'-----------------------------------------------------------
でやっていることは、昨日ちょっと触れたように、
日本語にすると、
'----------------------------------------------------- (日本語)
 If 「dic にA列のclass名が Key登録されていなかったら」
  → dic のなかに子供の辞書を作成する
 End If
 dic(A列のclass名)のなかの 子供辞書の(D列ID)のKeyラベル
 をもつ容器に 配列(B列の数値, C列の数値) を容れる、
'-----------------------------------------------------------
というようなことの繰り返しです。

 一番最初のデータ i = 1(表の2行目のデータ)
> 2 chr1  1048371    1049797    ER_1_Carrol
に即していえば、
 上のコードは、
'----------------------------------------------------- (code)
For i = 1 のとき
>  If Not dic.Exists("chr1") Then
>    Set dic("chr1") = CreateObject("Scripting.Dictionary")
>  End If
>  dic("chr1")("ER_1_Carrol") = Array(1048371, 1049797)
'-----------------------------------------------------------
をやっているということです。

For i = 1 を実行した後
    ↓親dic
    ┏━━━━━━━━━━━━━━━━━━━━━━━
    ┃      ↓子のDictionary
 [chr1]┃       ┌─────────────
    ┃[ER_1_Carrol]│{1048371, 1049797}       
    ┃       └─────────────
    ┃      
    ┗━━━━━━━━━━━━━━━━━━━━━━━

という構造になります。(今度は図を横にしました)

つぎに、For i = 2 を実行した後は
    ↓親dic
    ┏━━━━━━━━━━━━━━━━━━━━━━━
    ┃       ┌─────────────
    ┃[ER_1_Carrol]│{1048371, 1049797}       
    ┃       └─────────────
 [chr1]┃       ┌─────────────
    ┃[ER_2_Carrol]│{1054645, 1055778}    
    ┃       └─────────────
    ┃      
    ┗━━━━━━━━━━━━━━━━━━━━━━━
となります。

以下同様にして、17行目(i = 16) まで繰り返したあとは dicは
こういう構造になります。(↓図は 最後のほうだけ示します)

 [chr3]┃         └─────────────
    ┃         ┌─────────────
    ┃[ER_760_Carrol] │{61649269, 61649870}    
    ┃         └─────────────
    ┃         ┌─────────────
    ┃[ER_761_Carrol] │{61768126, 61769266}    
    ┃         └─────────────
    ┃         ┌─────────────
    ┃[ER_762_Carrol] │{62137944, 62138544}    
    ┃         └─────────────
    ┗━━━━━━━━━━━━━━━━━━━━━━━

dic内には 3つのKey ("chr1","chr2","chr3") があり、
各Keyは それぞれ別の(独自の)子Dictionary をもつ --- このような
構成になります。

以上で、あたりチェックのSheet1側の範囲表データがDictionary
に登録されました。
これで、たとえば、"chr3" class の ID "ER_760_Carrol" の範囲は
  dic("chr3")("ER_760_Carrol")
に配列として格納されていますから、
  dic("chr3")("ER_760_Carrol")(0) とすれば start値が、
  dic("chr3")("ER_760_Carrol")(1) とすれば end値を得ること
ができます。


さて、
>   Dim a1, a2
>   Dim b1, b2
>   Dim vv
以降でやっていることは
>   Set r = Worksheets("Sheet2").Cells(1).CurrentRegion
>   With Intersect(r, r.Offset(1))
>     v = .Resize(, 3).Value
>     vv = .Columns(4).Cells.Value
>   End With

で、

[Sheet2]
class start     end     ID
chr1  61649566   61649584
chr2  61649566   61649584
chr3  61649566   61649584

の1行目を除いたのち、
A,B,Cの3列を 変数v に格納し、
さらに4列目のID用に(現在空白セルの)4列目だけを 変数vv に入れ
たあと、
>  For i = 1 To UBound(v)  'Sheet2 2行目から
>    vv(i, 1) = Empty
    たとえば i = 3 のとき、
>     chr3   61649566   61649584
     v(i,1)  v(i,2)     v(i,3)
     という変数とデータの関係になってますから、
     ↓辞書dicに "chr3" というKeyが存在したら
>     If dic.Exists(v(i, 1)) Then
      ↑は「真」となり、以下を実行します。
      まず、
      a1 に 2列目のstart値 61649566を、
      a2 に 3列目のend値 61649584 を覚えておき↓、
>       a1 = v(i, 2)
>       a2 = v(i, 3)
     dicのほうから class が "chr3" の子辞書内の
     すべてのIDの start値と end値をb1,b2 として
     a1,a2範囲との当たり判定をチェックしていきます。
>       For Each ID In dic(v(i, 1)).Keys()
>         b1 = dic(v(i, 1))(ID)(0)
>         b2 = dic(v(i, 1))(ID)(1)
ここのところを iが3 の時の実数例で示せば、
>       For Each ID In dic("chr3").Keys()
           ▼
       最初のIDは "ER_756_Carrol"ですから、
>         b1 = dic("chr3")("ER_756_Carrol")(0)
>         b2 = dic("chr3")("ER_756_Carrol")(1)

  b1 = 58431890
  b2 = 58432547
になります。

これと
 a1 = 61649566
 a2 = 61649584
とで重なる部分の判定をします。
判定方法は前々回まとめたように
---------------------------------------- 判定の手順
> b1とa1をくらべて、
>  ●b1 のほうが小さいときは、a1がb2より小さければ「あたり」
>   (言い換えると「b1 <= a1 <b2 の関係にある」とき→あたり)
>
>     a1
>  b1───────b2
>
>  ●a1 のほうが小さいときは、b1がa2より小さければ「あたり」
>   (言い換えると「a1 < b1 <a2 の関係にあるとき」→あたり)
>
>  a1━━━━━━━a2
>     b1
------------------------------------------------------
という考えを用います。
最初のID ="ER_756_Carrol" のばあい、
> b1とa1をくらべて、
とは、b1 = 58431890 と a1 = 61649566 を比べることになります。
この結果、b1のほうが小さいので、次に a1と b2を比べますが、
b2のほうがa1より小さいので、このIDは「ハズレ」となります。
 :
 :
こんな調子で
>       For Each ID In dic("chr3").Keys()
            ▼
に従って、IDをループしていくと、
▼"chr3" class 内で、ID = "ER_760_Carrol" のときは、
 b1 = 61649269
 b2 = 61649870
で、このときは
 「 b1 <= a1 で、さらに、a1 < b2 の関係が成立」 します。
 なので、このときのIDの値を vv(i,1)に代入して、
 "chr3" 内のIDのLoop を中断(Exit For)します。


こうして、
> [Sheet2]
> chr1  61649566   61649584
> chr2  61649566   61649584
> chr3  61649566   61649584
のすべての行について、
各行の start値,end値(a1,a2) が
[Sheet1]の同名のclass内のID範囲(b1,b2)と比べて、
重なるIDがあればその値を vv(i) に入れ、
どれとも重ならなければ vv(i)には「ハズレ」を記入します。

最後に メモリ内のvv配列をシートの(2行目,4列目)以降に貼り
付けます.
    ↓範囲r内の 2行目4列目のセルのこと
>   r.Item(2, 4).Resize(UBound(vv)).Value = vv
             ↑vvの最大要素数で 3 が返る


だいたい、こういう流れです。
4 hits

【72485】数値範囲のデータから数値がその範囲にあるか、もしくは含むかを調べる方法 T.K 12/8/15(水) 20:24 質問
【72486】Re:数値範囲のデータから数値がその範囲に... UO3 12/8/15(水) 22:09 発言
【72489】Re:数値範囲のデータから数値がその範囲に... kanabun 12/8/15(水) 22:38 発言
【72491】Re:数値範囲のデータから数値がその範囲に... UO3 12/8/15(水) 22:48 発言
【72492】Re:数値範囲のデータから数値がその範囲に... kanabun 12/8/16(木) 0:37 発言
【72493】Re:数値範囲のデータから数値がその範囲に... kanabun 12/8/16(木) 0:51 発言
【72494】Re:数値範囲のデータから数値がその範囲に... UO3 12/8/16(木) 8:16 発言
【72495】Re:数値範囲のデータから数値がその範囲に... kanabun 12/8/16(木) 9:03 発言
【72500】Re:数値範囲のデータから数値がその範囲に... UO3 12/8/16(木) 12:54 発言
【72505】Re:数値範囲のデータから数値がその範囲に... UO3 12/8/17(金) 21:25 発言
【72596】Re:数値範囲のデータから数値がその範囲に... T.K 12/8/27(月) 21:46 質問
【72598】Re:数値範囲のデータから数値がその範囲に... T.K 12/8/27(月) 23:55 発言
【72602】Re:数値範囲のデータから数値がその範囲に... kanabun 12/8/28(火) 9:51 質問
【72603】Re:数値範囲のデータから数値がその範囲に... T.K 12/8/28(火) 10:07 発言
【72605】Re:数値範囲のデータから数値がその範囲に... T.K 12/8/28(火) 10:12 発言
【72606】Re:数値範囲のデータから数値がその範囲に... kanabun 12/8/28(火) 10:38 発言
【72607】Re:数値範囲のデータから数値がその範囲に... T.K 12/8/28(火) 11:27 お礼
【72608】Re:数値範囲のデータから数値がその範囲に... T.K 12/8/28(火) 11:56 お礼
【72630】Re:数値範囲のデータから数値がその範囲に... kanabun 12/8/29(水) 19:29 発言
【72631】Re:数値範囲のデータから数値がその範囲に... kanabun 12/8/29(水) 19:43 発言
【72612】Re:数値範囲のデータから数値がその範囲に... T.K 12/8/28(火) 15:49 質問
【72613】Re:数値範囲のデータから数値がその範囲に... T.K 12/8/28(火) 18:37 質問
【72628】Re:数値範囲のデータから数値がその範囲に... kanabun 12/8/29(水) 18:53 発言
【72629】Re:数値範囲のデータから数値がその範囲に... kanabun 12/8/29(水) 19:12 発言
【72632】Re:数値範囲のデータから数値がその範囲に... kanabun 12/8/29(水) 20:17 発言
【72634】Re:数値範囲のデータから数値がその範囲に... kanabun 12/8/29(水) 21:35 発言
【72635】Re:数値範囲のデータから数値がその範囲に... kanabun 12/8/29(水) 21:40 発言
【72657】Re:数値範囲のデータから数値がその範囲に... T.K 12/8/31(金) 15:49 発言
【72659】Re:数値範囲のデータから数値がその範囲に... kanabun 12/8/31(金) 16:25 発言
【72660】Re:数値範囲のデータから数値がその範囲に... T.K 12/8/31(金) 17:30 質問
【72664】Re:数値範囲のデータから数値がその範囲に... kanabun 12/8/31(金) 19:35 発言
【72666】Re:数値範囲のデータから数値がその範囲に... T.K 12/8/31(金) 19:45 お礼

9636 / 76734 ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free