オートフィルタを使った表で３０行ごとに小計を出したい。Excel2002

質問

部署毎の経費一覧表みたいなのを作成しています。１ヶ月で経費が０円の部署もあるので、オートフィルタで０円の部署を除いた、部署のみプリントします。

　　B列　・・・・　AJ列　　AK列　　　　AN列
　通し番号・・・・　合計　入力番号　　　小計
　　1　　　　　　　　　　　　1
　　2　　　　　　　　　　　　2

　　30　　　　　　　　　　　30　　　　　X円
　　31　　　　　　　　　　　1
　　32　　　　　　　　　　　2　　　　　　　

　　60　　　　　　　　　　　30　　　　　X円
　　61　　　　　　　　　　　1　　　　　 　　
　　62 　・・・・・・・・・ ・・ 2

・B列の番号はオートフィルタで歯抜けにならないようにSUBTOTAL関数を使って連番になるようにしています。
・AK列の入力番号は入力が30件ごとなので、B列の番号に応じてVLOOKUPで1～30が繰り返し表示されるています。

そこで入力番号が30の時に、合計のAJ列の直近の1～30までの小計をAN列に表示したいです。どんな関数を使えばよいのでしょうか？

また端数処理についても悩んでいます。データ件数が毎月バラバラで最終ページでは30件以内の端数がでます。でその端数の部分も端数の小計を表示させられれば、尚良いのですが。　

何卒よろしくお願いします。

回答No.3

shiotan99 · ベストアンサー

No.1 ＆ No.2です。

やっぱり AK列の入力番号はフィルタをかけたら変わるんですね。
AK列の値は固定でテストしていましたので、変動するなら No.2の式ではうまくいかないです。

作業列を使えば単純な式でできると思って試してみました。
詳細は省きますが、テストしてみると条件によってどうもうまくいかないのです。
バグなのかオートフィルタの仕様なのか、とにかく摩訶不思議な現象でした。

結局、どういう条件だとうまくいかないのかわからなかったので、作業列を使う方法はあきらめました。（←←コレにけっこう時間をかけたので返事が遅くなりました ）

■次の数式で試してみてください。

《前提条件》
・データは、5行目から始まっている
・B列には、5行目から最終データ行以外に数値は入っていない

AN5に
=IF(OR(AK5=30,B5=MAX(B:B)),SUM(INDIRECT("AJ"&MATCH(CEILING(B5,30)-29,B:B,0)):AJ5),"")

と入れて、必要なだけ下にフィルコピー

★今回、AK列の入力番号はこちらでも VLOOKUP を使って番号が変動するデータでテストしました。
何回かテストした限りでは正しい小計が出ているように思いますが、自信はありません。フィルタがかかった状態でこのような計算をすると思わぬ落とし穴があるようです。

★これでダメなら、VBAで別シートに抽出・計算させるほうがカンタンで確実だと思います。

お礼

早速数式を貼り付けましたら、何一つ修正する事なく完璧な数値が表示されました。
しかも端数とゆうか30の倍数で終わっていない場合の処理も完璧で、驚嘆してしまいました。
ちょっとあきらめかけていて、手間がかかるけど小計を手で入力していこうと思っていただけに、心が震える程の感動でした。
私の質問に貴重なお時間を割いて頂き、どんな言葉でも言い表せない程、感謝いたしております。
またご丁寧で親切な回答内容で大変分かりやすく、重ねてお礼申し上げます。

本当にありがとうございました。

回答No.4

shiotan99

何度もすみません、No.1 & No.2 & No.3です。

No.3 の補足です。

★No.3 の数式にある
MATCH(CEILING(B5,30)-29,B:B,0)

の -29 は、No.2とは違って、データ開始行に関係なく -29 で固定です。（ 30ごとに小計を出す、という条件が変わらない限り ）

MATCH 関数で、小計を出す範囲の最初の行が何行目なのかを特定しています。
B列の連番が 30なら、連番 1の行、60 → 31 の行、90 → 61 の行…を検索しているということです。
データの最後が 30の倍数で終わっていない場合、たとえば 107 で終わっていても、範囲の先頭 91の行を見つけてきます。
CEILING 関数については、↓をご覧ください。
http://pc21.nikkeibp.co.jp/tech/excel36/12/

ですから、B列の連番は（ SUBTOTALを使っておられるので問題はありませんが ）、1か、または30の倍数＋1 のどちらかで始まっていて、1ずつ加算されていることが絶対条件です（ フィルタがかかっている、かかっていないに関係なく ）。

★その B列の連番ですが、SUBTOTAL はどんな式を入れておられますか？
C列のデータを参照しているとして、もし
=SUBTOTAL(3,$C$5:C5)

という式が入っているとしたら、これではオートフィルタをかけたときに不具合が生じます（ ←EXCELのバグです ）
必ず、
=IF(C5="","",SUBTOTAL(3,$C$5:C5))

のようにしてください。詳しくは↓をご覧ください。ご存知でしたらゴメンナサイ。
http://www.relief.jp/itnote/archives/000439.php

http://www.officetanaka.net/excel/function/tips/tips21.htm

★No.3の数式でもう一度テストしてみましたが、問題はないようです。（ あくまでこちらのテストデータでは、の話ですが‥ ）

お礼

ご教示頂いた関数式は今の私ではちょっと理解できない部分が多く、参照サイトを見ながら勉強させていただきます。CEILING 関数は使った事がありませんで、大変参考になりす。

あと、SUBTOTAL関数につきましては、オートフィルタを使うと連番が歯抜けになって不便だと長い間思っていたのですが、つい先日こちらのサイトの過去の質問を見まして、こんな素晴らしい方法があるなんて！と認識を改めたとこでした。=IF(C5="","",SUBTOTAL(3,$C$5:C5))
の式が入っております。ご丁寧にありがとうございます。

本当に何度もご丁寧に回答下さいまして、心より感謝致します。
とてもよい勉強になりました。

回答No.2

shiotan99

No.1です。

ふと思ったのですが、ひょっとしてオートフィルタの条件は＜小計＞の AN 列ですか？
＜小計＞が 0より大きいものだけを抽出するのでしょうか。

だとしたら No.1の式で、合計を出すのに SUBTOTAL 関数を使っているのはマズイです。
SUM 関数に変えてください。

AN2に

=IF(OR(AK2=30,COUNT(AK:AK)=ROW(A1)),SUM(INDIRECT("AJ"&CEILING(ROW(A1),30)-28):AJ2),"")

と入れて、下にフィルコピーしてください。
注意事項は、No.1と同じです。

いずれにしてもご質問の場合、小計を出すのに SUBTOTAL 関数を使う意味はないような気がしてきました。

お礼

おはようございます。
わかりずらい文章があるなか、ご回答下さいまして本当にありがとうございます。

・0円はAJ列の合計の事です。1ヶ月の使用した経費がAJ列に表示されまして、使用金額が0円の部署は（AJ列が0の場合）フィルターオプションで除いてます。

・AK列の1～30が繰り返して表示される件ですが、別シートにテーブルを作ってます。1→1、31→1、60→30、61→1みたいな単純な方法です。B列の表示が変動しますのでそれに応じてAK列の表示もかわります。

・ご記入頂いた前提条件はすべてその通りですが、4行目まではタイトルとかがありまして、データは5行目からはじまっています。

とゆうことで、ご回答頂いた２つの関数をいれてみました。フィルターオプションをかけていない状態ではまさしく完璧です。
但し、フィルターオプションでAJ列が0と等しくない（0を除く）とすると、30番目には数値が表示されているのですが、正しくない数値です。

AJ列が0の場合を除くとするフィルターオプションをかけた状態で、AK列が1～30までの小計をAN列に表示させたいです。

AN列以降は空白なのでそこに作業列を5行くらい使い悩んでおります。いいとこまでいきそうで完璧になりません。なにかいい方法がありましたらご教示下さいますよう宜しくお願いします。

ご回答心より感謝致します。

回答No.1

shiotan99

こんにちは～

ちょっと確認ですが・・
> オートフィルタで０円の部署を除いた、部署のみプリントします。

この 「0円」 というのはどの列のことでしょうか？　AJ 列ですか？
だとすれば、小計を出す行の合計（ AJ列 ）がたまたま 0だったら、その行（ 小計の行 ）はオートフィルタでは抽出されませんよね。
その場合は、オートフィルタではなく、同じメニューにある フィルタオプションの方で複数列の条件を指定してくださいね。

それと、
> AK列の入力番号は入力が30件ごとなので、
> B列の番号に応じてVLOOKUPで1～30が繰り返し表示されています。

↑がよくわかりませんが、どんな式ですか？
B列の番号は、SUBTOTAL で連番にしているんですよね。
ということは、フィルタをかけたとき B列の番号は変わると思うのですが、それに応じて、ということは、フィルタをかけたとき AK列の入力番号も変わるのでしょうか？

■とりあえず以下の方法を試してみてください。

《前提条件》
・1行目はタイトル行、データは 2行目から
・AK列には、2行目から最終行の間に空白はない
・AK列には、最終行より下に数値は入らない

★ ＜小計＞の AN2 に

=IF(OR(AK2=30,COUNT(AK:AK)=ROW(A1)),SUBTOTAL(9,INDIRECT("AJ"&CEILING(ROW(A1),30)-28):AJ2),"")

と入れて、データ最終行までフィルコピーしてください。
あらかじめ多めにコピーしておいてもかまいません。

もし、データが 3行目から始まっている場合は、
CEILING(ROW(A1),30)-28) の -28 を -27にしてください。
データの開始行が2行目から 1行下がるごとに -27、-26、…となります。
もちろん、数式にある AK2、AJ2 もデータの開始行に合わせてください。
★ただし、ROW(A1) の A1は、何行目から始まっていようと必ずそのままにしておいてください。

SUBTOTAL 関数の代わりに、SUM 関数を使っても結果は同じだと思いますが、
念のため SUBTOTAL 関数を使いました。