- ベストアンサー
夏・冬時間による日本時間の表示
- Excel2010で、夏時間と冬時間による日本時間を表示する方法について教えてください。
- 「損益計算」シートの開始日時と終了日時を日本時間で表示したいのですが、計算式が分かりません。夏時間と冬時間を考慮して表示する方法を教えてください。
- 具体的な手順として、夏時間のGMTと開始日時、終了日時を「設定」シートに入力し、「損益計算」シートの開始日時と終了日時を日本時間で表示するための計算式を教えてください。
- miya_HN
- お礼率100% (1683/1683)
- Excel（エクセル）
- 回答数37
- ありがとう数37
- みんなの回答 （37）
- 専門家の回答
質問者が選んだベストアンサー
その他の回答 (36)
- kagakusuki
- ベストアンサー率51% (2610/5101)
又、日本の日時に変換した日時を、「元データ」シートと「設定」シートのデータから直接求めるのではなく、「設定」シートのデータと、「損益計算」シートのA列～H列及び同シートのAC列～AD列のデータから求める関数は次の様なものとなります。 取引開始日時を求める関数 =IF(COUNT($A3:$D3,$AC3:$AD3,設定!$D$3,設定!$E$3)=8,IF(OR(ISERROR(1/(ROUND($A3&"/"&$B3&"/"&$C3&" "&TEXT($D3,"h:m:s"),8)=ROUND(DATE($A3,$B3,$C3)+$D3,8))/DAY($AC3)/DAY($AD3)),ABS(設定!$D$3)>=24,ABS(設定!$E$3)>=24),"変換不能",TEXT(DATE($A3,$B3,$C3)+$D3+(9-IF(AND(DATE($A3,$B3,$C3)>$AC3,DATE($A3,$B3,$C3)<$AD3),設定!$D$3,設定!$E$3))/24+0.000001,"yyyy/m/d h:m:s")+0),"") 取引終了日時を求める関数 =IF(COUNT($E3:$H3,$AC3:$AD3,設定!$D$3,設定!$E$3)=8,IF(OR(ISERROR(1/(ROUND($E3&"/"&$F3&"/"&$G3&" "&TEXT($H3,"h:m:s"),8)=ROUND(DATE($E3,$F3,$G3)+$H3,8))/DAY($AC3)/DAY($AD3)),ABS(設定!$D$3)>=24,ABS(設定!$E$3)>=24),"変換不能",TEXT(DATE($E3,$F3,$G3)+$H3+(9-IF(AND(DATE($E3,$F3,$G3)>$AC3,DATE($E3,$F3,$G3)<$AD3),設定!$D$3,設定!$E$3))/24+0.000001,"yyyy/m/d h:m:s")+0),"") 後、余談ですが、ダウンロードしたExcelbookの「設定」シートのAC列～AD列に入力されていた夏時間の開始日と終了日を求める関数は、あのままでは指定されている日が日曜日の場合にしか正しい日付を求める事が出来ない様ですが、取引場所によっては「日曜以外曜日が休日となっている等の理由から、日曜以外の日に夏時間と冬時間の切り替えが行われる」という恐れは無いのでしょうか? 念の為、日曜以外の曜日でも切り替え日を求める事が出来る関数を御伝え致しておきます。 夏時間の開始日を求める関数 =IF(OR(設定!$F$3="",$A3=""),"",IF(ISERROR(1/(TEXT(($A3&"年"&LEFT(設定!$F$3,FIND("月",設定!$F$3)))+MOD(FIND(MID(設定!$F$3,FIND("曜日",設定!$F$3)-1,1),"日月火水木金土")-WEEKDAY($A3&"年"&LEFT(設定!$F$3,FIND("月",設定!$F$3))),7)+(MID(設定!$F$3,FIND("第",設定!$F$3)+1,1)-1)*7,"m月第"""&MID(設定!$F$3,FIND("第",設定!$F$3)+1,1)+0&"""aaaa")=LEFT(設定!$F$3,FIND("曜日",設定!$F$3)+1))),"変換不能",($A3&"年"&LEFT(設定!$F$3,FIND("月",設定!$F$3)))+MOD(FIND(MID(設定!$F$3,FIND("曜日",設定!$F$3)-1,1),"日月火水木金土")-WEEKDAY($A3&"年"&LEFT(設定!$F$3,FIND("月",設定!$F$3))),7)+(MID(設定!$F$3,FIND("第",設定!$F$3)+1,1)-1)*7)) 夏時間の終了日を求める関数 =IF(OR(設定!$G$3="",$A3=""),"",IF(ISERROR(1/(TEXT(($A3&"年"&LEFT(設定!$G$3,FIND("月",設定!$G$3)))+MOD(FIND(MID(設定!$G$3,FIND("曜日",設定!$G$3)-1,1),"日月火水木金土")-WEEKDAY($A3&"年"&LEFT(設定!$G$3,FIND("月",設定!$G$3))),7)+(MID(設定!$G$3,FIND("第",設定!$G$3)+1,1)-1)*7,"m月第"""&MID(設定!$G$3,FIND("第",設定!$G$3)+1,1)+0&"""aaaa")=LEFT(設定!$G$3,FIND("曜日",設定!$G$3)+1))),"変換不能",($A3&"年"&LEFT(設定!$G$3,FIND("月",設定!$G$3)))+MOD(FIND(MID(設定!$G$3,FIND("曜日",設定!$G$3)-1,1),"日月火水木金土")-WEEKDAY($A3&"年"&LEFT(設定!$G$3,FIND("月",設定!$G$3))),7)+(MID(設定!$G$3,FIND("第",設定!$G$3)+1,1)-1)*7)) 後、気になったのは、「損益計算」シートの各セルに入力されている関数がいずれも =IF(元データ!A3="","", で始まっているという点です。 これでは元データのA列のセルに何らかの値が入力されてさえいれば、他の列のセルに何も入力されていない場合や、誤った値(計算処理の元データとして使用する事が出来ない値)が入力されていた場合などにおいても計算が行われ、エラーが出たり、誤った計算結果が表示されたり、などといった問題が発生する恐れがあります。 ですから、IF関数を使って表示するのかしないのかを切り替える場合には、「元データ!A3が空欄か否か」等という点で判定するのではなく、「関数内で参照している全てのセル」が「空欄か否か」という事と、それらのセルが「空欄ではない場合には、『計算処理の元データとして使用する事が出来る値』であるか否か」という事で判定するべきだと思います。
お礼
回答ありがとうございます。 >又、日本の日時に変換した日時を、「元データ」シートと「設定」シートのデータから直接求めるのではなく、「設定」シートのデータと、「損益計算」シートのA列～H列及び同シートのAC列～AD列のデータから求める関数は次の様なものとなります。 この計算式の方が短くて済みますね。 >後、余談ですが、ダウンロードしたExcelbookの「設定」シートのAC列～AD列に入力されていた夏時間の開始日と終了日を求める関数は、あのままでは指定されている日が日曜日の場合にしか正しい日付を求める事が出来ない様ですが、取引場所によっては「日曜以外曜日が休日となっている等の理由から、日曜以外の日に夏時間と冬時間の切り替えが行われる」という恐れは無いのでしょうか? 念の為、日曜以外の曜日でも切り替え日を求める事が出来る関数を御伝え致しておきます。 「日曜以外の日に夏時間と冬時間の切り替えが行われる」という可能性に関しましては、全世界の業者を把握しているわけではありませんので分かりません。 私の質問に対しそこまでお考えになられ、本当に感謝しています。 >後、気になったのは、「損益計算」シートの各セルに入力されている関数がいずれも >=IF(元データ!A3="","", >で始まっているという点です。 これでは元データのA列のセルに何らかの値が入力されてさえいれば、他の列のセルに何も入力されていない場合や、誤った値(計算処理の元データとして使用する事が出来ない値)が入力されていた場合などにおいても計算が行われ、エラーが出たり、誤った計算結果が表示されたり、などといった問題が発生する恐れがあります。 ですから、IF関数を使って表示するのかしないのかを切り替える場合には、「元データ!A3が空欄か否か」等という点で判定するのではなく、「関数内で参照している全てのセル」が「空欄か否か」という事と、それらのセルが「空欄ではない場合には、『計算処理の元データとして使用する事が出来る値』であるか否か」という事で判定するべきだと思います。 なるほど、そのようなご配慮につきましてもアドバイスいただきありがとうございます。 このファイルを作ろうとした当初から「元データ」シートのA行には必ず入力するという前提で作成してまいりました。 「誤った値(計算処理の元データとして使用する事が出来ない値)が入力されていた場合」というのは基本的にはあり得ませんが、回答者様のご意見を聞き、その方が間違いが少ないのではないかと思います。 あと、下記のようなURLを発見いたしました。 http://www.fx-soken.co.jp/kawase/k_j_010.html URLの下部の方の「各国の夏時間」という項目なんですが、例えば、 オーストラリア：10月最終日曜日から3月最終日曜日まで という記述です。 回答No.10のお礼コメントより、 「月の半角数値」（1桁、または2桁）、「月」（文字列）、「第」（文字列）、「週の何週目か半角数値」、「曜日」（文字列）、「時刻の半角数値」（1桁、または2桁）、「:」（半角コロン）、「分の半角数値」（2桁）の順で入力します。 と入力規則をお伝えしましたが、「最終日曜日」の「最終」という部分を付け加えなければならなくなりました。 どのようにしたらよいでしょうか？ また、回答No.8のお礼コメントの「■本質問と別の問題」より、 「MT損益計算 - テスト.xlsx」の「元データ」シートに「2015年6月分.htm」のデータを入力した場合と、「MT損益計算用データ.xlsx」のデータを入力した場合で、「2015年6月分.htm」のデータを入力した場合では、「月別結果」シートと「ストラテジー評価」シートの計算結果に何も問題がないのにも関わらず、「MT損益計算用データ.xlsx」のデータを入力した場合、エラーというか正しい結果が表示されません。 「2015年6月分.htm」のデータ数が極端に少ない、厳密に言えば1～9月分では問題ないが、10～12月では問題が生じてくると思われます。 これは、何故なんでしょうか？ 本質問とは異なるため、本来は質問の場ではないのですが、これに関してご理解いただけているのはkagakusuki様と、bunjii様だけです。 この問題に対しては、本質問が解決した後、新規に質問した方がいいのでしょうか？ それともこのまま続行した方がいいのでしょうか？ 次々と変更点や、問題が見つかり、長くなってしまったことをお詫び申し上げます。 大変申し訳ありませんが、回答よろしくお願いします。
- kagakusuki
- ベストアンサー率51% (2610/5101)
>「元データ」シート、「設定」シートのデータを理解していただくためには下記URLよりダウンロードしてもらえるとよく分かると思います。 このサイトでは、投稿者自身で内容の変更が可能なWebスペース上にアップロードされたファイルのURLを記載しての投稿は禁止されておりますので、情報を提示する際にその様な方法を使う事はなるべく避けた方が宜しいのではないかと思います。 【参考URL】 OKWaveコミュニティ > はじめてガイド > 禁止事項ガイドライン 後それから、ダウンロードしたExcelbookの「設定」シートには、夏時間の設定が1行しか入力されておりませんでしたが、「元データ」シートのデータは全て同じ地域内で行われた取引データという事なのでしょうか? 私はてっきり、「元データ」シートのデータは1行ごとに取引場所が異なっていて、それ故、「設定」シートには各行ごとの取引場所のサーバー時間に対応した夏時間の設定情報が、1行ごとに入力されているものと思っていました。 >そうしますと、右にずらし、 >「設定」シートのD3（サーバー時間の夏時間のGMT） >「設定」シートのE3（サーバー時間の冬時間のGMT） >「設定」シートのF3（夏時間の開始日時） >「設定」シートのG3（夏時間の終了日時） >とするのがよいかと思います。 そういう事でしたら日本の日時に変換した取引開始日時を、「元データ」シートと「設定」シートのデータから直接求める関数は次の様なものとなります。 =IF(元データ!$B3="","",IF(ISERROR(1/(TEXT(SUBSTITUTE(元データ!$B3,".","/")+0,LEFT("yyyy.mm.dd hh:mm:ss",MAX(LEN(元データ!$B3),16)))=元データ!$B3)/(TEXT((LEFT(元データ!$B3,4)&"年"&LEFT(設定!$F$3,FIND("月",設定!$F$3)))+(MID(設定!$F$3,FIND("第",設定!$F$3)+1,1)-1)*7+MOD(FIND(MID(設定!$F$3,FIND("曜日",設定!$F$3)-1,1),"日月火水木金土")-WEEKDAY(LEFT(元データ!$B3,4)&"年"&LEFT(設定!$F$3,FIND("月",設定!$F$3))),7)+REPLACE(設定!$F$3,1,FIND("曜日",設定!$F$3)+1,),"m月第"""&MID(設定!$F$3,FIND("第",設定!$F$3)+1,1)+0&"""aaaah:mm")=設定!$F$3)/(TEXT((LEFT(元データ!$B3,4)&"年"&LEFT(設定!$G$3,FIND("月",設定!$G$3)))+(MID(設定!$G$3,FIND("第",設定!$G$3)+1,1)-1)*7+MOD(FIND(MID(設定!$G$3,FIND("曜日",設定!$G$3)-1,1),"日月火水木金土")-WEEKDAY(LEFT(元データ!$B3,4)&"年"&LEFT(設定!$G$3,FIND("月",設定!$G$3))),7)+REPLACE(設定!$G$3,1,FIND("曜日",設定!$G$3)+1,),"m月第"""&MID(設定!$G$3,FIND("第",設定!$G$3)+1,1)+0&"""aaaah:mm")=設定!$G$3)/(ABS(設定!$D$3)<24)/(ABS(設定!$E$3)<24)),"変換不能",TEXT(SUBSTITUTE(元データ!$B3,".","/")+(9-IF(AND(SUBSTITUTE(元データ!$B3,".","/")+0>(LEFT(元データ!$B3,4)&"年"&LEFT(設定!$F$3,FIND("月",設定!$F$3)))+(MID(設定!$F$3,FIND("第",設定!$F$3)+1,1)-1)*7+MOD(FIND(MID(設定!$F$3,FIND("曜日",設定!$F$3)-1,1),"日月火水木金土")-WEEKDAY(LEFT(元データ!$B3,4)&"年"&LEFT(設定!$F$3,FIND("月",設定!$F$3))),7)+REPLACE(設定!$F$3,1,FIND("曜日",設定!$F$3)+1,),SUBSTITUTE(元データ!$B3,".","/")+0<(LEFT(元データ!$B3,4)&"年"&LEFT(設定!$G$3,FIND("月",設定!$G$3)))+(MID(設定!$G$3,FIND("第",設定!$G$3)+1,1)-1)*7+MOD(FIND(MID(設定!$G$3,FIND("曜日",設定!$G$3)-1,1),"日月火水木金土")-WEEKDAY(LEFT(元データ!$B3,4)&"年"&LEFT(設定!$G$3,FIND("月",設定!$G$3))),7)+REPLACE(設定!$G$3,1,FIND("曜日",設定!$G$3)+1,)),設定!$D$3,設定!$E$3))/24+0.000001,"yyyy/m/d h:m:s")+0)) 同様に、日本の日時に変換した取引終了日時を、「元データ」シートと「設定」シートのデータから直接求める関数は次の様なものとなります。 =IF(元データ!$I3="","",IF(ISERROR(1/(TEXT(SUBSTITUTE(元データ!$I3,".","/")+0,LEFT("yyyy.mm.dd hh:mm:ss",MAX(LEN(元データ!$I3),16)))=元データ!$I3)/(TEXT((LEFT(元データ!$I3,4)&"年"&LEFT(設定!$F$3,FIND("月",設定!$F$3)))+(MID(設定!$F$3,FIND("第",設定!$F$3)+1,1)-1)*7+MOD(FIND(MID(設定!$F$3,FIND("曜日",設定!$F$3)-1,1),"日月火水木金土")-WEEKDAY(LEFT(元データ!$I3,4)&"年"&LEFT(設定!$F$3,FIND("月",設定!$F$3))),7)+REPLACE(設定!$F$3,1,FIND("曜日",設定!$F$3)+1,),"m月第"""&MID(設定!$F$3,FIND("第",設定!$F$3)+1,1)+0&"""aaaah:mm")=設定!$F$3)/(TEXT((LEFT(元データ!$I3,4)&"年"&LEFT(設定!$G$3,FIND("月",設定!$G$3)))+(MID(設定!$G$3,FIND("第",設定!$G$3)+1,1)-1)*7+MOD(FIND(MID(設定!$G$3,FIND("曜日",設定!$G$3)-1,1),"日月火水木金土")-WEEKDAY(LEFT(元データ!$I3,4)&"年"&LEFT(設定!$G$3,FIND("月",設定!$G$3))),7)+REPLACE(設定!$G$3,1,FIND("曜日",設定!$G$3)+1,),"m月第"""&MID(設定!$G$3,FIND("第",設定!$G$3)+1,1)+0&"""aaaah:mm")=設定!$G$3)/(ABS(設定!$D$3)<24)/(ABS(設定!$E$3)<24)),"変換不能",TEXT(SUBSTITUTE(元データ!$I3,".","/")+(9-IF(AND(SUBSTITUTE(元データ!$I3,".","/")+0>(LEFT(元データ!$I3,4)&"年"&LEFT(設定!$F$3,FIND("月",設定!$F$3)))+(MID(設定!$F$3,FIND("第",設定!$F$3)+1,1)-1)*7+MOD(FIND(MID(設定!$F$3,FIND("曜日",設定!$F$3)-1,1),"日月火水木金土")-WEEKDAY(LEFT(元データ!$I3,4)&"年"&LEFT(設定!$F$3,FIND("月",設定!$F$3))),7)+REPLACE(設定!$F$3,1,FIND("曜日",設定!$F$3)+1,),SUBSTITUTE(元データ!$I3,".","/")+0<(LEFT(元データ!$I3,4)&"年"&LEFT(設定!$G$3,FIND("月",設定!$G$3)))+(MID(設定!$G$3,FIND("第",設定!$G$3)+1,1)-1)*7+MOD(FIND(MID(設定!$G$3,FIND("曜日",設定!$G$3)-1,1),"日月火水木金土")-WEEKDAY(LEFT(元データ!$I3,4)&"年"&LEFT(設定!$G$3,FIND("月",設定!$G$3))),7)+REPLACE(設定!$G$3,1,FIND("曜日",設定!$G$3)+1,)),設定!$D$3,設定!$E$3))/24+0.000001,"yyyy/m/d h:m:s")+0))
お礼
回答ありがとうございます。 >このサイトでは、投稿者自身で内容の変更が可能なWebスペース上にアップロードされたファイルのURLを記載しての投稿は禁止されておりますので、情報を提示する際にその様な方法を使う事はなるべく避けた方が宜しいのではないかと思います。 >【参考URL】 OKWaveコミュニティ > はじめてガイド > 禁止事項ガイドライン 「■会員自身の運営するサイト・ブログ等を開示する投稿 当該スペースを利用しての私的なメッセージの発信が行われる可能性や、思わぬ個人情報やプライバシーの漏洩に繋がるおそれもございますため、会員自身が運営するホームページ・ブログやメールマガジン等を開示する投稿（容易に自身のサイトの検索を可能にするキーワード等の記載により間接的に誘導が可能な場合も含む）は、削除・編集の対象とさせていただきます。 また、他者運営のサイトでも、アップローダー・ブリーフケースのように、投稿者自身で内容の変更が可能なWebスペース上にアップロードされた画像などのファイルのURLを記載しての投稿も、同様に判断いたします。」 この部分のことでしょうか。 教えて頂き、ありがとうございます。 個人情報の漏えいは困りますね。 ただ、このサイトの質問形式に不満がありまして画像は添付できるのですがあまりに小さすぎて入りきらない場合もあります。 文章では伝わらないことが、画像や動画などを使用することで回答者の皆様方に伝わりやすいのではないかと思います。 特に説明下手な自分としては。 と、自分の都合ばかり言いましたがこのサイトで画像サイズを制限しているのには何らかの理由があってのことでしょう。 今後、自粛するつもりではありますが、このサイトの改善点でもあるように感じました。 >後それから、ダウンロードしたExcelbookの「設定」シートには、夏時間の設定が1行しか入力されておりませんでしたが、「元データ」シートのデータは全て同じ地域内で行われた取引データという事なのでしょうか? 私はてっきり、「元データ」シートのデータは1行ごとに取引場所が異なっていて、それ故、「設定」シートには各行ごとの取引場所のサーバー時間に対応した夏時間の設定情報が、1行ごとに入力されているものと思っていました。 大元のデータは全て同一のサーバーからのデータです。 異なったサーバーのデータを入力しますと業者によってGMTとの時差が異なったり、同一の時刻であっても価格が異なります。 それとも回答者様に何か良いアイデアがあるのでしょうか？ 非常に長文の計算式を記載していただき、ありがとうございます。 大変感謝しております。
- bunjii
- ベストアンサー率43% (3589/8249)
>「2015年6月分.htm」、「MT損益計算用データ.xlsx」のファイルは、下記URLよりダウンロードできます。 ファイルがダウンロードできましたので、「MT損益計算 - テスト.xlsx」の内容を確認しました。 「損益計算」シートのZ3の数式は回答No.7で提示した数式と同じでした。 回答No.7で正しい時刻にならなかったのはGMTとの時差を正しく設定していなかっただけです。 MT4サーバーの時刻は標準TimeがGMT+3時間でSummer TimeはGMT+2時間となっています。 また、AC3とAD3も回答No.7で提示した数式と同じです。 従って、あなたの思い違いです。 私の回答でこの回答を有効とし、回答No.8以降のものは不正な情報からの誘導なので無効になります。 正しい情報をて提示しないと正しい結果を得られません。
お礼
回答ありがとうございます。 >MT4サーバーの時刻は標準TimeがGMT+3時間でSummer TimeはGMT+2時間となっています。 また、AC3とAD3も回答No.7で提示した数式と同じです。 従って、あなたの思い違いです。 私の回答でこの回答を有効とし、回答No.8以降のものは不正な情報からの誘導なので無効になります。 >正しい情報をて提示しないと正しい結果を得られません。 そうですね、自分が夏時間・冬時間の記載の意味をよく理解していませんでした。 回答者様から教えられ理解できました。 大変感謝しています。 ありがとうございました。
- bunjii
- ベストアンサー率43% (3589/8249)
>大変申し訳ありませんが業者によって異なる可能性がある以上、それも考慮していただけないでしょうか？ 具体的な例（模擬データ）を提示して頂ければ検討できますが、抽象的な質問には対応困難です。 >恐らく可能性は低いと思われますがもし、夏時間と冬時間が逆転するような業者があった場合、また質問しなければなりません。 可能性が低い仮説に対して無駄な時間を費やすつもりはありませんので、必要が生じた時点で再質問してください。 尚、当方で提示した処理について論理的な処理過程が理解できれば応用力で解決できると思います。（丸投げは好ましくありません） >「設定」シートのD3（サーバー時間の夏時間のGMT） >「設定」シートのE3（サーバー時間の冬時間のGMT） >「設定」シートのF3（夏時間の開始日時） >「設定」シートのG3（夏時間の終了日時） として考えて頂けないでしょうか？ その考え方は数式を複雑化するのでお勧めできません。 「設定」シートのD3はMT4の基準時刻（GMT+2）として固定し、Summer Timeの開始日時と終了日時でSummer Timeの期間内はMT4の時刻がGMT+3となるような仕組みが良いでしょう。 例えば、南半球の事業者の間合いはSummer Timeの開始日時を「9月第2日曜日2:00」、Summer Time終了日時を「翌年4月第1日曜日2:00」のようにすることで、すべての地域についてカバーできる処理方法が確立できるでしょう。 因みに、世界のSummer Time採用地域は検索すれば（"ググれば"とは言いたくない）情報を得られます。 尚、（サーバー時間の夏時間のGMT）と言う表現は誤解の元になりますので、（サーバー基準時刻のGMTとの時差）とすることをお勧めします。 サーバーの基準時刻=冬時間であり、Summer Timeを適用されていないときの地域時刻になります。（冬時間と言う表現は本来ないものとします）
お礼
回答ありがとうございます。 >具体的な例（模擬データ）を提示して頂ければ検討できますが、抽象的な質問には対応困難です。 なるほど、そうですか。 こちらには残念ながら、そういったデータは存在しないためできませんね。 >可能性が低い仮説に対して無駄な時間を費やすつもりはありませんので、必要が生じた時点で再質問してください。 尚、当方で提示した処理について論理的な処理過程が理解できれば応用力で解決できると思います。（丸投げは好ましくありません） 可能性が低いと言いましたが、調べたところそのような業者を発見しました。 http://www.fxplus.com/broker/tfx/ 上記URLの下部の方の「取引情報」の「GMT時差」には、 夏時間：+1時間 冬時間：+2時間 と載っておりました。 口座履歴のデータがないためこんな情報を記載しても意味がないのですが。 応用力ですか、自分に理解できるでしょうか。 >その考え方は数式を複雑化するのでお勧めできません。 「設定」シートのD3はMT4の基準時刻（GMT+2）として固定し、Summer Timeの開始日時と終了日時でSummer Timeの期間内はMT4の時刻がGMT+3となるような仕組みが良いでしょう。 例えば、南半球の事業者の間合いはSummer Timeの開始日時を「9月第2日曜日2:00」、Summer Time終了日時を「翌年4月第1日曜日2:00」のようにすることで、すべての地域についてカバーできる処理方法が確立できるでしょう。 因みに、世界のSummer Time採用地域は検索すれば（"ググれば"とは言いたくない）情報を得られます。 複雑化ですか。 たしかに、複雑化するとただでさえ関数に疎い自分にとっては理解不能となります。 はい、今後「サーバー基準時刻のGMTとの時差」というような表現に変えた方がいいようですね。 ありがとうございました。
- bunjii
- ベストアンサー率43% (3589/8249)
回答No.11の追加です。 他の回答者（回答No.8）へのお礼で次のように提示していますので、回答No.11での「元データの提示」に代替して検証しました。 Z3=IF(A3="","",DATE(A3,B3,C3)+D3+(9-設定!$D$3+((DATE(A3,B3,C3)+D3)<AC3)+((DATE(A3,B3,C3)+D3)>AD3))/24) 「設定」シートのD3へ入力する値が夏時間（GMT+3）の場合と冬時間（GMT+2）の場合では時差調整の論理が異なります。 当初の質問（貼付画像）では冬時間のGMT+2が「設定」シートのD3セルに入力されています。 従って、回答No.7へのお礼で突如、夏時間のGMT+3を引き合いに出されたので、それに対する修正が必要になったことを理解してください。 尚、作業用列のACとADについてはあなたの見解の「1週目に日曜日が無い」と言うことは「第1日曜日が無い」とは関係なく、「第1日曜日」とは「月初めから見て最初の日曜日」と言うことになり、2011年3月の第2日曜日は2011年3月1日以降の2回目の日曜日と言うことになり、3月6日が第1日曜日で3月13日が第2日曜日と言うことです。 >夏時間と冬時間が逆の業者というのは存在しているのか？という問いに対しては、業者ごとに違いがある可能性もあるとの回答でした。 今回の質問対象は北半球に限ったものと推測します。 しかし、南半球の場合は夏冬が逆転しますのでサマータイムを採用している場合は夏時間の開始日と終了日が反転するでしょう。 しかし、現地の夏時間は標準時（冬時間）を1時間早めるだけと思います。
お礼
回答ありがとうございます。 >他の回答者（回答No.8）へのお礼で次のように提示していますので、回答No.11での「元データの提示」に代替して検証しました。 Z3=IF(A3="","",DATE(A3,B3,C3)+D3+(9-設定!$D$3+((DATE(A3,B3,C3)+D3)<AC3)+((DATE(A3,B3,C3)+D3)>AD3))/24) 「設定」シートのD3へ入力する値が夏時間（GMT+3）の場合と冬時間（GMT+2）の場合では時差調整の論理が異なります。 当初の質問（貼付画像）では冬時間のGMT+2が「設定」シートのD3セルに入力されています。 従って、回答No.7へのお礼で突如、夏時間のGMT+3を引き合いに出されたので、それに対する修正が必要になったことを理解してください。 尚、作業用列のACとADについてはあなたの見解の「1週目に日曜日が無い」と言うことは「第1日曜日が無い」とは関係なく、「第1日曜日」とは「月初めから見て最初の日曜日」と言うことになり、2011年3月の第2日曜日は2011年3月1日以降の2回目の日曜日と言うことになり、3月6日が第1日曜日で3月13日が第2日曜日と言うことです。 申し訳ありません。 自分があまり理解していないことと、変更点がありましてご迷惑をおかけしました。 夏時間・冬時間の開始日時・終了日時については理解できました。 例えば、「第2」というのは月の始めが1週目で、その次の2週目、という意味ではなく、その月の始めから2番目の週という意味なんですね。 だから、夏時間の開始日時が「3月第2日曜日2:00」の場合、2011年で言えば、「2011年3月6日」ではなく、「2011年3月13日」となるんですね。 >今回の質問対象は北半球に限ったものと推測します。 しかし、南半球の場合は夏冬が逆転しますのでサマータイムを採用している場合は夏時間の開始日と終了日が反転するでしょう。 しかし、現地の夏時間は標準時（冬時間）を1時間早めるだけと思います。 大変申し訳ありませんが業者によって異なる可能性がある以上、それも考慮していただけないでしょうか？ 質問当初では、絶対に「冬時間＝夏時間-1時間」であると思ってたんです。 恐らく可能性は低いと思われますがもし、夏時間と冬時間が逆転するような業者があった場合、また質問しなければなりません。 「設定」シートのD3（サーバー時間の夏時間のGMT） 「設定」シートのE3（サーバー時間の冬時間のGMT） 「設定」シートのF3（夏時間の開始日時） 「設定」シートのG3（夏時間の終了日時） として考えて頂けないでしょうか？ 何度も変更点があり、申し訳ないのですが引き続きよろしくお願いします。
- bunjii
- ベストアンサー率43% (3589/8249)
>「設定」シートのD3（サーバー時間GMT（夏時間））に3、 質問の画像では「D3=+2」となっていますのでそのまま利用しての計算になっていますので変更するのであればZ3の数式の一部を修正する必要があります。 何故、D3の+2を+3に修正されたのでしょうか？ 当方ではD3セルに+2と入力されていることを条件とした数式を提示しています。 >「MT損益計算 - テスト.xlsx」、「2015年6月分.htm」、「MT損益計算用データ.xlsx」のファイルは、下記URLよりダウンロードできます。 私は会員ではないので「MT損益計算 - テスト.xlsx」であったもダウンロードできませんでした。 >Z3の表示結果は、 >2011年1月3日 18:22:00 元データの日時を提示してください。 Z3の数式を修正して検証してからD3に+3と入力したときの数式を提示します。 >AC3：2011年3月6日 夏時間の開始日は第2日曜日のはずですからAC3は「2011年3月13日」になるでしょう。 尚、元データの日時が2011/1/3 ?:??:??なので明らかに冬時間です。 因って、AC3とAD3の年月まで合っていれば影響ない範囲です。 取引日時が3月と11月については日付までチェックする必要があります。 >AD3：不明。2011年11月に第1日曜日はありませんでした。（2011年11月6日となるのでしょうか？） 当方での検証では2011年11月の第1日曜日は2011年11月6日になりました。
お礼
回答ありがとうございます。 >質問の画像では「D3=+2」となっていますのでそのまま利用しての計算になっていますので変更するのであればZ3の数式の一部を修正する必要があります。 何故、D3の+2を+3に修正されたのでしょうか？ 当方ではD3セルに+2と入力されていることを条件とした数式を提示しています。 そんなに繊細なものなんですか？ 「設定」シートのD3（サーバー時間の夏時間のGMT）の数値を、気軽に変更しても日本時間を表示できるようにしていただきたいのですが。 >私は会員ではないので「MT損益計算 - テスト.xlsx」であったもダウンロードできませんでした。 下記URLより、会員でなくても誰でもダウンロードできます。そのように設定しましたから。 URLをクリックしたら画面上部の中央付近「↓」をクリックすればダウンロードできます。 https://drive.google.com/file/d/0By6NZj4rYFMwM0p2R1dyVmxZRms/view?usp=sharing >元データの日時を提示してください。 Z3の数式を修正して検証してからD3に+3と入力したときの数式を提示します。 「元データ」シートの開始時刻（B3）のデータは、 2011.01.03 12:22 となっております。 本来なら、秒も含まれます。 「設定」シートのD3（サーバー時間の夏時間のGMT）の数値は、自由に変更しても日本時間が表示されるようにしていただきたいです。 ただし、入力値は整数のみです。 上記URLより、ダウンロードしていただければ分かると思います。 >夏時間の開始日は第2日曜日のはずですからAC3は「2011年3月13日」になるでしょう。 尚、元データの日時が2011/1/3 ?:??:??なので明らかに冬時間です。 因って、AC3とAD3の年月まで合っていれば影響ない範囲です。 取引日時が3月と11月については日付までチェックする必要があります。 「損益計算」シートのAC3（夏時間の開始日時）は、「2011年3月6日」となるはずですが・・・。 確かに「元データ」シートの開始時刻（B3）のデータは冬時間で影響はないですが、「損益計算」シートのAC4～AC350（2011年度の夏時間の開始日時）が「2011年3月13日」となっています。 2011年3月7日（月曜日）（日本時間）以降は、夏時間を適用しないと正しい日本時間を表示できないのではないかと思うのですが。 これも、ファイルをダウンロードしてもらえると分かると思います。 >当方での検証では2011年11月の第1日曜日は2011年11月6日になりました。 「設定」シートの夏時間の開始日時・終了日時で設定した何週目かが存在しない場合、正直言いますと自分には分かりません。 回答者様の方が正確に理解されているようなので、任せます。 引き続き、よろしくお願いします。
- kagakusuki
- ベストアンサー率51% (2610/5101)
>>・「元データ」という名称のシートのB3セル以下にサーバー時間による取引開始日時がyyyy.mm.dd hh:mm:ssの形式の文字列データ(シリアル値ではない)で入力されている >>・「元データ」という名称のシートのI3セル以下にサーバー時間による取引終了日時がyyyy.mm.dd hh:mm:ssの形式の文字列データ(シリアル値ではない)で入力されている >「元データ」シートのデータは全て標準の形式です。 いえ、セルの書式設定の事を尋ねている訳では御座いません。 「4桁の西暦年」+「.」+「2桁の月の数」+「.」+「2桁の日の数」+「半角文字の空白スペース」+「2桁の時間の数」+「:」+「2桁の分の数」+「:」+「2桁の秒の数」 という形式で入力されていて、それ以外の形式(「2015.7.16 1:2:3」や「16-Jul-15 01:02:03」、全角文字の「２０１５．７．１６ ０１：０２：０３」等々)で入力されている事はあるのでしょうか、それともないのでしょうか? 尚、「yyyy.mm.dd hh:mm:ss」の形式で入力されているデータと >「yyyy.mm.dd hh:mm」の状態です。 が混在していたとしても、(本件で私が回答した方法の場合は)同じ様に変換する事は可能ですので、その点に関しましては問題は無いと思われます。 >>・「設定」という名称のシートのD3セル以下に夏時間中のサーバー時間とGMTとの時差が1時間単位の整数値で入力されていて、夏時間中の場合においては、その値を24で割ってサーバー時間の日時から差し引くと年月日付きのGMTの日時のシリアル値となる。 >「設定」シートのD3には整数値が入っています。小数点はいりません。 「小数点はいりません」と態々書いておられるという事は、「設定」という名称のシートのD3セル以下に、夏時間中のサーバー時間とGMTの時差として入力されている値の中には、小数点以下の桁数を含んでいる値(例えば+1.5等)が入力されている場合もあり得るという事なのでしょうか? そうしますと、単純に「1時間単位の値」として取り扱う訳には行かなくなるかも知れませんので、その点を明確にして頂きたいと思います。 それと、 >夏時間中の場合においては、その値を24で割ってサーバー時間の日時から差し引くと年月日付きのGMTの日時のシリアル値となる。 という点に関しては何もお答え頂けておりませんが、「サーバー時間から時差として表示されている値を差し引くとGMTになる」のか、それとも逆に「サーバー時間に時差として表示されている値を加えるとGMTになる」のか、どちらなのでしょうか? >冬時間が必ずしも夏時間-1時間であるということではない可能性もあるかもしれません。 >これも、計算に考慮しなければならないです。 との事ですが、そうしますと、GMTに対する夏時間の時差に関しては「設定」シートのD3セル以下に入力されている値から求める事は出来るとしましても、GMTに対する冬時間の時差の方はどこにもデータが入力されていないため、サーバーの冬時間を日本時間に変換する事は不可能という事になってしまいますが、この問題に関してはどうするおつもりなのでしょうか? >>・「設定」という名称のシートのF3セル以下に夏時間が終わる日時が、 >>m月第0aaa曜日h:mm >>の形式の文字列データで入力されている >設定」シートのE3、F3いずれも標準の形式ですが、文字列としてとらえてもらった方がいいです。 いえ、セルの書式設定の事を尋ねている訳では御座いません。 >m月第0aaa曜日h:mm >の形式の文字列データで入力されている 即ち、「何月何日の第何何曜日の何時何分」という形式で必ず表記されていて、例えば「何月何日の何時何分」という様な他の形式で表記されている事は無いのかどうかという事を確認している訳です。 >「変換不能」は絶対ないようにしていただきたいです。 >「変換不能」にしてしまうと、本質問では記載していないのですが別のシートに影響を与えてしまうからです。つまり、エラーになってしまいます。 勘違いされておられる様ですが、サーバー時間を日本時間に変換するためには、 「GMTとサーバーの夏時間の時差」、「夏時間と冬時間の差」、「夏時間が始まる日時」、「夏時間が終わる日時」、「取引開始・終了日時」 という５つのデータが"一定の形式"で入力されている事が必要になります。 ですから万が一、上記の５つのデータの中に、未入力のものがある場合や、"想定されたものとは異なる形式"で入力されているもの、誤ったデータが入力されているもの、等々が存在している場合には、当然の事ながら日本時間に変換する事など出来ません。 そのため、その様な変換不能なデータである場合には「変換不能」である事を示す表示とするか、誤った値が表示されてしまう事になっても知らないふりをするか、エラーとするか、の３通りしか無い訳なのですから、 >つまり、エラーになってしまいます。 等という心配をする意味が御座いません。 それとも「変換不能」と表示せずに、エラーとしてしまった方が宜しいのでしょうか?
お礼
回答ありがとうございます。 >いえ、セルの書式設定の事を尋ねている訳では御座いません。 「4桁の西暦年」+「.」+「2桁の月の数」+「.」+「2桁の日の数」+「半角文字の空白スペース」+「2桁の時間の数」+「:」+「2桁の分の数」+「:」+「2桁の秒の数」 という形式で入力されていて、それ以外の形式(「2015.7.16 1:2:3」や「16-Jul-15 01:02:03」、全角文字の「２０１５．７．１６ ０１：０２：０３」等々)で入力されている事はあるのでしょうか、それともないのでしょうか? はい、分かっています。 例のある通り、全て半角の「yyyy.mm.dd hh:mm:ss」です（スペースも含む）。 >「小数点はいりません」と態々書いておられるという事は、「設定」という名称のシートのD3セル以下に、夏時間中のサーバー時間とGMTの時差として入力されている値の中には、小数点以下の桁数を含んでいる値(例えば+1.5等)が入力されている場合もあり得るという事なのでしょうか? そうしますと、単純に「1時間単位の値」として取り扱う訳には行かなくなるかも知れませんので、その点を明確にして頂きたいと思います。 「小数点はいりません」というのは整数しか入力しないという意味です。 >という点に関しては何もお答え頂けておりませんが、「サーバー時間から時差として表示されている値を差し引くとGMTになる」のか、それとも逆に「サーバー時間に時差として表示されている値を加えるとGMTになる」のか、どちらなのでしょうか? 日本はGMT+9で、例えば「設定」シートのD3（サーバー時間の夏時間のGMT）に3が入っていたとしたらその時差で日本時間にするには、サーバー時間から+6となるため、「9-3」となります。 >との事ですが、そうしますと、GMTに対する夏時間の時差に関しては「設定」シートのD3セル以下に入力されている値から求める事は出来るとしましても、GMTに対する冬時間の時差の方はどこにもデータが入力されていないため、サーバーの冬時間を日本時間に変換する事は不可能という事になってしまいますが、この問題に関してはどうするおつもりなのでしょうか? そうしますと、右にずらし、 「設定」シートのD3（サーバー時間の夏時間のGMT） 「設定」シートのE3（サーバー時間の冬時間のGMT） 「設定」シートのF3（夏時間の開始日時） 「設定」シートのG3（夏時間の終了日時） とするのがよいかと思います。 >いえ、セルの書式設定の事を尋ねている訳では御座いません。 >m月第0aaa曜日h:mm >の形式の文字列データで入力されている >即ち、「何月何日の第何何曜日の何時何分」という形式で必ず表記されていて、例えば「何月何日の何時何分」という様な他の形式で表記されている事は無いのかどうかという事を確認している訳です。 「0aaa」という意味は分かりませんが、例のある通り、「月の半角数値」（1桁、または2桁）、「月」（文字列）、「第」（文字列）、「週の何週目か半角数値」、「曜日」（文字列）、「時刻の半角数値」（1桁、または2桁）、「:」（半角コロン）、「分の半角数値」（2桁）の順で入力します。 >勘違いされておられる様ですが、サーバー時間を日本時間に変換するためには、 「GMTとサーバーの夏時間の時差」、「夏時間と冬時間の差」、「夏時間が始まる日時」、「夏時間が終わる日時」、「取引開始・終了日時」 という５つのデータが"一定の形式"で入力されている事が必要になります。 ですから万が一、上記の５つのデータの中に、未入力のものがある場合や、"想定されたものとは異なる形式"で入力されているもの、誤ったデータが入力されているもの、等々が存在している場合には、当然の事ながら日本時間に変換する事など出来ません。 そのため、その様な変換不能なデータである場合には「変換不能」である事を示す表示とするか、誤った値が表示されてしまう事になっても知らないふりをするか、エラーとするか、の３通りしか無い訳なのですから、 「>つまり、エラーになってしまいます。」 等という心配をする意味が御座いません。 それとも「変換不能」と表示せずに、エラーとしてしまった方が宜しいのでしょうか? そうですか。 それなら、そのままで結構です。 「元データ」シート、「設定」シートのデータを理解していただくためには下記URLよりダウンロードしてもらえるとよく分かると思います。 https://drive.google.com/file/d/0By6NZj4rYFMwM0p2R1dyVmxZRms/view?usp=sharing なお、誰でもダウンロードできます。 URLをクリックしたら画面上部の中央付近「↓」をクリックすればダウンロードできます。 よろしくお願いします。
- kagakusuki
- ベストアンサー率51% (2610/5101)
同様に、損益計算シートのAA3セルの関数は次の様なものとなります。 =IF(元データ!$I3="","",IF(ISERROR(1/(TEXT((YEAR(SUBSTITUTE(TEXT(元データ!$I3,"yyyy/m/d"),".","/"))&"/"&LEFT(設定!$E3,FIND("月",設定!$E3)-1)&"/"&(MID(設定!$E3,FIND("第",設定!$E3)+1,1)-1)*7+MOD(FIND(MID(設定!$E3,FIND("曜日",設定!$E3)-1,1),"日月火水木金土")-WEEKDAY(DATE(YEAR(SUBSTITUTE(TEXT(元データ!$I3,"yyyy/m/d"),".","/")),LEFT(設定!$E3,FIND("月",設定!$E3)-1),1)),7)+1)+REPLACE(設定!$E3,1,FIND("曜日",設定!$E3)+1,),"m月第"""&INT(DAY(YEAR(SUBSTITUTE(TEXT(元データ!$I3,"yyyy/m/d"),".","/"))&"/"&LEFT(設定!$E3,FIND("月",設定!$E3)-1)&"/"&(MID(設定!$E3,FIND("第",設定!$E3)+1,1)-1)*7+MOD(FIND(MID(設定!$E3,FIND("曜日",設定!$E3)-1,1),"日月火水木金土")-WEEKDAY(DATE(YEAR(SUBSTITUTE(TEXT(元データ!$I3,"yyyy/m/d"),".","/")),LEFT(設定!$E3,FIND("月",設定!$E3)-1),1)),7)+1)/7)+1&"""aaaah:mm")=設定!$E3)/(TEXT((YEAR(SUBSTITUTE(TEXT(元データ!$I3,"yyyy/m/d"),".","/"))&"/"&LEFT(設定!$F3,FIND("月",設定!$F3)-1)&"/"&(MID(設定!$F3,FIND("第",設定!$F3)+1,1)-1)*7+MOD(FIND(MID(設定!$F3,FIND("曜日",設定!$F3)-1,1),"日月火水木金土")-WEEKDAY(DATE(YEAR(SUBSTITUTE(TEXT(元データ!$I3,"yyyy/m/d"),".","/")),LEFT(設定!$F3,FIND("月",設定!$F3)-1),1)),7)+1)+REPLACE(設定!$F3,1,FIND("曜日",設定!$F3)+1,),"m月第"""&INT(DAY(YEAR(SUBSTITUTE(TEXT(元データ!$I3,"yyyy/m/d"),".","/"))&"/"&LEFT(設定!$F3,FIND("月",設定!$F3)-1)&"/"&(MID(設定!$F3,FIND("第",設定!$F3)+1,1)-1)*7+MOD(FIND(MID(設定!$F3,FIND("曜日",設定!$F3)-1,1),"日月火水木金土")-WEEKDAY(DATE(YEAR(SUBSTITUTE(TEXT(元データ!$I3,"yyyy/m/d"),".","/")),LEFT(設定!$F3,FIND("月",設定!$F3)-1),1)),7)+1)/7)+1&"""aaaah:mm")=設定!$F3)/(TEXT(SUBSTITUTE(TEXT(元データ!$I3,"yyyy/m/d h:m:s"),".","/")+0,"yyyy.mm.dd hh:mm:ss")=元データ!$I3)/(YEAR(SUBSTITUTE(TEXT(元データ!$I3,"yyyy/m/d"),".","/"))>1904)/(ABS(設定!$D3)<24)),"変換不能",TEXT(SUBSTITUTE(TEXT(元データ!$I3,"yyyy/m/d h:m:s"),".","/")+(9- 設定!$D3+OR(SUBSTITUTE(TEXT(元データ!$I3,"yyyy/m/d h:m:s"),".","/")+0 <(YEAR(SUBSTITUTE(TEXT(元データ!$I3,"yyyy/m/d"),".","/"))&"/"&LEFT(設定!$E3,FIND("月",設定!$E3)-1)&"/"&(MID(設定!$E3,FIND("第",設定!$E3)+1,1)-1)*7+MOD(FIND(MID(設定!$E3,FIND("曜日",設定!$E3)-1,1),"日月火水木金土")-WEEKDAY(DATE(YEAR(SUBSTITUTE(TEXT(元データ!$I3,"yyyy/m/d"),".","/")),LEFT(設定!$E3,FIND("月",設定!$E3)-1),1)),7)+1)+REPLACE(設定!$E3,1,FIND("曜日",設定!$E3)+1,), SUBSTITUTE(TEXT(元データ!$I3,"yyyy/m/d h:m:s"),".","/")+0 >(YEAR(SUBSTITUTE(TEXT(元データ!$I3,"yyyy/m/"),".","/"))&"/"&LEFT(設定!$F3,FIND("月",設定!$F3)-1)&"/"&(MID(設定!$F3,FIND("第",設定!$F3)+1,1)-1)*7+MOD(FIND(MID(設定!$F3,FIND("曜日",設定!$F3)-1,1),"日月火水木金土")-WEEKDAY(DATE(YEAR(SUBSTITUTE(TEXT(元データ!$I3,"yyyy/m/d"),".","/")),LEFT(設定!$F3,FIND("月",設定!$F3)-1),1)),7)+1)+REPLACE(設定!$F3,1,FIND("曜日",設定!$F3)+1,)) )/24+0.000001,"yyyy/m/d h:m:s")+0))
お礼
回答者の皆様方ありがとうございます。 長い間、付き合っていただいたことに対しましてお詫び申し上げます。 また、ここまで回答をくださった方々に非常に感謝しております。 ありがとうございました。
- kagakusuki
- ベストアンサー率51% (2610/5101)
QNo.9010139 夏・冬時間による日本時間の表示 回答No.1です。 >回答者様の疑問点はサーバー元の方に問い合わせないと分からないため少しばかりというか、時間がかかります。 >それまでお待ち願えるでしょうか？ との事でしたので、このサイトの機能の1つである「自分が投稿した回答にお礼や補足が付いた場合にお知らせする」という機能によるお知らせがあるまで待っておりましたが、未だ「お礼があった」という御知らせが無かったため念のために確認しました処、他の回答者様の方には色々と追加情報を入れておられる様ですが、それらの情報は私の疑問点に対する御返答の意味もあるのでしょうか? それで、色々と追加情報があり、一部には変更点もある様ですが、要するに、 ・「元データ」という名称のシートのB3セル以下にサーバー時間による取引開始日時がyyyy.mm.dd hh:mm:ssの形式の文字列データ(シリアル値ではない)で入力されている ・「元データ」という名称のシートのI3セル以下にサーバー時間による取引終了日時がyyyy.mm.dd hh:mm:ssの形式の文字列データ(シリアル値ではない)で入力されている ・「設定」という名称のシートのD3セル以下に夏時間中のサーバー時間とGMTとの時差が1時間単位の整数値で入力されていて、夏時間中の場合においては、その値を24で割ってサーバー時間の日時から差し引くと年月日付きのGMTの日時のシリアル値となる。 ・御質問文中にある関数 =IF(元データ!A3="","",DATE(A3,B3,C3)+D3+(1/24*(9-設定!$D$3))) において、サーバー時間にD3の値を足しているのは間違いで、D3の値を差し引かなければGMTにはならない ・冬時間中のサーバー時間の日時は、同日時における夏時間中のサーバー時間に相当する日時に対して必ず1時間遅れの日時となる ・「設定」という名称のシートのE3セル以下に夏時間が始まる日時が、 m月第0aaa曜日h:mm の形式の文字列データで入力されている ・「設定」という名称のシートのF3セル以下に夏時間が終わる日時が、 m月第0aaa曜日h:mm の形式の文字列データで入力されている ・夏時間が終わる日時は必ず取引が行われない土日の日になるため、夏時間の終わり間際に同じ時刻が2回現れる事に関しては考慮する必要がない ・上記の情報を基にして、「元データ」という名称のシートのB3以下に入力されている取引開始日時と、I3以下に入力されている取引終了日時を、それぞれ日本時間(西暦年月日付き)に変換した上で、取引開始日時は「損益計算」という名称のシートのZ3以下に、取引終了日時は同シートのAA3以下にそれぞれ関数を使って自動的に表示させる。 という事で宜しいのでしょうか? それでしたら損益計算シートのZ3セルの関数は次の様なものとなります。 (万が一、間違ったデータが入力されていて、日本時間に変換出来ない様な場合にもエラーを出さずに、「変換不能」と表示させる様にした処、これまでにない程長い関数となってしまいました) =IF(元データ!$B3="","",IF(ISERROR(1/(TEXT((YEAR(SUBSTITUTE(TEXT(元データ!$B3,"yyyy/m/d"),".","/"))&"/"&LEFT(設定!$E3,FIND("月",設定!$E3)-1)&"/"&(MID(設定!$E3,FIND("第",設定!$E3)+1,1)-1)*7+MOD(FIND(MID(設定!$E3,FIND("曜日",設定!$E3)-1,1),"日月火水木金土")-WEEKDAY(DATE(YEAR(SUBSTITUTE(TEXT(元データ!$B3,"yyyy/m/d"),".","/")),LEFT(設定!$E3,FIND("月",設定!$E3)-1),1)),7)+1)+REPLACE(設定!$E3,1,FIND("曜日",設定!$E3)+1,),"m月第"""&INT(DAY(YEAR(SUBSTITUTE(TEXT(元データ!$B3,"yyyy/m/d"),".","/"))&"/"&LEFT(設定!$E3,FIND("月",設定!$E3)-1)&"/"&(MID(設定!$E3,FIND("第",設定!$E3)+1,1)-1)*7+MOD(FIND(MID(設定!$E3,FIND("曜日",設定!$E3)-1,1),"日月火水木金土")-WEEKDAY(DATE(YEAR(SUBSTITUTE(TEXT(元データ!$B3,"yyyy/m/d"),".","/")),LEFT(設定!$E3,FIND("月",設定!$E3)-1),1)),7)+1)/7)+1&"""aaaah:mm")=設定!$E3)/(TEXT((YEAR(SUBSTITUTE(TEXT(元データ!$B3,"yyyy/m/d"),".","/"))&"/"&LEFT(設定!$F3,FIND("月",設定!$F3)-1)&"/"&(MID(設定!$F3,FIND("第",設定!$F3)+1,1)-1)*7+MOD(FIND(MID(設定!$F3,FIND("曜日",設定!$F3)-1,1),"日月火水木金土")-WEEKDAY(DATE(YEAR(SUBSTITUTE(TEXT(元データ!$B3,"yyyy/m/d"),".","/")),LEFT(設定!$F3,FIND("月",設定!$F3)-1),1)),7)+1)+REPLACE(設定!$F3,1,FIND("曜日",設定!$F3)+1,),"m月第"""&INT(DAY(YEAR(SUBSTITUTE(TEXT(元データ!$B3,"yyyy/m/d"),".","/"))&"/"&LEFT(設定!$F3,FIND("月",設定!$F3)-1)&"/"&(MID(設定!$F3,FIND("第",設定!$F3)+1,1)-1)*7+MOD(FIND(MID(設定!$F3,FIND("曜日",設定!$F3)-1,1),"日月火水木金土")-WEEKDAY(DATE(YEAR(SUBSTITUTE(TEXT(元データ!$B3,"yyyy/m/d"),".","/")),LEFT(設定!$F3,FIND("月",設定!$F3)-1),1)),7)+1)/7)+1&"""aaaah:mm")=設定!$F3)/(TEXT(SUBSTITUTE(TEXT(元データ!$B3,"yyyy/m/d h:m:s"),".","/")+0,"yyyy.mm.dd hh:mm:ss")=元データ!$B3)/(YEAR(SUBSTITUTE(TEXT(元データ!$B3,"yyyy/m/d"),".","/"))>1904)/(ABS(設定!$D3)<24)),"変換不能",TEXT(SUBSTITUTE(TEXT(元データ!$B3,"yyyy/m/d h:m:s"),".","/")+(9- 設定!$D3+OR(SUBSTITUTE(TEXT(元データ!$B3,"yyyy/m/d h:m:s"),".","/")+0 <(YEAR(SUBSTITUTE(TEXT(元データ!$B3,"yyyy/m/d"),".","/"))&"/"&LEFT(設定!$E3,FIND("月",設定!$E3)-1)&"/"&(MID(設定!$E3,FIND("第",設定!$E3)+1,1)-1)*7+MOD(FIND(MID(設定!$E3,FIND("曜日",設定!$E3)-1,1),"日月火水木金土")-WEEKDAY(DATE(YEAR(SUBSTITUTE(TEXT(元データ!$B3,"yyyy/m/d"),".","/")),LEFT(設定!$E3,FIND("月",設定!$E3)-1),1)),7)+1)+REPLACE(設定!$E3,1,FIND("曜日",設定!$E3)+1,), SUBSTITUTE(TEXT(元データ!$B3,"yyyy/m/d h:m:s"),".","/")+0 >(YEAR(SUBSTITUTE(TEXT(元データ!$B3,"yyyy/m/"),".","/"))&"/"&LEFT(設定!$F3,FIND("月",設定!$F3)-1)&"/"&(MID(設定!$F3,FIND("第",設定!$F3)+1,1)-1)*7+MOD(FIND(MID(設定!$F3,FIND("曜日",設定!$F3)-1,1),"日月火水木金土")-WEEKDAY(DATE(YEAR(SUBSTITUTE(TEXT(元データ!$B3,"yyyy/m/d"),".","/")),LEFT(設定!$F3,FIND("月",設定!$F3)-1),1)),7)+1)+REPLACE(設定!$F3,1,FIND("曜日",設定!$F3)+1,)) )/24+0.000001,"yyyy/m/d h:m:s")+0)) 因みに、上記の関数では「損益計算」シートのA3～H3の値は使用せず、「元データ」シートと「設定」シートのデータを使用して値を求めています。
お礼
回答ありがとうございます。 >との事でしたので、このサイトの機能の1つである「自分が投稿した回答にお礼や補足が付いた場合にお知らせする」という機能によるお知らせがあるまで待っておりましたが、未だ「お礼があった」という御知らせが無かったため念のために確認しました処、他の回答者様の方には色々と追加情報を入れておられる様ですが、それらの情報は私の疑問点に対する御返答の意味もあるのでしょうか? 申し訳ございいません。 他の方の回答が頻繁に来てその対応や（誤解されてしまうかもしれませんが、その回答者様のせいではありません。）、他に重要な作業がありまして、それに追われていました。 言い訳に過ぎませんが。 >・「元データ」という名称のシートのB3セル以下にサーバー時間による取引開始日時がyyyy.mm.dd hh:mm:ssの形式の文字列データ(シリアル値ではない)で入力されている >・「元データ」という名称のシートのI3セル以下にサーバー時間による取引終了日時がyyyy.mm.dd hh:mm:ssの形式の文字列データ(シリアル値ではない)で入力されている 「元データ」シートのデータは全て標準の形式です。 また、B3、I3いずれもサーバー時間です。 本来は、「yyyy.mm.dd hh:mm:ss」で表示しているのですが、現在持っている口座履歴のデータ数が足りず夏時間・冬時間の確認のため、他のデータを使用しており、「yyyy.mm.dd hh:mm」の状態です。 例： 「元データ」シートのB3（開始時刻）：2011.01.03 12:22 「元データ」シートのI3（終了時刻）：2011.01.03 19:32 >・「設定」という名称のシートのD3セル以下に夏時間中のサーバー時間とGMTとの時差が1時間単位の整数値で入力されていて、夏時間中の場合においては、その値を24で割ってサーバー時間の日時から差し引くと年月日付きのGMTの日時のシリアル値となる。 「設定」シートのD3には整数値が入っています。小数点はいりません。 >・冬時間中のサーバー時間の日時は、同日時における夏時間中のサーバー時間に相当する日時に対して必ず1時間遅れの日時となる この点なんですが、正直自分にも分らないのです。 業者の方にも問い合わせたのですが、業者の方も正確には分からないそうです。 夏時間と冬時間が逆の業者というのは存在しているのか？という問いに対しては、業者ごとに違いがある可能性もあるとの回答でした。 （例えば、A社の夏時間がGMT+3、冬時間がGMT+2だとして、B社の夏時間がGMT+2、冬時間がGMT+3という場合があるのか？という意味です。） つまり、冬時間が必ずしも夏時間-1時間であるということではない可能性もあるかもしれません。 これも、計算に考慮しなければならないです。 >・「設定」という名称のシートのE3セル以下に夏時間が始まる日時が、 m月第0aaa曜日h:mm の形式の文字列データで入力されている >・「設定」という名称のシートのF3セル以下に夏時間が終わる日時が、 m月第0aaa曜日h:mm の形式の文字列データで入力されている 「設定」シートのE3、F3いずれも標準の形式ですが、文字列としてとらえてもらった方がいいです。 「設定」シートのE3：3月第2日曜日2:00 「設定」シートのF3：11月第1日曜日2:00 のようにデータが入っています。 >・夏時間が終わる日時は必ず取引が行われない土日の日になるため、夏時間の終わり間際に同じ時刻が2回現れる事に関しては考慮する必要がない 正直どうしようか迷ったのですが、以前土日も取引可能な業者があったそうです。しかし、あまりにも相場の変動が少ないため取り引きする人がほとんどおらず、土日の取引は廃止となったそうです。 現在のところ、土日の取引をしている業者は国内・国外問わず存在していないようです。 ただ、下記の情報として計算に影響があるようでしたら計算を考慮していただきたいと思います。 自分が問い合わせた業者に限った話ですが、 営業時間（夏時間、日本時間として）：月曜5:00～土曜5:45 営業時間（冬時間、日本時間として）：月曜6:00～土曜6:45 ※時間表記：24時間 だそうです。 はっきり申しますと、土日は考慮しなくて結構です。 もし、土日取引可能な業者が現れたとしたら改めて質問させていただきます。 >・上記の情報を基にして、「元データ」という名称のシートのB3以下に入力されている取引開始日時と、I3以下に入力されている取引終了日時を、それぞれ日本時間(西暦年月日付き)に変換した上で、取引開始日時は「損益計算」という名称のシートのZ3以下に、取引終了日時は同シートのAA3以下にそれぞれ関数を使って自動的に表示させる。 上記の通り、条件が若干異なります。 >という事で宜しいのでしょうか? それでしたら損益計算シートのZ3セルの関数は次の様なものとなります。 (万が一、間違ったデータが入力されていて、日本時間に変換出来ない様な場合にもエラーを出さずに、「変換不能」と表示させる様にした処、これまでにない程長い関数となってしまいました) 大変申し訳ありません。 こんなに長い計算式を考えて頂いて、ご苦労があったかと思います。 それにも関わらず、改めて計算を考えて頂かなくてはいけないのは大変心苦しく思います。 条件が若干異なること、また、「変換不能」は絶対ないようにしていただきたいです。 「変換不能」にしてしまうと、本質問では記載していないのですが別のシートに影響を与えてしまうからです。つまり、エラーになってしまいます。 また、質問「Excel2010：年月の出力」で回答してくださった回答者の方なんですね。 その節は、大変お世話になりました。 http://okwave.jp/qa/q8355796.html 本質問のExcelファイルで回答者様の計算式を利用させてもらってます。 回答No.7の方にも申し上げたのですが、下記のような問題が起きてしまいました。 -------------------------------------------------- ■本質問と別の問題： 本質問とは異なりますが、Excelファイルの本質問に載っていない別シートに重大な問題が起こりました。 恐らく、口座履歴のデータを変更したからだと思われます。 今までの、口座履歴のデータはデータ数が足りず、日本時間が夏時間と冬時間で変更されているか確認するためでした。 本質問のExcelファイルは「MT損益計算 - テスト.xlsx」なんですが、その中の「月別結果」シートと「ストラテジー評価」シートというシートで、月別に結果を出したかったのですがエラーが出てしまいました。 本来なら、「2015年6月分.htm」（MT4というチャートソフトから口座履歴を取り出したデータ）の口座履歴のデータを「元データ」シートに入力していたのですが、代わりに「MT損益計算用データ.xlsx」のデータを「MT損益計算 - テスト.xlsx」の「元データ」シートに入力しました。 今までならサーバー時間としての「月別結果」を出していましたが、今後、日本時間として「月別結果」を出したいと思っています。 「MT損益計算 - テスト.xlsx」、「2015年6月分.htm」、「MT損益計算用データ.xlsx」のファイルは、下記URLよりダウンロードできます。 https://drive.google.com/file/d/0By6NZj4rYFMwM0p2R1dyVmxZRms/view?usp=sharing ただ、本質問と異なりますし、この問題はまた質問の詳細を記載しなければなりませんし、これ以上本質問で長引かせるのも回答者の皆様方にご負担・ご迷惑をおかけする可能性があるため、この問題は本質問が解決してから、別の質問として改めて質問した方がいいでしょうか？ また、時間を割いていただけなければならず申し訳ないのですが、まずは、本質問の回答をよろしくお願いします。
- bunjii
- ベストアンサー率43% (3589/8249)
>この情報で大丈夫でしょうか？ 当方の認識で夏時間の時差に誤りがあったようです。 今回の補足で時差の算出に必要な情報が揃ったようです。 1つの数式で開始日時を算出することは可能ですが、数式が長くなり解読し難いと思われますので、作業用の列を使った方が分かり易いでしょう。 尚、サーバーの設置場所に関しては今回提示のURLで確認できましたのでMT4（システムサーバー）の時刻が設置場所に関係なくGMT+3時間であることが分かりました。また、夏時間は1時間早めるためGMT+2時間になることも説明されているので問題ありません。 作業用列としては該当取引年の夏時間開始日と終了日を算出することで良いでしょう。 AC列を取引該当年の夏時間開始日、AD列を取引該当年の夏時間終了日とすれば AC3=IF(A3="","",DATE($A3,LEFT(設定!E$3,FIND("月",設定!E$3)-1),7*MID(設定!E$3,FIND("第",設定!E$3)+1,1))-WEEKDAY(DATE($A3,LEFT(設定!E$3,FIND("月",設定!E$3)-1),7*MID(設定!E$3,FIND("第",設定!E$3)+1,1)))+1) AD3はAC3をコピペすれば良いでしょう。 肝心のZ列は次のようになります。 Z3=IF(A3="","",DATE(A3,B3,C3)+D3+(9-設定!$D$3+((DATE(A3,B3,C3)+D3)>AC3)*((DATE(A3,B3,C3)+D3)<AD3))/24) 貼付画像はあなたが質問文で提示した「損益計算」シートと「設定」シートの内容から算出する方法で試算した結果です。 AA3はZ3の数式を一部修正すれば良いでしょう。 私は『MultiTrader』を全く知りませんので、勘違いがありましたらご指摘ください。 但し、計算に必要な情報のみで結構です。
お礼
回答ありがとうございます。 不要な情報により、混乱してしまったかと思います。 自分としましてもそういうつもりではなかったのですが、申し訳ありません。 回答者様の通り、 AC3に、 =IF(元データ!A3="","",DATE($A3,LEFT(設定!E$3,FIND("月",設定!E$3)-1),7*MID(設定!E$3,FIND("第",設定!E$3)+1,1))-WEEKDAY(DATE($A3,LEFT(設定!E$3,FIND("月",設定!E$3)-1),7*MID(設定!E$3,FIND("第",設定!E$3)+1,1)))+1) AD3に、 =IF(元データ!A3="","",DATE($A3,LEFT(設定!F$3,FIND("月",設定!F$3)-1),7*MID(設定!F$3,FIND("第",設定!F$3)+1,1))-WEEKDAY(DATE($A3,LEFT(設定!F$3,FIND("月",設定!F$3)-1),7*MID(設定!F$3,FIND("第",設定!F$3)+1,1)))+1) と入力しました。以下コピー。前半は若干異なりますが、問題ないかと思います。 Z3に、 =IF(元データ!A3="","",DATE(A3,B3,C3)+D3+(9-設定!$D$3+((DATE(A3,B3,C3)+D3)>AC3)*((DATE(A3,B3,C3)+D3)<AD3))/24) AA3に、 =IF(元データ!A3="","",DATE(E3,F3,G3)+H3+(9-設定!$D$3+((DATE(E3,F3,G3)+H3)>AC3)*((DATE(E3,F3,G3)+H3)<AD3))/24) と入力しました。以下コピー。前半は若干異なりますが、問題ないかと思います。 「設定」シートのD3（サーバー時間GMT（夏時間））に3、 「設定」シートのE3（夏時間：開始日時）に3月第2日曜日2:00、 「設定」シートのF3（夏時間：終了日時）に11月第1日曜日2:00 と入力しています。 Z3の表示結果は、 2011年1月3日 18:22:00 AA3の表示結果は、 2011年1月4日 1:32:00 AC3の表示結果は、 2011年3月13日 AD3の表示結果は、 2011年11月6日 と、元データは冬時間（GMT+2）のものですが、残念ながら表示結果は正しくありませんでした。 AC3とAD3の表示結果が間違っているからだと思われます。 正しくは、 AC3：2011年3月6日 AD3：不明。2011年11月に第1日曜日はありませんでした。（2011年11月6日となるのでしょうか？） 下記URL（万年カレンダー）より http://www5a.biglobe.ne.jp/%257eaccent/kazeno/calendar/2011.htm なお、業者の方からの回答で、 夏時間と冬時間が逆の業者というのは存在しているのか？という問いに対しては、業者ごとに違いがある可能性もあるとの回答でした。 （例えば、A社の夏時間がGMT+3、冬時間がGMT+2だとして、B社の夏時間がGMT+2、冬時間がGMT+3という場合があるのか？という意味です。） つまり、冬時間が必ずしも夏時間-1時間であるということではない可能性もあるかもしれません。 これも、計算に考慮していただけないでしょうか？ それと、自分には分からないため一応情報を載せていただきます。 土日は考慮しなくてよいのですが、下記の情報として計算に影響があるようでしたら計算を考慮していただきたいと思います。 自分が問い合わせた業者に限った話ですが、 営業時間（夏時間、日本時間として）：月曜5:00～土曜5:45 営業時間（冬時間、日本時間として）：月曜6:00～土曜6:45 ※時間表記：24時間 だそうです。 またもし、土日取引可能な業者が現れたとしたら改めて質問させていただきます。 -------------------------------------------------- ■本質問と別の問題： また、本質問とは異なりますが、Excelファイルの本質問に載っていない別シートに重大な問題が起こりました。 恐らく、口座履歴のデータを変更したからだと思われます。 今までの、口座履歴のデータはデータ数が足りず、日本時間が夏時間と冬時間で変更されているか確認するためでした。 本質問のExcelファイルは「MT損益計算 - テスト.xlsx」なんですが、その中の「月別結果」シートと「ストラテジー評価」シートというシートで、月別に結果を出したかったのですがエラーが出てしまいました。 本来なら、「2015年6月分.htm」（MT4というチャートソフトから口座履歴を取り出したデータ）の口座履歴のデータを「元データ」シートに入力していたのですが、代わりに「MT損益計算用データ.xlsx」のデータを「MT損益計算 - テスト.xlsx」の「元データ」シートに入力しました。 今までならサーバー時間としての「月別結果」を出していましたが、今後、日本時間として「月別結果」を出したいと思っています。 「MT損益計算 - テスト.xlsx」、「2015年6月分.htm」、「MT損益計算用データ.xlsx」のファイルは、下記URLよりダウンロードできます。 https://drive.google.com/file/d/0By6NZj4rYFMwM0p2R1dyVmxZRms/view?usp=sharing ただ、本質問と異なりますし、この問題はまた質問の詳細を記載しなければなりませんし、これ以上本質問で長引かせるのも回答者の皆様方にご負担・ご迷惑をおかけする可能性があるため、この問題は本質問が解決してから、別の質問として改めて質問した方がいいでしょうか？ 取りあえず、お手数ですが本質問の回答をよろしくお願いします。
お礼
回答ありがとうございます。 申し訳ありません。 回答者様のおっしゃる通り、「損益計算」シートで再度調べたところ22日でした。 焦って確認したせいか抜けていた部分がありました。 日本時間では、2011年7月23日 0:31:00（開始日時）～2011年7月23日 1:06:00（終了日時）となっており、土曜日に行われていました。 土・日・元日は、取り引きが行われていないというのが一般的に言われていることなんですが、土曜日に限っては厳密に金曜日の23:59:59に終了というわけではなく、土曜日の早朝まで取り引きが行われていまして、本来ならそこまで計算に入れなければならなかったんです。 ですが、ここではもうそれができないため、新規の質問を検討したいと思います。 正しいご指摘、ありがとうございます。 また、この件に関しまして質問する際には是非とも回答をいただけたら大変ありがたいです。 ありがとうございました。