エクセルで値：〇～〇(数字範囲)の数字を全てテキスト化する方法を教えてください。

Question

下記の元データを完成データの形に打ち出せる関数などあれば、教えて頂きたいです。

【元データ】

【完成希望データ】

上記の元データの１行目の場合、下記のように縦にテキストで羅列させたい。

Answer 1

とりあえず、Power Queryを使用する場合の例を挙げておきます。

• あらかじめ【元データ】の範囲を（先頭行をテーブルの見出しとして）テーブルに変換しておく。以下、このテーブルの名前を[テーブル1]とする。
• [データ]タブ -> [データの取得と変換]グループ -> [データの取得] -> [その他のデータソースから] -> [空のクエリ]をクリックし、Power Query エディターを起動する。
• [ホーム]タブ -> [クエリ]グループ -> [詳細エディター]をクリックし、以下のようなクエリを記述する（今回はコピーアンドペーストで可）。

let
    Source = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"日付", type date}, {"顧客名", type text}, {"製品名", type text}, {"数量", Int64.Type}, {"M", type text}, {"C", type text}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"M", each Text.BeforeDelimiter(_, "-"), type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Extracted Text Before Delimiter", "インデックス", 0, 1, Int64.Type),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Index",{{"M", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"M", "Mの開始値"}}),
    #"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Renamed Columns", {{"C", each Text.BeforeDelimiter(_, "-"), type text}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter1",{{"C", Int64.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"C", "Cの開始値"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "レコード別加算値", each List.Numbers(0,[数量])),
    #"Expanded {0}" = Table.ExpandListColumn(#"Added Custom", "レコード別加算値"),
    #"Added Custom1" = Table.AddColumn(#"Expanded {0}", "新しい数量", each 1),
    #"Inserted Addition" = Table.AddColumn(#"Added Custom1", "加算", each [Mの開始値] + [レコード別加算値], type number),
    #"Changed Type3" = Table.TransformColumnTypes(#"Inserted Addition",{{"加算", type text}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type3",{{"加算", "M"}}),
    #"Inserted Addition1" = Table.AddColumn(#"Renamed Columns2", "加算", each [Cの開始値] + [レコード別加算値], type number),
    #"Changed Type4" = Table.TransformColumnTypes(#"Inserted Addition1",{{"加算", type text}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Changed Type4",{{"加算", "C"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns3",{{"インデックス", Order.Ascending}, {"レコード別加算値", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"数量", "Mの開始値", "Cの開始値", "インデックス", "レコード別加算値"}),
    #"Renamed Columns4" = Table.RenameColumns(#"Removed Columns",{{"新しい数量", "数量"}})
in
    #"Renamed Columns4"

• 構文エラーがないことを確認できたら[完了]ボタンをクリックする。
• プレビューの内容に問題がなければ、[クエリの設定]ウィンドウよりそのクエリを任意の名前に変更し、[ホーム]タブ -> [閉じる]グループ -> [閉じて読み込む]をクリックする。

Answer 2

VBAの方がわかりやすいと思いますが

Microsoft 365 であれば、関数で下記のように記述できます。（複雑です）

元のシートを Sheet1 としています。

A2# のようにセル名の後ろに#を付けると A2セル の値でなく、A2に設定されている配列数式を参照します(スピル範囲演算子)

（１）作業シートを使用する方法

【作業シート（WorkSとしています）】

●A2 Sheet1から日付のコピー

=Sheet1!A2:A6

●B2 Sheet1から顧客名のコピー

=Sheet1!B2:B6

●C2 Sheet1から製品名のコピー

=Sheet1!C2:C6

●D2 Sheet1から数量のコピー

=Sheet1!D2:D6

●E2 Sheet1からMのコピー(-で区切って最初の数値だけ取り出す)

=TAKE(TEXTSPLIT(Sheet1!E2:E6,"-"),,1)

●F2 Sheet1からCのコピー(-で区切って最初の数値だけ取り出す)

=TAKE(TEXTSPLIT(Sheet1!F2:F6,"-"),,1)

●A2 入力データ

=HSTACK(
Sheet1!A2:D6,
TAKE(TEXTSPLIT(Sheet1!E2:E6,"-"),,1),
TAKE(TEXTSPLIT(Sheet1!F2:F6,"-"),,1))

●G2 数量

=TAKE(TAKE(A2#,,4),,-1)

●H2 行番号

=SEQUENCE(ROWS(A2#),1)

●I2 累計数量（先頭からその行までの数量の合計）

=MAKEARRAY(ROWS(D2#),1,LAMBDA(r,c, SUM(OFFSET(D2#,0,0,r,1))))

=MAKEARRAY(ROWS(G2#),1,LAMBDA(r,c, SUM(OFFSET(G2#,0,0,r,1))))

※ MAKEARRAYは配列を作成する関数です（[ROWS(G2#)]行 1列）

個々のセルは LAMBDAで値を計算します

r を行 c を列として３番目のパラメータで計算する

●J2 格納する先頭の行位置（１行目を１として、１行前の累計数量+1）

=DROP(VSTACK(1,I2#+1),-1)

※ １行余分になるので削除しておく

●K1 格納先の全行数

=SUM(D2#)

=SUM(G2#)

●K2 格納先の行番号（１から連番）

=SEQUENCE(K1,1)

●L2 格納先の行に対応する入力行の位置

=XLOOKUP(K2#,I2#,H2#,,1,1)

●M2 M,Cの相対位置（格納先の行に対応するM,Cに加算する値）

=K2#-XLOOKUP(K2#,I2#,J2#,,1,1)

【作成するシート（TargetSとしています）】

●A1 見出し

=Sheet1!A1:F1

●A2 日付(格納先の行に対応する入力行の日付)

=INDEX(WorkS!A2#,WorkS!L2#,1)

●B2 顧客名(格納先の行に対応する入力行の顧客名)

=INDEX(WorkS!B2#,WorkS!L2#,1)

●C2 製品名(格納先の行に対応する入力行の製品名)

=INDEX(WorkS!C2#,WorkS!L2#,1)

●D2 数量(全て 1)

=SEQUENCE(WorkS!K1,1,1,0)

●E2 M(格納先の行に対応する入力行のM+相対位置)

=INDEX(WorkS!E2#,WorkS!L2#,1)+WorkS!M2#

●F2 C(格納先の行に対応する入力行のC+相対位置)

=INDEX(WorkS!F2#,WorkS!L2#,1)+WorkS!M2#

●A2

=MAKEARRAY(WorkS!K1,6,LAMBDA(r,c,
IF(c<4,INDEX(WorkS!A2#,INDEX(WorkS!L2#,r,1),c),
IF(c=4,1,INDEX(WorkS!A2#,INDEX(WorkS!L2#,r,1),c)+INDEX(WorkS!M2#,r,1)))))

関数については下記を参照してください。

Excel 関数 (機能別) - Microsoft サポート

https://support.microsoft.com/ja-jp/office/excel-%E9%96%A2%E6%95%B0-%E6%A9%9F%E8%83%BD%E5%88%A5-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb

論理関数

　MAKEARRAY

検索／行列関数

　TAKE

　DROP

　HSTACK

　VSTACK

　ROWS

　OFFSET

　XLOOKUP

　INDEX

数学／三角関数

　SEQUENCE

　SUM

文字列関数

　TEXTSPLIT

（２）作業シートを使用しない方法

【作成するシート（Target2Sとしています）】

（１）の数式をLET関数で一つにまとめただけです。

LET関数は名前とそれに対応する数式をペアにして、最後の数式がLET関数の結果になります。

●A1

=LET(
見出,Sheet1!A1:F1,
日付,Sheet1!A2:A6,
顧客名,Sheet1!B2:B6,
製品名,Sheet1!C2:C6,
数量,Sheet1!D2:D6,
M,TAKE(TEXTSPLIT(Sheet1!E2:E6,"-"),,1),
C,TAKE(TEXTSPLIT(Sheet1!F2:F6,"-"),,1),
入力連番,SEQUENCE(ROWS(数量),1),
累計数量,MAKEARRAY(ROWS(数量),1,LAMBDA(r,c, SUM(OFFSET(数量,0,0,r,1)))),
格納開始位置,DROP(VSTACK(1,累計数量+1),-1),
総数量,SUM(数量),
出力連番,SEQUENCE(総数量,1),
行位置,XLOOKUP(出力連番,累計数量,入力連番,,1,1),
相対位置,出力連番-XLOOKUP(出力連番,累計数量,格納開始位置,,1,1),
VSTACK(
見出,
HSTACK(
INDEX(日付,行位置,1),
INDEX(顧客名,行位置,1),
INDEX(製品名,行位置,1),
SEQUENCE(総数量,1,1,0),
INDEX(M,行位置,1)+相対位置,
INDEX(C,行位置,1)+相対位置
)))

※個々の列を定義しなくて済むように修正しました。

=LET(
数量,Sheet1!D2:D6,
DATA,HSTACK(
Sheet1!A2:D6,
TAKE(TEXTSPLIT(Sheet1!E2:E6,"-"),,1),
TAKE(TEXTSPLIT(Sheet1!F2:F6,"-"),,1)),
入力連番,SEQUENCE(ROWS(数量),1),
累計数量,MAKEARRAY(ROWS(数量),1,LAMBDA(r,c, SUM(OFFSET(数量,0,0,r,1)))),
格納開始位置,DROP(VSTACK(1,累計数量+1),-1),
総数量,SUM(数量),
出力連番,SEQUENCE(総数量,1),
行位置,XLOOKUP(出力連番,累計数量,入力連番,,1,1),
相対位置,出力連番-XLOOKUP(出力連番,累計数量,格納開始位置,,1,1),
VSTACK(
Sheet1!A1:F1,
MAKEARRAY(総数量,6,LAMBDA(r,c,
IF(c<4,INDEX(DATA,INDEX(行位置,r,1),c),
IF(c=4,1,INDEX(DATA,INDEX(行位置,r,1),c)+INDEX(相対位置,r,1)))))))

式内の改行は {Alt+Enter} で行ってください。

わかりにくくて申し訳ありません。

作業シートを使用する方法では、各数式の結果が確認できるので、参考にしてください。

修正 2025/10/8

　個々の列を定義しなくて済むように修正しました。

追記 2025/10/8 15:40

　TEXTSPLIT関数の第一引数に配列を指定すると分割した最初の文字列しかとりこめないようです。

今回は最初の文字列だけ取得できればよかったので問題ありませんでしたが、他の部分を取り出すにはBYROW関数かMAKEARRAY関数を使う必要があります。

=BYROW(Sheet1!E2:F6,LAMBDA(array,TAKE(TEXTSPLIT(array,"-"),,1)))

Answer 3

この回答は自動翻訳されているため、文法的な誤りや表現の誤りがある場合がございますが、ご容赦ください。ご理解のほどよろしくお願いいたします。

こんにちは @Tomomi

Microsoft Q&A フォーラムにご質問を投稿いただきありがとうございます。

以下の点について確認させていただきます。

• 最初の 3 列の既存の情報は変更されません。
• 4 列目の数字は拡張する行数を表し、5 列目と 6 列目の値に対応しています。

詳細なご回答は、問題の診断と調査をより効率的に行うために役立ちます。 ご質問の内容が私の理解不足でしたら、お気軽にお知らせいただくか、スクリーンショットを共有してください。 喜んでお手伝いさせていただきます。

ご協力ありがとうございます。 ご返信をお待ちしております。