VBAでとかる箇所のデータをもとに
別の箇所からデータを引っ張ってくるコードを書いて
いざ実行してみると、上手く値が返ってこなかったのです。
エクセルの関数でいうとのことの
VLOOKUP関数の要領でやっていたのですが。
あれっ？って思い
今度は個別にMATCH関数を使用して
データの整合性を確認した見たところ、
やはりエラー値が返ってきます。
見た目には同じ値が入っているので、
結果は返ってくるはずなのに、エラー値が出る。
…ということはそれぞれのデータは別ものということです。
さて、何が理由か調べてみました。
まず、データの内容は数字、片方は0から1000くらいまで整数、
もう片方は、小数点以降2桁までの数値がある値で、
その値に100倍掛けして、結果的に整数に変えてます。
例): 0.02×100 =2 / 10.35×100 =1035
元々のデータはCSVから取り込んだデータです。
さて、まず原因について、判明した限りをお話しします。
どうやら、小数点以下の数値というかExcelの機能が
悪さをしていたようです。
図込みで、ちょっと説明します。
まず、メモ帳(Text)に0.123456789123456789と入力します。
小数点以下、18桁ですね。
そのテキストをそのままExcelに貼り付けます。
セルの書式は標準です。
セルの幅を自動でワイドにすると、そこで表示される文字は【0.123456789】となります。
しかし、セルの書式を数値にして、小数点以下30桁まで表示とします。
すると、出てきた表示は【0.123456789123456000000000000000】となってました。
最初にテキストで作成した文字が【0.123456789123456789】で
最終的にExcel内で処理されているのは【0.123456789123456】まで
そして、6の次の7は四捨五入されず、0に置き換えられています。
既にこの段階でユーザーの思いは儚く打ち砕かれているわけですね。
もう、ソッコーパーン!ですよ。
そして、このファイルを一旦CSVファイルとして保存してみます。
そのファイルを開きなおしてみると…
どうでしょうか。のっけから嫌な予感しかしません。
セルのA1にカーソルを合わせ、数式バーを確認したところ
【0.123456789】と表示が出ています。
そこで、その下にMATCH関数を入れてみます。
B6にはテキストに一番最初に起こした【0.123456789123456789】を入れて
その下のB7にはCSVファイルのA1の値をコピーし、メモ帳に貼り付け、
それを再度コピーし、CSVファイルのB7に貼ったもの、
それに対して=MATCH(B6,$A$1,0)の式を当て込んでみたところ、
何と、元々の値は一致しないというエラー値が返ってきたのです。
ぶべらっ～!! (-3*)
もう、この時点で、元の【0.123456789123456789】は存在しないことになっています。
ところが、A1とA2、同じ値が入っているように見えますが
実際に見直してみると
値が違うことが分かります。
CSV化することで、その時のデータの書式により
保存される内容が変わってきます。
初めてこのファイルを見た人は
すでに真実は夢の彼方なのです。
さてさて、ここまで余計なことを含め、お話してきましたが
そもそもはデータが一致しないというお話でした。
その為、100倍掛けした数値を一旦Int(Integer)に置き換えてみたところ
きっちりとかみ合い、結果希望するデータを導き出すことが出来ました。
原因はおそらく元々のCSVファイルデータにおいて
小数点以下でも取り切れない箇所に、余計なデータが隠れてたとしか
推測することが出来ません。
プログラムの世界ではFloatやDoubleといって
この浮動小数点に対する細かい型が決められており
計算もきっちり行われるのですが、
こと、Excelに関しては、まず書式で小数点以下30桁まで設定できるにも拘らず
小数点15桁までしか表示がされません。
また、関数の一致に関しても同じ扱いになります。
というか入れられないものは入れられないよのスタンスみたいです。
(IEEE 754 仕様に準拠らしい)
結構いい加減。
なお、15桁以上の数字を表示するためには
書式を文字列にすればいらしいのですが
これもいったんCSVファイルにしてしまうと、15桁まで扱いになってしまいます。
関数をかけた結果、あっているはずの答えがエラー値で返ってきてしまうようであれば
まず、この辺りを疑い、
しかるべきデータ型(Int,Double)などに置き換えてみてください。
あと、Rondなどで四捨五入する方法もあります。
0 件のコメント:
コメントを投稿