1 IF関数の基本的な使い方
| 数 式 例 と 説 明 | ||
1-1 |
ＩＦ関数、AND関数、OR関数の書式 IF（論理式，真の場合，偽の場合） 論理式は、等号、不等号を 含む数式です。 AND（論理式１，論理式２，・・・ ） 全ての（ ）内の論理式が正しい ときに、TRUEを返します。 TRUEとは、真の場合に該当する ということです。 つまり、IF関数の論理式中に AND関数を使うと、 AND関数の（ ）内の論理式が 全て正しい時に、 IF関数の真の場合を実行します。 AND関数の（ ）内の論理式の 一つでも偽であれば、 IF関数の偽の場合を実行します。 OR（論理式１，論理式２，・・・ ） （ ）内の論理式の一つでも正しい ときに、TRUEを返します。 IF関数の論理式中にOR関数を 使うと、 AND関数の（ ）内の論理式の 一つでも正しい時に、 IF関数の真の場合を実行します。 |
例えば、D2のセルに次式を入力します。 =IF（B2="","",B2+C2） ""は、ブランク（何も入ってなく空白）ということです。 論理式（B2=""）は、セルB2がブランクかどうか確認しています。 論理式（B2=""）が正しければ、D2はブランク表示とし、論理式（B2=""）が正しくなければ、つまりB2に数値か入っているとB2とC2を足してD2に結果を表示します。 もちろんB2に数値でなく文字が入っていれば、足せないのでエラー（＃VALUE!）となります。 この場合、C2は確認してないので、C2に数値が入っていても、B2がブランクであれば結果はブランクとなります。 C2に数値があればB2にも必ず数値の入る表であれば問題ないのですが、C2のみ数値が入ることがある表であれば、B2もC2もブランクかどうか確認する必要があり、次式のAND関数の出番です。 =IF（AND（B2="",C2=""）,"",B2+C2） IF関数の論理式中にAND関数を使っています。 B2もC2も両方ブランクであれば、AND（B2="",C2=""）が正しいことになりブランク表示します。 この場合、C2のみに数値が入っていても、B2+C2の結果は、ブランクではなくC2の値が表示されます。 なお、AND関数の変わりにOR関数を使うと、B2かC2かどちらかブランクであれば、ブランクとなります。 この場合、B2もC2も両方数値が入ってないとB2+C2の結果は表示されずブランクとなります。 IF関数の使い方の誤りとしては、（ ）でくくってなかったり、カンマ（，）を落としたりすることが多く、注意が必要です。 |
1-2
|
データが無いセルを空白表示 データが未入力のセルを全てブラ ンク表示とします。 エクセルではブランク「””」と「０」を 区別します。 数式の計算結果が出力されるセル を「０」と表示するのではなくブランク にします。 |
下の「汎用マルチ集計表」の事例 横の合計欄（列）の数式 =IF(AND(B3="",C3="",D3="",E3="",F3=""),"", SUM(B3:F3)) 合計を「０」と表示させても良いのですが、入力データ１から５のいずれにもデータがない場合は、ブランク表示としています。他のセルで参照するときも扱い易いし、未入力であることが解り易く表が見やすくなります。 なお、SUM(B3:F3)は、（ ）内の範囲のセルの値を集計する関数で、B3+C3+ ・・・ +F3を実行します。 平均値の欄（行）の数式 =IF(B15="","",AVERAGE(B3:B14)) AVERAGE(B3:B14)は、（ ）内の範囲のセルの平均値を求める関数です。 この場合、B15がブランクならブランクとし、そうでなければ平均値を計算させてますが、合計であるB15がブランクであれば、当然B3〜B14は全てブランクのためです。 四半期計（列）の数式 =IF(OR(G3="",G4="",G5=""),"",SUM($G3:$G5)) OR関数を使って、G3かG4かG5の一つでもブランクであればブランクとし、G3〜G4全てに値が入っていれば集計しています。 |
＜スポンサーリンク＞
 |
||
1-3 |
０除算エラー回避のため使用 数式が正しくても、数式の分母となる 値が「０」で あれば０除算エラーとな り、「＃DIV/0!」と表示されます。 式の分母となるセルは、必ず「０」や ブランクとなることがないか確認が 必要です。 ＜参考 他のエラー表示例＞ 「＃＃＃」 セルに値が表示できない セルの幅を広げれば治ります 「＃VALUE!」 数式、関数、値の誤り 数値でなく文字は足せない等 「＃NUM!」 数式、関数の数値が不適切で 答えが出せない 「＃NAME?」 数式、関数の名前が不適切 数式中の文字列が""で囲まれてい ない 「＃N/A」 数式、関数で使う値がない 引数の指定方法の誤り 「＃NULL!」 参照範囲の指定が正しくない 「＃REF!」 参照するセルがない（無効） 参照先セルが削除されている |
上の「汎用マルチ集計表」の事例 前月比欄（列）の数式 =IF(OR(G3="",G4=""),"",G4/G3) 目標達成率欄（列）の数式 =IF(OR(G3="",G$19=""),"",G3/$G$19) 目標達成率欄（行）の数式 =IF(OR(B15="",B19=""),"",B15/B19) 前期比欄（列）の数式 =IF(OR(K4="",K7=""),"",K7/K4) シェア（構成比）を求める =IF(B15="","",B15/$G$15) いずれも分母がブランクであれば、数式の計算をせずにブランクのまま表示させています。 なお最後のシェアを求める数式は、B15に数値が入れば、合計欄のG15には必ず数値が入るためG15がブランクかどうかの確認は省いています。 エラー回避のための他の参考事例 騒音レベルの計算式 =IF(AND(D$5>0, D$6>0),D$5-20*LOG10(D$6) +D20," ") 基準距離の騒音レベル（D$5）から、距離(D$6)が離れた地点の騒音レベルを求める数式です。 D$5もD$6も負の値となることは想定してないため、正の数値が入っていれば計算させています。 この式で、IF関数を使わないで、 「=D$5-20*LOG10(D$6)+D20」とすると D$5がブランクであれば、「＃VALUE!」エラーとなり、 D$6がブランクであれば、「＃NUM!」エラーとなります。 D$6がブランクまたは「0」であれば、LOGが取れないため、当然「＃NUM!」エラーとなります。 D$5がブランクであるとなぜ「＃VALUE!」エラーとなるか解りづらいと思いますが、実はD20のセルには回折効果による補正量を求める数式（技術系の数式であり省きます）が入っているためです。 |
1-4 |
エラーチェックに使う 表が問題なくても、データの入力ミス は避けれません。 特に重要なデータは、面倒でも小計 や合計、あるいは増減も入力して、 チェックすると精度が高まるでしょう。 |
エラーチェックの数式の事例 =IF(G13-G14=J12,G13-G14,"エラー") G13もG14も集計値ですが、あらかじめJ12の増減がわかっており、一致しないと「エラー」と表示させています。 =IF(L19="","",IF(L19>=L20,"適合","否")) ある一定値以下のデータは、確認して集計から除外または再入力するために、「否」と表示させています。 ３次元座標の2地点の長さを求める数式 =IF(OR(D$26="",D66<D38),"",SQRT(POWER((D36 -D$23),2)+POWER((D37-D$24),2)+POWER((D66 -D$25),2))) D26がブランクか、D66がD38より小さいときはブランクとしています。つまり、この場合はD66がD38より大きくなるはずというチェックをかけています。 数式は、ピタゴラスの定理、ルートＸ自乗＋Ｙ自乗＋Ｚ自乗を計算する式です。 |
1-5 |
特定データを区別して計算 ＩＦ関数は、一般的にはある特定条件 に当てはまるものだけをピックアップ して、数式を当てはめて計算するとい う使い方をするものです。 全データをいくつかの区分に分類して 集計するという使い方が現実には多 用されます。 ADSLコーナーの 「ADSL12M速度分析」や 「ＣａｒｒｉｅｒLｏａｄ分析表」などでは、 NTT局から自宅までの回線距離や伝 送損失により速度測定結果を分類し て集計しています。 |
データを補完する数式 =IF(B6="",C5,B6) 左隣のセルがブランクであれば、すぐ上のセルの値を入れるだけのことです。 伝送損失が平均未満のデータを抽出する数式 =IF($C7="","",IF($D7<19.58*LN($C7)+20.75, $E7,"")) 距離相応（回帰式による理論値）の伝送損失より低いデータを表示しています。 伝送損失が平均的回線の速度差を求める数式 =IF($C9="","",IF(AND($D9<19.58*LN($C9)+20.75 +3,$D9>=19.58*LN($C9)+20.75-3),$E9+3945.8* LN($C9)-7388.2,"")) 伝送損失が平均±3dB未満のデータを抽出し、その速度と平均速度（回帰式による理論値）との差を計算し、表示しています。 この他、ADSLの速度は距離区分ごとに分類して集計していますが、集計はSUMIF関数を多用しており、ＳＵＭＩＦ関数の説明で紹介しています。 下の「ADSLランニングコスト比較表」の事例 特定のデータのみ別計算をする数式 =IF(A4="","",IF(D4="",IF(H4="",B4+C4+E4+F4,(B4 +C4+E4+F4)*1.05-0.5),IF(H4="",(B4+C4)/12+E4 +F4,((B4+C4)/12+E4+F4)*1.05-0.5))) 通常月額料金合計の数式です。 年額払いはD4が○の電光石火、消費税外税のＸも電光石火（実際は内税も表示されている）ですが、ほとんどの業者が利用料月額払いで、内税表示ですから、その場合何も入力しないブランクのままの表としています。なおD4もH4もブランクかどうか調べているだけで○やＸでなくても何を入力してもブランクではなく同じことです。 つまりD4もH4もブランクの通常のケースは、最初の青色の式、月額払い外税は次の緑色の式、年額払い内税は茶色の式、年額払い外税は最後の赤色の式で計算されます。 |
 |
||
＜スポンサーリンク＞
2 COUNTIF関数の使い方
| 数 式 例 と 説 明 | ||
数を数える関数として、 COUNT関数があります。 この応用で、一定の条件に該当 するものを数えるための関数が、 COUNTIF関数です。 特定のものを数えたいことの方が 多いため、COUNTIF関数は便利で よく使われます。 ＜COUNTIF関数の書式＞ COUNTIF(範囲，検索条件) 範囲内のセルで、検索条件に一致 する値のセルの数を数えます。 ＜COUNT関数の書式＞ COUNT(値1、値2・・・) 引数リストの各項目に含まれる数 値の個数の合計を返します。 つまり COUNT(範囲) とセルの範囲を指定すれば、 範囲内のセルで、数値のあるセル の個数を数えます。 ＜参考 比較演算子の種類＞ (等号) = 左辺と右辺が等しい (不等号) <> 左辺と右辺が等しくない > 左辺が右辺よりも大きい >= 左辺が右辺以上である < 左辺が右辺よりも小さい <= 左辺が右辺以下である |
「汎用マルチ集計表」の年間予想合計値欄の数式 =IF(OR(G15="",G19=""),"",G15/(12-COUNTIF (G3:G14,""))*12) 一年12か月のうち、まだデータが入力されていない月数を数えています。 「汎用マルチ集計表」の縦の合計欄（行）の数式 =IF(AND(B3="",B4="",B5="",B6="",B7="",B8="", B9="",B10="",B11="",B12="",B13="",B14=""), "",SUM(B3:B14)) 上の数式中のAND関数では、B3〜B14までの全てのセルがブランクかどうか確認し、全てブランクであれば合計もブランクとしていますが、式が長くなり面倒です。 また、この場合はB3〜B14まで１２個のセルですが、さらにセルが多くなった場合は無理があります。 対象セルが多くなれば、COUNT関数または、COUNTIF関数を使用して、セルの範囲を指定して、確認する方法が便利です。 COUNT関数で、指定範囲のセルに一つでも数値があれば集計するための式です。 =IF(COUNT(B3:B14)<>0,SUM(B3:B14),"") B3〜B14の範囲のセルで、数値の個数を数えて、0でなければ、B3〜B14を集計しています。 =IF(COUNT(B3:B14)=0,"",SUM(B3:B14)) でもIF関数の真の場合と偽の場合を入れ替えただけで同じです。 また同じことを、COUNTIF関数を使うと次の式となります。 =IF(COUNTIF(B3:B14,">0")=0,"",SUM(B3:B14)) B3〜B14の範囲のセルで、正の数値が入っているセルを数えています。正の数値が入っているセルが0でなければ、B3〜B14をSUM関数で集計しています。上述のいずれの数式でも、この場合結果は同じです。 上の「ADSLランニングコスト比較表」の事例 最下段の最も安い業者・プランとして表示されている「電光石火12M年風神」を表示する数式 =IF(COUNTIF(L4:L9,">0")=0,"",INDEX (A4:A9,MATCH($L$10,L4:L9,0),1)) 下で説明するCOUNTIF関数とINDEX関数、MATCH関数を使用して表示しています。 なおセルの書式設定でフォントを太字として表示しています。 他のCOUNTIF関数の使用事例 同じ値の件数を数える数式 =COUNTIF(C$1:C$366,C12) C1〜C366までの範囲のセルで、C12のセルと同じ値のセルの数を数えます。 値が1.5以上2.0未満の件数を数える数式 =COUNTIF(C$6:C$505,"<2.0")-COUNTIF(C$6: C$505,"<1.5") ADSLの速度集計で、距離1.5km以上、2.5km未満のデータの数を数えています。 |
＜スポンサーリンク＞
 |
||
3 SUMIF関数の使い方
| 数 式 例 と 説 明 | ||
集計するための関数として、 SUM関数があります。 この応用で、一定の条件に該当す るもののみ集計するための関数が、 SUMIF関数です。 ＜SUMIF関数の書式＞ SUMIF(範囲，検索条件，合計範囲) 範囲内のセルで、検索条件に一致 する値のセルの値を集計します。 |
上の「商品購入コスト比較表」の事例 選択NO1の購入価格（66,080）の数式 =IF(COUNTIF($B$3:$B$14,">0")=0,"",SUMIF ($B$3:$B$14,0,C$3:C$14)+SUMIF($B$3:$B$14,1, C$3:C$14)) 最初のSUMIF関数は、選択NO「０」のCPUとCPUファンの購入価格を集計し、次のSUMIF関数は、選択NO「１」のマザーボードとPC3200メモリーとPCケースの購入価格を集計し、さらにその両方を足しています。 選択NO2も選択NO3も同じ考え方で、要は２列目の「選択NO」の０〜３の番号で商品を区別して、「０」は全てのケース（購入を決めているもの）で合算し、「１〜３」は同じ番号のもののみ集計します。 外税の縦の合計欄の数式 =IF(G15-C15-E15-F15=ROUNDDOWN(SUMIF ($D$3:$D$14,"Ｘ",C$3:C$14)*0.05,0),G15-C15- E15-F15,"エラー") 外税の合計欄（消費税計）は、TOTALの合計額から購入価格計、配送料計、振込手数料交通費計を控除して求められますが逆算となるため、SUMIF関数を使いＸ印のついた商品の消費税を集計し付き合わせています。 なお、この場合はブランクでなく「0」と表示させています。またこの表は説明用につくったもので、普通は消費税の金額を表示するセルをもう一列付け加えればすむことです。 他のSUMIF関数の使用事例 値が1.0以上1.5未満のデータを集計する数式 =IF($AP8>0,SUMIF($C$6:$C$505,"<1.5",E$6:E$505) -SUMIF($C$6:$C$505,"<1",E$6:E$505),"") ADSL12M速度分析では、距離区分ごとに伝送損失や速度を集計しており、この式は距離1.0km以上、1.5km未満のデータを集計しています。 値が1.0以上1.5未満の平均値を計算する数式 =IF(COUNTIF($C$6:$C$30,">=1.5")-COUNTIF ($C$6:$C$30,">=2.5")>0,(SUMIF($C$6:$C$30, ">=1.5",D$6:D$30)-SUMIF($C$6:$C$30,">=2.5", D$6:D$30))/(COUNTIF($C$6:$C$30,">=1.5") -COUNTIF($C$6:$C$30,">=2.5")),"") 平均値を算出するためには、何もAVERAGE関数を使わなくても、集計値を集計件数で割れば良いのです。そのためにCOUNTIF関数とSUMIF関数を組み合わせて使います。 |
＜スポンサーリンク＞
4 論理関数の併用とネストの制限
| 数 式 例 と 説 明 | ||
初めに、ＡＮＤ関数、ＯＲ関数は説明し ましたが、類似のNOT関数も含めて、 他の関数と組み合わせて使って意味 があるものです。 関数の引数（関数の括弧内で使うこと） として関数を重ねて使うことをネストと 呼び、ネストの制限で７レベルまで しか使えません。 このネストの制限は、特にIF関数を 重ねて使うときに制約となることが 多く、そのクリアのためにAND関数 やＯＲ関数が役に立つのです。 ＜エクセルの仕様に関する制限＞ ワークシートのサイズ 65,536 行、256 列 256列までという制限は、不足する ことがあるかもしれませんが、別の シートに分ければ良いでしょう。 引数の制限 一つの関数に使用できる引数は３０ までですが、ネストの制限と比べれば 困ることは少ないでしょう。 セルの内容の長さ (文字列) 32,767 文字 セルに表示できるのは 1,024 文字 まで。 数式バーでは 32,767 文字すべて の表示が可能。 数式が長くて使用できないということ はないでしょう。 ネストの制限内であれば、長い式で あっても気にすることではなく、短く しようとか余計なことを考えてる暇に 次のセルの数式を考えた方が早く 表ができます。 |
「汎用マルチ集計表」の横の合計欄（列）の数式 =IF(AND(B3="",C3="",D3="",E3="",F3=""),"", SUM(B3:F3)) この式のAND関数とSUM関数は、IF関数の引数であり、いずれも第２レベルの関数です。 上の式は、AND関数を使わなくても数式ができます。 =IF(B3="",IF(C3="",IF(D3="",IF(E3="",IF(F3= "","",SUM(B3:F3)),SUM(B3:F3)),SUM(B3:F3)), SUM(B3:F3)),SUM(B3:F3)) もしB3=""が正しい（真の場合）、つまりブランクであれば、C3がブランクかどうか確認し、C3もブランクであればD3は？と次々とF3まで真の場合に該当してブランクであれば結果はブランクとし、そうでなければSUM(B3:F3）と集計しています。 最初のIF(B3=""のIF関数は第１レベルの関数、次のIF（C3=""のIF関数は第2レベルの関数となり、 さらにIF関数の真の場合の引数としてIF関数を使い続けて、最後のIF（F3="","",SUM(B3:F3)のSUM関数は第６レベルの関数となります。 この場合は、ネストの制限７レベルをクリアしていますが、小さな表でもAND関数を使わないと危ないのです。 ネスト制限の7レベル使用した実例 障害物（水平Ｘ軸プラス方向）判定数式 =IF(D$23="","",IF(AND($D$11>D$24+($D$10-D$23)*($D$15-D$24)/($D$14-D$23),$D$11<D$24+($D$10-D$23)*($D$18-D$24)/($D$17-D$23)),1,IF(AND($D$11>D$24+($D$10-D$23)*($E$15-D$24)/($E$14-D$23),$D$11<D$24+($D$10-D$23)*($E$18-D$24)/($E$17-D$23)),2,IF(AND($D$11>D$24+($D$10-D$23)*($F$15-D$24)/($F$14-D$23),$D$11<D$24+($D$10-D$23)*($F$18-D$24)/($F$17-D$23)),3,IF(AND($D$11>D$24+($D$10-D$23)*($G$15-D$24)/($G$14-D$23),$D$11<D$24+($D$10-D$23)*($G$18-D$24)/($G$17-D$23)),4,IF(AND($D$11>D$24+($D$10-D$23)*($H$15-D$24)/($H$14-D$23),$D$11<D$24+($D$10-D$23)*($H$18-D$24)/($H$17-D$23)),5,"")))))) 上の式は、IF関数を6つ、AND関数を１つ、合計７つ使用しており、これ以上、関数の引数として関数を使うことができない事例です。 なお、途中のAND関数は、同じ括弧内のIF関数のレベルと同じです。 この場合、AND関数を使わなければ、IF関数は11個必要で、ネストの制限をクリアできません。 数式の意味は、３次元座標で、Ｘ軸プラスの進行方向に、障害壁が５つあり、最初に当たる壁を特定する数式です。 この後、障害物に当たるＸ座標の位置、障害物の高さの算出、超えていける距離の算出と続き、数式の説明は省略しますが、いずれもネストの制限はぎりぎりクリアしています。 私自身は、平気で長い数式を使いますが、上の障害物判定数式でも、本来の式は青色部分のみであり、他は繰り返しにすぎません。 式が長くてもセルへの貼り付けはコピーすれば良いし、むしろ長くても考え方がすっきりしている方が、後で修正するときに解り易いという面があります。 「汎用マルチ集計表」の縦の合計欄の数式は、 =IF(COUNTIF(B3:B14,">0")=0,"",SUM(B3:B14)) よりも、 =IF(AND(B3="",B4="",B5="",B6="",B7="", B8="",B9="",B10="",B11="",B12="",B13="",B14 =""),"",SUM(B3:B14)) の方が後からみたときに解り易く、頭が疲れないのです。 |
