Excel Python プログラム

Pythonを使用してExcelのデータを昇順・降順する方法

2021/01/31

Pythonを使用してExcelのデータの昇順・降順を行う方法について紹介。

Excel内のデータが多くなるとソートに時間がかかったりする。
また、複数のExcelファイルをソートするとなるとさらに時間がかかるため、Pythonで自動化してしまえば、その時間は優雅にティータイムにでもあてておきたい。

pythonでExcelのデータを昇順・降順

今回は2つの方法で紹介しようと思う。

  1. win32comを使用した昇順・降順
  2. openpyxl & pandasを使用した昇順・降順

どちらの方法でも同じような結果を出すことが出来る。個人的な使い分けとしては、Excelファイルのみで完結するのか？どうかで使い分けをしている。

【win32com】

  • officeでの連携を考えている場合はコチラがオススメ。例えば、Excelのファイルを作成後にOutlookでメールを送る・Excelで集計した結果をWordに張り付けるなど。

【openpyxl & pandas】

  • Excelのデータの集計や自動化のみをしたい場合はこちらがオススメ。単純なデータ分析の自動化などExcelのみで完結して、その他Officeとの連携は考えていない

使用データ

今回は日本の祝日(syukujitsu.xlsx)のデータを使用。
内閣府が出している「国民の祝日」についてのデータをExcelにして使用しております。

win32comを使用したExcelの昇順・降順

最初はwin32comというライブラリを使用した場合の昇順・降順を紹介していきます。

詳しいことは後程説明していきますが、下のコードで実施した場合、ExcelのデータをB列の昇順・A列の降順の順にソートされて保存されます。


import win32com.client

file_path = r"C:\Users\user\Desktop\syukujitsu.xlsx"


#Excelファイル操作のための準備
excel = win32com.client.Dispatch("Excel.Application")
wb = excel.Workbooks.Open(Filename=file_path)
    
#Sort 数値の割り振り
#http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_sort.html
xlAscending = 1
xlDescendig = 2
xlYes = 1
    
#B列で昇順
wb.Sheets(1).Columns("A:B").Sort(Key1=wb.Sheets(1).Range('B2'), Order1=xlAscending, Header=xlYes)
#A列で降順
wb.Sheets(1).Columns("A:B").Sort(Key1=wb.Sheets(1).Range('A2'), Order1=xlDescendig, Header=xlYes)
    
#Excelファイルを保存
wb.Save()
    
#Excelを閉じる
excel.Quit()

結果は、このようにA列の降順で表示されています。

もう少し詳しく説明していきます。

Excelファイル操作のための準備

まず、Excelのファイルを操作できるように準備していきます。

  1. Excelの準備＆ファイルを開く
  2. Excelのファイルを保存
  3. Excelを閉じる

これは、pythonでExcelを操作する際にほとんど使うような機能になります。これにプラスして操作をすることになります。

エクセルのファイルを開いたり、保存する方法については下記リンクを参考にしてください。

Pythonを使用してExcelのファイルを開いたり保存する方法


import win32com.client

file_path = r"C:\Users\user\Desktop\syukujitsu.xlsx"

def excelSort(file_path):

    #Excelファイル操作のための準備
    excel = win32com.client.Dispatch("Excel.Application")
    wb = excel.Workbooks.Open(Filename=file_path)
    
    #Excelファイルを保存
    wb.Save()
    
    #Excelを閉じる
    excel.Quit()
    
    return

excelSort(file_path)

Sort数値割り振り

プログラム内にありますが、xlAscending(昇順)やxlDescendig(降順)・xlYes(ヘッダー有)などの数値について説明していきます。
これはVBAでも使用される数値になります。

pythonの場合は、変数でxlAscending(昇順)などを使うのですが、これは数値で割り振られています。
下記リンクを確認してもらうと詳細にSortしたいときに使用する数値について記載があります。

データの並べ替え(Sort):Excel VBA入門


    #Sort 数値の割り振り
    #http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_sort.html
    xlAscending = 1
    xlDescendig = 2
    xlYes = 1

こちらは変数を定義しておかないとpythonでエラーが発生してしまうため、必ず定義しておきましょう。

昇順・降順

最後に昇順・降順について話していきます。


    #B列で昇順
    wb.Sheets(1).Columns("A:B").Sort(Key1=wb.Sheets(1).Range('B2'), Order1=xlAscending, Header=xlYes)
    #A列で降順
    wb.Sheets(1).Columns("A:B").Sort(Key1=wb.Sheets(1).Range('A2'), Order1=xlDescendig, Header=xlYes)

まず、ソートしたいデータ範囲の指定。
現在syukujitsuファイルの1番目のsheetの2列(A:B)を指定している。

2列以上ある場合はその範囲を指定。(例："A:Z")

wb.Sheets(1).Columns("A:B")

B列を指定してヘッダー有の昇順でソートを行う。
この際に注意していただきたいのが、先頭行(ヘッダー)にタイトルがあるときは、必ず次の行(下記の場合は'B2')を指定すること。でないとタイトル行もソートされてしまいます。

.Sort(Key1=wb.Sheets(1).Range('B2'), Order1=xlAscending, Header=xlYes)("A:B")

複数のファイルのソート方法

同じようなファイルでソートの操作が同じ場合は、下記のプログラムを作成して使用するだけで複数のファイルをソートして保存することができる。

import win32com.client

files_path =  [
    r"C:\Users\user\Desktop\sample\syukujitsu.xlsx"
    ,r"C:\Users\user\Desktop\sample\syukujitsu_v2.xlsx"
              
    ]


def excelSort(file_path):

    #Excelオブジェクト生成とファイルを開く
    excel = win32com.client.Dispatch("Excel.Application")
    wb = excel.Workbooks.Open(Filename=file_path)
    
    #Sort Numberの割り振り
    xlAscending = 1
    xlDescendig = 2
    xlYes = 1
    
    #B列で昇順
    wb.Sheets(1).Columns("A:B").Sort(Key1=wb.Sheets(1).Range('B2'), Order1=xlAscending, Header=xlYes)
    #A列で降順
    wb.Sheets(1).Columns("A:B").Sort(Key1=wb.Sheets(1).Range('A2'), Order1=xlDescendig, Header=xlYes)
    
    #Excelファイルを保存
    wb.Save()
    
    #Excelを閉じる
    excel.Quit()
    
    return

for file_path in files_path:
    excelSort(file_path)

上記の方法で、複数の同じようなファイル更新ができます。

openpyxl & pandasを使用したExcelの昇順・降順

2つ目は、openpyxl & pandasを使用した昇順・降順の方法を紹介していきます。

  1. pandasでopenpyxlを使用してExcelデータの読み込み
  2. pandasでデータの昇順・降順
  3. Excelに書き込む

こちらの方法は、正確には昇順・降順をpandasで行い、その結果をExcelに書き込んでいるので、先程のwin32comとのやり方と少し違います。

import pandas as pd

file_name = r"C:\Users\user\Desktop\syukujitsu.xlsx"

#DataFrameにExcelの読み込み
df = pd.read_excel(file_name, sheet_name='syukujitsu', engine='openpyxl')

#A列の国民の祝日・休日月日を基準にして降順(False)・昇順（True）ができる
df_a = df.sort_values('国民の祝日・休日月日', ascending=False)

#B列の国民の祝日・休日名称を基準にして降順(False)・昇順（True）ができる
df_b = df.sort_values('国民の祝日・休日名称', ascending=False)

#ExcelWriterを追加することで、SaveとCloseの必要がなくなる。またengineをopenpyxlに指定ができる
with pd.ExcelWriter(file_name, engine='openpyxl', mode='a') as writer:
    
    #A列の国民の祝日・休日月日を基準にして降順(False)にしたデータをresultシートに追加
    df_a.to_excel(writer, sheet_name='result', index=False)
    
    #B列の国民の祝日・休日名称を基準にして降順(False)にしたデータをresult2シートに追加
    df_b.to_excel(writer, sheet_name='result2', index=False)

上記では少し説明不足なので、説明していきます。

pandasでExcelデータの読み込み

最初でも少し話しましたが、pandasでExcelのデータを読み込ます。
その後に、pandasのソートを使用して昇順・降順をしております。

その部分は下記の部分ですね。

import pandas as pd

file_name = r"C:\Users\user\Desktop\syukujitsu.xlsx"

#DataFrameにExcelの読み込み
df = pd.read_excel(file_name, sheet_name='syukujitsu', engine='openpyxl')

#A列の国民の祝日・休日月日を基準にして降順(False)・昇順（True）ができる
df_a = df.sort_values('国民の祝日・休日月日', ascending=False)

#B列の国民の祝日・休日名称を基準にして降順(False)・昇順（True）ができる
df_b = df.sort_values('国民の祝日・休日名称', ascending=False)

参考サイト：pandas.DataFrame, Seriesをソートするsort_values, sort_index

pandasでExcelデータの書き込み

pandasで昇順・降順したデータはExcelには書き込みがされておりません。
なので、ExcelWriterを使用して書き込みしていきます。

  1. df_a（A列の国民の祝日・休日月日を基準にして降順(False)にしたデータ）→resultシート
  2. df_b（B列の国民の祝日・休日名称を基準にして降順(False)にしたデータ）→result2シート

その部分が下記の部分ですね。

import pandas as pd

file_name = r"C:\Users\user\Desktop\syukujitsu.xlsx"

#ExcelWriterを追加することで、SaveとCloseの必要がなくなる。またengineをopenpyxlに指定ができる
with pd.ExcelWriter(file_name, engine='openpyxl', mode='a') as writer:
    
    #A列の国民の祝日・休日月日を基準にして降順(False)にしたデータをresultシートに追加
    df_a.to_excel(writer, sheet_name='result', index=False)
    
    #B列の国民の祝日・休日名称を基準にして降順(False)にしたデータをresult2シートに追加
    df_b.to_excel(writer, sheet_name='result2', index=False)

参考サイト：pandasでExcelファイル（xlsx, xls）の書き込み（to_excel）

まとめ

pythonでExcelの操作を紹介していますが、win32comの書き方は割とVBAと似ている部分があるなと感じます。特に今回のSortはほとんどVBAでの構文と変わりません。

正直な話、pythonは環境構築にそこそこハードルがあるため、VBAから勉強したほうが割と覚えられるのではないかとか考えています。

VBAの場合は、ある程度参考サイトや参考本も多いためプログラミング入門するのであれば個人的にはVBA(マクロ)から始めるのが割と環境構築とか気にしなくていいから楽だったりする。

ただ、Excelのみで完結するのであれば、openpyxl & pandasの組み合わせもいいですね。

pythonでやるとブラックボックス化しやすく、相手に説明するにもプログラミングの状態では分かりにくいという欠点がある。Excelにpythonのデータの過程を見せていければ相手に説明するときは良かったりしますね。

まあ、Pythonだといろんなことができるため勉強という意味ではExcelの操作は取っ付きやすいという利点もあったりする。

退屈なことはPythonにやらせよう ―ノンプログラマーにもできる自動化処理プログラミング
Al Sweigart (著)

-Excel, Python, プログラム