• iPhone
    これは好みだ…写真にノスタルジックなエフェクトをかけられるiPhoneアプリ『Vintique』
    2013年7月20日
  • Mac
    MacBook用USB-Cハブ+SDカードリーダ+ディスプレイ端子付きのオールインワンハブを買った
    2017年12月22日
  • Mac
    MacBook Airを初期化する方法 (2012年モデル・Mountain Lion)
    2015年10月8日
  • ニュージーランド
    【NZ】ニュージーランド・ノマドツアー その15 人生2度目のミルフォードトラックで再び絶景を歩く
    2017年10月15日
  • Windows
    【Windows10】「コンピュータの管理」ウィンドウを表示する3つの方法
    2015年9月3日
  • 旅行
    【NZ】もしもの時のために「在留届」を提出! 在留届の出し方をまとめました
    2016年11月30日
  • Excel
    【Excel】「20150325」という数字の羅列を日付フォーマットに直す方法
    2015年4月11日
  • アイテム
    マウスのホイールで左右移動できるマウスが役立ちすぎるのでみんな使った方がいいよ
    2014年8月8日
  • Instagram
    Instagramは積極的にタグを付けてみよう 世界中からの「いいね!」が貰えて面白いよ
    2014年9月17日
  • コマンド
    テスト用通信メモ。 特定のポート番号の通信を、コマンドプロンプトから発生させる方法。
    2014年2月15日
  • Mac
    ボーナスが出たのでMacbook Airの開封の儀を執り行ってみた
    2012年12月19日
  • Windows
    Windows7の修復をしたくてもリカバリディスクが手元にない場合…こっそりダウンロードしてみよう
    2013年8月19日
  • Excel
    【Excel】前日と比べて数字が増えた/減ったら文字の色を変える方法
    2017年12月11日
  • Excel
    【Excel】日付(〆切)が迫っているセルに、自動的に色を付けて目立たせる方法(動画あり)
    2014年8月25日
  • 鹿児島
    屋久島・宮之浦地区に滞在するなら「民宿いわかわ」がリーズナブル+バス停近し
    2015年12月18日
  • Windows
    【Windows10】「仮想デスクトップ」機能が超絶便利 これでMacのMission Controlと同じ使い方ができるようになった!
    2015年9月2日
  • Excel
    【Excel】スペース区切りでセルを分割する方法
    2016年4月24日
  • フィリピン
    マニラ・マカティの「神戸屋」で鹿児島の棒ラーメンや焼酎が売ってた件 これなら日本食がいつでも楽しめそう
    2016年3月4日
  • Mac
    【MacOS】Command+Hでウィンドウ非表示にするのを防ぐ暫定策(Yosemite版)
    2015年10月7日
  • Webサイト
    「NomadList」がノマド/バックパッキングするのにもっとも良い国・都市を教えてくれる
    2015年10月4日
  • Windows
    【Windows10】ネットワーク接続ウィンドウを簡単に表示する方法【キーボード操作のみでOK!】
    2017年9月5日
  • Excel
    【Excel】データの行・列の方向を一発で入れ替える方法
    2012年12月5日
  • Excel
    【Excel】色フィルター機能を使ってカラーリングで表管理する方法
    2015年3月12日
  • Excel
    【Excel】ファイルを開くたびに「保護されたビュー」のメッセージが表示されるのをやめさせる
    2014年5月22日
  • Windows
    【Windows10】インストール用ISOファイルをダウンロードする公式の方法(2018年版)
    2018年4月12日
  • コマンド
    【Powershell】実行時間や結果を含むコマンド実行履歴をCSVに出力する方法
    2015年9月29日
  • Excel
    【Excel】オブジェクト間を直線コネクタでつないだときに起きる微妙なズレを調整する方法
    2012年7月12日
  • 音楽
    サマソニ2016行ってきた - Day2はメッセ内で快適ファンク三昧!+レディオヘッドでクリープの巻
    2016年8月28日
  • Excel
    【Excel】現在つかっているバージョンを確認する方法【2016 Win/Mac版両方】
    2017年11月25日
  • Ruby
    MacにデフォルトでインストールされているRubyのバージョンを確認する方法 (Yosemite版)
    2016年5月12日

通常の計算式、関数式だと、フィルターでデータを抽出しても計算結果は変わらずに表示されます。

もちろん、通常の場合はそれでも良いのだけど、時々「フィルターで表示したデータだけで合計数を表示したいなぁ…」なんて思うことも、ありますよね。

今回はそんなことが可能な関数について、ご紹介しますね!

スポンサードリンク

関連記事

SUBTOTAL関数を使用して合計値を算出

抽出されたデータだけの合計値を求める場合、SUBTOTAL関数を利用するのが便利です。

SUBTOTAL関数の関数式はこちら。

=SUBTOTAL(集計方法,参照1,参照2…)

SUBTOTALで抽出したデータを合計する

実際に使ってみましょう。

果物、野菜、肉のカテゴリの販売数が、入り混じった表があります。

これをカテゴリごとに販売合計数を算出したい場合、どうすれば良いでしょうか。

SUBTOTAL関数を利用して計算した場合、オートフィルタ機能で抽出されたデータを自動的に合計してくれるため、大変便利です。

フィルターしてみるとこうなる

まずはデータをフィルターしてみます。

一番上の行で、データが入力されているセルを選択します。 この画像の場合はB1ですね。

データタブを選択し、フィルターボタンを押すすることで、抽出機能がオンになります。

SUBTOTALで計算

続いて、D10にSUBTOTAL関数を記述してみます。

=SUBTOTAL(9,D2:D9)

関数式に入力している「9」の数字は集計方法の指定した番号です。

「9」の場合は範囲内の合計値を算出する(つまり通常のSUM関数と同じ)という指定になります。

SUBTOTAL関数の計算方法の種類

この数値を別の数値に変えることで、計算方法を変えることも可能ですよ。

SUBTOTAL関数を使って、個数のカウントや平均値、最大値、最小値の計算などにも利用可能です。

なかなか便利なので、ぜひ使ってみてくださいな。

  • 「1」の場合: 平均値を計算(AVERAGE)
  • 「2」の場合: 個数をカウント(COUNT)
  • 「3」の場合: 文字列を含めてカウント(COUNTA)
  • 「4」の場合: 最大値を表示(MAX)
  • 「5」の場合: 最小値を表示(MIN)
  • 「6」の場合: 積(掛け算)を求める(PRODUCT)
  • 「7」の場合: 不便標準偏差を求める(STDEV)
  • 「8」の場合: 標本標準偏差を求める(STDEVP)
  • 「9」の場合: 合計値を計算(SUM)
  • 「10」の場合: 不偏分散を求める(VAR)
  • 「11」の場合: 標本分散を求める(VARP)

フィルターされた数値の合計だけが表示される!

さて、さきほどの図に戻って。

D2:D9のセルが、販売数の数値が入力されているセルの範囲指定です。

 

SUBTOTAL関数で合計値を表示したら、次にデータをフィルター抽出してみます。

 

データを抽出すると、自動的に表示されているカテゴリのデータのみの合計数が算出され、表示されているはず!

 

同様に別カテゴリを選択しても、そのカテゴリの合計数が自動的に反映され、表示されます。

さらに複数カテゴリを選択した場合でも、フィルターで表示されているデータのみで計算された数字が算出されるので、非常に便利な関数です。

スポンサードリンク

 

まとめ

このように、SUBTOTAL関数は雑多なデータから必要な数値を算出できる便利な機能です。

最終的な計算結果を表示するための関数…というよりも、実際にデータの集まりをフィルターで抽出しながら、計算した値がどのように変化するかを見ながら操作することに適していると思います。

 

ということで、オートフィルタ機能とSUBTOTAL関数を組みあわせることによって、簡単に必要な数値を算出することができました。

上でも挙げた通り、SUBTOTAL関数は、他にも様々な集計パターンを指定することができます。

場面によって、使い分けてみてくださいな。

それでは!