エクセル関数の小技の紹介です。
Excelで連番を振る場面はけっこうありますよね。
普通に１から順番に連番を振る方法はいろいろありますよね。
分類ごとに連番を振るのも関数でサクッとできます！
下の表は卵の会の会員の購入履歴です。
A列に購入毎の連番、B列に会員番号ごとの連番を振ります。
■連番の入れ方
①A列は最初に「１」を入力して、オートフィルで連番を振る方法
コントロールキーを押すのが面倒という場合は、
ドラッグ後、右下に出るオートフィルオプションから「連続データ」を選択。
または、最初に「１」次のセルに「２」を入力して、その二つのセルを選択し、右下の+マークを下までドラッグ。
②足し算で連番を入力
・最初のセルに「１」を入力。次のセルに「一つ上のセル＋１」の足し算を入力。そのセルをコピー、最後のセルまで貼り付け。
③ROW関数で連番を入力
=ROW ( ) と入力すると行番号が返ります。
なので、上の例だと4行目からデータが始まるので =ROW() の結果は「4」。
1番にしたいので
=ROW() – 3
と入力。すると結果は「１」
セルA4の式を最後の行までコピーすると連番になります。
この方法の式が入っている場合、表の並べ替えが行われても連番はぐちゃぐちゃにならず１からの順番をしっかり死守します。逆に連番も一緒に並べ替えしたい場合は式をコピー＆値貼り付けして、関数をけしておく必要があります。
■会員番号ごとの連番の入れ方（分類ごと、グループごとの連番の入れ方）
一番下のセルから式を入力します。（別に一番下からでなければいけないわけではないが私はこれがやりやすい）
B列会員毎連番にCOUNTIF関数を入れます。
検索範囲は会員番号の一番上から自分自身のセルの行まで。
検索条件は自分自身の行の会員番号。
= COUNTIF ( C4:C13 , C13 )
ここで、検索範囲の最初の行番号だけを絶対参照とします。
= COUNTIF ( C$4:C13 , C13 )
ここがポイントなんです！
この式を一番上までコピーします。
検索範囲の最初の行は絶対参照なのでずれませんが、最後の行は自分自身の行の会員番号までとなります。自分より前の行には同じ番号はいくつあるかを数えるので上に行くほど少なくなり、下に行くほど多くなるはずですね！
この方法は会員番号がぐちゃぐちゃに並んでいても大丈夫です。
購入日の昇順に並べ替えをしました。
会員番号B02の佐藤B男さんを見てみると、ちゃんと上から「１・２・３」となっています。
以上です。
お読みいただいた方、ありがとうございました。
ご参考となれば幸いです。
ちなみに、VBAで連番を振るときにセルに一つずつ前のセル＋１、前のセル＋１とループでやっていったらえらい時間がかかってしまったことがあります。十万行くらいあったとき。パソコンの性能も悪かったこともあって、連番を振るだけなのに20分くらい待っていた。。。( ;∀;)
i =2
Do Until Cells(i,1) = “”
Cells(i,1).Value = j
i = i+1
j = j +1
Loop
いちいちセルにアクセスするのは効率が悪いんですね(*’▽’)
配列に書き込んでからばひっと一気に入力するとか、VBAでオートフィルするとかやる方が早いのですな。
コメント