条件付き書式 例

メモ

セルの値に基づいてすべてのセルを書式設定

'colorScale'【カラー スケール】〔 目次 〕
ColorScaleRule【書式ルール生成 (カラー スケール)】 

from openpyxl import Workbook
from openpyxl.formatting import Rule
from openpyxl.formatting.rule import ColorScale, FormatObject
from openpyxl.formatting.rule import ColorScaleRule

# データ設定
data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
def set_data(row, col):
    for value in data:
        ws.cell(row, col, value)
        row += 1


# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active

# 通常生成 (2 色スケール)
COLOR2_1 = 'FFFF7128'
COLOR2_2 = 'FFE1EF9C'
ws['B2'] = '2 色スケール'
ws['B3'] = '通常生成'
colorscale = ColorScale(
    cfvo=[
        FormatObject('min'),
        FormatObject('max'),
    ],
    color=[COLOR2_1, COLOR2_2],
)
rule = Rule(
    type="colorScale",
    colorScale=colorscale,
)
ws.conditional_formatting.add('B4:B13', rule)
set_data(4, 2)

# 簡易生成 (2 色スケール)
ws['C3'] = '簡易生成'
rule = ColorScaleRule(
    start_type='min',
    start_color=COLOR2_1,
    end_type='max',
    end_color=COLOR2_2
)
ws.conditional_formatting.add('C4:C13', rule)
set_data(4, 3)

# 通常生成 (3 色スケール)
COLOR3_1 = 'FFF8696B'
COLOR3_2 = 'FFFFEB84'
COLOR3_3 = 'FF63BE7B'
ws['D2'] = '3 色スケール'
ws['D3'] = '通常生成'
colorscale = ColorScale(
    cfvo=(
        FormatObject('min'),
        FormatObject('percentile', 50),
        FormatObject('max'),
    ),
    color=(COLOR3_1, COLOR3_2, COLOR3_3),
)
rule = Rule(
    type="colorScale",
    colorScale=colorscale,
)
ws.conditional_formatting.add('D4:D13', rule)
set_data(4, 4)

# 簡易生成 (3 色スケール)
ws['E3'] = '簡易生成'
rule = ColorScaleRule(
    'min', None, COLOR3_1,
    'percentile', 50, COLOR3_2,
    'max', None, COLOR3_3,
)
ws.conditional_formatting.add('E4:E13', rule)
set_data(4, 5)

# ワークブック保存
wb.save(WORKBOOK)

条件付き書式【カラー スケール】のサンプル画像

'dataBar'【データ バー】〔 目次 〕
DataBarRule【書式ルール生成 (データ バー)】 

from openpyxl import Workbook
from openpyxl.formatting import Rule
from openpyxl.formatting.rule import DataBar, FormatObject
from openpyxl.formatting.rule import DataBarRule

# データ設定
data = [0, 1, 10, 3, 8, 5, 6, 7, 4, 9, 2]
def set_data(row, col):
    for value in data:
        ws.cell(row, col, value)
        row += 1


# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active
ws.column_dimensions['B'].width = 20
ws.column_dimensions['C'].width = 20

# 通常
ws['B2'] = '通常生成'
data_bar = DataBar(
    cfvo=[
        FormatObject('min'),
        FormatObject('max'),
    ],
    color='FF0000FF',
)
rule = Rule(
    type='dataBar',
    dataBar=data_bar,
)
ws.conditional_formatting.add('B4:B14', rule)
set_data(4, 2)

# 通常
ws['C3'] = 'minLength / maxLength'
data_bar = DataBar(
    cfvo=[
        FormatObject('min'),
        FormatObject('max'),
    ],
    color='FF0000FF',
    minLength=30,
    maxLength=70,
)
rule = Rule(
    type='dataBar',
    dataBar=data_bar,
)
ws.conditional_formatting.add('C4:C14', rule)
set_data(4, 3)


# 簡易生成
ws['B17'] = '簡易生成'
rule = DataBarRule(
    start_type='min',
    end_type='max',
    color='FF0000FF',
)
ws.conditional_formatting.add('B19:B29', rule)
set_data(19, 2)

# 簡易生成
ws['C18'] = 'minLength / maxLength'
rule = DataBarRule(
    start_type='min',
    end_type='max',
    color='FF0000FF',
    minLength=30,
    maxLength=70,
)
ws.conditional_formatting.add('C19:C29', rule)
set_data(19, 3)

# ワークブック保存
wb.save(WORKBOOK)

条件付き書式【データ バー】のサンプル画像

'iconSet'【アイコン セット】〔 目次 〕
IconSetRule【書式ルール生成 (アイコン セット)】 

from openpyxl import Workbook
from openpyxl.formatting import Rule
from openpyxl.formatting.rule import IconSet, FormatObject
from openpyxl.formatting.rule import IconSetRule

# データ設定
data = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
def set_data(row, col):
    for value in data:
        ws.cell(row, col, value)
        row += 1

# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active
ws.column_dimensions['B'].width = 15
ws.column_dimensions['C'].width = 15
ws.column_dimensions['D'].width = 15

# 通常生成 (３アイコン)
ws['B2'] ='通常生成'
ICON_SET_3_1 = '3TrafficLights1'
ws['B3'] = '３アイコン'
ws['B4'] = ICON_SET_3_1
sep_1 = FormatObject(type='percent', val=0)
sep_2 = FormatObject(type='percent', val=33)
sep_3 = FormatObject(type='percent', val=67)
iconset = IconSet(
    iconSet=ICON_SET_3_1,
    cfvo=[sep_1, sep_2, sep_3],
)
rule = Rule(
    type='iconSet',
    iconSet=iconset,
)
ws.conditional_formatting.add('B5:B14', rule)
set_data(5, 2)

# 通常生成 (４アイコン・値なし)
ICON_SET_4_1 = '4Rating'
ws['C3'] = '４アイコン・値なし'
ws['C4'] = ICON_SET_4_1
sep_1 = FormatObject(type='percent', val=0)
sep_2 = FormatObject(type='percent', val=25)
sep_3 = FormatObject(type='percent', val=50)
sep_4 = FormatObject(type='percent', val=75)
iconset = IconSet(
    iconSet=ICON_SET_4_1,
    showValue=False,
    cfvo=[sep_1, sep_2, sep_3, sep_4],
)
rule = Rule(
    type='iconSet',
    iconSet=iconset,
)
ws.conditional_formatting.add('C5:C14', rule)
set_data(5, 3)

# 通常生成 (５アイコン・逆順)
ICON_SET_5_1 = '5Arrows'
ws['D3'] = '５アイコン・逆順'
ws['D4'] = ICON_SET_5_1
sep_1 = FormatObject(type='percent', val=0)
sep_2 = FormatObject(type='percent', val=20)
sep_3 = FormatObject(type='percent', val=40)
sep_4 = FormatObject(type='percent', val=60)
sep_5 = FormatObject(type='percent', val=80)
iconset = IconSet(
    iconSet=ICON_SET_5_1,
    reverse=True,
    cfvo=[sep_1, sep_2, sep_3, sep_4, sep_5],
)
rule = Rule(
    type='iconSet',
    iconSet=iconset,
)
ws.conditional_formatting.add('D5:D14', rule)
set_data(5, 4)

# 簡易生成 (３アイコン)
ws['B16'] ='簡易生成'
ICON_SET_3_2 = '3Symbols'
ws['B17'] = '３アイコン'
ws['B18'] = ICON_SET_3_2
rule = IconSetRule(
    icon_style=ICON_SET_3_2,
    type='percent',
    values=[0, 33, 67],
)
ws.conditional_formatting.add('B19:B28', rule)
set_data(19, 2)

# 簡易生成 (４アイコン・値なし)
ICON_SET_4_2 = '4Arrows'
ws['C17'] = '４アイコン・値なし'
ws['C18'] = ICON_SET_4_2
rule = IconSetRule(
    icon_style=ICON_SET_4_2,
    type='percent',
    showValue=False,
    values=[0, 25, 50, 75],
)
ws.conditional_formatting.add('C19:C28', rule)
set_data(19, 3)

# 簡易生成 (５アイコン・逆順)
ICON_SET_5_2 = '5ArrowsGray'
ws['D17'] = '５アイコン・逆順'
ws['D18'] = ICON_SET_5_2
rule = IconSetRule(
    icon_style=ICON_SET_5_2,
    type='percent',
    reverse=True,
    values=[0, 20, 40, 60, 80],
)
ws.conditional_formatting.add('D19:D28', rule)
set_data(19, 4)

# ワークブック保存
wb.save(WORKBOOK)

条件付き書式【アイコン セット】のサンプル画像

指定の値を含むセルだけを書式設定

'cellIs'【セルの値】〔 目次 〕
CellIsRule【書式ルール生成 (セルの値)】 

from openpyxl import Workbook
from openpyxl.formatting.rule import Rule
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.styles import PatternFill, fills
from openpyxl.styles import Font

# データ設定
data = [1, 2, 3, 4, 5, 6, 7, 8]
def set_data(row, col):
    for value in data:
        ws.cell(row, col, value)
        row += 1


# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active
font = Font(bold=True, color='FFFFFFFF')
fill = PatternFill(fills.FILL_SOLID, 'FF0000FF', 'FF0000FF')
dxf = DifferentialStyle(
    font=font,
    fill=fill,
)
ws.column_dimensions['B'].width = 15
ws.column_dimensions['C'].width = 15
ws.column_dimensions['D'].width = 15
ws.column_dimensions['E'].width = 15

# 次の値の間
ws['B2'] = '次の値の間'
ws['B3'] = '通常生成'
ws['B4'] = 'between'
rule = Rule(
    type='cellIs',
    operator='between',
    formula=[3, 6],
    dxf=dxf,
)
ws.conditional_formatting.add('B5:B11', rule)
set_data(5, 2)

# 次の値の間以外
ws['C2'] = '次の値の間以外'
ws['C3'] = '簡易生成'
ws['C4'] = 'notBetween'
rule = CellIsRule(
    operator='notBetween',
    formula=[3, 6],
    font=font,
    fill=fill,
)
ws.conditional_formatting.add('C5:C12', rule)
set_data(5, 3)

# 次の値に等しい
ws['D2'] = '次の値に等しい'
ws['D3'] = '通常生成'
ws['D4'] = 'equal'
rule = Rule(
    type='cellIs',
    operator='equal',
    formula=[5],
    dxf=dxf,
)
ws.conditional_formatting.add('D5:D12', rule)
set_data(5, 4)

# 次の値に等しくない
ws['E2'] = '次の値に等しくない'
ws['E3'] = '簡易生成'
ws['E4'] = '!='
rule = CellIsRule(
    operator='!=',
    formula=[5],
    font=font,
    fill=fill,
)
ws.conditional_formatting.add('E5:E12', rule)
set_data(5, 5)

# 次の値より大きい
ws['B15'] = '次の値より大きい'
ws['B16'] = '通常生成'
ws['B17'] = 'greaterThan'
rule = Rule(
    type='cellIs',
    operator='greaterThan',
    formula=[5],
    dxf=dxf,
)
ws.conditional_formatting.add('B18:B25', rule)
set_data(18, 2)

# 次の値より小さい
ws['C15'] = '次の値より小さい'
ws['C16'] = '簡易生成'
ws['C17'] = '<'
rule = CellIsRule(
    operator='<',
    formula=[5],
    font=font,
    fill=fill,
)
ws.conditional_formatting.add('C18:C25', rule)
set_data(18, 3)

# 次の値以上
ws['D15'] = '次の値以上'
ws['D16'] = '通常生成'
ws['D17'] = 'greaterThanOrEqual'
rule = Rule(
    type='cellIs',
    operator='greaterThanOrEqual',
    formula=[5],
    dxf=dxf,
)
ws.conditional_formatting.add('D18:D25', rule)
set_data(18, 4)

# 次の値以下
ws['E15'] = '次の値以下'
ws['E16'] = '簡易生成'
ws['E17'] = '<='
rule = CellIsRule(
    operator='<=',
    formula=[5],
    font=font,
    fill=fill,
)
ws.conditional_formatting.add('E18:E25', rule)
set_data(18, 5)

# ワークブック保存
wb.save(WORKBOOK)

条件付き書式【セルの値】のサンプル画像

'containsText'【次の値を含む】〔 目次 〕
'notContainsText'【次の値を含まない】
'beginsWith'【次の値で始まる】
'endsWith'【次の値で終わる】
'containsBlanks'【空白】
'notContainsBlanks'【空白なし】
'containsErrors'【エラー】
'notContainsErrors'【エラーなし】 

from openpyxl import Workbook
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import Rule
from openpyxl.styles import PatternFill, fills
from openpyxl.styles import Font
from openpyxl.utils import get_column_letter

# データ設定
def set_data(row, col, data):
    for value in data:
        ws.cell(row, col, value)
        row += 1


# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active
font = Font(bold=True, color='FFFFFFFF')
fill = PatternFill(fills.FILL_SOLID, 'FF0000FF', 'FF0000FF')
dxf = DifferentialStyle(
    font=font,
    fill=fill,
)
for col in range(2, 11):
    ws.column_dimensions[get_column_letter(col)].width = 20
data_text = [
    'ABC', 'DEF', 'GHI',
    'ABC and DEF', 'ABC or DEF',
    'ABC and GHI', 'ABC or GHI',
    'DEF and GHI', 'DEF or GHI',
]
data_blank = [
    'ABC', 'DEF', 'ABC DEF',
    '',
    'GHI', 'JKL', 'GHI JKL',
    '',
    'MNO', 'PQR', 'MNO PQR',
]
data_error = [
    123, 'abc',
    '=123/0', '=UNKNOWN',
]

# 特定の文字列を含む
text = 'and'
ws['B2'] = '特定の文字列を含む'
ws['B3'] = text
rule = Rule(
    type='containsText',
    operator='containsText',
    text=text,
    dxf=dxf,
    formula=[f'NOT(ISERROR(SEARCH("{text}", B5)))'],
)
print(rule.formula)
# 出力：['NOT(ISERROR(SEARCH("and", B5)))']
ws.conditional_formatting.add('B5:B13', rule)
set_data(5, 2, data_text)

# 特定の文字列を含まない
text = 'and'
ws['C2'] = '特定の文字列を含まない'
ws['C3'] = text
rule = Rule(
    type='notContainsText',
    operator='notContains',
    text=text,
    dxf=dxf,
    formula=[f'ISERROR(SEARCH("{text}", C5))'],
)
print(rule.formula)
# 出力：['ISERROR(SEARCH("and", C5))']
ws.conditional_formatting.add('C5:C13', rule)
set_data(5, 3, data_text)

# 特定の文字列で始まる
text = 'ABC'
ws['D2'] = '特定の文字列で始まる'
ws['D3'] = text
rule = Rule(
    type='beginsWith',
    operator='beginsWith',
    text=text,
    dxf=dxf,
    formula=[f'LEFT(D5, {len(text)})="{text}"'],
)
print(rule.formula)
# 出力：['LEFT(D5, 3)="ABC"']
ws.conditional_formatting.add('D5:D13', rule)
set_data(5, 4, data_text)

# 特定の文字列で終わる
text = 'DEF'
ws['E2'] = '特定の文字列で終わる'
ws['E3'] = text
rule = Rule(
    type='endsWith',
    operator='endsWith',
    text=text,
    dxf=dxf,
    formula=[f'RIGHT(E5, {len(text)})="{text}"'],
)
print(rule.formula)
# 出力：['RIGHT(E5, 3)="DEF"']
ws.conditional_formatting.add('E5:E13', rule)
set_data(5, 5, data_text)

# 空白 (空)
ws['B16'] = '空白 (空)'
rule = Rule(
    type='containsBlanks',
    dxf=dxf,
    formula=['ISERROR(SEARCH("", B18))'],
)
ws.conditional_formatting.add('B18:B28', rule)
set_data(18, 2, data_blank)

# 空白なし (空以外)
ws['C16'] = '空白なし (空以外)'
rule = Rule(
    type='notContainsBlanks',
    dxf=dxf,
    formula=['NOT(ISERROR(SEARCH("", C18)))'],
)
ws.conditional_formatting.add('C18:C28', rule)
set_data(18, 3, data_blank)

# エラー
ws['D16'] = 'エラー'
rule = Rule(
    type='containsErrors',
    dxf=dxf,
    formula=['ISERROR(D18)'],
)
ws.conditional_formatting.add('D18:D21', rule)
set_data(18, 4, data_error)

# エラーなし
ws['E16'] = 'エラーなし'
rule = Rule(
    type='notContainsErrors',
    dxf=dxf,
    formula=['NOT(ISERROR(E18))'],
)
ws.conditional_formatting.add('E18:E21', rule)
set_data(18, 5, data_error)

# ワークブック保存
wb.save(WORKBOOK)

条件付き書式【次の値を含む 等】のサンプル画像

'timePeriod'【日付】 〔 目次 〕 

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

# データ設定
data = [
    '=TODAY()-8', '=TODAY()-7', '=TODAY()-6', '=TODAY()-5',
    '=TODAY()-4', '=TODAY()-3', '=TODAY()-2', '=TODAY()-1',
    '=TODAY()',
    '=TODAY()+1', '=TODAY()+2', '=TODAY()+3', '=TODAY()+4',
]
def set_data(row, col):
    for value in data:
        ws.cell(row, col, value)
        row += 1


# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active
font = Font(bold=True, color='FFFFFFFF')
fill_red = PatternFill(fills.FILL_SOLID, 'FFFF0000', 'FFFF0000')
fill_green = PatternFill(fills.FILL_SOLID, 'FF00FF00', 'FF00FF00')
fill_blue = PatternFill(fills.FILL_SOLID, 'FF0000FF', 'FF0000FF')
dxf_red = DifferentialStyle(
    font=font,
    fill=fill_red,
)
dxf_green = DifferentialStyle(
    font=font,
    fill=fill_green,
)
dxf_blue = DifferentialStyle(
    font=font,
    fill=fill_blue,
)

# 日付
ws.column_dimensions['B'].width = 15
ws['B2'] = '日付'
ws['B3'] = '【数式の表示】'
CELL_RANGE = 'B5:B17'

# 今日
rule_today = Rule(
    type='timePeriod',
    timePeriod='today',
    dxf=dxf_red,
)
ws.conditional_formatting.add(CELL_RANGE, rule_today)

# 昨日
rule_yesterday = Rule(
    type='timePeriod',
    timePeriod='yesterday',
    dxf=dxf_green,
)
ws.conditional_formatting.add(CELL_RANGE, rule_yesterday)

# 明日
rule_tomorrow = Rule(
    type='timePeriod',
    timePeriod='tomorrow',
    dxf=dxf_green,
)
ws.conditional_formatting.add(CELL_RANGE, rule_tomorrow)

# 過去 7日間
rule_last7Days = Rule(
    type='timePeriod',
    timePeriod='last7Days',
    dxf=dxf_blue,
)
ws.conditional_formatting.add(CELL_RANGE, rule_last7Days)

# データ設定
for row in ws[CELL_RANGE]:
    for cell in row:
        cell.number_format = 'yyyy/mm/dd'
set_data(5, 2)

# ワークブック保存
wb.save(WORKBOOK)

条件付き書式【日付】のサンプル画像

上位または下位に入る値だけを書式設定

'top10'【上位 / 下位】〔 目次 〕 

from openpyxl import Workbook
from openpyxl.formatting import Rule
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.styles import PatternFill, fills
from openpyxl.styles import Font

# データ設定
data = [1, 10, 3, 8, 5, 6, 7, 4, 9, 2]
def set_data(row, col):
    for value in data:
        ws.cell(row, col, value)
        row += 1


# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active

# 初期設定
fill = PatternFill(fills.FILL_SOLID, 'FF0000FF', 'FF0000FF')
font = Font(bold=True, color='FFFFFFFF')
dxf = DifferentialStyle(
    font=font,
    fill=fill,
)
ws['B2'] = '上位 / 下位 ルール'
ws.column_dimensions['B'].width = 15
ws.column_dimensions['C'].width = 15
ws.column_dimensions['D'].width = 15
ws.column_dimensions['E'].width = 15

# 上位/下位ルール (上位件数)
rank = 1
ws['B3'] = f'上位 ({rank} 件)'
rule = Rule(
    type='top10',
    rank=rank,
    dxf=dxf,
)
ws.conditional_formatting.add('B4:B13', rule)
set_data(4, 2)

# 上位/下位ルール (上位 パーセンテージ)
percentage = 20
ws['C3'] = f'上位 ({percentage} %)'
rule = Rule(
    type='top10',
    rank=percentage,
    percent=True,
    dxf=dxf,
)
ws.conditional_formatting.add('C4:C13', rule)
set_data(4, 3)

# 上位/下位ルール (下位 件数)
rank = 3
ws['D3'] = f'下位 ({rank} 件)'
rule = Rule(
    type='top10',
    rank=rank,
    bottom=True,
    dxf=dxf,
)
ws.conditional_formatting.add('D4:D13', rule)
set_data(4, 4)

# 上位/下位ルール (下位 パーセンテージ)
percentage = 40
ws['E3'] = f'下位 ({percentage} %)'
rule = Rule(
    type='top10',
    rank=percentage,
    percent=True,
    bottom=True,
    dxf=dxf,
)
ws.conditional_formatting.add('E4:E13', rule)
set_data(4, 5)

# ワークブック保存
wb.save(WORKBOOK)

条件付き書式【上位 / 下位】のサンプル画像

平均より上または下の値だけを書式設定

'aboveAverage'【平均より上 / 下】〔 目次 〕 

from openpyxl import Workbook
from openpyxl.formatting import Rule
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.styles import PatternFill, fills
from openpyxl.styles import Font
from openpyxl.utils import get_column_letter

# データ設定１
data_1 = [1, 8, 3, 6, 5, 4, 7, 2, 9]
def set_data_1(row, col):
    for value in data_1:
        ws.cell(row, col, value)
        row += 1


# データ設定２
data_2 = [
    [1, 2, 3, 4, 5, 6, 7, 8, 9, 20],
    [0, 2, 3, 4, 5, 6, 7, 8, 0, 20],
    [0, 0, 3, 4, 5, 6, 7, 0, 0, -10],
    [0, 0, 0, 4, 5, 6, 0, 0, 0, -10],
    [0, 0, 0, 0, 5, 0, 0, 0, 0],
]
def set_data_2(row, min, max):
    for data in data_2:
        col = 7
        for value in data:
            if min <= value <= max:
                ws.cell(row, col, value)
            col += 1
        row += 1


# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active

# 初期設定
fill = PatternFill(fills.FILL_SOLID, 'FF0000FF', 'FF0000FF')
font = Font(bold=True, color='FFFFFFFF')
dxf = DifferentialStyle(
    font=font,
    fill=fill,
)
average = sum(data_1) / len(data_1)
ws['B2'] = f'平均より上 / 下 ルール (平均：{average})'
ws.column_dimensions['A'].width = 2
ws.column_dimensions['B'].width = 10
ws.column_dimensions['C'].width = 10
ws.column_dimensions['D'].width = 10
ws.column_dimensions['E'].width = 10

# 平均より上
ws['B4'] = '平均より上'
rule = Rule(
    type='aboveAverage',
    dxf=dxf,
)
ws.conditional_formatting.add('B4:B12', rule)
set_data_1(5, 2)

# 平均より下
ws['C4'] = '平均より下'
rule = Rule(
    type='aboveAverage',
    aboveAverage=False,
    dxf=dxf,
)
ws.conditional_formatting.add('C4:C12', rule)
set_data_1(5, 3)

# 平均以上
ws['D4'] = '平均以上'
rule = Rule(
    type='aboveAverage',
    equalAverage=True,
    dxf=dxf,
)
ws.conditional_formatting.add('D4:D12', rule)
set_data_1(5, 4)

# 平均以下
ws['E4'] = '平均以下'
rule = Rule(
    type='aboveAverage',
    aboveAverage=False,
    equalAverage=True,
    dxf=dxf,
)
ws.conditional_formatting.add('E4:E12', rule)
set_data_1(5, 5)

# 標準偏差
ws['G2'] = '標準偏差'
for col in range(7, 16):
    ws.column_dimensions[get_column_letter(col)].width = 3
ws.column_dimensions['P'].width = 5

# 平均値より 1 標準偏差上
ws['G4'] = '平均値より 1 標準偏差上'
rule = Rule(
    type='aboveAverage',
    stdDev=1,
    dxf=dxf,
)
ws.conditional_formatting.add('G5:P9', rule)
row = 5
set_data_2(row, 1, 9)

# 平均値より 1 標準偏差下
ws['G11'] = '平均値より 1 標準偏差下'
rule = Rule(
    type='aboveAverage',
    aboveAverage=False,
    stdDev=1,
    dxf=dxf,
)
ws.conditional_formatting.add('G12:P16', rule)
row += 7
set_data_2(row, 1, 9)

# 平均値より 2 標準偏差上
ws['G18'] = '平均値より 2 標準偏差上'
rule = Rule(
    type='aboveAverage',
    stdDev=2,
    dxf=dxf,
)
ws.conditional_formatting.add('G19:P23', rule)
row += 7
set_data_2(row, 1, 9)

# 平均値より 2 標準偏差下
ws['G25'] = '平均値より 2 標準偏差下'
rule = Rule(
    type='aboveAverage',
    aboveAverage=False,
    stdDev=2,
    dxf=dxf,
)
ws.conditional_formatting.add('G26:P30', rule)
row += 7
set_data_2(row, 1, 9)

# 平均値より 3 標準偏差上
ws['G32'] = '平均値より 3 標準偏差上'
rule = Rule(
    type='aboveAverage',
    stdDev=3,
    dxf=dxf,
)
ws.conditional_formatting.add('G33:P37', rule)
row += 7
set_data_2(row, 1, 99)

# 平均値より 3 標準偏差下
ws['G39'] = '平均値より 3 標準偏差下'
rule = Rule(
    type='aboveAverage',
    aboveAverage=False,
    stdDev=3,
    dxf=dxf,
)
ws.conditional_formatting.add('G40:P44', rule)
row += 7
set_data_2(row, -99, 9)

# ワークブック保存
wb.save(WORKBOOK)

条件付き書式【平均より上 / 下】のサンプル画像

一意の値または重複する値だけを書式設定

'duplicateValues'【重複】〔 目次 〕
'uniqueValues'【一意】 

from openpyxl import Workbook
from openpyxl.formatting import Rule
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.styles import PatternFill, fills
from openpyxl.styles import Font

# データ設定
data = [1, 2, 3, 4, 5, 6, 2, 4, 6, '重複の値', '一意の値', '重複の値']
def set_data(row, col):
    for value in data:
        ws.cell(row, col, value)
        row += 1


# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active

# 初期設定
fill = PatternFill(fills.FILL_SOLID, 'FF0000FF', 'FF0000FF')
font = Font(bold=True, color='FFFFFFFF')
dxf = DifferentialStyle(
    font=font,
    fill=fill,
)
ws['B2'] = '一意 / 重複'
ws.column_dimensions['B'].width = 10
ws.column_dimensions['C'].width = 10

# 重複
ws['B3'] = '重複'
rule = Rule(
    type='duplicateValues',
    dxf=dxf,
)
ws.conditional_formatting.add('B5:B16', rule)
set_data(5, 2)

# 一意
ws['C3'] = '一意'
rule = Rule(
    type='uniqueValues',
    dxf=dxf,
)
ws.conditional_formatting.add('C5:C16', rule)
set_data(5, 3)

# ワークブック保存
wb.save(WORKBOOK)

条件付き書式【一意 / 重複】のサンプル画像

数式を使用して、書式設定するセルを決定

'expression'【数式】〔 目次 〕
FormulaRule【書式ルール生成 (数式)】 

from openpyxl import Workbook
from openpyxl.formatting import Rule
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.styles import PatternFill, fills
from openpyxl.styles import Font

# データ設定
data = [1, 12, 3, 10, 5, 8, 7, 6, 9, 4, 11, 2]
def set_data(row, col):
    for value in data:
        ws.cell(row, col, value)
        row += 1


# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active

# 初期設定
fill = PatternFill(fills.FILL_SOLID, 'FF0000FF', 'FF0000FF')
font = Font(bold=True, color='FFFFFFFF')
dxf = DifferentialStyle(
    font=font,
    fill=fill,
)
ws['B2'] = '数式'

# 通常生成
ws['B3'] = '通常生成'
ws['B4'] = '奇数行'
rule = Rule(
    type='expression',
    formula=['MOD(ROW(),2)=1'],
    dxf=dxf,
)
ws.conditional_formatting.add('B5:B16', rule)
set_data(5, 2)
ws['C4'] = '３の倍数'
rule = Rule(
    type='expression',
    formula=['MOD(C5,3)=0'],
    dxf=dxf,
)
ws.conditional_formatting.add('C5:C16', rule)
set_data(5, 3)

# 簡易生成
ws['D3'] = '簡易生成'
ws['D4'] = '偶数行'
rule = FormulaRule(
    formula=['MOD(ROW(),2)=0'],
    font=font,
    fill=fill,
)
ws.conditional_formatting.add('D5:D16', rule)
set_data(5, 4)
ws['E4'] = '４の倍数'
rule = FormulaRule(
    formula=['MOD(E5,4)=0'],
    font=font,
    fill=fill,
)
ws.conditional_formatting.add('E5:E16', rule)
set_data(5, 5)

# ワークブック保存
wb.save(WORKBOOK)

条件付き書式【数式】のサンプル画像