Excelで該当する数値をカンマ区切りで連結、連続する数値は始まりと終わりをハイフンでつなぐ
前置き
Yahoo!知恵袋に次のような質問がありました。
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q12321961622
taihenda****さんのVBAとpietaさんのワークシート式の回答が付いています。
このLAMBDAを使うワークシート式の回答が理解できなかったので、Copilotに解説してもらいました。
また、Power Queryとメジャーの式もCopilotに作ってもらいました。
本記事では、ワークシート式、Power Query、メジャーを紹介します。VBA、ワークシート式、Power Query、メジャー(DAX)を比較してみてください。
1. 入力
左上の氏名のセルアドレスはA1です。合算は空欄です。
| 氏名 | AMPM | 合算 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 省略 | 29 | 30 | 31 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 斎藤隆 | AM | 1 | 1 | 1 | |||||||||
| PM | 1 | 1 | 1 | 1 | |||||||||
| 佐々木裕 | AM | 2 | 1 | 1 | 1 | ||||||||
| PM | 1 | 1 | 1 |
2. 出力
| 氏名 | AMPM | 合算 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 省略 | 29 | 30 | 31 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 斎藤隆 | AM | AM/1-2,6 | 1 | 1 | 1 | ||||||||
| PM | PM/3-5,30 | 1 | 1 | 1 | 1 | ||||||||
| 佐々木裕 | AM | AM/2-3,30-31 | 2 | 1 | 1 | 1 | |||||||
| PM | PM/3,5,31 | 1 | 1 | 1 |
合算を出力します。Power Queryとメジャーは、合算だけ表示し、右の日は表示しないことにします。
3. ワークシート式
手順
セルアドレスC2に次の式を入力します。そして、C2をC3:C5にコピーします。
=LET(
数列, FILTER(SEQUENCE(, 31), D2:AH2),
結合, "," & TEXTJOIN(",",, 数列),
連結, REDUCE(
結合,
数列,
LAMBDA(x,y,
SUBSTITUTE(
SUBSTITUTE(
x,
"-" & y-1 & ",",
"-"
),
"," & y-1 & ",",
"," & y-1 & "-"
)
)
),
SUBSTITUTE(連結, ",", B2 & "/", 1)
)式の解説
式の全体構造
=LET(
数列, ...,
結合, ...,
連結, ...,
最終出力, ...
)LET関数で中間変数を定義しながら、最終的に整形された文字列を返しています。
🔢 ステップ1：日付列から値のある列番号を抽出
数列, FILTER(SEQUENCE(, 31), D2:AH2)SEQUENCE(, 31) → {1,2,3,...,31} の横ベクトルを生成。
FILTER(..., D2:AH2) → 対応するセル（D2:AH2）に値がある列だけを抽出。
例：D2=1, E2=1, F2=空, G2=空, H2=1 → {1,2,5}🔗 ステップ2：数列をカンマで連結
結合, "," & TEXTJOIN(",",, 数列)TEXTJOIN(",",, 数列) → "1,2,6" のような文字列に変換。
先頭にカンマを追加 → ",1,2,6"（後の置換処理のため）
🔁 ステップ3：連続する数値を範囲に変換
連結, REDUCE(この部分が連続範囲の検出と変換のコアロジックです。
結合,
数列,
LAMBDA(x,y,
SUBSTITUTE(
SUBSTITUTE(x, "-" & y-1 & ",", "-"),
"," & y-1 & ",", "," & y-1 & "-"
)
)
)
仕組み：
REDUCEで数列を1つずつ処理。
直前の数（y-1）が "-,y," または ",y," の形で存在する場合、それを "-,y" や "y-" に置換。
これにより、たとえば ",1,2,3,6" → ",1-3,6" に変換されます。
🔁 REDUCE の構造
REDUCE(
初期値（結合文字列）,
数列,
LAMBDA(x, y, ...置換処理...)
)
🧪 REDUCEの1ステップごとの変化の例
🔹 初期値
x = ",1,2,3,6,7,9"
🔹 y = 1
y-1 = 0 → "-0," や ",0," は存在しない → 変化なし
x = ",1,2,3,6,7,9"
🔹 y = 2
y-1 = 1
"-1," → 存在しない
",1," → あり → ",1," → ",1-" に置換
x = ",1-2,3,6,7,9"
🔹 y = 3
y-1 = 2
"-2," → あり → "-2," → "-" に置換
",2," → なし（すでに "1-2" に変換済み）
x = ",1-3,6,7,9"
🔹 y = 6
y-1 = 5 → "-5," や ",5," は存在しない → 変化なし
x = ",1-3,6,7,9"
🔹 y = 7
y-1 = 6
",6," → あり → ",6," → ",6-" に置換
x = ",1-3,6-7,9"
🔹 y = 9
y-1 = 8 → "-8," や ",8," は存在しない → 変化なし
x = ",1-3,6-7,9"
✅ 最終結果
",1-3,6-7,9"
🧠 補足：なぜ2段階の SUBSTITUTE？
SUBSTITUTE(
SUBSTITUTE(x, "-" & y-1 & ",", "-"),
"," & y-1 & ",", "," & y-1 & "-"
)前段："-2," → "–" に変換 → "1-2,3" → "1-3"この2段階により、前後の連続性を検出して、範囲を拡張することができます。
後段：",2," → "1,2,3" → "1-2,3"
🧾 ステップ4：先頭のカンマをAM/PMで置換
SUBSTITUTE(連結, ",", B2 & "/", 1)最初のカンマ , を B2（AMまたはPM）とスラッシュ / に置換。
例：",1-2,6" → "AM/1-2,6"
✅ 最終出力例
たとえば、D2:AH2 に値がある日が 1, 2, 6 の場合：
数列 → {1,2,6}
結合 → ",1,2,6"
連結 → ",1-2,6"
最終出力 → "AM/1-2,6"
💡ポイントまとめ
| ステップ | 処理内容 | 結果例（1,2,6が入力） |
|---|---|---|
| 数列 | 値のある日を抽出 | {1,2,6} |
| 結合 | カンマ連結 | ",1,2,6" |
| 連結 | 連続範囲に変換 | ",1-2,6" |
| 出力 | AM/PMを付加 | "AM/1-2,6" |
4. Power Query
データが存在する日付のみに絞り込んでソート後に連番を振り[日付]-[連番]を計算すると、連続している日付の差分が同じ値になるので、差分でグループ化して日付を結合します。
- データの取得
- 集計ブックで[データ]-[データの取得v]-[ファイルから>]-[Excelブックから]でファイルを選択し[インポート]、シートを選択し[データの変換]でPower Queryエディタに取込む。
- 右の名前を"まとめpq"に変更する。
- 右の"変更された型"の左の×をクリックして削除。
- 氏名の穴埋め
"Column1"列を選択し[変換]-[フィルv]-[下へ]。 - ヘッダー化
- [変換]-[1行目をヘッダとして使用v]-[1行目をヘッダとして使用]。
- 右の"変更された型"の左の×をクリックして削除。
- 日をピボット解除 ⇒ データベース形式になる
- "氏名"、"AMPM"、"合算"列を選択し、[変換]-[列のピボット解除v]-[その他の列のピボット解除]。
- 数式バーの"属性"を"日"、"値"を"回数"に変更。
-
型設定
表を選択し、Ctrl+aで全列を選択し[変換]-[データ型の検出]。適切でない型の列を選択し、[変換]-[データ型]で適切な型を選択し[現在のものを置換]。 -
"回数"が無い行を削除
"回数"列の▼-[空の削除]。 -
"氏名"、"AMPM"、"日"のソート
- "氏名"、"AMPM"、"日"の順に▼をクリックし、[昇順で並び替え]。
- 数式バーを次のように変更。（Table.Buffer()で囲む）
= Table.Sort(フィルターされた行,{{"氏名", Order.Ascending}, {"AMPM", Order.Ascending}, {"日", Order.Ascending}})
↓
= Table.Buffer(Table.Sort(フィルターされた行,{{"氏名", Order.Ascending}, {"AMPM", Order.Ascending}, {"日", Order.Ascending}}))-
"氏名"と"AMPM"のグループ化（同じ行の日をまとめる）
"氏名"と"AMPM"列を選択し[ホーム]-[グループ化]で、次のように入力し[OK]。
新しい列名：カウント
操作：すべての行 -
"日"の展開
"カウント"列の矢印アイコンをクリックし、"日"のみチェックし、"元の列名をプレフィックスとして使用します"のチェックを外し[OK]。 -
連番の追加
[列の追加]-[インデックス列v]-[0から]。 -
差分列の追加（連続日が同じ差分値になる）
[列の追加]-[カスタム列]で次のように入力し[OK]。
新しい列名：差分
カスタム列の式：= [日] - [インデックス] -
"氏名"、"AMPM"、"差分"でグループ化（連続する日(差分)をグループ化⇒連続日が1行になり、日の最小と最大を開始日と終了日列にする）
"氏名"、"AMPM"、"差分"列を選択し[ホーム]-[グループ化]-[集計の追加]で、次の様に入力し[OK]。
新しい列名：操作：列
開始日：最小：日
終了日：最大：日 -
"開始日-終了日"のように連結したカスタム列を追加
[列の追加]-[カスタム列]で次のように入力し[OK]。
新しい列名：カスタム
カスタム列の式：= if [開始日] = [終了日] then Text.From([開始日]) else Text.From([開始日]) & "-" & Text.From([終了日]) -
"氏名"と"AMPM"でグループ化（開始日-終了日のカスタムをまとめる）
"氏名"と"AMPM"列を選択し[ホーム]-[グループ化]で、次のように入力し[OK]。
新しい列名：カウント
操作：すべての行 -
日を結合するカスタム列を追加
[列の追加]-[カスタム列]で次のように入力し[OK]。
新しい列名：合算
カスタム列の式：= [AMPM] & "/" & Text.Combine([カウント][カスタム], ", ") -
作業列を削除
"カウント"列を選択し[ホーム]-[列の削除v]-[列の削除]。 -
型の設定
"合算"列を選択し[変換]-[データ型]-[テキスト]。 -
Excelに読み込み
[ホーム]-[閉じて読み込むv]-[閉じて次に読み込む]で"テーブル"を選択し[OK]。
日毎の列が必要であれば、ピボット解除しないクエリを作成し、上記クエリにマージします。
5. メジャー
データが存在する日付のみに絞り込んでRANKXで連番を振り[日付]-[連番]を計算すると、連続している日付の差分が同じ値になるので、差分でグループ化して日付を結合します。最初にPower Queryで入力表をデータベース形式に変換します。
- データの取得
- 集計ブックで[データ]-[データの取得v]-[ファイルから>]-[Excelブックから]でファイルを選択し[インポート]、シートを選択し[データの変換]でPower Queryエディタに取込む。
- 右の名前を"まとめm"に変更する。
- 右の"変更された型"の左の×をクリックして削除。
- 氏名の穴埋め
"Column1"列を選択し[変換]-[フィルv]-[下へ]。 - ヘッダー化
- [変換]-[1行目をヘッダとして使用v]-[1行目をヘッダとして使用]。
- 右の"変更された型"の左の×をクリックして削除。
- 日をピボット解除 ⇒ データベース形式になる
- "氏名"、"AMPM"、"合算"列を選択し、[変換]-[列のピボット解除v]-[その他の列のピボット解除]。
- 数式バーの"属性"を"日"、"値"を"回数"に変更。
-
型設定
表を選択し、Ctrl+aで全列を選択し[変換]-[データ型の検出]。適切でない型の列を選択し、[変換]-[データ型]で適切な型を選択し[現在のものを置換]。 -
Excelに読み込み
[ホーム]-[閉じて読み込むv]-[閉じて次に読み込む]で"ピボットテーブル"と"このデータをデータモデルに追加する"を選択し[OK]。これでExcelに戻ります。 -
ピボットテーブルの書式設定
- [ピボットテーブル分析]-[ピボットテーブルv]-[オプションv]-[レイアウトと書式]で"更新時に列幅を自動調整する"のチェックを外す。
- [デザイン]-[レポートのレイアウトv]-[表形式]。[デザイン]-[総計v]-[行と列の集計を行わない]。[デザイン]-[小計]-[小計を表示しない]。
- メジャーの追加
"ピボットテーブルのフィールド"の'まとめm’を右クリックし、[メジャーの追加]で次のように入力し[OK]。
メジャーの名前：M合算
数式：
=VAR CurrentName = MAX('まとめm'[氏名])
// ピボットテーブルのコンテキストから現在の氏名を取得
VAR CurrentAMPM = MAX('まとめm'[AMPM])
// 同様に現在のAMPM（午前・午後）を取得
VAR ValidDays =
CALCULATETABLE (
SELECTCOLUMNS (
'まとめm',
"Day", VALUE('まとめm'[日])
),
'まとめm'[氏名] = CurrentName,
'まとめm'[AMPM] = CurrentAMPM,
'まとめm'[回数] <> BLANK()
)
// 氏名＋AMPMでフィルターし、回数が空でない日だけを抽出
VAR IndexedDays =
ADDCOLUMNS (
ValidDays,
"Index", RANKX(ValidDays, [Day],, ASC)
)
// DayNum に対して昇順のインデックスを付与（1,2,3,...）
VAR WithDiff =
ADDCOLUMNS (
IndexedDays,
"GroupKey", [Day] - [Index]
)
// DayNum - Index の差分で連続グループを判定
// 連続する日付は差分が同じになる → GroupKeyでグループ化可能
VAR Grouped =
GROUPBY (
WithDiff,
[GroupKey],
"StartDay", MINX ( CURRENTGROUP(), [Day] ),
"EndDay", MAXX ( CURRENTGROUP(), [Day] )
)
// GroupKeyごとに、最小・最大のDayNumを抽出 → 開始日・終了日
VAR Result =
ADDCOLUMNS (
Grouped,
"Formatted",
IF (
[StartDay] = [EndDay],
FORMAT([StartDay], "0"),
FORMAT([StartDay], "0") & "-" & FORMAT([EndDay], "0")
)
)
// 開始日＝終了日なら単独日、異なれば "開始-終了" の形式で整形
RETURN
CurrentAMPM & "/" & // 先頭にAM/PMを付ける
CONCATENATEX ( // ピボットテーブルのコンテキストに含まれる'Result'[Formated]を","で結合する
Result,
[Formatted],
", ",
[StartDay], ASC
)
- ピボットテーブルの項目
"ピボットテーブルのフィールド"で次のように項目をドラッグする。
行：'まとめm'[氏名]、'まとめm'[AMPM]。Σ値：[M合算]。
6. VBAマクロ
'https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q12321961622
'2025/11/07 by taihenda
Sub test()
Dim i As Long, j As Long, k As Long, mstr As String, ary() 'kの初期値は0
Columns(3).ClearContents
Columns(3).NumberFormat = "@"
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row + 1 '行ループ(i=row)
For j = 4 To Cells(1, Columns.Count).End(xlToLeft).Column '日のループ(j=Column,日=j-3)
If Cells(i, j) <> "" And (j = 4 Or Cells(i, j - 1) = "") And Cells(i, j + 1) <> "" Then
ReDim Preserve ary(k)
ary(k) = j - 3 & "-" '連続開始日(d-)
k = k + 1
ElseIf Cells(i, j) <> "" And Cells(i, j - 1) <> "" And Cells(i, j + 1) = "" Then
ReDim Preserve ary(k)
ary(k) = "-" & j - 3 '連続終了日(-d)
k = k + 1
ElseIf Cells(i, j) <> "" And Cells(i, j - 1) = "" And Cells(i, j + 1) = "" Then
ReDim Preserve ary(k)
ary(k) = j - 3 '単独日(d)
k = k + 1
End If
Next
If k > 0 Then
Cells(i, 3) = Join(ary, ",") '","で結合
Cells(i, 3) = Cells(i, 2) & "/" & Replace(Cells(i, 3), "-,-", "-") '開始と終了が","で結合され"-,-"となるので"-"で置換
Erase ary
k = 0
End If
Next
End Sub7. サンプル
C:\Users\Public\Documents に保存してください。Yahoo!知恵袋のワークシート式の別解も入っています。
8. VBA、ワークシート式、Power Query、メジャー(DAX)の比較
- VBA：一番理解しやすいロジックだと思います。
- ワークシート式：一番短いです。SUBSTITUTEのロジックは、思いつきにくいです。コメントが書けないので、可読性が悪いです。Excel LabsのAFEを使うと、可読性が改善します。
- Power Query：差分でグループ化するロジックは、思いつきにくいです。でも、可読性は良いと思います。Power Queryエディタで、処理の経過を見ることができることも、可読性が良い点になっています。
- メジャー：Power Queryのロジックとほぼ同じですが、RANKX()やCURRENTGROUP()などが思いつきにくいです。ADDCOLUMNSで作業列を追加していく手法は、データベースの処理で見られる手法なので参考にしても良いかも知れません。
このような式は、プログラマーで無いと発想できないと思います。当方は素人なので、ChatGPTとGeminiとCopilotにメジャーの作成を依頼したところ、回答に辿り着きませんでした。そこで、Copilotにワークシート式の回答を伝え、Power Queryの回答を依頼したところ、正解が出ました。その正解を元にメジャーを依頼したら、メジャーも正解に辿り着きました。この課題に対して、ChatGPTは今一つでした。
最近のコメント