いろいろな関数　８

主な内容
１．ネスト関数（Excelヘルプより）
２．IF関数にAND関数をネストさせる(OR関数も同様です。)
３．関数の修正
４．「偽の場合」は空欄にする方法
５．[IF関数]に[IF関数]をネストする

今回の関数は[IF]関数にネストする関数です。

IF関数のおさらい（いろいろな関数の３と重複しますが再度）

IF 関数

書式:「 IF(論理式,真の場合,偽の場合) 」

[引数1]は[論理式]です。式と言っても下記のように右辺と左辺を比較して、「＝」か「大きい」か「小さい」かを判断させる式です。

[論理式]で使用される「比較演算子」

• 　左側 = 右側 　　　左側 と 右側が等しい
• 　左側 > 右側 　　　左側 が 右側よりも大きい
• 　左側 < 右側 　　　左側 が 右側よりも小さい
• 　左側 >= 右側 　　 左側 が 右側以上
• 　左側 <= 右側 　　 左側 が 右側以下
• 　左側 <> 右側 　　 左側 と 右側が等しくない

[引数2][引数3]には＜論理式＞の結果に応じて、指定され値を返します。

◇「条件が満たされている場合」（真の場合）は[引数2]で指定した値を返します。特に指定しなければ[TRUE]と返えします。

◇「条件が満たされていない場合」（偽の場合）は[引数3]で指定した値を返します。特に指定しなければ[FALSE]と返します。

◇[引数2][引数3]の[TRUE]や[FALSE]の判定表示に換えて「文字列」で指定できます。但しその場合、文字列を["]のダブル クォテーションで囲む必要があります。

文字列で指定できる関数には、次ページ掲載の[TEXT関数]があります。[IF関数]と共に「文字列指定の利点」を活かし多用されています。

ネスト関数（Excelヘルプより）

IF関数にAND関数をネストさせる(OR関数も同様です。)

IF関数の書式は 「 IF(論理式,真の場合,偽の場合) 」

IF関数の書式の基本形　　 「 =IF(引数1,引数2,引数3) 」

AND(OR)関数の書式は「 AND(論理式1,論理式2,...論理式n) 」

AND(OR)関数の書式の基本形「 AND(引数1,引数2,...,引数n) 」

上に見られるように、IF関数では論理式が１個」のみで「条件」が1つしか設定できません。
それに対して、AND(またはOR)関数では論理式が３０個、即ち条件を３０個も設定ができるのです。
そこで、IF関数に、AND(またはOR)関数をネストして、複数の条件設定をできるようにし、しかもIF関数の「真」「偽」の判定に「好きな文字列」を使用できる利点を活かしているのです。

[IF関数・引数1]の論理式の代わりとして[AND(またはOR)関数]の全式を組み込む

ネストの仕方
上図 EXCELヘルプには「関数ボックス」使用の「関数をネストするには」があります。

下表をExcelの[A1]セルにコピーして、ご使用ください。
右の４項目の各行をクリックすると「数式」が出ますのでそれを参考に試してみてください。

実際に、上を貼り付けた表を使い[IF関数]に[AND関数]をネストさせてみます。

命題は国語且つ数学が、基準点「６５点」以上を合格、それ以外を不合格と表示しなさい。」です。
[H4]から[H13]を「クリア」してください。

手順

1. セル[H4]を選択選択します。（４行目の点数について、判定を表示するセルです。）
2. 関数貼り付けボタン[fx]をクリックします。
   （「関数貼り付け」ダイアログボックスが現れます。）
3. 「関数貼り付け」ダイアログボックスの[関数の分類(C)]から[論理]を選択します。
4. [関数名(N)]から[IF]を選択して、[ＯＫ]ボタンを押します。
5. IF 関数用の数式パレットが表示されます。
   
6. 表示された数式パレットの論理式の入力欄に、カーソルが点滅していることを確認します。
   （これから、論理式の記入に入ります。論理式には大か小かイコールかの「比較演算子」を使用します。）
7. [関数ボックス]の▼矢印をクリックします。
8. [関数ボックス]のリストから[AND 関数]を選択します。AND [関数]が無い場合は、下段の [その他の関数]を選択して､現れた[関数貼り付け]ダイアログボックスから[AND 関数]を選択します。
9. [AND 関数]を選択すると､数式パレットは自動的に[IF 関数]から[AND 関数]の数式パレットに替わります。
10. 基準値「６５」のセル[B14]をクリック（セル参照）、[F4]キーをクリックして$付きの絶対参照にします。
11. 「比較演算子」の[<=]と入力し、国語の[C4]をセル参照します。）
    
12. [Tab]キーをおして、論理式２にカーソルを移します。
    
13. 数学の条件式を[論理式２]の記入欄に記入します。
14. 基準値「６５」のセル[B14]をクリック（セル参照）、[F4]キーをクリックして$付きの絶対参照にします。
15. 「比較演算子」の[<=]と入力し、数学の[D4]をセル参照します。、論理式は２つで完成です。
16. [Tab]キーをおして、論理式３にカーソルを移します。
    （論理式３に記入するためではありません。論理式２を確定するためです。）
17. 次に、IF関数の数式パレットを表示させる作業です。
    このとき、[AND 関数]の数式パレットの[ＯＫ]ボタンをクリックしないように注意してください。 （ネスト作業中は「数式パレット」の[ＯＫ]ボタンは押さないように）
18. 数式バーの[IF]の文字をクリックします。
    すると、再び[IF 関数]の数式パレットが表示されます。
19. 表示された数式パレットの論理式には､[AND 関数]の２つの条件式が入力されている筈です。そうして自動的に[,]カンマで区切られています。
20. 真の場合の処理["合格"]、偽の場合の処理["不合格"]をそれぞれ入力します。
    [""]のダブルクオーティションは文字列を表示する際に使用します。 これで、[IF]と[AND] 関数を使ったネストの完成です。
21. ここで初めて、数式パレットの[ＯＫ]ボタンをクリックします。
22. [Enter]キーを押して確定して完了します。
23. 後は、オートフィル機能で、[H4]セルのフィルハンドルを下にドラッグします。

完成したネスト関数

「 =IF(AND($B$14<=C4,$B$14<=D4),"合格","不合格") 」となります。
判定結果は、上から合格、合格、不合格、不合格、合格、不合格、合格、合格、不合格、合格です。
※ネスト関数でも、数式パレットを使わず、上記の完成した関数のとおり、キーボードから直接入力することができます。

関数の修正

作成した関数は､時として修正が必要になります。通常の関数でもネスト関数でも修正は ほぼ同じ操作ですが､ネスト関数の場合は、少しコツが必要です。関数を修正するには、 ２つの方法があります。

数式バーで修正

下段の図２のように数式バーへ直接カーソルを入れ、必要個所を修正します。方向キーでカーソルを動かし、 DeleteキーやBackキーで修正箇所を消去して、修正します。
数式バーへカーソルを入れると、ワークシートのセル参照部分は、色つきの枠で囲まれ、 数式バーはセル参照部分のフォント色が変化して、セル参照を確認しやすいようになっています。
ネスト関数の場合でも操作は同じです。 　

数式パレットで修正

入力時に使用した数式パレットを表示させると、引数を修正する事ができます。
数式パレットを表示させるには､ネスト関数でないもの、ネスト関数でも、第１レベルの関数の 場合は､数式バーの「=」記号をクリックします。(ネスト・レベルの制限参照)
ネスト関数の第２レベル以下の関数の場合は、数式バー内の表示させたい関数式をクリックしてから、 数式バーの「=」記号をクリックします。
下の図２は､上の図１のAVERAGED関数部分のの数式パレットを表示させたものです。 修正したい入力ボックスへクリックでカーソル を入れます。 方向キーでカーソルを動かし、DeleteキーやBackキーで修正箇所を消去して、修正します。 数式パレットを表示させたとき、数式バーの関数式は、下の図２のように太字に変化します。 　

「偽の場合」は空欄にする方法

書式は「 IF(論理式,真の場合,"") 」になります。

「偽の場合」の小窓には､＜論理式＞の結果がFALSEであった場合に返される値を入力するのですが、 上の表のように特に「不合格」を表示しないで「空白で返す」方法です。

「""」ダブル クォテーションは、引数に文字列を使うとき、文字列を囲んで使うものですが､これを「文字列抜きで」使うことで、何も表示されず「空白」で返す設定になります。良く使われるテクニックです。

[IF関数]に[IF関数]をネストする

書式は「 IF(B3<=800,"安価",IF(B3>=1500,"高価","並")) 」です。

下の表はお弁当の価格表です。３段階の設定で[IF関数]を２っつ並べました。若し、「デラックス」や「超豪華版」と階級を増やした場合でも、[IF]関数を続けて行けば判定できます。但しネストできる数は７つ迄です。
下表をExcel[A1]から書き移して下さい。(または、ソースを開いて下記の３列６行をコピーしてExcel[A1]に貼り付けると各セルに入力されます。）
なお、セル（B3）〜（B6）の表示形式は「通貨」です。

お弁当価格表
お品書き	価格	判定
パックＡ	\700	安価
パックＢ	\800	安価
パックＣ	\1000	並
特製お膳	\1500	高価

[IF関数]に[ROUND関数]をネストして、構成比を小数点以下1桁まで求める

書式は「 IF(ROUND(B2/$B$5*100,1),ROUND(B2/$B$5*100,1),"") 」です。

そうして、３支店の「構成比合計」をΣ（オートSUM）で出したところ[100.1%]になってしましました。さて、修正するのに、何処を「0.1」減じたら良いでしょうか？

解説

• 比率を小数点以下１桁まで求める為に[ROUND関数]（こちら参照）を使いました。同ページやや下に、桁数表示一覧表があります。それによると、[***.ｎ]で止めるには書式の「 ）」の左側の数字は[１]になります。
  IF関数の書式「IF(論理式，真の場合，偽の場合)」で
• 論理式に当たる部分は「ROUND(B2/$B$5*100,1)」です。
• 真の場合に当たる部分を数値で表示しました「ROUND(B2/$B$5*100,1」上と全く同じです。論理式で出た結果をそのまま真の値としたのです。
• 偽の場合に当たる部分は「""」と空欄にしました。

書式は「 IF($C$2-$D$2>$C$4-$D$4,56.2,18.7) 」を作って見ましたところ [18.8]を[18.7]にするのが正解のようです。試してみてください。（下図参照）

解説

• 小数点以下３桁表示からは「東京支店」も「福岡支店」も[0,05]を切り上げた格好ですが、厳密には「福岡支店」のほうが切り上げ幅が大きいので、減らすとしたら」こちらが候補になります。
  それを[IF関数]で表すと次のとおりになります。
• 左側の[C2]-[D2]より右側の[C4]-[D4]が小さい場合を「正しい場合」とし[56.3-0.1]の「56.2」を返すようにしました。若しそうでなかったら「偽の場合」とし「18.8-0.1」の[18.7]を返すようにしました。
• 「偽の場合」が正解では面白くありません。式の立て方が間違っていたのです。その場合は不等号の「>」を「<」に替え[18.7]、「56.2」の順に並べ替えると「真の場合」が[18.7]になります。
  また、「偽の場合」の「56.2」が不必要なら、前項「偽の場合」は空欄にする方法の[""]を使います。
  「偽の場合」が正解だって有り得ることを知った例です？。）

下表をExcelの[A1]セルにコピーして、ご使用ください。
列幅特にＦ列を十分に広げて下さい。

上図で[ROUND]関数を使った「構成比」は[桁上げ]ツールボタンを押しても[０]が続くのみです。
一方[桁上げ]ツールボタンで３桁表示（３桁目四捨五入）した値は[桁上げ]すると、例えば右端のように変化します。
[桁上げ]「桁下げ」○○桁表示は「仮の姿」で数式計算では何桁にもおよぶ「本当の値」が使われます。

トップへ戻る     いろいろな関数目次へ戻る
　　 いろいろな関数8-2へ進む