エクセルのワークシート関数

質問

=E16&" / "&E17&" / "&E18&" / "&E19&" / "&E20&" / "&E21&" / "&E22&" / "&H16&" / "&H17&" / "&H18&" / "&H19&" / "&H20&" / "&H21&" / "&H22&" / "&L16&" / "&L17&" / "&L18&" / "&L19&" / "&L20&" / "&L21&" / "&L22

ワークシートのセル
E16～E22
H16～H22
L16～L22
にある値を、 / で結合して表示する関数なのですが、もっと簡単に記述する方法はないでしょうか？

回答No.6

cj_mover · ベストアンサー

こんにちは。お邪魔します。

エクセルの関数や数式で、ということだと、
文字列の連結については、何れにしても、セルをひとつずつ指定して連結するしかないです。
ちょっと回答を躊躇いましたが、
自分なら迷わずユーザー定義関数（ＵＤＦ）での対応を選ぶでしょうから、
２例、紹介してみようと思います。
同じ記述を繰り返すExcel数式の作成って、モチベーション下がっちゃって、
手が止まっちゃうんですよね。
そういったこともあって私の場合はＵＤＦ書いた方が、早く仕事が終わことが多いです。
迷わず、というよりは、迷いたくないから、の方が本音に近いですね。
後は好みの問題でしょうか。
ご質問のテーマとしては、ワークシート関数、ということなので、
いくら簡単に書けるとはいえ、ＵＤＦはお望みでないのかも知れませんが、
その場合は、ご参考まで。

二つめのＵＤＦについて、お求めの仕様によっては、
　　　　　　For nR = 1& To UBound(vTmp)
　　　　　　　　For nC = 1& To UBound(vTmp, 2)
この部分の For Next のネストを入れ替える方がいいのかも知れません。
　　　　　　For nC = 1& To UBound(vTmp, 2)
　　　　　　　　For nR = 1& To UBound(vTmp)
今回の例示では、どっちでも結果は一緒ですけれど。

第一引数のセル範囲に、複数の領域を持つセル範囲を指定する時は、
セル参照を括弧で括る必要があります（◆数式使用例、参照）。

' ' 簡易限定版。単列複数セルの組み合わせを指定すること。単一セル、複数列、はエラー。
' ' ◆数式使用例　　=JoinTextP((E16:E22,H16:H22,L16:L22)," / ")
Function JoinTextP(ByVal Target As Range, Optional ByVal Delimiter As String)
　　Dim vTmp As Variant
　　Dim r As Range
　　Dim sBuf As String

　　For Each r In Target.Areas
　　　　vTmp = r.Cells.Value
　　　　vTmp = Application.Transpose(vTmp)
　　　　sBuf = sBuf & Delimiter & Join(vTmp, Delimiter)
　　Next

　　JoinTextP = Mid$(sBuf, Len(Delimiter) + 1)
End Function

' ' 高性能汎用版。セル範囲ならば、どのような指定にも対応。計算速い。
' ' ◆数式使用例　　=JoinText((E16:E22,H16:H22,L16:L22)," / ")
Function JoinText(ByVal Target As Range, Optional ByVal Delimiter As String)
　　Dim vTmp As Variant
　　Dim r As Range
　　Dim sBuf As String
　　Dim nDlmLen As Long
　　Dim nPos As Long
　　Dim nR As Long
　　Dim nC As Long

　　nDlmLen = Len(Delimiter)
　　sBuf = String$(Target.Count * 255, vbCr)

　　nPos = 1&
　　For Each r In Target.Areas
　　　　vTmp = r.Cells.Value
　　　　If IsArray(vTmp) Then
　　　　　　For nR = 1& To UBound(vTmp)
　　　　　　　　For nC = 1& To UBound(vTmp, 2)
　　　　　　　　　　Mid(sBuf, nPos) = Delimiter
　　　　　　　　　　nPos = nPos + nDlmLen
　　　　　　　　　　Mid(sBuf, nPos) = vTmp(nR, nC)
　　　　　　　　　　nPos = InStr(nPos + 1&, sBuf, vbCr)
　　　　　　　　Next nC
　　　　　　Next nR
　　　　Else
　　　　　　Mid(sBuf, nPos) = Delimiter
　　　　　　nPos = nPos + nDlmLen
　　　　　　Mid(sBuf, nPos) = vTmp
　　　　　　nPos = InStr(nPos + 1&, sBuf, vbCr)
　　　　End If
　　Next

　　JoinText = Mid$(sBuf, nDlmLen + 1&, nPos - nDlmLen)
End Function

お礼

お礼が遅くなり申し訳ありませんでした。
参考にさせていただきます。

回答No.5

MackyNo1

No4の回答の補足です。

いま一つ操作の目的やデータの種類が不明なので、効率的な操作が異なりますが、同じような操作を連続して行いたいなら、数式は特定のセルに入力しておき、F9キーで表示した結合文字列の必要な部分を数式バーからコピーして、Escキーで数式を戻しておき（今後も使用できるように）、M1セルでCtrl+Vで必要な文字列部分を貼り付ける操作を行った方が良いかもしれません。

ちなみに、目的にもよりますが、単純に文字列をそのまま結合するなら（区切り文字を入れなくてもよいなら）PHONETIC関数（この場合は関数で表示したデータは使用できません。またふりがな情報を一括削除する必要があります）やフィル（英数字なら空白で区切られる）の機能を利用することもできます。

お礼

お礼が遅くなり申し訳ありませんでした。
参考にさせていただきます。

回答No.4

MackyNo1

関数との合わせ技でそれらの（多数の）セルを単純に結合するなら、以下のような方法もあります。

どこかのセル（例えばM1セル）に以下の式を入力します。

=CONCATENATE(E16:E22,"/",H16:H22,"/",L16:L22)

このセルの数式バーで数式全体を選択しF9キーを押します。

この表示された数式内容の先頭の「={"」と末尾の「"}」を削除し、表示したいセルに以下の式を入力します。
=SUBSTITUTE(M1,""";""","/")

お礼

お礼が遅くなり申し訳ありませんでした。
参考にさせていただきます。

回答No.3

noname#204879

１．セル F16 に式 =E16 を入力
２．セル F17 に式 =F16&"/"&E17 を入力して、此れを下方にズズーッと
　　(セル F22 まで)ドラッグ＆ペースト
３．範囲 F16:F22 を[コピー]して、此れをセル I16 および M16 に
　　[貼り付け]
４．任意のセルに 式 =F22&"/"&I22&"/"&M22 を入力した結果が、
　　貴方が求めるもの

お礼

お礼が遅くなり申し訳ありませんでした。
参考にさせていただきます。

回答No.2

tom04

こんばんは！
どうしても関数で！というコトであればNo.１さんもおっしゃっている通り
地道にお示しの数式のようにやるしかないと思います。

他の方法としてはVBAになってしまいますが、一例です。
結果をA1セルに表示する場合のコードです。
範囲内に空白セルがあっても対応できるようにしています。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long, myArray, str As String
myArray = Array(5, 8, 12) '←E列・H列・L列の列番号
For k = 0 To UBound(myArray)
For i = 16 To 22
If Cells(i, myArray(k)) <> "" Then
str = str & Cells(i, myArray(k)) & " / "
End If
Next i
Next k
Range("A1") = Left(str, Len(str) - 3) '←「A1」部分は実際に表示したいセル番地に！
End Sub 'この行まで

※　関数でないのでデータ変更があるたびにマクロを実行する必要があります。m(_ _)m

お礼

お礼が遅くなり申し訳ありませんでした。
参考にさせていただきます。

回答No.1

MackyNo1

>・・・にある値を、 / で結合して表示する関数なのですが、もっと簡単に記述する方法はないでしょうか？

基本的に関数でご希望の操作をするなら、提示された式が最も簡単な数式になります。

しかし、提示された数式はかなり特殊な形式でデータを表示しているように思うのですが、具体的にどのような目的でそのような結合した文字列をつくりたいのでしょうか？
目的によっては、関数ではなくExcelの他の機能を利用するほうが簡単に文字列結合ができる方法もありますので、具体的にどのようなデータ（例：英数文字、かな漢字入りの文字など）をどのように（例：区切りはスラッシュに限定されないなど）結合したいのか補足説明されるとよいと思います。

お礼

早速ありがとうございます。

> 具体的にどのような目的でそのような結合した文字列をつくりたいのでしょうか？

計算上求められたデータ（文字列）を限られたスペース（結合したセル範囲）に折り返して表示させるため、データを一個ずつ改行しないで一つの文字列として / （半角スペース+/+半角スペース）で区切って結合させたいのです。