如何在工作表中編輯公式

查克尼思·賓

2023年10月17日

已更新 2024年12月10日

Translated

View the article in English

Excel 公式是一種以等號 (=) 開頭的表達式，用於根據單元格值進行數學計算、資料處理和結果推導。它可能包含算術運算、函數、單元格引用、常量和邏輯操作。公式允許在單元格值變更時進行動態更新，使Excel成為自動化任務和數據分析的多功能工具。

IronXL 支援在 Excel 檔案中編輯現有公式，擷取公式的結果，並強制工作簿重新評估。這確保每個公式都將重新計算以獲得準確結果。 IronXL 支援超過 165 種公式。

如何在工作表中編輯公式

下載 C# 函式庫以編輯工作表中的公式
建立一個新的 Excel 檔案或導入現有檔案
編輯或設定公式屬性以將公式指定到儲存格
透過存取儲存格的FormattedCellValue屬性來檢索結果值
匯出編輯過的Excel文件

開始使用IronXL

立即在您的專案中使用IronXL，並享受免費試用。

第一步：

編輯公式範例

若要編輯或設置公式，請訪問Formula屬性。首先，選擇一個範圍或單元格，然後訪問並設置公式屬性。 Formula 屬性是一個同時具有 get 和 set 功能的屬性，如果有的話，它會返回公式字符串。調用EvaluateAll方法重新評估整個工作簿，以確保計算準確。

:path=/static-assets/excel/content-code-examples/how-to/edit-formulas-edit-formulas.cs

using IronXL;

// Load workbook
WorkBook workBook = WorkBook.Load("Book1.xlsx");

// Select worksheet
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Edit or Set formula
workSheet["A4"].Formula = "=SUM(A1,A3)";

// Reevaluate the entire workbook
workBook.EvaluateAll();

Imports IronXL

' Load workbook
Private workBook As WorkBook = WorkBook.Load("Book1.xlsx")

' Select worksheet
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Edit or Set formula
Private workSheet("A4").Formula = "=SUM(A1,A3)"

' Reevaluate the entire workbook
workBook.EvaluateAll()

$vbLabelText $csharpLabel

從公式檢索結果

雖然有時可以從選定範圍和單元格的Value屬性中獲取結果，但建議從單元格的FormattedCellValue屬性中獲取結果，以獲得公式的更準確結果。在選定的範圍內，您可以通過調用First方法來訪問單元格。此方法將選擇列表的第一個元素，在我們的例子中，是單元格“A4”。從那裡，您可以訪問FormattedCellValue屬性。

:path=/static-assets/excel/content-code-examples/how-to/edit-formulas-retrieve-formula-value.cs

using IronXL;
using System;
using System.Linq;

// Load workbook
WorkBook workBook = WorkBook.Load("Book1.xlsx");

// Select worksheet
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Retrieve the result value
string value = workSheet["A4"].First().FormattedCellValue;

// Print the result to console
Console.WriteLine(value);

Imports IronXL
Imports System
Imports System.Linq

' Load workbook
Private workBook As WorkBook = WorkBook.Load("Book1.xlsx")

' Select worksheet
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Retrieve the result value
Private value As String = workSheet("A4").First().FormattedCellValue

' Print the result to console
Console.WriteLine(value)

$vbLabelText $csharpLabel

支持的公式

Excel 包含 450 多個公式，可用於各種計算目的。 IronXL 支援約 165 種最常用的公式。請檢查以下支持的公式：

Formula Name	Description
ABS	Returns the absolute value of a number, disregarding its sign.
INT	Rounds a number down to the nearest integer.
COUNT	Counts the number of cells that contain numbers within a specified range.
IF	Performs a conditional test and returns one value if the condition is true and another if it's false.
SUM	Adds up a range of numbers.
AVERAGE	Calculates the average of a range of numbers.
MIN	Returns the minimum value from a set of numbers.
MAX	Returns the maximum value from a set of numbers.
ROW	Returns the row number of a cell reference.
COLUMN	Returns the column number of a cell reference.
NA	Represents an error value for "Not Available" or missing data.
NPV	Calculates the Net Present Value of a series of cash flows at a specified discount rate.
STDEV	Calculates the standard deviation of a set of numbers.
SIGN	Returns the sign of a number as -1 for negative, 0 for zero, or 1 for positive.
ROUND	Rounds a number to a specified number of decimal places.
LOOKUP	Searches for a value in a range and returns a corresponding value from another range.
INDEX	Returns the value of a cell in a specified row and column of a given range.
REPT	Repeats a text string a specified number of times.
MID	Extracts a portion of text from a given text string based on a specified starting position and length.
LEN	Returns the number of characters in a text string.
VALUE	Converts a text string that represents a number to an actual number.
TRUE	Represents the logical value for "True."
FALSE	Represents the logical value for "False."
AND	Checks if all specified conditions are true and returns "True" if they are, and "False" otherwise.
OR	Checks if at least one of the specified conditions is true and returns "True" if it is, and "False" otherwise.
NOT	Inverts the logical value of a condition, turning "True" into "False" and vice versa.
MOD	Returns the remainder when one number is divided by another.
DMIN	Extracts the minimum value from a database based on specified criteria.
VAR	Calculates the variance of a set of numbers.
TEXT	Converts a number to text using a specified format.
PV	Calculates the present value of an investment or loan based on a series of cash flows and a discount rate.
FV (Future Value)	Calculates the future value of an investment or loan based on periodic payments and a specified interest rate.
NPER (Number of Periods)	Determines the number of payment periods required to reach a certain financial goal, given regular payments and an interest rate.
PMT (Payment)	Calculates the periodic payment needed to pay off a loan or investment, including principal and interest.
RATE (Interest Rate)	Calculates the interest rate required to reach a financial goal with a series of periodic payments.
MIRR (Modified Internal Rate of Return)	Calculates the internal rate of return for a series of cash flows, addressing multiple reinvestment and financing rates.
IRR (Internal Rate of Return)	Calculates the internal rate of return for a series of cash flows, indicating the rate at which an investment breaks even.
RAND	Generates a random decimal number between 0 and 1.
MATCH	Searches for a specified value in a range and returns the relative position of the item found.
DATE	Creates a date value by specifying the year, month, and day.
TIME	Creates a time value by specifying the hour, minute, and second.
DAY	Extracts the day from a given date.
MONTH	Extracts the month from a given date.
YEAR	Extracts the year from a given date.
WEEKDAY	Returns the day of the week for a specified date.
HOUR	Extracts the hour from a given time.
MINUTE	Extracts the minute from a given time.
SECOND	Extracts the second from a given time.
NOW	Returns the current date and time.
AREAS	Counts the number of individual ranges within a reference.
ROWS	Counts the number of rows in a specified range.
COLUMNS	Counts the number of columns in a specified range.
OFFSET	Returns a reference offset from a specified cell by a certain number of rows and columns.
SEARCH	Searches for a substring within a text string and returns its position.
TRANSPOSE	Transposes the rows and columns of a range.
ATAN2	Calculates the arctangent of a specified x and y coordinate.
ASIN	Calculates the arcsine of a specified value.
ACOS	Calculates the arccosine of a specified value.
CHOOSE	Returns a value from a list of values based on a specified position.
HLOOKUP	Searches for a value in the top row of a table or range and returns a value in the same column from a specified row.
VLOOKUP	Searches for a value in the first column of a table or range and returns a value in the same row from a specified column.
ISREF	Checks if a value is a reference and returns "True" if it is, or "False" if it's not.
LOG	Calculates the logarithm of a number to a specified base.
CHAR	Returns the character specified by a given number.
LOWER	Converts text to lowercase.
UPPER	Converts text to uppercase.
PROPER	Capitalizes the first letter of each word in a text string.
LEFT	Extracts a specified number of characters from the beginning of a text string.
RIGHT	Extracts a specified number of characters from the end of a text string.
EXACT	Compares two text strings and returns "True" if they are identical, and "False" if they are not.
TRIM	Removes extra spaces from a text string, except for single spaces between words.
REPLACE	Replaces a specified number of characters in a text string with new text.
SUBSTITUTE	Replaces occurrences of a specified text in a text string with new text.
CODE	Returns the numeric Unicode value of the first character in a text string.
FIND	Searches for a specific substring within a text string and returns its position.
ISERR	Checks if a value is an error value other than "#N/A" and returns "True" if it is, or "False" if it's not.
ISTEXT	Checks if a value is text and returns "True" if it is, or "False" if it's not.
ISNUMBER	Checks if a value is a number and returns "True" if it is, or "False" if it's not.
ISBLANK	Checks if a cell is empty and returns "True" if it is, or "False" if it's not.
T	Converts a value to text format.
DATEVALUE	Converts a date represented as text into a date serial number.
CLEAN	Removes non-printable characters from text.
MDETERM	Calculates the matrix determinant of an array.
MINVERSE	Returns the multiplicative inverse (reciprocal) of a matrix.
MMULT	Multiplies two matrices together.
IPMT	Calculates the interest portion of a loan payment for a given period.
PPMT	Calculates the principal portion of a loan payment for a given period.
COUNTA	Counts the number of non-empty cells in a range, including text and numbers.
PRODUCT	Multiplies all the numbers in a range.
FACT	Calculates the factorial of a number.
ISNONTEXT	Checks if a value is not text and returns "True" if it's not text, or "False" if it is text.
VARP	Estimates the variance of a population based on a sample.
TRUNC	Truncates a number to a specified number of decimal places.
ISLOGICAL	Checks if a value is a logical (Boolean) value and returns "True" if it is, or "False" if it's not.
USDOLLAR	Converts a number to text format with a currency symbol and two decimal places.
ROUNDUP	Rounds a number up to a specified number of decimal places.
ROUNDDOWN	Rounds a number down to a specified number of decimal places.
RANK	Returns the rank of a number in a list, with options to handle ties.
ADDRESS	Returns the cell address as text based on row and column numbers.
DAYS360	Calculates the number of days between two dates using the 360-day year.
TODAY	Returns the current date.
MEDIAN	Returns the median (middle value) of a set of numbers.
SUMPRODUCT	Multiplies corresponding components in arrays and returns the sum of the products.
SINH	Calculates the hyperbolic sine of a number.
COSH	Calculates the hyperbolic cosine of a number.
TANH	Calculates the hyperbolic tangent of a number.
ASINH	Calculates the inverse hyperbolic sine of a number.
ACOSH	Calculates the inverse hyperbolic cosine of a number.
ATANH	Calculates the inverse hyperbolic tangent of a number.
ExternalFunction	Represents a function call or operation provided by an external add-in or custom function.
ERRORTYPE	Returns a number that corresponds to the error type in a given value.
AVEDEV	Calculates the average absolute deviation of a set of values from their mean.
COMBIN	Calculates the number of combinations for a given number of items taken from a larger set.
EVEN	Rounds a number up to the nearest even integer.
FLOOR	Rounds a number down to the nearest multiple of a specified significance.
CEILING	Rounds a number up to the nearest multiple of a specified significance.
NORMDIST	Calculates the cumulative normal distribution function for a specified value.
NORMSDIST	Calculates the standard normal cumulative distribution function.
NORMINV	Calculates the inverse of the normal cumulative distribution function for a specified probability.
NORMSINV	Calculates the inverse of the standard normal cumulative distribution function.
STANDARDIZE	Converts a value to a standard normal distribution with a mean of 0 and a standard deviation of 1.
ODD	Rounds a number up to the nearest odd integer.
POISSON	Calculates the Poisson distribution probability for a given number of events.
TDIST	Calculates the Student's t-distribution for a specified value and degrees of freedom.
SUMXMY2	Calculates the sum of squares of the differences between corresponding values in two arrays.
SUMX2MY2	Calculates the sum of squares of the differences between corresponding values in two arrays.
SUMX2PY2	Calculates the sum of squares of the sum of corresponding values in two arrays.
INTERCEPT	Calculates the point at which a trendline crosses the y-axis in a chart.
SLOPE	Calculates the slope of a trendline in a chart.
DEVSQ	Returns the sum of squares of deviations of data points from their mean.
SUMSQ	Calculates the sum of squares of a set of numbers.
LARGE	Returns the k-th largest value in a dataset, where k is specified.
SMALL	Returns the k-th smallest value in a dataset, where k is specified.
PERCENTILE	Returns the k-th percentile of a dataset, where k is specified.
PERCENTRANK	Returns the rank of a value in a dataset as a percentage of the total number of values.
MODE	Returns the most frequently occurring value in a dataset.
CONCATENATE	Combines multiple text strings into one.
POWER	Raises a number to a specified power.
RADIANS	Converts degrees to radians.
DEGREES	Converts radians to degrees.
SUBTOTAL	Performs various calculations (e.g., sum, average) on a range, and you can choose whether to include or exclude other SUBTOTAL results within the range.
SUMIF	Adds up all the numbers in a range that meet a specified condition.
COUNTIF	Counts the number of cells in a range that meet a specified condition.
COUNTBLANK	Counts the number of empty cells in a range.
ROMAN	Converts an Arabic numeral to a Roman numeral.
HYPERLINK	Creates a hyperlink to a webpage or file.
MAXA	Returns the maximum value from a set of numbers, including text and logical values.
MINA	Returns the minimum value from a set of numbers, including text and logical values.

查克尼思·賓

立即與工程團隊聊天

軟體工程師

Chaknith 是開發者界的夏洛克福爾摩斯。他第一次意識到自己可能有個軟體工程的未來，是在他為了娛樂而參加程式挑戰的時候。他的重點是 IronXL 和 IronBarcode，但他也引以為豪的是，他幫助客戶解決所有產品的問題。Chaknith 利用他與客戶直接對話中獲得的知識，以進一步改進產品。他的實際反饋超越了 Jira 工單，並支持產品開發、文件撰寫和行銷，以提升客戶的整體體驗。不在公司時，他通常在學習機器學習、寫程式和徒步旅行。