- ベストアンサー
夏・冬時間による日本時間の表示
- Excel2010で、夏時間と冬時間による日本時間を表示する方法について教えてください。
- 「損益計算」シートの開始日時と終了日時を日本時間で表示したいのですが、計算式が分かりません。夏時間と冬時間を考慮して表示する方法を教えてください。
- 具体的な手順として、夏時間のGMTと開始日時、終了日時を「設定」シートに入力し、「損益計算」シートの開始日時と終了日時を日本時間で表示するための計算式を教えてください。
- miya_HN
- お礼率100% (1683/1683)
- Excel（エクセル）
- 回答数37
- ありがとう数37
- みんなの回答 （37）
- 専門家の回答
質問者が選んだベストアンサー
その他の回答 (36)
- kagakusuki
- ベストアンサー率51% (2610/5101)
回答No.25の続きです。 それで「MT損益計算 - テスト」の改良案に関してですが、まず「設定」シートに関しましては、回答No.10に対して質問者様が書かれたお礼コメント内容である >そうしますと、右にずらし、 >「設定」シートのD3（サーバー時間の夏時間のGMT） >「設定」シートのE3（サーバー時間の冬時間のGMT） >「設定」シートのF3（夏時間の開始日時） >「設定」シートのG3（夏時間の終了日時） >とするのがよいかと思います。 に準じて「設定」シートのA列～G列を変更して下さい。 それに加えて「設定」シートに以下の様な変更点を追加して下さい。 まず、「設定」シートのI1セルとI2セルを結合して下さい。 そしてその結合セルに「夏時間 有無」と入力して下さい。 次に、「設定」シートのJ1セル～M1セルを結合して下さい。 そしてその結合セルに「夏時間開始日時」と入力して下さい。 次に、「設定」シートのN1セル～R1セルを結合して下さい。 そしてその結合セルに「夏時間終了日時」と入力して下さい。 次に、「設定」シートのJ3セルとO3セルに「月」と入力して下さい。 次に、「設定」シートのK3セルとP3セルに「週」と入力して下さい。 次に、「設定」シートのL3セルとQ3セルに「曜日」と入力して下さい。 次に、「設定」シートのM3セルとR3セルに「時刻」と入力して下さい。 次に、N3セルに「加算年」と入力して下さい。 次に「設定」シートのI3セルに次の関数を入力して下さい。 =IF($D3&$E3&$F3&$G3="","",IF(AND($D3>=-12,$D3<=14,COUNTIF($F3,"*?月???曜日*?:??"),ISNUMBER(("1904年"&LEFT($F3,FIND("月",$F3)))+ MATCH(MID($F3,FIND("月",$F3)+1,2),{"第1","第2","第3","第4","第5","最終"},0)+ FIND(MID($F3,FIND("曜日",$F3)-1,1),"月火水木金土日")+1/(REPLACE($F3,1,FIND("曜日",$F3)+1,)+0>=0)/(REPLACE($F3,1,FIND("曜日",$F3)+1,)+0<1)),COUNTIF($G3,"*?月???曜日*?:??"),ISNUMBER(("1904年"&REPLACE(LEFT($G3,FIND("月",$G3)),1,2*(LEFT($G3,2)="翌年"),))+ MATCH(MID($G3,FIND("月",$G3)+1,2),{"第1","第2","第3","第4","第5","最終"},0)+ FIND(MID($G3,FIND("曜日",$G3)-1,1),"月火水木金土日")+1/(REPLACE($G3,1,FIND("曜日",$G3)+1,)+0>=0)/(REPLACE($G3,1,FIND("曜日",$G3)+1,)+0<1))),"有",IF(AND($E3>=-12,$E3<=14,$D3&$F3&$G3=""),"無","無効な設定"))) 次に「設定」シートのJ3セルに次の関数を入力して下さい。 =IF($I3="有",LEFT($F3,FIND("月",$F3)-1)+0,"") 次に「設定」シートのK3セルに次の関数を入力して下さい。 =IF($I3="有",IF(ISNUMBER(FIND("最終",$F3)),"最終",MID($F3,FIND("第",$F3)+1,1)+0),"") 次に「設定」シートのL3セルに次の関数を入力して下さい。 =IF($I3="有",MID($F3,FIND("曜日",$F3)-1,1),"") 次に「設定」シートのM3セルに次の関数を入力して下さい。 =IF($I3="有",REPLACE($F3,1,FIND("曜日",$F3)+1,)+0,"") 次に「設定」シートのN3セルに次の関数を入力して下さい。 =IF($I3="有",(LEFT($G3,2)="翌年")+0,"") 次に「設定」シートのO3セルに次の関数を入力して下さい。 =IF($I3="有",SUBSTITUTE(LEFT($G3,FIND("月",$G3)-1),"翌年",)+0,"") 次に「設定」シートのP3セルに次の関数を入力して下さい。 =IF($I3="有",IF(ISNUMBER(FIND("最終",$G3)),"最終",MID($G3,FIND("第",$G3)+1,1)+0),"") 次に「設定」シートのQ3セルに次の関数を入力して下さい。 =IF($I3="有",MID($G3,FIND("曜日",$G3)-1,1),"") 次に「設定」シートのR3セルに次の関数を入力して下さい。 =IF($I3="有",REPLACE($G3,1,FIND("曜日",$G3)+1,)+0,"") 次に「損益計算」シートに関してですが、このシートのみはレイアウトが変わりますので以下の様に作り直して下さい。 まず、「損益計算」シートのA1セルとB1セルを結合して下さい。 そしてその結合セルに「取引開始日時」と入力して下さい。 次に「損益計算」シートのC1セルとD1セルを結合して下さい。 そしてその結合セルに「取引終了日時」と入力して下さい。 次に「損益計算」シートのA2セルに「現地サーバー時間」と入力して下さい。 次に「損益計算」シートのA3セルに次の関数を入力して下さい。 =IF(OR(元データ!$A3="",元データ!$B3=""),"",IFERROR(SUBSTITUTE(元データ!$B3,".","/")/COUNTIF(元データ!$B3,"????.??.?? ??:??*"),"変換不能")) 次に「損益計算」シートのB2セルに「日本時間」と入力して下さい。 次に「損益計算」シートのB3セルに次の関数を入力して下さい。 =IF(ISNUMBER($A3),IF(OR(設定!$I$3="無",AND(IF(ISNUMBER(設定!$K$3),MONTH(DATE(YEAR($A3),設定!$J$3,8-WEEKDAY(YEAR($A3)&"/"&設定!$J$3,FIND(設定!$L$3,"日月火水木金土")+10)+(設定!$K$3-1)*7))=設定!$J$3,設定!$K$3="最終"),IF(ISNUMBER(設定!$P$3),MONTH(DATE(YEAR($A3)+設定!$N$3,設定!$O$3,8-WEEKDAY(YEAR($A3)+設定!$N$3&"/"&設定!$O$3,FIND(設定!$Q$3,"日月火水木金土")+10)+(設定!$P$3-1)*7))=設定!$O$3,設定!$P$3="最終"))),TEXT($A3+(9-IF(IF(設定!$I$3="有",AND($A3>DATE(YEAR($A3),設定!$J$3+(設定!$K$3="最終"),8-WEEKDAY(YEAR($A3)&"/"&設定!$J$3+(設定!$K$3="最終"),FIND(設定!$L$3,"日月火水木金土")+10)+(SUM(設定!$K$3)-1)*7)+設定!$M$3,$A3<DATE(YEAR($A3)+設定!$N$3,設定!$O$3+(設定!$P$3="最終"),8-WEEKDAY(YEAR($A3)+設定!$N$3&"/"&設定!$O$3+(設定!$P$3="最終"),FIND(設定!$Q$3,"日月火水木金土")+10)+(SUM(設定!$P$3)-1)*7)+設定!$R$3),FALSE),設定!$D$3,設定!$E$3))/24+0.000001,"yyyy/m/d h:m:s")+0,"変換不能"),"") 次に「損益計算」シートのC2セルに「現地サーバー時間」と入力して下さい。 次に「損益計算」シートのC3セルに次の関数を入力して下さい。 =IF(OR(元データ!$A3="",元データ!$I3=""),"",IFERROR(SUBSTITUTE(元データ!$I3,".","/")/COUNTIF(元データ!$I3,"????.??.?? ??:??*"),"変換不能")) ※まだ途中なのですが、そろそろこのサイトの回答欄に入力可能な文字数の限度を超えてしまいますので、残りは又後に投稿致します。
お礼
回答ありがとうございます。 回答者様の回答通り、計算式を入れ確認しました。 >済みません、「作りなおしたブック」に関する回答の中で一部抜けがありました。 各シートの説明で3行目にしか関数を入力する様にしか説明しておりませんでしたが、「損益計算」シートと「月別結果」シートに関しては４行目以下にも関数が必要となりますので、３行目をコピーして４行目以下に貼り付けて下さい。 質問者様が作られた「MT損益計算 - テスト」のブックで使用されていた関数の中には、３行目と４行目以下で異なる関数が使用されている箇所も一部には御座いましたが、私が提案した「作りなおしたブック」の場合は３行目の関数をコピー&ペーストする事で、４行目以下にも対応可能なものとなっている筈ですので御確認下さい。 分かりました。 自分には分からなかったため、3行目と4行目以下で異なる計算式を入れていた箇所がありました。 全てコピペでできるんですね。 ありがとうございます。 >「JPY」（日本円という意味です）を含んだ場合と、含まない場合とでは桁数が異なるからと言って「ISERROR(SEARCH("JPY",J3,1))=FALSE」等の様に、IF関数の判定式の中に「=FALSE」を付ける必要は御座いません。 そうなんですね。 ISERROR関数は、必ず「TRUE」または「FALSE」を入れるものだと思っていました。 ということは、IS関連の関数は全て「TRUE」、「FALSE」はいらないんですね。 教えて頂き、ありがとうございます。 月別の稼働日数（取引可能日数）の計算式の件ですが、「DATE(A3,B3+1,0)」と「DATE(A3,B3,DAY(DATE(A3,B3+1,0))とでは、同じ結果となってしまうんですね。 しかしながら、「NETWORKDAYS(開始日,終了日,祭日)」の祭日の部分を「DATE(A3,1,1)」とすると全て1月1日となり除外対象となると思うのですが。 なぜ、元日を除外する式ではないのでしょうか？ また、逸脱してしまい申し訳ございません。 >了解致しました。私が提案した「作りなおしたブック」には、目的のデータが存在していない場合であっても、一部のセルに0等の表示が現れる箇所が御座いますので、質問者様が他の部分の確認を終えられて追加の補足内容等を投稿されるのを待って、修正案を提示したいと思います。 ありがとうございます。 ---------------------------------------------------------------------------------------------------- ある程度の確認を終了しました。 ■確認1： まず、「損益計算」シートを確認しました。 「取引終了日時」の「日本時間」の項目のD行（D3以下）ですが、結果が異なります。 回答者様の回答の計算式、 =IF(ISNUMBER($C3),IF(OR(設定!$I$3="無",AND(IF(ISNUMBER(設定!$K$3),MONTH(DATE(YEAR($C3),設定!$J$3,8-WEEKDAY(YEAR($C3)&"/"&設定!$J$3,FIND(設定!$L$3,"日月火水木金土")+10)+(設定!$K$3-1)*7))=設定!$J$3,設定!$K$3="最終"),IF(ISNUMBER(設定!$P$3),MONTH(DATE(YEAR($C3)+設定!$N$3,設定!$O$3,8-WEEKDAY(YEAR($C3)+設定!$N$3&"/"&設定!$O$3,FIND(設定!$Q$3,"日月火水木金土")+10)+(設定!$P$3-1)*7))=設定!$O$3,設定!$P$3="最終"))),TEXT($C3+(9-IF(IF(設定!$I$3="有",AND($C3>DATE(YEAR($C3),設定!$J$3,8-WEEKDAY(YEAR($C3)&"/"&設定!$J$3+(設定!$K$3="最終"),FIND(設定!$L$3,"日月火水木金土")+10)+(SUM(設定!$K$3)-1)*7)+設定!$M$3,$C3<DATE(YEAR($C3)+設定!$N$3,設定!$O$3,8-WEEKDAY(YEAR($C3)+設定!$N$3&"/"&設定!$O$3+(設定!$P$3="最終"),FIND(設定!$Q$3,"日月火水木金土")+10)+(SUM(設定!$P$3)-1)*7)+設定!$R$3),FALSE),設定!$D$3,設定!$E$3))+0.000001,"yyyy/m/d h:m:s")+0,"変換不能"),"") 以下コピーしました。 「設定」シートの「サーバー時間（GMT±）」の「夏時間」（D3）を「3」、「冬時間」（E3）を「2」、「夏時間の開始・終了日時」の「開始日時」（F3）を「3月第2日曜日2:00」、「終了日時」（G3）を「11月第1日曜日2:00」とした場合ですが、具体的な表示結果を挙げますと、 「損益計算」シートの、 「取引開始日時」「現地サーバー時間」の項目（A3）：2011年1月3日 12:22:00 「取引開始日時」「日本時間」の項目（B3）：2011年1月3日 19:22:00 「取引終了日時」「現地サーバー時間」の項目（C3）：2011年1月3日 19:32:00 「取引開始日時」「日本時間」の項目（D3）：2011年1月10日 19:32:00 と、明らかにD行は異なります。 恐らく、ほかの行列は関係ないと思ったので、 ・「経過時間 時間：分：秒」（E3以下） ・「通貨ペア」（F3以下） ・「開始価格」（G3以下） ・「終了価格」（H3以下） ・「Pips」（I3以下） ・「損益」（J3以下） ・「スワップ等」（K3以下） ・「残高」（L3以下） ・「ロット数」（M3以下） ・「取引数量」（N3以下） ・「ドローダウン比率」（O3以下） ・「連勝」（P3以下） ・「連敗」（Q3以下） を確認しまして全て合っていました。 なお、「損益率」（R3以下）は、不要と判断したためR行は削除しました。 ■確認2： 次に、「ストラテジー評価」シートの確認をしました。 「その他」「経過日数」の項目（D34）が異なっていました。これは、断定はできませんが上記の記載にある「■確認1」の「損益計算」シートの「取引終了日時」「日本時間」の項目のD行（D3以下）に問題があるのではないかと思います。 それに伴い、「収益」「平均獲得pips/日」の項目（D17）の計算式に「経過日数」が含まれているため、合っているかどうかは不明です。 ・「収益」「プロフィットファクター（PF）」「pips」の項目（D22） ・「収益」「調整プロフィットファクター（APF）」「pips」の項目（D24） につきましては回答者様ではなく、自分が作った計算式に誤りがありました。 これらは、絶対値でありマイナスにはなりません。 その他の項目については全て合っていました。 ■確認3： 次に、「月別結果」シートの確認をしました。 このシートについては、 ・「年」（A3以下） ・「月」（B3以下） のみ確認しました。 表示結果としましては、2011年1月から2012年10月まで表示されていたため合っています。 その他の項目は未確認です。 文字数が足りなくなったため、補足コメントで続きを記載します。
補足
お礼コメントより続きです。 ■確認4： 次に、「設定」シートの設定を変更した場合の「損益計算」シートの、 ・「取引開始日時」「現地サーバー時間」の項目（A3以下） ・「取引開始日時」「日本時間」の項目（B3以下） ・「取引終了日時」「現地サーバー時間」の項目（C3以下） ・「取引終了日時」「日本時間」の項目（D3以下） を確認しました。 変更項目は、「夏時間の開始・終了日時」「開始日時」（F3）と「終了日時」（G3）です。 それと、夏季と冬季の節目の時差が正しいか確認しました。 「サーバー時間（GMT±）」「夏時間」（D3）と「冬時間」（E3）は、変更しても影響がないと思ったため変更していません。 「夏時間の開始・終了日時」「開始日時」（F3）と「終了日時」（G3）を下記のように変更しても、いずれの場合も「夏時間」（D3）は、「3」、「冬時間」（E3）は「2」と変更していません。 変更例1： 「夏時間の開始・終了日時」「開始日時」（F3）：3月第2日曜日2:00 「夏時間の開始・終了日時」「終了日時」（G3）：11月第1日曜日2:00 「■確認1」より、D行以外正しかったです。 変更例2： 「夏時間の開始・終了日時」「開始日時」（F3）：3月最終日曜日1:00 「夏時間の開始・終了日時」「終了日時」（G3）：10月最終日曜日1:00 「■確認1」より、D行以外正しかったです。 変更例3： 「夏時間の開始・終了日時」「開始日時」（F3）：10月第1日曜日2:00 「夏時間の開始・終了日時」「終了日時」（G3）：翌年4月第1日曜日3:00 「変更例1～2」と同様、D行に誤りがある上、「取引開始日時」「日本時間」の項目（B3以下）が異なっていました。 変更例3で入力しますと、夏時間の期間は、2010年10月3日2:00～2011年4月3日3:00となるんですが、具体的な例を挙げますと表示結果は下記のようになりました。 「取引開始日時」「現地サーバー時間」の項目（A3）：2011年1月3日 12:22:00 「取引開始日時」「日本時間」の項目（B3）：2011年1月3日 19:22:00 と、夏時間の期間内では日本時間（GMT+9）、「設定」シートの「サーバー時間（GMT±）」「夏時間」の項目D3「3」（GMT+3）で設定しており、 「取引開始日時」「日本時間」の項目（B3）は、「取引開始日時」「現地サーバー時間」の項目（A3）の「+6時間」となり、 本来、「取引開始日時」「日本時間」の項目（B3）は、「2011年1月3日 18:22:00」となるはずが、「+7時間」の表示結果となり、冬季の期間内の時刻でした。 以上で確認終了しました。 また、「目的のデータに表示がない場合、絶対に表示させたくないという個人的な好き嫌いの問題です。」という件についてですが、 自分としましては最初「=IF(元データ!A3="",""」としましたが、回答者様に最適な方法があればそれに変えて頂きたいと思います。 また、ご苦労をおかけしますが回答よろしくお願いします。
- kagakusuki
- ベストアンサー率51% (2610/5101)
>本当は「月別結果」シートのデータを全て日本時間を基準としたいんです。 > 類似したファイルがあり、それは元データから日本時間なので計算は必要ないのですが、この元データはサーバー時間のもなので日本時間に統一しようと思いました。 >また、このファイルを元に作り直すということになりますと大変な作業となるような気がするのですが。 >お二方のファイルを別々に作りたいと思います。 との事でしたので、御質問の件の範囲を逸脱してしまいますが、「損益計算」シートにおいて「サーバー時間」や「損益」等を始めとする、1列にまとめた方が良さそうなもの等関しては1列にまとめて表示する様にした上で、「月別結果」シートに関しても、日本時間を基準にした結果を表示させる様にBookを全面的に作り変えてみました。 その作り変えを行う過程において、質問者様が作られたブックには何故その様な事になっているのか意味が良く理解出来ない点が多々見つかりましたので、作り変えたブックの内容を説明する前に、それらの意味不明な点に関して先に御報告させて頂きます。 質問者様が作られた関数ではIF関数の判定式で、 =TRUE とか =FALSE 等といった無駄な判定が随所に見られますが、 =TRUE は削除した処で動作には影響御座いませんし、 =FALSE 等とはせずとも、単にORとANDを入れ替えた上で、IF関数の真の場合と偽の場合を入れ替えれば済む話であるというのに、何故 =TRUE や =FALSE 等といった無駄な判定を設ける事で、態々解り難くしておられるのでしょうか? 「損益計算」シートの「Pips」欄と「損益」欄はそれぞれ「利益」と「損失」を態々別の列に分けて表示しているのは何故なのでしょうか? 1つの列に纏めて表示する様にして、「利益」と「損失」の区別は、値が＋であるのか－であるのかで(人間が)判断する様にするのでは駄目なのでしょうか? 「損益計算」シートの「ドローダウン」欄の「金額」の値は、「損益」欄の「損失」の値の絶対値と全く同じ値(←入力されている関数から考えて、必ずそうなる)で、「損益」欄の「損失」の値を見れば済む話であるというのに、何故その様な項目を作って表示させているのでしょうか? 質問者様が作られた「損益計算」シートの「損益率」欄の関数は =IF(A3="","",IF(O3>=0,R3/設定!$A$3-1,-(1-R3/設定!$A$3))) となっておりましたが、 R3/設定!$A$3-1 と -(1-R3/設定!$A$3) は同じ値なのですから、O3>=0の場合とO3<0の場合とで返される値は何ら変わりがないというのに、何故 IF(O3>=0, を使っておられるのでしょうか? それと、4行目においても =IF(A4="","",IF(O4>=0,R4/設定!$A$3-1,-(1-R4/設定!$A$3))) という具合に分母が設定!$A$3になっておりましたが、これでは何年たっても分母は変わらず、「過去からその時点まで行った全ての取引の損益額の総計」が「その取引の事業に参入した当初の残高」に対して何パーセントになるのかという値を求めている事になりますが、質問者様が所属されておられる部署のやり方ではそれで宜しいのでしょうか? 損益率とは「前回の残高」に対する「今回の損益額」の比ではないのでしょうか? 「質問者様が作られた月別結果」シートの「月利」欄の関数で、IF関数の「偽の場合」にどの様な処理を行うのかという事が指定されていない箇所があるのは何故なのでしょうか? 質問者様が作られた「月別結果」シートの「最終日」欄の関数が =IF(OR(A3="",B3=""),"",LOOKUP(DATE(A3,B3+1,-1),損益計算!AA:AA)) となっていますが、例えばA3の値が2011でB3の値が1の場合、DATE(A3,B3+1,-1)の部分が返す値は2011/1/30に相当するシリアル値になりますので、もしもサーバー時間で2011/1/31の日に行われた取引があった場合でも、質問者様の関数では2011/1/31が反映される事はありません。 又、もしもサーバー時間で2011/1/30 0:00:01の日時に行われた取引があった場合でも、2011/1/30 0:00:01は2011/1/30よりも1秒だけ大きな値なのですから、質問者様の関数では2011/1/30 0:00:01以降に行われた取引終了日時が反映される事はありません。 又、元データが取引開始日時順に並んでいる場合、取引に要した時間である保有期間の長さによっては、先に始められた取引が終了した日時が、後から始められた取引が終了した日時よりも遅い日時になるという可能性もあるのですから、取引終了日時は必ずしも昇順に並んでいるとは限りません。 そのため、取引終了の月ごとの最終日時をLOOKUP関数で求めた場合、誤った時刻の値が返される恐れがあります。 質問者様が作られた「ストラテジー評価」シートの関数が =IF(元データ!A3="","", で始まっているのは何の意味があるのでしょうか? これでは例え元データ!A4以下にチケット番号が入力されている行があった場合でも、元データ!A3だけが空欄になっていれば結果を表示させる事が出来ません。 或いはもしかしますと、元データ!A3が空欄である場合には、必ず他の行も空欄になっている事が決まっているので、他の行は考慮しなくても良いという事なのでしょうか? しかし、もしそうであるのなら、そもそも元データ!A3が空欄であるという事は、即ち表示すべきデータが全く無いという事なのですから、その様な場合の結果は要するに白紙という事であり、その様な結果など誰も見る必要などないのですから、態々表示を空欄とする意味も無いと思います。 それから「ストラテジー評価」シートの「トレード回数/月」欄と「リライアビリティー・ファクター」欄に入力されていた関数では、1ヶ月あたりの値を求めるために、「サーバーが置かれている場所における平日(取引場所が休日ではない日)の経過日数」ではなく「日本における平日の経過日数」で割った値に21.681を掛けている様ですが、その求め方が正しいものであるのか否か、私には何とも判断が付きません。 上記の様な数々の不明な点が御座いますので、この次の回答で御伝えする予定の「作りなおしたブック」は、単に「質問者様がアップロードされた『MT損益計算 - テスト』Bookに表示される数値」と同様の結果を日本時間を基準に算出する様にしているというだけのものに過ぎません。(「月別結果」シートの「最終日」欄の件だけは修正する事が出来ました) ですから、質問者様がアップロードされた「MT損益計算 - テスト」Bookにおける各値の求め方の中に、もしも間違った求め方をしているものがある場合には、この次の回答で御伝えする予定の「作りなおしたブック」においても同じ間違いがそのまま引き継がれる事になりますので、その点は注意して下さい。
お礼
回答ありがとうございます。 >との事でしたので、御質問の件の範囲を逸脱してしまいますが、「損益計算」シートにおいて「サーバー時間」や「損益」等を始めとする、1列にまとめた方が良さそうなもの等関しては1列にまとめて表示する様にした上で、「月別結果」シートに関しても、日本時間を基準にした結果を表示させる様にBookを全面的に作り変えてみました。 ご指摘の通り本質問とは異なりますが、「損益計算」シートの整理を行いますとその他のシートに影響が出てしまうためお伺いしました。 本質問で評価し、いったん閉じ新規に質問するという方法の方が本来はよいのでしょうが。 本質問でも長期間にわたってしまったにも関わらず、関連性があるとは言え、本質問とは異なることでさらに長引いてしまったことに対しまして改めてお詫び申し上げます。 >質問者様が作られた関数ではIF関数の判定式で、 =TRUE とか =FALSE 等といった無駄な判定が随所に見られますが、 =TRUE は削除した処で動作には影響御座いませんし、 =FALSE 等とはせずとも、単にORとANDを入れ替えた上で、IF関数の真の場合と偽の場合を入れ替えれば済む話であるというのに、何故 =TRUE や =FALSE 等といった無駄な判定を設ける事で、態々解り難くしておられるのでしょうか? これは「損益計算」シートの、 M3： =IF(元データ!A3="","",IF(OR(AND(元データ!C3="sell",K3-L3>=0),AND(元データ!C3="buy",K3-L3<=0)),IF(ISERROR(SEARCH("JPY",J3,1))=FALSE,ABS(ROUND(K3-L3,3)*100),ABS(ROUND(K3-L3,5)*10000)),"")) N3： =IF(元データ!A3="","",IF(OR(AND(元データ!C3="sell",K3-L3<0),AND(元データ!C3="buy",K3-L3>0)),IF(ISERROR(SEARCH("JPY",J3,1))=FALSE,-(ABS(ROUND(K3-L3,3)*100)),-(ABS(ROUND(K3-L3,5)*10000))),"")) W3： =IF(元データ!A3="","",IF(ISNUMBER(O3)=TRUE,1,"")) W4： =IF(元データ!A4="","",IF(AND(ISNUMBER(O4)=TRUE,W3>0),IF(W3="",0,W3)+1,"")) 以下コピー。 X3： =IF(元データ!A3="","",IF(ISNUMBER(P3)=TRUE,1,"")) X4： =IF(元データ!A4="","",IF(AND(ISNUMBER(P4)=TRUE,X3>0),IF(X3="",0,X3)+1,"")) 以下コピー。 「月別結果」シートの、 Q3： =IF(OR(A3="",B3=""),"",IF(OR(AND(ISNUMBER(設定!$A$3)=FALSE,ISNUMBER(P3)=FALSE),AND(ISNUMBER(設定!$A$3)=TRUE,ISNUMBER(P3)=FALSE)),"",IF(AND(ISNUMBER(設定!$A$3)=FALSE,ISNUMBER(P3)=TRUE),IF(設定!$A$3<P3,P3/設定!$A$3-1,-(1-設定!$A$3/P3)),IF(AND(ISNUMBER(設定!$A$3)=TRUE,ISNUMBER(P3)=TRUE),IF(設定!$A$3<P3,P3/設定!$A$3-1,-(1-P3/設定!$A$3)))))) のことでしょうか。 「損益計算」シートの、M3、N3の「ISERROR(SEARCH("JPY",J3,1))=FALSE」という部分は、「JPY」（日本円という意味です）を含んだ場合と、含まない場合とでは桁数が異なるため計算式に入っています。 「JPY」を含んだ場合、0.01（小数点以下二桁目）で1pips、「JPY」を含まない場合、0.0001（小数点以下四桁目）で1pipsと、「JPY」があるかないかで1pipsが異なりますので計算式に入れています。 「pips」とは日本円で言うところの「銭」（0.01円＝1pips）に当たります。日本円以外は「銭」という単位がありませんので「pips」という単位となります。 「損益計算」シートの、W3、W4（以下のセル）、X3、X4（以下のセル）は、目的のデータに表示がない場合、絶対に表示させたくないという個人的な好き嫌いの問題です。効率は考えていません。 W4以下のセルの計算式は、同列のO4にデータが表示されていた場合、一つ上のセル、この場合W3ですが、それに1を加え連勝回数を計算しています。 X4以下のセルの計算式についてもW行と同様の考え方です。 「月別結果」シートのQ3についても、個人的な好き嫌いの問題です。 >「損益計算」シートの「Pips」欄と「損益」欄はそれぞれ「利益」と「損失」を態々別の列に分けて表示しているのは何故なのでしょうか? 1つの列に纏めて表示する様にして、「利益」と「損失」の区別は、値が＋であるのか－であるのかで(人間が)判断する様にするのでは駄目なのでしょうか? これらは、「損益計算」シートの「Pips」欄と「損益」欄を元に、「月別結果」シートの「Pips」の項目の「利益」（C3以下のセル）、「損失」（D3以下のセル）、「金額」の項目の「利益」（M3以下のセル）、「損失」（N3以下のセル）に月別で合計を求める計算となっています。 まとめられるなら、まとめて「月別結果」シートの各項目に反映させたかったのですが、以前述べたようにExcel関数の知識が乏しいため全て別々にしました。 この部分に関しましては、「月別結果」シートの項目にも考慮し、まとめて頂きたいです。 >「損益計算」シートの「ドローダウン」欄の「金額」の値は、「損益」欄の「損失」の値の絶対値と全く同じ値(←入力されている関数から考えて、必ずそうなる)で、「損益」欄の「損失」の値を見れば済む話であるというのに、何故その様な項目を作って表示させているのでしょうか? はい、全くその通りです。 この行は削除していいです。 >質問者様が作られた「損益計算」シートの「損益率」欄の関数は =IF(A3="","",IF(O3>=0,R3/設定!$A$3-1,-(1-R3/設定!$A$3))) となっておりましたが、 R3/設定!$A$3-1 と -(1-R3/設定!$A$3) は同じ値なのですから、O3>=0の場合とO3<0の場合とで返される値は何ら変わりがないというのに、何故 IF(O3>=0, を使っておられるのでしょうか? 本当ですね。 今となっては、自分でもなぜそうしたのか分かりませんが「R3/設定!$A$3-1」だけでいいですね。 >それと、4行目においても =IF(A4="","",IF(O4>=0,R4/設定!$A$3-1,-(1-R4/設定!$A$3))) という具合に分母が設定!$A$3になっておりましたが、これでは何年たっても分母は変わらず、「過去からその時点まで行った全ての取引の損益額の総計」が「その取引の事業に参入した当初の残高」に対して何パーセントになるのかという値を求めている事になりますが、質問者様が所属されておられる部署のやり方ではそれで宜しいのでしょうか? 損益率とは「前回の残高」に対する「今回の損益額」の比ではないのでしょうか? そもそも「損益計算」シートの「損益率」（Y3以下のセル）の項目は他のシートの計算式に一切入っていませんでした。 作成した当初は何らかの目的で作ったんだとは思いますが、「損益計算」シートの「損益率」の項目であるY行自体不要なものでした。 「損益計算」シートの「損益率」の項目であるY行は削除していいです。 >「質問者様が作られた月別結果」シートの「月利」欄の関数で、IF関数の「偽の場合」にどの様な処理を行うのかという事が指定されていない箇所があるのは何故なのでしょうか? 「月別結果」シートの「月利％」のQ3のことですね。 これは何度調べてもよく分からないのですが。 どこの計算式の部分か教えてください。 恐らくなのですが、大元のデータから全て計算できるという前提で作っていますので「偽の場合」というのを計算に入れていないのだと思われます。
補足
文字数が足りないため、補足コメントから続きを記載します。 >質問者様が作られた「月別結果」シートの「最終日」欄の関数が =IF(OR(A3="",B3=""),"",LOOKUP(DATE(A3,B3+1,-1),損益計算!AA:AA)) となっていますが、例えばA3の値が2011でB3の値が1の場合、DATE(A3,B3+1,-1)の部分が返す値は2011/1/30に相当するシリアル値になりますので、もしもサーバー時間で2011/1/31の日に行われた取引があった場合でも、質問者様の関数では2011/1/31が反映される事はありません。 又、もしもサーバー時間で2011/1/30 0:00:01の日時に行われた取引があった場合でも、2011/1/30 0:00:01は2011/1/30よりも1秒だけ大きな値なのですから、質問者様の関数では2011/1/30 0:00:01以降に行われた取引終了日時が反映される事はありません。 又、元データが取引開始日時順に並んでいる場合、取引に要した時間である保有期間の長さによっては、先に始められた取引が終了した日時が、後から始められた取引が終了した日時よりも遅い日時になるという可能性もあるのですから、取引終了日時は必ずしも昇順に並んでいるとは限りません。 そのため、取引終了の月ごとの最終日時をLOOKUP関数で求めた場合、誤った時刻の値が返される恐れがあります。 これは、計算が間違っていることは分かっています。自分でいろいろ試している途中でした。 「月別結果」シートで月別の「開始日」、「最終日」の項目を設け、その「開始日」から「最終日」までの間で実際に取り引きした「日数」を計算したかったのですが。 申し訳ありませんが、「月別結果」シートで月別の「開始日」と、実際の取り引きした「日数」の計算式を追加していただけないでしょうか？ それと、土曜日・日曜日・元日以外は稼働日数（取引可能日数）となるのですが、NETWORKDAYS関数で求められることは知っています。 その場合、月別の稼働日数（取引可能日数）の計算式は下記の通りで合っているでしょうか？ =IF(A3="","",NETWORKDAYS(DATE(A3,B3,1), DATE(A3,B3,DAY(DATE(A3,B3+1,0))), DATE(A3,1,1))) 以下コピー。 >質問者様が作られた「ストラテジー評価」シートの関数が =IF(元データ!A3="","", で始まっているのは何の意味があるのでしょうか? これでは例え元データ!A4以下にチケット番号が入力されている行があった場合でも、元データ!A3だけが空欄になっていれば結果を表示させる事が出来ません。 或いはもしかしますと、元データ!A3が空欄である場合には、必ず他の行も空欄になっている事が決まっているので、他の行は考慮しなくても良いという事なのでしょうか? しかし、もしそうであるのなら、そもそも元データ!A3が空欄であるという事は、即ち表示すべきデータが全く無いという事なのですから、その様な場合の結果は要するに白紙という事であり、その様な結果など誰も見る必要などないのですから、態々表示を空欄とする意味も無いと思います。 これは、単に統一したいという性格からです。見落としたりするのですが。 基本的に何でも統一したい性分なんです。例えば同じものを揃えたい、色など一つにまとめたい、一つでも違うものがあるとあまり好きではないんです。 それと、「元データ」シートのA3に入力しないということはほぼあり得ないです。自分は必ずA3からデータを入力するようにするためです。 ですがこれは、もっと効率的な方法があればそれに越したことはありません。 特に具体的にこうしてほしいというのはないのですが、回答者様に良い方法があれば全面的に任せます。 任せっきりになり、大変申し訳ないのですが。 >それから「ストラテジー評価」シートの「トレード回数/月」欄と「リライアビリティー・ファクター」欄に入力されていた関数では、1ヶ月あたりの値を求めるために、「サーバーが置かれている場所における平日(取引場所が休日ではない日)の経過日数」ではなく「日本における平日の経過日数」で割った値に21.681を掛けている様ですが、その求め方が正しいものであるのか否か、私には何とも判断が付きません。 「ストラテジー評価」シートの「トレード回数/月」と「リライアビリティー・ファクター」に関しましては、本来は大元のデータ数が多いほど当然ながら信頼性が高まります。最低でも1ヶ月分のデータ数が必要です。 この「21.681」という数値は、大元のデータ数が足りなかった場合「予測の数値」でしかなく、苦肉の策で考えたのですが「ストラテジー評価」シートの「経過日数」（D34）が21以下の場合は空白にするなどした方がよいかもしれません。 この計算式につきましては自分で考えます。 なので、回答者様が考えなくてもいいです。 ただ「経過日数」（D34）は、自分では分からなかったため他の方に計算してもらいましたが。 >上記の様な数々の不明な点が御座いますので、この次の回答で御伝えする予定の「作りなおしたブック」は、単に「質問者様がアップロードされた『MT損益計算 - テスト』Bookに表示される数値」と同様の結果を日本時間を基準に算出する様にしているというだけのものに過ぎません。(「月別結果」シートの「最終日」欄の件だけは修正する事が出来ました) ですから、質問者様がアップロードされた「MT損益計算 - テスト」Bookにおける各値の求め方の中に、もしも間違った求め方をしているものがある場合には、この次の回答で御伝えする予定の「作りなおしたブック」においても同じ間違いがそのまま引き継がれる事になりますので、その点は注意して下さい。 はい、分かりました。 ありがとうございます。 まだ、回答者様の他の回答は見ていないのですが、よろしくお願いします。
- bunjii
- ベストアンサー率43% (3589/8249)
>F3：10月最終日曜日2:00 >G3：翌年3月最終日曜日2:00 >というような表記で、「翌年」を入れてください。 「損益計算」シートのAC3とAD3には対象年のSummer Timeの開始日と終了日を算出するのですが、暦年では南半球についてSummer Timeの終了日が当年分と翌年分が必要になります。 従って、AE列に当年のSummer Time終了日を算出し、チェック項目を追加する必要があるようです。 AC3=IF($A3="","",IF(COUNTIF(設定!F$3,"*最終*"),EOMONTH(DATE($A3,LEFT(設定!F$3,FIND("月",設定!F$3)-1),1),0)-WEEKDAY(EOMONTH(DATE($A3,LEFT(設定!F$3,FIND("月",設定!F$3)-1),1),0))+1,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)) AD3=IF($A3="","",IF(COUNTIF(設定!G$3,"*最終*"),EOMONTH(DATE($A3+COUNTIF(設定!G$3,"翌年*"),SUBSTITUTE(LEFT(設定!G$3,FIND("月",設定!G$3)-1),"翌年",""),1),0)-WEEKDAY(EOMONTH(DATE($A3+COUNTIF(設定!G$3,"翌年*"),SUBSTITUTE(LEFT(設定!G$3,FIND("月",設定!G$3)-1),"翌年",""),1),0))+1,DATE($A3+COUNTIF(設定!G$3,"翌年*"),SUBSTITUTE(LEFT(設定!G$3,FIND("月",設定!G$3)-1),"翌年",""),7*MID(SUBSTITUTE(設定!G$3,"翌年",""),FIND("第",SUBSTITUTE(設定!G$3,"翌年",""))+1,1))-WEEKDAY(DATE($A3+COUNTIF(設定!G$3,"翌年*"),LEFT(SUBSTITUTE(設定!G$3,"翌年",""),FIND("月",SUBSTITUTE(設定!G$3,"翌年",""))-1),7*MID(SUBSTITUTE(設定!G$3,"翌年",""),FIND("第",SUBSTITUTE(設定!G$3,"翌年",""))+1,1)))+1)) AE3=IF(COUNTIF(設定!G$3,"翌年*"),IF(COUNTIF(設定!G$3,"*最終*"),EOMONTH(DATE($A3,SUBSTITUTE(LEFT(設定!G$3,FIND("月",設定!G$3)-1),"翌年",""),1),0)-WEEKDAY(EOMONTH(DATE($A3,SUBSTITUTE(LEFT(設定!G$3,FIND("月",設定!G$3)-1),"翌年",""),1),0))+1,DATE($A3,SUBSTITUTE(LEFT(設定!G$3,FIND("月",設定!G$3)-1),"翌年",""),7*MID(SUBSTITUTE(設定!G$3,"翌年",""),FIND("第",SUBSTITUTE(設定!G$3,"翌年",""))+1,1))-WEEKDAY(DATE($A3,LEFT(SUBSTITUTE(設定!G$3,"翌年",""),FIND("月",SUBSTITUTE(設定!G$3,"翌年",""))-1),7*MID(SUBSTITUTE(設定!G$3,"翌年",""),FIND("第",SUBSTITUTE(設定!G$3,"翌年",""))+1,1)))+1),0) Z3=IF(A3="","",DATE(A3,B3,C3)+D3+(9-設定!$D$3+IFERROR(((DATE(A3,B3,C3)+D3)>AC3)*((DATE(A3,B3,C3)+D3)<AD3)+((DATE(E3,F3,G3)+H3)<AE3),0))/24) AA3=IF(A3="","",DATE(E3,F3,G3)+H3+(9-設定!$D$3+IFERROR(((DATE(E3,F3,G3)+H3)>AC3)*((DATE(E3,F3,G3)+H3)<AD3)+((DATE(E3,F3,G3)+H3)<AE3),0))/24) 各セルの数式は解読が困難かも知れません。 論理演算を多用していますので、括弧内の数式が何を意味しているか考えれば全体の数式を理解できると思います。 例えばZ3のIFERROR関数の引数である次の数式は ((DATE(A3,B3,C3)+D3)>AC3)*((DATE(A3,B3,C3)+D3)<AD3)+((DATE(E3,F3,G3)+H3)<AE3) サーバーのタイムスタンプ[DATE(A3,B3,C3)+D3)]がSummer Timeの開始日[AC3]より大きく、タイムスタンプがSummer Timeの終了日[AD3]より小さいか、またはタイムスタンプが南半球の場合の本年のSummer Timeの終了日[AE3]より小さいとき1を返し、それ以外は0を返します。 論理式同士を乗算するとANDとなり、加算するとORになります。 論理演算の結果を数値化するとFALSEが0でTRUEは1になります。 関数の引数についてはヘルプで確認できますので、関数毎の数式の構成を確認してください。
お礼
回答ありがとうございます。 お礼が遅れましたことをお詫び申し上げます。 サーバー時間の夏季と冬季で、日本時間とした場合、合っているかどうかですが「設定」シートでLocal Timeの項目がないと確認しようがありません。 回答No.5のお礼コメントより（また、回答No.6の補足コメントにわずかに記載してるのですが）記載しているのですが、 夏時間：GMT＋3 冬時間：GMT＋2 夏時間の期間：3月第2日曜日2:00～11月第1日曜日2:00 冬時間の期間：11月第1日曜日2:00～翌年3月第2日曜日2:00 業者のサーバーがある地域＝そのサーバーの地域の夏時間の期間とはならないからです（その業者が採用している夏時間の期間が異なるためです。）。 上記の記載にある項目は、自分が問い合わせた先であり、上記の項目を確認しましたので間違いないです。 そのため、やはり「設定」シートの項目にLocal Timeが必要です。 「設定」シートのE3にLocal Timeの入力項目を設定し、計算式に追加しなければなりません。 日本時間としての夏時間の開始・終了日時は基本的に合っていました。 AC3、AD3、AE3、Z3、AA3の計算式をそのままコピーし、それぞれ以下のセルをそのままコピーしました。 「設定」シートのF3（日本時間としての夏時間の開始日時）、G3（日本時間としての夏時間の終了日時）は、 例1： F3：3月第2日曜日2:00 G3：11月第1日曜日2:00 例2： F3：3月最終日曜日1:00 G3：10月最終日曜日1:00 例3： F3：10月第1日曜日2:00 G3：翌年4月第1日曜日3:00 と、それぞれの場合で入力しました。 「設定」シートのD3（夏時間：GMT±）は全て「3」としました。現実に存在するのか不明ですが、結果が正しければ問題ありません。 大元となるデータは、「MT損益計算用データ.xlsx」を使用しました。 上記の例1～3の「損益計算」シートのAC、AD、AE列の結果は、 例1： 夏時間開始日時：2011年3月13日 0:00 夏時間終了日時：2011年11月6日 0:00 夏時間開始日時：2012年3月11日 0:00 夏時間終了日時：2012年11月4日 0:00 例2： 夏時間開始日時：2011年3月27日 0:00 夏時間終了日時：2011年10月30日 0:00 夏時間開始日時：2012年3月25日 0:00 夏時間終了日時：2012年10月28日 0:00 例3： 夏時間開始日時：2011年10月2日 0:00 夏時間終了日時：2011年4月3日 0:00 夏時間開始日時：2012年10月7日 0:00 夏時間終了日時：2012年4月1日 3:00 となり、時刻は異なりますが日曜日のため問題ないです。 大変申し訳ありませんが、再度回答よろしくお願いします。 よろしくお願いします。
- kagakusuki
- ベストアンサー率51% (2610/5101)
>>冬時間の項目がなくても計算式は可能なんですね。 >Local Timeの時差とSummer Timeの開始日時と終了日時が分かればサーバーのタイムスタンプをJST（日本の標準時）に変換できます。 という話は、あくまでも夏時間と冬時間の差が一定である場合の話です。 常識的には 冬時間＝夏時間-1時間 となる筈ですから、その関係がどこのサーバーでも成り立っているという事であれば確かに冬時間の項目がなくても計算式は可能という事になります。 しかしながら、回答No.7に対する質問者様のお礼には >つまり、冬時間が必ずしも夏時間-1時間であるということではない可能性もあるかもしれません。 >これも、計算に考慮していただけないでしょうか？ とあります。 又、回答No.8に対する質問者様のお礼にも >>・冬時間中のサーバー時間の日時は、同日時における夏時間中のサーバー時間に相当する日時に対して必ず1時間遅れの日時となる >つまり、冬時間が必ずしも夏時間-1時間であるということではない可能性もあるかもしれません。 >これも、計算に考慮しなければならないです。 とあります。 その様な可能性も考慮に入れなければならないとした場合には、やはり冬時間の項目は必要不可欠という事になります。 ですから、質問者様が「冬時間が必ずしも夏時間-1時間であるということではない可能性」を考慮に入れるべきか、それとも入れなくとも良いとするのかによって状況が変わって参りますので、どちらにされるのかを明確にして頂きたい処です。(もし「設定」シートのE列に入力されている「GMTとサーバーの冬時間の時差」の値を使わなくとも良いと質問者様が判断されるのであれば、現在私が検討中の関数も修正しなければならなくなりますので) >つまり、「元データ」シートのデータに誤りがあったわけではなく、「月別結果」シートのB3以下の計算式に誤りがあったわけです。 これは私のミスで御迷惑をおかけしてしまい誠に申し訳御座いません。 つまり、質問No.8355796の関数を使って「損益計算」シートのA～B列に入力されている年と月を基準にデータを纏めているという事は、「月別結果」シートとは「損益計算」シートのデータを、日本時間における月ではなく、サーバー時間における月を基準として、1ヶ月ごとに纏めたものという事なのでしょうか? 例えば、サーバー時間がGMT+2の場合、サーバー時間は日本時間よりも11時間遅れになりますから、サーバー時間で月末の13時以降に行われた取引は、日本時間では翌月に行われた取引という事になりますが、サーバー時間を基準に考えるため、翌月の1日ではなく、その月の月末に行われた取引として処理するという訳ですね。 >>このサイトでは、投稿者自身で内容の変更が可能なWebスペース上にアップロードされたファイルのURLを記載しての投稿は禁止されておりますので、情報を提示する際にその様な方法を使う事はなるべく避けた方が宜しいのではないかと思います。 >このようなご指摘を受けました。 >このサイトの管理者の方は大目に見てくれて削除しなかったみたいですね。 このサイトに投稿されてくる質問の数は膨大なものですから、サイトの管理者もその全てをチェックし切る事は事実上不可能ですので、大目に見てくれたのではなく、未だ気づいていないだけであるという可能性の方が高いと思います。 基本的に質問や回答、お礼、補足、等に関して、サイトのユーザーから違反であるとする通報が管理者へと送られなければ、管理者が気付く確率は比較的低いと思われます。 ですから、今後、他のユーザーが偶々この質問を見た際に、問題の箇所を違反であると判断して管理者に通報してしまい、後になってから削除されてしまうという恐れもあります。 >また、このサイトの管理者の方に要望があります。違法的・不道徳的な行為は問題外ですが、「アップロードしたファイルのURLを記載しての投稿」について許可というか、改善できないでしょうか。 >改善を期待しつつ、管理者の方にはご負担をおかけしますがどうかよろしくお願いします。 前述しました様に、ここでその様な事を書かれても管理者の目に留まる確率は非常に低いと思われます。 管理者に何か御要望があるのでしたら、このサイトのページの下の方にある「FAQ(よくある質問)」へのリンクをクリックし、現れた多数のFAQの中から適当なものを選んでそのページを開きますと、そのページの下の方に「お問い合わせはこちら」というボタンが現れますので、そのボタンをクリックして、必要な項目と問い合わせ内容を入力された上でサイトの管理者に送信されると宜しいかと思います。 尤も、質問者様が今回求めておられる御要望が叶えられる可能性は殆ど無いと思います。 質問者様は失念しておられる様ですが、アップロードが禁止されているのは、質問者様の個人情報の流失だけの問題では御座いません。 仮の話として、サイトの管理者が、不特定多数の質問がアップロードされたファイルをダウンロードする事を許可したとします。 不特定多数の質問者が対象となりますので、中にはウイルスに感染したファイルをアップロードするユーザーも居るかも知れません。 ウイルスの中には、感染させたパソコンに記録されている、個人情報を始めとする様々なデータを流出させる事を目的に作成されたウイルスというものもあり、その様なウイルスを感染させる目的で質問を投稿して、別サイト上にウイルス入りのファイルをアップロードする輩も現れる事でしょう。(その様な輩の中には、パソコンに疎いふりをして回答者を油断させようとする輩もいるかも知れません) その様なファイルをダウンロードする事によって、回答者のパソコンがウイルスに感染してしまう危険がある以上、サイトの管理者には、アップロードによる情報提供を許可した事によって、回答者に損害が発生する危険有ったのにそれを放置したという責任が発生します。 しかしながら、他のサイトにアップロードされたファイルをこのサイトの管理者がチェックして削除する事など不可能なのですから、アップロードによる情報提供を許可するという事は、管理者が管理する事が出来ない範囲にまで責任を負うという事になります。 そんな事にまで責任を負う訳には行かないと思われますので、アップロードが解禁される事はおそらく無いと思います。 質問者様が回答者に対してアップロードしたファイルを見るように求めるという事は、回答者に対してウイルスに感染する危険を冒せと要求しているのも同然の行為なのです。 >「MT損益計算 - テスト.xlsx」の「月別結果」シートには「利益」や「損失」などの項目があります。 >同一の列に、日本時間としての同年・同月の開始日から終了日に「利益」などの項目に合計を表示したいのですが。 それでは何故、「月別結果」シートに表示される月のデータを求めるのに、「損益計算」シートのA～B列のデータを使用しておられるのか意味が解りません。 「月別結果」シートはサーバー時間の月を基準に作られているというのに、何故、「日本時間としての同年・同月」のデータを表示させたいと仰るのでしょうか? >非常に長い計算式が入っていて開くのにも時間がかかります。 それでしたら、私が挙げた「元データ」シートのデータから全て計算するという方法では、どうしても1つの関数の中で1度やった同じ計算を複数回やり直す事になりますので、やはり「損益計算」シート上の他の列に表示されているデータを利用する方式の方が宜しいかと思います。 但し、サーバー時間を「年」、「月」、「日」、「時刻」に分けて表示させたりする等といった無駄な表示をしますと、その分余計な計算をしなければならなくなりますので、その様な必要のないものまで表示するのはなるべく避けて必要な情報だけを整理して表示させる様にすべきかと思います。(作業列として必要な場合は別ですが、サーバー時間を「年」、「月」、「日」を作業列として分ける意味はあまりないと思います) 上記の整理して無駄な表示を無くするという件や、「冬時間が必ずしも夏時間-1時間であるということではない可能性」を考慮に入れるべきか否かという件を始めとして、まだはっきりとはしていない点が幾つか御座いますので、具体的な関数を提示する事は現時点では保留にさせて頂きます。
お礼
回答ありがとうございます。 >これは私のミスで御迷惑をおかけしてしまい誠に申し訳御座いません。 つまり、質問No.8355796の関数を使って「損益計算」シートのA～B列に入力されている年と月を基準にデータを纏めているという事は、「月別結果」シートとは「損益計算」シートのデータを、日本時間における月ではなく、サーバー時間における月を基準として、1ヶ月ごとに纏めたものという事なのでしょうか? 例えば、サーバー時間がGMT+2の場合、サーバー時間は日本時間よりも11時間遅れになりますから、サーバー時間で月末の13時以降に行われた取引は、日本時間では翌月に行われた取引という事になりますが、サーバー時間を基準に考えるため、翌月の1日ではなく、その月の月末に行われた取引として処理するという訳ですね。 いえ、大丈夫ですよ。 この計算式がなかったら自分には分からなかったわけですし、どのようなことでも言えることですが複雑になればなるほどミスする可能性が高まります。人間なんですから。 まぁ、取り返しのつかないミスという場合もありますが、この場合はそれほど深刻なものではないので大丈夫です。 そうですね、「月別結果」シートはサーバー時間を基準として1ヶ月ごとにまとめたものです。 それを日本時間を基準としてまとめたいのです。本質問とは異なりますが、関連性はあるんです。 回答者様の例に挙げているサーバー時間が夏時間でGMT+2とすると、日本時間はGMT+9なので夏時間の期間内であれば「9-2」で、日本時間とサーバー時間の時差は7時間で日本時間より7時間遅れということになります。 >このサイトに投稿されてくる質問の数は膨大なものですから、サイトの管理者もその全てをチェックし切る事は事実上不可能ですので、大目に見てくれたのではなく、未だ気づいていないだけであるという可能性の方が高いと思います。 基本的に質問や回答、お礼、補足、等に関して、サイトのユーザーから違反であるとする通報が管理者へと送られなければ、管理者が気付く確率は比較的低いと思われます。 ですから、今後、他のユーザーが偶々この質問を見た際に、問題の箇所を違反であると判断して管理者に通報してしまい、後になってから削除されてしまうという恐れもあります。 なるほど。 管理者の方は、気付いていない可能性が高いんですか。 >管理者に何か御要望があるのでしたら、このサイトのページの下の方にある「FAQ(よくある質問)」へのリンクをクリックし、現れた多数のFAQの中から適当なものを選んでそのページを開きますと、そのページの下の方に「お問い合わせはこちら」というボタンが現れますので、そのボタンをクリックして、必要な項目と問い合わせ内容を入力された上でサイトの管理者に送信されると宜しいかと思います。 要望の問い合わせ先を教えてくださりありがとうございます。 その下記の記載を読みましたが、そういう理由でアップロードしたファイルのURLを記載することを禁じているんですね。 ですが、非常に残念です。伝わりにくいことも動画などで説明すると理解しやすくなると思うのですが。 このサイトが無料であることも禁じている理由の一つである可能性が高いですね。 セキュリティソフトみたいに常に新しいウイルスに対しても自動検知する機能を付ければ比較的安心できると思うのですが。しかも安価なもので。理想ですが。 ということは、自分のことを信じてくださりダウンロードしてくれたんですね。ありがとうございます。 >それでは何故、「月別結果」シートに表示される月のデータを求めるのに、「損益計算」シートのA～B列のデータを使用しておられるのか意味が解りません。 「月別結果」シートはサーバー時間の月を基準に作られているというのに、何故、「日本時間としての同年・同月」のデータを表示させたいと仰るのでしょうか? 本当は「月別結果」シートのデータを全て日本時間を基準としたいんです。 類似したファイルがあり、それは元データから日本時間なので計算は必要ないのですが、この元データはサーバー時間のもなので日本時間に統一しようと思いました。 今更なのですが。 これは、私事ですが病気でして生きているうちに治ることは極めて低いでしょう。 現在でも体調が極端に悪くなり寝込むことがあります。 最近ではよくなっているのかさえよく分かりません。 このファイルを作った当初はさらに悪くいい加減だったのかもしれません。 >それでしたら、私が挙げた「元データ」シートのデータから全て計算するという方法では、どうしても1つの関数の中で1度やった同じ計算を複数回やり直す事になりますので、やはり「損益計算」シート上の他の列に表示されているデータを利用する方式の方が宜しいかと思います。 但し、サーバー時間を「年」、「月」、「日」、「時刻」に分けて表示させたりする等といった無駄な表示をしますと、その分余計な計算をしなければならなくなりますので、その様な必要のないものまで表示するのはなるべく避けて必要な情報だけを整理して表示させる様にすべきかと思います。(作業列として必要な場合は別ですが、サーバー時間を「年」、「月」、「日」を作業列として分ける意味はあまりないと思います) 自分はExcel関数の知識が乏しいですが、今思うと「開始日時」、「終了日時」をそれぞれ一つのセルに計算すればよかったと思っています。 また、このファイルを元に作り直すということになりますと大変な作業となるような気がするのですが。 >上記の整理して無駄な表示を無くするという件や、「冬時間が必ずしも夏時間-1時間であるということではない可能性」を考慮に入れるべきか否かという件を始めとして、まだはっきりとはしていない点が幾つか御座いますので、具体的な関数を提示する事は現時点では保留にさせて頂きます。 上記に述べたように自分はExcel関数の知識が乏しいですし、kagakusuki様とbunjii様の見解が異なるようです。 なので、お二方のファイルを別々に作りたいと思います。 kagakusuki様には、夏時間と冬時間の項目を設け入力できるようにしていただきたいと思います。 あと、夏時間（日本時間として）の開始日時と夏時間（日本時間として）の終了日時の項目ですが、 例として、 例1： 夏時間（日本時間として）の開始日時：3月第2日曜日2:00 夏時間（日本時間として）の終了日時：11月第1日曜日2:00 例2： 夏時間（日本時間として）の開始日時：3月最終日曜日1:00 夏時間（日本時間として）の終了日時：10月最終日曜日1:00 例3： 夏時間（日本時間として）の開始日時：10月第1日曜日2:00 夏時間（日本時間として）の終了日時：翌年4月第1日曜日3:00 つまり、夏時間（日本時間として）の開始日時の項目は下記の順で表示。 「月を表す半角数字」、「月」（文字列）、「第」または「最終」（文字列）、「月のn回目」（n：半角数値）または「最終」の場合は「月のn回目」は必要なし、「曜日」（文字列）、「時刻」（時間:分。時間・分は半角数値。コロンは半角。） 夏時間（日本時間として）の終了日時の項目は下記の順で表示。 「翌年」（文字列、「翌年」は存在する場合と存在しない場合あり。）、「月を表す半角数字」、「月」（文字列）、「第」または「最終」（文字列）、「月のn回目」（n：半角数値）または「最終」の場合は「月のn回目」は必要なし、「曜日」（文字列）、「時刻」（時間:分。時間・分は半角数値。コロンは半角。） となります。 さらに変更・追加がありましたことに関しまして、申し訳ありませんがこれ以上追加点はありません。 「設定」シートに入力するデータについては、 D3：夏時間 E3：冬時間 F3：夏時間の開始日時 G3：夏時間の終了日時 と変わりありません。 「整理して無駄な表示をなくするという」部分ですが、これを実行するとなるとこちらからも、具体的なセルの位置を提示できません。考えられるのはそれぞれの年、月、日、時刻を開始日時、終了日時にまとめることですが。 理想としましては、回答者様のようにしたいですが。 さらに、「月別結果」シートの計算式も大幅に変更しなくてはいけなくなると思うのですが。 そうなると、さらに回答者様にお時間を割いていただき、ご苦労をおかけしなくてはいけなくなりますがご了承いただけますでしょうか？ この点に関しましては、回答者様のご意見を伺わなければなりません。 回答よろしくお願いします。
- bunjii
- ベストアンサー率43% (3589/8249)
回答No.21の追加です。 「損益計算」シートのZ3についてはAC3とAD3が正常に算出できれば、次の数式で良いでしょう。 Z3=IF(A3="","",DATE(A3,B3,C3)+D3+(9-設定!$D$3+IFERROR(((DATE(A3,B3,C3)+D3)>AC3)*((DATE(A3,B3,C3)+D3)<AD3),0))/24) 上記の数式で、IFERROR関数はSummer Timeの開始日時と終了日時が空欄のとき（Summer Timeを実施していない地域）のために付加したものです。 Z3の数式は下へオートフィルコピーすることはできますが、右へオートフィルコピーすると目的に合いませんので、AA3の数式についてはZ3の数式を準用してセル番地を手動で訂正してください。 尚、AC3とAD3については「設定」シートのF3とG3の文字列入力内容の再補足がありましたら数式の提示をさせて頂きます。
お礼
回答ありがとうございます。 >尚、AC3とAD3については「設定」シートのF3とG3の文字列入力内容の再補足がありましたら数式の提示をさせて頂きます。 特にもうないです。 また、すでに回答をくれたんですね。 ありがとうございます。
- bunjii
- ベストアンサー率43% (3589/8249)
>冬時間の項目がなくても計算式は可能なんですね。 「冬時間」ではなく「Local Time（地域の標準時）」です。 Local Timeの時差とSummer Timeの開始日時と終了日時が分かればサーバーのタイムスタンプをJST（日本の標準時）に変換できます。 >それでは、 >F3：3月第2日曜日2:00 >G3：11月第1日曜日2:00 >でよろしいでしょうか？ 私の提起の仕方が悪かったようです。 その情報は既に提示されたものなので必要ありません。 オーストラリアの事業者の場合はF3セルとG3セルはどのような表記になるかを提示して頂きたかったのです。 F3： 10月最終日曜日2:00 G3：3月最終日曜日2:00 上記のようになる場合とG3セルには"翌年3月最終日曜日2:00"と"翌年"が入るか否かが重要になります。 >F3、G3の文字の順番は、 >「月の半角数値」（1桁、または2桁）、 >「月」（文字列）、 あなたの認識の誤りです。 1つのセルに数値と文字列が混在できません。 従って、「月の半角数値」は「月を表す半角数字」です。 >■【「第」（文字列）、「週の何週目か半角数値」】、 これも前に指摘したように「第n日曜日」のnは対象月のn週目ではなくn回目と言う表現にしないと誤りが起ります。 >これでよろしいでしょうか？ 毎回のことですが必要な情報が不足し、余分な説明が多すぎて効率が悪いので具体的な数式の回答が困難です。 >ただ、MT4のチャートソフトの問題ではなく、Excelの計算式なんです。 それなら、質問のジャンルはExcelでも良いでしょうが、計算の元になる模擬データと計算結果の表の構成を提示することで良いでしょう。 しかし、「MT損益計算-テスト.xlsx」の内容を見ると「損益計算」シートにあなたが必要とする数式が含まれているように見受けられます。 月別集計が必要ならSUMIFS関数で目的達成できると思います。 Excelの関数については解説書を書店で購入し、自習してください。 >また、質問した当初こんなに長くなるとは思いませんでした。 回答→補足（不備あり）→回答（補足の不備を指摘）→補足（不備あり）→・・・・ 上記の繰り返しが主な原因です。 通常では回答者の根気が持ちません。
お礼
回答ありがとうございます。 >「冬時間」ではなく「Local Time（地域の標準時）」です。 地域の標準時という表現が正しいんですね。 >Local Timeの時差とSummer Timeの開始日時と終了日時が分かればサーバーのタイムスタンプをJST（日本の標準時）に変換できます。 はい、分かりました。 >私の提起の仕方が悪かったようです。 その情報は既に提示されたものなので必要ありません。 オーストラリアの事業者の場合はF3セルとG3セルはどのような表記になるかを提示して頂きたかったのです。 F3： 10月最終日曜日2:00 G3：3月最終日曜日2:00 上記のようになる場合とG3セルには"翌年3月最終日曜日2:00"と"翌年"が入るか否かが重要になります。 そうでしたか。それでは、 F3：10月最終日曜日2:00 G3：翌年3月最終日曜日2:00 というような表記で、「翌年」を入れてください。 >あなたの認識の誤りです。 1つのセルに数値と文字列が混在できません。 従って、「月の半角数値」は「月を表す半角数字」です。 認識というか表現の誤りですね。 「1つのセルに数値と文字列が混在できません。」という点に関しましては、Excelの書式としては標準となっていますが実質文字列ですね。 >これも前に指摘したように「第n日曜日」のnは対象月のn週目ではなくn回目と言う表現にしないと誤りが起ります。 はい。 >毎回のことですが必要な情報が不足し、余分な説明が多すぎて効率が悪いので具体的な数式の回答が困難です。 最初の情報で計算式が導き出せると思ったのですが、次々と計算式に必要な情報が見つかってしまったことに関しましては申し訳なく思います。 >それなら、質問のジャンルはExcelでも良いでしょうが、計算の元になる模擬データと計算結果の表の構成を提示することで良いでしょう。 しかし、「MT損益計算-テスト.xlsx」の内容を見ると「損益計算」シートにあなたが必要とする数式が含まれているように見受けられます。 月別集計が必要ならSUMIFS関数で目的達成できると思います。 Excelの関数については解説書を書店で購入し、自習してください。 分かりました。 ですが、分からなかった場合、新規に質問するかもしれません。 >回答→補足（不備あり）→回答（補足の不備を指摘）→補足（不備あり）→・・・・ 上記の繰り返しが主な原因です。 通常では回答者の根気が持ちません。 大変申し訳ありません。 回答者様の文面からすると、嫌気がさしているのではないかと察しられるのですが、ここまで付き合っていただいて大変感謝しております。 すでに新たに回答を頂けているのですね。 ありがとうございます。
- kagakusuki
- ベストアンサー率51% (2610/5101)
後、少し気になったのですが、ダウンロードした「MT損益計算 - テスト.xlsx」の「元データ」シートには608行目までしかデータが存在していないにもかかわらず、「損益計算」シートの方には10002行目まで関数が入力されていましたが、入力されている関数自体もデータの一種ですので、表示には現れていない609行～10002行もファイルのデータ量のを増やす原因となっています。 また、再計算が行われる度に、609行～10002行に入力されている関数でも「どの様な内容を表示するのか」という事を計算する事になるため、その分、処理が重くなります。 Excelの計算モードを自動にしている場合には、どこかの1個のセルに何らかの値を入力する度に、そのブック内のセルに入力されている全ての関数で再計算が行われますので、関数が入力されているセルが必要以上に多ければ、データの入力作業に時間が掛かる様になる恐れが高いのです。 また、保存の際にも再計算が行われるため、保存や呼び出しにも時間が掛かる様になります。 実際、こちらでダウンロードした「MT損益計算 - テスト.xlsx」に、改良した関数を入力して見た際に、すぐには結果が表示されませんでした。 ですから、もし元データが10000行近くにもなる事が無い場合には、「損益計算」シートにおいて関数が入力されている行をもっと少なくしておかれた方が宜しいのではないかと思います。 また、もしも実際の元データが何千行にもなるといった理由で、「損益計算」シートにおいて関数が入力されている行はどうしても1万行程度が必要になるという事でしたら、関数で処理を行っていたのではファイルのデータ量がむやみに多くなりますので、ワークシート関数を使わずに、新たにデータを更新して表示内容を変更する必要が生じる毎にVBAを使って処理を行うようにされた方が良いかも知れません。
お礼
回答ありがとうございます。 そうですね。 非常に長い計算式が入っていて開くのにも時間がかかります。 VBAはちょっと抵抗があるため、計算式を大幅に削除しました。 1日にどれだけの件数になるのか予想できないのですが、だいたいの計算で元データの件数を700件にしました。多分、2年分ぐらいです。 また、回答No.18のお礼コメント、及び、補足コメントをお手数ですがお読みいただき、ご理解いただけたなら引き続き、回答の方をよろしくお願いします。 非常に長くなってしまったことをお詫び申し上げます。 よろしくお願いします。
- bunjii
- ベストアンサー率43% (3589/8249)
>まず、「設定」シートに入力するデータについてですが、 >D3：夏時間（サーバー時間のGMTとの時差） >E3：冬時間（サーバー時間のGMTとの時差） >F3：夏時間の開始日時 >G3：夏時間の終了日時 >としてください。 「E3：冬時間（サーバー時間のGMTとの時差）」は冬時間と言う用語が不適切であり、Local Time（地域の標準時）と言う表現にしてください。 Summer TimeはLocal Timeを夏の間だけ1時間早めることであり、他の期間はLocal Timeに戻すことです。（冬の期間はLocal Timeを遅らせるのであれば冬時間と言う考え方もあるでしょう） 尚、前に回答したように「E3：冬時間（サーバー時間のGMTとの時差）」を追加する意味は無くSummer Timeの開始日時と終了日時を指定することですべての時差調整を行う方がシンプルになります。 従って、項目を設定しても利用しない数式になると思います。 >「設定」シートの入力についてはこれで問題ないでしょうか？ Summer Timeの開始日時と終了日時がニュージーランドについて当方の示した情報とあなたの提示サイトの日時に喰い違いがありますので、1つの例としてあなたの判断でF3とG3への入力文字列を具体的に提示してください。 >また、「■本質問と別の問題」より、・・中略・・これは、何故なんでしょうか？ 今回の質問とは異なる内容であり、別に質問された方が良いでしょう。 Excelのカテゴリーでは無くMT4に関する質問と考えてください。 >これに関してご理解いただけているのはbunjii様と、kagakusuki様だけです。 私はMT4について全く知識はありませんので、Excelで数式の扱い方についてのみ回答できます。
お礼
回答ありがとうございます。 >「E3：冬時間（サーバー時間のGMTとの時差）」は冬時間と言う用語が不適切であり、Local Time（地域の標準時）と言う表現にしてください。 はい、分かりました。 >尚、前に回答したように「E3：冬時間（サーバー時間のGMTとの時差）」を追加する意味は無くSummer Timeの開始日時と終了日時を指定することですべての時差調整を行う方がシンプルになります。 従って、項目を設定しても利用しない数式になると思います。 冬時間の項目がなくても計算式は可能なんですね。 >Summer Timeの開始日時と終了日時がニュージーランドについて当方の示した情報とあなたの提示サイトの日時に喰い違いがありますので、1つの例としてあなたの判断でF3とG3への入力文字列を具体的に提示してください。 それでは、 F3：3月第2日曜日2:00 G3：11月第1日曜日2:00 でよろしいでしょうか？ F3、G3の文字の順番は、 「月の半角数値」（1桁、または2桁）、 「月」（文字列）、 ■【「第」（文字列）、「週の何週目か半角数値」】、 「曜日」（文字列）、 「時刻の半角数値」（1桁、または2桁）、 「:」（半角コロン）、 「分の半角数値」（2桁） の順で入力。 ただし、■【】内の、「第」（文字列）と「週の何週目か半角数値」の部分が、「最終」（文字列）であった場合、この部分を「最終」を入れる。 これでよろしいでしょうか？ >今回の質問とは異なる内容であり、別に質問された方が良いでしょう。 Excelのカテゴリーでは無くMT4に関する質問と考えてください。 はい、たしかに本質問とは異なります。 ただ、MT4のチャートソフトの問題ではなく、Excelの計算式なんです。 なので、Excelに関する質問です。 「■本質問と別の問題」については自己解決できました。 お手数ですが、回答No.18のお礼コメント、及び、補足コメントを読んでください。 また、回答No.18の補足コメントの、 「またまた本質問とは異なるのですが、質問があります。」以下の文章を読んいただけないでしょうか？ ファイルをダウンロードしていただいたのはbunjii様と、kagakusuki様だけですので「MT損益計算 - テスト.xlsx」の「月別結果」シートというシートの中で「利益」、「損失」などの項目があり、そこでまた疑問点があります。 これも本質問とは異なるため、ここで解決するのはどうかと思うのですが、新規に質問した場合、回答を頂けるでしょうか？ ここで解決してもいいのですが、回答者様の意見を尊重したいと思います。 このような説明でご理解いただけたでしょうか？ ご理解いただけたなら、引き続きよろしくお願いします。 また、質問した当初こんなに長くなるとは思いませんでした。 お詫び申し上げます。
- kagakusuki
- ベストアンサー率51% (2610/5101)
>異なったサーバーのデータを入力しますと業者によってGMTとの時差が異なったり、同一の時刻であっても価格が異なります。 >それとも回答者様に何か良いアイデアがあるのでしょうか？ 例えば「設定」シートのA列にどのサーバー(或いは取引場所)の設定であるのかを示す文字列(例えば業者名等)か、番号の一覧を入力しておき、そのサーバーに対応する「初回証拠金」～「夏時間終了日時」等のデータはB列～G列に入力するという形式で、1行ごとに1つのサーバーに対応する設定データを入力した表を作っておきます。 そして、「元データ」シートの適当な列に「その行の取引がどのサーバー(或いは取引場所)のデータから取り出した取引のデータであるのかを示す文字列(例えば業者名等)か、番号」を入力しておく事で、「設定」シートの何行目のデータを参照すれば良いのかを判断出来るようにしておけば良いと思います。 その際、もしも同一のサーバーでから得た情報であっても、時期や取引内容等によって設定データが異なるパターンが複数存在する場合、例えば「○○」というサーバーには設定が異なるパターンが3パターンある場合には、「設定」シート上に「○○」サーバーに関する設定データを3行に分けて入力し、そのA列には「○○#1」、「○○#2」、「○○#3」等の様にはパターンごとに異なる名称を付けておけば良い訳です。 又、サーバーごとにデータが並んでいる順番が異なる場合には、列全体を切り取って、所定の位置に切り取った列を挿入するという方法で、データが並んでいる順番を並べ替えてから、元データとして使用する様にすれば良い訳です。 その上で、「損益計算」シート等で「設定データ」を使って計算処理を行う際には、「元データ」シート上の「その行の取引がどのサーバー(或いは取引場所)のデータから取り出した取引のデータであるのかを示す文字列(例えば業者名等)か、番号」のデータを検索値としたVLOOKUP関数を使って、適切な「設定データ」を参照すれば良いのです。 >この計算式の方が短くて済みますね。 という事は、こちらの方が望ましいという事でしょうか? >「最終日曜日」の「最終」という部分を付け加えなければならなくなりました。 どのようにしたらよいでしょうか？ その件に関しては、その点にも対応する関数を今回の回答で提示させて頂いたとしましても、もしも前述の >異なったサーバーのデータを入力しますと業者によってGMTとの時差が異なったり、同一の時刻であっても価格が異なります。 >それとも回答者様に何か良いアイデアがあるのでしょうか？ に対応して「設定」シートや「損益計算」シートのレイアウトが変更となった場合には、それに応じて再度関数を提示し直さねばならなくなり、二度手間になってしまうという恐れがありますので、複数のサーバーの設定にも対応する様にした場合、どの列にどのようなデータが入るのかという事が決まってから回答したいと思います。 何故なら以前にも >つまり、冬時間が必ずしも夏時間-1時間であるということではない可能性もあるかもしれません。 >これも、計算に考慮していただけないでしょうか？ という事が後になってから判明し、 >そうしますと、右にずらし、 >「設定」シートのD3（サーバー時間の夏時間のGMT） >「設定」シートのE3（サーバー時間の冬時間のGMT） >「設定」シートのF3（夏時間の開始日時） >「設定」シートのG3（夏時間の終了日時） >とするのがよいかと思います。 というレイアウトの変更があった事により、既に1度、二度手間となっているのですから、これ以上、質問者様がどの様にされるのか決めておらず、又、状況も明確に説明が行われている訳ではない内から関数を提示致しましても、結局その関数を提示するという行為は無駄な行為を繰り返している事に過ぎないという事になってしまうからです。 >回答No.8のお礼コメントの「■本質問と別の問題」 に関しては未だ詳しく確認した訳では御座いませんが、「月別結果」シートのB列の「月」の欄が0となっているのはどういう訳なのでしょうか? 「0月」などという月は存在しませんし、少なくとも「損益計算」シートのF列(取引終了月の欄)が0になっているデータなど存在しないのですから、「月別結果」シートのB列が0になっている行においては、「月別結果」シートにおいてSUMIFS関数やCOUNTIFS関数の条件に、「B列の『月』の欄」を条件として使用しているC列～F列及びH列～K列の値は0となりますし、その0となっているE列やK列の値を割り算の除数(分数の分母)として使用しているG列やL列の結果が#DIV/0!になるのは当たり前ですし、M列やN列の値も「"B列の『月』の欄"の値を条件としたCOUNTIFS関数」の結果が0の場合は""を返すような関数となっているのですから結果は空欄となり、その空欄(正確には文字数が0の文字列データ)の値を足し算の計算に使用しているO列、P列、R列の関数は文字列を足し合わせる事になるのですから、#VALUE!になるのは当たり前ですし、その#VALUE!の値を使って四則演算を行う様になっているQ列やS列の関数が#VALUE!になるのは当たり前です。 又、「月別結果」シートのP列の中に#VALUE!となっているものがあるのですから、その「月別結果」シートのP列のデータを基に計算を行っている「ストラテジー評価」シートの一部のセルの関数の結果が#VALUE!になるのは当たり前です。 全ては「月別結果」シートにおいて「0月」などという存在しない月のデータを使っている事が問題だと思われます。 回答No.16に対するお礼にて質問者様は、 >「誤った値(計算処理の元データとして使用する事が出来ない値)が入力されていた場合」というのは基本的にはあり得ませんが、 と書いておられましたが、実際には質問者様が入力された元データも同然の「月別結果」シートB列の「月」の欄の値に誤りがあった訳です。 ですから、参照先のセルの値が「想定した計算処理を行う事が出来ない様な値」の場合には、エラー回避策を講じた関数とした方が良いと思います。 後、おそらく質問者様は「月別結果」シートB列に正しい月を表示させる事が出来る様な関数を教えて欲しいと仰る事でしょうが、それは「質問者様がどの様な事をしたいのか」という事によって必要となる関数も異なって参りますので、「質問者様が『どんな条件を満たした月』を表示させたいのか」という事を文章で明確に御説明して頂く様お願い致します。
お礼
回答ありがとうございます。 お礼が遅れました。申し訳ありません。 「■本質問と別の問題」についていろいろ試してみたり、この文章を作成するのに時間がかかってしまいました。 >例えば「設定」シートのA列にどのサーバー(或いは取引場所)の設定であるのかを示す文字列(例えば業者名等)か、番号の一覧を入力しておき、そのサーバーに対応する「初回証拠金」～「夏時間終了日時」等のデータはB列～G列に入力するという形式で、1行ごとに1つのサーバーに対応する設定データを入力した表を作っておきます。 そして、「元データ」シートの適当な列に「その行の取引がどのサーバー(或いは取引場所)のデータから取り出した取引のデータであるのかを示す文字列(例えば業者名等)か、番号」を入力しておく事で、「設定」シートの何行目のデータを参照すれば良いのかを判断出来るようにしておけば良いと思います。 その際、もしも同一のサーバーでから得た情報であっても、時期や取引内容等によって設定データが異なるパターンが複数存在する場合、例えば「○○」というサーバーには設定が異なるパターンが3パターンある場合には、「設定」シート上に「○○」サーバーに関する設定データを3行に分けて入力し、そのA列には「○○#1」、「○○#2」、「○○#3」等の様にはパターンごとに異なる名称を付けておけば良い訳です。 又、サーバーごとにデータが並んでいる順番が異なる場合には、列全体を切り取って、所定の位置に切り取った列を挿入するという方法で、データが並んでいる順番を並べ替えてから、元データとして使用する様にすれば良い訳です。 その上で、「損益計算」シート等で「設定データ」を使って計算処理を行う際には、「元データ」シート上の「その行の取引がどのサーバー(或いは取引場所)のデータから取り出した取引のデータであるのかを示す文字列(例えば業者名等)か、番号」のデータを検索値としたVLOOKUP関数を使って、適切な「設定データ」を参照すれば良いのです。 なるほど。 そのような考え方があるんですね。 下記の記載を読みましたが、今回は同一サーバー（同一業者）として計算していただきたいと思います。 >という事は、こちらの方が望ましいという事でしょうか? 一度、夏時間の開始・終了日時の計算をして、別のセルに最終的な結果を出した方が単純にそれぞれ計算式が「短くて」いいのかな？と思いました。 ですが、今回は回答者様の下記の計算式を採用させていただきました。 =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)) >というレイアウトの変更があった事により、既に1度、二度手間となっているのですから、これ以上、質問者様がどの様にされるのか決めておらず、又、状況も明確に説明が行われている訳ではない内から関数を提示致しましても、結局その関数を提示するという行為は無駄な行為を繰り返している事に過ぎないという事になってしまうからです。 これは本当に申し訳ありません。 言い訳をさせていただけるなら、夏時間の理解が不足しており、世界の業者を把握するのは不可能なためこのようになってしまいました。 それで変更・追加となってしまいました。 お詫び申し上げます。 「■本質問と別の問題」に関してですが、自分でも不思議でたまらなかったのですが、自己解決できました。 以前の質問「Excel2010：年月の出力」の「回答No.5」より、 http://okwave.jp/qa/q8355796.html 回答者様から下記のような計算式を教えて頂きました。 =IF(ISNUMBER($A3),SUMPRODUCT({1,2,3,4,5,6,7,8,9,11,12}*({1,2,3,4,5,6,7,8,9,11,12}>SUM(B2)*($A2=$A3))*(COUNTIFS(Sheet1!$A:$A,$A3,Sheet1!$B:$B,{1,2,3,4,5,6,7,8,9,11,12})>0)*(COUNTIFS(Sheet1!$A:$A,$A3,Sheet1!$B:$B,">"&SUM(B2)*($A2=$A3),Sheet1!$B:$B,"<"&{1,2,3,4,5,6,7,8,9,11,12})=0)),"") そこでよく見てみると、「{1,2,3,4,5,6,7,8,9,11,12}」の部分で、「10」が抜けていました。 自分は、Excel関数はほぼ無知なのですが、1～12月の部分であることは分かったため「{1,2,3,4,5,6,7,8,9,11,12}」の部分に全て「10」を入れました。 下記の計算式が「MT損益計算 - テスト.xlsx」の「月別結果」シートのB3（月）に入れた式です。 =IF(ISNUMBER($A3),SUMPRODUCT({1,2,3,4,5,6,7,8,9,10,11,12}*({1,2,3,4,5,6,7,8,9,10,11,12}>SUM(B2)*($A2=$A3))*(COUNTIFS(損益計算!$AD:$AD,$A3,損益計算!$AE:$AE,{1,2,3,4,5,6,7,8,9,10,11,12})>0)*(COUNTIFS(損益計算!$AD:$AD,$A3,損益計算!$AE:$AE,">"&SUM(B2)*($A2=$A3),損益計算!$AE:$AE,"<"&{1,2,3,4,5,6,7,8,9,10,11,12})=0)),"") また、「損益計算!$AD:$AD」、「損益計算!$AE:$AE」の内容は、 「損益計算」シートのAD3（日本時間としての終了年）の式：=IF(A3="","",YEAR(AA3)) 以下コピー。 「損益計算」シートのAE3（日本時間としての終了月）の式：=IF(A3="","",MONTH(AA3)) 以下コピー。 です。 つまり、「元データ」シートのデータに誤りがあったわけではなく、「月別結果」シートのB3以下の計算式に誤りがあったわけです。
補足
お礼のコメントで文字数が足りなくなったため、補足コメントから続きを記載したいと思います。 >後、おそらく質問者様は「月別結果」シートB列に正しい月を表示させる事が出来る様な関数を教えて欲しいと仰る事でしょうが、それは「質問者様がどの様な事をしたいのか」という事によって必要となる関数も異なって参りますので、「質問者様が『どんな条件を満たした月』を表示させたいのか」という事を文章で明確に御説明して頂く様お願い致します。 「MT損益計算用データ.xlsx」のデータより、 A3から下 B3から下 2011 1 2011 2 2011 3 2011 4 2011 5 2011 6 2011 7 2011 8 2011 9 2011 10 2011 11 2011 12 2012 1 2012 2 2012 3 2012 4 2012 5 2012 6 2012 7 2012 8 2012 9 2012 10 このように、同一の列で、日本時間としての同一の開始年・月、または、日本時間としての同一の終了年・月を表示したかったわけです。開始、または、終了は、どちらでもいいです。 このような説明でご理解いただけたでしょうか？ 結果として「月別結果」シートB列に関しては、自己解決できました。 しかし、以前の質問での回答者様の計算式がなかったら自分には全く分かりませんでした。 大変感謝しております。 またこの質問内で回答者様から、 >このサイトでは、投稿者自身で内容の変更が可能なWebスペース上にアップロードされたファイルのURLを記載しての投稿は禁止されておりますので、情報を提示する際にその様な方法を使う事はなるべく避けた方が宜しいのではないかと思います。 >【参考URL】 OKWaveコミュニティ > はじめてガイド > 禁止事項ガイドライン このようなご指摘を受けました。 このサイトの管理者の方は大目に見てくれて削除しなかったみたいですね。 このサイトの管理者の方にも感謝致します。 そのようなご指摘を受け、削除されないか不安だったため「■本質問と別の問題」について回答者様に本質問にて、お伺いをしたわけです。 また、このサイトの管理者の方に要望があります。違法的・不道徳的な行為は問題外ですが、「アップロードしたファイルのURLを記載しての投稿」について許可というか、改善できないでしょうか。 改善を期待しつつ、管理者の方にはご負担をおかけしますがどうかよろしくお願いします。 回答者様にはこのような説明で引き続き、お願いできるでしょうか？ ---------------------------------------------------------------------- またまた本質問とは異なるのですが、質問があります。 「MT損益計算 - テスト.xlsx」の「月別結果」シートには「利益」や「損失」などの項目があります。 同一の列に、日本時間としての同年・同月の開始日から終了日に「利益」などの項目に合計を表示したいのですが。 例えば、「月別結果」シートのA3に、2011（年）、B3に、1（月）が入っています。 それをC3に、「損益計算」シートにある「2011年1月3日 19:22:00」（Z3：日本時間としての開始日時）から「2011年1月29日 3:52:00」（AA33：日本時間としての終了日時）、 同様にC4に、「損益計算」シートにある「2011年2月1日 3:03:00」（Z34：日本時間としての開始日時）から「2011年2月28日 16:14:00」（AA66：日本時間としての終了日時）、 以下、同様にしたいのです。 現在、C3には下記のような計算式が入っています。 =IF(A3="","",SUMIFS(損益計算!$M$3:$M$1048576,損益計算!$AD$3:$AD$1048576,A3,損益計算!$AE$3:$AE$1048576,B3)) また、新たに下記のように計算式を作りました。 「損益計算」シートのAD3（日本時間としての終了年）の式：=IF(A3="","",YEAR(AA3)) 以下コピー。 「損益計算」シートのAE3（日本時間としての終了月）の式：=IF(A3="","",MONTH(AA3)) 以下コピー。 計算式としては、日本時間としての終了年・月を使用しているため、「日本時間としての同年・同月の開始日から終了日の利益の合計」かどうか分かりません。 もうすでに十分回答者様にはご苦労をおかけしまして申し訳ないのですが、ファイルをダウンロードされているため、計算式についてご理解いただけるのではないかと思います。 説明に関してご理解いただけたでしょうか？ これに関しては、新規に質問して回答を頂いた方がよいでしょうか？ それとも、本質問にて解決した方がよろしいでしょうか？ このような長文になってしまい、申し訳ありませんがご容赦ください。 回答よろしくお願いします。
- bunjii
- ベストアンサー率43% (3589/8249)
>可能性が低いと言いましたが、調べたところそのような業者を発見しました。 >http://www.fxplus.com/broker/tfx/ >口座履歴のデータがないためこんな情報を記載しても意味がないのですが。 ご提示の会社はニュージーランド（南半球）に所在していますので夏冬の期間が日本とは逆転します。 しかし、提示のサイトにはSummer Timeの開始日時と終了日時の記載がありませんので別のサイトから規則性のある条件を得なければなりません。 因みに、本年のSummer Time終了日時は次のようになっています。 ・前回のサマータイムの終了日時:2015年4月5日（日）3時0分 NZDT また、本年のSummer Time開始日時は次の通りです。 ・次回のサマータイムの開始日時:2015年9月27日（日）2時0分 NZST 更に、上記のSummer Timeに伴う終了日時は次のようになっています。 ・次回のサマータイムの終了日時:2016年4月3日（日）3時0分 NZDT これらのことから「設定」シートのD3、E3、F3にはどのような値（数値/文字列）を入力するかについてあなたの見解を提示してください。
お礼
早速の回答ありがとうございます。 >しかし、提示のサイトにはSummer Timeの開始日時と終了日時の記載がありませんので別のサイトから規則性のある条件を得なければなりません。 >これらのことから「設定」シートのD3、E3、F3にはどのような値（数値/文字列）を入力するかについてあなたの見解を提示してください。 まず、「設定」シートに入力するデータについてですが、 D3：夏時間（サーバー時間のGMTとの時差） E3：冬時間（サーバー時間のGMTとの時差） F3：夏時間の開始日時 G3：夏時間の終了日時 としてください。 あと値ですが、 D3：整数値のみ E3：整数値のみ F3、G3の値についてですが、 「月の半角数値」（1桁、または2桁）、「月」（文字列）、「第」（文字列）、「週の何週目か半角数値」、「曜日」（文字列）、「時刻の半角数値」（1桁、または2桁）、「:」（半角コロン）、「分の半角数値」（2桁）の順で入力します。 と入力規則をお伝えしましたが下記URLの下部の方の「各国の夏時間」の項目より、 http://www.fx-soken.co.jp/kawase/k_j_010.html 「最終日曜日」の「最終」という部分を付け加えなければならなくなりました。 したがって、 「月の半角数値」（1桁、または2桁）、 「月」（文字列）、 ■【「第」（文字列）、「週の何週目か半角数値」】、 「曜日」（文字列）、 「時刻の半角数値」（1桁、または2桁）、 「:」（半角コロン）、 「分の半角数値」（2桁）の順で入力。 の■【】内の、「第」（文字列）と「週の何週目か半角数値」の部分を、 「最終」（文字列）にした場合も、考えて頂きたいのです。 夏時間の、開始日時と終了日時については検索してもヒットしませんので、業者の方に問い合わせなくてはいけません。 また、お時間をいただくことなってしまいます。 変更点や、問題が見つかり、大変申し訳ないのですが、全世界の業者を把握しているわけではないので、ご了承願います。 「設定」シートの入力についてはこれで問題ないでしょうか？ 夏時間の開始日時と終了日時については、業者の回答後ご連絡差し上げます。 また、「■本質問と別の問題」より、 「MT損益計算 - テスト.xlsx」の「元データ」シートに「2015年6月分.htm」のデータを入力した場合と、「MT損益計算用データ.xlsx」のデータを入力した場合で、「2015年6月分.htm」のデータを入力した場合では、「月別結果」シートと「ストラテジー評価」シートの計算結果に何も問題がないのにも関わらず、「MT損益計算用データ.xlsx」のデータを入力した場合、エラーというか正しい結果が表示されません。 「2015年6月分.htm」のデータ数が極端に少ない、厳密に言えば1～9月分では問題ないが、10～12月では問題が生じてくると思われます。 これは、何故なんでしょうか？ 本質問とは異なるため、本来は質問の場ではないのですが、これに関してご理解いただけているのはbunjii様と、kagakusuki様だけです。 この問題に対しては、本質問が解決した後、新規に質問した方がいいのでしょうか？ それともこのまま続行した方がいいのでしょうか？ 回答よろしくお願いします。
お礼
回答ありがとうございます。 申し訳ありません。 回答者様のおっしゃる通り、「損益計算」シートで再度調べたところ22日でした。 焦って確認したせいか抜けていた部分がありました。 日本時間では、2011年7月23日 0:31:00（開始日時）～2011年7月23日 1:06:00（終了日時）となっており、土曜日に行われていました。 土・日・元日は、取り引きが行われていないというのが一般的に言われていることなんですが、土曜日に限っては厳密に金曜日の23:59:59に終了というわけではなく、土曜日の早朝まで取り引きが行われていまして、本来ならそこまで計算に入れなければならなかったんです。 ですが、ここではもうそれができないため、新規の質問を検討したいと思います。 正しいご指摘、ありがとうございます。 また、この件に関しまして質問する際には是非とも回答をいただけたら大変ありがたいです。 ありがとうございました。