今回は、シートに長い連番を入力するときに少しでもお手軽な方法がないか、しつこく調べたいと思います。
遥かなるドラッグの旅
表の各データ行などに連番を振りたいことはよくあると思います。
もちろん手入力ではやっていられないので、便利なオートフィルを使ってドラッグ入力しますが、何千何万もの連番だとそれさえやってられません。
今日私たちが Excel で扱うデータの規模はどんどん巨大になる一方で、ドラッグするにも延々とシートのスクロールを眺めている時間はないのです。
Excel には一発で連番を自動入力する方法はないのでしょうか。
Excel の連番入力機能
Excel には、いくつか自動で連番を生成する方法がありますが、本記事ではそのうち以下の機能について、使い勝手を調べてみます。
- オートフィル
- 連続データの生成
- SEQUENCE関数（Office 365のみ）
オートフィルを使う
まずは基本の「オートフィル」です。
オートフィルというのは、セル範囲の最初のセル値を基にして残りのセルへ自動で値を入力してくれるという便利な入力支援機能です。 数値、日付、曜日、項番（文字列）といった種類のどれをどういう規則でどこからどこまで入力すべきかという、本来なら人間がいちいち指定しなければならないところを、Excel が初期セルの内容やその周囲の状況、マウス操作などから 自動で判断してくれます。
便利は便利なのですが、使い方が微妙に分かりづらくて、いつも試行錯誤してしまいます。 機能の全貌もよく分からないので、もっといい方法がないのかこの機会に調べてみました。
まず、オートフィルにショートカットはありません。 それどころか、ボタンやメニューといったコマンドさえいくら探してもないという、知らないと使いようがない機能です。
ではどう使うのかというと、「フィルハンドル」と呼ばれるコントロールをマウスで操作します。 フィルハンドルとはセルの選択枠の右下の角に、よーく見るとある、小さなポッチです。
この5ピクセル角のポッチにマウスカーソルを合わせると、カーソルアイコンが黒い十字になりフィルハンドルをつかめる状態となります。 こんなの教えてもらえないと分かりようがないですよね。
フィルハンドルにはいくつかのマウス操作があって、状況によって使い分けます。
- ダブルクリック
- ドラッグ
- 右ボタンドラッグ
すでにデータ入力済みの表などがあり、その隣に連続データの列を添えたいときにはフィルハンドルの「ダブルクリック」が非常に便利です。 セル範囲を指定しなくても、表の下端までを自動で連続データをフィルしてくれます。
一方、何もないところで新規に連番を作成したい場合には、フィルハンドルを「ドラッグ」します。 このとき、「オートフィルオプション」というオプションメニューが出てくるので、必要に応じてフィルの形式を選択できます。
「右ボタンドラッグ」もありますが、これもフィル形式を選択するメニューがポップアップします。 ただのドラッグ（左ボタン）とほとんど機能がダブっているのであえて使うこともないでしょう。
では、オートフィルで連番を入力してみましょう。 最初のセルに「1」を入力してフィルハンドルをドラッグしてみます。
すると残念なことに、連番ではなく全てのセルが「1」になってしまいますが、その後右下に現れるオートフィルオプションを開き、「連続データ」を選択すれば連番になります。 ちょっと手間ですね。
オプションにあるようにオートフィルにはいくつかのフィル方式が選べるのですが、普通は、つまり日付、曜日、文字列項番などでは、デフォルトで「連続データ」が選択され、そのままでOKです。
ところがなぜか「1」など数値のときだけ「値のコピー」が選択されるというイジワル仕様なのです。
数値でも最初から連番で入力させる技があります。
- 2番目も手入力
- 複数列でドラッグ
Ctrl+ドラッグ
連番の最初のいくつかをセルにあらかじめ入力しておくと、それらを参考に連続データがフィルされます。
たとえば、「1」と 「2」を入力した2つのセルに対してフィルハンドルをドラッグすると連番データとなります。 これは、フィルハンドルのダブルクリックでも同様です。
「2」を入力するのさえ面倒なら、横に複数セルを選択して「複数列でドラッグ」しても連番になります。 どうしてそうなるのかは分かりません。
一番簡単なのは、Ctrlキーを押しながらフィルハンドルをドラッグする方法です。
Ctrlキーはデフォルト方式を切り替えるようで、数値なら連続データで連番入力になります。
もう「Ctrl+ドラッグ」だけ覚えておけば良さそうですが、他の方法にも使い道があります。
例えば、
- 小数点以下の連番
- 値のみの連番
「小数点以下の連番」を作りたいとき、小数をCtrl+ドラッグするとヘンな結果になります。
先頭の2セルに 「0.01」、「0.02」 のように入力しておけば、その刻み幅での連番にすることができます。
また、「値のみの連番」を入力したいときにも使えます。 というのもオートフィルは基本的に、セル値だけでなくスタイルごとコピーするので、表の罫線などが崩してしまうことがあるのです。
オートフィルには「書式なしコピー」というオプションもあるのですが、「Ctrl+ドラッグ」後にこれを選択するとなぜか連番までが解除されてコピーになってしまいうまくいきません。
その場合、「2番目も手入力」をしてから「書式なしコピー」を選択すればOKです。
まとめると、連番をオートフィルする基本操作は、
- フィルハンドルをダブルクリックする場合
⇒「1」、「2」を先頭に入力してからダブルクリック - フィルハンドルをドラッグする場合
⇒「1」を先頭に入力してからCtrlキー押しながらドラッグ
でいいでしょう。
オートフィルは便利ですが、マウス操作でしか使えないのが長い連番では難点です。
連続データの作成を使う
Excelにはオートフィルと同様の機能として、「連続データの作成」が用意されています。 紛らわしいですが別の機能です。
その「連続データ」ダイアログは、これまた分りにくいのですが、様々なパターンの連続データを生成するための設定項目があり、長い連番でもマウスドラッグなしで生成できます。
これで 1 から 1000 までの連番を入力してみましょう。 次のような手順になります。
- 最初のセルに 1 を入力し選択状態にします
- 「連続データ」ダイアログを開きます
- 「ホーム」タブ ⇒「フィル」メニュー ⇒「連続データの生成」
- 「範囲」で「列」を選択します
- 「種類」で「加算」を選択します（デフォルト）
- 「増分値」に「1」を入力します（デフォルト）
- 「停止値」に「1000」を入力します
- 「OK」ボタンを押します
やってみると、たかが連番を入力するためにしては煩雑すぎる感じがします。 高機能なのはいいのですが、ダイアログを開くのも、設定項目を入力するのも、キー操作だけでサクサクというわけにはいかず、結局マウスも併用します。
もうちょっとお手軽にはならないのでしょうか。 折角なので、できるだけ簡単にする方法を模索してみます。
まず「連続データの作成」にショートカットがないのが難点です。
アクセスキーならAlt+H⇒FI⇒Sになりますが、ちょっと長くてキータイプも打ちづらいです。
実は裏アクセスキーもあって、それはAlt+E⇒I⇒Sとなります。
1字減ってタイプ感も少しはマシな感じです。
つなげてオルテイス（AltEIS）と覚えましょう。
（何かのキャラクター名みたいな響きで覚えやすいです）
ちなみに、どうしても一発ショートカットにしたければ、クイックアクセスツールバーに「連続データ」ボタンを追加することで、Alt+数字で呼ぶことも可能です。
つぎに、「連続データ」ダイアログの項目設定を軽減したいところです。
「停止値」（最終値）の入力はまあ仕方ないとして、「範囲」がデフォルトで「行」方向になっているのは気が利きません。 初期状態として「増分値」が編集状態になっているのも意味が分かりません。
そこであらかじめ連番のセル範囲を選択状態にしておくと、これら項目設定がほぼ省略できます。 セル方向と最終値は、選択範囲から自動で判断してくれるからです。
ただこうすると、最大値までのセル範囲を選択しておく必要があります。 そのためにまた長々とマウスドラッグしなければならないとしたら、本末転倒ですね。
マウスドラッグやスクロールなしでセル範囲を選択するには「名前ボックス」が使えます。
名前ボックスとは数式バーの左あって普段は選択セルのアドレスを表示しているボックスのことです。 ここには任意のセルアドレスや名前の入力もできて、そのセルが選択状態になります。
たとえば今、A1セルが選択されているとして、そこからA1000セルまでを範囲選択するには次の２つの方法が使えます。
- 名前ボックスに「A1:A1000」と入力して
Enterを押す - 名前ボックスに「A1000」と入力して
Shift+Enterを押す
セル範囲をアドレスで表現するには、「A1:A1000」のように開始アドレスと最終アドレスを「:」(コロン)で連結します。
あるいは、名前ボックスを別のアドレスに変更してShift+Enterを押すと、元のセルから変更アドレスのセルまでが選択状態になります。
これらを組み合わせれば、例えばC2セルから1000個の連番を入れる手順は以下のようになります。
- C2セルに 1 を入力します
- 名前ボックスのアドレス「C2」を「C1001」に書き換えて
Shift+Enterを押します- C2からC1001までが選択状態になります
- 「連続データ」ダイアログを開きそのまま「OK」ボタンを押します
- キー操作：
Alt + E⇒I⇒S⇒Enter
- キー操作：
どうでしょう。 少しは手早くなったのはないでしょうか。
ただ、最終値のセルのアドレスの行番号は、自分で計算する必要があります。 それは最初のセルの行をS、必要な連番の数をNとすると、S+N-1で求まります。 上記の例でC2セルから1000個のセルを選択するには 2+1000-1 で最終セルはC1001になります。
簡単な暗算なのですが、計算はもっぱらエクセル頼りになって退化した筆者の脳が、今度はボトルネックになります。
SEQUENCE関数を使う
「SEQUENCE」という新しい関数を使えば、数式から連番をお手軽に生成できます。
たとえばセルに以下のような数式を入力するだけで、1から1000までの連番が列方向にピロッと展開されます。
=SEQUENCE(1000)
もうこれでいいよ、て感じですね。
ただし、これは最近追加されたばかりの新しい関数で、Office 365 (現 Microsoft 365）版の最新 Excel でしかサポートされていません。 残念ながら買い切り版では最新の Excel 2019 でさえも使えない関数なのです。
なぜならこれは単なるお便利関数の追加ではなく、Office 365 でリリースされた二つの新機能の追加に伴い、その組み合わせを応用して実現されたものだからです。
その機能の一つは「動的配列数式」、もう一つは「スピル」と呼ばれます。 聞いたことがあるかもしれません。
詳しい解説は別の機会に譲るとして、SEQUENCE関数との関係でいうと、①SEQUENCE関数は結果（つまり連番の番号の集まり）を動的配列として返し、②数式セルは動的配列を受け取ると、配列の各要素（値）を、外のセル範囲に自動展開、すなわちスピル（Spill）します。 動的配列のサイズは可変で、スピルはそれに応じて展開先のセル範囲を自動調整する、というところがポイントです。
スピル先のセルの値は変更できません。 スピルされた値はあくまで動的配列数式の持ち物で、スピルはそのセルを貸し切りで間借りしているのです。 また、スピル先に先住の値があると「#SPILL」というエラーになります。
スピルを解除して通常のセル値に書き戻したいときには、スピル範囲をコピーして「値のみ貼り付け」をしてください。
ちなみにSEQUENCE関数の仕様を見ると、渡せるパラメータがほかにも3個あり、もっと高度な連続データを作成することができます。それらを省略したときに、列方向の連番になるのは珍しく気が利いています。
さて、ここまで触れてきませんでしたが、連番にはメンテナンス問題という話題もあります。 つまり、行を追加や削除したときに、欠けたりズレたりする連番をどう管理していくのかという問題で、いくつかのテクニックが知られていますが、どれも一長一短です。
SEQUENCE関数はそこに新たな手法を提供します。
今、表があって、連番を入力したい列の先頭行のセルをA2、最終行のセルをA10としたとき、次のような数式が書けます。
=SEQUENCE(ROWS(A2:A10))
このようにして展開された連番は、途中の行で追加・削除があっても壊れないので、メンテナンスコストの削減につながります。
まあ、未だに古い Excel を使っている筆者には縁のないはなしですが。
まとめ
Excelで長い連番を入力する方法をいくつか調べてみました。
オートフィルは、すでにデータがある場合にはフィルハンドルをダブルクリックするだけで連番を一発入力することができます。 そうでない場合、例えば真っサラなシートにとりあえず連番だけ作っておきたいような場合には、長いドラッグをするしかありません。
「連続エータの生成」ならいくらでも長い連番を生成できますが、ダイアログの使い勝手がイマイチです。
SEQUENCE関数は一番使い勝手がよさそうでしたが、Office 365 の Excel のみのサポートで、残念ながら古い Excel では使えません。 また互換性や「#SPILL」エラーなど対応するため、動的配列やスピルなどの新しい機能について勉強しておかなけれ使いこなせません。
どれもなかなかピロッとはいきません。 高度な機能を提供してくれる反面、単純なことが簡単にできなくなっている印象です。 ただ、得意不得意なことが分かってきたので、状況によって使い分けることができるでしょう。
本記事の内容は、Windows 10 環境で Excel 2013 と Office 365 版 Excel を使って検証しました。
