Excelで連番が増えるごとに数式範囲も自動的に増やせますか？

質問

Excel2000を使用してます。

ある一覧で、都度、入力数をカウントしてます。
数えるポイントは「ID」という連番の列なんですが、これをいつもフィルで縦に伸ばしてる状態です。

この場合「COUNTA(範囲)」の関数を使用しますが、フィルで伸ばすので、出来たら範囲が自動的に増える関数かVBAがあればと思います。

こんなこと出来ますか？

回答No.5

AloneAgain · ベストアンサー

すみません、No.4です。
訂正です、頭ボケてました^^;

＞自動的に増える関数かVBA

・・でしたね。
No.4 の VBAでは自動的にはデータ件数を表示できません。
いちいち手動でマクロを実行する必要があります。

VBAを使って、自動的にデータ件数を表示させるには
次のようにしてください。
-------------------------
ALT ＋ F11キーで VBEを起動
　　↓
画面左上のプロジェクトエクスプローラーから
該当するシート名をダブルクリック
（Sheet1 に表があるのなら Sheet1 をダブルクリック）
　　↓
右に表示されたコードウィンドウに
以下のコードをコピーして貼り付けてください。
------------- ↓ココから ---------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 2 Then Exit Sub

If Range("B2").Value = "" Then
Range("F2") = 0
Else
Range("F2") = Range("B1").End(xlDown).Row - 1
End If

End Sub
------------- ↑ココまで ---------------------------
　　↓
メニュー ＜ファイル＞－＜終了して Microsoft Excel へ戻る＞
　　↓
オートフィルでB列の連番を増やすと
自動的に F2セルにデータ件数が表示されると思います。
＜条件＞
◆B1 に見出し名が入力されていること。
◆ID番号は B2 から始まること。
◆最終のID番号と下に追加する文との間に空白行が（B列に）あること。
-----
VBAを使うならこんな感じです。
参考まで。

ただし、No.4 に書いたように、
ID番号が数値だけなら（記号との組み合わせではないのなら）
COUNTA関数ではなく、
=COUNT(B:B) でいいような気もするのですが・・。
（下に追加入力するのが数値のみ、ならダメですけど・・）

わかりにくい回答ですみません(>_<)

お礼

AloneAgainさん、再びの細かな回答をありがとうございます。

＞わかりにくい回答ですみません(>_<)

何をおっしゃるんですか！
そんなこと、まるっきりナッシングですよ～！
逆に、私の集計手間まで考えていただいて、ホントにありがたい事です。

最初はフォームのボタンで対応しようと思ったんですが、何しろオジさんがオートフィルでの連番にこだわってるもので(^^;)、今回の記述でそれが可能になったのはとても嬉しいです。

ちなみに、ID以外にも一覧全ての列でカウントすることになったので「COUNT(範囲)」関数は使えないんです。
(ほんと、困ったもんだ・・・)

でも、VBAがこんなに使えると分かっただけで良いお勉強になりました。
本当にどうもありがとうございました！

回答No.4

AloneAgain

こんばんは。

つまりこういうことですか？
------------------------
現在、データ件数を =COUNTA(B:B) という関数で表示している。
　　↓
ただし、これでは表の下に 注）を記入すると、
その 注）もデータとしてカウントされる。
　　↓
当たり前である。
　　↓
ところが知恵は出さぬが口だけは出すオジさんが
なんとかしぃや、できるやろ、パソコンなんやから…
と、無理難題をふっかける。
　　↓
できませんっ！
　　↓
できるて、パソコンなんやから…
　　↓
不毛な戦いが繰り広げられる毎日
　　↓
・・・ウザったい
------------------------
と、こういうことでしたら
No.3 の方の方法では 注）にひっかかりますので、
正しいデータ件数は出ません。

VBAでやるとすれば、↓でどうでしょうか？
※B1が見出しで、データはB2から始まっているとします。
　見出し行をカウントしないように - 1 にしています。
※件数を F2セルに出力する場合です。
◆ID番号が1件以上は入力されていることと、
　表の下に注）を入力する行と ID番号の最終行の間に
　1行以上の空白が（B列に）あることが必須条件です。
--------------------------------------------------------
Sub DataKensu()
Range("F2") = Range("B1").End(xlDown).Row - 1

End Sub
--------------------------------------------------------

または、↓なら ID番号が1件も入力されていない場合
正しく 0 が表示されます。（ 注) との間に空白が必要なのは同じですが）
--------------------------------------------------------
Sub DataKensu2()
Dim i As Long

i = 1
Do Until Cells(i, 2).Value = ""
i = i + 1
Loop

Range("F2") = i - 2
End Sub
---------------------------------------------------------

ただ、わざわざ VBAなんか使わなくても解決策はあると思うのですが・・
（ B列には 下に何も書かない、ということ以外では ）
1）COUNTA 関数ではなく、COUNT関数を使えば
　数値しかカウントしないので 注）が数値でなければカウントされないのでは・・？
　=COUNT（B：B）

あるいは、
2）表の下にどういうものを入力されるのかわかりませんが、
　セルに直接入力するのではなく、
　テキストボックスとかオートシェイプを使えば、
　関数を使ってもカウントはされませんよね？

そんな単純なことではないっ！
または、
そんな姑息な手段ではオジさんを説得できないっ！
・・ということなんでしょうか？
-----
あと、EXCEL についての質問は
コンピューター〔家庭向け〕＞ソフトウェア＞Office系ソフト、
のカテゴリーの方が回答がつきやすいですよ。

お礼

AloneAgainさん、回答ありがとうございます。

＞無理難題をふっかける。
＞　　↓
＞できませんっ！
＞　　↓
＞できるて、パソコンなんやから…
＞　　↓
＞不毛な戦いが繰り広げられる毎日

(爆)！！
もうもう、その通りでございます！
何で会話まで分かったんでしょうか。
特に、注)をテキストボックスで貼り付けるアイデアだって、そのまま「姑息」呼ばわりでした。
まったく困ったオジさんが居るんですよ(-_-;)

でもですね、AloneAgainさんの記述でVBAばっちりでした。
ためしに注)も書いてみましたが、一行離せばちゃんとノーカウントでしたよ。

ホントにホントに、どうもありがとうございました！
(＊カテの件は今後そうします)

回答No.3

kouzi_kouji

sidneyさん　回答遅くなりました。
＞デバッグになる
これは、多分　－　実行時エラー　９　インデックスが有効範囲にありません　－　では、ないでしょうか？
もし、そうだとすれば、ワークシート名の　"ＩＤ順"　だと思います。ＶＢＡのEditer（プログラムを入力する場所）は、""で、明示的に示さない場合、アルファベットと数字は勝手に半角文字に変換してしまう事があります。変数名に全角のアルファベットを用いても勝手に半角のアルファベットにされてしまいます。"ＩＤ順"の　"ＩＤ"が全角か、半角か確かめてみて下さい。また、シート名も全角か、半角か確認して下さい。
＞カウントし始めは、見出し行を抜かした2行目からなんですが、その記述はなくてもいいのでしょうか？
　見出し行を抜かす場合は下記のように　－１して下さい。
最終行 = Worksheets("ID順").Cells(Rows.Count, 2).End(xlUp).Row-1

＞カウントした数の表示先も指定しなくてもいいのでしょうか？
　ＶＢＡで表示させる場合、一番簡単なのは、MsgBoxで表示する方法です。以下の様な行を加えると、ウィンドウの中心部にＯＫボタン付きのダイアログで表示されます。
　MsgBox ("入力数は" & 最終行)
また、シート上の任意の場所に表示することも出来ます。例えば、以下の行でセルＡ１に入力出来ます。
Cells(1, 1).Value = 最終行
もしくは、Range("A1").Value = 最終行
　セルＢ１に入力する場合は　
Cells(1, 2.Value = 最終行
もしくは、Range("B1").Value = 最終行
と、することが出来ます。

このSub カウント() を、呼び出した側で、使用する場合は、最終行をどのプロシージャーにも含まれない位置で、Dim 定義するか、以下のようにします。
Sub カウント(最終行 as Integer)
最終行 = Worksheets("ID順").Cells(Rows.Count, 2).End(xlUp).Row - 1

End Sub

Sub 呼び出し側()
Dim 行数 as Integer

カウント(行数)
・
・
End Sub
　呼び出し側と、カウントサブルーチンとで、上記のように変数名が変わっても大丈夫です。但し、変数の型は同じにして下さい。
　試してみて下さい。

お礼

kouzi_koujiさん、再びの回答ありがとうございます。

VBAじたいは上の方の方法で出来たんですが、私のいちいちの質問に対して、きちんとそれぞれに回答いただけて嬉しかったです。

また「ID順」の「ID」は、記述もシート名も半角でした。
でも、やはりデバッグになります。
現在は注)も一覧の下には無い状態ですが、いまいち何故なのか分かりません。
もう一度ご説明をちくいち読んで、記述を確認しようと思います。

どうもありがとうございました。

回答No.2

kouzi_kouji

こんな、ＶＢＡは、どうでしょうか？
最終行 = Worksheets("ある一覧").Cells(Rows.Count, 1).End(xlUp).Row

(Rows.Count,1)の１は、１列目の事ですので、ＩＤが、Ｂ列の場合は２　Ｃ列の場合は３　に、なります。
ついでながら、漢字の変数名も使えるみたいなので、
dim 最終行 as Integer
として、このまま使用可能です。

補足

kouzi_koujiさん、回答ありがとうございます。
一応下のように書き出しました。
(ちなみに「ID」はB列なので「2」になってます)

Sub カウント()

Dim 最終行 As Integer

最終行 = Worksheets("ID順").Cells(Rows.Count, 2).End(xlUp).Row

End Sub

でもデバッグになります。
カウントし始めは、見出し行を抜かした2行目からなんですが、その記述はなくてもいいのでしょうか。
また、カウントした数の表示先も指定しなくてもいいのでしょうか。
VBAはほとんど初心者なので、出来ましたら再びの回答をお願い致します。

回答No.1

telescope

あらかじめ範囲を多めに取っておいたらどうでしょう。

=COUNTA(A1:A30)
だったら
=COUNTA(A1:A3000)
または、列全体を指定して
=COUNTA(A:A)

お礼

telescopeさん、回答ありがとうございます。

それが、私もtelescopeさんと同じやり方をしようとしたんです。
でも、一緒に仕事してる人が、「それだと何か下に入力する場合、それもカウントされてしまうからダメ」と言われてしまったんです。
ご説明しなくてすみません。

でも、本当はこの方法でも全然かまわないと思うんですよね。
どうもありがとうございました。