IronXL 操作指南 编辑公式 How to Edit Formulas in a Worksheet Chaknith Bin 已更新:七月 22, 2025 Download IronXL NuGet 下载 DLL 下载 Start Free Trial Copy for LLMs Copy for LLMs Copy page as Markdown for LLMs Open in ChatGPT Ask ChatGPT about this page Open in Gemini Ask Gemini about this page Open in Grok Ask Grok about this page Open in Perplexity Ask Perplexity about this page Share Share on Facebook Share on X (Twitter) Share on LinkedIn Copy URL Email article This article was translated from English: Does it need improvement? Translated View the article in English An Excel formula is an expression starting with an equal sign (=) used for mathematical calculations, data manipulation, and result derivation based on cell values. It may contain arithmetic, functions, cell references, constants, and logical operations. Formulas enable dynamic updates as cell values change, making Excel a versatile tool for automating tasks and data analysis. IronXL supports editing existing formulas in an Excel file, retrieving the result from the formula, and forcing the workbook to be re-evaluated. This ensures that each formula will be recalculated for accurate results. IronXL supports over 165 formulas. Quickstart: Change a Cell Formula Instantly Set or update a formula using IronXL’s Formula property on any cell. Then call EvaluateAll() to recalculate all formulas—getting started fast and ensuring updated results without fuss. Get started making PDFs with NuGet now: Install IronXL with NuGet Package Manager PM > Install-Package IronXL.Excel Copy and run this code snippet. IronXL.WorkBook.Load("MyFile.xlsx").DefaultWorkSheet["B2"].Formula = "=AVERAGE(C1,C2)"; workBook.EvaluateAll(); Deploy to test on your live environment Start using IronXL in your project today with a free trial Free 30 day Trial Minimal Workflow (5 steps) Download the C# library to edit formulas in a worksheet Create a new Excel file or import an existing one Edit or set the Formula property to assign a formula to the cell Retrieve the result value by accessing the FormattedCellValue property of the cell Export the edited Excel file Get started with IronXL Edit Formulas Example To edit or set the formula, access the Formula property. First, select a Range or Cell, and then access and set the Formula property. The Formula property is both a get and set property that returns the formula string, if any. Invoke the EvaluateAll method to re-evaluate the entire workbook, ensuring accurate calculations. :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 Retrieve Result from Formula To retrieve the result from a formula, it is preferable to use the FormattedCellValue property of the Cell for a more precise result. Within a selected Range, you can access the Cell by using the First method, which selects the first element of the sequence, typically "A4" in our example. From there, access the FormattedCellValue property. :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 Supported Formula Excel contains 450+ formulas that can be used to calculate for a variety of purposes. IronXL supports around 165 of the most commonly used formulas. Please check the following for the supported formulas: Formula Name Description ABSReturns the absolute value of a number, disregarding its sign. INTRounds a number down to the nearest integer. COUNTCounts the number of cells that contain numbers within a specified range. IFPerforms a conditional test and returns one value if the condition is true and another if it's false. SUMAdds up a range of numbers. AVERAGECalculates the average of a range of numbers. MINReturns the minimum value from a set of numbers. MAXReturns the maximum value from a set of numbers. ROWReturns the row number of a cell reference. COLUMNReturns the column number of a cell reference. NARepresents an error value for "Not Available" or missing data. NPVCalculates the Net Present Value of a series of cash flows at a specified discount rate. STDEVCalculates the standard deviation of a set of numbers. SIGNReturns the sign of a number as -1 for negative, 0 for zero, or 1 for positive. ROUNDRounds a number to a specified number of decimal places. LOOKUPSearches for a value in a range and returns a corresponding value from another range. INDEXReturns the value of a cell in a specified row and column of a given range. REPTRepeats a text string a specified number of times. MIDExtracts a portion of text from a given text string based on a specified starting position and length. LENReturns the number of characters in a text string. VALUEConverts a text string that represents a number to an actual number. TRUERepresents the logical value for "True." FALSERepresents the logical value for "False." ANDChecks if all specified conditions are true and returns "True" if they are, and "False" otherwise. ORChecks if at least one of the specified conditions is true and returns "True" if it is, and "False" otherwise. NOTInverts the logical value of a condition, turning "True" into "False" and vice versa. MODReturns the remainder when one number is divided by another. DMINExtracts the minimum value from a database based on specified criteria. VARCalculates the variance of a set of numbers. TEXTConverts a number to text using a specified format. PVCalculates 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. RANDGenerates a random decimal number between 0 and 1. MATCHSearches for a specified value in a range and returns the relative position of the item found. DATECreates a date value by specifying the year, month, and day. TIMECreates a time value by specifying the hour, minute, and second. DAYExtracts the day from a given date. MONTHExtracts the month from a given date. YEARExtracts the year from a given date. WEEKDAYReturns the day of the week for a specified date. HOURExtracts the hour from a given time. MINUTEExtracts the minute from a given time. SECONDExtracts the second from a given time. NOWReturns the current date and time. AREASCounts the number of individual ranges within a reference. ROWSCounts the number of rows in a specified range. COLUMNSCounts the number of columns in a specified range. OFFSETReturns a reference offset from a specified cell by a certain number of rows and columns. SEARCHSearches for a substring within a text string and returns its position. TRANSPOSETransposes the rows and columns of a range. ATAN2Calculates the arctangent of a specified x and y coordinate. ASINCalculates the arcsine of a specified value. ACOSCalculates the arccosine of a specified value. CHOOSEReturns a value from a list of values based on a specified position. HLOOKUPSearches for a value in the top row of a table or range and returns a value in the same column from a specified row. VLOOKUPSearches for a value in the first column of a table or range and returns a value in the same row from a specified column. ISREFChecks if a value is a reference and returns "True" if it is, or "False" if it's not. LOGCalculates the logarithm of a number to a specified base. CHARReturns the character specified by a given number. LOWERConverts text to lowercase. UPPERConverts text to uppercase. PROPERCapitalizes the first letter of each word in a text string. LEFTExtracts a specified number of characters from the beginning of a text string. RIGHTExtracts a specified number of characters from the end of a text string. EXACTCompares two text strings and returns "True" if they are identical, and "False" if they are not. TRIMRemoves extra spaces from a text string, except for single spaces between words. REPLACEReplaces a specified number of characters in a text string with new text. SUBSTITUTEReplaces occurrences of a specified text in a text string with new text. CODEReturns the numeric Unicode value of the first character in a text string. FINDSearches for a specific substring within a text string and returns its position. ISERRChecks if a value is an error value other than "#N/A" and returns "True" if it is, or "False" if it's not. ISTEXTChecks if a value is text and returns "True" if it is, or "False" if it's not. ISNUMBERChecks if a value is a number and returns "True" if it is, or "False" if it's not. ISBLANKChecks if a cell is empty and returns "True" if it is, or "False" if it's not. TConverts a value to text format. DATEVALUEConverts a date represented as text into a date serial number. CLEANRemoves non-printable characters from text. MDETERMCalculates the matrix determinant of an array. MINVERSEReturns the multiplicative inverse (reciprocal) of a matrix. MMULTMultiplies two matrices together. IPMTCalculates the interest portion of a loan payment for a given period. PPMTCalculates the principal portion of a loan payment for a given period. COUNTACounts the number of non-empty cells in a range, including text and numbers. PRODUCTMultiplies all the numbers in a range. FACTCalculates the factorial of a number. ISNONTEXTChecks if a value is not text and returns "True" if it's not text, or "False" if it is text. VARPEstimates the variance of a population based on a sample. TRUNCTruncates a number to a specified number of decimal places. ISLOGICALChecks if a value is a logical (Boolean) value and returns "True" if it is, or "False" if it's not. USDOLLARConverts a number to text format with a currency symbol and two decimal places. ROUNDUPRounds a number up to a specified number of decimal places. ROUNDDOWNRounds a number down to a specified number of decimal places. RANKReturns the rank of a number in a list, with options to handle ties. ADDRESSReturns the cell address as text based on row and column numbers. DAYS360Calculates the number of days between two dates using the 360-day year. TODAYReturns the current date. MEDIANReturns the median (middle value) of a set of numbers. SUMPRODUCTMultiplies corresponding components in arrays and returns the sum of the products. SINHCalculates the hyperbolic sine of a number. COSHCalculates the hyperbolic cosine of a number. TANHCalculates the hyperbolic tangent of a number. ASINHCalculates the inverse hyperbolic sine of a number. ACOSHCalculates the inverse hyperbolic cosine of a number. ATANHCalculates the inverse hyperbolic tangent of a number. ExternalFunctionRepresents a function call or operation provided by an external add-in or custom function. ERRORTYPEReturns a number that corresponds to the error type in a given value. AVEDEVCalculates the average absolute deviation of a set of values from their mean. COMBINCalculates the number of combinations for a given number of items taken from a larger set. EVENRounds a number up to the nearest even integer. FLOORRounds a number down to the nearest multiple of a specified significance. CEILINGRounds a number up to the nearest multiple of a specified significance. NORMDISTCalculates the cumulative normal distribution function for a specified value. NORMSDISTCalculates the standard normal cumulative distribution function. NORMINVCalculates the inverse of the normal cumulative distribution function for a specified probability. NORMSINVCalculates the inverse of the standard normal cumulative distribution function. STANDARDIZEConverts a value to a standard normal distribution with a mean of 0 and a standard deviation of 1. ODDRounds a number up to the nearest odd integer. POISSONCalculates the Poisson distribution probability for a given number of events. TDISTCalculates the Student's t-distribution for a specified value and degrees of freedom. SUMXMY2Calculates the sum of squares of the differences between corresponding values in two arrays. SUMX2MY2Calculates the sum of squares of the differences between corresponding values in two arrays. SUMX2PY2Calculates the sum of squares of the sum of corresponding values in two arrays. INTERCEPTCalculates the point at which a trendline crosses the y-axis in a chart. SLOPECalculates the slope of a trendline in a chart. DEVSQReturns the sum of squares of deviations of data points from their mean. SUMSQCalculates the sum of squares of a set of numbers. LARGEReturns the k-th largest value in a dataset, where k is specified. SMALLReturns the k-th smallest value in a dataset, where k is specified. PERCENTILEReturns the k-th percentile of a dataset, where k is specified. PERCENTRANKReturns the rank of a value in a dataset as a percentage of the total number of values. MODEReturns the most frequently occurring value in a dataset. CONCATENATECombines multiple text strings into one. POWERRaises a number to a specified power. RADIANSConverts degrees to radians. DEGREESConverts radians to degrees. SUBTOTALPerforms various calculations (e.g., sum, average) on a range, and you can choose whether to include or exclude other SUBTOTAL results within the range. SUMIFAdds up all the numbers in a range that meet a specified condition. COUNTIFCounts the number of cells in a range that meet a specified condition. COUNTBLANKCounts the number of empty cells in a range. ROMANConverts an Arabic numeral to a Roman numeral. HYPERLINKCreates a hyperlink to a webpage or file. MAXAReturns the maximum value from a set of numbers, including text and logical values. MINAReturns the minimum value from a set of numbers, including text and logical values. 常见问题解答 如何使用C#编辑Excel工作表中的公式？ 要使用C#编辑Excel工作表中的公式，可以使用IronXL库。首先，从NuGet下载库，然后加载或创建Excel文件。访问单元格或范围的Formula属性以设置或修改所需的公式。 使用C#在Excel文件中检索公式结果的步骤是什么？ 可以通过访问单元格的FormattedCellValue属性，使用IronXL库在Excel文件中检索公式的结果。此属性提供在Excel中显示的公式计算结果。 如何确保在编辑后，Excel工作簿中的所有公式都是最新的？ 使用IronXL的EvaluateAll方法重新评估工作簿中的所有公式。此方法确保所有公式重新计算并反映最新的数据更改。 像IronXL这样的.NET库支持哪些类型的Excel公式？ IronXL支持超过165种常见的Excel公式，包括SUM、AVERAGE、IF、COUNT、VLOOKUP、ABS、INT和其他公式，涵盖广泛的算术计算、逻辑测试和统计分析。 我可以使用C#库在Excel公式中执行逻辑运算吗？ 可以，使用IronXL，您可以在Excel公式中执行逻辑运算。这使您可以在Excel文件中创建动态的和条件的计算。 在修改公式后，如何使用C#导出已编辑的Excel文件？ 使用IronXL编辑Excel文件中的公式后，您可以通过SaveAs方法导出更改。这允许您将已编辑的文件保存到系统的指定位置。 是否可以使用C#库一次编辑多个单元格的公式？ 可以，通过IronXL，您可以一次编辑多个单元格或单元格范围的公式。方法是遍历所需的范围并为每个单元格设置Formula属性。 开始使用C#编辑Excel文件的最佳方式是什么？ 要开始使用C#编辑Excel文件，从NuGet下载IronXL。然后，创建一个新项目，加载您的Excel文件，并使用IronXL的API来处理公式和其他数据。 如何使用C#库排除Excel中的公式错误？ 如果使用IronXL遇到公式错误，请确保公式语法正确，引用的单元格中有必要的数据，并使用EvaluateAll方法重新计算工作簿以确保准确性。 Chaknith Bin 立即与工程团队聊天 软件工程师 Chaknith 在 IronXL 和 IronBarcode 工作。他在 C# 和 .NET 方面有着深厚的专业知识，帮助改进软件并支持客户。他从用户互动中获得的见解有助于更好的产品、文档和整体体验。 准备开始了吗？ Nuget 下载 1,686,155 | 版本: 2025.11 刚刚发布 免费 NuGet 下载 总下载量：1,686,155 查看许可证