皆さんは、エクセルのグラフを条件によって変化させたりしたいですか？ピボットグラフを使ったりBIツールを使ったりして作成することができますが、今回はエクセルの元データをそのまま利用して作成する方法を共有します。
元データを見ながら色々シミュレーションが出来るので結構便利です。
今回は以下のサンプルデータを使用します。
商品名と売上金額の合計が表示される集計表があります。ここの数字はSUMIFを使っています。
元データには売上日・売上No・商品名・売上金額が記載されております。
今回は、売上日・売上Noでフィルターをかけても、SUMIFの数値が変化するようにしたいと思います。
SUBTOTAL関数を使ってフィルターを判定しよう！！
元データに、以下の数式を入力が入った列を追加しました。
セルA11に=SUBTOTAL(3,B11:B11)を入れました。
※11行以降はコピペしてください
SUBTOTAL関数の3はCOUNTA関数になります。これによって、
"行が表示されているときに1が出力され、非表示のときに0が出力されます。"
これによりフィルター検索し表示されている箇所のみ表示可能になります。
SUMIFをSUMIFSに変更してフィルターを反映させる
集計表のSUMIFの計算をSUMIFSにしてE3セルを以下のように変更します。（以降はコピペ）
=SUMIFS(E$11:E$29,$D$11:$D$29,$D3,$A$11:$A$29,1)
※絶対参照にしてからコピペしてください。
商品名が一致してかつ、SUBTOTAL表示用の列が1になっている明細のみが計算されるようになります。
集計表をグラフに反映させるとフィルターに合わせてグラフが変化します。
まるでPower BIやTableauを触っているかのような感覚だと思いませんか！？！？
おまけ
・最初はSUMIFの中にSUBTOTAL関数を入れてみましたが、これだとエラーになってしまいます。こんな式です⇒｛=sumif(D11:D29,D3,subtotal(9,E11:E29))｝ なので今回のような方法を考えました。
・SUBTOTALの列はB列以降であればどのセルでもOKです。※A列だと循環参照になってしまいます。
・この方法ならエクセルが詳しくない方（ピボットテーブルすら分からない）でも色々シミュレーション出来るのではないでしょうか。