subtotal関数の使い方と範囲指定について
- subtotal関数を使って特定の範囲の合計値を求める方法について教えてください。
- 毎日行が追加されるような売上管理表で、範囲が広がる場合、どのように範囲指定すれば良いか教えてください。
- D3:D何万といった範囲指定以外に、よりスマートな範囲指定方法があれば教えてください。
- ベストアンサー
subtotal関数
subtotal関数の使い方を調べているのですが、検索しても なかなか出ないので教えてください。 売上管理表を作成していて、毎日行が追加されるような場合に、 範囲が変わってきますが、ある月の合計を知りたい状況の ときです。 例えば、 subtotal(9,D3:D100)とした場合、D3～D100の範囲で 8月だけを抽出した場合、8月の合計値を出してくれますが、 毎日表は下の行に広がっていきます。 この範囲が広がる場合はどう範囲を指定したらよいでしょうか？ 簡単にやるのでしたら、D3:D何万とか指定しておけば十分足りるのですが、 もう少しスマートに指定する方法はどうすればよいのでしょうか？ よろしくお願いします。
- commupla
- お礼率99% (214/216)
- Excel（エクセル）
- 回答数3
- ありがとう数3
- みんなの回答 （3）
- 専門家の回答
質問者が選んだベストアンサー
もしD1とD2に数値が入っていないのであれば、 SUBTOTAL(9, D:D) で、列Dにある範囲の数値の合計値を出してくれます。 もしD1とD2に数値が入っているとき、上記の関数ですとD1やD2のものまで計算してしまいます。この場合、多少汚くはなりますが、 SUBTOTAL(9, INDIRECT("D3:D" & (COUNTA(D:D)-COUNTA(D1:D2)+3))) で、D3以下の範囲を自動的に計算して合計値を出すことができます。 簡単のため、D3以下の範囲では数値が連続して存在する（空白セルをはさまない）ものと仮定しています。
その他の回答 (2)
- Nobu-W
- ベストアンサー率39% (725/1832)
参考程度まで・・・細かいセル位置は変更してください Ａ１セルに =SUBTOTAL(9,OFFSET(B1,0,0,COUNTA($B:$B)+1,1)) Ｂ１セルから下に数値を入力するとぉ～ これでは・・・使い物にならない？ ＞＜
お礼
ご回答ありがとうございます。
- Chiquilin
- ベストアンサー率30% (94/305)
テーブル書式と構造化参照を覚えましょう。 http://www4.synapse.ne.jp/yone/excel2013/excel2013_table_kouzouka.html
お礼
ご回答ありがとうございます。
関連するQ&A
- SUBTOTAL関数
エクセルの表にオートフィルターをかけて、抽出したデータの集計を出す時、SUBTOTALを入れた行まで非表示になることがあります。なぜでしょうか？
- ベストアンサー
- オフィス系ソフト
- エクセルのSUBTOTALで
=SUBTOTAL(102,E7:E65) という関数がありますが行数が表によって変化します。 そこで E65 ではなくデータのある行のマイナス１を常に指定したい。 （最終行は合計なのでカウントしたくない） こんなときどうしたらいいでしょうか？ VBAを使わないと無理でしょうか。 その場合のコードはどう記述すればいいでしょうか。 教えてください。
- ベストアンサー
- オフィス系ソフト
- SUBTOTAL関数での選択範囲を表示する方法
仕事で使う日報に、毎日あるデータを手入力しています。 入力件数が多い時は結構面倒なので、VBAで数値を日報のシートに飛ばそうと考えています。 いくつかあるデータをVBAのSUBTOTALメソッドで合計などを集計し、 各集計を指定のセルに飛ばすことはできるのですが、 SUBTOTALで指定されたセル範囲の最頻値を取り出す方法がわかりません。 MODE関数を使うということは分かるのですが、 SUBTOTALで指定されるセル範囲は毎回変わるので、 MODE関数で指定するセル範囲をSUBTOTALのセル範囲を同じになるようにできればと考えています。 どなたかお知恵を貸して頂ければと思います。 宜しくお願いいたします。
- ベストアンサー
- その他MS Office製品
- Excel関数：SUBTOTALとSUMIFを組み合わせる？
下記のような場合の集計ができる関数を教えて下さい。 SUBTOTALとSUMIFを組み合わせようと思ったのですが、うまくいきませんでした。 よろしくお願いします。 ＜表の内容＞ ・セルA1：「ランク」 ・セルA2～A50：「A」or「B」or「C」を入力している ・セルB1：「金額」 ・セルB2～B50：各金額を入力している ＜集計方法＞ ・セルB51に「ランクが"A"」の金額合計を表示したい ・ただしオートフィルターを使って他の条件で抽出もおこなっているのでSUBTOTAL関数のように、表示されている行の値のみを合計したい
- ベストアンサー
- オフィス系ソフト
- オートフィルタ で抽出した結果の合計（SUBTOTAL）
データ抽出機能 (オートフィルタ) で抽出した結果をＳＵＢＴＯＴＡＬ関数で合計を出しました。１０項目ぐらいあるうち３項目のみ合計が１だけ違って表示されてしまいます。残りの７項目は、ぴったり合計で表示されています。例えば、合計額-４８７，５３１のはずが、４８７，５３０。６６６，３６３のはずが、６６６，３６２と表示されてしまいます。現在、ＳＵＢＴＯＴＡＬ（9，Ｄ2：Ｄ37）の書式を使用していますが、1合わない合計数は、どのようにすれば、修正できますか？教えてください。
- 締切済み
- オフィス系ソフト
- Excel のSUBTOTAL関数の応用についてお尋ねします。
Excel のSUBTOTAL関数の応用についてお尋ねします。 SUBTOTAL関数は対象データに小計が多いとき便利ですが、同じ列にAVERAGE値など集計対象外の数値が入っているときには、はじめから終わりまでベタの範囲指定ではうまくいきません。 集計対象外の数値をよけ、コンマで区切ってSUBTOTAL範囲に指定すれば、計算はできますが、それではSUBTOTALを使う意味がなくなってしまいます。 ここで別の関数を組み込んで、一発ベタの範囲指定でやっつける方法はないものでしょうか、教えてください、よろしくお願いします。
- ベストアンサー
- その他MS Office製品
- subtotal関数についての質問です。
subtotal関数をつかって、練習をしています。どんな練習かといいますと。 A店、B店の月別売り上げを計算して月ごとの小計をだします。そして年間の総売り上げをだしているという作業なんですが、この総合計で、なぜsubtotal関数ですべての値をドラッグしているのに小計だけ除かれてしまうのかわかりません。
- ベストアンサー
- オフィス系ソフト
- エクセルの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関数の使い方
Excel 2013 の Excelヘルプに、次の記述があります。 》 SUBTOTAL(集計方法,範囲 1,[範囲 2],...) 》 》 集計方法は 1～11 または 101～111 の数字で、 》 小計の算出に使用する関数を指定します。 》 1～11 には手動で非表示にした行が含まれますが、 》 101～111 には含まれません。 》 フィルター処理されたセルは常に除外されます。 上で言っている「手動で非表示にした」は「フィルター処理で非表示にした」とは異なることは理解できますが、具体的にどういう操作が「手動で非表示にした」ことになるのか教えてください。
- ベストアンサー
- Excel（エクセル）
- 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でしたが、Ｄ列に金額を直接入力すれば小計は正しく表示されます。 理由がおわかりの方、教えてくださいませんか
- ベストアンサー
- オフィス系ソフト
お礼
早速のご回答ありがとうございます。 書き損ねていましたが、実はD:Dとの指定をしたら、フィルターをかけていない状態での合計が 本来の合計額の2倍になってしまったことが質問のきっかけです。 このときD1D2には数値でなく、文字が入っていたのですが、これが影響しての謝った合計額の表示だったんでしょうか？