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

Sachertorten | Flickr

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


オーダーの概要は

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

というもの。

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

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


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



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

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

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

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

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

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

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


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



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

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

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



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




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


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

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


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



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

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

こんな感じに。


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



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

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


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


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

関数としては

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

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



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

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


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

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


セル内の数字を３で割って「割り切れる」「１余る」「２余る」で色分けです。

　　（無指定）

=MOD(A1,3)=1
=MOD(A1,3)=2

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



シンプルに偶数／奇数で分けるなら

（無指定）　と　=MOD(A1,2)=1　　を併用。

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


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


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


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

ここで行った処理は

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

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


こんな具合に。


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

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



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

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



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

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


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

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


７） ファミリー内の出願人名を穴埋めする
＝IF関数の利用

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



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



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



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


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

優先順位１／A列（Family）／昇順
優先順位２／M列（出願人名）／降順

としました。

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



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


関数の日本語(?)訳

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

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

・・・・となります。


８）ついでにGooglePatentにリンクを貼る
＝Hypertext関数

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

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


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

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


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

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


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

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



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


それでは。
よい１日を。



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

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



別窓で登録フォームが開きます

■ 連絡板 ■

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