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を上書きする。
(たとえば、画像を作って重ねる)
できあがり!!
お化粧はあまり上手ではありませんが、何とか完成。