週末の最後の時刻ごとに、その1週間の毎日を区切り、その範囲のセルを関数で書きたい
（（（（処理手順）））））
?、週末の最後の時間を出す。
A列に Endと書いた行が区切りの時刻。
その基準は データが、週末から週明けになるときは B列の =WEEKDAY(C列) の数字が小さくなる。
63行目でいえば、B列が７→２ となった。
A63:=IF(WEEKDAY($B63)>WEEKDAY($B64),"End","")
このときの、A６３のEnd文字の 63行目の E63:５：００が 週末最後の時間である。
? その週を1日ごとに区切る
Endを週末とする1週間は 2008/8/25（月）〜2008/8/30(土）の1週間である。
これを A63 Endの 5:00 が、区切りの時間なので、 毎日を5：01〜翌日の5：00で区切る。
具体的には
曜日 年月日 時刻・区切時刻超 曜日 年月日 時刻・まで 範囲の終わりのセル
2 2008/8/25 5:01 〜 3 2008/8/26 5:00
3 2008/8/26 5:01 〜 4 2008/8/27 5:00
4 2008/8/27 5:01 〜 5 2008/8/28 5:00
5 2008/8/28 5:01 〜 6 2008/8/29 5:00
6 2008/8/29 5:01 〜 7 2008/8/30 5:00
となる。
?、範囲の終わりと始まりのセルを出す。Endがあるとき
2008/8/29(金）5：01〜2008（土）5：00
�饅�わりのセル
End行のある 範囲の終わりは A６３：Endと同じ、63行で、I列の I６３である。
�鮖呂泙蠅離札�
End行の区切りの時間は E63:5：00なので、 さかのぼること 25行上に、E３９：5：00がある。
開始時間は 5：01からなので、その次の行40行を見つける。
さらに、開始範囲はF列なので、
求める 範囲の始まりのセルは F40である。
?、Endがない範囲の終わりのセルを出す ：範囲の終わりのセルと始まりのセルを出す。
2008/8/28(木）5：01〜2008/8/29(金）5：00
�饅�わりのセル
「?」でE39の5：00が見つけられたので、、範囲の終わりのセルを求める。
なお、範囲の2008/8/29（金）5：00は、
求める範囲の終わりは I３９である。
�鮖呂泙蠅離札�
今回の 区切時刻は （E63の） 5：00なので、
さらに、上にさかのぼり5：00の次の時刻を探す。
E15が5：00なので、次のE16 6：00を探し出した。
求める範囲の始まりのセルはF１６である。
? 始まりの行が24行（24時間分ないとき）：範囲の終わりと始まりのセルを出す。
E63から、区切時刻は5：00なので、
2008/8/27(水）5：01〜2008/8/28(木）5：00
�顱―�わりのセルは?と同様に、
求める範囲の終わりは I１５である。
�髻‘瑛佑烹横換圈複横柑�間分）さかのぼるが、
データが、これまでのように24行（24時間分）に足りない。
2008/8/27で、5：01後の時刻が存在するデータは
2行目のE２の 2008/8/27(水）16：00である。
求める範囲の始まりのセルはF２である。
? データがないとき：範囲の終わりと始まりのセルを出す
2008/8/26(火）5：01〜2008/8/27(水）5：00
データがないで、始まりのセル・終わりのセルともに 空白
? 2008/8/25（月）5：01〜2008/8/26(火）5：00
で、同様。
データがないので、始まりと終わりともに空白
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
（データの特徴）
区切り時間は常にE63の5：00ではない。
その1週間のうちで A列のEndのある時刻が区切り位置である。
そのために、区切りは 週末の最後の時間を締め切りとする
A列に、下記の関数で 週末の最後の時間のA列に End を表示した。
A63: =IF(WEEKDAY($B63)>WEEKDAY($B64),"End","")
データは 日付順・時間順に並んでいる。
日曜日は、必ずデータがない。
C列：日付が順に並んでいる
E列：時刻が順に並んでいる
B列：C列に対応する曜日を WEEKDAY（）で出してある
求めるはじめのセルは F列
求める終わりのセルは I列
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
お時間のあるときに、アドバイスよろしくお願いします。
OS：XP
EXCEL ２００３
数時間前に、コメントいただいた方へ
コメントありがとうございます。
私の質問分が 長文だったために、
質問文をわかりやすく訂正しました。
その関係で、当該コメントがMIXIの手続き上削除されてしまいました。
お詫び申し上げます。
（（（（処理手順）））））
?、週末の最後の時間を出す。
A列に Endと書いた行が区切りの時刻。
その基準は データが、週末から週明けになるときは B列の =WEEKDAY(C列) の数字が小さくなる。
63行目でいえば、B列が７→２ となった。
A63:=IF(WEEKDAY($B63)>WEEKDAY($B64),"End","")
このときの、A６３のEnd文字の 63行目の E63:５：００が 週末最後の時間である。
? その週を1日ごとに区切る
Endを週末とする1週間は 2008/8/25（月）〜2008/8/30(土）の1週間である。
これを A63 Endの 5:00 が、区切りの時間なので、 毎日を5：01〜翌日の5：00で区切る。
具体的には
曜日 年月日 時刻・区切時刻超 曜日 年月日 時刻・まで 範囲の終わりのセル
2 2008/8/25 5:01 〜 3 2008/8/26 5:00
3 2008/8/26 5:01 〜 4 2008/8/27 5:00
4 2008/8/27 5:01 〜 5 2008/8/28 5:00
5 2008/8/28 5:01 〜 6 2008/8/29 5:00
6 2008/8/29 5:01 〜 7 2008/8/30 5:00
となる。
?、範囲の終わりと始まりのセルを出す。Endがあるとき
2008/8/29(金）5：01〜2008（土）5：00
�饅�わりのセル
End行のある 範囲の終わりは A６３：Endと同じ、63行で、I列の I６３である。
�鮖呂泙蠅離札�
End行の区切りの時間は E63:5：00なので、 さかのぼること 25行上に、E３９：5：00がある。
開始時間は 5：01からなので、その次の行40行を見つける。
さらに、開始範囲はF列なので、
求める 範囲の始まりのセルは F40である。
?、Endがない範囲の終わりのセルを出す ：範囲の終わりのセルと始まりのセルを出す。
2008/8/28(木）5：01〜2008/8/29(金）5：00
�饅�わりのセル
「?」でE39の5：00が見つけられたので、、範囲の終わりのセルを求める。
なお、範囲の2008/8/29（金）5：00は、
求める範囲の終わりは I３９である。
�鮖呂泙蠅離札�
今回の 区切時刻は （E63の） 5：00なので、
さらに、上にさかのぼり5：00の次の時刻を探す。
E15が5：00なので、次のE16 6：00を探し出した。
求める範囲の始まりのセルはF１６である。
? 始まりの行が24行（24時間分ないとき）：範囲の終わりと始まりのセルを出す。
E63から、区切時刻は5：00なので、
2008/8/27(水）5：01〜2008/8/28(木）5：00
�顱―�わりのセルは?と同様に、
求める範囲の終わりは I１５である。
�髻‘瑛佑烹横換圈複横柑�間分）さかのぼるが、
データが、これまでのように24行（24時間分）に足りない。
2008/8/27で、5：01後の時刻が存在するデータは
2行目のE２の 2008/8/27(水）16：00である。
求める範囲の始まりのセルはF２である。
? データがないとき：範囲の終わりと始まりのセルを出す
2008/8/26(火）5：01〜2008/8/27(水）5：00
データがないで、始まりのセル・終わりのセルともに 空白
? 2008/8/25（月）5：01〜2008/8/26(火）5：00
で、同様。
データがないので、始まりと終わりともに空白
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
（データの特徴）
区切り時間は常にE63の5：00ではない。
その1週間のうちで A列のEndのある時刻が区切り位置である。
そのために、区切りは 週末の最後の時間を締め切りとする
A列に、下記の関数で 週末の最後の時間のA列に End を表示した。
A63: =IF(WEEKDAY($B63)>WEEKDAY($B64),"End","")
データは 日付順・時間順に並んでいる。
日曜日は、必ずデータがない。
C列：日付が順に並んでいる
E列：時刻が順に並んでいる
B列：C列に対応する曜日を WEEKDAY（）で出してある
求めるはじめのセルは F列
求める終わりのセルは I列
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
お時間のあるときに、アドバイスよろしくお願いします。
OS：XP
EXCEL ２００３
数時間前に、コメントいただいた方へ
コメントありがとうございます。
私の質問分が 長文だったために、
質問文をわかりやすく訂正しました。
その関係で、当該コメントがMIXIの手続き上削除されてしまいました。
お詫び申し上げます。
|
|
|
|
コメント(37)
週末の最後の時刻ごとに、その1週間の毎日を区切り、その範囲のセルを関数で書きたい
（（（（処理手順）））））
<1>、週末の最後の時間を出す。
A列に Endと書いた行が区切りの時刻。
その基準は データが、週末から週明けになるときは B列の =WEEKDAY(C列) の数字が小さくなる。
63行目でいえば、B列が７→２ となった。
A63:=IF(WEEKDAY($B63)>WEEKDAY($B64),"End","")
このときの、A６３のEnd文字の 63行目の E63:５：００が 週末最後の時間である。
<2> その週を1日ごとに区切る
Endを週末とする1週間は 2008/8/25（月）〜2008/8/30(土）の1週間である。
これを A63 Endの 5:00 が、区切りの時間なので、 毎日を5：01〜翌日の5：00で区切る。
具体的には
曜日 年月日 時刻・区切時刻超 曜日 年月日 時刻・まで 範囲の終わりのセル
2 2008/8/25 5:01 〜 3 2008/8/26 5:00
3 2008/8/26 5:01 〜 4 2008/8/27 5:00
4 2008/8/27 5:01 〜 5 2008/8/28 5:00
5 2008/8/28 5:01 〜 6 2008/8/29 5:00
6 2008/8/29 5:01 〜 7 2008/8/30 5:00
となる。
<3>、範囲の終わりと始まりのセルを出す。Endがあるとき
2008/8/29(金）5：01〜2008（土）5：00
<3-1>終わりのセル
End行のある 範囲の終わりは A６３：Endと同じ、63行で、I列の I６３である。
<3-2>始まりのセル
End行の区切りの時間は E63:5：00なので、 さかのぼること 25行上に、E３９：5：00がある。
開始時間は 5：01からなので、その次の行40行を見つける。
さらに、開始範囲はF列なので、
求める 範囲の始まりのセルは F40である。
<4>、Endがない範囲の終わりのセルを出す ：範囲の終わりのセルと始まりのセルを出す。
2008/8/28(木）5：01〜2008/8/29(金）5：00
<4-1>終わりのセル
「3」でE39の5：00が見つけられたので、、範囲の終わりのセルを求める。
なお、範囲の2008/8/29（金）5：00は、
求める範囲の終わりは I３９である。
<4-2>始まりのセル
今回の 区切時刻は （E63の） 5：00なので、
さらに、上にさかのぼり5：00の次の時刻を探す。
E15が5：00なので、次のE16 6：00を探し出した。
求める範囲の始まりのセルはF１６である。
<5> 始まりの行が24行（24時間分ないとき）：範囲の終わりと始まりのセルを出す。
E63から、区切時刻は5：00なので、
2008/8/27(水）5：01〜2008/8/28(木）5：00
<5-1> 終わりのセルは4と同様に、
求める範囲の終わりは I１５である。
<5-2> 同様に２４行（２４時間分）さかのぼるが、
データが、これまでのように24行（24時間分）に足りない。
2008/8/27で、5：01後の時刻が存在するデータは
2行目のE２の 2008/8/27(水）16：00である。
求める範囲の始まりのセルはF２である。
<6> データがないとき：範囲の終わりと始まりのセルを出す
2008/8/26(火）5：01〜2008/8/27(水）5：00
データがないで、始まりのセル・終わりのセルともに 空白
<7> 2008/8/25（月）5：01〜2008/8/26(火）5：00
で、同様。
データがないので、始まりと終わりともに空白
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
（データの特徴）
区切り時間は常にE63の5：00ではない。
その1週間のうちで A列のEndのある時刻が区切り位置である。
そのために、区切りは 週末の最後の時間を締め切りとする
A列に、下記の関数で 週末の最後の時間のA列に End を表示した。
A63: =IF(WEEKDAY($B63)>WEEKDAY($B64),"End","")
データは 日付順・時間順に並んでいる。
日曜日は、必ずデータがない。
C列：日付が順に並んでいる
E列：時刻が順に並んでいる
B列：C列に対応する曜日を WEEKDAY（）で出してある
求めるはじめのセルは F列
求める終わりのセルは I列
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
お時間のあるときに、アドバイスよろしくお願いします。
OS：XP
EXCEL ２００３
（（（（処理手順）））））
<1>、週末の最後の時間を出す。
A列に Endと書いた行が区切りの時刻。
その基準は データが、週末から週明けになるときは B列の =WEEKDAY(C列) の数字が小さくなる。
63行目でいえば、B列が７→２ となった。
A63:=IF(WEEKDAY($B63)>WEEKDAY($B64),"End","")
このときの、A６３のEnd文字の 63行目の E63:５：００が 週末最後の時間である。
<2> その週を1日ごとに区切る
Endを週末とする1週間は 2008/8/25（月）〜2008/8/30(土）の1週間である。
これを A63 Endの 5:00 が、区切りの時間なので、 毎日を5：01〜翌日の5：00で区切る。
具体的には
曜日 年月日 時刻・区切時刻超 曜日 年月日 時刻・まで 範囲の終わりのセル
2 2008/8/25 5:01 〜 3 2008/8/26 5:00
3 2008/8/26 5:01 〜 4 2008/8/27 5:00
4 2008/8/27 5:01 〜 5 2008/8/28 5:00
5 2008/8/28 5:01 〜 6 2008/8/29 5:00
6 2008/8/29 5:01 〜 7 2008/8/30 5:00
となる。
<3>、範囲の終わりと始まりのセルを出す。Endがあるとき
2008/8/29(金）5：01〜2008（土）5：00
<3-1>終わりのセル
End行のある 範囲の終わりは A６３：Endと同じ、63行で、I列の I６３である。
<3-2>始まりのセル
End行の区切りの時間は E63:5：00なので、 さかのぼること 25行上に、E３９：5：00がある。
開始時間は 5：01からなので、その次の行40行を見つける。
さらに、開始範囲はF列なので、
求める 範囲の始まりのセルは F40である。
<4>、Endがない範囲の終わりのセルを出す ：範囲の終わりのセルと始まりのセルを出す。
2008/8/28(木）5：01〜2008/8/29(金）5：00
<4-1>終わりのセル
「3」でE39の5：00が見つけられたので、、範囲の終わりのセルを求める。
なお、範囲の2008/8/29（金）5：00は、
求める範囲の終わりは I３９である。
<4-2>始まりのセル
今回の 区切時刻は （E63の） 5：00なので、
さらに、上にさかのぼり5：00の次の時刻を探す。
E15が5：00なので、次のE16 6：00を探し出した。
求める範囲の始まりのセルはF１６である。
<5> 始まりの行が24行（24時間分ないとき）：範囲の終わりと始まりのセルを出す。
E63から、区切時刻は5：00なので、
2008/8/27(水）5：01〜2008/8/28(木）5：00
<5-1> 終わりのセルは4と同様に、
求める範囲の終わりは I１５である。
<5-2> 同様に２４行（２４時間分）さかのぼるが、
データが、これまでのように24行（24時間分）に足りない。
2008/8/27で、5：01後の時刻が存在するデータは
2行目のE２の 2008/8/27(水）16：00である。
求める範囲の始まりのセルはF２である。
<6> データがないとき：範囲の終わりと始まりのセルを出す
2008/8/26(火）5：01〜2008/8/27(水）5：00
データがないで、始まりのセル・終わりのセルともに 空白
<7> 2008/8/25（月）5：01〜2008/8/26(火）5：00
で、同様。
データがないので、始まりと終わりともに空白
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
（データの特徴）
区切り時間は常にE63の5：00ではない。
その1週間のうちで A列のEndのある時刻が区切り位置である。
そのために、区切りは 週末の最後の時間を締め切りとする
A列に、下記の関数で 週末の最後の時間のA列に End を表示した。
A63: =IF(WEEKDAY($B63)>WEEKDAY($B64),"End","")
データは 日付順・時間順に並んでいる。
日曜日は、必ずデータがない。
C列：日付が順に並んでいる
E列：時刻が順に並んでいる
B列：C列に対応する曜日を WEEKDAY（）で出してある
求めるはじめのセルは F列
求める終わりのセルは I列
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
お時間のあるときに、アドバイスよろしくお願いします。
OS：XP
EXCEL ２００３
この作業の頻度、どのようにデータが発生しているのか
１シートにどれだけのデータがあることを想定すれば
よいのか云々…と、運用？形態がよくわからないんですけど、
"END"表示をさせるために =IF… (=WEEKDAY()も手作業？
データの初期状態がわかりません、どこからが作業の開始
なんでしょう？)等々されているので、
手作業で色々やっていく手法がお知りになりたいという
ことなんでしょうかね…？
思いついたことを…
C列〜I列のデータがあったとして、B列に曜日を表す数値
とA列に"END"が入っているところから始めるとして…
また"END"は一つしかないものとして…
M1に=VLOOKUP("END",$A$2:$E$87,5,FALSE)で"END"の時刻を取得。
（これも手入力するなら別に"END"が一つでなくても可能。）
データの範囲は適当にはめたものです。以下同様。
J列にグループを作ります。
J2に=C2-IF(E2>$M$1,0,1)として下方向にフィル。
これで、知りたい範囲のデータは同じグループ名になるかと。
M5には表示させたい週にあたる先頭のグループ名（日付）を
手入力。
M6〜M10には上のセル＋１になるようにしておきます。
先頭位置はN5に=MATCH(M5,$J$1:$J$218,0)
最初に一致する位置を求めます。下方向にフィル。
末尾位置は先頭位置＋このグループ名のデータ個数-1
O5に=N5+COUNTIF($J$2:$J$218,M5)-1 下方向にフィル。
求めたいセル範囲の列範囲はF〜Iなので、うまくつなげて
ください。
エラー対応はしていません。悪しからず。
---
こんな感じで、意図はあってますでしょうかね？
しかし、気になるのは、最終目的はセル範囲なのではなく
それを使って何かされるのではないですか？
結局何がしたいということは、できれば明らかにしていただいた
方がありがたいです。
他の方法があったかもしれませんし、この作業が遠回り
だったということもあり得ますので。
１シートにどれだけのデータがあることを想定すれば
よいのか云々…と、運用？形態がよくわからないんですけど、
"END"表示をさせるために =IF… (=WEEKDAY()も手作業？
データの初期状態がわかりません、どこからが作業の開始
なんでしょう？)等々されているので、
手作業で色々やっていく手法がお知りになりたいという
ことなんでしょうかね…？
思いついたことを…
C列〜I列のデータがあったとして、B列に曜日を表す数値
とA列に"END"が入っているところから始めるとして…
また"END"は一つしかないものとして…
M1に=VLOOKUP("END",$A$2:$E$87,5,FALSE)で"END"の時刻を取得。
（これも手入力するなら別に"END"が一つでなくても可能。）
データの範囲は適当にはめたものです。以下同様。
J列にグループを作ります。
J2に=C2-IF(E2>$M$1,0,1)として下方向にフィル。
これで、知りたい範囲のデータは同じグループ名になるかと。
M5には表示させたい週にあたる先頭のグループ名（日付）を
手入力。
M6〜M10には上のセル＋１になるようにしておきます。
先頭位置はN5に=MATCH(M5,$J$1:$J$218,0)
最初に一致する位置を求めます。下方向にフィル。
末尾位置は先頭位置＋このグループ名のデータ個数-1
O5に=N5+COUNTIF($J$2:$J$218,M5)-1 下方向にフィル。
求めたいセル範囲の列範囲はF〜Iなので、うまくつなげて
ください。
エラー対応はしていません。悪しからず。
---
こんな感じで、意図はあってますでしょうかね？
しかし、気になるのは、最終目的はセル範囲なのではなく
それを使って何かされるのではないですか？
結局何がしたいということは、できれば明らかにしていただいた
方がありがたいです。
他の方法があったかもしれませんし、この作業が遠回り
だったということもあり得ますので。
takecさん、ありがとうございます。
＞結局何がしたい
説明不足ですみません。
最終的に求めたいのは ４つの数字です。
具体的には、区切った日ごとの、
F行：openの数字（始値）
その範囲での最大値：高値
その範囲での最小値：安値
その範囲でのI行の最後の時間ｃlose：終値
です。
2008/8/27に当てはめれば、
始値＝F2
高値＝MAX（F2:I15)
安値＝MIN(F2:I15）
終値＝I15
です。
すなわち、途中経過ではなく、
範囲の F2:I15 という数字だけが 必要なのです。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
元々のデータですが、
C行：日付
E行：時刻
F行：open
G行：hi
H:lo
I行：close
です。
＞運用・形態
データは 値段です。
約１０年＊２４時間＝約６万行あります。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
この元データ時間は日本時間なので、これをニューヨークの時間で１日ごとに区切りたいのです。
外国は 冬時間と夏時間があり、その年によって、
データの区切り時刻が 土曜の朝５時や６時と変わります。
年初と、年央では、区切り時刻が異なるわけです。
そのために、区切りを５：００とかかずに、Endで区切っているわけです。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
教わった関数の式については これからよく読ませていただきます。
とりいそぎ、お礼のみ書かせていただきました。
ありがとうございました。
＞結局何がしたい
説明不足ですみません。
最終的に求めたいのは ４つの数字です。
具体的には、区切った日ごとの、
F行：openの数字（始値）
その範囲での最大値：高値
その範囲での最小値：安値
その範囲でのI行の最後の時間ｃlose：終値
です。
2008/8/27に当てはめれば、
始値＝F2
高値＝MAX（F2:I15)
安値＝MIN(F2:I15）
終値＝I15
です。
すなわち、途中経過ではなく、
範囲の F2:I15 という数字だけが 必要なのです。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
元々のデータですが、
C行：日付
E行：時刻
F行：open
G行：hi
H:lo
I行：close
です。
＞運用・形態
データは 値段です。
約１０年＊２４時間＝約６万行あります。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
この元データ時間は日本時間なので、これをニューヨークの時間で１日ごとに区切りたいのです。
外国は 冬時間と夏時間があり、その年によって、
データの区切り時刻が 土曜の朝５時や６時と変わります。
年初と、年央では、区切り時刻が異なるわけです。
そのために、区切りを５：００とかかずに、Endで区切っているわけです。
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
教わった関数の式については これからよく読ませていただきます。
とりいそぎ、お礼のみ書かせていただきました。
ありがとうございました。
早速教わったことをしてみました。
私のしたいことにかなり近くなりました。
ありがとうございます。
すみませんが、３つほど、質問させてください。
＞M1に=VLOOKUP("END",$A$2:$E$87,5,FALSE)で"END"の時刻を取得。
１週間を各グループとして、
各週ごとに、End時刻をかえたいです。
その時刻はA列のEndで判断したいのです。
データが１０年あるうちでは 外国の祭日など週末の終わりが特定の曜日の特定の時刻に固定していないので、各週ごとに動くようにしたいのです。
＞M1に=VLOOKUP("END",$A$2:$E$87,5,FALSE)で"END"の時刻を取得。
この VLOOKUP の$A$2:$E$87という範囲を各週ごとに変えたのです。
私も試してみましたが、相対セルにしても 各週ごとにとはうまく動きません。
困っています。
End位置は
A63
A182
A301なので、
各週は
2008/8/27：8/30 A2：J63
2008/9/1：9/6 A64:J182
2008/9/7:9/13 A183:J301
以下、約１０年分続く
祭日等の関係で、１週間＊２４時間（２４行と固定しているわけではなく）
毎週の行数は毎週同じではないのです。
N7:２
O7:１５
となっているときに、
P７に F2:I15と書きたくて、
＝"F"&INDEX(N:N,N7)&":I"&INDEX(O:O,O7)
と書きましたが、うまくいきません。
お忙しいところすみませんが、よろしくお願いします。
私のしたいことにかなり近くなりました。
ありがとうございます。
すみませんが、３つほど、質問させてください。
＞M1に=VLOOKUP("END",$A$2:$E$87,5,FALSE)で"END"の時刻を取得。
１週間を各グループとして、
各週ごとに、End時刻をかえたいです。
その時刻はA列のEndで判断したいのです。
データが１０年あるうちでは 外国の祭日など週末の終わりが特定の曜日の特定の時刻に固定していないので、各週ごとに動くようにしたいのです。
＞M1に=VLOOKUP("END",$A$2:$E$87,5,FALSE)で"END"の時刻を取得。
この VLOOKUP の$A$2:$E$87という範囲を各週ごとに変えたのです。
私も試してみましたが、相対セルにしても 各週ごとにとはうまく動きません。
困っています。
End位置は
A63
A182
A301なので、
各週は
2008/8/27：8/30 A2：J63
2008/9/1：9/6 A64:J182
2008/9/7:9/13 A183:J301
以下、約１０年分続く
祭日等の関係で、１週間＊２４時間（２４行と固定しているわけではなく）
毎週の行数は毎週同じではないのです。
N7:２
O7:１５
となっているときに、
P７に F2:I15と書きたくて、
＝"F"&INDEX(N:N,N7)&":I"&INDEX(O:O,O7)
と書きましたが、うまくいきません。
お忙しいところすみませんが、よろしくお願いします。
>私のしたいことにかなり近くなりました。
近く…日の区切り方とか合っているのでしょうか。
>＞M1に=VLOOKUP("END",$A$2:$E$87,5,FALSE)で"END"の時刻を取得。
>１週間を各グループとして、
>各週ごとに、End時刻をかえたいです。
夏時間→冬時間、冬時間→夏時間の時、範囲の境目で重なりや
どちらの週にも該当しない部分が生じないのでしょうか？
その行にhiやloがあった場合はどうなるんでしょう？
>外国は 冬時間と夏時間があり、その年によって、
>データの区切り時刻が 土曜の朝５時や６時と変わります。
>年初と、年央では、区切り時刻が異なるわけです。
これをENDで区切るだけで出せるんでしょうか？
今時間も配慮してやっていますが、要するに時差の問題なんですよね。
結果をどんな形で考えればいいのでしょう？
先ほど載せた画像のように１日が１行がダーっと下に
続いていく形で考えたらいいのでしょうか？
>その時刻はA列のEndで判断したいのです。
別の方法を考えました。"END"を…A列そのものを使うのをやめ、
J列のグループを
J2に=C2-IF(E2>LOOKUP((C2+7-B2)+"0:01",C:E),0,1) として下方向にフィル。
これで、僕がさきに載せていた画像の式を下に伸ばしていくだけで出来ると
思いますが、どうでしょう？
範囲も気にしなくて済みます。
>P７に F2:I15と書きたくて、
> ＝"F"&INDEX(N:N,N7)&":I"&INDEX(O:O,O7)
>と書きましたが、うまくいきません。
僕ならごく普通に ="F"&N7&":I"&O7 とします。
近く…日の区切り方とか合っているのでしょうか。
>＞M1に=VLOOKUP("END",$A$2:$E$87,5,FALSE)で"END"の時刻を取得。
>１週間を各グループとして、
>各週ごとに、End時刻をかえたいです。
夏時間→冬時間、冬時間→夏時間の時、範囲の境目で重なりや
どちらの週にも該当しない部分が生じないのでしょうか？
その行にhiやloがあった場合はどうなるんでしょう？
>外国は 冬時間と夏時間があり、その年によって、
>データの区切り時刻が 土曜の朝５時や６時と変わります。
>年初と、年央では、区切り時刻が異なるわけです。
これをENDで区切るだけで出せるんでしょうか？
今時間も配慮してやっていますが、要するに時差の問題なんですよね。
結果をどんな形で考えればいいのでしょう？
先ほど載せた画像のように１日が１行がダーっと下に
続いていく形で考えたらいいのでしょうか？
>その時刻はA列のEndで判断したいのです。
別の方法を考えました。"END"を…A列そのものを使うのをやめ、
J列のグループを
J2に=C2-IF(E2>LOOKUP((C2+7-B2)+"0:01",C:E),0,1) として下方向にフィル。
これで、僕がさきに載せていた画像の式を下に伸ばしていくだけで出来ると
思いますが、どうでしょう？
範囲も気にしなくて済みます。
>P７に F2:I15と書きたくて、
> ＝"F"&INDEX(N:N,N7)&":I"&INDEX(O:O,O7)
>と書きましたが、うまくいきません。
僕ならごく普通に ="F"&N7&":I"&O7 とします。
takecさん、繰り返し教えていただきありがとうございます。
＞日の区切り方
あっています。
＞どちらの週にも該当しない部分
日曜日は常にデータがないので、週末で区切るので
問題にはなりません。
＞要するに時差の問題
おっしゃるとおりです。
＞結果をどんな形で…先ほど載せた画像
はい。
＞普通に ="F"&N7&":I"&O7
ありがとうございます。
＞J2に=C2-IF(E2>LOOKUP((C2+7-B2)+"0:01",C:E),0,1)
この関数を理解するのに少し時間をください。
というは、J行のグループ日付をほかの列に動かして、
N列とO列の範囲を変えるのに 私にはもう少し時間がかかるので。
結果がしだい、あらためてご相談させていただきます。
ありがとうございました。
＞日の区切り方
あっています。
＞どちらの週にも該当しない部分
日曜日は常にデータがないので、週末で区切るので
問題にはなりません。
＞要するに時差の問題
おっしゃるとおりです。
＞結果をどんな形で…先ほど載せた画像
はい。
＞普通に ="F"&N7&":I"&O7
ありがとうございます。
＞J2に=C2-IF(E2>LOOKUP((C2+7-B2)+"0:01",C:E),0,1)
この関数を理解するのに少し時間をください。
というは、J行のグループ日付をほかの列に動かして、
N列とO列の範囲を変えるのに 私にはもう少し時間がかかるので。
結果がしだい、あらためてご相談させていただきます。
ありがとうございました。
saru999さん
>15
そうですね、N列、O列も変更が必要でしたね。
これは不親切なことでした。
N5に =MATCH(M5,$J:$J,0)
O5に =N5+COUNTIF($J:$J,M5)-1
で下方向にフィル。書式が勝手に変わってしまったら
標準に戻してください。
範囲は列全体を対象にしてJ:Jとしました。
データ量も多く動きが重くなる可能性がありますから
具体的に限定するのも一つですが。
>J2に=C2-IF(E2>LOOKUP((C2+7-B2)+"0:01",C:E),0,1)
この説明がなかったのも不親切でしたね。
確かに、逆に僕自身がいきなり見せられても、
考えてしまいます。
考え方は
E2の時刻がENDの時刻より大きければC2の日付グループに、
そうでなければ、前日(C2-1)の日付グループにします。
それが =C2-IF(…) です。
IFの中身ですが…
E2と比較するEND時刻をA列を使わずに求めます。
データ各行には曜日を表すB列がありますので、
その週末の日付はC2+7-B2で求められます。
週末の日付（またはそのグループ）の最終行を
LOOKUPしたい…ので、
>LOOKUP(週末の日付+"0:01",C:E)
+"0:01"は 検索値を週末の日付けと、次の開始の
間の値にする為の＋αです。するとLOOKUPは検索値を
超えない最大の値を探します。
引数を2つしか指定しない場合はC:E範囲は縦長なので
右端の列＝E列の値が返ります。
これで、週末の最終行の時刻が取り出せます。
それをE2と比較し、日付が変わっている場合、どちらの
グループにするかを調整します。
>15
そうですね、N列、O列も変更が必要でしたね。
これは不親切なことでした。
N5に =MATCH(M5,$J:$J,0)
O5に =N5+COUNTIF($J:$J,M5)-1
で下方向にフィル。書式が勝手に変わってしまったら
標準に戻してください。
範囲は列全体を対象にしてJ:Jとしました。
データ量も多く動きが重くなる可能性がありますから
具体的に限定するのも一つですが。
>J2に=C2-IF(E2>LOOKUP((C2+7-B2)+"0:01",C:E),0,1)
この説明がなかったのも不親切でしたね。
確かに、逆に僕自身がいきなり見せられても、
考えてしまいます。
考え方は
E2の時刻がENDの時刻より大きければC2の日付グループに、
そうでなければ、前日(C2-1)の日付グループにします。
それが =C2-IF(…) です。
IFの中身ですが…
E2と比較するEND時刻をA列を使わずに求めます。
データ各行には曜日を表すB列がありますので、
その週末の日付はC2+7-B2で求められます。
週末の日付（またはそのグループ）の最終行を
LOOKUPしたい…ので、
>LOOKUP(週末の日付+"0:01",C:E)
+"0:01"は 検索値を週末の日付けと、次の開始の
間の値にする為の＋αです。するとLOOKUPは検索値を
超えない最大の値を探します。
引数を2つしか指定しない場合はC:E範囲は縦長なので
右端の列＝E列の値が返ります。
これで、週末の最終行の時刻が取り出せます。
それをE2と比較し、日付が変わっている場合、どちらの
グループにするかを調整します。
ＭＫＴ ＤＡＮＣＥＲさん、ありがとうございます。
＞＊１として文字列ならエラー
なるほど。
エラー対策なのですね。
＞「Ｌ２」=IF(and($O1<>$O2,$O1<>"日足close"),F2,"")
せっかく教わったので、
「Ｌ２」=IF(AND($O1<>$O2,$O1<>ISTEXT(O1)),F2,"")
とかいてみたら、うまくいきましたが、
次に
=IF(AND($O1<>$O2,$O1=ISNUMBER(O1)),F2,"")
L16も始値がでなくなってしまいました（汗
ということで、ISNUMBERは失敗でした。
私にはその理由はわかりませんでした。
エラー対策は難しいですね。
勉強になりました。
＞MAX($G2,$M3の部分は
なるほど。
２行目・１６行目にある
始値・高値・安値・終値
が その日の 数字ということですね。
それにしても、いろいろな解法があるのですね。
ありがとうございました。
＞＊１として文字列ならエラー
なるほど。
エラー対策なのですね。
＞「Ｌ２」=IF(and($O1<>$O2,$O1<>"日足close"),F2,"")
せっかく教わったので、
「Ｌ２」=IF(AND($O1<>$O2,$O1<>ISTEXT(O1)),F2,"")
とかいてみたら、うまくいきましたが、
次に
=IF(AND($O1<>$O2,$O1=ISNUMBER(O1)),F2,"")
L16も始値がでなくなってしまいました（汗
ということで、ISNUMBERは失敗でした。
私にはその理由はわかりませんでした。
エラー対策は難しいですね。
勉強になりました。
＞MAX($G2,$M3の部分は
なるほど。
２行目・１６行目にある
始値・高値・安値・終値
が その日の 数字ということですね。
それにしても、いろいろな解法があるのですね。
ありがとうございました。
takecさん,お世話になっています。
先ほどの件ですが、
やはり、区切りの始の行の日付が１日ずれてしまいます。
J１６
J４０
J87
J111
のセルの日付です。
その理由は
＞＞＞J2に=C2-IF(E2>LOOKUP((C2+7-B2)+"0:01",C:E),0,1)
上記セルでは
のIF文が 本来は「０」となるべきなのですが、
「１」となるようです。
なお、Endのある行は おそわった関数で
正常に数字が出ています。
何度もすみませんが、お時間のあるときに、
よろしくお願いします。
追記）
週末が５：００なので、
0時〜5時が IF文＝１となるべきなのですが、
0時〜６時が IF文＝１となっていて、
区切り時刻の次の6時が問題のようです。
=LOOKUP((C3+7-B3)+"0:01",C:E)
を だすと、6：00になっていました。
ためしに、End行の E63に、20：00といれても、
=LOOKUP((C63+7-B63)+"0:01",C:E)
LOOKUP文は 6：00のままでした。
これは私のEXCELのエラーなのでしょうか？？？
ほかの方のEXCELでは どのようになるのでしょうか？
コメントいただけると助かります。
先ほどの件ですが、
やはり、区切りの始の行の日付が１日ずれてしまいます。
J１６
J４０
J87
J111
のセルの日付です。
その理由は
＞＞＞J2に=C2-IF(E2>LOOKUP((C2+7-B2)+"0:01",C:E),0,1)
上記セルでは
のIF文が 本来は「０」となるべきなのですが、
「１」となるようです。
なお、Endのある行は おそわった関数で
正常に数字が出ています。
何度もすみませんが、お時間のあるときに、
よろしくお願いします。
追記）
週末が５：００なので、
0時〜5時が IF文＝１となるべきなのですが、
0時〜６時が IF文＝１となっていて、
区切り時刻の次の6時が問題のようです。
=LOOKUP((C3+7-B3)+"0:01",C:E)
を だすと、6：00になっていました。
ためしに、End行の E63に、20：00といれても、
=LOOKUP((C63+7-B63)+"0:01",C:E)
LOOKUP文は 6：00のままでした。
これは私のEXCELのエラーなのでしょうか？？？
ほかの方のEXCELでは どのようになるのでしょうか？
コメントいただけると助かります。
え〜と。。。
いま、いろいろと確認をしています。
まず、
書式設定の確認
C列は
セルの書式設定＞表示形式＞日付
E列は
セルの書式設定＞表示形式＞時刻
となっています。
これは 大丈夫なようですが。。。
１つきになったのは、
当初のデータは
正確には D列に日付がありました。
D63
2008.08.30
これを
C63
=CONCATENATE(LEFT(D63,4),"/",MID(D63,6,2),"/",RIGHT(D63,2))
とかいて、
2008/08/30
と表示させています。
これが 問題なのでしょうか？？？
お気づきの点がありましたら、よろしくお願いします。
いま、いろいろと確認をしています。
まず、
書式設定の確認
C列は
セルの書式設定＞表示形式＞日付
E列は
セルの書式設定＞表示形式＞時刻
となっています。
これは 大丈夫なようですが。。。
１つきになったのは、
当初のデータは
正確には D列に日付がありました。
D63
2008.08.30
これを
C63
=CONCATENATE(LEFT(D63,4),"/",MID(D63,6,2),"/",RIGHT(D63,2))
とかいて、
2008/08/30
と表示させています。
これが 問題なのでしょうか？？？
お気づきの点がありましたら、よろしくお願いします。
＞肝心のJ40の式
式の画像です。
＞C列の日付が右詰のものと、左詰めのもの
C列の日付をすべて下記の式にしたら、今度はLOOKUPのK行の数字がすべて16時になってしまいました（大汗
＞C63
=CONCATENATE(LEFT(D63,4),"/",MID(D63,6,2),"/",RIGHT(D63,2))
貴方の式を
最初、教わったときに、J2にコピペしたときに J２がエラーになりました。
そこで、C2だけ日付を手入力したら正常に動きました。
その下J列はオートフィルをしました。
今見ると、
手入力したのが、
C２
2008/8/27
その他の
=CONCATENATE(LEFT(D63,4),"/",MID(D63,6,2),"/",RIGHT(D63,2))
は
2008/08/27
となっていますね。
まず、
=CONCATENATE(LEFT(D3,4),"/",MID(D3,7,1),"/",RIGHT(D3,2))
とMIDを変えてみましたが、、、
うまくいきません。
式の画像です。
＞C列の日付が右詰のものと、左詰めのもの
C列の日付をすべて下記の式にしたら、今度はLOOKUPのK行の数字がすべて16時になってしまいました（大汗
＞C63
=CONCATENATE(LEFT(D63,4),"/",MID(D63,6,2),"/",RIGHT(D63,2))
貴方の式を
最初、教わったときに、J2にコピペしたときに J２がエラーになりました。
そこで、C2だけ日付を手入力したら正常に動きました。
その下J列はオートフィルをしました。
今見ると、
手入力したのが、
C２
2008/8/27
その他の
=CONCATENATE(LEFT(D63,4),"/",MID(D63,6,2),"/",RIGHT(D63,2))
は
2008/08/27
となっていますね。
まず、
=CONCATENATE(LEFT(D3,4),"/",MID(D3,7,1),"/",RIGHT(D3,2))
とMIDを変えてみましたが、、、
うまくいきません。
saru999さん
>「39690」と変な数値になっているようです。
これはシリアル値です。日付形式といっても数値の一種です。
1900/1/1を1とする日付に対応した連番のことです。
間違ってはいないようです。
また「39690」の後に小数点以下の数字があると思いますが、
それは時刻部分の数値です。＋αした時間数値が加算されています。
そこまでは、こちらと変わりません。
>=CONCATENATE(LEFT(D63,4),"/",MID(D63,6,2),"/",RIGHT(D63,2))
とすると、文字列扱いになっているのかもしれません。
=DATE(LEFT(D63,4),MID(D63,6,2),RIGHT(D63,2))と書き換えてみて
ください。
>「39690」と変な数値になっているようです。
これはシリアル値です。日付形式といっても数値の一種です。
1900/1/1を1とする日付に対応した連番のことです。
間違ってはいないようです。
また「39690」の後に小数点以下の数字があると思いますが、
それは時刻部分の数値です。＋αした時間数値が加算されています。
そこまでは、こちらと変わりません。
>=CONCATENATE(LEFT(D63,4),"/",MID(D63,6,2),"/",RIGHT(D63,2))
とすると、文字列扱いになっているのかもしれません。
=DATE(LEFT(D63,4),MID(D63,6,2),RIGHT(D63,2))と書き換えてみて
ください。
>メモ帳をコピーして、再び元のC列に戻す。
該当範囲を選択状態でコピー、そのまま右クリックして、
形式を選択して貼り付け で 「値」にチェックを入れＯＫ で。
で、最終目的ですが…達成できたのでしょうか？
>始値＝F2
>高値＝MAX（F2:I15)
>安値＝MIN(F2:I15）
>終値＝I15
この作業を2500回程されるわけではありませんよね…。
セル範囲を出した右、Q,R,S,T列に
OPEN HI LO CLOSE 列に出すなら
Q5に=INDIRECT("F"&N5)
R5に=MAX(INDIRECT(P5))
S5に=MIN(INDIRECT(P5))
T5に=INDIRECT("I"&O5)
として、下方向にフィルで。
もう、お済みでしたでしょうか？
該当範囲を選択状態でコピー、そのまま右クリックして、
形式を選択して貼り付け で 「値」にチェックを入れＯＫ で。
で、最終目的ですが…達成できたのでしょうか？
>始値＝F2
>高値＝MAX（F2:I15)
>安値＝MIN(F2:I15）
>終値＝I15
この作業を2500回程されるわけではありませんよね…。
セル範囲を出した右、Q,R,S,T列に
OPEN HI LO CLOSE 列に出すなら
Q5に=INDIRECT("F"&N5)
R5に=MAX(INDIRECT(P5))
S5に=MIN(INDIRECT(P5))
T5に=INDIRECT("I"&O5)
として、下方向にフィルで。
もう、お済みでしたでしょうか？
takecさん、
ＭＫＴ ＤＡＮＣＥＲさん、
Yujinさん、
そして、多くの方にアドバイスをいただきありがとうございました。
おかげさまで、解決できました。
お礼申し上げます。
私と同様の質問を持って後からこのスレッドをご覧になる方のために、
今回の貴重なる手法をここでまとめさせていただきます。
過去ログとして参考になる方がいれば幸いです。
質問）
日本時間の１時間足を 週末のニューヨークで区切り、日足（始値・高値・安値・終値）にしたい。
当初データ）
D列 年月日
E列 時刻
F列 open
G列 hi
H列 lo
I列 close
J列 出来高
データ元
１２１証券
FX（為替）
１H足。
答え 式）
B列 =WEEKDAY(C2)
C列 =DATE(LEFT(D2,4),MID(D2,6,2),RIGHT(D2,2))
K列 =C2-IF(E2>LOOKUP((C2+7-B2)+"0:01",C:E),0,1)
L列 日付を入れてオートフィル
M列 =MATCH(L2,$K:$K,0)
N列 =M2+COUNTIF($K:$K,L2)-1
O列 ="F"&M2&":I"&N2
始値 P列 =INDIRECT("F"&M2)
高値 Q列 =MAX(INDIRECT(O2))
安値 R列 =MIN(INDIRECT(O2))
終値 S列 =INDIRECT("I"&N2)
みなさま、ありがとうございました。
ＭＫＴ ＤＡＮＣＥＲさん、
Yujinさん、
そして、多くの方にアドバイスをいただきありがとうございました。
おかげさまで、解決できました。
お礼申し上げます。
私と同様の質問を持って後からこのスレッドをご覧になる方のために、
今回の貴重なる手法をここでまとめさせていただきます。
過去ログとして参考になる方がいれば幸いです。
質問）
日本時間の１時間足を 週末のニューヨークで区切り、日足（始値・高値・安値・終値）にしたい。
当初データ）
D列 年月日
E列 時刻
F列 open
G列 hi
H列 lo
I列 close
J列 出来高
データ元
１２１証券
FX（為替）
１H足。
答え 式）
B列 =WEEKDAY(C2)
C列 =DATE(LEFT(D2,4),MID(D2,6,2),RIGHT(D2,2))
K列 =C2-IF(E2>LOOKUP((C2+7-B2)+"0:01",C:E),0,1)
L列 日付を入れてオートフィル
M列 =MATCH(L2,$K:$K,0)
N列 =M2+COUNTIF($K:$K,L2)-1
O列 ="F"&M2&":I"&N2
始値 P列 =INDIRECT("F"&M2)
高値 Q列 =MAX(INDIRECT(O2))
安値 R列 =MIN(INDIRECT(O2))
終値 S列 =INDIRECT("I"&N2)
みなさま、ありがとうございました。
- mixiユーザー
- ログインしてコメントしよう！
|
|
|
|
