エクセルで試験結果、研修の修了判定、検品結果、営業目標の達成状況などを管理していると、点数だけではなく、出席率や提出物、必須項目の確認結果も含めて合格か不合格かを判断したい場面があります。
このような複数条件の合否判定では、IF関数にAND関数やOR関数を組み合わせると、条件に応じた結果を自動表示できます。
条件式を正しく組み立てれば、判定基準を変更した場合も数式を修正するだけで一覧全体へ反映できるため、手作業による確認漏れの防止にも役立ちます。
複数条件で合格にする基本は、すべて満たす場合にAND関数、いずれかを満たせばよい場合にOR関数をIF関数の論理式へ入れる方法です。
この記事では、1行目を見出し行としたサンプルデータを使い、エクセルで複数条件の合否判定を行うIF関数の書き方、数式のコピー方法、判定ミスを減らす考え方を解説していきます。
IF関数とAND関数による複数条件の合否判定
それではまず、点数と出席率の両方を満たした人を合格にする基本的な数式について解説していきます。
| 受験者 | 点数 | 出席率 | 判定 |
|---|---|---|---|
| 田中 | 82 | 95% | 合格 |
| 佐藤 | 76 | 88% | 不合格 |
| 鈴木 | 68 | 93% | 不合格 |
合格条件をすべて満たす数式
まず、点数が70点以上、かつ出席率が90パーセント以上なら合格とするケースを確認していきます。
サンプルではB列に点数、C列に出席率、D列に判定結果を表示します。
=IF(AND(B2>=70,C2>=90%),”合格”,”不合格”)
この数式をD2セルへ入力すると、B2セルとC2セルの条件が両方とも成立した場合だけ、合格と表示されます。
AND関数は、指定した条件がすべて真である場合にTRUEを返す関数です。
田中さんは82点かつ出席率95パーセントなので、二つの基準を満たし、D2セルには合格が表示されます。
一方で、佐藤さんは点数が基準以上でも出席率が90パーセント未満のため、不合格です。
IF関数だけで複数条件を書こうとすると数式が読みにくくなりやすいため、すべて満たす条件ではAND関数を使うと整理しやすくなります。
数式内のカンマは条件を区切る役目を持ち、最後の合格と不合格は判定後に表示する文字列です。
文字列を数式に直接入れる場合は、必ず半角の二重引用符で囲みましょう。
【操作のポイント】点数や出席率の基準値は数式内へ直接書けますが、基準変更が多い表では別セルに基準値を置く方法も便利です。
IF関数の引数と判定の流れ
続いては、IF関数とAND関数がどの順番で計算されるのかを確認していきます。
IF関数は、最初に書いた条件が成立するかを判定し、成立するときの結果と、成立しないときの結果を切り替える関数です。
IF関数の基本形は、=IF(条件,条件が成り立つ場合,条件が成り立たない場合)です。
今回のAND(B2>=70,C2>=90%)では、B2が70以上か、C2が90パーセント以上かを個別に確認します。
二つとも条件を満たすとAND関数の結果がTRUEになり、IF関数は合格を返します。
どちらか一つでも条件を満たさない場合、AND関数はFALSEとなり、IF関数は不合格を返す仕組みです。
複数条件の合否判定では、合格に必要な条件を先に文章で整理してから数式に変えると、比較演算子の誤りを防げます。
たとえば「70点以上」はB2>=70、「90パーセント以上」はC2>=90%と書きます。
以上、未満、以下を取り違えると境界値の受験者だけ誤判定になるため、特に注意が必要です。
【操作のポイント】条件を日本語で書き出し、すべて必要ならAND、どれか一つでよいならORと決めてから入力しましょう。
数式を下の行へコピーする方法
続いては、作成した合否判定の数式を複数の受験者へ反映する方法を確認していきます。
D2セルへ数式を入力したら、セル右下に表示される小さな四角形、フィルハンドルへマウスポインターを合わせます。
ポインターが黒い十字に変わった状態で下方向へドラッグすると、数式を下の行へコピーできます。
データが連続している場合は、フィルハンドルをダブルクリックする方法も効率的です。
通常のセル参照は数式をコピーすると行番号が自動で変わるため、D3ではB3とC3、D4ではB4とC4が判定対象になります。
コピー後のD列で不合格が続く場合は、数式ではなく元データの表示形式も確認しましょう。
出席率が90ではなく0.9として保存されている場合でも、セルの表示形式がパーセンテージなら条件式はC2>=90%で問題ありません。
【操作のポイント】数式をコピーした後は、合格になる行と不合格になる行を一件ずつ確認し、参照先がずれていないか確かめましょう。
OR関数を使ったいずれかの条件による判定
続いては、複数の救済条件のうち一つでも満たせば合格とするOR関数の使い方を確認していきます。
| 受験者 | 筆記点 | 実技点 | 判定 |
|---|---|---|---|
| 高橋 | 75 | 61 | 合格 |
| 伊藤 | 62 | 72 | 合格 |
| 渡辺 | 64 | 59 | 不合格 |
筆記または実技が基準以上の場合
続いては、筆記点または実技点のどちらかが70点以上なら合格とする数式を解説していきます。
=IF(OR(B2>=70,C2>=70),”合格”,”不合格”)
OR関数は、指定した条件のうち少なくとも一つが成立するとTRUEを返します。
高橋さんは筆記点が75点なので、実技点が61点でも合格になります。
伊藤さんは筆記点が基準未満でも、実技点が72点のため合格です。
OR関数は、一つでも条件を満たせばよい特例判定や、複数の資格のどれかを保有していればよい確認に向いています。
ただし、OR関数を使うべきところでAND関数を使うと、すべての項目が基準以上でなければ合格になりません。
逆に、両方必須の条件でOR関数を使うと想定外の合格者が出るため、判定ルールとの照合が重要です。
【操作のポイント】文章中に「または」「いずれか」「どちらか」とある基準は、OR関数を使う候補として確認しましょう。
文字列条件を含める判定式
続いては、点数だけでなく、提出済みや免除といった文字列を条件に含める方法を確認していきます。
たとえばB列の点数が70点以上、またはC列の提出状況が免除なら合格にする場合があります。
=IF(OR(B2>=70,C2=”免除”),”合格”,”不合格”)
文字列を条件に使う場合は、免除のような文字を二重引用符で囲みます。
セル内に余分な空白があると一致しないため、入力規則のリストを使って提出済み、未提出、免除などの表記を統一すると安心です。
文字列の比較では、全角と半角、末尾のスペース、表記ゆれが判定結果に影響することがあります。
なお、数値が文字列として入力されている表では、見た目が同じ70でも数式の比較が期待どおりにならない場合があります。
セルの左上に緑色の三角が表示されるときは、数値への変換も確認しましょう。
【操作のポイント】文字列の条件は入力内容を統一し、数式に書く文字も実際のセルと同じ表記にそろえます。
OR関数とAND関数を選ぶ基準
続いては、OR関数とAND関数をどのように使い分けるかを確認していきます。
「点数が70点以上で、出席率が90パーセント以上」のように、すべての条件が必須ならAND関数です。
「筆記か実技のどちらかが70点以上」のように、条件の一つを満たせばよい場合はOR関数を使います。
すべて必要な条件はAND関数、少なくとも一つ必要な条件はOR関数という区別です。
数式の前に、条件を満たさない受験者の例を二人ほど想定すると、関数の選択ミスを見つけやすくなります。
基準が複雑な表では、担当者だけが理解できる数式にしないことも大切です。
判定列の近くに基準説明を置き、誰が確認しても同じ結論にたどり着ける状態を作りましょう。
【操作のポイント】ANDとORを混在させる前に、各条件が必須か任意かを表に書き出して整理することが近道です。
AND関数とOR関数を組み合わせる条件式
続いては、必須条件と救済条件が混在する、より実務的な合否判定の組み立て方を確認していきます。
| 氏名 | 筆記点 | 実技点 | 出席率 | 判定 |
|---|---|---|---|---|
| 山本 | 72 | 68 | 92% | 合格 |
| 中村 | 68 | 75 | 94% | 合格 |
| 小林 | 80 | 71 | 85% | 不合格 |
出席率を必須にして試験点を選択する数式
続いては、出席率90パーセント以上を必須とし、筆記点または実技点のどちらかが70点以上なら合格とする方法を解説していきます。
=IF(AND(D2>=90%,OR(B2>=70,C2>=70)),”合格”,”不合格”)
この数式では、外側のAND関数で出席率の条件と試験点の条件をまとめています。
内側のOR関数は、筆記点と実技点のどちらかが70点以上かを判断します。
山本さんは出席率が92パーセントで、筆記点が72点のため合格です。
中村さんも出席率が94パーセントで、実技点が75点なので合格となります。
小林さんは試験点を満たしていても出席率が85パーセントであるため、必須条件を満たさず不合格です。
関数を入れ子にするときは、先にOR関数の判定を作り、それをAND関数の一条件として入れると考えると分かりやすくなります。
【操作のポイント】複雑な式では、先に内側のOR関数だけを別セルで試し、期待どおりTRUEまたはFALSEになるか確認すると安全です。
数式入力画面の確認手順
続いては、実際のExcel画面で数式を入力し、先頭セルからオートフィルで判定を広げる流れを確認していきます。
画面上部の数式バーには、選択しているセルの数式が表示されます。
E2セルを選択して数式を入力し、Enterキーを押すと判定結果がセルに表示されます。
次にE2セルの右下にあるフィルハンドルを下方向へドラッグするか、連続データであればダブルクリックして下の行へ数式を反映します。
数式バーで参照セルの色分けを確認すると、B2、C2、D2が意図した列を参照しているか見分けやすくなります。
【操作のポイント】数式を確定する前に、数式バーの括弧の数と、AND関数およびOR関数の閉じ括弧の位置を確認しましょう。
括弧と区切り記号の確認方法
続いては、複数関数を組み合わせたときに起こりやすい数式エラーの確認方法を解説していきます。
AND関数の中にOR関数を入れる場合は、関数名の直後に開き括弧を置き、条件を書いた後に対応する閉じ括弧を置きます。
=IF(AND(D2>=90%,OR(B2>=70,C2>=70)),”合格”,”不合格”)では、OR関数を閉じた後にAND関数を閉じ、最後にIF関数を閉じています。
数式を内側から読むと、筆記または実技を判定し、その結果と出席率を判定し、最後に合格または不合格を表示する流れです。
数式を入力してエラーが出る場合は、全角の括弧や全角カンマが混ざっていないかも確認してください。
エクセルの数式では、括弧とカンマは原則として半角で入力します。
数式が長くなる場合は、数式バーを広げて見やすくしてから編集すると、参照先や二重引用符の不足に気付きやすくなります。
【操作のポイント】数式エラーが出たら、一度に全部を直そうとせず、内側の関数から順に括弧と条件を確認しましょう。
複数の判定結果を返す入れ子のIF関数
続いては、合格と不合格だけではなく、再試験や保留など複数の結果を表示する入れ子のIF関数を確認していきます。
| 氏名 | 総合点 | 出席率 | 判定 |
|---|---|---|---|
| 加藤 | 85 | 96% | 合格 |
| 吉田 | 64 | 94% | 再試験 |
| 斎藤 | 61 | 82% | 不合格 |
合格と再試験と不合格の表示
続いては、70点以上かつ出席率90パーセント以上を合格、60点以上かつ出席率90パーセント以上を再試験、それ以外を不合格にする方法を解説していきます。
=IF(AND(B2>=70,C2>=90%),”合格”,IF(AND(B2>=60,C2>=90%),”再試験”,”不合格”))
最初のIF関数で合格条件を確認し、該当しなかった場合だけ、二つ目のIF関数で再試験条件を確認します。
加藤さんは最初の条件を満たすため合格です。
吉田さんは70点以上ではないため最初の条件では不合格側へ進みますが、60点以上かつ出席率90パーセント以上なので再試験となります。
入れ子のIF関数では、より厳しい条件や優先順位の高い結果から先に書くことが重要です。
60点以上の条件を先に書いてしまうと、85点の人も再試験になってしまうため、条件の並び順に注意しましょう。
【操作のポイント】複数結果を返すときは、最優先の判定から順に書き、最後の不合格はそれ以外の結果として配置します。
空白セルを保留にする条件
続いては、点数や出席率が未入力の人を不合格にせず、保留と表示する方法を確認していきます。
=IF(OR(B2=””,C2=””),”保留”,IF(AND(B2>=70,C2>=90%),”合格”,”不合格”))
B2またはC2が空白なら、最初のOR関数がTRUEとなり、保留を返します。
空白ではない場合だけ、後半のIF関数で通常の合格判定を実行する流れです。
未入力データを不合格と区別して保留にすると、集計途中の一覧でも確認対象を見落としにくくなります。
ただし、数式が入っていて見た目が空白のセルを扱う場合は、空文字列を返している数式との関係も確認が必要です。
入力担当者が複数いる表では、保留の意味を共有しておくと、判定済みデータと未確認データが混ざりません。
【操作のポイント】空欄を許さない運用でも、入力途中の状態を扱う一覧では保留判定を設けると管理しやすくなります。
IFS関数を使う場合の考え方
続いては、対応するExcelでIFS関数を使う場合の考え方を確認していきます。
IFS関数は、複数の条件と結果を順番に並べて書けるため、入れ子のIF関数より読みやすくなる場合があります。
=IFS(AND(B2>=70,C2>=90%),”合格”,AND(B2>=60,C2>=90%),”再試験”,TRUE,”不合格”)
IFS関数でも、上から順に条件を確認するため、合格条件を再試験条件より先に置きます。
最後のTRUEは、それまでの条件に当てはまらない場合を受け止めるための条件です。
古いバージョンのExcelではIFS関数が使えないことがあるため、ファイルを共有する相手の環境も確認しましょう。
汎用性を優先したい場合は、基本のIF関数とAND関数で作成すると、多くの環境で扱いやすくなります。
【操作のポイント】共同利用するブックでは、利用者のExcelバージョンを考慮して関数を選びましょう。
基準値をセル参照にした合否判定の管理
続いては、合格点や必要出席率を数式内に直接書かず、別セルで管理する方法を確認していきます。
| F列 | G列 |
|---|---|
| 合格点 | 70 |
| 必要出席率 | 90% |
絶対参照を使う数式
続いては、G2セルに合格点、G3セルに必要出席率を入力した場合の数式を解説していきます。
=IF(AND(B2>=$G$2,C2>=$G$3),”合格”,”不合格”)
$G$2と$G$3は絶対参照です。
この数式を下の行へコピーしても、基準値の参照先は常にG2セルとG3セルに固定されます。
絶対参照では、列記号と行番号の前にドル記号を付けて、コピー時に参照先が動かないようにします。
基準が変更されたときはG2またはG3の値を更新するだけで、判定列全体の結果が自動で変わります。
毎年の合格基準が変わる研修や、部門ごとに基準を調整する管理表に特に便利な方法です。
【操作のポイント】基準値を別セルに置いたら、数式コピー前に参照へドル記号が付いているか確認しましょう。
基準値の見える化
続いては、判定基準を他の利用者にも分かりやすく伝える表の作り方を確認していきます。
基準値のセルを一覧表の右側や上部へ配置し、背景色や罫線で判定データと区別すると、表を見る人がルールを把握しやすくなります。
合格点、出席率、必須提出物の有無などを個別に並べると、数式を開かなくても判定の前提を確認できます。
判定基準をセルで見える化すると、数式の修正権限を持たない人でも条件変更の影響を確認しやすくなります。
基準セルには入力規則を設定し、出席率は0パーセントから100パーセントの範囲に制限しておくと、誤入力の予防になります。
基準を変える担当者が限られる場合は、シートの保護も検討するとよいでしょう。
【操作のポイント】基準値の近くには、適用開始日や判定対象を記載しておくと、古いルールとの混同を防げます。
条件付き書式による結果の確認
続いては、合格、不合格、保留の結果を見やすくする条件付き書式を確認していきます。
判定列を選択し、ホームタブの条件付き書式から新しいルールを選ぶと、セルの文字列に応じて色を変えられます。
たとえば合格には淡い緑、不合格には淡い赤、保留には淡い黄を設定すると、一覧を見た瞬間に状況を把握できます。
条件付き書式は数式の判定そのものを変える機能ではなく、結果を見やすくする表示上の補助機能です。
色だけでは区別しにくい場合もあるため、セル内の合格、不合格、保留という文字は残しておくことをおすすめします。
フィルター機能と組み合わせれば、不合格だけ、または保留だけを抽出して確認することも可能です。
【操作のポイント】色分けは補助として使い、誰が見ても意味が分かる判定文字列をセルに残しましょう。
複数条件の合否判定で起こりやすいエラー
続いては、IF関数による合否判定で結果が合わないときに確認したいポイントを解説していきます。
| 表示結果 | 確認する内容 |
|---|---|
| #VALUE! | 数値と文字列、参照セル、演算子 |
| 想定外の不合格 | 以上と超過、パーセント表示、空白 |
| コピー後の誤判定 | 絶対参照のドル記号 |
境界値と比較演算子の違い
続いては、70点ちょうどや出席率90パーセントちょうどの人が想定どおりに判定されない原因を確認していきます。
70点以上なら合格にする場合は、B2>=70を使います。
B2>70と書くと、70点ちょうどの人は条件を満たさないため不合格です。
以上は>=、以下は<=、より大きいは>、より小さいは<です。
合否基準に「以上」「以下」「超える」「未満」のどれが書かれているかで、使う比較演算子は変わります。
また、90パーセントという基準を90と比較するのか、90%と比較するのかは、セル内の実際の数値によって確認します。
パーセンテージ表示の90パーセントは内部的には0.9であるため、通常は90%と書くと分かりやすいでしょう。
【操作のポイント】境界値に当たるテストデータを用意し、70点、69点、90パーセント、89パーセントの結果を必ず確認します。
数値と文字列の混在
続いては、見た目は数値でも計算できない場合の確認方法を解説していきます。
外部システムから貼り付けた点数や、CSVファイルから取り込んだデータでは、数値が文字列として保存されていることがあります。
セルを選択して数式バーを確認し、先頭にアポストロフィが付いている場合や、セルが左揃えになっている場合は文字列の可能性があります。
エラー表示の横に警告マークが出ている場合は、数値に変換する操作を利用できます。
数値として扱うべき列に文字列が混ざると、比較結果や集計結果が不安定になることがあります。
文字列の前後にスペースが入っている場合は、TRIM関数で余分な空白を取り除く方法もあります。
元データの形式を整えてから判定式を作ることが、後からのトラブルを減らす近道です。
【操作のポイント】点数列と出席率列は数値形式、提出状況などの項目は文字列形式として、入力ルールを分けて管理しましょう。
数式コピー後の参照ずれ
続いては、先頭行では正しいのに下の行で判定が崩れる場合の原因を確認していきます。
受験者ごとの点数や出席率は、B2、C2のような相対参照で問題ありません。
一方、全員共通の合格基準を置いたG2セルなどは、数式をコピーしても動かないよう$G$2と絶対参照にします。
基準セルにドル記号を付けないと、下へコピーしたD3ではG3、D4ではG4を参照してしまい、空白や別の値と比較することになります。
数式をコピーした直後に、下の行を一つ選択して数式バーを見れば、参照ずれは早い段階で発見できます。
意図的に列だけ固定したい場合は$G2、行だけ固定したい場合はG$2という混合参照も使用できます。
ただし、基本的な合否判定では、受験者データは相対参照、共通基準は絶対参照と覚えると十分です。
【操作のポイント】コピー前の数式とコピー後の数式を比較し、動くべき参照と固定すべき参照が期待どおりか確認しましょう。
まとめ エクセルで複数条件の合否判定を行う方法
エクセルで複数条件の合否判定を行うときは、まず合格に必要な条件を整理し、その関係に合う関数を選ぶことが基本です。
すべての条件を満たす必要がある場合は、IF関数とAND関数を組み合わせます。
複数の条件のうち一つを満たせばよい場合は、IF関数とOR関数の組み合わせが適しています。
出席率を必須にしつつ筆記または実技の点数を見るような判定では、AND関数の中へOR関数を入れることで、実務に合ったルールを作成できます。
複数条件の判定式は、条件を日本語で整理してから、必須条件と選択条件に分けて数式化することが重要です。
合格、再試験、不合格、保留など複数の結果を表示したい場合は、入れ子のIF関数を使い、優先度の高い条件から順に記述しましょう。
基準値を別セルに置いて絶対参照にすれば、合格点や必要出席率が変わっても、数式全体を書き換えずに対応できます。
最後に、70点ちょうどなどの境界値、空白セル、文字列として保存された数値、コピー後の参照ずれを確認すれば、合否判定の精度を高められます。
IF関数、AND関数、OR関数を使い分けて、確認しやすく信頼できる合否判定表を作っていきましょう。
