エクセルのちょっといい話（１２） [システムトレード]
エクセルには様々な機能が備わっていますが、よく使用される機能の一つに、並べ替え（ソート）があります。
これは、指定範囲のデータの並び順を変更するための機能であり、行方向もしくは列方向に並んだデータを、昇順あるいは降順で並べ替えることができます。
そのような基本的な機能の他に、優先されるソート条件を最大3通りまで指定できたり、並べ替え順序を決定するためのオプションを設定できたりと、何かと便利な機能です。
しかし、最も頻繁に用いられるのは、単純に元のデータを逆順に並べ替えることではないかと思います。
例えば、株価データをサイトから取得するような場合、通常は、日付の新しい順からシートに取り込まれることになります。
しかし、それをトレーディングシステム等で利用するためには、一般的に日付の古い順に並べ替えてやる必要があります。
そこで、私たちはエクセルのソート機能を用いて、株価データを並べ替えることになるわけですが、取得するデータの種類によっては、一筋縄ではいかない場合があります。
例えば、HYPER SBIでは、以前ご紹介した様々な時系列データの取得において、個別銘柄に限っては過去8営業日ほどの歩み値を取得することができます。
それらはCSV形式で取得可能ですが、それをエクセルで開くと、A～D列の2行目以降に、日付や時間の新しい順にデータが取り込まれていることが分かります。
ちなみに、1行目は表題であり、A列は日付、B列は時間、C列は出来高、D列は約定値となっています。
さて、ここで曲者となるのが時間の列です。データは約定が新しい順に並んではいますが、時間は分単位でしか取得できません。
そのため、同じ時間の中に複数の歩み値が存在することになります。
これをそのまま、エクセルのソート機能を用いて日付や時間の古い順に並べ替えようとすると、出来高や約定値を正しく並べ替えることができません。
そのため、通常は並べ替えを行うための順番を決める数字列を別に作成し、それに対して並べ替えを行うなどといった方法を採る必要があります。
しかし、毎回そのようなことを行っていたのでは面倒ですし、そもそも、範囲を指定した後、メニューを開いて並べ替えを行うこと自体、手間が掛かります。
もっと簡単に、例えばデータを貼り付けるだけで、自動的に並べ替えを行ってくれるようなスタイルシートはできないものでしょうか。
そんなのはマクロを使えば簡単にできる、という声が聞こえてきそうですが、まあ、それは最終手段ということで、ここでは考えないことにします。
実はマクロを使わなくても、エクセル関数だけでデータの上下を入れ替えることは可能です。
HYPER SBIから取得した歩み値データで考えますと、A～D列に取得データが存在することになります。見やすいように、1列空けてF列以降にそれらを並べ替えたデータを求めることにしましょう。
すなわち、行は揃えて、F列に日付、G列に時間、H列に出来高、I列に約定値を並べ替えて求めることになります。
そのためには、まずデータの最終行を知る必要があります。これは、COUNTA関数を使えば容易に実現できます。
F1セルに次式を入力します。
=COUNTA($A:$A)
続いて、データを並び替える計算式を求めます。これは、OFFSET関数を用いても可能かもしれませんが、ここでは分かり易いように、ADDRESS関数とINDIRECT関数を用います。
なお、日付データと時間データを求めるには、ちょっと細工が必要ですので、先に出来高データと約定値データの並び替え計算を行います。まず、H2セルに次式を入力します。
=IF($A2="","",INDIRECT(ADDRESS($F$1-ROW()+2,3)))
=IF($A2="","",INDEX($C:$C,$F$1-ROW()+2))
=IF($A2="","",INDIRECT("C"&$F$1-ROW()+2))
=IF($A2="","",OFFSET($C$1,$F$1-ROW()+1,0))
続いて、I2セルに次式を入力します。
=IF($A2="","",INDIRECT(ADDRESS($F$1-ROW()+2,4)))
=IF($A2="","",INDEX($D:$D,$F$1-ROW()+2))
=IF($A2="","",INDIRECT("D"&$F$1-ROW()+2))
=IF($A2="","",OFFSET($D$1,$F$1-ROW()+1,0))
これらの数式のポイントは、ADDRESS関数内の「$F$1-ROW()+2」の部分です。これは、現在のセルが最初から何行目のデータであるかを求め、最終行からその値分遡ったデータを返します。
すなわち、2行目は最終行と、3行目は最終行の前行と、以下同様に入れ替わることになります。
なお、INDIRECT関数やADDRESS関数については、エクセルのヘルプや解説書、解説サイトなどでご確認ください。
ちなみに、最初のIF関数は、シートにデータを貼り付けるだけで良い様にするためのおまじないです。
続いて、F列の日付データに移ります。これも基本的には、出来高や約定値の場合と同じですが、3月10日のコラムで述べましたように、HYPER SBIから取得した日付データを、エクセルで扱えるシリアル値に変換してやる必要があります。
その方法につきましては、同コラムをご参照ください。ここでは、結果のみを示します。ちょっと長いですが、F2セルに次式を入力します。
=IF($A2="","",DATE(LEFT(INDIRECT(ADDRESS($F$1-ROW()+2,1)),4),
MID(INDIRECT(ADDRESS($F$1-ROW()+2,1)),5,2),
MID(INDIRECT(ADDRESS($F$1-ROW()+2,1)),7,2)))
=IF($A2="","",DATE(LEFT(INDEX($A:$A,$F$1-ROW()+2),4),
MID(INDEX($A:$A,$F$1-ROW()+2),5,2),
MID(INDEX($A:$A,$F$1-ROW()+2),7,2)))
=IF($A2="","",DATE(LEFT(INDIRECT("A"&$F$1-ROW()+2),4),
MID(INDIRECT("A"&$F$1-ROW()+2),5,2),
MID(INDIRECT("A"&$F$1-ROW()+2),7,2)))
=IF($A2="","",DATE(LEFT(OFFSET($A$1,$F$1-ROW()+1,0),4),
MID(OFFSET($A$1,$F$1-ROW()+1,0),5,2),
MID(OFFSET($A$1,$F$1-ROW()+1,0),7,2)))
これは、先日のコラムにおける「$A2」の箇所を、「INDIRECT(ADDRESS($F$1-ROW()+2,1))」に置き換えて、全体をIF関数で括っただけです。
その意味は、お分かりのことと思います。
さて、最後に時間データの並べ替えですが、これも日付データ同様、シリアル値に変換してやる必要があります。
ちょっと長くなりますので、まずは並び替えを考慮せずに、B列の値をそのままシリアル値に変換する方法を考えます。
日付データの場合は桁数が8桁で固定されていましたが、時間データの方は4桁と3桁の場合が存在します。そのため、単純に日付データの場合と同じ方法を採ることはできません。
そこで、そのデータが4桁なのか3桁なのかを判定し、それに応じて処理が異なるようにします。B2セルのデータを変換する数式を次に示します。
=TIME(MID($B2,1,LEN($B2)-2),MID($B2,LEN($B2)-1,2),0)
TIME関数は、3つの数値の組み合わせを時刻のシリアル値に変換するための関数です。3つ目の秒の値は元の時間データには存在しませんから、常に"0"とします。
問題は、時と分の部分ですが、それにはMID関数とLEN関数を用います。
MID関数は先日のコラムでも用いましたので、改めてご説明の必要はないかと思います。ここでのポイントは、LEN関数です。
これは、指定した値の文字数を求める関数で、今回の場合はB2セルの値に応じて"4"か"3"になります。
B2セルの値が4文字の場合は、時の部分は「MID($B2,1,2)」となり、最初から2文字を時間として切り出します。一方、B2セルの値が3文字の場合は、時の部分は「MID($B2,1,1)」となり、最初から1文字を時間として切り出します。
同様に、B2セルの値が4文字の場合は、分の部分は「MID($B2,3,2)」となり、3文字目から2文字を分数として切り出します。一方、B2セルの値が3文字の場合は、分の部分は「MID($B2,2,2)」となり、2文字目から2文字を分数として切り出します。
すなわち、B2セルの値が4文字であっても3文字であっても、時間と分数を正確に切り出すことができるわけです。
あとは、日付の場合と同様に、$B2の箇所をINDIRECT関数で置き換え、最初にIF関数のおまじないを付け加えるだけです。すなわち、次式のようになります。
=IF($A2="","",TIME(MID(INDIRECT(ADDRESS($F$1-ROW()+2,2)),1,
LEN(INDIRECT(ADDRESS($F$1-ROW()+2,2)))-2),
MID(INDIRECT(ADDRESS($F$1-ROW()+2,2)),
LEN(INDIRECT(ADDRESS($F$1-ROW()+2,2)))-1,2),0))
=IF($A2="","",TIME(MID(INDEX($B:$B,$F$1-ROW()+2),1,
LEN(INDEX($B:$B,$F$1-ROW()+2))-2),
MID(INDEX($B:$B,$F$1-ROW()+2),
LEN(INDEX($B:$B,$F$1-ROW()+2))-1,2),0))
=IF($A2="","",TIME(MID(INDIRECT("B"&$F$1-ROW()+2),1,
LEN(INDIRECT("B"&$F$1-ROW()+2))-2),
MID(INDIRECT("B"&$F$1-ROW()+2),
LEN(INDIRECT("B"&$F$1-ROW()+2))-1,2),0))
=IF($A2="","",TIME(MID(OFFSET($B$1,$F$1-ROW()+1,0),1,
LEN(OFFSET($B$1,$F$1-ROW()+1,0))-2),
MID(OFFSET($B$1,$F$1-ROW()+1,0),
LEN(OFFSET($B$1,$F$1-ROW()+1,0))-1,2),0))
これで、必要な数式は全て整いましたので、後はF2～I2セルを最下行（65536行）までコピーします。最後に、必要に応じて書式を整えれば完成です。
このシートを保存しておき、HYPER SBIから取得した歩み値データをA～D列に貼り付ければ、F～I列に日付や時間の古い順に再配置されます。
なお、エクセルの最下行を表示するには、"Ctrl+↓"キーを押すと瞬時に移動できます。最初にF2～I2セルを選択してコピーした後、F65536セルを選択した状態でShiftキーを押しながらF3セルを選択し（F3～F65536セルが選択されます）貼り付ければ簡単です。
今回は、HYPER SBIから取得した歩み値データを並べ替える方法についてご説明いたしましたが、もちろん、どのようなデータであっても、逆順に並び替えることが可能です。
ただし、その場合は、数式を設定する列を変えてやる必要が生じることがあります。その際、ADDRESS関数の列番地を変更する必要がありますので、ご注意ください。
これは、指定範囲のデータの並び順を変更するための機能であり、行方向もしくは列方向に並んだデータを、昇順あるいは降順で並べ替えることができます。
そのような基本的な機能の他に、優先されるソート条件を最大3通りまで指定できたり、並べ替え順序を決定するためのオプションを設定できたりと、何かと便利な機能です。
しかし、最も頻繁に用いられるのは、単純に元のデータを逆順に並べ替えることではないかと思います。
例えば、株価データをサイトから取得するような場合、通常は、日付の新しい順からシートに取り込まれることになります。
しかし、それをトレーディングシステム等で利用するためには、一般的に日付の古い順に並べ替えてやる必要があります。
そこで、私たちはエクセルのソート機能を用いて、株価データを並べ替えることになるわけですが、取得するデータの種類によっては、一筋縄ではいかない場合があります。
例えば、HYPER SBIでは、以前ご紹介した様々な時系列データの取得において、個別銘柄に限っては過去8営業日ほどの歩み値を取得することができます。
それらはCSV形式で取得可能ですが、それをエクセルで開くと、A～D列の2行目以降に、日付や時間の新しい順にデータが取り込まれていることが分かります。
ちなみに、1行目は表題であり、A列は日付、B列は時間、C列は出来高、D列は約定値となっています。
さて、ここで曲者となるのが時間の列です。データは約定が新しい順に並んではいますが、時間は分単位でしか取得できません。
そのため、同じ時間の中に複数の歩み値が存在することになります。
これをそのまま、エクセルのソート機能を用いて日付や時間の古い順に並べ替えようとすると、出来高や約定値を正しく並べ替えることができません。
そのため、通常は並べ替えを行うための順番を決める数字列を別に作成し、それに対して並べ替えを行うなどといった方法を採る必要があります。
しかし、毎回そのようなことを行っていたのでは面倒ですし、そもそも、範囲を指定した後、メニューを開いて並べ替えを行うこと自体、手間が掛かります。
もっと簡単に、例えばデータを貼り付けるだけで、自動的に並べ替えを行ってくれるようなスタイルシートはできないものでしょうか。
そんなのはマクロを使えば簡単にできる、という声が聞こえてきそうですが、まあ、それは最終手段ということで、ここでは考えないことにします。
実はマクロを使わなくても、エクセル関数だけでデータの上下を入れ替えることは可能です。
HYPER SBIから取得した歩み値データで考えますと、A～D列に取得データが存在することになります。見やすいように、1列空けてF列以降にそれらを並べ替えたデータを求めることにしましょう。
すなわち、行は揃えて、F列に日付、G列に時間、H列に出来高、I列に約定値を並べ替えて求めることになります。
そのためには、まずデータの最終行を知る必要があります。これは、COUNTA関数を使えば容易に実現できます。
F1セルに次式を入力します。
=COUNTA($A:$A)
続いて、データを並び替える計算式を求めます。これは、OFFSET関数を用いても可能かもしれませんが、ここでは分かり易いように、ADDRESS関数とINDIRECT関数を用います。
なお、日付データと時間データを求めるには、ちょっと細工が必要ですので、先に出来高データと約定値データの並び替え計算を行います。まず、H2セルに次式を入力します。
=IF($A2="","",INDIRECT(ADDRESS($F$1-ROW()+2,3)))
=IF($A2="","",INDEX($C:$C,$F$1-ROW()+2))
=IF($A2="","",INDIRECT("C"&$F$1-ROW()+2))
=IF($A2="","",OFFSET($C$1,$F$1-ROW()+1,0))
続いて、I2セルに次式を入力します。
=IF($A2="","",INDIRECT(ADDRESS($F$1-ROW()+2,4)))
=IF($A2="","",INDEX($D:$D,$F$1-ROW()+2))
=IF($A2="","",INDIRECT("D"&$F$1-ROW()+2))
=IF($A2="","",OFFSET($D$1,$F$1-ROW()+1,0))
これらの数式のポイントは、ADDRESS関数内の「$F$1-ROW()+2」の部分です。これは、現在のセルが最初から何行目のデータであるかを求め、最終行からその値分遡ったデータを返します。
すなわち、2行目は最終行と、3行目は最終行の前行と、以下同様に入れ替わることになります。
なお、INDIRECT関数やADDRESS関数については、エクセルのヘルプや解説書、解説サイトなどでご確認ください。
ちなみに、最初のIF関数は、シートにデータを貼り付けるだけで良い様にするためのおまじないです。
続いて、F列の日付データに移ります。これも基本的には、出来高や約定値の場合と同じですが、3月10日のコラムで述べましたように、HYPER SBIから取得した日付データを、エクセルで扱えるシリアル値に変換してやる必要があります。
その方法につきましては、同コラムをご参照ください。ここでは、結果のみを示します。ちょっと長いですが、F2セルに次式を入力します。
=IF($A2="","",DATE(LEFT(INDIRECT(ADDRESS($F$1-ROW()+2,1)),4),
MID(INDIRECT(ADDRESS($F$1-ROW()+2,1)),5,2),
MID(INDIRECT(ADDRESS($F$1-ROW()+2,1)),7,2)))
=IF($A2="","",DATE(LEFT(INDEX($A:$A,$F$1-ROW()+2),4),
MID(INDEX($A:$A,$F$1-ROW()+2),5,2),
MID(INDEX($A:$A,$F$1-ROW()+2),7,2)))
=IF($A2="","",DATE(LEFT(INDIRECT("A"&$F$1-ROW()+2),4),
MID(INDIRECT("A"&$F$1-ROW()+2),5,2),
MID(INDIRECT("A"&$F$1-ROW()+2),7,2)))
=IF($A2="","",DATE(LEFT(OFFSET($A$1,$F$1-ROW()+1,0),4),
MID(OFFSET($A$1,$F$1-ROW()+1,0),5,2),
MID(OFFSET($A$1,$F$1-ROW()+1,0),7,2)))
これは、先日のコラムにおける「$A2」の箇所を、「INDIRECT(ADDRESS($F$1-ROW()+2,1))」に置き換えて、全体をIF関数で括っただけです。
その意味は、お分かりのことと思います。
さて、最後に時間データの並べ替えですが、これも日付データ同様、シリアル値に変換してやる必要があります。
ちょっと長くなりますので、まずは並び替えを考慮せずに、B列の値をそのままシリアル値に変換する方法を考えます。
日付データの場合は桁数が8桁で固定されていましたが、時間データの方は4桁と3桁の場合が存在します。そのため、単純に日付データの場合と同じ方法を採ることはできません。
そこで、そのデータが4桁なのか3桁なのかを判定し、それに応じて処理が異なるようにします。B2セルのデータを変換する数式を次に示します。
=TIME(MID($B2,1,LEN($B2)-2),MID($B2,LEN($B2)-1,2),0)
TIME関数は、3つの数値の組み合わせを時刻のシリアル値に変換するための関数です。3つ目の秒の値は元の時間データには存在しませんから、常に"0"とします。
問題は、時と分の部分ですが、それにはMID関数とLEN関数を用います。
MID関数は先日のコラムでも用いましたので、改めてご説明の必要はないかと思います。ここでのポイントは、LEN関数です。
これは、指定した値の文字数を求める関数で、今回の場合はB2セルの値に応じて"4"か"3"になります。
B2セルの値が4文字の場合は、時の部分は「MID($B2,1,2)」となり、最初から2文字を時間として切り出します。一方、B2セルの値が3文字の場合は、時の部分は「MID($B2,1,1)」となり、最初から1文字を時間として切り出します。
同様に、B2セルの値が4文字の場合は、分の部分は「MID($B2,3,2)」となり、3文字目から2文字を分数として切り出します。一方、B2セルの値が3文字の場合は、分の部分は「MID($B2,2,2)」となり、2文字目から2文字を分数として切り出します。
すなわち、B2セルの値が4文字であっても3文字であっても、時間と分数を正確に切り出すことができるわけです。
あとは、日付の場合と同様に、$B2の箇所をINDIRECT関数で置き換え、最初にIF関数のおまじないを付け加えるだけです。すなわち、次式のようになります。
=IF($A2="","",TIME(MID(INDIRECT(ADDRESS($F$1-ROW()+2,2)),1,
LEN(INDIRECT(ADDRESS($F$1-ROW()+2,2)))-2),
MID(INDIRECT(ADDRESS($F$1-ROW()+2,2)),
LEN(INDIRECT(ADDRESS($F$1-ROW()+2,2)))-1,2),0))
=IF($A2="","",TIME(MID(INDEX($B:$B,$F$1-ROW()+2),1,
LEN(INDEX($B:$B,$F$1-ROW()+2))-2),
MID(INDEX($B:$B,$F$1-ROW()+2),
LEN(INDEX($B:$B,$F$1-ROW()+2))-1,2),0))
=IF($A2="","",TIME(MID(INDIRECT("B"&$F$1-ROW()+2),1,
LEN(INDIRECT("B"&$F$1-ROW()+2))-2),
MID(INDIRECT("B"&$F$1-ROW()+2),
LEN(INDIRECT("B"&$F$1-ROW()+2))-1,2),0))
=IF($A2="","",TIME(MID(OFFSET($B$1,$F$1-ROW()+1,0),1,
LEN(OFFSET($B$1,$F$1-ROW()+1,0))-2),
MID(OFFSET($B$1,$F$1-ROW()+1,0),
LEN(OFFSET($B$1,$F$1-ROW()+1,0))-1,2),0))
これで、必要な数式は全て整いましたので、後はF2～I2セルを最下行（65536行）までコピーします。最後に、必要に応じて書式を整えれば完成です。
このシートを保存しておき、HYPER SBIから取得した歩み値データをA～D列に貼り付ければ、F～I列に日付や時間の古い順に再配置されます。
なお、エクセルの最下行を表示するには、"Ctrl+↓"キーを押すと瞬時に移動できます。最初にF2～I2セルを選択してコピーした後、F65536セルを選択した状態でShiftキーを押しながらF3セルを選択し（F3～F65536セルが選択されます）貼り付ければ簡単です。
今回は、HYPER SBIから取得した歩み値データを並べ替える方法についてご説明いたしましたが、もちろん、どのようなデータであっても、逆順に並び替えることが可能です。
ただし、その場合は、数式を設定する列を変えてやる必要が生じることがあります。その際、ADDRESS関数の列番地を変更する必要がありますので、ご注意ください。
よく考えたら、INDIRECT(ADDRESS())は、INDEX()で置き換えることができますね。
大変失礼いたしました。
INDEX関数での表記例を、赤字で追記いたしましたので、ご参照ください。
これで、多少は処理が軽くなるものと思います。
by Ｋフロー (2009-04-12 17:36)
たびたびの修正です。
よく見たら、INDEX関数の書式に誤りがありました。
すぐに修正しようと思ったのですが、現在So-netの管理ページにアクセスし辛い状況となっています。
しばらくしてから修正いたしますので、それまでは赤字部分の式を使用しないようお願い申し上げます。
まずは、コメントにてご報告いたしました。
by Ｋフロー (2009-04-13 10:20)
さきほど、ようやく修正できました。
お騒がせして、申し訳ありませんでした。
赤字の個所が、正しい修正式となります。
by Ｋフロー (2009-04-13 10:41)
お詫びというわけではありませんが、他の方法も記してみました。
これらの中で、どれが一番優れているということは無いように思いますが、多少は処理速度に違いがあるかもしれません。
なお、ファイル容量はいずれも同一です。
by Ｋフロー (2009-04-13 11:38)