Excel (エクセル) 学習室 / KENZO30


エクセル中級 10

中級・特別編トップへ

エクセル学習室 HOME

   中 級 10

Excelの基礎

  統計関数(ネスト関数)        
  入力規則(関数入力)   

 

 

統計関数(ネスト関数)

ここで、論理関数統計関数ネストについて使用例をあげながら解説します。ネスト関数の入力方法と要点は、中級5、6に解説していますので、参考にしてください。

 

IFとMAX関数のネスト

使用例 1

下図のボーリング大会のデータで解説します。図のデータは、第3回目のものですが、過去2回の最高点と比較して、それをクリアしたかどうかを評価したものです。

図のネスト関数は、IF関数論理式の引数に、MAX関数を使って今回の最高点を求め、これが過去2回の最高点を超えているかどうかを判定させ、真の場合(超えている場合)は、達成の文字、偽の場合(超えていない場合)は、非達成の文字を表示させるようにしています。

佐々木選手の評価欄B10セルへネスト関数をつくり、あとの選手へはコピーしたものです。(セル参照は、相対参照。)
さらに、評価にあわせ条件付き書式で達成の場合は、フォントの色を赤で表示するように設定しています。

このような関数を作る場合、論理式の作り方に注意が必要です。
論理式の引数にAND関数などの論理関数を入れる場合は、そのままで論理式は成り立ちますが、例のようにMAX関数などを使った場合、その関数で求めたものと他のデータを比較する構造にしなければいけません。
この例では、比較演算子を使い過去の最高点と比較する(
>B9)ようになっています。

 

使用例 2

さて、「使用例1」では過去の最高点と同点であった場合、論理式に(>B9)と設定していますので未達成となります。これを別途に同点の場合は、”同点”と表示させるネスト関数を作ることもできます。

図のように、IF関数の引数にさらにIF関数を使っています。構造は、始めのIF関数で、過去2回の最高点を超えているかどうかを判定させ、さらにIF関数で同点かどうかを判定させるものになっています。

=IF(MAX(B3:B5)>B9,"達成",IF(MAX(B3:B5)=B9,"同点","未達成"))
(赤字は第1階層、青字は第2階層、ピンクは、第3階層の関数)

始めのIF関数の偽の場合の引数に、もう一回IF関数を入れ、さらにその論理式の引数にもう一度MAX関数で最高点を求め、過去2回の最高点と同じかどうかを判定させて、真の場合(同点の場合)は、同点の文字、偽の場合(以下の場合)は、非達成の文字を表示させるようにしています。

このように、IF関数の引数にさらにIF関数を使うと、より複雑な条件分岐が可能です。入力方法は、少し複雑ですが、マスターすれば関数の使い道が広がります。

 

先頭へ戻る

 

 

入力規則(関数入力)

入力規則を数式で設定

入力規則(初級9、10参照。)の設定には、ユーザー設定として数式を入力し、入力を制限することができます。


データ(D)から入力規則をクリックすると「データの入力規則ダイアログボックスが表示されます。
左図のように、「設定」タブ内の入力値の種類(A)でユーザー設定を選択します。数式(F)の入力枠へ数式で入力条件を設定することができます。
空白セルを対象にしない場合は、空白セルを無視する(B)にチェックを入れておきます。

たとえば、A列へ入力規則を「=A1>B1」と設定すると、横のB列の値より大きいデータしか入力できなくなります。小さい値を入力するとエラーメッセージが表示されます。(入力手順は、下記の関数の場合と同じ。)

 

 

入力規則を関数で設定

入力規則を数式で設定できると言うことは、当然、関数でも設定できます。ただし、配列数式は、使えませんので注意してください。

使用例

リスト(データベース)では、たとえば住所録の電話番号のように、同じ列内へ同じデータの入力を避ける必要があるものです。このような時、COUNTIF関数(中級9参照)を使って入力規則を設定すると入力ミスを回避できます。

A列全体へ入力規則を設定する場合の例です。

  • 列番号AをクリックしてA列を選択 

  • 「データ」の「入力規則」をクリック →「データの入力規則ダイアログボックスが表示される

  • 「設定」タブの「入力値の種類」を「ユーザー設定」にし

  • 「数式」に =COUNTIF(A:A,A1)=1 と入力します 
    =や関数名、括弧は、キーボードから直接入力します。A:Aは、列番号Aをクリック、A1は、A1セルをクリックすると入力できます。=1は、キーボードから入力します。
    A:Aは、A列全体を示す範囲です。入力した検索条件A1は、A2セルでは、A2と、A5セルでは、A5と自動的に変化します。

  • OKボタンをクリック

これでA列全体へ入力規則が設定され、同じデータが入力できなくなります。同じデータを入力するとエラーメッセージが表示されます。

設定した入力規則をA5セルで確認します。

 

設定した関数の入力条件は、図のように検索条件A1がA5に自動的に変化して設定されています。

設定された、=COUNTIF(A:A,A5)=1の意味は、A列内を検索し、A5セルのデータと同じものの数を数え、それが1つであること(A5と同じものがないと言う意味。)を条件に入力できると言う設定です。

 

この例では、A列全体へ設定していますが、たとえばA2からA20のように範囲を選択して、指定範囲に設定することもできます。操作は、全く同じです。

条件として使用できる関数は、COUNTIFだけでなく他のいろいろな関数でも設定できますので、工夫してみてください。

 

   先頭へ戻る   

中級・特別編トップへ

Excel(エクセル)学習室 HOME


KENZO30 内容の無断転載は、禁止しています