#3 エクセル関数(成績表を作る)

IF文の復習

英語が50点以下の場合、「oh No...」という文字列を出してみましょう。

=IF(
   C4<=50,     // セルC4が50点より低ければ(50点含む)
   "Oh No...",    // 文字列を表示
   ""             // そうでなければ空白
)

IF以外の条件式

3教科で1つでも50点以下があると「不合格」を表示(OR)

これまでIF文で使っていた論理式はC4<50といったぐあいに、単発の式でしたが、 新たに関数を入れることで、より複雑な条件分岐ができるようになります。 今回利用できるのは「OR」関数です。 複数の条件を入れ、いずれかに正しい条件があればTRUEを返します。

=OR(条件1, 条件2, 条件3, ・・・)
↓
=IF( OR(A1<=0, A1>=100), "真", "偽" )
もし、条件1, 条件2, 条件3のなかで一つでも該当するればtrue。 いずれも該当しない場合はfalseを実行するという式です。

絶対参照で合格点を自由に変化させる($)

B11に合格点として「60点」を設定しています。
ですが、毎回60点とは限らなく、50点のときもあれば、70点のときもあるかもしれません。 そんなときにせっかく作った計算式を、毎度毎度60点だったものを50点, 50点, 50点...と変更していくのは手間ですね。 なので、ある一つのセルに「合格点」として数字を入れておき、ここ1箇所を変更すれば式全体が変わるように設計します。
またその際、計算式でオートフィルを使うと自動的にずれていくので、 B11の次にB12, B13, B14...となりますがそこには実数がないのでエラーとなります。 絶対参照で表示するようにしましょう。
オートフィルでコピーすると、各行や列の該当エリアを自動で変えて式を作ってくれて便利ですが、 変えたくない場合もあります。
そういった、オートフィルコピー時にセルを動かしたくない場合「絶対参照」という記述方法があります。 「$A$1」「$BA$54」といったぐあいに、行列それぞれの記号の頭に“$”ドルマークを入れます。 絶対参照でのセルを書いておくと、オートフィルコピーしてもセルの参照位置は変わりません。

=A1      ←相対参照
=$A$1    ←絶対参照

3教科の中で2つ以上60点以下があると「よく出来ました!」を表示

その1(AND)
OR関数は“論理式の中で1つでも該当があれば”でしたが、 AND関数は“論理式の中すべて該当した場合”にtrueを返す関数になります。 下の式はネスト(入れ子)したIF文の中にAND関数を使って、2つずつ条件分岐で60点以上を調べています。

=IF(
  AND(C5>=$B$11, D5>=$B$11),        // ① まず英語と数学をチェック。両方60点以上なら...
  "よく出来ました!",                // 早速“よく出来ました”を表示
  IF(
    AND(C5>=$B$11, E5>=$B$11),      // ② 英語か数学で、もしくは両方60点以下だったので、英語と国語でチェック
    "よく出来ました!",        // 運良く両方60点以上なら“よく出来ました”を表示
    IF(
      AND(D5>=$B$11,  E5>=$B$11),  // ③ 最後に国語と数学でも試してみましょう。
      "よく出来ました!",      // どちらも60点以上であれば“よく出来ました”を表示
      ""               // だめなら空白
    )
  )
)
しかし、この方法は3教科だから出来た「力技」的な解決方法です。 10も20も教科があればけっこう大変です。またエクセルのIF文は7つまでしかネストできません。 もう少しインテリジェントな関数を使ってみましょう。
その2(COUNTIF)
ある範囲のなかで数字だけでなく「文字列」も検索してくれる優れた関数です。 検索して該当したセルの個数を数えて帰してくれます。その数値を使って、条件分岐させます。

文字列検索して個数の数値を返します
(例)
=COUNTIF(範囲, "検索条件")
=COUNTIF(A1:D6, "合格")   // A1〜D6にある合格の個数が返ってきます
↓
=IF(
  COUNTIF(C4:E4, ">=" & $B$11) >= 2,
  "良くできました!",
  ""
)
C4〜E4までのなかで、$B$11の値つまり60という数字より大きいのが2つか、 それ以上ある場合はtrueにしてねという式になります。
比較演算子の「">="」とセル「$B$11」を連結しなければいけないので、文字列を連結するために使う演算子「&」を使います。 ですので「"<=" & $B$11」となります。

メモ

真偽について

真はtrue(トゥルー)、偽はfalse(フォルス)と英語表記で表現されます。

等号・不等号について

演算子演算名使用例結果
=等しい"=2"2 と同じ
<>等しくない"<>2"2 以外
>大きい">2"2 より大きい
<小さい"<2"2 より小さい
>=以上">=2"2 以上
<=以下"<=2"2 以下

文字がカラの場合、カラじゃない場合

IF文でよく使う論理式で「なんでもいいから文字が入っていた場合」としたい時や、何も入っていないときの判定方法。

=IF(A1="", "", B1-C1)  // セルA1になにもデータがなければ、空白にそうでなければ計算式を実行
=IF(A1<>"", "", B1-C1)  // セルA1になにかデータが有れば、空白にそうでなければ計算式を実行

数値と文字列について

文字列である数字は計算式に入れることが出来ません。 例えば「60点」と点をつけると文字列になってしまいます。 でも計算では使いたいので、人間が見たときは文字列、計算のときは数字という表現方法が有ります。 セルの書式設定から、表示形式->ユーザー定義で登録します。

#,##0"点"
数字の最後に""(ダブルクオーテーション)で好きな単位を入れます 見た目は「60点」ですが、数字としての取扱いが可能になります
powerd by