関数シェアするLINE!0SUBTOTAL関数は合計や平均などをに使う関数です。合計に関しては小計の要素を考えると非常に使い勝手が良い関数です。今回はこのSUBTOTAL関数について見てみましょう。SUM関数との違いAVERAGE関数との違い集計方法と範囲を検索関数にして、多角的アプローチ検索関数に関しては様々な形があるので、作りたいものによって使い分けられるようにしておくと便利です。検索関数としてこれまでLOOKUP関数、INDEX関数+MATCH関数を紹介してきました。今回はOFFSET関数の特性・出来ることを見ていきます。23:27【Excel】INDEXとMATCH関数【VLOOKUPとの差】VLOOKUP関数とは別の検索方法である、INDEX関数とMATCH関数の使い方、その組み合わせ方、同じ検索関数であるVLOOKUP関数との差を比較してみます。14:48【Excel】VLOOKUPで一致を複数抽出【INDEX+MATCHも可】VLOOKUPは検索によく使われる関数です。弱点もいくつかあります。その中でも、「検索条件に一致するセルが複数ある場合は、一致するセルの中で一番上にあるセルを抽出する」ということがネックになることがよくあります。03:30今回も実際に使用したものを練習用に貼っておくのでご活用ください。SUBTOTALSUM関数との違いSUBTOTAL関数は=SUBTOTAL(集計方法,参照)となります。集計方法には1~11の数字が入り、それぞれに対応した関数によって参照元のデータに数式を当てはめていきます。合計や平均ならばSUMやAVERAGE関数があるのでこれだけでは必要性が分かりづらいので実際の使い方を見ていきます。全支店合計を出してみます。F11:F14)とSUM関数で括ります。一方で全支店合計を出すには、SUM(F5,F11,F15)と範囲を指定しなければなりません。地方が増えた際にはいちいち全支店合計は範囲を追加する必要があります。これに対して、SUBTOTAL関数なら小計欄はSUBTOTAL(9,F2:F4)やSUBTOTAL(9,F6:F10)、SUBTOTAL(9,F11:F14)となりますが最後の全支店合計はSUBTOTAL(9,F2:F15)になります。SUBTOTAL関数を入れていれば、合計の範囲は全部の支店の範囲を選択していれば純粋な全支店合計になります。SUM(F2:F15)としてしまうと、各支店の合計と一緒に小計の欄も合計してしまう為、2重の計算となってしまいます。注意しなければならないのは、小計欄をSUM関数で計算してしまうとSUBTOTAL関数では除外できずに小計も含めての計算になってしまいます。SUBTOTAL関数を使うならSUBTOTAL関数で統一をする必要があります。AVERAGE関数との違いAVERAGE関数とSUBTOTAL関数(集計方法1:平均)の比較SUBTOTAL関数の集計方法1は平均となっているので、今度はAVERAGE関数との比較をしてみます。先程のSUM関数と同様に、AVERAGE関数では平均する部分が2重で計算されてしまうので全支店平均もぶれてしまいます。今回は参照している範囲の値の最小値と最大値の差が大きくないのでそこまで大きくぶれませんが、桁が1つ2つと変わるとそれだけ平均もぶれやすくなってしまうので、平均範囲をミスしないようにするためにもSUBTOTAL関数のほうが安全とも言えます。集計方法と範囲を検索関数にして、多角的アプローチ集計方法に1~11で集計方法を選択するというのがSUBTOTAL関数の数式の形です。Excelはデータ集計・分析ソフトですが、一口にデータといっても合計のほうが伝わりやすかったり、平均のほうが伝わりやすかったり様々です。その都度「合計」なのか「平均」なのか「最大・最少値」を探すのかによって数式を変更するのが面倒なので検索関数を使って集計方法をセル操作1つで簡単に変更できるようにします。このような表を用意しておきます。機能の種類が1~11と一致するようにしてください。必要ないものに関してはリストから外しても問題ありません。以下のような形で大丈夫です。続いてデータを表示させたいところに関数対応表のリストをドロップダウンリストで設定をしておきましょう。ドロップダウンリスト(プルダウンとも呼ばれている)はこんな形の入力フォームです。00:46この表であれば、情報収集したいセルの範囲を変化させられるようにしながら、収集する項目を選びつつ項目に併せて平均なのか合計なのかを算出できるようにしています。E7セル(検索結果を出すセル)の数式は以下のようになっています。=SUBTOTAL(VLOOKUP(E18,SUBTOTAL関数表!$A$2:$B$12,2,FALSE),(OFFSET($A$2,MATCH($E$3,$A$3:$A$33,0),MATCH($E$5,$B$2:$D$2,0),E16-E15+1,1)))VLOOKUP関数でE6(平均などの算出方法)を検索値にして、検索範囲である関数一覧表から対応した検索方法の数字をSUBTOTAL関数に埋め込むという意味ですね。関数や機能を組み合わせればこういったデータ集計も簡単に出来るようになります。使いこなしていきましょう。コメントをキャンセルメールアドレスが公開されることはありません。コメントデータの処理方法の詳細はこちらをご覧ください。