Python×Excel｜比較マッチングの事例(COUNTIF関数をopenpyxlで作る)

Pythonを使うとエクセル関数を作成することができます。

ここではPython×Excelの入門事例として、条件に合うセルの数をカウントする「countif関数」を作っていきます。

それでは以下で詳しく紹介していきます。

PythonでエクセルcountIf関数を作ってみる

今回は以下の作業をpythonで行います。

B列(B2~B21)とF列(F2~F21)の値のうち、一致する件数をカウントする。

そのカウント数(件数)をG列(G2~G21)に出力する。

これはExcel関数でいえば、「=countif(B2:B21,F2)」などをG列に入力すれば、件数を出力することができます。

しかしopenpyxlというpythonライブラリを使ったら、どのようにプログラムを作ればいいのでしょうか。

本記事では、そのプログラム作成について詳しく紹介していきます。

Pythonプログラムの実行前準備｜Excelファイル保管とライブラリインストール

準備1｜Excelファイルをフォルダに保存(「.py」と同じフォルダ)

対象となるExcelファイルをフォルダに保存します。

本事例では「Sample.xlsx」というファイル名にしています。

後半で紹介するプログラムをそのまま使用する場合は、「.py」と「.xlsx」が同じフォルダでないとエラーが発生します。

準備2｜必要なライブラリをインストール

今回は以下の2つのライブラリをインストールします。

Excelを操作するために使用するライブラリです。

上記をインストールしておかないと動かないので、注意が必要です。

Pythonプログラム解説

この記事で紹介するプログラムを解説しています。

以下で詳しく説明しています。

プログラム1｜ライブラリ設定

openpyxlはExcel操作で使います。

「openpyxl as px」とすることで、openpyxlをpxという変数で扱うようにしています。

プログラム2｜対象のエクセルシートを読み込む

以下で一行ずつプログラムを解説します

プログラム解説

「.py」ファイルと同じフォルダ内の「Sample.xlsx」を変数filepathとします。

filepath(‘Sample.xlsx’)で指定したExcelファイルを読み込みます。

所定フォルダに「Sample.xlsx」が存在しない場合、エラーとなりますので注意が必要です。

読み込んだExcelファイルの「Sheet3」をws3として扱います。

なお読み込んだExcelに名前が「Sheet3」のシートが存在しない場合、エラーとなりますので注意が必要です。

シート名にも気を付けましょう。

プログラム3｜最終行の取得

「シート.max_row」で最終行を取得することができます。

この事例では、ws3(Sheet3)の最終行は21です。

ここで取得した最終行までC列の数値を計算していきます。

プログラム4｜F列の値を取得

プログラム解説

以下で一行ずつプログラムを解説します

「for i in range(2, lastrow+1)」で「i= 2, 3,・・・,lastrow」まで繰り返し処理を行います。

なお「for i in range(lastrow+1)」とすると「i = 0,1,2,・・・,lastrow」と繰り返しを処理を実行させることができます。

変数kensuをゼロリセットします。

kensuはG列に入力する件数をカウントするための変数です。

実際のkensuのカウントはプログラム5で行います。

変数kokyakuをセルFi(i=2,3,・・・,lastrow)の値とします。

補足ですが、セルF12～セルF21は空欄のため、「None」となります。

Noneのセルは、確認してもムダです。

したがって、「Noneの場合は処理しない」ようプログラム5で対応します。

プログラム5｜B列の値とF列の値をマッチング

プログラム解説

以下で一行ずつプログラムを解説します

「if not kokyaku is None」というのは、変数kokyaku(プログラム4)が「None」ではない場合の処理を指しています。

前述のとおり、F列は12行目～21行目は空欄のため、処理する必要がありません。

そこで、「空欄ではないときに処理を行う」というプログラムを組みます。

「if not セルの値 is None」で空欄をスキップして処理させることができますので、覚えておくと便利です。

「for j in range(2, lastrow+1)」で「j= 2, 3,・・・,lastrow」まで繰り返し処理を行います。

ここは「i」ではなく「j」としています。

iはプログラム4で使っているため、再びiを使うと、正しく処理が実行されなくなります。

for文をコピペするときは、注意が必要です。

変数torihikiにセルBj(j=2,3,・・・,lastrow)の値を入れていきます。

これでkokyaku(F列)とtorihiki(B列)の値を取得できたので、マッチングをしていきます。

kokyaku(F列)とtorihiki(B列)を総当たりでマッチングしていきます。

上記の順番で実施していきます。

kokyaku(F列)とtorihiki(B列)が一致していれば、kensuに1を加算していきます。

「kensu=kensu+1」と表記しても同じように加算することができます。

kensu(加算結果)はプログラム6でG列に出力します。

プログラム6｜F列に件数を出力

G列にkensuの値を出力します。

プログラム6が終了(G列に値を出力)したらプログラム4に戻り、F列の次の値を処理していきます。

なおkensuの値はゼロリセット（プログラム4）されます。

プログラム7｜ファイル保存

「Sample.xlsx」を「Sample3.xlsx」としてエクセルを保存します。

エクセルファイルの保存先は「.py」ファイルと同じフォルダに保存されます。

Pythonプログラム解説（Countif関数をそのまま入力する方法）

Pythonのopenpyxlを使ったcountif関数の表現方法は別にもあります。

ここでは「=countif()」を直接入力する方法も紹介します。

プログラム1、プログラム2、プログラム3、プログラム5は上記で説明済みなので、省略します。

プログラム4についてのみ詳細を紹介していきます。

プログラム4｜関数式をセルに入力

プログラム解説

以下で一行ずつプログラムを解説します

2行目から4行目までは説明済みのため、省略します。

プログラム解説は5行目と6行目に限定します。

「countif_kansu」を「’=countif(B2:B’ + str(lastrow) + ‘,F’ + str(i) +’)’」とします。

要は「=countif(B2:B21,F2)」のような関数式を取得するのです。

以下でプログラムを実行してみます。

次のプログラムで、この関数式をG2~G21まで入力していきます。

このように関数をそのままExcelのセルに入れ込むことで、countifを実現することが可能です。

関数式を入力するのはオススメしない理由

関数式を入力すれば、上記のようにcountif関数をエクセルに入力することは可能です。

しかしながら関数式をそのまま入力する方法はオススメしません。

なぜなら関数式をそのまま入力するなら、pythonでやる必要がないからです。

pythonを使うメリットは、他のライブラリと組み合わせて、関数では出来ないようなことを実現することにあります。

たとえばウェブスクレイピングをして、Excelに出力していくといった効率化をすることがpythonを使う魅力です。

しかし関数式を入力することで対応していたら、pythonを使うための基礎的な理解が進みません。

pythonを使うなら、関数式にはとどまらず少し難しいことに挑戦していただければ嬉しいです。

実際に、以下でPythonの自動化プログラムを紹介しています。

どれも魅力的なプログラムなので、ぜひご覧いただきたいです。

Pythonについて詳しく理解したいなら

Pythonを活用すると、仕事を効率化できる幅を広げることができます。

たとえば私が実際にPythonを活用して効率化してきた作業は以下の記事で紹介しています。

興味がある人は以下の記事もご覧ください。

Python×効率化のサンプル

Pythonで効率化できる事例をサンプルコード付きで紹介しています。

Python×Excel

PythonとExcelで自動化できることを紹介しています。

事例も数多く紹介しているので、ぜひ参考にしてみてください。

Pythonって難しい？

Pythonの難易度などについては、以下で紹介しています。

勉強の参考になれば幸いです。