[ピボット] 出願人別出願件数の年次推移

[関数]を使って作成した「出願年」(N列)と「筆頭FI」(O列)と「筆頭出願人」(P)を使って、ピボットテーブルを作成する。

図1 ピボットテーブルの挿入

① まずQ列に「出願件数」を入力する。値はすべての行で「1」。
  (不可欠ではないがあった方がわかりやすい)
② 集計したい範囲を選択する(N1からQ43までの範囲)
③ 「挿入」タブで
④ 「ピボットテーブル」をクリック
⑤ どこに挿入するかを指定するウィンドウがポップアップする(図2)

図2 「ピボットテーブルの作成」のためのポップアップウィンドウ

ピボットテーブルのために新しいシート「Sheet1」が作られ、そこにピボットテーブルが生成される(図3)

図3 生成されたピボットテーブル


「筆頭出願人」を選んで「行」にドラッグ、「出願年」を選んで「列」へドラッグ、「出願件数」を選んで「値」へドラッグすると、出願人ごとに出願年ごとに、出願件数が集計される(図4)。

図4 ピボットテーブルでの集計が完成する

「行ラベル」にある▽ボタンをクリックして、データを並べ替えると見やすい(図5、図6)

図5 並べ替え
図6 並べ替えた結果

この例では文献数が少ないため、見栄えのよいグラフにはならないが、ピボットテーブルをコピーしてグラフの元データを作ると、出願人別出願件数の年次推移のグラフが描ける。
注:なぜかデータをコピーしないと、グラフの元データにならない。ペーストは「形式を指定して値だけをペーストすること。また、空欄はには「0」を埋めること。

さらに、「筆頭FI」を選ぶと、「筆頭出願人」ごとにどんな技術分野に出願しているのかも集計される。

《サンプル》
http://lib.aq-patent.com/wp-content/uploads/2020/05/pivot.xlsx

[関数] 筆頭FIを抽出(LEFT関数)

複数のFIが入力されているセルから、先頭のFI(筆頭FI)のサブクラスまでを抜き出す。
FIのセル(G列)には複数のFIは、半角コンマ「,」で区切られている。(セミコロン「;」で区切られているデータもある)またFIのサブクラスは先頭4文字で、文字数にゆらぎはない。
先頭だけ抜き出したいので、半角コンマ「,」で区切られた2つめ以降は無視して良いので、FIのセル(G列)の左から4文字を抜き出すことにする。

LEFT関数を使うことにする。
LEFT関数が参照するのは、元のセル(G列)。引数として文字数を入力する。

O2セルにLEFT関数を入力する
 ① O2セルに「=」から入力する
 ② 「=L」と入力すると、Lから始まる関数が候補として表示される
   (引数は、文字列と文字数であることがわかる)
 ③ 「=LEFT(」まで入力した後、参照するセルG2をマウスで選ぶと入力される(上図)
 ④ 2つめの引数として、抜き出す文字「4」を入力する
 ⑤ 括弧を閉じてリターン(「=LEFT(G2,4)」)すると、計算結果「H01M」が表示される
 ⑥ O2セルをコピー、下方向のすべての行にペーストする(下図)

《サンプル》
http://lib.aq-patent.com/wp-content/uploads/2020/05/func_left.xlsx

《コラム》
関数が入力されたセルをコピーすると、参照先がペースト先に応じて変化する。
O2 =LEFT(G2,4) コピー元
O3 =LEFT(G3,4) ペースト先
O3 =LEFT(G3,4) ペースト先
      :
コピー元ではG2セルを参照している
次の行にペーストすると、参照先の行番号が1増えてG3セルになる
さらに次の行では、さらに行番号が増えてG4セルになる

列方向でも同様に変化していく

変化させたくないときは、変化させたくない行/列/両方に「$」マークを付ける(コピー元)
O2 =LEFT(G$2,4) コピー元
O3 =LEFT(G$2,4) ペースト先
O3 =LEFT(G$2,4) ペースト先
      :

[関数] 出願日から出願年を抽出(YEAR関数)

N2セルにYEAR関数を入力する
 ① N2セルに「=」から入力する
 ② 「=Y」と入力すると、Yから始まる関数が候補として表示される
 ③ 「=YEAR(」まで入力した後、参照するセルC2をマウスで選ぶと入力される(図1)
 ④ 括弧を閉じてリターン(「=YEAR(C2)」)すると、計算結果「2019」が表示される
 ⑤ N2セルをコピー、下方向のすべての行にペーストする

各行(各特許文献)について、「出願日」から「出願年」が抽出された。

《サンプル》
http://lib.aq-patent.com/wp-content/uploads/2020/05/func_year.xlsx

《コラム》
関数が入力されたセルをコピーすると、参照先がペースト先に応じて変化する。
N2 =YEAR(C2) コピー元
N3 =YEAR(C3) ペースト先
N3 =YEAR(C4) ペースト先
      :
コピー元ではC2セルを参照している
次の行にペーストすると、参照先の行番号が1増えてC3セルになる
さらに次の行では、さらに行番号が増えてC4セルになる

列方向でも同様に変化していく

変化させたくないときは、変化させたくない行/列/両方に「$」マークを付ける(コピー元)
N2 =YEAR(C$2) コピー元
N3 =YEAR(C$2) ペースト先
N3 =YEAR(C$2) ペースト先
      :

EXCEL (関数、ピボット、マクロ)入門 《はじめに》

このブログでは、関数、ピボット、マクロ(VBA))などのEXCELのお役立ち機能を紹介していきます。特に、特許マップを作るためなどの特許情報の処理に便利な機能を中心に解説します。
手はじめに、関数とピボットとマクロ(VBA)がどんなものか、そこから簡単に解説します。

〔関数〕
セルに数式として書き込みます。他のセルを参照して計算をし、計算結果をそのセルに表示します。
例:
 ・「出願日」から「出願年」を抽出

〔ピボット〕
EXCELで提供されている集計機能です。ピボットテーブルを作成して、どこかのEXCELシートに挿入します。
例:
 ・出願年ごとの出願件数を集計
 ・出願人ごとの出願件数を集計

〔マクロ(VBA)〕
EXCELで提供されているBASICプログラムです。(”BASIC”というのはプログラミング言語の名前)「EXCELでのいろいろな操作を記録しておいて、同じ操作を繰り返し使える」という売り文句でPRされていますが、私はそのような使い方はほとんどやったことがありません。
「〔関数〕だけではうまく処理できないような、ちょっと複雑な処理」をやらせるのに便利です。
例:
 ・「出願日」と「(複数の)優先日」の中から、最先の日を特定


入門用自習キット【特許マップDIYサポートキャンペーン】

特許マップDIY 自習キット

「調査テーマや目的などを思いつかないけれども、やり方だけは一通り体験してみたい」という方のために、入門用自習キットを準備しました。

「自習」とは言っても、遠慮なくどんどんご質問ください。

  • 関数を使うくらいで、出願件数の年次推移の折れ線グラフを描く
  • ピボットを使って、出願人別の出願件数年次推移のグラフを描く
  • EXCELマクロ(VBA)を使って、課題X解決手段のマップを描く

下のリンクからダウンロードしてください。(ファイルサイズが大きいので、2つの圧縮ファイルに分けてあります。

map_demo-1

map_demo-2

〔内容と使い方〕

“Excelを使った特許マップ_v1r1.pptx”:なぜ「Excelを使って自分で作ろう」なのかという背景と全体の流れの説明

“Excelを使った特許マップ_実習_v1r1.pptx”:作業のポイントを解説

“1_csvDL.xlsx”:csvダウンロードしたファイルを1つにまとめたExcel book
以下の4つのシートからなる(検索を実行は2017年)
・ ”LC”:液晶表示装置の特許=[G09G 3/36]/FIにヒットした1833件
・ ”PL”:プラズマ表示装置の特許=[G09G 3/28]/FIにヒットした245件
・ ”EL”:有機EL表示装置の特許=[G09G 3/30]/FIにヒットした827件
・ ”AL”:表示装置全体の特許=[G09G 3/20]/FIにヒットした3101件

“2_出願件数の年次推移.xlsx”:出願件数の年次推移
関数”YEAR”, “COUNTIF”を使用

“3_出願件数の年次推移_グラフ.xlsx” :「2」で作ったデータからグラフ描画
折れ線グラフ(散布図)、シェア棒グラフ

“4_ピボット.xlsx” : ピボットテーブルを使った出願人別の出願件数年次推移

“5_課題x解決手段.xlsx” : 「マクロ有効ブック」として保存
⇒ ”5_課題x解決手段.xlsxm”

“6_課題x解決手段-マクロ.xlsm” : 「解決手段」に相当するFタームが付与されている特許にフラグ”1″を立てるプログラム;
プログラム全体の構成と、Forループ、If文、InStr関数を学ぶ

“7_課題x解決手段-マクロ.xlsm” : 「課題」に相当するFタームが付与されている特許にフラグ”1″を立てるプログラム;
「課題」に相当するFタームは「解決手段」とは異なり、階層が深いので、その点をどう解決するかを学ぶ

“8_課題x解決手段-マクロ.xlsm” :7の実行結果

“9_課題x解決手段-マクロ.xlsm” :  8の実行結果(各特許にフラグを立てた)からマトリックスを作成(Macro2)⇒”LC-matrix”シート

“A_課題x解決手段-マクロ.xlsm” : 9の実行結果 マトリックスの完成
(可視化は未実施)

“A_課題x解決手段-マクロなし.xlsx” : マクロを無効化したExcel book