subtotal関数でのエラーについて

質問

現在前任者が使っているエクセルファイルを使いやすくするために、エクセル勉強中です。他の方の質問や回答を見させていただいてとてもためになりました！しかし・・・つまずいてしまったので教えてください★
Ａ支店　東京地区　在庫数
　　　　　　　　　収入
　　　　　　　　　支出
Ｂ支店　東京地区　在庫数　
　　　　　　　　　収入　　　　　　　　
　　　　　　　　　支出
Ｃ支店　大阪地区　在庫数
　　　　　　　　　収入
　　　　　　　　　支出
＿＿＿＿＿＿＿＿＿＿＿
合計　　件　　在庫数
　　　　　　　収入
　　　　　　　支出

といった表をオートフィルタを使って、subtotal関数で在庫数、収入、支出の合計と件数を出したいのですが・・・
収入と支出は上手くいくのですが、在庫数が上手くいきません。データはすべて直接入力ではなくＶＬＯＯＫＵＰで持ってきており、収入と支出は00千円という書式設定をしています。この場合、何がいけなくて在庫数だけが＃ＶＡＬＵＥ！という表示になってしまうのでしょうか？
ちなみに、=SUMPRODUCT((MOD(ROW(データ行),3)=0)*SUBTOTAL(2,INDIRECT("Ｃ"&ROW(データ行)))*データ行)という式を入れています。
また、たとえば東京地区のみの件数の出し方を教えてください。
わかりにくい質問で申し訳ありませんが、どなたかご回答宜しくお願いいたします。

回答No.6

Wendy02 · ベストアンサー

こんばんは。Wendy02です。

追伸：

#前回の範囲と同じということにすれば、以下のようにしか今は思いつきません。

私は、なるべく配列の確定をしないで、できる数式がないか探していました。今、やっと、私のイメージにあるものが、出来ましたので、ここに書いておきます。（使わなくても結構です)

4行目, 7行目, 10行目, 13行目　のそれぞれのセルを個別に計算する数式です。

=SUMPRODUCT(SUBTOTAL(9,INDIRECT("C"&ROW(INDIRECT("C1:C"&ROWS(C2:C13)/3))*3+1)))

お礼

Wendy02様
ありがとうございます。ずばり私の求めていた回答でした！できるんですね～。感激です。
せっかく教えていただいた式も、内容をわかっていなかったら応用できないのでがんばって勉強します＾＾配列数式も使ったことがなくちょっと抵抗があったので、今回の式を使わせていただきました！社会人二年目でまだまだな私ですが、Wendy02さんを目標にがんばります！！

回答No.7

Wendy02

mipmip様
こんにちは。Wendy02です。

私も感激です!

配列数式は、一種のパターンで、通常、誰かが考えたパターンを借りているに過ぎません。そんなに多くのパターンがあるわけではありません。前任者の方は、たぶん、達人に近いほうだと思います。元の式は、私個人ですと、じっくりと考える手間を惜しんで、たぶん、VBAで作ってしまうだろうと思います。

それで、今回の数式は、今までにはないオリジナル・パターンが出来たので、大変にうれしいです。最初は、ダメかと思いました。

そこで、その数式の解析しかたを教えておきます。
これは、私が教わった、配列数式の作り方のコツです。

=SUMPRODUCT(SUBTOTAL(9,INDIRECT("C"&ROW(INDIRECT("C1:C"&ROWS(C2:C13)/3))*3+1)))
================================================

メニューの中の[ツール]－[ワークシート分析]－[数式の検証]というのがありますが、これでは、小さくてよく分かりません。

それで、このようにします。

数式の中を、マウスで範囲を取って、F9 を押しますと、中身のデータが出てきます。
そうすると、その出てきたデータによって、考え方が分かるはずです。

INDIRECT("C1:C"&ROWS(C2:C13)/3)
　　↓

ROW(INDIRECT("C1:C"&ROWS(C2:C13)/3))*3+1
　　↓

INDIRECT("C"&ROW(INDIRECT("C1:C"&ROWS(C2:C13)/3))*3+1)
　　↓

SUBTOTAL(9,INDIRECT("C"&ROW(INDIRECT("C1:C"&ROWS(C2:C13)/3))*3+1))

このようにして、だんだん広げていきます。
2番目の「ROW(INDIRECT...」ところが、少し、ややこしいように思います。
そこをクリアすれば、後は、加工できるかと思います。作るときは、そうして一番中心になる部分から付け足して作っていきます。このコツが分かると、すぐに出来るようになります。

回答No.5

Wendy02

mipmip　様
Wendy02です。

前回の範囲と同じということにすれば、以下のようにしか今は思いつきません。

=SUM(IF(SUBTOTAL(3,INDIRECT("C"&ROW(C2:C13)))*(MOD(ROW(C2:C13),3)=1),C2:C13,0))

これは、配列数式ですから、『配列の確定』が必要です。数式は、{=...}で囲まれます。

『配列の確定』
一旦、数式のところで、F2を押して、再度、SHIFT キーとCTRL キーを押しながら、ENTER を押すと、式が実体化して、値が出てきます。

お礼

Wendy02様
できました～～～！！！！すごいうれしいです！
ほんとにほんとにありがとうございました！！お世話になりました(>_<)

回答No.4

Wendy02

mipmip　様
こんにちは。Wendy02です。

そうでしたか! 昔、一度出合ったことがある、配列の範囲をずらす技が必要かと思いましたので、それは、難問だなって思いましたが、どうやら違ったようです。（逆にヨカッたです)

>支店数の合計のみエラーになってしまったんです。

例えば、昨日と同じように、地区ブロックを4つ設けてありますので、13行までになりますが、このようにすれば出ますね。(3 ×　4 + 上部の項目1行 = 13 行)

支店数

=SUMPRODUCT(SUBTOTAL(3,INDIRECT("C" & ROW(C2:C13)))*(ISERROR(FIND("支店",C2:C13))=FALSE))

>本当はほかにもっといい方法があるのかもしれないですが。。。

東京とか大阪が同じ文字が三つ並んでいますから、ブロックの中の2行目の東京なんかは、フォントの色を白で消してあげると、きれいに見えるかもしれませんね。（こちらに分かりやすく書いてくださっているので、もう、なさっているかもしれませんが。)

それ以外については、私には、表自体は、これは完成されたものとしか見られません。私も、会社勤めの経験で、同じような表は見てきましたから、この表なら、どこの会社でも定番の表だと思います。

これに、VLookUp とか、オートフィルタがついているのですから、これ以上のものを望むのは、贅沢かもしれませんネ。^^;　初期は、このような表は、ワープロで作っていたのですから、夢のようです。

うまくいくと良いのですが・・・。

お礼

Wendy02様
ありがとうございます！！支店数合計、上手くいきました＾＾
支店数合計はsubtotalのカウントを使って、やってるんですよね。うまくいくと、エクセルはすかーっとしてほんとにうれしいです。・・・あと一回だけお力をお借りしてもいいですか？
この表で、取扱件数合計は、やはり支店名やオープン日があるため、エラーになります。その切り抜け方を教えて下さい(>_<)ずうずうしくいろいろお願いしてしまってすいません。

回答No.3

Wendy02

こんばんは。Wendy02です。

ずっと、半日、頭の中でぐるぐると同じ絵が回っていた感じがします。^^;

いまさら大変すみませんが、私は、その表の中で、どのようにして、オートフィルタは使っているのか良く分からなくなったのです。ちょっと確認しながら、回答させてください。

エラーになる原因は、たぶん、(データ行)というのを、それぞれの計算をさせるために、ずらしてあったのですね。たぶん、実際の式を書いてくだされば分かったのですが、それは、かなり高度なテクニックです。私は、間違いなく、これは難問のひとつとして、挙げられると思います。

　　　Ａ　　　　　　　Ｂ
＊
１　　○支店　　　　　収入
２　　オープン日　　　支出
３　　取扱件数　　　　差引
４　　地区
_________________________
５　　★支店　　　　　収入
６　　オープン日　　　支出
７　　取扱件数　　　　差引
８　　地区
_______________________
・
・
・
＿＿＿＿＿＿＿＿＿＿＿＿＿
　　合計件数　　　　合計収入
　　　　　　　　　　合計支出
　　　　　　　　　　合計差引

*　1行目に、項目名を入れないと、オートフィルタはうまくいかないと思いますが、それはどのようにしていますか？

次に、今の状態では、オートフィルタでは、地区別というか、例えば、東京地区という分類は、4行目・8行目は選べても、

地区ブロック
┌───────────┐
│　○支店　　　　　収入
│　オープン日　　　支出
│　取扱件数　　　　差引
│　東京地区　　　　　　
└───────────┘

という、ブロックでは見られませんよね。東京地区という表示の中で、一番下に、
合計を出すということになりますね。

（修正表)
　　　Ａ　　　　　　Ｂ
１　　支店名　　　　金額　　←項目名を加えました
─────────────
２　　○支店　　　　　収入
３　　オープン日　　　支出
４　　取扱件数　　　　差引
５　　東京地区
─────────────
６　　★支店　　　　　収入
７　　オープン日　　　支出
８　　取扱件数　　　　差引
９　　大阪地区
─────────────

オートフィルタを使えば、

1　　支店名　　　　金額　　←項目名を加えました
5　　東京地区

としか出てきませんね。
以下は、上で書いた地区ブロックを4つ下に作ってみて、数式を考えてみました。

それで、それぞれの地区の合計をオートフィルタで出すとすると、

合計収支の計算式は、以下のようになります。

今は、項目名を入れて、A1:B17 の範囲にデータがあるとします。（修正表)
A19:

合計収入

B19:
　(B18は、一行空けました。もし、くっつける場合は、オートフィルタの領域を再度設定したほうがよいです。数式までをオートフィルタの範囲とされます。)

=SUMPRODUCT((MOD(ROW($A$5:$A$17),4)=1)*SUBTOTAL(3,INDIRECT("A"&ROW($A$5:$A$17)))*B2:B14)
フィルダウンコピーで、式をB21 までコピーします。

A20:
合計支出
B20:
=SUMPRODUCT((MOD(ROW($A$5:$A$17),4)=1)*SUBTOTAL(3,INDIRECT("A"&ROW($A$5:$A$17)))*B3:B15)

A21:
合計差引
B21:
=SUMPRODUCT((MOD(ROW($A$5:$A$17),4)=1)*SUBTOTAL(3,INDIRECT("A"&ROW($A$5:$A$17)))*B4:B16)
に入れました。

このようにしたらいかがでしょうか？
解説すると、最初の「地区」のセルを基点とします。気をつけなくてはならないのは、それぞれの計算する行の B2:B14, B3:B15, B4:B16 の領域は、同じ長さ（サイズ)にしなければなりません。そうしないとエラーが出ます。

本来は、ピボットテーブルやデータメニューの中の「集計」のほうが便利かもしれません。
いまさらですが、かなり凝った表のような気がします。

お礼

Wendy02さん
本当に親切に答えていただいて、ありがとうございます。初めて教えてgoo!を利用したのですが、こんなに親切に答えていただけて本当に感謝です。つたない説明で、申し訳ありません。質問に関連する部分だけの抜粋のつもりで省略した表の説明をさせていただいたのですが、やはり全部関連しているのできちんと説明するべきでした。親切に答えていただいたのを説明不足で無にするようでいまさらながら、本当にすみませんでした。実際は下のような表です。

　Ａ　　　　　Ｂ　　Ｃ　　　　Ｄ　　Ｅ　　Ｅ・・・
１　支店コード　地区　支店情報　項目　４月　５月・・・
２　　1　　　東京　　○支店　　収入
３　　　　　 東京　　オープン日　　支出
４　　　　　 東京 　取扱件数　 差引
________＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿

５　　2　　　大阪　　★支店　　収入
６　　　　　 大阪　　オープン日　　支出
７　　　　　 大阪 　取扱件数　 差引
________＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
・
・
・
_______＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
　　　　　　合計　　　　　　収入
　　　　　　支店数　　　　　支出
　　　　　　　　　　　　　　差引
このようになっていて合計を出し、オートフィルタで地区ごとに合計収入・合計支出・合計差引を月ごとに出してプリントアウトしています。そこまでは上手くいったのですが、支店情報の行はＶＬＯＯＫＵＰで支店名やオープン日が入っているので支店数の合計のみエラーになってしまったんです。それで、この質問をさせていただいたのです。もともと私にそれほど知識がないのと、前任者から引き継いだ表なので、本当はほかにもっといい方法があるのかもしれないですが。。。

回答No.2

Wendy02

こんにちは。

>計算範囲には1項目（1支店）に付文字が入っています。

=SUMPRODUCT((MOD(ROW(データ行),3)=0)*SUBTOTAL(2,INDIRECT("C"&ROW(データ行)))*データ行)
<半角修正済み>

この中の３番目の「データ行」に文字列が入っていたら、それは、エラーが出ますね。
その配列式は、掛け算になっていて、IF条件文になっていないので、エラーの回避が出来ません。

>他に方法はないですか？

そうすると、一般的には、現在の配列方式をやめる以外は方法がないように思います。

例えば、このような式です。
=(MOD(ROW(),3)=0)*SUBTOTAL(2,C2)*C2

それで、最後に、SUM関数で足してやります。もし、途中でエラーが出ているようでしたら、その部分は修正してあげます。

他に、ちょっと長くなるけれど、こんな式でも良いですね。
=IF(ISERROR((MOD(ROW(),3)=0)*SUBTOTAL(2,C2)*C2),0,(MOD(ROW(),3)=0)*SUBTOTAL(2,C2)*C2)

それで、SUM関数で合計を出せば、エラーは出しません。本当は、もっとうまいやり方があるはずですが、今の文字情報だけでは、限界があります。

お礼

ご回答していただき、ありがとうございます★エラーの理由も説明していただいてとても勉強になります！
今回のアドバイスなんですが・・・私の知識不足で、=(MOD(ROW(),3)=0)*SUBTOTAL(2,C2)*C2の式から、sum関数で足すやり方がわかりません。。詳しく教えていただけますか？？
＜データ情報の捕捉＞
sheet１に、
支店名　オープン日　取扱件数　住所などなど・・
それをＶＬＯＯＫＵＰで、sheet2に
　　　Ａ　　　　　　Ｂ
１　　○支店　　　　　収入
２　　オープン日　　　 支出
３　　取扱件数　　　差引
４　　地区
_________________________
５　　★支店　　　　　収入
６　　オープン日　　　 支出
７　　取扱件数　　　差引
８　　地区
_______________________
・
・
・
＿＿＿＿＿＿＿＿＿＿＿＿＿
　　　合計件数　　　合計収入
　　　　　　　　　　合計支出
　　　　　　　　　　合計差引

この表で、オートフィルタで地区ごとに合計を出すべく、エクセル改良中です。宜しくお願いします！！

回答No.1

Wendy02

こんにちは。

=SUMPRODUCT((MOD(ROW(データ行),3)=0)*SUBTOTAL(2,INDIRECT("Ｃ"&ROW(データ行)))*データ行)

"Ｃ"　が、全角になっているのは、ご愛嬌として、
オートフィルタの一番上は、項目の文字として、在庫の2行目を対象とするので、MOD(ROW(C2:C13),3)=2 と
して計算してみました。

=SUMPRODUCT((MOD(ROW(C2:C13),3)=2)*SUBTOTAL(3,INDIRECT("C"&ROW(A2:A13)))*C2:C13)

と実際に計算してみて、特に、エラーは発生しないようなのです。数式と計算も検証してみましたが、問題はありませんでした。オートフィルタで正しく計算してくれます。

その上記の全角文字以外としてあるのは、計算範囲に文字を入れているのではないでしょうか？

お礼

ご回答ありがとうございます！質問内容には書かなかったのですが、計算範囲には1項目（1支店）に付文字が入っています。その場合上手くいかないのですね。。他に方法はないですか？すいません(>_<)