Pythonライブラリ(openpyxl)によるExcelの条件付き書式(データバー・アイコンセット・カラースケール)の設定【徹底解説】

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

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

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

これまでの連載の中で、openpyxlを使う上で必ず知っておかなければならない必須事項を取り上げてきました。以降の記事を読み進めていくにはある程度、前提知識を必要とします。あらかじめ、関連記事に目を通して頂けますと、解説の理解がより進むものと思われます。

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

セルの書式設定(フォントや罫線など装飾)に関しては<連載3回目>が参考になります。


さて、連載5回目となる本記事では「セルの条件付き書式」をopenpyxlを使って操作する方法を紹介します。

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

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

もちろん、openpyxlは「セルの条件付き書式」関連するモジュールやクラスを提供しています。

連載の目次(予定)は以下のようになっていますので参考にして下さい。

今回のテーマについても、説明が広範わたるため、記事内容を前編(本記事)と後編の2回に分けて解説していきます。

Part5_目次
図2 記事の解説予定範囲

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

➀. セルに条件付き書式を設定するための手順が体系的に把握できる。

➁. 条件定義(ルール定義)をするための各種クラスの仕様と使い方がわかる

➂. データバーやカラースケールなど使用頻度の高い条件書式の適用例が理解できる。

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

記事内で動作確認した開発環境とバージョン情報は次のとおりとなります。異なる環境・バージョンのライブラリを使う際はこの点、ご留意ください。

・Python 3.7.6(64bit)

・openpyxl 3.0.3

・JupyterNotebook 6.0.3

・Microsoft Office Personal 2016

スポンサーリンク

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

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

まず「条件付き書式とはどういったものなのか」という機能の概要を、次に大まかな「書式設定のフロー(手順)」を説明したのち、「条件定義に必要なクラスやオブジェクトの用法」を解説するといった順番で進めていきます。

1.1 条件付き書式とは

条件付き書式とはExcelが備えるデータ分析機能の一部です。ExcelのUIメニューからは「ホームタブ -> スタイルグループ -> 条件付き書式」で設定ウィンドウが表示されます。そこには図2のように様々な分析項目があらかじめ用意されています。

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

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

さらに、前者は特定の条件を満たすセルを抽出する「セルの強調表示」と、またあるセル範囲内における統計順位を求める「上位・下位ルール」といったように用途別でも分けられます。

(ライブラリの公式ドキュメントでは「Standard/Custom formats」に区分されます。)

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

本記事は、openpyxlの解説記事ですからExcelの詳細な機能説明は省略します。

Excelによるデータ分析手法などを扱う専門書を参考にして下さい。

1.2 書式設定の流れ

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

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

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

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


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

図からもわかるようにRuleオブジェクトを生成する手順は3系統に分類され、目的により適切に選択します。

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

<Ruleオブジェクト生成の3つのパターン>

それでは、各系統(以降パターン〇という呼称で表現)について説明します。

なお、この呼び方は筆者の解説用の定義で公式な表現ではありません。

パターン1」は、Ruleクラスの引数に条件を直接指定してコンストラクタする構造

パターン2」は、Builtin formats(3種類)専用のクラスからオブジェクトを生成し、それをRuleクラスの引数に指定する構造(いちばん煩雑)

パターン3」はパターン1とパターン2のハイブリッドタイプで、より手順を簡素化させたものでRuleオブジェクトの生成までを一括処理できる専用のクラスを使う構造

以上3つパターンがあります。

さて、条件付き書式の分析項目がどのパターンに該当するのか使い分けの基準を図4にまとめました。

Builtin formats(3種類)にはそれぞれ専用のクラスが用意されており、かつ2通り(クラスが2系統)のRuleオブジェクトの生成手順があります。(パターン2,パターン3)

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

Standard/Custom formatsについてはパターン1と3を使い分けることになります。特定の値をもつセルの抽出にはパターン3、それ以外の条件にはパターン1の手順を採用します。

図4 Ruleオブジェクトの作成パターン一覧

今回の記事では、Builtin formatsに分類される条件付き書式である「データバー」「アイコンセット」「カラースケール」の3種類について取り上げます。(パターン2,パターン3)

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

1.2.1 データの書式(FormatObject)を設定する

パターン2ではFormatObjectというデータの属性情報(値、型名、境界)をもつオブジェクトの定義を必要とします。

このデータの属性情報は、データの分布や可視化の「判定しきい値」設定に使われます。

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

条件付き書式_FormatObjectクラスの引数の概要
図5 FormatObjectの属性情報

FormatObjectオブジェクトは次の書式で、条件判定に必要なデータ数分(2又は3個)を取得します。

FormatObjectオブジェクト

from openpyxl.formatting.rule import FormatObject

FormatObject(type, val, gte)


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

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

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

戻り値:FormatObjectオブジェクト

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

引数gte(しきい値境界)はアイコンセットの書式でのみ有効となります。

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

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

1.2項(図3,4)でも述べたように、「条件付き書式」の具体的な条件を定義したものがRuleオブジェクトです。Ruleオブジェクトの生成には、パターン(3種類)に応じていくつかのクラスが提供されています。

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

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

Ruleクラスの引数に必要な情報を渡し、オブジェクトを生成します。単独クラスのみですが、定義する条件書式によって引数指定を使い分ける必要があります。

引数指定の詳細は次回の記事<連載6回目>で解説しています。

Ruleオブジェクト(パターン1)で使用するクラス
図6 パターン1で必要なクラス

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

パターン2はbuilt-in書式に特化した手順です。データ型、条件型とRule型の3つのクラスを組合わせてRuleオブジェクトを定義します。条件型にはタイプに応じてさらに3種類のクラスがあります。

Ruleオブジェクト(パターン2)で使用するクラス
図7 パターン2で必要なクラス

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

パターン2の手順をより簡素化して単独クラスでRuleオブジェクトまでを生成する。タイプに応じて4種類のクラスがあります。

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

それぞれのクラスの詳細書式と使い方については、このあと<第2節>で解説します。

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

条件付き書式は数多く用意されていますが、ルールを適用する箇所は共通となります。

セルへ書式を適用するには、Worksheetオブジェクトの ConditionalFormattingListコンテナ

Ruleオブジェクトを次の書式のようにして追加します。

見出し

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

戻り値: ConditionalFormattingListオブジェクト

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


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

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

引数range_string に書式を適用させるセル範囲を指定します。引数cfRule には設定する条件書式(1.2.2項のRuleオブジェクト)を設定します。

具体的なコード例については以降の節で紹介します。

2. 条件付き書式の定義(Ruleオブジェクトの生成)

Python_基本文法_内包表記

条件付き書式の定義をまとめたRuleオブジェクトの作成方法について以降で詳しく解説していきます。今回は、組込み書式である「データバー」「アイコンセット」「カラースケール」(Bultin formats)の各種クラスの詳細な仕様を示していきます。

そして実際に、どのようにして条件付き書式を定義し適用させるのかをサンプルコードで追いながら確認していきましょう。

2.1 データバー(DataBar) のRuleオブジェクト

データバー(DataBar)のRuleオブジェクトの生成手順は2通りあります。1.2項(図3,図4) DataBarクラスとRuleクラスのオブジェクトを組合わせた手順(パターン2)とDataBarRuleクラス単体で効率的にRuleオブジェクトを作る手順(パターン3)の2つです。

2.1.1 DataBarクラスとRuleクラスによる定義(パターン2)

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クラスの引数とExcelの対応関係

引数cfvoにデータの属性情報(FormatObject)<1.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オブジェクト

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

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

[A]では、基準データとなるFormatObjectオブジェクトを生成しています。例ではデータの種類は最大・最小としているため、値は省略しています。次にそれと色情報を引数としてDataBarクラスのオブジェクトを生成しています。

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

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

※(A)などの識別記号はコードのコメントに記しています。

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

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

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

DataBarRuleクラス からRuleオブジェクトを取得する書式は次のとおりです。

データの始点を「引数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クラスの引数とExcelの対応関係

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

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

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

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の実行結果は次のようになりました。最小値・最大値を基準としたデータバーが各セルに適用できました。

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

2.2 アイコンセット(IconSet) のRuleオブジェクト

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

IconSetクラスRuleクラス のオブジェクトを組合わせた手順(パターン2)とIconSetRuleクラス単体で効率的にRuleオブジェクトを作る手順(パターン3)の2つです。

2.2.1 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クラスの引数とExcelの対応関係

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

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

次にRuleクラスによるRuleオブジェクトの生成です。

次の書式のようにクラスの引数typeに’iconSet’を指定した上で、引数iconSetには先程のIconSetオブジェクトを設定します。

Ruleオブジェクト

from openpyxl.formatting.rule import Rule

Rule(type, iconSet)


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

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

戻り値: Ruleオブジェクト

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

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

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

例ではデータの種類を「”percent”(割合(%))」とし、アイコン切替えのしきい値となる値をそれぞれ指定しています。

次に作成したFormatObjectオブジェクトとアイコンの種類を対応する引数に渡してIconSetオブジェクトを生成しています。

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

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

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('Conditional_Formatting_apply.xlsx')

List3の実行結果はこのあと図15に示します。

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

IconSetRuleクラスからRuleオブジェクトを取得する書式は次のとおりです。

アイコンの種類を引数icon_styleに文字列定数で、データは種類を引数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クラスの引数とExcelの対応関係

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

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

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('Conditional_Formatting_apply.xlsx')

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

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

2.3 カラースケール(ColorScale) のRuleオブジェクト

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

ColorScaleクラスとRuleクラスのオブジェクトを組合わせた手順(パターン2)とColorScaleRuleクラス単体で効率的にRuleオブジェクトを作る手順(パターン3)の2つです。

2.3.1 ColorScaleクラスとRuleクラスによる定義(パターン2)

ColorScaleクラス を次の書式ような引数指定でコンストラクタ(初期化)してオブジェクトを取得します。

引数cfvoにデータの属性情報(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クラスの引数とExcelの対応関係

次にRuleクラスによるRuleオブジェクトの生成です。

次の書式のようにクラスの 引数typeに’colorScale’ を指定した上で、引数colorScaleには先程のColorScaleオブジェクトを設定します。

Ruleオブジェクト

from openpyxl.formatting.rule import Rule

Rule(type, colorScale)


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

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

戻り値: Ruleオブジェクト

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

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

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

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

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

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

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

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

2.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の対応関係

それでは、実際のプログラムでクラスの使い方を確認しましょう。<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・最小)で色が変化するカラースケールが対象のセル範囲に適用できました。

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

3. まとめ

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

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

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

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

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

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

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

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

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

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


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

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

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

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