So bearbeiten Sie Formeln in einem Arbeitsblatt
Eine Excel-Formel ist ein Ausdruck, der mit einem Gleichheitszeichen (=) beginnt und für mathematische Berechnungen, Datenmanipulation und die Ableitung von Ergebnissen basierend auf den Zellwerten verwendet wird. Sie kann Arithmetik, Funktionen, Zellbezüge, Konstanten und logische Operationen enthalten. Formeln ermöglichen dynamische Aktualisierungen bei sich ändernden Zellwerten und machen Excel zu einem vielseitigen Werkzeug für die Automatisierung von Aufgaben und die Datenanalyse.
IronXL unterstützt die Bearbeitung bestehender Formeln in einer Excel-Datei, die Abfrage des Ergebnisses der Formel und die Erzwingung einer Neuauswertung der Arbeitsmappe. Dadurch wird sichergestellt, dass jede Formel für genaue Ergebnisse neu berechnet wird. IronXL unterstützt über 165 Formeln.
So bearbeiten Sie Formeln in einem Arbeitsblatt
- Laden Sie die C#-Bibliothek herunter, um Formeln in einem Arbeitsblatt zu bearbeiten
- Erstellen Sie eine neue Excel-Datei oder importieren Sie eine bestehende Datei
- Bearbeiten oder festlegen der Formel-Eigenschaft, um einer Zelle eine Formel zuzuweisen
- Rufen Sie den Ergebniswert ab, indem Sie auf die FormattedCellValue-Eigenschaft der Zelle zugreifen
- Exportieren Sie die bearbeitete Excel-Datei
Legen Sie los mit IronXL
Beginnen Sie noch heute mit der Verwendung von IronXL in Ihrem Projekt mit einer kostenlosen Testversion.
Formeln bearbeiten Beispiel
Um die Formel zu bearbeiten oder festzulegen, greifen Sie auf die Formula-Eigenschaft zu. Wählen Sie zunächst einen Bereich oder eine Zelle aus, und rufen Sie dann die Eigenschaft Formel auf und legen Sie sie fest. Die Eigenschaft Formula ist sowohl eine Get- als auch eine Set-Eigenschaft, die die Formelzeichenfolge zurückgibt, falls vorhanden. Rufen Sie die EvaluateAll
-Methode auf, um das gesamte Arbeitsbuch neu zu bewerten und genaue Berechnungen sicherzustellen.
: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()
Ergebnis aus Formel abrufen
Obwohl es manchmal anwendbar ist, das Ergebnis aus der Value-Eigenschaft des ausgewählten Bereichs und der Zelle abzurufen, wird empfohlen, das Ergebnis aus der FormattedCellValue-Eigenschaft der Zelle abzurufen, um ein genaueres Ergebnis der Formel zu erhalten. In dem ausgewählten Bereich können Sie auf die Zelle zugreifen, indem Sie die First
-Methode aufrufen. Mit dieser Methode wird das erste Element der Liste ausgewählt, in unserem Fall die Zelle "A4" Von dort aus können Sie auf die Eigenschaft "FormattedCellValue" zugreifen.
: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)
Unterstützte Formel
Excel enthält über 450 Formeln, die für eine Vielzahl von Berechnungen verwendet werden können. IronXL unterstützt rund 165 der am häufigsten verwendeten Formeln. Im Folgenden finden Sie die unterstützten Formeln:
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. |