例文を使って繰り返し業務を時短

【マクロ・VBA】特定の列の指数表示を変更する

エクセル、マクロ、VBAで指数表示を変更する方法 VBA

指数表示とは

指数表示は、非常に大きな数字や小さな数字を簡潔に表現するために使われます。例えば、1,000,000は10の6乗、0.000001は10のマイナス6乗と表現されます。これらの数値を、指数表示を使うことで、「1.0E+06」や「1.0E-06」といった短い表記で表すことができます。

例えば、大きな数を書くときに、「100万」と書く代わりに、「10の6乗」と書くことができるように、指数表示は「1.0E+06」という書き方で、10の6乗を表しています。同じように、小さな数を書くときに、「0.000001」と書く代わりに、「10のマイナス6乗」と書くことができるように、指数表示は「1.0E-06」という書き方で、10のマイナス6乗を表しています。

指数表示を使うことで、非常に大きな数や小さな数を簡潔に表現することができ、複雑な計算を行う際にも、数字が混乱することなく、正確に計算することができます。

Format関数

VBAで指数表示させるには、数値を表す変数に対して、Format 関数を使用し、指数表示を表す文字列を指定します。

Dim x As Double
x = 1234567890
Debug.Print Format(x, "0.00E+00")

この場合、Format 関数の第一引数には数値を表す変数 x を指定し、第二引数には指数表示を表す文字列 "0.00E+00" を指定しています。この文字列は、数値を小数点以下2桁まで表示し、指数部分を E+00 形式で表現することを指示しています。

上記の例では、変数 x に格納された数値 1234567890 は、指数表記で 1.23E+09 と表示されます。

エクセルで指数表示されてしまう要因とは

エクセルで指数表示になる主な要因は、セルの書式設定にあります。エクセルは、大きな数値を扱う場合に、自動的に指数表記に変換することがありますが、その挙動は書式設定によって異なります。

例えば、数値がセルに収まりきらない場合や、セルの書式が一定の桁数を超える場合には、エクセルは指数表記に変換することがあります。また、セルの書式が指数表記に設定されている場合には、数値が自動的に指数表記に変換されます。

指数表記を回避する方法

  1. セルの書式を一般形式に変更する 　セルの書式を一般形式に変更すると、指数表記を回避できます。具体的には、セルを選択し、[ホーム] タブの [数値] グループから [一般] を選択します。
  2. セルの幅を広げる 　セルの幅を広げることで、数値が指数表記になることを回避できます。セルを選択し、マウスをセルの右端に移動させると、カーソルが変化し、セルの幅を調整できます。
  3. 数値をテキストとして扱う 　数値をテキストとして扱うことで、指数表記を回避できます。具体的には、数値の前にシングルクオート（’）を付けて、セルに入力します。例えば、’1234567890 と入力することで、指数表記を回避できます。

指数表示を直す方法の解説

CDbl 関数

e.Value = CDbl(e.Value)

上記のコードでは、CDbl 関数を使用して、e.Value の値を倍精度浮動小数点数の数値形式に変換しています。これにより、指数表記を回避し、数値形式で値を取得できます。

また、指数表記が発生しないように、値を出力する前に適切な書式を指定することもできます。例えば、以下のように記述することで、小数点以下2桁までの数値形式で値を出力することができます。

e.NumberFormat = "0.00"
e.Value = CDbl(e.Value)

上記のコードでは、NumberFormat プロパティを使用して、セルの書式を "0.00" に指定しています。その後、先に説明した CDbl 関数を使用して、e.Value の値を数値形式に変換しています。このようにすることで、指数表記を回避しつつ、小数点以下2桁までの数値形式で値を出力することができます。

指数表示を直す方法　Part1

このマクロは、Excelのセルに表示されている値が正しく格納されているが、表示形式が変更されない場合に、セルの表示形式を更新するために使用されます。

Sub 表示形式が変更されない場合の対処()

   'セル選択ダイアログを表示
    Set selectedRange = Application.InputBox("セル範囲を選択", Type:=8)

    For Each e In Selection
        e.Value = e.Value
    Next

MsgBox "完了"
End Sub

解説

  • Set selectedRange = Application.InputBox("セル範囲を選択", Type:=8) の行で、ユーザーにセル範囲を選択するように求めるダイアログが表示されます。Type:=8 は、入力する値が範囲であることを指定するためのオプションです。
  • 選択されたセル範囲内の各セルに対して、For Each e In Selection のループが開始されます。e は、範囲内の各セルを表します。
  • 各セルの値を再計算するために、e.Value = e.Value の行が実行されます。この行により、数値が再計算され、指数表記などの表示形式が自動的に変更されます。
    • これは、選択されたセルの値を取得して、同じ値を再びセルに設定することで、セルの表示形式を更新することができます。このように、値を再入力することによって、表示形式が変更されたセルを再描画することができます。
  • ループが終了すると、MsgBox "完了" の行で、処理が完了したことを示すメッセージボックスが表示されます。
  • サブルーチンが終了します。

※このコードは、指数表記などの表示形式が変更されない場合に使用されることを想定しています。　　　　　　セル選択ダイアログが表示され、ユーザーが範囲を選択することで、範囲内のセルの値を再計算し、表示形式を変更することができます。

指数表示を直す方法　Part２

このマクロは、選択範囲のデータをテキスト形式に変換して、指数表示を直すことができます。

例えば、選択範囲のデータを指数表記にしていた場合、例えば “1.23E+09″ のように表示されていた場合、このマクロを実行することで、”1,230,000,000” のように通常の数値表示に変換することができます。

Sub 指数表示を直す2() 

Selection.TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, xlTextFormat)
'Columns("A:A").Select
'Selection.NumberFormatLocal = "@"

MsgBox "完了"
End Sub

解説

  • Selection.TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, xlTextFormat) : 選択範囲をテキスト形式に変換します。この際、データの区切り文字が必要ですが、このマクロでは、xlDelimited という引数を指定することで、区切り文字が自動的に認識されるようになっています。また、FieldInfo:=Array(1, xlTextFormat) という引数を指定することで、1列目にはテキスト形式でデータを表示するように設定しています。
  • 'Columns("A:A").Select : A列を選択します。この行はコメントアウトされているので、現在は実行されていません。
  • 'Selection.NumberFormatLocal = "@" : 選択範囲をテキスト形式に変換するためのコードです。この行はコメントアウトされているので、現在は実行されていません。
  • MsgBox "完了" : 処理が完了したことを示すメッセージボックスを表示します。

指数表示を直す方法　Part３

Sub 複数列_指数表示から文字列へ()

'該当列を複数選択する

Dim r As Range

For Each r In Selection.Columns
         r.TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, xlTextFormat)
Next

End Sub

解説

  • Selection.Columnsを使用して、ユーザーが選択した複数の列について、各列に対して以下の処理を繰り返します。
  • TextToColumnsメソッドを使用して、各列内のすべてのセルに対して、指数表記から文字列に変換する処理を実行します。
  • DataType:=xlDelimitedは、区切り記号を使用して文字列を分割することを指定します。FieldInfo:=Array(1, xlTextFormat)は、分割するフィールドのフォーマットを指定するためのオプションであり、1番目のフィールドをテキスト形式に変換することを示します。
  • 処理が終了すると、サブルーチンが終了します。

※つまり、このVBAコードは、ユーザーが選択した複数列について、各列内のすべてのセルの指数表記を文字列に変換する処理を行います。このコードは、複数の列を一度に処理することができ、手動で数値を文字列に変換する手間を省くことができます。

コメント

タイトルとURLをコピーしました