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

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

English is here >>

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

Excelには多くの機能が搭載されておりますので、一記事で簡潔に解説することはできません。大項目(機能)ごとに分けた連載記事として執筆しています。

これまでの連載の中で、openpyxlを使う上での必須事項を取り上げています。以降の解説を読み進めていくにはある程度、予備知識を必要としますので、関連記事もあわせて参考にして下さい。

ブックの作成・保存、ワークシートの追加、それから行、列、セル、各オブジェクトの取得とその操作については<連載1,2回目>が参考になります。

セルの書式設定(フォントや罫線など装飾)に関してはこちらです。


さて、連載5回目となる本記事では、openpyxlで「セルの条件付き書式」を操作する方法を紹介します。もちろん、openpyxlには関連するモジュールやクラスが提供されています。

Excelの重要な機能のひとつにデータ分析があります。この「セルの条件付き書式」はそのデータ分析機能の一部です。詳細はExcelの専門書籍・サイトに譲りますが、ある条件を満たすデータ含むセルを抽出したり、規則性を発見できたりといったことが簡単に行えます

共にデータ分析を得意とする「Python」と「Excel」、それぞれの特徴と便利な機能を使い分け仕事の質と効率アップを目指していきましょう。

その他、本連載【Python×Excel】のコンテンツ構成はこちらのようになっていますので、参考にして下さい。今回の「セルの条件付き書式」についても、説明が広範わたるため記事内容を前編(本記事)と後編の2回に分けて解説していきます。

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

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

この記事で学べること

➀.セルに条件付き書式を適用させるための手順が体系的に把握できる。

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

データバー(DataBar):「DataBarRule」オブジェクトの仕様と定義方法

アイコンセット(IconSet):「IconSetRule」オブジェクトの仕様と定義方法

カラーバー(ColorScale):「ColorScaleRule」オブジェクトの仕様と定義方法

➂.Excelの設定ウィンドウとクラスの「引数」「オプション」の対応関係を図解で示し、具体例をサンプルコードを紹介しています。

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

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

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

スポンサーリンク

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

それでは、「openpyxl」でセルに条件付き書式を設定する手順を解説していきます。

本節では、まず「条件付き書式とはどのようなものなのか」という機能の概要について触れたのち、大まかな「書式設定のフロー(手順)」について解説していきます。そして、抽出条件定義に必要となるクラスやオブジェクトについて整理したいと思います。

1.1 条件付き書式とは?

条件付き書式とはExcelが備えるデータ分析機能のひとつです。Excelの メニューからは「ホームタブ -> スタイルグループ -> 条件付き書式」で設定ウィンドウを表示させることができます。設定ウィンドウには図2のように様々な分析項目が並んでいます。

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

分析機能により「データの抽出・統計」と「データ分布の可視化」に大別されます。

さらに、前者は特定の条件を満たすセルを抽出する「強調表示ルール」と、セル範囲内での統計順位を求める「上位・下位ルール」に分けられます。(公式ドキュメントでは「Standard/Custom formats」に区分されます。)

一方後者は、データ分布の可視化手段により「データバー」「アイコンセット」「カラースケール」から構成されます。(公式ドキュメントでは「Builtin formats」に区分されます。)

本記事は、openpyxlを解説する記事ですから、Excel機能の説明はこの程度に留めます。その他、Excelによるデータ分析手法は各専門書・サイトなどを参考にして下さい。

1.2 条件付き書式を設定する手順

プログラムで条件付き書式を定義し適用させるのは、少し複雑です。

条件付き書式を定義するにあたってコアとなる手順は、セルの抽出や分布といった「何を・どのように」といった条件をまとめた「Ruleオブジェクト」を生成することです。

このRuleオブジェクトの生成方法は、所望する書式によって、使用するクラスやその引数指定が決まっており適切に選択する必要がありまます。

以降では、まず定義の手順フローの概要についてまとめ、そのあとに個別の条件書式について詳しく解説していきます。


次の図3は、条件付き書式をセルに適用するまでの手順(フロー)を示したものです。

このように、Ruleオブジェクトを生成する手順には3つのパターンに分類され、目的に応じて適切に選択する必要があります。

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

Ruleオブジェクトを定義するための3つのパターン

それでは、各定義パターンについて説明を加えます。(以降パターン〇という呼称で表現していますが、この呼び方は筆者の解説用の定義で公式な表現ではありませんので注意して下さい。)

パターン」は、Ruleクラス (後述)の引数に抽出条件を直接設定してオブジェクトを生成(コンストラクタ)するフロー

パターン」は、Builtin formats(データーバーなど)の抽出条件を定義するために用意された専用のクラスからオブジェクトを生成し、それをRuleクラスの引数に設定するフロー(いちばん煩雑)

パターン」は、パターンとパターンのハイブリッド型となります。

「抽出条件の定義オブジェクトの生成」と「Ruleオブジェクトの生成」をまとめて一つのクラスで対応することができます。ただ、対応可能なクラスは限定されます。(Builtin formats3種+その他)

以上、3通りのパターンがあります。


さて、「条件付き書式の分析項目がどのパターンによって定義できるか」使い分けの基準を図4にまとめました。

図4. Ruleオブジェクトの定義パターン一覧

データバーなどBuiltin formatsは、図4のように「パターン」と「パターン」の2通りの抽出条件の定義方法があります。それぞれで、必要とするクラスは異なり、Ruleオブジェクト の生成手順も違いがあります。

どちらも、できることは同じですので使いやすい方を選択すると良いかと思います。

Standard/Custom formatsに分類される分析項目は「パターン」で Ruleオブジェクト を定義します。一部、例外的に「セルの値」だけは「パターン3」の手順(専用クラス)もあります。

それでは次項より図3の手順に沿って、各クラス(オブジェクト)の具体的な扱い方について解説をしていきます。

スポンサーリンク

2. 「条件付き書式」の定義に必要なクラス

Python_基本文法_内包表記

本節では、「条件付き書式」を設定するために必要となるクラスについて解説をします。

「Ruleオブジェクト」を取得するには、書式のタイプに応じてさまざまなクラスが存在します。

2.1 抽出条件の値や属性を定義する(FormatObject)

パターン2では、「FormatObject」というデータの属性(値、型名、境界)を管理するオブジェクトを必要とします。この属性情報は、データの分布や可視化をする際の「判定しきい値」設定に使われます。

例えば、次の図5は「アイコンセット」の書式設定のダイアログを抜粋していますが、「67%以上であれば緑」、「33%以上は黄色」、それ以外は赤といった判定しきい値をFormatObjectクラスのオブジェクトで定義します。

条件付き書式_FormatObjectクラスの引数の概要
図5. FormatObjectで定義する属性

FormatObjectオブジェクトは、クラスに次の引数を指定して定義します。抽出する判定条件に必要なデータ数分のオブジェクトを取得します。図5の例でいえばアイコンの種類に応じて、2個であったり3個の場合があります。

FormatObjectオブジェクト

from openpyxl.formatting.rule import FormatObject

FormatObject(type, val, gte)


引数: type : 値(しきい値)のデータ種類を指定する

引数: val : 値(しきい値)を指定する (デフォルト:None)

引数: gte : 値(しきい値)を含む・含まないを指定する (デフォルト:None)

戻り値FormatObjectオブジェクト

各引数の設定値は、以下から選択します。

引数:type は、データの種類を選択します。引数:val は値を設定しますが、データの種類にmin/maxを指定した場合は、設定しません。また、引数:gte は、しきい値の境界を含む(True)、含まない(False)を指定します。

【引数名】引数設定オプション】
typeデータの種類: ‘percent’(%), ‘num’(数値), ‘min’(最大), ‘max’(最小), ‘formula’(書式), ‘percentile’(百分率)
val数値(整数、浮動小数): typeにmin(最小)/max(最大)を指定した場合は設定しない
gteしきい値の境界: True又はNone: ( >= ) / False: ( > )
typeにmin(最小)/max(最大)を指定した場合は設定しない
表1 FormatObjectクラスの引数指定

2.2 抽出条件を定義する(Ruleオブジェクト)

1.2節<図3,4>で説明したように、「条件付き書式」の条件の部分を定義したものが「Ruleオブジェクト」です。この「Ruleオブジェクト」を取得するには、書式タイプに応じて3つのパターンが存在するのでした。

ここで、各パターンごとに必要となるクラスを整理します。

パターン】(Standard/Custom [特定の文字列/日付/上位・下位])

Ruleクラスの引数に必要な情報を渡し、オブジェクトを生成します。目的の書式タイプに応じて引数の指定方法を使い分ける必要があります。(図6)引数指定の詳細については、次回の記事<連載6回>で解説しています。

openpyxl_Ruleオブジェクト(パターン1)で使用するクラス_rev0.2
図6. パターン1(Standard/Custom書式)で必要なRuleクラス

パターン(Built-in [データバー、アイコンセット、カラースケール])

パターン2は、Built-in型の書式タイプに特化した手順です。<2.1項>の「FormatObject(抽出データ属性)」を含めたタイプ別の「組込み書式オブジェクト 」をさらに、Ruleクラスの引数に渡してRuleオブジェクトを定義します。組込み書式には3つのクラスがあります。(図7)

openpyxl_Ruleオブジェクト(パターン2)で使用するクラス_rev0.3
図7. パターン2(Built-in書式)で必要なクラス

パターン(セルの値、データバー、アイコンセット、カラースケール)

パターン2は、3つのクラス(オブジェクト)を組合わせて、Ruleオブジェクトを取得する必要があり、プログラムが煩雑になりやすいとも言えます。openpyxlには、手続きを簡素化してひとつのクラスだけで、Ruleオブジェクトの生成までを行う方法も提供しています。

対応する書式タイプは、Built-in型の3種類と、Standard/Custom型に分類される「セルの値」です。(図8)

openpyxl_Ruleオブジェクト(パターン3)で使用するクラス_rev0.2
図8. パターン3で必要なクラス

パターン2,3どちらもできることは同じです。プログラマの嗜好にあわせて選択してください。

Ruleオブジェクトの定義に必要となる、各種クラスの詳細については、このあと第3節で解説します。

2.3 セルに書式付き書式を適用する

次に、<2.2項>で定義したRuleオブジェクトを実際にセルに適用します。

条件付き書式を適用した「セル範囲」は、ワークシート(Worksheetオブジェクト)ごとに管理されています。もちろん、同一シート内に複数もたせることができますので、ConditionalFormattingList というイテラブルなオブジェクトにまとめられています。

Ruleオブジェクトをセルに適用するには、ConditionalFormattingListオブジェクト配下の add()メソッド を次の書式のようにして使います。

ConditionalFormattingListオブジェクト

Worksheetオブジェクト.conditional_formattingプロパティ

戻り値: ConditionalFormattingListオブジェクト


ConditionalFormattingListオブジェクト.add(range_string, cfRule)

引数: range_string : 条件付き書式を適用するセル範囲を指定する

引数: cfRule : Ruleオブジェクトを設定する

引数:range_string に対象となるセル範囲を‘A1:C3’のように文字列で指定します。引数:cfRule には条件書式の定義(Ruleオブジェクト)を設定します。

以上が、条件付き書式の定義に必要なクラスの概要となります。次節より具体的なサンプルコードの実例を紹介します。

スポンサーリンク

3. 条件付き書式を定義する(Ruleオブジェクト)

<2.2項>では、抽出条件を定義する「Ruleオブジェクト」のクラス種類について概要を解説しましたが、本節は具体的なクラスの書式やサンプルコードによる実用例を紹介していきます。

Excelが備える条件書式タイプは多くありますが、本記事では組込み書式(Bultin formats)に分類される「データバー」「アイコンセット」「カラースケール」の適用例を示します。

3.1 データバー(DataBar)を条件定義する

データバー(DataBar)のRuleオブジェクトを定義する手順は2通りあります。1.2項(図3,図4)

FormatObject/DataBar/Ruleクラス を組合わせるパターンと、DataBarRuleクラス 単体で定義するパターンの2つです。

3.1.1 「FormatObject/DataBar/Ruleクラス」による定義(パターン2)

FormatObjectオブジェクトについては、<2.1項>で解説しましたので、本項では DataBarクラス の書式を解説します。

DataBarオブジェクト

from openpyxl.formatting.rule import DataBar

DataBar(minLength, maxLength, showValue, cfvo, color)


引数: minLength : バーの始まりの位置(左側)(デフォルト:None)

(0で左端、大きくなるほど右へ移動する)

引数: maxLength : バーの終わりの位置(右側)(デフォルト:None)

(100で右端、小さくなるほど左へ移動する)

引数: showValue : 値を表示する(デフォルト:None)

(False:None(表示しない) /  True(表示する))

引数: cfvo : FormatObjectオブジェクトを要素とするリストを設定する(デフォルト:None)

(データバーの始まり、終わりを示す値(2要素指定する))

引数: color : バー色の設定をする(例 Colorオブジェクト, ‘FFFFFF’)

(現状グラデーションのみ指定可能(単色は指定できない))

戻り値: DataBarオブジェクト

Excelの設定ウィンドウと各引数との対応関係は図9のとおりです。

データバー_DataBarクラス
図9. DataBarクラスの引数

引数:cfvo には、データの属性情報(FormatObject)<2.1項>を2つ用意しリストにして渡します。その他の引数については書式のとおりです。

補足として、図9や公式ドキュメントの下記コメントにもあるとおり、「バーの色は白色とのグラデーションしか選択できない」「枠線の設定はできない」「棒の方向は設定でいない」などといった制約があります。

その一方で、引数:minLength/maxLength でバーの左右端の長さを調整する設定が追加されているなどExcelの仕様とは異なる部分もあるようです。

Currently, openpyxl supports the DataBars as defined in the original specification. Borders and directions were added in a later extension.

openpyxl 公式ドキュメント(データーバーの書式について)

次に、Ruleオブジェクトを取得します。オブジェクトは、Ruleクラスを次の書式のように設定します。引数:type には、‘dataBar’を指定して書式タイプを「データバー」とします。

そして、引数:dataBar には、先の条件定義である DataBarオブジェクト を指定します。

Ruleオブジェクト

from openpyxl.formatting.rule import Rule

Rule(type, dataBar)


引数: type : 条件書式の種類は‘dataBar’に設定する

引数: dataBar : DataBarオブジェクトを設定する

戻り値: Ruleオブジェクト

SAMPLE (1)

それでは、これらのクラス(オブジェクト)の扱い方をサンプルコードで確認してみましょう。<List1>

コードの概要は、2つのセル範囲(“A1:A10”,”C1:F10”)に入力された数値データに対してデータバーを表示します。バーの基準は、各セル範囲の最小値を左端、最大値を右端になるようにします。

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

from openpyxl import load_workbook

# DataBarの書式設定に必要なクラスをインポート
from openpyxl.formatting.rule import DataBar, FormatObject
from openpyxl.formatting.rule import Rule

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


# [A]-----------------------------------------------------------------------------------
# FormatObjectオブジェクトとDataBarオブジェクトの生成

# データバーの基準データの属性(タイプ)を最小値・最大値とする
first = FormatObject(type='min')
second = FormatObject(type='max')

# DataBarクラスの引数dfvoに基準データをリスト形式で設定して、オブジェクトを生成する
data_bar = DataBar(cfvo=[first, second], color="638EC6", showValue=None, minLength=None, maxLength=None)

print(type(data_bar))
# >> <class 'openpyxl.formatting.rule.DataBar'>


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

# 引数typeに'dataBar'を指定して、引数dataBarにDataBarオブジェクトを指定する
rule = Rule(type='dataBar', dataBar=data_bar)

# [C]-----------------------------------------------------------------------------------
# 条件付き書式を適用する(Ruleオブジェクトを設定する)

ws.conditional_formatting.add("A1:A10", rule)
ws.conditional_formatting.add("C1:F10", rule)

wb.save('CF_DataBar_apply.xlsx')

それでは、コードのポイントを解説します。

15,16行目:【抽出データ属性の定義】

Formatobjectクラス引数:type に‘min’/’max’を指定してバーの左端が“最小値”、右端が“最大値”となるように抽出条件のしきい値属性を定義します。

なお、‘min’/’max’の場合は 引数:val引数:gte 省略します。

19行目:【組込み条件書式の定義】

DataBarクラス で、条件書式を定義します。引数:cfvo には先の「Formatobjectオブジェクト」を要素とするリストを渡します。引数:color には、バーの色情報をHex表記で指定します。その他、値の表示や、バーの長さ調整の指定は省略(‘None’[デフォルト])としています。

29行目:【Ruleオブジェクトの取得】

Ruleクラス引数:type を‘dataBar’とし、引数:dataBar には、先の「DataBarオブジェクト」を指定して、Ruleオブジェクトを取得します。

34,335行目:【Ruleオブジェクトの適用】

最後に、ConditionalFomattingListの add()メソッド で定義した書式を“A1:A10”, “C1:F10”へ適用します。

実行結果は、このあとに示す図11のようになります。

3.1.2 「DataBarRuleクラス」による定義(パターン3)

DataBarRuleクラス からRuleオブジェクトを取得する書式(パターン3)は次のとおりです。

バーの始点(左端)情報を引数:start_value 引数:start_type で、終点(右端)情報を 引数:end_value 引数:end_type に直接指定します。

DataBarRuleオブジェクト

from openpyxl.formatting.rule import DataBarRule

DataBarRule(start_type, start_value, end_type, end_value, color, minLength, maxLength)


引数: start_type :  バー始まりの値の種類(デフォルト:None)

(‘percent’(%), ‘num’(数値), ‘min’(最大), ‘max’(最小), ‘formula’(書式), ‘percentile’(百分率))

引数: start_value : バーの始まりの値(デフォルト:None)

(typeにmin(最小)/max(最大)を指定した場合は、設定しない)

引数: end_type :  バー終わりの値の種類(デフォルト:None)

(‘percent’(%), ‘num’(数値), ‘min’(最大), ‘max’(最小), ‘formula’(書式), ‘percentile’(百分率))

引数: end_value : バーの始まりの値(デフォルト:None)

(typeにmin(最小)/max(最大)を指定した場合は、設定しない)

引数: color :バー色の設定をする(デフォルト:None)

(例 Colorオブジェクト, ‘FFFFFF’)

引数: minLength :バーの始まりの位置(左側)(デフォルト:None)

(0で左端、大きくなるほど右へ移動する)

引数: maxLength :バーの終わりの位置(右側)(デフォルト:None)

(100で右端、小さくなるほど左へ移動する)

戻り値: Ruleオブジェクト

Excelの設定ウィンドウと各引数との対応関係は図10のとおりです。

データバー_DataBarRuleクラス
図10. DataBarRuleクラスの引数

SAMPLE (2)

それでは、DataBarRuleクラスの実例をサンプルコードで確認してみましょう。<List2>

コードの概要は、先の<List1>と同様ですが、DataBarRuleクラスを使う部分(14行目)のみが異なりますRuleオブジェクトの生成までを、1行で行えますのでコードがすっきりまとまります

from openpyxl import load_workbook

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


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

# [A]-----------------------------------------------------------------------------------
# Rule(DataBarRule)オブジェクトの生成

# 引数に直接データの属性(タイプ、値)を指定し、一度にRuleオブジェクトの生成まで行う
rule = DataBarRule(start_type='min', start_value=None, end_type='max', end_value=None,
                   color="FF638EC6", showValue="None", minLength=None, maxLength=None)

# [B]-----------------------------------------------------------------------------------
# 条件付き書式を適用する(Ruleオブジェクトを設定する)

ws.conditional_formatting.add("A1:A10", rule)
ws.conditional_formatting.add("C1:F10", rule)

wb.save('CF_DataBar_apply.xlsx')

List1、List2の実行結果は次のようになりました。最小値・最大値を基準としたデータバーが各セルに適用できました。

<List1>,<List2>の実行結果は以下からダウンロードできます。

DataBarクラスのサンプルプログラム_List1,2の実行結果
図11. List1/List2の実行結果

3.2 アイコンセット(IconSet) を条件定義する

アイコンセット(IconSet)のRuleオブジェクトの生成手順も同様です。

FormatObject/ IconSet /Ruleクラス を組合わせるパターンと、IconSetRuleクラス 単体で定義するパターンの2つがあります。

3.2.1 「FormatObject/ IconSet /Ruleクラス」による定義(パターン2)

IconSetオブジェクトは、次の書式のような引数指定で取得します。

引数:cfvo にデータの属性情報(FormatObject)を設定するのは先のデータバーと同様ですが、引数:iconSet で指定するアイコンの種類に応じて、必要なオブジェクトの数(リストの要素数)が異なります。例えば’3Symbols2’であれば3要素分、‘4ArrowsGray’であれば4要素分のFormatObjectを用意します。

IconSetオブジェクト

from openpyxl.formatting.rule import IconSet

IconSet(iconSet, cfvo, showValue, reverse)


引数: iconSet : アイコンの種類を指定する(デフォルト:None)

(‘3Symbols2’, ‘4ArrowsGray’, ‘4RedToBlack’ 他)

引数: cfvo: FormatObjectオブジェクトを要素とするリストを設定する(アイコンの階調区分)

(‘3Symbols2’であれば、3要素分 ‘4ArrowsGray’であれば、4要素分)

引数: showValue : 値を表示する(デフォルト:None)

(False:None(表示しない) /  True(表示する))

引数: reverse : アイコンの順番を反転する(デフォルト:None)

(False:None(表示しない) /  True(表示する))

戻り値: IconSetオブジェクト

Excelの設定ウィンドウと各引数との対応関係は図12のとおりです。

アイコンセット_IconSetクラス
図12. IconSetクラスの引数

引数:iconSet にはアイコンの種類を設定します。Excelには次のようなアイコンが用意されていますので、対応する既定定数を文字列(‘’で囲む)で指定します。

アイコンセット_アイコンの種類の選択
図13. 設定できるアイコンの種類の一覧

次に、Ruleオブジェクトを取得します。Ruleクラスの 引数:type には、’iconSet’‘を指定して書式タイプを「アイコンセット」とします。そして、引数:iconSet には、先ほど定義した「IconSetオブジェクト」を指定します。

Ruleオブジェクト

from openpyxl.formatting.rule import Rule

Rule(type, iconSet)


引数: type : 条件書式の種類は‘iconSet’に設定する

引数: iconSet: IconSetオブジェクトを設定する

戻り値: Ruleオブジェクト

SAMPLE (3)

それでは、これらのクラス(オブジェクト)の扱い方をサンプルコードで確認してみましょう。<List3>

コードの概要は、数値(割合)データが入ったセルの範囲に対して、“3TrafficLights1”(信号アイコン)を適用するというものです。

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

from openpyxl import load_workbook

# IconSetの書式設定に必要なクラスをインポート
from openpyxl.formatting.rule import IconSet, FormatObject
from openpyxl.formatting.rule import Rule

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


# [A]-----------------------------------------------------------------------------------
# FormatObjectオブジェクトとIconSetオブジェクトの生成

# アイコンセットの基準データの属性(タイプと値)をそれぞれ「%」とする
first = FormatObject(type='percent', val=0)
second = FormatObject(type='percent', val=33)
third = FormatObject(type='percent', val=67)

# IconSetクラスの引数iconSetにアイコンの種類(信号器), 引数cfvoに基準データのリストを設定してオブジェクトを生成する
iconset = IconSet(iconSet='3TrafficLights1', cfvo=[first, second, third], showValue=None, reverse=None)

print(type(iconset))
# 型の確認 >><class 'openpyxl.formatting.rule.IconSet'>


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

# 引数typeに'iconSet'を指定して、引数iconSetにIconSetオブジェクトを指定する
rule = Rule(type='iconSet', iconSet=iconset)

print(type(rule))
# 型の確認 >><class 'openpyxl.formatting.rule.Rule'>


# [C]-----------------------------------------------------------------------------------
# 条件付き書式を適用する(Ruleオブジェクトを設定する)

ws.conditional_formatting.add("A1:A10", rule)
ws.conditional_formatting.add("C1:F10", rule)

print(type(ws.conditional_formatting))
# 型の確認 >> <class 'openpyxl.formatting.formatting.ConditionalFormattingList'>

wb.save('CF_IconSet_apply.xlsx')

それでは、コードのポイントを解説します。

15~17行目:【基準データ属性を定義】

アイコンの切り替わりの基準となるデータ属性をFormatObjectクラスで定義します。ここでは、3つの基準値(データの種類[%]、しきい値[0,33,67])を定義しています。

20行目:【IconSetオブジェクトの定義】

IconSetオブジェクトを定義します。引数:iconset は“3TrafficLights1”(信号アイコン)とし、

引数:cfvo には、アイコンの切替えのしきい値(FormatObjectオブジェクト)を要素とするリストで渡します。

30行目:【Ruleオブジェクトの定義】

Ruleオブジェクトを定義します。引数:type には“iconSet”を、引数:iconSet には、先のIconSetオブジェクトを指定します。最後に、ConditionalFomattingListのadd()メソッドでRuleオブジェクトを “A1:A10”, “C1:F10”へ適用します。

実行結果は、このあとに示す図15のようになります。

3.2.2 「IconSetRuleクラス」による定義(パターン3)

IconSetRuleクラス からRuleオブジェクトを取得する書式(パターン3)は次のとおりです。

アイコンの種類は、引数:icon_style に文字列定数(図13)で指定します。データの種別は 引数:type に、値は 引数:values にリスト形式で必要な要素数分を指定します。

IconSetRuleオブジェクト

from openpyxl.formatting.rule import IconSetRule

IconSetRule(icon_style, type, values, showValue, reverse)


引数: icon_style : アイコンの種類を指定する(デフォルト:None)

(‘3Symbols2’, ‘4ArrowsGray’, ‘4RedToBlack’ 他)

引数: type : 値(しきい値)のデータの種類を指定する(デフォルト:None)

(‘percent’(%), ‘num’(数値), ‘min’(最大), ‘max’(最小), ‘formula’(書式), ‘percentile’(百分率))

引数: values : 値(しきい値)を要素とするリストを設定する アイコンの階調区分

(‘3Symbols2’であれば、3要素分 ‘4ArrowsGray’であれば、4要素分)

引数: showValue : 値を表示する(デフォルト:None)

(False:None(表示しない) /  True(表示する))

引数: reverse : アイコンの順番を反転する(デフォルト:None)

(False:None(反転しない) /  True(反転する)

戻り値: Ruleオブジェクト

Excelの設定ウィンドウと各引数との対応関係は図14のとおりです。

アイコンセット_IconSetRuleクラス
図14. IconSetRuleクラスの引数

SAMPLE (4)

それでは、IconSetRuleクラスの実例をサンプルコードで確認してみましょう。<List4>

コードの概要は、先の<List3>とほぼ同様ですが、IconSetRuleクラスを使う部分、(14行目)のみが異なります。Ruleオブジェクトの生成までを、1行で行えますので、コードがすっきりまとまります。

from openpyxl import load_workbook

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

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

# [A]-----------------------------------------------------------------------------------
# Rule(IconSetRule)オブジェクトの生成

# 引数に直接データの属性(タイプ、値)を指定し、一度にRuleオブジェクトの生成まで行う
# アイコンの種類は引数icon_styleで指定(3色信号機)する
rule = IconSetRule(icon_style='3TrafficLights1', type='percent', values=[0, 33, 67], showValue=None, reverse=None)

print(type(rule))
# 型の確認 >><class 'openpyxl.formatting.rule.Rule'>


# [B]-----------------------------------------------------------------------------------
# 条件付き書式を適用する(Ruleオブジェクトを設定する)

ws.conditional_formatting.add("A1:A10", rule)
ws.conditional_formatting.add("C1:F10", rule)

print(type(ws.conditional_formatting))
# >> <class 'openpyxl.formatting.formatting.ConditionalFormattingList'>

wb.save('CF_IconSet_apply.xlsx')

List3、List4の実行結果は次のようになりました。3つの割合値(%)をしきい値(0%, 33%, 67%)としたアイコンセットが適用されました。

<List3><List4>の実行結果は以下からダウンロードできます。

IconSetクラスのサンプルプログラム_List3,4の実行結果
図15. List3/List4の実行結果

3.3 カラースケール(ColorScale) を条件定義する

カラースケール(ColorScale)のRuleオブジェクトの生成手順も同様です。

FormatObject/ ColorScale /Ruleクラス を組合わせるパターンと、ColorScaleRuleクラス 単体で定義するパターンの2つがあります。

3.3.1 「FormatObject/ ColorScale/Ruleクラス」による定義(パターン2)

ColorScaleオブジェクトは、次の書式のような引数指定で取得します。

引数:cfvo にデータのしきい値情報(FormatObject)を設定するのはこれまでと同様です。引数:color には、FormatObjectと対となる色情報(Colorオブジェクトなど)をリスト形式(2要素もしくは3要素を始点、[中間]、終点の順番で)で渡します。

ColorScaleオブジェクト

from openpyxl.formatting.rule import ColorScale

ColorScale(cfvo, color)


引数: cfvo : FormatObjectオブジェクトを要素とするリストを設定する(デフォルト:None)

(カラースケールの始点、(中点)、終点を示す値(2または,3要素))

引数: color : スケールの色(例 Colorオブジェクト, ‘FFFFFF’)を要素とするリストを設定する

(カラースケールの始点、(中点)、終点をの色(2または,3要素))

戻り値: ColorScaleオブジェクト

Excelの設定ウィンドウと各引数との対応関係は図16のとおりです。

カラースケール_ColorScaleクラス
図16. ColorScalクラスの引数

次に、Ruleオブジェクトを取得します。Ruleクラスの 引数:type には、’colorScale‘を指定して書式タイプを「カラースケール」とします。そして、引数:colorScale には、先ほど定義した「IconSetオブジェクト」を指定します。

Ruleオブジェクト

from openpyxl.formatting.rule import Rule

Rule(type, colorScale)


引数: type : 条件書式の種類は‘colorScale’に設定する

引数: colorScale: ColorScaleオブジェクトを設定する

戻り値: Ruleオブジェクト

SAMPLE (5)

それでは、実際のプログラムでそれぞれのクラスの扱い方を確認してみましょう。<List5>

コードの概要は以下の通りです。

[A]では、基準データとなるFormatObjectオブジェクトを生成しています。

例では基準データを3つ用意し、始点・終点の種類は「”max”(最大)・”min”(最小)」とし、中間は「”num”(値)」指定としています。各データの色情報もそれに合わせて用意します。

次に作成したFormatObjectオブジェクトと色情報のリストをColorScaleクラスの引数に渡してオブジェクトを取得します。

そして[B]では、Ruleクラスの 引数:type に‘colorScaleを、引数:colorScale に[A]のColorScaleオブジェクトを指定してRuleオブジェクトを生成します。

最後に[C]で、定義した書式を指定セル範囲(A1:A10, C1:F10)へ適用します。

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

from openpyxl import load_workbook
from openpyxl.styles import Color

# ColorScaleの書式設定に必要なクラスをインポート
from openpyxl.formatting.rule import ColorScale, FormatObject
from openpyxl.formatting.rule import Rule

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

# [A]-----------------------------------------------------------------------------------
# FormatObjectオブジェクトとColorScaleオブジェクトの生成

# 基準データ(始点(最小値)と終点(最大値)、中間は値)のオブジェクト
first = FormatObject(type='min')
mid = FormatObject(type='num', val=50)
last = FormatObject(type='max')

# 始点、中間、終点の順番で色情報(Colorオブジェクト)をリスト形式で用意
colors = [Color('FFCC00'), Color('3366FF'), Color('99CC00')]

# ColorScaleクラスから、基準データと色情報を引数にしてオブジェクトを生成
color_scale = ColorScale(cfvo=[first, mid, last], color=colors)


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

# 引数typeに'colorScale'を指定して、引数colorScaleにColorScaleオブジェクトを指定する
rule = Rule(type='colorScale', colorScale=color_scale)


# [C]-----------------------------------------------------------------------------------
# 条件付き書式を適用する(Ruleオブジェクトを設定する)

ws.conditional_formatting.add("A1:A10", rule)
ws.conditional_formatting.add("C1:F10", rule)


wb.save('CF_ColorScale_apply.xlsx')

実行結果は、このあとに示す図18のようになります。

3.3.2 ColorScaleRuleクラスによる定義(パターン3)

ColorScaleRuleクラス からRuleオブジェクトを取得する書式は次のとおりです。データ属性(値・種類・色)を直接、引数:(min_*, mid_*, max_*) に指定して、オブジェクトを生成します。

ColorScaleRuleオブジェクト

from openpyxl.formatting.rule import ColorScaleRule

ColorScaleRule(start_type, start_value, start_color, mid_type, mid_value, mid_color, end_type, end_value, end_color)


引数: start_type : スケールの始点のデータ型を指定する

(‘percent’(%), ‘num’(数値), ‘min’(最大), ‘max’(最小), ‘formula’(書式), ‘percentile’(百分率))

引数: start_value : スケールの始点の値を設定する

(typeにmin(最小)/max(最大)を指定した場合は、設定しない)

引数: start_color : スケールの始点の色を設定する

(例)  Colorオブジェクト, ‘FFFFFF’)

引数: mid_* : スケールの中点のデータ型・値・色を設定する

引数: end_* : スケールの終点のデータ型・値・色を設定する

戻り値: Ruleオブジェクト

Excelの設定ウィンドウと各引数との対応関係は図17のとおりです。

カラースケール_ColorScaleRuleクラス
図17. ColorScalRuleクラスの引数とExcelの対応関係

SAMPLE (6)

それでは、実際のプログラムでクラスの使い方を確認しましょう。<List6>

先の<2.3.1>のパターン2とは異なり、[A]でColorScaleRuleクラスだけでRuleオブジェクトまでを一度に生成しています。そのため引数の数は増えますが、コード全体がすっきりとまとまります。

その他の処理の内容はList5と全く同じです。

from openpyxl import load_workbook

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

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

# [A]-----------------------------------------------------------------------------------
# Rule(ColorScaleRule)オブジェクトの生成

# 引数に直接データの属性(タイプ、値), 色情報を指定し、一度にRuleオブジェクトの生成まで行う
rule = ColorScaleRule(start_type='min', start_value=None, start_color='FFCC00',
                        mid_type='percentile', mid_value=50, mid_color='3366FF',
                        end_type='max', end_value=None, end_color='99CC00')

# [B]-----------------------------------------------------------------------------------
# 条件付き書式を適用する(Ruleオブジェクトを設定する)

ws.conditional_formatting.add("A1:A10", rule)
ws.conditional_formatting.add("C1:F10", rule)

wb.save('CF_ColorScale_apply.xlsx')

List5、List6の実行結果は次のようになりました。3つ基準(最大・50・最小)で色が変化するカラースケールが対象のセル範囲に適用できました。

<List5><List6>の実行結果は以下からダウンロードできます。

ColorScaleクラスのサンプルプログラム_List5,6の実行結果
図18. List5/List6の実行結果

4. まとめ

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

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

Excelにはデータ分析や統計に関連する機能たくさん備わっていますが、今回紹介した「セルの条件付き書式」はその一つです。openpyxにはそれらを操作するモジュールやクラスが提供されています。

ともにデータ分析を得意とする「Python」と「Excel」ですから、その相性は抜群ですExcelの分析ツールを使い慣れているという方も多くおられると思いますし、関連する情報なども豊富にあります。

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

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

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

➀. 条件付き書式を設定するには、「何を・どうする」といった条件となる部分をRuleオブジェクトとして定義する。

➁. Ruleオブジェクトを生成する手順は、目的の条件書式のタイプによって3つのパターンがある。

  • タイプ1:「Ruleクラス」の引数に抽出条件を直接指定してオブジェクトを取得
  • タイプ2:組込み条件書式特化クラスのオブジェクトと「Ruleクラス」を組合わせる
  • タイプ3:パターン1とパターン2のハイブリッド型

➂. 組込み書式に区分される(データバー、アイコンセット、カラースケール)には、それぞれに専用クラスが用意されている。さらに書式タイプごとに2通りのクラスの使い分けを必要とする。おすすめは、Ruleオブジェクトの定義までを、単一クラスで完結できる***Ruleクラス(ex. DataBarRule)の活用である。


さて、次回も引き続き「条件付き書式」の後編を紹介していきます。セルの強調表示ルール」と「上下限ルール」などについて解説していきますので、どうぞお楽しみに!

リンク先はこちらになります。↓

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

English is here >>

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