CSVの金額や数量が、取り込んだ後に小さくなったり、1000倍のような値になったりしたら、元の文字列・出力仕様・数値変換のロケールを分けて確認します。数値型になったことや、エラーが出ないことだけでは、正しい値だと判断できません。
今回のExcel 2024のPower Query検証では、文字列1.234は日本語(日本)・英語(米国)指定で1.234、ドイツ語(ドイツ)指定で1234になりました。この記事では、架空CSVの4行を3ロケールで変換した12条件の結果と、誤りに気付いたときの確認順を説明します。
対象はWindows版Excel 2024のロケールを明示したPower Query変換です。画面のクリック操作を最初から最後まで実測した記事ではありません。CSVのダブルクリックや既定の自動取込の結果は、この実測からは判断しません。
列区切り・小数記号・桁区切り・ロケールは別の指定
| 確認するもの | 役割 | 今回の例 |
|---|---|---|
| 列区切り | 1行を複数のフィールドに分ける | US素材はコンマ、DE素材はセミコロン |
| 小数記号 | 整数部分と小数部分を分ける | US素材は「.」、DE素材は「,」 |
| 桁区切り | 数値の桁を読みやすくまとめる | US素材は「,」、DE素材は「.」 |
| 変換ロケール | 文字列を数値などの型へ解釈する規則 | en-US/ja-JP/de-DEを個別に指定 |
コンマがあるから列区切りとは限りません。1,234.50をコンマ区切りCSVの1フィールドに入れる今回の素材では、"1,234.50"と二重引用符で囲みます。CSVの構文としての引用符を除いたフィールド値は1,234.50です。
逆に、列がずれている問題を数値ロケールだけで直すことはできません。行数や列の分かれ方から確認する場合は、CSVの行数・カンマ・引用符の確認方法を参照してください。
MicrosoftのCsv.Documentの説明でも、列区切り、文字コード、引用符の扱いは個別のパラメーターです。国名だけで実ファイルの区切りや数値記号を決めず、出力元の仕様を確認します。
元CSVの4行と、出力元が宣言した期待値
入力は2026年11月2日に確認した架空データです。2ファイルともUTF-8 BOM付き・CRLF、見出し1行とデータ2行、3列で、末尾の確認用文字はすべてENDです。次の値は業務データではありません。
US素材:コンマ区切り、出力仕様en-US。小数記号はピリオド、桁区切りはコンマと宣言しています。
case_id,value,marker
U_group,"1,234.50",END
U_dot,1.234,END
この出力仕様での期待値は、U_groupが1234.5、U_dotが1.234です。
DE素材:セミコロン区切り、出力仕様de-DE。小数記号はコンマ、桁区切りはピリオドと宣言しています。
case_id;value;marker
D_group;1.234,50;END
D_dot;1.234;END
この出力仕様での期待値は、D_groupが1234.5、D_dotが1234です。セミコロンはこの素材で選んだ列区切りで、ドイツ由来のファイルがすべて同じ仕様という意味ではありません。
今回の通常の数値表記では、日本語(日本)ja-JPと英語(米国)en-USは小数「.」・桁区切り「,」が共通です。ドイツ語(ドイツ)de-DEを日本式と呼んだり、欧州全体を同じ形式として扱ったりしないようにします。
実測:4行×3ロケールの12条件
最初に全列をテキストとして取り込み、原文字列・ID・ENDが一致することを確認しました。その後、valueのコピーをロケールごとに独立して数値化しています。別のロケールで変換した数値を、次のロケールへ変換し直した結果ではありません。
| ID | 原文字列 | 指定ロケール | 実測値・状態 | 期待値 | 意味の一致 |
|---|---|---|---|---|---|
| U_group | 1,234.50 | en-US | 1234.5 | 1234.5 | 一致 |
| U_group | 1,234.50 | ja-JP | 1234.5 | 1234.5 | 一致 |
| U_group | 1,234.50 | de-DE | 変換エラー | 1234.5 | 不一致 |
| U_dot | 1.234 | en-US | 1.234 | 1.234 | 一致 |
| U_dot | 1.234 | ja-JP | 1.234 | 1.234 | 一致 |
| U_dot | 1.234 | de-DE | 1234 | 1.234 | 誤値 |
| D_group | 1.234,50 | en-US | 変換エラー | 1234.5 | 不一致 |
| D_group | 1.234,50 | ja-JP | 変換エラー | 1234.5 | 不一致 |
| D_group | 1.234,50 | de-DE | 1234.5 | 1234.5 | 一致 |
| D_dot | 1.234 | en-US | 1.234 | 1234 | 誤値 |
| D_dot | 1.234 | ja-JP | 1.234 | 1234 | 誤値 |
| D_dot | 1.234 | de-DE | 1234 | 1234 | 一致 |
結果は期待値一致6件・誤値3件・変換エラー3件でした。表の数値はすべてnumber型です。3件のエラーはDataFormat.Errorで、メッセージは「Number に変換できませんでした。」、詳細にはそれぞれの原文字列が残りました。
en-USとja-JPの結果は、この4行ではすべて同じです。一方、1.234はde-DEで1234になるため、US素材の期待値1.234には合わず、DE素材の期待値1234には合います。同じ見た目の文字列でも、どの意味で出力されたかが必要です。
数値型への変換成功と、値の正しさを分ける
12件中、数値型への変換自体は9件成功しています。しかし、そのうち3件は出力元が宣言した値と不一致でした。例えばUSのU_dotをde-DEで変換すると1234となり、期待値1.234との差は1232.766です。DEのD_dotをen-USまたはja-JPで変換すると1.234となり、期待値1234との差は−1232.766です。
ExcelのISNUMBERがTRUEでも、数値の意味まで正しいとは限りません。今回はMの列型・値型、ExcelのISNUMBER・TYPE・ISTEXT、原文字列と期待値を併せて確認しました。
エラー3件は監査用の数値欄をnullにし、エラー内容を別列へ残したものです。エラーが0になったわけではありません。このブックでは空の参照に対するTYPEも1になったため、TYPE=1だけを成功条件にはしていません。
また、numberは浮動小数点の数値です。上の差は読取値を比較するための10進表現で、Excel内部の厳密な10進計算を保証するものではありません。型の仕様はMicrosoft:Power Queryのデータ型で確認できます。
値がおかしいときは、元文字列から確認し直す
- 原本を残す。変換結果で元CSVを上書きせず、どのファイル・出力仕様・列を確認しているかを特定します。
- 列の分かれ方を確認する。文字コード、列区切り、引用符を確かめ、ID・列数・原文字列を照合します。列ずれと数値の解釈違いを分けます。
- 出力元へ意味を確認する。小数記号・桁区切り・単位と、照合用の期待値を確認します。仕様が不明なら、国名や見た目で推測せず数値変換を保留します。
- 変換前の文字列のコピーに、合うロケールを指定する。既に誤った数値を文字列に戻すだけで原文が復元したとは扱いません。元CSVからの取込ステップへ戻って照合します。
- 値と型を両方照合する。エラーの有無だけで終わらず、期待値・件数・対象ID・原文字列・単位を確認し、未確認行は分けて残します。
ピリオドやコンマの一括置換、表示形式の変更、一律1000倍・1000分の1だけで修正する方法にはしません。今回の4行も、原文字列と出力仕様がなければ、1.234と1234のどちらを採るべきか決められません。
画面から指定する場合、Microsoftの公式案内は、列見出しを右クリックし、Change Type > Using Localeで型とロケールを選ぶ方法を示しています。これは公式資料に基づく案内であり、今回その画面操作を一連で完遂した実測ではありません。既定の地域設定を変える必要性とは分けて考えてください。
今回実行したMの数値変換部分は次のとおりです。RawTextは原文字列を保持する取込ステップで、実測ではUS_rawまたはDE_rawを参照しています。比較ごとに元の文字列からコピーを作り、末尾のロケールだけを変えました。この断片だけで任意のCSVを取り込めるわけではありません。
CopyOfOriginal = Table.DuplicateColumn(RawText, "value", "converted"),
Converted = Table.TransformColumnTypes(
CopyOfOriginal, {{"converted", type number}}, "en-US"
)
関数のculture引数はTable.TransformColumnTypesの公式仕様に基づきます。具体的な1234への変化やエラーは、仕様からの推測ではなく上の実測結果です。
今回の検証方法と、保存読戻しで確認した範囲
検証環境はWindows・Excel2024Retail 64bit、バージョン16.0.20527.20112です。Windows地域形式はja-JP、Excelはシステムの区切り記号を使う設定で、小数「.」・桁区切り「,」でした。これらの全体設定は変更していません。
検証用の別Excelプロセスに新規ブックを作り、COMからWorkbook.Queries.AddでMを登録し、実際のPower Queryエンジンで処理しました。CSVの取込はCsv.Document、結果のシート読込はMicrosoft.Mashup.OleDb.1を使用しています。従来のテキスト取込の結果で代用していません。
全4行の原文字列・ID・END、12条件の値・型・エラー、確認式、8本のMクエリを記録しました。検証用xlsxを保存して閉じ、同じExcelインスタンス内で読取専用に開き直した後、全セル・数式・M定義が一致しました。元CSV2本のhashも不変でした。
確認していないのは、GUI一連操作、CSVダブルクリック、既定ロケールによる自動型判定、他の文字コード、混在ロケール、全角数値、空白による桁区切り、他のExcel版・Mac・Webです。Excel再起動後の再更新や別PCへの移送まで確認した結果でもありません。今回の12条件をそれらの動作保証にしないでください。
数値の意味を確認してから、集計へ進む
まず1行について、原文字列と出力元の期待値を並べ、列区切りが正しいか、数値変換に使ったロケールは何かを確かめます。1行が合っても全件を確認したことにはならないため、業務の対象範囲に応じた照合を続けてください。
管理番号のように計算する必要がない列は、金額と同じ数値変換をかけず、CSVの先頭ゼロ・長い管理番号を文字列で保持する方法で役割を分けます。空欄・空文字・NULLの違いが問題なら、CSVの空欄・空文字・NULLの実測も別に確認してください。
ロケールに合わない変換は、エラーになる場合と、数値のまま誤る場合があります。表示を整える前に原文字列・仕様・実値を照合することが、誤った集計を持ち込まないための確認になります。
