いろいろな関数 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になります。
オートフィルタ使用して上の課題を処理する
- 先ず、年齢を「昇順」に並べ替えます。
- 表内の任意セルを選択。表に「オ−トフィルタ」を設定します。
- 年齢▼をクリックして、(オプション)を選択
- ダイアログで、左窓[75] 右窓[以上]を選択して、75歳以上を抽出します。
- ツールバー[fx]ボタンをクリック
- 論理式に[B2="男性"]
- 真の場合に[セル参照で、7000]
- 偽の場合に[セル参照で、4200]を入力
- OKボタンで半分完了
1回目 =IF(B10="男性",$D$15,$D$16)
の数式が完了。 (B10はオートフィルタの(すべて)を押した時に自動的にセル順が戻ったため)
- 年齢オートフィルタの(すべて)を押しリストすべてを表示させる。
- ツールバー[fx]ボタンをクリック
- 論理式に[B2="男性"]
- 真の場合に[セル参照で、10000]
- 偽の場合に[セル参照で、5000]を入力
- OKボタンで完了
2回目 =IF(B2="男性",$D$17,$D$18)
の数式が完了して、全部の処理が済んだことになります。
「オ−トフィルタ」を使用した方が頭の整理が簡単のようです。なぜなら、私の場合、上の方法では、10回も試行錯誤がありました。下の方法では1回でできたのです。
- エラー値 ##### の意味(読み方:シャープ)
- セルの幅よりも長い数値、日付、時刻が入力されている場合、または結果が負の値になる日付
または時刻の数式が入力されている場合、エラー値 ##### が表示されます。
原因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へ進む