ExcelでCSVデータや外部システムのデータを取り込んだとき、数値に「人」「km」「マイル」などの単位が付いた状態になっていることはよくあります。
例えば次のようなデータです。
- 100マイル
- 50マイル
- 10人
- 25km
見た目としては分かりやすいのですが、この状態のままだとExcelで計算ができないことがあります。
なぜなら、Excelは「100マイル」のような値を数値ではなく文字列として扱ってしまうからです。
そのため、合計や掛け算などを行おうとするとエラーになったり、計算がうまくいかなかったりします。
そこで今回は、「100マイル」などの文字列から数値だけを取り出す方法を紹介します。
Excelの関数を使えば、単位を簡単に取り除くことができます。
今回の課題
よくあるケースとして、CSVや別のシステムからデータをExcelに取り込むと、数値のすべてに「～人」や「～km」など単位が付いていることがあります。
そのままだとExcelはこれを文字列として扱うため、数値計算ができません。
例えば合計を出そうとしても、正しく計算されない場合があります。
そこで今回は、JALの「マイル」という単位が付いていた場合を例に、数値だけを取り出す方法を解説します。
使う関数
今回使う関数は次の2つです。
LEFT関数
文字列の左側から指定した文字数を取り出す関数です。
LEN関数
文字列の長さ（文字数）を取得する関数です。
この2つを組み合わせることで、末尾の単位だけを削除することができます。
結論！これで解決
結論！これで解決
もしセルA1に「100マイル」と入っている場合、次の式を使います。
=LEFT(A1,LEN(A1)-3)
この式の意味を分解してみます。
まず、
LEN(A1)
は、A1の文字数を取得します。
「100マイル」は「1、0、0、マ、イ、ル」の6文字です。つまり、LEN(A1)は6になります。
次に、
LEN(A1)-3
という部分があります。
これは「マイル」の3文字を除いた文字数の意味です。つまり、6 − 3 = 3文字になります。
「～人」みたいに単位が1文字の時はLEN(A1)ー1を使おう！
最後にLEFT関数です。
LEFT(A1,3)
という意味になるので、左から3文字を取り出すことになります。
その結果、取得される値は
100
となります。
これで「100マイル」という文字列から、数値部分だけを取り出すことができました。
応用：前に単位が付いている場合
場合によっては、単位が後ろではなく前に付いていることもあります。
例えば次のようなデータです。
- ＄100
- ￥250
- ＃500
この場合は、RIGHT関数を使います。
=RIGHT(A1,LEN(A1)-1)
または、単位の長さが3文字の場合は次のようになります。
=RIGHT(A1,LEN(A1)-3)
この式は次の意味になります。
- 全体の文字数を取得
- 単位の文字数を引く
- 右側から残りの文字を取り出す
こうすることで、数値が何桁でも先頭の単位を削除して数値だけを取得できます。
応用：単位が1文字の場合
例えば次のようなデータです。
- 100人
- 25人
- 8人
この場合は「人」が1文字なので、次の式で解決できます。
=LEFT(A1,LEN(A1)-1)
この式は、
- 文字列の長さを取得
- そこから1文字引く
- 左から取り出す
という意味になります。
結果として「100人」から100だけを取り出すことができます。
逆に全ての数値に単位をつけたい場合
逆に、数値だけのデータに単位を追加したい場合もあります。
「1」を「マイル」という単位付きにしたい場合は、CONCAT関数を使います。
=CONCAT(A1,"マイル")
この式の意味は、A1の値と「マイル」という文字を結合するという意味です。
例えばA1が「1」なら、結果は
1マイル
になります。この方法を使えば、数値データに簡単に単位を追加できます。
結合に関して詳しくは、別記事にまとめました。
まとめ
CSVデータや外部システムのデータをExcelに取り込むと、数値に「人」「km」「マイル」などの単位が付いていることがあります。
この状態ではExcelはそれを文字列として扱うため、計算ができない場合があります。
そんなときは、次の関数を使えば簡単に解決できます。
単位が後ろにある場合
=LEFT(A1,LEN(A1)-3)
単位が前にある場合
=RIGHT(A1,LEN(A1)-3)
単位が1文字の場合
=LEFT(A1,LEN(A1)-1)
また、逆に数値に単位を付けたい場合は
=CONCAT(A1,"マイル")
を使えば簡単に追加できます。
Excelでは、こうした文字列操作の関数を覚えておくと、CSVデータや外部データの加工がとても楽になります。
単位付きのデータで困ったときは、ぜひ今回紹介した方法を試してみてください。
コメント