Openpyxlを使うとPythonでExcel作業を効率化することができます。
Openpyxlによるエクセルの作業自動化は多岐にわたりますが、全容を掴めないまま手探りで勉強していく人も多いです。
そこで、基礎コードと入門事例サンプルを題材にして、Openpyxlを学んでいく方法を提案します。
・Openpyxlの基礎サンプルコード
・Openpyxlを使った事例
目次
- 1 Openpyxlとは？読み方や使い方
- 2 Python×Excelの入門としてopenpyxlを使うことを推奨する理由
- 3 Openpyxlの基礎プログラムコード一覧｜新規作成から最終行取得まで
- 4 CSVファイルの読み込み｜Openpyxlよりpandasを推奨
- 5 Openpyxlをエクセル関数の事例で解説してみる
- 6 サンプル事例｜エクセル関数をopenpyxlで作ってみる
- 6.1 事例1｜エクセルSUM関数をOpenpyxlで作成(最終行まで自動計算)
- 6.2 事例2｜エクセルIF関数をopenpyxlで作成(条件に一致で文字を赤くする)
- 6.3 事例3｜エクセルCOUNTIF関数をopenpyxlで作成(条件に一致する件数をカウント)
- 6.4 事例4｜エクセルCOUNTIFS関数をopenpyxlで作成(2条件に一致する件数をカウント)
- 6.5 事例5｜エクセルSUMIFS関数をopenpyxlで作成(条件に一致する値を合計)
- 6.6 事例6｜エクセルSUMIFS関数をopenpyxlで作成(条件に一致する値を合計)
- 6.7 事例7｜エクセルSUMIFS関数をopenpyxlで作成(条件に一致する値を合計)
- 7 Pythonについて詳しく理解したいなら
Openpyxlとは？読み方や使い方
Openpyxlとは、PythonでExcelを操作するときによく使われるライブラリです。
Openpyxlの使い方｜インストール方法
1. コマンドプロンプトを起動（ショートカットキー[windows] + [R]）
2. 「cmd」と記入して[Enter]
3. 「pip install openpyxl」と入力して[Enter]
4. インストール完了
これでインストールが完了し、openpyxlを使えるようになります。
Openpyxlの読み方
openpyxlは、オープンパイエクセルやオープンピーワイエックスエルと読めます。
海外サイトでは、openpixelと紹介されている場合もありますが、正確な読み方として紹介されているものは私が調べる限りありません。
Python×Excelの入門としてopenpyxlを使うことを推奨する理由
この記事ではopenpyxlに関して詳しく紹介をしていきます。
しかしExcelを操作できるライブラリにはいくつかの選択肢が存在します。
1. openpyxl
2. pandas
3. xlwings
4. win32com
5. xlrd
これらのライブラリのなかでOpenpyxlを紹介する理由は以下です。
openpyxlのウェブ情報が豊富だから
上記の理由により、Pythonを学ぶ人にはopenpyxlを推奨しています。
ウェブで手に入る情報が多いことのメリットは、調べるためのコストが低くて済むことにあります。
たとえば海外文献は日本語文献より数は多いですが、英語を理解するのに時間がかかる人も多いはずです。
その点、現時点でopenpyxlに関する情報はPython×Excelでは最も多い部類に入ります。
ウェブで調べながらPythonプログラムを作っていく人が多いことを考えると、openpyxlを扱うことが一番良いと考えています。
Openpyxlの基礎プログラムコード一覧｜新規作成から最終行取得まで
Openpyxlの基本サンプルコードを紹介します。
より詳細は情報はこちらの外部サイトでも紹介されています。
本記事以外のサンプルコードも数多く記載されていますので、一度読んでみると勉強になるはずです。
この記事では、以下のサンプルコードについて紹介していきます。
1.Excelファイルの読み込み・保存(新規作成や上書き保存)
1-1. 新規ファイルの読み込み・保存
|
1 2 3 4 |
import openpyxl wb = openpyxl.Workbook() ws = wb.active wb.save('Sample1.xlsx') |
新規のExcelファイルを作成し「Sample1.xlsx」で保存します。
1-2. 既存ファイル読み込み・上書き保存
|
1 2 3 4 5 |
import openpyxl filepath = 'Sample1.xlsx' wb = openpyxl.load_workbook(filepath) wb.save(filepath) wb.save('Sample1.xlsx') |
「Sample1.xlsx」のExcelファイルを読み込み、上書き保存します。。
1-3. 既存ファイル読み込み・新しい名前で保存
|
1 2 3 4 5 |
import openpyxl filepath = 'Sample1.xlsx' wb = openpyxl.load_workbook(filepath) wb.save(filepath) wb.save('Sample2.xlsx') |
「Sample1.xlsx」のExcelファイルを読み込み「Sample2.xlsx」という名前で保存します。
2. Excelのワークシート操作
2-1. シート指定
|
1 2 3 4 5 |
import openpyxl filepath = 'Sample1.xlsx' wb = openpyxl.load_workbook(filepath) ws = wb['Sheet1'] ws = wb.worksheets[0] |
2-1-1. シート名で指定
|
4 |
ws = wb['Sheet1'] |
シート名で指定する。
2-1-2. シートの順番で指定
|
5 |
ws = wb.worksheets[0] |
2-2. シート名を取得
|
1 2 3 4 5 6 |
import openpyxl filepath = 'Sample1.xlsx' wb = openpyxl.load_workbook(filepath) ws = wb.worksheets[0] ws.title wb.sheetnames |
2-2-1. 指定したシートの名前を取得
|
4 5 |
ws = wb.worksheets[0] ws.title |
Excelの0番目(一番左のシート)をwsとして、ws.titleでシート名を取得します。
2-2-2. Excelファイルの全シート名を取得
|
6 |
wb.sheetnames |
wbで読み込んだExcelの全シート名を取得します。
2-3. シート名変更
|
1 2 3 4 5 |
import openpyxl filepath = 'Sample1.xlsx' wb = openpyxl.load_workbook(filepath) ws = wb['Sheet1'] ws.title = 'シート1' |
シート名「Sheet1」を「シート1」に変更します。
2-4. シート追加
|
1 2 3 4 5 |
import openpyxl filepath = 'Sample1.xlsx' wb = openpyxl.load_workbook(filepath) ws_new = wb.create_sheet(title='NewSheet') ws_new2 = wb.create_sheet(title='New Sheet', index=2) |
2-4-1. 挿入先を指定せずシート追加
|
4 |
ws_new = wb.create_sheet(title='NewSheet') |
「NewSheet」というシートを追加する。
2-4-2. 挿入先を指定してシート追加
|
5 |
ws_new2 = wb.create_sheet(title='New Sheet', index=2) |
2番目に「NewSheet」というシートを追加する。
2-5. シートコピー
|
1 2 3 4 5 |
import openpyxl filepath = 'Sample1.xlsx' wb = openpyxl.load_workbook(filepath) ws = wb['Sheet1'] wb.copy_worksheet(wb['Sheet1']) |
「Sheet1」というシートをコピーする。
2-6. シート削除
|
1 2 3 4 5 6 |
import openpyxl filepath = 'Sample1.xlsx' wb = openpyxl.load_workbook(filepath) ws = wb['Sheet1'] wb.remove(ws) wb.remove(wb.worksheets[-1]) |
2-6-1. 挿入先を指定せずシート追加
|
5 |
wb.remove(ws) |
ws(ここでは「Sheet1」のシート)を削除する。
2-6-2. 挿入先を指定してシート追加
|
6 |
wb.remove(wb.worksheets[-1]) |
末尾のシートを削除する。
3. Excelファイルのセル操作
3-1. セル取得
|
1 2 3 4 5 6 7 |
import openpyxl filepath = 'Sample1.xlsx' wb = openpyxl.load_workbook(filepath) ws = wb['Sheet1'] myCell1 = ws["A1"] myCell2 = ws.cell(row=1, column=1) myCell3 = ws.cell(1, 1) |
以下の3つは同じセルを取得することができます。
|
5 6 7 |
myCell1 = ws["A1"] myCell2 = ws.cell(row=1, column=1) myCell3 = ws.cell(1, 1) |
上記の3つはセルA1を指定します。
3-2. セルの行を取得
|
1 2 3 4 5 6 |
import openpyxl filepath = 'Sample1.xlsx' wb = openpyxl.load_workbook(filepath) ws = wb['Sheet1'] myCell1 = ws["A1"] myCell.row |
「myCell.row」は1となります（myCellがA1であり、1行目に存在するため）
3-3. セルの列を取得
|
1 2 3 4 5 6 |
import openpyxl filepath = 'Sample1.xlsx' wb = openpyxl.load_workbook(filepath) ws = wb['Sheet1'] myCell1 = ws["A1"] myCell.column |
「myCell.column」はAとなります（myCellがA1であり、A列に存在するため）
3-4. セルのアドレスを取得
|
1 2 3 4 5 6 |
import openpyxl filepath = 'Sample1.xlsx' wb = openpyxl.load_workbook(filepath) ws = wb['Sheet1'] myCell1 = ws["A1"] myCell.coordinate |
「myCell.coordinate」はA1となります。
3-5. セル値の取得
|
1 2 3 4 5 6 |
import openpyxl filepath = 'Sample1.xlsx' wb = openpyxl.load_workbook(filepath) ws = wb['Sheet1'] myCell1 = ws["A1"] myValue = myCell.value |
myValueでセルA1の値を取得することができます。
3-6. セルに値を入力
|
1 2 3 4 5 6 |
import openpyxl filepath = 'Sample1.xlsx' wb = openpyxl.load_workbook(filepath) ws = wb['Sheet1'] myCell = ws["A1"] myCell.value = 'ABCDE' |
セルA1に「ABCDE」を入力します。
4. Excelシートの最終行最終列を取得
4-1. 最終行を取得
|
1 2 3 4 5 |
import openpyxl filepath = 'Sample1.xlsx' wb = openpyxl.load_workbook(filepath) ws = wb['Sheet1'] lastrow = ws.max_row |
lastrowは「Sheet1」の最終行を取得します。
4-2. 最終列を取得
|
1 2 3 4 5 |
import openpyxl filepath = 'Sample1.xlsx' wb = openpyxl.load_workbook(filepath) ws = wb['Sheet1'] lastcol = ws.max_col |
lastcolは「Sheet1」の最終列(一番右の列)を取得します。
5. 複数セルの操作
|
1 2 3 4 5 |
import openpyxl filepath = 'Sample1.xlsx' wb = openpyxl.load_workbook(filepath) ws = wb['Sheet1'] myRange = ws['A2:E5'] |
|
5 |
myRange = ws['A2:E5'] |
指定した範囲をタプル型で取得します。
そのため、後述する繰り返し処理を組み合わせて使うと威力を発揮します。
6. 行列の挿入・削除
6-1. 行の追加挿入
|
1 2 3 4 5 |
import openpyxl wb = openpyxl.load_workbook("Book1.xlsx") ws = wb["Sheet1"] ws.insert_rows(2) ws.insert_rows(2, 5) |
6-1-1. 行の追加
|
4 |
ws.insert_rows(2) |
2行目に1行挿入する。
6-1-2. 行の追加(複数行を挿入)
|
5 |
ws.insert_rows(2, 5) |
2行目に5行挿入する。
6-2. 行の削除
|
1 2 3 4 5 |
import openpyxl wb = openpyxl.load_workbook("Book1.xlsx") ws = wb["Sheet1"] ws.delete_rows(2) ws.delete_rows(2, 5) |
6-2-1. 行の削除
|
4 |
ws.delete_rows(2) |
2行目を削除する。
6-2-2. 行の削除(複数)
|
5 |
ws.delete_rows(2, 5) |
2行目に5行分削除する。
6-3. 列の追加挿入
|
1 2 3 4 5 |
import openpyxl wb = openpyxl.load_workbook("Book1.xlsx") ws = wb["Sheet1"] ws.insert_cols(2) ws.insert_cols(2, 5) |
6-3-1. 列の追加
|
4 |
ws.insert_cols(2) |
2列目(B列)に1列挿入する。
6-3-2. 列の追加(複数)
|
5 |
ws.insert_cols(2, 5) |
2列目(B列)に5列挿入する。
6-4. 列の削除
|
1 2 3 4 5 |
import openpyxl wb = openpyxl.load_workbook("Book1.xlsx") ws = wb["Sheet1"] ws.delete_cols(2) ws.delete_cols(2, 5) |
6-4-1. 列の削除
|
4 |
ws.delete_cols(2) |
2列目(B列)に1列削除する。
6-4-2. 列の削除(複数)
|
5 |
ws.delete_cols(2, 5) |
2列目(B列)から5列削除する。
7. シート内のセルの値を一括読み込み・書き出し
|
1 2 3 4 5 6 7 8 |
import openpyxl wb = openpyxl.load_workbook("Book1.xlsx") ws = wb["Sheet1"] values=[[cell.value for cell in row] for row in ws] ws1 = wb["Sheet2"] for y, row in enumerate(values): for x, cell in enumerate(row): ws1.cell(row= y+2, column= x+1, value=values[y][x]) |
7-1. 一括読み込み
|
4 |
values=[[cell.value for cell in row] for row in ws] |
上記の記載で、valuesに「Sheet1」のデータを取得することができます。
7-2. 一括書き出し
|
5 6 7 8 |
ws1 = wb["Sheet2"] for y, row in enumerate(values): for x, cell in enumerate(row): ws1.cell(row= y+2, column= x+1, value=values[y][x]) |
values(Sheet1のデータ)をSheet2に書き出すことができます。
CSVファイルの読み込み｜Openpyxlよりpandasを推奨
CSVファイルの読み込みはopenpyxlよりpandasで読み込むことをオススメします。
理由はpandasのほうが、その後の解析に便利だからです。
たとえばpandasで読み込んだ値を週ごとの分析したり、機械学習のscikitlearnなどに渡したりする場合、pandasで前処理をすることになります。
CSVファイルを読み込むとき、解析を目的にしていることが多いため、pandasをオススメします。
pandasについては以下で事例とともに紹介していますので、ご活用ください。
Openpyxlをエクセル関数の事例で解説してみる
本記事はOpenpyxl初心者の人が、Pythonに慣れることを目的としています。
しかしいきなりレベルの高い内容を紹介されても、理解が進まないことも少なくありません。
そこでよく知られたエクセル関数をOpenpyxlで実現する方法を紹介していきます。
エクセル関数を取り扱う理由は以下です。
エクセル関数という結果をイメージしやすい内容を取り扱うことで、Pythonの感覚をつかみやすいから
Pythonでエクセル操作するにあたって、Pythonのプログラムの書き方を勉強するのにまずは集中したほうが無難です。
そのため、結果を予想しやすいエクセル関数の挙動を取り扱います。
そうすることで、Pythonプログラムの書き方を理解しやすくなります。
サンプル事例｜エクセル関数をopenpyxlで作ってみる
1. sum関数
2. if関数
3. countif関数
4. countifs関数
5. sumif関数
6. sumifs関数
7. vlookup関数
以下の事例について紹介していきます。
事例1｜エクセルSUM関数をOpenpyxlで作成(最終行まで自動計算)
エクセルを操作するときに、数値の合計を計算することは多いです。
最終行を自動取得して、値の合計値を算出していきます。
エクセルの読み込みから書き込みまで、超基本ですがopenpyxlを使ったExcel操作をイメージできるはずです。
以下で詳しく紹介していきます。
事例2｜エクセルIF関数をopenpyxlで作成(条件に一致で文字を赤くする)
エクセルを操作するときに、条件分岐は頻出工程です。
セルの値を読み込みif文を使って、条件に合致する場合にセルの文字を赤くしていきます。
Excel操作の超基本ですが、if文をイメージするのには最適な事例です。
以下で詳しく紹介していきます。
事例3｜エクセルCOUNTIF関数をopenpyxlで作成(条件に一致する件数をカウント)
エクセルを操作するときに、条件に合致する件数を調べることは多いです。
Excel操作ではよくある内容なので、Pythonプログラムを学ぶのにも適している内容です。
以下で詳しく紹介していきます。
事例4｜エクセルCOUNTIFS関数をopenpyxlで作成(2条件に一致する件数をカウント)
エクセル操作では、2条件に合致する件数を調べることがあります。
単純にエクセル関数を使うときは、countifsを使うのですが、python(openpyxl)ではどのように実現するのかイメージしにくい人もいるはずです。
条件数に応じて、絞り込みを行う基本事例ですので、ぜひ理解しておくことをオススメします。
以下で詳しく紹介していきます。
事例5｜エクセルSUMIFS関数をopenpyxlで作成(条件に一致する値を合計)
エクセル操作では、条件に合致する値を合計することがあります。
エクセル作業として考えるなら、sum関数とif関数を組み合わせたsumif関数を使います。
このsumif関数をpython(openpyxl)で実現する方法を紹介していきます。
途中でゼロリセットしたり、空白の取り扱いだったりと基本事例を学ぶのに良い事例なので、ぜひ理解しておくことをオススメします。
以下で詳しく紹介していきます。
事例6｜エクセルSUMIFS関数をopenpyxlで作成(条件に一致する値を合計)
ここではPython×Excelの入門事例として、2条件を満たす場合に累算する「sumifs関数」を作っていきます。
エクセル操作では、2つの条件に合致する値を算出したいときがあります。
エクセルの作業として考えるなら、sumifs関数です
このsumifs関数をpython(openpyxl)で実現する方法を紹介していきます。
データの取得からif文の作り方まで参考になることが多いので、ぜひご覧ください。
以下で詳しく紹介していきます。
事例7｜エクセルSUMIFS関数をopenpyxlで作成(条件に一致する値を合計)
Python×Excelの入門事例として、マスタデータを取得する「vlookup関数」を作ります。
Pythonを使って作業を効率化していくとき、マスタデータとのマッチングは頻度の高い処理です。
マッチングの基本やfor文から抜け出す方法などの参考になる処理がいくつかあるので、ぜひ理解しておくことをオススメします。
以下で詳しく紹介していきます。
Pythonについて詳しく理解したいなら
Pythonを活用すると、仕事を効率化できる幅を広げることができます。
たとえば私が実際にPythonを活用して効率化してきた作業は以下の記事で紹介しています。
興味がある人は以下の記事もご覧ください。
Python×Excel
PythonとExcelで自動化できることを紹介しています。
事例も数多く紹介しているので、ぜひ参考にしてみてください。
Python×効率化のサンプル
Pythonで効率化できる事例をサンプルコード付きで紹介しています。
Python×pdf
PythonとPDFで自動化できることを紹介しています。
事例も数多く紹介しているので、ぜひ参考にしてみてください。
Pythonって難しい？
Pythonの難易度などについては、以下で紹介しています。
勉強の参考になれば幸いです。
