最も重要な excel 関数トップ 10
1.IF関数
IF 関数は、指定した条件が TRUE の場合は値を返し、それ以外の場合は他の値を返します。簡単に言えば、IF 関数は最初に条件をテストし、その条件の結果に基づいて値を返すことができます。
構文
IF(論理テスト、真の場合の値、偽の場合の値)
引数
- logical_test:評価する条件。
- value_if_true:この条件が TRUE の場合に取得する値。
- value_if_false:この条件が FALSE の場合に取得する値。
コメント
- 実行できるネストされた条件の最大数は 64 です。
- 比較演算子を使用して条件を評価できます。
例
以下の例では、比較演算子を使用してさまざまな条件を評価しました。
- 特定のテキストを使用して、条件が満たされるかどうかの結果を取得しました。
- TRUE と FALSE を使用して結果を取得することもできます。
- 条件が TRUE の場合に結果を取得するために値の指定をスキップすると、ゼロが返されます。
- また、条件が FALSE の場合に結果を取得するために値の指定をスキップすると、ゼロが返されます。
以下の例では、IF 関数を使用して入れ子の数式を作成しました。
条件を指定し、その条件が false の場合は別の IF を使用して別の条件を評価してタスクを実行し、その条件が FALSE の場合は別の IF を使用しました。
このようにして、IF を 5 回使用して入れ子式を作成しました。入れ子式には同じものを 64 回使用できます。
2.IFERROR関数
IFERROR 関数は、エラーが発生した場合に特定の値を返します。簡単に言うと、値をテストし、その値がエラーであれば、指定した値を返します。
構文
IFERROR(値, エラーの場合の値)
引数
- value:エラーをテストする値。
- value_if_error:エラーが発生したときに取得する値。
コメント
- IFERROR 関数は、エラーの種類ではなく、エラーの発生に関係します。
- value または value_if_error を無視すると、結果として 0 が返されます。
- #N/A、#REF!、#DIV/0!、#VALUE!、#NUM!、#NAME? をテストできます。そして#NULL!。
- 配列を評価すると、指定された各要素の結果の配列が返されます。
例
以下の例では、IFERROR 関数を使用して #DIV/0! を置き換えています。意味のあるテキスト付き。
IFERROR は、バージョン 2007 以前とのみ互換性があります。この問題を解決するには、ISERROR を使用します。
3.TRUNC機能
TRUNC 関数は、元の数値を切り捨てた後の整数を返します。簡単に言うと、特定の精度で数値から小数点以下の桁を削除し、結果の整数部分を返します。
構文
TRUNC(数字, [数字_桁])
引数
- 数値:切り捨てたい数値。
- [num_digitals]:数値を切り捨てる精度を指定する数値。
コメント
- 複数の指定を無視すると、エラーが返されます。
- ゼロから丸めます。
- 同じ距離に 2 つの倍数がある場合は、四捨五入している数値の大きい倍数を返します。
例
以下の例では、TRUNC を使用してデータを切り詰め、日付から時間を削除しました。
4.SUMIF関数
SUMIF 関数は、指定した条件を満たす数値の合計を返します。簡単に言えば、条件を満たす値の合計を考慮して計算するだけです。
構文
SUMIF(範囲, 基準, [合計範囲])
引数
- range:条件を確認するセルの範囲。
- 条件:数値、テキスト、式、セル参照、関数などの条件。
- [sum_range]:合計したい値を含むセルの範囲。
コメント
- sum_range を省略すると、範囲内のセルが合計されます。
- テキスト条件を指定する場合、または数学記号を含む条件を指定する場合は、必ず二重引用符を使用してください。二重引用符で囲む必要があります。
- 基準範囲と合計範囲のサイズは同じである必要があります。
例
以下の例では、基準範囲として A1:A9 を指定し、合計範囲として B1:B9 を指定し、その後、値 C を持つ A12 に基準を指定しています。
条件を関数に直接挿入することもできます。以下の例では、アスタリスク ワイルドカードを使用して、アルファベット「S」を含む条件を指定しました。
また、合計範囲を無視すると、基準範囲の合計が表示されます。ただし、これは基準範囲に数値がある場合にのみ可能です。
5. INDEX機能
INDEX 関数は、インデックス番号に基づいて値のリストから値を返します。簡単に言えば、INDEX は値のリストから値を返し、その値の位置を指定する必要があります。
構文
INDEX には 2 つの異なる構文があります。最初の方法では、インデックスの配列形式を使用して、その位置を使用してリストから値を単純に取得できます。
INDEX(配列, row_num, [column_num])
2 番目では、実際にはあまり使用されないスポンサーシップ フォームを使用できますが、宣伝する範囲が複数ある場合に使用できます。
INDEX(参照, 行番号, [列番号], [領域番号])
引数
- array:セルの範囲または配列定数。
- 参照:セルの範囲または複数の範囲。
- row_number:値を取得する行番号。
- [col_number]:値を取得する列の番号。
- [area_number]:複数のセル範囲を参照する場合 (参照構文を使用)、それらすべての中の 1 つの範囲を参照する番号を指定します。
コメント
- row_num 引数と column_num 引数を指定すると、その 2 つの交点にあるセルの値が返されます。
- row_num または column_num を 0 (ゼロ) として指定すると、それぞれ列全体または行全体の値の配列が返されます。
- row_num と column_num が範囲外の場合、#REF! が返されます。エラー。
- area_number が指定した数値範囲より大きい場合は、#REF! が返されます。
例 1 – ARRAY を使用してリストから値を取得する
以下の例では、INDEX 関数を使用して 6 月の数量を取得しました。リストでは、Jun は 6 番目 (6 行目) にあるため、row_number に 6 を指定しました。 INDEX は結果として値 1904 を返しました。
複数の列を含む範囲を参照する場合は、列番号を指定する必要があります。
例 2 – REFERENCE を使用して複数のリストの値を取得する
以下の例では、範囲全体を一度に選択するのではなく、3 つの異なる範囲で選択しています。最後の引数では、area_number に 2 を指定しました。これにより、これら 3 つの異なる範囲から使用する範囲が定義されます。
2 行目では、5 行目の 1 列目を参照します。 INDEX は、2 行目の 5 行目にある値 172 を返しました。
6.VLOOKUP関数
VLOOKUP 関数は、テーブルの最初の列の値を検索し、インデックス番号を使用して、対応する値と同じ行の値を返します。簡単に言えば、垂直方向の検索を実行します。
構文
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
引数
- lookup_value:列内で検索する値。検索値を含むセルを参照することも、その値を関数に直接入力することもできます。
- table_array:値を検索するセルの範囲、名前付き範囲。
- Col_index_num:数値は、値を取得する列番号を表します。
- range_lookup:完全一致の場合は false または 0 を使用し、適切な一致の場合は true または 1 を使用します。デフォルトは True です。
コメント
- VLOOKUP で探している値が見つからない場合は、#N/A が返されます。
- VLOOKUP では、ルックアップ値の右側の値のみが得られます。右側から見たい場合は、INDEX と MATCH を使用します。
- 完全一致を使用すると、列の最初の値とのみ一致します。
- VLOOKUP ではワイルドカードを使用することもできます。
- 適切な一致が必要な場合は TRUE または 1 を使用し、完全一致の場合は FALSE または 0 を使用できます。
- 適切な一致 (True) を使用する場合: 完全に一致するものがない場合は、リスト内の次に小さい値が返されます。
- 探している値がリスト内の最小値より小さい場合、VLOOKUP は #N/A を返します。
- 探している正確な値がある場合は、その正確な値が表示されます。
- リストが昇順に並べ替えられていることを確認してください。
例
1. カテゴリに VLOOKUP を使用する
以下の例では、受け取った成績を含む学生のリストがあり、備考欄には成績に基づいた成績が必要です。
上記ブランドリストにおいて、以下のカテゴリー範囲に応じて備考を追加させて頂きます。
この場合、使用できるオプションが 2 つあります。
最初のオプションは、IF を使用してネストされた数式を作成することです。これには少し時間がかかります。2番目のオプションは、適切な一致を含む VLOOKUP を使用して数式を作成することです。そして、式は次のようになります。
=VLOOKUP(B2,$E$2:$G$5,3,TRUE)
使い方
「MIN MARKS」列を使用して検索値を照合し、「備考」列から値を取得します。
TRUE を使用すると、完全に一致する検索値がない場合、検索値から次に小さい値が返されることはすでに述べました。たとえば、カテゴリ テーブルで値 77 を検索すると、65 が 77 の次の最小値になります。
これが、コメントで「良い」と評価した理由です。
2.VLOOKUP関数でのエラー処理
VLOOKUP を使用するときに発生する最も一般的な問題の 1 つは、一致するものが見つからない場合に #N/A が返されることです。しかし、この問題の解決策はシンプルかつ簡単です。簡単な例で説明しましょう。
以下の例では、名前とその年齢のリストがあり、セル E6 で VLOOKUP 関数を使用してリスト内の名前を検索します。リストにない名前を入力するたびに #N/A が表示されます。
ただし、ここで必要なのは、エラーの代わりに意味のあるメッセージを表示することです。式は次のようになります: =IFNA(VLOOKUP(D6,Sheet3!$A$1:$B$14,2,0),”Not Found”)
仕組み: IFNA は #N/A の値をテストでき、エラーがある場合はエラーの代わりに値を指定できます。
7.IFNA機能
IFNA 関数は、#N/A エラーが発生した場合に特定の値を返します。 IFERROR とは異なり、#N/A エラーのみを評価し、指定した値を返します。
構文
IFNA(値, 値_if_na)
引数
- value: #N/A エラーをテストする値。
- value_if_na:エラーが発生した場合に返す値。
コメント
- 引数を指定しない場合、IFNA は空の文字列 (“”) として扱います。
- 値が配列の場合、結果を配列として返します。
- 他のすべてのエラー #REF!、#DIV/0!、#VALUE!、#NUM!、#NAME? は無視されます。そして#NULL!。
例
VLOOKUP 関数では、ルックアップ値がルックアップ範囲にない場合に #N/A が発生します。これに対して、IFNA を使用して意味のあるメッセージを指定しました。
注: IFNA は Excel 2013 で導入されたため、以前のバージョンでは使用できません。
8. RAND関数
RAND 関数は、0 ～ 1 の範囲の乱数を返します。簡単に言えば、0 ～ 1 の範囲の乱数を生成できます (ワークシートに変更を加えるたびに値が更新されます)。
構文
ランド()
引数
- RAND関数に指定する引数はありません
コメント
- 倍数にゼロを入力すると、結果としてゼロが返されます。
- 複数の指定を無視すると、エラーが返されます。
- ゼロから丸めます。
- 同じ距離に 2 つの倍数がある場合は、四捨五入している数値の大きい倍数を返します。
例
0 から 1 までの数値を指定するだけでなく、2 つの特定の数値の間の乱数を取得するために RAND を使用することもできます。以下の例では、これを使用して 50 ～ 100 の範囲の乱数を生成する数式を作成しました。
この数式をセルに入力すると、RAND によって返された値と使用した数式を乗算して、100 から 50 までの数値が返されます。この式を理解するには、次の 3 つの部分に分ける必要があります。
- まず、大きい数値から小さい数値を検出すると、2 つの数値の差が得られます。
- 次に、この差に、減算後に返された乱数を乗算します。
- そして 3 番目に、この数値を方程式の 3 番目の部分に残っている最小の数値と加算します。
関連: Excel でランダムな文字をすばやく生成する方法
9. SUM関数
SUM 関数は、指定された値の合計を返します。 SUM関数を簡単に言うと、値のリストの合計を計算できます（関数に値を直接入力したり、セル範囲を参照したりできます）。
構文
SUM(数値1,[数値2],…)
引数
- number1 : 数値、数値を含むセル範囲、または数値を含む単一のセル。
- [number2] : 数値、数値を含むセル範囲、または数値を含む単一のセル。
コメント
- テキスト値は無視されます。
例
以下の例では、数値の間にカンマを使用して、数値を関数に直接挿入できます。
範囲を参照して数値の合計を計算することもできます。テキスト、論理値、または空のセルがある場合は、それらを無視します。
参照しているセルにエラー値がある場合、結果として #N/A が返されます。
数値がテキストとしてフォーマットされている場合、それらは無視されます。 SUM を使用する前に、数値に変換することをお勧めします。
10.OR関数
OR 関数は、指定した条件をテストした後、ブール値 (TRUE または FALSE) を返します。簡単に言うと、AND 関数を使用して複数の条件をテストできます。AND 関数は、これらの条件のいずれか (またはすべて) が TRUE の場合に TRUE を返し、これらの条件がすべて FALSE の場合にのみ FALSE を返します。
構文
OR(ロジック1, [ロジック2], …)
引数
- logical1:確認したい条件。
- [logical2]:確認したい追加条件。
コメント
- 参照セルまたはテーブルに空のセルまたはテキストが含まれている場合、値は無視されます。
- 条件の結果は論理値 (TRUE または FALSE) である必要があります。
- 論理値が返されない場合はエラーが返されます。
例
以下の例では、IF 関数を使用して、生徒が 2 つの科目のいずれかで 60 点以上のスコアを獲得した場合に、式が TRUE を返すという条件を作成しました。
次の例では、数式で論理値を取得するために数値を使用しています。上記の条件を逆の順序で実行することもできます。
数値の代わりに TRUE と FALSE を使用できます。 OR 関数は、これらの論理値を数値として扱います。