SUBTOTAL関数

エクセルを使用するときに便利な関数で、私にとって一番役に立っているのがこの関数です。
この関数は、数学/三角関数に分類されているので知っている人は少ないようです。

例えば、売上げデータを日付、得意先、営業担当で管理しているシートがあったとします。
このシートに対して、オートフィルタで必要なデータを選択し分析するときに、選択結果に対する売上げ金額の合計や売上げ単価の平均を取得するときに便利な関数です。
使い方は、「=SUBTOTAL(集計方法,集計範囲)」と記述し、集計方法は以下のとおりです(詳しくはエクセルのヘルプ参照)。

集計方法は関数名で記載されていますので、エクセルのヘルプで確認ください。ちなみに合計はSUM、平均はAVERAGEです。

サンプル(excel-4.xls)を置いておきます。右クリックで保存してください。(使用によるトラブルには責任を負いません)
合計行の前に空白行がありますが、ここがミソです。新しい行を追加するときにこの行を選択して「挿入」すると関数の範囲が自動変更されます。

SUMIF関数

データが溜まって大きな表になった時、ある項目の合計値だけ必要な場合に利用します。
この関数も、数学/三角関数に分類されています。
例えば、売上げデータを日付、得意先、営業担当で管理しているシートがあったとします。
このシートに対して、営業担当毎の売上げ金額の集計でグラフ等作成する時などに使います 。
使い方は、「=SUMIF(範囲,検索条件,集計範囲)」と記述し、範囲は検索条件と比較するセル範囲を指定します。例えば、ある営業担当の実績を集計する場合、データ表の営業担当が入力されている列を指定します(例えばC列ならばC:C)。
検索条件は、探したい文字列を直接指定しても文字列が入力されているセルを指定してもOKです。
集計範囲は、集計するデータが入力されているセル範囲を指定します(例えばG列ならばG:G)。
SUBTOTALのサンプル(excel-4.xls)の営業成績シートを参照ください。(右クリックで保存)(使用によるトラブルには責任を負いません)

SUMIF関数の応用

SUMIF関数は、1つの列しか検索対象に出来ません。この関数を使って、2つの列の項目の集計を行う場合はどうすればいいのでしょうか。
下の記事で使用しているエクセルシート(売上げデータを日付、得意先、営業担当で管理しているシート)で、月別・担当別の集計を行う場合を想定します。

  1. データシートに集計用の列を作成します。
    この列は、印刷の必要が無いので[ファイル]-[ページ設定]で印刷範囲を指定しその外に作成します。サンプルではG列。
  2. 作成した列に、集計するデータキーを作成します。
    A列に売上日付、C列に担当があり「売上月別担当別集計」を行う場合「=MONTH(A2) & “/” &C2」の式を入れる。
    オートフィルで全ての行に式をコピーする。
  3. 集計用シートのSUMIFを変更します。
    集計用シート名を「売上帳」、集計用の列を「G列」の場合、を行う場合「=SUMIF(売上帳!$H:$H,A2&”/”&B2,売上帳!$G:$G)」の式を入れる。 オートフィルで全ての行に式をコピーする。

このようにデータシートに工夫することで、様々な集計に利用できます。
サンプル(excel-4-2.xls)の営業成績シートを参照ください。(右クリックで保存)(使用によるトラブルには責任を負いません)
上記2つの関数(SUBTOTAL,SUMIF)を活用すれば、相当のデータ分析がエクセルで可能になると思います。