Excelで、自動でデータのナンバー付けをしたい

質問

エクセルで、商品管理をしているのですが、以下のようなエクセルファイルのデータをフィルタ後（件数が０より多いものでフィルタをかけた後）、項目のナンバーを再び1から順につけていきたいと思っています。

------------------エクセルファイル------------------

項目　　商品名　　商品ID　　　単価　　　件数　　　金額
　1　　　　AAA　　　BBB　　　　¥2,000　　　1　　　　¥2,000
　2　　　　CCC　　　DDD　　　　¥1,200　　 19　　　　¥22,800
　3　　　　EEE　　　FFF　　　　　¥1,000　　　0　　　　¥0
　4　　　　GGG　　　HHH　　　　¥1,000　　　3　　　　¥3,000
　5　　　　III　　　　　JJJ　　　　 ¥500　　　　0　　　　¥0
　6　　　　KKK　　　LLL　　　　　¥400　　　　2　　　　¥800

　　　　　　　　　　　　　　　　　　　　　　　　　小計　　　¥28,600
---------------------------------------------------

上記のファイルを、件数が0より多いものでフィルタをかける。
つまり、エクセルファイルは、以下のようになります。

------------------エクセルファイル------------------

項目　　商品名　　商品ID　　　単価　　　件数　　　金額
　1　　　　AAA　　　BBB　　　　¥2,000　　　1　　　　¥2,000
　2　　　　CCC　　　DDD　　　　¥1,200　　 19　　　　¥22,800
　4　　　　GGG　　　HHH　　　　¥1,000　　　3　　　　¥3,000
　6　　　　KKK　　　LLL　　　　　¥400　　　　2　　　　¥800

　　　　　　　　　　　　　　　　　　　　　　　　　小計　　　¥28,600
---------------------------------------------------

この場合、項目のナンバーが1、2、4、6、という順番になっているので、
これを1、2、3、4、という順番に置き換えていきたいと思っています。

データ数が少なければ手動でやっても良いのですが、
データ数が膨大な量になるので、これを自動で出来るようにしたいと思っています。

VBAで挑戦を試みましたが、なかなか上手くいきませんでした。

何か上手くできる方法はございませんか？

どなたかご教授をお願いします。

回答No.1

kunpoo · ベストアンサー

A列を再ナンバリング時の「項目_再」として
１行目は空白行にして貰って、２行目に列名があるとして、
A3から
=if(F3=0,0,if(A2="項目_再",1,if(A2=0,max(A$2:A2)+1,A2+1)))

てな感じでどうでしょう。
フィルタ掛ければ、「項目_再」列が0の行は見えないし。

お礼

回答ありがとうございました！
みなさんの回答を全て試しましたが、kunpooさんのやり方が一番理解しやすかったので、
kunpooさんの回答をベストアンサーにしたいと思います！

ありがとうございました！

回答No.6

layy

基本的には発生したデータ値は変更しないものです。
何件かを知りたいがためなら「項目」列は残すべき、他の列やセルで「明細番号」として管理やら関数利用、と思います。連番付けた後、３行の扱いはどうするのか知りませんが、３行目の３、４行目の３は意味が変わります。

「件数」が変わることはないと思いますが、誤入力で０が０でなくなったらどうするのか。

外部からこの明細データをインポートしているならそこで連番付与させるとか、
どうなのでしょうか。
大丈夫でしょうか。

回答No.5

yy_kd

既存の項目データを消去しておいて
フィルターをかけます。非表示には
番号がふられません。商品名　C列に
データがある間、実行されます。

マクロで　下記を実行
sub renumber()
y=3
　C=1
while cells(y,"C")>""
If Rows(Y).Hidden = False Then
cells(y,"B")=C
c=c+1
endif
y=y+1
wend
end sub

お礼

回答ありがとうございます！

確かに、非表示のものには番号がふられませんでした！
If Rows(Y).Hidden = False Then　で、非表示を確認してるのですかね??

色々と勉強になります＾＾
ありがとうございました。

回答No.4

kagakusuki

以下の方法では駄目なのでしょうか？

　まず、B3セルに次の数式を入力した上で、B3セルをコピーしてB4以下に貼り付けておきます。

=IF(C3="","",ROW()-ROW(B$2))

　そして、フィルタを使用して、件数が0の行を抽出して、削除する。

回答No.3

KURUMITO

回答No２です。お示しの表でしたらB３セルには次の式を入力して下方にオートフィルドラッグします。

=IF(C3="","",SUBTOTAL(3,C$3:C3))

G１０セルには次の式を入力します。

=SUBTOTAL(9,G3:G9)

回答No.2

KURUMITO

フィルタなどの操作を行った後で表示されているデータのついてデータの数を求めたり、合計の金額を求めたりするための関数としてSUBTOTAL関数があります。
ご質問の場合には例えばA2セルには次の式を入力して下方にオートフィルドラッグすればよいでしょう。

=IF(B2="","",SUBTOTAL(3,B$2:B2))

また、合計金額についても今回は件数が０でないデータの合計ですから問題はありませんが件数が１０件以上の金額となればフィルタ操作の後の合計金額は今のままではできませんが次のような式にすればできるようになります。

=SUBTOTAL(9,F1:F7)