シーゴの Excel 研究室

タイトル変更しました （旧称：今日を乗り切るExcel研究所）

トップ > セル-入力 > 複数セルの文字列を連結したい

複数セルの文字列を連結したい

セル-入力

複数セルの内容を連結したテキストを作成する手順と、それを自動化するマクロの検討をしてみます。

f:id:shego:20171007174627p:plain

セルの内容を結合する

複数セルの内容を合わせて一つのテキストデータにしたいことはよくあります。

テキストエディタなどにコピー＆ペーストして編集してもいいのですが、表のデータなどからたくさんのテキストを作成するなら、セルをつなげる数式を作成して、一気に全行変換したいところです。

数式でセルの内容を連結するのは＆演算子でできます。

それ自体難しい事ではないのですが、やってみると非常に面倒な作業です。 ２・３個のセルを結合するだけならこれで何とかなりますが、もっと多くのセルの内容を結合したり、区切り文字を間に挟もうとすると、もう苦痛でやっていられません。

これを楽にやる方法はないのでしょうか。

手作業でセル内容を連結するには

これまで＆演算子で文字列を連携するやり方ぐらいしか使ってきませんでしたが、 調べてみると、ほかにもいくつかのやり方があるようです。

Excel のバージョンによっても少しずつ便利になっているようです。

  • Excel 2010 まで、＆演算子、CONCATENATE関数
  • Excel 2013 以降、フラッシュフィル機能追加
  • Excel 2016/Office 365 で、CONCAT関数、TEXTJOIN関数追加

ここでは、フラッシュフィルを使ったやり方と、CONCATENATE関数を使ったやり方を検証してみます。

フラッシュフィルを使ってみる

Excel 2013 以降を使用している方は、「フラッシュフィル」機能を使うのが良さそうです。

「フラッシュフィル」というのは、「お手本」となるテキストを参考にして、他の行のセルのデータからテキストを一括で自動生成する仕組みです。

１行目だけお手本となるテキストを用意すれば、Excel がその行にある各セルの内容と見比べて、どこのセルのどの部分からデータを持ってくればいいのか自動でパターン判別を行い、それ以降の行でそのパターンを適用した自動入力してくれます。

フラッシュフィルを実行するにもいろいろな経路があります。

  • 「データ」タブ → 「フラッシュフィル」ボタン
  • 「ホーム」タブ → 「フィル」メニュー → 「フラッシュフィル」
  • ドラッグフィル → 「オートフィル」メニュー → 「フラッシュフィル」
  • 自動フラッシュフィル
  • ショートカット：Ctrl+E

それらの詳細な使い方は他のサイト記事を参照してもらうこととして、ここではショートカット（Ctrl+E）を使って、セル内容の連結をしてみます。

  1. 最初の行の空きセルに、結合後のテキスト（お手本）を手入力します
    • セルデータのほか、区切り文字やクオートなど追加を挿入できます
    • セルの数が多い時は、セル範囲をいったんテキストエディタにコピペし、編集してから入力セルに戻します
    • お手本セルは、データセルに隣接している必要があります
  2. そのままフラッシュフィルを実行します
    • ショートカット：　Ctrl+E
  3. 残りの行のセルに自動生成された連結テキストが入力されます
    • お手本が曖昧だとパターンの抽出に失敗したりお誤解釈をすることがあります

f:id:shego:20171007174731p:plain

個人的にフラッシュフィルのことは不勉強で、今回初めてちゃんと使いました。 フラッシュフィルは連結だけでなくいろいろ使い道があって、使いこなせばとても便利な機能ですが、その割に一般にもあまり知られていないような気がします。

おそらく名前がよくなかったのだと思うのですが、どうでしょうか。

【追記】 フラッシュフィルについて詳細は以下の記事を参考にしてください。 www.shegolab.jp

CONCATENATE関数を使ってみる

ところで、悲しいことに筆者の仕事場に導入されている Excel は、いまだに Excel 2007 です。

今でも昔ながらの&演算子を使ってセルを連結する数式をちまちまと書いていたのですが、調べたところ CONCATENATE という関数があって、まさにそのために使うもののようです。

CONCATENATE 関数は複数のパラメータ（最大 255 個）を受け取り、それらを単純に結合した文字列を返します。 CONCATENATE 関数にセルの参照を渡せば、その値が連結されたテキストを作る数式になります

ただ、CONCATENATE 関数では SUM 関数のようなセルの範囲指定ができず、セル参照を一つ一つ入力しなければならないところが難点です。

これを手早くやるコツは、Ctrl キーを押しながらクリック選択をすることです。 Ctrl キーを押したままセルをクリックして選択していけば、次々とセル参照をCONCATENATEに追加できます。

f:id:shego:20171007231952p:plain

さらに、区切り文字など挟みたいときには、それを文字列にしてパラメータ間に挿入します。 あらかじめ「両カンマ付き」の文字列をクリップボードにコピーしておけば、セルの選択（Ctrl+クリック）と貼り付け（Ctrl+V）を交互に行う事で素早く入力できます。

「両カンマ付き」の文字列とは、ダブルクオート(")で囲んだ文字列のさらに両側をカンマ（,）で囲んだものです。 たとえばハイフン（-）区切りにしたければ、,"-",をコピーしておきます。

f:id:shego:20171007232035p:plain

【作業手順】

  1. 区切り文字が必要ならその文字列を両カンマ付きでクリップボードにコピーしておきます。
  2. セルに数式で CONATENATE 関数入力し、開き括弧の後にカーソルがある状態にします
    • ｀＝CONC｀あたりで候補が絞られたらTABで補完できます
  3. Ctrl キーを押したままセルをクリックして選択していきます
    • 選択したセルのアドレスがCONCATENATE関数のパラメータに追加されていきます
    • Ctrl+V で区切り文字を挿入します
  4. 最後にEnterキーまたはTABキーを押します
    • 自動的に閉じ括弧が補完されます

ちなみに、Excel 2016 からは、CONCAT 関数と TEXTJOIN 関数という関数が追加されていて、もう少し便利になっています。

CONCAT 関数は、基本的に従来の CONCATENATE 関数と同じ機能ですが、連結するセルを範囲選択で指定できるように改善されています。

TEXTJOIN 関数も、テキストを連結する機能ですが、区切り文字を指定できるのが CONCATENATE や CONCAT と違うところです。 また、CONCAT関数同様、連結するセルを範囲選択できるようです。

ただし、買い切り版の Excel 2016 では無効になっていて、Office 365 版の Excel 2016 でないと使えません。 Office 365 はクラウド版の Office で、利用するには月額の契約が必要です。

さて CONCATENATE 関数でも、セルの値が単純なテキストであるかぎり、うまくいきます。

しかしセルの値が数値の場合にその表示形式は反映されません。 特に、日付や時刻は数値に戻されてしまい、残念な結果になります。

セルの表示形式を再現するには、TEXT 関数というのを使ってその書式を指定すればいいようですが、そこまでするともう複雑すぎてもう人間が手作業でできることではありません。

　 　

文字列を連結する数式を作成するマクロ

セル内容からテキストを作成する手間を軽減するよう、自動でそれを行うマクロを作成してみました。

本マクロ VBA は、複数の選択セルから連結テキストを生成する「数式」を作成します。

作成される数式は一見複雑なようですが、基本的に CONCATENATE 関数で各セル内容を結合するだけのものです。 各セルの間に区切り文字や追加の文言も追加できるように文字列をはさみ、日付や通貨を含む数値の書式も保持されるよう TEXT 関数も使っています。

連結したいセルを選択後に本マクロを実行すると、ダイアログにセル内容の連結結果のサンプルテキストが提示されるので、必要ならこれを編集することができます。 ただし、フラッシュフィルのように空気を読む事はできませんので、できるのはセル内容の単純な展開だけです。

数式内でのセルの指定は相対参照になっているので、数式を別の行にそのままコピーできます。

Option Explicit

Sub セルテキストを連結する数式の作成()
    Const cInputBoxTypeText = 2
    Const cInputBoxTypeRange = 8
    Dim ret As Variant
    
    If TypeName(Selection) <> "Range" Then
        Beep
        Exit Sub
    End If
    
    Dim selectedCells As Range
    Set selectedCells = Selection.Cells
    
    Dim delim As String
    delim = ","
    ret = Application.InputBox("区切り文字を入力してください（空なら区切り文字なし）", "区切り文字", _
            default:=delim, Type:=cInputBoxTypeText)
    If ret = False Then Exit Sub
    delim = CStr(ret)
    
    Dim sampleText As String
    sampleText = joinRangeText(selectedCells, delim)
        
    ret = Application.InputBox("サンプルテキスト編集してください（最大256文字まで）", "サンプルテキスト", _
            default:=sampleText, Type:=cInputBoxTypeText)
    If ret = False Then Exit Sub
    sampleText = CStr(ret)
    
    Dim dstRng As Range
    On Error Resume Next
    Set dstRng = Application.InputBox("数式を貼り付けるセルを選択してください", "セルの選択", _
            Type:=cInputBoxTypeRange)
    On Error GoTo 0
    If dstRng Is Nothing Then Exit Sub
    If Not selectedCells.Parent Is dstRng.Parent Then
        Beep ' 別シートは無効
        Exit Sub
    End If
    
    Dim templateText As String
    templateText = makeTemplate(selectedCells, sampleText)
    dstRng.FormulaLocal = "=CONCATENATE(" & toList(templateText) & ")"
End Sub

Private Function joinRangeText(rng As Range, ByVal delim As String) As String
    joinRangeText = ""
    Dim c As Range
    For Each c In rng
        If c.text <> "" Then
            joinRangeText = joinRangeText & delim & c.text
        Else
            joinRangeText = joinRangeText & delim & "${" & c.Address(False, False) & "}"
        End If
    Next
    joinRangeText = Replace(joinRangeText, delim, "", 1, 1)
End Function

Private Function makeTemplate(rng As Range, ByVal smpl As String) As String
    Dim tmpl As String
    tmpl = ""
    Dim c As Range
    For Each c In rng
        Dim arr As Variant
        arr = Split(smpl, c.text, 2)
        If UBound(arr) > 0 Then
            tmpl = tmpl & arr(0) & "${TEXT(" & _
                c.Address(False, False) & _
                ",""" & Replace(c.NumberFormatLocal, """", """""") & """)}"
            smpl = arr(1)
        End If
    Next
    tmpl = Replace(tmpl & smpl, "\n", "${CHAR(10)}")
    makeTemplate = tmpl
End Function

Private Function toList(ByVal tmpl As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .pattern = """(?=[^}]*(\$\{|$))"   ' FIXME
        tmpl = .Replace(tmpl, """""")
        .pattern = "(\$\{)(.*?)(\})"
        tmpl = .Replace(tmpl, """,$2,""")
    End With
    toList = """" & tmpl & """"
End Function

【使い方】

  1. 上記 VBA プログラムを標準モジュールにコピー＆ペーストします
  2. 連結したいセルを複数選択します
    • 対象セルは、範囲選択（ドラック選択）で指定しても、個別選択（Ctrl+クリック）で飛び飛びに指定しても問題ありません。
    • セルは選択した順序で見本テキストに適用され、また同じセルを複数回指定しても問題ありません
  3. 本マクロを実行します
  4. 「区切り文字入力」ダイアログで区切り文字を指定します
    • カンマやスーペース、その他任意の文字列を項目区切りとしてテキストボックスに入力します（デフォルトはカンマ ,）
    • 入力を空にして区切り文字なしにする事も出来ます（直結）
    • 「OK」ボタンを押して続行します
  5. 「サンプルテキスト編集」ダイアログで、連結結果のサンプルテキストが提示されます
    • 単純にセル内容を結合した結果のテキストが「見本」としてテキストボックスに表示されます
    • 必要があれば、ここでサンプルテキストを編集します
      • 項目をカッコやクオートで囲んだり、前後にメッセージ文言を追加できます
      • Excel 入力ダイアログの制限で、最大256文字までか入力できません
      • セル内容（値）の文字列自体は変更しないでそのままにしておいてください
    • Excel 入力ダイアログはアローキーが使えないなど編集しづらいので、いったんテキストエディタなどにとって編集することをお勧めします
    • 見本テキストがイメージ通りになったら「OK」ボタンを押します
  6. 「セルの選択」ダイアログで連結テキストの展開先のセルを指定します
    • 「OK」ボタンを押します
  7. 指定先のセルに数式が入力され、連結テキストが表示されます

【技術者向けメモ】

見本テキストには、選択セルだけでなく、変数展開風の＄{...}という形式で Excel の式を直接埋め込む事ができます。

ただし単純なテキスト置換なので数式が壊れることもあります。選択セルの値によっては、式内の文字列に引っかかって数式が壊れることがあるのでご注意。

\nは折り返し(CHAR(10))に置換されます。

例）

${A2 / $A$1},　${ROWS() -1},　${ASC(D2)},　${CHAR(10)},　'${REPLACE(B2, "'", "''")}', ${IFF(E2, E2, "NULL")}, ${TEXT(A1, "yyyy-mm-dd hh:mm:ss")}

まとめ

  • セル内容の連結には「フラッシュフィル」を使うのが便利です
  • 連結テキストを数式にするには&演算子より CONCATENATE 関数の方が簡単です
  • 数式でセルの表示形式を再現するのは手間なので、これを自動化するマクロをつくりました

本記事の内容は Excel 2013 で確認しています。

関連記事

www.shegolab.jp

www.shegolab.jp