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

社会福祉法人Sの会様の講座の続きを書きます。
フェイスブックで示しましたように、時刻・時間を表示するのにシリアル値を使用する場合には
間違うことがあります。
この問題は前回紹介しましたように、日経BP社発行の雑誌「PC21」の腕自慢大会（1999年2月号？）に掲載された問題で、非常に実務的でいい問題だっとので、講座での使用許諾のお願いをしたところ、日経BP様のご好意により使えるようになった問題です。



上の表は、第１回から第５回の腕自慢を集め表計算総集編として刊行された本に付属していたCDから優秀作作品として紹介されていた表をダウンロードして、その表のセル範囲D2：R9]のデータをデリートしたもので、セル[D2]に「=IF(AND($B2<=D$1,$C2>D$1),"■■■","")」と入力して、ほかのセルにコピーしたものです。ところがおかしいところが数多くあります。
新堂さん、中山さん、平沢さん、古川さん、松木さん、村井さんの最終列が間違っています。
たとえば、平沢さんは開始が17：00、終了が23：00ですが、23時のところにも「■■■」が表示されています。23時という意味は23：00～24：00なので、セル[R5]に「■■■」が表示されているのは、おかしいわけで、「■■■」はセル範囲[Q5]の10時のところまでしか表示されないはずです。
そのほかの方についても最終列の判断が間違っています。
開始と終了の入力値を数式バーでみると、「10：00：00」などと、時分秒すべて00になっています。同様にセル範囲[D1:R1]の時刻も「9：00：00」から「23：00：00」と時分秒すべて「00」になっています。にもかかわらず誤った解答はどうしてでしょう？

さらに、詳しく調べると、セルの書式設定ダイアログボックスの表示型式タブの数値の[小数点以下の桁数]を16（15ケタまでしか表示できない）にしてにセル[C5]とセル[R1]の保存値（セルに実際に格納されている値）はそれぞれ「0.9583333333333330」と「0.9583333333333300」となり、わずかですが違っています。わずかな差であっても、「=IF(AND($B5<=Q$1,$C5>Q$1),"■■■","")における、最初の条件式の「$B5<=Q$1」はもちろん、かんじんの「$C5>Q$1」までも満足してしまい、間違った表示をしてしまいます。

エクセルは小数計算を正確にできないということです。くわしくは日経BP社の達人芳坂和幸氏に学ぶエクセル演算講座などを参考にしてください。このサイト演算誤差について丁寧に説明されています。

たとえば「＝8.2-7.2」を計算してみてください。表示形式が標準なら「1」になりますが、数値にして少数の桁数を増やしてみてください。14桁までは「1.00000000000000」ですが、15桁にすると「0.999999999999999」となり、限りなく1に近いのですが、厳密には1よりわずかですが小さくなっています。
このような例はは多分いくらでもあると思います。

このように小数の計算で演算誤差が生じるのは、整数であれば、完全に2進数に変換できるのに、小数の場合わずかながら誤差が生じてしまうからです。
そこで、演算誤差を防ぐためには整数化して計算、微小値を使って補正、ROUND関数で丸めるなどの方法があります。
整数化するには「＝8.2-7.2」を計算の場合、答えは「0.999999999999999」になりますが、この整数化は「=ROUND(C1,0)」(セル[C1]に答えがはいっている）でOKです。
比較式などを使用する場合に、微小値を加えたり、引いたりすることにより誤差を防ぐことができます。このあたりのことも達人芳坂和幸氏に学ぶエクセル演算講義に詳しくかいてあります。

上の問題の場合にどうして誤った答えが導かれたかは、再現できないので、断定はできませんが多分、開始と終了の時間は手入力されてるはずですが、セル範囲[D1:R1]の時間を表示するセルはセル[D1]に「9:00」と入力、オートフィル機能によってセル{R1}まで埋めて、そのあと表示形式によって「9時」などと表示させたものだと思います。

この表の場合に正しく表示させるには、セル範囲[D1:Q1]の時刻欄を手入力すればOKです。
例えば、セル[R1]に「23:00」と入力しなおせばセル[R5]の「■■■」が消えて正しく表示されます。
オートフィル機能は誤差を増幅させてしまう（？）おそれがあるので使わない方が無難です。




Sの会の生徒のみなさんは時刻欄をほとんどが手入力していましたので、正しく表示させることができました。
その中でY君は素早く解いてしまったので、別の整数化する方法で解くように指示しました。最初とまどってましたが、ヒントを与えたところ見事に解いてみせました。HOUR関数で、たとえば「9：00」を整数「9」として扱う方法です。
この場合、「0.9583333333333330」も「0.9583333333333300」もHOUR関数の引数にすると、いずれも」「23」になり、誤差は生じません。

セル[D2]に「IF(AND(HOUR($B2)<=HOUR(D$1),HOUR($C2)>HOUR(D$1)),"■■■","")」と入力し、ほかのセルにコピーすればOKです。

もし開始と終了に分単位で入力する場合にはMINUTE関数で「分」を算出しこの値を60で割ったものにHOUR関数で求めた時間を加算すればOKです。

せっかくですから、該当する時間を塗りつぶす方法も知っておくと便利だと思います。

次回にします。










、

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と特定できるので、こちらの方法がより優れていると思います。

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