Excel 2003のエクセル関数

質問

皆様にとっては非常に簡単なことだと思いますが、何卒ご回答お願いいたします。

F2/F3/F4の計算式は=E2-D2/=E3-D3/=E3-D3にしたところ以下のように表示されました。
セルの表示形式はユーザー定義 h:mmとしています。

C D E F
1 　　2013/10 2013/11 前月比
2 A　　0:24　 0:28 0:04
3 B 0:39 0:06 #######
4 平均 0:47 0:31 #######

負の時間は######と表示されるみたいですが、0:06は6秒、0:39は39秒を意味していますので、
F3には-0:33、F4には-0:16と表示するようにすることは可能でしょうか？

つまらない質問してすみませんが、よろしくお願いします。

回答No.3

keithin · ベストアンサー

＞-0:32、-0:15と表示されました。
＞-0:33、-0:16と表示されるべきだと思ったのですが、これで正しいのでしょうか？

数式が正しくても、データが間違っていれば意図しない計算結果になったり、計算エラーになったりします。

試しに今「0:39」や「0:06」が現れているセルをＤｅｌｅｔｅして、もう一回手で時刻を入れなおしてみれば、意図通りの計算結果が現れます。これが示しているのは、これまで「0:39」や「0:06」になってると思ってたセルには、実際には見た目とはちょっとだけ「違う時刻」が記入なり計算されていたという事です。たとえば「0:06」の内容が実際には「0:06:01」だったりといった具合ですね。

もう一回元データを検証するところから、やってみて下さい。

お礼

ご返信ありがとうございます。
仰るとおり手動で入力すると正確に表示されました。
調べましたところお恥ずかしながら元データが間違っていることに気づきました。
正しくは、0:39は0:24、0:06は0:22、前月比 -0:32は-0:01と表示されています。
表示形式をh:mm:ssに変更すると以下のように表示されています。
0:24-->0:24:02
0:22-->0:22:53
-0:01-->-0:01:10
秒まで表示させてもまだ微妙にずれています。
私の希望としては分までの表示で前月比が正しい値にしたいです。
0:24:02はSortedタブのC15にありセル式は='Oct1-31集計'!$C$5となります。
0:22:53はSortedタブのD15にありセル式は='Nov1-30集計'!$C$5となります。
-0:01:10はSortedタブのE15にありセル式は=D15-C15となります。
'Oct1-31集計'!$C$5には
=AVERAGE(RawData_2013Oct!C5,RawData_2013Oct!C12,RawData_2013Oct!C19,RawData_2013Oct!C26,RawData_2013Oct!C33,RawData_2013Oct!C40,RawData_2013Oct!C47,RawData_2013Oct!C54,RawData_2013Oct!C61,RawData_2013Oct!C68,RawData_2013Oct!C75,RawData_2013Oct!C82,RawData_2013Oct!C89,RawData_2013Oct!C96,RawData_2013Oct!C103,RawData_2013Oct!C110,,RawData_2013Oct!C117,RawData_2013Oct!C124,,RawData_2013Oct!C131,,RawData_2013Oct!C138,RawData_2013Oct!C145,,RawData_2013Oct!C152)
'Nov1-30集計'!$C$5には
=AVERAGE(RawData_2013Nov!C5,RawData_2013Nov!C12,RawData_2013Nov!C19,RawData_2013Nov!C26,RawData_2013Nov!C33,RawData_2013Nov!C40,RawData_2013Nov!C47,RawData_2013Nov!C54,RawData_2013Nov!C61,RawData_2013Nov!C68,RawData_2013Nov!C75,RawData_2013Nov!C82,RawData_2013Nov!C89,RawData_2013Nov!C96,RawData_2013Nov!C103,RawData_2013Nov!C110,,RawData_2013Nov!C117,RawData_2013Nov!C124,,RawData_2013Nov!C131,,RawData_2013Nov!C138,RawData_2013Nov!C145,,RawData_2013Nov!C152)
と入力されています。
2013/10月と11月の生データそれぞれの平均をとったときに細かい端数が出てくるためこのような状況になっていると思っています。
上記AVERAGE関数を利用する際に秒を切り捨てにして前月比を正しく表示させる方法はございますでしょうか？
恐れ入りますが、引き続きアドバイスのほどよろしくお願いいたします。

回答No.6

MarcoRossiItaly

＞0:06、0:39は6分、39分を意味しております。

そのような時間のシリアル値になっているのであれば、No.2 の数式を利用する場合は、「"m:ss"」→「"[h]:mm"」と書き換えればいいだけですね。「"h:mm"」とした場合は、25 時間が 1 時間（= 25 - 24 [時間]）と表示されます。

＞……グラフを作成しないといけないため文字列は望んでおりません。

その条件があったのであれば、「時:分」という形式ではなくなってしまいますが、小数で負の時間を表示すればいいですね。

セルの書式を「数値」などの時間以外のものに設定するだけでも、「いわば時間のシリアル値に相当する負の小数」が表示されます。ですがその値よりも、引き算の結果として「マイナス 1 時間 30 分（に相当するある小数）」ではなく「-1.5 時間」といった値を始めから求めてしまえば、グラフ化は簡単ですね。

添付図では、次式により時間数を計算しています。「0.067 時間」などの計算結果が出ています。

F2　=(e2-d2)/"1:00"　　※ "1:00" を "0:01" と書き換えれば、分数が出ます。

繰り返しになりますが、「Excel の仕様により」、通常の使い方では、セルにもグラフの数値軸にも、負の時間を表示することはできません。

添付の数値軸（縦軸）には、設定を変更しなければ -0.6、-0.5、…、0、0.1 といった数列が表示されると思います。今回は、「軸の書式設定」にてユーザー定義書式「;;」と目盛間隔「0.25」を設定することにより数字を消した上で、「-0:15」などのテキストボックスを配置してみました。

グラフにテキストボックスを置くときは、必ずグラフ内を一度クリックしてから、挿入のボタンを押してください。Excel 2003 であれば、グラフをクリック後、数式バーに文字列を記入して Enter すればテキストボックスが挿入されます。こうすることで、テキストボックスをグラフの外にドラッグで出せない状態になります。一回シート上に置いたテキストボックスをグラフ上に重ねただけでは、ドラッグでいつでも分離できてしまいます。

＞ちなみにD2、C2の数値は直接数字を入力しているのではく、Nov1-30シートのあるセルを表示するようにしております。

直接セルに記入した数値でも数式で算出されているものでも、両者が同じ値になるならば、F 列での計算結果も同じになります。

＞教えていただいた関数式でも#######と表示されました。

(1)時間データは負の場合に「#####」になります。(2)時間は数値ですが、それ以外の数値などであっても、列幅が狭すぎると「#####」になります。(3)文字列の場合は狭くても通常「#####」になりませんが、文字数があまりに多くて、かつ「文字列」、「日付」などのセルの書式を設定している場合に、「#####」となります（参考 URL）。「標準」では、手元の Excel 2007 では文字数が多くても「#####」になりませんでした。

以上のどれかに当てはまっていないでしょうか？

＞気になりましたが、文字列表示の回避方法がわかれば教えていただけますか？

「文字列表示」とは、何のことですか？「#####」のことですか？

Microsoft は「#####」を、エラー値の一種であると表現しています。しかし実際には、うまく表示されていないことを無視すれば、「#####」と表示されているセルから参照して計算することも可能なので、「#N/A」などの他のエラー値とは性質が異なります。

回避する方法は、上の(1)～(3)の状態を取り除くことです。つまり、負を正にしたり、数値などを文字列化したり、列幅を広げたり、列幅を変えず「縮小して全体を表示する」を設定したり、文字列の文字数を減らすことです。

参考URL：

http://support.microsoft.com/kb/410234/ja

お礼

ご丁寧にご回答いただきありがとうございました。
参考になりました。

回答No.5

keithin

＞AVERAGE関数を利用する際に秒を切り捨てに

とりあえず
=FLOOR(AVERAGE(…),"0:1")
とでもしてみてください。

＞恐れ入りますが、引き続きアドバイスのほどよろしくお願いいたします。

お断りします。
当初の問題が片付いたのですから一回このご相談は「解決」で閉じていただき、新たな課題は次のご相談として詳しい説明を添えて別途のご相談として投稿してください。
これはこちらのような質問相談掲示板の、ごく一般的なマナーです。実際こうやってダラダラと「実はあれもこれもついでについでに」と終わらないご相談がホントに多くて、困っています。

お礼

了解しました。
ありがとうございます。

回答No.4

bunjii

添付画像のようにできます。
セルの値はシリアル値なので計算可能となっています。
「ツール」の「オプション」から「計算方法」タブにある「ブックオプション」の「1904年から計算する」にチェックを入れるとマイナス時間数を表示できます。

お礼

ご回答ありがとうございます。
keithinさんに返信させていただきましたように手動入力すると正しく計算されることは理解させていただいております。
No3への返信を見ていただければ幸いです。

回答No.2

MarcoRossiItaly

「ユーザー定義 h:mmとしています」という情報と「0:06は6秒、0:39は39秒を意味しています」というのが、両立していない気がするのですが……これらは同じセルの話ですか？

おっしゃるとおり、基本、負の時間は表示できないことになっています。セルの表示形式による表示は諦めて、「-」を付加した文字列を生成するということなら、簡単にできます。「分:秒」の場合は、次式。

F2　=if(e2-d2<0,"-",)&text(abs(e2-d2),"m:ss")

文字列なので、標準では左揃えになります。右揃えにしたければ、右揃えのボタンを押してください。

お礼

すみません、間違って記載していました。
0:06、0:39は6分、39分を意味しております。

このあと、別シートでこの数値のグラフを作成しないといけないため文字列は望んでおりません。

ご回答ありがとうございました。

補足

教えていただいた関数式を試してみましたが、なぜか私のシートではうまく表示されませんでした。
ちなみにD2、C2の数値は直接数字を入力しているのではく、Nov1-30シートのあるセルを表示するように
しております。またNov1-30シートのあるセルはRawDataNovシートの必要箇所を計算して表示されるようにしております。
教えていただいた関数式でも#######と表示されました。表示形式はh:mmのままですが、変更する必要がありますか？念のため標準にしてみましたが変わりませんでした。
気になりましたが、文字列表示の回避方法がわかれば教えていただけますか？
アドバイスいただきありがとうございます。

回答No.1

keithin

＞F3には-0:33、F4には-0:16と表示するようにすることは可能でしょうか？

可能かと言われれば、次の手順により可能です。

手順：
ツールメニューのオプションの計算方法で「1904年から計算する」のチェックを入れる

ただし。
「時：分」で記入されているセルは問題ありませんが、「年/月/日」若しくは「年/月/日 時:分:秒」で『生データを既に記入済みのセル』の日付が４年と１日勝手に書き換わるため、データを「入れなおす」必要があります。正しい日付に入れなおしたセル、また今後年月日を記入したセルは問題ありません。

また、この設定を施した「ブック」と、設定を施していないブック間で年月日を生データで記入したセルを
・コピーしたり
・ブック間で数式で参照したり計算する
と、同様に日付の間違いが起こるので扱いに注意が必要です。

お礼

またまたご回答いただきありがとうございます。
注意点も教えていただきありがとうございます。
「1904年から計算する」にチェックを入れると、-0:32、-0:15と表示されました。
-0:33、-0:16と表示されるべきだと思ったのですが、これで正しいのでしょうか？
「A」の2013/10の数字が0:39、2013/11の数字が0:06、前月比が-0:33なのか-0:32なのか？
「B」の2013/10の数字が0:47、2013/11の数字が0:31、前月比が-0:16なのか-0:15なのか？
このあと、別シートでグラフを作りますので、数字表示でないといけません。
知識が足りず簡単なことですみません。引き続きよろしくお願いいたします。

補足

質問の中の0:06、0:39は6秒、39秒ではなく、6分、39分となります。
ごめんなさい。。。