エクセルでSUBTOTALとSUMIFを組み合わせられますか？

質問

エクセル関数でSUBTOTALとSUMIFを組み合わせたいです。

例えば八百屋さんでの集計で、
ある期間の野菜別の合計とさらに任意の期間の合計をだします。

A列は野菜の名前が入っています。
B列は値段が入っています。

A
きゅうり
ピーマン
なす
かぼちゃ
かぼちゃ
なす
もやし
にんじん
もやし

以下続く

隣のＢ列には値段が入っています。（野菜の値段は時期によって違います）

SUMIFでセール期間の野菜ごとの合計をＢ列にだしました。
さらに、いくつかのセール期間の野菜ごとの合計もＢ列に出したいです。

わがままですが、列はふやせません。
どうしても重複して計算してしまい、うまくいきません。

回答No.3

noname#77845 · ベストアンサー

【A42】ピーマン【B42】[B1:B42の合計の出し方？？
これだけなら、
=SUMIF(A1:A41,"ピーマン",B1:B41)
で出ると思いますが…。
小計には「小計」と入力されているなら、
=SUMIF(A1:A42,"小計",B1:B42)
でもOK。
但し、前のお礼に記載されていたようなアバウトな条件での検索となると、関数だけでやるにはかなり複雑になりますよ？
アバウトな条件でSUBTOTALを使うなら、期間の開始・終了、品目、計算方法を入力する欄を設けて、INDIRECT関数を組み合わせれば求められるような気がします。（未検証）

お礼

何度もありがとうございます。

多人数で使う会社の物なのでいろいろ制約があり難儀していました。
今のままでは難しそうなので、
もう一度入力方式の見直しをお願いして、INDIRECT関数の使い方を調べてみます。

回答No.4

n_na_tto

ところどころに小計がある、下のような表なのでしょうか。
そして"小計""中計"の見出し[下の例ではC列]は
どうしても追加できない、と。

＿＿＿＿＿_A＿＿＿＿_B_
_1＿きゅうり＿＿＿_130_
_2＿ピーマン＿＿＿_190_
_3＿＿＿なす＿＿＿_230_
_4＿かぼちゃ＿＿＿_100_
_5＿かぼちゃ＿＿＿_190_
_6＿＿＿なす＿＿＿_240_
_7＿********＿********_
_8＿かぼちゃ＿＿＿_290_小計B1:B6
_9＿********＿********_
10＿＿＿なす＿＿＿_280_
11＿ピーマン＿＿＿_250_
12＿かぼちゃ＿＿＿_110_
13＿＿＿なす＿＿＿_150_
14＿かぼちゃ＿＿＿_120_
15＿ピーマン＿＿＿_270_
16＿********＿********_
17＿かぼちゃ＿＿＿_230_小計B10:B15
18＿********＿********_
19＿かぼちゃ＿＿＿_520_中計B1:B17

【方法1・小計も含めて合計して、最後に2で割る】
B8
=SUMIF(A1:A6,A8,B1:B6)
B17
=SUMIF(A10:A15,A17,B10:B15)
B19
=SUMIF(A1:A18,A19,B1:B18)/2
《欠点》逆に必ず重複して合計するように
B19の範囲を設定しなければならないので、
注意が必要。

【方法2・小計、中計は常に文字列として挿入、無視】
SUMやSUMIFは、範囲内の文字列は無視して数値のみ
合計してくれることを利用。
小計、中計は最後に&""をつけて文字列にして、
重複して計算されないようにしておく。

B8
=SUMIF(A1:A6,A8,B1:B6)&""
B17
=SUMIF(A10:A15,A17,B10:B15)&""
B19
=SUMIF(A1:A18,A19,B1:B18)&""
《欠点》小計の式もすべて修正しなければならない。
数値は右揃え、文字列は左揃えの配置になるが、
見た目の区別がしにくい。

お礼

ありがとうございます。

まさにこんな感じの表です。
2で割るのも文字列にするのも盲点でした。

柔軟な発想力を持ちたいものです。

回答No.2

noname#77845

＃１です。
補足読みました。

なら、さらに判らないことが…。

期間はどうやって判定するのですか？
（A列の名前とB列の値段しかないのに…。）
何か判断する基準がないとどうしようもないと思いますが。

補足してください。

お礼

かえって混乱させてしまった気がするので、補足の補足です。

ピーマン
ナス
以下ランダムに続く

いったん21行目に小計をだす。

【A21】ピーマン【B21】 [=SUMIF(A1:A20,"ピーマン",B1:b20)]

ピーマン
ナス
以下続く

いったん42行目に小計をだす。
【A42】ピーマン【B42】 [=SUMIF(A22:A41,"ピーマン",B22:B41)]

これを繰り返します。

A1～A42のピーマンの合計
【A42】ピーマン【B42】[B1:B42の合計の出し方？？]

今は、小計を[=SUM(A21,A42)]でやっていますが、量が多くてミスが怖いです。

補足

ありがとうございます。

期間は任意で選択しますので、自分で選びます。

「最近ピーマン高くなったけど売り上げどれくらいかな？」（B100:B200)
「先週の売り上げどれくらいだった？」（B150:B200)
「月ごとも知りたいね」（B1:B200)
という感じです。
縦方向に時系列で入れていきます。

回答No.1

noname#77845

補足してください。

セール期間の値段、他のセール期間の値段及びセール期間じゃない時の値段はどうやって区別するのですか？

補足

B列の値段は個々に入れていきますので値段の区別はしません。

例えばピーマンにも産地や、ばら売り等で値段は変わります。
ある期間のピーマンの売上額はいくらか？
そして、その期間を含めたより広い範囲（例えば季節ごと）の合計のピーマンの売上額は？
をすべてB列に出したいです。