Pythonを使うとエクセル関数を作成することができます。
ここではPython×Excelの入門事例として、2条件を満たす場合に累算する「sumifs関数」を作っていきます。
それでは以下で詳しく紹介していきます。
目次
Pythonでエクセルsumifs関数(2条件マッチング)を作ってみる
今回は以下の作業をpythonで行います。
1. B2~B26, C2~C26の2条件でマッチング
2. 取引金額の合計をG2~I12に出力
上記はExcel関数「=sumifs()」を使えば、実現可能です。
しかし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 35 36 37 38 39 |
# sumifs関数 # プログラム1｜ライブラリ設定 import openpyxl as px # プログラム2｜対象のエクセルシートを読み込む filepath = 'Sample.xlsx' wb = px.load_workbook(filename=filepath) ws6 = wb['Sheet6'] # プログラム3｜集計範囲の取得 lastrow = ws6.max_row # プログラム4｜F列の値を取得 for i in range(2, lastrow + 1): table_tate = ws6['F' + str(i)].value if not table_tate is None: # プログラム5｜G1,H1,I1の値を取得 for j in range(3): table_yoko = ws6['G1'].offset(0, j).value # プログラム6｜ゼロリセット goukei = 0 # プログラム7｜B列,C列,D列の値を取得 for k in range(2, lastrow + 1): torihiki = ws6['B' + str(k)].value tanto = ws6['C' + str(k)].value kingaku = ws6['D' + str(k)].value # プログラム8｜2条件でマッチング if table_tate == torihiki and table_yoko == tanto: goukei += kingaku # プログラム9｜G列~I列に件数を出力 ws6['G' + str(i)].offset(0, j).value = goukei # プログラム10｜ファイル保存 wb.save('Sample6.xlsx') |
以下で詳しく説明しています。
プログラム1｜ライブラリ設定
|
1 2 |
# プログラム1｜ライブラリ設定 import openpyxl as px |
openpyxlはExcel操作で使います。
「openpyxl as px」とすることで、openpyxlをpxという変数で扱うようにしています。
プログラム2｜対象のエクセルシートを読み込む
|
1 2 3 4 |
# プログラム2｜対象のエクセルシートを読み込む filepath = 'Sample.xlsx' wb = px.load_workbook(filename=filepath) ws6 = wb['Sheet6'] |
以下で一行ずつプログラムを解説します
プログラム解説
|
2 |
filepath = 'Sample.xlsx' |
「.py」ファイルと同じフォルダ内の「Sample.xlsx」を変数filepathとします。
|
3 |
wb = px.load_workbook(filename=filepath) |
filepath(‘Sample.xlsx’)で指定したExcelファイルを「wb」として読み込みます。
所定フォルダに「Sample.xlsx」が存在しない場合、エラーとなりますので注意が必要です。
|
4 |
ws6 = wb['Sheet6'] |
読み込んだExcelファイルの「Sheet6」をws6して扱います。
なお読み込んだExcelに名前が「Sheet6」のシートが存在しない場合、エラーとなりますので注意が必要です。
シート名にも気を付けましょう。
プログラム3｜最終行の取得
|
1 2 |
# プログラム3｜最終行の取得 lastrow = ws6.max_row |
「シート.max_row」で最終行を取得することができます。
この事例では、ws6(Sheet6)の最終行は26です。
|
1 2 |
lastrow = ws6.max_row print(lastrow) |
実行結果
|
1 |
>>>26 |
ここで取得した最終行を使って処理をしていきます。
プログラム4｜F列の値を取得
|
1 2 3 4 |
# プログラム4｜F列の値を取得 for i in range(2, lastrow + 1): table_tate = ws6['F' + str(i)].value if not table_tate is None: |
プログラム解説
以下で一行ずつプログラムを解説します
|
2 |
for i in range(2, lastrow + 1): |
2行目から最終行まで処理を行わせます。（最終行はデータによって、50行になったり100行になったり可変することを想定）
ここでは最終行は26なので、2~26まで処理を行います。
したがって、「for i in range(2, lastrow+1)」と記載し、「i= 2, 3,・・・,lastrow」まで繰り返し処理を実行させます。
|
1 2 |
for i in range(2, lastrow + 1): print(i) |
実行結果
|
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 |
>>>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 |
なお「for i in range(lastrow+1)」とすると「i = 0,1,2,・・・,lastrow」と繰り返しを処理を実行させることができます。
|
3 |
table_tate = ws6['F' + str(i)].value |
変数table_tateにセルFi(i=2,3,・・・,lastrow)の値を取得していきます。
|
1 2 3 |
for i in range(2, lastrow + 1): table_tate = ws6['F' + str(i)].value print(table_tate) |
実行結果
|
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 |
>>>愛知販売 >>>岐阜商会 >>>岐阜商船 >>>愛媛不動産 >>>横浜航空 >>>岡山百貨店 >>>宮崎商船 >>>岡山研究所 >>>沖縄紡績 >>>宇部工業 >>>茨城信託 >>>None >>>None >>>None >>>None >>>None >>>None >>>None >>>None >>>None >>>None >>>None >>>None >>>None >>>None |
補足ですが、セルF13～セルF26は空欄のため、「None」となります。
「Noneの場合は処理しない」ようにプログラム5で対応を行います。
|
4 |
if not table_tate is None: |
「if not table_tate is None」というのは、変数table_tateが「None」ではない場合の処理を指しています。
というのも、F13~F26は空欄であり処理をする意味がありません。
そこで「if not table_tate is None」を使って空欄判定を行います。
「if not セルの値 is None」で、空欄セルをスキップ処理できますので、覚えておくと便利です。
プログラム5｜G1,H1,I1の値を取得
|
1 2 3 |
# プログラム5｜G1,H1,I1の値を取得 for j in range(3): table_yoko = ws6['G1'].offset(0, j).value |
プログラム解説
以下で一行ずつプログラムを解説します
|
2 |
for j in range(3): |
「for j in range(3)」とすることで、「j= 0, 1, 2」の3つの数を順々に処理を行います。
ここは「i」ではなく「j」としています。
なおiはプログラム4で使っているため、再びiを使うと、正しく処理が実行されなくなります。
for文を作成するときは、注意が必要です。
|
3 |
table_yoko = ws6['G1'].offset(0, j).value |
このプログラムで注目すべきは、「offset(0,j)」です。
ws4[‘G1’].offset(0,j)とすることで、対象セルをG1から移動させて取得することができます。
・j=0のとき、セルG1
・j=1のとき、セルH1
・j=2のとき、セルI1
これにより、for文とoffsetで1列ずつずらしてセルの値を取得できます。
|
1 2 3 |
for j in range(3): table_yoko = ws6['G1'].offset(0, j).value print(j, table_yoko) |
実行結果
|
1 2 3 |
>>>0 A >>>1 B >>>2 C |
補足：offsetの使い方
|
1 |
ws1['B2'].offset(0,0).value #B2の値を取得 |
|
1 |
ws1['B2'].offset(1,0).value #B3の値を取得 |
|
1 |
ws1['B2'].offset(0,1).value #C2の値を取得 |
|
1 |
ws1['B2'].offset(-1,0).value #B1の値を取得 |
|
1 |
ws1['B2'].offset(0,-1).value #A2の値を取得 |
エクセルを扱うとき、offsetを知っていると役立つので覚えておくと便利です。
プログラム6｜ゼロリセット
|
1 2 |
# プログラム6｜ゼロリセット goukei = 0 |
変数goukeiをゼロリセットします。
goukeiはG列~I列に取引金額の合計を計算するための変数です。
ここでゼロリセットすると、正しい合計値を計算できます。
逆にゼロリセットのタイミングを間違えると、合計値がズレてしまうので注意が必要です。
プログラム7｜B列,C列,D列の値を取得
|
1 2 3 4 5 |
# プログラム7｜B列,C列,D列の値を取得 for k in range(2, lastrow + 1): torihiki = ws6['B' + str(k)].value tanto = ws6['C' + str(k)].value kingaku = ws6['D' + str(k)].value |
プログラム解説
以下で一行ずつプログラムを解説します
|
2 |
for k in range(2, lastrow + 1): |
2行目から最終行(26行目)まで処理を行います。
「for k in range(2, lastrow+1)」と記載し、「k= 2, 3,・・・,lastrow」まで繰り返し処理を実行させます。
なおiとjは既に使用しているため、ここではkを使用します。
|
3 |
torihiki = ws6['B' + str(k)].value |
変数torihikiにB列(取引先名称)の値を取得していきます。
セルBk(k=2,3,・・・,lastrow)として、一つずつ値を取得します。
|
4 |
tanto = ws6['C' + str(k)].value |
変数tantoにC列(担当者)の値を取得していきます。
セルCk(k=2,3,・・・,lastrow)として、一つずつ値を取得します。
|
4 |
tanto = ws6['C' + str(k)].value |
変数kingakuにD列(取引金額)の値を取得していきます。
セルDk(k=2,3,・・・,lastrow)として、一つずつ値を取得します。
プログラム8｜2条件でマッチング
|
1 2 3 |
# プログラム8｜2条件でマッチング if table_tate == torihiki and table_yoko == tanto: goukei += kingaku |
プログラム解説
以下で一行ずつプログラムを解説します
|
2 |
if table_tate == torihiki and table_yoko == tanto: |
「table_tateとtorihiki」かつ「table_yokoとtanto」の2つが合致する行をif文でマッチングしていきます。
「if A==B and C==D」とすることで、2条件の両方に合致する場合のみを取得することができます。
この2条件を満たすものがあれば、次のプログラムで加算をしていきます。
|
3 |
kensu += 1 |
goukeiにkingaku(D列の取引金額：プログラム7)を加算します。
なお「goukei=goukei+kingaku」と表記しても同じように加算することができます。
kensu(加算結果)はプログラム9でG列~I列に出力します。
プログラム9｜G列~I列に件数を出力
|
1 2 |
# プログラム9｜G列~I列に件数を出力 ws6['G' + str(i)].offset(0, j).value = goukei |
G列~I列にgoukeiの値を出力します。
件数を出力したらプログラム6に戻り、繰り返し処理を継続します。
なおgoukeiの値はゼロリセット（プログラム6）されます。
プログラム10｜ファイル保存
|
1 2 |
# プログラム10｜ファイル保存 wb.save('Sample6.xlsx') |
「Sample.xlsx」を「Sample6.xlsx」としてエクセルを保存します。
エクセルファイルの保存先は「.py」ファイルと同じフォルダに保存されます。
関数式sumifsを入力しても可能だが、オススメしない
エクセルでsumifs関数を使って計算したいだけなら、sumifs関数を使うことはオススメしません。
なぜなら関数式をそのまま入力するなら、pythonでやる必要がないからです。
単純にエクセルにsumifs関数を入れて計算させたほうがカンタンです。
単なる計算だけならば、わざわざpythonで計算させるメリットがないのです。
Pythonのメリットは他のライブラリとの組み合わせにある
pythonを使うメリットは、他のライブラリと組み合わります。
これによりエクセル関数だけでは実現できない効率化を達成することにあります。
たとえばウェブスクレイピングをして、Excelに出力していくといった効率化をすることがpythonを使う魅力です。
エクセル関数で出来ることはせいぜいエクセルに限定される作業の効率化です。
しかし関数式を入力することで対応していたら、pythonを使うための基礎的な理解が進みません。
pythonを使うなら、関数式にはとどまらず少し難しいことに挑戦していただければ嬉しいです。
実際に、以下でPythonの自動化プログラムを紹介しています。
どれも魅力的なプログラムなので、ぜひご覧いただきたいです。
Pythonについて詳しく理解したいなら
Pythonを活用すると、仕事を効率化できる幅を広げることができます。
たとえば私が実際にPythonを活用して効率化してきた作業は以下の記事で紹介しています。
興味がある人は以下の記事もご覧ください。
Python×効率化のサンプル
Pythonで効率化できる事例をサンプルコード付きで紹介しています。
Python×Excel
PythonとExcelで自動化できることを紹介しています。
事例も数多く紹介しているので、ぜひ参考にしてみてください。
Python×PDF
PythonとPDFで自動化できることを紹介しています。
Pythonって難しい？
Pythonの難易度などについては、以下で紹介しています。
勉強の参考になれば幸いです。
