【Excel】Power Queryでデータ型を一括変換する!Excelの文字列から数値や日付への型変更手順

Excelで大量のデータを扱う際、数値や日付が文字列として扱われてしまい、計算や並べ替えができないといった問題に直面することがあります。

このようなデータ型の不一致は、手作業で一つずつ修正すると膨大な時間がかかります。

しかし、Power Queryを使えば、これらのデータ型変換を一括で効率的に行うことができます。

この記事では、Power Queryを使用してExcelの文字列データを数値や日付型に変換する具体的な手順を解説します。

Power Queryにおけるデータ型の重要性

Power Queryは、データの整形や変換を自動化するための強力なツールです。データ型が正しく設定されていることは、その後のデータ分析や集計を正確に行う上で非常に重要です。

例えば、数値として認識されていない文字列の列は、合計や平均を計算できません。また、日付として認識されていない文字列は、期間で絞り込んだり、時系列分析を行ったりすることが困難になります。

Power Queryでは、各列のデータ型を明示的に指定することで、これらの問題を未然に防ぐことができます。

Excelデータソースの読み込みとデータ型の確認

まず、Power Queryを使用してExcelデータを読み込みます。データがPower Queryエディターに表示されたら、各列のデータ型を確認します。

通常、Power Queryは自動的にデータ型を推測しますが、それが常に正しいとは限りません。特に、数値や日付が文字列として読み込まれている場合があります。

Excelファイルの読み込み手順

Excelシートにデータがある場合、以下の手順で読み込みます。

1. 「データ」タブを選択
   Excelのリボンメニューから「データ」タブをクリックします。
2. 「データの取得」を選択
   「データの取得」グループにある「ファイルから」を選択し、「Excelブックから」をクリックします。
3. ファイルを選択
   読み込みたいExcelファイルを選択し、「インポート」をクリックします。
4. シートまたはテーブルを選択
   表示されたナビゲーターウィンドウで、取り込みたいシートまたはテーブルを選択し、「データの変換」をクリックします。これにより、Power Queryエディターが開きます。

Power Queryエディターでのデータ型確認

Power Queryエディターが開いたら、各列のヘッダー部分に注目してください。

ヘッダーの左端には、その列のデータ型を示すアイコンが表示されています。例えば、「ABC」は文字列、「123」は整数、「1.2」は10進数、「1/1/1900」は日付、「12:00:00 PM」は時刻、「1/1/1900 12:00:00 PM」は日付/時刻型を表します。

もし、本来数値であるべき列が「ABC」アイコンになっていたり、日付であるべき列が「ABC」アイコンになっていたりする場合は、データ型が正しく認識されていません。

データ型の一括変換手順

データ型を正しく変換するには、Power Queryエディターの機能を使用します。複数の列に対して同時にデータ型を変更することも可能です。

列のデータ型を変更する方法

特定の列のデータ型を変更する基本的な手順は以下の通りです。

1. 対象の列を選択
   データ型を変更したい列のヘッダーをクリックして選択します。
2. 「データ型」ドロップダウンを開く
   「ホーム」タブにある「変換」グループ、「データ型」のドロップダウンメニューをクリックします。または、列ヘッダーのアイコンをクリックして表示されるメニューから選択します。
3. 目的のデータ型を選択
   「整数」、「10進数」、「固定小数点数」、「通貨」、「パーセント」、「日付」、「時刻」、「日付/時刻」、「テキスト」など、目的に合ったデータ型を選択します。
4. 変換の適用
   選択したデータ型に変換が実行されます。

複数の列のデータ型をまとめて変更する方法

複数の列に対して同じデータ型を適用したい場合は、以下の方法が効率的です。

1. 対象の列を複数選択
   Ctrlキーを押しながら、データ型を変更したい複数の列ヘッダーをクリックして選択します。
2. 「データ型」ドロップダウンを開く
   「ホーム」タブにある「変換」グループ、「データ型」のドロップダウンメニューをクリックします。または、選択した列のいずれかのヘッダーアイコンをクリックして表示されるメニューから選択します。
3. 目的のデータ型を選択
   「整数」、「10進数」、「日付」など、目的に合ったデータ型を選択します。
4. 変換の適用
   選択した全ての列に対して、指定したデータ型への変換が一度に実行されます。

特定のデータ型への変換例

文字列から数値への変換、文字列から日付への変換は、ビジネスデータで特に頻繁に行われます。

文字列から数値への変換:

1. 対象列の選択
   数値に変換したい文字列の列を選択します。
2. データ型を「整数」または「10進数」に変更
   「データ型」ドロップダウンから「整数」または「10進数」を選択します。

文字列から日付への変換:

1. 対象列の選択
   日付に変換したい文字列の列を選択します。
2. データ型を「日付」に変更
   「データ型」ドロップダウンから「日付」を選択します。
3. 日付形式の確認(必要に応じて)
   もし変換がうまくいかない場合、日付の文字列形式がExcelで認識できる標準的な形式(例: YYYY/MM/DD, MM-DD-YYYY)でない可能性があります。その場合は、「変換」タブの「データ型」グループにある「書式」オプションや、「列の分割」機能と組み合わせて、日付部分を正しく抽出・整形してからデータ型を「日付」に変換する必要があります。

変換後のデータの読み込み

Power Queryエディターでデータ型の変換が完了したら、その結果をExcelシートに読み込みます。

変換されたデータは、元のExcelファイルに上書きされるわけではなく、新しいシートまたは指定した場所に読み込まれます。これにより、元のデータは安全に保たれます。

Power QueryエディターからExcelへデータを読み込む手順

以下の手順で、変換結果をExcelに反映させます。

1. 「閉じて読み込む」を選択
   Power Queryエディターの「ホーム」タブにある「閉じて読み込む」ボタンをクリックします。
2. 「閉じて次に読み込む」を選択
   ドロップダウンメニューから「閉じて次に読み込む」を選択します。
3. 読み込み先の選択
   「データのインポート」ダイアログが表示されます。「テーブル」を選択し、「既存のワークシート」または「新規ワークシート」のいずれかを選択します。既存のワークシートを選択した場合は、データを配置したいセルを指定します。
4. 「OK」をクリック
   指定した場所に、データ型が変換されたテーブルが読み込まれます。

これで、Power Queryによってデータ型が変換されたデータがExcelシートに表示されます。数値列は合計や平均の計算に、日付列は並べ替えやフィルター処理に利用できるようになります。

データ型変換でよくある問題と対処法

データ型変換は通常スムーズに進みますが、予期せぬ問題が発生することもあります。ここでは、よくある問題とその対処法を解説します。

変換エラーが発生する場合

特定の列でデータ型変換エラー(例: #ERROR!、null)が発生する場合、その列に含まれるデータが指定したデータ型に適合しない可能性があります。

原因:

• 数値列に数字以外の文字(例: “¥1,000” の “¥” や “,”)、空白文字が含まれている。
• 日付列に標準的でない形式の文字列(例: “2023年12月1日”)、無効な日付(例: 2月30日)が含まれている。
• 意図しないデータ(例: 備考欄のテキスト)が混入している。

対処法:

1. エラーが発生している行を確認
   Power Queryエディターで、エラーが発生している行を特定します。
2. エラーの原因となっているデータを修正または除外
   エラーの原因となっているセルを特定し、手作業で修正するか、その行を削除します。
3. データクリーニングのステップを追加
   変換前に、不要な文字の削除(例: “¥”, “,”)、空白文字のトリミング、無効な日付の置換などのデータクリーニングステップをPower Queryに追加します。
4. データ型を「テキスト」として保持するオプション
   どうしても変換できないデータが含まれる場合は、その列のデータ型を「テキスト」のままにしておき、必要に応じて計算時にINT()やDATEVALUE()関数などをExcel側で適用することも検討します。

日付の形式が正しく認識されない場合

「2023/12/01」のような日付文字列を「日付」型に変換しようとしても、正しく認識されないことがあります。これは、Power Queryが日付の区切り文字や順序(月/日、日/月)を誤って推測するためです。

対処法:

1. 「変換」タブの「データ型」オプションを使用
   対象の列を選択し、「変換」タブの「データ型」グループにあるドロップダウンから「日付」を選択します。この際、Excelの地域設定に基づいた日付形式が適用されます。
2. 「書式」オプションで日付形式を指定
   列ヘッダーのアイコンをクリックし、「書式」メニューから「日付」を選択します。ここで、Excelが認識できる日付形式(例: YYYY-MM-DD, MM/DD/YYYY)を選択します。
3. 「列の分割」と「テキストの結合」で整形
   上記の方法でうまくいかない場合、まず列を「区切り記号」で分割したり、「テキストの長さ」で分割して、年・月・日の部分をそれぞれ別々の列にします。その後、これらの列を「YYYY-MM-DD」のような標準的な形式で結合し、最後にデータ型を「日付」に変換します。

数値列のカンマや通貨記号を除去したい場合

Excelで「1,000」や「¥1,000」のように表示されている数値が、Power Queryでは文字列として読み込まれ、さらにカンマや通貨記号が含まれているために数値型への変換ができないことがあります。

対処法:

1. 「置換」機能を使用
   対象の列を選択し、「変換」タブの「テキスト」グループにある「置換」をクリックします。
2. 検索する値と置換後の値を指定
   「検索する値」にカンマ(`,`)や通貨記号(`¥`)などを入力し、「置換後の値」には何も入力せずに(空白にして)「OK」をクリックします。
3. データ型を数値に変更
   不要な文字が除去されたら、列のデータ型を「整数」または「10進数」に変更します。

Power QueryとExcel関数との連携

Power Queryは、Excelの関数と組み合わせてさらに高度なデータ処理を行うことも可能です。

例えば、Power Queryでデータ型を正しく変換した後、Excelシート上でVLOOKUP関数やSUMIFS関数などを使用して、変換された数値や日付データを活用した集計や分析を行うことができます。

また、複雑なデータ整形が必要な場合は、Power Queryで可能な範囲で前処理を行い、Excel関数で最終的な仕上げを行うというワークフローも有効です。

まとめ

Power Queryを使用することで、Excelにおけるデータ型の不一致による問題を効率的に解消できます。

データのインポートからデータ型の確認、一括変換、そしてExcelへの読み込みまでの一連の流れを習得すれば、データ処理の時間を大幅に削減できるでしょう。

次回は、Power Queryの「列のマージ」機能を使ったデータ結合について解説します。ぜひ、今回学んだデータ型変換のスキルと組み合わせて活用してください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】