- ベストアンサー
Excel関数:SUBTOTALとSUMIFを組み合わせる?
下記のような場合の集計ができる関数を教えて下さい。 SUBTOTALとSUMIFを組み合わせようと思ったのですが、うまくいきませんでした。 よろしくお願いします。 <表の内容> ・セルA1:「ランク」 ・セルA2~A50:「A」or「B」or「C」を入力している ・セルB1:「金額」 ・セルB2~B50:各金額を入力している <集計方法> ・セルB51に「ランクが"A"」の金額合計を表示したい ・ただしオートフィルターを使って他の条件で抽出もおこなっているのでSUBTOTAL関数のように、表示されている行の値のみを合計したい
- haruko2005
- お礼率98% (60/61)
- オフィス系ソフト
- 回答数3
- ありがとう数13
- みんなの回答 (3)
- 専門家の回答
質問者が選んだベストアンサー
オートフィルタ実行時でも可能な集計です。 =SUMPRODUCT((SUBTOTAL(3,INDIRECT("A"&ROW(A2:A50))))*(A2:A50="A")*(B2:B50)) で試してみてください。 B51セルだとフィルタ実行時に非表示になるかもしれませんので、もう少し下のセルに入れてください。
その他の回答 (2)
SUBTOTAL関数だけで駄目なら、その理由(例を示せばOKかと)を教えてください。
- mshr1962
- ベストアンサー率39% (7418/18948)
SUBTOTALで条件付けなら、ランクも条件にするしかないですね。 SUMPRODUCTを使う場合 =SUMPRODUCT(($A$2:$A$50="A")*($B$2:$B$50)*(他の条件)) 条件は基本的にその列の範囲と等号・不等号か関数を使います。 金額の積の和なら列の範囲だけを設定してください。 DSUMの場合 表とは別の範囲に ランク 条件2 条件2 条件3 '条件がFrom-Toの場合は同じ項目をもう1列設定 A =">100" ="<200"=" <>&"XXX" =DSUM("表の範囲","集計する項目名","条件のセルの範囲")
お礼
早速のご回答ありがとうございます。 やはり条件も数式に入れるしかないのでしょうか…。 利用する人があまりExcel関数に詳しくないので、できればデータベース関数も使わない方がいいのですが、なかなか難しそうですね。
関連するQ&A
- Excel関数(SUMIFの応用?)
いつも参考にさせて頂いています。 現在、ある集計表をつくっていますが集計用の関数がわからなくて困っています。 <表の内容> ・セルA1:「ランク」 ・セルA2~A10:「A」or「B」or「C」を入力している ・セルB1:「要望」 ・セルB2~B10:「あり」or「なし」or「不明」を入力している ・セルC1:「金額」 ・セルC2~C10:各金額を入力している <集計> ・セルC11に「ランクが"A"」かつ「要望が"あり"」の金額合計を表示 ・セルC11に「ランクが"C"」かつ「要望が"なし"」の金額合計を表示 ・・・等々 この集計にはどのような関数を使えばよいでしょうか? 表を利用する者がDatabase関数は理解していないので、Database関数以外で使えるものがありましたら、教えてください。 今はD列に「=A2 & B2」のような式を入れて「=SUMIF(D2:D10,"Aあり",C2:C10」として集計していますが、もう少しスマートな方法があれば、教えていただきたいと思います。 どうぞよろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- Excel2003 ユーザー定義関数 で SUBTOTALとSUMPRODUCTの複合できますか?(複数条件)
こんばんは。お願いします。 まず、セルA2、B2にそれぞれフィールド名"あ"、"い"があるとします。 この2列目以下にオートフィルターを使います。 A列のA3,A4…には文字列"a","b","う",または"かき"が入っていて、 B列のB3,B4…には数値(整数)が入っているとします。 このとき、セルB1にVBAで作成した関数を入れたいと思っています。 Excel2003です。 その関数は、以下の条件(1)と(2)を同時に満たします。 (1)A列が"a"または"かき"である場合のみ、その行のB列の数値の合計を計算する(SUMIF関数で可能) (2)オートフィルターを用いて抽出したら、表示されているB列の数値だけの合計を計算する(SUBTOTAL関数で可能) (3)作業列は使えません。 要するに、表示されているセルのうち、条件にあうもののみ計算したい、ということです。 もしこのようなことがワークシート関数でもできるのであれば、それで構いません。 どうぞ、よろしくお願いいたします。
- 締切済み
- その他MS Office製品
- EXCEL関数
お世話になります。 現在、EXCEL関数で悩んでおります。 例えば 売上ランク 数量 単価 商品A B 20 300 商品B C 10 520 商品C A 72 430 商品D A 53 290 商品E B 31 330 をEXCELに入力をしているとします。 このデータをABCランク毎に集計し、売上金額を自動計算したいと考えています。 SUMIFでは個々の数量や単価の集計はできますが、どのようにすればランク毎の売上金額を計算できますでしょうか? 決算時期に近付いており、膨大なデータを効率的に集計したいので、ご存じの方がいらっしゃいましたらご教授ください。 ※データからAランクだけを抽出し、その数量と金額の合計を出したいです。 よろしくお願いいたします。
- ベストアンサー
- Excel(エクセル)
- エクセルのSUBTOTALについて
エクセルのSUBTOTALという関数についてなのですが、 例えば A B C D E 1 2 日付 品名 担当者 金額 キャンセル 3 7/1 文具 伊藤 1,000 * 4 7/5 青果 工藤 1,230 5 8/3 雑貨 佐藤 1,500 * 6 8/11 文具 伊藤 5,200 7 9/13 衣料 鈴木 7,800 といった表で、オートフィルタで、8月分だったら8月分だけを絞れるようにしています。 D1のセルに、金額の合計が表示されるようにしたいとおもい、オートSUMで関数を入れたところ、 「=SUBTOTAL(9,D5:D200)」といった関数になって、フィルタをかけないときは全部の合計で、8月分だけ絞ると8月分だけの合計が表示されるようになりました。 そこで、キャンセルのところも集計して、同じような集計をしたいのです。最初はキャンセル発生の場合は「1」とでも入力することにして集計しようと思ったのですが、あいにくキャンセルの場合は「*」を入力する事になっていて、理由としては条件付書式で「*」を入力すると、その行の色が変わるように設定している為です。 この場合、E1のセルに、キャンセル数(8月分だけ絞った場合は8月分だけの集計)を表示させることは出来ますか????
- ベストアンサー
- オフィス系ソフト
- エクセルでフィルター結果をSUBTOTALする
エクセル2010で、フィルターの結果をSUBTOTAL関数でB1セルに表示したとして、その横のセルA1セルにフィルターした項目を表示させたいのですが。 例えばSUBTOTALした「合計金額」が何の項目かを表示させないと分かりにくいので何か方法があれば教えてください。 フィルターオプション以外の方法でお願いします。
- ベストアンサー
- Excel(エクセル)
- SUBTOTAL関数の不思議
SUBTOTAL関数で期待する合計が出せません。 A列 B列 C列 D列 品名 数量 単価 金額 A 1 100 100 B 2 200 400 C 3 300 900 小計 0 →ここが期待どおりでない D2セルの式は =IF(A2="品名",SUBTOTAL(9,INDIRECT("D$2:D"&ROW()-1)),B2*C2) でD5までコピーしました。 つまりA列が"小計"でないときは数量×単価を、"小計"と入力されていたらD2:D4をSUBTOTALで集計するつもりなのに結果が0になってしまいます。 ちなみにD5に =SUBTOTAL(9,D2:D4) と手入力しても結果は0でしたが、D列に金額を直接入力すれば小計は正しく表示されます。 理由がおわかりの方、教えてくださいませんか
- ベストアンサー
- オフィス系ソフト
- エクセルでSUBTOTALとSUMIFを組み合わせられますか?
エクセル関数でSUBTOTALとSUMIFを組み合わせたいです。 例えば八百屋さんでの集計で、 ある期間の野菜別の合計とさらに任意の期間の合計をだします。 A列は野菜の名前が入っています。 B列は値段が入っています。 A きゅうり ピーマン なす かぼちゃ かぼちゃ なす もやし にんじん もやし 以下続く 隣のB列には値段が入っています。(野菜の値段は時期によって違います) SUMIFでセール期間の野菜ごとの合計をB列にだしました。 さらに、いくつかのセール期間の野菜ごとの合計もB列に出したいです。 わがままですが、列はふやせません。 どうしても重複して計算してしまい、うまくいきません。
- ベストアンサー
- オフィス系ソフト
- Excel関数のことで教えていただきたいのですが、、、
Excel関数のことで教えていただきたいのですが、、、 SUBTOTAL と COUNTIF を1セルに同時に使用できませんか? 全行で”A”を数えることはできます。ここで、フィルタで行をしぼっても しぼった中から”A”を数えたいのです。 数字の合計ならSUBTOTAL(9,範囲)ですし、数字入力セル数を数えるならSUBTOTAL(2,範囲) ですが‥ よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- SUBTOTAL関数
エクセルの表にオートフィルターをかけて、抽出したデータの集計を出す時、SUBTOTALを入れた行まで非表示になることがあります。なぜでしょうか?
- ベストアンサー
- オフィス系ソフト
- エクセル SUBTOTALとSUMIFの組み合わせ
条件付きで、フィルター時の合計を返したいとき、どのような数式を作ればいいのでしょうか? 現在、SUMIFS関数で、フィルターなしの状態の結果は出せているのですが、 フィルター時にも、それを反映して、返させたいと考えています。 例えば、A列に合計したいデータがあるとき、(1行目が項目) =SUBTOTAL(9,A2:A100) と入力すると、フィルター時でも合計を返してくれます。 ここにSUMIFS関数を組み合わせるには、どうすればよいのでしょうか? 宜しくお願いいたします。 バージョンは、エクセル2013RTです。
- ベストアンサー
- Excel(エクセル)
お礼
できました! SUMPRODUCTとSUBTOTALをこのように組み合わせるのは思いつきませんでした。 ありがとうございます。大変助かりました。