【Excel/図解】SUBTOTAL関数でフィルター抽出後、表示セルのみを合計。最新版「高還元チャージルート」をチェックホームUncategorized【Excel/図解】SUBTOTAL関数でフィルター抽出後、表示セルのみを合計。【Excel/図解】SUBTOTAL関数でフィルター抽出後、表示セルのみを合計。2022年2月23日URLをコピーしました!\細かいポイ活も良いけど、まずは固定費から/困り人Excelでオートフィルターはよく使う。抽出後データの合計をSUM関数で求めると、なぜか計算が合わない...このような悩みはありませんか?僕も以前、悩みました。オートフィルター使用時に合計を求める場合は「SUBTOTAL(9,指定範囲)」か「SUBTOTAL(109,指定範囲)」を使いましょう。パパなお、オートフィルターでSUBTOTAL関数を使う場合、第一引数は「9」でも「109」でも大勢に影響がないことがほとんどです。本記事では具体例を用い、違いにもふれます。「SUM(指定範囲)」はオートフィルターや非表示で隠れたセルも計算します。ポイントは上記2点ですが、本記事では簡単な例を用い解説します。ついて、ざっくり次のようなイメージだけお持ちください。この予測変換からもわかるように「SUBTOTAL(9,指定範囲)」は⇒SUMと「ほぼ」同じ意味を持つそれでは、簡単な表で説明していきます。この例では、C3からC7までの合計をSUBTOTAL関数とSUM関数のそれぞれで計算します。計算結果はいずれも33,000。パパ結果が同じなんだから、SUMでも良さそうな気もします。オートフィルターでデータを抽出する可能性がある場合は、SUMではダメ。次の例をご覧ください。オートフィルターで交通費以外の項目だけを抽出してみます。計算結果が変わりました。正しいのはSUBTOTALで計算した1,000。ここでSUBTOTAL関数の意味を説明します。SUMとの違いが1つあります。それは、SUBTOTALは「オートフィルターで隠れたセルは計算しない」ここが今回のポイントでした。いうことで、オートフィルターを使う場合はSUBTOTALをご活用ください。引数「9」と「109」の違い違いは以下のとおりです。「9」...フィルター抽出後、表示されているセルのみを合計する「109」...「非表示」にした行は除外して合計する(「9」と同様、「フィルター抽出後、表示されているセルのみを合計する」としても使用可能)ややこしいので、実例でみてみます。全ての項目を表示しているので、計算結果は当然「9」でも「109」でも同じ。次にフィルターで交通費以外を抽出してみます。この場合も、計算結果は同じ。⇒フィルターを使い、抽出後に表示されているセルを合計する場合は「9」・「109」どちらでも大丈夫。非表示にする場合(フィルターは使用しない)では、フィルターは一切使用せず、4行目(=蛍光ペン100円)を「非表示」にしてみます。パパ右クリック「非表示」でも同じ結果ですが、今回は見やすくするためにアウトライン⇒「グループ化」で非表示を行っています。すると、計算結果が異なりました。正しいのは、「109」の方です。⇒「非表示」を使い、表示されたセルだけを合計したい場合は「109」を使うのが正しい。【参考】フィルターで一部の項目を抽出し、その後、表示されている行を一部非表示にする場合は「9」でも「109」でも結果は同じになります。一度、次の例をご覧ください。フィルターで「コピー用紙」「蛍光ペン」「封筒」を抽出した例です。仮に、この状態で4行目を非表示にすると、どうなるでしょうか?「109」だけが正しい計算になりそうな気もしますが...実は、答えは同じになります。⇒フィルターで抽出し、表示されたセルを合計する場合は「9」・「109」どちらでも大丈夫。SUBTOTALの他の使い道(小計)SUBTOTAL関数は小計を計算するのに便利です。パパというか、英単語の意味からすれば、こちらが本来の使い方かな...次の画像の右の表をご覧ください。SUBTOTAL関数は「範囲内のSUBTOTAL関数セルを計算しない」その特徴を生かせば、小計の計算は楽勝です。(なお、左の表のSUM関数は計算が誤っています。)ここでも「9」と「109」の違いをチェックまずは、非表示にする前の状態です。9行目(値「5」)を非表示にしてみます。「109」の方は正しい計算結果50に変わりました。「9」の方は55のまま。先述のとおりですが、非表示セルを計算したくない場合は「109」をご活用ください。さいごに仕事などでオートフィルターで項目を抽出するシーンは多々あると思います。その際、SUBTOTAL関数を使えば、抽出後のデータに対して適切に計算ができます。「SUBTOTAL(109,指定範囲)」は一字一句完璧に覚える必要はありません。パパExcelには予測変換機能があります!「=SU」まで入力すれば予測変換で「=SUBTOTAL」が候補に出てきます。「=SUBTOTAL」を選択すれば第一引数である「集計方法」の候補も出てきます。いうシーンでSUBTOTALを使えば便利かを何となく知っておけば、実務で使えると思います。いったところで今回はこのあたりで。最後までご覧いただきありがとうございました。