
[Excel]データ集計をどう行うか? 実際の自治体データをもとに考える⑪ テキスト形式のデータを数値に直す(データクレンジング)
このシリーズでは、サンプルデータを茨城県ホームページ 「旅館業」 ページの下の方の「7.旅館業法に基づく許可施設一覧」にある 旅館業法に基づく許可施設 として、実際に集計しながら、その方法を説明しています。
過去記事は、マガジン「実際の自治体データで集計をしてみる」でどうぞ。
前記事「総客室数と総定員の合計欄を作る(枠はコピーで使い回す)」では、「営業の種類」別の「総客室数」と「総定員」を出すための枠を増やしました。
ファイルは以下です。実際に操作しながらだと、分りやすいと思います。

普通なら、この枠に計算式を入れていけばいいのですが、今回は、その前にやることがあります。
テキスト形式のデータは計算できない
それは・・・、

「DATA」シートの「総客室数」「総定員」のデータには、セルの左上に緑の三角が付いています。
これ、なんでしょう?
セルを選択してみると、「!」という△の警告マークが出て、
「このセルにある数値が、テキスト形式か、またはアポストロフィではじまっています。」と表示されます(日本語的には「数値が」より「数値は」の方が理解しやすいかと。)。

「 ’ 」(アポストロフィ)で始まっていないので、この数値はテキスト形式ということになります。
いずれにせよ、これだと計算できません。
試しに、最下行まで移動して、合計を出してみますが・・・

合計が出ません・・・。さてどうしましょう?
「セルの書式選択」から「数値」を選択しても・・・

数値(右寄せ)にはなりません。困ります。
でも、大丈夫。数値に修正できます。
テキスト形式のデータを数値に変える方法
①警告マークから修正する
一番上のセルに戻って、警告マークをクリックすすると、エラー対処の選択肢が出ます。

ここで「数値に変換する」を選択/クリックすると・・・

「26」が右寄せになり、左の緑三角マークも消えました。
テキスト形式だった数値が、普通の数値に直ったということです。
同じことを隣の「101」でも行います。

これで、「総定員」のデータも数値になりました。
しかし、修正したセルを、他のセルに「形式」のみコピーしても、直りません。
セルの右下にカーソルを当てて「+」を出し、ダブルクリックすると・・・上のセルと同じ数値が入ってしまいます。これはCtrl+Zで取り消します。
「列」全体を範囲指定した上で「警告マーク」から修正しようとしても、そそもそも「警告マーク」が出てきません。
こんな時は、「列」全体ではなく、データが入っている部分だけを範囲指定します。そうすれば、警告マークが出て、修正出来ます。
範囲指定は、Shiftを押しながらのスクロールや、↓ キーでも出来ますが、データが多いと面倒です(スクロールに時間が掛かります。)。
ショートカット Shift + Ctrl + ↓ で範囲指定
そんな時は、範囲指定したい最初セルをクリックし、
Shift + Ctrl + ↓ を同時押しします。
そうすると・・・

今後の記事で問題にします。
一瞬で、データの最後まで範囲指定されます。
(途中に空白行があると、うまく範囲指定されない場合があります。)
並んだ二つの列「総客室数」「総定員」の一番上のデータを選択した後に、
Shift + Ctrl + ↓ を押せば、2つの列を範囲指定できます。

ただし、画面もデータの最後に移動してしまうので、一番上に戻します。
右端のスライドバーで一気に一番上に持ってくる または Shift + Alt + ↑からのEsc などが楽でしょう。
一番上のデータの警告マークをクリックし、「数値に変換する」をクリックすれば・・・

数値に戻りました(右寄せになる。)。

これで集計可能です。
「総客室数」の列を選択してみると

合計が出ています。
(データがテキスト形式の時には、この合計は出ませんでした。)
テキスト形式のデータを数値に直す方法は他にもあります。
② データに1を掛ける
テキスト形式のデータに1を掛けると数値に戻ります。
=セル名*1

この場合、別の列が必要になります。
この後、セル右下にマウスを当て、「+」が出たらダブルクリックすれば、オートフィルにより、下のセルに自動で計算式が入ります(複数列を一緒に行えます。)。
*オートフィルは、左右いずれかの列にデータが入ってないと使えませす。なお、数値になったデータは計算式を含んでいるので、計算式を消す必要があります。
列全体をコピーしたうえで、そのまま「値」のみ貼り付け(上書き)をすれば計算式は消えます。
元のセルに「値」のみ貼り付けて、追加した列は削除するのも手です。
なお、データを1で割っても(=データセル/1 )同じ結果になります。
③ VALUE関数を使う
テキスト形式のセルをVALUE関数で指定しても数値に直せます。
=VALUE(セル名) *最後の「 ) 」を入れずにEnterでも大丈夫です。
VLAUEは「価値」という意味ですが、「データを値にする」関数になります。valueは小文字でも構いません。Enter押下後に大文字になります。

この場合も、別の列が必要になります。
また、②と同様の操作で、下のセルに計算式を反映させたうえで、計算式を取り除きます。
④「形式を選択して貼り付け」の「乗算」「除算」を使う
空いているセルに「1」を入れます。
その1をコピーしたら(これ重要)、
テキスト形式から数値に直したいセルを範囲指定します。
この場合は、項目名(1行目)を含む列全体を範囲指定して構いません。

「1」をコピーしてから数値に直したいセルを範囲指定します。
項目を含んだ列全体でも構いません。
その後、「形式を選択して貼り付け」を開きます。
右クリック → S → S でも(非推奨)、
Ctrl + Alt + V でも(一般的だけど押しづらい?)
Alt → E → S でも(私はこれ。指運びが自然)、構いません。
形式を選択して貼り付け」の小ウィンドウが開くと、「1」が点線で囲まれていることが分かります。
ここから、右下の「乗算」を選びます。Mを押しても選択できます。
すると・・・

選択した範囲が右寄せになり、数値になったことが分かります。

「1」は消しておきます。
「乗算」とは「掛け算」という意味です。
上の操作は、範囲指定したセルに、コピーしておいた「1」を掛ける、というものです。つまり、上の②と同じことを、計算式なしでやった、ということになります。
なお、「除算」(割り算)を選んでも同じ結果になります。
いずれの場合も、最初に入れた「1」は消しておく必要があります。
⑤ 「データ」の「区切り位置」を使う
列を1つだけ選択し、
メニューの「データ」から「区切り入り」を選び、
「区切り位置指定ウィザード」が開いたら・・・

右下の「完了」をクリックすると、数値になります。
以上、テキスト形式のデータを数値に戻す方法でした。
分かりやすいのは、①でしょう。
②以下は、こういう方法もある、という程度で。
以上で、テキスト形式だったデータが、計算できる数値に戻りました。
今回のデータは、おそらく、専用システムで作られたデータをダウンロード(エクスポート)したために、本来数値であるべきデータがテキスト形式になってしまったと思われます。
本当は、HPに掲載する前段階で、このあたりは修正しておいてほしいところですが、まだまだこの手のものが散見されます。
そのため、エクセルで集計できる形に直す必要があります。
このような作業を「データクレンジング」(データ洗浄)と呼ぶそうです。
データクレンジングは多岐に渡りますが、いずれ主なものを整理してお伝えしたいと思います。
というわけで、次回は数値に戻したデータを使って、「営業の種類」別に「総客室数」「総定員数」を出します。
、