Excel で「10/1889/1460#001」「10/1889/1461#001」…といった連番を作りたいのに、コピーしていくと「10/1889/14610#001」など桁が狂う――この現象は TEXT 関数の誤用が原因です。本記事では、なぜそうなるのかを仕組みから解説し、最短の修正式・ゼロ埋め・行挿入や並べ替えに強い応用式・動的配列（SEQUENCE）による一括生成まで、実務で迷わない作り方を丁寧に解説します。
症状の再現と原因の一言まとめ
問題の式は次のような形でした。
= "10/1889/" & TEXT(ROWS(D4:D$4), "1460") & "#001"
下方向へコピーすると本来は 1460 → 1461 → 1462 … と増えてほしいところで、14610 のように桁が増えます。これは、TEXT(数値, "1460") の第二引数「1460」が 数値 1460 ではなく “書式コード” として解釈されるためです。Excel の書式コードでは 0 や # が桁のプレースホルダー、1～9 は ただの文字 として扱われます。つまり "1460" は「先頭に文字列 146 を付け、最後の 0 は数字を 1 桁表示せよ」という意味になり、10 を渡すと 14610 という結果になるわけです。
最短の解決策：書式化をやめて「単純加算」にする
開始値を 1460 にしたいだけなら、TEXT での書式化は不要です。行数に 1459 を足すだけで解決します。
= "10/1889/" & (ROWS($D$4:D4) + 1459) & "#001"
| 部品 | 意味 | ポイント |
|---|---|---|
ROWS($D$4:D4) | 1 行目で 1 / 2 行目で 2 … と増えるカウンタ | 左側の $D$4 を絶対参照、右側の D4 は相対参照にする |
+ 1459 | 開始値 1460 (= 1459 + 1) に補正 | 開始値 n にしたいなら + (n - 1) |
| 文字列の前後 | "10/1889/" と "#001" を結合 | 演算順は不要だが括弧で数値部分を明確に |
出力例（4 行目を起点とした場合）
- 4 行目 →
10/1889/1460#001 - 5 行目 →
10/1889/1461#001 - 6 行目 →
10/1889/1462#001
桁数を固定したい（ゼロ埋め）場合の正解
「常に 4 桁で表示したい（1460, 0147 など）」といったニーズなら、TEXT を使いますが、書式コードは "0"（ゼロ）で指定します。
= "10/1889/" & TEXT(ROWS($D$4:D4) + 1459, "0000") & "#001"
"0000" は「最低 4 桁で表示し、足りなければ先頭を 0 で埋める」の意味です。これなら 1 桁〜3 桁のときも桁ブレせず安定します。
よくある間違い：絶対参照の向き
ROWS をカウンタとして使う場合、上端を固定して下端を動かすのが定石です。次の比較を見てください。
| 式 | コピー時の挙動 | 推奨 |
|---|---|---|
ROWS($D$4:D4) | 1, 2, 3, … と安定して増える | ◎ （正しい） |
ROWS(D4:D$4) | 下にコピーすると範囲が逆転し不安定 | × （非推奨） |
ROWS(D4:D4) | 常に 1（カウンタにならない） | × |
並べ替えや行挿入に強くする（代替カウンタ）
カウンタ生成は ROWS 以外でも可能です。次の式は行番号を相対化してカウントします。アンカー行の挿入・削除に比較的強く、シートの構成変更に追従しやすいのが特徴です。
= "10/1889/" & (ROW() - ROW($D$4) + 1 + 1459) & "#001"
ROW()は現在行の番号、ROW($D$4)はアンカー（開始）行の番号。+1はアンカー行を 1 とみなす補正です。
ゼロ埋めするなら、数値部分を TEXT(…, "0000") でくるみます。
動的配列で一括生成：SEQUENCE + TEXT
Microsoft 365 以降なら、1 つのセルから連番をスピル出力できます。行数 n を指定して一気に作る方法は次のとおりです。
= "10/1889/" & TEXT(SEQUENCE(行数, 1, 1460, 1), "0000") & "#001"
SEQUENCE(行数, 列数, 開始値, 増分)で 1460 から 1 ずつ増やす列ベクトルを生成。TEXT(…, "0000")で 4 桁ゼロ埋め。- スピル範囲は自動拡張・縮小し、行の挿入にも強いです。
プレフィックス／サフィックスを別セルに分離して汎用化
定数を式中にベタ書きすると後のメンテが大変です。次のように分離すると再利用しやすくなります。
| セル | 値 | 説明 |
|---|---|---|
| A1 | 10/1889/ | プレフィックス |
| B1 | #001 | サフィックス |
| C1 | 1460 | 開始番号 |
このときの式：
= $A$1 & (ROWS($D$4:D4) + $C$1 - 1) & $B$1
ゼロ埋め版：
= $A$1 & TEXT(ROWS($D$4:D4) + $C$1 - 1, "0000") & $B$1
LET で読みやすく・速くする
LET を使うと、式の中で名前を定義でき、可読性とパフォーマンスが上がります。
= LET(
base, 1460,
cnt, ROWS($D$4:D4),
num, base + cnt - 1,
"10/1889/" & TEXT(num, "0000") & "#001"
)
大量データでも崩れにくいベストプラクティス
- 数値の生成と表示の責務分離：内部では数値（
num）を計算し、見せ方（ゼロ埋めなど）は最後にTEXT。これで集計や比較に数値を再利用しやすい。 - 定数はセル参照または
LETへ：プレフィックス・開始値・書式は外出し。 - 動的配列を活用：行の増減に自動追従。スピル範囲に表（テーブル）を被せない。
- テーブル機能（Ctrl + T）：構造化参照と組み合わせるとコピー不要、最終行の追加で自動展開。
実務でありがちな応用パターン集
桁数が途中で増える可能性に備える
将来 10000 以上になるなら、書式は「00000」など余裕を持たせます。桁不足で見た目が変わるトラブルを防げます。
= "10/1889/" & TEXT(ROWS($D$4:D4) + 1459, "00000") & "#001"
番号の真ん中に区切り（ハイフン）を入れる
見分けやすさ重視なら、数字部分だけを作ってから結合します。
= "10/1889-" & TEXT(ROWS($D$4:D4) + 1459, "0000") & "-#001"
列方向（横方向）に増やす
= "10/1889/" & (COLUMNS($D$4:D4) + 1459) & "#001"
動的配列なら = "10/1889/" & SEQUENCE(1, 列数, 1460, 1) & "#001" で横にスピル。
任意の開始行から安全にスタート
途中行から貼っても 1 から始まるカウンタが欲しいなら、ROW との差分で作るのが明快です。
= "10/1889/" & (ROW() - ROW($D$4) + 1460) & "#001"
トラブルシュート：これで迷わない
| 現象 | 原因 | 対処 |
|---|---|---|
| 1460 が 14610 になる | TEXT(…, "1460") と書式を誤指定（146 は文字、0 だけプレースホルダー） | "0000" など 0 のみで桁指定、または TEXT を使わない |
| 常に 1 しか増えない | ROWS(D4:D4) のように範囲が固定 | ROWS($D$4:D4) に修正 |
| 下にコピーするとエラー | ROWS(D4:D$4) のように上下が逆転 | ROWS($D$4:D4) または ROW()-ROW($D$4)+1 |
| 並べ替えしたら番号が入れ替わる | 式はセルに紐づくため、行が動くと番号も行と一緒に移動 | 「番号は行と運命共同体」でOKなら問題なし。固定値にしたいなら最後に 値貼り付け |
| 途中行を削除・挿入するとズレる | アンカー位置の変更やテーブル外挿入 | アンカーを表の見出し行にする／ROW() 方式に切替／動的配列＋テーブルで管理 |
確認用：最小の検証シナリオ
次の表は、4 行目（D4）から式を開始した場合の挙動を示します。
| 行 | 式（数値部分） | 出力 |
|---|---|---|
| 4 | ROWS($D$4:D4)+1459 | 10/1889/1460#001 |
| 5 | ROWS($D$4:D5)+1459 | 10/1889/1461#001 |
| 6 | ROWS($D$4:D6)+1459 | 10/1889/1462#001 |
| … | … | … |
「値に確定」するタイミング
連番を後工程（例えば別システムへの取り込み）に渡す際、式のままだと再計算で変わる可能性があります。確定が必要になった段階で、列全体をコピーして「値の貼り付け」に置き換えると安心です。表（テーブル）運用時は、確定前に並べ替えやフィルタ条件が変わると番号が再割当される点にも注意してください。
関数を使わない方法（補足）
ケースによっては、関数を使わず「連続データの作成」（ホーム > フィル > 連続データの作成）で 開始値 1460・増分 1 の数列を作り、前後の文字列を結合するだけでも十分です。見た目を固定したいときは「表示形式」でユーザー定義（例：0000）を設定すればゼロ埋めの効果が得られます。ただし、途中に行を挿入したとき自動で振り直したい場合は関数方式に軍配が上がります。
TEXT 関数の書式コードを正しく理解する
最後に、今回のつまずきの根源である書式コードの基礎を整理します。
| コード | 意味 | 例 |
|---|---|---|
0 | 必ず 1 桁表示。足りなければ 0 で埋める | TEXT(7,"0000") = "0007" |
# | 桁があるときだけ表示 | TEXT(7,"####") = "7" |
"文字" | 引用符内はそのまま表示 | TEXT(7,"No-0000") = "No-0007" |
1～9 | 単なる文字（数字のプレースホルダーではない） | TEXT(10,"1460") = "14610" |
この仕様を踏まえれば、「1460 を TEXT の第二引数に渡す」のは 「146」＋「数値 1 桁」の指定になってしまうことが直感的に理解できます。桁固定は 0 のみ、任意の文字は引用符（または数字をそのまま）で表す、というルールを覚えておくと安全です。
まとめ
- 原因：
TEXT(…, "1460")は 1460 という数値ではなく 書式。1/4/6は文字、0が桁指定のため10→14610になる。 - 最短解：
= "10/1889/" & (ROWS($D$4:D4) + 1459) & "#001"（数字は単純加算）。 - 桁固定：
TEXT(…, "0000")を使う。ゼロ埋めは0で指定。 - 堅牢化：
ROW()-ROW($D$4)+1方式やSEQUENCEを活用。定数は外出し orLET。 - 運用：後工程に渡すときは「値貼り付け」で確定。テーブル・動的配列で増減に強い設計に。
即コピペ用テンプレート
要件に応じてそのまま使える形でまとめます。
| 要件 | 式 |
|---|---|
| 最小構成（行ごとに 1460, 1461, …） | = "10/1889/" & (ROWS($D$4:D4) + 1459) & "#001" |
| 4 桁ゼロ埋め | = "10/1889/" & TEXT(ROWS($D$4:D4) + 1459, "0000") & "#001" |
| 並べ替え・挿入に強い | = "10/1889/" & TEXT(ROW() - ROW($D$4) + 1460, "0000") & "#001" |
| 動的配列で一括出力（行数 = N） | = "10/1889/" & TEXT(SEQUENCE(N,1,1460,1), "0000") & "#001" |
| パラメータをセル参照 | = $A$1 & TEXT(ROWS($D$4:D4) + $C$1 - 1, "0000") & $B$1（A1=前置、B1=後置、C1=開始） |
おまけ：元の NG 式を分解して理解する
最後に、もとの式がなぜ “そう動くのか” を文字通り分解して見ておきます。
= "10/1889/" & TEXT(ROWS(D4:D$4), "1460") & "#001"
ROWS(D4:D$4)は 1 行目では 1 を返すが、下にコピーすると範囲が逆転する。TEXT(1, "1460")は"1461"を返す（1/4/6は文字、最後の0に1が入る）。TEXT(10, "1460")は"14610"を返す（0プレースホルダーに10が 1 桁としてそのまま表示される）。
“書式” と “数値そのもの” を取り違えるとこうした混乱が起きます。今回のポイントは、数値は加算で作り、見た目は TEXT で整える――この分離だけ覚えておけば、以降の連番設計は迷いません。
結論：これだけ覚えれば OK
Excel で「1460, 1461, …」の連番を作る最短の正解
= "10/1889/" & (ROWS($D$4:D4) + 1459) & "#001"4 桁固定の正解
= "10/1889/" & TEXT(ROWS($D$4:D4) + 1459, "0000") & "#001"
これらを起点に、ROW／SEQUENCE／LET を組み合わせれば、行挿入・並べ替え・大量データにも耐える “壊れない連番” が実現できます。
コメント