いろいろな関数 9

主な内容
1.複数の値を基準に判断する
2.IF 関数では条件判断できない場合
3.IF関数ネスト例題
4.エラーについて

条件判断と処理の分岐 (IF 関数の使用方法)

IF関数は、[論理式]に[1つの条件のみ]を与えます。
論理式の結果が真 (True) の場合は真の場合の値を返し、偽 (False) の場合は偽の場合の値を返します。

書式は、「 IF(論理式, 真の場合, 偽の場合) 」で表します。

複数の値を基準に判断する

複数の値を基準にし条件判断を行うには、IF 関数を組み合わせます。

例題 下図のような「年齢構成」で。条件が「5段階」有ったとします。

書式は下記のとおりで、当初の[IF関数]とネストした[IF関数]3個の、計4個で5段階をカバーすることになります。
=IF(C2<30,$B$13,IF(C2<40,$B$14,IF(C2<50,$B$15,IF(C2<60,$B$16,$B$17))))

AND 関数や OR 関数と組み合わせて複数の値を判断する

複数の値を基準にし判断する場合に、AND 関数や OR 関数を組み合わせると IF 関数のネストを少なくすることができます。

IF 関数では条件判断できない場合

IF 関数は最大 7 つまで組み合わせることができます。以下のような判断をしたい場合は、7 つの IF 関数では求めることができません。IF 関数の代わりに VLOOKUP 関数など、他の関数を使用して求めることができます。

用語

IF 関数のネスト:IF 関数の引数に関数を利用することを言います。
(IF関数の限らず、他の関数の組み合わせについても同様です。)
IF関数は条件が1つしか設定できませんので、更にIF関数や他の関数をネストして、条件を増やして使う場合が多いのです。
IF関数にIF関数を1つネストすると条件が2つ設定でき、2つネストすると条件が3つ設定できる仕組みになっています。

2つネストした書式:
「 =IF(論理式1,処理1,IF(論理式2,処理2,IF(論理式3,処理3,処理4))) 」
図示すると
論理式1⇒Yes(ならば) 処理1     ⇒No (ならば) 論理式2⇒Yes 処理2                 ⇒No  論理式3⇒Yes 処理3                        ⇒No  処理4 ネストできる関数は最大7個までです。

IF関数ネスト例題

関数のみで処理する([IF][AND]関数で)

下表例題を作りました。[IF]関数のネストだけで処理しようとしましたが、何としても上手く行きません。止むを得ず[AND]関数をネストしました。 オートフィルタで分離すれば、もっと簡単な数式ですむでしょうが練習のため敢えて作りました。

「ネストの仕方」、「数式の立て方」にもいろいろあるでしょうが、私の方法で行いました。

下表を[A1]セルに貼り付け、[F2]セルでお試しください。

OB会 性別 年齢   会費
山田太郎 男性 80 7000
川田花子 女性 75 4200
草田一郎 男性 64 10000
木田一美 女性 49 5000
上田春子 女性 68 5000
中田夏男 男性 63 10000
下田秋子 女性 65 5000
天野和雄 男性 62 10000
地脇智子 女性 60 5000
人見賢治 男性 65 10000
某校0B会です。会費は4段階です。
会費徴収基準
1老人割引 75歳以上 7000
2老女割引 75歳以上 4200 40%
3.男性 10,000
4.女性割引 5000 50%
 決め方:1.2.3.の順で[TRUE]を決め、4には[FALES]を当てます。
1.[E2]セルをアクティブにします。
2.fxアイコンから、IFを選択します。
 (3〜8まで、男性75歳以上の条件設定です。)
3.数式バー左の関数ボックスから[AND]を選択し、ネストします。
4.[AND]数式パレットの論理値1に[B2="男性"]
5.[AND]数式パレットの論理値2に[C2>=75]と入力します。
6.Tabキーを押して4,5を一旦確定します。(マウスでも可)
7.数式バー左端の[IF]を押して数式パレットを[IF]に戻します。
8..[IF]真の場合に金額のセル参照で[$D$15]と入力し[Tab]キーです。
9.[IF]偽の場合に、数式バー左の関数ボックスから[IF]を選択し、ネストします。
 (女性75歳以上の条件です。上記3〜8を繰り返します。)
10.3.数式バー左の関数ボックスから[AND]を選択し、ネストします。
10.4.[AND]数式パレットの論理値1に[B2="女性"]
11.5.[AND]数式パレットの論理値2に[C2>=75]
12.6.Tabキーを押して4,5を一旦確定し
13.7.数式バー中央辺の[IF]を押して数式パレットを[IF]に戻します。
 (一般男性を[真の場合]に、それ以外は一般女性に該当します。)
14.8..[IF]真の場合に金額のセル参照で[$D$16]と入力し[Tab]キーです。
15.[IF]偽の場合に金額のセル参照で[$D$17]と入力してOKです。

コピーした[E2]をクリックすると、下記数式が現れます。

=IF(AND(B2="男性",C2>=75),$D$15,IF(AND(B2="女性",C2>=75),$D$16,IF(B2="男性",$D$17,$D$18)))

割引率%使用の場合は「 セル参照7000*(1-セル参照40%) 」で、7000の60%で4200になります。

オートフィルタ使用して上の課題を処理する

エラーについて

エラー値 ##### の意味(読み方:シャープ)
セルの幅よりも長い数値、日付、時刻が入力されている場合、または結果が負の値になる日付 または時刻の数式が入力されている場合、エラー値 ##### が表示されます。
原因1.
標準の状態でセルに9桁以上の桁数の数字を打った時、自然とセル幅は大きくなってしまいます。 ところが、セル幅が決まっている場合セル幅を狭めると、このエラーになってしまいます。 対処
セル幅を大きく調整(列番号の間の境界をドラッグします。)するか、文字サイズを小さくします。
原因2.日付けの設定
日付けの設定がされているセルにマイナス値が入力された場合(日付けの表示や計算は通常1以上の正の値でなければなりません。
対処
正の値を入れ直します。
[セルの書式設定]で[表示形式]のタブの分類で、数値、または日付を選択して設定します。

エラー値 #VALUE! の意味(読み方:バリュー)
エラー値 #VALUE! は、引数やオペランドの種類が正しくないときや、数式のオートコレクト機能が数式を訂正できないときに返されます。

(用語:オペランドとは、等号 (=) で始まり、演算の対象となる要素のことで、
演算の対象となるオペランドには、一定の値を保持する値 (定数)、セルまたはセル範囲の参照、 見出し (ラベル)、名前、またはワークシート関数を使用できます。)

(用語:定数とは、セルまたは数式に直接入力する数値や名前で表される文字列。
例えば日付 ”2004/8/15 ”、数値 ”210 ”、文字列 ”四半期の売上 ” はすべて定数です。数式や数式の計算結果は定数ではありません。)

端的にいうと、「不適切なデータが入っている」ということです。
原因
数値または TRUE や FALSE などの論理値が必要な部分に文字列が入力されていて、その文字列が正しいデータ型に変換されません。
対処
必要な演算数や引数について、数式または関数が正しいかどうか、また、数式で参照するセルの値が有効かどうかを確認します。

図: #VALUE!

数値と文字列の足し算、たとえば図 #VALUE!、の上の表で、数値をセル参照で個別に並べたものですが、セル B4 に [欠席] という文字列が入力されている場合、数式 =B2+B3+B4+B5 は、エラー値 #VALUE! を返します。
同様に、数値をセル参照で個別に並べて人数で割った平均値もエラーになります。

下の表は合計、平均をそれぞれ[SUM]と[AVERAGE]関数を用いて計算した結果ですが、エラーは表示されませんでした。
補記:「欠席」と入力されていたセルを0か、空欄のままにすると、エラー値が数値に変わります。

エラー値 #DIV/0! の意味(読み方:デバイド・バー・ゼロ)
エラー値 #DIV/0! は、数式で 0 (ゼロ) による除算が行われた場合に返されます。
下図例2の場合です。わざわざ0(ゼロ)で割ることはしないと思いますが、数式をオートフィル機能でコピーした産物です。空欄で割った場合でも0で割ったことになります。

図:#DIV/0!
エラー値 #NAME? の意味(読み方:ネーム)
エラー値 #NAME? は、Excel で認識できない名前が使われた場合に返されます。
原因 対処
数式で使用する名前を削除したか、または存在しない名前を使っています。
関数名やセル範囲名などの名前が正しくない。アルファベット綴りを見直しましょう。
上図例1の場合、AVERAGEとするところをAVEREGEとスペルミスを犯しています。

エラー値 #N/A の意味(読み方:ノー・アサイン)
エラー値 #N/A は、関数や数式に使用できる値がない場合に返されます。
種類が検索型関数(VLOOKUP等で)引数に不適当な値を与えたとき発生します。
(下図では、右の2行の表参照データに[-10]のデータが無いため生じたエラーです。

図:#N/A

エラー値 #REF! の意味(読み方:リファレンス)
エラー値 #REF! は、数式中のセル参照が無効なときに返されます。
原因 対処
数式作成後、参照位置のセルを不用意に削除したり、値の種類を変更したりすると発生します。
セル参照をもう1度見直しましょう。

エラー値 #NUM! の意味(読み方:ナンバー)
エラー値 #NUM! は、数式または関数の数値に問題がある場合に返されます。
原因 対処
引数として数値を指定する関数に、不適切な値を使っています。 関数に正しい引数を指定しているかどうかを確認します。 IRR、RATE など、反復計算を行うワークシート関数で、解が見つかりません。 ワークシート関数の初期値を変更してみます。 Excel で処理できないような、 大きな値または小さな値が計算結果となる数式を入力しています。 数式を変更し、計算結果が[-1×10^307]と[1.8×10^308]の 範囲に収まるようにします。気の遠くなるような数値で現実的ではなさそうですが、例えば、面積計算で「メートル単位で計算していたのを、キロメートル単位」に変更するとかして。
(ベキ乗計算の場合、[1e+10」という表示になるときもあります。数値を指数表示したもので「1e+10=10000000000」です。)

エラー値 #NULL! の意味(読み方:ヌル)
エラー値 #NULL! は、指定した 2 つのセル範囲に共通部分がない場合に返されます。
原因 対処
正しくない参照演算子、または正しくないセル参照を使っています。 共通部分がない 2 つの範囲を参照する場合は、複数選択の参照演算子であるカンマ (,) を使います。たとえば、数式で 2 つの範囲の値を合計する場合は、2 つの範囲がカンマで分割されている (SUM(A1:A10,C1:C10)) ことを確認します。カンマが付いていないと、両方の範囲に共通するセルの値を合計しようとしますが、セル範囲 A1:A10 とセル範囲 C1:C10 に共通のセルはありません。範囲指定が正しく入力されていることを確認します。

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

inserted by FC2 system