文字列を数値へ変換して、計算や加工可能なExcelデータへ修正する方法

以前の記事で、総務省から公開された以下の資料をたたき台に
「統計表における機械判読可能なデータの表記方法」
https://www.soumu.go.jp/main_content/000723697.pdf
入力しやすい・加工しやすいデータファイルへの修正方法をご紹介しました。

前回の作業は、文字列として入力された内容を、Excelが数値として認識できるように、セルを分ける作業でした。

今回は、その続きとして、文字列として入力された内容を、
Excelが数値として認識できるように、素材へ戻す作業をみていきましょう
(文字列と数値の意味の理解に不安のある方は、先に一番最後の
文字列や数値?セルの書式の確認方法」をご覧いただくとよいかもしれません) 。

【目次】

「円」や「()かっこ」などの文字列を削除して数値へ・置換
関数を入れておけば毎回同じ作業を繰り返さないで済む
全角を半角へ変換、空白や改行を一括削除する関数
関数を使わずに文字列を数値へ 
文字列や数値?セルの書式の確認方法

今回も例となる表を前述の総務省資料から引用しています。

「円」や「()かっこ」などの文字列を削除して数値へ・置換

まず、数値として使用したい素材に、余計な文字などが含まれて文字列となっているものを

置き換える方法で修正しましょう。
下記左が、総務省資料から再現した表です。置換機能が判る方は適当に読み飛ばしてください。

再現した表は比較のためにそのままにして、右側にコピーした表で作業していきます。

できるだけショートカットキーを使用する方法で、まず「円」を削除してみましょう。
1) Shift + 矢印キー 置き換えたい値のあるセルを範囲指定 (範囲指定しない場合はシート内全ての「円」が置換される)
2) Ctrl + H 「検索と置換」ダイアログが開く(Ctrl + PgUp, PgDn で検索と置換を移動)
3) Tab (Shift + Tab) 又はAlt +下線付きアルファベット で、ダイアログ内の移動

削除するだけの場合は、「置換後の文字列」は何も入力しません。
このまま「円」「▲」「,」を置換・削除していけば、それらのセルが数字のみになります。すると、Excelが自動的に数値と判断し、関数も正しく計算されました。

関数を入れておけば毎回同じ作業を繰り返さないで済む

実務では、クレジットカードの明細や銀行の取引明細などをCSVでエクスポートしても、
それぞれ独特の書式になっていることがあります。

自社の集計表にあわせたり、会計ソフトなどへのインポート作業の際に、毎回同じ作業をするのではなく、
関数を入れた専用のシートを作っておけば、その後の作業時間を少しは短縮できますね。

「円」を削除する列には、以下のような関数を入れてみました。
他にも方法はありますが、今回は判りやすいSUBSTITUTE関数です。

「円」の入っているB5まで同じ関数をいれると、加算演算には合計が現れましたが、SUM関数は0のままです。

見た目は同じ数字ですし、書式も「数値」になっていますが、何かが違うようですね。

そこで、文字列を数値にする関数 VALUE を加えてみました。

SUM関数でも合計が正しく表示されるようになりました。

CD列にも同じ関数を入れていきます。

これで、次回同じようなデータがきても、「元データ貼付」シートに貼るだけで、
一瞬で「集計用」シートに、文字列と数字交じりではない、数値のみの表が現れてきます。

ただし、ここから更に加工する場合は、別のシートまたはファイルに値貼り付けしてからする方が安心です。

全角を半角へ変換、空白や改行を一括削除する関数

次の表は、データの形式などを一括で変換する主な関数です。

見た目では判りにくいですが、元データは半角の空白が複数含まれていたり、
全角と半角の数字が混在していたり…といったデータです。

これらを組み合わせて必要なデータへ一括変換させることができます。

関数を使わずに文字列を数値へ

関数は苦手意識があるという方も、区切り位置機能でも文字列から数値への変換が可能です。

範囲指定後、この画面まで進み、ここで標準=数値を指定して完了…なのですが、

実際は、デフォルトで標準になっているので、
区切り位置ウィザードの最初の画面で「完了」をクリックしても大丈夫です。

仮に文字列になっていたとしても、Excelが、入力されている値を数字であると認識すると、「数値」として処理してくれます。

たとえば全て数字に見えるのに、計算結果がおかしい場合などにこの機能を使ってみましょう。

でもやはり可能であれば、最初からデータの作成時に、数値の項目には数値のみなど、 統一しておくことが大事ですね。

文字列か数値か?セルの書式の確認方法

最後に、書式・表示形式について、簡単にお話しておきます。

該当のセルがどの形式なのかは、上部に表示されています。またはCtrl + 1(数字のイチ)で、セルの書式設定を開き確認、変更可能です。

前回もお話ししたように、書式設定や表示形式は、
素材を飾り立てる透明なラッピングのようなものです。

見栄えよく、わかりやすくするためにこの機能を使います。

データの入力自体は、最低限のシンプルな素材のみにしておくと、
入力作業も楽になり、加工作業もデータになります。

セルは内容ごとに分け、数値は数値のセル、文字・説明などは別のセルに入力しましょう。

誰にでも見やすく使いやすいデータは、作業効率を上げるはずです。
基本的なことばかりだったかもしれませんが、Excelもやはり基本が大事。
新たにデータを作る際のお役に立てましたら幸いです。

関連記事

  1. 所有者不明土地の解消に向けた民事基本法制の見直し
  2. 「収益認識に関する会計基準」適用による法人税法の改正について
  3. 税理士試験
  4. 消費税の軽減税率制度導入により変更となる事務処理
  5. 確定申告に向けた留意点
  6. 企業が従業員の感染予防対策費用を負担した場合の給与課税の取扱い
  7. 消費税軽減税率制度に向けて従業員の教育の必要性
  8. 消費税の総額表示義務の注意点

最近の記事

PAGE TOP