[[20130218121230]]　『複数セルの内容を１セルにまとめる』（ふるさと）　ページの最後に飛ぶ

『複数セルの内容を１セルにまとめる』（ふるさと）

 お世話になります。お力をお貸しください。
現在、複数セルに入力されているものを、行ごとに１つのセルにまとめたいです。

 	F	G	H	I	J	K	L
5			52,53	57-60			92,94,95,96
6	20	41	52,53	57-60		76	90,91,92,94,95
7			53	57-60			92,94

 上記のような表があり、結果としては

 5行目　52,53,57-60,92,94,95,96
 6行目　20,41,52,53,57-60,76,90,91,92,94,95
 7行目　53,57-60,92,94

 としたいのです。

 自分なりに考え、PHONETIC関数でいけると思ったのですが、
52,5357-6092,94,95,96
のようになってしまい、セルとセルの間にカンマが入れられません。
実際のセル範囲はF列からBA列まであり、行数は５行目から88行目までです。

 なにかいい方法はありませんでしょうか？
どうぞよろしくお願いいたします。

 WindowsXP　Excel2003　使用です。

 =SUBSTITUTE(TRIM(F5&" "&G5&" "&H5&" "&I5&" "&J5&" "&K5&" "&L5)," ",",")

 下へコピー。

 >実際のセル範囲はF列からBA列まであり

 地道にがんばるか、VBAだね。（VBAは識者さん回答をどうぞ）

 (GobGob)

 地道に頑張った例。
　
=SUBSTITUTE(TRIM((F5&" "&G5&" "&H5&" "&I5&" "&J5&" "&K5&" "&L5&" "&M5&" "&N5&" "&O5&" "&P5&" "
 &Q5&" "&R5&" "&S5&" "&T5&" "&U5&" "&V5&" "&W5&" "&X5&" "&Y5&" "&Z5)&" "&
 (AA5&" "&AB5&" "&AC5&" "&AD5&" "&AE5&" "&AF5&" "&AG5&" "&AH5&" "&AI5&" "&
 AJ5&" "&AK5&" "&AL5&" "&AM5&" "&AN5&" "&AO5&" "&AP5&" "&AQ5&" "&AR5&" "&AS5&
 " "&AT5&" "&AU5&" "&AV5&" "&AW5&" "&AX5&" "&AY5&" "&AZ5&" "&BA5))," ",",")
　
(ROUGE)

 対象領域がどこかがあいまいなのと、どこに結果を記入するかがあいまいだけど
 Ｆ～Ｌ列の ５～７行目が対象で、結果は各行のＡ列にセットするとして。

 Sub Sample()
    Dim w As Variant
    Dim r As Range
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = ",+"
        For Each r In Range("F5:L7").Rows
            w = Join(WorksheetFunction.Index(r.Value, 1, 0), ",")
            w = .Replace(w, ",")
            If Left(w, 1) = "," Then w = Mid(w, 2)
            If Right(w, 1) = "," Then w = Left(w, Len(w) - 1)
            r.EntireRow.Range("A1").Value = w
        Next
    End With
 End Sub

 (ぶらっと)

 おじゃまします。

 （=SUBSTITUTE(TRIM(F5&" "&G5&" "&H5&" "&I5&" "&J5&" "&K5&" "&L5)," ",",") のVBA）

  Sub Try1()
    Dim r As Range
    Dim ss As String

    With Application
        For Each r In [F5:L7].Rows
            ss = .Trim(Join(.Index(r.Value, 0#)))
            r.Cells(1, 0).Value = Replace(ss, " ", ",")
        Next
    End With
 End Sub
 (kanabun)

 ↑　なぁるほど！！　半角スペースでJoinしたものをシート関数のほうのTrimで先頭と末尾のスペースを除去し
　　間の連続したスペースを１つにした上で、スペースを , に変換。

 　　勉強になります。私のコードは重厚長大でしたね。おそまつでした。

 (ぶらっと)

UDF

 =ConcatRng(F5:L5,",")

 Function ConcatRng(rng As Range, txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = ",+"
        ConcatRng = .Replace(Join$(Application.Transpose( _
            Application.Transpose(rng.Value)), ","), ",")
        .Pattern = "^,+|,+$"
        ConcatRng = .Replace(ConcatRng, "")
    End With
End Function
(seiya)

 皆様、本当にありがとうございます。

 GobGobさま
 TRIM関数を初めて知りました。
空白でつなげればTRIM関数で消せるということなのですね。驚きでした。
ありがとうございます。

 ROUGEさま
 全文書いていただきましてありがとうございます。
これそのまますぐに使えました。

 ところで、TRIM関数の中がカッコで２つに分けられていますが、これはなにか理由がありますか？

 ぶらっとさま
 すみません。範囲はF5からBA88までで、結果はBB5からBB88までに出したかったのです。
説明不足で申し訳ありません。
いただきましたマクロを走らせてみましたら、数字が３桁のものが並んでいる行があり、その行は１つの数字になってしまいました。
例）F10が68　H10が133　Jが156　AAが245　　結果が68133156245に（表示は68,133,156,245ですが）
これを回避するにはどうしたらいいのでしょうか？
後学のためもしよろしければお教えください。

 kanabunさま
 GobGobさまの式がマクロだとこうなるのですね。
マクロはマクロの記録したしたことがないので、まったく詳しくなく、とんちんかんな質問かもしれませんが、
途中出てくるTrimやIndexは通常の関数と同じと考えてよろしいのでしょうか？

 なお、ぶらっとさまにもお聞きした数字が１つになる現象がコチラでも出るのですが、関数のときと何が違うのでしょう？

 皆様のおかげで目的は達成できたのですが、もしよろしければ疑問点を御指導いただければ幸いです。
（ふるさと）

すみません、ちまちま書き込んでいる間にさらに御指導いただきまして。
seiyaさまのは今から試します！
（ふるさと）

 いけね

 コードを下記に変更

 Option Explicit

 Function ConcatRng(rng As Range, txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "(" & txt & ")+"
        ConcatRng = .Replace(Join$(Application.Transpose( _
            Application.Transpose(rng.Value)), txt), txt)
        .Pattern = "^" & txt & "+|" & txt & "+$|^" & txt & "+$"
        ConcatRng = .Replace(ConcatRng, "")
    End With
End Function
(seiya)
 2行変更 14:25

 > 途中出てくるTrimやIndexは通常の関数と同じと考えてよろしいのでしょうか？

 はい、ワークシート関数です。
 VBA にも Trim関数ありますが、動作が異なります。
 SUBSTITUTEワークシート関数 は VBAのPeplaceで代替しています。

 別件で、
 seiya さんの join$ は 単に join でいいのではないでしょうか VBA6で追加された
 文字列操作関数なので。

 (kanabun)

 2003はVB6.xじゃなかった？
 2003当時からJoin$で使用してたので問題ないと思いますが？
 (seiya)

 >その行は１つの数字になってしまいました

 こめんとしたように、私のコードは重厚長大で、でくのぼうコードなんだけど、説明のあったレイアウトに合わせれば以下。
 ただし、ロジックはかえていない。連続した数字になることはないと思うけど？

 >F10が68　H10が133　Jが156　AAが245

 このデータで実行すると BB10 は　68,133,156,245　になるけどねぇ？？

 Sub Sample2()
    Dim w As Variant
    Dim r As Range
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = ",+"
        For Each r In Range("F5:BA88").Rows
            w = Join(WorksheetFunction.Index(r.Value, 1, 0), ",")
            w = .Replace(w, ",")
            If Left(w, 1) = "," Then w = Mid(w, 2)
            If Right(w, 1) = "," Then w = Left(w, Len(w) - 1)
            r.EntireRow.Range("BB1").Value = w
        Next
    End With
 End Sub

 (ぶらっと)

 > 2003当時からJoin$で使用してた

 2000 で追加された Join, Split, Replace などの文字列関数ですが、
 それ以前の Mid$, Trim$... などのレガシーな文字列関数がもっていた $付き関数では
 なくなりました。もちろん、$ をつけてもエラーにはなりませんが。

http://www.shoeisha.com/book/hp/pc/office/Function/index.html

 (kanabun)

 $付きの文字列関数は返り値が文字列型限定になる(Variant/Stringでは無い)
 ので、処理速度が若干早くなるはず（経験からも)
 なにかもんだいあるのかな～？
 (seiya)

 > $付きの文字列関数は返り値が文字列型限定になる(Variant/Stringでは無い)
 > ので、処理速度が若干早くなるはず（経験からも)

 オブジェクト・ブラウザには
 Function Join(SourceArray, [Delimiter]) As String
 とありますが。

 (kanabun)

 seiyaさま
 すみません、最初ふつうのマクロと思いこんでいて遅くなりました。
できました！ありがとうございます！

 最初のと後からのと、なにが違うのか今の時点ではさっぱり分かっていないのですが、
せっかくなので比べながらどうなっているのか調べてみたいと思います。

 いろいろな方法があったのですね～

 kanabunさま
 お返事ありがとうございます。
マクロはふつうの関数（ワークシート関数というのですね）も使えたのですね！
今後、マクロ記録をいじるときに便利になりそうです。
そして「Peplaceで代替」ということはワークシート関数と同じような機能を持つものも多いということですね。
このあたりを取っ掛かりにして調べてみたら面白そうです。
ありがとうございます！

 ぶらっとさま
 新しいコードをありがとうございます。
重厚長大だなんてとんでもない。
シロウトの私からすると、いただいたコードを解読しながらここがこうなるのかと勉強するのが楽しいです。
こんな風になるのかと目からうろこです。

 >このデータで実行すると BB10 は　68,133,156,245　になるけどねぇ？？

 見た目は　68,133,156,245　なのですが、数式バーをみますと　68133156245　になっているんです。
たまたま3桁ごとにカンマなので、たぶんExcelが勝手に書式を通貨にしているんだと思います。
他のセルはみな標準なのに、そこだけ通貨になってしまっています。

 （ふるさと）

 > 最初のと後からのと、なにが違うのか今の時点ではさっぱり分かっていないのですが、
 >せっかくなので比べながらどうなっているのか調べてみたいと思います。

 最初のは第二引数が作用しないが、後のは生かされている。
 と、いうことです。（単に記述ミス）

 違いは

 =ConcatRng(F5:L5,"-")

 等としたときに、違いが判るはずです。
 (seiya)

 seiyaさま

 本当だ！後ろに入力した記号が区切り記号になってます！
面白いですね～～
今回はカンマだけなので最初のでも問題はなかったのですが、こういう風に別に設定できるのは楽しいですね。

 ヘルプだけでは出てこないところもあるので、またさらに調べてきます。
解説もありがとうございました！
（ふるさと）

 >見た目は　68,133,156,245　なのですが、数式バーをみますと　68133156245　になっているんです。

 なぁるほど。

 じゃぁ、BB列の書式をあらかじめ文字列書式にしておくか、あるいは
 r.EntireRow.Range("BB1").Value = w　を　r.EntireRow.Range("BB1").Value = "'" & w　かな？

 (ぶらっと)

 ぶらっとさま
 お返事ありがとうございます。

 > じゃぁ、BB列の書式をあらかじめ文字列書式にしておくか、あるいは
 > r.EntireRow.Range("BB1").Value = w　を　r.EntireRow.Range("BB1").Value = "'" & w　かな？

 ！！！あらかじめ文字列！
 なぜこんな基本的なことが思いつかなかったのか…
お恥ずかしい限りです。
頭に　"'"　でもいいですよね、本当になぜ思いつかなかったのか。
御指導ありがとうございました。
"'"　付けるようにいたします。

（ふるさと）

 >オブジェクト・ブラウザには
 >Function Join(SourceArray, [Delimiter]) As String
 >とありますが。

 と、あるよね？
 String型の戻り値に限定されているよね？

 Sub test()
    MsgBox myLeft$("abcde", 2)
    MsgBox myLeft("abcde", 2)
 End Sub

 Function myLeft(txt As Variant, length As Long) As String
    myLeft = Left(txt, length)
 End Function

 何か問題あるのだろうか？
 (seiya)

 問題はないです。

 ただ、Join や Replace関数には $付きは無いんですよね。

  （関数例は一部です）
   String型を返す関数      Variant(内部形式String)型を返す関数
  --------------------     -------------------
　 Dir$                    Dir
　 Format$                 Format
　 Hex$                    Hex
　 LCase$                  LCase
　 Left$                   Left
　 Mid$                    Mid
　 Right$                  Right
　 Space$                  Space
　 Trim$                   Trim
　 UCase$                  UCase
　 
　(Office2000で追加)
　 Join
　 Replace
　 
   (kanabun)

 Join関数 Replace関数 に関しては 既に両方共戻り値がString型に限定されているので
 $を 付けても付けなくても変わりがないということですよね？
 (seiya)

 > Join関数 Replace関数 に関しては 既に両方共戻り値がString型に限定されているので
 > $を 付けても付けなくても変わりがないということですよね？

 そうです。
 対して、Mid, Left, Trim, Chr などなどは後で追加された関数です。
こういう類は以前からある「$付き」関数を使ったほうが効率よい、と
いうことになります。

 seiya さんが直前に挙げられた例を引用させていただきますと、
　
　　Left(txt, length)
と
　　Left$(txt, length)
　
とでは明確なちがいがある
ということになります。
ぼく自身は Left$ をつかっています。
  (kanabun)

 ヨコから失礼。昔のことはよくわからない、ぶらっとです。

 ・$ を付けたほうが効果的なものがある
 ・$をつけても、変わりがないものがある

 タイプが、これだけ（つまり $ をつけると　デメリットがあるというものがない）なら
 これはあれ、あれはこれと悩まずに、すべて $ をつけるのがいいのかな？

 ただ、これらによって、どれぐらいのインパクトがあるのかないのか、わからないので
 私の場合は、基本、すべて　【$をつけない】記述が好きですね。
 (かってあった処理効率の悪さは、ＯＳやＰＣ、あるいはメモリー環境の格段の進歩で、もう吸収されている部分も
　多いのではと期待しつつ）

 (ぶらっと)

 たとえば以下のようなコードを走らせると、私の環境（Win7 + xl2010) では
 Test1 は 0.0118秒前後、一方　Test2 は 0.0078秒前後。

 確かに Test2 のほうが 0.004秒ぐらい早い。早いけど、５万回の処理で　0.004秒。
 これを【効率が悪い】とみるか、
【かわらないので、単純なコードのほうがいい。神経は別のところに注入しよう】とみるか
 それは、ひとそれぞれですけどね。

 Sub Test1()
    Dim t As Double
    Dim s As String
    Dim x As Long
    Dim z As String

    t = Timer
    z = "ABCDEFG"

    For x = 1 To 50000
        s = Left(z, 1)
    Next

    MsgBox Timer - t

 End Sub

 Sub Test2()
    Dim t As Double
    Dim s As String
    Dim x As Long
    Dim z As String

    t = Timer
    z = "ABCDEFG"

    For x = 1 To 50000
        s = Left$(z, 1)
    Next

    MsgBox Timer - t

 End Sub

 (ぶらっと)

 そんな単純な比較したってはじまらないんだよね...
 (seiya)

コメント返信：

[ 一覧(最新更新順) ]

[[20130218121230]] 『複数セルの内容を１セルにまとめる』（ふるさと） ページの最後に飛ぶ

[[20130218121230]]　『複数セルの内容を１セルにまとめる』（ふるさと）　ページの最後に飛ぶ