EXCELで行を表示しない場合のオートフィル

質問

EXCELで、行を表示していない場合のオートフィルの方法、何か良い方法あるんでしょうか？

今作業しているのは「集計」を使ってその集計された合計の部分だけに連番をつけていきたいのですが、オートフィルをそのまま使うと表示していない行もオートフィルが認識してしまい、合計だけみると飛び飛びの数字になってしまいます。
（標題は行を表示しない場合でも同じ事だと思ったのでこうつけさせてもらいました。同様にオートフィルタ使用時でも同じ事が起こるかと思います）

何か良いお知恵をお貸しください。
仕事で使っていて自分が異動になると使用不可になると困るので、なるべく簡単な方法で…って勝手な話ですがよろしくお願いします。

回答No.1

DoragonFang · ベストアンサー

「集計」を使っているということは、このような感じでしょうか。

　　Ａ列　　Ｂ列　　Ｃ列
1　　　　　項目　　データ
2　　　　　Ａ　　　12
3　　　　　Ａ　　　24
4　　　　　Ａ　　　89
5　　　　　Ｂ　　　123
6　　　　　Ｂ　　　11
7　　　　　Ｃ　　　90
8　　　　　Ｃ　　　47

というデータ（並べ替え後）があって「集計」すると、

　　Ａ列　　Ｂ列　　Ｃ列
1　　　　　項目　　データ
2　　　　　Ａ　　　12
3　　　　　Ａ　　　24
4　　　　　Ａ　　　89
5　　　　　Ａ合計　125
6　　　　　Ｂ　　　123
7　　　　　Ｂ　　　11
8　　　　　Ｂ合計　134
9　　　　　Ｃ　　　90
10　　　　　Ｃ　　　47
11　　　　　Ｃ合計　137

となり、ここで、

　　Ａ列　　Ｂ列　　Ｃ列
1　　　　　項目　　データ
5　1　　　　Ａ合計　125
8　2　　　　Ｂ合計　134
11　3　　　　Ｃ合計　137

というふうにＡ列に連番を入れたいということでよろしんでしょうか。（というかその前提で進めます）

単純には難しいので、上の例でいくと、
Ａ２セルに次の式を入力します。

=IF(RIGHT(B2,2)<>"合計","",SUMPRODUCT((RIGHT($B$2:$B2,2)="合計")*1))

で、この式を集計したまま、総計ところまで下にコピーしてください。

最初のIF関数は合計の行以外は空白を返すために使っています。これがないと、Ｂ列が「Ａ」のときは1,「Ｂ」のときは2,とデータの行まで数字を表示するので、ちょっとうるさいと思って、入れてあります。
そのあとのSUMPRODUCT関数がミソで、Ｂ２セルからそのセルのある行までの「○○合計」というセルの数を数えています。条件判断文のままでは１とカウントせずTRUEが帰ってしまうので、*1として数字にしています。
もちろん集計を解除すれば、無効なので、集計するたびに式のコピーはしてもらわなくてはなりませんが。

お礼

見事これで解決しました。私のつたない質問で真意を汲んでくださりありがとうございます。
SUMPRODUCT関数なんて使ったことが無かったので、思いもよらない方法でした。EXCELはホントに想像力のソフトだなぁと再認識しました。
ただ、集計だと「合計」ではなく「計」と出てた（バージョン違いのせいもあるのかわかりませんが）のでそれと、RIGHT関数の２を１に、の部分だけ直しました。
ホントにありがとうございました。師匠と呼ばせてください。