● オートフィル～数式～連番01：途中に切れ目のある表10000行に数式で連番を一発入力
目次
★ はじめに
★ 途中に切れ目のある表10000行に数式で連番を一発入力する方法
(00)A列に空白列を挿入
(01)列の作成と初期値の入力
(02)連番表示用の数式の入力
(03)数式のコピー
(04)数式を貼り付ける範囲の指定
(05)指定した範囲に一括で数式を埋めて連番表示
(06)数式を消して値のみに変換（＝数式に値を上書き：並べ替え異常や連番異常を回避するために）
(07)正常に処理ができたかの確認
(08)一番上のセルに戻って上書き保存
※補足01：途中で数式が1万行埋まったかをチェックする方法
※補足02「A列に連番自動作成」マクロ
※補足03「A列に連番自動作成02」マクロ
※Shift＋TABキー、もしくは、Homeキー、Homeキー＋TAB数回、を押すと、目次付近に戻れます。
※まだ書きかけです。すみません。
※間違ってたらすみません。
※メモ書きなので、自分でも意味不明な箇所も多いです。ごめんなさい。
この記事はExcel2000用です。もしくは連番以外のデータでの「一列全部埋め」の方法です。
セル選択を横方向にすれば、1行全部埋めもできるかもしれません。
連番に関しては2003以降では次のサイトのやりかたのほうが簡単です。
「Excel 2010・2013で連続データの作成は？」
http://www.relief.jp/docs/018374.html
僕はこんなことができるということすら知らない、Excel音痴です。(^^) すみません(^^)
※なお、本記事の内容を「マクロの自動記録」をしてメニューボタン化してもいいと思います。
（リボンへのメニューボタン化・関連の記事5つ→1、2、3、4、5。あるいは巻末のリンクで。）
では、以下、本文です。
表の行数が、例えば一万行あるような場合、例えば連番の列を埋める場合を考えてみます。
（連番じゃない場合や、数式だけを1万行や10万行、一瞬で埋め込みたい場合にも使えます。）
このとき、連番を埋める方法としては、セルの右下の角をつかんでのドラッグやダブルクリックがあります。
でも、さすがに1万行もあるとドラッグは面倒ですし、ドラッグミスもしてしまいそうなので、多くの方はダブルクリックで1万行を埋めていくことを考えると思います。
でもこの、ダブルクリックでのオートフィルは、もしとなりの列に空白セル（空白行）があるとそこまでしかオートフィルされないことがあります。左右の途中にいくつも空白セル（空白行）があると、そのたびに途中から何度もダブルクリックしなおし・・・ということになってしまいます。
あるいは、オートフィルしたい列に、たまたま、「まん中あたりのたった一つのセル」に、スペースや記号などがタイプミスされていたら・・・、そこまでしかオートフィルされません。
（※注・・・左右いずれかの列のセルが、すべて何らかの値で埋まっていればこうならず、ダブルクリックでのオートフィルが成功しますので大丈夫です。どの列にも切れ目＝空白セルが存在する場合（＝空白行）を想定しています）
いちいち、どの行に切れ目（空白セル）があるかのチェックも大変だし、結構面倒くさくなってきます。仮に1万行じゃなくても1000行でも切れ目が多かったら面倒は面倒です。
そのような場合は、オートフィルではないんですが、数式で連番を埋める方法があります。
連番を表現する数式を作っておいて、あとは1万行までを選択し、1回のコピペで1万個全部のセルに連番を一発入力する方法です。
これは隣の列に、途中にいくつ切れ目（空白セル）があっても、数式でやってしまうので関係ありません。選択したセル全部に連番が埋まります。（横方向にも使えます）
ただ、数式でやっているもんですから、その「作った連番の列」での並べ替えの時に値が正常に表示されない・正常に並べ替えができなくなる、などの不具合が出ます。
また、連番入力後に操作ミスで その列のセルを一か所空白にしてしまうと（これも数式で処理しているがゆえに）、その空白セルの次のセルからまた、１、２、３、４・・・と、再度「１から」連番が始まってしまいます。（下手すると、１から始まる連番が１つの連番の列の中に複数、できてしまいます。）
というわけで、そういった不都合を避け、並べ替えもちゃんとできるようにするには、もうひとつ連番列を作り、そこに現在の連番の値を「形式を選択して貼り付け」にて、「値」にて貼り付けする必要があります。
もしくは別の連番の列は作らずに、数式を埋めた列全体をいったん「コピー」操作をして、そのあと、そのまま同じ列に「形式を選択して貼り付け」で「値」で貼り付けます。
式は消え、値だけになります。（また、無駄な数式が減るため、微力ながらシート全体の動作速度向上（というか回復）にも貢献します。）
ちょっと手間はかかりますが、もしこれが 1万行ならまだしも、10万行とか20万行もあるような表だと本当に苦しくなってきますので やはりこういった方法は必要になるのではないかと思います。繰り返しになりますが、仮に1万行じゃなくても1000行でも切れ目の空白セルが多かったら面倒は面倒ですし・・・。
なので、今回、この「数式で連番などを埋める」という方法をお伝えしたいと思います。
また、この方法は連番だけでなく、何らかの計算や文字列結合などでも使える方法だと思いますので、そういった活用もしてみてください。
では以降で、そのやり方をご説明します。
★ 途中に切れ目のある表10000行に数式で連番を一発入力する方法
(00)A列に空白列を挿入
一番左に、新しい列を挿入したとします。
新しい列の挿入の方法はこちら
(01)列の作成と初期値の入力
A1に「連番」と入力し、A2セルに「1」と入力します。
(02)連番表示用の数式の入力
A3セルに「=A2+1」と数式を入力してEnterします。
Enterした瞬間に計算がなされて、「2」という値が表示されます。
※数式は「=ROW()-××」をいきなりA2セルから入れてもOKです。「xx」の部分は「行番号」から「1」を引いた数です。その数式をA3セル以降のセルに、オートフィルや次項以降のように数式埋め（A2セルのコピペ）をすれば連番になります。
(03)数式のコピー
A3セルをクリックして、Ctrlキーを押しながらCキーを押します。（Ctrl＋C＝コピー）
（もしくはA3セルの上で右クリックして「コピー」を押します。）
※「数式のみ」をコピーしても貼り付けの時にエラーになってだめなので、必ず「セル」をコピーしてください。セルをコピーすればエラーにはなりません。
(04)数式を貼り付ける範囲の指定
A3のセルが点線点滅している状態のまま、名前ボックス（下図赤枠）をクリックします。
日本語入力モードを必ず「半角英数」にします。
名前ボックスに半角英数で「a10001」と入力します。
（もし操作対象の列がB列なら「b10001」、C列なら「c10001」と入力します。）
1万行分なのですが、一番上の行は列名が入力されているので「10001」と入力します。
（下図参照。※ご注意・・・一番右の縦棒は点滅カーソルなので数字ではありません。）
(05)指定した範囲に一括で数式を埋めて連番表示
名前ボックスの中で点滅カーソルが点滅している状態のまま、Shiftキーを押しながらEnterキーを押します。（Shift＋Enter）
（このとき、A3セルは点線点滅したままのはずです。）
下図のようにA3セルから、A10001セルまでが選択されます。
※もしShiftキーを押すほうがかえって面倒だったら、名前ボックスに「a3:a10001」と半角英数モードで入力してEnterします。すると、Shiftキーを押さなくても「A3セル～A10001セル」が選択されます。
このタイミングで、すぐに、Ctrlキーを押しながらVキーを押します。（Ctrl＋V＝貼付け）
下図のように、10001行目まで、数式によって連番が埋まります。
(06)数式を消して値のみに変換（＝数式に値を上書き：並べ替え異常や連番異常を回避するために）
このままだと、操作ミスによって この列のどこかのセルで値が変わると、それ以降のすべての行（=セル）の値が変わってしまいます。また、この列での並べ替えも正常にできないままです。（降順で並べ替えた後元に戻らない。昇順にできない・・・、など。）
なので、ここで「数式を消して値だけ残す」作業をします。
前項の(05)の最後の状態のまま、続きで、Ctrl＋C（コピー）をします。下図のようになります。
見えはしていませんが、1万1行目まで、すべてのセルが選択・コピーされます。
この状態のまま、選択された水色の部分で右クリックし、「形式を選択して貼り付け」を「ポイント（マウスを乗せるだけ）」して、「値（下図では123のボタン）」を押します。
（※ 下図は「形式を選択して貼り付け」をポイントしたときのものです。もし「形式を選択して貼り付け」を「クリックで押した」場合は、ダイアログが出るので「値」に丸ポチを入れてOKします。）
これで数式が消えて、値だけが残ります。（＝数式に値だけが上書きされます。）
万が一どこかのセルの値が書き換えられてしまっても、それ以降の行には影響は何もなくなります。この列での並べ替えも正常にできるようになります。
(07)正常に処理ができたかの確認
最後に、ミスが無いかの確認として、「Ctrl＋↓」をして、10001行目までジャンプしてみます。数式バーで A10001セルの数式を見ると、値だけが「10000」と表示されています。（これは特別な確認ではなく、毎回やります。値が目的の行まで埋まってなかった・・・といったことが無いことも確認したいので。名前ボックスで選択範囲を入力ミスすることもありますから・・・。）
これで、一番最後の行まで、数式が全部消えたことを確認できました。
また、行番号が「10001」で実際の値が「10000」ということは、「行番号よりも1行少ない値」、ということですので、基本、「正常」、という理屈になります。（1行分は、先頭で「列名」に使われているため）
※もし、より詳しくチェックをしたかったら、A10001セルをクリック後、↑矢印キーを押しっぱなしにしながら、数式バーの値をチェックしてみてください。数式は出てこないはずです。
今回の場合は、「一番下のセルの値だけを確認すれば、他のセルも同じ状況になっているに決まっている」という・・・、そういう操作をもとからしましたので一番下のセルだけを確認すればOKです。
(08)一番上のセルに戻って上書き保存
Ctrl＋Homeキーで一番上に戻り、Ctrl＋Sで上書き保存します。
以上の操作を、他人からファイルをもらったときに、すぐに、「すべての列と行を表示させたあと」にやっておくと、（この連番の列で並べ替えをすることで）原状にすぐに戻れます。
作業の目的によっては、この連番列の入った状態のファイルのバックアップをとっておくと便利なケースもあります。（ケースバイケースで連番列を入れる前の「もらったまんま」の状態もバックアップを作成してください。）
また、この列には絶対に「セルの切れ目」はありませんから、もし確実に「セルの右下隅のダブルクリックによるオートフィル」をしたいときは、この列をまず先に作成し、その後、そのオートフィルしたい列の隣に移動すれば確実に、「ダブルクリックによるオートフィル」」ができます。あるいはオートフィルしたい列を連番列のとなりに作り、その列をあとで（オートフィルしたあとで）目的の場所に移動させるかです。
いずれにしても「セルの切れ目が無いかなどを調べる手間」がかかりません。
やる必要がありません。
移動する位置は目的の列の右でも左でもかまいません。（列の移動は列全体を選択後、Shiftキーを押しながら列の境界線を左右にドラッグします。
なお、この操作を「マクロの記録」機能で記録すれば、A列に空白列を挿入しておけば、1万行分ならいつでも瞬時に連番が入力できます。上記の操作をいちいちやる必要はありません。
細かい制御をしたいなら、VBAプログラムを作成すれば良いと思います。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
※補足01：途中で数式が1万行埋まったかをチェックする方法
(05)の途中で、10001行目まで連番を一括貼り付けした直後に、以下の方法で本当に10001行目まで数式が埋まったかをチェックできます。
念のため、Ctrlを押しながら、下矢印キー（↓キー）を押して確認します。
下図のように、10001行目に「10000」という値が表示されています。
※補足02「A列に連番自動作成」マクロ
以上の操作を「A列に連番列を追加するマクロ」として自動追加できるマクロを、「マクロの記録」を利用して作ってみました。（「A列に連番自動作成」マクロのプログラムをご参照下さい）
実行するとどのセルが選択された状態でも、A列に1万行分の連番列が瞬時に自動作成されます。
標準モジュールにコピペして実行してみてください。
また、Personal.xlsなどにコピペしてリボンやメニューに「ボタンとして登録」をすれば、そのボタンを押すことで、1万行分の連番列が何回でも作成できます。
★「A列に連番自動作成」マクロ
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
' ' ' Sub A列に連番自動作成() ' ' A列に連番自動作成 Macro ' ' Columns("A:A").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1").Select ActiveCell.FormulaR1C1 = "連番" ActiveCell.Characters(1, 2).PhoneticCharacters = "レンバン" Range("A2").Select ActiveCell.FormulaR1C1 = "1" Range("A3").Select ActiveCell.FormulaR1C1 = "=R[-1]C+1" Range("A3").Select Selection.Copy Range("A3:A10001").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False End Sub ' |
※補足03「A列に連番自動作成02」マクロ
前項のマクロを、行をユーザーが指定できるように作り変えてみました。
ユーザーに、何行分の連番をA列に作りたいかを聞いてくるので、「10」と入力してOKすると、10行分の連番列を自動作成してくれます。
「200000」と入力すれば20万行分の連番列を自動作成してくれます。
標準モジュールにコピペして実行してみてください。
また、Personal.xlsなどにコピペしてリボンやメニューに「ボタンとして登録」をすれば、そのボタンを押すことで、指定した行数分の連番列が何回でも作成できます。
★「A列に連番自動作成02」マクロ
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
' ' ' Sub A列に連番自動作成02() ' ' A列に連番自動作成 Macro ' ' Dim RowNum01 As Long '以下の2行は新たに追加したものです。 'そのほかには、「Range("A3:A10001").Select」の行だけを書き換えました。 '全部で3行分の追加や修正です。 RowNum01 = InputBox("何行分の連番を作りますか？") RowNum01 = RowNum01 + 1 '列名の分を含めた行数で指定するために、1行分足しておく Columns("A:A").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1").Select ActiveCell.FormulaR1C1 = "連番" ActiveCell.Characters(1, 2).PhoneticCharacters = "レンバン" Range("A2").Select ActiveCell.FormulaR1C1 = "1" Range("A3").Select ActiveCell.FormulaR1C1 = "=R[-1]C+1" Range("A3").Select Selection.Copy ' Range("A3:A10001").Select '作り変えたのでコメントアウト。次行を使います。 Range("A3:A" & RowNum01).Select ActiveSheet.Paste Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False End Sub ' |
★ 上記のコードをさらに短く書くとどうなるかのコード
以降のプログラム中の、コメントアウトしたコードは、「連番」の場合は、本来はその下に書いた「1行」で書けてしまうそうです。
でも、だからといって「マクロの記録」機能で作成したコードが「無駄」ということにはりません。
その「1行コード」を調べる時間がもったいないケースもあるからです。特に「プログラム作成」に慣れていない人」は、「AutoFillメソッド」についてWeb検索したところで、それがヒットしたとしても、そのWebページに書いてあることがそもそも理解できません。または、理解がしづらいです。（特にエラーが出たときとか）
「AutoFillメソッドについて調べればいい」ということすら、思いつけないかもしれませんし。
反面、「マクロの記録」で作られたコードはそういうエラーが少ない場合もあります。実際に「プログラム作成」に慣れていない人ひと」でも動かせます。
結果、「マクロの記録」で作られたコード・また・コメントアウトし行から上のコードも、十分「コスパアップ」につながると思います。
繰り返しになりますが、特に、「プログラム作成に慣れてない人」には「マクロの記録」は有効なので、その意義は決して小さくないと思います。
職場にはプログラムに慣れた人ばかりじゃありませんから。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
' ' Sub A列に連番自動作成03() ' ' A列に連番自動作成 Macro ' ' Dim RowNum01 As Long '以下の2行は新たに追加したものです。 'そのほかには、「Range("A3:A10001").Select」の行だけを書き換えました。 '全部で3行分の追加や修正です。 RowNum01 = InputBox("何行分の連番を作りますか？") RowNum01 = RowNum01 + 1 '列名の分を含めた行数で指定するために、1行分足しておく Columns("A:A").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1").Select ActiveCell.FormulaR1C1 = "連番" ActiveCell.Characters(1, 2).PhoneticCharacters = "レンバン" Range("A2").Select ActiveCell.FormulaR1C1 = "1" ' Range("A3").Select ' ActiveCell.FormulaR1C1 = "=R[-1]C+1" ' Range("A3").Select ' Selection.Copy '' Range("A3:A10001").Select '作り変えたのでコメントアウト。次行を使います。 ' Range("A3:A" & RowNum01).Select ' ActiveSheet.Paste ' Application.CutCopyMode = False ' Selection.Copy ' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ ' :=False, Transpose:=False ' Application.CutCopyMode = False '上記のコメントアウトしたコードは、「連番」の場合は、本来は以下の1行でできるそうです。 Range("A2").AutoFill Destination:=Range("A2:A" & RowNum01), Type:=xlFillSeries '↑でも、これを調べる時間がもったいないケースもあるので、上記のようなコメントアウトした '「マクロの記録」で作られたコード・また・そこから上のコードも、十分「コスパアップ」につながると思います。 '特に、「プログラム作成に慣れてない人」には有効なので、その意義は決して小さくないと思います。 '職場にはプログラムに慣れた人ばかりじゃありませんから。 End Sub ' ' |
※関連記事01（リボン・メニュー登録関連）
Excel2010のリボンに、Excel2000と同じプログラムコードのコピペでユーザー設定ボタンを生成する。（一応ツールバー単位で）
ExcelVBA:WordVBA:他のVBA：ビジネス基礎：VBAプログラム（マクロ）をクイックツールバーにボタンとして組み込む方法
Word2010のリボンに、Word2000と同じプログラムコードのコピペでユーザー設定ボタンを生成する。（一応ツールバー単位で）
