【Python × Excel】OpenpyxlでVlookupをやってみる

11346 ワード
Python3 Python vlookup Openpyxl Excel Python テキストリンク

はじめに

PythonでExcelを操作していて、Vlookupしたいときありませんか？Vlookup便利ですよね。
ということでOpenpyxlを使ってブックをまたいでVlookupする方法を解説します。

今回は下記の評価データと給与データをサンプルとして使用します。

評価データ

給与データ

準備

必要なライブラリをインストールしましょう。今回使うのはOpenpyxlです。

import openpyxl as px

必要ファイルの読み込み

ここで評価データと給与データの２つのエクセルを読み込みます。

wb = px.load_workbook("C:\\Users\\ユーザー名\\Documents\\Qiita用サンプル\\評価データ.xlsx")
ws = wb["評価"]
wb2 = px.load_workbook("C:\\Users\\ユーザー名\\Documents\\Qiita用サンプル\\給与データ.xlsx")
ws2 = wb2["給与"]

Vlookup

いよいよVlookupをしていきます。

#Get last row
lastrow = ws.max_row
lastrow2 = ws2.max_row

#get values from IMaster Sheet
for i in range(2, lastrow + 1):
    name_hyoka = ws['B' + str(i)].value
    for j in range(2, lastrow2 + 1):
        name_salary = ws2['A' + str(j)].value
        # get values by vlook up
        if name_hyoka == name_salary:
            salary = ws2['B' + str(j)].value
            ws.cell(row = i, column = 5, value = salary)
            break

#Save
def save():
    wb.save(filename = "C:\\Users\\ユーザー名\\Documents\\Qiita用サンプル\\評価データ_給与.xlsx")
save()

上記コードでは、評価データの社員名を検索値として給与データの社員名カラムで検索をかけています。そして社員名が一致した場合は給与データの給与を、評価データのE列に書き込むようにしています。

ポイントは
①２つのシート入れ子にする形でそれぞれをForループで回す。
②if文で文字列一致の判定をする。
③一致していた場合のみ値をセルに書き込む
④break忘れない＊ここ大事
⑤最後にwbを保存

データの読み込みはカラムのアルファベット指定で出来たのですが、書き込みの時はそれがうまくいかなかったので数字でカラムを指定しています。

ちなみにbreakを入れるのは文字列一致を発見した後、再び外側のループに戻って次の検索値を検索するためです。

コード全体としてはこんな感じです。

import openpyxl as px

wb = px.load_workbook("C:\\Users\\ユーザー名\\Documents\\Qiita用サンプル\\評価データ.xlsx")
ws = wb["評価"]
wb2 = px.load_workbook("C:\\Users\\ユーザー名\\Documents\\Qiita用サンプル\\給与データ.xlsx")
ws2 = wb2["給与"]

#Get last row
lastrow = ws.max_row
lastrow2 = ws2.max_row

#get values from IMaster Sheet
for i in range(2, lastrow + 1):
    name_hyoka = ws['B' + str(i)].value
    for j in range(2, lastrow2 + 1):
        name_salary = ws2['A' + str(j)].value
        # get values by vlook up
        if name_hyoka == name_salary:
            salary = ws2['B' + str(j)].value
            ws.cell(row = i, column = 5, value = salary)
            break

#Save
def save():
    wb.save(filename = "C:\\Users\\ユーザー名\\Documents\\Qiita用サンプル\\評価データ_給与.xlsx")
save()

動作の結果はこちらです。

E列に給与が入ってますね。

参考にしたサイト

https://fastclassinfo.com/entry/python_excel_vlookup/
こちらのサイトの解説を参考に実践しました。