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

Question

Excel2000を使用してます。

ある一覧で、都度、入力数をカウントしてます。
数えるポイントは「ID」という連番の列なんですが、これをいつもフィルで縦に伸ばしてる状態です。

この場合「COUNTA(範囲)」の関数を使用しますが、フィルで伸ばすので、出来たら範囲が自動的に増える関数かVBAがあればと思います。

こんなこと出来ますか？

AloneAgain · Accepted Answer

すみません、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 · Answer

こんばんは。

つまりこういうことですか？
------------------------
現在、データ件数を =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系ソフト、
のカテゴリーの方が回答がつきやすいですよ。

kouzi_kouji · Answer

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 · Answer

こんな、ＶＢＡは、どうでしょうか？
最終行 = Worksheets("ある一覧").Cells(Rows.Count, 1).End(xlUp).Row

(Rows.Count,1)の１は、１列目の事ですので、ＩＤが、Ｂ列の場合は２　Ｃ列の場合は３　に、なります。
ついでながら、漢字の変数名も使えるみたいなので、
dim 最終行 as Integer
として、このまま使用可能です。

telescope · Answer

あらかじめ範囲を多めに取っておいたらどうでしょう。

=COUNTA(A1:A30)
だったら
=COUNTA(A1:A3000)
または、列全体を指定して
=COUNTA(A:A)

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

すみません、No.4です。

こんばんは。

sidneyさん 回答遅くなりました。

こんな、ＶＢＡは、どうでしょうか？

この回答への補足

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

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

sidneyさん　回答遅くなりました。