Excel 関数早見表 (チートシート).
デスクワークの即時リファレンス。検索、ワンクリック構文コピー、すっきり印刷に対応。
| カテゴリ | 関数一覧 | 構文 | 説明 | コピー |
|---|---|---|---|---|
| 検索/行列 | XLOOKUP | =XLOOKUP(検索値, 検索配列, 戻り配列, [見つからない場合], [一致モード]) | 範囲または配列を検索し、対応する2つ目の範囲または配列から一致する項目を返します。 | |
| 検索/行列 | VLOOKUP | =VLOOKUP(検索値, 範囲, 列番号, [検索方法]) | 表の左端の列で値を検索し、同じ行の指定した列にある値を返します。 | |
| 検索/行列 | INDEX & MATCH | =INDEX(配列, MATCH(検索値, 検査範囲, 0)) | INDEXとMATCHを組み合わせて、列制限のない柔軟なデータ照合・検索を実現します。 | |
| 論理 | IF | =IF(論理式, [真の場合], [偽の場合]) | 条件が満たされているかどうかを判定し、真の場合と偽の場合で異なる値を返します。 | |
| 論理 | IFS | =IFS(論理式1, 真の場合1, [論理式2, 真の場合2], ...) | 複数の条件が満たされているかを順次判定し、最初にTRUEとなった条件に対応する値を返します。 | |
| 論理 | IFERROR | =IFERROR(値, エラーの場合の値) | 数式がエラーとなる場合に指定した代替値を返し、正常な場合は計算結果を返します。 | |
| 数学/統計 | SUMIFS | =SUMIFS(合計対象範囲, 条件範囲1, 条件1, [条件範囲2, 条件2], ...) | 複数の条件をすべて満たすセル範囲の数値を合計します。 | |
| 数学/統計 | COUNTIF | =COUNTIF(range, criteria) | Counts the number of cells within a range that meet a single specified condition or criteria. | |
| Statistical & Analysis | COUNTIFS | =COUNTIFS(条件範囲1, 条件1, [条件範囲2, 条件2], ...) | 複数の範囲内で複数の条件をすべて満たすセルの個数をカウントします。 | |
| テキスト | TEXTJOIN | =TEXTJOIN(区切り文字, 空白を無視, 文字列1, [文字列2], ...) | 区切り文字を指定して、複数のセル範囲や文字列を空白セルを無視しながら結合します。 | |
| 動的配列 | FILTER | =FILTER(配列, 含む, [空の場合]) | 指定した条件に基づいてデータ配列を動的に抽出し、自動展開（スピル）します。 | |
| 動的配列 | UNIQUE | =UNIQUE(配列, [列単位], [1回のみ出現]) | リストまたは範囲から重複を排除し、一意（ユニーク）な値のリストを返します。 | |
| 日付/時刻 | TODAY | =TODAY() | 現在の日付をシリアル値として返します。ブックを開くたびに自動更新されます。 | |
| 日付/時刻 | NETWORKDAYS | =NETWORKDAYS(開始日, 終了日, [祭日]) | 開始日と終了日の間の実働日数（土日・祝日を除く稼働日数）を計算します。 | |
| 財務 | PMT | =PMT(利率, 期間, 現在価値, [将来価値], [支払期日]) | 一定の金利と均等支払い方式に基づいて、融資の定期支払額（返済額）を計算します。 | |
| 数学/統計 | SUM | =SUM(数値1, [数値2], ...) | セル範囲に含まれるすべての数値を合計します。 | |
| Statistical & Analysis | AVERAGE | =AVERAGE(数値1, [数値2], ...) | 指定した範囲内の数値の算術平均（アベレージ）を計算します。 | |
| Statistical & Analysis | COUNT | =COUNT(値1, [値2], ...) | 指定した範囲内で数値が含まれているセルの個数を数えます。 | |
| テキスト | LEFT | =LEFT(文字列, [文字数]) | 文字列の先頭（左端）から指定した文字数の文字を取り出します。 | |
| テキスト | RIGHT | =RIGHT(文字列, [文字数]) | 文字列の末尾（右端）から指定した文字数の文字を取り出します。 | |
| テキスト | TRIM | =TRIM(文字列) | 単語間の1文字分のスペースを除き、文字列の前後や連続した不要な空白をすべて削除します。 | |
| テキスト | PROPER | =PROPER(文字列) | 文字列内の各単語の先頭文字を大文字に変換し、その他の文字を小文字に変換します。 | |
| 数学/統計 | ROUND | =ROUND(数値, 桁数) | 数値を指定した桁数（小数点以下または整数部）に四捨五入します。 | |
| 論理 | AND | =AND(論理式1, [論理式2], ...) | すべての引数がTRUEである場合にTRUEを返し、1つでもFALSEがあればFALSEを返します。 | |
| 論理 | OR | =OR(論理式1, [論理式2], ...) | いずれかの引数がTRUEであればTRUEを返し、すべての引数がFALSEの場合にのみFALSEを返します。 | |
| テキスト | CONCAT | =CONCAT(テキスト1, [テキスト2], ...) | 区切り記号なしで、複数の範囲やセルに含まれる文字列を1つに結合します。 | |
| テキスト | LEN | =LEN(文字列) | 文字列に含まれる文字数（スペースや句読点を含む）を返します。 | |
| 検索/行列 | HLOOKUP | =HLOOKUP(検索値, 範囲, 行番号, [検索方法]) | テーブルの最上行で値を検索し、同じ列の指定した行にある値を返します。 | |
| Information | ISBLANK | =ISBLANK(value) | Checks whether a cell is completely empty and returns TRUE or FALSE. | |
| Information | ISNUMBER | =ISNUMBER(value) | Checks whether a value is numeric and returns TRUE or FALSE. | |
| Information | ISTEXT | =ISTEXT(value) | Checks whether a value is text and returns TRUE or FALSE. | |
| Information | ISERROR | =ISERROR(value) | Checks whether a value is any Excel error and returns TRUE or FALSE. | |
| Database | DSUM | =DSUM(database, field, criteria) | Sums the numbers in a database column that match specified criteria. | |
| Database | DCOUNT | =DCOUNT(database, field, criteria) | Counts the numeric cells in a database column that match criteria. | |
| Database | DAVERAGE | =DAVERAGE(database, field, criteria) | Averages the values in a database column that match conditions. | |
| Engineering | CONVERT | =CONVERT(number, from_unit, to_unit) | Converts a number from one measurement unit to another. | |
| Engineering | DELTA | =DELTA(number1, [number2]) | Tests whether two numbers are equal (returns 1 if equal, 0 if not). | |
| Engineering | BIN2DEC | =BIN2DEC(number) | Converts a binary number to a decimal integer. | |
| 動的配列 | SORT | =SORT(array, [sort_index], [sort_order], [by_col]) | Sorts the contents of a range or array dynamically. | |
| 動的配列 | SORTBY | =SORTBY(array, by_array1, [sort_order1], ...) | Sorts a range based on values in a separate range or array. | |
| 動的配列 | SEQUENCE | =SEQUENCE(rows, [columns], [start], [step]) | Generates a list or 2D grid of sequential numbers. | |
| 動的配列 | TOCOL | =TOCOL(array, [ignore], [scan_by_column]) | Transforms a 2D table or array into a single vertical column. | |
| Compatibility | RANK | =RANK(number, ref, [order]) | Returns the rank of a number in a list of numbers (Excel 2007 compatibility). | |
| Compatibility | MODE | =MODE(number1, [number2], ...) | Returns the most frequently occurring value in an array or data set. | |
| Google Sheets | ARRAYFORMULA | =ARRAYFORMULA(array_formula) | Enables array calculations across entire columns in Google Sheets. | |
| Google Sheets | QUERY | =QUERY(data, query, [headers]) | Runs Google Visualization API SQL-like queries on data tables. | |
| Google Sheets | SPARKLINE | =SPARKLINE(data, [options]) | Generates a miniature chart within a single spreadsheet cell. | |
| 財務 | FV | =FV(rate, nper, pmt, [pv], [type]) | Calculates the future value of an investment with periodic payments. | |
| 財務 | PV | =PV(rate, nper, pmt, [fv], [type]) | Returns the present value of a series of future cash flows. | |
| 財務 | NPV | =NPV(rate, value1, [value2], ...) | Calculates the net present value of an investment using a discount rate. | |
| 数学/統計 | ABS | =ABS(number) | Returns the absolute (positive) value of a number. | |
| 数学/統計 | MOD | =MOD(number, divisor) | Returns the remainder after a number is divided by a divisor. | |
| 数学/統計 | POWER | =POWER(number, power) | Returns the result of a number raised to a power. | |
| 数学/統計 | SQRT | =SQRT(number) | Returns the positive square root of a number. | |
| 数学/統計 | PRODUCT | =PRODUCT(number1, [number2], ...) | Multiplies all the numbers given as arguments. | |
| 数学/統計 | CEILING | =CEILING(number, significance) | Rounds a number up to the nearest multiple of significance. | |
| 数学/統計 | FLOOR | =FLOOR(number, significance) | Rounds a number down to the nearest multiple of significance. | |
| 論理 | SWITCH | =SWITCH(expression, val1, result1, [default]) | Evaluates an expression against a list of values and returns the matching result. | |
| 論理 | NOT | =NOT(logical) | Reverses the logic of its argument (TRUE becomes FALSE, FALSE becomes TRUE). | |
| 論理 | XOR | =XOR(logical1, [logical2], ...) | Returns exclusive OR: TRUE if an odd number of arguments evaluate to TRUE. | |
| 論理 | IFNA | =IFNA(value, value_if_na) | Catches and replaces #N/A errors specifically while preserving other errors. | |
| 検索/行列 | INDEX | =INDEX(array, row_num, [column_num]) | Returns the value of a cell at a specific row and column intersection. | |
| 検索/行列 | MATCH | =MATCH(lookup_value, lookup_array, [match_type]) | Returns the relative numeric position of an item in a range. | |
| 検索/行列 | CHOOSE | =CHOOSE(index_num, value1, [value2], ...) | Returns a value from a list of arguments based on an index number. | |
| Reference & Information | OFFSET | =OFFSET(reference, rows, cols, [height], [width]) | Constructs a reference offset from a starting cell by rows and columns. | |
| テキスト | MID | =MID(text, start_num, num_chars) | Extracts a specific number of characters from text starting at any position. | |
| テキスト | UPPER | =UPPER(text) | Converts all letters in a text string to uppercase. | |
| テキスト | LOWER | =LOWER(text) | Converts all letters in a text string to lowercase. | |
| テキスト | SUBSTITUTE | =SUBSTITUTE(text, old_text, new_text, [instance_num]) | Replaces occurrences of existing text with new text in a string. | |
| テキスト | REPLACE | =REPLACE(old_text, start_num, num_chars, new_text) | Replaces part of a text string with different text based on character count. | |
| テキスト | VALUE | =VALUE(text) | Converts a text string that represents a number into a numeric value. | |
| 日付/時刻 | DATE | =DATE(year, month, day) | Creates a valid date serial number from separate Year, Month, and Day. | |
| 日付/時刻 | DAYS | =DAYS(end_date, start_date) | Returns the number of calendar days between two dates. | |
| 日付/時刻 | EDATE | =EDATE(start_date, months) | Returns the date a specified number of months before or after a start date. | |
| 日付/時刻 | EOMONTH | =EOMONTH(start_date, months) | Returns the last day of the month before or after a specified number of months. | |
| 日付/時刻 | YEAR | =YEAR(serial_number) | Extracts the 4-digit year from a date value. | |
| 日付/時刻 | MONTH | =MONTH(serial_number) | Extracts the month (1 to 12) from a date value. | |
| 日付/時刻 | DAY | =DAY(serial_number) | Extracts the day of the month (1 to 31) from a date value. | |
| Statistical & Analysis | MEDIAN | =MEDIAN(number1, [number2], ...) | Returns the median (middle numeric value) of a data set. | |
| Statistical & Analysis | MIN | =MIN(number1, [number2], ...) | Returns the smallest (minimum) numeric value in a set of values. | |
| Statistical & Analysis | MAX | =MAX(number1, [number2], ...) | Returns the largest (maximum) numeric value in a set of values. | |
| Statistical & Analysis | COUNTA | =COUNTA(value1, [value2], ...) | Counts the number of cells in a range that are not empty. | |
| Statistical & Analysis | COUNTBLANK | =COUNTBLANK(range) | Counts empty cells in a specified range of cells. | |
| Statistical & Analysis | LARGE | =LARGE(array, k) | Returns the k-th largest value in a data set. | |
| Statistical & Analysis | SMALL | =SMALL(array, k) | Returns the k-th smallest value in a data set. |
該当する数式が見つかりませんでした。
検索ワードを変更するか、カテゴリータブを切り替えてみてください。
=XLOOKUP(検索値, 検索配列, 戻り配列, [見つからない場合], [一致モード])範囲または配列を検索し、対応する2つ目の範囲または配列から一致する項目を返します。
=INDEX(配列, MATCH(検索値, 検査範囲, 0))INDEXとMATCHを組み合わせて、列制限のない柔軟なデータ照合・検索を実現します。
=IFS(論理式1, 真の場合1, [論理式2, 真の場合2], ...)複数の条件が満たされているかを順次判定し、最初にTRUEとなった条件に対応する値を返します。
=SUMIFS(合計対象範囲, 条件範囲1, 条件1, [条件範囲2, 条件2], ...)複数の条件をすべて満たすセル範囲の数値を合計します。
=COUNTIF(range, criteria)Counts the number of cells within a range that meet a single specified condition or criteria.
=COUNTIFS(条件範囲1, 条件1, [条件範囲2, 条件2], ...)複数の範囲内で複数の条件をすべて満たすセルの個数をカウントします。
=TEXTJOIN(区切り文字, 空白を無視, 文字列1, [文字列2], ...)区切り文字を指定して、複数のセル範囲や文字列を空白セルを無視しながら結合します。
=NETWORKDAYS(開始日, 終了日, [祭日])開始日と終了日の間の実働日数（土日・祝日を除く稼働日数）を計算します。
=PMT(利率, 期間, 現在価値, [将来価値], [支払期日])一定の金利と均等支払い方式に基づいて、融資の定期支払額（返済額）を計算します。
=AVERAGE(数値1, [数値2], ...)指定した範囲内の数値の算術平均（アベレージ）を計算します。
=OR(論理式1, [論理式2], ...)いずれかの引数がTRUEであればTRUEを返し、すべての引数がFALSEの場合にのみFALSEを返します。
=ISBLANK(value)Checks whether a cell is completely empty and returns TRUE or FALSE.
=ISNUMBER(value)Checks whether a value is numeric and returns TRUE or FALSE.
=ISTEXT(value)Checks whether a value is text and returns TRUE or FALSE.
=ISERROR(value)Checks whether a value is any Excel error and returns TRUE or FALSE.
=DSUM(database, field, criteria)Sums the numbers in a database column that match specified criteria.
=DCOUNT(database, field, criteria)Counts the numeric cells in a database column that match criteria.
=DAVERAGE(database, field, criteria)Averages the values in a database column that match conditions.
=CONVERT(number, from_unit, to_unit)Converts a number from one measurement unit to another.
=DELTA(number1, [number2])Tests whether two numbers are equal (returns 1 if equal, 0 if not).
=BIN2DEC(number)Converts a binary number to a decimal integer.
=SORT(array, [sort_index], [sort_order], [by_col])Sorts the contents of a range or array dynamically.
=SORTBY(array, by_array1, [sort_order1], ...)Sorts a range based on values in a separate range or array.
=SEQUENCE(rows, [columns], [start], [step])Generates a list or 2D grid of sequential numbers.
=TOCOL(array, [ignore], [scan_by_column])Transforms a 2D table or array into a single vertical column.
=RANK(number, ref, [order])Returns the rank of a number in a list of numbers (Excel 2007 compatibility).
=MODE(number1, [number2], ...)Returns the most frequently occurring value in an array or data set.
=ARRAYFORMULA(array_formula)Enables array calculations across entire columns in Google Sheets.
=QUERY(data, query, [headers])Runs Google Visualization API SQL-like queries on data tables.
=SPARKLINE(data, [options])Generates a miniature chart within a single spreadsheet cell.
=FV(rate, nper, pmt, [pv], [type])Calculates the future value of an investment with periodic payments.
=PV(rate, nper, pmt, [fv], [type])Returns the present value of a series of future cash flows.
=NPV(rate, value1, [value2], ...)Calculates the net present value of an investment using a discount rate.
=MOD(number, divisor)Returns the remainder after a number is divided by a divisor.
=POWER(number, power)Returns the result of a number raised to a power.
=PRODUCT(number1, [number2], ...)Multiplies all the numbers given as arguments.
=CEILING(number, significance)Rounds a number up to the nearest multiple of significance.
=FLOOR(number, significance)Rounds a number down to the nearest multiple of significance.
=SWITCH(expression, val1, result1, [default])Evaluates an expression against a list of values and returns the matching result.
=NOT(logical)Reverses the logic of its argument (TRUE becomes FALSE, FALSE becomes TRUE).
=XOR(logical1, [logical2], ...)Returns exclusive OR: TRUE if an odd number of arguments evaluate to TRUE.
=IFNA(value, value_if_na)Catches and replaces #N/A errors specifically while preserving other errors.
=INDEX(array, row_num, [column_num])Returns the value of a cell at a specific row and column intersection.
=MATCH(lookup_value, lookup_array, [match_type])Returns the relative numeric position of an item in a range.
=CHOOSE(index_num, value1, [value2], ...)Returns a value from a list of arguments based on an index number.
=OFFSET(reference, rows, cols, [height], [width])Constructs a reference offset from a starting cell by rows and columns.
=MID(text, start_num, num_chars)Extracts a specific number of characters from text starting at any position.
=SUBSTITUTE(text, old_text, new_text, [instance_num])Replaces occurrences of existing text with new text in a string.
=REPLACE(old_text, start_num, num_chars, new_text)Replaces part of a text string with different text based on character count.
=VALUE(text)Converts a text string that represents a number into a numeric value.
=DATE(year, month, day)Creates a valid date serial number from separate Year, Month, and Day.
=DAYS(end_date, start_date)Returns the number of calendar days between two dates.
=EDATE(start_date, months)Returns the date a specified number of months before or after a start date.
=EOMONTH(start_date, months)Returns the last day of the month before or after a specified number of months.
=MONTH(serial_number)Extracts the month (1 to 12) from a date value.
=DAY(serial_number)Extracts the day of the month (1 to 31) from a date value.
=MEDIAN(number1, [number2], ...)Returns the median (middle numeric value) of a data set.
=MIN(number1, [number2], ...)Returns the smallest (minimum) numeric value in a set of values.
=MAX(number1, [number2], ...)Returns the largest (maximum) numeric value in a set of values.
=COUNTA(value1, [value2], ...)Counts the number of cells in a range that are not empty.
=COUNTBLANK(range)Counts empty cells in a specified range of cells.
=LARGE(array, k)Returns the k-th largest value in a data set.
=SMALL(array, k)Returns the k-th smallest value in a data set.