[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『Transposeについて』（稲葉）
以前[[20090622145857]]でお世話になりました。 あれから理解するためにいろいろ読み漁って http://www.clayhouse.jp/array/array03_d.htm こちらのページで「列は1列でも2次元配列になる」ことを学び、 「1次元配列でしか使えない関数で使用するためにTransposeを使い、1次元に直す」 ところまで理解できました。
appleの文字をJoinを使って一つのセルにすることもできたのですが最後の文章の 『また、INDEXを利用して、複数行×複数列の2次元配列から、指定した1行だけを 1次元配列として取り出すことも可能です。同様に、1列だけを取り出して、TAN SPOSEでやはり1次元配列にすることができます。』 でまたよくわからなくなってしました。
そこでA1:E5まで下記のように並んでいる文字列を配列Aに取り込み、 A N G E L P I R A E P G E R V L H A T E E T T H L
下記のような回答を出すにはどうすればよいのかを聞きたく参りました。
ANGEL APPLE NIGHT LEVEL
まだわからないのかこの馬鹿はと思いつつつきあっていただけたらと存じます。
よろしくお願いいたします。
> INDEXを利用して、 こんなことですか？
Dim r As Range
Set r = [A1:E5]
Range("G1").Value = Join(Application.Index(r.Rows(1).Value, 0), "")
Range("G2").Value = Join(Application.Transpose(r.Columns(1)), "")
Range("G3").Value = Join(Application.Transpose(r.Columns(2)), "")
Range("G4").Value = Join(Application.Transpose(r.Columns(5)), "")
(kanabun)
思ったとおりの結果になりました。 それで、自分なりの解釈なんですが
＞Set r = [A1:E5]
＞Range("G1").Value = Join(Application.Index(r.Rows(1).Value, 0), "")
r(1,1)=A
r(1,2)=N
r(1,3)=G
r(1,4)=E
r(1,5)=L
だから、Rows(1)はr(1 のことで、0はそのままだからjoinで上の通り結合されると...
そうすると本来2次配列なのにJoinが使えるのはおかしいような…？
それで
＞Range("G2").Value = Join(Application.Transpose(r.Columns(1)), "")
これは
r(1,1)=A
r(2,1)=P
r(3,1)=P
r(4,1)=L
r(5,1)=E
で1次配列にするためにTransposeすることで
r(1,1)=A
r(1,2)=P
r(1,3)=P
r(1,4)=L
r(1,5)=E
になるってことですか？？
r(行数,列数)?
またわからなくなってきちゃいました…
自分が何が分からないのかわからなくてひどくもどかしいです…。
何が分からないのかわからないので聞きようがないのですが、何か思い当たる節があったらあてずっぽうでもいいので教えていただけるとたすかります…
（稲葉）
そこでA1:E5まで下記のように並んでいる文字列を配列Aに取り込み... ですよね？
myArray = [a1:e5]
Join関数は1次元配列でしか機能しません。 ですので、２次元配列 myArray の一行・列を取り出して１次元配列になるようにしてやる必要があります。
Index関数を使用して任意の行を取り出すと
x = Application.Index(myArray, 1, 0)
この取り出された配列 x が１次元配列か２次元配列かでその後の処理（Tranpose)が変わってきます。
同様に任意の列
y = Application.Index(myArray, 0, 1)
y が１次元配列か２次元配列であるかで後の処理が変わります。
Step debug して確認してください。 (seiya)
> だから、Rows(1)はr(1 のことで、0はそのままだからjoinで上の通り結合されると... > そうすると本来2次配列なのにJoinが使えるのはおかしいような…？
おかしいですか？
r.Item(1,1)="A" … r はセル範囲です。 .Valueプロパティは省略しています。 （以下同様） r.Item(1,2)="N" r.Item(1,3)="G" r.Item(1,4)="E" r.Item(1,5)="L" が a(1)=A … a は配列です a(2)=N a(3)=G a(4)=E a(5)=L という一次元配列に格納される、ということなんです。
1列データを TRANSPOSEしたばあいも同様です。一次元配列に格納されるということです。 どうしてってことではなく、Excelの仕様でしょうから。。。
(kanabun)
あと、
Dim r As Range
Set r = [a1:e5]
Range("G1").Value = Join(Application.Index(r.Rows(1).Value, 0), "") ' …(1)
Range("H1").Value = Join(Application.Index(r.Value, 1, 0), "") ' …(2)
では、
(1)のほうが 処理が速いですよ。
少し大きな範囲で 繰り返して 検証してみてください。(^^
(kanabun)
VBEでローカルウィンドウは表示させていますか？ 表示させていないなら、表示させてみて下さい。
Sub test()
Dim r, x1, x2, y1, y2, z1, z2
Set r = [A1:E5]
x1 = r.Rows(1).Value
x2 = Application.Index(x1, 0)
y1 = r.Columns(1).Value
y2 = Application.Transpose(y1)
z1 = r.Value
z2 = Application.Transpose(z1)
Stop
Set r = Nothing
End Sub
このコードを実行すると「Stop」の行で 一度マクロが止まります。
ローカルウィンドウを見ると [＋] x1 [＋] x2 [＋] y1 [＋] y2 と成っていますから、[+]をクリックして開いてみて下さい。 （さらに[+]が有るものは、それも開いて下さい。）
x1,y1は２次元配列。 それを、INDEXやTORANSPOSEさせた x2,y2は １次元配列に成っているのが確認出来ると思います。
z1,z2は前も後も、２次元配列のままですね。 TRANSPOSEが、y2の時に１次元にしてしまうのは そう言う物だと思っておくようなお話だと思います。
(HANA)
seiya様 以前にも指摘されたStep debug ですがようやく意味が理解できました！ xのほうは、 x(1) x(2) x(3) x(4) x(5) となっていましたが、ｙのほうは y(1,1) y(2,1) y(3,1) y(4,1) y(5,1) となっていました。 そこで、yをtransposeしたところ、y(1)・・・ となりました。
先ほどに加え、下記のようにしたところ、視覚的に理解できたのでようやくもやもやが取れそうです。 myArray = [a1:e5] x = Application.Index(myArray, 1, 0) xx = Application.Index(myArray, 2, 0) y = Application.Transpose(Application.Index(myArray, 0, 1)) yy = Application.Transpose(Application.Index(myArray, 0, 2))
y
yy
↓ ↓
x⇒ A N G E L
xx⇒ P I R A E
P G E R V
L H A T E
E T T H L
Transpose(y)
y⇒ A P P L E
yy⇒ N I G H T
G R E A T
E A R T H
L E V E L
ってことですよね？
なんとなくわかったきがします！ ありがとうございました。
kanabun様 列は2次配列になるけど、INDEXで列を指定するとその列r(1,x)が取り出され、 それをさらにtranposeするとr(x,1)=r(x)となるってことですよね？
理屈については「仕様だから」と割り切ることにします…。
＞Range("G1").Value = Join(Application.Index(r.Rows(1).Value, 0), "") ' …(1)
＞Range("H1").Value = Join(Application.Index(r.Value, 1, 0), "") ' …(2)
ですが、5000行*100でやったのですが…よく考えたら5000は意味ないですね... 100だと違いを感じることができませんでしたorz
意味的にはどういう違いがあるのですか？
HANA様 書いている途中で衝突してしまいました。 とりあえず投稿させていただいて、のちにお返事申し上げます。 ありがとうございます。 （稲葉）
> 100だと違いを感じることができませんでしたorz
250行×250列 のマトリックスデータで実験してみました。
いちおう【Sheet3】を使用することにします。 まず、データを作成します。 Sub 準備() Dim v(1 To 250, 1 To 250) Dim i&, j&
For i = 1 To 250
For j = 1 To 250
v(i, j) = Int(250 * Rnd + 1)
Next
Next
Worksheets("Sheet3").Range("A1").Resize(250, 250).Value = v
End Sub
'---- ここから 実験です。 Sub TestA() Dim ss$ Dim i& Dim r As Range Dim t!
t = Timer
Set r = Worksheets("Sheet3").Range("A1").Resize(250, 250)
For i = 1 To 250
'ss = Join(Application.Index(r, i)) '★------ 不可
ss = Join(Application.Index(r.Value, i))
Next
Debug.Print "'A "; Timer - t; """"
Beep
End Sub
Sub TestB() Dim ss$ Dim i& Dim r As Range Dim t!
t = Timer
Set r = Worksheets("Sheet3").Range("A1").Resize(250, 250)
For i = 1 To 250
'ss = Join(Application.Index(r, i)) '★------ 不可
ss = Join(Application.Index(r.Value2, i))
Next
Debug.Print "'B "; Timer - t; """"
Beep
End Sub
Sub TestC() Dim ss$ Dim i& Dim r As Range Dim t!
t = Timer
Set r = Worksheets("Sheet3").Range("A1").Resize(250, 250)
For i = 1 To 250
ss = Join(Application.Index(r.Rows(i).Value2, 0#))
Next
Debug.Print "'C "; Timer - t; """"
Beep
End Sub
こちらでの 結果はこのようになりました。
'A 5.835938 " 'B 4.695313 " 'C 0.078125 "
(kanabun)
ついでに（参考まで）...
行だったら、こんなことも
Range("g1").Value = Join([a1:e1&""],"")
(seiya)
HANA様 ローカル開いてやってみました。
+押して展開していくとよくわかりますね。 この 2重に展開できるものと1回展開すればすべて見られるものの違いは列か行かの違いですかね？ zがたくさんあったのは、列と行両方表示されるからですよね？ あとはこれを何に使えばいいのかとかを勉強していかなくちゃですね... ありがとうございました！
kanabun様 こんなんでました！ 'A 10.09375 " 'B 6.0625 " 'C 0.09375 " Aが単純な値で Bがdateとかを含まない数字？で Cが列を指定した場合ってことであってますかね？
それと、関係ないところなんですが、変数tのt!←これなんの意味があるのでしょうか；
seiya様 直接配列読み込んでもよいわけですね... それがa1:a5になるとできなくなるからtransposeが必要になると…
このe1&""←このスペースはどのような意味があるのですか？？
（稲葉）
> このe1&""←このスペースはどのような意味があるのですか？？ なぜそれで一次元配列になるのかはわかりません... (seiya)
あ、それを入れることで一次配列になるのですね。 エクセルの仕様って不思議ですね… ありがとうございました。
（稲葉）
＞あとはこれを何に使えばいいのか 今回の様に、確認するときに使えば良いと思います。
testのコードは途中でStopを入れて 強制的にそこで止まるようなコードにしましたが 実行させたいコード内にカーソルが有る状態で [ F8 ]を押すと、一回押す毎に一行ずつコードが実行され それに伴い、ローカルウィンドウの状態も変わっていきます。 （エクセルメニューから、マクロの実行するときに [ 実行(R) ] と [ ステップイン(S) ]が有りますね。 ステップインの方で実行して、[F8]を押して続けてもらっても同じです。）
前回の質問で寄せられたコードも 一行ずつ実行させながら、どの変数のどこがどの様に変わっていくのか 確認して見られても良いかもしれません。
こうやって、こう書けばこうなるハズ が 本当にそうなっているか、いないか 分かりやすくなるのではないかと思います。
(HANA)
> kanabun様 > こんなんでました！ > 'A 10.09375 " > 'B 6.0625 " > 'C 0.09375 " ほぼこちらの実験結果と同じですね。 Valueプロパティと Value2 プロパティを使ったほうが速くなる効果が際立った結果となってますね。
> Aが単純な値で > Bがdateとかを含まない数字？で > Cが列を指定した場合ってことであってますかね？ いえいえ。ループの中で、対象としているのはどのプロシージャも 「範囲内の一行」で、同じデータですよ。
> それと、関係ないところなんですが、変数tのt!←これなんの意味があるのでしょうか；
型宣言文字と呼ばれるもので、
Dim t! は
Dim t As Single
と同じ意味です。むかしのBasicは As 〜 という宣言せず、
皆こういう型宣言文字で変数宣言してました。
他にも
s$ s As String i% i As Integer n& n As Long d# d As Double c@ c As Currency
などがあります。 そういえば、リテラル値にも型宣言文字使いますね。 > ss = Join(Application.Index(r.Rows(i).Value2, 0#)) の、0# がその例です。 文字列操作関数で Mid(ss,2) とやるより、 Mid$(ss,2) としたほうが ほんの少しですが効率良くなるのと同じで、引数が要求する型を宣言して渡してやってる わけですね。
あと、ですが、 1行データを 1次元配列にするには、よく知られた方法で Transpose関数を2回使うという手もあります。 先ほどの実験プログラムに追加して、テストしてみました。
Sub TestD() '【Sheet3】 250×250
Dim ss$
Dim i&
Dim r As Range
Dim t!
t = Timer
Set r = Worksheets("Sheet3").Range("A1").Resize(250, 250)
With Application
For i = 1 To 250
ss = Join(.Transpose(.Transpose(r.Rows(i))))
Next
End With
Debug.Print "'D "; Timer - t; """"
Beep
End Sub
結果はこのようになりました。
'A 5.785156 "
'B 4.632813 "
'C 0.078125 "
'D 0.09375 "
これからすると、
> Application.Index(r.Value, i)
のような使い方をするくらいなら、
TRANSPOSEを2回使ったほうが速いということになります。
(kanabun)
ありがとうございます。 今回の一番の収穫でした。 VBEの使い方すら知らなかったのかよと鼻で笑われても仕方ありませんね…。
以前教えていただきながら作ったコードは、色付きセルに1セルずつ転記するってコンセプトで 作っておいたら いつの間にか私の手を離れ表をフォームに変更するマクロとされていました… 間違っちゃいないんですけど、想定外のことに使われるとしょんぼりですね
時間を作って後ほど試させていただきます。
まとめると ・列を取り込むと1列でも2次配列になる ・それをtransposeすると1次配列になぜかなる ・Indexで行を指定することで2次配列を1次配列として関数で扱うことができる ・上記の場合、列を指定したときはTransposeすることで1次配列に直すことができる ・行配列の場合、Joinで直接行範囲を指定した場合、最後に""を挟むことで1次配列にすることができる ってことですね！
理屈はわかっても、使えなくちゃ意味がない…ということなんですが、たとえばどんな表をどのように 処理したい場合に役に立つよとかありますか？ 今の私は原始人がレールガン構えて右往左往してる感じです…
kanabun様 衝突してしまったので、また試してからお礼申し上げます。
（稲葉）
使う機会をねらっておいて、ご自身で 「ここだ！！」と思った時に使うので良いと思います。
ご覧になっていたページでも、 詳細な説明無く文章だけで書かれているのは、作者さんが 「でも、重要でどうしても外せないから！！」と思ったから書かれたのか （だったら、詳しい説明が有っても良さそうですが。） 「まぁ、こんな事も出来るよね」程度で書かれたのか・・・・。
マクロは同じ結果を生む色々な書き方が出来ますが その中でも 一般的な書き方と、そうでない書き方と 分かれてくると思いますよ？
よっぽど回りくどい書き方をするのでないのなら どのタイミングでどれを使うかは、好き好きだと思います。
(HANA)
kanabun様 こんなんなりました 'A 10.09375 " 'B 6.0625 " 'C 0.09375 " 'D 0.09375 " ほとんど奇跡に近いような数値叩き出しましたね…。
なんだか理解しかけてたのがTranspose二回てまたわからなくorz ようは行を取りだしたいからTransposeを一回かけて列にして、それをまたTranspsoeで元に戻してるってことでしょうか？
そうなると、最初にかけた時点で1次配列になるわけだから、二回かけると(1,1)しか残らなくて値は一つになっちゃうとかそういうことではないんですよね。。。
うーん。。。
HANA様 なるほど、まずは作りたいものを決めて、実際に作ってみた後ここだ！って思うところにねじ込んで… また聞きに来ればいいわけですね（前提） 重要かどうかはその人次第で、kanabunさんのようにいろんなやり方提示してすべて同じ結果になることもあれば 仕様が変わったときに型が違うとか怒られちゃうとか大切なのは（ ）=1 （）の部分を自分が作りやすいように作るための勉強なわけですね。 文章が支離滅裂になってきた・・・
（稲葉）
稲葉さん
セル範囲を直接扱うのと、配列変数で扱うことは別に考えないと混乱しますよ。
セル範囲は一行のみの範囲を配列に取り込んでも、２次元配列になります。 myArray = [A1:E1]
Transpose関数は配列のRow/Column を入れ替えますが、２次元目の添え字がひとつの場合（縦長配列） は１次元配列に変換する機能を持っています。
一方、一次元目の添え字がひとつで、２次元目の添え字が複数（横長配列）の場合は 一変に変換できないので、一度２次元の縦長配列に変換してから一次元配列に変換する必要があります。 Transpose関数を使用する回数は上記の理由です。
Index関数を使用して配列の任意の列・行を取得することと混同しない方がいいですよ？ (seiya)
seiya様 なるほど！ transpose y(1,2) ⇒ y(2,1) で transpose y(2,1) ⇒ y(2) ってことですね
それでIndexを使って任意の行、または列を取りだす場合、 行を取りだす場合はIndexでそのまま1次配列（1次元目の配列を直接指定するから）を取りだせるが 列を取りだす場合2次元目の配列を指定するため、 ＞２次元目の添え字がひとつの場合（縦長配列） ＞ は１次元配列に変換する機能を持っています。 の仕様を利用する訳ですね。 あってます。。よね
詳しい解説ありがとうございます。 （稲葉）
いやなんか間違ってるきがする えーっと y(1,1) y(1,2) y(1,3) の場合 y(1,1) y(2,1) y(3,1) となるから y(1) y(2) y(3) となる。。。かな？ （稲葉）
> 'C 0.09375 " > 'D 0.09375 " > ほとんど奇跡に近いような数値叩き出しましたね…。 ひぇ〜、そうなりましたか！ 面白いですね〜〜
> なんだか理解しかけてたのがTranspose二回てまたわからなくorz > ようは行を取りだしたいからTransposeを一回かけて列にして、それをまたTranspsoeで元に戻してるってことでしょうか？
Dim r As Range
Set r = Range("A1:E1") 'まず
r.Value = [{"A","N","G","E","L"}] '一行セル範囲に値を代入します
'このとき r.Value は 1行×5列の2次元配列です。
Dim a As Variant 'つぎに
a = Application.Transpose(r) 'この一行範囲を TRANSPOSE して配列に代入します。
Debug.Print LBound(a, 1), UBound(a, 1) '配列a は ５行×1列の 2次元配列です。
Debug.Print LBound(a, 2), UBound(a, 2) 'つまり [{"A";"N";"G";"E";"L"}] の（1列の）2次元配列となります。
a = Application.Transpose(a) 'もう一度 TRANSPOSE します。
Debug.Print LBound(a, 1), UBound(a, 1) 'すると、_
あ〜ら不思議、{"A","N","G","E","L"} の「一次元配列」にメタフォしました♪
msgbox join(a)
msgbox join(a, vbCrLf)
(kanabun)
例えば
Range("a1:e1")
は２次元配列（シート上の範囲は例外なく２次元配列）
a = Range("a1:e1").Value
配列 a も２次元配列。
これを Transpose(Transpose(a)) とすれば 最初のTranspose で a は行・列を入れ替えただけの２次元配列 2回目のTransposeで一次元配列に変換 (seiya)
上で書きましたように、
Application.Index のばあいは
Application.Index(r, i) は '★------ 不可
で、
Application.Index(r.Rows(1).Value2, 0)
のように、Value(または Value2)プロパティを付さないと機能しませんが、
逆に、
Transpose関数のばあいは
a = Application.Transpose(r)
のように、セル範囲そのものを関数に渡すようにします。
（a = Application.Transpose(r.VALUE) のように、
値をTransposeすることもできますが、値化するだけ 遅くなります）
(kanabun)
kanabun様 ローカルとイミディエイトウンドウ見ながらやってみました 大変わかりやすかったです！
ひとつ ＞Debug.Print LBound(a, 1), UBound(a, 1) '配列a は ５行×1列の 2次元配列です。 ＞Debug.Print LBound(a, 1), UBound(a, 1) 'すると、_ は同じく 1 5 の値が表示されましたが、配列を数えた時の最初と最後というのはわかるのですが この二つを比較したときに何を学べばよいのかわからず… 相変わらず馬鹿ですみません；
RangeからValueを取りだす手間分遅くなるってことですね それでTransposeはRangeそのものを扱えると…。 奥が深いです；
seiya様 なるほどシート上の範囲はどうあがいても2次配列になってしまうわけですね。 2次配列＝単純に次元を複数持つ配列と考えていたのが間違いでした… 以前のスレッドのジャグ配列？なるものが自分が考えたいたものでした。
根本が間違っていたから理解できていなかったんですね^^;
そうすると、a1:b2のような場合はtransposeしても1次配列にはならないわけですよね。 そこでIndexを使って指定してあげると…。 同じ範囲に書き出す場合は書きだしたい範囲の最初を選択して、LBoundとUBoundでresizeしてそこに 落とし込むと…
2次配列で任意の値を取りだす場合、検索するキーとなるのは順番だけですかね？ dictionaryの場合、文字列をキーにすることで検索がかけられるーとは聞いていたのですが どっちにしろわからないorz
今日は実家に帰るためしばらくお返事できなくなると思います。 本日は付き合っていただきありがとうございました。 また、勝手にいなくなる無礼をお許しください。 （稲葉）
だいぶすっきりしてきたようですね。
> 2次配列で任意の値を取りだす場合、検索するキーとなるのは順番だけですかね？
例えば、
myArray = [{"a",1;"b",2;"c",3}]
この場合,VLookUp,Match,Index関数が使用できます。
ans = Application.VLookup("b", myArray, 2, False)
x = Application.Match("b", Application.Index(myArray, 0, 1), 0)
ans = myArray(LBound(myArray) - x + 1, 2)
Match関数使用で注意すべき点は、配列(myArray)のLower Bound がどの数値を取っているかによって 調整が必要になってくる、ということです。 (seiya)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.