Pythonでエクセルのデータ集計分析！Excel読み込み書き込みはOpenpyxlで

python パイソンエクセル分析 excel 読み込み書き込み openpyxl

Pythonを使うとエクセルのデータを読み込んだり、結果を書き込んだりできます。

そこで、Pythonを使ってExcelのデータを読み込み、そのデータをもとに計算結果を出力するプログラムを紹介します。

●この記事で分かること

・Pythonでエクセルのデータを読み込み、IF文で場合分けする

・Pythonで日付を比較する

・Pythonでエクセルの特定範囲に結果を出力する

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

ExcelのデータをPythonで処理するプログラムの概要
PythonでExcelの読み込み書き込みを行い結果を書き出すコード
Pythonプログラムをステップごとに解説
PythonでExcelデータを処理してみよう

ExcelのデータをPythonで処理するプログラムの概要

この記事では、Pythonを使ってエクセルの売上データをもとに表計算するプログラムを解説します。

実際に以下の作業をpythonで行うことを目指します。

python excel openpyxl

条件1｜表計算に使うデータは「データ」シート内のものを使う

条件2｜表計算の集計範囲は特定の期間にする

条件3｜表計算の結果は「集計」シートのセルA7～E26に出力する

PythonでExcelの読み込み書き込みを行い結果を書き出すコード

Pythonでエクセルのデータを集計し出力するコードは以下です。

#ステップ1｜ライブラリの設定
from openpyxl import load_workbook
import datetime

#ステップ2｜所定フォルダ内の「Book1.xlsm」を指定して読み込む
filepath = 'C:/Users/---/Book1.xlsm'

wb = load_workbook(filename=filepath)
ws1 = wb['データ']
ws2 = wb['集計']

#ステップ3｜集計範囲の取得
startdate=datetime.datetime(int(ws2['B2'].value) , int(ws2['C2'].value) , int(ws2['D2'].value))
enddate=datetime.datetime(int(ws2['B3'].value) , int(ws2['C3'].value) , int(ws2['D3'].value))

#ステップ4｜エクセルの最終行や最終列の取得
lastrow1=ws1.max_row
lastrow2=ws2.max_row
lastcol2=ws2.max_column

#ステップ5｜「データ」シートを読み込み、2次元配列values1として取得
values1=[[cell.value for cell in row1] for row1 in ws1]

#ステップ6｜条件に合う情報をFor文とIF文で場合分けして取得
for i in range(7, lastrow2+1):
    for j in range(2, lastcol2+1):
        counter = 0
        for k in range(1, lastrow1):
            if  values1[k][1] == ws2.cell(row=i, column=1).value:
                if values1[k][2] == ws2.cell(row=6, column=j).value:
                    torihikidate = values1[k][3]
                    if startdate <= torihikidate <= enddate:
                        kingaku=values1[k][4]
                        counter = counter + int(kingaku)

#ステップ7｜ステップ6に合わない場合に0を入れる
        if counter is None:
            counter=0

#ステップ8｜「集計」シートに結果を書き出す
        ws2.cell(row=i, column=j).value = counter

#ステップ9｜「Book2.xlsm」として所定のフォルダに保存する
newfilepath = 'C:/Users/---/Book2.xlsm'
wb.save(newfilepath)

以下でプログラムについて解説していきます。

Pythonプログラムをステップごとに解説

ステップ1｜ライブラリの設定
ステップ2｜所定フォルダ内の「Book1.xlsm」を指定して読み込む
ステップ3｜集計範囲の取得
ステップ4｜エクセルの最終行や最終列の取得
ステップ5｜「データ」シートを読み込み、データを取得
ステップ6｜条件に合う情報をFor文とIF文で場合分けして取得
ステップ7｜ステップ6に合わない場合に0を入れる
ステップ8｜「集計」シートに結果を書き出す
ステップ9｜「Book2.xlsm」として所定のフォルダに保存する

以下で一つずつ紹介していきます。

ステップ1｜ライブラリの設定

from openpyxl import load_workbook
import datetime

このプログラムでは、Excelの情報を読み込んだり書き込んだりするためにOpenpyxlライブラリを使用します。

また日付比較をするために、datetimeのライブラリも使います。

ライブラリの読み込みはこちら(外部サイト)の記事を読むと分かりやすいです。

PythonがインストールされているPCであれば、以下のコードをコマンドプロンプトで入力し、EnterをおせばOpenpyxlを使えるようになります。

pip install openpyxl

なおdatetimeはPythonに同梱されているので、設定は不要です。いきなりimport datetimeを記述して問題ありません。

ステップ2｜所定フォルダ内の「Book1.xlsm」を指定して読み込む

filepath = 'C:/Users/---/Book1.xlsm'

wb = load_workbook(filename=filepath)
ws1 = wb['データ']
ws2 = wb['集計']

以下でプログラムの説明をしていきます。

1. filepath = 'C:/Users/---/Book1.xlsm'

開きたいエクセルのファイルパスを記述します。

開きたいエクセルのファイルパスやファイル名に応じて、「C:/Users/---/Book1.xlsm」を書き換えてください。

ちなみに、ファイルパスは以下の手順で調べることができます。

Openpyxlを使うと「.xlsx」か「.xlsm」の拡張子のエクセルを処理することができます。

しかし、「.xls」は対応していないので注意が必要です。

3. wb = load_workbook(filename=filepath)
4. ws1 = wb['データ']
5. ws2 = wb['集計']

3行目｜wbに1行目で指定したファイルパスのエクセルの読み込みます。

4行目と5行目では、wb内の「データ」シートをws1、「集計」シートをws2とします。

ステップ3｜集計範囲の取得

startdate=datetime.datetime(int(ws2['B2'].value) , int(ws2['C2'].value) , int(ws2['D2'].value))
enddate=datetime.datetime(int(ws2['B3'].value) , int(ws2['C3'].value) , int(ws2['D3'].value))

1行目｜集計開始日をstartdateとして、「集計」シートのB2, C2, D2セルの値に相当する部分を取得する

2行目｜集計終了日をenddateとして、「集計」シートのB3, C3, D3セルの値に相当する部分を取得する

ステップ4｜エクセルの最終行や最終列の取得

lastrow1=ws1.max_row
lastrow2=ws2.max_row
lastcol2=ws2.max_column

1行目｜シート名「データ」の最終行をlastrow1として取得する

2行目｜シート名「集計」の最終行をlastrow2として取得

3行目｜シート名「集計」の最終列をlastcol2として取得

ステップ5｜「データ」シートを読み込み、2次元配列values1として取得

values1=[[cell.value for cell in row1] for row1 in ws1]

1行目｜「データ」シートの情報を読み込み、2次元配列values1として情報を取得する

ステップ6｜条件に合う情報をFor文とIF文で場合分けして取得

for i in range(7, lastrow2+1):
    for j in range(2, lastcol2+1):
        counter = 0
        for k in range(1, lastrow1):
            if  values1[k][1] == ws2.cell(row=i, column=1).value:
                if values1[k][2] == ws2.cell(row=6, column=j).value:
                    torihikidate = values1[k][3]
                    if startdate <= torihikidate <= enddate:
                        kingaku=values1[k][4]
                        counter = counter + int(kingaku)

1行目｜i を 7からlastrow2+1の範囲で、for文の繰り返しを使います。

2行目｜j を 2からlastcol2+1の範囲で、for文の繰り返しを使います。

3行目｜counterを0にします。

4行目｜k を 1からlastrow1の範囲でfor文の繰り返しを使います。

5行目｜「データ」シートのB列の値(取引先)と「集計」シートA列の値(取引先001~取引先020)が一致している場合をif文で比較します。

6行目｜「データ」シートのC列の値(担当者)と「集計」シート6行目の値(担当者A~担当者E)が一致している場合をif文で比較します。

7行目｜変数torihikidateに「データ」シートのD列の値(日付)を代入します。

8行目｜7行目のtorihikidateが集計期間（startdateからenddateまで）に入っているかをif文で比較します。

9行目｜集計期間に入っている場合、「データ」シートのE列の値(取引金額)をkingakuに代入します。

10行目｜counterにkingakuを加えて、累積して加算します

ステップ7｜ステップ6に合わない場合に0を入れる

        if counter is None:
            counter=0

1行目｜counterに何も情報がない場合をif文で見つける

2行目｜counterを0にする

ステップ8｜「集計」シートに結果を書き出す

        ws2.cell(row=i, column=j).value = counter

1行目｜「集計」シートの i行目、j列目にcounterを入れる

なお、この処理が終わるとステップ6の1行目に戻って繰り返し処理を行います

ステップ9｜「Book2.xlsm」として所定のフォルダに保存する

newfilepath = 'C:/Users/---/Book2.xlsm'
wb.save(newfilepath)

1行目｜newfilepathに「C:/Users/---/Book2.xlsm」を入れる

保存したいファイルパスやファイル名に応じて、「C:/Users/---/Book2.xlsm」を書き換えてください。

2行目｜newfilepathで指定したパスにエクセルワークブックを保存します。

プログラム終了後に、Book2.xlsmを開くと以下のとおりになっています。

PythonでExcelデータを処理してみよう

Pythonを使ってエクセルの読み込みや書き込みを行う方法を紹介しました。

ぜひチャレンジしてみてください。

またPythonを使ってGoogleスプレッドシートのデータを読み込んだり書き込んだりするプログラムも紹介しています。

この記事で紹介している内容とほぼ同様のデータサンプルを使用して解説しています。

ぜひ合わせて読んでみてください。