Excel 連番の関数（SUBTOTAL）のコピー方法

今使用している下記のような表があるのですが、同じシート内に上下に合計表があります。

SUBTOTALを使ってオートフィルタでＢ列が　a またはｂ　を選んだ時でも
Ａ列が連番になるよう、Ｃ列の合計が絞り込み後もに計算させるように簡単ではありますが、こんな表があるのですが、
今後上下の表の一部データを入れ替えする作業もあり、普通にコピーをしていれかえさせたいのですが、うまくいかない為、
＄絶対参照を変えたりと、いろいろ試しているのですが、うまくいきません。

知恵をお貸し頂きたく、質問させて頂きました。
可能であれば教えて頂きたいのですが、
どうかよろしくお願いします。


-------------------------------------------------------
　　　　 A　　　B　　　　C 　　　A1＝SUBTOTAL(3,$B$1:B1)　
１　　　１　　　ａ　　　　1
２　　　２　　　ｂ　　　　1　　　　 A2～A4＝コピー
３　　　３　　　ａ　　　　1
４　　　４　　　ｂ　　　　1
５　 　合計　ａまたはｂ　　　　　　　C5=SUBTOTAL(3,C2:C4)
６--------------------------------------------------
７　　　１　　　　ａ　　　　1　　　　A7＝SUBTOTAL(3,$B$7:B7)
８　　　２　　　　ｂ　　　　1　　　　A8～A10＝コピー
９　　　３　　　　ａ　　　　1
１０　　４　　　　ｂ　　　　1　　　　　
１１　 合計　 ａまたはｂ　　　　　　Ｃ11=SUBTOTAL(3,$B$7:B7)
-------------------------------------------

No.1 ベストアンサー 回答者： OKERAUMI 回答日時： 2008/10/25 00:15

的外れなら申し訳ありません。

以下、私の所感です。

１行目にはオートフィルタのスイッチが出現する為、b だけを選んでも出現した侭になります。
もし b を選ぶ時には非常時にしたいのであれば、１行下げる必要があると思います。

Ｃ５及びＣ１１で求めたい集計はデータ個数の合計ですか？　データの足し算ですか？
もし足し算なら、SUBTOTALの集計方法は 3 ではなく 9 又は 109 だと思います。

絞込みはオートフィルタだけですか？
もし非表示も併用するなら、SUBTOTALの集計方法は 3 ではなく103 （足し算なら 109） だと思います。

Ｃ５の集計範囲が C2:C4 になっていますが、Ｃ１は集計対象外ですか？
１行目～４行目までが集計対象なら、範囲指定は C1：C4 だと思います。

Ｃ１１は $B$7:B7 になっていますが、集計対象はＢ７だけですか？
もし７行目～１０行目までが集計対象なら、範囲指定は $B$7:B10 だと思います。

この回答への補足

お忙しい中回答ありがとうございます。
質問と表がわかりづらくてすみません。
１行目は、オートフィルタがつく為、文字を左詰めにしています。
Ｃ５及びＣ１１で求めたい集計はデータ個数の合計です。
絞り込みは、オートフィルタのみです。他に方法がわからないもので。。。
数式もまちがっていました。現在下記のようなものが入っています。Ｃ５=SUBTOTAL(3,C1:C4)
Ｃ１１=SUBTOTAL(3,$C$7:C10)
集計方法が9とはわかりませんでした。
合計は上記のもので、なんとか対応できるのかなぁとおもっていましたが、Ａ列がコピーセルの挿入などしてしまうと、1～の連番が崩れてしまうのです。

補足日時：2008/10/25 18:36

No.4 回答者： Daiki_PaPa 回答日時： 2008/10/26 09:35

No.2補足への回答です。

「コピーしたセルの挿入」とは、下の表が下にずれると言うことでしょうか？
それなら、下表の位置を取得するためにB列などに(セルB6)にタイトル("第2表")などを入れ、
セルD1に[=MATCH("第2表",B:B)]を入力。下表の位置が取得できる。
A列の数式は、[=MOD(ROW(),$D$1)]と入力すれば、表がずれても大丈夫です。
複雑になりますけどね。

No.3 回答者： hallo-2007 回答日時： 2008/10/25 10:17

A1に　=SUBTOTAL(3,INDEX(B:B,1):INDEX(B:B,ROW()))

A2～A4へコピィ
C5は　=SUBTOTAL(9,INDEX(C:C,1):INDEX(C:C,ROW()-1))
A7に　=SUBTOTAL(3,INDEX(B:B,7):INDEX(B:B,ROW()))
A8～A10へコピィ
C11は　=SUBTOTAL(9,INDEX(C:C,7):INDEX(C:C,ROW()-1))
では如何でしょうか。的外れでしたらスルーしてください。

C5は　

No.2 回答者： Daiki_PaPa 回答日時： 2008/10/25 09:15

ど～も。

なかなか解りづらい質問ではありますが、
要するに、A列の連番を表示するのに、コピーしても崩れないようにしたいのですよね。
違うようならごめんなさい。

A列の連番は、縦に表が2つなので、
=MOD(ROW(),7)
で表示できます。
MOD関数は、数値を除数で割ったときの剰余を返します。
ROW関数は、行番号を取得します。
もし、2つめの表の行番号が「101」なら、
除数を「100」に設定すると、101行目が「1」になります。

合計以外のB列,C列は数式ではないですよね。
もし数式を入力する場合なら、補足しておいてください。

どうでしょう。

この回答への補足

回答ありがとうございます。
=MOD(ROW(),7)
こんな方法があるとは、とても勉強になります。
説明が足りませんでしたが。コピーしたセルの挿入の
動作をした時は可能なのでしょうか。。。

補足日時：2008/10/25 18:52