[[20210521094936]]　『PowerQueryとExcelで計算結果が違う』（you）　ページの最後に飛ぶ

『PowerQueryとExcelで計算結果が違う』（you）

過去10年位の売上集計を行っているファイルがあります。
余りにもファイルが重たいので、ここ数年分をそれぞれ別ファイルにして、PowerQueryで集約し、集計を行おうと思っていました。

実際取込んでみたところ、小数点がある列の合計値がExcelと異なりました。
データ型を「通貨」に変更し、元データも小数点を第３位まで表示した状態で取り込みましたが、合計値は変わらずでした。

計算結果は、Excel＜PowerQuery　となります。

小数点データの扱いに何かあるのだと思いますが、これを回避する方法等ご存じでしたら教えてください。

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

 パワークエリの事はわかりませんが
 元ファイルもどんなものかわかりませんし､
 パワークエリの詳細エディタの中身もわからないのでみんな何をしているのかわからないと思います

 大体小数点の扱いに問題が有ると思っているならばパワークエリにした時に
 四捨五入などしていないか確認がまず必要かなと思います
 Excelも表示位置を変更しただけなら見た目は四捨五入になっていますが元データは四捨五入していません
 四捨五入すると計算結果も変わってくると思いますのでそのあたりではないでしょうか

 そうでなければ元データ自体が違うものなのではないかなと思います
（なるへそ） 2021/05/21(金) 12:43

 (1) １つのブックに１０年以上のデータが集約されている
 (2) (1)のブックをから、 1年分で１ブック に分割した
 (3) (2) 複数年のブックをPower Queryで集計した
 という状況でしょうか。
 どのくらい違うんでしょう？

 実数の計算は、計算誤差がつきまといます。
 Excelは計算誤差を少なくするような工夫がいろいろされてるみたいで、結構複雑です。
 でも、ある程度の計算誤差はしかたないいっちゃ仕方ないです。

 検証
 A1にタイトルを打ち込みます。
 A2に =Round(Rand(),5) と式を打ち込んで、最終行(A1048576セル)までコピー
 A列を選択して、コピーして、値貼り付け

 C2セルに　=SUM(A2:A1048576)

 D2セルに、以下のクエリの結果を読み込む
　let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"A", type number}}),
    計算された合計 = List.Sum(変更された型[A])
  in
    計算された合計
　
 結果
 C2セル　524271.376480006
 D2セル　524271.376480003
 このくらいは違っても驚かないというか、しょうがない感じします
 で、質問者さんの計算結果の相違はどれくらいでしょうか
（´・ω・`） 2021/05/21(金) 13:11

＞なるへそさん、（´・ω・`）さん
コメントありがとうございます。
状況は（´・ω・`）さんの言われている状況です。
判り辛くてすみません。

多少の誤差では驚かないのですが、実際は「80,606」（円）位の差が出ます。
流石にこれは・・・と思っている次第です。
※挙げて頂いた例で試しましたが、このデータでの違いはありませんでした。

色々試して分かったことがありました。
　・Excel側を「通貨」から「数値」に変更した後、
　「any」（自動判定のまま）として取り込むと、合計値は合致する
　・上記方法で取り込んだ後に型変換を行うと、
　　小数点以下の表示がなくなり合計値が変わる（80,606円の差）
　・「販売単価」「差益」といった項目にも小数点があるが、
　　それらの列は型変換を行っても小数点以下の表示はあり、
　　それらの合計値は合致する
　・メジャーを作ろうとすると「String型が・・・」と言ったメッセージが表示され、
　　作ることはできない（当たり前ではありますが）
　　PowerPivotのテーブル画面で型の変換をすると、誤差80,606円の差となる
（おまけ）
　・一度Accessに取り込み、[データの取得と変換］を使ってExcelに読み込ませたデータは、
　　元データの合計値と合致した。

念のため、他の列と比べて違うところを探してみると、
　・上から５件目くらいのデータで金額がマイナス（キャンセル処理と思われる）されている
　・小数点が最初に現れる位置も、他の列と比べて200件以上後に出てくる
という違いがありました。

上記のことから、「型の変換」が何かしらの影響を与えているのかなと思いネットで調べると、「”銀行まるめ”が行われる」といった情報がありました。
実際、Excel上で「銀行まるめ」の計算式で確認したところ、PowerQueryでの集計結果に一番近い数字が出ました。
※誤差３円

仕様と思って、集約方法を考え直してみます。
お付き合いいただき、ありがとうございました。
（you） 2021/05/21(金) 16:46

コメント返信：

[ 一覧(最新更新順) ]

[[20210521094936]] 『PowerQueryとExcelで計算結果が違う』（you） ページの最後に飛ぶ

[[20210521094936]]　『PowerQueryとExcelで計算結果が違う』（you）　ページの最後に飛ぶ