Excel（実践４）出欠表

前回の記事では目標売上表2をテーマに複合参照に関する考え方やVLOOKUP関数、SUMIF関数の使い方について説明しました。
今回は出欠表をテーマにデータの入力規則や条件付き書式について学習していきましょう。
かなり色々な内容が盛り込まれているのでしっかり読んでください。
また、読むだけでなく自分で実際にやってみてください

出欠表の作成
COUNTIF関数、COUNTA関数
データの入力規則
条件付き書式

出欠表の作成

以下のような出欠表を作成していきます。
ただし、C12～D15は空白にしておきます。（あとで計算式で埋めるためです）
またD8は未回答という意味でわざと空白にしておきます。
この表はある会社の忘年会の出欠に関するアンケート結果をとりまとめた表です。
アンケート対象者に必ず出席「〇」か欠席「×」のどちらかを入力して貰う予定でしたが
残念なことに一部の人が正しく答えてくれませんでした。

COUNTIF関数、COUNTA関数

COUNTIF関数を使って12月1日と12月8日の出席「〇」と欠席「×」の数を数えてみましょう。
COUNTIF関数は検索範囲の中に検索する値がいくつあるか数える関数で「=COUNTIF(検索範囲 , 検索する値)」という書き方です。
C12の式を「=COUNTIF(C3:C10 , “〇”)」としてオートフィルで右に引っ張ってみましょう。
同様にC13の式も「=COUNTIF(C3:C10 , “×”)」としてオートフィルで右に引っ張ってみましょう。
なお、数式の中で文字を指定する時はダブルクォーテーションの記号「”」で文字を挟む必要があります。

合計は上で求めた出席と欠席の数字を単純に足して合計数を表示します。
SUM関数を使っても直接上2つのセルを足してもどちらでも良いです。

回答チェックは全員「〇」か「×」を入力したか確認して、全員入力していたら「OK」そうでなければ「NG」を表示します。
ここで改めて成績表で学習したIF関数の使い方を思い出してみましょう。
IF関数の式は「=IF(条件 , 条件を満たした場合の結果 , 条件を満たさなかった場合の結果)」という書き方でしたね。
今回の回答チェックではIF関数を使って「=IF（全員「〇」か「×」を入力した , “OK” , “NG”）」という式になるのですが、では全員「〇」か「×」を入力したかどうかはどのように判断すれば良いでしょう。
その場合はCOUNTA関数という関数を使ってアンケート対象者の数を求めて、その結果と「〇」と「×」の合計数（C14やD14）が一致しているか比較すれば良いのです。
なお、今回は関係ないですがCOUNTA関数は空白のセルは数えないので対象範囲の中に空白のセルがある場合は気を付けましょう。
※下図はCOUNTA関数の使い方の例を示すためにF3～F4に「アンケート対象者」というセルを追加して表示していますが、実際はC15のIF関数の式の中にCOUTA関数を入れて比較します。

C15の式は「=IF(COUNTA($B3:$B10)=C14, “OK”, “NG”)」として、オートフィルで右へ引っ張りましょう。

これで出欠表が完成しましたがD6には「△」が入力されており、D8は空白となっているためD14が8ではなく6となってしまいD15が「NG」となっています。
そのような回答を防ぐ方法として以下でデータの入力規則や条件付き書式について説明します。

データの入力規則

D6のセルに「△」が入力された原因は、セルに自由に好きな文字を入力できる状態にしていたからです
Excelに限らずシステムや機械全般に言えることですが、いくらルールを決めたところで人間による予期せぬエラーは発生するものですので、そのようなエラーを防ぐためにはそもそもエラーを起こせないような仕組みにしておくことが大切です。（この考え方をフールプルーフと言います）
指定の値以外を入力出来なくするためにデータの入力規則という機能を使ってみましょう。
C3～D10を選択した状態でデータタブを選択し、リボンの「データツール」グループ内の「データの入力規則」ボタンをクリックします。

「データの入力規則」画面が表示されるので「設定」タブの条件の設定の中の「入力値の種類」欄を「すべての値」から「リスト」へ変更し「元の値」の欄に「〇,×」を入力して「OK」をクリックします。

C3～D10のセルの右下に▼マークが付いて「〇」か「×」のどちらかしか入力できなくなりました。
（もともと入力されていたD6の「△」はそのまま残っています）

もし無理やり「〇」と「×」以外の値を入力してもエラーメッセージが表示されて入力出来なくなっています。

なお、エラーメッセージの内容は「データの入力規則」画面の「エラーメッセージ」タブのエラーメッセージ欄から変更することが出来ます。

なお、入力規則はリスト以外にも日付や時刻や文字数を制限することも出来ます。
「データの入力規則」画面の設定タブの条件の設定の中の「入力値の種類」欄をクリックすると色々な種類から選ぶことが出来ます。

また、元の値を直接入力するのではなく、セルを参照させることも出来ます。
元の値の横にある「セル参照ボタン」をクリックし、入力値にしたい値のセル範囲をマウスでドラッグします。（自動的に絶対参照で対象のセルが入力されます）

後で変更する可能性がある値を元の値とする場合はは直接入力ではなくセルの参照にしておくと、変更する際の手間が省けたり入力ミスする可能性が減ります。
例えば会社の備品を管理する備品管理簿で部署名をリストから選択させる場合などありますが、部署名は会社の組織再編などで変更になることが良くあるので、その場合は参照元のセルの部署名を変えるだけでリストの値が自動的に変わります。

データの入力規則によりD6の「△」は防止することが出来ましたがD8の空白は防ぐことが出来ません。
そのような場合は空白のセルを目立たせることで気まずい雰囲気にして無言の圧を掛ければ入力してくれるでしょう。
以下では特定の条件のセルの見た目を変える「条件付き書式」という機能について学習しましょう。

条件付き書式

ここでは、空白だったらセルを黄色にするという条件付き書式の設定をしていきます。
C3～D10を選択した状態でホームタブを選択し、リボンの「スタイル」グループ内の「条件付き書式」ボタンをクリックし、「新しいルール」をクリックします。

「新しい書式ルール」画面の「ルールの種類を選択してください」の中から「▶指定の値を含むセルだけを書式設定」を選択し、「ルールの内容を編集してください」の中の「次のセルのみを書式設定」の中から「空白」を選択して「書式」ボタンをクリックします。

「セルの書式設定」画面の「塗りつぶし」タブの黄色を選択し、サンプル欄に黄色が表示されている状態で「OK」ボタンをクリックします。

「新しい書式ルール」画面に戻ってプレビュー欄に黄色が表示されている状態で「OK」ボタンをクリックします。

空白のセルが黄色になりました。

なお、一度作成した条件付き書式のルールは「ルールの管理」から編集することも出来ます。

今度は出席「〇」だったら太い青文字してみましょう。
先ほどと同様の手順で「条件付き書式」の「新しいルール」をクリックし「新しい書式ルール」画面の「ルールの種類を選択してください」の中から「▶指定の値を含むセルだけを書式設定」を選択し、「ルールの内容を編集してください」の中の「次のセルのみを書式設定」の中から「セルの値」「次の値に等しい」「〇」と設定して「書式」ボタンをクリックします。