Excel>REPT関数で不足桁を0埋め、ピボット順序を正しく表示｜IT小技7

この記事で分かること

連番付きの項目が増えても、ピボットテーブルの順序が崩れない方法
Excel関数の実践的な使い方｜REPT関数を中心に、MAX関数・LEN関数・RIGHT関数の組み合わせ

課題｜ピボットテーブル項目の表示順序の落とし穴

次のようなケースを考えてみます。

(1)ID（連番）と商品名、価格が記録されたテーブルがある
(2)ピボットテーブル表示用に次の列を追加する
　D列(ID_商品)=A2&”_”&B2

<テーブルデータ>

D列を行ラベルにしてピボットテーブルを作成すると、ID10番目以降の順序が崩れます（図赤枠）

<ピボットテーブルの不具合①>

対策として「0」を手動で追加して2桁を揃える方法もあります。例えば次の式を使うと、2桁まで正しく並びます。

D列(ID_商品)=RIGHT(“0″&A2,2)&”_”&B2

しかしIDが3桁以上になると順序が崩れます。

<ID追加後のテーブルデータ>

<ID追加後のピボットテーブル不具合②>

そこで今回は、最大桁数に合わせて動的に0埋めを行う方法をご紹介します。

答え｜ポイントはREPT関数

テーブルデータのD列、E列に次の式を挿入します。D列が答えです。

D列(【解】ID_商品)
=RIGHT(REPT(“0”,E2)&A2,E2)&”_”&B2

E列(不足桁の0埋め数)
=LEN(MAX($A$2:$A$1000))

Excelバージョン：2021 MSO (バージョン 2507 ビルド 16.0.19029.20136) 64 ビット

解説

MAX関数：
連番ID列の最大値を求めます。順序の上限値を取得することで、必要な桁数が決まります。

LEN関数：
数値を文字列に変換したときの長さ（桁数）を返します。MAX関数との組み合わせで、最大値の桁数を動的に取得できるのがポイントです。

REPT関数：
指定した文字を繰り返す関数です。ここではゼロをLEN関数で求めた最大桁数分だけ繰り返します。REPT関数を使うことで、将来的に桁数が増えても対応可能になります。

RIGHT関数：
右から必要な桁数を取り出すことで、元の順序をゼロ埋めされた文字列に変換します。

以上、REPT関数を中心にLEN関数・MAX関数を組み合わせるのが効果的な解決策です。

まとめ

• 順序番号は桁上がりで順序が崩れるため注意が必要です。
• 2桁固定の0埋めでは限界があり、3桁以上に対応できません。
• MAX関数＋LEN関数＋REPT関数+RIGHT関数を組み合わせることで、最大桁数に応じた動的な0埋めが可能です。
• REPT関数を活用することで、ピボットテーブルの項目並び順を常に正しく維持できます。

Excelの実務に、ぜひ役立ててください。（終わり）