【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法

この記事では、Excelでフィルタを使用中に
抽出されたデータの中から、さらに条件を指定して
合計する方法を御紹介します

【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法
ごん

こんにちは、ごんです。
普段はツイッターでExcelの関数をイラストで紹介したりしてます。

目次

【Excel】フィルタで抽出された値にさらに条件を付け加える

例題:販売個数管理表でフィルタと条件をしぼり、仕入先ごとに販売個数を表示する

【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法
ごん

言葉にするととても難しく感じるので、実際に画像を使ってみていきましょう

たとえば、下の表のように品物の販売を管理しているお店。

【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法

普段はフィルタを利用して、いろいろなデータを抽出しています。

【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法
ごん

フィルタで「りんご」を抽出しました

【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法
【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法
ごん

りんごが「仕入先別に何個売れたのか」を確認したいとき、仕入先のフィルタを何回も触る必要がありますよね

【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法
【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法
【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法
【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法
ごん

はっきりいって、めちゃくちゃ面倒だし「時間を無駄」につかっています

そこで、SUBTOTAL関数とSUMIFS関数の登場です。

【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法
【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法
ごん

上記のように、表に作業列を追加し、計算式をいれておくと・・・

【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法

フィルタでりんごを抽出しただけで、仕入先ごとの販売数や販売価格が表示されました。

もちろん、みかんやばななにしても同じ結果が返ってきます。

【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法
【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法
【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法
ごん

このように「フィルタと関数の併せ技」を使うことで、さまざまなデータの抽出を瞬時に行う事ができます

ExcelでフィルタとSUMIFSを組み合わせる方法

【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法
ごん

それでは実際に、下記の動画の表の作り方をご説明します

この動画で使っているサンプルデータもダウンロード可能です。
数式を確認したいときにご利用ください。
※数式はわかりやすいように絶対参照にしていません※

作業列を作成し、計算式を入れる

まず、表の端の方(今回はI列)に作業列を作成します。

【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法

作業列は、普段は非表示でOKです。
今回は分かりやすいように表示したままになっています。

作業列の式

=SUBTOTAL(103,セルの指定)

今回のサンプルデータの場合

I3 に 

=SUBTOTAL(103,C3)

の式が入っています

SUBTOTALの103とは

データの個数を返す式です。3でもOKです。
この式をいれることで

フィルタで表示されている状態の場合
非表示になった場合に0

返します。

セルの指定箇所

表の中で、必ず入力されるセルを指定します

コピーして完了

作業列に入力した式を、下までコピーして完了です

SUMIFS関数を使って計算式を入れる

【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法

仕入別の列が 1 になっていて、表示されている商品の販売個数を調べる式を入力してみます。

フィルタで表示中のものに条件を付けて合計を求める
=SUMIFS(合計したい範囲,条件範囲,条件,作業列,1)

今回のサンプルデータの場合

仕入別1の販売個数(C14)に

C14 = SUMIFS(D3:D11,G3:G11,B14,I3:I11,1)

の式が入っています。

SUMIFSとは

特定の条件に一致する値を求める式で、条件を複数つけることができます

SUMIFS(合計したい範囲,条件範囲1,条件1,条件範囲2,条件2…)

本来はこのように書きますが、今回の場合、

条件の一つに「フィルタで表示されているもの」があるので、
作業列範囲,1」を必ず入力する必要があります

合計したい範囲を指定する

まずは SUMIFS の初めに、合計したい範囲を指定します。
今回は「個数」を指定したいのでD3:D11となります

【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法
条件をつける

今回は仕入先が「1」になっているものの合計を表示したいので、

範囲→G2:G11
条件→B14 または 1

となります

【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法
フィルタで表示されているものを指定する条件

フィルタで表示されているものだけ計算したいので、先ほど作った作業列を範囲としてます。

範囲:作業列(I3:I11)
条件:1

【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法

これでフィルタを表示しているもののなかから、さらに条件をつけて表示させることができるようになりました。

たとえば、りんごをフィルタで表示して、で仕入先ごとにりんごの売れた個数を知りたい。
期間でフィルタを表示させて、3月に売れた果物を種類別に知りたい・・・。

いろんな条件を組んで結果を表示させることができます。

まとめ:SUMIFSとフィルタを同時に使う

【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法
ごん

いかがでしたか?
今回の方法を使うと、データの統計を簡単にとることができます

その時その時で、表示したいデータがあるとおもうので
それに合わせた条件の値が表示されるこの方法は、いろいろなことに役立つと思います。

https://chocogon.com/excel-up

この記事が気に入ったら
フォローしてね!

お手数でなければシェアしてください

この記事を書いた人

ごんのアバター ごん でざいなー

デザイン・パソコン・副業がお好き

Excelやillustratorの使い方
日々の生活で効率をあげる方法など
あらゆることを書いています

目次
閉じる