Pythonを使うとエクセル関数を作成することができます。
ここではPython×Excelの入門事例として、2条件に合致する件数をカウントする「countifs関数」を作っていきます。
・条件に合う件数を出力する
それでは以下で詳しく紹介していきます。
目次
Pythonでエクセルcountifs関数(2条件マッチング)を作ってみる
今回は以下の作業をpythonで行います。
B2~B26, C2~C26の2条件に合致する件数をG2~I12に出力
B列(B2~B26)とC列(C2~C26)の2条件に合致する件数をカウントする。
その結果をG2~I12に一覧で出力する。
これはExcel関数でいえば、「=countifs()」を使えば、実現可能です。
しかし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 |
# プログラム1｜ライブラリ設定 |
以下で詳しく説明しています。
プログラム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) ws4 = wb['Sheet4'] |
以下で一行ずつプログラムを解説します
プログラム解説
|
2 |
filepath = 'Sample.xlsx' |
「.py」ファイルと同じフォルダ内の「Sample.xlsx」を変数filepathとします。
|
3 |
wb = px.load_workbook(filename=filepath) |
filepath(‘Sample.xlsx’)で指定したExcelファイルを読み込みます。
所定フォルダに「Sample.xlsx」が存在しない場合、エラーとなりますので注意が必要です。
|
4 |
ws4 = wb['Sheet4'] |
読み込んだExcelファイルの「Sheet4」をws4として扱います。
なお読み込んだExcelに名前が「Sheet4」のシートが存在しない場合、エラーとなりますので注意が必要です。
シート名にも気を付けましょう。
プログラム3｜最終行の取得
|
1 2 |
# プログラム3｜最終行の取得 lastrow = ws4.max_row |
「シート.max_row」で最終行を取得することができます。
この事例では、ws4(Sheet4)の最終行は26です。
|
1 2 |
lastrow = ws4.max_row print(lastrow) |
実行結果
|
1 |
>>>26 |
ここで取得した最終行を使って処理をしていきます。
プログラム4｜F列の値を取得
|
1 2 3 |
# プログラム4｜F列の値を取得 for i in range(2, lastrow + 1): table_tate = ws4['F' + str(i)].value |
プログラム解説
以下で一行ずつプログラムを解説します
|
2 |
for i in range(2, lastrow + 1): |
ここでは2行目から最終行まで処理を想定しています。（最終行はデータによって、50行になったり100行になったり可変することを想定）
したがって、「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 = ws4['F' + str(i)].value |
変数kokyakuをセルFi(i=2,3,・・・,lastrow)の値とします。
|
1 2 3 |
for i in range(2, lastrow + 1): table_tate = ws4['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で対応を行います。
プログラム5｜G1,H1,I1の値を取得
|
1 2 3 4 |
# プログラム5｜G1,H1,I1の値を取得 if not table_tate is None: for j in range(3): table_yoko = ws4['G1'].offset(0, j).value |
プログラム解説
以下で一行ずつプログラムを解説します
|
2 |
if not table_tate is None: |
「if not table_tate is None」というのは、変数table_tate(プログラム4)が「None」ではない場合の処理を指しています。
前述のとおり、F列は13行目～26行目は空欄のため、処理する必要がありません。
そこで、「空欄ではないときに処理を行う」というプログラムを組みます。
「if not セルの値 is None」で、空欄セルをスキップ処理できますので、覚えておくと便利です。
|
3 |
for j in range(3): |
「for j in range(3)」とすることで、「j= 0, 1, 2」の3つの数を順々に処理を行います。
ここは「i」ではなく「j」としています。
なおiはプログラム4で使っているため、再びiを使うと、正しく処理が実行されなくなります。
for文をコピペするときは、注意が必要です。
|
4 |
table_yoko = ws4['G1'].offset(0, j).value |
このプログラムで注目すべきは、「offset(0,j)」です。
j=0のときセルG1、j=1のときセルH1、j=2のときセルI1と対象セルを移動させることができます。
これにより、for文とoffsetで1列ずつずらしてセルの値を取得できます。
|
1 2 3 |
for j in range(3): table_yoko = ws4['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｜ゼロリセット kensu = 0 |
変数kensuをゼロリセットします。
kensuはG列~I列に件数を入力するためのカウンターです。
ここでゼロリセットすると、正しい数値をカウントできます。
逆にゼロリセットのタイミングを間違えると、件数が重複して正しい件数にならなくなるので、注意が必要です。
プログラム7｜B列,C列の値を取得
|
1 2 3 4 |
# プログラム7｜B列,C列の値を取得 for k in range(2, lastrow + 1): torihiki = ws4['B' + str(k)].value tanto = ws4['C' + 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 = ws4['B' + str(k)].value |
変数torihikiにB列(取引先名称)の値を取得していきます。
セルBk(k=2,3,・・・,lastrow)として、一つずつ値を取得します。
|
4 |
tanto = ws4['C' + str(k)].value |
変数tantoにC列(担当者)の値を取得していきます。
セルCk(k=2,3,・・・,lastrow)として、一つずつ値を取得します。
プログラム8｜B列,C列の値を取得
|
1 2 3 |
# プログラム8｜2条件でマッチング if table_tate == torihiki and table_yoko == tanto: kensu += 1 |
プログラム解説
以下で一行ずつプログラムを解説します
|
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 |
kensuに1を加算します。
なお「kensu=kensu+1」と表記しても同じように加算することができます。
kensu(加算結果)はプログラム9でG列~I列に出力します。
プログラム9｜G列~I列に件数を出力
|
1 2 |
# プログラム9｜G列~I列に件数を出力 ws4['G' + str(i)].offset(0, j).value = kensu |
G列~I列にkensuの値を出力します。
件数を出力したらプログラム6に戻り、繰り返し処理を継続します。
なおkensuの値はゼロリセット（プログラム6）されます。
プログラム10｜ファイル保存
|
1 2 |
# プログラム10｜ファイル保存 wb.save('Sample4.xlsx') |
「Sample.xlsx」を「Sample4.xlsx」としてエクセルを保存します。
エクセルファイルの保存先は「.py」ファイルと同じフォルダに保存されます。
関数式countifsを入力しても可能だが、オススメしない
openpyxlの場合、エクセルで関数式を使いたい場合、countifs関数を文字列として入力されて使用することが可能です。
たとえば上記のようにセルG2に以下の式を入れれば、countifs関数によって2条件以上に合致する件数を出力することが可能です。
しかしながら関数式をそのまま入力する方法はオススメしません。
なぜなら関数式をそのまま入力するなら、pythonでやる必要がないからです。
Pythonのメリットは他のライブラリとの組み合わせにある
pythonを使うメリットは、他のライブラリと組み合わります。
これによりエクセル関数だけでは実現できない効率化を達成することにあります。
たとえばウェブスクレイピングをして、Excelに出力していくといった効率化をすることがpythonを使う魅力です。
エクセル関数で出来ることはせいぜいエクセルに限定される作業の効率化です。
しかし関数式を入力することで対応していたら、pythonを使うための基礎的な理解が進みません。
pythonを使うなら、関数式にはとどまらず少し難しいことに挑戦していただければ嬉しいです。
実際に、以下でPythonの自動化プログラムを紹介しています。
どれも魅力的なプログラムなので、ぜひご覧いただきたいです。
Pythonについて詳しく理解したいなら
Pythonを活用すると、仕事を効率化できる幅を広げることができます。
たとえば私が実際にPythonを活用して効率化してきた作業は以下の記事で紹介しています。
興味がある人は以下の記事もご覧ください。
Python×効率化のサンプル
Pythonで効率化できる事例をサンプルコード付きで紹介しています。
Python×Excel
PythonとExcelで自動化できることを紹介しています。
事例も数多く紹介しているので、ぜひ参考にしてみてください。
Pythonって難しい？
Pythonの難易度などについては、以下で紹介しています。
勉強の参考になれば幸いです。
