社会福祉法人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です。
せっかくですから、該当する時間を塗りつぶす方法も知っておくと便利だと思います。
次回にします。
、
フェイスブックで示しましたように、時刻・時間を表示するのにシリアル値を使用する場合には
間違うことがあります。
この問題は前回紹介しましたように、日経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です。
せっかくですから、該当する時間を塗りつぶす方法も知っておくと便利だと思います。
次回にします。
、
エクセル講座、凄いですね。最高です。
私も教わってみたいです。
鉄井さんのスポーツ評論も好きです。
柔道、サッカーに続き他のスポーツの評論を楽しみにしています。
寒くなりました。風邪にはご注意ください。