カテゴリ
パソコン関連のお得情報
【Excel】フィルタを使用中にSUMIFSで条件に当てはまる値を合計する方法
この記事では、Excelでフィルタを使用中に
抽出されたデータの中から、さらに条件を指定して
合計する方法を御紹介します
こんにちは、ごんです。
普段はツイッターでExcelの関数をイラストで紹介したりしてます。
目次
【Excel】フィルタで抽出された値にさらに条件を付け加える
例題：販売個数管理表でフィルタと条件をしぼり、仕入先ごとに販売個数を表示する
言葉にするととても難しく感じるので、実際に画像を使ってみていきましょう
たとえば、下の表のように品物の販売を管理しているお店。
普段はフィルタを利用して、いろいろなデータを抽出しています。
フィルタで「りんご」を抽出しました
りんごが「仕入先別に何個売れたのか」を確認したいとき、仕入先のフィルタを何回も触る必要がありますよね
はっきりいって、めちゃくちゃ面倒だし「時間を無駄」につかっています
そこで、SUBTOTAL関数とSUMIFS関数の登場です。
上記のように、表に作業列を追加し、計算式をいれておくと･･･
フィルタでりんごを抽出しただけで、仕入先ごとの販売数や販売価格が表示されました。
もちろん、みかんやばななにしても同じ結果が返ってきます。
このように「フィルタと関数の併せ技」を使うことで、さまざまなデータの抽出を瞬時に行う事ができます
ExcelでフィルタとSUMIFSを組み合わせる方法
それでは実際に、下記の動画の表の作り方をご説明します
この動画で使っているサンプルデータもダウンロード可能です。
数式を確認したいときにご利用ください。
※数式はわかりやすいように絶対参照にしていません※
作業列を作成し、計算式を入れる
まず、表の端の方（今回はI列）に作業列を作成します。
作業列は、普段は非表示でOKです。
今回は分かりやすいように表示したままになっています。
作業列の式
=SUBTOTAL(103,セルの指定)
今回のサンプルデータの場合
I3 に
=SUBTOTAL(103,C3)
の式が入っています
SUBTOTALの103とは
データの個数を返す式です。3でもOKです。
この式をいれることで
フィルタで表示されている状態の場合１を
非表示になった場合に0を
返します。
セルの指定箇所
表の中で、必ず入力されるセルを指定します
コピーして完了
作業列に入力した式を、下までコピーして完了です
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となります
条件をつける
今回は仕入先が「１」になっているものの合計を表示したいので、
範囲→G2：G11
条件→B14 または 1
となります
フィルタで表示されているものを指定する条件
フィルタで表示されているものだけ計算したいので、先ほど作った作業列を範囲としてます。
範囲：作業列（I3：I11）
条件：1
これでフィルタを表示しているもののなかから、さらに条件をつけて表示させることができるようになりました。
たとえば、りんごをフィルタで表示して、で仕入先ごとにりんごの売れた個数を知りたい。
期間でフィルタを表示させて、３月に売れた果物を種類別に知りたい･･･。
いろんな条件を組んで結果を表示させることができます。
まとめ：SUMIFSとフィルタを同時に使う
いかがでしたか？
今回の方法を使うと、データの統計を簡単にとることができます
その時その時で、表示したいデータがあるとおもうので
それに合わせた条件の値が表示されるこの方法は、いろいろなことに役立つと思います。
管理人：ごん（デザイナー）
「時短・楽したい」がモットー！Twitterでも情報公開しています
記事のライティング依頼やPR記事依頼・広告掲載のご依頼も受付中
