ワークシートの数式を編集する方法
Excelの数式は、イコールサイン(=)で始まり、セルの値に基づいて数値計算、データ操作、および結果の導出に使用される表現です。 それには算術、関数、セル参照、定数、および論理演算が含まれることがあります。 数式により、セルの値が変わると動的に更新されるため、Excelはタスクの自動化やデータ分析において非常に柔軟なツールとなります。
IronXLは、Excelファイル内の既存の数式の編集、数式からの結果の取得、およびワークブックの再評価の強制をサポートします。 これにより、各数式が正確な結果を得るために再計算されます。 IronXLは165以上の数式をサポートしています。
ワークシートの数式を編集する方法
- ワークシートで数式を編集するためのC#ライブラリをダウンロード
- 新しいExcelファイルを作成するか、既存のファイルをインポートしてください。
- セルに数式を割り当てるために数式プロパティを編集または設定する
- セルのFormattedCellValueプロパティにアクセスして結果値を取得する
- 編集されたExcelファイルをエクスポート
IronXLで始めましょう
今日から無料トライアルでIronXLをあなたのプロジェクトで使い始めましょう。
数式の編集例
数式を編集または設定するには、Formulaプロパティにアクセスします。 まず、範囲またはセルを選択し、次に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()
数式から結果を取得
選択された範囲とセルの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)
サポートされている数式
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. |