J-Plat PatとExcelで描くバブルチャート

J-Plat Patで500件/回のCSV出力ができるようになったので、Excelでバブルチャートを描いてみた

任天堂の特許2858件(下図)を6~7回かけてCSVダウンロードして、Excelで集計、バブルチャートを描いてみる。

目指すグラフをイメージしてみると、上の「FI別」を縦軸、「公開年」を横軸、特許件数をバブルサイズにした,バブルチャート(下図)

① CSVダウロードしたデータを結合して、Execlシートを作成

② 公開年と分析対象のFIの有無(フラグ)を示す列を作成

公開年は、公開日のD列を参照するYEAR関数

分析対象のFIのフラグは、関数 =IF(ISERROR(FIND(O$1,$G2)),0,1)
FIND関数=対象のG列(FI)に検索文字列のO列(検索するA63)にみつかればその位置、みつからなければエラーになる
IF(ISERROR(FIND・・・),0,1)は、FIND関数がエラーなら 0、なければ 1
分析対象のFIの有無を示すフラグになる。
注:この例(FI全体を対象)に代えて、筆頭FIを対象にするのも一案

③ 入力した関数を最下行までコピーして、FI分析を完了

④ 公開年ごとにFIの件数を集計(ピボットテーブル)

⑤ 5年ごとに集計

ピボットテーブルをコピーして(10行~)、SUM関数を使って、5年ごとに件数を集計する(1~8行)
(横軸を公開年のままにするか、何年かごとに集計するかは、適宜調整)

⑥ 集計した表に「条件付き書式」を設定すると、バブルチャート風(?)の簡易的な見える化ができる

⑦ Excelでバブルチャートを描く

Excelのバブルチャートは、X, Y, Zの元データが必要。X, Yは数値で座標を示し、Zはバブルの大きさを表す。すべて数値で示す必要がある。

上のまとめ表を、
X=公開年(範囲に代えて代表年)
Y=FIを表示する位置(座標)。A63を最上、H05を最下にしたいのでそれに応じた数値を入力。
Z=集計した件数=バブルの大きさになる
の形に並べ直す。

並べ直したデータを元にして、バブルチャートを描く。

⑧ バブルチャートの書式を整える

「データ系列の書式」でバブルサイズを調整(ex.バブルが重ならないように)
「データラベルの書式」で「バブルサイズ」を表示

縦軸、横軸は、座標(数値)にしたので、元のFIを上書きする。
(たとえば、画像を作って重ねる)

できあがり!!

お化粧はあまり上手ではありませんが、何とか完成。

コメントを残す

メールアドレスが公開されることはありません。