エクセルで作業をしているとどうしてもそのまま使えないデータに遭遇します。
本当は数字として使いたいのに『文字列』だったり文字数制限があって全角だと入りきらなかったり。
そんな時に使うのが値を変換してくれる関数です。
数値⇔文字列変換の関数
文字列を数値に変換する
データを扱っていると必ず使うのがVALUE関数です。
この関数は見た目が数字の文字列を数値に変換してくれる関数です。
引数は=VALUE(文字列)と非常に単純です。他の関数(LEFT,MID等)で得た結果を数値に変換することも可能で、文字列の部分に式を入れるだけです。
この様に数値に変換することで加算や減算も可能になります。
数値を文字列に変換する
次はVALUE関数とは逆に数値を文字列に変換してくれるのがTEXT関数です。
TEXT関数は変換と同時に表示形式の設定も出来るので非常に重宝する関数です。
引数は=TEXT(値,表示形式)となり表示形式によって様々な表示が可能になります。
表示形式を覚えるのがちょっと大変かもしれませんが幾つかを覚えればあとは応用で何とかなるものです。
表示形式は必ず”(ダブルクォーテーション)で囲う必要があります
TEXT関数で使う表示形式
では表示形式の代表的なものを幾つか説明します。
表示形式 ”0″
まずは “0” これは余り一つでは使用しません。
よく使うのは数値を決まった桁数に揃えて文字列に変換する場合等に使います。
例えば 12345という数字を10桁の左０詰めの文字列にする場合=TEXT(12345,”0000000000″)と０を10個並べるとセルに0000012345と表示されます。
表示形式 ”#”
次は # の使い方ですが、１以上の数値しか表示せず数値を文字列に変換します。
ちょっと分り辛いので例をあげて説明します。セルに12345と入っている場合、表示形式に”#“と設定するとそのまま12345と文字列で表示されます。
しかし、セルが０となっている場合は何も表示されません。
つまり計算結果が０になった場合は表示したくない場合に使うことがよくあります。
VLOOKUP関数等でデータを参照した場合検索値は存在するがセルに
何も入っていない場合、結果が０と表示されます。
他にも表示をカンマ表示し０は０として表示する場合には”#,##0“の様にすると12345は12,345と表示され０も表示されます。
更に通貨表示は”￥#,##0“では￥12,345と表示されます。
他にも千円単位表示にしたい場合は”#,##0,“と最後にカンマを一つ付けることで千円単位に、カンマを二つ付けると百万円単位の表示が出来ます。
“#“を使ってよくあるパターンは”#,##0円“で文字も表示形式の
一部として使えますよ。
表示形式 ”a”
この表示形式はとても面白い表示形式です。
スケジュール表などを作ることが多い場合は使える表示形式で、日付(シリアル値)をその日付に対応する曜日に変換してくれます。
どうでしょう 今まで月、火、水と手入力したりせいぜいオートフィルで入力していたのではないでしょうか。
この表示形式は“aaa”で『日』『月』『火』で表示され“aaaa”で『日曜日』『月曜日』と表示されます。
オートフィルも簡単ですが、日付が変わってしまった時には余計大変な思いをしてしまいます。
この場合は計算式が入っているのでもし日付が変更になっても自動的に曜日も変わってくれます。
日付の表示形式
最後に日付の表示形式です。
日付の表示は様々ですので、代表的な表示を二つ元になる値はもちろん日付(シリアル値)です。
日本で一般的に使っている西暦での年月日表示です
yyyyやmm、ddは表示する桁に合わせて下さい。
例えば8月はｍｍなら08月、mなら8月と表示されます
ただし、年は下二桁での表示もあるため、yyとした場合は2022年が22年と表示されます。
次に和暦での表示です。
上記は元号を略して表示する場合です。
他にも”ggge年m月d日“とすると令和4年8月12日と表示されます。
本来は和暦を出来るだけ使いたくはないのですが未だに行政の書類は和暦ばかりなので仕方なく使って
います。
全角文字⇔半角文字変換の関数
ASC関数で全角文字を半角文字に変換
全角文字を半角文字に変換するにはASC関数を使います。
使い方は単純で=ASC(文字列)のみで文字列の部分はセル参照でもOKです。
住所などは漢字と数字が混在するので入力の仕方によっては全角と半角が混在している場合が多々あります。
これを全て手入力で修正するのは件数が多いとかなり時間のかかる作業となります。
ASC関数は半角文字が存在する場合は半角に変換し存在しない場合は無視してそのまま表示します。
JIS関数で半角文字を全角文字に変換
ASC関数とは逆の変換をするのがJIS関数です。
使い方はASC関数と同じく=JIS(文字列)です（当然セル参照も出来ます）
PHONETIC関数で漢字をフリガナに変換
おまけの様な関数で滅多に使うことは無いかもしれませんが氏名や住所でフリガナが必要な場合に使える関数です。
使い方は=PHONETIC(文字列)でセル参照も可です。
この関数を使うには幾つか注意点があります
まず下記のホームメニューのフォントにある赤丸の部分でフリガナの種類を特定しておく必要があります。
フリガナには『ひらがな』『全角カナ』『半角ｶﾅ』が用意されていますので、必要なカナ種に変更して下さい。
また入力されたままのフリガナが表示されますので違うフリガナの場合は上記のメニューでフリガナの編集を選択しフリガナ自体を書き換えて下さい。
フリガナが全角カナで表示された場合はASC関数で半角に変換してしまうのも手法の一つです。
コピー＆ペーストした文字列の場合フリガナが設定されていない場合があるので注意です。