[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『vlookupとsumproductの速度比較』（FX見習い）
はじめまして
sumproductは万能でvlookupの用に条件に合う数値の取得演算が
できますが速度を比較したときどちらが早いでしょうという質問です。
いろいろ状況に寄るかとはおもいますが…
環境
Ver Excel2003 OS WindowsXP CPU PentiumD MEM 2GB です。
以下、少し長くなりますが経緯等（FXの専門用語がまじります）
ツールのパフォーマンス向上を考えいろいろ調査試行錯誤中です。。。
ニックネームにもあるとおりFXに関するツールを考えてます。
そのツールの機能概要
取り扱い通貨は30種類で指定可能、最大保有ポジションは40件までです。
（外貨決済通貨に伴う円換算、レバレッジ等、基本Fxツールにある
細々データ表示はあります。全部は割愛させて頂きます）
たいした規模ではないと思うですがDDEでMT4からデータがリアルタイムに送られ
レートに伴う損益計算が通貨別集計といった内容を常に更新する物です。
取引実績等保存計算機能は有りません。
動かすと再計算がぱらぱら表示されるような状況です。
Excel2007だとエンジンのパフォーマンスが上がっているのか
再計算は表示されません。
今後最大保有ポジション数は上げていきたいのでいまのうちに
より効率のいい物を考えてます。
今までやってみた最適化
・揮発性関数の撤廃 ・自作ループ関数の撤廃（セル関数に全て移行） VBAはリアルタイムに関係しないスワップの付与程度 ・不要オブジェクトの除去など
最後についでといってはなんですが、
VBAマクロ殆ど無し状況でのパフォーマンス測定
とかできるのでしょうか？
その他、パフォーマンス向上になる案を頂ければ助かります。
http://msdn.microsoft.com/ja-jp/library/aa730921%28v=office.12%29.aspx#aa730921_topic4
も見ましたが理解についていけなかったりで
全部対応できてなかったりで。。。orz
ながながなりましたがよろしくお願いします。
>いろいろ状況に寄るかとはおもいますが… ↑ これに尽きます。 具体的な数式と計算範囲が分からないと始まらないです。
>最大保有ポジションは40件までです。 これもよく分からないです。 結局、最大何件の損益評価するのですか？ そのレコード数によりますよ。
一つひとつのレコードを個別に計算させておけば、 単純にSUMIFくらいで集計できそうなものです。
ネットポジションで損益計算するなら大した数にならないので その計算で遅くなるとは考えにくいです。
ネットポジションや損益は、データベース関数(頭にDの付く関数)で 出せるのではないかと思います。(具体案は頭にないです)
Vlookupを何に使っているのかも分かりません。 VlookupとSumproductを同じ様に使うなんてことは、 関数で遊ぼうと思った時くらいしかやりません。 成程と思える様な具体例を示していただかないと、 納得いかないです。
(半平太) 2011/04/08 13:43
回答ありがとうございます。
まずは、いろいろ不備な点があった事お詫びします。
正直、いろいろ見直す点が多いとは思ってます。 まずは機能実現＞機能改善の繰り返しで エクセルを熟知してないですし、ネット情報での つぎはぎかも知れません。
コミュ力不足申し訳なく思います。 整理力不足かも知れませんがなるべく具体例を交え答えます。 よろしくお願いします。
質問に答えていきます。 >最大保有ポジションは40件までです。 これもよく分からないです。 結局、最大何件の損益評価するのですか？ そのレコード数によりますよ。
具体的に例を提示します（１枚１万通貨とした売買例です） *ポジションリスト A B C D E F 通貨ペア 売/買 取得レート 評価レート 取得pip数 損益 1 ドル円 買い 79.80 88.00 20 2000円[2*10000] 2 ユーロドル 売り 1.4444 1.4442 2 176円[2*88*10000]
(pip数とはレート動く最小単位と思って下さい) 上記の場合２ポジション、２レコードです。 F2で88を掛けてるのは外貨建て決済から円建てに変えているためです
*通貨ペアリスト AA AB AC AD 1 通貨ペア BID ASK 換算通貨ペア 2 ドル円 88.00 88.20 3 ユーロドル 1.4442 1.4443 ドル円
*通貨ペア単位設定リスト AAA AAB 1 ドル円 100 2 ユーロドル 10000
A,B,Cは手入力セル D1:IF(AND($A1<>"",$B1<>"",$C1<>""),VLOOKUP($A1,$AA$1:$AC$30,IF($B1="買い",2,3),FALSE),"") E1:IF(AND($A1<>"",$B1<>"",$C1<>""),IF($B1="買い",$D1-$C1,$C1-$D1)*VLOOKUP($A1,$AAA$1:$AAB$30,2,FALSE),"") F1:E1*IF(VLOOKUP($A1,$AA$1:$AA$30,5,FALSE)<>""),VLOOKUP(VLOOKUP($A1,$AA$1:$AA$30,5,FALSE),$AA1:$AC30,IF($B1="買い",2,3),FALSE),1) といった具合です。
SUMPRODUCTでは D1を例に =IF(AND($A1<>"",$B1<>"",$C1<>""),SUMPRODUCT(--($AA$1:$AA$30=$A1),IF($B1="買い",$AB$1:$AB$30,$AC$1:$AC$30)),"")
と考えてます。
長くなりましたがなぜSUMPRODUCTかというとVLOOKUPは内部処理的に条件比較の のループと思える事と、SUMPRODUCTは配列積だけなので早くなりそうなイメージ があったと言うのが調べている理由です。後SUMIFSの代替としてSUMPRODUCTを利用 するケースもあったのも理由のひとつです。
DSUMも有る事自体は分かってましたが、適応方法、速度の観点でまだ検証 していない状況です。
ポジションリストにはスワップ(外貨/円建て)[利息]項目、約定平均レート、合算損益 約定日付、経過年月日もありますが上記と処理系統も異なりますし書き表しきれない ので計算式は書いてません。
以上です。
>最大保有ポジションは40件までです。 > : : : >上記の場合２ポジション、２レコードです。
と云うことは、現状、処理データ行数は40ですね。
その程度で計算速度を云々されるのには、違和感を持ちます。 どんな計算式でもエクセル君は瞬時に結果を出してくれると思います。
> D1:IF(AND($A1<>"",$B1<>"",$C1<>""),VLOOKUP($A1,$AA$1:$AC$30,IF($B1="買い",2,3),FALSE),"") > D1:IF(AND($A1<>"",$B1<>"",$C1<>""),SUMPRODUCT(--($AA$1:$AA$30=$A1),IF($B1="買い",$AB$1:$AB$30,$AC$1:$AC$30)),"")
上二式の計算速度比較がメインテーマなのでしたら、VLOOKUPの方が早いと思います。
VLOOKUPは、AA列に通貨ペアがないか探して、ヒットした行の2列目か3列目をみる。 つまり平均15回(←30÷2)プラスアルファの計算時間です。
SUMPRODUCTは、AA列全部チェックした後、真偽値を数値に変換、 AB列またはAC列全部の行と掛け算をし、全行を足し上げている。 つまり、いつも120回(←30x4)の計算時間です。
それぞれの1回が同じ処理時間ではないですが、 明らかにSUMPRODUCTは余分なことをやり過ぎています。 (通貨ペアの行だけピンポイントで見ればいいんですから。)
F1の数式は、不正確すぎて、評価できません。
(半平太) 2011/04/09 09:21
答えて頂いててなんなのですが、 なにか悪い質問でもしたのでしょうか？
>その程度で計算速度を云々されるのには、違和感を持ちます。 >どんな計算式でもエクセル君は瞬時に結果を出してくれると思います。
何もExcelの遅さを批判してる訳ではありません そうとらえられてる感が見られるのですが… 残念です。
自分のソースを疑うことは当然で思った事を 検証しているだけの事です。 （パファーマンスを考える事自体高度な事は百も承知です）
これは仕事ではなく、趣味の範疇ではあります。（お金は発生してません） なので半平太さん、世間一般が思われている常識の範疇を超えた 操作検証で質問だったかもしれません。 他にもっと見直す点が有るんでしょうねきっと。 ２，３点の具体例を出すだけでも数式が長かったり、掲示板で表すにも 今の自分には正直無理がありました。
いろいろ勉強になりました。 ありがとうございました。
>なにか悪い質問でもしたのでしょうか？
ありゃ、ご気分を害されたようで、誠に済みませんでした m(__)m
(半平太) 2011/04/09 16:30
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.