Sの会講座から（エクセル１）

社会福祉法人Sの会様の講座で、完成図が下記の表をしめして問題を出題しました。




実際にはセル範囲[D1:R9]を空欄として、セル範囲[D1:R1]に「9時」～「23時」と表示するように入力してもらい、左欄の「開始」と「終了」の時間を入力したときに、勤務可能な時間帯に「■■■」を表示させ、セル範囲[D10:R10]の「合計人数欄」その時間の合計を算出する問題にしました。

この問題は日経BP社発行の雑誌「PC21」の腕自慢大会（）に掲載された問題で、非常に実務的でいい問題だっとので、講座での使用許諾のお願いをしたところ、日経BP様から使用の許可について快諾していただきました。日経BP様の寛大なるご決断に敬意を表します。



最初のセル範囲[D1:R1]に「9時」～「23時」と表示させる問題ですが、やっぱり「9時」と入力してオートフィルで23時まで入力する人がいました。
「9時」と入力すると、文字列になってしまい、不等式が使えません。
したがって、「9：00」と入力し、同様の方法で「23：00」まで入力し、つぎにセル範囲[D1:R1]を選択してCtrl+1キーを押してセルの書式ダイアログボックスを表示させて［表示形式］タブで［ユーザー定義］を選択して「h"時"」入力して、表示だけを9時などと見せかかる必要があります。



次いで、セル[D2]について考えます。これは9時（9：00～10：00）の時間帯に浅丘さんが勤務可能かどうかを判断するもので、セル[B2]の開始時刻がセル[D1]の9時以下であること（=でも構わないことを確認してください）、かつセル[C2]の終了時刻がセル[D1]に9時より大である(=を含むと次の時間帯に勤務可能になってしまいます）ことが必要です。
したがって、ほかのセルにコピーすることを考慮して、「=IF(AND($B2<=D$1,$C2>D$1),"■■■","")」と入力して、ほかのセルにコピーすればOKです。
この方法が一番わかりやすくてオーソドックスな方法だと思います。

さらに、セル[D10]の9時に勤務可能な人数は「=COUNTIF(D2:D9,"■■■")」と入力して、セル[R10]までコピーすればOKです。
なお、セル[D10:R10にはセルの書式ダイアログボックスの表示形式タブのユーザー定義で「0"人"]と入力してあります。

上記の方法以外にもいろいろな方法があると思いますが、同じシリアル値どうしで比較して求める方法を一つ紹介します。
<、>、=、<=、>=などで構成される条件式はその式を満足すれば、「true」が、満足しなければ「false」を返します。これらの条件式を加減乗除に使用すると「true」が１として、「false」が0として扱われます。すなわち「true」×、「true」は１に、「true」×「fase」は0になります。
上記の問題では($B2<=D$1）と（$C2>D$1）の両方を満足させる必要がありますのでセル[D2]に「=($B2

この表の「１」を「■■■」に「０」を空欄にすればよいことになります。

エクセルにはセルに入力されているデータが正の場合、負の場合、０の場合、文字列の場合に表示形式を設定し、場合分けすることができます。
セミコロン(;)で区切って書式記号を並べます。



この表の正の場合；負の場合；0の場合に相当し、セル[D2:R9]を選択、はセルの書式設定ダイアログボックスのユーザー定義にして、「"■■■";;」と入力OKクリックすればできます。
この場合正(=1）の場合、「■■■を表示し、負の場合何も入力されていないので空欄に、0の場合も何も入力されていないので空欄になります。
当然、正の数が1以外にも存在するとおかしなことになりますが、この場合には問題ありません。

さらに、条件に応じて数値の表示形式を変えることができます。
条件は二つまで設定でき、条件と条件を満たさない場合の表示形式を、それぞれ；（セミコロン）で区切って指定します。

したがって、日経BPの優秀作品の一つにあったように、セル[D2:R9]を選択してユーザー定義にして「[=1]"■■■";""」と入力してもOKです。
この意味は「1」の場合「■■■」、それ以外には空欄になりというわけです。
上記の正、負、０の場合に比較し、数字が1と特定できるので、こちらの方法がより優れていると思います。

次回はシリアル値によらない方法について触れたいとおもいます。