初めてメールさせて頂きます。エクセルでの、時間の差分について教えて下さ

質問

初めてメールさせて頂きます。エクセルでの、時間の差分について教えて下さい。宜しくお願いします。
マシーンの稼働率を算出する為に、LogData内のStart/Endの時間から差分を求め、その合計を、業務営業で割り、稼働率を求めたい所ですが、時間表示が、以下のように12Ｈ(AM/PM)になっています。
2010/10/18 Start 12:10 AM: 2010/10/18 End 1:23 PM
2010/10/18 Start 12:45 PM: 2010/10/19 End 3:01 AM
このような行が、Monthlyベースで、多い時は900行前後有ります。今回、稼働率の精度を上げる為に、初めてLogDataをベースとした解析に取り組んでいますが、具体的な作業としては、"Start-AM","Start-PM","End-AM","End-PM"というワークシートに、フィルターで指定した各データを、それぞれのワークシートにコピーし、個々のワークシートにて、12Ｈから24Ｈに表示変換し、最終的に、それらを1つのファイルにマージさせています。また、マージさせたファイルから差分を求める際、0時をまたぐ場合のデータに関しては、別のワークシートにて、End時間にStart時間(0時までの残時間）を加算し、それを再度、マージしたデータに反映させて、最終的なSUMを求め、稼働率を算出しました。　以上の説明で、ご理解頂けるか不安ですが、解析工数を削減する為のアドバイスを頂きたく、深くお願い申し上げます。　不明な点が有りましたら、お知らせの程、宜しく願い致します。

回答No.3

kagakusuki

回答番号:ANo.2です。
　もし、毎回のStart時刻やEnd時刻、及び、毎回の稼働時間の一覧表が必要ない場合には、条件さえ揃えば、Sheet2が無くとも総稼働時間や稼働率を求める事も可能です。

　LogDataが並んでいるSheet1のB列の2行目以下には、途中に空欄等のLogData以外のデータが存在せず、加えて、B2よりも上のセルには空欄が存在しない場合には、
Sheet2を使用しなくても、次の数式で総稼働時間を求める事が出来ます。

=SUMPRODUCT(VALUE(SUBSTITUTE(MID(OFFSET(Sheet1!$B$2,,,COUNTIF(Sheet1!$B:$B,"><")-1),FIND("M:",OFFSET(Sheet1!$B$2,,,COUNTIF(Sheet1!$B:$B,"><")-1))+3,99),"End ",))-VALUE(SUBSTITUTE(LEFT(OFFSET(Sheet1!$B$2,,,COUNTIF(Sheet1!$B:$B,"><")-1),FIND("M:",OFFSET(Sheet1!$B$2,,,COUNTIF(Sheet1!$B:$B,"><")-1))),"Start ",)))

　更に加えて、LogDataは必ず、最古のデータが最も上に表示され、最新のデータが最も下に表示される場合には、Sheet2を使用しなくても、次の数式で稼働率を求める事が出来ます。

=SUMPRODUCT(VALUE(SUBSTITUTE(MID(OFFSET(Sheet1!$B$2,,,COUNTIF(Sheet1!$B:$B,"><")-1),FIND("M:",OFFSET(Sheet1!$B$2,,,COUNTIF(Sheet1!$B:$B,"><")-1))+3,99),"End ",))-VALUE(SUBSTITUTE(LEFT(OFFSET(Sheet1!$B$2,,,COUNTIF(Sheet1!$B:$B,"><")-1),FIND("M:",OFFSET(Sheet1!$B$2,,,COUNTIF(Sheet1!$B:$B,"><")-1))),"Start ",)))/(VALUE(SUBSTITUTE(MID(INDEX(Sheet1!$B:$B,COUNTIF(Sheet1!$B:$B,"><")),FIND("M:",INDEX(Sheet1!$B:$B,COUNTIF(Sheet1!$B:$B,"><")))+3,99),"End ",))-VALUE(SUBSTITUTE(LEFT(Sheet1!$B$2,FIND("M:",Sheet1!$B$2)),"Start ",)))

補足

こんなに早くご対応頂けるとは思いもせず、深く感謝申し上げます。ここで、追加補足させて頂きます。今回、9月分に関しての稼働率を、時間がかなり掛かりましたが算出しました。パラメータを述べます。マシーン台数:4台,明細数(注文依頼書):400-900件/マシーン(明細間には、システムセットアップの為のInterval(段取り)が発生),Logdata：htmlファイル。 作業手順を以下に述べます。
1．html -> txtに変換　フリー変換ソフトを使用（900件の場合でも、全てのデータをドラック＆ドロップで変換可能。非常に助かりました）
2．変換されたtxtファイルを、100ファイル毎(1-100,101-200のような）のディレクトリに仕分けし、個々のディレクトリ内の100ファイルをマージする(フリー結合ソフトの制約の為、100ファイルまでしか出来ない為）
3．マージされたtxtファイルを、xlsファイルに変換。その際、区切り文字として、タブ/スペースを選択
4．個々のxlsファイルを、1つのファイルにコピー/ペーストして、マージする
5．マージされたファイルは、以下のように、個々のセルに表示される
Start　8/31/2010　08:21:45　PM　End　9/01/2010　01:27:02　AM
Start　9/01/2010 　01:33:08　AM　End　9/01/2010　01:34:06 AM
Start　9/01/2010　01:37:13　AM　End　9/01/2010　01:38:39 AM
Start　9/01/2010　01:44:16　AM　End　9/01/2010　03:27:53 PM
尚、今回の稼働率算出から、明細間のIntervalも算出し、Yield低下の原因として、Intervalにかなりのバラツキがある事が判明しました。参考までに、これまでは、オペレータからの停止時間の自己申告によって、運用状況を把握していたので、Intervalのバラツキは、全く見えていませんでした。
作業手順については先日のメールの通りですが、今回アドバイス頂いた内容をじっくり読ませて頂き、改めてメールさせて頂きますので、ご理解の程、宜しくお願い申し上げます。

回答No.2

kagakusuki

確認したいのですが、LogDataは、Startの日付、Startの時刻、Endの日付、Endの時刻が、各々別のセルに表示されているのではなく、

2010/10/18 Start 12:10 AM: 2010/10/18 End 1:23 PM

というパターンの文字列が、１つのセルに表示されていると考えて宜しいのでしょうか？

　もし、そうだとした場合には、以下の様にされると良いと思います。

　今仮に、Sheet1のB2以下にLogDataが並んでいて、Sheet2に24時間表示に変換したデータを表示するものとします。

　まず、Sheet2のA2セルに次の数式を入力して下さい。

=IF(Sheet1!$B2="","",Sheet1!$B2)

　次に、Sheet2のB2セル（Start時刻を表示するセル）に次の数式を入力して下さい。

=VALUE(SUBSTITUTE(LEFT($A2,FIND("M:",$A2)),"Start ",))

　次に、Sheet2のC2セル（End時刻を表示するセル）に次の数式を入力して下さい。

=VALUE(SUBSTITUTE(MID($A2,FIND("M:",$A2)+3,99),"End ",))

　次に、Sheet2のD2セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER($C2),ISNUMBER($B2)),$C2-$B2,"")

　次に、Sheet2のB2セルとC2セルを選択してから、次の操作を行って下さい。

選択範囲を示す黒い太枠の内側にカーソルを合わせて、マウスを右クリック
　　↓
現れた選択肢の中から、[セルの書式設定]を選択してクリック
　　↓
現れた[セルの書式設定]ウィンドウの[表示形式]タグをクリック
　　↓
[分類]欄の中から、[ユーザー定義]を選択してクリック
　　↓
[種類]欄に

yyyy/m/dd h:mm

と入力する
　　↓
[セルの書式設定]ウィンドウの[OK]ボタンをクリックする

　次に、Sheet2のD2セルを選択してから、同様の操作を行い、[セルの書式設定]ウィンドウの[種類]欄に

[h]:mm

と入力後、[セルの書式設定]ウィンドウの[OK]ボタンをクリック下さい。
　そして、Sheet2のA2～D2の範囲をコピーして、同じ列の3行目から1000行目辺りまで貼り付けて下さい。
　次に、総稼働時間を表示するセルに、Sheet2のD2セルと同じ書式を設定し、次の数式を入力して下さい。

=SUM(Sheet2!$D:$D)

　最後に、稼働率を表示するセルに次の数式を入力して下さい。

=SUM(Sheet2!$D:$D)/(MAX(Sheet2!$C:$C)-MIN(Sheet2!$B:$B))

回答No.1

shinkami

自分の思い違いかもしれませんが、日付と時刻は別々の系列とご理解されているのではないでしょうか
ウィンドウズの世界では日付も時間も連続した正の実数の系列として取り扱われています。
従って　差分 ＝ END - START　でよろしいのでは

因みにセルA1に　=now()と入力してどのように表示されるか確認してください。

尚　EXCELのカテゴリーは
「teacupちょっとききタイ！ > デジタルライフ > ソフトウェア > Office系ソフト」　です。

参考URL：

http://www.eurus.dti.ne.jp/~yoneyama/Excel/hiduke.htm#hiduke-text