IFとIS関数を組み合わせ、セルの内容が
文字列か数値かによって計算方法を変える
エクセルにはセルの情報を知る関数が、情報関数として18種類(アドイン登録後)用意されています。情報関数は、テスト関数､情報検索関数、文字･数値変換関数に分類され､そのなかで関数の頭にISがつく11種類の関数はすべてセルの内容をチェックするテスト関数です。テスト関数はISのあとに続く単語名をもとにしてセルの内容を調べます。今回ご紹介するISODD関数は､セルの値が「奇数」なのかどうかを、ISTEXT関数は、セルの値が「文字列」なのかどうかを､ISNUMBER関数はセルの値が「数値」なのかどうかをそれぞれ調べる関数です。IS関数は答えを、そうであった場合TRUE(真)を、そうでない場合FALSE(偽)を返します。そこで、IF関数と組み合わせ､条件式にIS関数を利用すれば、セルの内容をチェックし、内容に応じてダータの処理を分岐させ、セルに指定した値を表示させる事ができるようになります。今回は実際にIFとIS関数を利用し､「振込状況表」を作成してみましょう。
| 今月の関数 |
対象のセルの値が数値かどうか調べる
ISNUMBER |
||
| 書式 | =ISNUMBER(テストの対象 ) | |
| ISNUMBERは、対象となるセルの値が数字のとき、TRUEを返す関数。I5では、IF関数と組み合わせ、H５に数値が入っていれば､数値をそのまま表示。電話番号なら何も表示しないよう設定。H15にはI列に抽出された数値を合計。 | ||
対象のセルの値が文字列かどうかを調べる
ISTEXT |
||
| 書式 | =ISTEXT(テストの対象 ) | |
| 対象となるセルの値が文字列かどうか調べ、文字列ならばTRUEを返すISTEXT関数。H6では、IF関数と組み合わせ、G6に文字列が入っていれば電話番号を､それ以外ならば､EとF列の値を合計するよう設定。 | ||
対象となるセルの値が奇数かどうかを調べる
ISODD関数 |
||
| 書式 | =ISODD(テストの対象 ) | |
| ISODDは、対象となるセルの値が奇数かどうかを調べ、奇数ならばTRUEを返す関数。E5ではIF関数と組み合わせ、D５の値が奇数ならば入会金1万円を表示。なお偶数を調べるにはISEVENを使う。 | ||
| 1 IF+ISODDで、奇数年だけで入会金を表示 |
2年に一度だけ入会金が必要な場合、奇数年だけ入会金を表示させる。まず、（1）C5セルで氏名を別シートに作成しておいた、（2）のタブ「個人情報」のシート（氏名、使用年とコード番号を対応させたリスト）からVLOOKUP関数を利用して求める。D5の利用年も同様に求める。
E5では、ISODD関数でセルの値が奇数かどうかを調べ、奇数であった場合は「\10,000」と表示させ、そうでない場合は「済」と表示されるようにIF関数を使って式を作る。まずIF関数の数式パレットの（3）「論理式」のボックスで、情報関数のISODD関数を設定。「数値」には、奇数かどうかを調べたいセルの値を入力。ここでは利用年であるD5をクリック。数式バーのIF関数のところをクリックし、IF関数の数式パレットに戻り、（4）「真の場合」には、奇数であった場合セルに表示させる値を表示させるので、［10000］と入力、（5）「偽の場合」には奇数ではないので［済］を入力して、［OK］ボタンをクリック。
E5のセルに入った式は
=IF(ISODD(D5),10000,"済")
| 2 IF+ISTEXTで、文字列の場合の計算方法を変更 |
振込が未納の場合、個人情報一覧から連絡先を表示させ、入会金有りの場合は年間利用料との合計を、
入会金が済みの場合は年間利用料のみを振込金額とする。（6）H5で、IF関数を設定。（7）「論理式」には
状況が未納であった場合を条件とするので、「G5="未納"」と入力。
「真の場合」ボックスで、（8）［VLOOKUP］関数をネストし、未納であった場合、個人情報から連絡先を表示させる式をたてる。（9）「検査値」にはコード番号が入ったB5を選択、（10）「範囲」は個人情報シートの個人情報一覧のB5からE12を範囲選択し絶対参照をかけ、（11）「列番号」には4列目なので4と入力、（12）「検索の型」には完全一致なので0と入力。数式バーをクリックし、再びIF関数に戻る。
「偽の場合」には、未納でない場合の金額を計算して入力する式をたてるが、入会金が「済」の場合と有りの場合とで振込金額の計算方法が違うので、ここで「偽の場合」のボックスにカーソルを置いてIF関数をネスト。（13）「理論式」には入会金が「済」であった場合（文字列であった場合）の式をたてるので、情報関数から［ISTEXT］をネスト。「テストの対象」には、E5を指定。（14）「真の場合」は「済」であった場合、年間利用料のみを表示させるのでF5をクリック、（15）「偽の場合」は「済」でない場合、入会金と年間利用料を足した金額を表示させるので、E5をクリックしてキーボードから［＋］を入力、F5をクリックし、［OK］を押す。
H5のセルに入った式は
=IF(G5="未納",
VLOOKUP(B5,個人情報!$B$5:$E$12,4,0),
IF(ISTEXT(E5),F5,E5+F5))
| 3 IF+ISNUMBERで、数値のみの合計を算出 |
数値や文字列が混在したデータから、［オート
SUM］ボタンで合計する場合、自動的に文字列は省かれて数値のみで計算が行なわれるが、エラー値が含まれていると答えもエラーになってしまう。そこで、ISNUMBER関数でセルの値が数値かどうかを調べ、IF関数であった場合として別セル(I列）に数値のみを表示させることで数値のみの合計をしてみよう。（16）I5を選択し、IF関数を指定。
（17）「論理式」で情報関数から［ISNUMBER］関数を選択。「テストの対象」には、数値かどうかを調べる値であるH5をクリック。IF関数に戻る。（18）「真の場合」は数値であった場合にセルに振込金額の値を表示させるのでH5とし、（19）「偽の場合」は数値でない場合に空白を表示させるので「""」と入力。［OK］ボタンをクリック。
I5のセルに入った式は
=IF(ISNUMBER,(H5),H5,"")
（20）I6からI13まではオートフィルをかけておく。（21）H15を選択、（22）[オート SUM］ボタンをクリック、範囲としてI5からI13までをドラッグし［Enter］キーで振込金額を求める。I列は見栄えをよくするため列番号上で右クリック→［表示しない］で非表示にする。
