MENU

Category カテゴリカテゴリ

PC/Gadget パソコン・ガジェット
Soft/App ソフト・アプリ
Entertainment エンタメ・イベント
Shopping お買い物
D.I.Y. セルフリフォーム
Download ダウンロード

パソコン関連のお得情報

マイクロソフトオフィス
マッキントッシュ
ウインドウズ
Adobe イラストレーター
ワードプレス
Apple iPhone
iPad & Apple Pencil
アプリ

©Goblog ごぶろぐ
created by N/A | powerd by SWELL

ソフトやガジェットのPR記事依頼はお気軽にお問い合わせください
  • D.I.Y.

【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となります

条件をつける

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

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

となります

フィルタで表示されているものを指定する条件

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

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

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

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

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

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

ごん

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

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

https://back-old.chocogon.com/excel-up
管理人:ごん(デザイナー)

管理人:ごん(デザイナー)

「時短・楽したい」がモットー!Twitterでも情報公開しています
記事のライティング依頼やPR記事依頼・広告掲載のご依頼も受付中

よかったらシェアしてね!
  • URLをコピーしました!
目次