ヘッダーメニュー

2014/06/06

[Excel] パテントファミリーのリスト作成に使った8つのExcel関数。


Sachertorten | Flickr

酒井です。こんにちは。
今週前半は 「パテントファミリー単位のリスト」 を作成しておりました。


オーダーの概要は
・ 出願人名(日本企業)が指定され
・ かつ、特定の国(複数)で公報発行済みもの、をピックアップ
・ クライアント様の指定項目を入れて、リストを作成。
というもの。

リスト作成に、いくつかExcelの関数使いました。
※私、マクロの知識はほぼゼロです。いつも関数ばかり使ってます。

それぞれ、知らなくてもリスト作成自体はたぶん可能・・・?ですが、
知ってると何倍も早くリストが完成したり、
完成したリストが使いやすくなるものばかり、です。


備忘録兼ねて、使った関数をご紹介します。
記事の続きからどうぞ。



0) はじめに (関数じゃないけど)

この検索「特定の国(複数)で公報発行済」という条件なのですが、
「米国」が入っているのがミソだったりもしました。

普通に考えたら、
「海外公報を収録しているデータベースで出願人検索」ってなりそうですが、

公開直後で、Assigneeが入っていない米国公報

があるかもねー? と考えると、

日本特許をサーチ 
→ ファミリー情報(各国出願番号)持ってるので、これを抽出
→ 海外データベースで番号検索

の手順の方が、確実かな?って考えました。
※ここはお使いのデータベース、対象国、年代・・・などで、設計が変わりますYO!


というわけで、日本特許のサーチからスタートです。
※説明図には案件と無関係なデータを使っております。念のため。



1) セルに、特定の国の番号が含まれるか?を調べる
= セルに特定の文字列が含まれるか否か調べる

日本特許をサーチし、ファミリー文献(各国出願番号)を出力したところ。

データはこんな状態 (汗)
1セルに、全部の番号が入ってるタイプですねー。



このセルの中に、特定の国コードが含まれるか否か?を調べます。
関数使うと、大量にデータがあっても数秒で・確実に調べられますよ~(≧∇≦)




上記の例ですと、
セルB2には  =IF(ISERROR(FIND("US",A2))=TRUE,"","US")
セルC2には  =IF(ISERROR(FIND("EP",A2))=TRUE,"","EP")


それぞれ、セル内に特定の文字列があれば、
指定の文字列を返す、という関数でございます。

後は、指示された国が揃ってる行だけを取り出して、
そこから、必要な出願番号を取得すればOK!


・・・・という手順で
出願番号リストを作ります。



2)出願番号を検索システム側の仕様に合わせて変換
= 文字列から指定の箇所を切り出す+くっつける

番号形式の変換には、文字列操作の関数が便利です。(^^)

こんな感じに。


セルB2の関数は ="EP"&RIGHT(A2,8) を入れてます。



3) わかりやすくファミリー番号をつける
= IF関数で文字列を識別する

弊社で使用している海外DBの出力に 「ファミリー管理番号」という項目があります。
この項目によってパテントファミリーがわかるんですが・・・


・・・前言撤回。気を付けて見ればわかるけど、ぱっと見、よくわかりません。


これを見やすくするため、ファミリー番号を入れます。(A列)

関数としては

A2には 「1」(数字) のみ。
A3には =IF(B3=B2,A2,A2+1)
A4以降には、A3をずーーーっとドラッグします。

後でソート等を行う可能性があるので、
ファミリー番号が入ったら、列全体をコピー → 「数値」をペースト します。



4)ファミリーを色分けする。
= 条件付き書式+割り算の余り。行に入っている数値で色分け。

先ほどのファミリー番号を色分けすると、更に見やすくなりますねー♪


白→緑→青、の3色で色分けしてみました。

ここは「条件付き書式」を使ってます。


セル内の数字を3で割って「割り切れる」「1余る」「2余る」で色分けです。

  (無指定)
=MOD(A1,3)=1
=MOD(A1,3)=2

を使い分けます。
書式管理画面で見ると、こうなります。



シンプルに偶数/奇数で分けるなら
(無指定) と =MOD(A1,2)=1  を併用。

もっと色数を増やしたい場合は、
=MOD(A1,5)=1、=MOD(A1,5)=2 ・・・  という風に
「割る数」と「余り」を増やして指定すればOKです。


5) 出願人の表記を揃える ①
= 文字を全部大文字(小文字)にする


データベースから出力された出願人名は、結構フリーダム(笑)です。


大文字・小文字も混在してますし、
米国公開などですと、発明者名だけが入っているレコードも珍しくありません。

ここで行った処理は

「最新の出願人・権利者と、標準化出願人。どちらでも良いので、ターゲットの出願人名が入っていたら識別する」 こと。

その下準備として、
データをすべて大文字(又は小文字)に揃えます。
ついでに、一挙に識別作業ができるよう、「最新の出願人・権利者と、標準化出願人」2つのセル内容を合体させます。


こんな具合に。


M列の関数は
=UPPER(N2)&" / "&UPPER(O2)

大文字に揃えてます。真ん中の " / " は区切り文字です。なくても構いません。



6) 出願人の表記を揃える ②
= セルに特定の文字列が含まれるか否か調べる

先ほど下準備したセルを対象に、文字列を識別させます。
識別結果は日本語で返してもらうことにします。



・・・これ、最初の関数と同じものを使ってます。

セルM2ですと
=IF(ISERROR(FIND("APPLE",N2))=TRUE,"","アップル")  です。


識別結果は、ソートをかけて集め、
コピー → 値をペースト、で確定させましょう。

複数の出願人についてリストを作っている時は、
対象文字列を変えて、出願人の数だけ同じ操作を繰り返します。


7) ファミリー内の出願人名を穴埋めする
=IF関数の利用

出願人名の表記揃えが終わり、改めてデータを見ますと、
同じファミリーなのに、穴が空いている箇所があったりします。



権利譲渡データがまだ反映されてないのかしらん? なんて思うのですけどね。



ここ、穴埋めしましょう♪



ファミリー内に「アップル」があれば、空欄を穴埋め。
なければ穴埋めしない。 という処理です。


下準備として、データをソートしておきます。
この例では

優先順位1/A列(Family)/昇順
優先順位2/M列(出願人名)/降順

としました。

そして、修正用のL列に関数を入れます。



=IF(M2="",IF(A2=A1,L1,""),M2)


関数の日本語(?)訳

「もし セルM2の出願人名が空欄("")なら、
A2とA1のファミリーが一致するかどうかを見る。
一致したら、L1の出願人名をL2にコピー。

M2の出願人名が空欄でなければ、
そのまま、M2の出願人名をL2にコピー。」

・・・・となります。


8)ついでにGooglePatentにリンクを貼る
=Hypertext関数

公報番号から外部サイトにリンクがあると、
何かと便利かもですね♪

一番リンクを作りやすいのは、たぶんGooglePatentかな、と。
※Espacenetが良いのでしょうが、URL形式が複雑なんですよ~(^^;


上記の例、セルM3ですと、L3の番号を参照させる形式で

=HYPERLINK("https://www.google.com/patents/"&L3,L3) です。


・・・欧州公開&米国公開は、
Google形式との桁合わせをしているので、もう少しだけ複雑な関数入れてます。

ここは、お使いのデータベースとの相性(=番号形式の一致具合)で
変化するので、工夫なさってみてくださいませ。


こちらにサンプルファイルを置きます
欧州公開・米国公開の関数や、
条件付き書式の設定などを確認されたい方は、ご自由にどうぞ。

設置場所はGoogle Driveです。
「リンクを知っていれば、ログインなしで閲覧可」に設定しております。
※Google Driveの利用方法のサポートはいたしかねます。ご容赦くださいませ(ぺこり)。



特許関連のデータは、規則性のあるものが多いので、
関数と相性が良いのですよね~


それでは。
よい1日を。



---------------------------
★ まだ書けていないリスト ★

インド特許の公報種別 (INPADOCの公報種別リスト)
「姿勢」の後日談
デジタル×アナログで公報を読む
「短気な者・検索を制す」説
インド特許庁で「20XX年に公開」を調べる
C-Sets



別窓で登録フォームが開きます
連絡板 ■少しずつ増えてます → 検索コラム集目次
代行特許調査のお問い合せはこちらからどうぞ
●  講習テキストの販売を始めました。
採用情報 はこちらから。よろしくお願いいたします。

1 件のコメント:

Shoukaku さんのコメント...

「=IF(ISERROR(FIND("US",A2))=TRUE,"","US")」の
「=true」は無しで
「=IF(ISERROR(FIND("US",A2)),"","US")」
で使えると思います。

コメントを投稿