- ベストアンサー
subtotal関数で在庫数の計算がうまくいかない問題について
- エクセル勉強中で、他の方の質問や回答を参考にしている中で、subtotal関数を使用して在庫数、収入、支出の合計と件数を計算しようとしています。
- 収入と支出の計算はうまくいくのですが、在庫数の計算がうまくいきません。データが直接入力ではなくVLOOKUP関数で取得されており、収入と支出には00千円という書式設定がされています。
- 在庫数の計算には以下の式を使っています: =SUMPRODUCT((MOD(ROW(データ行),3)=0)*SUBTOTAL(2,INDIRECT("C"&ROW(データ行)))*データ行)。東京地区の件数の計算方法も教えてほしいです。
- みんなの回答 （7）
- 専門家の回答
質問者が選んだベストアンサー
その他の回答 (6)
- Wendy02
- ベストアンサー率57% (3570/6232)
- Wendy02
- ベストアンサー率57% (3570/6232)
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様 できました～～～！！！！すごいうれしいです！ ほんとにほんとにありがとうございました！！お世話になりました(>_<)
- Wendy02
- ベストアンサー率57% (3570/6232)
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のカウントを使って、やってるんですよね。うまくいくと、エクセルはすかーっとしてほんとにうれしいです。・・・あと一回だけお力をお借りしてもいいですか？ この表で、取扱件数合計は、やはり支店名やオープン日があるため、エラーになります。その切り抜け方を教えて下さい(>_<)ずうずうしくいろいろお願いしてしまってすいません。
- Wendy02
- ベストアンサー率57% (3570/6232)
こんばんは。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 大阪 ★支店 収入 ６ 大阪 オープン日 支出 ７ 大阪 取扱件数 差引 ________＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ ・ ・ ・ _______＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿ 合計 収入 支店数 支出 差引 このようになっていて合計を出し、オートフィルタで地区ごとに合計収入・合計支出・合計差引を月ごとに出してプリントアウトしています。そこまでは上手くいったのですが、支店情報の行はＶＬＯＯＫＵＰで支店名やオープン日が入っているので支店数の合計のみエラーになってしまったんです。それで、この質問をさせていただいたのです。もともと私にそれほど知識がないのと、前任者から引き継いだ表なので、本当はほかにもっといい方法があるのかもしれないですが。。。
- Wendy02
- ベストアンサー率57% (3570/6232)
こんにちは。 >計算範囲には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に Ａ Ｂ １ ○支店 収入 ２ オープン日 支出 ３ 取扱件数 差引 ４ 地区 _________________________ ５ ★支店 収入 ６ オープン日 支出 ７ 取扱件数 差引 ８ 地区 _______________________ ・ ・ ・ ＿＿＿＿＿＿＿＿＿＿＿＿＿ 合計件数 合計収入 合計支出 合計差引 この表で、オートフィルタで地区ごとに合計を出すべく、エクセル改良中です。宜しくお願いします！！
- Wendy02
- ベストアンサー率57% (3570/6232)
こんにちは。 =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支店）に付文字が入っています。その場合上手くいかないのですね。。他に方法はないですか？すいません(>_<)
お礼
Wendy02様 ありがとうございます。ずばり私の求めていた回答でした！できるんですね～。感激です。 せっかく教えていただいた式も、内容をわかっていなかったら応用できないのでがんばって勉強します＾＾配列数式も使ったことがなくちょっと抵抗があったので、今回の式を使わせていただきました！社会人二年目でまだまだな私ですが、Wendy02さんを目標にがんばります！！