【Excel】数字と文字が混在した値のクリーニング
総務省のルールに従って統計表を直していくシリーズの第２回目
目視では数値のように見えるけど、スペースが入力されていて計算ができてない…なんて経験をしたことはありませんか？
本記事では、数字と文字が混在した値のクリーニングについて解説いたします。
＜目次＞
- 概要
- データクリーニング
- 文字列の削除
- ワークシート上で作業
- パワークエリで作業
- 注釈の分割
- ワークシート上で作業
- パワークエリで作業
- 最後に
見た目は数字でも数値データでなければ計算できない
総務省が公表した『Excelの統一ルール』では「数値データは数値属性とし、⽂字列を含まないこと」がチェックポイントに挙げられています。
Excelは入力された値に対して、通貨、日付、時間、パーセンテージなど、様々な形式での表示が可能ですが、決められたパターンから逸脱していたり、単位を一緒に入力してしまうと、値は“文字列”になってしまい計算ができなくなってしまいます。
チェック項目1-3の例1及び例2では、単位や▲、桁区切りのカンマやスペースが入力された表、例3では値に注釈が書き込まれている表が例として挙げられていますので、これらの表を修正してみましょう。
データクリーニング
文字列の削除
例1と例2の表を使います。
例としては分けられていましたが、文字もスペースもやることは同じなので、表は一つにまとめてあります。
 |
| 左が悪い例、右が良い例 |
ワークシート上で作業
値を置換する
ホームタブの「検索と選択」から“置換”を開きます。
表を選択して、Ctrl＆Hでも開くことができます。
“検索する文字列”に削除したい文字を入力し、“置換後の文字列”には何も入力せずに“すべて置換”を押します。
マイナスを示す「▲」は、“置換後の文字列”に「-（マイナス）」を入力して置換します。
「円」に、「,（カンマ）」「␣（スペース）」を削除し、「▲」は「-（マイナス）」に置換して完了です。
 |
| 置換後は6・7行目の計算式も機能している |
パワークエリで作業
データの取得
表を選択して、データタブの「データの取得と変換」の中から“テーブルまたは範囲から”をクリックします。
表を選択して右クリック、“テーブルまたは範囲からデータを…”というメニューからでも開くことができます。
範囲を確認してOKを押すと、データを取り込むことができます。
データの型を変更する
列名の左側に、それぞれの列の“データの型”が表示されています。
通常は取り込まれた時点で自動判別されデータの型が変更されますが、列の中に数値データのセルと文字列データのセルがある場合は、値が混在していることを示すアイコンが表示されます。
 |
| 混在していることを示すアイコン |
この状態だと置換ができないため、一旦“テキスト”に統一します。
アイコンをクリックし、“テキスト”をクリックします。
値を置換する
該当する列を選択して、ホームタブ（もしくは変換タブ）から、“値の置換”を開きます。
“検索する値”に削除したい文字を入力し、“置換後”には何も入力せず、OKを押します。
データの型を変更する
全ての列を選択して、ホームタブ、「変換」の「データ型：すべて▼」の中から、“整数”を選択します。
あるいは、変換タブ「任意の列」の中から“データ型の検出”をクリックすれば、自動的に判別されてデータ型が選択されます。
 |
| データ型が変更された |
閉じて読み込む
「ホームタブ」、「閉じて読み込む」をクリックし、パワークエリを閉じます。
クエリを選択して右クリック、「読み込み先」を開いて、「テーブル」にチェックを入れ、読み込む場所を確認してＯＫを押します。
 |
| 右クリック⇒読み込み先 |
あとはお好みで列幅や書式設定を調整し、作業完了です。
 |
| 作業完了！ |
注釈の分割
例3の表を使います。
 |
| 左が悪い例、右が良い例 |
ワークシート上で作業
列の挿入
先ず列を分割する場所を作ります。
分割したい値がある右隣に列を挿入します。
ドラッグせずに、Ctrlを押しながらそれぞれクリックして複数選択し、右クリックして“挿入”を選択します。
区切り記号で値を分割
置換したい文字がある列を選択して、データタブ、データツールの中から“区切り位置”を開きます。
注釈を示す文字列の前に入力されている、半角スペースで区切りを付けます。
次へをクリックし、区切り文字の選択肢の中から“スペース”にチェックを入れて完了を押します。
 |
| プレビューが確認できるので安心 |
商品Ｂの列も同様の手順で値を分割します。
 |
| 値の分割 |
値の置換
a)を「冷凍」、b)を「調理済」にそれぞれ置換します。
置換の手順は前述のとおりですので割愛します。
列名とビジュアルの編集
列名を入力し、罫線の追加、列幅の調整を行って完了です。
 |
| 作業完了 |
パワークエリで作業
データの取得
手順は先述の通りです。
値を分割する
商品Ａの列を選択した状態で、ホームタブ、「変換」、「列の分割」の中から“区切り記号による分割”を選択します。
自動的に判別が行われ「スペース」が選択されているはずですので、確認して“OK”を押します。
商品Bの列も同様に分割します。
 |
| 列を分割することができた |
列名の編集
列名を編集します。
列名をダブルクリックし、例3の良い例に倣って編集を行います。
値の置換
a)を「冷凍」、b)を「調理済」にそれぞれ置換します。
商品A.2の列を選択して、ホームタブ、“値の置換”を開いて置換を行います。
商品B.2の列も同様に作業します。
閉じて読み込む
「ホームタブ」、「閉じて読み込む」をクリックし、パワークエリを閉じます。
クエリを選択して右クリック、「読み込み先」を開いて、「テーブル」にチェックを入れ、読み込む場所を確認してＯＫを押します。
最後に
いかがだったでしょうか？
サンプルファイル程度のデータクリーニングであれば、ワークシート上での作業のほうが効率的でしたが、もっと込み入ったクリーニングが必要な場合や、その他の修正作業を伴う場合はパワークエリで作業したほうが結果的に手間を省くことができそうな印象があります。
クリーニング手法については関数を使う方法もありますので、それについては別の機会で解説いたします。
今回はここまで。
それではまた！
動画もチェック！
是非参考にしてください！
【総務省のルール紹介動画】
https://youtu.be/0VZshK6hw3U
【総務省が定めたExcelのルール】
https://www.soumu.go.jp/main_content/000723626.pdf
#Excel #パワークエリ #値の置換 #列の分割 #区切り記号 #データクリーニング
コメント