こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

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


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

以下続く

隣のB列には値段が入っています。(野菜の値段は時期によって違います)

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

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

投稿日時 - 2009-01-31 11:02:48

QNo.4676463

困ってます

質問者が選んだベストアンサー

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

投稿日時 - 2009-01-31 13:02:38

お礼

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

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

投稿日時 - 2009-01-31 13:36:29

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

ところどころに小計がある、下のような表なのでしょうか。
そして"小計""中計"の見出し[下の例では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)&""
《欠点》小計の式もすべて修正しなければならない。
数値は右揃え、文字列は左揃えの配置になるが、
見た目の区別がしにくい。

投稿日時 - 2009-01-31 14:15:25

お礼

ありがとうございます。

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

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

投稿日時 - 2009-01-31 14:59:33

ANo.2

#1です。
補足読みました。

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

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

補足してください。

投稿日時 - 2009-01-31 11:50:52

補足

ありがとうございます。

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

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

投稿日時 - 2009-01-31 11:51:27

お礼

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


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

いったん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)]でやっていますが、量が多くてミスが怖いです。

投稿日時 - 2009-01-31 12:44:30

ANo.1

補足してください。

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

投稿日時 - 2009-01-31 11:09:06

補足

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

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

投稿日時 - 2009-01-31 11:41:24

あなたにオススメの質問