0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@iceblue(リー ニック)

PythonでExcelワークシートに数式を書き込む：基本演算から配列計算まで

0
Posted at

日々の業務において、Excelの数式と関数はデータ処理の中核能力です。合計、平均、条件判定、線形回帰……Excelに手入力で数式を入力することはそれほど難しくありませんが、データがデータベースから来る場合、定期的にレポートを生成する必要がある場合、あるいは何百何千ものワークシートを一括処理する場合には、手動操作では現実的ではありません。Pythonでプログラム的に数式を書き込めば、Excelを生きたデータボードのように自動計算させることができ、人手を一切介しません。

PythonでExcelを操作する方法は数多くありますが、本記事では Free Spire.XLS for Python を使用します。コードで直接Excel数式を書き込むことができ、SUM、AVERAGE、COUNT、MAX、MIN、IF、SUBTOTALなどの一般的な関数に加え、名前付き範囲の参照や配列数式などの高度な機能にも対応しています。インストールは簡単です：

pip install spire.xls.free

1. データの準備

数式の例を分かりやすくするため、まず短いコードで売上データをワークシート「Sales Report」のB4:D7領域に書き込みます：

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.Version = ExcelVersion.Version2010
ws = workbook.Worksheets[0]
ws.Name = "Sales Report"

data = [
    ["North America", 120000, 135000, 148000],
    ["Europe",        98000,  112000, 125000],
    ["Asia Pacific",  87000,  95000,  108000],
    ["Latin America", 56000,  62000,  71000],
]

for row_idx, row_data in enumerate(data, start=4):
    for col_idx, value in enumerate(row_data, start=1):
        if col_idx == 1:
            ws.Range[row_idx, col_idx].Text = str(value)
        else:
            ws.Range[row_idx, col_idx].NumberValue = float(value)
            ws.Range[row_idx, col_idx].Style.NumberFormat = "$#,##0"

実行後、B4:D7には4地域の四半期売上データが格納されます。B4はNorth AmericaのQ1売上120,000、D7はLatin AmericaのQ3売上71,000です。以降の数式例はすべてこのデータに基づきます。

2. 基本的な集計数式

集計数式は最もよく使われるExcel数式のタイプで、数値範囲に対して統計計算を行います。Free Spire.XLS for Pythonは SUM、AVERAGE、MAX、MIN、COUNT などの一般的な集計関数に対応しており、Excelの画面での使い方とまったく同じです。ここでは集計数式を第1節のデータの下（第8〜12行）に書き込みます：

集計数式の書き込みの核心は Range.Formula プロパティです。これは = で始まる文字列を受け付け、形式はExcelで手入力する場合とまったく同じです：

# SUM：合計
ws.Range["B8"].Formula = "=SUM(B4:B7)"

# AVERAGE：平均
ws.Range["B9"].Formula = "=AVERAGE(B4:B7)"

# MAX：最大値
ws.Range["B10"].Formula = "=MAX(B4:B7)"

# MIN：最小値
ws.Range["B11"].Formula = "=MIN(B4:B7)"

# COUNT：数値の個数
ws.Range["B12"].Formula = "=COUNT(B4:B7)"

注意事項：

  • 数式文字列は = で始まる必要があります（Excelの規則です）
  • セル参照はA1形式を使用します（例：B4:B7）
  • 数値書式は別途設定する必要があります（例：.Style.NumberFormat = "$#,##0"）。数式自体には書式情報は含まれません
  • 数式を書き込んだだけでは計算結果はすぐには表示されません——workbook.CalculateAllValue() を呼び出すか、Excelで開くことで計算されます

3. 名前付き範囲と数式参照

名前付き範囲はExcelの重要な機能です。セルまたは範囲に意味のある名前を付け、その後は数式内でその名前を直接使用できます。これはセル参照をハードコードするよりも保守しやすく、特に大規模なワークブックで役立ちます。ここでは第1節のB4セル（North America Q1）に NorthAmerica_Q1 という名前を付け、第14行に名前付き範囲を使った予測数式を書き込みます。

Free Spire.XLSでは Workbook.NameRanges コレクションを使用して名前付き範囲を管理します：

# 名前付き範囲を作成：NorthAmerica_Q1 を B4 セルに割り当てる
nr = workbook.NameRanges.Add("NorthAmerica_Q1")
nr.RefersToRange = ws.Range["B4"]

# 数式内で名前付き範囲を直接使用
ws.Range["B14"].Formula = "=NorthAmerica_Q1*1.1"

このコードは2つのことを行います：

  1. NorthAmerica_Q1 という名前付き範囲を作成し、B4（North America Q1の売上データ120,000）を指す
  2. B14 セルに =NorthAmerica_Q1*1.1 という数式を書き込む。これはNorth America Q1データの10%増加分の予測値です

名前付き範囲を使う理由：

  • 可読性：=NorthAmerica_Q1*1.1 は =B4*1.1 よりも数式の意図を理解しやすい
  • 保守性：データの位置が変わった場合、名前付き範囲の RefersToRange を変更するだけで、その名前を参照するすべての数式が自動的に更新される
  • シート間参照：名前付き範囲は他のワークシートのセルを参照でき、数式内で Sheet2!A1 のような長い参照を書く必要がない

4. 条件数式：IF関数

IFはExcelで最もよく使われる条件関数で、条件に応じて異なる値を返します。構文は次のとおりです：

=IF(条件, 条件成立時の値, 条件不成立時の値)

PythonコードでIF数式を書き込む場合、数式自体に引用符と動的な行番号が含まれるため、f-stringでの連結がおすすめです。ここでは第1節の各地域について、各四半期の売上が目標に達しているかを判定し、結果を第17〜20行に書き込みます：

for i, row_data in enumerate(data, start=4):
    target_row = i + 13  # データは第4〜7行、IFの結果は第17〜20行に表示
    ws.Range[target_row, 2].Formula = f'=IF(B{i}>110000,"Above Target","Below Target")'
    ws.Range[target_row, 3].Formula = f'=IF(C{i}>120000,"Above Target","Below Target")'
    ws.Range[target_row, 4].Formula = f'=IF(D{i}>130000,"Above Target","Below Target")'

ここでは各地域の各四半期に異なる目標閾値を設定し（Q1 > 110000、Q2 > 120000、Q3 > 130000）、自動的に "Above Target" または "Below Target" をマークします。

引用符の処理に注意：Excel数式内の文字列用の引用符は、Pythonのf-string内では別の引用符で囲む必要があります。上記の例では数式の外側をシングルクォート、内部の文字列をダブルクォートで囲むことで、エスケープの問題を回避しています。

5. SUBTOTAL関数とグループ計算

SUBTOTAL関数は非表示行やフィルタリング後の行を無視できるため、データのグループ化やフィルタリングの場面で非常に便利です。最初の引数が実行する計算の種類を決定します。1-11はフィルターで非表示にされた行を無視し、101-111はさらに手動で非表示にした行や、他のSUBTOTAL数式にネスト参照されている行も無視します：

関数コード 機能
9 SUM（合計）
109 SUM（ネストされたSUBTOTALの結果を無視）
101 AVERAGE（平均）
102 COUNT（数値の個数）
103 COUNTA（空白以外のセルの個数）
104 MAX（最大値）
105 MIN（最小値）
107 STDEV（標本標準偏差）

ここでは第22行で第1節のデータに対してSUBTOTALによるグループ集計を行います：

ws.Range["B22"].Formula = "=SUBTOTAL(9,B4:B7)"       # 合計
ws.Range["C22"].Formula = "=SUBTOTAL(109,C4:C7)"      # 合計（ネストされたSUBTOTALの結果を無視）
ws.Range["D22"].Formula = "=SUBTOTAL(102,D4:D7)"      # 数値の個数

6. 配列数式：FormulaArray

通常の数式は一度に1つのセルの値だけを処理しますが、配列数式は配列（範囲）に対して同時に演算を実行でき、一度に複数の計算を完了できます。これはExcelの高度な計算のための重要なツールで、条件付き集計や複雑な統計などの場面に適しています。

Free Spire.XLS for Pythonでは FormulaArray プロパティで配列数式を書き込みます。使い方は Formula と似ていますが、数式の内容に範囲全体に対する演算を含めることができます。次の例では配列数式をB24:B26領域（SUBTOTALの例の下）に配置し、第1節のデータに対して配列演算を行います：

# B4:B7の各値×2の合計を求める（4つのセルをSUMで個別に書くのと等価）
ws.Range["B24"].FormulaArray = "=SUM(B4:B7*2)"

# 配列の条件付きカウント：B4:B7で90000より大きいセルの個数
ws.Range["B25"].FormulaArray = "=SUM(IF(B4:B7>90000,1,0))"

# 別の書き方：ブール値を算術演算に利用
ws.Range["B26"].FormulaArray = "=SUM((B4:B7>90000)*1)"

計算結果（CalculateAllValue() 後の確認）：

  • =SUM(B4:B7*2) → 722000（120000×2 + 98000×2 + 87000×2 + 56000×2）
  • =SUM(IF(B4:B7>90000,1,0)) → 2（120000、98000 の2つの値が90000を超える）
  • =SUM((B4:B7>90000)*1) → 2（上記と等価）

注意事項：

  • 配列数式も CalculateAllValue() で計算して初めて結果が得られます
  • FormulaArray は LINEST（線形回帰）など配列結果を返す関数にも対応しており、トレンド予測に適しています
  • 配列数式の読み取りには .FormulaArray プロパティを使用します。.Formula も数式文字列を返します

7. 数式と計算結果の読み取り

数式を書き込んだ後、コード内で数式やその計算結果を読み取る必要が出ることもあります。Free Spire.XLSは以下のAPIを提供します：

# セルが数式を含むかどうかを判定
if cell.HasFormula:
    print("This cell has a formula")

# 数式文字列を取得（例："=SUM(B4:B7)"）
formula_str = cell.Formula

# 数式の計算結果を取得（テキスト形式）
computed_value = cell.FormulaValue

# 数式の計算結果を取得（数値形式）
computed_number = cell.FormulaNumberValue

例：数式を含むすべてのセルを走査

以下のコードは、数式が書き込まれたワークブック（例えば第9節の完全な例で生成した FormulaDemo.xlsx）を読み込み、全数式を計算してから走査・表示します：

from spire.xls import *

workbook = Workbook()
workbook.LoadFromFile("FormulaDemo.xlsx")
workbook.CalculateAllValue()

ws = workbook.Worksheets[0]

# 指定範囲を走査して数式を検索・表示
for row in range(1, 25):
    for col in range(1, 5):
        cell = ws.Range[row, col]
        if cell.HasFormula:
            print(f"Cell ({row},{col}): Formula={cell.Formula}, Value={cell.FormulaValue}")

workbook.Dispose()

出力結果：

Cell (8,2): Formula==SUM(B4:B7), Value=361000
Cell (8,3): Formula==SUM(C4:C7), Value=404000
Cell (8,4): Formula==SUM(D4:D7), Value=452000
Cell (9,2): Formula==AVERAGE(B4:B7), Value=90250
...

注意：数式を書き込んだ直後でまだ計算していない場合、FormulaValue が空の値を返すことがあります。その場合は先に workbook.CalculateAllValue() を呼び出して計算する必要があります。

8. 主要クラスとメソッドの解説

Rangeクラス

Range はワークシート内の1つのセルまたはセル範囲を表し、数式操作の主要な入口です。

主なプロパティ：

プロパティ 説明
Formula セルの数式を取得・設定（文字列、= で始まる）
FormulaArray セルの配列数式を取得・設定
FormulaValue 数式の計算結果を取得（テキスト形式）
FormulaNumberValue 数式の計算結果を取得（数値形式）
HasFormula セルが数式を含むかどうかを判定（ブール値）
Text セルのテキスト値を取得・設定
NumberValue セルの数値を取得・設定

Workbookクラス

Workbook はワークブック全体の操作の入口であり、名前付き範囲の管理と数式の計算も担当します。

主なプロパティとメソッド：

プロパティ/メソッド 説明
NameRanges 名前付き範囲のコレクションを取得。追加・削除に使用
CalculateAllValue() ワークブック内のすべての数式の値を計算
LoadFromFile(filePath) 指定パスからExcelファイルを読み込む
SaveToFile(filePath, version) ワークブックをExcelファイルとして保存
Dispose() リソースを解放

NameRangeクラス

NameRange は名前付き範囲を表し、数式内で特定のセルや範囲を参照するために使用します。

主なプロパティ：

プロパティ 説明
Name 名前付き範囲の名前（例：NorthAmerica_Q1）
RefersToRange 名前付き範囲が参照するセル範囲

9. 完全な例：数式付き売上レポートをワンクリック生成

第2〜6節のテクニックを組み合わせれば、完全に実行可能な1つのスクリプトになります。タイトルとヘッダーの作成、売上データの書き込み、集計数式・名前付き範囲・IF・SUBTOTAL・配列数式の書き込み、最後に計算と保存までを行います。実行すれば数式付きのExcelレポートをすぐに得られます：

from spire.xls import *
from spire.xls.common import *

outputFile = "FormulaDemo.xlsx"

workbook = Workbook()
workbook.Version = ExcelVersion.Version2010
ws = workbook.Worksheets[0]
ws.Name = "Sales Report"

# タイトルとヘッダー
ws.Range["A1"].Text = "Sales Report with Formulas"
ws.Range["A1"].Style.Font.Size = 14
ws.Range["A1"].Style.Font.IsBold = True
ws.Range["A1"].Style.Color = Color.FromRgb(0, 70, 127)
ws.Range["A1"].Style.Font.Color = Color.get_White()
ws.Range["A1:D1"].Merge()

for col, header in enumerate(["Region", "Q1", "Q2", "Q3"], start=1):
    ws.Range[3, col].Text = header
    ws.Range[3, col].Style.Font.IsBold = True
    ws.Range[3, col].Style.Font.Color = Color.get_White()
    ws.Range[3, col].Style.Color = Color.FromRgb(0, 112, 192)
    ws.Range[3, col].Style.HorizontalAlignment = HorizontalAlignType.Center

# データ（第1節と同じ）
data = [
    ["North America", 120000, 135000, 148000],
    ["Europe",        98000,  112000, 125000],
    ["Asia Pacific",  87000,  95000,  108000],
    ["Latin America", 56000,  62000,  71000],
]
for row_idx, row_data in enumerate(data, start=4):
    for col_idx, value in enumerate(row_data, start=1):
        if col_idx == 1:
            ws.Range[row_idx, col_idx].Text = str(value)
        else:
            ws.Range[row_idx, col_idx].NumberValue = float(value)
            ws.Range[row_idx, col_idx].Style.NumberFormat = "$#,##0"
            ws.Range[row_idx, col_idx].Style.HorizontalAlignment = HorizontalAlignType.Right

# 集計数式（第2節）
funcs = [("Total", "SUM"), ("Average", "AVERAGE"), ("Max", "MAX"),
         ("Min", "MIN"), ("Count", "COUNT")]
for r, (label, fn) in enumerate(funcs, start=8):
    ws.Range[r, 1].Text = label
    ws.Range[r, 1].Style.Font.IsBold = True
    for col in range(2, 5):
        col_letter = chr(64 + col)
        ws.Range[r, col].Formula = f"={fn}({col_letter}4:{col_letter}7)"
        ws.Range[r, col].Style.NumberFormat = "0" if fn == "COUNT" else "$#,##0"
        if fn == "SUM":
            ws.Range[r, col].Style.Font.IsBold = True

# 名前付き範囲（第3節）
nr = workbook.NameRanges.Add("NorthAmerica_Q1")
nr.RefersToRange = ws.Range["B4"]
ws.Range["B14"].Formula = "=NorthAmerica_Q1*1.1"
ws.Range["B14"].Style.NumberFormat = "$#,##0"
ws.Range["A14"].Text = "Projected (10% growth)"
ws.Range["A14"].Style.Font.IsBold = True
ws.Range["A14:B14"].Merge()

# IF条件数式（第4節）
ws.Range["A16"].Text = "Performance"
ws.Range["A16"].Style.Font.IsBold = True
for col, h in zip(range(2, 5), ["Q1", "Q2", "Q3"]):
    ws.Range[16, col].Text = h
    ws.Range[16, col].Style.Font.IsBold = True
    ws.Range[16, col].Style.Color = Color.FromRgb(0, 112, 192)
    ws.Range[16, col].Style.Font.Color = Color.get_White()
for i, row_data in enumerate(data, start=4):
    target_row = i + 13  # データは第4〜7行、IFの結果は第17〜20行に表示
    ws.Range[target_row, 2].Formula = f'=IF(B{i}>110000,"Above Target","Below Target")'
    ws.Range[target_row, 3].Formula = f'=IF(C{i}>120000,"Above Target","Below Target")'
    ws.Range[target_row, 4].Formula = f'=IF(D{i}>130000,"Above Target","Below Target")'

# SUBTOTALグループ数式（第5節）
ws.Range["A22"].Text = "SUBTOTAL Test"
ws.Range["A22"].Style.Font.IsBold = True
ws.Range["B22"].Formula = "=SUBTOTAL(9,B4:B7)"
ws.Range["B22"].Style.NumberFormat = "$#,##0"
ws.Range["C22"].Formula = "=SUBTOTAL(109,C4:C7)"
ws.Range["C22"].Style.NumberFormat = "$#,##0"
ws.Range["D22"].Formula = "=SUBTOTAL(102,D4:D7)"
ws.Range["D22"].Style.NumberFormat = "$#,##0"

# 配列数式（第6節）
ws.Range["A24"].Text = "Array Formula Test"
ws.Range["A24"].Style.Font.IsBold = True
ws.Range["B24"].FormulaArray = "=SUM(B4:B7*2)"
ws.Range["B24"].Style.NumberFormat = "$#,##0"
ws.Range["B25"].FormulaArray = "=SUM(IF(B4:B7>90000,1,0))"
ws.Range["B26"].FormulaArray = "=SUM((B4:B7>90000)*1)"

# 列幅
ws.Columns[0].ColumnWidth = 20
ws.Columns[1].ColumnWidth = 12
ws.Columns[2].ColumnWidth = 14
ws.Columns[3].ColumnWidth = 14

# すべての数式を計算して保存
workbook.CalculateAllValue()
workbook.SaveToFile(outputFile, ExcelVersion.Version2010)
workbook.Dispose()
print(f"Report generated: {outputFile}")

実行後、Excelで開くと次のような結果になります（第2〜6節の各数式の説明と一対一で対応します）：

完全な例で生成した数式付き売上レポート

説明：

  • プロセス全体でExcelを開く必要がなく、データ作成から数式の書き込み、保存まで完全に自動化されます
  • CalculateAllValue() を保存前に呼び出すことで、ファイルを開いた時点で数値が計算済みになります
  • 集計数式は chr(64 + col) で列文字を生成し、ループでB/C/Dの3列を自動対応します
  • データソースをデータベース、CSV、APIから読み込んだ結果に置き換えても、スクリプトの構造は変わりません

まとめ

本記事の例を通じて、PythonでExcelワークシートに各種数式を書き込む方法を理解できました。基本的なSUM、AVERAGE、COUNTなどの集計関数から、名前付き範囲の参照、IF条件判定、SUBTOTALによるグループ計算、そして数式と計算結果の読み取りまで、Free Spire.XLS for Pythonは数式機能を完全にサポートしています。

Excelへの手動入力と比較した場合、Pythonによる方法には以下のメリットがあります：

  • 一括生成：一度に何百何千ものワークシートを処理でき、数式が自動的に適応します
  • データ駆動：数式の範囲がデータ行数に応じて動的に計算され、手動調整が不要です
  • プロセスの自動化：データベースからデータを読み込む→レポート生成→メール送信まで、人手を一切介しません
  • 書式の統一：すべてのレポートの数式ロジックとスタイルを完全に統一できます

さらに拡張することもできます。例えば、定時タスクと組み合わせて数式付きの販売週報を定期的に生成したり、顧客から提供されたテンプレートファイルに数式を書き込んで書式を維持したり、BIシステムと連携してダウンロード可能な分析レポートを自動生成したりできます。数式の自動化が必要な場面では、このPythonベースのソリューションが作業効率を大きく向上させるでしょう。

より高度な機能については、Free Spire.XLS for Python公式ドキュメントを参照してください。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?