Excelで「1,2,3,4,5」を「1-5」とまとめることはできますか？

Excelで「1,2,3,4,5」のような小→大に連続した数字（自然数）を、
「1-5」のようにまとめることはできますか？

実際は「1,2,3,4,5,10,12,14,16,18,20,26,27,28,29,30」みたいに混ざっていて、
連続している部分だけ「1-5,10,12,14,16,18,20,26-30」のようにまとめたいです。

No.4 ベストアンサー 回答者： mike_g 回答日時： 2021/09/27 21:13

失礼を承知で書きますが、m(_._)m

ひょっとして貴方は YAHOO!知恵袋における 1050224079さんではありませんか？
なぜなら、貴方の質問の内容がが９日前の
https://detail.chiebukuro.yahoo.co.jp/qa/questio …
と瓜二つだから。
でもホントにそうなら、不思議なことに思います。
なぜなら、そこで貴方がベストアンサー(BA)と選定した回答者の kabuto_yhさんのとおりにやれば、ここで再質問することもあるまいに、と思うからです。

当該BAに私が忠実にやってみた結果を添付図に示しておきますネ。

貴方のお蔭で私が勉強させてもらったことを感謝する次第です。

この回答へのお礼

当該質問は私ではないのですが、
教えていただいたリンクの内容でうまくできました。
ありがとうございました。

お礼日時：2021/09/30 20:47

No.8 回答者： bonaron 回答日時： 2021/09/29 14:29

Qchan1962 さんの回答をベースに改変しました。

変数を使い回したため分かりにくいかも。

Function myFunc(trg As String) As String
Dim i As Long
Dim strQ, strA, tmp
Dim iMax As Long
Dim ara

strQ = Split(trg, ",")
iMax = strQ(UBound(strQ))
ReDim ara(0 To iMax) As String
On Error Resume Next
For i = 0 To iMax
ara(i) = " "
Next
For i = 0 To UBound(strQ)
ara(strQ(i)) = "-" & strQ(i)
Next
strA = Join(ara, "")
strA = Replace(strA, " -", " ")
strA = WorksheetFunction.Trim(strA)
strQ = Split(strA, " ")
For i = 0 To UBound(strQ)
If strQ(i) Like "*-*-*" Then
tmp = strQ(i)
strQ(i) = Left(tmp, InStr(tmp, "-")) & Mid(tmp, InStrRev(tmp, "-") + 1)
End If
Next
strA = Join(strQ, ",")
myFunc = strA
End Function


ワークシートに記述するほか
他から呼び出すこともできます。

Debug.Print MyFunc("1,2,3,4,5,10,12,14,16")

No.7 回答者： d-q-t-p 回答日時： 2021/09/29 14:08

したいやりたいばかりで バージョンすら書かない人は困りますね。

=LET(_val,"-9,"&A1&",-9",_arry,FILTERXML("<x><y>"&SUBSTITUTE(_val,",","</y><y>")&"</y></x>","//y"),_cnt,COUNT(_arry),_ary1,INDEX(_arry,SEQUENCE(_cnt-2)),_ary2,INDEX(_arry,SEQUENCE(_cnt-2,,2)),_ary3,INDEX(_arry,SEQUENCE(_cnt-2,,3)),IFERROR(SUBSTITUTE(TEXTJOIN(",",,IF((_ary1+1=_ary2)*(_ary2+1<>_ary3),"-","")&IF((_ary1+1=_ary2)*(_ary2+1=_ary3),"",_ary2)),",-","-"),""))

この回答へのお礼

バージョンを書くべきなんですね。。
次回以降、気をつけます。
教えていただいてありがとうございました。

お礼日時：2021/09/30 20:47

No.6 回答者： Qchan1962 回答日時： 2021/09/28 01:51

連投すみません。

#2＃５です
取り敢えず、最後の文字が数値に変えられない場合は文字そのままが書き込まれますが、ご質問の範囲の仕様で
スプリット最後の値を　strA = strA & strQ(UBound(strQ))で加えていますのでループを配列最後まで、ではなく下記の様にしてください。

For i = 0 To UBound(strQ) - 1

なお、数字に変えられない文字入力がある場合、VBEのオプション全般でエラートラップにおいてエラー発生時に中断を選択している場合、#VALUE!が返ります。

この回答へのお礼

ご回答ありがとうございました。
私の能力ではまったく理解できませんでした。
VBAの勉強をしないと……
ゆっくり学んで理解していきたいと思います。
ありがとうございました。

お礼日時：2021/09/30 20:47

No.5 回答者： Qchan1962 回答日時： 2021/09/27 21:51

furfur様　スレッドをお借りします。

こんばんは、tatsumaru77様
検証ありがとうございます。
やはり、デバッグは時間をかけてやらないと私のスキルではいけない事を
あらためて認識しました。。。

初め（1，2番目）の文字列は気が付いたのですが最後の文字も安易に
strQ(UBound(strQ))にしてしまってました。
UBound(strQ)の時、抜けるとかIf Right(strA, 1) <>を実行しない処理が必要なのかな、、　今Excelで検証できないので申し訳ありません。

＃１，＃２のFunctionコードは参考程度として
ユーザー定義関数を作成する方法もある事を提案します。

具体的コードについては、スレッドが閉じなければ、就寝前（25時以降）に今一度確認したいと思います。（今見ると違う方法がありそうです）

No.3 回答者： tatsumaru77 回答日時： 2021/09/27 20:33

Qchan1962様

よこから失礼いたします。
MyTESTをこちらで試してみました。
入力が　3,4,8　の時
出力が　3-4,8-8
になります。
正しくは　3-4,8 ではないでしょうか。

No.2 回答者： Qchan1962 回答日時： 2021/09/27 19:37

すみません

＃１には間違え(バグ）があるので訂正させてください
数字でない文字がある場合無視されます。

Function myTEST(trg As Range)
Dim i As Integer
Dim strQ, strA, tmp
strQ = Split(trg.Text, ",")
For i = 0 To UBound(strQ)
On Error Resume Next
tmp = CInt(strQ(i))
If IsNumeric(strQ(i + 1)) Then
If tmp = CInt(strQ(i + 1)) - 1 Then
If Right(strA, 1) <> "-" Then strA = strA & tmp & "-"
Else
If strA = "" Then strA = tmp & "," Else strA = strA & tmp & ","
End If
Else
If Err.Number <> 0 Then Err.Number = 0
End If
Next
strA = strA & strQ(UBound(strQ))
myTEST = strA
End Function

上記の場合
表示したいセルに　=myTEST(元のセル)

参考：ユーザー定義関数の作り方
https://valmore.work/excel-vba-function/

m(_ _)m

No.1 回答者： Qchan1962 回答日時： 2021/09/27 18:46

こんばんは、

関数をあまりよく知らないのですが、
シート関数では、難しそうですね、、多分

ユーザー関数を作成するかVBAで処理するかで・・

参考　ユーザー関数
文字が含まれている場合の処理対応は考えていません。
単純に省かれます。

Function myTEST(trg As Range)
Dim i As Integer
Dim strQ, strA, tmp
Dim flg As Boolean
strQ = Split(trg.Text, ",")
For i = 0 To UBound(strQ)
tmp = CInt(strQ(i))
On Error Resume Next
If tmp = CInt(strQ(i + 1)) - 1 Then flg = True Else flg = False
If flg = True Then
If Right(strA, 1) <> "-" Then strA = strA & tmp & "-"
Else
If strA = "" Then strA = tmp & "," Else strA = strA & tmp & ","
If Err.Number <> 0 Then flg = False: Err.Number = 0
End If
Next
myEnd:
strA = strA & strQ(UBound(strQ))
myTEST = strA
End Function

意味不明な場合は無視してください。