エクセル大量のセルのデータを接続したいのですが・・・

質問

１．A1～A6にある値を接続したい場合、通常は
=CONCATENATE(A1,A2,A3,A4,A5,A6)　または
=A1&A2&A3&A4&A5&A6　というように書くと思います。

セルの個数が少なければこれでぜんぜん問題はないのですが、たとえばセルが100個もあった場合はおそろしく長い式になり、書く気力がわきません。

例えば「=CONCATENATE(A1:A100)」　のような形式で簡単に一定範囲のセルのデータを接続できる方法はないものでしょうか？

２．上記のワザがあったとして、

A1～A100にある各データを、空白セルを除き、間に中黒（・）または全角のカンマ（、）でつなぎたいのです。ワークシート関数でやるならどうしたらよいでしょうか？

もちろん小数のデータなら
=A1&IF(A1<>"","・","")&A2&IF(A2<>"","・","")&A3&IF(A13<>"","・","")&A4&IF(A4<>"","・","")&A5&IF(A5<>"","・","")&A6 という書き方で出来るのですが、これを100個もつなげるなんてできません。・°°・(((p(≧□≦)q)))・°°・。

どうかお知恵をお貸しくださいませ。

回答No.3

Dxak · ベストアンサー

私なら・・・例の通りだとＢ列を計算用セルとして使いますね

B2に
=A1&IF(B2="","",","&B2)
で、以下最後の予定の所まで、コピー＆参照で・・・

関数をＶＢＡで作成するなら・・・

Function StrADD(usCells As Range, Optional usStr As String = "") As String

Dim usCell As Variant
Dim usTmp As String

usTmp = ""
For Each usCell In usCells
If usCell <> "" Then
usTmp = usTmp & IIf(usTmp = "", usCell, usStr & usCell)
End If
Next usCell

StrADD = usTmp

End Function

こんな感じかな？
=StrADD(A1:A100,"・")
で、計算するようにはしてあるつもり＾＾；

お礼

多少はVBAもかじっているのですが、ユーザー定義関数は馴染み薄です。
=StrADD(A1:A100,"・")
すごいですね！"・"を変えれば、随分応用がききますね！
とても助かりました。
有難うございます。　(o。_。)oペコッ.

回答No.8

Wendy02

こんにちは。

Sub test()
Dim 範囲 As Range '←ここに型の宣言が必要になります。
Set 範囲 = Range("A1:A100")　
MsgBox myJoin(範囲)　'←デリミタ(Delimiter)は省略可
End Sub

こんな風になりますね。
型の宣言が必要になるのは、ユーザー定義関数で、引数の型を指定しているからです。

お礼

有難うございます。
よくわかりました。

回答No.7

Wendy02

こんにちは。

>最初JOIN関数を使おうとして

本来は、1行、1列を選択するような判定のコードが必要になりますが、もっとも簡単な書き方で書くと、以下のようになります。Join関数は、「1次元配列」を引数としますので、Range オブジェクトから取り出した配列は、2次元なので引き受けられません。

Function myJoin(範囲 As Range, Optional デリミタ As String = "")
Dim v As Variant
Dim i As Long
Dim myArray() As Variant
For Each v In 範囲
　ReDim Preserve myArray(i)
　myArray(i) = v.Value
　i = i + 1
Next v
　myJoin = Join(myArray, デリミタ)
End Function

それから、
>「正規表現置換」ですか・・・。敷居が高そうです。

私自身、「正規表現」は、簡単なものしか知りません。ワイルドカードの延長だと思えばよいと思います。VBAでは、VBScript.RegExp が多いようですが。Web 関連では、多く出てきます。

お礼

ありがとうございます。
ただ、わたしは基本的な事がわかっていないようで、VBAコードの中でせっかくのFunction myjoinが使えませんでした。
以下のようにしてみたのですが、「引数は省略できない」とのコンパイルエラーです。

Sub test()
範囲 = Range("A1:A100")
MsgBox myJoin
End Sub

Function myJoin(範囲 As Range, Optional デリミタ As String = "")
Dim v As Variant
Dim i As Long
Dim myArray() As Variant
For Each v In 範囲
ReDim Preserve myArray(i)
myArray(i) = v.Value
i = i + 1
Next v
myJoin = Join(myArray, デリミタ)
End Function

どのようにつかうのでしょうか？
もちろんワークシート上では
=myjoin(A1:A100,"・")　でちゃんとできましたが・・・・。

回答No.6

noname#204879

A　B
1　a　a
2　b　ab
3　c　abc
4　d　abcd
5　e　abcde
6　f　abcdef

B1: =A1
B2: =B1&A2
セル B2 を下方にズズーッと複写。

あるいは、セル B2 のフィルハンドルをダブルクリックすれば一瞬でセル B6 の結果（=A1&A2&A3&A4&A5&A6 の結果と等価）が得られます。

回答No.5

Wendy02

こんにちは。

>たとえばセルが100個もあった場合はおそろしく長い式になり、書く気力がわきません。

>=A1&A2&A3&A4&A5&A6

それは、VBAのJOIN関数をワークシートで実現したいという、時々ある質問ですが、純粋にExcelだけで実現することに意義を感じていないので、私は真剣に考えたことがありません。

実務的考えれば、Excelの外でエディタなどで、タブを抜いてしまえばよいでしょうし、中黒点を入れるなら、タブを「・」に置換してしまえばよいと思います。

数式でも、ひとつふたつセルに数式を文字として入れておいて、Excelのオートフィルでドラッグして、後はエディタで置換すればいくつでも作れてしまいます。

<正規表現置換>
横なら、\t -> &, 縦なら、\n -> &

=A1&A2&A3&A4&A5&A6&A7&A8&A9&A10&A11&A12&A13&A14&A15
&A16&A17&A18&A19&A20&A21&A22&A23&A24&A25&A26&A27&A28&A29&A30

=A1&IF(A1<>"","・","")&A2&IF(A2<>"","・","")&A3
&IF(A3<>"","・","")&A4&IF(A4<>"","・","")&A5
&IF(A5<>"","・","")&A6&IF(A6<>"","・","")&A7
&IF(A7<>"","・","")&A8&IF(A8<>"","・","")&A9
&IF(A9<>"","・","")&A10&IF(A10<>"","・","")&A11
&IF(A11<>"","・","")&A12&IF(A12<>"","・","")&A13
&IF(A13<>"","・","")&A14&IF(A14<>"","・","")&A15
&IF(A15<>"","・","")&A16&IF(A16<>"","・","")&A17
&IF(A17<>"","・","")&A18&IF(A18<>"","・","")&A19
&IF(A19<>"","・","")&A20&IF(A20<>"","・","")&A21
&IF(A21<>"","・","")&A22&IF(A22<>"","・","")&A23
&IF(A23<>"","・","")&A24&IF(A24<>"","・","")&A25
&IF(A25<>"","・","")&A26&IF(A26<>"","・","")&A27
&IF(A27<>"","・","")&A28&IF(A28<>"","・","")&A29
&IF(A29<>"","・","")&A30&IF(A30<>"","・","")

そういう趣旨のご質問でないのでしたら、後は、ユーザー定義関数を作ればよいかと思います。一般VBAと同じ扱いではないユーザー定義関数を組み込み方も方法はありますが、それは、merlionXXさんに説明は必要ないと思います。

なお、#1 でimogasiさんが、記憶にないと書かれていますが、私は、某掲示板で、何度かであって、最後に、この質問内容で、相手の方が、VBAのJOIN関数を使ったユーザー定義関数は認めながらも、最終的に、私の何種類かの解決策を全て否定されたので、それ以来、まともに考える気がしません。業務の効率化とExcelの数式の解法とは必ずしも一致しない、というところでしょうね。

お礼

Wendy02さま、いつも丁寧にありがとうございます。
「正規表現置換」ですか・・・。敷居が高そうです。
ユーザー定義関数ですら、自分ではできなかったのですから。
わたしはまだまだ修業が足りませんねえ。
これからもよろしくご指導くださりませ。

補足

実は最初JOIN関数を使おうとして、セル範囲を配列に取り込むところでつまずいてしまいました。

aryStrings = Range("A1:A100")
strVal = Join(aryStrings, "・")
MsgBox strVal

どう直せばいいのでしょうか？

回答No.4

Dxak

＃３です
すみません、ＶＢＡ考えてる最中に、かなりかぶってますね＾＾；
で、訂正で・・・
> B2に
> =A1&IF(B2="","",","&B2)
=B1&IF(B2="","",","&B2)
> で、以下最後の予定の所まで、コピー＆参照で・・・
の部分と
B1に
=A1
が、入ってる前提ね；；

お礼

有難うございます。

回答No.2

a-saitoh

これは1回限りなのでしょうか、あるいは同じ操作を違うデータに対して繰り返し(毎月とか毎日)行わねばならないのでしょうか？

1回限りなら、いろいろ手はありますが。。。ワークシート関数ではなく、他のアプリへのcopy&pasteなどをいろいろ駆使して。

お礼

繰り返し(毎月とか毎日)行わねばならないのです。
ありがとうございます。

回答No.1

imogasi

こういう課題は２００１年来OKWAVEの質問を見ているが、記憶に無い。特殊なニーズで、書く著版拡張版CONCATENATE関数はない。
そういう特殊なニーズを解決したい方は勉強が必要です。
簡単なVBAの知識が要りますが、ユーザー関数を作成するのが効率的と思います。
ーー
B列から右列に不定列に語句文章があるとして、VBEの標準モジュールに（ツールーマクローVBEその後VBE画面で挿入ー標準モジュール）
下記を貼り付ける。

Function conc(a)
'問題にしている行を取得
r = a.Row
'問題行の最右列番号を取得
c = Cells(r, 255).End(xlToLeft).Column
'文字列を初期化
s = ""
'B列からｃ列まで各セルの文字列を結合
For i = 2 To c
s = s & " " & Cells(r, i)
Next i
'結合繰り返し結果を関数値として返す
conc = s
End Function
ワークシートのA列A2に =CONC(A2) と入れて、下方向に式を複写。
結果　A列が結果。Bretuより右はデータ（エクセル許容なら何列でも良い）
A列　　　　　　　B列　　　C列　　　　D列　　E列　　F列
aa bb cc dd aa bb cc dd
dd ff gg hh ii dd ff gg hh ii
ll mm 　　　　ll mm
nn 　　　　nn
oo pp qq oo pp qq
（OKWAVEの画面で左に詰まって見難いかもしれないが、自分のデータでやってください。）

お礼

有難うございました。
特殊なニーズで相すみませんでした。

ユーザー定義関数、勉強になりました。