Python?Excel自動化插入各種類型的公式和函數(shù)的完整指南

更新時(shí)間：2026年07月11日 08:29:57 作者：用戶835629078051

本文將介紹如何使用?Python?在?Excel?工作表中插入各種類型的公式和函數(shù),包括基本算術(shù)運(yùn)算、內(nèi)置函數(shù)、數(shù)組公式以及命名范圍的使用,這些技術(shù)適用于財(cái)務(wù)報(bào)表自動化、數(shù)據(jù)分析管道構(gòu)建以及批量數(shù)據(jù)處理等場景,有需要的可以了解下

在現(xiàn)代數(shù)據(jù)處理工作中，Excel 公式和函數(shù)是執(zhí)行計(jì)算、數(shù)據(jù)分析和業(yè)務(wù)邏輯的核心工具。然而，手動輸入和管理大量公式不僅耗時(shí)，還容易出錯。通過 Python 自動化 Excel 公式的插入和管理，開發(fā)者可以批量處理電子表格、動態(tài)生成計(jì)算邏輯，并確保數(shù)據(jù)處理的準(zhǔn)確性和一致性。

本文將介紹如何使用 Python 在 Excel 工作表中插入各種類型的公式和函數(shù)，包括基本算術(shù)運(yùn)算、內(nèi)置函數(shù)、數(shù)組公式以及命名范圍的使用。這些技術(shù)適用于財(cái)務(wù)報(bào)表自動化、數(shù)據(jù)分析管道構(gòu)建以及批量數(shù)據(jù)處理等場景。

環(huán)境準(zhǔn)備

首先，需要安裝 Spire.XLS for Python 庫：

pip install Spire.XLS

該庫提供了完整的 Excel 文件操作 API，支持公式插入、計(jì)算執(zhí)行和文件格式轉(zhuǎn)換等功能。

插入基本公式

Excel 公式以等號（=）開頭，可以包含常量、單元格引用、運(yùn)算符和函數(shù)調(diào)用。使用 Python 插入公式時(shí)，只需將公式字符串賦值給單元格的 Formula 屬性。

以下示例演示了如何在工作表中插入多種基本公式：

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

# 創(chuàng)建工作簿
workbook = Workbook()
sheet = workbook.Worksheets[0]

# 設(shè)置測試數(shù)據(jù)
sheet.Range["B2"].NumberValue = 7.3
sheet.Range["C2"].NumberValue = 5
sheet.Range["D2"].NumberValue = 8.2

# 插入字符串公式
sheet.Range["A4"].Text = '="hello"'
sheet.Range["B4"].Formula = '="hello"'

# 插入算術(shù)運(yùn)算公式
sheet.Range["A5"].Text = "=1+2+3+4+5-6-7+8-9"
sheet.Range["B5"].Formula = "=1+2+3+4+5-6-7+8-9"

# 插入乘法運(yùn)算
sheet.Range["A6"].Text = "=33*3/4-2+10"
sheet.Range["B6"].Formula = "=33*3/4-2+10"

# 引用其他單元格
sheet.Range["A7"].Text = "=Sheet1!$B$2"
sheet.Range["B7"].Formula = "=Sheet1!$B$2"

# 引用單元格區(qū)域并計(jì)算平均值
sheet.Range["A8"].Text = "=AVERAGE(Sheet1!$B$2:$D$2)"
sheet.Range["B8"].Formula = "=AVERAGE(Sheet1!$B$2:$D$2)"

# 保存文件
workbook.SaveToFile("BasicFormulas.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

在這個示例中，我們展示了：

字符串常量：使用雙引號包裹文本
算術(shù)運(yùn)算：支持加減乘除和括號優(yōu)先級
單元格引用：使用 $ 符號創(chuàng)建絕對引用
區(qū)域引用：使用冒號指定連續(xù)單元格范圍

使用內(nèi)置函數(shù)

Excel 提供了數(shù)百個內(nèi)置函數(shù)，涵蓋數(shù)學(xué)、統(tǒng)計(jì)、日期時(shí)間、邏輯判斷和文本處理等領(lǐng)域。通過 Python 可以靈活地插入這些函數(shù)。

數(shù)學(xué)和統(tǒng)計(jì)函數(shù)

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

workbook = Workbook()
sheet = workbook.Worksheets[0]

currentRow = 1

# COUNT 函數(shù) - 統(tǒng)計(jì)參數(shù)個數(shù)
sheet.Range[f"A{currentRow}"].Text = "=Count(3,5,8,10,2,34)"
sheet.Range[f"B{currentRow}"].Formula = "=Count(3,5,8,10,2,34)"
currentRow += 1

# SUM 函數(shù) - 求和
sheet.Range[f"A{currentRow}"].Text = "=SUM(18,29)"
sheet.Range[f"B{currentRow}"].Formula = "=SUM(18,29)"
currentRow += 1

# AVERAGE 函數(shù) - 平均值
sheet.Range[f"A{currentRow}"].Text = "=AVERAGE(12,45)"
sheet.Range[f"B{currentRow}"].Formula = "=AVERAGE(12,45)"
currentRow += 1

# MAX 和 MIN 函數(shù)
sheet.Range[f"A{currentRow}"].Text = "=MAX(10,30)"
sheet.Range[f"B{currentRow}"].Formula = "=MAX(10,30)"
currentRow += 1

sheet.Range[f"A{currentRow}"].Text = "=MIN(5,7)"
sheet.Range[f"B{currentRow}"].Formula = "=MIN(5,7)"
currentRow += 1

# ROUND 函數(shù) - 四舍五入
sheet.Range[f"A{currentRow}"].Text = "=ROUND(7,3)"
sheet.Range[f"B{currentRow}"].Formula = "=ROUND(7,3)"
currentRow += 1

# SQRT 函數(shù) - 平方根
sheet.Range[f"A{currentRow}"].Text = "=SQRT(40)"
sheet.Range[f"B{currentRow}"].Formula = "=SQRT(40)"
currentRow += 1

workbook.SaveToFile("MathFunctions.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

日期和時(shí)間函數(shù)

處理日期和時(shí)間數(shù)據(jù)是 Excel 自動化的常見需求。以下代碼展示了如何使用日期時(shí)間函數(shù)：

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

workbook = Workbook()
sheet = workbook.Worksheets[0]

# NOW 函數(shù) - 當(dāng)前日期時(shí)間
sheet.Range["A1"].Text = "=NOW()"
sheet.Range["B1"].Formula = "=NOW()"
sheet.Range["B1"].Style.NumberFormat = "yyyy-MM-DD HH:mm:ss"

# DATE 函數(shù) - 構(gòu)造日期
sheet.Range["A2"].Text = "=DATE(2024,1,15)"
sheet.Range["B2"].Formula = "=DATE(2024,1,15)"
sheet.Range["B2"].Style.NumberFormat = "yyyy-MM-DD"

# TIME 函數(shù) - 構(gòu)造時(shí)間
sheet.Range["A3"].Text = "=TIME(14,30,0)"
sheet.Range["B3"].Formula = "=TIME(14,30,0)"
sheet.Range["B3"].Style.NumberFormat = "HH:mm:ss"

# YEAR、MONTH、DAY 函數(shù) - 提取日期部分
sheet.Range["A4"].Text = "=YEAR(NOW())"
sheet.Range["B4"].Formula = "=YEAR(NOW())"

sheet.Range["A5"].Text = "=MONTH(NOW())"
sheet.Range["B5"].Formula = "=MONTH(NOW())"

sheet.Range["A6"].Text = "=DAY(NOW())"
sheet.Range["B6"].Formula = "=DAY(NOW())"

# HOUR、MINUTE、SECOND 函數(shù) - 提取時(shí)間部分
sheet.Range["A7"].Text = "=HOUR(NOW())"
sheet.Range["B7"].Formula = "=HOUR(NOW())"

sheet.Range["A8"].Text = "=MINUTE(NOW())"
sheet.Range["B8"].Formula = "=MINUTE(NOW())"

sheet.Range["A9"].Text = "=SECOND(NOW())"
sheet.Range["B9"].Formula = "=SECOND(NOW())"

workbook.SaveToFile("DateTimeFunctions.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

邏輯和條件函數(shù)

邏輯函數(shù)用于實(shí)現(xiàn)條件判斷和業(yè)務(wù)規(guī)則：

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

workbook = Workbook()
sheet = workbook.Worksheets[0]

# IF 函數(shù) - 條件判斷
sheet.Range["A1"].Text = "=IF(10>5, \"大于\", \"小于\")"
sheet.Range["B1"].Formula = "=IF(10>5, \"大于\", \"小于\")"

# AND 函數(shù) - 邏輯與
sheet.Range["A2"].Text = "=AND(TRUE, TRUE)"
sheet.Range["B2"].Formula = "=AND(TRUE, TRUE)"

# OR 函數(shù) - 邏輯或
sheet.Range["A3"].Text = "=OR(FALSE, TRUE)"
sheet.Range["B3"].Formula = "=OR(FALSE, TRUE)"

# NOT 函數(shù) - 邏輯非
sheet.Range["A4"].Text = "=NOT(FALSE)"
sheet.Range["B4"].Formula = "=NOT(FALSE)"

workbook.SaveToFile("LogicFunctions.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

文本處理函數(shù)

文本函數(shù)用于字符串操作和數(shù)據(jù)清洗：

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

workbook = Workbook()
sheet = workbook.Worksheets[0]

# LEN 函數(shù) - 字符串長度
sheet.Range["A1"].Text = '=LEN("Hello World")'
sheet.Range["B1"].Formula = '=LEN("Hello World")'

# MID 函數(shù) - 提取子字符串
sheet.Range["A2"].Text = '=MID("Hello World",7,5)'
sheet.Range["B2"].Formula = '=MID("Hello World",7,5)'

# VALUE 函數(shù) - 文本轉(zhuǎn)數(shù)值
sheet.Range["A3"].Text = '=VALUE("123")'
sheet.Range["B3"].Formula = '=VALUE("123")'

workbook.SaveToFile("TextFunctions.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

使用數(shù)組公式

數(shù)組公式可以對多個值執(zhí)行計(jì)算并返回單個結(jié)果或多個結(jié)果。這在執(zhí)行矩陣運(yùn)算和批量數(shù)據(jù)處理時(shí)非常有用。

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

workbook = Workbook()
sheet = workbook.Worksheets[0]

# 準(zhǔn)備數(shù)據(jù)
sheet.Range["A1"].NumberValue = 1
sheet.Range["A2"].NumberValue = 2
sheet.Range["A3"].NumberValue = 3

sheet.Range["B1"].NumberValue = 4
sheet.Range["B2"].NumberValue = 5
sheet.Range["B3"].NumberValue = 6

sheet.Range["C1"].NumberValue = 7
sheet.Range["C2"].NumberValue = 8
sheet.Range["C3"].NumberValue = 9

# 插入數(shù)組公式 - LINEST 函數(shù)執(zhí)行線性回歸分析
sheet.Range["A5:C6"].FormulaArray = "=LINEST(A1:A3,B1:C3,TRUE,TRUE)"

# 計(jì)算所有公式的值
workbook.CalculateAllValue()

workbook.SaveToFile("ArrayFormulas.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

關(guān)鍵點(diǎn)說明：

使用 FormulaArray 屬性而非 Formula 來插入數(shù)組公式
數(shù)組公式需要跨越多個單元格區(qū)域
調(diào)用 CalculateAllValue() 方法確保公式被正確計(jì)算

使用 SUBTOTAL 函數(shù)

SUBTOTAL 函數(shù)可以對數(shù)據(jù)列表執(zhí)行聚合計(jì)算，并且能夠忽略隱藏行，這在創(chuàng)建交互式報(bào)表時(shí)非常有用。

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

workbook = Workbook()
sheet = workbook.Worksheets[0]

# 準(zhǔn)備數(shù)據(jù)
for row in range(1, 4):
    sheet.Range[f"A{row}"].NumberValue = row
    sheet.Range[f"B{row}"].NumberValue = row + 3
    sheet.Range[f"C{row}"].NumberValue = row + 6

# 插入不同的 SUBTOTAL 函數(shù)
# 1 表示 AVERAGE
sheet.Range["A5"].Formula = "=SUBTOTAL(1,A1:C3)"

# 2 表示 COUNT
sheet.Range["B5"].Formula = "=SUBTOTAL(2,A1:C3)"

# 5 表示 MIN
sheet.Range["C5"].Formula = "=SUBTOTAL(5,A1:C3)"

# 計(jì)算公式
workbook.CalculateAllValue()

workbook.SaveToFile("SubtotalFormulas.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

SUBTOTAL 函數(shù)的第一個參數(shù)指定計(jì)算類型：

1: AVERAGE（平均值）
2: COUNT（計(jì)數(shù)）
3: COUNTA（非空計(jì)數(shù)）
4: MAX（最大值）
5: MIN（最小值）
9: SUM（求和）

使用命名范圍和公式

命名范圍可以提高公式的可讀性和可維護(hù)性。通過將復(fù)雜的單元格引用替換為有意義的名稱，可以使公式更易于理解。

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

workbook = Workbook()
sheet = workbook.Worksheets[0]

# 設(shè)置數(shù)據(jù)
sheet.Range["A1"].Value = "10"
sheet.Range["A2"].Value = "20"

# 創(chuàng)建命名范圍
namedRange = workbook.NameRanges.Add("SumRange")
namedRange.RefersToRange = sheet.Range["A1:A2"]

# 在公式中使用命名范圍
sheet.Range["C1"].Formula = "=SUM(SumRange)"

# 也可以直接定義命名公式
namedFormula = workbook.NameRanges.Add("TotalCalc")
namedFormula.NameLocal = "=SUM(A1+A2)"
sheet.Range["C2"].Formula = "TotalCalc"

workbook.SaveToFile("NamedRangeFormulas.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

這種方法的優(yōu)勢在于：

提高可讀性：=SUM(SumRange) 比 =SUM(A1:A2) 更清晰
便于維護(hù)：修改命名范圍的引用會自動更新所有使用該名稱的公式
減少錯誤：避免手動輸入復(fù)雜的單元格引用

實(shí)用技巧

格式化公式單元格

為了區(qū)分公式單元格和數(shù)據(jù)單元格，可以應(yīng)用特殊的格式：

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

workbook = Workbook()
sheet = workbook.Worksheets[0]

# 插入公式
sheet.Range["A1"].Formula = "=SUM(B1:B10)"

# 設(shè)置公式單元格背景色
sheet.Range["A1"].Style.Color = Color.get_LightYellow()

# 添加邊框
sheet.Range["A1"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin
sheet.Range["A1"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin

workbook.SaveToFile("FormattedFormula.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

強(qiáng)制計(jì)算公式值

在某些情況下，可能需要立即獲取公式的計(jì)算結(jié)果而不是保留公式本身：

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

workbook = Workbook()
sheet = workbook.Worksheets[0]

sheet.Range["A1"].NumberValue = 10
sheet.Range["A2"].NumberValue = 20
sheet.Range["A3"].Formula = "=A1+A2"

# 計(jì)算所有公式
workbook.CalculateAllValue()

# 此時(shí) A3 的值已經(jīng)是 30
result = sheet.Range["A3"].Value
print(f"計(jì)算結(jié)果: {result}")

workbook.SaveToFile("CalculatedFormula.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

處理跨工作表引用

當(dāng)公式需要引用其他工作表的數(shù)據(jù)時(shí)，使用工作表名稱加感嘆號的語法：

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

workbook = Workbook()

# 創(chuàng)建兩個工作表
sheet1 = workbook.Worksheets[0]
sheet1.Name = "Data"
sheet2 = workbook.Worksheets.Add("Calculation")

# 在第一個工作表中設(shè)置數(shù)據(jù)
sheet1.Range["A1"].NumberValue = 100
sheet1.Range["A2"].NumberValue = 200

# 在第二個工作表中引用第一個工作表的數(shù)據(jù)
sheet2.Range["A1"].Formula = "=Data!A1+Data!A2"

workbook.CalculateAllValue()
workbook.SaveToFile("CrossSheetFormula.xlsx", ExcelVersion.Version2010)
workbook.Dispose()