【Excel】目から鱗！動くグラフの簡単な作り方

PCスキルアップ

仕事の中でExcelを使ってデータを説明する機会があるかたは多いのではないかと思います。グラフを使って説明すると簡潔で視覚的にもわかりやすいですが、例えば様々な地域について説明する場合、条件を変えて複数の状況を説明する場合に、１つのグラフで全て説明できると便利だと思いませんか？
そんなの無理でしょ！と思われるかもしれませんが、簡単な工夫で見たい形に合わせて動くグラフが作れますので、ぜひお読みください。

動くグラフのコツは4つ！

動くグラフのコツは以下の３つです。順番に説明していきたいと思います。

  1. SUBTOTAL(103)
  2. SUMIFS
  3. テーブル
  4. スライサー

今回の説明のため、以下の表を用いたいと思います。例えば以下のような情報で各商品の予算進捗をミーティングで説明する場合、表で説明するよりもグラフで示せるとわかりやすいと思います。(これは簡易的な表ですが、実際仕事ではもっと複雑なケースが多いと思います)

このままグラフにしてみようと思うと、こんな感じになってしまいます。これではむしろグラフ化により見辛くなってしまいました。

例えば表にフィルタをつけてソートすれば、見やすくなりました。でも例えば、商品Aと商品Bというように、複数商品を選択して合算で見る、というような事はできません。

これをもっとうまいこと整理する方法をご説明します。

コツ① SUBTOTAL(103)

SUBTOTALと聞くと、SUBTOTAL(9, )のように、可視化されたセルの集計をするときに使うもの、と思っている方が多いと感じます。でも今回はSUBTOTAL(103, )という関数を使います。

SUBTOTAL(103)は、可視化されたセルの個数をカウントしてくれる関数です。具体的には例えばA列の表の横にSUBTOTAL(103,同行のB列のセル)を指定すると、”1″が表示されます。フィルターで非表示にすると”1″とはカウントされません。この関数をA列の表の上から下までの行に貼り付けます。(今回の場合、その後の計算の都合から、貼り付けは予算と実績部分のみで十分ですが、グラフの一番下まで貼り付けた方が漏れる心配がなくなります。)

コツ② SUMIFS

16-19行目の表を足しました。
17行目と18行目は、D17セルの計算をコピペしています。各月の22−３１行目から、
C列が予算か実績かを選び(式の”$C$22:$C$31,$C17″の部分)、
且つ、A列で1となるものを選び(式の”$A$22:$A$31,1″の部分)、
それらを集計した結果を表示させるというものです。
19行目の達成率は、単純に各列の18行目/19行目です。(例えばD19セルの式は”D18/D17″)

コツ③ テーブル

テーブルもグラフを整理するのに非常に便利です。今回は元の表のBC列を選び、「挿入」→「テーブル」を選択し、テーブル化します。

テーブル化すると、以下のようにその範囲に自動的に色がつきます。

コツ④ スライサー

テーブル化した理由はスライサーを使いたいからです。表を選びつつ、「挿入」→「スライサー」を選択すると、テーブルの項目名が出てきますので、スライサーにしたい項目を選択します。

作成されたスライサーの大きさを調整し、使いやすい場所に置きます。
また、グラフは元の表ではなく、先ほどSUMIFSで集計した表から作成しました。

スライサーで選択(クリック)すると、元のグラフはフィルターと同様に選択されたもののみが表示されます。スライサーでの選択は、Ctrl (MacはCommand)を押しながら選択すると、複数選択も可能です。以下の例では、製品Aと製品Cを選択していますので、この2つの製品の合算のグラフが表示されています。
尚、右のグラフは最初に元データの方で作成したグラフです。元データの場合、フィルターにより表示される製品は減るものの、合算されないため、複数選択するとそれぞれのグラフが同時に表示されてしまいます。

応用がしやすい

今回は単純な表で基本的な動くグラフの作り方を説明しました。このグラフであれば説明しながら画面をスクロールすることもなく、説明に応じてスライサーで表示内容を調整し、流れるように説明できます。また、自分で事前に確認するときも見やすくて便利です。
また、この方法だとスライサーで条件を絞ってもグラフの色は変わらないですし、グラフの元データが簡素化できるため、例えばデータラベルの表示をする際にラベル内容でセルの値を追加する等がしやすくなる等の利点があります。その他関数やテクニックと合わせ、色々な応用が考えられますので、皆様もぜひ試してみてはいかがでしょうか。

コメント