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

Japanese | English

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

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

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

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

【Python×Excel】openpyxlで条件付き書式(データバー・アイコンセット・カラースケール)を設定する方法【前編】

「openpyxlライブラリ」にて、Excelの「条件付き書式」を操作する方法を解説します。本記事では「データバー・アイコンセット・カラースケール」の適用例を図解を用いて紹介します。

www.shibutan-bloomers.com

2021.06.20

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

また、引数:operaotor 引数:text 引数:formula 引数:timePeriod は図6のマトリックスのように書式タイプによって指定が必要であったり、不要であったりします。

詳細は、<2.1.1>以降の書式タイプ別の解説の中で行います。

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

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

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

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

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

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

【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>の実行結果は以下のようになります。

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

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

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

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

【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”)を指定します。

ちなみに「特定の文字で終わる」の場合の抽出には、引数:formula に ‘RIGHT(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”を含むセルが赤色でハイライトされました。

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

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

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

【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列)に塗りつぶしが適用されました。

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

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

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

【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>の実行結果は以下のようになります。

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

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

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

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

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

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

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

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

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

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

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

【SAMPLEプログラム】

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

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

【例】

「セルの値が全体の下位３０%以内に含まれる場合は、赤で塗り潰す」という条件書式を定義するには<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>の実行結果は以下のようになります。

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

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

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

【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)以上のセルがハイライトされました。

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

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

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

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

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

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

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

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

【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')

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

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

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

4. まとめ

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

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

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

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

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

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

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

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

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

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

【Python×Excel】openpyxlでグラフを作成する方法【概要編】

「openpyxlライブラリ」にて、Excelにグラフを挿入するための手順を図解で解説します。データの参照範囲の定義や「タイトル」「凡例」「ライン」「マーカー」などグラフを構成する要素(オブジェクト)について取り上げています。

www.shibutan-bloomers.com

2021.07.12

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

Japanese | English