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