[関数] 筆頭出願人を抽出(FIND, IF, ISERROR, LEFT)

共同出願の特許文献には、出願人欄に複数の出願人名が入力されている。そのような場合に、先頭の出願人のみに注目して分析を進めたい場合がある。例えば、出願人(会社)ごとの出願件数を集計したい場合、その会社(出願人)単独の出願と共同出願とが別人として扱われるとうまく集計できない。

単独出願と共同出願が混在するので場合分けが必要で、EXCELでは関数で処理するのは少々面倒。しかし、複数の関数を使った数式の作り方の例になるのでご紹介する。

FIND関数
G30セルが「ツィンファ ユニバーシティ」と「鴻海精密工業股▲ふん▼有限公司」の共同出願で、2社が半角コンマ「,」で区切られている。(区切り記号としてセミコロン「;」が使われているデータもある。)
FIND関数は、この区切り記号である半角コンマ「,」が何文字目にあるのかを出力する(図1)。

図1 FIND関数

LEFT関数
複数の出願人名が記載されているセルから、区切り記号である半角コンマ「,」よりも前に部分だけを抜き出す。LEFT関数は、参照先の文字列の先頭から指定された文字数だけを抜き出して出力する。
参照先は出願人であるF30セル、文字数はFIND関数で求めた区切り記号「,」の位置(FIND(“,”,F30)よりも1文字前までである。
① P30セルにおいて、既に入力した「FIND(“,”,F30)」の直前にカーソルを移動する。
② 「LEFT(」を入力して、参照する「文字列」に「F30」を入力する。
③ 半角コンマ「,」を入力して「文字数」の入力に移る。
④ 「FIND(“,”,F30)」の後ろに「-1」を入力する(図2)。

図2 FIND関数とLEFT関数の組み合わせ

他の行にコピーするとエラーになる(図3)。

図3 他の行(単独出願)にコピーするとエラーになる

FIND関数は、探す対象の文字列の中に目的の文字(文字列)が見つからないと、上のように「#VALUE!」というエラーを出力する。

そこでエラーになったら、つまり出願人欄(F列)に区切り記号「,」が見つからなければ、(単独出願だと判断して)、出願人欄(F列)をそのまま出力する。

IF関数とISERROR関数
IF関数 IF(条件,[真の場合],[偽の場合]) 条件を判断した結果、「真」なら「真の場合」、「偽」なら「偽の場合」に指定された結果を出力する。なお、カギ括弧「[・・・]」の引数は省略してもよい。
ISERROR関数 ISERROR(数式) 数式を実行した結果、エラーなら「真」、エラーでなければ「偽」を出力する。上のIF関数の「条件」部分に使う。

図4 エラーになるかどうかで場合わけする

「=IF(ISERROR(FIND(“,”,F31)),F31,LEFT(F31,FIND(“,”,F31)-1))」
IF関数の「条件」にはFIND関数(FIND(“,”,F31))がエラーになる(F31に区切り文字「,」が含まれない)かどうかを調べるISERROR関数「真の場合」(エラーになる場合)はF31セルの値をそのまま出力「偽の場合」(エラーにならない場合:F31に区切り文字「,」が含まれている場合)は区切り文字の前までを抜き出すLEFT関数の結果を出力する。

P31セルをすべての行にコピーする。このとき共同出願の行(30行、40行)にも同じようにコピーする。共同出願の場合は筆頭出願人だけが抽出される(図5)。

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

[マクロ]  変数とデータ型、Forループ

〖 [マクロ] はじめてのプログラミング 〗で紹介したとおり、プログラムは、
   Sub プログラム名()
     :
   End Sub
の間に書くが、前回紹介した処理のように、同じ処理を繰り返すときには、「変数」を使って、ループを組む方が良い。

前回と同じ例で、各行で発明者数が3人以上かどうかを判定するプログラムを書いてみよう。

「行」を指定する変数を使って、2行目から11行目に同じ処理を繰り返す。

〔 Dim(変数宣言)文 〕

「変数」は「この変数を使いますよ」という意味で、予め「宣言」しておく。このときに、その変数のデータ型も合わせて宣言する。

Dim 変数名 As データ型

 

主なデータ型と使い方

データ型 説明 使い方
Integer 整数(-32,768~32,767) 行や列の指定
Long 整数(-2,147,483,648~2,147,483,648) 大きな数の場合
String 文字列 出願人名など
Date 日付 出願日など

ここで紹介したのは、特許情報分析でよく使うものだけ。他にもたくさんのデータ型がある。
詳しくはこちら(Microsoftのサポート)

〔Forループ〕

同じ処理を繰り返すには、「ループ」という命令を使うが、代表的なものが「Forループ」

For 変数=初期値 To 最終値 Step 増分
    繰り返したい処理の内容
Next 変数

この他、Do While,Do Untilなどいろいろなループがある。

〔Cellsプロパティ〕

前回は「Range(“G2”)」のようにセル名を文字列「”G2″」で指定したが、2行目、3行目、・・・のように、行と列を数値で指定したいので、「Cellsプロパティ」を使う。

Worksheet(“Sheet1”).Cells(,).value

上のように、前側にシートを指定する(Worksheet()やActiveSheet)をドット”.”を使って連結し、後方にはそのセルの属性をドット”.”を使って連結する。

後方に連結する属性には、値(value)の他、フォント(Font.Name, Font.Size)などがある。内容をクリアする”ClearContents”などようなメソッドを連結することもできる。
詳しくはこちら(Microsoftのサポート)

〔プログラム〕

いよいよプログラムを書いてみよう。

Sub step_2()
    Dim row As Integer
    Cells(1, 7) = “発明者数判定
    For row = 2 To 11
        If Cells(row, 6) >= 3 Then
            Cells(row, 7) = “多”
        Else
            Cells(row, 7) = “少”
        End If
    Next row
End Sub

プログラム名は、前回の”step_1″に続いて”step_2″としてみた。”step_1″の”End Sub”の後ろに改行して続けて書いていけば良い。

Dim row As Integer
行を指定する変数「row」を整数(Integer)として宣言している。まれに”Integer”の数値範囲を超える行数になる場合がある。そのときは”Long”を使う。

Cells(1, 7) = “発明者数判定”
“G1″セルは、1行目、7列目なので、Cells(1, 7)と指定して、書き込みたい文字列を入力している。上の〔Cellsプロパティ〕では前側にシート指定、後方に属性を指定するように説明したが、省略可能な場合があるので、とりあえず指定しないで試してみると良い。元に戻れる工夫(コピーを作っておくとか)をして、うまくいかなかったら、Googleなどで調べて対策すればよい。
“G”列を「7行目」と読み替えるのが少々面倒だが、”A列”から順に1, 2, 3, ・・・の連続値を入力した行を作っておくなど、工夫すれば良い。

For row = 2 To 11
  :
Next row
行の変数”row”を2から11まで増やしていくForループ。増分が1なら”Step”は省略して良い。

If Cells(row, 6) >= 3 Then
        Cells(row, 7) = “多”
Else
        Cells(row, 7) = “少”
End If
セルを指定する方法を”Range”から”Cell”に変えただけで、前回のプログラムと同じ処理。

[マクロ] はじめてのプログラミング

[準備]が終わったら、マクロプログラム本体を書き始めてみよう。
Visual Basicのウィンドウ(図1)の
「Sub utility()」と、
「End Sub」の間に書き込んでいく。

図1 Visual Basicウィンドウ

マクロプログラムを書いたら、[実行]や[デバッグ]を行うことになる。
ショートカットで表示されている。
緑色の「▷」は実行、二本の縦棒「|| 」は中断、四角「□」はリセット。
試しに、この状態で実行「▷」をクリックしてみよう。
何も起こらない…..
マクロ本体に何も書いてないからあたりまえ!

《STEP 1》

EXCELに下の図2のようなデータがあったとしよう。(出願番号は実際にあるデータだが、「発明者数」の欄はまったくの創作である。)
「発明者数」の欄の右に、発明者が3人以上かそれより少ないかを判定して、多ければ「多」、少なければ「少」を出力する「発明者数判定」欄を追加するプログラムを作ってみよう。


図2 処理対象のEXCELデータ

プログラムは、データを入力して、何らかの処理をして、その結果を出力するのであるから、最も基本的な機能である。

G1セルに「発明者数判定」と書き込んでみる。
① まずプログラム名を「step_1」に変更しよう。(いきなり「utility」では名前負けする気がする。因みに、ハイフン「-」ではなくアンダーバー「_」を使うのは、引き算のマイナス「-」との混同を避けるため。プログラミングではおなじみなので、慣れていただきたい。)
② 「G1セル」に「発明者数判定」と書きこむ

Sub step_1()
    Range(“G1”) = “発明者数判定”
End Sub

早速、実行「▷」してみよう。 意外と簡単に成功!


図3 G1セルへの出力に成功!

③ 「F2セル」の値を読み込んで3以上かどうかを判定して、その結果を「G2セル」に出力する。
  「判定」には「if文」を使おう。文法は、

  If 条件 Then
    条件が「真」だったときの処理
  Else
    条件が「偽」だったときの処理
  End If
  
  である。書いてみよう。

Sub step_1()
    Range(“G1”) = “発明者数判定”
    If Range(“F2”) >= 3 Then
        Range(“G2”) = “多”
    Else
        Range(“G2”) = “少”
    End If
End Sub

早速、実行「▷」してみよう。 またまた意外と簡単に成功!


図3 発明者数を読み込んで判定し、結果を出力した

ここまでできれば後は簡単!他の行にも同じ処理を繰り返せば良いのだから。プログラムはこんな風になる。

Sub step_1()
    Range(“G1”) = “発明者数判定”
    If Range(“F2”) >= 3 Then
        Range(“G2”) = “多”
    Else
        Range(“G2”) = “少”
    End If
    If Range(“F3”) >= 3 Then
        Range(“G3”) = “多”
    Else
        Range(“G3”) = “少”
    End If
             :
             :
    If Range(“F11”) >= 3 Then
        Range(“G11”) = “多”
    Else
        Range(“G11”) = “少”
    End If
End Sub

正しく動作するとは思うけれど、さすがに芸がなさすぎる。それに、行数が膨大になるとやってられない。
そこでプログラミングの世界では、同じ動作を繰り返すときには、「ループ」を使う。
次回「[マクロ] STEP 1 “For Loop”」に続く。

《コラム》 用語「マクロ」「VBA」「プログラム」「マクロプログラム」について
上の4語をなんとなく使い分けているような、同じ意味で使っているような。
マクロ:コンピュータ(アプリケーション)を自動的に操作するための仕組みを一般的に「マクロ」と呼ぶ
マクロプログラム:マクロの内容を、プログラミング言語を使って記述したもの、つまり、「マクロのためのプログラム」のこと
プログラム:プログラミング言語で記述された、コンピュータにさせたい動作の内容
ここで、「プログラミング言語」を説明なしで使ってしまっている。
プログラミング言語:プログラムを解釈してユーザーが期待する通りにコンピュータに動作させるために、どのような命令を与えれば良いか、プログラムを書くときの一定の決め事、約束。その決め事、約束は、「文法」とも呼ばれ、「言語」にみたてられて名付けられている。
VBA:Visual Basic for Applicaton.  古くから使われている「BASIC」というプログラミング言語がある。これを改善されて「Visual」になったのが「Visual Basic」。アプリケーション向けにカスタマイズしたのが「VBA」。
まとめると、「マイクロソフト社が、Excelを含むOfficeアプリケーションのマクロを実現するためにVBAを提供していて、ユーザーがそこに書くプログラム(マクロプログラム)を「マクロ」と呼ぶことも多い」ということになる。

[マクロ] 準備

EXCELマクロを使うための準備。
① [名前を付けて保存]→「EXCELマクロ有効ブック (*.xlsm) 」で保存(図1)
② [ファイル]ー[オプション]ー[リボンのユーザー設定]で、「開発」タブを有効化(図2)

図1 「EXCELマクロ有効ブック (*.xlsm) 」として保存
図2 [開発]タブの有効化

次に、[開発]タブを使って、Visual Basicを起動(図3)して、マクロプログラムを入力(編集)し、実行できるウィンドウ(図4)を立ち上げる。

図3 マクロプログラムの新規作成

新規作成のときは、マクロ名(プログラム名)を入力して[作成]する。
作成したマクロプログラムがいくつかたまってくると、[編集]や[実行]が可能になる。

図4 [マクロ]プログラムを編集・実行するためのウィンドウ

図3で入力されたマクロ名(プログラム名)「utility」で、マクロプログラムが作成されるが、最初なので中身は空。

Sub utility()
    この部分にプログラムを書いていく。
End Sub

マクロ名(プログラム名)「utility」の後ろの「()」は、引数を指定するための領域。
「引数」というのは、このプログラムに引き渡したり実行結果を返してもらうデータのことだが、当面は使わないので忘れて良い。

《コラム》「EXCELマクロ有効ブック (*.xlsm)」はコンピュータウィルス!?

「EXCELマクロ有効ブック (*.xlsm)」で保存したEXCELファイルをメールに添付して他の人に送るのは、うまくいかないことがあることを覚えておいてほしい。
メールサーバーや受信側PCで働いてくれているセキュリティソフトが、コンピュータウィルスだと判断して削除してまうことがあるからだ。「添付ファイルが削除されました」のようなメッセージが表示されるならよいが、だまってメール自体を削除してしまうこともある。
他の人にメールで送る場合は、「届かないこともある」と思って、届いたかどうかの確認を怠らないこと。
届かないときの代替策は、例えば以下。
(1)Dropboxなどクラウドサーバーを介して、送りたい人にダウンロードしてもらう。(下の(2)よりも簡単だが、ダウンロードの際にセキュリティソフトにブロックされてしまう恐れもある。)
(2)マクロなしの普通のEXCELブックと、プログラムを記載したテキストファイル(テキスト情報なら何でもよいので、送るEXCELブックのシートの一つにテキストで貼り付けても良い)とを送って、受信側で「EXCELマクロ有効ブック (*.xlsm)」を再構成してもらう。

《コラム》 データとマクロは必ず同じファイルか?

マクロプログラムで処理したいデータは、同じファイル内になくてもよい。
便利マクロを作って、いろいろなデータに使い回すなら、マクロを保存しているEXCELファイルとは別のファイルのデータを使えるように、マクロプログラムを書いておくのがよい。
ただし、あるデータではうまく動作したマクロが、別のデータではうまく動かないことがよくあるため、初心者のうちは、データとマクロはセットにして扱うこととして、同じファイルに保存した方がよい。

《コラム》 WordやPowerPointにもマクロはある!

EXCELマクロと較べると知名度は低いが、WordやPowerPointでもマクロを使うことができる。この「準備」で紹介したマクロを使うための準備は、WordやPowerPointのファイルでも同じなので、興味のある方はチャレンジしてみてはいかがだろうか?
マクロ本体のプログラミング言語はVisual Basicで、EXCELマクロと同じ。データの参照方法、使えるメソッドなどを調べれば、何とか使える。

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

[関数]を使って作成した「出願年」(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