如何编辑工作表中的公式

查克尼特·宾

2023年十月17日

更新 2024年十二月10日

Translated

View the article in English

Excel公式是一个以等号（=）开头的表达式，用于根据单元格值进行数学计算、数据处理和结果推导。它可能包含算术运算、函数、单元格引用、常量和逻辑运算。公式可以在单元格值发生变化时启用动态更新，使 Excel 成为自动化任务和数据分析的多功能工具。

IronXL支持编辑Excel文件中现有的公式、检索公式的结果，并强制重新评估工作簿。这确保每个公式都将重新计算以获得准确的结果。 IronXL 支持超过165 个公式。

如何编辑工作表中的公式

下载 C# 库以在工作表中编辑公式
创建新的 Excel 文件或导入现有文件
编辑或设置公式属性，以将公式分配给单元格
通过访问单元格的FormattedCellValue属性检索结果值
导出已编辑的 Excel 文件

开始使用IronXL

立即在您的项目中开始使用IronXL，并享受免费试用。

第一步：

编辑公式示例

要编辑或设置公式，请访问Formula属性。首先，选择一个范围或单元格，然后访问并设置公式属性。 Formula 属性是一个既能获取也能设置的属性，如果有的话，它返回公式字符串。调用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 票据，还支持产品开发、文档编写和市场营销，从而提升客户的整体体验。当他不在办公室时，他可能会在学习机器学习、编程或徒步旅行。