すべての関数について、明確な構文、引数、実例、よくある質問を掲載。構文を覚えたくないですか？1文の説明からAIで数式を生成しましょう。
AI で数式を生成VLOOKUP
範囲の最初の列で値を検索し、同じ行の別の列の値を返します。
XLOOKUP
VLOOKUP と HLOOKUP の現代的な代替。任意の列を検索し、任意の方向で任意の列を返します。
INDEX
範囲内の指定した行と列の位置にある値を返します。
MATCH
行または列の中での値の位置を返します。
HLOOKUP
範囲の最上行で値を検索し、同じ列の別の行の値を返します。
CHOOSE
位置番号に基づいてリストから値を返します。
OFFSET
Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
INDIRECT
Returns the reference specified by a text string.
XMATCH
Returns the relative position of an item in an array or range of cells.
ADDRESS
Creates a cell reference as text, given specified row and column numbers.
ROW
Returns the row number of a reference.
COLUMN
Returns the column number of a reference.
ROWS
Returns the number of rows in a reference or array.
COLUMNS
Returns the number of columns in a reference or array.
LOOKUP
Returns a value from a range or array that matches a specified lookup value.
GETPIVOTDATA
Returns data stored in a PivotTable report.
FILTER
Filters a range of data based on criteria you define.
SORT
Sorts the contents of a range or array.
UNIQUE
Returns a list of unique values from a range or array.
HYPERLINK
Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet.
TRANSPOSE
Converts a vertical range of cells into a horizontal range, or vice versa.
FORMULATEXT
Returns the formula at the given reference as a text string.
SORTBY
Sorts the contents of a range or array based on the values in a corresponding range or array.
TAKE
Returns a specified number of contiguous rows or columns from the start or end of an array.
DROP
Excludes a specified number of rows or columns from the start or end of an array.
EXPAND
Expands or pads an array to a specified number of rows and columns.
HSTACK
Appends arrays horizontally and in sequence to return a larger array.
VSTACK
Appends arrays vertically to return a single combined array.
CHOOSECOLS
Returns the specified columns from an array.
CHOOSEROWS
Returns the specified rows from an array.
TOCOL
Returns the array in a single column.
TOROW
Returns the array as a single row.
WRAPROWS
Wraps a provided row or column of values into a two-dimensional array by row.
WRAPCOLS
Wraps a provided row or column of values into columns by a specified count.
AREAS
Returns the number of areas in a reference.
IF
条件が真のときに一方の値を、偽のときに別の値を返します。
IFS
複数の条件を順番に確認し、最初に真になる条件の値を返します。
IFERROR
数式がエラーを生成したときにカスタムの結果を返し、そうでなければ数式の結果を返します。
AND
すべての条件が真のときにのみ TRUE を返します。
OR
少なくとも 1 つの条件が真のときに TRUE を返します。
SWITCH
式を値のリストと照合し、最初に一致した結果を返します。
NOT
Reverses the logical value of its argument.
IFNA
Returns a specified value if the expression evaluates to an #N/A error, otherwise returns the result of the expression.
XOR
Returns the exclusive OR of all arguments.
TRUE
Returns the logical value TRUE.
FALSE
Returns the logical value FALSE.
LET
Assigns names to calculation results to allow for intermediate variables in a formula.
LAMBDA
Creates custom, reusable functions and calls them by a friendly name.
BYROW
Applies a LAMBDA function to each row in an array and returns an array of results.
BYCOL
Applies a LAMBDA function to each column in an array and returns an array of the results.
MAP
Applies a LAMBDA function to each value in the provided arrays to create a new mapped array.
REDUCE
Reduces an array to an accumulated value by applying a LAMBDA to each element.
SCAN
Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.
ISBLANK
Checks whether a reference is to an empty cell and returns TRUE or FALSE.
ISERROR
Checks if a value is any error type and returns TRUE or FALSE.
ISNUMBER
Checks if a value is a number and returns TRUE or FALSE.
ISTEXT
Checks if a value is text and returns TRUE or FALSE.
ISNA
Checks whether a value is the #N/A error and returns TRUE or FALSE.
MAKEARRAY
Returns a calculated array of a specified row and column size by applying a LAMBDA function.
ISFORMULA
Checks whether a reference contains a formula and returns TRUE or FALSE.
ISREF
Checks if a value is a reference.
ISLOGICAL
Checks if a value is a logical value (TRUE or FALSE) and returns TRUE or FALSE.
ISEVEN
Returns TRUE if the number is even, and FALSE if it is odd.
ISODD
Checks whether a value is odd and returns TRUE or FALSE.
ISERR
Checks if a value is any error type except #N/A.
ISNONTEXT
Checks if a value is not text and returns TRUE or FALSE.
N
Returns a value converted to a number.
NA
Returns the error value #N/A.
TYPE
Returns an integer representing the data type of a value.
ERROR.TYPE
Returns a number corresponding to an error type.
CELL
Returns information about the formatting, location, or contents of a cell.
SHEET
Returns the sheet number of the referenced sheet.
SHEETS
Returns the number of sheets in a reference.
SUMIF
1 つの条件を満たすセルを合計します。
SUMIFS
複数の条件を同時に満たすセルを合計します。
SUM
数値の範囲を合計します。
ROUND
数値を指定した桁数に丸めます。
SUMPRODUCT
配列内の対応する要素を掛け合わせ、その結果を合計します。加重合計や複数条件の合計に最適です。
ROUNDUP
Rounds a number up, away from zero.
ROUNDDOWN
Rounds a number down, toward zero.
MROUND
Returns a number rounded to the nearest specified multiple.
MOD
Returns the remainder after a number is divided by a divisor.
ABS
Returns the absolute value of a number.
INT
Rounds a number down to the nearest integer.
CEILING
Rounds a number up to the nearest integer or to the nearest multiple of significance.
FLOOR
Rounds a number down to the nearest multiple of significance.
POWER
Returns the result of a number raised to a given power.
SQRT
Returns the square root of a positive number.
SUBTOTAL
Returns a subtotal in a list or database.
AGGREGATE
Returns an aggregate in a list or database using a specified function with the option to ignore hidden rows and error values.
PRODUCT
Multiplies all the numbers given as arguments and returns the product.
RAND
Returns a random number greater than or equal to 0 and less than 1.
RANDBETWEEN
Returns a random integer number between the numbers you specify.
SUMSQ
Returns the sum of the squares of the arguments.
GCD
Returns the greatest common divisor of two or more integers.
LCM
Returns the least common multiple of integers.
FACT
Returns the factorial of a number.
COMBIN
Returns the number of combinations for a given number of items.
PERMUT
Returns the number of permutations for a given number of objects.
EXP
Returns e raised to the power of a given number.
LN
Returns the natural logarithm of a number.
LOG
Returns the logarithm of a number to a specified base.
LOG10
Returns the base-10 logarithm of a number.
SIGN
Returns the sign of a number.
TRUNC
Truncates a number to an integer by removing the fractional part.
EVEN
Rounds a number up to the nearest even integer.
ODD
Rounds a number up to the nearest odd integer.
QUOTIENT
Returns the integer portion of a division operation.
SEQUENCE
Generates a list of sequential numbers in an array.
RANDARRAY
Returns an array of random numbers between 0 and 1 or within a specified range.
PI
Returns the value of the mathematical constant Pi, accurate to 15 digits.
SIN
Returns the sine of a given angle.
COS
Returns the cosine of a given angle.
TAN
Returns the tangent of a given angle.
ATAN2
Returns the arctangent of the specified x and y coordinates.
DEGREES
Converts an angle from radians to degrees.
RADIANS
Converts degrees to radians.
CEILING.MATH
Rounds a number up to the nearest integer or to the nearest multiple of significance.
FLOOR.MATH
Rounds a number down to the nearest integer or to the nearest multiple of significance.
MMULT
Returns the matrix product of two arrays.
MINVERSE
Returns the inverse matrix for the matrix stored in an array.
MDETERM
Returns the matrix determinant of an array.
MUNIT
Returns the unit matrix for the specified dimension.
SQRTPI
Returns the square root of (number * pi).
MULTINOMIAL
Returns the ratio of the factorial of a sum of values to the product of factorials of those values.
SUMXMY2
Returns the sum of the squares of the differences of corresponding values in two arrays.
DSUM
Adds the numbers in a column of a list or database that match conditions you specify.
DAVERAGE
Returns the average of entries in a database column that match specified conditions.
DCOUNT
Counts the number of cells containing numbers in a database that match specified conditions.
DCOUNTA
Counts the number of non-empty cells in a database column that match specified conditions.
DGET
Extracts a single record from a database that matches specified conditions.
DMAX
Returns the largest number in a column of a list or database that matches specified conditions.
DMIN
Returns the smallest number in a field of records in a database that match the specified conditions.
DPRODUCT
Multiplies the values in a particular field of records in a database that match the specified criteria.
DSTDEV
Estimates the standard deviation of a population based on a sample of entries in a database that match specified conditions.
DVAR
Estimates the variance of a population based on a sample from entries in a database that match specified conditions.
CONVERT
Converts a number from one measurement system to another.
DEC2BIN
Converts a decimal number to binary.
BIN2DEC
Converts a binary number to a decimal number.
DEC2HEX
Converts a decimal number to a hexadecimal number.
HEX2DEC
Converts a hexadecimal number to a decimal number.
BITAND
Returns a bitwise 'AND' of two numbers.
BITOR
Returns a bitwise OR of two numbers.
BITXOR
Returns a bitwise 'exclusive OR' of two numbers.
DELTA
Tests whether two values are equal.
GESTEP
Returns 1 if a number is greater than or equal to a threshold, and 0 otherwise.
ROMAN
Converts an Arabic numeral to Roman, as text.
ARABIC
Converts a Roman numeral to an Arabic numeral.
BASE
Converts a number into a text representation with the given radix (base).
DECIMAL
Converts a text representation of a number in a given base into a decimal number.
COMBINA
Returns the number of combinations with repetitions for a given number of items.
SERIESSUM
Returns the sum of a power series based on the formula: x^n + m*x^(n+m) + m^2*x^(n+2m) + ...
ACOS
Returns the arccosine of a number.
ASIN
Returns the arcsine of a number in radians.
ATAN
Returns the arctangent of a number in radians.
ACOSH
Returns the inverse hyperbolic cosine of a number.
ASINH
Returns the inverse hyperbolic sine of a number.
ATANH
Returns the inverse hyperbolic tangent of a number.
COSH
Returns the hyperbolic cosine of a number.
SINH
Returns the hyperbolic sine of a number.
TANH
Returns the hyperbolic tangent of a number.
COT
Returns the cotangent of an angle given in radians.
SEC
Returns the secant of an angle.
CSC
Returns the cosecant of an angle.
OCT2DEC
Converts an octal number to a decimal number.
DEC2OCT
Converts a decimal number to an octal number.
BIN2HEX
Converts a binary number to a hexadecimal number.
HEX2BIN
Converts a hexadecimal number to a binary number.
OCT2BIN
Converts an octal number to binary.
BIN2OCT
Converts a binary number to octal.
COMPLEX
Converts real and imaginary coefficients into a complex number of the form x + yi or x + yj.
IMABS
Returns the absolute value (modulus) of a complex number.
IMREAL
Returns the real coefficient of a complex number in x + yi or x + yj text format.
ERF
Returns the error function integrated between lower and upper limits.
ERFC
Returns the complementary error function integrated between x and infinity.
BITLSHIFT
Shifts a number to the left by the specified number of bits.
BITRSHIFT
Shifts the bits of a number to the right by a specified number of places.
TEXTJOIN
複数のセルや範囲のテキストを、各項目の間に区切り文字を入れて結合します。
CONCAT
複数のセルや範囲のテキストを 1 つの文字列に結合します。
LEFT
文字列の先頭から指定した文字数を返します。
MID
開始位置と長さを指定して、文字列の途中から文字を返します。
LEN
文字列の文字数を返します。
TRIM
テキストから余分なスペースを取り除き、単語間に 1 つのスペースを残します。
RIGHT
文字列の末尾から指定した文字数を返します。
FIND
あるテキスト文字列が別の文字列内にある位置を返します（大文字と小文字を区別）。
SUBSTITUTE
文字列内の特定のテキストの出現箇所を置き換えます。
TEXT
数値または日付を特定の書式のテキストに変換します。
UPPER
Converts all characters in a text string to uppercase.
LOWER
Converts all uppercase letters in a text string to lowercase.
PROPER
Capitalizes the first letter in each word of a text string and converts all other letters to lowercase.
REPLACE
Replaces part of a text string with a different text string based on the number of characters you specify.
SEARCH
Returns the position of a text string within another text string, case-insensitive.
VALUE
Converts a text string that represents a number into a number.
NUMBERVALUE
Converts text to a number in a locale-independent way.
REPT
Repeats a given text string a specified number of times.
CHAR
Returns the character specified by the code number.
CODE
Returns a numeric code for the first character in a text string.
EXACT
Checks whether two text strings are exactly the same.
CLEAN
Removes all non-printable characters from a text string.
TEXTSPLIT
Splits text strings into rows or columns using specified delimiters.
TEXTBEFORE
Returns text that occurs before a given delimiter.
TEXTAFTER
Returns the text that occurs after a given delimiter.
CONCATENATE
Joins two or more text strings into one string.
DOLLAR
Converts a number to text using the currency format, with the decimals rounded to the specified place.
FIXED
Rounds a number to a specified number of decimals and formats it as text with or without commas.
UNICHAR
Returns the Unicode character that corresponds to the numeric value provided.
UNICODE
Returns the number (code point) corresponding to the first character of the text.
ARRAYTOTEXT
Converts an array of values into a single text string.
VALUETOTEXT
Converts any value to text.
T
Checks whether a value is text and returns the text if it is, or an empty string if it is not.
DATEDIF
2 つの日付の差を、年、月、または日で計算します。
TODAY
現在の日付を返し、毎日自動的に更新されます。
EOMONTH
ある日付の指定した月数だけ前または後の月の最終日を返します。
NETWORKDAYS
週末（および任意の祝日）を除いて、2 つの日付間の営業日を数えます。
WEEKDAY
日付の曜日を数値として返します。
NOW
Returns the current date and time formatted as a date and time.
YEAR
Returns the year corresponding to a date.
MONTH
Returns the month of a date represented by a serial number.
DAY
Returns the day of the month for a given date as a number from 1 to 31.
DATE
Returns the serial number of a particular date.
DATEVALUE
Converts a date stored as text into a serial number that Excel recognizes as a date.
EDATE
Returns the serial number of the date that is the indicated number of months before or after the start date.
WORKDAY
Returns a date that is the indicated number of working days before or after a start date.
WORKDAY.INTL
Returns the serial number of the date before or after a specified number of workdays with custom weekend parameters.
HOUR
Returns the hour of a time value as a number from 0 to 23.
MINUTE
Returns the minute component of a time value as an integer ranging from 0 to 59.
TIME
Returns the decimal number for a particular time.
YEARFRAC
Calculates the fraction of the year represented by the number of whole days between two dates.
TIMEVALUE
Converts a time in the form of text to a serial number representing a time.
SECOND
Returns the seconds of a time value as an integer from 0 to 59.
WEEKNUM
Returns the week number of a specific date within a year.
ISOWEEKNUM
Returns the ISO 8601 week number of the year for a given date.
DAYS
2 つの日付の間の日数を返します。
DAYS360
Calculates the number of days between two dates based on a 360-day year.
NETWORKDAYS.INTL
Returns the number of whole working days between two dates using custom weekend parameters.
COUNTIF
範囲内で 1 つの条件を満たすセルを数えます。
COUNTIFS
複数の条件を同時に満たすセルを数えます。
AVERAGE
数値の範囲の算術平均を計算します。
AVERAGEIF
1 つの条件を満たすセルの平均を求めます。
MAX
範囲内の最大値を返します。
MIN
範囲内の最小値を返します。
PERCENTILE.INC
0 番目と 100 番目のパーセンタイルを含むパーセンタイルの値を返します。
PERCENTRANK.INC
データセット内の値のパーセンタイル ランクを返します。
QUARTILE.INC
データセットの最小値、四分位数、または最大値を返します。
FORECAST.LINEAR
線形トレンドから将来の価値を予測します。
COUNTA
空でないセル（数値と文字列）を数えます。
MEDIAN
Returns the median of the given numbers.
MODE
Returns the most frequently occurring value in a dataset.
RANK
Returns the rank of a number in a list of numbers.
LARGE
Returns the k-th largest value in a data set.
SMALL
Returns the k-th smallest value in a data set.
COUNT
Counts the number of cells that contain numbers in a range or list of arguments.
COUNTBLANK
Counts the number of empty cells in a specified range of cells.
AVERAGEIFS
Returns the average of all cells that meet multiple criteria.
MAXIFS
Returns the maximum value among cells specified by a given set of conditions or criteria.
MINIFS
Returns the minimum value among cells specified by a given set of conditions or criteria.
STDEV
Estimates standard deviation based on a sample.
STDEV.P
Calculates the standard deviation based on the entire population.
VAR
Calculates the variance based on a sample of data.
CORREL
Returns the correlation coefficient between two data sets.
PERCENTILE
Returns the k-th percentile of values in a range.
QUARTILE
Returns the quartile of a data set based on percentile values from 0 to 1.
FORECAST
Calculates or predicts a future value by using existing values along a linear trend.
TREND
Returns values along a linear trend.
FREQUENCY
Calculates how often values occur within a range of values and returns a vertical array of numbers.
VAR.P
Calculates the variance of a population based on the entire set of provided numbers.
VARA
Calculates the variance based on a sample, including numbers, text, and logical values.
AVERAGEA
Calculates the average of a list of arguments, including numbers, text, and logical values.
GEOMEAN
Returns the geometric mean of an array or range of positive numeric data.
HARMEAN
Returns the harmonic mean of a data set.
MODE.SNGL
Returns the most frequently occurring value in a data set.
MODE.MULT
Returns a vertical array of the most frequently occurring values in a data set.
PERCENTILE.EXC
Returns the k-th percentile of values in a range, where k is in the range 0 to 1, exclusive.
PERCENTRANK
Returns the rank of a value in a data set as a percentage of the data set.
RANK.EQ
Returns the rank of a number in a list of numbers.
RANK.AVG
Returns the rank of a number in a list of numbers, using the average rank for tied values.
SLOPE
Returns the slope of the linear regression line through the given data points.
INTERCEPT
Calculates the y-intercept of a linear regression line using existing x and y values.
RSQ
Returns the square of the Pearson product-moment correlation coefficient for data points in two arrays.
STANDARDIZE
Returns a normalized value from a distribution characterized by mean and standard_dev.
NORM.DIST
Returns the normal distribution for the specified mean and standard deviation.
NORM.INV
Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.
VAR.S
Calculates the variance based on a sample of data.
STDEV.S
Calculates the standard deviation based on a sample of a population.
COVARIANCE.P
Returns the population covariance, the average of the products of deviations for each data point pair in two data sets.
COVARIANCE.S
Returns the sample covariance, which is the average of the products of deviations for each data point pair in two data sets.
SKEW
Returns the skewness of a distribution, which characterizes the degree of asymmetry of a distribution around its mean.
KURT
Returns the kurtosis of a data set.
DEVSQ
Returns the sum of squares of deviations of data points from their sample mean.
AVEDEV
Returns the average of the absolute deviations of data points from their mean.
TRIMMEAN
Returns the mean of the interior of a data set by excluding a specified percentage of data points from the top and bottom tails.
BINOM.DIST
Returns the individual term binomial distribution probability.
POISSON.DIST
Returns the Poisson distribution for a given number of events and mean.
NORM.S.DIST
Returns the standard normal distribution for a given value.
LINEST
Calculates the statistics for a line by using the 'least squares' method to calculate a straight line that best fits your data.
GROWTH
Calculates predicted exponential growth by using existing data.
FISHER
Returns the Fisher transformation for a specified value.
FISHERINV
Returns the inverse of the Fisher transformation for a specified value.
STEYX
Returns the standard error of the predicted y-value for each x in the regression.
PEARSON
Returns the Pearson product-moment correlation coefficient of two data sets.
GAMMALN
Returns the natural logarithm of the gamma function, Γ(x).
PMT
一定の利率の借入金の定期的な支払額を計算します。
NPV
一連の将来のキャッシュフローの正味現在価値を計算します。
FV
Calculates the future value of an investment based on periodic, constant payments and a constant interest rate.
PV
Calculates the present value of a loan or an investment based on a constant interest rate.
IRR
Returns the internal rate of return for a series of cash flows represented by the numbers in values.
XIRR
Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.
XNPV
Calculates the net present value for a schedule of cash flows that is not necessarily periodic.
RATE
Returns the interest rate per period of an annuity.
NPER
Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
IPMT
Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
PPMT
Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.
SLN
Calculates the straight-line depreciation of an asset for one period.
DB
Calculates the depreciation of an asset for a specified period using the fixed-declining balance method.
DDB
Returns the depreciation of an asset for a specified period using the double-declining balance method or some other specified rate.
SYD
Returns the sum-of-years' digits depreciation of an asset for a specified period.
MIRR
Calculates the modified internal rate of return for a series of periodic cash flows.
CUMIPMT
Returns the cumulative interest paid on a loan between two specified periods.
CUMPRINC
Returns the cumulative principal paid on a loan between two periods.
EFFECT
Calculates the effective annual interest rate for a given nominal annual interest rate and the number of compounding periods per year.
NOMINAL
Returns the annual nominal interest rate for a given effective interest rate and number of compounding periods per year.
PDURATION
Returns the number of periods required for an investment to reach a specified value.
RRI
Returns the equivalent interest rate for the growth of an investment.
DOLLARDE
Converts a dollar price expressed as a fraction into a dollar price expressed as a decimal number.
DOLLARFR
Converts a dollar price expressed as a decimal number into a dollar price expressed as a fraction.
ISPMT
Calculates the interest paid during a specific period of an investment.
VDB
Returns the depreciation of an asset for any given period using the double-declining balance method or some other method you specify.
PRICE
Returns the price per $100 face value of a security that pays periodic interest.
YIELD
Returns the yield on a security that pays periodic interest.
DISC
Returns the discount rate for a security.
INTRATE
Calculates the interest rate for a fully invested security.
DURATION
Returns the Macauley duration for a security with an assumed par value of $100.
COUPNUM
Returns the number of coupons payable between the settlement date and maturity date.
ACCRINT
Returns the accrued interest for a security that pays periodic interest.
COUPDAYS
Returns the number of days in the coupon period that contains the settlement date.
COUPNCD
Returns the next coupon date after the settlement date.
YIELDMAT
Returns the annual yield of a security that pays interest at maturity.
PRICEMAT
Returns the price per $100 face value of a security that pays interest at maturity.
TBILLPRICE
Calculates the price per $100 face value for a Treasury bill.
TBILLYIELD
Calculates the yield for a Treasury bill.
FVSCHEDULE
Returns the future value of an initial principal after applying a series of compound interest rates.
AMORLINC
Calculates the depreciation for each accounting period using a linear depreciation method.
MDURATION
Returns the modified Macauley duration for a security with an assumed par value of $100.