[[20150428160902]]　『切り貼り処理』（choi）　ページの最後に飛ぶ

『切り貼り処理』（choi）

「新聞の切り貼り」ならぬネット上で切り貼りする際に困っている点をお助けください。

質問）
表１のＡ列－Ｂ列の組みで、Ａ列に名前が複数ある場合に表２のようにバラして表示する

 －表１－
 A       B
 ｱｲｳ　　 135
 ｶｷｸ,ｻｼｽ 156
 ﾀﾁﾂ     113
 －表２－
 E       F
 ｱｲｳ　　 135　
 ｶｷｸ     156
 ｻｼｽ     156
 ﾀﾁﾂ     113
説明）
・ネット上の表などからＡ，Ｂ列にコピペします
・表１のデータ範囲は「A2-B100」までを想定
・表２のデータ範囲は「E2-F???」（表１の内容による）
・Ａ列の名前は一行に多くて１０個までとします
・Ａ列の名前は漢字、カタカナ（全角、半角）です（※）
・Ａ列の名前の区切りは「,（コンマ）」か「、（読点）」です
・「,」「、」は全角と半角です（※）
※ひとつのコピペ対象でコンマと読点が混在することはほぼないですが、
　いくつかの対象をまとめて処理するため両方対象とします
　（漢字とカタカナ、全角と半角も同様）

よろしくお願いします。

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 関数（？）あるいは操作方法で回答があるまでのつなぎで、ＶＢＡです。

 Sub とりあえず()
    Dim dic As Object
    Dim w As Variant
    Dim c As Range
    Dim d As Variant

    Set dic = CreateObject("Scripting.Dictionary")

    For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        w = Split(Replace(Replace(c.Value, ",", " "), "、", " "))
        For Each d In w
            dic(dic.Count) = Array(d, c.Offset(, 1).Value)
        Next
    Next

    Range("E2").Resize(dic.Count, 2).Value = WorksheetFunction.Transpose(WorksheetFunction.Transpose(dic.items))

 End Sub

（β） 2015/04/28(火) 16:43

β様
うまくいきましたっ！

とりあえず．．．でできちゃうもんなんですか、こんなのが。
私にはまるで呪文のようで（すみません）、これを理解するには
ハードル高すぎですが、挑戦してみます。

ありがとうございました。

（choi） 2015/04/28(火) 17:30

挑戦してみましたがあえなく白旗なので教えていただけますか？
単語？を調べての理解ですが、
・「,」「、」を目印にＡ列の名前をバラバラに取り出して「w」に格納する
・「w」を「d」に格納して「Array」でＢ列の内容と一緒に格納する
．．ここまでは合っていますか？

「dic(dic.Count)」、これが何をしようとされているかさっぱりわかりません。
複数の名前が１行にある場合にＢ列の内容を個々に結びつける“おまじない”では
ないかと思うのですが、「Dictionary」の説明を見ても今一で．．

急ぎませんが、よろしくお願いします。

（choi） 2015/04/30(木) 11:17

 >Replacee

 Repace そのものはおわかりだと思いますが念のため。
 まず　Replace(c.Value, ",", " ")　これは、文字列の中の ","(半角の , ) を　半角スペースにしています。
 で、その結果をさらに、外側にReplaceをいれて、Replace(その結果), "、", " ")　これで、"、"(全角の　、)を半角スペースにしています。
 最終的には、元の文字列の　"," も "、" も半角スペースに変換されます。
 たとえば Dim s As String と規定しておいて

 s=Replace(c.Value, ",", " ")
 s=Replace(s), "、", " ")

 この２行で書くのと同じことを１行でやっています。

 >Split

 文字列を、文字列内の指定区切り文字で区切って１次元配列とします。区切り文字のディフォルトは半角スペースです。
 ですから、仮に変換後の文字列が　"aaa bbb ccc ddd" だった場合、そこから生成される配列は要素を 4つ（要素番号は0から）もったもの。

 w(0) : aaa
 w(1) : bbb
 w(2) : ccc
 w(3) : ddd

 となります。

 >For Each d In w

 １次元配列の各要素を、１つずつ　Variant型変数の　d に取り出します。
 ↑の例でいえば、aaa 、bbb、ccc、ddd と順番に４回取り出すことになります。

 >Dictionary

 ＶＢＡの外の外部プログラムです。これを使うために最初に　Set dic = CreateObject("Scripting.Dictionary")　で呼びこんでいます。
 （この他に参照設定して使う方法もありますが割愛します）

 で、Dictionary とは文字通り、辞書　です。　辞書には「見出し語（キー）」があって、その見出し語のページをめくると「説明（アイテム）」がありますね。
 Dictionaryに登録する方法は他にもあるのですが、簡単な記述として　辞書(キー)=説明　こんなふうに書けます。

 ところで、Dictionary には　２つのポイントがあります。
 １つは、「登録順」に格納されるということ。もう１つは、「キーはユニーク」、逆にいえば同じキーなら、すでに登録されているアイテムを置き換えます。
 今回はセルの中に複数存在する aaa や bbb を格納しようとしています。セルを上から順に処理していっていますし、また aaa や bbb も、その発生順に取り出していますので
 「登録順に格納」という面では目的に合致しているのですが、問題は、「キーがユニーク」というところです。ですから、ここでは登録にあたって「連番」を使ってimasu.
 連番は、自分で管理して使ってもいいのですが、たまたま Dictionaryのプロパティで Count というものがあります。登録されているデータ数なんですが、最初は 0、１件登録されるたびに
 1 ずつ増えていきますので、これを連番の代用としました。

 >dic(dic.Count) = Array(d, c.Offset(, 1).Value)

 左辺については↑で説明した通りです。その見出しに対してアイテムとして右辺に、Array(d, c.Offset(, 1).Value)
 Array関数は、引数群を１次元配列（これも要素番号は 0　から始まります）にしするもので、
 要素(0) : d つまり aaa や bbb
 要素(1) : c.Offset(,1).Value ここで、c はA列のセル、C.Offset(,1) は B列のセル。なので、C.Offset(,1).Value は、そのB列の値になります。

 最終的には Dictionary は 提示の例でいえば

 キー　　データ
  0     配列(ｱｲｳ,135)
  1     配列(ｶｷｸ,136)
  2     配列(ｻｼｽ,136)
  3     配列(ﾀﾁﾂ,113)

 になります。

 > Range("E2").Resize(dic.Count, 2).Value = WorksheetFunction.Transpose(WorksheetFunction.Transpose(dic.items))

 これは、説明がちょっと難しいので、エッセンスだけ。
 Dictionary のキーは Dic.Keys というメソッドで、アイテムは Dic.Items というメソッドで１次元配列に取り出すことができます。
 ここでは Dic.Items を指定しています。これが１次元配列ですが、↑で説明した通り、その各要素も要素数が２つある１次元配列。
 １次元配列の中身が１次元配列になっている場合、シート関数のTranspos(縦横入れ替え）を２回かけてやると、シートに落とし込める２次元配列になります。
 で、この２次元配列を、E2から始まり、Dictionaryに登録した件数分の行、登録した２項目分の列の領域に一挙に落とし込んでいます。
（β） 2015/04/30(木) 13:03

ご丁寧な説明ありがとうございます。

 >辞書(キー)＝説明　こんなふうに書けます

そうなんですか、「dic.Count」はキー（連番）だったんですね。
こういう使い方がある．．と今は覚えておきます。

 >．．Transpos(縦横入れ替え）を２回かけてやると．．２次元配列になります

これも知りませんでした。
左辺は「Resize」で２次元配列にしてると思ってたんですが、この右辺の２つの「Transpose」は
いったい何なんだ？？と思考停止してました＾＾

これでコードの“流れ”はわかったような気になりました。
まだまだ人間でいえば“ハイハイ”レベルの理解度かと思いますが、
これからもよろしくお願いします。

（choi） 2015/04/30(木) 14:51

追記です）
ふと思ったのですが、「Transpose」２回って元にもどるのでは．．
わけわからなくなってきました（21:36）

 外側の大きな配列（内側の２項目の配列）

 こういう構成なんですが、最初のTranspose では 内側の配列がひっくりかえります。
 で、２回目の Transpose で外側の配列がひっくり返ります。

 Sub Test()
    Dim w(1 To 3)
    Dim v1 As Variant
    Dim v2 As Variant

    w(1) = Array("aaa", "bbb")
    w(2) = Array("ccc", "ddd")
    w(3) = Array("eee", "fff")

    v1 = WorksheetFunction.Transpose(w)

    v2 = WorksheetFunction.Transpose(v1)

    Stop

 End Sub

 このコードを実行して Stop で止まった時点で、表示 -> ローカルウィンドウ
 でてきた画面で +w +v1 +v2 となっているところをクリック、さらにその下のレベルの + もクリック。
 こうやって、それぞれの配列がどうなっているか、目で見て確かめてみてください。

（β） 2015/04/30(木) 22:33

手取り足取りありがとうございます。

こうして手順を追っていくと配列の“箱”のイメージが湧いてきましたが
今ひとつ自信がありません。
・「v1」は２行３列、「v2」は３行２列でいいんですよね？
・「w」は１行２列が３個ある．．でいいんでしょうか？
※「v1」式の「Transpose」は、１行２列×３個の「w」の行列を入替えて
　２行３列に“くっつける”働きがある．．という理解でよろしいでしょうか？
　（どうも違うような．．）

（choi） 2015/05/01(金) 10:41

 まず以下のような１次元配列があったとして

 配列(1) "ABC"
 配列(2) "DEF"
 配列(3) "GHI"

 私なんかは、今でも、【縦】に並んでいるイメージを持ってしまいます。
 実際にメモリー内でどのように配置されているということはさておき、
 我々が身近なシートにあてはめますと、１次元配列は【横】に並んだ配列です。

 Sub Test1()
    Dim 配列(1 To 3)

    配列(1) = "ABC"
    配列(2) = "DEF"
    配列(3) = "GHI"

    MsgBox "今から縦に転記してみます"

    Range("A1:A3").Value = 配列

    MsgBox "どうでしたか？"

    MsgBox "次に横に転記してみます"

    Range("A1:C1").Value = 配列

    MsgBox "今度は大丈夫でしたね"

 End Sub

 イメージとして表現すると

 [ABC][DEF][GHI]

 つまり２次元配列でいうと、その１行目というイメージです。

 そうすると　Array("aaa","bbb") で出来上がる１次元配列イメージは

 [aaa][bbb]

 これが大きな１次元配列の中の各要素になりますから　配列 w は、

 【[aaa][bbb]】【[ccc][ddd]】【[eee][fff]】   ★１

 で、これを　Traspose すると　配列内の小さな配列部分がひっくりかえり、v1 ２次元配列になります。

 [aaa][ccc][eee]
 [bbb][ddd][fff]

 (追記) ここで、中身がひっくりかえるのはわかるけど、それは外側の１次元配列の各要素内のことで

 ｜[aaa]｜[ccc]｜[eee]｜
 ｜[bbb]｜[ddd]｜[fff]｜

 と、あくまで、外側は１次元なんじゃないのか？　という疑問が残ると思います。
 なぜ、ここで外側／内側の構造がなくなり、通常の２次元になるのか、βにもわかりません。実態としてこうなるということなんです。
 イメージ的に自分自身を納得させるなら、薄いラッピングで、きっちり包装されていた w が、中身を縦にしたので、表の包装が破れて
 中身だけになったと、そんな、非論理的なことを考えたりしています。

 (追記) ここまで。

 これを、もう１度、Transpose を掛けて縦横入れ替えますと、これはわかりやすいと思いますが以下。これが v2 です。

 [aaa][bbb]
 [ccc][ddd]
 [eee][fff]

 ★１で横並びになっていた２項目ずつのセットが縦に配置されましたね。

（β） 2015/05/01(金) 12:51

β様
心から感謝いたします。

 >１次元配列は【横】に並んだ配列です
 >薄いラッピングで、きっちり包装されていた w が、中身を縦にしたので、表の包装が破れて中身だけになった．．

これでストーンと“つかえていた”ものが取れた気がします。

さらなる疑問ですが、「w」から一気に「v2」にできる命令？はないのでしょうか？
（ないから「Transpose」２回なんですよね．．愚問ですか）

（choi） 2015/05/02(土) 12:31

 >「w」から一気に「v2」にできる命令？はないのでしょうか？

 そうなんですよねぇ。なさそうなので、ずっと　Transpose２連発でやってきましたけど
 もしあれば、いいですねぇ。

 (choi)さん、探究してみられてはいかがですか？

 ところで、今回は、Dictionary内に転記イメージを作成しましたので、そこにセットできる形として「１次元配列」を格納して Transpose２連発でひっくりかえしましたけど
 Dictionaryをインデックス管理だけに使い、全体の転記イメージを最初から２次元配列に格納しておいて
 最後にどさっと、そのまま、１回で転記　という方法もあります。

（β） 2015/05/02(土) 14:59

β様
何度もありがとうございます。

やはり“一発”はないんですね。　　ド素人の感覚では意外に思ってしまいますが。

 >探究してみられてはいかがですか？
 >．．最後にどさっと、そのまま、１回で転記．．

う～ん、これは未だ幼稚園入園前の幼児にとっての大学入試問題みたいで．．．＾＾；

“飛び級”できるレベルになれという励ましと勝手に解釈して精進したいと思います。
これからもよろしくお願いします。

（choi） 2015/05/03(日) 07:10

コメント返信：

[ 一覧(最新更新順) ]

[[20150428160902]] 『切り貼り処理』（choi） ページの最後に飛ぶ

[[20150428160902]]　『切り貼り処理』（choi）　ページの最後に飛ぶ