エクセル 便利関数 表示されたセルのみSUMIF

エクセル 便利関数

表示されたセルのみSUMIF

エクセルの便利関数(関数応用の便利技)として、フィルターを使用し表示されたセルのみをSUMIFする方法をご紹介します。こちらは知っているとビジネス利用で重宝すると思います。

関数「SUBTOTAL」と「SUMIF」を組み合わせる事で解決していきます。

スポンサーリンク

SUBTOTAL関数について

フィルターを使用し、表示セルのみを集計するときなど「SUBTOTAL関数」がよく利用されます。SUBTOTALは小計という意味です。

 1または101: AVERAGE(平均)
 2または102: COUNT(数値の個数)
 3または103: COUNTA(データの個数)
 4または104: MAX(最大値)
 5または105: MIN(最小値)
 6または106: PRODUCT(積)
 7または107: STDEV.S(不偏標準偏差)
 8または108: STDEV.P(標本標準偏差)
 9または109: SUM(合計)
 10または110: VAR.S(不偏分散)
 11または111: VAR.P(標本分散)

表示された場合のみ、データの個数を返す

SUBTOTAL関数でCOUNTAの結果を得たい場合「3」でも「103」でも結果を得られますが、この二つには違いがあります。

引数「3」 すべてをカウントする

数「103」 表示セルをカウントする(非表示セルをカウントしない)

フィルターを用いて表示セルのみカウントしたい場合はは「103」を使用します。

SUBTOTALは便利な関数ですが、この関数のみで「表示されたセルのみSUMIF」する事は出来ません。

表示されたセルのみSUMIF

それでは「表示されたセルのみSUMIF」するための手順をご紹介していきます。

上記の表「くだもの」列をフィルター「いちご」で絞り込んだ時の数量を表示できるようにします。

まず「E6」セルに「SUBTOTAL(103,B6)」を入力します。

(フィルターで)表示された行をに「1」を表示します。

この数式を16行目までコピペします(誤作動防止のため、データが入っている行の一つ下の行のセルまでコピペします)

次に「D2」セルに「SUMIF(E6:E15,1,D6:D15)」と入力します。

この数式は、「E列」に「1」が表示されている行の「D列」の数値の合計を表示します。

「C5」セルのフィルターで「いちご」を選択すると、合計の22が表示されました。

便利なので覚えておきましょう。

コメント

タイトルとURLをコピーしました