データを分析・試算する

エクセルは数値の計算だけでなく、データの分析や試算にも役立ちます。

出席者や合格者などを数える

◆ データの個数を数える（COUNT、COUNTA関数）

一覧表にあるデータの個数を数えるには、COUNT（カウント）関数とCOUNTA（カウントエー）関数を利用します。

例題のテスト結果の表で、受験者数を調べるには、点数が表示されているセルを数えればよいです。よって、COUNT関数を使ってセルの個数を調べます。セルE3には「=COUNT(B4:B13)」と入力します。
欠席者は、社員の総数から受験者を引けば求められますので、数値も文字も数えるCOUNTA関数で社員総数を求めて、先ほどの受験者数を引けば欠席者数となります。セルE4に「=COUNTA(B4:B13)-E3」と入力します。

◆ 指定した条件に合致したデータを数える（COUNTIF関数）

例題は、男性の数と女性の数を調べる表です。このような時は、COUNTIF（カウントイフ）関数を使います。引数は調べる範囲と調べるための条件を指定します。
男性および女性の数を数えるわけですので、調べる範囲は性別の列です。条件は、「男」「女」となります。この場合、文字列ですので、ダブルクォーテーションで挟んで記述します。

セルG3に「=COUNTIF(B3:B12,"男")」と入力します。

◆ 複数の条件に合致したデータを数える（DCOUNTA関数）

例題では、「社員セミナー受講記録」表から「広報部の男性」の人数を調べます。条件が複数あるときのデータを数えるにはDCOUNTA（ディーカウントエー）関数を使います。

この関数は、3つの引数をとります。「Database」引数には、数を調べる表全体を指定します。先頭行の項目名も含めて指定することに注意します。「フィールド」引数には、個数を数える項目名を指定します。この引数を省略することは可能です。省略した場合は単純に条件に合致したデータの行数を数えます。「Criteria」引数には、検索条件となる別表を指定します。例題では、所属と性別の２つの条件で「かつ」（AND）条件ですので、横並びに入力されていることに注目します。縦並びだとOR条件となります。

セルG6に「=DCOUNTA(A2:D12,,F2:G3)」と入力します。

◆ 以上～以下などの条件でデータを数える（COUNTIF、DCOUNTA関数）

指定した条件に合致するデータの数を数える時、「～以上、～以下」という数値の範囲を指定したいことがあります。これらは、COUNTIF及びDCOUNTA関数を利用すると可能です。数値の範囲は、等号や不等号を使います。ただし、引数の検索条件に入力する際に、ダブルクォーテーションで挟む必要があります。

「社員セミナー受講記録」表の中から出席数が９回以上であった社員の人数を調べるには、セルG3に「=COUNTIF(D3:D12,">=9")」と入力します。

また、出席数が7回以上9回以下のような複数の条件を指定する場合は、DCOUNTA関数を用いますので、条件用の別表を作成します。セルG6に「=DCOUNTA(A2:D12,,F2:G3)」と入力します。

データを分析し、平均や最高点を求める

◆ データの平均点を計算(AVERAGE関数)

セル内の数値の平均を求める時にはAVERAGE（アベレージ）関数を使います。社員の得点の平均を求めるには、平均を求める対象となるセルを指定します。なお、AVERAGE関数は、指定した範囲の中に空欄や文字列が含まれていても、それらを無視して、数値のみを平均します。ただし、「0点」の入力を省いて空欄にしているなら注意が必要です。空欄は平均計算の対象にならないので、正しい結果が得られません。0点の場合でも、かならず「0」と入力しておきます。

◆ データの中央値や最頻値を調べる(MEDIAN、MODE関数)

テスト結果を分析する時、平均値のほかに中央値や最頻値といった数値を求めると、より詳しい受験全体の得点の分布や傾向を分析できます。中央値を求めるにはMEDIAN（メジアン）関数、最頻値を求めるにはMODE（モード）関数を使います。
どちらの関数も引数として数値のセル範囲を指定します。

セルE3に「=MEDIAN(C3:C17)」、セルE5に「=MODE(C3:C17)」と入力します。

◆ 得点一覧から最高点や最低点を調べる(MAX、MIN関数)

最大値を求めるのがMAX（マックス）関数、最小値を求めるのがMIN（ミン）関数です。いずれの関数も、引数として数値データが入力されているセル範囲を指定します。また、引数に指定したセル範囲に含まれる文字列や空白のセルは無視されます。データが１つでも入力されていない場合は、MAX及びMIN関数のどちらも「0」になります。

セルE3には「=MAX(C3:C17)」、セルE5には「=MIN(C3:C17)」と入力します。

◆ 指定した順位のデータを調べる(LARGE、SMALL関数)

例題は、テスト結果の表から、上位３名と下位３名の得点を取り出したものです。上位であればLARGE（ラージ）関数、下位であればSMALL（スモール）関数を使います。また、例題ではE列には順位を入力しておいたので、引数の順位にそれぞれのセルを指定しています。
範囲には絶対参照を設定していますが、これはセルF3及びF8の式を下の２つのセルにコピーするために、範囲がズレないようにしています。

セルF3に「=LARGE($C$3:$C$17,E3)」と入力して下のセルにコピーします。セルF8に「=SMALL($C$3:$C$17,E8)」と入力して下のセルにコピーします。

◆ 条件に合致したデータの平均を計算する(DAVERAGE関数)

数値データ全体ではなく、条件に合ったデータのみの平均値を求める時には、DAVERAGE（ディーアベレージ）関数を使います。引数には３つあり、最初の引数「Database」には、集計対象の表全体のセル範囲を指定します。次に引数「フィールド」には、実際に平均値を計算する項目名を指定します。最後の引数「Criteria」には、平均するデータの条件を示した別表のセル範囲を指定します。
別表は、横並びに作ると「かつ」（AND条件）となり、縦並びだと「または」（OR条件）を意味します。

セルG7に「=DAVERAGE(A2:D17,"得点",F2:G3)」と入力します。

◆ 複数条件で最高点、最低点を調べる（DMAX、DMIN関数)

条件に合致したデータの中の最大値を調べる時はDMAX（ディーマックス）関数を、同じく最小値を調べる時はDMIN（ディーミン）関数を使います。これらの引数は、先ほどのDAVERAGE関数と指定方法はまったく同じになります。

セルG6に「=DMAX(A2:D17,"得点",F2:G3)」、セルG7に「=DMIN(A2:D17,"得点",F2:G3)」と入力します。

◆ 得点に対応する偏差値を計算する(STDEVP関数)

成績表などで、平均点などと同様に成績の指標となるのが偏差値です。偏差値を出す場合には、平均点と標準偏差が必要ですが、その標準偏差を求めるのがSTDEVP（スタンダードディビエーションピー）関数です。標準偏差は、平均からのデータの散らばり具合を表す数値です。同じ平均点でも、個々の得点が広範囲に分布していれば標準偏差は大きくなり、逆に狭い範囲に集中していれば標準偏差は小さくなります。

偏差値は、各自の得点を平均が50、標準偏差が10になるように換算してもので、「（得点－平均点）×10÷標準偏差+50」で計算します。

セルD5に「=STDEVP(C3:C17)」と入力して、標準偏差を求めます。

◆ 「20点単位」の点数分布を調べる(FREQUENCY関数)

区間に含まれる値の個数を求めるにはFREQUENCY（フリークエンシー）関数を使います。これを使えば、例題の範囲ごとの人数を計算できます。引数は、「データ配列」と「区間配列」の２つです。最初の「データ配列」は、数値データが入っているセル範囲で、「区間配列」は区間分けした各区間の最大値が入っているセル範囲を指定します。

FREQUENCY関数を入力する時には、複数のセルをまとめて入力します。まず、セル範囲F4からF8までを選択します。次にセルF4に「=FREQUENCY(B3:B17,E4:E8)」と入力し、確定の時にCtrl＋Shift＋Enterキーを押します。数式は、自動的に「｛=FREQUENCY(B3:B17,E4:E8)｝」のように「｛ ｝」で括られます。

さまざまな視点から順位を調べる

◆ データが全体の何番目の順位かを調べる(RANK関数)

それぞれの得点が全体の何位にあたるかを調べる時には、RANK（ランク）関数を使います。引数には３つあり、最初の引数「数値」には、順位を調べる数値のセルを指定します。次に引数「範囲」には、数値データ全体のセル範囲を指定します。最後の引数「順序」は、大きい順（降順）、小さい順（昇順）を指定するもので、省略または「0」を指定した場合は降順、「1」を指定した場合は昇順の順位を求めます。

セルD3に「=RANK(C3,$C$3:$C$14,0)」と入力して、下のセルすべてにコピーします。

◆ 試験の得点の上位５位の名前を調べる(VLOOKUP関数)

例題のように順位から上位５名の名前を調べたいのであれば、VLOOKUP（ブイルックアップ）関数を使います。VLOOKUP関数は、表の左端の値を検索して、その右にあるデータを取り出す関数です。順位を検索して名前を取り出すには、順位の右に名前がなくてはなりません。そこで、E列の欄には、A列の名前を参照できるようにしておきます。引数の範囲をD列とE列のセルを指定すれば、仮の表という形で順位における名前を取り出すことが出来ます。

セルH4に「=VLOOKUP(G4,$D$3:$E$14,2,FALSE)」と入力して、下のセルにコピーします。

条件に合致するかどうかを判定する

◆ 得点に応じて合否を判定する(IF関数)

合格点の以上の場合は合格、そうでなかったら不合格というように、ある条件を満たすか満たさないかによって処理を分ける時には、IF（イフ）関数を使います。引数である「真の場合」「偽の場合」には、セルに表示したい値が文字列の場合は、「"」で挟んで設定します。

セルE3に「=IF(D3>=100,"合格","不合格")」と入力して、下のセルにコピーします。

◆ 得点に応じてランク分けをする(IF関数)

先ほどの例題では、合計点を満たす、満たさないという２者選択の形になります。しかし、点数の応じて４段階のクラス分けで処理する場合は、IFを使ったネスト（入れ子）の形態で式を立てます。

例題の判定基準は、170点以上であれば「A」、169～120点なら「B」、119～70点なら「C」、それ以外なら「D」というランクに設定しています。
1つ目のIF関数では、170以上であれば「A」、そうでなければ2つ目のIF関数で判定する形にしています。2つ目のIFでは、120点以上であれば「B」、そうでなければ3つ目のIF関数で判定します。3つ目のIF関数では、70点以上なら「C」、そうでなければ「D」を選ぶことを指定しています。このように、関数の中にさらに関数を入れることをネスト関数と呼び、複数の条件で分岐選択が可能となります。

セルE3に「=IF(D3>=170,"A",IF(D3>=120,"B",IF(D3>=70,"C","D")))」と入力して、下のセルにコピーします。

◆ 「または」「かつ」「以外」で条件を指定する(AND、OR、IF関数)

例題は、筆記と会話の両方とも80点以上の人は「表彰」される人、筆記または会話のどちらか一方でも30点未満であれば「再テスト」する人、またランクが「D」以外でなければ「合格」Dの方は「不合格」と表示する表となります。
条件の組み合わせ方に応じて、IF関数と一緒にAND（アンド）関数とOR（オア）関数を使うと、簡単に判定できます。
また、以外という条件には左側と右側が異なるということから「＜＞」という記号を使います。

セルF3に「=IF(AND(B3>=80,C3>=80),"表彰","-")」、
セルG3に「=IF(OR(B3<30,C3<30),"再テスト","-")」、
セルH3に「=IF(E3<>"D","合格","不合格")」と入力して、それぞれ下のセルにコピーします。

◆ 受験者の上位50％を合格と判定する(PERCENTRANK、IF関数)

「上位○パーセントに入れば合格」というように、合格者を相対評価で決めたい時は、IF関数とPERCENTRANK（パーセントランク）関数を組み合わせます。
PERCENTRANK関数は、引数「全体」に含まれる数値を小さい順に並べたい時に、最小値を0、最大値を1として「対象」が全体の何％の位置にあるか「相対順位」を調べるものです。
例題では、合計の値が上位50％に入れば合格としたいので、相対順位が「0.5以上」となります。

セルE3に「=IF(PERCENTRANK($D$3:$D$12,D3)>=0.5,"合格","")」と入力して、下のセルにコピーします。

◆ プレゼント当選者をランダムに選ぶ(RAND、RANK関数)

無作為に選びたい場合は、RAND（ランド）関数を使います。この関数を使うと、0以上1未満のランダムな数値を表示できます。例題では、各会員に乱数を振り、「乱数の大きい順に5名が当選者」というルールで当選者の○を決めています。まず、セルC3に「=RAND()」と入力して、下のセルにコピーします。これで全会員にランダムな数値が割り振られます。これは決して重複することはありません。シートにすべての式が入力し終えるまで、乱数が繰り返されます。
次に、RANK関数を使って、乱数の列の降順（大きい順）の順位を決めて、5以下の場合とそれ以外の場合をIF関数で指定します。

セルD3に「=IF(RANK(C3,$C$3:$C$14)<=5,"○","")」と入力して、下のセルにコピーします。結果が変化しないよう名前のセルをコピーして、別の場所に「値の貼り付け」で保存しておきます。

◆ セルの値がエラーの時に表示を変える(IS関数)

エクセルは、数式を正しく計算できない時にエラーが表示されます。数式自体は正しくても、「数値の変わりに文字が入っている」「データが未入力」などの理由でエラーになることもあります。エラーが表示される場合に、それを隠す関数としてIS関数があります。セルに入ったデータの種類を調べて、TRUEまたはFALSEを計算結果とするもので、エラーかどうかを調べるものにISERROR（イズエラー）関数があります。そのほかにも空欄かどうかを調べるISBLANK（イズブランク）関数、数値かどうかを調べるISNUMBER（イズナンバー）関数、文字かどうか調べるISTEXT（イズテキスト）関数などもあります。
例題でみると、前年比を計算したいのですが、前年度と今年度の両方に数値が入力されていれば、正しく前年比が求められます。しかし、「調査中」と文字列、また除算する数値「0」がセル内にあるとエラーが表示されます。これを回避するために、IF関数で今年度と前年度の割り算の結果でエラーが生じたら「--」という記号を表示し、エラーが生じなければ割り算を行うように指定します。

セルD3に「=IF(ISERROR(C3/B3),"--",C3/B3)」と入力して、下のセルにコピーします。

ローンの支払額などを試算する

◆ 住宅ローンの返済金額を計算する(PMT関数)

金利が固定の場合、支払金額はPMT（ペイメント）関数を使います。利率、期間、借入金額などを引数に指定すると、返済額を算出できます。
例題で見ていくと、利率は「3.1」というパーセントの数値ですので、これを100で割って実際の値に戻します。さらに、利率は年単位の値（年利）ですので、これを12で割って、月単位の利率に換算します。引数「期間」も、年単位から月単位に換算するために、B3セルの値に12を掛けます。「現在価値」には、借入金額を指定するので、セルB6の値をそのままを使います。将来価値は、ローン返済時には借入金がなくなるので「0」、支払期日はここでは月末払いとして「0」にします。将来価値と支払期日が「0」の場合は省略できます。
なお、返済額は支出であるため、金額はマイナスで表示されます。

◆ 途中で金利が変わる住宅ローンを試算する(CUMPRINC、PMT関数)

住宅ローンの金利が途中から変わるケースは多いです。このような時の試算をするには、最初の10年間と11年目以降を別のローン返済と考えることがポイントです。
11年目以降の毎月の返済額を計算するには、まず最初の10年で支払う元金分の総額を求めるCUMPRINC（キュムプリンシパル）関数を使い、利率や期間などの引数の値を月単位に換算して指定します。

セルB8に「=CUMPRINC(B2/100/12,B4*12,B5,1,120,0)」と入力します。

毎月の返済額が算出できたら、10年目までの返済金額と11年目以降の返済金額を加算して、総返済額を求めます。この時も、返済額に12を掛けて月単位にすることを忘れないようにします。

セルB10に「=PMT(B3/100/12,B4*12-120,B9)」、セルB11に「=B7*12*10+B10*(B4*12-120)」と入力します。