• 締切済み

SUBTOTALの応用について

エクセルで集計作業を行っている中で分からないことがあるので良い方法があれば教えてください。 エクセルには以下の情報があります。 A列：案件番号（通し番号） B列：担当営業（Aさん～Eさん） C列：該当案件の売上 D列：ステータス（1～5） E列：粗利 集計したい情報 1.営業Ｂさんの売上合計：上記情報と重ならないセルにてSUBTOTAL(109,B2:B99)で集計可←ここまではできている 2.フィルターをかけ、営業Bさんの情報のみ表示されている状態でステータスが1または3の案件の粗利合計を1.同様に上記情報と重ならないセルにて集計したい←この計算式が知りたい よろしくお願いします。

みんなの回答

  • imogasi
  • ベストアンサー率27% (4737/17068)
回答No.4

こういうのは、「フィルターをかけた状態で」といった、「特殊な難しい素人的な状況での問題にする」から難しいのであって、素直に下記のような考えで考えるべきと思う。自分で考えてみるのは勝手だが、途中までのやり方にこだわって、回答者に回答をもとめることはないだろう。 例　A1：C14 担当 　ステ　 数値 A 　1 　10 A 　2 　20 B 　1　　30 B 　2 　40 B 　1 　50 B 　2 　60 B 　3 　70 B 　2　 80 B 　1 　90 B 　1 　100 B 　2 　110 B 　3 　120 B 1 　130 C 2 　140 C 1 　150 空いたセルの３つのセルに =SUMIFS(C2:C14,A2:A14,"B",B2:B14,1)　　結果　４００ =SUMIFS(C3:C15,A3:A15,"B",B3:B15,2)　　結果　２９０ =SUMIFS(C4:C16,A4:A16,"B",B4:B16,3)　　結果　１９０ 第五引数はROW()－２のようにして、下方向に複写できるようにもできる。 ーー 参考までに、 質問は下記で言っているようなことかな？違ってていたらすみません。 http://excel-forest.net/2018/03/17/20180316a/ ーー 他にも、ピボットテーブルなどのやり方もある。これを嫌がる関数マニアが多いようだが。

asumoasumo
質問者

お礼

ありがとうございます！ 教えていただいた情報で見通しが立ちました！ 参考に送っていただいたURLも確認しました。（難しいと感じたので勉強しますw）

  • kkkkkm
  • ベストアンサー率65% (1606/2443)
回答No.3

どこかのセルに選択された営業さんの名前を以下の式で摘出します。仮にG16とします。 G16に =IF(SUBTOTAL(103,B2:B100)<>COUNTA(B2:B100),INDEX(B2:B100,MATCH(0,INDEX(0/SUBTOTAL(103,INDIRECT("B"&ROW(2:100))),),0)),"") としてください。 それぞれ粗利合計を出したいセルに以下の式を入れてください。 1または3の粗利合計 =SUMPRODUCT((B:B=G16)*((D:D=1)+(D:D=3)),E:E) 2の粗利合計 =SUMPRODUCT((B:B=G16)*(D:D=2),E:E) 4の粗利合計 =SUMPRODUCT((B:B=G16)*(D:D=4),E:E)

asumoasumo
質問者

お礼

ありがとうございます！ 教えていただいた情報を元に整理して試してみます！

  • kkkkkm
  • ベストアンサー率65% (1606/2443)
回答No.2

式を書き忘れました。 =SUBTOTAL(109,E2:E99)

asumoasumo
質問者

補足

ご回答ありがとうございます。 フィルターをかければ確かに確認できるのですが、補足しますと ステータス1または3の粗利合計、2の粗利合計、4の粗利合計という3つの粗利合計を各担当者ごとに出したいので、できれば担当者のみフィルターをかけたら３つの粗利合計についてそれぞれ計算式で算出できるようにしたいと思っています。

  • kkkkkm
  • ベストアンサー率65% (1606/2443)
回答No.1

フィルターでステータスの1と3の絞り込みを追加してやればいいのではないでしょうか。

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. Excel（エクセル）

関連するQ&A

  • SUBTOTALと他関数の合体はできますか？集計の時です

    エクセルの集計を行いたいのですが、オートフィルターを活用している中、集計をSUBTOTALとIF関数の合体のようなものを作りたいのです。出来ますでしょうか・・・（私の知識の無さですみません） 一例はこの通りです。 　　　Ａ列　Ｂ列　　Ｃ列 1　幼児　りんご　３００ 2　中学　みかん　１００ 3　高校　バナナ　２００ 4　大人　りんご　５００ 別の行にそれぞれの集計値を出すセルを作ります。 幼児合計 中学合計 高校合計 大人合計 B列の集計をしたいとき、各合計は、 普通にSUBTOTALだけだと幼児も中学も高校も全部一緒の合計になってしまいます。 さらにA列を絞りこめばいいのですが、分析において絞り込みたくないのです。 りんごは幼児、中学、高校、大人にどのくらいあるのかを 一目で分かるようにしたいためです。 どうか、ご教授ください。

  • Excel のSUBTOTAL関数の応用についてお尋ねします。

    Excel のSUBTOTAL関数の応用についてお尋ねします。 SUBTOTAL関数は対象データに小計が多いとき便利ですが、同じ列にAVERAGE値など集計対象外の数値が入っているときには、はじめから終わりまでベタの範囲指定ではうまくいきません。 集計対象外の数値をよけ、コンマで区切ってSUBTOTAL範囲に指定すれば、計算はできますが、それではSUBTOTALを使う意味がなくなってしまいます。 ここで別の関数を組み込んで、一発ベタの範囲指定でやっつける方法はないものでしょうか、教えてください、よろしくお願いします。

  • エクセルでフィルターを使用時に使う式=SUBTOTAL(3,A5:A50)や=SUBTOTAL(9,A5:A50)

    エクセルでフィルター機能を使用して合計金額や件数を確認する式で=SUBTOTAL(3,A5:A50)や=SUBTOTAL(9,A5:A50)あります。 エクセルで作成した表でA列に営業所名、B列に得意先名、C列に売上げを入れた表があります。返品があった場合はC列にマイナスが発生します。 営業所毎にフィルターをかけた場合、マイナスの得意先がカウントされないようにするには、どのように数式を入れたらよいのでしょうか？

  • エクセルの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関数の不思議

    SUBTOTAL関数で期待する合計が出せません。 Ａ列　　　Ｂ列　　　Ｃ列　　　Ｄ列 品名　　　数量　　　単価　　　金額 Ａ　　　　　1　　　　100　　　　100 Ｂ　　　　　2　　　　200　　　　400 Ｃ　　　　　3　　　　300　　　　900 小計　　　　　　　　　　　　　　　0　→ここが期待どおりでない Ｄ２セルの式は =IF(A2="品名",SUBTOTAL(9,INDIRECT("D$2:D"&ROW()-1)),B2*C2) でＤ５までコピーしました。 つまりＡ列が"小計"でないときは数量×単価を、"小計"と入力されていたらD2:D4をSUBTOTALで集計するつもりなのに結果が0になってしまいます。 ちなみにＤ５に =SUBTOTAL(9,D2:D4) と手入力しても結果は0でしたが、Ｄ列に金額を直接入力すれば小計は正しく表示されます。 理由がおわかりの方、教えてくださいませんか

  • Excel関数：SUBTOTALとSUMIFを組み合わせる？

    下記のような場合の集計ができる関数を教えて下さい。 SUBTOTALとSUMIFを組み合わせようと思ったのですが、うまくいきませんでした。 よろしくお願いします。 ＜表の内容＞ ・セルA1：「ランク」 ・セルA2～A50：「A」or「B」or「C」を入力している ・セルB1：「金額」 ・セルB2～B50：各金額を入力している ＜集計方法＞ ・セルB51に「ランクが"A"」の金額合計を表示したい ・ただしオートフィルターを使って他の条件で抽出もおこなっているのでSUBTOTAL関数のように、表示されている行の値のみを合計したい

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

    エクセル関数でSUBTOTALとSUMIFを組み合わせたいです。 例えば八百屋さんでの集計で、 ある期間の野菜別の合計とさらに任意の期間の合計をだします。 A列は野菜の名前が入っています。 B列は値段が入っています。 A きゅうり ピーマン なす かぼちゃ かぼちゃ なす もやし にんじん もやし 以下続く 隣のＢ列には値段が入っています。（野菜の値段は時期によって違います） SUMIFでセール期間の野菜ごとの合計をＢ列にだしました。 さらに、いくつかのセール期間の野菜ごとの合計もＢ列に出したいです。 わがままですが、列はふやせません。 どうしても重複して計算してしまい、うまくいきません。

  • エクセルでフィルター結果をSUBTOTALする

    エクセル2010で、フィルターの結果をSUBTOTAL関数でB1セルに表示したとして、その横のセルA1セルにフィルターした項目を表示させたいのですが。 例えばSUBTOTALした「合計金額」が何の項目かを表示させないと分かりにくいので何か方法があれば教えてください。 フィルターオプション以外の方法でお願いします。

  • SUBTOTAL関数の[列名]指定

    SUBTOTAL関数の[列名]指定が有効に動作するための方法を知りたい 図は、ある教本のサンプルExcelシートです。 テーブル全体には、 ホーム＞テーブルとして書式設定 で、「テーブル1」という”テーブル名"が設定されています。 このD8セルに、 ＝SUBTOTAL(109,[金額]) と入力が事前にしてあり、フィルタリングをするとこのD8列の数値は変動します（集計が起こります）。① 同じことを、自分で手作業でテーブルを作成し、テーブル全体を「テーブル1」と設定/定義し、計算セルに ＝SUBTOTAL(109,[金額]) としましたが、「循環参照」の警告が出て、集計をしてくれません② この関数を入力したセルをダブルクリックしてみると、計算対象のセルは数式が入った自分のセルを含んでおらず、循環参照が起きていないようにみえます③ [金額]のところを、計算対象セル範囲に指定 　例：=SUBTOTAL(109,D2:D22) してしまえば問題はないのですが、このやり方 すなわち ＝SUBTOTAL(109,[列名]) の入力で、一方のシートは（①）は集計でき、わたしが作った方（③）で集計がおこらない理由を知りたいです。

  • subtotal関数を使わずに・・・

    Excel2003では、subtotal(103,A1:A10)と数式を入れると、非表示のセルは集計されません。これと同じことをExcel2000でも行いたいのですが、Excel2000では、103番の計算方法が使用できません。Excel2000でも同じ結果を得るためには、どうすればよいのでしょうか？ご教授お願いいたします。

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する