【Python×Excel】openpyxlで条件付き書式(セルの強調表示、上位/下位の抽出)を設定する【後編】

スポンサーリンク
Python外部ライブラリ(openpyxl)_条件付き書式Part2 openpyxl

English is here >>

本記事では、ExcelをPythonで操作する「openpyxl」ライブラリの解説をしていきます。

Excelには多くの機能が搭載されておりますので、ひと記事で全てを網羅することはできません。大項目(機能)ごとに分けた連載記事【Python×Excel】として執筆しています。

openpyxl_目次_完結版_rev0.1
図1. 連載【Python×Excel】のコンテンツ一覧

前回(連載5回目)の記事では、前編としてセルに条件付き書式「データバー」「アイコンセット」「カラースケール」を設定する方法について解説してきました。条件付き書式を適用することでデータ全体の傾向を簡単に可視化することができます。

前回の記事はこちらになりますので、参考にして下さい。


さて、今回も引き続き、「セルに条件付き書式を設定する」の後編としてStandard/Custom形式に分類される「セルの強調表示ツール」「上位・下位抽出ルール」とその他補足事項について取り上げていきます。

この記事を読むことで、次のようなことが「できるわかる」ようになりますので最後までお付き合いください。

この記事で学べること

➀. 条件付き書式の設定手順について体系的に理解できる。

➁. 抽出条件を定義(Ruleオブジェクト)する方法を、書式タイプごとに解説している。

  • セルの強調表示:特定のセルの値・文字列・重複の抽出
  • 上位/下位ツール:上(下)位〇項目/〇%、平均より上(下)の値を抽出

➂. Ruleクラスの引数・オプション指定について図解で示し、サンプルコードで具体例を紹介します。

本サイトでの紹介内容は一例です。また、関数などの省略可能なオプション引数などについては割愛していますので、詳細や不明点などは必要に応じて公式サイトなどを参照してください。

【公式ドキュメント】:https://openpyxl.readthedocs.io/en/stable/

それでは、次節より「セルに条件付き書式」を適用するための具体的な手順について解説していきます。

スポンサーリンク

1. セルに「条件付き書式」を設定する

本節では、前編の復習も兼ねて「条件付き書式の種類(分類)」と「書式設定のフロー(手順)」の概要について解説します。

1.1 「条件付き書式」の種類(分類)について

Excelで扱うことができる条件付き書式の一覧を図2に再掲します。分析機能による分類では「データの分布を可視化する(Builtin-formats)」と「データの抽出や順位付けをする(Standard/Custom-formats)」の2つに大別されるのでした。

条件付き書式のタイプ別一覧
図2. 条件付き書式の分析項目(再掲)

さらに、Builtin-formatsには「データバー」「アイコンセット」「カラースケール」の3つがあります。

またStandard/Custom-formatsは「セルの強調表示」と「上位・下位抽出ルール」のカテゴリに分類されます。

前回は、Builtin-formatsの3タイプについて解説しましたので、今回は、Standard/Custom-formatsの書式設定の方法を紹介します。

1.2 書式設定の手順について

openpyxlによって条件付き書式を定義する手順は、少し複雑です。

セルの抽出条件(ルール)の定義をまとめる Ruleオブジェクト を用意する必要がありますが、その取得手順には、図3のように3通りのパターンがあります目的の書式タイプに応じて、適切なクラスの選択や引数指定を行う必要があります。

(図中の ”パターン” は、筆者の見解であり公式ドキュメントにはこのような分類方法は採っていませんので注意してください。)

openpyxl_条件付き書式_Ruleオブジェクトの使い分けフロ-_rev0.3
図3. 条件付き書式を設定する手順

今回解説する「Standard/Custom-formats」に分類される条件付き書式は、「パターン」または「パターン3」の手順にて「Ruleオブジェクト」を定義することになります。

関連記事>でも解説していますので参考にして下さい。

openpyxl_条件付き書式_Ruleオブジェクト使い分け早見表②_rev0.3
図4. Ruleオブジェクトの作成パターン一覧

「条件付き書式」の概要は以上となります。次節からは「セルの強調表示ルール」「上位・下位抽出ルール」のRuleオブジェクトの定義方法について詳しく解説していきます。

スポンサーリンク

2. Ruleクラスによる条件定義(パターン1)

Python_基本文法_内包表記

本節では、条件を管理する「Ruleオブジェクト」の定義について解説していきます。

先述のとおり、「Standard/Custom-formats」に分類される条件付き書式は、「パターン」または「パターン3」の手順によりRuleオブジェクト取得します

はじめに、パターン(Ruleクラス)による条件定義についてです。

また、以降の説明では図5のように「指定の値より大きい」「日付」「重複する値」などに分類される条件書式を「セルの強調表示ルール」と呼称し、「上位10項目」「平均より上」などに分類される条件書式を「上位・下位ルール」としています。

openpyxl_セルの強調表示_上位下位ツールの区分_rev0.2
図5. 「セルの強調表示ルール」「上位・下位ツール」

Ruleクラスには、数多くの引数が存在しています。どのような書式タイプの条件定義をするかによって、適切に使い分ける必要があります。

2.1 「セルの強調表示ルール」のRuleオブジェクトを定義

セルの強調表示ルール」に分類される書式のRuleオブジェクトは、次のような引数指定から取得します。

Ruleオブジェクト(セルの強調表示)

from openpyxl.formatting.rule import Rule

Rule(type, operator, text , formula, timePeriod, dxf)


条件書式タイプによる引数の使い分けについては下表を参照

戻り値Ruleオブジェクト

引数:type には、定義する条件書式タイプをオプション定数で指定します。

例えば「セルの値」であれば “Cells”「特定の文字列を含む」であれば “ContainsText” のように目的に応じて選択します。

また、引数:operaotor 引数:text 引数:formula 引数:timePeriod図6のマトリックスのように書式タイプによって指定が必要であったり、不要であったりします。詳細は、<2.1.1項>以降の書式タイプ別の解説の中で行います。

Ruleクラスの引数_セルの強調表示
図6. 「セルの強調表示ルール」書式の引数指定

また、引数:dxf には抽出条件が成立したセルに対して、適用する装飾効果を DifferentialStyleオブジェクト として与えます。オブジェクトは、次の DifferentialStyleクラス から定義・取得します。

DifferentialStyleクラス

from openpyxl.styles.differential import DifferentialStyle

DifferentialStyle(font, numFmt, fill, alignment, border, protection)


引数: font :Fontオブジェクト(フォント情報)を設定する

引数: numFmt :NumberFormattingオブジェクト(表示形式情報)を設定する

引数: fill :Fillオブジェクト(塗り潰し情報)を設定する

引数: alignment :Alignmentオブジェクト(配置情報)を設定する

引数: border :Borderオブジェクト(罫線情報)を設定する

引数: protection :Protectionオブジェクト(保護情報)を設定する

戻り値DifferentialStyleオブジェクト

※すべてオプショナル引数となります

個々の引数に設定する Styleオブジェクト の詳細については、関連記事<連載3回目><連載4回目>を参照してください。

2.1.1 「セルの値」による条件付き書式

セルの値」を抽出条件とするRuleオブジェクトは、次の書式図のようにして定義します。

openpyxl_Ruleクラス_type_cells_rev0.2
図7. 「セルの値」を抽出条件とするRuleオブジェクトの定義

引数:type には「’cells‘」を指定します。条件式は、引数:operator引数:formula によって組み立てます。前者は(表1)に示す等号の種類を、後者は範囲の値やセルアドレスなどを設定します。

また、引数:dxf には、条件式が成り立つ(True)場合に、セルに施す書式(Styleオブジェクト)を与えます。(図8の例は、40~60間の値をもつセルを抽出する条件式となります。)

引数:operator のオプション機能
lessThan次の値より小さい
lessThanOrEqual’ 次の値以下
greaterThan次の値より大きい
greaterThanOrEqual次の以上
between次の値の
notBetween次の値の間以外
equal’    次の値に等しい
notEqual次の値に等しくない
表1. 引数operatorのオプション定数一覧

SAMPLEプログラム

それでは、「セルの値」による抽出条件の実例を紹介します。

【例1】

セルの値が40と60の間にある場合には、赤で塗り潰す」という条件付き書式は<List1>のようになります。引数:operator には、“〇と〇の間”を表す「‘between’」を指定します。

また、引数:formula には等号の値を指定しますが、今回のように複数ある場合はリスト([要素1、要素2…])で渡します。

このSAMPLE(例1,2)で使用するExcelブック(.xlsx)は以下からダウンロードできます。

from openpyxl import load_workbook
from openpyxl.formatting.rule import Rule
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.styles import PatternFill, Font

wb = load_workbook('Conditional_Formatting_num.xlsx')
ws = wb.active

# セルの書式をDifferentialStyleオブジェクトとして定義
fill_red = PatternFill(start_color='EE1111', end_color='EE1111', fill_type='solid')
font = Font(bold=True, color='FFFFFF')
dxf=DifferentialStyle(font=font ,fill=fill_red)

# Ruleオブジェクトの生成
# 等号は以下は引数operatorに'between'を指定し、等号式の値は引数formulaにリスト形式で渡します。
# 〇と〇の間のように値が複数必要な場合は、必要な個数分を先頭要素から順番に並べます。

rule = Rule(type='cellIs', operator='between' ,formula=[40, 60], dxf=dxf)

ws.conditional_formatting.add('A1:A10', rule)
wb.save('Conditional_Formatting_num_apply.xlsx')

【例2】

セルの値が「“I1”セル」の値以下だった場合には、赤で塗り潰す」という条件付き書式は<List2>のようなコードになります。引数:operator には、以下を表「‘lessThanOrEqual’」を指定します。

なお、引数:formula にアドレスを渡す場合には、相対・絶対参照どちらでも問題ありません。今回の例では、絶対参照「$」を付与しています。

from openpyxl import load_workbook
from openpyxl.formatting.rule import Rule
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.styles import PatternFill, Font

wb = load_workbook('Conditional_Formatting_num.xlsx')
ws = wb.active

# セルの書式をDifferentialStyleオブジェクトとして定義
fill_red = PatternFill(start_color='EE1111', end_color='EE1111', fill_type='solid')
font = Font(bold=True, color='FFFFFF')
dxf=DifferentialStyle(font=font ,fill=fill_red)

# Ruleオブジェクトの生成
# 等号は以下は引数operatorに'lessThanOrEqual'を指定し、比較対象のセルアドレス
#(絶対・相対参照どちらも可)は、引数formulaにリスト形式で指定します。

rule = Rule(type='cellIs', operator='lessThanOrEqual',formula=['$I$1'], dxf=dxf)

ws.conditional_formatting.add('C1:F10', rule)
wb.save('Conditional_Formatting_num_apply.xlsx')

<List1><List2>の実行結果は以下のようになります。

セルの値の条件書式のサンプルコード実行例_rev0.1
図8. <List1><List2>の実行結果

2.1.2 「特定の文字列」による条件付き書式

特定の文字列」を抽出条件とするRuleオブジェクトは、次の書式図のようにして定義します。図9は、文字列“hoge”を含むセルを抽出する例を示します。

openpyxl_Ruleクラス_type_containsText_rev0.2
図9. 「特定の文字列」を抽出条件とするRuleオブジェクトの定義

引数:type には、抽出する文字列パターン(ex. ○○を含む/始まる)に応じて(表2左)のオプション一覧から選択します。また、これに合わせて 引数:formula には、文字列の判定式を設定する必要があります。(表2中央)判定式の“string”の部分には抽出対象となる文字列に差し替えます

実際に、“A1セル”に“対象の文字列(string)”が入力されている必要はありません。少し分かりずらいですが、この判定式を条件書式適用セル範囲で“走査”して“マッチ”・“アンマッチ”の判定をしているようです。

また、openpyxl は“A1セル”を基準と考えるますので、それ以外のセルを指定すると動作に不具合が発生しますので“A1セル”の指定を推奨します。

また、引数:operator引数:text も関連する引数のようなのですが、筆者が確認した限りの指定の有無は、動作に直接関係しないようです。本記事では公式サイトにならい指定することにします。

引数:type(operator) 引数:formula の演算式機能
containsText’NOT(ISERRROR(SEARCH(“string“, A1)))’指定の文字を含む
notContainsText’(ISERRROR(SEARCH(“string“, A1)))’指定の文字を含まない
beginsWithLEFT(A1,1)=”string“‘指定の文字で始まる
endsWithRIGHT(A1,1)=”string“‘指定の文字で終わる
containsBlanks’NOT(ISERRROR(SEARCH(“”, A1)))’空白なセル
notcontainsBlanks’(ISERRROR(SEARCH(“”, A1)))’空白でないセル
表2. 引数type/operatorのオプション定数formulaの判定式一覧

SAMPLEプログラム

それでは、「特定の文字列」による抽出条件の実例を示します。以降のコード例では条件付き書式に関連する部分のみを抜粋しています。ファイルの読込み・保存などの処理は必要に応じて追加して下さい。

このSAMPLE(例1,2)で使用するExcelブック(.xlsx)は以下からダウンロードできます。

【例1】

文字列”abc”を含むセルがあった場合には、赤で塗り潰す」という条件付き書式を定義する例が<List3>になります。

引数:type(operator) には“文字列を含む”を意味する“containsText”を設定します。また、

引数:formula には、”NOT(ISERRROR(SEARCH(“string“, A1)))“を指定します。”string”の部分は検索したい文字列を指定するので今回の場合では”abc”とします。引数:text も同様です。

from openpyxl.formatting.rule import Rule
from openpyxl.styles.differential import DifferentialStyle

# (途中省略)

# セルの書式をDifferentialStyleオブジェクトとして定義
fill_red = PatternFill(start_color='EE1111', end_color='EE1111', fill_type='solid')
font = Font(bold=True, color='FFFFFF')
dxf=DifferentialStyle(font=font ,fill=fill_red)

# Ruleオブジェクトの生成
# 引数operatorとtextは必須ではなく、設定しなくても問題なし
# 重要なのが引数formulaで、検索対象の文字列とExcel関数を組合わせた形式を設定する
# formulaがセル範囲全体の条件判定に使われる。基準セルはA1セルとする必要がある
rule = Rule(type='containsText', operator='containsText', formula=['NOT(ISERROR(SEARCH("abc",A1)))'], text='abc', dxf=dxf)

# (途中省略)

【例2】

”E”で始まる文字列を含むセルがある場合には、赤で塗り潰す」という条件付き書式を定義する例が<List4>になります。

引数:type(operator) には“~で始まる”を意味する“beginssWith”を設定します。また 引数:formula には、LEFT(A1,1)=”stringを設定します。”string”の部分は検索したい先頭文字(今回の場合は”E”)を指定します。

ちなみに「特定の文字で終わる」の場合の抽出には、引数:formulaRIGHT(A1,1)=”stringを設定します。

from openpyxl.formatting.rule import Rule
from openpyxl.styles.differential import DifferentialStyle

# (途中省略)

# セルの書式をDifferentialStyleオブジェクトとして定義
fill_red = PatternFill(start_color='EE1111', end_color='EE1111', fill_type='solid')
font = Font(bold=True, color='FFFFFF')
dxf=DifferentialStyle(font=font ,fill=fill_red)

# Ruleオブジェクトの生成
# 引数typeとoperatorを'beginsWith'に設定、引数formulaは先頭文字列の判定をする形式を指定
rule = Rule(type='beginsWith', operator='beginsWith', formula=['LEFT(A1,1)="E"'], text="E", dxf=dxf)

# (途中省略)

<List3>の実行結果は以下のようになります。“abc”を含むセルが赤色でハイライトされました。

文字列の値の条件書式のサンプルコード実行例_rev0.1
図10. <List3>の実行結果

2.1.3 「日付」による条件付き書式

特定の日付」を抽出条件とするRuleオブジェクトは、次の書式図のようにして定義します。図11は、“先月”の日付データを含むセルを抽出する書式です。

openpyxl_Ruleクラス_type_timePeriod_rev0.2
図11. 「日付」を抽出条件とするRuleオブジェクトの定義

引数:type には”timePeriod“を指定します。引数:timePeriod には(表3)から任意の日付期間を選択します。これまでの「数値」や「文字列」の抽出条件とは異なり、シンプルな条件指定となります。

引数:timePeriod のオプション機能
yesterday昨日
today今日
tomorrow’ 明日
last7Days過去7日間
thisWeek今週
lastWeek先週
nextWeek来週
lastMonth先月
thisMonth今月
nextMonth来月
表3. 引数timePeriodのオプション定数

SAMPLEプログラム

それでは、「日付」を抽出条件とする実例をサンプルコードで確認します。コードは条件付き書式に関連する部分のみを抜粋しています。

また、このプログラムで使用したブック(.xlsx)は以下からダウンロードできます。

【例】

セルの日付が先月である場合には、赤で塗り潰す」という条件付き書式を定義するには<List5>のようにします。引数timePeriodには、先月を意味するlastMonthを指定します。

from openpyxl.formatting.rule import Rule
from openpyxl.styles.differential import DifferentialStyle

# (途中省略)

# セルの書式をDifferentialStyleオブジェクトとして定義
fill_red = PatternFill(start_color='EE1111', end_color='EE1111', fill_type='solid')
font = Font(bold=True, color='FFFFFF')
dxf=DifferentialStyle(font=font ,fill=fill_red)

# Ruleオブジェクトの生成
# 引数timePeriodにて対象期間の指定をする
rule = Rule(type='timePeriod', timePeriod='lastMonth', dxf=dxf)

# (途中省略)

<List5>の実行結果は以下のようになります。

実行日 “8月2日”(セルG2)に対して、7月分の日付を含むセル(C/D列)に塗りつぶしが適用されました。

python_日付の値の条件書式のサンプルコード実行例_rev0.1
図12. List5の実行結果

2.1.4 「重複する値(しない値)」による条件付き書式

重複する(しない)値」を抽出条件とするRuleオブジェクトは、次の書式図のようにして定義します。

openpyxl_Ruleクラス_type_duplicateValues_rev0.2
図13「重複する(しない)」条件書式のRuleオブジェクトの定義

引数:type には「duplicateValues(重複するセル) / uniqueValues(重複しないセル)」を設定します。図13は重複するデータをもつセルを抽出する書式図です。

重複の判定基準は「数値型」「文字列型」「日付型」のすべてのデータ型が対象になります。特に、引数などで対象のデータ型を指定する必要はありません。

引数:type機能
duplicateValues重複する値
uniqueValues一意の値
表4. 引数typeのオプション

SAMPLEプログラム

それでは、「重複する値(しない値)」を抽出条件とする実例をサンプルコードで確認します。コードは条件付き書式に関連する部分のみを抜粋しています。

また、このプログラムで使用したブック(.xlsx)は以下からダウンロードできます。

【例】

セル範囲の中に重複するデータがあった場合には、対象セルを赤で塗り潰す」という条件付き書式を定義するには<List6>のようにします。

その逆で、重複がないセルを、抽出対象にする場合は、Ruleクラスの 引数type に”uniqueValues”を設定します。

from openpyxl.formatting.rule import Rule
from openpyxl.styles.differential import DifferentialStyle

# (途中省略)

# セルの書式をDifferentialStyleオブジェクトとして定義
fill_red = PatternFill(start_color='EE1111', end_color='EE1111', fill_type='solid')
font = Font(bold=True, color='FFFFFF')
dxf=DifferentialStyle(font=font ,fill=fill_red)

# Ruleオブジェクトの生成
# 引数typeが'duplicateValues'で値の重複を抽出
rule = Rule(type='duplicateValues', dxf=dxf)
# 引数typeが'uniqueValues'で値の一意を抽出
#rule = Rule(type='uniqueValues', dxf=dxf)

# (途中省略)

<List6>の実行結果は以下のようになります。

抽出の対象は、「数値」「日付」「文字」のいずれのデータ型も対象となります。

openpyxl_重複の条件書式のサンプルコード実行例_rev0.1
図14. <List6>の実行結果

2.2 「上位・下位ルール」のRuleオブジェクトを定義

上位・下位ルール」に分類される書式のRuleオブジェクトは、次のような引数指定から取得します。

Ruleクラス(上位・下位ツール)

from openpyxl.formatting.rule import Rule

Rule(type, rank, percent, aboveAverage, eualAverage, dxf)


条件書式タイプによる引数の使い分けについては下表を参照

戻り値: Ruleオブジェクト

引数:type には、定義する条件書式タイプをオプション定数で指定します。

例えば、「上位10項目」であれば“top10”、「平均よりも上」であれば“AboveAverage”のように選択します。

また、その他の 引数:rank 引数:percent 引数:bottom などは図7のマトリックスのように書式タイプによって指定が必要であったり、不要であったりします。詳細は、<2.2.1項>以降の書式タイプ別の解説の中で行います。引数:dxf は先の「セルの強調表示ルール」と同様に DifferentialStyleオブジェクト を与えます。

Ruleクラスの引数_上位下位ツール_改訂版
図15. 「上位・下位ルール」書式の引数指定

以降からは「上位・下位ルール」について個々の書式タイプについての詳細とサンプルコードで実例を紹介します。

2.2.1 「上位・下位」による条件付き書式

上位・下位ルール」を抽出条件とするRuleオブジェクトは、次の書式図のようにして定義します。

openpyxl_Ruleクラス_type_Top10
図16. 「上位・下位」条件書式のRuleオブジェクトの定義

引数:type には”top10“を設定します。デフォルトでは10位、10%と基準が「10」になっていますが、引数:rank に任意の順位(%)を指定することができます。

また、「上位から下位へ」・「順位から%へ」の切替は、引数:percent / 引数:bottom を表5のような組合わせで設定します。図16は全体下位35%までの値を持つセルを抽出する例となります。

引数:percent 引数:bottom機能
False(Default)True(Default)位〇
False(Default)False位〇
TrueTrue(Default)位〇%
TrueFalse位〇%
表5. 引数percent/bottomのオプション

SAMPLEプログラム

それでは、「上位・下位ルール」を抽出条件とする実例をサンプルコードで確認します。コードは条件付き書式に関連する部分のみを抜粋しています。

また、このプログラムで使用したブック(.xlsx)は以下からダウンロードできます。

【例】

セルの値が全体の下位30%以内に含まれる場合は、赤で塗り潰す」という条件書式を定義するには<List7>のようにします。

下位なので 引数:bottom を”False”とし、単位が%なので 引数:percent を”True”に設定しています。

from openpyxl.formatting.rule import Rule
from openpyxl.styles.differential import DifferentialStyle

# (途中省略)

# セルの書式をDifferentialStyleオブジェクトとして定義
fill_red = PatternFill(start_color='EE1111', end_color='EE1111', fill_type='solid')
font = Font(bold=True, color='FFFFFF')
dxf=DifferentialStyle(font=font ,fill=fill_red)

# Ruleオブジェクトの生成
# 引数rankで順位設定、引数bottomで上位・下位の選択、引数percentで順位・%の選択
rule = Rule(type='top10', rank=30, bottom=True, percent=True, dxf=dxf)

# (途中省略)

<List7>の実行結果は以下のようになります。

openpyxl_Top10の条件書式のサンプルコード実行例_rev0.1
図17. List7の実行結果

2.2.2 「平均より上/下」による条件付き書式

平均より上・下」を抽出条件とするRuleオブジェクトは、次の書式図のようにして定義します。

openpyxl_Ruleクラス_type_AboveAverage_rev0.2
図18. 「平均より上・下」条件書式のRuleオブジェクトの定義

引数:type には “aboveAverage” を設定します。平均より「/」「より上(>)/以上(≧)」の条件は、引数:aboveAverage引数:equalAverage を表6の組合せで対応します。図18は平均よりも小さい値をもつセルを判定する例となります。

引数aboveAverage 引数equalAverage 機能
True (Default)True平均以上
True (Default)False (Default)平均より大きい
FalseTrue平均以下
FalseFalse (Default)平均より小さい
表6. 引数: aboveAverage/equalAverageのオプション

SAMPLEプログラム

それでは、「平均より上/下」を抽出条件とする実例をサンプルコードで確認します。コードは条件付き書式に関連する部分のみを抜粋しています。

また、このプログラムで使用したブック(.xlsx)は以下からダウンロードできます。

【例】

セルの値が平均よりも大きい場合には、赤で塗り潰す」という条件付き書式を定義するには<List8>のようにします。「より大きい」なので 引数:aboveAverage / 引数:equalAverage はデフォルト値(None) としています。

from openpyxl.formatting.rule import Rule
from openpyxl.styles.differential import DifferentialStyle

# (途中省略)

# セルの書式をDifferentialStyleオブジェクトとして定義
fill_red = PatternFill(start_color='EE1111', end_color='EE1111', fill_type='solid')
font = Font(bold=True, color='FFFFFF')
dxf=DifferentialStyle(font=font ,fill=fill_red)

# Ruleオブジェクトの生成
# 引数:aboveAverageで平均以上・以下の設定、equalAverageで等号符号の有無の指定
rule = Rule(type='aboveAverage', aboveAverage=None, equalAverage=None , dxf=dxf)

# (途中省略)

<List8>の実行結果は以下のようになります。

平均値(58.6)以上のセルがハイライトされました。

平均以上以下の条件書式のサンプルコード実行例_rev0.1
図19. <List8>の実行結果
スポンサーリンク

3. CellIsRuleクラスによる条件定義(パターン3)

<1.2>で解説した「セルの値による条件付き書式」は、Ruleクラスによって定義する(パターン1)以外に、 CellIsRuleクラス を使う方法(パターン3)もあります。

どちらを選択しても、できることは同じですが「Ruleクラス」で指定していた、DifferentialStyleオブジェクト (セルの書式(装飾)情報をまとめたもの)を用意する必要はなくなります。

代わりに、直接 font(フォント) / border(罫線) / fill(塗り潰し) の各引数にセルの書式オブジェクトを設定します。

つまり、DifferentialStyleクラス の宣言と手続きを省略できるメリットがありますが、どちらを選択するかはプログラマの好みによります。

CellIsRuleクラスの書式は以下のとおりで、戻り値としてRuleオブジェクトを取得できます。

CellIsRullクラス

from openpyxl.formatting.rule import CellIsRule

CellIsRule(operator, formula, stopIfTrue, font, border, fill)


引数: operator : 等号式を以下オプション定数より指定する

(‘lessThan’, ‘lessThanOrEqual’, ‘greaterThan’, ‘greaterThanOrEqual’, ‘notBetween’, ‘notEqual’, ‘between’, ‘equal’)

引数: formula : 等号式の基準となる値やセルの参照アドレスの指定する

値やセルのアドレスの文字列を要素とするリストを設定する(指定可能な要素の数は等号式に依存する)

引数: stopIfTrue : 条件が成立した時に処理を継続するかの指定

(False: None(停止しない) / True(停止する))

引数: font : 判定式が成立した時に、セルに適用するフォントの書式

(Fontオブジェクト を設定する)

引数: border : 判定式が成立した時に、セルに適用する罫線の書式

(Borderオブジェクト を設定する)

引数: fill : 判定式が成立した時に、セルに適用する塗り潰しの書式

(Fillオブジェクト を設定する)

戻り値:Ruleオブジェクト

Excelの設定ウィンドウと各引数との関係は図20に示すとおりです。抽出条件となる不等号式は、

引数:operator引数:formula を組合せて構成します。等号は<2.2項>表1のオプション定数から選択します。

セルの書式_CellIsRuleクラス
図20. CellIsRuleクラスの引数とExcelの設定ウィンドウとの比較

SAMPLEプログラム

ここで、CellIsRuleクラス による条件書式の例をサンプルコードで確認します。

コードの概要は、「“I1”セルの値以下のセルの抽出」と「40~60の値をもつセルの抽出」する条件書式を「CellIsRuleクラス」で定義します。

このコードで使用するExcelブック(.xlsx)は以下からダウンロードできます。

from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font

# CellIsRuleの書式設定に必要なクラスをインポート(クラス一つ)
from openpyxl.formatting.rule import CellIsRule

wb = load_workbook('Conditional_Formatting_num.xlsx')
ws = wb.active


# [A]-----------------------------------------------------------------------------------
# Fillオブジェクトの生成 (セルの書式…セルの塗り潰しパターンの定義)

# 赤色で塗りつぶし
fill_red = PatternFill(start_color='EE1111', end_color='EE1111', fill_type='solid')
# 青色で塗りつぶし
fill_blue =  PatternFill(start_color='1111EE', end_color='1111EE', fill_type='solid')

# Fontオブジェクトの生成 (セルの書式…セルのフォント条件の定義(太字・白色))
font = Font(bold=True, color='FFFFFF')

# [B]-----------------------------------------------------------------------------------
# CellIsRuleオブジェクトの生成

# I1セルの値(40)以下のセルを赤色で塗り潰し、文字色を白くする
rule1 = CellIsRule(operator='lessThanOrEqual', formula=['$I$1'], stopIfTrue=None, fill=fill_red, font=font)

# 40以上60以下の値をもつセルを青色で塗り潰し、文字色を白くする
rule2 = CellIsRule(operator='between', formula=['40', '60'], stopIfTrue=None, fill=fill_blue, font=font)


# [C]-----------------------------------------------------------------------------------
# セル範囲へ条件書式ルールの適用

# A1:A10の範囲にrule1を適用
ws.conditional_formatting.add('A1:A10', rule1)
# C1:F10の範囲にrule2を適用
ws.conditional_formatting.add('C1:F10', rule2)


wb.save('CF_CellIsRule_apply.xlsx')

それではポイントを解説します。

15,17,20行目 :【セルの書式オブジェクトの定義】

抽出条件に適合(マッチ)した時に適用するセルの書式オブジェクトを定義します。ここでは、patternFillクラスから塗りつぶし効果を、Fontクラス からフォント設定をするオブジェクトをそれぞれ定義します。

26,29行目 : 【CellIsRule(Rule)オブジェクトの定義】

CellIsRuleクラスで、「セルの値」による抽出条件を2つ定義します。それぞれ 引数:operator引数:formula にて等号式を組み立てます。引数:fill 引数:font には先の書式オブジェクトを設定します。

36,38行目 : 【条件付き書式の適用】

Worksheetオブジェクトの conditional_formatting.add()メソッド で指定したセル範囲に、先のRuleオブジェクトを適用させます。

<List9>の実行結果は次のようになりました、以下からダウンロードできます。

条件に適合するセルが強調表示されました。

CellIsRuleクラスのサンプルプログラムの実行結果
図21. <List9>の実行結果

4. まとめ

いかがでしたでしょうか?

Excelを操作する外部ライブラリ「openpyxl」を使用して、セルに条件付き書式を設定する方法について解説してきました。

Excelにはデータ分析や統計に関連する機能たくさん備わっています。今回紹介した「セルの条件付き書式」はその一つです。これ以外にも「ソート(並び替え)」や「フィルター」といった便利な機能をopenpyxlから操作することができます。

「Python」と「Excel」ともにデータ分析を得意としますから、その相性は抜群です。

すべてをPythonだけで対応するのではなく、Excelとうまく連携させて「この処理・この機能はExcelで!」「データの可視化はExcelで!」といったように使い分けられると良いと思います。

その架け橋となるのがこの「openpyxl」であれば良いですね。

それでは、今回の記事のポイントをまとめます。

➀. Excelが提供する条件付き書式には「Standard/Custom型」と「Built-in型」の2つに区分される。前者はさらに、機能別に「セルの強調表示ルール」「上位/下位ルール」に分けられる

➁. 抽出条件はRuleオブジェクトとして定義する。Ruleオブジェクトは Ruleクラス から取得するが、条件タイプに応じて引数指定を適切に選択する必要がある。

条件タイプには、以下がある。

  • セルの強調表示ルール:「セルの値」「特定の文字列」「日付」「重複」
  • 上位/下位ルール:「上位」「下位」「平均より上」「平均より下」

➂.「セルの値」による条件書式には、Ruleクラス 以外にも CellisRuleクラス からRuleオブジェクトを取得ができる。できることは同じなのでプログラマの好みで選択すれば良い。


さて次回は、ワークシートにグラフを挿入する方法を取り上げていきます。

グラフはデータを「見える化」するためのExcelの最も得意とする機能となります。もちろん、openpyxlにはグラフに関するさまざまなモジュールやクラスが提供されていますので、基礎から丁寧に解説をしていきます。

次回もお楽しみに!リンク先はこちらになります。↓

最後までお読みいただきありがとうございました。

English is here >>

タイトルとURLをコピーしました