【Excel】ユーザー定義で個・人・円を付ける方法:数値を保つ設定と単位換算

数量を「100個」と表示しながら、合計や単価計算には数値の100として使いたい。この場合は、セルに単位を直接入力するのではなく、ユーザー定義の表示形式を設定します。数値と単位を分けて管理すれば、表の読みやすさと集計のしやすさを両立できます。

「100個」という文字列にあとから表示形式を付けても、数値の100には戻りません。また、「g」を「kg」に書き換えるだけでは単位換算になりません。Windows版のデスクトップExcelを中心に、設定方法と確認用の例、既存データの直し方を説明します。

要点: 入力は数値、単位は表示形式にします。換算が必要なら数式で値を変え、CSVへ渡すときは出力した文字列を別に確認してください。

1. 数値の100と文字列の100個を区別する

A2へ100、A3へ50を入力し、どちらにも0"個"という表示形式を付けると、見た目は「100個」「50個」になります。セルに保存された数値は100と50のままなので、=SUM(A2:A3)の結果は150です。

一方、セルへ「100個」「50個」と直接入力した場合は文字列です。範囲を参照するSUMは、その文字列を数値として加算しません。すべてがこのような文字列なら合計は0になりますが、数値が混在していれば数値のセルだけが集計されるため、エラーが出ないまま過少な合計になる点にも注意が必要です。

見た目だけでは違いを判断できません。対象セルを選んで数式バーを見たり、別セルで=ISNUMBER(A2)を使ったりすると確認できます。数値ならTRUE、文字列ならFALSEです。数式が入ったセルでは数式バーに式が出るので、結果の型を確認するにはISNUMBERが役立ちます。

2. 個・人・円の表示形式を設定する

まず単位を付けたい数値セルだけを選択します。見出しや別の単位の列まで含めず、例えば数量のA2:A20に絞ります。Ctrl + 1で[セルの書式設定]を開き、[表示形式]→[ユーザー定義]の[種類]へ書式コードを入力して[OK]を押します。

種類に入力するコード	表示例
0"個"	100 → 100個
0"人"	25 → 25人
#,##0"円"	12500 → 12,500円
0.0"kg"	1.5 → 1.5kg

単位は半角の二重引用符で囲みます。コードはセルへ入力する数式ではなく、[種類]欄に入れる文字列です。例えば次の1行をその欄へ入力します。

#,##0"円"

設定後は、正の数だけでなく0と負数も確認します。合計セルにも単位を表示したい場合は、合計セルの表示形式を別途設定してください。元の数値セルに単位を付けても、別のセルの表示が必ず同じになるとは限りません。

単位を消す場合は、対象セルを選び直して[標準]または必要な桁数の[数値]に変更します。セルの中身を消す必要はありません。Excel for the webではユーザー定義の表示形式を新規作成できないため、必要な設定はデスクトップ版で行います。

3. 0・#・小数点の役割を理解する

0は必要な位置に数字を表示する記号です。0"個"なら値が0のときも「0個」と表示できます。#は不要なゼロを表示しない桁の記号です。「#はすべてのゼロを消す」という意味ではなく、数値の中に必要な0まで削除するものではありません。

3桁区切りの#,##0は、例えば12000を12,000と表示します。小数第1位まで表示するなら0.0、第2位までなら0.00を使います。数量が整数とは限らないなら、0"個"で小数部分を見えなくしてよいかを先に決めます。

表示桁数を減らしても、通常は計算に使う元の値は変わりません。例えば1.24と1.24を小数第1位まで表示すると、見た目は1.2と1.2ですが、合計の元の値は2.48です。表示上の数字だけを足した2.4と一致しないことがあります。帳票上の丸め方をそろえる必要があるなら、表示形式とは別にROUNDなどで計算の規則を定めます。

「表示桁数で計算する」というブック設定は、単位を付けるために必要ありません。数値そのものへの影響がある設定を、見た目の調整のつもりで変更しないでください。まず表示形式だけで目的を満たすか確認します。

4. 入力済みの100個を安全に数値へ直す

すでに「100個」が多数ある場合は、元の列を残して補助列で変換すると、どの値を変えたか照合しやすくなります。A2が「100個」で、その列の非空白データがすべて同じ単位の単純な数量であることを確認したうえで、B2へ次の式を入力します。

=VALUE(SUBSTITUTE(A2,"個",""))

この例の結果は数値の100です。必要な行へコピーし、変換した件数、先頭と末尾、0、小数、負数を元データと比べます。SUBSTITUTEは「個」という文字を取り除く処理なので、「100個入り2箱」のような説明文を正しい数量に直す機能ではありません。「個」と「箱」が混在する列も、先に単位を整理します。

空欄や「未定」があるセルでは変換できない場合があります。エラーをすべて0に置き換えると、未確認の数量がゼロとして集計されます。変換できた数値と、担当者に確認する文字列を分けて扱ってください。列全体への一括置換は、商品名や備考まで変えるおそれがあるため、対象を限定できる場合にだけ使います。

変換結果を採用する際は、元データのコピーを残し、補助列をコピーして[値の貼り付け]を行います。その後で単位の表示形式を設定します。見た目を先に整えるのではなく、数値として集計できることを確認してから表示を仕上げます。

5. gからkgへの換算は値を1000で割る

1500gを1.5kgで表示したいとき、書式の末尾だけを「kg」にすると1500kgと表示され、意味が1000倍変わってしまいます。単位の文字を付ける操作と、数値を換算する操作を混同しないでください。

管理しやすい方法は、A列を「重量(g)」、B列を「重量(kg)」と分けることです。A2が1500なら、B2に次の式を入れます。

=A2/1000

B2の値は1.5になります。B列に0.000"kg"を設定すれば「1.500kg」と表示できます。500gなら0.500kg、1000gなら1.000kgです。1000未満だけ別の単位へ切り替える複雑な表示よりも、列ごとの単位を統一した方が、並べ替えや外部出力時の確認が容易です。

表示形式の末尾にカンマを置いて1000分の1へ縮めて表示する方法もありますが、元の値はgのままです。利用者がkgの数値だと思って再利用しないように、計算用の列と表示だけの調整を明確に区別します。本例では換算後の値が必要なので、数式で割る方法を採用しています。

6. CSVとTEXT関数では文字列の扱いを確認する

CSVはセルの書式設定そのものを保存しませんが、「だから単位が必ず落ちて生の数値になる」とは限りません。ExcelのCSV保存では、セルに表示されているテキストや値が書き出されます。単位付きの表示がCSV内に残ることがあるため、数値だけを必要とするシステムへ、そのまま渡さないでください。

数値だけのCSVを作るなら、出力用のシートで数値列を用意し、単位は見出しや別列で管理します。元表からコピーした場合は、出力用の数値列を選び、表示形式を単位なしの「標準」または必要な小数桁数の「数値」に変更します。元のxlsxを保存してからCSVを書き出し、テキストエディターなどで実際の内容を確認します。Excelで開き直すだけでは、日付や数値への自動変換で出力内容を見誤ることがあります。

逆に、文章へ差し込むため「100個」という文字列が必要なら、別セルで=TEXT(A2,"0")&"個"とします。A2が100なら結果は「100個」です。TEXTの結果は計算用の数値ではないため、合計は元のA列を参照します。渡し先が求めるのが数値か文字列かを先に確認すると、余計な変換を避けられます。

7. 仕上げは元の値と合計を照合する

設定後は、数値として認識されること、0が必要どおり表示されること、小数の丸めが誤解を生まないこと、合計セルの単位が正しいことを確認します。単位を変えた場合は、1500gが1.5kgになるような既知の値で確かめると、換算漏れを発見できます。

仕様はMicrosoftのユーザー定義の表示形式、書式コードのガイドライン、他形式に保存した場合の違いを参照できます。計算する値を保ち、読み手に必要な単位を表示する、という役割分担で表を整えましょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】