Pythonを使うとエクセル関数を作成することができます。
ここではPython×Excelの入門事例として、マスタデータを取得する「vlookup関数」を作っていきます。
それでは以下で詳しく紹介していきます。
目次
Pythonでエクセルvlookup関数(マスタデータ取得)を作ってみる
今回は以下の作業をpythonで行います。
1. Sheet9_1の商品コードを取得
2. Sheet9_2のマスタデータとマッチング
3. Sheet9_1に必要データを入力
上記のプロセスはvlookup関数を作成すれば対応可能です。
しかしopenpyxlというpythonライブラリを使ったら、どのようにプログラムを作ればいいのでしょうか。
本記事では、そのプログラム作成について詳しく紹介していきます。
Pythonプログラムの実行前準備｜Excelファイル保管とライブラリインストール
1. Excelファイルを準備
2. 必要なライブラリをインストール
準備1｜Excelファイルをフォルダに保存(「.py」と同じフォルダ)
対象となるExcelファイルをフォルダに保存します。
Pythonファイル「.py」と同じフォルダにExcelを保存すること
本事例では「Sample.xlsx」というファイル名にしています。
後半で紹介するプログラムをそのまま使用する場合は、「.py」と「.xlsx」が同じフォルダでないとエラーが発生します。
なお本事例では同じエクセルファイルの中にマスタデータを入れ込んでいますが、実際の仕事では別ファイルでマスタデータを管理することもあるはずです。
別ファイルのエクセルファイルを取得する方法は別記事で紹介していますので、そちらを参考にしてみてください（本記事後半で紹介しています）
準備2｜必要なライブラリをインストール
今回は以下の2つのライブラリをインストールします。
pip install openpyxl
Excelを操作するために使用するライブラリです。
上記をインストールしておかないと動かないので、注意が必要です。
Pythonプログラム解説
この記事で紹介するプログラムを解説しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
# プログラム1｜ライブラリ設定 import openpyxl as px # プログラム2｜対象のエクセルシートを読み込む filepath = 'Sample.xlsx' wb = px.load_workbook(filename=filepath) ws91 = wb['Sheet9_1'] ws92 = wb['Sheet9_2'] # プログラム3｜最終行の取得 lastrow91 = ws91.max_row lastrow92 = ws92.max_row # プログラム4｜シート9_1の商品コードを取得 for i in range(2, lastrow91 + 1): product_code = ws91['B' + str(i)].value # プログラム5｜シート9_2の商品コードとマッチング for k in range(2, lastrow92 + 1): master_code = ws92['A' + str(k)].value # プログラム6｜合致した行の値を取得 if product_code == master_code: product_name = ws92['B' + str(k)].value product_price = ws92['C' + str(k)].value break # プログラム7｜シート9_1に入力 ws91['E' + str(i)].value = product_name ws91['F' + str(i)].value = product_price ws91['G' + str(i)].value = product_price * ws91['D' + str(i)].value # プログラム8｜ファイル保存 wb.save('Sample9.xlsx') |
以下で詳しく説明しています。
プログラム1｜ライブラリ設定
|
1 2 |
# プログラム1｜ライブラリ設定 import openpyxl as px |
openpyxlはExcel操作で使います。
「openpyxl as px」とすることで、openpyxlをpxという変数で扱うようにしています。
プログラム2｜対象のエクセルシートを読み込む
|
1 2 3 4 5 |
# プログラム2｜対象のエクセルシートを読み込む filepath = 'Sample.xlsx' wb = px.load_workbook(filename=filepath) ws91 = wb['Sheet9_1'] ws92 = wb['Sheet9_2'] |
以下で一行ずつプログラムを解説します
プログラム解説
|
2 |
filepath = 'Sample.xlsx' |
「.py」ファイルと同じフォルダ内の「Sample.xlsx」を変数filepathとします。
|
3 |
wb = px.load_workbook(filename=filepath) |
filepath(‘Sample.xlsx’)で指定したExcelファイルを読み込みます。
所定フォルダに「Sample.xlsx」が存在しない場合、エラーとなりますので注意が必要です。
|
4 |
ws91 = wb['Sheet9_1'] |
読み込んだExcelファイルの「Sheet9_1」をws91として扱います。
なお読み込んだExcelに名前が「Sheet9_1」のシートが存在しない場合、エラーとなりますので注意が必要です。
|
5 |
ws92 = wb['Sheet9_2'] |
読み込んだExcelファイルの「Sheet9_2」をws92として扱います。
なお読み込んだExcelに名前が「Sheet9_1」のシートが存在しない場合、エラーとなりますので注意が必要です。
プログラム3｜最終行の取得
|
1 2 3 |
# プログラム3｜最終行の取得 lastrow91 = ws91.max_row lastrow92 = ws92.max_row |
以下で一行ずつプログラムを解説します
プログラム解説
|
2 |
lastrow91 = ws91.max_row |
「シート.max_row」で最終行を取得することができます。
この事例では、ws91(Sheet9_1)の最終行は21です。
|
1 2 |
lastrow = ws91.max_row print(lastrow91) |
実行結果
|
1 |
>>>21 |
|
3 |
lastrow92 = ws92.max_row |
この事例では、ws92(Sheet9_2)の最終行は8です。
|
1 2 |
lastrow92 = ws92.max_row print(lastrow92) |
実行結果
|
1 |
>>>8 |
ここで取得した最終行を使って処理をしていきます。
プログラム4｜シート9_1の商品コードを取得
|
1 2 3 |
# プログラム4｜シート9_1の商品コードを取得 for i in range(2, lastrow91 + 1): product_code = ws91['B' + str(i)].value |
プログラム解説
以下で一行ずつプログラムを解説します
|
2 |
for i in range(2, lastrow91 + 1): |
ここでは2行目から最終行まで処理を想定しています。（最終行はデータによって、50行になったり100行になったり可変することを想定）
したがって、「for i in range(2, lastrow+1)」と記載し、「i= 2, 3,・・・,lastrow」まで繰り返し処理を実行させます。
|
1 2 |
for i in range(2, lastrow91 + 1): print(i) |
実行結果
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
>>>2 >>>3 >>>4 >>>5 >>>6 >>>7 >>>8 >>>9 >>>10 >>>11 >>>12 >>>13 >>>14 >>>15 >>>16 >>>17 >>>18 >>>19 >>>20 >>>21 |
なお「for i in range(lastrow+1)」とすると「i = 0,1,2,・・・,lastrow」と繰り返しを処理を実行させることができます。
|
3 |
product_code = ws91['B' + str(i)].value |
変数product_codeに「Sheet9_1」のセルBi(i=2,3,・・・,lastrow91)の値とします。
|
1 2 3 |
for i in range(2, lastrow91 + 1): product_code = ws91['B' + str(i)].value print(product_code) |
実行結果
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
>>>A001 >>>A002 >>>A003 >>>A004 >>>A005 >>>A001 >>>A002 >>>A003 >>>A004 >>>A005 >>>A006 >>>A004 >>>A005 >>>A006 >>>A007 >>>A005 >>>A006 >>>A007 >>>A005 >>>A006 |
Sheet9_1のB列の値を取得して、処理を実行していきます。
プログラム5｜シート9_2の商品コードとマッチング
|
1 2 3 |
# プログラム5｜シート9_2の商品コードとマッチング for k in range(2, lastrow92 + 1): master_code = ws92['A' + str(k)].value |
プログラム解説
以下で一行ずつプログラムを解説します
|
2 |
for k in range(2, lastrow92 + 1): |
ここでは2行目からlastrow92まで処理を行います。
なおiとjは既に使用しているため、ここではkを使用します。
|
1 2 |
for k in range(2, lastrow92 + 1): print(k) |
実行結果
|
1 2 3 4 5 6 7 |
>>>2 >>>3 >>>4 >>>5 >>>6 >>>7 >>>8 |
「for k in range(2, lastrow92+1)」とすることで、「k= 2, 3,・・・,8(lastrow92)」まで繰り返し処理を実行させます。
|
3 |
master_code = ws92['A' + str(k)].value |
|
1 2 3 |
for k in range(2, lastrow92 + 1): master_code = ws92['A' + str(k)].value print(master_code) |
実行結果
|
1 2 3 4 5 6 7 |
>>>A001 >>>A002 >>>A003 >>>A004 >>>A005 >>>A006 >>>A007 |
変数master_codeに「Sheet9_2」のA列(取引先名称)の値を取得していきます。
プログラム6｜合致した行の値を取得
|
1 2 3 4 5 |
# プログラム6｜合致した行の値を取得 if product_code == master_code: product_name = ws92['B' + str(k)].value product_price = ws92['C' + str(k)].value break |
プログラム解説
以下で一行ずつプログラムを解説します
|
2 |
if product_code == master_code: |
「product_code(プログラム4)とmaster_code(プログラム5)が合致したら」というif文を作ります。
これにより「Sheet9_1のB列」と「Sheet9_2のA列」を比較マッチングすることができます。
|
3 |
product_name = ws92['B' + str(k)].value |
Sheet9_2のセルBk(k=2,3,・・・,lastrow92)をproduct_nameとして取得します。
このproduct_nameをSheet9_1に出力していきます。
|
4 |
product_price = ws92['C' + str(k)].value |
Sheet9_2のセルCk(k=2,3,・・・,lastrow92)をproduct_priceとして取得します
このproduct_priceをSheet9_1に出力していきます。
|
5 |
break |
breakでforの繰り返しループから抜け出します。
抜け出すループはインデントの位置によりますが、今回はプログラム5のforループの処理から抜け出します。
理由はマスタデータ(Sheet9_2)に記載されている製品コードは1つしか存在しないため、それを見つけた時点で他の製品コードを調べる必要はないからです。
breakを使うことで、余計な処理をしなくて済むので、プログラムの実行完了速度が速くなります。
プログラム7｜シート9_1に入力
|
1 2 3 4 |
# プログラム7｜シート9_1に入力 ws91['E' + str(i)].value = product_name ws91['F' + str(i)].value = product_price ws91['G' + str(i)].value = product_price * ws91['D' + str(i)].value |
プログラム解説
以下で一行ずつプログラムを解説します
|
2 |
ws91['E' + str(i)].value = product_name |
「Sheet9_1」のE列にproduct_name(プログラム6)を入力します。
product_nameは、Sheet9_2のB列の値です。
|
3 |
ws91['F' + str(i)].value = product_price |
「Sheet9_1」のF列にproduct_price(プログラム6)を入力します。
product_priceは、Sheet9_2のC列の値です。
|
4 |
ws91['G' + str(i)].value = product_price * ws91['D' + str(i)].value |
「Sheet9_1」のG列に売上を入力します。売上は単価×数量で計算します。
・数量：「Sheet9_1のD列」の数量
上記のデータを掛け算した値をG列に出力していきます。
プログラム8｜ファイル保存
|
1 2 |
# プログラム8｜ファイル保存 wb.save('Sample9.xlsx') |
「Sample.xlsx」を「Sample9.xlsx」としてエクセルを保存します。
エクセルファイルの保存先は「.py」ファイルと同じフォルダに保存されます。
vlookupを使いたいだけなら関数を入れた方がいい
上記のプログラムを使えば、pythonを使ってエクセルvlookup関数の動作を同じことを実現できます。
しかしながら、Excelでvlookupを使うだけであれば、わざわざpythonを使う必要はありません。
なぜなら関数式をそのまま入力するほうがラクだからです。
あえてpythonでやる必要がないのです。
Pythonのメリットは他のライブラリとの組み合わせにある
pythonを使うメリットは、他のライブラリと組み合わります。
これによりエクセル関数だけでは実現できない効率化を達成することにあります。
たとえばウェブスクレイピングをして、Excelに出力していくといった効率化をすることがpythonを使う魅力です。
エクセル関数で出来ることは、基本的にエクセルのなかで出来る作業の自動化です。
そのためpythonを使うのであれば、関数式にはとどまらず少し難しいことに挑戦していただければ嬉しいです。
実際に、以下でPythonの自動化プログラムを紹介しています。
どれも魅力的なプログラムなので、ぜひご覧いただきたいです。
Pythonについて詳しく理解したいなら
Pythonを活用すると、仕事を効率化できる幅を広げることができます。
たとえば私が実際にPythonを活用して効率化してきた作業は以下の記事で紹介しています。
興味がある人は以下の記事もご覧ください。
Python×効率化のサンプル
Pythonで効率化できる事例をサンプルコード付きで紹介しています。
Python×Excel
PythonとExcelで自動化できることを紹介しています。
事例も数多く紹介しているので、ぜひ参考にしてみてください。
Python×PDF
PythonとPDFで自動化できることを紹介しています。
Pythonって難しい？
Pythonの難易度などについては、以下で紹介しています。
勉強の参考になれば幸いです。
