エクセルファイルのダウンロード
HOME 便利テク集
前回はセル参照の、絶対参照と相対参照について説明しました。今回は、これらの参照を使った【条件付き書式】の使い方について説明したいと思います。この機能、少し複雑で慣れるのに時間がかかると思います。しかし、覚えると、非常に便利ですので、是非挑戦してみてください。
エクセルファイルのダウンロード
【条件付き書式】機能について、課題を通して、説明していきたいと思います。この機能を使って以下の課題を実装したいと思います。
まず、各科目ごとの平均点を算出したいと思います。最初に、英語の平均点を求めたいと思いますので、C17セルに、平均値を算出するAVERAGE関数を設定し、計算範囲には、C2:C16を指定してください。これにより、英語の平均点が求められるはずです。
続いて、他の国数理社と5教科についても平均点を算出したいので、C17セルをコピーし、D17～H17まで数式を貼り付けてください。これにより、科目ごとの平均点が全て、算出されます。
続いて、各科目ごとの分散について算出したいと思います。分散とは、データのバラツキ具合を表す指標です。この分散は、VAR.P関数で算出することができます。
C18セルに、VAR.P関数を設定し、計算対象の範囲には、平均点と同様に、C2:C16を指定します。これで、英語の得点データの分散が計算されたわけですが、他の国数理社と5教科の分散も算出したいので、C18セルをコピーし、D18～H18まで数式を貼り付けてください。これにより、科目ごとの分散が全て、算出されたことになります。
次に、各科目ごとの標準偏差について算出したいと思います。標準偏差も、分散と同様にデータのバラツキ具合を表す指標です。分散の平方根をとったものが標準偏差となります。べき乗計算は、POWER関数を利用し、平方根は1/2乗ですので、べき数には0.5を指定します。
C19セルにPOWER関数を設定し、底には、分散値(C18)を指定し、べき数には0.5を指定します。これにより、英語の標準偏差が求められます。
同様に、他の科目の標準偏差も計算するため、C19セルをコピーし、D19～H19まで数式を貼り付けます。
引き続き、生徒ごとの、5教科の偏差値を算出します。偏差値は、特定のデータが、データ全体(正規分布)のどのくらいの位置にあるかを示す値です。
偏差値は、STANDARDIZE関数(正規化)を使用しますが、パラメータ(引数)に、偏差値を求める値、平均値、標準偏差の3つの値が必要で、式:50+10*STANDARDIZE(値,平均値,標準偏差)により、算出します。
橋本一朗さんの5教科の偏差値を算出したいので、I2セルに、
式:50+10*STANDARDIZE(H2,H$17,H$19)
を設定します。この際、平均値と標準偏差は、生徒ごとに行方向に対して、絶対参照となりますので、行番号の前に$を付与します。
生徒全員の偏差値を算出しますので、I2セルをコピーし、数式を範囲I3～I16に貼り付けます。
以上で、生徒ごとの偏差値が算出できた訳ですが、この偏差値に対して、【条件付き書式】を使って、偏差値が60以上のデータを青の太文字、40以下のデータを赤の太文字で、表示したいと思います。
【リボン】の【ホーム】タブを選択して、【条件付き書式】-【新しいルール】をクリックします。
以下の画面が表示されますので、まず、ルールの種類の【数式を使用して、書式設定するセルを決定】を選択します。
次に、ルールの内容に、 =$I2<=$K$2 を入力します。これは、『偏差値が40以下であれば』という条件式となっています。
$I2 : 橋本一朗さんの偏差値。
$K$2 : 赤字の40。
現在は、橋本一朗さんの場合なのですが、杢谷さんの場合(条件式の対象が、行方向に移動)を考えてみます。ここが、ポイントです。
まず、I2セルへの参照についてですが、これは、杢谷さんの場合、I3セルへの参照となり、行方向の相対参照となることが分かります。従って、行番号の前の$については、削除しておきます。
続いて、赤点の40(K2セル)への参照ですが、これは、杢谷さんの場合も、同じセルとなりますので、絶対参照となります。ですので、行番号の$は、必ず付与しておいてください。
条件式の『偏差値が40以下であれば』が、成り立つ時に、赤太文字にしたいので、【書式】ボタンを押して、書式設定を行います。
※条件式 =($I2<=$K$2) に、括弧をつけて考えると分かりやすいと思います。括弧の中の条件式が判定され、True、Falseが返ります。Trueの時に、赤太文字の書式が設定されます。条件に入力した、一番左の = 以降の式が、論理判定されるということです。
【フォント】タブを選択して、スタイルを【太字】、色を【赤色】に設定し、【OK】ボタンを押下します。
元の画面に戻りますので、入力内容に問題がないことを確認して、さらに【OK】ボタンを押してください。
これで、橋本一郎さんの、偏差値に対する赤文字の条件付き書式が設定されたことになります。今回のサンプルでは、偏差値が40に満たないので、赤太文字になっていると思います。
他の生徒の偏差値に対しても、赤太文字の設定を行いたいので、今度は、リボンの【ホーム】を選択し、【条件付き書式】-【ルールの管理】をクリックします。
以下の画面が表示され、先ほど設定した条件付き書式が一覧に表示されますので、適用先に、 $I$2:$I$16 を設定して、【適用】ボタンを押してください。これで、全生徒に対して、赤文字表示の条件付き書式が設定されたことになります。
赤文字の設定ができたので、今度は、『偏差値が60以上であれば』の条件を満たすデータを青文字にする設定を行います。同じ画面の【新規ルール】をクリックしてください。
【新しい書式ルール】画面が表示されますので、ここまでの赤文字設定と同じやり方で、青文字設定についても行ってみてください。
最後に、【条件付き書式】設定の確認の為、シート右上の、赤字、青字の閾値を動的に変更してみてください。正しく設定できていれば、閾値の変更と同時に、それに該当する偏差値の書式も変更されるはずです。
今回の【条件付き書式】は非常に便利なのですが、設定が少し、複雑な印象を持っています。一つは、相対・絶対参照の理解度が要因のように思い、その4で、相対・絶対参照の説明をした訳ですが、使っているうちに慣れてくると思いますので、機会があれば、どんどん利用していきましょう。
個人的には、VBAと組み合わせると非常に強力な機能だと思っています。VBAのプログラムで実装していた機能も、この機能を使えば、プログラムせずに実装できるようなところもあり、プログラムの開発工数も格段に減らせます。また、Excel機能を利用していますので、VBAプログラムに負荷をかけずに同等の機能が実現でき、パフォーマンスも改善できるはずです。
サンプルのデータは、私の創作によるものです、サンプルデータに含まれる個人・団体名などの情報は、実在の人物とはまったく関係ありません。
ご意見・ご要望等ありましたら、画面最下部のメールアドレスまでご連絡ください。
