雑・Excel入門試論 - 脱VLOOKUPの思考 05 - セル - 数値データ

Last updated at 2023-01-17Posted at 2022-12-04

エクセルの入力評価

　エクセルのセルに入力し、Enterキーを押すとエクセルは以下の順序で処理を行います。

先頭に文字列プレフィックス（シングルクォート）がある場合、入力されたすべての文字を修正せずに、入力されたとおりにセルに配置します。ただし、文字列プレフィックスは表示されません。
入力が = + - で始まる場合、エクセルは数式を想定し、数式パーサーを使用して構文をチェックします。数式が意味を成さない場合はエラーダイアログが表示されます。
それ以外の場合、エクセルは入力したものが日付、時間、通貨単位、パーセント、または単なる数字のようにみえるかどうかを判断しようとします。これらのいずれでもない場合、入力は文字列とみなされ、変更されずにセルに配置されます。
- 注意：テキストよりも日付や時刻を先に認識します。 2:1 は、比率の文字列ではなく、時間形式に変更し、2:10:00 、数値表現で0.084027777 という数値に変換します。このような入力では、シングルクオートを前につけなくてはなりません。
数式とみなされた場合、関数を調べ、一番内側にあるものから順に評価しようとします。セル参照と範囲は値に変換され、必要に応じて正しいデータ型に変換されるなど、様々な処理が行われます。関数名や定義名として認識されないトークンがあった場合、変換と評価は #NAME? エラーで失敗します。それ以外の定義された名前は、セルや式と同じ方法で変換されます。
入力が確定すると、エクセルは新しい入力に依存するものを再計算しようとします。数値の入っていたセルに数値を入れ直した場合、値が変更されている場合のみ再計算が行われます。参照を含む数式が入力された場合、新しい数式が循環参照になっていないことを確認し、循環参照になっていた場合はメッセージを表示します。
セルの書式設定によっては、列幅または行の高さを変更することがあります。

データ型とフォーマット

　データの表示形式を整えるために、データ自体を変換する方法があります。
　しかし、数値にカンマ、ピリオドやプラス・マイナス記号や括弧、単位の追加などを行ってテキスト型に変換してしまうことは、元のデータを汚してしまうことになります。つまり、データを再利用するためには、正しく逆変換を行う手順が必要になりますし、精度を変えてしまうと元のデータを取り出すことはできません。
　そこで、前回と今回、そして次回に渡って紹介する、元のデータを変更せずに、書式設定を使って表示形式を変更する方法が重要になります。

元のデータの型や精度を変更しないことは、非常に大切な意味があります。

数値のデータ型

　数値データは、限られた精度で機能しています。エクセル内部では8バイトの倍精度浮動小数点(double)で扱われ、誤った値、無限大、および非正規化された数値に関するいくつかの例外を除き、Excel はIEEE 754 仕様の倍精度浮動小数点形式で計算します。取り扱いできる数値は、有効桁数15桁です。
　例えば、12345678901234567890 という数値を入力すると、セルの中は指数表記になり、数式バーには 12345678901234500000 と表示されます。15桁以上の数値は切り捨てられゼロになってしまいます。

　これは、小数点が含まれても同様で、 1234567890.1234567890 は、 1234567890.12345 となってしまいます。

上記2例は、Open XMLのファイルでは以下のようになっています。

    <row r="1" spans="1:1" x14ac:dyDescent="0.4">
      <c r="A1">
        <v>1.23456789012345E+19</v>
      </c>
    </row>
    <row r="2" spans="1:1" x14ac:dyDescent="0.4">
      <c r="A2">
        <v>1234567890.12345</v>
      </c>
    </row>

　ただし、内部で16桁まで格納されることがあります。

　演算は浮動小数点で行われており、小数点が含まれる数値の演算では誤差が発生することがあります。
　例えば、 =1*(0.5-0.4-0.1) はゼロになりません。これらはバグではなく、公式の仕様による制限です。

　非常に小さな数値の誤差は、丸めによって解決することができますが、結果が0や1になることを期待する計算で、そのとおりにならない時は問題になるかもしれません。計算前に整数値にしておき、計算後に戻すなどの処理を行うことがあります。
　また、有効桁数を超えた大きさの異なる数値の計算は行うことができません。10,000,000,000,000,000 + 1 の答えは、10,000,000,000,000,000 になります。

銀行丸め (bankers' rounding)

　丸めのための関数には、以下のようなものがあります。

INT
MROUND
ROUND
ROUNDDOWN
ROUNDUP
TRUNC

　一般的な丸め処理には、四捨五入の他に銀行丸め（bankers' rounding、JIS丸め、偶数まるめ）と言われるものがあります。この処理は、.5 の時、偶数になるように丸める方法です。
下図は小数点以下を銀行丸めした例です。

　単体のエクセル関数では、この銀行丸めを行う関数がありません。したがって、この丸めを行うにはIF文を使った式を書くことになります。

表示桁数で計算する (Set precision as displayed)

Excelには「表示桁数で計算する (Set precision as displayed)」というオプションがあり、オプションの「計算」タブで有効にすることができます。このオプションを有効にすると、Excelは計算の際に本来の数値ではなく、表示されている値を使用するように強制されます。しかし、このオプションを使用する際には十分な注意が必要です。一度有効にすると、すべての精度が失われ、元に戻すことはできません。すべてのセルは、表示されている値に基づいて計算されます。このオプションは、特定のセルやセル範囲にではなく、ワークブック全体に適用されます。

データの種類

　データには 量的データ と 質的データ の２種類があります。数値データは主に量的データとして扱われることが多いのですが、時には順序尺度の質的データとして扱われることがあります。例えば、性別や血液型を数値化して扱う場合や、学年や順位などです。

　また、量的データにも 間隔尺度 と 比例尺度 の２種類があります。

データの種類と尺度
データの種類	尺度の種類	説明	例	計算
質的データ	名義尺度	分類の順序に意味がない	性別、血液型	不可
質的データ	順序尺度	分類の順序に意味がある	順位、学年	不可
量的データ	間隔尺度	データの間隔だけが意味を持つ	温度、西暦	和差
量的データ	比例尺度	データの比率に意味があり、値０が意味を持つ	身長、体重、距離	和差積商

　データの特性に合わせて、適切な形式を使用することを心がけてください。

コンテンツ

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up