[Python] 基礎-7 EXCELファイルの読み書き

1.一般的な流れ

“openpyxl”モジュールをインポート:import openpyxl
Workbookを開く:openpyxl.load_workbook(“ファイル名”)
シートオブジェクトを生成:sheet_obj = workbook.get_sheet_by_name(“シート名”)

2.セルの値の読み書き(cell(row=行番号, column=列番号).value)

〔 値の取り込み 〕
変数 = sheet_obj.cell(row=行番号, column=列番号).value

〔 値の書き込み 〕
sheet_obj.cell(row=行番号, column=列番号).value = 値

3.行/列 単位の扱い( iter_rows() / iter_cols() )

(iteration processにより)1行ごと(1列ごと)に、リストを生成する。

〔 使い方の例 〕

for row in sheet_obj.iter_rows():
for col in range(len(row)):
print(row[col])

4.フォントの指定

〔 流れ 〕
① Font関数をopenpyxlモジュールのstyleからインポート
  from openpyxl.styles import Font
② フォントの指定
  font = Font(name=’フォント名’, size=ポイント数, ・・・)
③ 指定したフォントをセルに指定
  sheet_obj.cell(row=line,column=col+1).font = font

パラメータ説明
nameフォント名/例:’游ゴシック’, ‘Arial’
sizeポイント数/例:「size=10」等整数値で指定
color色を16進6桁の文字列で指定
例:’FF0000’(赤), ’00FF00’(緑), ‘0000FF’(青)
underline下線を文字列で指定/例:’single’
bold太字/例:True, False
italic斜字/例:True, False

5.位置揃え

〔 流れ 〕
① Alignment関数をopenpyxlモジュールのstyleからインポート
  from openpyxl.styles import Alignment
② セルごとにAlignmentを指定
  sheet_obj.cell(row=line,column=col+1).alignment
  = Alignment(horizontal=’left’,
   vertical=’top’,
   wrapText=None)

〔 Alignment関数の引数とパラメータ 〕

    引数パラメータ説明
horizontal‘left’
‘center’
‘right’
左揃え
中央揃え
右揃え
vertical‘top’
‘center’
‘bottom’
上揃え
中央揃え
下揃え
textWrapTrue
None
セル内折り返しあり
指定せず

6.セルの表示形式

〔 流れ 〕
① numbersをopenpyxlモジュールのstyleからインポート
  from openpyxl.styles import numbers
② セルのフォーマットを指定
  sheet_obj.cell(row=line,column=col+1).number_format = 表示形式

    表示形式 説明
‘0.000’小数点以下3桁
#,##0′3桁ごとにカンマ区切り&小数点以下は非表示
‘yyyy/mm/dd’短い日付(西暦4桁/月/日)

7.セルの幅と高さ

〔 セル幅の指定 〕
sheet_obj.column_dimensions[’A’].width= セル幅

〔 セル高の指定 〕
sheet_obj.row_dimensions[1].height = セル高さ

特許マップDIYサポートキャンペーン 第3回

外出自粛/在宅勤務 応援企画

特許マップDo It Yourself
サポートキャンペーン 第3回

(緊急事態宣言期間中無料)

緊急事態宣言が、昨年4月、今年1月に続き三度発出されてしまいました。特許マップDIYサポートの無料サービスキャンペーンも、このたびもう一度行うことにしました。この災禍を福に転じたいと願っております。

かねがね、特許マップを自分で作ってみたいと思っていた方、チャレンジしてみませんか?
新型コロナウィルスの脅威はありますが、まとまった時間がとれる機会は増えているのではないでしょうか? 

    • ・検索式作成 相談、代行
    • ・ヒット文献集合csvダウンロード
    • ・Excel(関数/ピボット/マクロ)操作 

特許マップを作るには、まず目的に合った特許文献集合を作る必要があります。是非、この機会にご相談ください。弁理士の職業的守秘義務の範囲です。業務上のテーマについてもご相談いただけます。

商用の検索データベースを利用できない方には、作成した文献集合をcsvダウンロードして無償提供します。

Excel(関数/ピボット/マクロ)については、セミナーのプレゼン資料(Seminar-PatentSearch-and-Map_20190703)か拙著を参考に進めてください。こみ入った内容のご質問にはSkype, ZoomなどのWeb会議システムで対応いたします。Excelシート画面を共有しながらQ&Aできますので。

メール(mail to: kojima#aq-patent.com 注:「#」を「@」に代えてください)でお申し込みください。

特許マップDIYサポートキャンペーン 第2回

外出自粛/在宅勤務 応援企画

特許マップDo It Yourself
サポートキャンペーン
 第2回

(緊急事態宣言期間中無料)

緊急事態宣言が、残念ながら昨年4月に続き再び発出されてしまいました。特許マップDIYサポートの無料サービスキャンペーンも、このたびもう一度行うことにしました。この災禍を福に転じたいと願っております。

かねがね、特許マップを自分で作ってみたいと思っていた方、チャレンジしてみませんか?
新型コロナウィルスの脅威はありますが、まとまった時間がとれる機会は増えているのではないでしょうか? 

    • ・検索式作成 相談、代行
    • ・ヒット文献集合csvダウンロード
    • ・Excel(関数/ピボット/マクロ)操作 

特許マップを作るには、まず目的に合った特許文献集合を作る必要があります。是非、この機会にご相談ください。弁理士の職業的守秘義務の範囲です。業務上のテーマについてもご相談いただけます。

商用の検索データベースを利用できない方には、作成した文献集合をcsvダウンロードして無償提供します。

Excel(関数/ピボット/マクロ)については、セミナーのプレゼン資料(Seminar-PatentSearch-and-Map_20190703)か拙著を参考に進めてください。こみ入った内容のご質問にはSkype, ZoomなどのWeb会議システムで対応いたします。Excelシート画面を共有しながらQ&Aできますので。

メール(mail to: kojima#aq-patent.com 注:「#」を「@」に代えてください)でお申し込みください。

[関数] 筆頭出願人を抽出(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”に変えただけで、前回のプログラムと同じ処理。