Excel関数の勉強　２

「関数の勉強１，２」では、「引数を主眼」にして学びます。」

主な内容
１．IF 関数について
２．AND 関数について　
３．OR 関数について　
４．AND 関数、OR 関数の「判定の結果」に対して「指示を与える」には ： (IF 関数に、組み込む(ネストする)
５．IF 関数を使って、引数タイプの１つ１つを検証
６．セルやセル範囲に名前を付ける
７．専ら[IF]関数を使って、７種別の会費算出
８．[IF]関数に[AND]関数をネストして会費算出
９．[VLOOKUP] 関数を使って会費算出
１０．引数に、配列（行方向や列方向に連続した値を一纏めしたデータ）を使う

前ページで勉強した[関数]とその[引数]を整理

()内が[引数]です。

引数が無い関数

[TODAY] 　関数　書式は TODAY() です。（注：引数が無くても「 TODAY() 」と記述）

[N0W] 　　関数　書式は N0W() 　です。（注：上と同様）

[PI]　　　関数　書式は PI() 　 です。（注：上と同様）

引数が１個の、[日付]、[時刻]の関数

[YEAR]　　関数　書式は YEAR(シリアル値) 　です。

[MONTH] 　関数　書式は MONTH(シリアル値)　です。

[DAY] 　　関数　書式は DAY(シリアル値) 　 です。

[HOUR]　　関数　書式は HOUR(シリアル値) 　です。

[MINUTE]　関数　書式は MINUTE(シリアル値) です。

[SECOND]　関数　書式は SECOND(シリアル値) です。

その他引数が１個の関数

[SQRT]　　関数　書式は SQRT(数値) です。

[INT]　　 関数　書式は INT(数値)　です。

引数が２個の関数

[ROUND]　 関数　書式は ROUND(数値,桁数) 　です。

[ROUNDUP] 関数　書式は ROUNDUP(数値,桁数) です。

[ROUNDDOWN] 関数　書式は ROUNDDOWN(数値,桁数) です。

[TRUNC] 　関数　書式は TRUNC(数値,桁数) 　です。

[TEXT]　　関数　書式は TEXT(値,表示形式)　です。

引数が３０個の関数

[SUM] 　　関数　書式は SUM(数値1,数値2,・・・) です。

◇引数のタイプ一覧表◇

1. 　論 理 式　：左辺と右辺を、下記比較演算子を使って比較します。(IF,AND,OR,NOT関数で使用されます。)
2. 　論 理 値　：TRUE（真）とFALSE（偽）の二つがあります。(IF,TRUE,FALSE関数で使用されます。)
3. 　数　　値　：整数、少数、負数を含むあらゆる数値です。
4. 　文　　字　："日付"、"文字列"などの普通の文字ですが、関数の中で引数として使うときは、
   　　　　　　　"文字列"のように前後を["]（ダブルクォーテーションで囲んで使います。
5. 　セル参照　：(1)セル単独指定
   　　　　　　：(2)セル範囲指定　[A1セル番地〜D10セル番地]は[A1:D10]の様に[:]コロンで区切ります。
   　　　　　　；(3)セル不連続複数指定　セル番地を[,]カンマで区切ります。例えば「A1、C5、D10」です。
6. 　数　　式　：[ =B2+C2 ]または、[ =1+2 ]などの計算式
7. 　関　　数　：関数を入れ子（ネスト）にして引数に使えます。
8. 　名　　前　：定義された名前、セル範囲に付けられた名前など使えます
9. 　配　　列　：配列とは同じ条件で整頓された引数の範囲を[{ }]の括弧で囲んで指定します。
10. 　エラー値　：####, #VALUE!, #DIV/0!, #NAME?, #N/A, #REF!, #NUM!, #NULL! の８種類があります。
    エラー値の詳細については、こちらを参照ください。

論理関数

Excelのワークシート関数（標準で装備）には、論理関数が６個存在しています。

1. [IF]　　関数　書式：IF(論理式(引数1), 真の場合(引数2), 偽の場合(引数3)
   　　　　　　　　　　　　　　論理式の結果に応じて、指定された値を返します。
2. [AND] 　関数　書式：AND(論理式1(引数1),論理式2(引数2),・・・)　
   　　　　　　　　　　　　　　すべての引数が TRUE のと、き論理値 TRUE を返します。
3. [OR]　　関数　書式：OR(論理式1(引数1),論理式2(引数2),・・・)
   　　　　　　　　　　　　　　引数に１つでも TRUE があれば TRUE を返します。
4. [NOT] 　関数　書式：NOT(論理式)
   　　　　　　　　　　　　　　引数が FALSE の場合は TRUE、 TRUE の場合は FALSE を返します。
5. [TRUE]　関数　書式：TRUE()
   　　　　　　　　　　　　　　論理値 TRUE を返します。
6. [FALSE] 関数　書式：FALSE()
   　　　　　　　　　　　　　　論理値 FALSE を返します。

IF 関数について　(引数３個)

条件判断と処理の分岐。（YesかNoかを判定し、処理1、処理2を行う。）

書式：IF(論理式, 真の場合, 偽の場合)

(引数１)論理式とは(=条件式)

AとBの２つの事象(数値表示)が有った場合、下記、比較演算子を用いて、条件(条件式)を提示します。
比較演算子(記号)　　　　内容　　　　　　　　　セル参照表示例

• [=] 「等号」　　　　：　左辺と右辺が等しい。　　A1=B1
• [>] 「〜より大きい」：　左辺が右辺より大きい。　A1>B1
• [<] 「〜より小さい」：　左辺が右辺よりも小さい。A1<B1
• [>=]「〜以上」　　　：　左辺が右辺以上である。　A1>=B1
• [<=]「〜以下」　　　：　左辺が右辺以下である。　A1<=B1
• [<>]「不等号」　　　：　左辺と右辺が等しくない。A1<>B1 (A1<>0　は条件式で「A1が0でない」という式です。)

(引数２)真の場合とは

論理式(=条件式)に適った場合(TRUEの場合)は、「数式パレット」引数２に「もし、真の場合なら、こうしなさい」と指示を与えます。

(引数３)偽の場合とは

論理式(=条件式)に不適合の場合(FALSEの場合)は、「数式パレット」引数３に「そうでない偽の場合なら、こうしなさい」と指示を与えます。
しかし、偽の場合の戻り値が欲しい場合が生じた場合には、真の場合の戻り値を[""]で空欄にする手法もあります。
IF 関数の使用法については、こちらのページと、こちらのページを参照ください。

AND 関数について　(引数は、論理式のみｎ個)

書式：AND(論理式1,論理式2,論理式3, ・・・論理式n)

論理式の組み立て方はAND 関数、下記OR 関数共IF 関数の論理式と同様です。

注釈では、「論理式1,論理式2・・・には、結果が TRUE または FALSE になる１〜３０個の条件式を指定できます。」となっています。
「あれも、これも」の、全てが「条件」を満たしているかどうかを判定する関数です。
ただし、判定は[TRUE(真)]か[FALSE(偽)]のみで、だから、「どうしろ、こうしろ」とまでは、指示していません。

OR 関数について　(引数は、論理式のみｎ個)

書式：OR(論理式1,論理式2,論理式3,・・・論理式n)

注釈では、「論理式1,論理式2・・・には、結果が TRUE または FALSE になる１〜３０個の値または論理式を指定できます。」となっています。
「あれか、これか」の、どれか一つでも「条件」を満たしているかどうかを判定する関数です。
ただし、AND 関数同様、判定は[TRUE(真)]か[FALSE(偽)]のみで、だから、「どうしろ、こうしろ」とまでは、指示していません。

AND 関数、OR 関数の「判定の結果」に対して、「指示を与える」には

IF 関数に、組み込む(ネストする)

IF(OR) 関数の使用法については、こちらのページを参照ください。
また、IF(AND) 関数の使用法については、こちらのページを参照ください。

IF 関数を使って、引数タイプの１つ１つを検証してみます。

IF 関数では、「引数１」に[論理式]、「引数２」に[論理値：真の場合]、「引数３」に[論理値：偽の場合]を使用します。

引数に数値を使う、引数に数式を使う、引数に文字を使う

　　A　　　　B　　　  C　　　　D　　　　E　　　　F　　　　G        H　　　　I
　
1　　　　　 国語　　算数　　　合計　AVERAGE関数　　　　　　　　合格基準
2　山田   　100　　　80　　　 180　　　90　　　　　　　合計       180　　　以上
3　川田　　　90　　　70　　　 160　　　80　　　　　　　AVERAGE     90　　　以上

問題１．
合計点180以上を合格、それ以外は不合格です。数値と数式で計算しなさい。
山田君の場合
IF(100+80>=180,"合格","不合格")

川田君の場合
IF(90+70>=180,"合格","不合格")
(セル参照で、済む場合は、数値の使用はできるだけ、避けた方が良いです。セル数値に変更が生じた場合、既に出来上がった数式には反映されません。 セルは変数の入れ物と考えましょう。)

引数にセル参照を使う、引数に数式を使う、引数に文字を使う

問題２．
上の表で、合計点180以上を合格、それ以外は不合格です。セル参照と数式で計算しなさい。
山田君の場合
IF(B2+C2)>=H2,"合格","不合格")

川田君の場合
IF(B3+C3>=H2,"合格","不合格")

引数に関数を使う、引数にセル参照を使う、引数に文字を使う

問題３．
上の表で、AVERAGE90以上を合格、それ以外は不合格です。セル参照とAVERAGE関数をネストして計算しなさい。
山田君の場合
IF(AVERAGE(B2:C2)>=H3,"合格","不合格")

川田君の場合
IF(AVERAGE(B3:C3)>=H3,"合格","不合格")

引数に名前(セルに付けられた名前、セル範囲に付けられた名前)を使う

セルやセル範囲に名前を付ける

１．セル範囲[B2:C2]に、「山田成績」と名前をつけます。
付ける方法

1. セル範囲[B2:C2]を選択して、名前ボックスに、例えば、山田成績 と書き込むだけです。
   同様に、セル範囲[B3:C3]に、「川田成績」と名前をつけます。
2. 名前の定義ダイアログボックスで付ける方法
   セル範囲[B2:C2]を選択して、挿入(I)メニュー →名前(N)ポイント→定義(D)をクリック→ 「名前の定義ダイアログボックス」で「名前(W)テキストボックス」に名前を入力し、[OK]ボタンをクリックします。
   なお、作成する名前は、既定では絶対参照になります。
3. 名前の作成ダイアログボックスで付ける方法(列見出しを含めた場合)
   「見出し(国語)」を含め[B1:Bn]を選択して、挿入(I)メニュー →名前(N)ポイント→作成(C)をクリック→「名前の作成」ダイアログボックスには、 既に「上端行(T)」にチェックが入っているので、[OK]ボタンをクリックします。名前は「国語」になります。
4. その他、「数式や定数に名前を付ける方法」などがあります。
5. 名前の削除方法
   挿入(I)メニュー →名前(N)ポイント→定義(D)をクリック→「名前の定義ダイアログボックス」で削除したい名前を選択し、[削除]ボタンをクリックします。

２．セル[H3]の90を選択して、「基準値」と名前を付けます。

　　A　　　　B　　　  C　　　　　D　　　　　　　　E　　　　　　　F　　　　G        H　　　　I
　
1　　　　　 国語　　算数　　　合計のSUM関数　　　AVERAGE関数　　　　　　　　　合格基準
2　山田   　100　　　80　　　=SUM(山田成績) 　=AVERAGE(山田成績)　　　合計      　 180　　　以上
3　川田　　　90　　　70　　　=SUM(川田成績)　 =AVERAGE(川田成績) 　　 AVERAGE 　基準値　　　以上

すると

山田君の合計点は[=SUM(山田成績)]、アベレージは、[=AVERAGE(山田成績)]で求まります。

川田君も同様です。

IF 関数を使って、合格、不合格の判定をします。

山田君の場合(結果は合格)

=IF(AVERAGE(山田成績)>=基準値,"合格","不合格")

川田君の場合(結果は不合格)

=IF(AVERAGE(川田成績)>=基準値,"合格","不合格")

専ら[IF]関数を使って、７種別(実際は８種別になります。)の会費算出

直接「年齢」列を対象にすると、うまく行きません。そこで、「年齢性別区分」列を作り、この列を対象にして、会費を算定しました。 「複数の値を基準にし条件判断をおこなうには、IF 関数を組み合わせます。こちらを参照ください。[IF]文は下図から読み取り願います。
また、[D3]に、「 =IF(C3<74,"一般",IF(C3<=87,"老人",IF(C3<=99,"高齢","100以上"))) 」を
また、[E3]に、「 =IF(B3="女性",B3&D3,D3) 」が埋め込まれています。

[IF]関数に[AND]関数をネストして会費算出

条件に「性別」「年齢」「区分」と分類されていますが、「AND」関数の場合、３０個の条件が設定できます。
[IF-AND]文は下図から読み取り願います。他のところに埋め込み書式はありません。また、特に年齢順に揃える必要はありません。自由です。

[VLOOKUP] 関数を使って会費算出

IF関数のみより、大幅に、書式が簡単になります。引数は従来のスタイルとは異なります。
検索値(引数1),範囲(引数2),列番号(引数3),検索の型(引数4)

書式：検索値,範囲,列番号,検索の型

下表をコピーして[A1]セルに貼り付けお試しください。また、年齢と性別を変えてみてください。
なお、[D4]に、「=IF(C4<74,"一般",IF(C4<=87,"老人",IF(C4<=99,"高齢","100以上")))」
また、[E4]に、「=IF(B4="女性",B4&D4,D4)」が、埋め込まれています。「B4&D4」で使用の「文字列演算子 &(アンバサンド)」は、２つのセルの文字列を結合、または連結して、1 つの連続する文字列の値を作成しています。
注意点：参照表は、昇順に並べ替えしておくこと。
VLOOKUP関数の使用法については、こちらを参照ください。

引数に、配列（行方向や列方向に連続した値を一纏めしたデータ）を使う

配列数式：行方向や列方向に連続した値を一纏めしたデータを参照し、配列に含まれる値ごと（複数の）の計算結果を配列範囲（戻り値のセル範囲）に返す数式のことです。 複数の計算を行い、1 つまたは複数の結果を返す数式で、計算を1つの数式で行うことができます。
配列数式については、こちらを参照ください。

本ページ上段の「引数タイプ一覧 No.10」の「エラー値」につて

エラー値８種類を直接引数として使う例は見当たりません。エラー値は戻り値として現れ、その現れたセル、または、 データが未入力のため当然エラー表示が予想されるセルを参照して、非表示の対策を講じているのです。
エラー値の解消法は、こちらを参照ください。

トップへ戻る     いろいろな関数目次へ戻る
関数の勉強 １ へ戻る     関数の勉強３へ進む